func trysqlite() {
conn, err := sqlite.Open(":memory:")
err = conn.Exec("create table x (a integer);")
dieOnError(err)
err = conn.Exec("insert into x values (1);")
dieOnError(err)
stmt, err := conn.Prepare("select a, 'hi' jacksons from x;")
dieOnError(err)
info, err := gemini.LoadTableFromSqlite(stmt)
dieOnError(err)
err = stmt.Finalize()
dieOnError(err)
err = conn.Close()
dieOnError(err)
tables := make(gemini.TableSet)
tables["stuff"] = info
fmt.Printf("%v", *tables["stuff"])
}
func (self *BundleManager) connGet() (*sqlite.Conn, error) {
conn, err := sqlite.Open(filepath.Join(common.StateDir, "bundlemanager.sdb"))
if err != nil {
log.Printf("connGet-Open: %v\n", err)
return nil, err
}
conn.BusyTimeout(1000 * 60 * 10) //Ten min.
err = conn.Exec("PRAGMA foreign_keys = ON;")
if err != nil {
log.Printf("connGet-foreign_keys: %v\n", err)
return nil, err
}
return conn, nil
}
func TestStoreTableToSqlite(t *testing.T) {
conn, err := sqlite.Open(":memory:")
fatalOnError(err, t)
tableInfo := &Table{
ColumnNames: []string{"name", "age", "height"},
ColumnTypes: []ColumnDatatype{
StringDatatype,
IntegerDatatype,
FloatDatatype,
},
}
tableInfo.initData()
tableInfo.writeRow([]interface{}{"tim", 5, 1.1})
tableInfo.writeRow([]interface{}{"lao", 4, 1.5})
err = StoreTableToSqlite(conn, "people", tableInfo)
fatalOnError(err, t)
err = conn.Close()
fatalOnError(err, t)
}
func TestLoadTableFromSqlite(t *testing.T) {
conn, err := sqlite.Open(":memory:")
fatalOnError(err, t)
err = conn.Exec("create table x (x integer);")
fatalOnError(err, t)
err = conn.Exec("insert into x values (1);")
fatalOnError(err, t)
stmt, err := conn.Prepare("select x, 'hi' jacksons from x;")
fatalOnError(err, t)
info, err := LoadTableFromSqlite(stmt)
fatalOnError(err, t)
var buf bytes.Buffer
err = info.JSONWrite(&buf)
fatalOnError(err, t)
js, err := ioutil.ReadAll(&buf)
t.Log(string(js))
}
func MurmurImport(filename string) (err os.Error) {
db, err := sqlite.Open(filename)
if err != nil {
panic(err.String())
}
stmt, err := db.Prepare("SELECT server_id FROM servers")
if err != nil {
panic(err.String())
}
var servers []int64
var sid int64
for stmt.Next() {
stmt.Scan(&sid)
servers = append(servers, sid)
}
log.Printf("Found servers: %v (%v servers)", servers, len(servers))
for _, sid := range servers {
m, err := NewServerFromSQLite(sid, db)
if err != nil {
return err
}
err = m.FreezeToFile(filepath.Join(*datadir, fmt.Sprintf("%v", sid)))
if err != nil {
return err
}
log.Printf("Successfully imported server %v", sid)
}
return
}
func (self *stateDatabase) setupDatabase() {
self.mutex.Lock()
defer self.mutex.Unlock()
common.Dprintln("Entered stateDatabase.setupDatabase")
//Open or create the database
c, err := sqlite.Open(self.path)
if err != nil {
log.Fatalf("Could not open or create %s, error: %v\n", self.path, err)
}
self.conn = c
//Turn on foreign key constraints
err = self.conn.Exec("PRAGMA foreign_keys = ON;")
if err != nil {
log.Fatalf("%v\n", err)
}
//Prepare to get the current database version
s, err := self.conn.Prepare("select value from system where name = \"version\";")
//The system table does not exist
if err != nil && err.Error() == "SQL error or missing database: no such table: system" {
err = self.conn.Exec("create table system(name string primary key, value string);")
if err != nil {
log.Fatalf("%v\n", err)
}
fileStatesSchema := "CREATE TABLE file_states(" +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"last_modified INTEGER NOT NULL, " +
"digest STRING NULLABLE, " +
"full_path STRING NOT NULL, " +
"last_seen INTEGER NOT NULL, " +
"bundle_id INTEGER NULLABLE, " +
"bundle_file_id INTEGER NULLABLE, " +
"pass_off INTEGER NOT NULL, " +
"user_name STRING NOT NULL, " +
"rule_name STRING NOT NULL, " +
"auto_added_to_bundle_time INTEGER NOT NULL);"
//Create the file_states table
err = self.conn.Exec(fileStatesSchema)
if err != nil {
log.Fatalf("%v\nSQL: %s\n", err, fileStatesSchema)
}
userBundlesSchema := "CREATE TABLE user_bundles(" +
"user STRING PRIMARY KEY, " +
"auto_submit_bid INTEGER NULLABLE, " +
"no_auto_submit_bid INTEGER NULLABLE, " +
"auto_submit_last_touched INTEGER NOT NULL);"
//Create the user_bundles table
err = self.conn.Exec(userBundlesSchema)
if err != nil {
log.Fatalf("%v\nSQL: %s\n", err, userBundlesSchema)
}
schema := strings.Split("create index file_states_full_path ON file_states(full_path);"+
"create index file_states_id ON file_states(id);", ";")
for _, sql := range schema {
if strings.TrimSpace(sql) == "" {
continue
}
err = self.conn.Exec(sql + ";")
if err != nil {
log.Printf("%v\nSQL: %s\n", err, sql)
os.Exit(1)
}
}
//Set the database version
err = self.conn.Exec("INSERT INTO system(name, value) VALUES(\"version\", \"" +
strconv.Itoa(int(_DATABASE_CODE_VER)) + "\");")
if err != nil {
log.Fatalf("%v\n", err)
}
//Prepare statement to get the database version
s, err = self.conn.Prepare("select value from system where name = \"version\";")
if err != nil {
log.Fatalf("%v\n", err)
}
}
//Get the database version
err = s.Exec()
if err != nil {
log.Fatalf("%v\n", err)
}
version := ""
for {
if !s.Next() {
break
}
var value string
err = s.Scan(&value)
if err != nil {
log.Fatalf("%v", err)
}
version = value
}
if version == "" {
log.Fatalf("Failed to get a version from database.\n")
}
ver, err := strconv.ParseInt(version, 10, 32)
if err != nil {
log.Fatalf("Unable to read version %s %v\n", version, err)
}
log.Printf("Got file manager database version %v\n", ver)
if ver > _DATABASE_CODE_VER {
log.Fatalf("File manager database is too new(%v). Version is %v\n", ver, _DATABASE_CODE_VER)
}
if ver < _DATABASE_CODE_VER {
if ver == 1 {
schema := strings.Split("begin transaction;"+
"create index file_states_full_path ON file_states(full_path);"+
"create index file_states_id ON file_states(id);"+
"update system set value = 2 where name=\"version\";"+
"commit;", ";")
for _, sql := range schema {
if strings.TrimSpace(sql) == "" {
continue
}
err = self.conn.Exec(sql + ";")
if err != nil {
log.Printf("Failed to upgrade schema! %v\nSQL: %s\n", err, sql)
os.Exit(1)
}
}
ver = 2
}
}
if ver != _DATABASE_CODE_VER {
log.Fatalf("File state manager database needs to be upgraded(%v). I'm %v\n", ver, _DATABASE_CODE_VER)
}
}
func (d *Datamart) PerformQueries() (TableSet, error) {
// os.Remove("/tmp/blah.db")
// conn, err := sqlite.Open("/tmp/blah.db")
conn, err := sqlite.Open(":memory:")
if err != nil {
return nil, err
}
err = StoreTableToSqlite(conn, "source", d.SourceTableData)
if err != nil {
return nil, err
}
dimDefs := d.SetupDimDefinitions()
err = d.CreateDimensionTables(dimDefs, conn)
if err != nil {
return nil, err
}
err = d.CreateFactTable(dimDefs, conn)
if err != nil {
return nil, err
}
ret := make(TableSet)
// factable
query := "select * from fact;"
stmt, err := conn.Prepare(query)
if err != nil {
return nil, err
}
err = stmt.Exec()
if err != nil {
return nil, err
}
ret["fact"], err = LoadTableFromSqlite(stmt)
if err != nil {
return nil, err
}
// dimtables
// should be just "select * from dimtable orderby indexcolumn" but
// these selects are more complicated because need ids to start a 0
// and sqlite auto increment starts at 1
// annoying
for name, dim := range dimDefs {
query := fmt.Sprintf(
"select %s - 1 %s, %s",
dim.IndexColumn,
dim.IndexColumn,
dim.UniqueColumn,
)
if len(dim.ExtraColumns) > 0 {
query += "," + strings.Join(dim.ExtraColumns, ",")
}
query += fmt.Sprintf(" from %s order by %s;", name, dim.IndexColumn)
stmt, err := conn.Prepare(query)
if err != nil {
return nil, fmt.Errorf(
"PerformQueries() error, sqlite error: %s\nquery:\n%s\n",
err.Error(),
query,
)
}
err = stmt.Exec()
if err != nil {
return nil, err
}
ret[name], err = LoadTableFromSqlite(stmt)
if err != nil {
return nil, err
}
}
err = conn.Close()
if err != nil {
return nil, err
}
return ret, nil
}
func ConnectDb(filename string) error {
_con, err := sqlite.Open(filename)
con = _con
return err
}
