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
}
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")
}
}
}
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
}
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()
}
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
}
// 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
}
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())
}
}
// 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
}
// 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)
}
}
}
}
}
func put(args sqlite3.NamedArgs) {
db, _ := sqlite3.Open("sgmq.db")
db.Exec("INSERT INTO jobs VALUES($Id, $Title, $Message, $Lock)", args)
}
