// 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 (b *Base) get(tx *sqlx.Tx, dest interface{}, query string, args ...interface{}) error {
if tx != nil {
return tx.Get(dest, query, args...)
}
return b.db.Get(dest, query, args...)
}
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
}
func CreateIdentity(tx *sqlx.Tx, identity *Identity) error {
const (
Q = `INSERT INTO identities DEFAULT VALUES RETURNING *;`
)
return tx.Get(identity, Q)
}
// 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
}
// GetProjectId get the project of the user
func (u *User) GetProjectId(tx *sqlx.Tx) (projectID int, err error) {
err = tx.Get(&projectID, "SELECT id FROM project WHERE user_id = $1", u.Id)
if err == sql.ErrNoRows {
err = nil
projectID = 0
}
return
}
func getBaseComponentTx(tx *sqlx.Tx, deviceID types.DeviceID, compName string) (Component, bool) {
var dbComp Component
err := tx.Get(&dbComp, "SELECT * FROM component WHERE device_id=? AND name=? LIMIT 1", deviceID, compName)
if err != nil || dbComp.ID == 0 {
return dbComp, false
}
return dbComp, true
}
// GetBuildIDFromDBTx retrieves the ID for the given build from the database
// as specified by the given master, builder, and build number.
func GetBuildIDFromDBTx(builder, master string, buildNumber int, tx *sqlx.Tx) (int, error) {
var id int
stmt := fmt.Sprintf("SELECT id FROM %s WHERE builder = ? AND master = ? AND number = ?", TABLE_BUILDS)
if err := tx.Get(&id, stmt, builder, master, buildNumber); err != nil {
return -1, fmt.Errorf("Unable to retrieve build ID from database: %v", err)
}
return id, nil
}
func getDBDeviceTx(tx *sqlx.Tx, extID types.ExternalDeviceID) (Device, bool) {
var dev Device
err := tx.Get(&dev, "SELECT * FROM device WHERE manufacturer=? AND external_id=? LIMIT 1", extID.Manufacturer, extID.ID)
if err != nil || dev.ID == 0 {
Log.Debug("could not get dbDevice", "err", err, "ext_id", extID, "dbDev_id", dev.ID)
return dev, false
}
return dev, true
}
// IsLinkedToProject returns true or false if database is linked or not to user project
func (d *Database) IsLinkedToProject(tx *sqlx.Tx, project_ID int) (linked bool, err error) {
linked = false
c := 0
err = tx.Get(&c, "SELECT count(*) FROM project__database WHERE project_id = $1 AND database_id = $2", project_ID, d.Id)
if c > 0 {
linked = true
}
return
}
// artifactsFindByRepoSha finds an artifact by image.
func artifactsFindByRepoSha(tx *sqlx.Tx, repoSha string) (*Artifact, error) {
parts := strings.Split(repoSha, "@")
var sql = `SELECT * FROM artifacts
WHERE repository = ?
AND sha = ?
ORDER BY seq desc
LIMIT 1`
var a Artifact
err := tx.Get(&a, tx.Rebind(sql), parts[0], parts[1])
return &a, err
}
func findConsumer(id int, db *sqlx.Tx) *Consumer {
consumer := &Consumer{}
consumer._db = db
db.Get(consumer, "SELECT `id`, `name`, `created_at`, `updated_at`, `created_by`, `updated_by`, `enabled`, `deleted`, `info_token` FROM `consumer` WHERE `id` = ?", id)
if consumer.Id == 0 {
return nil
}
return consumer
}
// buildsFindByRepoSha finds a build by repository and sha.
func buildsFindByRepoSha(tx *sqlx.Tx, repoSha string) (*Build, error) {
parts := strings.Split(repoSha, "@")
var sql = `SELECT * FROM builds
WHERE repository = ?
AND sha = ?
ORDER BY seq desc
LIMIT 1`
var b Build
err := tx.Get(&b, tx.Rebind(sql), parts[0], parts[1])
return &b, err
}
// GetHandles get last handle linked to a database
func (d *Database) GetLastHandle(tx *sqlx.Tx) (handle *Database_handle, err error) {
handle = &Database_handle{}
err = tx.Get(handle, "SELECT * FROM database_handle WHERE database_id = $1 ORDER BY id DESC LIMIT 1", d.Id)
switch {
case err == sql.ErrNoRows:
return handle, nil
case err != nil:
return
}
return
}
func getBaseComponentByIDTx(tx *sqlx.Tx, id int64) (Component, error) {
var dbComp Component
if err := tx.Get(&dbComp, "SELECT * FROM component WHERE id=? LIMIT 1", id); err != nil {
Log.Debug("could not get base component", "id", id, "err", err)
return Component{}, fmt.Errorf("error getting component with id %v: %v", id, err)
}
if dbComp.ID == 0 {
Log.Warn("base component has id 0", "search_id", id)
return Component{}, fmt.Errorf("got unexpected component id: 0")
}
Log.Debug("got base component", "id", id, "comp", dbComp)
return dbComp, nil
}
func dashboardAction(user *User, req *http.Request, session *Session, db *sqlx.Tx) response {
secrets := countResultSet{}
db.Get(&secrets, "SELECT COUNT(*) AS `num` FROM `secret`")
users := countResultSet{}
db.Get(&users, "SELECT COUNT(*) AS `num` FROM `user` WHERE `deleted` IS NULL")
consumers := countResultSet{}
db.Get(&consumers, "SELECT COUNT(*) AS `num` FROM `consumer` WHERE `deleted` = 0")
now := time.Now()
limit := now.AddDate(0, 0, -7).Format("2006-01-02")
recentHits := countResultSet{}
db.Get(&recentHits, "SELECT COUNT(*) AS `num` FROM `access_log` WHERE requested_at >= '"+limit+"'")
auditLog := NewAuditLog(db, req)
accessLog := NewAccessLog(db)
data := &dashboardData{
layoutData: NewLayoutData("Dashboard", "dashboard", user, session.CsrfToken),
Secrets: secrets.Count,
Consumers: consumers.Count,
Users: users.Count,
RecentHits: recentHits.Count,
AuditLog: auditLog.FindAll(10, 0),
AccessLog: accessLog.FindAll(10, 0),
}
return renderTemplate(200, "dashboard/index", data)
}
func (pfi *ProjectFullInfos) Get(tx *sqlx.Tx) (err error) {
// Infos
err = tx.Get(pfi, "SELECT *,ST_AsGeoJSON(geom) as geom from project WHERE id = $1", pfi.Id)
if err != nil {
log.Println(err)
return
}
// Chronologies
err = tx.Select(&pfi.Chronologies, "SELECT root_chronology_id from project__chronology WHERE project_id = $1", pfi.Id)
if err != nil {
log.Println(err)
return
}
// Characs
err = tx.Select(&pfi.Characs, "SELECT project__charac.root_charac_id from project__charac LEFT JOIN charac ON charac.id = project__charac.root_charac_id WHERE project_id = $1 ORDER BY charac.order", pfi.Id)
if err != nil {
log.Println(err)
return
}
// Databases
err = tx.Select(&pfi.Databases, "SELECT database_id from project__database WHERE project_id = $1", pfi.Id)
if err != nil {
log.Println(err)
return
}
// Layers WMS
// transquery := GetQueryTranslationsAsJSONObject("map_layer_tr", "tbl.map_layer_id = ml.id", "", false, "name", "attribution", "copyright")
// err = tx.Select(&pfi.Layers, "SELECT ml.id, ST_AsGeojson(ml.geographical_extent_geom) as geographical_extent_geom, url, identifier, ("+transquery+") as translations, ml.min_scale, ml.max_scale, ml.type, 'wms' || ml.id AS uniq_code FROM project__map_layer pml LEFT JOIN map_layer ml ON pml.map_layer_id = ml.id WHERE pml.project_id = $1", pfi.Id)
err = tx.Select(&pfi.Layers, "SELECT ml.id, ml.type, ml.type || '' || ml.id AS uniq_code FROM project__map_layer pml LEFT JOIN map_layer ml ON pml.map_layer_id = ml.id WHERE pml.project_id = $1", pfi.Id)
if err != nil {
log.Println(err)
return
}
// Layers Shapefile
// transquery = GetQueryTranslationsAsJSONObject("shapefile_tr", "tbl.shapefile_id = s.id", "", false, "name", "attribution", "copyright")
// err = tx.Select(&pfi.Layers, "SELECT s.id, ST_AsGeojson(s.geographical_extent_geom) as geographical_extent_geom, ("+transquery+") as translations, 'shp' as type, 'shp' || s.id AS uniq_code from project__shapefile ps LEFT JOIN shapefile s ON ps.shapefile_id = s.id WHERE ps.project_id = $1", pfi.Id)
err = tx.Select(&pfi.Layers, "SELECT s.id, 'shp' as type, 'shp' || s.id AS uniq_code from project__shapefile ps LEFT JOIN shapefile s ON ps.shapefile_id = s.id WHERE ps.project_id = $1", pfi.Id)
if err != nil {
log.Println(err)
return
}
return
}
// GetFullInfos returns all informations about a database
func (d *Database) GetFullInfos(tx *sqlx.Tx, langIsocode string) (db DatabaseFullInfos, err error) {
db = DatabaseFullInfos{}
if d.Id == 0 {
db.Imports = make([]ImportFullInfos, 0)
db.Countries = make([]CountryInfos, 0)
db.Continents = make([]ContinentInfos, 0)
db.Handles = make([]Database_handle, 0)
db.Authors = make([]DatabaseAuthor, 0)
db.Contexts = make([]Database_context, 0)
db.Handles = make([]Database_handle, 0)
db.License = "-"
return
}
// err = tx.Get(&db, "SELECT name, scale_resolution, geographical_extent, type, declared_creation_date, owner, editor, contributor, default_language, state, license_id, published, soft_deleted, d.created_at, d.updated_at, firstname || ' ' || lastname as owner_name FROM \"database\" d LEFT JOIN \"user\" u ON d.owner = u.id WHERE d.id = $1", d.Id)
err = tx.Get(&db, "SELECT d.*, ST_AsGeoJSON(d.geographical_extent_geom) as geographical_extent_geom, firstname || ' ' || lastname as owner_name, l.name AS license FROM \"database\" d LEFT JOIN \"user\" u ON d.owner = u.id LEFT JOIN \"license\" l ON d.license_id = l.id WHERE d.id = $1", d.Id)
if err != nil {
return
}
db.Authors, err = d.GetAuthorsList(tx)
if err != nil {
return
}
db.Countries, err = d.GetCountryList(tx, langIsocode)
if err != nil {
return
}
db.Continents, err = d.GetContinentList(tx, langIsocode)
if err != nil {
return
}
db.Handles, err = d.GetHandles(tx)
if err != nil {
return
}
db.Imports, err = d.GetImportList(tx)
if err != nil {
return
}
db.Contexts, err = d.GetContextsList(tx)
if err != nil {
return
}
err = db.GetTranslations(tx)
return
}
func CreateAccount(tx *sqlx.Tx, account *Account) error {
const (
Q = `INSERT INTO accounts (identity_id, remote_id, name, email, picture, raw_profile, raw_token) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *;`
)
return tx.Get(account, Q,
account.IdentityId,
account.RemoteId,
account.Name,
account.Email,
account.Picture,
account.RawProfile,
account.RawToken,
)
}
func GetAccountWithRemoteId(tx *sqlx.Tx, remote_id string) (*Account, error) {
const (
Q = `SELECT * FROM accounts WHERE remote_id = $1 LIMIT 1;`
)
account := &Account{}
err := tx.Get(account, Q, remote_id)
if err == sql.ErrNoRows {
return nil, nil
}
if err != nil {
return nil, err
}
return account, nil
}
// HaveGroups return true if the user have all the wanted groups
func (u *User) HaveGroups(tx *sqlx.Tx, groups ...Group) (ok bool, err error) {
var idsGroups = make([]int, len(groups))
for i, group := range groups {
idsGroups[i] = group.Id
}
count := 0
err = tx.Get(&count, "SELECT count(*) FROM user__group ug WHERE ug.user_id = "+strconv.Itoa(u.Id)+" AND ug.group_id in ("+IntJoin(idsGroups, true)+")")
if err != nil {
return false, err
}
if count == len(groups) {
return true, err
}
return false, err
}
// HavePermissions return true if the user have all the wanted permissions
func (u *User) HaveAtLeastOnePermission(tx *sqlx.Tx, permissions ...string) (ok bool, err error) {
if len(permissions) == 0 {
return true, nil
}
query, args, err := sqlx.In("SELECT count(distinct(p.id)) FROM permission p,user__group ug, group__permission gp WHERE ug.user_id = ? AND ug.group_id = gp.group_id AND gp.permission_id = p.id AND p.name in (?)", u.Id, permissions)
if err != nil {
return false, err
}
query = db.DB.Rebind(query)
var count int
err = tx.Get(&count, query, args...)
if count > 0 {
return true, err
}
return false, err
}
func findUser(id int, loadPassword bool, db *sqlx.Tx) *User {
user := &User{}
user._db = db
passwordCol := ""
if loadPassword {
passwordCol = ", `password`"
}
db.Get(user, "SELECT `id`, `login`, `name`, `last_login_at`, `deleted`"+passwordCol+" FROM `user` WHERE `id` = ?", id)
if user.Id == 0 {
return nil
}
return user
}
func findRestriction(consumerId int, rtype string, loadContext bool, db *sqlx.Tx) *Restriction {
restriction := &Restriction{}
restriction._db = db
contextCol := ""
if loadContext {
contextCol = ", `context`"
}
db.Get(restriction, "SELECT `consumer_id`, `type`"+contextCol+", `enabled` FROM `restriction` WHERE `consumer_id` = ? AND `type` = ?", consumerId, rtype)
if restriction.ConsumerId == 0 {
return nil
}
return restriction
}
// buildsFind finds a build by ID.
func buildsFind(tx *sqlx.Tx, buildID string) (*Build, error) {
const (
findBuildSql = `SELECT * FROM builds where id = ?`
findArtifactsSql = `SELECT image FROM artifacts WHERE build_id = ?`
)
var b Build
err := tx.Get(&b, tx.Rebind(findBuildSql), buildID)
if err != nil {
return nil, err
}
err = tx.Select(&b.Artifacts, tx.Rebind(findArtifactsSql), buildID)
if err != nil {
return nil, err
}
return &b, err
}
func (s *TodoStore) DeleteTodo(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()
}
}()
// Get the given Todo
todo := &model.Todo{}
if err = tx.Get(todo, s.db.Rebind(todoGetQuery), id); err != nil {
return
}
// Remove the given Todo
if _, err = tx.Exec(s.db.Rebind(todoDeleteQuery), id); err != nil {
return
}
// Fix any previous link - change any `previous_id` that points at our
// current id to point at what we point at.
if _, err = tx.Exec(s.db.Rebind(todoRelinkQuery),
todo.PreviousID, // New previous_id
id, // Old previous_id
todo.ListID, // List ID
); err != nil {
return
}
// Done!
return nil
}
func getPlayerStat(tx *sqlx.Tx, id int) (*models.PlayerStat, error) {
var playerStat playerStat
log.Debug("Getting player stat with id=%d", id)
err := tx.Get(&playerStat, `SELECT * FROM player_stats WHERE id=$1`, id)
if err != nil {
return nil, fmt.Errorf("Failed to get player stat with id=%d: %v", id, err)
}
playerStat.Player, err = GetPlayer(playerStat.PlayerId)
if err != nil {
return nil, err
}
playerStat.Character, err = GetCharacter(playerStat.CharacterId)
if err != nil {
return nil, err
}
log.Debug("Successfully got player_stat=%+v", playerStat)
return &playerStat.PlayerStat, nil
}
func getCharacRecursive(tx *sqlx.Tx, charac *CharacTreeStruct, project_id int) error {
var err error = nil
// load translations
tr := []model.Charac_tr{}
err = tx.Select(&tr, "SELECT * FROM charac_tr WHERE charac_id = "+strconv.Itoa(charac.Id))
if err != nil {
return err
}
charac.Name = model.MapSqlTranslations(tr, "Lang_isocode", "Name")
charac.Description = model.MapSqlTranslations(tr, "Lang_isocode", "Description")
// check if enabled in project
if project_id > 0 {
hiddenCount := 0
tx.Get(&hiddenCount, "SELECT count(*) FROM project_hidden_characs WHERE project_id = "+strconv.Itoa(project_id)+" AND charac_id = "+strconv.Itoa(charac.Id))
if hiddenCount > 0 {
charac.Hidden = true
log.Println("found hidden : ", charac.Id)
}
}
// get the childs of this charac from the db
childs, err := charac.Charac.Childs(tx)
if err != nil {
return err
}
// recurse
charac.Content = make([]CharacTreeStruct, len(childs))
for i, child := range childs {
charac.Content[i].Charac = child
err = getCharacRecursive(tx, &charac.Content[i], project_id)
if err != nil {
return err
}
}
return nil
}
func findUserByLogin(login string, loadPassword bool, db *sqlx.Tx) *User {
user := &User{}
user._db = db
passwordCol := ""
validated, err := validateSafeString(login, "login")
if err != nil {
return nil
}
if loadPassword {
passwordCol = ", `password`"
}
db.Get(user, "SELECT `id`, `login`, `name`, `last_login_at`, `deleted`"+passwordCol+" FROM `user` WHERE `login` = ? AND `deleted` IS NULL", validated)
if user.Id == 0 {
return nil
}
return user
}
func CreateApplication(tx *sqlx.Tx, application *Application) error {
const (
Q = `INSERT INTO applications (owner_id, name, client_id, client_secret, redirect_uri) VALUES ($1, $2, $3, $4, $5) RETURNING *;`
)
rand_a, err := make_rand(16)
if err != nil {
return err
}
rand_b, err := make_rand(32)
if err != nil {
return err
}
return tx.Get(application, Q,
application.OwnerId,
application.Name,
rand_a,
rand_b,
application.RedirectURI,
)
}
