func RegisterData(params martini.Params, l *log.Logger, db *sql.DB) (int, string) {
key := params["key"]
value := params["value"]
l.Println(key, value)
tx, err := db.Begin()
if err != nil {
log.Fatal(err)
}
stmt, err := tx.Prepare("insert into foo(id, name) values(?, ?)")
if err != nil {
log.Fatal(err)
}
defer stmt.Close()
for i := 0; i < 10; i++ {
_, err = stmt.Exec(i, fmt.Sprintf("[%s = %s] ねぷねぷのぷりん%03d", key, value, i))
if err != nil {
log.Fatal(err)
}
}
tx.Commit()
return 200, "Hello!" + value
}
// LoadFixtures loads test fixtures in a transaction
// if the transaction fails, no fatal error is triggered
func LoadFixtures(db *sql.DB, driver string) *sql.DB {
migrationFile, err := os.Open("./testdata/fixtures/" + driver + "/fixtures.sql")
if err != nil {
log.Fatal(err)
}
buffer := new(bytes.Buffer)
_, err = buffer.ReadFrom(migrationFile)
if err != nil {
log.Fatal(err)
}
tx, err := db.Begin()
if err != nil {
log.Fatal(err)
}
_, err = tx.Exec(buffer.String())
if err != nil {
log.Printf("Error loading fixtures, %s", err)
tx.Rollback()
} else {
err = tx.Commit()
if err != nil {
log.Fatal(err)
}
}
return db
}
// Run a migration specified in raw SQL.
//
// Sections of the script can be annotated with a special comment,
// starting with "-- +goose" to specify whether the section should
// be applied during an Up or Down migration
//
// All statements following an Up or Down directive are grouped together
// until another direction directive is found.
func runSQLMigration(conf *DBConf, db *sql.DB, scriptFile string, v int64, direction bool) error {
txn, err := db.Begin()
if err != nil {
log.Fatal("db.Begin:", err)
}
f, err := os.Open(scriptFile)
if err != nil {
log.Fatal(err)
}
// find each statement, checking annotations for up/down direction
// and execute each of them in the current transaction.
// Commits the transaction if successfully applied each statement and
// records the version into the version table or returns an error and
// rolls back the transaction.
for _, query := range splitSQLStatements(f, direction) {
if _, err = txn.Exec(query); err != nil {
txn.Rollback()
log.Fatalf("FAIL %s (%v), quitting migration.", filepath.Base(scriptFile), err)
return err
}
}
if err = FinalizeMigration(conf, txn, direction, v); err != nil {
log.Fatalf("error finalizing migration %s, quitting. (%v)", filepath.Base(scriptFile), err)
}
return nil
}
// Run a migration specified in raw SQL.
//
// Sections of the script can be annotated with a special comment,
// starting with "-- +goose" to specify whether the section should
// be applied during an Up or Down migration
//
// All statements following an Up or Down directive are grouped together
// until another direction directive is found.
func runSQLMigration(conf *DBConf, db *sql.DB, script string, v int64, direction bool) error {
txn, err := db.Begin()
if err != nil {
log.Fatal("db.Begin:", err)
}
f, err := os.Open(script)
if err != nil {
log.Fatal(err)
}
// find each statement, checking annotations for up/down direction
// and execute each of them in the current transaction
for _, query := range splitSQLStatements(f, direction) {
if _, err = txn.Exec(query); err != nil {
txn.Rollback()
log.Fatalf("FAIL %s (%v), quitting migration.", filepath.Base(script), err)
return err
}
}
if err = finalizeMigration(conf, txn, direction, v); err != nil {
log.Fatalf("error finalizing migration %s, quitting. (%v)", filepath.Base(script), err)
}
return nil
}
//In a transaction block of executive function
func Transaction(db *sql.DB, f func() error) error {
tx, err := db.Begin()
if err != nil {
//panic(err)
return err
}
//如果f()函数是通过panic抛出错误，那也将此错误使用panic抛出
defer func() {
if r := recover(); r != nil {
err := tx.Rollback()
if err != nil {
panic(err)
//return err
}
panic(r)
//return errors.New(fmt.Sprint(r))
} else {
err = tx.Commit()
if err != nil {
//panic(err)
//return err
log.Fatal(err)
}
}
}()
err = f()
if err != nil {
return tx.Rollback()
}
return err
}
// DeleteContact deletes the contact from the DB.
func (c *Contact) DeleteContact(db *sql.DB) error {
// Do in a transaction because we have to first delete the contact on the sites.
tx, err := db.Begin()
if err != nil {
return err
}
_, err = db.Exec(
`DELETE FROM SiteContacts WHERE ContactID = $1`,
c.ContactID,
)
if err != nil {
tx.Rollback()
return err
}
_, err = db.Exec(
`DELETE FROM Contacts WHERE ContactID = $1;`,
c.ContactID,
)
if err != nil {
tx.Rollback()
return err
}
tx.Commit()
return nil
}
func verifyBank(db *sql.DB) {
var sum int64
if *aggregate {
if err := db.QueryRow("SELECT SUM(balance) FROM accounts").Scan(&sum); err != nil {
log.Fatal(err)
}
} else {
tx, err := db.Begin()
if err != nil {
log.Fatal(err)
}
rows, err := tx.Query("SELECT balance FROM accounts")
if err != nil {
log.Fatal(err)
}
for rows.Next() {
var balance int64
if err = rows.Scan(&balance); err != nil {
log.Fatal(err)
}
sum += balance
}
if err = tx.Commit(); err != nil {
log.Fatal(err)
}
}
if sum == 0 {
log.Info("The bank is in good order.")
} else {
log.Fatalf("The bank is not in good order. Total value: %d", sum)
}
}
func UpdateBankPaper(rc *impl.RCM_Cmd) (interface{}, error) {
var uid, bankId int64
var paperIds, p2bIds, ext string
err := rc.ValidF(`
uid,R|I,R:0;
bankId,R|I,R:0;
paperIds,R|S,L:0;
p2bIds,O|S,L:0;
ext,O|S,L:0;
`, &uid, &bankId, &paperIds, &p2bIds, &ext)
if err != nil {
log.E("UpdateBankPaper arg err:%v", err)
return 0, err
}
extParse := []P2b_Paper{}
json.Unmarshal([]byte(ext), &extParse)
rs := []P2b_Paper{}
paperIdsArr := []int64{}
err = json.Unmarshal([]byte(paperIds), &paperIdsArr)
if err != nil {
return nil, err
}
if p2bIds == "" {
p2bIds = "0"
}
sql_ := "delete from ebs_p2b where bank_id=? and tid not in (" + p2bIds + ")"
var conn *sql.DB = dbMgr.DbConn()
tx, err := conn.Begin()
if err != nil {
return nil, err
}
//同步练习
for _, v := range extParse {
fmt.Println(tx.Exec("insert into ebs_p2b (tid,bank_id,paper_id,status,add1) values (?,?,?,'N','P')", v.P2bId, bankId, v.PId))
}
//同步练习
if ext != "" {
sql_ += " and add1='P'"
} else {
sql_ += " and (add1!='P' or add1 is null)"
}
log.D("%s", sql_)
if _, err := tx.Exec(sql_, bankId); err != nil {
tx.Rollback()
return nil, err
}
for _, v := range paperIdsArr {
if res, err := tx.Exec("insert into ebs_p2b (bank_id,paper_id,status) values (?,?,'N')", bankId, v); err != nil {
tx.Rollback()
return nil, err
} else {
p2bId, _ := res.LastInsertId()
rs = append(rs, P2b_Paper{p2bId, v})
}
}
return rs, tx.Commit()
}
// snippetCommentUpdate will update an existing comment in the database
func snippetCommentUpdate(db *sql.DB, comment *snippetComment) error {
var err error
tx, err := db.Begin()
if err != nil {
return err
}
comment.Updated = UnixMilliseconds()
_, err = db.Exec(
"UPDATE snippet_comment SET markdown=?,html=?,updated=? WHERE comment_id=?",
comment.Markdown,
markdownParse(comment.Markdown),
comment.Updated,
comment.ID,
)
if err != nil {
tx.Rollback()
return err
}
tx.Commit()
return nil
}
func InsertAll(db sql.DB, locale string, themes string, keywords []domains.KeywordObj) {
fmt.Println("Need Insert ", len(keywords), "keywords")
tx, err := db.Begin()
if err != nil {
log.Fatal(err)
os.Exit(1)
}
timenow := int64(time.Now().UnixNano()) / 1000000
for _, keywordObj := range keywords {
stmt, err := tx.Prepare("insert into keywords('Locale','Themes','Keyword','Created','Updated','Cl','Lang') values(?,?,?,?,?,?,?)")
if err != nil {
log.Fatal(err)
}
defer stmt.Close()
if _, err = stmt.Exec(locale, themes, keywordObj.Keyword, timenow, timenow, keywordObj.Cl, keywordObj.Lang); err != nil {
log.Fatal(err)
os.Exit(1)
}
}
tx.Commit()
}
func InsertAll(db sql.DB, locale string, themes string, phrases []string) {
fmt.Println("Need Insert ", len(phrases), "phrases")
tx, err := db.Begin()
if err != nil {
log.Fatal(err)
os.Exit(1)
}
// timenow := int64(time.Now().Unix())*1000
timenow := int64(time.Now().UnixNano()) / 1000000
for _, phrase := range phrases {
// fmt.Println("Insert ", phrase)
stmt, err := tx.Prepare("insert into phrases('Locale','Themes','Phrase','Created','Updated') values(?,?,?,?,?)")
if err != nil {
log.Fatal(err)
}
defer stmt.Close()
if _, err = stmt.Exec(locale, themes, phrase, timenow, timenow); err != nil {
log.Fatal(err)
os.Exit(1)
}
}
tx.Commit()
}
func (this *Schema) Update(db *sql.DB) (err error) {
if this.statements == nil {
this.statements = make(map[StatementKey]*sql.Stmt, 0)
}
tx, err := db.Begin()
if err != nil {
return
}
for _, stmt := range this.AlterTables {
if _, err := db.Exec(stmt); err != nil {
return tx.Rollback()
}
}
for _, stmt := range this.UpdateIndexes {
if _, err := db.Exec(stmt); err != nil {
glog.Warningln(stmt, "err:", err)
}
}
err = update_schema_version(db, this)
if err != nil {
tx.Rollback()
return
}
return tx.Commit()
}
func (this *Schema) DropTables(db *sql.DB) error {
system, has := platform_schemas[this.Platform]
if !has {
panic(errors.New("no-systems-versions"))
}
var err error
tx, err := db.Begin()
if err != nil {
return err
}
for table, _ := range this.CreateTables {
_, err = db.Exec("drop table " + table)
if err != nil {
tx.Rollback()
return err
}
}
// remove from the systems info
err = system.Delete(db, kDeleteVersionInfo, this.Name)
if err != nil {
tx.Rollback()
return err
} else {
tx.Commit()
}
return err
}
func insertShooters(db *sql.DB, m *Match) {
txn, err := db.Begin()
if err != nil {
log.Fatal(err)
}
stmt, err := txn.Prepare(pq.CopyIn("users", "name", "countryCode", "mos", "flags"))
if err != nil {
log.Fatal(err)
}
for _, results := range m.Results {
for _, result := range results {
_, err = stmt.Exec(result.Name, result.CountryCode, result.MosNumber, result.Flags)
if err != nil {
log.Fatal(err)
}
}
}
_, err = stmt.Exec()
if err != nil {
log.Fatal(err)
}
err = stmt.Close()
if err != nil {
log.Fatal(err)
}
err = txn.Commit()
if err != nil {
log.Fatal(err)
}
}
func addDB(db *sql.DB, data book_data, edit bool) (id int64, err error) {
tx, err := db.Begin()
if err != nil {
return id, err
}
if edit {
stmt, err := tx.Prepare("update book set name=?, surname=?, town=? where id=?")
if err != nil {
return id, err
}
if _, err = stmt.Exec(data.Name, data.Surname, data.Town, data.ID); err != nil {
return id, err
}
tx.Commit()
return 0, nil
}
stmt, err := tx.Prepare("insert into book(name, surname, town) values(?, ?, ?)")
if err != nil {
return 0, err
}
res, err := stmt.Exec(data.Name, data.Surname, data.Town)
if err != nil {
return 0, err
}
tx.Commit()
id, err = res.LastInsertId()
if err != nil {
return 0, err
}
return id, nil
}
// snippetCommentCreate will create a new comment in the database
func snippetCommentCreate(db *sql.DB, comment *snippetComment) error {
var err error
tx, err := db.Begin()
if err != nil {
return err
}
comment.Created = UnixMilliseconds()
comment.HTML = markdownParse(comment.Markdown)
result, err := db.Exec(
"INSERT INTO snippet_comment VALUES (NULL,?,?,?,?,?,0)",
comment.SnippetID,
comment.Username,
comment.Markdown,
comment.HTML,
comment.Created,
)
if err != nil {
tx.Rollback()
return err
}
comment.ID, err = result.LastInsertId()
if err != nil {
tx.Rollback()
return err
}
tx.Commit()
return nil
}
// 503 "pkg-updated.nw"
func UpdatePackage(db *sql.DB, set_field string, set_value string, where_field string, where_value string, opts ...string) int {
var query string
tx, err := db.Begin()
if err != nil {
Logging(LOG_FATAL, "updatepackage", fmt.Sprint(err))
}
if len(opts) > 0 {
query = "UPDATE " + opts[0] + " SET " + set_field + " = ? WHERE " + where_field + " = ?"
} else {
query = "UPDATE packages SET " + set_field + " = ? WHERE " + where_field + " = ?"
}
Logging(LOG_DEBUG, "updatepackage", fmt.Sprintf("sql query: %s | Params: %s;%s", query, set_value, where_value))
stmt, err := tx.Prepare(query)
if err != nil {
Logging(LOG_ERROR, "updatepackage", fmt.Sprintf("Error: %v", err))
return -1
}
defer stmt.Close()
_, err = stmt.Exec(set_value, where_value)
if err != nil {
Logging(LOG_ERROR, "updatepackage", fmt.Sprintf("Error: %v", err))
return -1
}
tx.Commit()
return 0
}
func (migration Migration) apply(db *sql.DB, location string) error {
println(fmt.Sprintf("Applying migration %d - '%s'", migration.Number, migration.Description))
content, _ := ioutil.ReadFile(fmt.Sprintf("%s/%s", location, migration.File.Name()))
if len(content) == 0 {
return errors.New("Empty migrations file")
}
tx, err := db.Begin()
if err != nil {
return err
}
statements := strings.Split(string(content), ";")
for _, statement := range statements {
statement = strings.TrimSpace(statement)
if len(statement) > 0 {
_, err = tx.Exec(statement)
if err != nil {
tx.Rollback()
return err
}
}
}
_, err = tx.Exec("INSERT INTO db_versions (version, name, applied) VALUES (?, ?, NOW())", migration.Number, migration.Description)
if err != nil {
tx.Rollback()
return err
}
return tx.Commit()
}
func runCommandAndExpireLease(t *testing.T, clock *hlc.Clock, sqlDB *gosql.DB, sql string) {
// Run a transaction that lets its table lease expire.
txn, err := sqlDB.Begin()
if err != nil {
t.Fatal(err)
}
// Use snapshot isolation so that the transaction is pushed without being
// restarted.
if _, err := txn.Exec("SET TRANSACTION ISOLATION LEVEL SNAPSHOT"); err != nil {
t.Fatal(err)
}
if _, err := txn.Exec(sql); err != nil {
t.Fatal(err)
}
// Update the clock to expire the table lease.
_ = clock.Update(clock.Now().Add(int64(2*csql.LeaseDuration), 0))
// Run another transaction that pushes the above transaction.
if _, err := sqlDB.Query("SELECT * FROM t.kv"); err != nil {
t.Fatal(err)
}
// Commit and see the aborted txn.
if err := txn.Commit(); !testutils.IsError(err, "pq: restart transaction: txn aborted") {
t.Fatalf("%s, err = %s", sql, err)
}
}
func createDemoTable(db *sql.DB) error {
tx, err := db.Begin()
if err != nil {
return fmt.Errorf("Begin: %s", err)
}
statement, err := tx.Prepare(`
CREATE TABLE demo (
id INTEGER PRIMARY KEY,
col1 VARCHAR(255)
)
`)
if err != nil {
return err
}
_, err = statement.Exec()
if err != nil {
return err
}
err = tx.Commit()
if err != nil {
return fmt.Errorf("Commit: %s", err)
}
return nil
}
func UpdateAll(db sql.DB, locale string, themes string, phrases []string) {
fmt.Println("Need Update ", len(phrases), "phrases")
tx, err := db.Begin()
if err != nil {
log.Fatal(err)
os.Exit(1)
}
// timenow := int64(time.Now().Unix())*1000
timenow := int64(time.Now().UnixNano()) / 1000000
for _, phrase := range phrases {
fmt.Println(phrase)
stmt, err := tx.Prepare("update phrases set Hits=Hits+1,Updated=? where phrase=?")
if err != nil {
log.Fatal(err)
}
defer stmt.Close()
if _, err = stmt.Exec(timenow, phrase); err != nil {
log.Fatal(err)
os.Exit(1)
}
}
tx.Commit()
}
func reguser(db *sql.DB, me *user.User) int {
tx, err := db.Begin()
if err != nil {
log.Println(err)
return 1
}
req := `
insert into users(id, username, name)
values(?, ?, ?)
`
stmt, err := tx.Prepare(req)
if err != nil {
log.Println(err)
return 1
}
_, err = stmt.Exec(me.Uid, me.Username, me.Name)
stmt.Close()
tx.Commit()
// don't log anything here, it just means the user already exits
if err != nil {
return 1
}
return 0
}
func execQuery(query string, args *[]interface{}, db *sql.DB) (*ExecResponse, error) {
resp := new(ExecResponse)
tx, err := db.Begin()
if err != nil {
return nil, err
}
defer tx.Commit()
stmt, err := tx.Prepare(query)
if err != nil {
return nil, err
}
defer stmt.Close()
res, err := stmt.Exec(*args...)
if err != nil {
return nil, err
}
lastId, err := res.LastInsertId()
if err != nil {
return nil, err
}
rowCnt, err := res.RowsAffected()
if err != nil {
return nil, err
}
// log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)
resp.lastId = lastId
resp.rowCount = rowCnt
return resp, nil
}
// Delete pad from db, also delete related tags and cooperators
func (pad *Pad) Delete(db *sql.DB) (err error) {
tx, err := db.Begin()
if err != nil {
return
}
defer tx.Rollback()
// delete tags
delTag := tx.Stmt(deletePadTagQuery)
if _, err = delTag.Exec(pad.ID); err != nil {
return
}
// delete coops
delCoop := tx.Stmt(deletePadCoopQuery)
if _, err = delCoop.Exec(pad.ID); err != nil {
return
}
// delete pad
delPad := tx.Stmt(deletePadQuery)
if _, err = delPad.Exec(pad.ID); err == nil {
pad.ID = 0
tx.Commit()
}
return
}
func bulkDirect(db *sql.DB) error {
tx, err := db.Begin()
if err != nil {
return err
}
stmt, err := tx.Prepare(pq.CopyIn("foo", "id", "val"))
if err != nil {
tx.Rollback()
return err
}
// Build up the COPY FROM by repeated Exec calls
for i := 0; i < *Count; i++ {
val := *Start + i
strVal := fmt.Sprintf("%07d", val)
if _, err := stmt.Exec(val, strVal); err != nil {
fmt.Printf("error while building up COPY: %s\n", err)
tx.Rollback()
return err
}
}
// Flush the COPY FROM to postgres
if _, err := stmt.Exec(); err != nil {
fmt.Printf("error while copying data: %s\n", err)
return err
}
if err := stmt.Close(); err != nil {
fmt.Printf("error during stmt.Close(): %s\n", err)
return err
}
if err := tx.Commit(); err != nil {
fmt.Printf("could not commit transaction: %s\n", err)
return err
}
return nil
}
// Migrate runs the indicated SQL migration files against the database.
//
// Any files that don't have entries in the schema_migrations table will be
// run to bring the database to the indicated version. If the schema_migrations
// table does not exist, this function will automatically create it.
//
// Indicate the version to roll towards, either forwards or backwards
// (rollback). By default we roll forwards to the current time, i.e. run all
// the migrations.
//
// If check is true, won't actually run the migrations against Cassandra.
// Instead just simulate the run and report on what would be migrated.
//
// Returns a list of the migrations that were successfully completed, or an
// error if there are problems with the migration. It is possible for this
// function to return both successful migrations and an error, if some of the
// migrations succeed before an error is encountered.
func Migrate(db *sql.DB, directory string, version int) error {
if err := CreateSchemaMigrations(db); err != nil {
return err
}
direction := Moving(db, version)
migrations, err := Available(directory, direction)
if err != nil {
return err
}
for _, migration := range migrations {
path := fmt.Sprintf("%s%c%s", directory, os.PathSeparator, migration)
tx, err := db.Begin()
if err != nil {
return err
}
if ShouldRun(tx, path, direction, version) {
glog.Infof("Running migration %s %s", path, direction)
if err = Run(tx, path, direction); err != nil {
tx.Rollback()
return err
}
}
if err = tx.Commit(); err != nil {
return err
}
}
return nil
}
func replace(oldID, newID string, db *sql.DB) {
tx, err := db.Begin()
if err != nil {
fmt.Fprintf(os.Stderr, "TX Begin: %v\n", err.Error())
os.Exit(1)
}
_, err = tx.Exec("DELETE FROM payloads SET WHERE id=?", oldID)
if err != nil {
tx.Rollback()
fmt.Fprintf(os.Stderr, "TX DELETE payloads: %v\n", err.Error())
os.Exit(1)
}
_, err = tx.Exec("UPDATE channel_payload_rel SET payload=? WHERE payload=?", newID, oldID)
if err != nil {
tx.Rollback()
fmt.Fprintf(os.Stderr, "TX UPDATE channel_payload_rel: %v\n", err.Error())
os.Exit(1)
}
err = tx.Commit()
if err != nil {
tx.Rollback()
fmt.Fprintf(os.Stderr, "TX COMMIT: %v\n", err.Error())
os.Exit(1)
}
filePath := path.Join("storage", oldID)
err = os.Remove(filePath)
if err != nil {
fmt.Fprintf(os.Stderr, "os.Remove(\"%v\"): %v\n", filePath, err.Error())
os.Exit(1)
}
}
func addDBph(db *sql.DB, data []phone, edit bool) (err error) {
tx, err := db.Begin()
if err != nil {
return err
}
if edit {
stmt, err := tx.Prepare("update phone_table set type=?, number=? where id=?")
if err != nil {
return err
}
for _, value := range data {
if _, err := stmt.Exec(value.Type_n, value.Number, value.ID); err != nil {
return err
}
}
tx.Commit()
return nil
}
stmt, err := tx.Prepare("insert into phone_table( type, number, id_b) values(?, ?, ?)")
if err != nil {
return err
}
for _, value := range data {
_, err := stmt.Exec(value.Type_n, value.Number, value.ID_b)
if err != nil {
return err
}
}
tx.Commit()
return nil
}
// WithTransaction begins a transaction on the given database connection, calls
// 'f' with the transaction as a parameter, then ensures the transaction is
// committed if 'f' completes successfully or rolled back in the event of an
// error. If an error occurs when committing or rolling back the transaction, a
// 'FailedCloseError' is returned.
func WithTransaction(db *sql.DB, f WithTransactionFunc) (err error) {
// Begin transaction
tx, err := db.Begin()
if err != nil {
return err
}
// Run the passed function
err = f(tx)
var closeErr error
var what string
// Commit or rollback the transaction
if err != nil {
closeErr = tx.Rollback()
what = "roll back transaction"
} else {
closeErr = tx.Commit()
what = "commit transaction"
}
// If committing/rolling back the transaction caused an error, return a
// FailedCloseError
if closeErr != nil {
err = &FailedCloseError{what, closeErr, err}
}
return err
}
// snippetDelete permanently removes a snippet
func snippetDelete(db *sql.DB, id string) error {
queries := []string{
"DELETE FROM snippet WHERE snippet_id=?",
"DELETE FROM snippet_comment WHERE snippet_id=?",
"DELETE FROM snippet_file WHERE snippet_id=?",
"DELETE FROM snippet_view WHERE snippet_id=?",
}
tx, err := db.Begin()
if err != nil {
return err
}
for _, q := range queries {
_, err = tx.Exec(q, id)
if err != nil {
tx.Rollback()
return err
}
}
searchId, _ := FromBase36(Reverse(id))
_, err = tx.Exec(
"DELETE FROM snippet_search WHERE docid=?",
searchId,
)
tx.Commit()
repoDelete(id)
return nil
}