Golang Tx.QueryRow方法代碼示例

// Create a new handler dumping data chunks and index info to db for an
// object/file identified by label.
func New(tx *sql.Tx, label string) (h *Handler, err error) {
	// get next file/object id
	var maxfid sql.NullInt64
	row := tx.QueryRow(getMaxFidSql)
	if err := row.Scan(&maxfid); err != nil {
		return nil, err
	}

	// get next chunk rowid
	var maxrow sql.NullInt64
	row = tx.QueryRow(getMaxChunkRowSql)
	if err := row.Scan(&maxrow); err != nil {
		return nil, err
	}

	// config and return handler
	h = &Handler{}
	h.tx = tx
	h.nextChunkRow = int(maxrow.Int64) + 1
	h.label = label
	h.fid = int(maxfid.Int64) + 1
	h.fullH = sha1.New()
	h.chunkH = sha1.New()
	return h, nil
}

func makeAttempt(tx *sql.Tx, unit *workUnit, w *worker, length time.Duration) (*attempt, error) {
	a := attempt{unit: unit, worker: w}

	now := a.Coordinate().clock.Now()
	expiration := now.Add(length)
	params := queryParams{}
	fields := fieldList{}
	fields.Add(&params, "work_unit_id", unit.id)
	fields.Add(&params, "work_spec_id", unit.spec.id)
	fields.Add(&params, "worker_id", w.id)
	fields.Add(&params, "start_time", now)
	fields.Add(&params, "expiration_time", expiration)
	query := fields.InsertStatement(attemptTable) + " RETURNING id"
	row := tx.QueryRow(query, params...)
	err := row.Scan(&a.id)
	if err != nil {
		return nil, err
	}

	params = queryParams{}
	fields = fieldList{}
	fields.Add(&params, "active_attempt_id", a.id)
	query = buildUpdate(workUnitTable, fields.UpdateChanges(), []string{
		isWorkUnit(&params, unit.id),
	})
	_, err = tx.Exec(query, params...)

	return &a, err
}

func (aca *ACA) populateAttribute(tx *sql.Tx, attr *AttributePair) error {
	fmt.Printf("*********************** ATTR %v %v %v\n", attr.GetID(), attr.attributeName, string(attr.attributeValue))

	var count int
	err := tx.QueryRow("SELECT count(row) AS cant FROM Attributes WHERE id=? AND affiliation =? AND attributeName =?",
		attr.GetID(), attr.GetAffiliation(), attr.GetAttributeName()).Scan(&count)

	if err != nil {
		return err
	}

	if count > 0 {
		_, err = tx.Exec("UPDATE Attributes SET validFrom = ?, validTo = ?,  attributeValue = ? WHERE  id=? AND affiliation =? AND attributeName =? AND validFrom < ?",
			attr.GetValidFrom(), attr.GetValidTo(), attr.GetAttributeValue(), attr.GetID(), attr.GetAffiliation(), attr.GetAttributeName(), attr.GetValidFrom())
		if err != nil {
			return err
		}
	} else {
		_, err = tx.Exec("INSERT INTO Attributes (validFrom , validTo,  attributeValue, id, affiliation, attributeName) VALUES (?,?,?,?,?,?)",
			attr.GetValidFrom(), attr.GetValidTo(), attr.GetAttributeValue(), attr.GetID(), attr.GetAffiliation(), attr.GetAttributeName())
		if err != nil {
			return err
		}
	}
	return nil
}

func (d *Postgres) addLogTrack(tx *sql.Tx, log *models.Log, track *models.Track) error {
	var name *string
	if track.Name != "" {
		name = &track.Name
	}
	query, args, dest := sqlbuilder.Insert().
		Dialect(sqlbuilder.Postgres).
		Into(`"track"`).
		Set(`"log_id"`, log.ID).
		Set(`"name"`, name).
		Set(`"start"`, track.Start).
		Set(`"end"`, track.End).
		Set(`"duration"`, track.Duration).
		Set(`"distance"`, track.Distance).
		Return(`"id"`, &track.ID).
		Build()

	if err := tx.QueryRow(query, args...).Scan(dest...); err != nil {
		return err
	}

	for _, point := range track.Points {
		if err := d.addTrackPoint(tx, track, point); err != nil {
			return err
		}
	}

	return nil
}

