Golang sql.Tx類代碼示例

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
}