Golang Tx.Exec方法代碼示例

// 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
}