func (mdb *MailDB) backend() {
//init db
conn, err := sqlite.Open("maildb.db")
if err != nil {
log.Print("Unable to open the database: ", err)
return
}
conn.Exec("CREATE TABLE mails(id INTEGER PRIMARY KEY AUTOINCREMENT, sender VARCHAR, subject VARCHAR, text VARCHAR,time INTEGER);")
conn.Close()
for {
event := <-mdb.In
if event.Topic == "SaveMail" {
mdb.SaveMail(event.Payload.(*Mail))
} else if event.Topic == "GetMailList" {
pay := event.Payload.(*GetMailListPayload)
list := mdb.GetMailList(pay.Max)
pay.Return <- list
} else if event.Topic == "GetMail" {
pay := event.Payload.(*GetMailPayload)
pay.Return <- mdb.GetMail(pay.Id)
} else if event.Topic == "DeleteMail" {
pay := event.Payload.(*GetMailPayload)
ok := mdb.DeleteMail(pay.Id)
if ok {
pay.Return <- &Mail{Id: 1}
} else {
pay.Return <- &Mail{Id: 0}
}
}
}
}
func (mdb *MailDB) GetMailList(maxcount int) []*Mail {
conn, err := sqlite.Open("maildb.db")
if err != nil {
log.Print("Unable to open the database: ", err)
return nil
}
s := "SELECT id,sender,subject,time FROM mails ORDER BY id ASC LIMIT " + strconv.Itoa(maxcount) + ";"
selectstmt, err := conn.Prepare(s)
if err != nil {
log.Print("Error preparing the selectstatement: ", s)
return nil
}
err = selectstmt.Exec()
if err != nil {
log.Print("Error execution of select stmt")
return nil
}
erglist := make([]*Mail, 0)
for {
if selectstmt.Next() {
var mail Mail
err = selectstmt.Scan(&mail.Id, &mail.Sender, &mail.Subject, &mail.Time)
if err != nil {
log.Print("Error while scanning row")
return erglist
}
erglist = append(erglist, &mail)
} else {
break
}
}
return erglist
}
func openDB() {
if *initialize {
// Delete the old database file
log.Printf(`Removing old db file`)
err := os.Remove(*dbFile)
if err != nil && !os.IsNotExist(err) {
panic(err)
}
}
log.Printf(`Opening sqlite (%s) db file: "%s"`, sqlite.Version(), *dbFile)
var err error
db, err = sqlite.Open(*dbFile)
if err != nil {
panic(err)
}
if *initialize {
// Initialize database structure
log.Printf(`Initializing database`)
for _, stmt := range initStmts {
err := db.Exec(stmt)
if err != nil {
panic(err)
}
}
}
}
func (mdb *MailDB) GetMail(id int) *Mail {
conn, err := sqlite.Open("maildb.db")
if err != nil {
log.Print("Unable to open the database: ", err)
return nil
}
selectstmt, err := conn.Prepare("SELECT id,sender,subject,text,time FROM mails WHERE id=" + strconv.Itoa(id) + ";")
if err != nil {
log.Print("Error preparing the selectstatement")
return nil
}
err = selectstmt.Exec()
if err != nil {
log.Print("Error execution of select stmt")
return nil
}
if selectstmt.Next() {
var mail Mail
err = selectstmt.Scan(&mail.Id, &mail.Sender, &mail.Subject, &mail.Text, &mail.Time)
if err != nil {
log.Print("Error while scanning row")
return nil
}
return &mail
}
return nil
}
func (mdb *MailDB) SaveMail(mail *Mail) {
conn, err := sqlite.Open("maildb.db")
if err != nil {
log.Print("Unable to open the database: ", err)
return
}
defer conn.Close()
insertsql := "INSERT INTO mails (sender,subject,text,time) VALUES (?,?,?,?) ;"
stmt, err := conn.Prepare(insertsql)
if err != nil {
log.Print("maildb insert fail @prepare: ", err)
return
}
err = stmt.Exec(mail.Sender, mail.Subject, mail.Text, time.Now().Unix())
if err != nil {
log.Print("maildb insert fail @exec: ", err)
return
}
stmt.Next()
err = stmt.Finalize()
if err != nil {
log.Print("finalize fail")
return
}
log.Print("New mail from ", mail.Sender)
}
func openDb() {
conn, err := sqlite.Open("db.sqlite")
if err != nil {
log.Fatalf("Unable to open the database: %s", err)
}
conn.Exec("CREATE TABLE devices(id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, email TEXT, deviceName TEXT, pushURL TEXT, lon FLOAT, lat FLOAT, UNIQUE (pushURL));")
gConn = conn
}
func getSqliteTimeSeries(site, field string, start, stop time.Time) *TimeSeries {
filename := *sqliteDB
dbconn, err := sqlite.Open(filename)
if err != nil {
fmt.Println(err)
return nil
}
defer dbconn.Close()
dbconn.BusyTimeout(2 * 1000)
switch field {
case "site", "date", "free", "max", "total", "used", "threads", "maxRequestTime": // all good
default:
fmt.Printf("Bad field: %s\n", field)
return nil
}
stmt, stmterr := dbconn.Prepare("select date, xml from jvmmetrics where site = ?1 and date between ?2 and ?3 order by date")
if stmterr != nil {
fmt.Println(stmterr)
return nil
}
defer stmt.Finalize()
err = stmt.Exec(site, start.Format(SqliteTimeFormat), stop.Format(SqliteTimeFormat))
if err != nil {
fmt.Println(err)
return nil
}
//fmt.Printf("sql: %s\n", stmt.SQL())
series := TimeSeries{}
series.Entries = []TimeSeriesEntry{}
var datestr string
var value string
for stmt.Next() {
err = stmt.Scan(&datestr, &value)
date, dateError := time.Parse(SqliteTimeFormat, datestr)
if dateError != nil {
fmt.Printf("bad date: %v", dateError)
}
entry := TimeSeriesEntry{field, date, getFieldValueFromXml(strings.NewReader(value), field)}
//fmt.Printf("%#v\n", entry)
series.Entries = append(series.Entries, entry)
}
if len(series.Entries) == 0 {
fmt.Printf("No Values for: %s\n", stmt.SQL())
}
return &series
}
func main() {
defer func() {
if err := recover(); err != nil {
fmt.Println(err)
con.Privmsg(channel, "An error ocurred and should have been logged")
}
}()
for {
func() {
flag.StringVar(&host, "host", "irc.freenode.net:6667", "The IRC host:port to connect to. Defaults to irc.freenode.net:6667")
flag.StringVar(&nick, "nick", "minibot", "The IRC nick to use. Defaults to minibot")
flag.StringVar(&user, "user", "minibot", "The IRC user to use. Defaults to minibot")
flag.StringVar(&channel, "channel", "#minibot", "The IRC channel to join. Defaults to #minibot")
flag.StringVar(&database, "database", "minibot.db", "The sqlite database file. Defaults to minibot.db")
flag.BoolVar(&verbose, "verbose", false, "Be verbose")
flag.Parse()
var err error
debug("connecting to irc")
con = irc.IRC(nick, user)
debug("opening sqlite database")
db, err = sqlite.Open(database)
if err != nil {
panic("An error occurred while opening the database: " + err.Error())
}
defer db.Close()
debug("if needed, initializing sqlite database")
err = db.Exec("create table if not exists messages (sender text, destination text, moment text, message text, primary key (sender, destination, moment))")
if err != nil {
panic("Could not initialize database: " + err.Error())
}
debug("connecting to irc server")
err = con.Connect(host)
if err != nil {
panic("An error occurred while connecting to irc: " + err.Error())
}
con.AddCallback("001", func(event *irc.Event) { con.Join(channel) })
con.AddCallback("311", func(event *irc.Event) { whoisReplies <- event.Message })
con.AddCallback("PRIVMSG", respond)
con.AddCallback("NOTICE", func(event *irc.Event) { debug(event.Message) })
con.AddCallback("NICK", messages)
debug("starting main loop")
con.Loop()
}()
}
}
func sqliteWriteHandler(queue chan *InsertRequest) chan bool {
ok := make(chan bool)
filename := *sqliteDB
dbconn, err := sqlite.Open(filename)
if err != nil {
fmt.Println(err)
return nil
}
dbconn.BusyTimeout(10 * 1000) /* 10 s timeout */
stmt, stmterr := dbconn.Prepare("INSERT INTO jvmmetrics (site, date, free, max, total, threads, xml) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)")
if stmterr != nil {
fmt.Print(err)
return nil
}
go func() {
fmt.Printf("Waiting for parses\n")
for {
request := <-queue
//fmt.Printf("got one request: %#v\n", request)
if request == nil || request.site == "" {
stmt.Finalize()
dbconn.Close()
close(queue)
ok <- true
return
}
xml, xmlErr := jbossinfo.InfoXML(request.jbossinfo)
if xmlErr != nil {
fmt.Printf("error generating xml: %s\n", xmlErr)
continue
}
err = stmt.Exec(request.site, request.date.Format(SqliteTimeFormat), request.jbossinfo.JvmStatus.Free, request.jbossinfo.JvmStatus.Max, request.jbossinfo.JvmStatus.Total, request.jbossinfo.Connectors[0].ThreadInfo.CurrentThreadCount, xml)
if err != nil {
fmt.Print(err)
}
//fmt.Printf("SQL: %s\n", stmt.SQL())
stmt.Next()
}
}()
return ok
}
func main() {
conn, err := sqlite.Open("chkbook.db")
if err != nil {
fmt.Println("Error opening file")
return
}
g, err := ChkbkEntries(conn, 1)
if err != nil {
fmt.Printf("Error reading ledger %v", err)
}
expenseTotaler := &Totaler{}
incomeTotaler := &Totaler{Income: true}
functional.MultiConsume(g, new(Entry), nil, Printer{}, expenseTotaler, incomeTotaler)
g.Close()
fmt.Printf("Total income: %d\n", incomeTotaler.Total)
fmt.Printf("Total expenses: %d\n", expenseTotaler.Total)
}
func openDatabase(dbfile string) (*sqlite.Conn, error) {
db, err := sqlite.Open(dbfile)
if err != nil {
return nil, err
}
sites, err := loadSites(filepath.Join(rootPath, sitesJsonFile))
if err != nil {
return nil, err
}
if db.Exec(`CREATE TABLE IF NOT EXISTS visit (
url VARCHAR(255) PRIMARY KEY,
rank INTEGER,
cookies TEXT,
resources TEXT,
screenshot TEXT,
stdout TEXT,
stderr TEXT,
success INTEGER);`); err != nil {
return nil, err
}
for i, site := range sites {
s, err := db.Prepare("SELECT url FROM visit WHERE url=?")
if err != nil {
return nil, err
}
if err := s.Exec(site); err != nil {
return nil, err
}
if !s.Next() {
if err := db.Exec("INSERT INTO visit (url, rank, success) VALUES(?1, ?2, 0)", site, i+1); err != nil {
return nil, err
}
}
}
return db, nil
}
func main() {
conn, err := sqlite.Open("chkbook.db")
if err != nil {
fmt.Println("Error opening file")
return
}
s, err := ChkbkEntries(conn, 1)
if err != nil {
fmt.Printf("Error reading ledger %v", err)
}
expenseTotaler := &Totaler{}
incomeTotaler := &Totaler{Income: true}
printer := Printer{}
err = functional.MultiConsume(s, new(Entry), nil, printer, expenseTotaler, incomeTotaler)
if err != nil {
fmt.Printf("Encountered error closing stream: %v", err)
}
fmt.Printf("Total income: %d\n", incomeTotaler.Total)
fmt.Printf("Total expenses: %d\n", expenseTotaler.Total)
}
func (mdb *MailDB) DeleteMail(id int) bool {
conn, err := sqlite.Open("maildb.db")
if err != nil {
log.Print("Unable to open the database: ", err)
return false
}
defer conn.Close()
s := "DELETE FROM mails WHERE id=?;"
stmt, err := conn.Prepare(s)
if err != nil {
log.Print("Error preparing the selectstatement: ", s)
return false
}
err = stmt.Exec(id)
if err != nil {
log.Print("Error execution of select stmt")
return false
}
stmt.Next()
stmt.Finalize()
return true
}
func main() {
dbfile := new(string)
flag.StringVar(dbfile, "db", "tac.sqlite", "Filename of sqlite3 database for persistent data.")
port := new(int)
flag.IntVar(port, "port", 8080, "Port for http server to listen on.")
flag.Parse()
log("Using database file", *dbfile)
var err error
db, err = sqlite.Open(*dbfile)
if err != nil {
log(err)
os.Exit(1)
}
err = setup(db)
if err != nil {
log(err)
os.Exit(1)
}
http.HandleFunc("/", requestHandler)
log("Starting server on port", *port)
log(http.ListenAndServe(":"+strconv.FormatInt(int64(*port), 10), nil))
}
func main() {
fmt.Println(sqlite.Version())
conn, err := sqlite.Open("tmp.sqlite")
defer func() {
err := conn.Close()
if err != nil {
fmt.Println(err)
}
}()
if err != nil {
fmt.Println(err)
return
}
err = conn.Exec("CREATE TABLE T1(id int, name string);")
if err != nil {
fmt.Println(err)
return
}
err = conn.Exec("INSERT INTO T1 VALUES(3, \"john\");")
if err != nil {
fmt.Println(err)
return
}
err = conn.Exec("INSERT INTO T1 VALUES(4, \"mark\");")
if err != nil {
fmt.Println(err)
return
}
stmt1, err := conn.Prepare("SELECT NAME FROM T1;")
if err != nil {
fmt.Println(err)
return
}
defer func() {
err := stmt1.Finalize()
if err != nil {
fmt.Println(err)
}
}()
err = stmt1.Exec()
if err != nil {
fmt.Println(err)
return
}
for stmt1.Next() {
// you can parse anything (name, ID) as string
// but parsing name as int will give an error
var s string
err = stmt1.Scan(&s)
if err != nil {
fmt.Println(err)
return
}
fmt.Println(s)
}
stmt2, err := conn.Prepare("SELECT NAME FROM T1 WHERE ID = ?;")
if err != nil {
fmt.Println(err)
return
}
defer func() {
err := stmt2.Finalize()
if err != nil {
fmt.Println(err)
}
}()
// http://sqlite.org/lang_expr.html#varparam
// ?, ?NNN, :VVV, @VVV, $VVV
// It looks like gosqlite binds by index, so only use '?'.
err = stmt2.Exec(3) // you can also use string "3"
if err != nil {
fmt.Println(err)
return
}
for stmt2.Next() {
// you can parse anything (name, ID) as string
// but parsing name as int will give an error
var s string
err = stmt2.Scan(&s)
if err != nil {
fmt.Println(err)
return
}
fmt.Println(s)
}
}
func InitDB(path string) (*Database, error) {
db := Database{
sync.RWMutex{},
make(map[object.ObjectID]*object.Object),
make(map[string]*object.Object),
make(chan *object.SerializableObject, WRITE_QUEUE_LENGTH),
make(chan bool),
make(chan bool),
0,
}
sqliteDb, err := sqlite.Open(path)
if err != nil {
return nil, err
}
selectStmnt, err := sqliteDb.Prepare("SELECT data FROM objects")
if err != nil {
return nil, err
}
err = selectStmnt.Exec()
if err != nil {
return nil, err
}
for selectStmnt.Next() {
jsonObj := make([]byte, 0)
selectStmnt.Scan(&jsonObj)
so := &object.SerializableObject{}
json.Unmarshal(jsonObj, so)
db.AddSerializableObject(nil, so)
if so.ID >= db.nextId {
db.nextId++
}
}
for _, obj := range db.objects {
obj.SetSaver(db.saver)
}
// We're done loading the database, start the save goroutine
go func() {
defer func() {
sqliteDb.Close()
db.done <- true
}()
insertStmnt, err := sqliteDb.Prepare("INSERT OR REPLACE INTO objects (id, data) VALUES (?, ?)")
if err != nil {
panic("Failed to create insert statement. This shouldn't happen!")
}
for {
select {
case <-db.die:
return
case so := <-db.saver:
sob, err := json.Marshal(so)
if err != nil {
log.Println(sob)
continue
}
err = insertStmnt.Exec(so.ID, sob)
if err != nil {
log.Println(sob)
continue
}
insertStmnt.Next()
}
}
}()
return &db, nil
}
// Get connection to db for user. Remeber to close it.
func getDb(user string) (*sqlite.Conn, error) {
return sqlite.Open(path.Join(dbDir, user+".sqlite"))
}