// 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
}
func deleteMediaPlayerTx(tx *sqlx.Tx, compID int64) error {
if _, err := tx.Queryx("DELETE FROM media_player_state WHERE id=?", compID); err != nil {
return fmt.Errorf("error deleteing from media_player_state: %v", err)
}
//TODO: also delete media_player_stats
return nil
}
// DeleteAuthors deletes the author linked to a database
func (d *Database) DeleteAuthors(tx *sqlx.Tx) (err error) {
_, err = tx.NamedExec("DELETE FROM \"database__authors\" WHERE database_id=:id", d)
if err != nil {
err = errors.New("database::DeleteAuthors: " + err.Error())
}
return
}
func (h Helper) CreateWithId(tx *sqlx.Tx, name string, args ...interface{}) (int64, error) {
var id int64
sql := h.SQL(name)
if sql == "" {
panic("No statement registered under " + name)
}
stmt, err := tx.Preparex(sql)
if err != nil {
return 0, err
}
defer stmt.Close()
res, err := stmt.Exec(args...)
if err != nil {
return 0, err
}
id, err = res.LastInsertId()
if err != nil {
return 0, err
}
return id, nil
}
// DeleteContexts deletes the context linked to a database
func (d *Database) DeleteContexts(tx *sqlx.Tx) error {
_, err := tx.NamedExec("DELETE FROM \"database_context\" WHERE database_id=:id", d)
if err != nil {
err = errors.New("database::DeleteContexts: " + err.Error())
}
return 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 CreateIdentity(tx *sqlx.Tx, identity *Identity) error {
const (
Q = `INSERT INTO identities DEFAULT VALUES RETURNING *;`
)
return tx.Get(identity, Q)
}
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
}
// Update the charac_root in the database
func (u *Charac_root) Update(tx *sqlx.Tx) (err error) {
_, err = tx.NamedExec("UPDATE \"charac_root\" SET \"admin_group_id\" = :admin_group_id, cached_langs = :cached_langs WHERE root_charac_id=:root_charac_id", u)
if err != nil {
err = errors.New("model.charac_root::Update" + err.Error())
}
return
}
func (b *Base) sel(tx *sqlx.Tx, dest interface{}, query string, args ...interface{}) error {
if tx != nil {
return tx.Select(dest, query, args...)
}
return b.db.Select(dest, query, args...)
}
func (b *Base) query(tx *sqlx.Tx, query string, args ...interface{}) (*sqlx.Rows, error) {
if tx != nil {
return tx.Queryx(query, args...)
}
return b.db.Queryx(query, args...)
}
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 getChronoRecursive(tx *sqlx.Tx, chrono *ChronologyTreeStruct) error {
var err error = nil
// load translations
tr := []model.Chronology_tr{}
err = tx.Select(&tr, "SELECT * FROM chronology_tr WHERE chronology_id = "+strconv.Itoa(chrono.Id))
if err != nil {
return err
}
chrono.Name = model.MapSqlTranslations(tr, "Lang_isocode", "Name")
chrono.Description = model.MapSqlTranslations(tr, "Lang_isocode", "Description")
// get the childs of this chronology from the db
childs, err := chrono.Chronology.Childs(tx)
if err != nil {
return err
}
// recurse
chrono.Content = make([]ChronologyTreeStruct, len(childs))
for i, child := range childs {
chrono.Content[i].Chronology = child
err = getChronoRecursive(tx, &chrono.Content[i])
if err != nil {
return err
}
}
return nil
}
// CacheDates get database sites extend and cache enveloppe
func (d *Database) CacheDates(tx *sqlx.Tx) (err error) {
_, err = tx.NamedExec("UPDATE database SET start_date = (SELECT COALESCE(min(start_date1),-2147483648) FROM site_range WHERE site_id IN (SELECT id FROM site where database_id = :id) AND start_date1 != -2147483648), end_date = (SELECT COALESCE(max(end_date2),2147483647) FROM site_range WHERE site_id IN (SELECT id FROM site where database_id = :id) AND end_date2 != 2147483647) WHERE id = :id", d)
if err != nil {
err = errors.New("database::CheckDates: " + err.Error())
}
return
}
// GetLastImport lists last import informations
func (d *Database) GetLastImport(tx *sqlx.Tx) (imp Import, err error) {
imp = Import{}
err = tx.Get(&imp, "SELECT * FROM import WHERE i.jdatabase_id = $1 ORDER by id DESC LIMIT 1", d.Id)
if err != nil {
err = errors.New("database::GetLastImport: " + err.Error())
}
return
}
// GetImportList lists all informations about an import (date, filename, etc)
func (d *Database) GetImportList(tx *sqlx.Tx) (imports []ImportFullInfos, err error) {
imports = []ImportFullInfos{}
err = tx.Select(&imports, "SELECT i.*, u.firstname || ' ' || u.lastname AS fullname FROM import i LEFT JOIN \"user\" u ON i.user_id = u.id WHERE i.database_id = $1 ORDER BY id DESC", d.Id)
if err != nil {
err = errors.New("database::GetImportList: " + err.Error())
}
return
}