Ejemplo n.º 1
0
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
}
Ejemplo n.º 3
0
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)
}
Ejemplo n.º 4
0
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))
}
Ejemplo n.º 5
0
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)
	}
}
Ejemplo n.º 7
0
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
}
Ejemplo n.º 8
0
func ConnectDb(filename string) error {
	_con, err := sqlite.Open(filename)
	con = _con
	return err
}