// Attach a prefix to the prefix tree, reparenting other prefixes if needed.
func (s *server) attachPrefix(tx *sql.Tx, realmID, prefixID int64, prefix string) error {
	var parentID *int64
	q := `SELECT prefix_id FROM prefixes WHERE realm_id=$1 AND prefixIsInside($2, prefix) ORDER BY prefixLen(prefix) DESC LIMIT 1`
	if err := tx.QueryRow(q, realmID, prefix).Scan(&parentID); err != nil && err != sql.ErrNoRows {
		return err
	}

	if parentID == nil {
		q = `UPDATE prefixes SET parent_id=NULL WHERE realm_id=$1 AND prefix_id=$2`
		if _, err := tx.Exec(q, realmID, prefixID); err != nil {
			return err
		}

		q = `UPDATE prefixes SET parent_id=$1 WHERE realm_id=$2 AND parent_id IS NULL AND prefixIsInside(prefix, $3)`
		if _, err := tx.Exec(q, prefixID, realmID, prefix); err != nil {
			return err
		}
	} else {
		q = `UPDATE prefixes SET parent_id=$1 WHERE realm_id=$2 AND prefix_id=$3`
		if _, err := tx.Exec(q, *parentID, realmID, prefixID); err != nil {
			return err
		}

		q = `UPDATE prefixes SET parent_id=$1 WHERE realm_id=$2 AND parent_id=$3 AND prefixIsInside(prefix, $4)`
		if _, err := tx.Exec(q, prefixID, realmID, *parentID, prefix); err != nil {
			return err
		}
	}
	return nil
}

// returns sql.ErrNoRows if not found
func FindPublication(tx *sql.Tx, domain string) (int, error) {
	// TODO (maybe) match both www. and non www. versions?
	//domain = strings.ToLower(domain)
	var pubID int
	err := tx.QueryRow(`SELECT pub_id FROM pub_domain WHERE domain=$1`, domain).Scan(&pubID)
	return pubID, err
}

func recipeContainsAlcohol(tx *sql.Tx, recipe_id string) bool {

	sql := `
        select 
          count(*)
        from recipe r
        inner join recipe_ingredient ri on ri.recipe_id = r.id
        inner join ingredient i on i.id = ri.ingredient_id
        where r.id = ?
          and alcoholic = 1`

	var alcoholic int
	row := tx.QueryRow(sql, recipe_id)

	err := row.Scan(&alcoholic)
	if err != nil {
		panic(fmt.Sprintf("recipeContainsAlcohol failed: %v", err))
		return true
	}

	if alcoholic > 0 {
		return true
	} else {
		return false
	}

}

// AddParent adds a parent. Uses passed transaction or a new one if one is not provided
func AddParent(familyID int, name string, email string, password string, tx *sql.Tx) (int, error) {
	hashedPassword, err := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
	needCommit := false
	if tx == nil {
		tx, err = db.Begin()
		if err != nil {
			return 0, err
		}
		needCommit = true
		defer tx.Rollback()
	}

	// Create the parent
	if _, err := tx.Exec("INSERT INTO parents VALUES(NULL, ?, ?, ?, ?, NULL, NULL)", familyID, name, email, hashedPassword); err != nil {
		return 0, err
	}
	// Get the ID
	var ID int
	row := tx.QueryRow("SELECT LAST_INSERT_ID()")
	err = row.Scan(&ID)
	if needCommit {
		tx.Commit()
	}

	return ID, err
}

func insertText(t *testing.T, conn *sql.Tx, spanish, chinese, russian string) bool {
	qry := "INSERT INTO " + tbl + " (F_int, F_text_spanish, F_text_chinese, F_text_russian)" +
		" VALUES (-1, :1, :2, :3)"
	if _, err := conn.Exec(qry, spanish, chinese, russian); err != nil {
		t.Errorf("cannot insert into "+tbl+" (%q): %v", qry, err)
	}
	row := conn.QueryRow("SELECT F_text_spanish, F_text_chinese, F_text_russian FROM " + tbl + " WHERE F_int = -1")
	var tSpanish, tChinese, tRussian string
	if err := row.Scan(&tSpanish, &tChinese, &tRussian); err != nil {
		t.Errorf("error scanning row: %v", errgo.Details(err))
		return false
	}
	t.Logf("spanish=%q chinese=%q russian=%q", spanish, chinese, russian)
	ok := true
	if tSpanish != spanish {
		t.Errorf("spanish mismatch: got %q, awaited %q", tSpanish, spanish)
		ok = false
	}
	if tChinese != chinese {
		t.Errorf("chinese mismatch: got %q, awaited %q", tChinese, chinese)
		ok = false
	}
	if tRussian != russian {
		t.Errorf("russian mismatch: got %q, awaited %q", tRussian, russian)
		ok = false
	}
	return ok
}

