Example #1
0
// 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
}
Example #2
0
// 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
}
Example #3
0
File: main.go Project: xrstf/raziel
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.")
		}
	}
}
Example #4
0
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)
	}
}
Example #5
0
// 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
}
Example #6
0
// 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
}
Example #7
0
// 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
}
Example #8
0
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
}
Example #9
0
func DestroyToken(db *sqlx.DB, token string) error {
	_, err := db.Exec("delete from token where token = ?", token)
	if err != nil {
		return err
	}

	return nil
}
Example #10
0
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
}
Example #11
0
File: user.go Project: itsabot/abot
// 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
}
Example #12
0
func ResetSlave(db *sqlx.DB, all bool) error {
	stmt := "RESET SLAVE"
	if all == true {
		stmt += " ALL"
	}
	_, err := db.Exec(stmt)
	return err
}
Example #13
0
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}
}
Example #14
0
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
	}
}
Example #15
0
// 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
	}
}
Example #16
0
// 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...)
}
Example #17
0
// 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
}
Example #18
0
File: db.go Project: upwrd/sift
// 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
}
Example #20
0
// 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
}
Example #21
0
File: main.go Project: caarlos0/it
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)
	}
}
Example #22
0
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
}
Example #23
0
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

}
Example #25
0
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
}
Example #26
0
// 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)
	}
}
Example #27
0
File: db.go Project: upwrd/sift
// 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
}
Example #28
0
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
}
Example #29
0
// 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
}
Example #30
0
// 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
}