// createPhoto looks up or creates a new user to match userID (it's
// the only method in this interface which doesn't match an existing
// user except for createUser). It then creates a new photo for the
// new or pre-existing user.
func createPhoto(tx *sql.Tx, userID int) error {
if err := createUser(tx, userID); err != nil {
return err
}
const insertSQL = `
INSERT INTO photos VALUES (DEFAULT, $1, 0, $2, $3, $4, NOW());
`
const minCaptionLen = 10
const maxCaptionLen = 200
caption := randString(minCaptionLen + rand.Intn(maxCaptionLen-minCaptionLen))
latitude := rand.Float32() * 90
longitude := rand.Float32() * 180
if _, err := tx.Exec(insertSQL, userID, caption, latitude, longitude); err != nil {
return err
}
const updateSQL = `
UPDATE users SET photoCount = photoCount + 1 WHERE id = $1;
`
if _, err := tx.Exec(updateSQL, userID); err != nil {
return err
}
return nil
}
func setTablesDataTransaction(data map[string][]map[string]string, tx *sql.Tx) error {
for tableName, tableData := range data {
sql := fmt.Sprintf("truncate `%s`", tableName)
_, err := tx.Exec(sql)
if err != nil {
return err
}
for _, row := range tableData {
colNameList := []string{}
placeHolderNum := len(row)
valueList := []interface{}{}
for name, value := range row {
colNameList = append(colNameList, name)
valueList = append(valueList, value)
}
sqlColNamePart := "`" + strings.Join(colNameList, "`, `") + "`"
sqlValuePart := strings.Repeat("?, ", placeHolderNum-1) + "?"
sql = fmt.Sprintf("INSERT INTO `%s` (%s) VALUES (%s)", tableName, sqlColNamePart, sqlValuePart)
_, err := tx.Exec(sql, valueList...)
if err != nil {
return err
}
}
}
return nil
}
// Down is executed when this migration is rolled back
func Down_20130708104836(txn *sql.Tx) {
_, err := txn.Exec(sqlDown)
if err != nil {
fmt.Printf("error adding col announce key: %s", err.Error())
}
}
func truncateAllTables(svc *Services) error {
var tx *sql.Tx
done := func(err error) error {
if err == nil {
err = tx.Commit()
}
if err != nil {
tx.Rollback()
return fmt.Errorf("truncateAllTables failed: %v", err)
} else {
return nil
}
}
tx, err := svc.db.Begin()
if err != nil {
return done(err)
}
names, err := getTableNames(tx)
if err != nil {
return done(err)
}
for _, name := range names {
stmt := fmt.Sprintf("TRUNCATE TABLE %v CASCADE", name)
if _, err := tx.Exec(stmt); err != nil {
return done(err)
}
}
return done(nil)
}
// RemoveACIInfo removes the ACIInfo with the given blobKey.
func RemoveACIInfo(tx *sql.Tx, blobKey string) error {
_, err := tx.Exec("DELETE from aciinfo where blobkey == $1", blobKey)
if err != nil {
return err
}
return 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
}
func (self *DBRcs) Upgrade(patch_root string, verbose bool) (err error) {
// 1. Read the version file passed in.
// 2. Verify the previous version exists and matches the *current*
// version of the database.
// 3. Start transactional schema upgrade
// 4. Update version code in schema
// 5. Rollback on failure, commit if no errors
var curr_db_ver string
var curr_patch_dir string
var node *versionNode
var sql_cmd string
var txn *sql.Tx
var upgrade_bytes []byte
if err = self.ComputeHistory(patch_root); err != nil {
return err
}
if curr_db_ver, err = self.CurrentDBVersion(); err != nil {
if err == sql.ErrNoRows {
return fmt.Errorf("No version code has been set yet. You need to initialize the versioning system.")
}
return err
}
// walk the nodes from back to front
for i := len(self.revisions) - 1; i >= 0; i-- {
node = self.revisions[i]
// Process this node
curr_patch_dir, err = GetPatchDirectory(patch_root, node.version)
if node.prev == curr_db_ver {
upgrade_sql := filepath.Join(curr_patch_dir, "upgrade.sql")
fmt.Printf("Reading from :[%v]\n", upgrade_sql)
upgrade_bytes, err = ioutil.ReadFile(upgrade_sql)
sql_cmd = string(upgrade_bytes)
fmt.Printf("Attempting SQL is: [%v]\n", sql_cmd)
txn, err = self.Db.Begin()
if _, err = txn.Exec(sql_cmd); err != nil {
fmt.Printf("Failed to execute: [%s]\n", sql_cmd)
txn.Rollback()
return err
}
// Roll the version forward
_, err = txn.Exec("update meta set value = $1 where key = 'db.hash';", node.version)
if err != nil {
txn.Rollback()
return err
}
fmt.Printf("Version switched from : [%v]\n", curr_db_ver)
curr_db_ver = node.version
fmt.Printf("Version switched to : [%v]\n", curr_db_ver)
txn.Commit()
fmt.Printf("Success! Executed [%v]\n", sql_cmd)
}
}
return err
}
// Remove removes the CloudFormation stack for the given app, if it exists.
func (s *Scheduler) remove(_ context.Context, tx *sql.Tx, appID string) error {
stackName, err := s.stackName(appID)
// if there's no stack entry in the db for this app, nothing to remove
if err == errNoStack {
return nil
}
if err != nil {
return err
}
_, err = tx.Exec(`DELETE FROM stacks WHERE app_id = $1`, appID)
if err != nil {
return err
}
_, err = s.cloudformation.DescribeStacks(&cloudformation.DescribeStacksInput{
StackName: aws.String(stackName),
})
if err, ok := err.(awserr.Error); ok && err.Message() == fmt.Sprintf("Stack with id %s does not exist", stackName) {
return nil
}
if _, err := s.cloudformation.DeleteStack(&cloudformation.DeleteStackInput{
StackName: aws.String(stackName),
}); err != nil {
return fmt.Errorf("error deleting stack: %v", 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 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 tagEvents(tx *sql.Tx, eventId int64, tagIds []int64) (err error) {
if len(tagIds) == 0 {
return
}
var buffer bytes.Buffer
buffer.WriteString(`INSERT INTO "event_tag" ("event_id", "tag_id") VALUES `)
for i, tagId := range tagIds {
buffer.WriteString(fmt.Sprintf("(%d, %d)", eventId, tagId))
if i < (len(tagIds) - 1) {
buffer.WriteString(", ")
}
}
buffer.WriteString(";")
if _, err = tx.Exec(buffer.String()); err != nil {
return fmt.Errorf("Error tagging event: %s\nQuery:\n\n%s", err.Error(), buffer.String())
}
return
}
// Down is executed when this migration is rolled back
func Down_20150712142846(txn *sql.Tx) {
log.Println("Drop Table Places")
if _, err := txn.Exec("DROP TABLE places;"); err != nil {
txn.Rollback()
log.Println("Error dropping table places", err)
}
}
// Insert saves the options to the database
func (m *ProfileOptionType) Insert(tx *sql.Tx) (int, error) {
_, err := tx.Exec(`
INSERT INTO profile_options (
profile_id
,show_dob_year
,show_dob_date
,send_email
,send_sms
,is_discouraged
) VALUES (
$1
,$2
,$3
,$4
,$5
,$6
)`,
m.ProfileID,
m.ShowDOBYear,
m.ShowDOB,
m.SendEMail,
m.SendSMS,
m.IsDiscouraged,
)
if err != nil {
tx.Rollback()
return http.StatusInternalServerError,
fmt.Errorf("Error inserting data: %v", err.Error())
}
go PurgeCacheByScope(c.CacheOptions, h.ItemTypes[h.ItemTypeProfile], m.ProfileID)
return http.StatusOK, nil
}
// FlushRoleMembersCacheByProfileID clears the database caches
func FlushRoleMembersCacheByProfileID(
tx *sql.Tx,
profileID int64,
) (
int,
error,
) {
_, err := tx.Exec(
`DELETE FROM permissions_cache WHERE profile_id = $1`,
profileID,
)
if err != nil {
return http.StatusInternalServerError,
fmt.Errorf("Error executing statement: %v", err.Error())
}
_, err = tx.Exec(
`DELETE FROM role_members_cache WHERE profile_id = $1`,
profileID,
)
if err != nil {
return http.StatusInternalServerError,
fmt.Errorf("Error executing statement: %v", err.Error())
}
return http.StatusOK, nil
}
// RemoveRemote removes the remote with the given blobKey.
func RemoveRemote(tx *sql.Tx, blobKey string) error {
_, err := tx.Exec("DELETE FROM remote WHERE blobkey == $1", blobKey)
if err != nil {
return err
}
return nil
}
func SessionUpdate(txn *sql.Tx, s Session) error {
res, e := txn.Exec(
`UPDATE
session
SET
bytes_in = bytes_in + ?,
bytes_out = bytes_out + ?,
packets_in = packets_in + ?,
packets_out = packets_out + ?,
session_time = ?
WHERE
session_id = ?
AND
user = ?
AND
nas_ip = ?`,
s.BytesIn, s.BytesOut, s.PacketsIn, s.PacketsOut, s.SessionTime,
s.SessionID, s.User, s.NasIP,
)
if e != nil {
return e
}
return affectCheck(res, 1, fmt.Errorf(
"session.update fail for sess=%s user=%s",
s.SessionID, s.User,
))
}
func (self *Source) doExec(terms ...interface{}) (res sql.Result, err error) {
var tx *sql.Tx
if self.session == nil {
return nil, db.ErrNotConnected
}
chunks := sqlCompile(terms)
query := strings.Join(chunks.Query, ` `)
for i := 0; i < len(chunks.Args); i++ {
query = strings.Replace(query, `?`, fmt.Sprintf(`$%d`, i+1), 1)
}
if debugEnabled() == true {
debugLogQuery(query, chunks)
}
if tx, err = self.session.Begin(); err != nil {
return nil, err
}
if res, err = tx.Exec(query, chunks.Args...); err != nil {
return nil, err
}
if err = tx.Commit(); err != nil {
return nil, err
}
return res, nil
}
// Copy session to log
func SessionLog(txn *sql.Tx, sessionId string, user string, nasIp string) error {
res, e := txn.Exec(
`INSERT INTO
session_log
(assigned_ip, bytes_in, bytes_out, client_ip,
nas_ip, packets_in, packets_out, session_id,
session_time, user, time_added)
SELECT
assigned_ip, bytes_in, bytes_out, client_ip,
nas_ip, packets_in, packets_out, session_id,
session_time, user, time_added
FROM
session
WHERE
session_id = ?
AND
user = ?
AND
nas_ip = ?`,
sessionId, user, nasIp,
)
if e != nil {
return e
}
return affectCheck(res, 1, fmt.Errorf(
"session.log fail for sess=%s",
sessionId,
))
}
func migrateToV4(tx *sql.Tx) error {
for _, t := range []string{
"CREATE TABLE aciinfo_tmp (blobkey string, name string, importtime time, lastusedtime time, latest bool);",
"INSERT INTO aciinfo_tmp (blobkey, name, importtime, latest) SELECT blobkey, name, importtime, latest from aciinfo",
"DROP TABLE aciinfo",
// We don't use now() as a DEFAULT for lastusedtime because it doesn't
// return a UTC time, which is what we want. Instead, we UPDATE it
// below.
"CREATE TABLE aciinfo (blobkey string, name string, importtime time, lastusedtime time, latest bool);",
"CREATE UNIQUE INDEX IF NOT EXISTS blobkeyidx ON aciinfo (blobkey)",
"CREATE INDEX IF NOT EXISTS nameidx ON aciinfo (name)",
"INSERT INTO aciinfo SELECT * from aciinfo_tmp",
"DROP TABLE aciinfo_tmp",
} {
_, err := tx.Exec(t)
if err != nil {
return err
}
}
t := time.Now().UTC()
_, err := tx.Exec("UPDATE aciinfo lastusedtime = $1", t)
if err != nil {
return err
}
return 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(¶ms, "work_unit_id", unit.id)
fields.Add(¶ms, "work_spec_id", unit.spec.id)
fields.Add(¶ms, "worker_id", w.id)
fields.Add(¶ms, "start_time", now)
fields.Add(¶ms, "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(¶ms, "active_attempt_id", a.id)
query = buildUpdate(workUnitTable, fields.UpdateChanges(), []string{
isWorkUnit(¶ms, 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
}
// 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
}
// saveLogEntries saves a slice of log entries, which must be in order by index.
func saveLogEntries(tx *sql.Tx, entries []*LogEntry) error {
if len(entries) == 0 {
return nil
}
// truncate the log if applicable
_, err := tx.Exec(`DELETE FROM log WHERE id >= ?`, entries[0].ID)
if err != nil {
log.Printf("db error truncating log: %v", err)
return err
}
for _, elt := range entries {
_, err := tx.Exec(`INSERT INTO log (id, term, client_id, client_serial, operation, key, value) `+
`VALUES (?,?,?,?,?,?,?)`,
elt.ID,
elt.Term,
elt.ClientRequest.ClientID,
elt.ClientRequest.ClientSerial,
elt.ClientRequest.Operation,
elt.ClientRequest.Key,
elt.ClientRequest.Value)
if err != nil {
log.Printf("db error inserting log entry: %v", err)
return err
}
}
return nil
}
func dataCompletenessDelete(r *http.Request, h http.Header, b *bytes.Buffer) *weft.Result {
v := r.URL.Query()
siteID := v.Get("siteID")
typeID := v.Get("typeID")
var txn *sql.Tx
var err error
if txn, err = db.Begin(); err != nil {
return weft.InternalServerError(err)
}
for _, table := range []string{"data.completeness", "data.completeness_summary", "data.completeness_tag"} {
if _, err = txn.Exec(`DELETE FROM `+table+` WHERE
sitePK = (SELECT sitePK FROM data.site WHERE siteID = $1)
AND typePK = (SELECT typePK FROM data.completeness_type WHERE typeID = $2)`,
siteID, typeID); err != nil {
txn.Rollback()
return weft.InternalServerError(err)
}
}
if err = txn.Commit(); err != nil {
return weft.InternalServerError(err)
}
return &weft.StatusOK
}
// insert saves a profile to a role
func (m *RoleProfileType) insert(
tx *sql.Tx,
siteID int64,
roleID int64,
) (
int,
error,
) {
status, err := m.Validate(siteID)
if err != nil {
return status, err
}
// upsert
_, err = tx.Exec(`
INSERT INTO role_profiles
SELECT $1, $2
WHERE NOT EXISTS (
SELECT role_id
,profile_id
FROM role_profiles
WHERE role_id = $1
AND profile_id = $2
)`,
roleID,
m.ID,
)
if err != nil {
return http.StatusInternalServerError,
fmt.Errorf("Error executing upsert: %v", err.Error())
}
return http.StatusOK, nil
}
// Up is executed when this migration is applied
func Up_20151005012132(txn *sql.Tx) {
sql := `
create table orders (
id uuid not null,
createdat timestamp with time zone not null,
updatedat timestamp with time zone not null,
orderedat timestamp with time zone not null,
delivered_at timestamp with time zone,
seller_id uuid not null,
buyer_id uuid not null,
quantity_purchased integer default 0,
constraint order_pk primary key (id)
)
with (
OIDS=FALSE
);
create index orders_placed on orders(orderedat);
create index orders_delivered on orders(delivered_at);
create index orders_sellers on orders(seller_id);
create index orders_buyers on orders(buyer_id);
`
if _, err := txn.Exec(sql); err != nil {
fmt.Println("Error creating inventory table:", err)
}
}
// 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 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
}
// Benchmarking raw database/sql.
func BenchmarkAppendTxRawSQL(b *testing.B) {
var err error
var sess db.Database
var tx *sql.Tx
if sess, err = db.Open(Adapter, settings); err != nil {
b.Fatal(err)
}
defer sess.Close()
driver := sess.Driver().(*sql.DB)
if tx, err = driver.Begin(); err != nil {
b.Fatal(err)
}
if _, err = tx.Exec("TRUNCATE TABLE `artist`"); err != nil {
b.Fatal(err)
}
b.ResetTimer()
for i := 0; i < b.N; i++ {
if _, err = tx.Exec("INSERT INTO `artist` (`name`) VALUES('Hayao Miyazaki')"); err != nil {
b.Fatal(err)
}
}
if err = tx.Commit(); err != nil {
b.Fatal(err)
}
}
// Down is executed when this migration is rolled back
func Down_20130713085811(txn *sql.Tx) {
_, err := txn.Exec(sqlDown)
if err != nil {
fmt.Printf("error commiting txn: %s\n", err.Error())
}
}
