Golang Tx.PrepareNamed方法代码示例

// Create the company by inserting it in the database
func (u *Company) Create(tx *sqlx.Tx) error {
	stmt, err := tx.PrepareNamed("INSERT INTO \"company\" (" + Company_InsertStr + ") VALUES (" + Company_InsertValuesStr + ") RETURNING id")
	if err != nil {
		return err
	}
	defer stmt.Close()
	return stmt.Get(&u.Id, u)
}

// Create the group by inserting it in the database
func (g *Group) Create(tx *sqlx.Tx) error {
	stmt, err := tx.PrepareNamed("INSERT INTO \"group\" (" + Group_InsertStr + ") VALUES (" + Group_InsertValuesStr + ") RETURNING id")
	if err != nil {
		return err
	}
	defer stmt.Close()
	return stmt.Get(&g.Id, g)
}

// Get the group from the database
func (g *Group) Get(tx *sqlx.Tx) error {
	stmt, err := tx.PrepareNamed("SELECT * FROM \"group\" WHERE id=:id")
	if err != nil {
		return err
	}
	defer stmt.Close()
	return stmt.Get(g, g)
}

// Get retrieves informations about a database stored in the main table
func (d *Database) Get(tx *sqlx.Tx) (err error) {
	stmt, err := tx.PrepareNamed("SELECT * from \"database\" WHERE id=:id")
	defer stmt.Close()
	if err != nil {
		return errors.New("database::Get: " + err.Error())
	}
	return stmt.Get(d, d)
}

func (i *Import) Create(tx *sqlx.Tx) error {
	stmt, err := tx.PrepareNamed("INSERT INTO \"import\" (" + Import_InsertStr + ") VALUES (" + Import_InsertValuesStr + ") RETURNING id")
	if err != nil {
		return err
	}
	defer stmt.Close()
	return stmt.Get(&i.Id, i)
}

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
}

// GetCompanies return an array of companies of the User
func (u *User) GetCompanies(tx *sqlx.Tx) (companies []Company, err error) {
	stmt, err := tx.PrepareNamed("SELECT g.* FROM \"company\" g, \"user__company\" ug WHERE ug.user_id = :id AND ug.company_id = g.id")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	err = stmt.Select(&companies, u)
	return companies, err
}

// GetGroups return an array of groups of the User
func (u *User) GetGroups(tx *sqlx.Tx) (groups []Group, err error) {
	stmt, err := tx.PrepareNamed("SELECT g.* FROM \"group\" g, \"user__group\" ug WHERE ug.user_id = :id AND ug.group_id = g.id")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	err = stmt.Select(&groups, u)
	return groups, err
}

// Get the lang from the database
func (l *Lang) Get(tx *sqlx.Tx) error {
	var q = "SELECT * FROM \"lang\" WHERE isocode=:isocode"
	stmt, err := tx.PrepareNamed(q)
	if err != nil {
		return err
	}
	defer stmt.Close()
	return stmt.Get(l, l)
}

// Create the chronology_root by inserting it in the database
func (u *Chronology_root) Create(tx *sqlx.Tx) error {
	//stmt, err := tx.PrepareNamed("INSERT INTO \"chronology_root\" (" + Chronology_root_InsertStr + ", root_chronology_id) VALUES (" + Chronology_root_InsertValuesStr + ", :root_chronology_id) RETURNING root_chronology_id")
	stmt, err := tx.PrepareNamed("INSERT INTO \"chronology_root\" (\"admin_group_id\", \"author_user_id\", \"credits\", \"active\", geom, root_chronology_id, cached_langs) VALUES (:admin_group_id, :author_user_id, :credits, :active, ST_GeomFromGeoJSON(:geom), :root_chronology_id, :cached_langs) RETURNING root_chronology_id")
	if err != nil {
		return err
	}
	defer stmt.Close()
	return stmt.Get(&u.Root_chronology_id, u)
}

// Get the map layer from the database
func (u *Map_layer) Get(tx *sqlx.Tx) error {
	var q = "SELECT * FROM \"map_layer\" WHERE id=:id"
	stmt, err := tx.PrepareNamed(q)
	if err != nil {
		return err
	}
	defer stmt.Close()
	return stmt.Get(u, u)
}

// Get the chronology from the database
func (u *Chronology) Get(tx *sqlx.Tx) error {
	var q = "SELECT * FROM \"chronology\" WHERE id=:id"
	stmt, err := tx.PrepareNamed(q)
	if err != nil {
		return err
	}
	defer stmt.Close()
	return stmt.Get(u, u)
}

// Get the country from the database
func (u *Country) Get(tx *sqlx.Tx) error {
	var q = "SELECT * FROM \"country\" WHERE geonameid=:geonameid"
	stmt, err := tx.PrepareNamed(q)
	defer stmt.Close()
	if err != nil {
		return err
	}
	return stmt.Get(u, u)
}

// Get the project_hidden_characs from the database
func (u *Project_hidden_characs) Get(tx *sqlx.Tx) error {
	var q = "SELECT * FROM \"project_hidden_characs\" WHERE charac_id=:charac_id AND project_id=:project_id"
	stmt, err := tx.PrepareNamed(q)
	if err != nil {
		return err
	}
	defer stmt.Close()
	return stmt.Get(u, u)
}

// Get the shapefile from the shapefile
func (u *Shapefile) Get(tx *sqlx.Tx) error {
	var q = "SELECT * FROM \"shapefile\" WHERE id=:id"
	stmt, err := tx.PrepareNamed(q)
	if err != nil {
		return err
	}
	defer stmt.Close()
	return stmt.Get(u, u)
}