func createJourno(tx *sql.Tx, journo *arts.Author) (int, error) {
	ref, err := uniqRef(tx, baseRef(journo.Name))
	if err != nil {
		return 0, err
	}

	prettyName := journo.Name
	firstName, lastName := splitName(journo.Name)
	firstNameMetaphone := phonetics.EncodeMetaphone(firstName)
	lastNameMetaphone := phonetics.EncodeMetaphone(lastName)

	var journoID int
	err = tx.QueryRow(`INSERT INTO journo (id,ref,prettyname,firstname,lastname,firstname_metaphone,lastname_metaphone,created) VALUES (DEFAULT,$1,$2,$3,$4,$5,$6,NOW()) RETURNING id`,
		ref,
		prettyName,
		firstName,
		lastName,
		firstNameMetaphone,
		lastNameMetaphone).Scan(&journoID)
	if err != nil {
		return 0, err
	}

	// TODO: future: fill out journo_alias table, and also rel-author links etc to help resolution...

	return journoID, nil
}

func addGeometryColumn(tx *sql.Tx, tableName string, spec TableSpec) error {
	colName := ""
	for _, col := range spec.Columns {
		if col.Type.Name() == "GEOMETRY" {
			colName = col.Name
			break
		}
	}

	if colName == "" {
		return nil
	}

	geomType := strings.ToUpper(spec.GeometryType)
	if geomType == "POLYGON" {
		geomType = "GEOMETRY" // for multipolygon support
	}
	sql := fmt.Sprintf("SELECT AddGeometryColumn('%s', '%s', '%s', '%d', '%s', 2);",
		spec.Schema, tableName, colName, spec.Srid, geomType)
	row := tx.QueryRow(sql)
	var void interface{}
	err := row.Scan(&void)
	if err != nil {
		return &SQLError{sql, err}
	}
	return nil
}

func createPublication(tx *sql.Tx, pub *arts.Publication) (int, error) {
	prettyName := pub.Name
	if prettyName == "" {
		prettyName = strippedDomain(pub.Domain)
	}
	shortName := genShortName(prettyName)

	// strip leading "the"s for more natural sort order
	sortName := strings.ToLower(prettyName)
	sortName = stripThePat.ReplaceAllLiteralString(prettyName, "")

	homeURL := "http://" + pub.Domain

	var pubID int
	err := tx.QueryRow(`INSERT INTO organisation (id,shortname,prettyname,sortname,home_url) VALUES (DEFAULT, $1,$2,$3,$4) RETURNING id`, shortName, prettyName, sortName, homeURL).Scan(&pubID)
	if err != nil {
		return 0, err
	}

	_, err = tx.Exec(`INSERT INTO pub_domain (pub_id,domain) VALUES ($1, $2)`, pubID, pub.Domain)
	if err != nil {
		return 0, err
	}

	_, err = tx.Exec(`INSERT INTO pub_alias (pub_id,alias) VALUES ($1, $2)`, pubID, prettyName)
	if err != nil {
		return 0, err
	}

	return pubID, nil
}

func UpdateSchema(txn *sql.Tx) error {
	var (
		err   error
		count int64
	)

	err = txn.QueryRow(
		`SELECT COUNT(table_name) FROM information_schema.tables WHERE table_schema = $1 AND table_name = $2;`,
		"public", "cas_objects",
	).Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		_, err = txn.Exec(
			`
      CREATE TABLE cas_objects (
        address  BYTEA NOT NULL,
        content  BYTEA,
        external VARCHAR,

        PRIMARY KEY (address),
        CHECK (octet_length(address) = 20),
        CHECK (content IS NOT NULL OR external IS NOT NULL)
      );
      `,
		)
		if err != nil {
			return err
		}
	}

	return nil
}

