Golang DB.QueryRow方法代碼示例

func ReadEpisodeResource(db *sql.DB, id int64) (EpisodeResource, error) {
	err := db.Ping()
	if err != nil {
		return EpisodeResource{}, err
	}

	m := "SELECT Series_ID, Name, URL FROM %v WHERE ID = ?"
	q := fmt.Sprintf(m, EpisodesResourceTable)
	var seriesID int64
	var name string
	var url string
	err = db.QueryRow(q, id).Scan(&seriesID, &name, &url)
	if err != nil {
		return EpisodeResource{}, err
	}

	r := EpisodeResource{
		ID:       id,
		SeriesID: seriesID,
		Name:     name,
		URL:      url,
	}

	return r, nil

}

func QuerySoundAlikeProduct(db *sql.DB, accountId int64, inProduct *Product) (product *Product) {
	s := []string{}

	s = append(s, "SELECT")
	s = append(s, "account_id, pid, name, product_url, image_url, unit_cost,")
	s = append(s, "unit_price, margin, margin_rate")
	s = append(s, "FROM product")
	s = append(s, "WHERE account_id = $1")
	s = append(s, "AND difference(name, $2) >= 3")
	s = append(s, "LIMIT 1")

	query := strings.Join(s, " ")

	row := db.QueryRow(query, accountId, inProduct.Name)
	product = &Product{}
	err := row.Scan(&product.AccountId, &product.Pid, &product.Name, &product.ProductUrl, &product.ImageUrl,
		&product.UnitCost, &product.UnitPrice, &product.Margin, &product.MarginRate)
	if err != nil {
		if err == sql.ErrNoRows {
			product = nil
		} else {
			panic(err)
		}
	}

	return
}

// as per http://www.mssqltips.com/sqlservertip/2563/understanding-the-sql-server-select-version-command/
func serverVersion(db *sql.DB) (sqlVersion, sqlPartNumber, osVersion string, err error) {
	var v string
	if err = db.QueryRow("select @@version").Scan(&v); err != nil {
		return "", "", "", err
	}
	a := strings.SplitN(v, "\n", -1)
	if len(a) < 4 {
		return "", "", "", errors.New("SQL Server version string must have at least 4 lines: " + v)
	}
	for i := range a {
		a[i] = strings.Trim(a[i], " \t")
	}
	l1 := strings.SplitN(a[0], "-", -1)
	if len(l1) != 2 {
		return "", "", "", errors.New("SQL Server version first line must have - in it: " + v)
	}
	i := strings.Index(a[3], " on ")
	if i < 0 {
		return "", "", "", errors.New("SQL Server version fourth line must have 'on' in it: " + v)
	}
	sqlVersion = l1[0] + a[3][:i]
	osVersion = a[3][i+4:]
	sqlPartNumber = strings.Trim(l1[1], " ")
	l12 := strings.SplitN(sqlPartNumber, " ", -1)
	if len(l12) < 2 {
		return "", "", "", errors.New("SQL Server version first line must have space after part number in it: " + v)
	}
	sqlPartNumber = l12[0]
	return sqlVersion, sqlPartNumber, osVersion, nil
}

// UpdateSchedule updates a schedule in the database
func UpdateSchedule(dbConn *sql.DB, s TaskSchedule) error {

	//logit.Info.Println("backup.UpdateSchedule called")

	queryStr := fmt.Sprintf("update taskschedule set ( enabled,  name, minutes, hours, dayofmonth, month, dayofweek, restoreset, restoreremotepath, restoreremotehost, restoreremoteuser, restoredbuser, restoredbpass, updatedt) = ('%s', '%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s','%s', '%s', '%s', now()) where id = %s  returning containername",
		s.Enabled,
		s.Name,
		s.Minutes,
		s.Hours,
		s.DayOfMonth,
		s.Month,
		s.DayOfWeek,
		s.RestoreSet,
		s.RestoreRemotePath,
		s.RestoreRemoteHost,
		s.RestoreRemoteUser,
		s.RestoreDbUser,
		s.RestoreDbPass,
		s.ID)

	logit.Info.Println("backup:UpdateSchedule:[" + queryStr + "]")
	var name string
	err := dbConn.QueryRow(queryStr).Scan(&name)
	switch {
	case err != nil:
		logit.Error.Println(err.Error())
		return err
	default:
	}

	return nil
}

