// 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
}