Example #1
0
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
}
Example #2
0
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())
	}
}
Example #4
0
// 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
}
Example #6
0
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()
}
Example #7
0
// 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
}
Example #8
0
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
}
Example #9
0
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
}
Example #11
0
// 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
}
Example #12
0
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
}
Example #13
0
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
}
Example #14
0
File: schema.go Project: fd/simplex
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
}
Example #15
0
// 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
}
Example #16
0
//	处理分时数据
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
}
Example #17
0
// 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
}
Example #18
0
//	保存错误信息
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
}
Example #19
0
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
}
Example #20
0
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()
}
Example #21
0
// 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
}
Example #22
0
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
}
Example #23
0
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
}
Example #24
0
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
}
Example #25
0
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
}
Example #26
0
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
}
Example #27
0
// 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()
}
Example #28
0
func tryRollback(lbl Label, tx *sql.Tx, err error) (Label, error) {
	rbErr := tx.Rollback()
	if rbErr != nil {
		return lbl, rbErr
	}
	return lbl, err
}
Example #29
0
// 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)
	}
}