Example #1
0
// 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
}
Example #2
0
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...)
}
Example #3
0
File: db.go Project: upwrd/sift
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
}
Example #4
0
func CreateIdentity(tx *sqlx.Tx, identity *Identity) error {
	const (
		Q = `INSERT INTO identities DEFAULT VALUES RETURNING *;`
	)

	return tx.Get(identity, Q)
}
Example #5
0
// 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
}
Example #6
0
// 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
}
Example #7
0
File: db.go Project: upwrd/sift
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
}
Example #8
0
// 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
}
Example #9
0
File: db.go Project: upwrd/sift
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
}
Example #10
0
// 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
}
Example #11
0
// 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
}
Example #12
0
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
}
Example #13
0
// 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
}
Example #14
0
// 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
}
Example #15
0
File: db.go Project: upwrd/sift
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
}
Example #16
0
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)
}
Example #17
0
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
}
Example #18
0
// 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
}
Example #19
0
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,
	)
}
Example #20
0
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
}
Example #21
0
// 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
}
Example #22
0
// 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
}
Example #23
0
File: user.go Project: xrstf/raziel
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
}
Example #24
0
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
}
Example #25
0
// 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
}
Example #26
0
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
}
Example #27
0
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
}
Example #28
0
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
}
Example #29
0
File: user.go Project: xrstf/raziel
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
}
Example #30
0
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,
	)
}