// NewLedgerClosePump starts a background proc that continually watches the
// history database provided.  The watch is stopped after the provided context
// is cancelled.
//
// Every second, the proc spawned by calling this func will check to see
// if a new ledger has been imported (by ruby-horizon as of 2015-04-30, but
// should eventually end up being in this project).  If a new ledger is seen
// the the channel returned by this function emits
func NewLedgerClosePump(ctx context.Context, db *sql.DB) <-chan time.Time {
	result := make(chan time.Time)

	go func() {
		var lastSeenLedger int32
		for {
			select {
			case <-time.After(1 * time.Second):
				var latestLedger int32
				row := db.QueryRow("SELECT MAX(sequence) FROM history_ledgers")
				err := row.Scan(&latestLedger)

				if err != nil {
					log.Warn(ctx, "Failed to check latest ledger", err)
					break
				}

				if latestLedger > lastSeenLedger {
					log.Debugf(ctx, "saw new ledger: %d, prev: %d", latestLedger, lastSeenLedger)
					lastSeenLedger = latestLedger
					result <- time.Now()
				}

			case <-ctx.Done():
				log.Info(ctx, "canceling ledger pump")
				return
			}
		}
	}()

	return result
}

// lit un compte en base. Renvoie nil si le compte n'existe pas en base.
func (store *MysqlStore) GetCompte(db *sql.DB, trollId int) (*Compte, error) {
	sql := "select statut, mdp_restreint, pv_max, pv_actuels, x, y, z, fatigue, pa, vue, prochain_tour, duree_tour, mise_a_jour"
	sql += " from compte where id=" + strconv.FormatUint(uint64(trollId), 10)
	row := db.QueryRow(sql)
	c, err := rowToCompte(trollId, row)
	return c, err
}

func waitReadWrite(c *C, db *sql.DB) {
	// Check if the master has transitioned the database into read/write
	var readOnly string
	err := queryAttempts.Run(func() error {
		if err := db.QueryRow("SELECT @@read_only").Scan(&readOnly); err != nil {
			return err
		}
		if readOnly == "0" {
			return nil
		}
		return fmt.Errorf("database is read_only")
	})
	c.Assert(err, IsNil)

	// Even if the database is read/write a slave must be connected
	// for writes to be allowed.
	err = queryAttempts.Run(func() error {
		var discard interface{}
		var masterClients int
		err = db.QueryRow("SHOW STATUS LIKE 'rpl_semi_sync_master_clients'").Scan(&discard, &masterClients)
		if err != nil {
			return err
		}
		if masterClients > 0 {
			return nil
		}
		return fmt.Errorf("no connected slave")
	})
	c.Assert(err, IsNil)
}

func UserLogin(r *http.Request, db *sql.DB, s sessions.Session, rw http.ResponseWriter) (int, string) {
	var id string
	var pass string

	email, password := r.FormValue("email"), r.FormValue("password")
	err := db.QueryRow("select id, password from appuser where email=$1", email).Scan(&id, &pass)

	if err != nil || bcrypt.CompareHashAndPassword([]byte(pass), []byte(password)) != nil {
		//return 401, "Not Authorized. Buuuurn!"
		http.Redirect(rw, r, "/wrong", http.StatusFound)
	}

	//set the user id in the session
	s.Set("userId", id)

	//return user
	if returnUrl, ok := s.Get("returnUrl").(string); ok {
		s.Delete("returnUrl")
		http.Redirect(rw, r, returnUrl, http.StatusFound)
	} else {
		http.Redirect(rw, r, "/", http.StatusFound)
	}

	return 200, "User id is " + id
}

func (users *UserRepository) Create(db *sql.DB, user User) (int, error) {
	fields := strings.Join(user.Fillable(), ", ")
	now := time.Now().Format("2006-01-02 15:04:05")

	query := fmt.Sprintf(
		"INSERT INTO users("+fields+") VALUES('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s') RETURNING id;",
		user.Username,
		user.FirstName,
		user.LastName,
		user.Email,
		user.PasswordHashed,
		user.PasswordSalt,
		user.Bio,
		StatusPending,
		now,
		now,
	)

	var lastId int
	err := db.QueryRow(query).Scan(&lastId)
	if err != nil {
		return 0, err
	}
	return int(lastId), err

}

func insertCourse(course Course, subid string, index int, db *sql.DB) string {
	var cid sql.NullString

	b, err := json.Marshal(course)
	if err != nil {
		fmt.Println(err)
	}
	json := string(b[:])

	row := database.QueryRow(`UPDATE course SET subid = $1, name = $2, number = $3, description = $4, data_json = $5 FROM subject WHERE
	subject.subid = course.subid AND
	subject.subid = $6 AND course.index = $7 RETURNING cid`,
		Atoi64(subid),
		ToNullString(course.CourseName),
		ToNullString(course.CourseNum),
		ToNullString(course.CourseDescription),
		ToNullString(json),
		Atoi64(subid),
		ToNullInt64(int64(index)))
	row.Scan(&cid)
	fmt.Printf("Rows affected on update course %s\n", cid.String)
	if cid.String == "" {
		row := db.QueryRow(`INSERT INTO course (subid, name, number, description, index, data_json) VALUES($1,$2,$3,$4,$5,$6) RETURNING cid`,
			Atoi64(subid),
			ToNullString(course.CourseName),
			ToNullString(course.CourseNum),
			ToNullString(course.CourseDescription),
			ToNullInt64(int64(index)),
			ToNullString(json))
		err := row.Scan(&cid)
		checkError(err)
		fmt.Printf("Rows affected on insert course %s\n", cid.String)
	}
	return cid.String
}

