Golang Tx.Select方法代码示例

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
}

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
}

// 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 (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...)
}

// 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
}

// 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
}

// 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
}

// 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
}

// 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
}

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
}

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
}

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)
}

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
}

// 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
}

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
}