Golang Tx.NamedExec方法代码示例

// Update the charac in the database
func (u *Charac) Update(tx *sqlx.Tx) (err error) {
	_, err = tx.NamedExec("UPDATE \"charac\" SET "+Charac_UpdateStr+" WHERE id=:id", u)
	if err != nil {
		err = errors.New("model.charac::Update " + err.Error())
	}
	return
}

// DeleteAuthors deletes the author linked to a database
func (d *Database) DeleteAuthors(tx *sqlx.Tx) (err error) {
	_, err = tx.NamedExec("DELETE FROM \"database__authors\" WHERE database_id=:id", d)
	if err != nil {
		err = errors.New("database::DeleteAuthors: " + err.Error())
	}
	return
}

// UpdateHandle links continents to a database
func (d *Database) UpdateHandle(tx *sqlx.Tx, handle *Database_handle) (err error) {
	_, err = tx.NamedExec("UPDATE database_handle SET "+Database_handle_UpdateStr+" WHERE id = :id", handle)
	if err != nil {
		err = errors.New("database::UpdateHandle: " + err.Error())
	}
	return
}

// CacheDates get database sites extend and cache enveloppe
func (d *Database) CacheDates(tx *sqlx.Tx) (err error) {
	_, err = tx.NamedExec("UPDATE database SET start_date = (SELECT COALESCE(min(start_date1),-2147483648) FROM site_range WHERE site_id IN (SELECT id FROM site where database_id = :id) AND start_date1 != -2147483648), end_date = (SELECT COALESCE(max(end_date2),2147483647) FROM site_range WHERE site_id IN (SELECT id FROM site where database_id = :id) AND end_date2 != 2147483647) WHERE id = :id", d)
	if err != nil {
		err = errors.New("database::CheckDates: " + err.Error())
	}
	return
}

// DeleteContexts deletes the context linked to a database
func (d *Database) DeleteContexts(tx *sqlx.Tx) error {
	_, err := tx.NamedExec("DELETE FROM \"database_context\" WHERE database_id=:id", d)
	if err != nil {
		err = errors.New("database::DeleteContexts: " + err.Error())
	}
	return err
}

// SetUsers of the group
func (g *Group) SetUsers(tx *sqlx.Tx, users []User) error {
	ids := make([]string, len(users)+1)
	for i, user := range users {
		ids[i] = fmt.Sprintf("%d", user.Id)
	}
	ids[len(users)] = "-1" // empty list is a problem

	_, err := tx.NamedExec("DELETE FROM \"user__group\" WHERE group_id=:id AND user_id NOT IN ("+strings.Join(ids, ",")+")", g)
	if err != nil {
		return err
	}

	rows, err := tx.Queryx("SELECT user_id FROM user__group WHERE group_id = $1 AND user_id IN ("+strings.Join(ids, ",")+")", g.Id)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var id string
		rows.Scan(&id)
		ids = removeString(ids, id)
	}
	ids = removeString(ids, "-1")

	for _, userid := range ids {
		_, err := tx.Exec("INSERT INTO user__group (group_id, user_id) VALUES ($1, $2)", g.Id, userid)
		if err != nil {
			return err
		}
	}
	return nil
}

// SetCompanies set user companies
func (u *User) SetCompanies(tx *sqlx.Tx, companies []Company) error {
	ids := make([]string, len(companies)+1)
	for i, company := range companies {
		ids[i] = fmt.Sprintf("%d", company.Id)
	}
	ids[len(companies)] = "-1" // empty list is a problem

	_, err := tx.NamedExec("DELETE FROM \"user__company\" WHERE user_id=:id AND company_id NOT IN ("+strings.Join(ids, ",")+")", u)
	if err != nil {
		return err
	}

	rows, err := tx.Queryx("SELECT company_id FROM user__company WHERE user_id = $1 AND company_id IN ("+strings.Join(ids, ",")+")", u.Id)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var id string
		rows.Scan(&id)
		ids = removeString(ids, id)
	}
	ids = removeString(ids, "-1")

	for _, companyid := range ids {
		_, err := tx.Exec("INSERT INTO user__company (user_id, company_id) VALUES ($1, $2)", u.Id, companyid)
		if err != nil {
			return err
		}
	}
	return nil
}

// SetGroups set user groups
func (u *User) SetGroups(tx *sqlx.Tx, groups []Group) error {
	ids := make([]string, len(groups))
	for i, group := range groups {
		ids[i] = fmt.Sprintf("%d", group.Id)
	}

	_, err := tx.NamedExec("DELETE FROM \"user__group\" WHERE user_id=:id AND group_id NOT IN ("+strings.Join(ids, ",")+")", u)
	if err != nil {
		return err
	}

	rows, err := tx.Queryx("SELECT group_id FROM user__group WHERE user_id = $1 AND group_id IN ("+strings.Join(ids, ",")+")", u.Id)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var id string
		rows.Scan(&id)
		ids = removeString(ids, id)
	}

	for _, groupid := range ids {
		_, err := tx.Exec("INSERT INTO user__group (user_id, group_id) VALUES ($1, $2)", u.Id, groupid)
		if err != nil {
			return err
		}
	}
	return nil
}

