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
}
func findTeamByName(db *sql.DB, teamName string) (dash.Team, error) {
var team = dash.Team{
Name: teamName,
}
var err = db.QueryRow(`SELECT t.id, t.access_key, tm.user_id FROM teams AS t INNER JOIN team_user AS tm ON tm.team_id = t.id WHERE name = ? AND tm.role = ? LIMIT 1`, teamName, "owner").Scan(&team.ID, &team.EncryptedAccessKey, &team.OwnerID)
return team, err
}
func (SQLiteHelper) databaseName(db *sql.DB) (dbName string) {
var seq int
var main string
db.QueryRow("PRAGMA database_list").Scan(&seq, &main, &dbName)
dbName = filepath.Base(dbName)
return
}
func loadModel(db *sql.DB) (s *Settings, err error) {
s = new(Settings)
var maxNGramStr string
rows := db.QueryRow(`select value from parameters where key = "maxngram"`)
err = rows.Scan(&maxNGramStr)
if err == sql.ErrNoRows {
log.Printf("no maxngram setting in database, using default=%d",
DefaultMaxNGram)
s.MaxNGram = DefaultMaxNGram
} else if maxNGramStr == "" {
// go-sqlite3 seems to return this if the parameter is not set...
s.MaxNGram = DefaultMaxNGram
} else {
var max64 int64
max64, err = strconv.ParseInt(maxNGramStr, 10, 0)
if max64 <= 0 {
err = fmt.Errorf("invalid value maxngram=%d, must be >0", max64)
} else {
s.MaxNGram = uint(max64)
}
}
rows = db.QueryRow(`select value from parameters where key = "dumpname"`)
if err = rows.Scan(&s.Dumpname); err != nil && err != sql.ErrNoRows {
s = nil
}
return
}
func waitRow(c *C, db *sql.DB, n int) {
var res int64
err := queryAttempts.Run(func() error {
return db.QueryRow(fmt.Sprintf("SELECT id FROM test WHERE id = %d", n)).Scan(&res)
})
c.Assert(err, IsNil)
}
// Load count-min sketch from table ngramfreq.
func LoadCM(db *sql.DB) (sketch *countmin.Sketch, err error) {
var nrows, ncols int
shapequery := "select max(row) + 1, max(col) + 1 from ngramfreq"
err = db.QueryRow(shapequery).Scan(&nrows, &ncols)
if err != nil {
return
}
cmrows := make([][]uint32, nrows)
for i := 0; i < nrows; i++ {
cmrows[i] = make([]uint32, ncols)
}
dbrows, err := db.Query("select row, col, count from ngramfreq")
if err != nil {
return
}
for dbrows.Next() {
var i, j, count uint32
if err = dbrows.Scan(&i, &j, &count); err != nil {
return
}
cmrows[i][j] = count
}
sketch, err = countmin.NewFromCounts(cmrows)
return
}
/*
Insert user default
insert a user with default data
*/
func (Lst_users *All_users) AddNewDefaultUser(Db *sql.DB, req *protocol.Request) *list.Element {
var err error
t := bytes.NewBufferString("1")
bpass := t.Bytes()
tmpUser := new(User)
tmpUser.Log = time.Now()
tmpUser.Followed = list.New()
tmpUser.Possessed = list.New()
tmpUser.HistoricReq = list.New()
tmpUser.Coord.Lon = req.Coord.Lon
tmpUser.Coord.Lat = req.Coord.Lat
tmpUser.Stats = new(StatsUser)
tmpUser.Stats.CreationDate = time.Now()
tmpUser.Stats.NbrBallCreate = 0
tmpUser.Stats.NbrCatch = 0
tmpUser.Stats.NbrSend = 0
tmpUser.Stats.NbrFollow = 0
tmpUser.Stats.NbrMessage = 0
err = Db.QueryRow("SELECT setsdefaultuserdata($1, $2, $3, $4, $5);", tmpUser.Coord.Lat, tmpUser.Coord.Lon, tmpUser.Log, tmpUser.Stats.CreationDate, bpass).Scan(&tmpUser.Id)
if err != nil {
Lst_users.Logger.Println(err)
return nil
}
Lst_users.Ulist.PushBack(tmpUser)
return Lst_users.Ulist.Back()
}
func read(db *sql.DB) {
fmt.Println("Read")
// multi row read
rows, err := db.Query("SELECT id, name, town, date FROM test order by id asc")
if err != nil {
log.Printf("Error selecting rows: %v", err)
}
for rows.Next() {
var name, town, date string
var id int
if err := rows.Scan(&id, &name, &town, &date); err != nil {
log.Println(err)
}
fmt.Println(id, name, town, date)
}
if err := rows.Err(); err != nil {
log.Println("Error reading rows", err)
}
rows.Close()
// single row read
town := "London"
var username string
err = db.QueryRow("SELECT name FROM test WHERE town=$1", town).Scan(&username)
switch {
case err == sql.ErrNoRows:
log.Printf("No user with that ID.")
case err != nil:
log.Println("Error reading row", err)
default:
fmt.Printf("Username is %s\n", username)
}
}
// AddStatus writes new status info to the database
func AddStatus(dbConn *sql.DB, status *TaskStatus) (string, error) {
//logit.Info.Println("AddStatus called")
queryStr := fmt.Sprintf("insert into taskstatus ( containername, starttime, taskname, path, elapsedtime, tasksize, status, profilename, scheduleid, updatedt) values ( '%s', now(), '%s', '%s', '%s', '%s', '%s', '%s', %s, now()) returning id",
status.ContainerName,
status.TaskName,
status.Path,
status.ElapsedTime,
status.TaskSize,
status.Status, status.ProfileName, status.ScheduleID)
logit.Info.Println("AddStatus:" + queryStr)
var theID int
err := dbConn.QueryRow(queryStr).Scan(
&theID)
switch {
case err != nil:
logit.Error.Println(err.Error())
return "", err
default:
}
var strvalue string
strvalue = strconv.Itoa(theID)
logit.Info.Println("AddStatus returning ID=" + strvalue)
return strvalue, nil
}
func createSchemaUnlessExists(c *Config, db *sql.DB) error {
// If there's no schema name in the config, we don't need to create the schema.
if !strings.Contains(c.MigrationTable, ".") {
return nil
}
var exists bool
schema := strings.SplitN(c.MigrationTable, ".", 2)[0]
err := db.QueryRow(
`SELECT EXISTS(SELECT 1 FROM pg_catalog.pg_namespace WHERE nspname = $1)`,
schema,
).Scan(&exists)
if err != nil {
return err
}
if exists {
return nil
}
_, err = db.Exec(fmt.Sprintf(
"CREATE SCHEMA %s;",
pq.QuoteIdentifier(schema),
))
return err
}
func QueryRowSql(db *sql.DB, sqlStmt string, params ...interface{}) (row *sql.Row) {
if db == nil {
return
}
row = db.QueryRow(sqlStmt, params...)
return
}
func Test_deleting_an_image_cascades_on_related_tables(t *testing.T) {
var db *sql.DB
var err error
var count int
var statements string
db = createTestDb(t)
defer db.Close()
// Drop the image we just created.
statements = `DELETE FROM images;`
_, err = db.Exec(statements)
if err != nil {
t.Errorf("Error deleting image! %s", err)
}
// Make sure there are 0 images_aliases entries left.
statements = `SELECT count(*) FROM images_aliases;`
err = db.QueryRow(statements).Scan(&count)
if count != 0 {
t.Errorf("Deleting an image didn't delete the image alias association! There are %d left", count)
}
// Make sure there are 0 images_properties entries left.
statements = `SELECT count(*) FROM images_properties;`
err = db.QueryRow(statements).Scan(&count)
if count != 0 {
t.Errorf("Deleting an image didn't delete the related images_properties! There are %d left", count)
}
}
func HasProductBeenPurchasedByPerson(db *sql.DB, accountId int64, person *Person, product *Product) (seen bool) {
s := []string{}
s = append(s, "SELECT 'x'")
s = append(s, "FROM user_product_purchases u JOIN product p ON (")
s = append(s, "u.account_id = p.account_id AND")
s = append(s, "u.pid = p.pid)")
s = append(s, "WHERE u.account_id = $1 AND u.monetate_id = $2 AND u.pid = $3")
query := strings.Join(s, " ")
row := db.QueryRow(query, accountId, person.MonetateId, product.Pid)
var foo string
err := row.Scan(&foo)
if err == nil {
seen = true
} else {
if err == sql.ErrNoRows {
seen = false
} else {
panic(err)
}
}
return
}
func makeFooTable(t *testing.T, db *sql.DB) *FooTable {
const (
testTable = "FOO"
createStmt = "CREATE TABLE FOO (k number(1), v varchar2(20))"
insertStmt = "INSERT INTO FOO (k, v) VALUES (:1, :2)"
initBarVal = "baz"
)
foo := FooTable{
InitVal: initBarVal,
InitVals: map[int]string{
1: initBarVal,
2: initBarVal,
},
GetQ: "SELECT k, v FROM FOO",
UpdQ: "UPDATE FOO SET v=:1 WHERE k=:2",
}
var tabExists int
catch := ErrCatcher{t}
catch.NE(db.QueryRow(
"SELECT count(1) FROM user_tables WHERE table_name=:1",
testTable).Scan(&tabExists))
if tabExists != 0 {
db.Exec("DROP TABLE " + testTable)
}
catch.EX(db.Exec(createStmt))
for k, v := range foo.InitVals {
catch.EX(db.Exec(insertStmt, k, v))
}
expectVal("init", t, catch.Q(db.Query(foo.GetQ)), foo.InitVals)
return &foo
}
func insert_session(db *sql.DB, user_id int, sid string) {
row := db.QueryRow("select count(id) from auth_session where user_id=?", user_id)
var count int
err := row.Scan(&count)
if err != nil {
fmt.Println(err)
}
if count > 0 {
stmt, err := db.Prepare("update auth_session set sid=? where user_id=?")
if err != nil {
fmt.Println(err)
return
}
stmt.Exec(sid, user_id)
defer stmt.Close()
return
}
stmt, err := db.Prepare("insert into auth_session(user_id, sid) values (?,?)")
if err != nil {
fmt.Println(err)
}
defer stmt.Close()
if _, err := stmt.Exec(user_id, sid); err != nil {
fmt.Println("smt.Exec failed: ", err)
}
}
// Vérifie qu'on n'a pas fait trop d'appels aux scripts publics
// pour : le troll qui demande
// troll : celui dont le compte est incrémenté
// si ok est true, alors il faut faire l'appel (on le compte)
func (store *MysqlStore) CheckBeforeSoapCall(db *sql.DB, pour int, troll int, cat string) (bool, error) {
limite, ok := MAX_SP_CALLS_PER_DAY[cat]
if !ok {
return false, errors.New("unknown SP category : " + cat)
}
seconds := time.Now().Unix()
// on commence par compter
yesterday := seconds - (24 * 60 * 60)
row := db.QueryRow("select count(*) from appel_soap where troll=? and type=? and date>?", troll, cat, yesterday)
var r int
err := row.Scan(&r)
if err != nil {
return false, err
}
log.Printf("Appels aux SP pour le troll %d : %d / %d\n", troll, r, limite)
if r >= limite {
return false, nil
}
// c'est bon, donc on va noter l'appel qui va suivre
sql := "insert into appel_soap (troll, pour, date, type) values (?, ?, ?, ?)"
_, err = db.Exec(sql, troll, pour, seconds, cat)
if err != nil {
return false, err
}
return true, nil
}