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