Golang Tx.Exec方法代码示例

func (s *ListStore) DeleteList(id int64) (err error) {
	var tx *sqlx.Tx

	tx, err = s.db.Beginx()
	if err != nil {
		return
	}

	// Automatically rollback/commit if there's an error.
	defer func() {
		if err != nil {
			tx.Rollback()
		} else {
			tx.Commit()
		}
	}()

	// Remove todos in this list.
	if _, err = tx.Exec(s.db.Rebind(listDeleteTodosQuery), id); err != nil {
		return
	}

	// Remove the given List
	if _, err = tx.Exec(s.db.Rebind(listDeleteQuery), id); err != nil {
		return
	}

	// Done!
	return nil
}

// SetUsers of the group
func (g *Group) SetUsers(tx *sqlx.Tx, users []User) error {
	ids := make([]string, len(users)+1)
	for i, user := range users {
		ids[i] = fmt.Sprintf("%d", user.Id)
	}
	ids[len(users)] = "-1" // empty list is a problem

	_, err := tx.NamedExec("DELETE FROM \"user__group\" WHERE group_id=:id AND user_id NOT IN ("+strings.Join(ids, ",")+")", g)
	if err != nil {
		return err
	}

	rows, err := tx.Queryx("SELECT user_id FROM user__group WHERE group_id = $1 AND user_id IN ("+strings.Join(ids, ",")+")", g.Id)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var id string
		rows.Scan(&id)
		ids = removeString(ids, id)
	}
	ids = removeString(ids, "-1")

	for _, userid := range ids {
		_, err := tx.Exec("INSERT INTO user__group (group_id, user_id) VALUES ($1, $2)", g.Id, userid)
		if err != nil {
			return err
		}
	}
	return nil
}

func (b *Base) DeleteById(tx *sqlx.Tx, id string) (sql.Result, error) {
	var result sql.Result

	if b.table == "" {
		return nil, errors.New("Table must not be empty.")
	}

	tx, wrapInSingleTransaction, err := b.newTransactionIfNeeded(tx)
	if tx == nil {
		return nil, errors.New("Transaction struct must not be empty.")
	}
	if err != nil {
		return nil, err
	}

	query := fmt.Sprintf("DELETE FROM %v WHERE id=$1", b.table)

	result, err = tx.Exec(query, id)

	if wrapInSingleTransaction == true {
		err = tx.Commit()
	}

	if err != nil {
		return nil, err
	}

	return result, err
}

// SetCompanies set user companies
func (u *User) SetCompanies(tx *sqlx.Tx, companies []Company) error {
	ids := make([]string, len(companies)+1)
	for i, company := range companies {
		ids[i] = fmt.Sprintf("%d", company.Id)
	}
	ids[len(companies)] = "-1" // empty list is a problem

	_, err := tx.NamedExec("DELETE FROM \"user__company\" WHERE user_id=:id AND company_id NOT IN ("+strings.Join(ids, ",")+")", u)
	if err != nil {
		return err
	}

	rows, err := tx.Queryx("SELECT company_id FROM user__company WHERE user_id = $1 AND company_id IN ("+strings.Join(ids, ",")+")", u.Id)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var id string
		rows.Scan(&id)
		ids = removeString(ids, id)
	}
	ids = removeString(ids, "-1")

	for _, companyid := range ids {
		_, err := tx.Exec("INSERT INTO user__company (user_id, company_id) VALUES ($1, $2)", u.Id, companyid)
		if err != nil {
			return err
		}
	}
	return nil
}

// SetGroups set user groups
func (u *User) SetGroups(tx *sqlx.Tx, groups []Group) error {
	ids := make([]string, len(groups))
	for i, group := range groups {
		ids[i] = fmt.Sprintf("%d", group.Id)
	}

	_, err := tx.NamedExec("DELETE FROM \"user__group\" WHERE user_id=:id AND group_id NOT IN ("+strings.Join(ids, ",")+")", u)
	if err != nil {
		return err
	}

	rows, err := tx.Queryx("SELECT group_id FROM user__group WHERE user_id = $1 AND group_id IN ("+strings.Join(ids, ",")+")", u.Id)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var id string
		rows.Scan(&id)
		ids = removeString(ids, id)
	}

	for _, groupid := range ids {
		_, err := tx.Exec("INSERT INTO user__group (user_id, group_id) VALUES ($1, $2)", u.Id, groupid)
		if err != nil {
			return err
		}
	}
	return nil
}

