Golang DB.Exec方法代码示例

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