Golang DB.Begin方法代碼示例

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
}