// Delete the charac from the database
func (u *Charac) Delete(tx *sqlx.Tx) (err error) {
	_, err = tx.NamedExec("DELETE FROM \"charac\" WHERE id=:id", u)
	if err != nil {
		err = errors.New("model.charac::Delete " + err.Error())
	}
	return
}

// Update database informations
func (d *Database) Update(tx *sqlx.Tx) (err error) {
	_, err = tx.NamedExec("UPDATE \"database\" SET "+Database_UpdateStr+" WHERE id=:id", d)
	if err != nil {
		err = errors.New("database::Update: " + err.Error())
	}
	return
}

// Update the shapefile in the shapefile
func (u *Shapefile) Update(tx *sqlx.Tx) error {
	fmt.Println("UPDATE \"shapefile\" SET " + Shapefile_UpdateStr + " WHERE id=:id")
	//q := "UPDATE \"shapefile\" SET \"creator_user_id\" = :creator_user_id, \"filename\" = :filename, \"md5sum\" = :md5sum, \"geojson_with_data\" = :geojson_with_data, \"geojson\" = :geojson, \"start_date\" = :start_date, \"end_date\" = :end_date, \"geographical_extent_geom\" = ST_GeomFromGeoJSON(:geographical_extent_geom), \"published\" = :published, \"license\" = :license, \"license_id\" = :license_id, \"declared_creation_date\" = :declared_creation_date, \"updated_at\" = now() WHERE id=:id"
	// Do not update geojson datas
	q := "UPDATE \"shapefile\" SET \"creator_user_id\" = :creator_user_id, \"filename\" = :filename, \"md5sum\" = :md5sum, \"start_date\" = :start_date, \"end_date\" = :end_date, \"geographical_extent_geom\" = ST_GeomFromGeoJSON(:geographical_extent_geom), \"published\" = :published, \"license\" = :license, \"license_id\" = :license_id, \"declared_creation_date\" = :declared_creation_date, \"updated_at\" = now() WHERE id=:id"
	_, err := tx.NamedExec(q, u)
	return err
}

// CacheGeom get database sites extend and cache enveloppe
func (d *Database) CacheGeom(tx *sqlx.Tx) (err error) {
	// Extent
	//_, err = tx.NamedExec("SELECT ST_Envelope(sites.geom::::geometry) FROM (SELECT geom FROM site WHERE database_id = :id) as sites", d)
	// Envelope
	_, err = tx.NamedExec("UPDATE database SET geographical_extent_geom = (SELECT (ST_Envelope((SELECT ST_Multi(ST_Collect(f.geom)) as singlegeom FROM (SELECT (ST_Dump(geom::::geometry)).geom As geom FROM site WHERE database_id = :id) As f)))) WHERE id = :id", d)
	if err != nil {
		err = errors.New("database::CacheGeom: " + err.Error())
	}
	return
}

// increment referer balance
func incrementRefererBalance(tx *sqlx.Tx, refererID int64, delta float64) (int64, error) {
	result, err := tx.NamedExec("UPDATE users SET `balance` = `balance` + :delta, `total_income_from_referees` = `total_income_from_referees` + :delta WHERE id = :id", map[string]interface{}{
		"id":    refererID,
		"delta": delta,
	})
	if err != nil {
		return 0, fmt.Errorf("increment referer balance error: %v", err)
	}

	rowAffected, _ := result.RowsAffected()
	return rowAffected, nil
}

// increment total reward
func incrementTotalReward(tx *sqlx.Tx, totalReward float64, now time.Time) error {
	sql := "INSERT INTO total_rewards (`total`, `created_at`) VALUES (:delta, :created_at) ON DUPLICATE KEY UPDATE `total` = `total` + :delta"
	args := map[string]interface{}{
		"delta":      totalReward,
		"created_at": now,
	}

	if _, err := tx.NamedExec(sql, args); err != nil {
		return fmt.Errorf("increment total reward error: %v", err)
	}

	return nil
}

func createPtcwallIncomeWithTx(tx *sqlx.Tx, income models.Income) error {
	incomeID, err := addIncome(tx, income)
	if err != nil {
		return err
	}

	// insert ptcwall offer
	offer := models.PtcwallOffer{
		IncomeID: incomeID,
		UserID:   income.UserID,
		Amount:   income.Income,
	}
	_, err = tx.NamedExec("INSERT INTO `ptcwalls` (`income_id`, `user_id`, `amount`) VALUE (:income_id, :user_id, :amount)", offer)
	return err
}