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
}
// AddContinents links continents to a database
func (d *Database) AddContinents(tx *sqlx.Tx, continentIds []int) (err error) {
for _, id := range continentIds {
_, err := tx.Exec("INSERT INTO database__continent (database_id, continent_geonameid) VALUES ($1, $2)", d.Id, id)
if err != nil {
return errors.New("database::AddContinents: " + err.Error())
}
}
return
}
// SetAuthors links users as authors to a shapefile
func (u *Shapefile) SetAuthors(tx *sqlx.Tx, authors []int) (err error) {
for _, uid := range authors {
_, err = tx.Exec("INSERT INTO \"shapefile__authors\" (shapefile_id, user_id) VALUES ($1, $2)", u.Id, uid)
if err != nil {
return
}
}
return
}
// SetAuthors links users as authors to a wms layer
func (u *Map_layer) SetAuthors(tx *sqlx.Tx, authors []int) (err error) {
for _, uid := range authors {
_, err = tx.Exec("INSERT INTO \"map_layer__authors\" (map_layer_id, user_id) VALUES ($1, $2)", u.Id, uid)
if err != nil {
return
}
}
return
}
// SetAuthors links users as authors to a database
func (d *Database) SetAuthors(tx *sqlx.Tx, authors []int) (err error) {
for _, uid := range authors {
_, err = tx.Exec("INSERT INTO \"database__authors\" (database_id, user_id) VALUES ($1, $2)", d.Id, uid)
if err != nil {
return errors.New("database::SetAuthors: " + err.Error())
}
}
return
}
// SetContexts links users as contexts to a database
func (d *Database) SetContexts(tx *sqlx.Tx, contexts []string) error {
for _, cname := range contexts {
_, err := tx.Exec("INSERT INTO \"database_context\" (database_id, context) VALUES ($1, $2)", d.Id, cname)
if err != nil {
return errors.New("database::SetContexts: " + err.Error())
}
}
return nil
}
// increment user balance, total_income, referer_total_income
func incrementUserBalance(tx *sqlx.Tx, userID int64, delta, refererDelta float64) error {
rawSQL := "UPDATE users SET `balance` = `balance` + ?, `total_income` = `total_income` + ?, `referer_total_income` = `referer_total_income` + ? WHERE id = ?"
args := []interface{}{delta, delta, refererDelta, userID}
if result, err := tx.Exec(rawSQL, args...); err != nil {
return fmt.Errorf("increment user balance error: %v", err)
} else if rowAffected, _ := result.RowsAffected(); rowAffected != 1 {
return fmt.Errorf("increment user balance affected %v rows", rowAffected)
}
return nil
}
func deductUserBalanceBy(tx *sqlx.Tx, userID int64, delta float64) error {
result, err := tx.Exec("UPDATE users SET `balance` = `balance` - ? WHERE `id` = ? AND `balance` >= ?", delta, userID, delta)
if err != nil {
return fmt.Errorf("deduct user balance error: %v", err)
}
// make sure the user has sufficient balance
if rowAffected, _ := result.RowsAffected(); rowAffected != 1 {
return errors.ErrInsufficientBalance
}
return nil
}
// buildsUpdateState changes the state of a build.
func buildsUpdateState(tx *sqlx.Tx, buildID string, state BuildState) error {
var sql string
switch state {
case StateBuilding:
sql = `UPDATE builds SET state = ?, started_at = ? WHERE id = ?`
case StateSucceeded, StateFailed:
sql = `UPDATE builds SET state = ?, completed_at = ? WHERE id = ?`
default:
panic(fmt.Sprintf("not implemented for %s", state))
}
_, err := tx.Exec(tx.Rebind(sql), state, time.Now(), buildID)
return err
}
// CacheDates get database sites extend and cache enveloppe
func (s *SiteInfos) CacheDates(tx *sqlx.Tx) (err error) {
// dates := struct {
// Start_Date1 int
// Start_Date2 int
// End_Date1 int
// End_Date2 int
// }{}
// _, err = tx.Exec("UPDATE site SET start_date1 = x.start_date1, SET start_date2 = x.start_date2, SET end_date1 = x.end_date1, SET end_date2 = x.end_date2 FROM (SELECT min(start_date1) as start_date1, min(start_date2) as start_date2, max(end_date1) as end_date1, max(end_date2) as end_date2 FROM site WHERE id = $1) x WHERE id = $1", s.Id)
_, err = tx.Exec("UPDATE site SET (start_date1, start_date2, end_date1, end_date2) = (SELECT min(start_date1), min(start_date2), max(end_date1), max(end_date2) FROM site_range WHERE site_id = $1) WHERE id = $1", s.Id)
return
}
func (b *Base) UpdateByKeyValueString(tx *sqlx.Tx, data map[string]interface{}, key, value 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
}
keysWithDollarMarks := make([]string, 0)
values := make([]interface{}, 0)
loopCounter := 1
for key, value := range data {
keysWithDollarMark := fmt.Sprintf("%v=$%v", key, loopCounter)
keysWithDollarMarks = append(keysWithDollarMarks, keysWithDollarMark)
values = append(values, value)
loopCounter++
}
// Add value as part of values
values = append(values, value)
query := fmt.Sprintf(
"UPDATE %v SET %v WHERE %v=$%v",
b.table,
strings.Join(keysWithDollarMarks, ","),
key,
loopCounter)
result, err = tx.Exec(query, values...)
if err != nil {
return nil, err
}
if wrapInSingleTransaction == true {
err = tx.Commit()
}
return result, err
}
func UpdateAccount(tx *sqlx.Tx, account *Account) error {
const (
Q = `UPDATE accounts SET name = $2, email = $3, picture = $4, raw_profile = $5, raw_token = $6 WHERE id = $1;`
)
_, err := tx.Exec(Q,
account.Id,
account.Name,
account.Email,
account.Picture,
account.RawProfile,
account.RawToken,
)
return err
}
func (b *BaseStore) UpdateByID(tx *sqlx.Tx, data map[string]interface{}, id int64) (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
}
keysWithQuestionMarks := make([]string, 0)
values := make([]interface{}, 0)
for key, value := range data {
keysWithQuestionMark := fmt.Sprintf("%v=?", key)
keysWithQuestionMarks = append(keysWithQuestionMarks, keysWithQuestionMark)
values = append(values, value)
}
// Add id as part of values
values = append(values, id)
query := fmt.Sprintf(
"UPDATE %v SET %v WHERE id=?",
b.Table,
strings.Join(keysWithQuestionMarks, ","))
result, err = tx.Exec(query, values...)
if err != nil {
return nil, err
}
if wrapInSingleTransaction == true {
err = tx.Commit()
}
return result, err
}
// saveKV saves a kv pair within the provided transaction
func (db *Driver) saveKV(tx *sqlx.Tx, key string, in interface{}) error {
vj, err := json.Marshal(in)
if err != nil {
return err
}
exists, err := db.hasKV(key)
if err != nil {
return err
}
sql := Queries.KV.Insert
if exists {
sql = Queries.KV.Update
}
_, err = tx.Exec(sql, key, types.JsonText(vj))
return err
}
func characsDeleteRecurse(charac CharacTreeStruct, tx *sqlx.Tx) error {
var err error
for _, charac := range charac.Content {
err = characsDeleteRecurse(charac, tx)
if err != nil {
return err
}
}
_, err = tx.Exec("DELETE FROM charac_tr WHERE charac_id = " + strconv.Itoa(charac.Id))
if err != nil {
return err
}
_, err = tx.Exec("DELETE FROM charac WHERE id = " + strconv.Itoa(charac.Id))
if err != nil {
return err
}
return nil
}
func chronologiesDeleteRecurse(chrono ChronologyTreeStruct, tx *sqlx.Tx) error {
var err error
for _, chrono := range chrono.Content {
err = chronologiesDeleteRecurse(chrono, tx)
if err != nil {
return err
}
}
_, err = tx.Exec("DELETE FROM chronology_tr WHERE chronology_id = " + strconv.Itoa(chrono.Id))
if err != nil {
return err
}
_, err = tx.Exec("DELETE FROM chronology WHERE id = " + strconv.Itoa(chrono.Id))
if err != nil {
return err
}
return nil
}