func insertTaskFunctions(tx *sql.Tx) error {
	var minTaskFunction, minPriority int
	row := tx.QueryRow("SELECT task_function, priority FROM scheduled_functions ORDER BY scheduled_time DESC, priority DESC, task_function DESC LIMIT 1")
	if err := row.Scan(&minTaskFunction, &minPriority); err == sql.ErrNoRows {
		log.Printf("no prior functions scheduled")
		noPriorFunctionsScheduledCounter.Inc(1)
	} else if err != nil {
		log.Printf("error finding max last priority: %v", err)
		lastMaxPriorityErrorCounter.Inc(1)
		return err
	}

	log.Printf("min task function: %v, min priority: %v", minTaskFunction, minPriority)

	if _, err := tx.Exec("DELETE FROM scheduled_functions WHERE expiration_time < now() OR measurements_remaining <= 0"); err != nil {
		log.Printf("error deleting expired task functions: %v", err)
		deleteExpiredFunctionsErrorCounter.Inc(1)
		return err
	}

	var toSchedule int
	row = tx.QueryRow("SELECT concurrent_functions - scheduled FROM (SELECT count(1) scheduled FROM scheduled_functions) AS c, scheduler_configuration")
	if err := row.Scan(&toSchedule); err != nil {
		log.Printf("error counting scheduled tasks: %v", err)
		countSchedluedTasksErrorCounter.Inc(1)
		return err
	}

	result, err := tx.Exec("INSERT INTO scheduled_functions (task_function, expiration_time, measurements_remaining, priority, scheduled_time) SELECT id, now() + max_duration_seconds * interval '1 second', max_measurements, priority, now() FROM task_functions WHERE enabled AND ((priority = $1 AND id > $2) OR priority > $1) ORDER BY priority, id LIMIT $3", minPriority, minTaskFunction, toSchedule)
	if err != nil {
		log.Printf("error inserting new schedules: %v", err)
		insertScheduledFunctionsErrorCounter.Inc(1)
		return err
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		log.Printf("error discovering number of affected rows: %v", err)
		countScheduledFunctionsErrorCounter.Inc(1)
		return err
	}
	toSchedule -= int(rowsAffected)
	result, err = tx.Exec("INSERT INTO scheduled_functions (task_function, expiration_time, measurements_remaining, priority, scheduled_time) SELECT id, now() + max_duration_seconds * interval '1 second', max_measurements, priority, now() FROM task_functions WHERE enabled ORDER BY priority, id LIMIT $1", toSchedule)
	if err != nil {
		log.Printf("error inserting new schedules: %v", err)
		insertScheduledFunctionsErrorCounter.Inc(1)
		return err
	}
	rowsAffected, err = result.RowsAffected()
	if err != nil {
		log.Printf("error discovering number of affected rows: %v", err)
		countScheduledFunctionsErrorCounter.Inc(1)
		return err
	}
	toSchedule -= int(rowsAffected)
	if toSchedule > 0 {
		log.Printf("unable to fill schedule")
		unfilledScheduleCounter.Inc(1)
	}
	return nil
}

// TODO: is this used?
func CreatePublication(tx *sql.Tx, domain, name string) (*Publication, error) {
	pub := NewPublication(domain, name)

	err := tx.QueryRow(`INSERT INTO organisation (id,shortname,prettyname,sortname,home_url) VALUES (DEFAULT, $1,$2,$3,$4) RETURNING id`,
		pub.ShortName,
		pub.PrettyName,
		pub.SortName,
		pub.HomeURL).Scan(&pub.ID)
	if err != nil {
		return nil, err
	}

	for _, domain := range pub.Domains {
		_, err = tx.Exec(`INSERT INTO pub_domain (pub_id,domain) VALUES ($1, $2)`, pub.ID, domain)
		if err != nil {
			return nil, err
		}
	}

	_, err = tx.Exec(`INSERT INTO pub_alias (pub_id,alias) VALUES ($1, $2)`, pub.ID, pub.PrettyName)
	if err != nil {
		return nil, err
	}

	return pub, nil
}