// upsertMediaPlayerTx upserts a media player to the database.
func upsertMediaPlayerTx(tx *sqlx.Tx, compID int64, mp types.MediaPlayer) error {
	// Try updating
	q := "UPDATE media_player_state SET play_state=?, media_type=?, source=? WHERE id=?"
	res, err := tx.Exec(q, mp.State.PlayState, mp.State.MediaType, mp.State.Source, compID)
	if err != nil {
		return fmt.Errorf("error updating component: %v", err)
	}

	// Check the number of rows affected by the udpate; should be 1 if the
	// media_player_state row existed, and 0 if not
	if n, err := res.RowsAffected(); err != nil {
		return fmt.Errorf("error getting row count (required for update): %v", err)
	} else if n == 0 {
		// The update failed, do an insert instead
		q = "INSERT INTO media_player_state (id, play_state, media_type, source) VALUES (?, ?, ?, ?)"
		res, err := tx.Exec(q, compID, mp.State.PlayState, mp.State.MediaType, mp.State.Source)
		if err != nil {
			return fmt.Errorf("error inserting component: %v", err)
		}
		id, err := res.LastInsertId() // Get ID from insert
		if err != nil || id == 0 {
			return fmt.Errorf("error or zero-value ID (id: %v, err: %v)", id, err)
		}
		Log.Debug("inserted new media player", "id", compID, "new_values", mp, "query", q)
		return nil
	}
	Log.Debug("updated existing media player", "id", compID, "new_values", mp, "query", q)
	return nil
}

func (b *Base) exec(tx *sqlx.Tx, query string, args ...interface{}) (driver.Result, error) {
	if tx != nil {
		return tx.Exec(query, args...)
	}

	return b.db.Exec(query, args...)
}

func (b *BaseStore) DeleteFromTable(tx *sqlx.Tx, where string) (sql.Result, error) {
	var result sql.Result

	if b.Table == "" {
		return nil, errors.New("Table must not be empty.")
	}

	tx, wrapInSingleTransaction, err := b.newTransactionIfNeeded(tx)
	if tx == nil {
		return nil, errors.New("Transaction struct must not be empty.")
	}
	if err != nil {
		return nil, err
	}

	query := fmt.Sprintf("DELETE FROM %v", b.Table)

	if where != "" {
		query = query + " WHERE " + where
	}

	result, err = tx.Exec(query)

	if wrapInSingleTransaction == true {
		err = tx.Commit()
	}

	if err != nil {
		return nil, err
	}

	return result, err
}

// DeleteHandles unlinks handles
func (d *Database) DeleteSpecificHandle(tx *sqlx.Tx, id int) error {
	_, err := tx.Exec("DELETE FROM \"database_handle\" WHERE identifier = $1", id)
	if err != nil {
		err = errors.New("database::DeleteHandle: " + err.Error())
	}
	return err
}

func upsertBaseComponentTx(tx *sqlx.Tx, deviceID types.DeviceID, name string, c types.Component) (int64, error) {
	base := c.GetBaseComponent()
	existingBaseComp, found := getBaseComponentTx(tx, deviceID, name)
	if !found {
		// not found: do insert
		q := "INSERT INTO component (device_id, name, make, model, type) VALUES (?, ?, ?, ?, ?)"
		res, err := tx.Exec(q, deviceID, name, base.Make, base.Model, c.Type())
		if err != nil {
			return 0, fmt.Errorf("error inserting component: %v", err)
		}
		id, err := res.LastInsertId() // Get ID from insert
		if err != nil || id == 0 {
			return 0, fmt.Errorf("error or zero-value ID (id: %v, err: %v)", id, err)
		}
		Log.Debug("inserted component", "id", id, "base_component", base, "stmt", q)
		return id, nil
	}

	// found: do update
	q := "UPDATE component SET make=?, model=?, type=? WHERE id=?;"
	_, err := tx.Exec(q, base.Make, base.Model, c.Type(), existingBaseComp.ID)
	if err != nil {
		return 0, fmt.Errorf("error updating base component: %v", err)
	}
	Log.Debug("updated component", "base", base, "query", q, "update_err", err)
	return existingBaseComp.ID, err
}

func (s *PeopleStore) DeletePerson(id int64) (err error) {
	var tx *sqlx.Tx

	tx, err = s.db.Beginx()
	if err != nil {
		return
	}

	// Automatically rollback/commit if there's an error.
	defer func() {
		if err != nil {
			tx.Rollback()
		} else {
			tx.Commit()
		}
	}()

	// Remove the given Person
	if _, err = tx.Exec(s.db.Rebind(personDeleteQuery), id); err != nil {
		return
	}

	// Done!
	return nil
}

