Golang Tx.Query方法代碼示例

// GetAuth is used by the supervisor to determine which users have access
// to which models.
func GetAuth(tx *sql.Tx) (*PredictionAuth, error) {
	q1 := `SELECT u.username, k.apikey, k.read_only_apikey
	FROM User u
	INNER JOIN ApiKey k
	ON u.user_id = k.user_id;`

	rows, err := tx.Query(q1)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	users := make(map[string]Apikeys)

	for rows.Next() {
		apikeys := Apikeys{}
		var username string
		err := rows.Scan(&username, &apikeys.Apikey, &apikeys.ReadOnly)
		if err != nil {
			return nil, err
		}
		users[username] = apikeys
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	q2 := `SELECT shareduser.username, owner.username, m.modelname
	FROM ModelSharedUser s
	INNER JOIN User shareduser
	ON shareduser.user_id = s.shared_user_id
	INNER JOIN Model m
	ON m.model_id = s.model_id
	INNER JOIN User owner
	ON owner.user_id = m.user_id;`

	rows, err = tx.Query(q2)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	sharedModels := []SharedModel{}

	for rows.Next() {
		s := SharedModel{}
		err := rows.Scan(&s.User, &s.Owner, &s.Model)
		if err != nil {
			return nil, err
		}
		sharedModels = append(sharedModels, s)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return &PredictionAuth{
		Users:  users,
		Shared: sharedModels,
	}, nil
}

// chooseWorkUnits chooses up to a specified number of work units from
// some work spec.
func (w *worker) chooseWorkUnits(tx *sql.Tx, spec *workSpec, numUnits int) ([]*workUnit, error) {
	query := buildSelect([]string{
		workUnitID,
		workUnitName,
	}, []string{
		workUnitTable,
	}, []string{
		inThisWorkSpec,
		workUnitAttempt + " IS NULL",
	})
	query += fmt.Sprintf(" ORDER BY priority DESC, name ASC")
	query += fmt.Sprintf(" LIMIT %v", numUnits)
	query += " FOR UPDATE OF work_unit"
	rows, err := tx.Query(query, spec.id)
	if err != nil {
		return nil, err
	}
	var result []*workUnit
	err = scanRows(rows, func() error {
		unit := workUnit{spec: spec}
		if err := rows.Scan(&unit.id, &unit.name); err == nil {
			result = append(result, &unit)
		}
		return err
	})
	if err != nil {
		return nil, err
	}
	return result, nil
}

func ModelSharedUsers(tx *sql.Tx, username, modelname string) ([]SharedUser, error) {
	// first get all users
	q1 := `SELECT 
	u.user_id, u.username 
	FROM User u
	INNER JOIN ModelSharedUser s
		ON u.user_id = shared_user_id
	INNER JOIN Model m
		ON m.model_id = s.model_id
	INNER JOIN User u2
		ON u2.user_id = m.user_id
	WHERE u2.username = ? AND m.modelname = ?;
	`
	users := []SharedUser{}

	rows, err := tx.Query(q1, username, modelname)
	if err != nil {
		return nil, fmt.Errorf("query failed: %v", err)
	}
	for rows.Next() {
		u := SharedUser{}
		err := rows.Scan(&u.Id, &u.Name)
		if err != nil {
			return nil, fmt.Errorf("GetSharedUsersByModel: error scanning rows: %v", err)
		}
		users = append(users, u)
	}
	return users, nil
}

func addLastAmongDup(tx *sql.Tx) error {
	// Upgrade from early version where lastAmongDup is missing.
	rows, err := tx.Query("select * from cmd limit 1")
	if err != nil {
		return err
	}
	defer rows.Close()

	hasLastAmongDup, err := hasColumn(rows, "lastAmongDup")
	if err != nil {
		return err
	}

	if !hasLastAmongDup {
		_, err := tx.Exec("alter table cmd add column lastAmongDup bool")
		if err != nil {
			return err
		}
		_, err = tx.Exec("update cmd set lastAmongDup = (rowid in (select max(rowid) from cmd group by content));")
		if err != nil {
			return err
		}
	}
	return nil
}

// postgres does not have REPLACE INTO (upsert), so we use that custom
// one for Set operations in batch instead
func altBatchSet(tx *sql.Tx, key, value string) error {
	r, err := tx.Query("SELECT replaceinto($1, $2)", key, value)
	if err != nil {
		return err
	}
	return r.Close()
}

// getLogEntries retrieves log entries in the range [from, to)
func getLogEntries(tx *sql.Tx, from, to int) ([]*LogEntry, error) {
	var out []*LogEntry
	rows, err := tx.Query(`SELECT id, term, client_id, client_serial, operation, key, value `+
		`FROM log WHERE id >= ? AND id < ? ORDER BY id ASC`, from, to)
	if err != nil {
		log.Printf("db error loading log entries [%d,%d): %v", from, to, err)
		return nil, err
	}

	for rows.Next() {
		l := new(LogEntry)
		out = append(out, l)
		err := rows.Scan(
			&l.ID,
			&l.Term,
			&l.ClientRequest.ClientID,
			&l.ClientRequest.ClientSerial,
			&l.ClientRequest.Operation,
			&l.ClientRequest.Key,
			&l.ClientRequest.Value)
		if err != nil {
			log.Printf("db error scanning log entry: %v", err)
			return nil, err
		}
	}
	if err := rows.Err(); err != nil {
		log.Printf("db error reading log entries: %v", err)
		return nil, err
	}
	return out, nil
}

func getLoadBatch(tx *sql.Tx, batchUuid string) (*LoadBatch, error) {
	var batch LoadBatch
	batch.UUID = batchUuid

	rows, err := tx.Query("SELECT keyname, tablename FROM "+pendingLoadTable+" WHERE batch_uuid = $1", batchUuid)
	if err != nil {
		return nil, err
	}

	defer rows.Close()
	for rows.Next() {
		var load Load
		err := rows.Scan(&load.KeyName, &load.TableName)
		if err != nil {
			log.Println("Scan threw an error!")
			return nil, err
		}

		batch.Loads = append(batch.Loads, load)
	}

	if len(batch.Loads) == 0 {
		return nil, noLoadsError
	}

	batch.TableName = batch.Loads[0].TableName

	return &batch, nil
}

func loadQuery(tx *sql.Tx, q string, args ...interface{}) (*table, error) {
	rows, err := tx.Query(q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	names, err := rows.Columns()
	if err != nil {
		return nil, err
	}
	t := &table{Header: names}
	for rows.Next() {
		ints := []*interface{}{}
		refs := []interface{}{}
		for i := 0; i < len(names); i++ {
			var i interface{}
			refs = append(refs, &i)
			ints = append(ints, &i)
		}
		err = rows.Scan(refs...)
		if err != nil {
			return nil, err
		}
		r := row{}
		for _, v := range ints {
			r = append(r, valueToString(*v))
		}
		t.Rows = append(t.Rows, r)
	}
	return t, rows.Err()
}

func (d *Postgres) getLogTags(tx *sql.Tx, log *models.Log) error {
	var (
		tag  string
		tags []string
	)

	query, args, dest := sqlbuilder.Select().
		Dialect(sqlbuilder.Postgres).
		From(`"log_tag"`).
		Map(`"tag"`, &tag).
		Where(`"log_id" = ?`, log.ID).
		Build()

	rows, err := tx.Query(query, args...)
	if err != nil {
		return err
	}
	for rows.Next() {
		if err := rows.Scan(dest...); err != nil {
			return err
		}
		tags = append(tags, tag)
	}
	if err := rows.Err(); err != nil {
		return err
	}
	log.Tags = tags
	return nil
}

//
// MIGRATION TWO: DOWN
//
func twoDown(tx *sql.Tx) error {
	_, err := tx.Exec("ALTER TABLE foobar ADD COLUMN s varchar(255)")
	if err != nil {
		return err
	}
	rows, err := tx.Query("SELECT i,t FROM foobar")
	if err != nil {
		return err
	}
	forLater := make(map[int]string)
	for rows.Next() {
		var i int
		var t string
		if err := rows.Scan(&i, &t); err != nil {
			return err
		}
		forLater[i] = t
	}
	if err := rows.Err(); err != nil {
		return err
	}
	for i, t := range forLater {
		///XXX Why does this not work with ? as placeholders for the values?
		_, err := tx.Exec(fmt.Sprintf("UPDATE foobar SET s = '%s' WHERE i = %d", t, i))
		if err != nil {
			return err
		}
	}
	_, err = tx.Exec("ALTER TABLE foobar DROP COLUMN t")
	if err != nil {
		return err
	}
	return nil
}

// FetchProfileID returns 0 if profile does not exist. Revision is used to
// ensure the profile exists on the same site as the revision
func FetchProfileID(tx *sql.Tx, profileName string, revisionID int64) int64 {
	var profileID int64
	rows, err := tx.Query(`
SELECT profile_id
  FROM profiles
 WHERE LOWER(profile_name) = $1
   AND site_id = (
           SELECT site_id
             FROM revisions r
                  LEFT JOIN profiles p ON r.profile_id = p.profile_id
            WHERE revision_id = $2
       )
 ORDER BY profile_id ASC
 LIMIT 1
OFFSET 0`,
		strings.ToLower(profileName),
		revisionID,
	)
	defer rows.Close()

	for rows.Next() {
		err = rows.Scan(&profileID)
		if err != nil {
			return 0
		}
	}
	err = rows.Err()
	if err != nil {
		return 0
	}
	rows.Close()

	return profileID
}

// GetAllRemotes returns all the ACIInfos sorted by optional sortfields and
// with ascending or descending order.
func GetAllRemotes(tx *sql.Tx) ([]*Remote, error) {
	var remotes []*Remote
	query := "SELECT * from remote"

	rows, err := tx.Query(query)
	if err != nil {
		return nil, err

	}

	for rows.Next() {
		r := &Remote{}
		if err := remoteRowScan(rows, r); err != nil {
			return nil, err

		}

		remotes = append(remotes, r)

	}

	if err := rows.Err(); err != nil {
		return nil, err

	}

	return remotes, nil

}

func findArticles(tx *sql.Tx, urls []string) ([]int, error) {
	out := []int{}
	sql := `SELECT DISTINCT article_id FROM article_url WHERE url IN (` + pgMarkerList(1, len(urls)) + `)`

	params := make([]interface{}, len(urls))
	for i := 0; i < len(urls); i++ {
		params[i] = urls[i]
	}

	rows, err := tx.Query(sql, params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var artID int
		if err := rows.Scan(&artID); err != nil {
			return nil, err
		}
		out = append(out, artID)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return out, nil
}

func FindJournoByName(tx *sql.Tx, name string) ([]*Journo, error) {
	// TODO: use journo_alias table to do lookup!
	// KLUDGE ALERT: we're using refs to look up journos. This sucks, but
	// we're stuck with it until we transition over to a properly-populated journo_alias table

	// check first 20 possible refs.
	r := baseRef(name)
	if r == "" {
		return []*Journo{}, nil
	}

	refs := []interface{}{r}
	for i := 1; i < 20; i++ {
		refs = append(refs, fmt.Sprintf("%s-%d", r, i))
	}

	sql := `SELECT id,ref,prettyname,lastname,firstname,created,status,oneliner,last_similar,modified,firstname_metaphone, lastname_metaphone, admin_notes, admin_tags,fake FROM journo WHERE ref IN (` + pgMarkerList(1, len(refs)) + `)`
	rows, err := tx.Query(sql, refs...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []*Journo{}
	for rows.Next() {
		var j Journo
		if err := rows.Scan(&j.ID, &j.Ref, &j.Prettyname, &j.Lastname, &j.Firstname, &j.Created, &j.Status, &j.Oneliner, &j.LastSimilar, &j.Modified, &j.FirstnameMetaphone, &j.LastnameMetaphone, &j.AdminNotes, &j.AdminTags, &j.Fake); err != nil {
			return nil, err
		}
		out = append(out, &j)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return out, nil
}

func processArticles(articleChannel *chan *[]Article, tx *sql.Tx, waitGroup *sync.WaitGroup) {
	waitGroup.Add(1)
	for articles := range *articleChannel {
		rows, err := tx.Query("select subscription_id, datetime(published), url from articles where published >= datetime(?) and subscription_id = ? order by datetime(published) asc", (*articles)[0].Published.Format(time.RFC3339), (*articles)[0].SubscriptionId)
		var existingArticles []Article
		if err != nil {
			log.Println("Article Download Error | Error retrieving existing articles " + err.Error())
		}
		for rows.Next() {
			var article Article
			var dateString string
			rows.Scan(&article.SubscriptionId, &dateString, &article.Url)
			article.Published, err = time.Parse("2006-01-02 15:04:05", dateString)
			existingArticles = append(existingArticles, Article{})
			copy(existingArticles[0+1:], existingArticles[0:])
			existingArticles[0] = article
		}
		rows.Close()
		if len(existingArticles) == 0 {
			insertFinalArticleSlice(articles, tx)
		} else if (existingArticles[0].Published != (*articles)[len(*articles)-1].Published) && (existingArticles[0].Url != (*articles)[len(*articles)-1].Url) {
			index := sort.Search(len(*articles), func(i int) bool {
				return (*articles)[i].Published.Unix() > existingArticles[0].Published.Unix()
			})
			newSlice := ((*articles)[index:])
			insertFinalArticleSlice(&newSlice, tx)
		}
	}
	waitGroup.Done()
}