Golang sqlx.Tx类代码示例

// Update the charac in the database
func (u *Charac) Update(tx *sqlx.Tx) (err error) {
	_, err = tx.NamedExec("UPDATE \"charac\" SET "+Charac_UpdateStr+" WHERE id=:id", u)
	if err != nil {
		err = errors.New("model.charac::Update " + err.Error())
	}
	return
}

// Update database informations
func (d *Database) Update(tx *sqlx.Tx) (err error) {
	_, err = tx.NamedExec("UPDATE \"database\" SET "+Database_UpdateStr+" WHERE id=:id", d)
	if err != nil {
		err = errors.New("database::Update: " + err.Error())
	}
	return
}

// UpdateHandle links continents to a database
func (d *Database) UpdateHandle(tx *sqlx.Tx, handle *Database_handle) (err error) {
	_, err = tx.NamedExec("UPDATE database_handle SET "+Database_handle_UpdateStr+" WHERE id = :id", handle)
	if err != nil {
		err = errors.New("database::UpdateHandle: " + err.Error())
	}
	return
}

func (db Database) CommitTransaction(tx *sqlx.Tx) error {
	err := tx.Commit()
	if err != nil {
		fmt.Println("Could not commit database transaction:", err)
	}
	return err
}

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
}

// GetContextsList lists all user designed as context of a database
func (d *Database) GetContextsList(tx *sqlx.Tx) (contexts []Database_context, err error) {
	err = tx.Select(&contexts, "SELECT id, context FROM database_context WHERE database_id = $1", d.Id)
	if err != nil {
		err = errors.New("database::GetContextsList: " + err.Error())
	}
	return
}

// GetOwnerInfos get all informations about the owner of the database
func (d *Database) GetOwnerInfos(tx *sqlx.Tx) (owner DatabaseAuthor, err error) {
	err = tx.Get(owner, "SELECT * FROM \"user\" u LEFT JOIN \"database\" d ON u.id = d.owner WHERE d.id = $1", d.Id)
	if err != nil {
		err = errors.New("database::GetOwnerInfos: " + err.Error())
	}
	return
}

func getDBMediaPlayerTx(tx *sqlx.Tx, id int64, baseComp Component, exFlags ExpansionFlags) (dbMediaPlayer, error) {
	var dbMP dbMediaPlayer

	// build a select statement based on the expand keys
	stmt := "SELECT * FROM component c JOIN media_player_state mpstate ON c.id=mpstate.id"

	if exFlags&(ExpandAll|ExpandSpecs) != 0 {
		stmt += " JOIN media_player_spec mpspec ON mpspec.make=c.make AND mpspec.model=c.model"
	}

	// TODO: Add stats
	//	if exFlags&(ExpandAll|exFlags&ExpandStats) != 0 {
	//		stmt += " JOIN media_player_stats lstats ON c.id=lstats.id"
	//	}
	stmt += " WHERE c.id=? LIMIT 1"
	Log.Debug("getting media player", "query", stmt, "id", id)
	if err := tx.Get(&dbMP, stmt, id); err != nil {
		return dbMediaPlayer{}, fmt.Errorf("error getting media player with id %v: %v", id, err)
	}

	if dbMP.ID == 0 {
		Log.Warn("media player has id 0", "search_id", id)
		return dbMediaPlayer{}, fmt.Errorf("got unexpected component id: 0")
	}
	return dbMP, 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
}

// 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 deleteLightEmitterTx(tx *sqlx.Tx, compID int64) error {
	if _, err := tx.Queryx("DELETE FROM light_emitter_state WHERE id=?", compID); err != nil {
		return fmt.Errorf("error deleteing from light_emitter_state: %v", err)
	}
	//TODO: also delete light_emitter_stats
	return nil
}

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 getAllComponents(tx *sqlx.Tx, exFlags ExpansionFlags) (map[types.ComponentID]types.Component, error) {
	type result struct {
		ID       int64
		DeviceID int64 `db:"device_id"`
	}
	// get results for each component in the database
	results := []result{}
	err := tx.Select(&results, "SELECT id, device_id FROM component")
	if err != nil {
		return nil, fmt.Errorf("error getting component IDs: %v", err)
	}

	// Build a set of components from database
	componentsByKey := make(map[types.ComponentID]types.Component)
	for _, result := range results {
		name, comp, err := getComponentTx(tx, result.ID, exFlags)
		if err != nil {
			return nil, fmt.Errorf("error getting component with ID %v: %v", result.ID, err)
		}
		key := types.ComponentID{
			Name:     name,
			DeviceID: types.DeviceID(result.DeviceID),
		}
		componentsByKey[key] = comp // put into the destination map
	}
	return componentsByKey, nil // all components gathered without error
}

// Delete the charac from the database
func (u *Charac) Delete(tx *sqlx.Tx) (err error) {
	_, err = tx.NamedExec("DELETE FROM \"charac\" WHERE id=:id", u)
	if err != nil {
		err = errors.New("model.charac::Delete " + err.Error())
	}
	return
}

// GetAuthorsList lists all user designed as author of a database
func (d *Database) GetAuthorsList(tx *sqlx.Tx) (authors []DatabaseAuthor, err error) {
	err = tx.Select(&authors, "SELECT u.id, u.firstname, u.lastname, u.firstname || ' ' || u.lastname AS fullname FROM \"user\" u LEFT JOIN database__authors da ON u.id = da.user_id WHERE da.database_id = $1", d.Id)
	if err != nil {
		err = errors.New("database::GetAuthorsList: " + err.Error())
	}
	return
}