Golang DB.QueryRow方法代码示例

func getSingleFact(db *sqlx.DB, fact string) (*factoid, error) {
	var f factoid
	var tmpCreated int64
	var tmpAccessed int64
	err := db.QueryRow(`select
			id,
			fact,
			tidbit,
			verb,
			owner,
			created,
			accessed,
			count
		from factoid
		where fact like ?
		order by random() limit 1;`,
		fact).Scan(
		&f.id,
		&f.Fact,
		&f.Tidbit,
		&f.Verb,
		&f.Owner,
		&tmpCreated,
		&tmpAccessed,
		&f.Count,
	)
	f.created = time.Unix(tmpCreated, 0)
	f.accessed = time.Unix(tmpAccessed, 0)
	return &f, err
}

func createSubmit(db *sqlx.DB, sub *scannedSubmit, submitNo int) *tickets.Ticket_Submit {
	var result tickets.Ticket_Submit
	result.SubmitNumber = proto.Uint32(uint32(submitNo))
	if sub.Arrived > 0 {
		result.Arrived = proto.Uint64(uint64(sub.Arrived))
	}
	if result.Compiled = proto.Bool(sub.Compiled == 1); !result.GetCompiled() {
		return &result
	}
	if sub.SchoolMode != 0 {
		result.School = &tickets.Ticket_Submit_School{TestsTaken: proto.Uint32(uint32(sub.Taken)), TestsPassed: proto.Uint32(uint32(sub.Passed))}
	} else {
		var description string
		var test int64
		err := db.QueryRow("select ResultDesc.Description, Results.Test from Results, ResultDesc where "+
			"Results.UID = ? and ResultDesc.ID = Results.Result and not ResultDesc.Success order by Results.Test",
			sub.TestingID).Scan(&description, &test)
		switch {
		case err == sql.ErrNoRows:
			if sub.Passed != 0 && sub.Passed == sub.Taken {
				result.Acm = &tickets.Ticket_Submit_ACM{Result: proto.String("ACCEPTED")}
			}
		case err != nil:
			log.Fatal(err)
			return nil
		default:
			result.Acm = &tickets.Ticket_Submit_ACM{Result: &description, TestId: proto.Uint32(uint32(test))}
		}
	}
	return &result
}

func Select(dbx *sqlx.DB, testuuid string) {
	uuid, _ := uuid.FromString(testuuid)
	var user UserTest
	row := dbx.QueryRow("SELECT id from test_table WHERE ID = ?", uuid.Bytes())

	err := row.Scan(&user.ID)

	if err != nil {
		fmt.Println(err)
		return
	}

	fmt.Printf("%#v", user.ID.String())
}

func InsertIntAuto(dbx *sqlx.DB) uint64 {

	_, _ = dbx.Exec("INSERT INTO test_table_int() values()")

	row := dbx.QueryRow("SELECT MAX(id) FROM test_table_int")

	var testuint uint64
	// var testuintX int64
	_ = row.Scan(&testuint)
	// testuintX, _ = res.LastInsertId()

	return testuint

}

func addPerson(db *sqlx.DB, p person) error {
	rs, err := db.Exec("INSERT INTO people (name) VALUES (?)", cryptoString(p.Name))

	if err != nil {
		return err
	}

	personID, err := rs.LastInsertId()

	if err != nil {
		return err
	}

	tokens := strings.Split(string(p.Name), " ")

	for _, token := range tokens {
		token = strings.TrimSpace(strings.ToLower(token))
		crypted := fmt.Sprintf("%x", sha256.Sum256([]byte(token)))
		row := db.QueryRow("SELECT h.id FROM hashes h WHERE h.hash = ?", crypted)

		if err != nil {
			return err
		}

		var hashID int64

		if err = row.Scan(&hashID); err != nil {
			rs, err = db.Exec("INSERT INTO hashes (hash) VALUES (?)", crypted)
			if err != nil {
				return err
			}

			hashID, err = rs.LastInsertId()

			if err != nil {
				return err
			}
		}

		_, err = db.Exec("INSERT INTO hashes_people (hash_id, person_id) VALUES (?, ?)", hashID, personID)

		if err != nil {
			return err
		}
	}

	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 *sqlx.DB) <-chan struct{} {
	result := make(chan struct{})

	go func() {
		var lastSeenLedger int32
		for {
			select {
			case <-time.After(1 * time.Second):
				var latestLedger int32
				row := db.QueryRow(MaxHistoryLedger)
				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)

					select {
					case result <- struct{}{}:
						lastSeenLedger = latestLedger
					default:
						log.Debug(ctx, "ledger pump channel is blocked.  waiting...")
					}
				} else if latestLedger < lastSeenLedger {
					log.Warn(ctx, "latest ledger went backwards! reseting ledger pump")
					lastSeenLedger = 0
				}

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

	return result
}

func getIdleEntryByNick(db *sqlx.DB, nick string) (idleEntry, error) {
	var id sql.NullInt64
	var lastSeen sql.NullInt64
	err := db.QueryRow(`select id, max(lastSeen) from downtime
		where nick = ?`, nick).Scan(&id, &lastSeen)
	if err != nil {
		log.Println("Error selecting downtime: ", err)
		return idleEntry{}, err
	}
	if !id.Valid {
		return idleEntry{
			nick:     nick,
			lastSeen: time.Now(),
		}, nil
	}
	return idleEntry{
		id:       id,
		nick:     nick,
		lastSeen: time.Unix(lastSeen.Int64, 0),
	}, nil
}

func getLastFirst(db *sqlx.DB) (*FirstEntry, error) {
	// Get last first entry
	var id sql.NullInt64
	var day sql.NullInt64
	var timeEntered sql.NullInt64
	var body sql.NullString
	var nick sql.NullString

	err := db.QueryRow(`select
		id, max(day), time, body, nick from first
		limit 1;
	`).Scan(
		&id,
		&day,
		&timeEntered,
		&body,
		&nick,
	)
	switch {
	case err == sql.ErrNoRows || !id.Valid:
		log.Println("No previous first entries")
		return nil, nil
	case err != nil:
		log.Println("Error on first query row: ", err)
		return nil, err
	}
	log.Println(id, day, timeEntered, body, nick)
	return &FirstEntry{
		id:    id.Int64,
		day:   time.Unix(day.Int64, 0),
		time:  time.Unix(timeEntered.Int64, 0),
		body:  body.String,
		nick:  nick.String,
		saved: true,
	}, nil
}

func InsertUser(db *sqlx.DB, user *User) error {
	err := db.QueryRow("INSERT INTO users(email, name, password, timestamp) VALUES(lower($1),$2,$3,$4) RETURNING id", user.Email, user.Name, user.Password, time.Now()).Scan(&user.Id)
	return err
}