Example #1
0
File: db.go Project: upwrd/sift
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
}
Example #2
0
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
}
Example #3
0
// 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
}
Example #4
0
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...)
}
Example #5
0
// 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
}
Example #6
0
// GetCountryList lists all countries linked to a database
func (d *Database) GetCountryList(tx *sqlx.Tx, langIsocode string) ([]CountryInfos, error) {
	countries := []CountryInfos{}
	err := tx.Select(&countries, "SELECT ct.name, c.geonameid, c.iso_code, c.geom FROM country c LEFT JOIN database__country dc ON c.geonameid = dc.country_geonameid LEFT JOIN country_tr ct ON c.geonameid = ct.country_geonameid WHERE dc.database_id = $1 and ct.lang_isocode = $2", d.Id, langIsocode)
	if err != nil {
		err = errors.New("database::GetCountryList: " + err.Error())
	}
	return countries, err
}
Example #7
0
// GetHandles lists all handles linked to a database
func (d *Database) GetHandles(tx *sqlx.Tx) (handles []Database_handle, err error) {
	handles = []Database_handle{}
	err = tx.Select(&handles, "SELECT import_id, identifier, url, declared_creation_date, created_at FROM database_handle WHERE database_id = $1", d.Id)
	if err != nil {
		err = errors.New("database::GetHandles: " + err.Error())
	}
	return
}
Example #8
0
// GetContinentList lists all continents linked to a database
func (d *Database) GetContinentList(tx *sqlx.Tx, langIsocode string) (continents []ContinentInfos, err error) {
	continents = []ContinentInfos{}
	err = tx.Select(&continents, "SELECT ct.name, c.geonameid, c.iso_code, c.geom FROM continent c LEFT JOIN database__continent dc ON c.geonameid = dc.continent_geonameid LEFT JOIN continent_tr ct ON c.geonameid = ct.continent_geonameid WHERE dc.database_id = $1 AND ct.lang_isocode = $2", d.Id, langIsocode)
	if err != nil {
		err = errors.New("database::GetContinentList: " + err.Error())
	}
	return continents, err
}
Example #9
0
// 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
}
Example #10
0
func getWmLayers(params *LayersParams, viewUnpublished bool, tx *sqlx.Tx) (layers []*model.LayerFullInfos, err error) {

	layers = []*model.LayerFullInfos{}

	q := "SELECT m.id, m.type, m.start_date, m.end_date, m.min_scale, m.max_scale, ST_AsGeoJSON(m.geographical_extent_geom) as geographical_extent_geom, m.published, m.created_at, m.creator_user_id, u.firstname || ' ' || u.lastname as author FROM map_layer m LEFT JOIN \"user\" u ON m.creator_user_id = u.id WHERE m.id > 0"

	if params.Author > 0 {
		q += " AND u.id = :author"
	}

	if params.Published || !viewUnpublished {
		q += " AND m.published = 't'"
	}

	if params.Type != "" {
		q += " AND m.type= :type"
	}

	if params.Bounding_box != "" {
		q += " AND (ST_Contains(ST_GeomFromGeoJSON(:bounding_box), m.geographical_extent_geom::::geometry) OR ST_Contains(m.geographical_extent_geom::::geometry, ST_GeomFromGeoJSON(:bounding_box)) OR ST_Overlaps(ST_GeomFromGeoJSON(:bounding_box), m.geographical_extent_geom::::geometry))"
	}

	if params.Check_dates {
		q += " AND m.start_date >= :start_date AND m.end_date <= :end_date"
	}

	in := model.IntJoin(params.Ids, false)

	if in != "" {
		q += " AND m.id IN (" + in + ")"
	}

	nstmt, err := tx.PrepareNamed(q)
	if err != nil {
		log.Println(err)
		_ = tx.Rollback()
		return
	}
	err = nstmt.Select(&layers, params)

	for _, layer := range layers {

		tr := []model.Map_layer_tr{}
		err = tx.Select(&tr, "SELECT * FROM map_layer_tr WHERE map_layer_id = "+strconv.Itoa(layer.Id))
		if err != nil {
			log.Println(err)
			_ = tx.Rollback()
			return
		}
		layer.Uniq_code = layer.Type + strconv.Itoa(layer.Id)
		layer.Name = model.MapSqlTranslations(tr, "Lang_isocode", "Name")
		layer.Attribution = model.MapSqlTranslations(tr, "Lang_isocode", "Attribution")
		layer.Copyright = model.MapSqlTranslations(tr, "Lang_isocode", "Copyright")
		layer.Description = model.MapSqlTranslations(tr, "Lang_isocode", "Description")
	}

	return
}
Example #11
0
func findAllConsumers(db *sqlx.Tx) []Consumer {
	list := make([]Consumer, 0)
	db.Select(&list, "SELECT `id`, `name`, `created_at`, `updated_at`, `created_by`, `updated_by`, `enabled`, `deleted`, `info_token` FROM `consumer` c WHERE `deleted` = 0 ORDER BY `name`")

	for i := range list {
		list[i]._db = db
	}

	return list
}
Example #12
0
File: user.go Project: xrstf/raziel
func usersIndexAction(user *User, session *Session, db *sqlx.Tx) response {
	data := &userListData{NewLayoutData("Users", "users", user, session.CsrfToken), make([]User, 0)}

	// find users (do not even select the user itself, we don't need it)
	db.Select(&data.Users, "SELECT `id`, `login`, `name`, `last_login_at`, `deleted` FROM `user` WHERE `deleted` IS NULL ORDER BY `name`")

	for i := range data.Users {
		data.Users[i]._db = db
	}

	return renderTemplate(200, "users/index", data)
}
Example #13
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 #14
0
// GetTranslations lists all translated fields from database
func (d *DatabaseFullInfos) GetTranslations(tx *sqlx.Tx) (err error) {
	tr := []Database_tr{}
	err = tx.Select(&tr, "SELECT * FROM database_tr WHERE database_id = $1", d.Id)
	if err != nil {
		return
	}
	d.Description = MapSqlTranslations(tr, "Lang_isocode", "Description")
	d.Geographical_limit = MapSqlTranslations(tr, "Lang_isocode", "Geographical_limit")
	d.Bibliography = MapSqlTranslations(tr, "Lang_isocode", "Bibliography")
	d.Context_description = MapSqlTranslations(tr, "Lang_isocode", "Context_description")
	d.Source_description = MapSqlTranslations(tr, "Lang_isocode", "Source_description")
	d.Source_relation = MapSqlTranslations(tr, "Lang_isocode", "Source_relation")
	d.Copyright = MapSqlTranslations(tr, "Lang_isocode", "Copyright")
	d.Subject = MapSqlTranslations(tr, "Lang_isocode", "Subject")
	return
}
Example #15
0
func GetAccountsWithIdentityId(tx *sqlx.Tx, identity_id int64) ([]*Account, error) {
	const (
		Q = `SELECT * FROM accounts WHERE identity_id = $1 ORDER BY remote_id;`
	)

	accounts := []*Account{}
	err := tx.Select(&accounts, Q, identity_id)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}

	return accounts, nil
}
Example #16
0
File: user.go Project: xrstf/raziel
func findAllUsers(loadPasswords bool, db *sqlx.Tx) []User {
	list := make([]User, 0)
	passwordCol := ""

	if loadPasswords {
		passwordCol = ", `password`"
	}

	db.Select(&list, "SELECT `id`, `login`, `name`, `last_login_at`, `deleted`"+passwordCol+" FROM `user` WHERE `deleted` IS NULL ORDER BY `name`, `login`")

	for i := range list {
		list[i]._db = db
	}

	return list
}
Example #17
0
func findRestrictionsByConsumer(consumerId int, loadContext bool, db *sqlx.Tx) []Restriction {
	list := make([]Restriction, 0)
	contextCol := ""

	if loadContext {
		contextCol = ", `context`"
	}

	db.Select(&list, "SELECT `consumer_id`, `type`"+contextCol+", `enabled` FROM `restriction` WHERE `consumer_id` = ?", consumerId)

	for i := range list {
		list[i]._db = db
	}

	return list
}
Example #18
0
func GetApplicationsOwnedByIdentity(tx *sqlx.Tx, identity_id int64) ([]*Application, error) {
	const (
		Q = `SELECT * FROM applications WHERE owner_id = $1 ORDER BY name;`
	)

	applications := []*Application{}
	err := tx.Select(&applications, Q, identity_id)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}

	return applications, nil
}
Example #19
0
func consumersIndexAction(user *User, session *Session, db *sqlx.Tx) response {
	data := &consumerListData{NewLayoutData("Consumers", "consumers", user, session.CsrfToken), make([]Consumer, 0)}
	lastSeen := "(SELECT a.`requested_at` FROM `access_log` a WHERE a.`consumer_id` = c.`id` ORDER BY `id` DESC LIMIT 1) AS `last_seen`"

	// find consumers (do not even select the consumer itself, we don't need it)
	db.Select(
		&data.Consumers,
		"SELECT `id`, `name`, `created_at`, `updated_at`, `created_by`, `updated_by`, `enabled`, `info_token`, "+lastSeen+" FROM `consumer` c WHERE `deleted` = 0 ORDER BY `name`",
	)

	for i := range data.Consumers {
		data.Consumers[i]._db = db
	}

	return renderTemplate(200, "consumers/index", data)
}
Example #20
0
File: db.go Project: upwrd/sift
func getDevicesTx(tx *sqlx.Tx, exFlags ExpansionFlags) (map[types.DeviceID]types.Device, error) {
	devIDs := []int64{}
	if err := tx.Select(&devIDs, "SELECT id FROM device"); err != nil {
		return nil, fmt.Errorf("error getting device IDs from database: %v", err)
	}

	devs := make(map[types.DeviceID]types.Device)
	for _, id := range devIDs {
		dev := types.Device{}
		devID := types.DeviceID(id)
		if err := getDeviceTx(tx, &dev, devID, exFlags); err != nil {
			return nil, fmt.Errorf("could not get expanded Device with ID %v: %v", id, err)
		}
		devs[devID] = dev
	}
	return devs, nil
}
Example #21
0
func mapDebug(sites []int, tx *sqlx.Tx) {
	type row struct {
		Id          int
		Start_date1 int
		Start_date2 int
		End_date1   int
		End_date2   int
	}
	rows := []row{}
	err := tx.Select(&rows, "SELECT site.id, sr.start_date1, sr.start_date2, sr.end_date1, sr.end_date2 FROM site LEFT JOIN site_range sr ON sr.site_id = site.id WHERE site.id IN("+model.IntJoin(sites, true)+")")
	if err != nil {
		fmt.Println("err: ", err)
	} /*else {
		for _, r := range rows {
			fmt.Println("r: ", r)
		}
	}*/
}
Example #22
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 #23
0
func getPlayerStatsForGame(tx *sqlx.Tx, gameId int) (models.PlayerStats, error) {
	var ids []int
	log.Debug("Getting player stats for game with id=%d", gameId)
	err := tx.Select(&ids, `SELECT id FROM player_stats WHERE game_id=$1`, gameId)
	if err != nil {
		return nil, fmt.Errorf("Failed to get player stats for game with id=%d: %v", gameId, err)
	}

	var playerStats models.PlayerStats
	for _, id := range ids {
		playerStat, err := getPlayerStat(tx, id)
		if err != nil {
			return nil, err
		}
		playerStats = append(playerStats, playerStat)
	}

	log.Debug("Successfully got player stats for game with id=%d", gameId)
	return playerStats, nil
}
Example #24
0
File: db.go Project: upwrd/sift
func getComponentsForDevice(tx *sqlx.Tx, dest map[string]types.Component, deviceID types.DeviceID, exFlags ExpansionFlags) error {
	// get base components matching the device ID
	componentIDs := []int64{}
	err := tx.Select(&componentIDs, "SELECT id FROM component WHERE device_id=?", deviceID)
	if err != nil {
		return fmt.Errorf("error getting components for device %v: %v", deviceID, err)
	}

	Log.Debug("queried for components", "device_id", deviceID, "found_component_ids", componentIDs)

	// Build a set of components from database
	for _, id := range componentIDs {
		name, comp, err := getComponentTx(tx, id, exFlags)
		if err != nil {
			return fmt.Errorf("error getting component with ID %v: %v", id, err)
		}
		dest[name] = comp // put into the destination map
	}
	return nil // all components gathered without error
}
Example #25
0
func mapGetSitesAsJson(sites []int, tx *sqlx.Tx) string {

	// for measuring execution time
	start := time.Now()

	var jsonResult []string

	q := `SELECT '{"type": "Feature", ' ||`
	q += `'"geometry": {"type": "Point", "coordinates": [' || (`
	q += `	SELECT ST_X(geom::geometry) || ', ' || ST_Y(geom::geometry) AS coordinates FROM site WHERE id = s.id`
	q += `) || ']}, ' ||`
	q += `'"properties": {"infos": ' || (`
	q += `	SELECT row_to_json(site_infos) || ',' || `
	q += `	'"site_ranges": ' || (`
	q += `		SELECT  array_to_json(array_agg(row_to_json(q_src))) FROM (`
	q += `			SELECT start_date1, start_date2, end_date1, end_date2, `
	q += `			(`
	q += `				SELECT array_to_json(array_agg(row_to_json(q_src2))) FROM (`
	q += `					SELECT src.* FROM site_range__charac src WHERE src.site_range_id IN (SELECT site_range_id FROM site_range__charac WHERE site_range_id = sr.id)`
	q += `				) q_src2`
	q += `			) characs`
	q += `	   	FROM site_range sr WHERE sr.site_id = s.id) q_src`
	q += `	)`
	q += `	 FROM (SELECT si.id, si.code, si.name, si.centroid, si.occupation, si.start_date1, si.start_date2, si.end_date1, si.end_date2, d.id AS database_id, d.name as database_name FROM site si LEFT JOIN database d ON si.database_id = d.id WHERE si.id = s.id) site_infos`
	q += `)`
	q += `|| '}}'`
	q += ` FROM site s WHERE s.id IN (` + model.IntJoin(sites, true) + `)`

	err := tx.Select(&jsonResult, q)

	elapsed := time.Since(start)
	fmt.Printf("mapGetSitesAsJson took %s", elapsed)

	if err != nil {
		fmt.Println(err.Error())
	}

	jsonString := `{"type": "FeatureCollection", "features": [` + strings.Join(jsonResult, ",") + `]}`

	return jsonString
}
Example #26
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 #27
0
// replaceCommentsIntoDB inserts, updates, or deletes the given BuildComments
// into the database. Returns a map whose keys are pointers to any
// newly-inserted BuildComments and whose values are the IDs which should be
// assigned to those comments. This allows us to postpone assigning any IDs
// until the transaction has completed successfully.
func replaceCommentsIntoDB(tx *sqlx.Tx, comments []*BuildComment, newBuildID int) (map[*BuildComment]int, error) {
	// First, determine which comments need to be inserted, updated, and deleted.
	commentsFromDB := []*BuildComment{}
	stmt := fmt.Sprintf("SELECT * FROM %s WHERE buildId = ?;", TABLE_BUILD_COMMENTS)
	if err := tx.Select(&commentsFromDB, stmt, newBuildID); err != nil {
		return nil, fmt.Errorf("Could not retrieve build comments from database: %v", err)
	}
	oldComments := make(map[int]bool, len(commentsFromDB))
	for _, c := range commentsFromDB {
		oldComments[int(c.Id)] = true
	}
	newComments := make(map[int]*BuildComment, len(comments))
	for _, c := range comments {
		newComments[c.Id] = c
	}
	update := make([]*BuildComment, 0, len(comments))
	insert := make([]*BuildComment, 0, len(comments))
	remove := make([]int, 0, len(comments))
	for _, c := range comments {
		if _, ok := oldComments[c.Id]; ok {
			update = append(update, c)
			delete(oldComments, c.Id)
		} else {
			insert = append(insert, c)
		}
	}
	for id, _ := range oldComments {
		remove = append(remove, id)
	}

	rv := map[*BuildComment]int{}

	// Delete any no-longer-existing comments.
	if len(remove) > 0 {
		idTmpl := util.RepeatJoin("?", ",", len(remove))
		removeIds := make([]interface{}, 0, len(remove))
		for _, id := range remove {
			removeIds = append(removeIds, id)
		}
		stmt := fmt.Sprintf("DELETE FROM %s WHERE id IN (%s)", TABLE_BUILD_COMMENTS, idTmpl)
		if _, err := tx.Exec(stmt, removeIds...); err != nil {
			return nil, fmt.Errorf("Could not remove old build comments.")
		}
	}

	// Insert any new comments.
	for _, c := range insert {
		stmt := fmt.Sprintf("INSERT INTO %s (buildId,user,timestamp,message) VALUES (?,?,?,?);", TABLE_BUILD_COMMENTS)
		res, err := tx.Exec(stmt, newBuildID, c.User, c.Timestamp, c.Message)
		if err != nil {
			return nil, fmt.Errorf("Unable to push build comments into database: %v", err)
		}
		id, err := res.LastInsertId()
		if err != nil {
			return nil, fmt.Errorf("Unable to get ID for inserted build comment: %v", err)
		}
		rv[c] = int(id)
	}

	// Update any already-existing comments.
	for _, c := range update {
		stmt := fmt.Sprintf("UPDATE %s SET buildId=?, user=?, timestamp=?, message=? WHERE id = ?", TABLE_BUILD_COMMENTS)
		if _, err := tx.Exec(stmt, c.BuildId, c.User, c.Timestamp, c.Message, c.Id); err != nil {
			return nil, fmt.Errorf("Failed to update build comments: %v", err)
		}
	}

	return rv, nil
}
Example #28
0
// replaceStepsIntoDB inserts, updates, or deletes the given BuildSteps into
// the database. Returns a map whose keys are pointers to any newly-inserted
// BuildSteps and whose values are the IDs which should be assigned to those
// steps. This allows us to postpone assigning any IDs until the transaction
// has completed successfully.
func replaceStepsIntoDB(tx *sqlx.Tx, steps []*BuildStep, newBuildID int) (map[*BuildStep]int, error) {
	// First, determine which steps need to be inserted, updated, and deleted.
	stepsFromDB := []*buildStepFromDB{}
	stmt := fmt.Sprintf("SELECT * FROM %s WHERE buildId = ?;", TABLE_BUILD_STEPS)
	if err := tx.Select(&stepsFromDB, stmt, newBuildID); err != nil {
		return nil, fmt.Errorf("Could not retrieve build steps from database: %v", err)
	}
	oldSteps := make(map[int]bool, len(stepsFromDB))
	for _, s := range stepsFromDB {
		oldSteps[int(s.Id)] = true
	}
	newSteps := make(map[int]*BuildStep, len(steps))
	for _, s := range steps {
		newSteps[s.Id] = s
	}
	update := make([]*BuildStep, 0, len(steps))
	insert := make([]*BuildStep, 0, len(steps))
	remove := make([]int, 0, len(steps))
	for _, s := range steps {
		if _, ok := oldSteps[s.Id]; ok {
			update = append(update, s)
			delete(oldSteps, s.Id)
		} else {
			insert = append(insert, s)
		}
	}
	for id, _ := range oldSteps {
		remove = append(remove, id)
	}

	rv := map[*BuildStep]int{}

	// Delete any no-longer-existing steps.
	if len(remove) > 0 {
		idTmpl := util.RepeatJoin("?", ",", len(remove))
		removeIds := make([]interface{}, 0, len(remove))
		for _, id := range remove {
			removeIds = append(removeIds, id)
		}
		stmt := fmt.Sprintf("DELETE FROM %s WHERE id IN (%s)", TABLE_BUILD_STEPS, idTmpl)
		if _, err := tx.Exec(stmt, removeIds...); err != nil {
			return nil, fmt.Errorf("Could not remove old build steps.")
		}
	}

	// Insert any new steps.
	for _, s := range insert {
		stmt := fmt.Sprintf("INSERT INTO %s (buildId,name,results,number,started,finished) VALUES (?,?,?,?,?,?);", TABLE_BUILD_STEPS)
		res, err := tx.Exec(stmt, newBuildID, s.Name, s.Results, s.Number, s.Started, s.Finished)
		if err != nil {
			return nil, fmt.Errorf("Unable to push buildsteps into database: %v", err)
		}
		id, err := res.LastInsertId()
		if err != nil {
			return nil, fmt.Errorf("Unable to get ID for inserted buildstep: %v", err)
		}
		rv[s] = int(id)
	}

	// Update any already-existing steps.
	for _, s := range update {
		stmt := fmt.Sprintf("UPDATE %s SET buildId=?, name=?, results=?, number=?, started=?, finished=? WHERE id = ?", TABLE_BUILD_STEPS)
		if _, err := tx.Exec(stmt, s.BuildID, s.Name, s.Results, s.Number, s.Started, s.Finished, s.Id); err != nil {
			return nil, fmt.Errorf("Failed to update build steps: %v", err)
		}
	}

	return rv, nil
}
Example #29
0
// update charac recursively
func setCharacRecursive(tx *sqlx.Tx, charac *CharacTreeStruct, parent *CharacTreeStruct) error {
	var err error = nil

	// if we are the root, we have no parent id
	if parent != nil {
		charac.Parent_id = parent.Id
	} else {
		charac.Parent_id = 0
	}

	// save charac...
	if charac.Id > 0 {
		err = charac.Update(tx)
		if err != nil {
			return err
		}
	} else {
		err = charac.Create(tx)
		if err != nil {
			return err
		}
	}

	//log.Println("c: ", charac)

	// delete any translations
	_, err = tx.Exec("DELETE FROM charac_tr WHERE charac_id = $1", charac.Id)
	if err != nil {
		return err
	}

	// create a map of translations for name...
	tr := map[string]*model.Charac_tr{}
	for isocode, name := range charac.Name {
		tr[isocode] = &model.Charac_tr{
			Charac_id:    charac.Id,
			Lang_isocode: isocode,
			Name:         name,
		}
	}

	// continue to update this map with descriptions...
	for isocode, description := range charac.Description {
		m, ok := tr[isocode]
		if ok {
			m.Description = description
		} else {
			tr[isocode] = &model.Charac_tr{
				Charac_id:    charac.Id,
				Lang_isocode: isocode,
				Description:  description,
			}
		}
	}

	// now insert translations rows in database...
	for _, m := range tr {
		err = m.Create(tx)
		if err != nil {
			return err
		}
	}

	// recursively call to subcontents...
	ids := []int{} // this array will be usefull to delete others charac of this sub level that does not exists anymore
	for _, sub := range charac.Content {
		err = setCharacRecursive(tx, &sub, charac)
		if err != nil {
			return err
		}
		ids = append(ids, sub.Charac.Id)
	}

	// search any charac that should be deleted
	ids_to_delete := []int{} // the array of characs id to delete
	err = tx.Select(&ids_to_delete, "SELECT id FROM charac WHERE id NOT IN ("+model.IntJoin(ids, true)+") AND parent_id = "+strconv.Itoa(charac.Charac.Id))
	if err != nil {
		return err
	}

	// delete translations of the characs that should be deleted
	_, err = tx.Exec("DELETE FROM charac_tr WHERE charac_id IN (" + model.IntJoin(ids_to_delete, true) + ")")
	if err != nil {
		return err
	}

	// delete characs itselfs...
	_, err = tx.Exec("DELETE FROM charac WHERE id IN (" + model.IntJoin(ids_to_delete, true) + ")")
	if err != nil {
		return err
	}

	return err
}
Example #30
0
func (app *Application) newestParts(tx *sqlx.Tx) ([]PartView, error) {
	var partViews []PartView
	err := tx.Select(&partViews, `SELECT * FROM 'part_view' ORDER BY "created_at" DESC LIMIT 10`)
	return partViews, err
}