// savePeopleContext records contextual information about people being // discussed, enabling Abot to replace things like "him", "her", or "they" with // the names the pronouns represent. func savePeopleContext(db *sqlx.DB, in *dt.Msg) error { if len(in.StructuredInput.People) == 0 { return nil } byt, err := json.Marshal(in.StructuredInput.People) if err != nil { return err } if in.User.ID > 0 { q := `INSERT INTO states (key, value, userid, pluginname) VALUES ($1, $2, $3, '') ON CONFLICT (userid, pluginname, key) DO UPDATE SET value=$2` _, err = db.Exec(q, keyContextPeople, byt, in.User.ID) } else { q := `INSERT INTO states (key, value, flexid, flexidtype, pluginname) VALUES ($1, $2, $3, $4, '') ON CONFLICT (flexid, flexidtype, pluginname, key) DO UPDATE SET value=$2` _, err = db.Exec(q, keyContextPeople, byt, in.User.FlexID, in.User.FlexIDType) } if err != nil { return err } return nil }
// DropAndCreateTable удаляет таблицу, если она уже существует и создает заново func DropAndCreateTable(schema string, tableName string, db *sqlx.DB) (bool, error) { var err error var rows *sqlx.Rows // Проверяем нет ли такой таблицы в базе rows, err = db.Queryx("SELECT to_regclass('" + tableName + "');") if err != nil { //fmt.Println("Error on check table '"+tableName+"':", err) return false, err } defer rows.Close() // И если есть удаляем rowsCount := 0 for rows.Next() { rowsCount++ } if rowsCount > 0 { _, err = db.Exec("DROP TABLE IF EXISTS " + tableName + ";") if err != nil { //fmt.Println("Error on drop table '"+tableName+"':", err) return false, err } } // Создаем таблицу _, err = db.Exec(schema) if err != nil { //fmt.Println("Error on create table '"+tableName+"':", err) return false, err } return true, nil }
func validateMasterPassword(db *sqlx.DB) { c := dbConfig{} db.Get(&c, "SELECT `key`, `value` FROM `config` WHERE `key` = 'teststring'") if c.Key == "" { panic("Could not read the teststring from the config table. Your database is broken.") } // not yet initialized, so store the ciphertext if len(c.Value) == 0 { ciphertext, err := Encrypt([]byte(TestString)) if err != nil { panic(err) } _, err = db.Exec("UPDATE `config` SET `value` = ? WHERE `key` = ?", ciphertext, c.Key) if err != nil { panic("Could not write initial password marker: " + err.Error()) } } else { plaintext, err := Decrypt(c.Value) if err != nil { panic("The configured password is not usable for the configured database.") } // this should never happen: a wrong password should always yield an error in Decrypt() if TestString != string(plaintext) { panic("The configured password is not usable for the configured database.") } } }
func KillThreads(db *sqlx.DB) { var ids []int db.Select(&ids, "SELECT Id FROM information_schema.PROCESSLIST WHERE Command != 'binlog dump' AND User != 'system user' AND Id != CONNECTION_ID()") for _, id := range ids { db.Exec("KILL ?", id) } }
// CreateNode creates the given Node. func CreateNode(db *sqlx.DB, n models.Node) error { if n.RXDelay > 15 { return errors.New("max value of RXDelay is 15") } _, err := db.Exec(` insert into node ( dev_eui, app_eui, app_key, rx_delay, rx1_dr_offset, channel_list_id ) values ($1, $2, $3, $4, $5, $6)`, n.DevEUI[:], n.AppEUI[:], n.AppKey[:], n.RXDelay, n.RX1DROffset, n.ChannelListID, ) if err != nil { return fmt.Errorf("create node %s error: %s", n.DevEUI, err) } log.WithField("dev_eui", n.DevEUI).Info("node created") return nil }
// UpdateNode updates the given Node. func UpdateNode(db *sqlx.DB, n models.Node) error { if n.RXDelay > 15 { return errors.New("max value of RXDelay is 15") } res, err := db.Exec(` update node set app_eui = $2, app_key = $3, used_dev_nonces = $4, rx_delay = $5, rx1_dr_offset = $6, channel_list_id = $7 where dev_eui = $1`, n.DevEUI[:], n.AppEUI[:], n.AppKey[:], n.UsedDevNonces, n.RXDelay, n.RX1DROffset, n.ChannelListID, ) if err != nil { return fmt.Errorf("update node %s error: %s", n.DevEUI, err) } ra, err := res.RowsAffected() if err != nil { return err } if ra == 0 { return fmt.Errorf("node %s does not exist", n.DevEUI) } log.WithField("dev_eui", n.DevEUI).Info("node updated") return nil }
// Update a message as needing training. func (m *Msg) Update(db *sqlx.DB) error { q := `UPDATE messages SET needstraining=$1 WHERE id=$2` if _, err := db.Exec(q, m.NeedsTraining, m.ID); err != nil { return err } return nil }
func (f *factoid) delete(db *sqlx.DB) error { var err error if f.id.Valid { _, err = db.Exec(`delete from factoid where id=?`, f.id) } f.id.Valid = false return err }
func DestroyToken(db *sqlx.DB, token string) error { _, err := db.Exec("delete from token where token = ?", token) if err != nil { return err } return nil }
func IncrementToken(db *sqlx.DB, token string) error { _, err := db.Exec("update token set attempts = attempts + 1 where token = ?", token) if err != nil { return err } return nil }
// DeleteSessions removes any open sessions by the user. This enables "logging // out" of the web-based client. func (u *User) DeleteSessions(db *sqlx.DB) error { q := `DELETE FROM sessions WHERE userid=$1` _, err := db.Exec(q, u.ID) if err != nil && err != sql.ErrNoRows { return err } return nil }
func ResetSlave(db *sqlx.DB, all bool) error { stmt := "RESET SLAVE" if all == true { stmt += " ALL" } _, err := db.Exec(stmt) return err }
func Clean(db *sqlx.DB, config *eqemuconfig.Config) (err error) { type ItemList struct { Name string `db:"name"` NpcId int64 `db:"npcid"` LootTableId int64 `db:"loottableid"` LootDropId int64 `db:"lootdropid"` ItemId int64 `db:"itemid"` Price float64 `db:"price"` Chance float64 `db:"chance"` } zone := "wakening" fmt.Println("Adjusting pricing for", zone) rows, err := db.Queryx(`SELECT se.npcID npcid, nt.name name, i.id itemid, lte.loottable_id loottableid, lde.lootdrop_id lootdropid, lde.chance chance, i.name, i.price price FROM spawn2 s2 INNER JOIN spawngroup sg ON sg.id = s2.spawngroupID INNER JOIN spawnentry se ON se.spawngroupID = sg.id INNER JOIN npc_types nt ON nt.id = se.npcID INNER JOIN loottable_entries lte ON lte.loottable_id = nt.loottable_id INNER JOIN lootdrop_entries lde ON lde.lootdrop_id = nt.loottable_id INNER JOIN items i on i.id = lde.item_id INNER JOIN merchantlist ml ON ml.item = i.id WHERE s2.zone = ? AND lde.chance > 0 ORDER BY price desc LIMIT 1 `, zone) if err != nil { fmt.Println("Error initializing", err.Error()) return } //&{Diamond 119111 7741 7741 10037 200000 3.75} //iterate results for rows.Next() { itemList := &ItemList{} err = rows.StructScan(itemList) if err != nil { return } fmt.Println(itemList) newPrice := itemList.Price * (itemList.Chance / 100) fmt.Println("Setting price from", itemList.Price, "to", newPrice) //Set pricing _, err = db.Exec("UPDATE loottable SET mincash = mincash + ?, maxcash = maxcash + ? WHERE id = ?", itemList.Price, itemList.Price, itemList.LootTableId) if err != nil { return } //Remove chance of drop _, err = db.Exec("UPDATE lootdrop_entries SET chance = 0, disabled_chance = ? WHERE lootdrop_id = ? and item_id = ?", itemList.Chance, itemList.LootDropId, itemList.ItemId) if err != nil { return } return } return //&{Diamond 119017 377 10037 200000} }
func SetReadOnly(db *sqlx.DB, flag bool) error { if flag == true { _, err := db.Exec("SET GLOBAL read_only=1") return err } else { _, err := db.Exec("SET GLOBAL read_only=0") return err } }
// insert will insert the given value into foo, returning the row's id func insertFoo(db *sqlx.DB, value string) (int, error) { if result, err := db.Exec(insertFooQuery, value); err != nil { // HL return 0, err } else if id, err := result.LastInsertId(); err != nil { // HL return 0, err } else { return int(id), nil } }
// Execute a write query (INSERT/UPDATE/DELETE) on a given SQL database func (q *Query) ExecWrite(db *sqlx.DB) (sql.Result, error) { sql, vars := q.GetSQL() if debugEnabled { marshaled, _ := json.Marshal(vars) Debug("%s, %s", sql, string(marshaled)) } return db.Exec(sql, vars...) }
// createApplication creates the given Application func createApplication(db *sqlx.DB, a models.Application) error { _, err := db.Exec("insert into application (app_eui, name) values ($1, $2)", a.AppEUI[:], a.Name, ) if err != nil { return fmt.Errorf("create application %s error: %s", a.AppEUI, err) } log.WithField("app_eui", a.AppEUI).Info("application created") return nil }
// TODO: implement import from .sql files which is portable. // May require embedding files in the binary; see https://github.com/jteeuwen/go-bindata func dbInitByGoFile(db *sqlx.DB) error { sqls := []string{rawsql.InitSql, rawsql.PopulateSpecsSQL} for i, sql := range sqls { // Exec it _, err := db.Exec(string(sql)) if err != nil { return fmt.Errorf("error while execing sql %v: %v", i, err) } } return nil }
func InsertBin(dbx *sqlx.DB) uuid.UUID { // Creating UUID Version 4 u1 := uuid.NewV4() _, err := dbx.Exec("INSERT INTO test_table_bin (id) VALUES (?)", u1.Bytes()) if err != nil { fmt.Printf("%v", err) } return u1 }
// Insert or update the first entry func (fe *FirstEntry) save(db *sqlx.DB) error { if _, err := db.Exec(`insert into first (day, time, body, nick) values (?, ?, ?, ?)`, fe.day.Unix(), fe.time.Unix(), fe.body, fe.nick, ); err != nil { return err } return nil }
func createBook(db *sqlx.DB) func(c *echo.Context) error { return func(c *echo.Context) error { _, err := db.Exec( "INSERT INTO books(id, name) VALUES(nextval('books_seq'), $1)", c.Param("name"), ) if err != nil { return c.NoContent(http.StatusBadRequest) } return c.NoContent(http.StatusOK) } }
func (entry idleEntry) saveIdleEntry(db *sqlx.DB) error { var err error if entry.id.Valid { log.Println("Updating downtime for: ", entry) _, err = db.Exec(`update downtime set nick=?, lastSeen=? where id=?;`, entry.nick, entry.lastSeen.Unix(), entry.id.Int64) } else { log.Println("Inserting downtime for: ", entry) _, err = db.Exec(`insert into downtime (nick, lastSeen) values (?, ?)`, entry.nick, entry.lastSeen.Unix()) } return err }
func UpdateStatusActivity(db *sqlx.DB, id int, estado string) error { if id <= 0 { return ErrNoIDSent } _, err := db.Exec(sqlUpdateActivityByID, estado, id) if err != nil { return err } return nil }
func InsertIntAuto(dbx *sqlx.DB) uint64 { _, _ = dbx.Exec("INSERT INTO test_table_int() values()") row := dbx.QueryRow("SELECT MAX(id) FROM test_table_int") var testuint uint64 // var testuintX int64 _ = row.Scan(&testuint) // testuintX, _ = res.LastInsertId() return testuint }
func (f *factoid) save(db *sqlx.DB) error { var err error if f.id.Valid { // update _, err = db.Exec(`update factoid set fact=?, tidbit=?, verb=?, owner=?, accessed=?, count=? where id=?`, f.Fact, f.Tidbit, f.Verb, f.Owner, f.accessed.Unix(), f.Count, f.id.Int64) } else { f.created = time.Now() f.accessed = time.Now() // insert res, err := db.Exec(`insert into factoid ( fact, tidbit, verb, owner, created, accessed, count ) values (?, ?, ?, ?, ?, ?, ?);`, f.Fact, f.Tidbit, f.Verb, f.Owner, f.created.Unix(), f.accessed.Unix(), f.Count, ) if err != nil { return err } id, err := res.LastInsertId() // hackhackhack? f.id.Int64 = id f.id.Valid = true } return err }
// Truncate truncates teh DB func Truncate(db *sqlx.DB) { var sql string switch db.DriverName() { case "postgres": sql = pgTruncateTables case "sqlite3": sql = sqliteTruncateTables default: panic("Unknown driver") } if _, err := db.Exec(sql); err != nil { panic(err) } }
// dbInit initializes the database func dbInit(db *sqlx.DB) error { sqlPaths := []string{"sql2/init.sql", "sql2/populate_specs.sql"} for _, path := range sqlPaths { // Load the init SQL file initSQL, err := ioutil.ReadFile(path) if err != nil { return fmt.Errorf("could not read %v: %v", path, err) } // Exec it _, err = db.Exec(string(initSQL)) if err != nil { return fmt.Errorf("error while execing %v: %v", path, err) } } return nil }
func DeleteQuery(db *sqlx.DB, query string, itemid int64, focus string) (affect int64, err error) { var result sql.Result result, err = db.Exec(query, itemid) if err != nil { fmt.Println("Error removing ", itemid, "from", focus, ":", err.Error()) return } affect, err = result.RowsAffected() if err != nil { fmt.Println("Error getting rows affected for", focus, itemid, err.Error()) return } return }
// DeleteChannelList deletes the ChannelList matching the given id. func DeleteChannelList(db *sqlx.DB, id int64) error { res, err := db.Exec("delete from channel_list where id = $1", id, ) if err != nil { return fmt.Errorf("delete channel-list %d error: %s", id, err) } ra, err := res.RowsAffected() if err != nil { return err } if ra == 0 { return fmt.Errorf("channel-list %d does not exist", id) } log.WithField("id", id).Info("channel-list deleted") return nil }
// DeleteNode deletes the Node matching the given DevEUI. func DeleteNode(db *sqlx.DB, devEUI lorawan.EUI64) error { res, err := db.Exec("delete from node where dev_eui = $1", devEUI[:], ) if err != nil { return fmt.Errorf("delete node %s error: %s", devEUI, err) } ra, err := res.RowsAffected() if err != nil { return err } if ra == 0 { return fmt.Errorf("node %s does not exist", devEUI) } log.WithField("dev_eui", devEUI).Info("node deleted") return nil }