Exemple #1
1
func InitializeDB(coords ConnCoordinates) (*sqlite3.Conn, error) {
	// attempt to open the sqlite db file
	db, dbErr := sqlite3.Open(path.Join(coords.DBPath, coords.DBFile))
	if dbErr != nil {
		return db, dbErr
	}

	// load the table definitions file, if coords.DBTablesPath is defined
	if len(coords.DBTablesPath) > 0 {
		content, err := ioutil.ReadFile(path.Join(coords.DBTablesPath, TABLE_SQL_DEFINITIONS))
		if err != nil {
			return db, err
		}

		// attempt to create (if not exists) each table
		tables := strings.Split(string(content), ";")
		for _, table := range tables {
			err = db.Exec(table)
			if err != nil {
				return db, err
			}
		}
	}

	return db, nil
}
Exemple #2
0
func main() {
	sql, _ := sqlite3.Open("./sqlite.sqlite")
	mode := os.Args[1]
	var qu Table
	if mode == "select" {
		q, err := sql.Query("SELECT * FROM \"main\".\"test\" ORDER BY id DESC LIMIT 1;")
		if err != nil {
			fmt.Printf("Error while getting data: %s\n", err)
		} else {
			err = q.Scan(&qu.Id, &qu.Text, &qu.Time)
			if err != nil {
				fmt.Printf("Error while getting row data: %s\n", err)
			}
			fmt.Println("Last inserted data is: " + qu.Text)
			t := time.Unix(qu.Time, 0)
			fmt.Println("Inserted on " + t.Format(time.RFC1123Z))
		}
	} else {
		if len(os.Args) > 2 {
			data := os.Args[2]
			now := strconv.FormatInt(time.Now().Unix(), 10)
			sql.Query("INSERT INTO \"main\".\"test\" (\"text\", \"time\") VALUES('" + data + "', '" + now + "')")
			fmt.Println(data + " inserted")
		}
	}
}
Exemple #3
0
func getConn(dbPath string) *sqlite3.Conn {
	conn, err := sqlite3.Open(dbPath)
	if err != nil {
		log.Panic("open db ", err)
	}
	conn.Exec(`PRAGMA foreign_keys = ON;`)

	return conn
}
Exemple #4
0
func dbConnectionAndClose() {
	c, _ := sqlite3.Open("E:/go/dev/sample/src/sqlite/test.s3db")
	sql := "SELECT * FROM userinfo"
	row := make(sqlite3.RowMap)
	for s, err := c.Query(sql); err == nil; err = s.Next() {
		var rowid int64
		s.Scan(&rowid, row)     // Assigns 1st column to rowid, the rest to row
		fmt.Println(rowid, row) // Prints "1 map[a:1 b:demo c:<nil>]"
	}

	defer c.Close()
}
Exemple #5
0
func OpenConn() *sqlite3.Conn {
	file_exists, _ := File_exists(DB_FILE)
	if !file_exists {
		init_db()
	}
	conn, err := sqlite3.Open(DB_FILE)
	if err != nil {
		Err("Unable to access local db: " + err.Error())
		return nil
	}
	return conn
}
Exemple #6
0
// Initialize database with mutexes from file.
func Initialize(log chan string, dbFile string) error {
	var err error
	if LocalDB != nil {
		return nil
	}

	localMutex = new(sync.Mutex)

	// Create Database Connection
	LocalDB, err = sqlite3.Open(dbFile)
	if err != nil || LocalDB == nil {
		log <- fmt.Sprintf("Error opening sqlite database at %s... %s", dbFile, err)
		LocalDB = nil
		return err
	}

	// Create Database Schema

	err = LocalDB.Exec("CREATE TABLE IF NOT EXISTS addressbook (hash BLOB NOT NULL UNIQUE, address BLOB NOT NULL UNIQUE, registered INTEGER NOT NULL, pubkey BLOB, privkey BLOB, label TEXT, subscribed INTEGER NOT NULL, PRIMARY KEY (hash) ON CONFLICT REPLACE)")
	if err != nil {
		log <- fmt.Sprintf("Error setting up addressbook schema... %s", err)
		LocalDB = nil
		return err
	}

	// Migration, Ignore error
	LocalDB.Exec("ALTER TABLE addressbook ADD COLUMN subscribed INTEGER NOT NULL DEFAULT 0")
	LocalDB.Exec("ALTER TABLE addressbook ADD COLUMN encprivkey BLOB")

	err = LocalDB.Exec("CREATE TABLE IF NOT EXISTS msg (txid_hash BLOB NOT NULL, recipient BLOB, timestamp INTEGER, box INTEGER, encrypted BLOB, decrypted BLOB, purged INTEGER, sender BLOB, PRIMARY KEY (txid_hash) ON CONFLICT REPLACE)")
	if err != nil {
		log <- fmt.Sprintf("Error setting up msg schema... %s", err)
		LocalDB = nil
		return err
	}

	if hashList == nil {
		hashList = make(map[string]int)
		return populateHashes()
	}

	if LocalDB == nil || hashList == nil {
		fmt.Println("ERROR! ERROR! WTF!!! SHOULD BE INITIALIZED!")
	}

	return nil
}
Exemple #7
0
func init_db() {
	file_exists, _ := File_exists(DB_FILE)
	if !file_exists {
		Info("Local DB doesn't exist, creating new")
	}

	conn, err := sqlite3.Open(DB_FILE)
	if err != nil {
		panic(err)
	}

	create_table(CPUTIMES_TB, CPUTIMES_CREATE, conn)
	create_table(LOADAVG_TB, LOADAVG_CREATE, conn)
	create_table(VIRTUAL_MEMORY_TB, VIRTUAL_MEMORY_CREATE, conn)
	create_table(SWAP_MEMORY_TB, SWAP_MEMORY_CREATE, conn)

	err = conn.Close()
	if err != nil {
		Err("Closing local DB: " + err.Error())
	}
}
Exemple #8
0
// Initialize Database connection from Database File (Absolute Path), mutexes, and the global hash list.
func Initialize(log chan string, dbFile string) error {
	var err error
	if dbConn != nil {
		return nil
	}

	mutex = new(sync.Mutex)

	// Create Database Connection
	dbConn, err = sqlite3.Open(dbFile)
	if err != nil || dbConn == nil {
		log <- fmt.Sprintf("Error opening sqlite database at %s... %s", dbFile, err)
		dbConn = nil
		return err
	}

	// Create Database Schema
	err = dbConn.Exec("CREATE TABLE IF NOT EXISTS pubkey (hash BLOB NOT NULL UNIQUE, payload BLOB NOT NULL, PRIMARY KEY (hash))")
	if err != nil {
		log <- fmt.Sprintf("Error setting up pubkey schema... %s", err)
		dbConn = nil
		return err
	}

	err = dbConn.Exec("CREATE TABLE IF NOT EXISTS purge (hash BLOB NOT NULL UNIQUE, txid BLOB NOT NULL UNIQUE, PRIMARY KEY (hash))")
	if err != nil {
		log <- fmt.Sprintf("Error setting up purge schema... %s", err)
		dbConn = nil
		return err
	}

	err = dbConn.Exec("CREATE TABLE IF NOT EXISTS msg (hash BLOB NOT NULL UNIQUE, addrHash BLOB NOT NULL, timestamp INTEGER NOT NULL, payload BLOB NOT NULL, PRIMARY KEY (hash))")
	if err != nil {
		log <- fmt.Sprintf("Error setting up msg schema... %s", err)
		dbConn = nil
		return err
	}

	err = dbConn.Exec("CREATE TABLE IF NOT EXISTS pub (hash BLOB NOT NULL UNIQUE, addrHash BLOB NOT NULL, timestamp INTEGER NOT NULL, payload BLOB NOT NULL, PRIMARY KEY (hash))")
	if err != nil {
		log <- fmt.Sprintf("Error setting up pub schema... %s", err)
		dbConn = nil
		return err
	}

	err = dbConn.Exec("CREATE TABLE IF NOT EXISTS peer (ip BLOB NOT NULL, port INTEGER NOT NULL, port_admin INTEGER NOT NULL, last_seen INTEGER NOT NULL, id INTEGER PRIMARY KEY AUTOINCREMENT)")
	if err != nil {
		log <- fmt.Sprintf("Error setting up peer schema... %s", err)
		dbConn = nil
		return err
	}

	err = dbConn.Exec("CREATE UNIQUE INDEX IF NOT EXISTS ip_index ON peer (ip, port, port_admin)")
	if err != nil {
		log <- fmt.Sprintf("Error setting up peer index... %s", err)
		dbConn = nil
		return err
	}

	if hashList == nil {
		hashList = make(map[string]int)
		return populateHashes()
	}

	if dbConn == nil || hashList == nil {
		fmt.Println("ERROR! ERROR! WTF!!! SHOULD BE INITIALIZED!")
	}

	return nil
}
Exemple #9
0
// The evaluate function collects all the data and stores it in a database
func evaluate(context *cli.Context) {

	// Create our tables
	db, err := sqlite3.Open(context.String("db"))
	if err != nil {
		logger.Error("Could not open SQLite3 DB %v: %v", context.String("db"), err.Error())
		os.Exit(1)
	}
	for table, s := range create_tables {
		err = db.Exec(s)
		if err != nil {
			logger.Error("Could not create %v table: %v", table, err.Error())
			os.Exit(1)
		}
	}
	for idx, SegmentedDir := range context.Args() {

		// Check the segmented directory
		ReportFile := filepath.Join(SegmentedDir, "reads.json")
		if !Exists(ReportFile) {
			logger.Info("reads file DOES NOT exist, skipping (%v)", ReportFile)
			continue
		}

		logger.Debug("Processing (%v/%v) %v", idx+1, len(context.Args()), ReportFile)
		// Now parse the JSON
		JsonFile := filepath.Join(SegmentedDir, "reads.json")
		fid, err := os.Open(JsonFile)
		if err != nil {
			logger.Error("Error opening %v -- %v", JsonFile, err.Error())
			continue
		}
		everything, _ := jason.NewObjectFromReader(fid)
		fid.Close()
		series_uid, _ := everything.GetString("uid")
		Save(db, "series", series_uid, everything)

		// Looping over all the reads
		reads, _ := everything.GetObjectArray("reads")
		for _, read := range reads {
			nodules, _ := read.GetObjectArray("nodules")
			for _, nodule := range nodules {

				// Create an entry for the nodule
				normalized_nodule_id, _ := nodule.GetInt64("normalized_nodule_id")
				nodule_uid := fmt.Sprintf("%v.%v", series_uid, normalized_nodule_id)
				db.Exec("insert or ignore into nodules ( uid, series_uid, normalized_nodule_id ) values (?,?,?)", nodule_uid, series_uid, normalized_nodule_id)

				// Create the read
				read_id, _ := read.GetInt64("id")

				logger.Debug("Read %v -- nodule %v", read_id, normalized_nodule_id)

				read_uid := fmt.Sprintf("%v.%v.%v", series_uid, read_id, normalized_nodule_id)
				Save(db, "reads", read_uid, nodule)
				characteristics, _ := nodule.GetObject("characteristics")
				Save(db, "reads", read_uid, characteristics)
				db.Exec("update reads set nodule_uid = ? where uid = ?", nodule_uid, read_uid)

				// evaluate segmentation
				suffix := fmt.Sprintf("_read_%v_nodule_%v_eval.json", read_id, normalized_nodule_id)
				files, _ := filepath.Glob(filepath.Join(SegmentedDir, "*"+suffix))
				for _, measures := range files {
					logger.Debug("Looking at results file: %v", measures)
					// Now, read and combine into a SQLite DB
					fid, _ := os.Open(measures)
					measure_object, _ := jason.NewObjectFromReader(fid)
					fid.Close()
					// The UID is the SHA1 of the command string
					command_line, _ := measure_object.GetString("command_line")
					measure_detail, _ := measure_object.GetObject("measures")
					measures_uid := fmt.Sprintf("%x", sha1.Sum([]byte(command_line)))
					Save(db, "measures", measures_uid, measure_detail)
					db.Exec("update measures set command_line = ? where uid = ?", command_line, measures_uid)
					db.Exec("update measures set nodule_uid = ? where uid = ?", nodule_uid, measures_uid)
					db.Exec("update measures set read_uid = ? where uid = ?", read_uid, measures_uid)
				}
			}
		}
	}
}
Exemple #10
0
func put(args sqlite3.NamedArgs) {
	db, _ := sqlite3.Open("sgmq.db")
	db.Exec("INSERT INTO jobs VALUES($Id, $Title, $Message, $Lock)", args)
}