// upsertLightEmitterTx upserts a light emitter to the database.
func upsertLightEmitterTx(tx *sqlx.Tx, compID int64, le types.LightEmitter) error {
	// Try updating
	q := "UPDATE light_emitter_state SET brightness_in_percent=? WHERE id=?"
	res, err := tx.Exec(q, le.State.BrightnessInPercent, compID)
	if err != nil {
		return fmt.Errorf("error updating component: %v", err)
	}

	// Check the number of rows affected by the udpate; should be 1 if the
	// light_emitter_state row existed, and 0 if not
	if n, err := res.RowsAffected(); err != nil {
		return fmt.Errorf("error getting row count (required for update): %v", err)
	} else if n == 0 {
		// The update failed, do an insert instead
		q = "INSERT INTO light_emitter_state (id, brightness_in_percent) VALUES (?, ?)"
		res, err := tx.Exec(q, compID, le.State.BrightnessInPercent)
		if err != nil {
			return fmt.Errorf("error inserting component: %v", err)
		}
		id, err := res.LastInsertId() // Get ID from insert
		if err != nil || id == 0 {
			return fmt.Errorf("error or zero-value ID (id: %v, err: %v)", id, err)
		}
		Log.Debug("inserted new light emitter", "id", compID, "new_values", le, "query", q)
		return nil
	}
	Log.Debug("updated existing light emitter", "id", compID, "new_values", le, "query", q)
	return nil
}

func (d *Database) SetTranslations(tx *sqlx.Tx, field string, translations []struct {
	Lang_Isocode string
	Text         string
}) (err error) {

	// Check if translation entry exists for this database and this lang

	var transID int

	for _, tr := range translations {
		err = tx.QueryRow("SELECT count(database_id) FROM database_tr WHERE database_id = $1 AND lang_isocode = $2", d.Id, tr.Lang_Isocode).Scan(&transID)
		if transID == 0 {
			_, err = tx.Exec("INSERT INTO database_tr (database_id, lang_isocode, description, geographical_limit, bibliography, context_description, source_description, source_relation, copyright, subject) VALUES ($1, $2, '', '', '', '', '', '', '', '')", d.Id, tr.Lang_Isocode)
			if err != nil {
				err = errors.New("database::SetTranslations: " + err.Error())
			}
		}
		if tr.Text != "" {
			_, err = tx.Exec("UPDATE database_tr SET "+field+" = $1 WHERE database_id = $2 and lang_isocode = $3", tr.Text, d.Id, tr.Lang_Isocode)
		}
	}

	if err != nil {
		err = errors.New("database::SetTranslations: " + err.Error())
	}
	return
}

func (b *Base) InsertIntoTable(tx *sqlx.Tx, data map[string]interface{}) (sql.Result, error) {
	if b.table == "" {
		return nil, errors.New("Table must not be empty.")
	}

	tx, wrapInSingleTransaction, err := b.newTransactionIfNeeded(tx)
	if tx == nil {
		return nil, errors.New("Transaction struct must not be empty.")
	}
	if err != nil {
		return nil, err
	}

	keys := make([]string, 0)
	dollarMarks := make([]string, 0)
	values := make([]interface{}, 0)

	loopCounter := 1
	for key, value := range data {
		keys = append(keys, key)
		dollarMarks = append(dollarMarks, fmt.Sprintf("$%v", loopCounter))
		values = append(values, value)

		loopCounter++
	}

	query := fmt.Sprintf(
		"INSERT INTO %v (%v) VALUES (%v)",
		b.table,
		strings.Join(keys, ","),
		strings.Join(dollarMarks, ","))

	result := &InsertResult{}
	result.rowsAffected = 1

	if b.hasID {
		query = query + " RETURNING id"

		var lastInsertId int64
		err = tx.QueryRow(query, values...).Scan(&lastInsertId)
		if err != nil {
			return nil, err
		}

		result.lastInsertId = lastInsertId

	} else {
		_, err := tx.Exec(query, values...)
		if err != nil {
			return nil, err
		}
	}

	if wrapInSingleTransaction == true {
		err = tx.Commit()
	}

	return result, err
}

func insertWithdrawal(tx *sqlx.Tx, userID int64, address string, amount float64) error {
	rawSQL := "INSERT INTO withdrawals (`user_id`, `address`, `amount`) VALUES (?, ?, ?)"
	if _, err := tx.Exec(rawSQL, userID, address, amount); err != nil {
		return fmt.Errorf("create withdrawal error: %v", err)
	}

	return nil
}