// checkAllocatorStable returns whether the replica distribution within the cluster has
// been stable for at least `StableInterval`.
func (at *allocatorTest) checkAllocatorStable(db *gosql.DB) (bool, error) {
	q := `SELECT NOW() - MAX(timestamp) FROM rangelog WHERE eventType IN ($1, $2, $3)`
	eventTypes := []interface{}{
		string(storage.RangeEventLogSplit),
		string(storage.RangeEventLogAdd),
		string(storage.RangeEventLogRemove),
	}
	var elapsedStr string
	if err := db.QueryRow(q, eventTypes...).Scan(&elapsedStr); err != nil {
		// Log but don't return errors, to increase resilience against transient
		// errors.
		log.Errorf("error checking rebalancer: %s", err)
		return false, nil
	}
	elapsedSinceLastRangeEvent, err := time.ParseDuration(elapsedStr)
	if err != nil {
		return false, err
	}

	var status string
	stable := elapsedSinceLastRangeEvent >= StableInterval
	if stable {
		status = fmt.Sprintf("allocator is stable (idle for %s)", StableInterval)
	} else {
		status = "waiting for idle allocator"
	}
	log.Infof("last range event was %s ago: %s", elapsedSinceLastRangeEvent, status)
	return stable, nil
}

func insertMeeting(meeting MeetingTime, sid string, index int, db *sql.DB) string {
	var mid sql.NullString

	row := db.QueryRow(`UPDATE meeting SET sid = $1, start_time = $2, end_time = $3, day = $4, room = $5 FROM section
	WHERE section.sid = meeting.sid AND section.sid = $6 AND meeting.index = $7 RETURNING mid`,
		Atoi64(sid),
		ToNullString(meeting.StartTime),
		ToNullString(meeting.EndTime),
		ToNullString(meeting.Day),
		ToNullString(meeting.Room),
		Atoi64(sid),
		ToNullInt64(int64(index)))
	row.Scan(&mid)
	fmt.Printf("Rows affected on update meeting  %s\n", mid.String)

	if mid.String == "" {
		row := db.QueryRow(`INSERT INTO meeting (sid, start_time, end_time, day, room, index) VALUES($1,$2,$3,$4,$5,$6) RETURNING mid`,
			Atoi64(sid),
			ToNullString(meeting.StartTime),
			ToNullString(meeting.EndTime),
			ToNullString(meeting.Day),
			ToNullString(meeting.Room),
			ToNullInt64(int64(index)))
		err := row.Scan(&mid)
		checkError(err)
		fmt.Printf("Rows affected on insert meeting %s\n", mid.String)
	}
	return mid.String
}

func FindUserByName(db *sql.DB, name string) (User, error) {
	err := db.Ping()
	if err != nil {
		return User{}, err
	}

	m := "SELECT ID,Name,Password FROM %v WHERE Name = ?"
	q := fmt.Sprintf(m, UserTable)

	var id int64
	var nameTmp string
	var pass string

	err = db.QueryRow(q, name).Scan(&id, &nameTmp, &pass)
	if err != nil {
		return User{}, err
	}

	user := User{
		ID:       id,
		Name:     nameTmp,
		Password: pass,
	}

	return user, nil
}

func ReadUser(db *sql.DB, id int64) (User, error) {
	err := db.Ping()
	if err != nil {
		return User{}, err
	}

	m := "SELECT ID,Name,Password FROM %v WHERE ID = ?"
	q := fmt.Sprintf(m, UserTable)

	var idTmp int64
	var name string
	var pass string

	err = db.QueryRow(q, id).Scan(&idTmp, &name, &pass)
	if err != nil {
		return User{}, err
	}

	user := User{
		ID:       idTmp,
		Name:     name,
		Password: pass,
	}

	return user, nil
}

func LoadUnitContact(unitId string, db *sql.DB) (AcademicUnit, error) {
	var result AcademicUnit

	if len(unitId) == 0 {
		return result, errors.New("unitId is required")
	}

	var query = `
select address,
       phone_number,
       email_address,
       web_address
  from ACADUNIT.academic_unit_contact_info
 where academic_unit_id = :unitId
`

	err := db.QueryRow(query, unitId).
		Scan(&result.Address, &result.PhoneNumber, &result.Email, &result.Website)
	if err != nil {
		if err == sql.ErrNoRows {
			return AcademicUnit{}, nil
		}
		log.Print("Error in LoadAcademicUnits: ", err)
		return result, err
	}

	return result, nil
}