Example #1
0
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
}
Example #2
0
// 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
}
Example #3
0
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
}
Example #4
0
// 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
}
Example #5
0
// 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
}
Example #6
0
File: db.go Project: upwrd/sift
// 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
}
Example #7
0
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...)
}
Example #8
0
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
}
Example #9
0
// 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
}
Example #10
0
File: db.go Project: upwrd/sift
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
}
Example #11
0
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
}
Example #12
0
File: db.go Project: upwrd/sift
// 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
}
Example #13
0
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
}
Example #14
0
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
}
Example #15
0
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
}
Example #16
0
// 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
}
Example #17
0
// 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
}
Example #18
0
// 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
}
Example #19
0
// 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
}
Example #20
0
// 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
}
Example #21
0
// 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
}
Example #22
0
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
}
Example #23
0
// 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
}
Example #24
0
// 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
}
Example #25
0
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
}
Example #26
0
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
}
Example #27
0
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
}
Example #28
0
// 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
}
Example #29
0
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
}
Example #30
0
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
}