func createLoadStmt(tableName *string, values *[]string, vld_op string, blWithPK bool, db *sql.Tx, verbose *bool) *sql.Stmt {
if len(*values) == 0 {
log.Fatalln("Nothing to build insert with!")
}
var buffer bytes.Buffer
buffer.WriteString("INSERT ")
if len(vld_op) > 0 && blWithPK {
buffer.WriteString(" OR " + vld_op + " ")
}
buffer.WriteString(" INTO " + (*tableName) + " VALUES (")
for i := range *values {
buffer.WriteString("?")
if i != len(*values)-1 {
buffer.WriteString(", ")
}
}
buffer.WriteString(");")
if *verbose {
fmt.Println(buffer.String())
}
stmt, err := db.Prepare(buffer.String())
if err != nil {
log.Fatalln(err)
}
return stmt
}
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())
}
}
// chooseWorkUnits chooses up to a specified number of work units from
// some work spec.
func (w *worker) chooseWorkUnits(tx *sql.Tx, spec *workSpec, numUnits int) ([]*workUnit, error) {
query := buildSelect([]string{
workUnitID,
workUnitName,
}, []string{
workUnitTable,
}, []string{
inThisWorkSpec,
workUnitAttempt + " IS NULL",
})
query += fmt.Sprintf(" ORDER BY priority DESC, name ASC")
query += fmt.Sprintf(" LIMIT %v", numUnits)
query += " FOR UPDATE OF work_unit"
rows, err := tx.Query(query, spec.id)
if err != nil {
return nil, err
}
var result []*workUnit
err = scanRows(rows, func() error {
unit := workUnit{spec: spec}
if err := rows.Scan(&unit.id, &unit.name); err == nil {
result = append(result, &unit)
}
return err
})
if err != nil {
return nil, err
}
return result, nil
}
// 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
}
func processArticles(articleChannel *chan *[]Article, tx *sql.Tx, waitGroup *sync.WaitGroup) {
waitGroup.Add(1)
for articles := range *articleChannel {
rows, err := tx.Query("select subscription_id, datetime(published), url from articles where published >= datetime(?) and subscription_id = ? order by datetime(published) asc", (*articles)[0].Published.Format(time.RFC3339), (*articles)[0].SubscriptionId)
var existingArticles []Article
if err != nil {
log.Println("Article Download Error | Error retrieving existing articles " + err.Error())
}
for rows.Next() {
var article Article
var dateString string
rows.Scan(&article.SubscriptionId, &dateString, &article.Url)
article.Published, err = time.Parse("2006-01-02 15:04:05", dateString)
existingArticles = append(existingArticles, Article{})
copy(existingArticles[0+1:], existingArticles[0:])
existingArticles[0] = article
}
rows.Close()
if len(existingArticles) == 0 {
insertFinalArticleSlice(articles, tx)
} else if (existingArticles[0].Published != (*articles)[len(*articles)-1].Published) && (existingArticles[0].Url != (*articles)[len(*articles)-1].Url) {
index := sort.Search(len(*articles), func(i int) bool {
return (*articles)[i].Published.Unix() > existingArticles[0].Published.Unix()
})
newSlice := ((*articles)[index:])
insertFinalArticleSlice(&newSlice, tx)
}
}
waitGroup.Done()
}
// 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 (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
}
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 insertConsumerStatusLog(tx *sql.Tx, consumerId, employeeId, oldStatus, newStatus string) (id int64, err error) {
sql := "insert into consumer_status_log(consumer_id,employee_id,create_time,old_status,new_status) values(?,?,?,?,?)"
lessgo.Log.Debug(sql)
stmt, err := tx.Prepare(sql)
if err != nil {
lessgo.Log.Error(err.Error())
return 0, err
}
res, err := stmt.Exec(consumerId, employeeId, time.Now().Format("20060102150405"), oldStatus, newStatus)
if err != nil {
lessgo.Log.Error(err.Error())
return 0, err
}
logId, err := res.LastInsertId()
if err != nil {
lessgo.Log.Error(err.Error())
return 0, err
}
return logId, err
}
// 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 rollbackAndError(tx *sql.Tx, err error) error {
new_err := tx.Rollback()
if new_err != nil {
return fmt.Errorf("Rollback error (%v); previous error (%v)", new_err, err)
}
return err
}
func getLoadBatch(tx *sql.Tx, batchUuid string) (*LoadBatch, error) {
var batch LoadBatch
batch.UUID = batchUuid
rows, err := tx.Query("SELECT keyname, tablename FROM "+pendingLoadTable+" WHERE batch_uuid = $1", batchUuid)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
var load Load
err := rows.Scan(&load.KeyName, &load.TableName)
if err != nil {
log.Println("Scan threw an error!")
return nil, err
}
batch.Loads = append(batch.Loads, load)
}
if len(batch.Loads) == 0 {
return nil, noLoadsError
}
batch.TableName = batch.Loads[0].TableName
return &batch, 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
}
// getLogEntries retrieves log entries in the range [from, to)
func getLogEntries(tx *sql.Tx, from, to int) ([]*LogEntry, error) {
var out []*LogEntry
rows, err := tx.Query(`SELECT id, term, client_id, client_serial, operation, key, value `+
`FROM log WHERE id >= ? AND id < ? ORDER BY id ASC`, from, to)
if err != nil {
log.Printf("db error loading log entries [%d,%d): %v", from, to, err)
return nil, err
}
for rows.Next() {
l := new(LogEntry)
out = append(out, l)
err := rows.Scan(
&l.ID,
&l.Term,
&l.ClientRequest.ClientID,
&l.ClientRequest.ClientSerial,
&l.ClientRequest.Operation,
&l.ClientRequest.Key,
&l.ClientRequest.Value)
if err != nil {
log.Printf("db error scanning log entry: %v", err)
return nil, err
}
}
if err := rows.Err(); err != nil {
log.Printf("db error reading log entries: %v", err)
return nil, err
}
return out, nil
}
// 处理分时数据
func savePeroid(tx *sql.Tx, table string, peroid []Peroid60) error {
if len(peroid) == 0 {
return nil
}
stmt, err := tx.Prepare("replace into " + table + " values(?,?,?,?,?,?)")
if err != nil {
return err
}
defer stmt.Close()
for _, p := range peroid {
// 新增
result, err := stmt.Exec(p.Time, p.Open, p.Close, p.High, p.Low, p.Volume)
if err != nil {
return err
}
ra, err := result.RowsAffected()
if err != nil {
return err
}
if ra == 0 {
return sql.ErrNoRows
}
}
return 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 saveError(tx *sql.Tx, date, message string) error {
stmt, err := tx.Prepare("replace into error values(?,?)")
if err != nil {
return err
}
defer stmt.Close()
// 新增
result, err := stmt.Exec(date, message)
if err != nil {
return err
}
ra, err := result.RowsAffected()
if err != nil {
return err
}
if ra == 0 {
return sql.ErrNoRows
}
return nil
}
func (r *Runner) invokeMigration(isTest bool, m Migration, c *spiffy.DbConnection, optionalTx ...*sql.Tx) (err error) {
defer func() {
if r := recover(); r != nil {
err = fmt.Errorf("%v", err)
}
}()
if m.IsTransactionIsolated() {
err = m.Apply(c, spiffy.OptionalTx(optionalTx...))
return
}
var tx *sql.Tx
tx, err = c.Begin()
if err != nil {
return err
}
defer func() {
if err == nil {
err = exception.Wrap(tx.Commit())
} else {
err = exception.WrapMany(err, exception.New(tx.Rollback()))
}
}()
err = m.Apply(c, tx)
return
}
func (qs *QuadStore) copyFrom(tx *sql.Tx, in []graph.Delta) error {
stmt, err := tx.Prepare(pq.CopyIn("quads", "subject", "predicate", "object", "label", "id", "ts", "subject_hash", "predicate_hash", "object_hash", "label_hash"))
if err != nil {
return err
}
for _, d := range in {
_, err := stmt.Exec(
d.Quad.Subject,
d.Quad.Predicate,
d.Quad.Object,
d.Quad.Label,
d.ID.Int(),
d.Timestamp,
hashOf(d.Quad.Subject),
hashOf(d.Quad.Predicate),
hashOf(d.Quad.Object),
hashOf(d.Quad.Label),
)
if err != nil {
glog.Errorf("couldn't prepare COPY statement: %v", err)
return err
}
}
_, err = stmt.Exec()
if err != nil {
return err
}
return stmt.Close()
}
// GetAllACIInfos returns all the ACIInfos sorted by optional sortfields and
// with ascending or descending order.
func GetAllACIInfos(tx *sql.Tx, sortfields []string, ascending bool) ([]*ACIInfo, error) {
aciinfos := []*ACIInfo{}
query := "SELECT * from aciinfo"
if len(sortfields) > 0 {
query += fmt.Sprintf(" ORDER BY %s ", strings.Join(sortfields, ", "))
if ascending {
query += "ASC"
} else {
query += "DESC"
}
}
rows, err := tx.Query(query)
if err != nil {
return nil, err
}
for rows.Next() {
aciinfo := &ACIInfo{}
if err := rows.Scan(&aciinfo.BlobKey, &aciinfo.AppName, &aciinfo.ImportTime, &aciinfo.Latest); err != nil {
return nil, err
}
aciinfos = append(aciinfos, aciinfo)
}
if err := rows.Err(); err != nil {
return nil, err
}
return aciinfos, err
}
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 insertScheduleChild(tx *sql.Tx, childId, scheduleId, classId, employeeId, contractId, isFree string) error {
sql := ""
if classId != "" {
sql += "insert into schedule_detail_child(schedule_detail_id,child_id,create_time,create_user,sms_status,wyclass_id,is_free) values(?,?,?,?,?,?,?) "
} else {
sql += "insert into schedule_detail_child(schedule_detail_id,child_id,create_time,create_user,sms_status,is_free,contract_id) values(?,?,?,?,?,?,?) "
}
lessgo.Log.Debug(sql)
stmt, err := tx.Prepare(sql)
if err != nil {
lessgo.Log.Error(err.Error())
return err
}
if classId != "" {
_, err = stmt.Exec(scheduleId, childId, time.Now().Format("20060102150405"), employeeId, 1, classId, isFree)
} else {
_, err = stmt.Exec(scheduleId, childId, time.Now().Format("20060102150405"), employeeId, 1, isFree, contractId)
}
if err != nil {
lessgo.Log.Error(err.Error())
return err
}
return nil
}
func dbContainerConfigInsert(tx *sql.Tx, id int, config map[string]string) error {
str := "INSERT INTO containers_config (container_id, key, value) values (?, ?, ?)"
stmt, err := tx.Prepare(str)
if err != nil {
return err
}
defer stmt.Close()
for k, v := range config {
if k == "raw.lxc" {
err := validateRawLxc(config["raw.lxc"])
if err != nil {
return err
}
}
if !ValidContainerConfigKey(k) {
return fmt.Errorf("Bad key: %s\n", k)
}
_, err = stmt.Exec(id, k, v)
if err != nil {
shared.Debugf("Error adding configuration item %s = %s to container %d\n",
k, v, id)
return err
}
}
return nil
}
func insertContacts(tx *sql.Tx, name, phone, consumerId string) (id int64, err error) {
sql := "insert into contacts(name,phone,is_default,consumer_id) values(?,?,?,?)"
lessgo.Log.Debug(sql)
stmt, err := tx.Prepare(sql)
if err != nil {
lessgo.Log.Error(err.Error())
return 0, err
}
res, err := stmt.Exec(name, phone, "1", consumerId)
if err != nil {
lessgo.Log.Error(err.Error())
return 0, err
}
contactsId, err := res.LastInsertId()
if err != nil {
lessgo.Log.Error(err.Error())
return 0, err
}
return contactsId, nil
}
func FindJournoByName(tx *sql.Tx, name string) ([]*Journo, error) {
// TODO: use journo_alias table to do lookup!
// KLUDGE ALERT: we're using refs to look up journos. This sucks, but
// we're stuck with it until we transition over to a properly-populated journo_alias table
// check first 20 possible refs.
r := baseRef(name)
if r == "" {
return []*Journo{}, nil
}
refs := []interface{}{r}
for i := 1; i < 20; i++ {
refs = append(refs, fmt.Sprintf("%s-%d", r, i))
}
sql := `SELECT id,ref,prettyname,lastname,firstname,created,status,oneliner,last_similar,modified,firstname_metaphone, lastname_metaphone, admin_notes, admin_tags,fake FROM journo WHERE ref IN (` + pgMarkerList(1, len(refs)) + `)`
rows, err := tx.Query(sql, refs...)
if err != nil {
return nil, err
}
defer rows.Close()
out := []*Journo{}
for rows.Next() {
var j Journo
if err := rows.Scan(&j.ID, &j.Ref, &j.Prettyname, &j.Lastname, &j.Firstname, &j.Created, &j.Status, &j.Oneliner, &j.LastSimilar, &j.Modified, &j.FirstnameMetaphone, &j.LastnameMetaphone, &j.AdminNotes, &j.AdminTags, &j.Fake); err != nil {
return nil, err
}
out = append(out, &j)
}
if err := rows.Err(); err != nil {
return nil, err
}
return out, nil
}
// postgres does not have REPLACE INTO (upsert), so we use that custom
// one for Set operations in batch instead
func altBatchSet(tx *sql.Tx, key, value string) error {
r, err := tx.Query("SELECT replaceinto($1, $2)", key, value)
if err != nil {
return err
}
return r.Close()
}
func tryRollback(lbl Label, tx *sql.Tx, err error) (Label, error) {
rbErr := tx.Rollback()
if rbErr != nil {
return lbl, rbErr
}
return lbl, err
}
// 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
}
// 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)
}
}