Golang DB.Rebind方法代码示例

// ExtractCities efficiently from a user's message.
func ExtractCities(db *sqlx.DB, in *dt.Msg) ([]dt.City, error) {
	// Interface type is used to expand the args in db.Select below.
	// Although we're only storing strings, []string{} doesn't work.
	var args []interface{}

	// Look for "at", "in", "on" prepositions to signal that locations
	// follow, skipping everything before
	var start int
	for i := range in.Stems {
		switch in.Stems[i] {
		case "at", "in", "on":
			start = i
			break
		}
	}

	// Prepare sentence for iteration
	tmp := regexNonWords.ReplaceAllString(in.Sentence, "")
	words := strings.Fields(strings.Title(tmp))

	// Iterate through words and bigrams to assemble a DB query
	for i := start; i < len(words); i++ {
		args = append(args, words[i])
	}
	bgs := bigrams(words, start)
	for i := 0; i < len(bgs); i++ {
		args = append(args, bgs[i])
	}

	cities := []dt.City{}
	q := `SELECT name, countrycode FROM cities
	      WHERE countrycode='US' AND name IN (?)
	      ORDER BY LENGTH(name) DESC`
	query, arguments, err := sqlx.In(q, args)
	query = db.Rebind(query)
	rows, err := db.Query(query, arguments...)
	if err != nil {
		return nil, err
	}
	defer func() {
		if err = rows.Close(); err != nil {
			log.Info("failed to close db rows.", err)
		}
	}()
	for rows.Next() {
		city := dt.City{}
		if err = rows.Scan(&city.Name, &city.CountryCode); err != nil {
			return nil, err
		}
		cities = append(cities, city)
	}
	if err = rows.Err(); err != nil {
		return nil, err
	}
	if len(cities) == 0 {
		return nil, ErrNotFound
	}
	return cities, nil
}

func InsertUser(db *sqlx.DB, user *models.User) error {
	_, err := db.NamedExec(db.Rebind(`INSERT INTO users (fbid, first_name, last_name, roles) VALUES (:fbid, :first_name, :last_name, :roles)`), user)
	if err != nil {
		return err
	}

	return nil
}

// GetLocationBySlug Looks up a location by its Yelp ID
func GetLocationBySlug(db *sqlx.DB, slug string) (*models.Location, error) {
	l := models.Location{}
	err := db.Get(&l, db.Rebind(`SELECT * FROM locations WHERE slug=?`), slug)
	if err != nil {
		return nil, err
	}
	return &l, nil
}

func RebindInsert(db *sqlx.DB, q string) string {
	q = db.Rebind(q)
	q = strings.TrimRight(q, " \t\n;")
	if db.DriverName() == "postgres" {
		q = q + " RETURNING id"
	}

	return q
}

func GetUserByID(db *sqlx.DB, id int) (*models.User, error) {
	u := models.User{}
	err := db.Get(&u, db.Rebind(`SELECT * FROM users WHERE id=?`), id)
	if err != nil {
		log.Printf("Error while getting user by id: %v", err)
		return nil, err
	}
	return &u, nil
}

func insertNotices(conn *sqlx.DB, results []NoticeResult) {
	sql := conn.Rebind(`
		INSERT INTO notice(id, published)
		SELECT ?, ? 
		WHERE NOT EXISTS (SELECT 1 FROM notice WHERE id = ?)`)
	for _, result := range results {
		conn.MustExec(
			sql, result.DocumentNumber, result.Published,
			result.DocumentNumber)
	}
	insertAgencies(conn, results)
}

func insertAgencies(conn *sqlx.DB, results []NoticeResult) {
	delete := conn.Rebind("DELETE FROM notice_agency WHERE notice_id = ?")
	insert := conn.Rebind(`
		INSERT INTO notice_agency (notice_id, agency)
		VALUES (?, ?)`)
	for _, result := range results {
		conn.MustExec(delete, result.DocumentNumber)
		for _, agency := range result.Agencies {
			conn.MustExec(insert, result.DocumentNumber, agency)
		}
	}
}

func ParseQueryStruct(q string, d interface{}, db *sqlx.DB) (string, []interface{}, error) {
	errors := make([]error, 2)
	query, args, err := sqlx.Named(q, d)
	errors = append(errors, err)
	query, args, err = sqlx.In(query, args...)
	errors = append(errors, err)
	query = db.Rebind(query)
	for _, e := range errors {
		if e != nil {
			return "", nil, e
		}
	}
	return query, args, nil
}

func createLinkSQL(db *sqlx.DB, tx *sql.Tx, table string, p Policy, templates []string) error {
	for _, template := range templates {
		reg, err := compiler.CompileRegex(template, p.GetStartDelimiter(), p.GetEndDelimiter())

		// Execute SQL statement
		query := db.Rebind(fmt.Sprintf("INSERT INTO %s (policy, template, compiled) VALUES (?, ?, ?)", table))
		if _, err = tx.Exec(query, p.GetID(), template, reg.String()); err != nil {
			if rb := tx.Rollback(); rb != nil {
				return errors.Wrap(rb, "")
			}
			return errors.Wrap(err, "")
		}
	}
	return nil
}

// InsertEvent takes a DB connect and inserts a new EventEntry
func InsertEvent(db *sqlx.DB, event *models.Event) (*models.Event, error) {
	rows, err := db.NamedQuery(db.Rebind(`
		INSERT INTO events (title, slug, start_time, end_time, organizer_id, location_id, rsvps)
		VALUES (:title, :slug, :start_time, :end_time, :organizer_id, :location_id, :rsvps)
		RETURNING id`), event)
	if err != nil {
		return nil, err
	}

	if rows.Next() {
		rows.Scan(&event.ID)
	}

	return event, nil
}

// InsertLocation inserts a new Location object into the DB
func InsertLocation(db *sqlx.DB, location *models.Location) (*models.Location, error) {
	rows, err := db.NamedQuery(db.Rebind(`
		INSERT INTO locations (name, slug, address, lat_lng)
		VALUES (:name, :slug, :address, :lat_lng)
		RETURNING id
	`), location)
	if err != nil {
		return nil, err
	}

	if rows.Next() {
		rows.Scan(&location.ID)
	}

	return location, nil
}

func getLinkedSQL(db *sqlx.DB, table, policy string) ([]string, error) {
	urns := []string{}
	rows, err := db.Query(db.Rebind(fmt.Sprintf("SELECT template FROM %s WHERE policy=?", table)), policy)
	if err == sql.ErrNoRows {
		return nil, errors.Wrap(pkg.ErrNotFound, "")
	} else if err != nil {
		return nil, errors.Wrap(err, "")
	}

	defer rows.Close()
	for rows.Next() {
		var urn string
		if err = rows.Scan(&urn); err != nil {
			return []string{}, errors.Wrap(err, "")
		}
		urns = append(urns, urn)
	}
	return urns, nil
}