func SaveAttrs(log *logging.Logger, tx *sql.Tx, section *common.Section, metadata common.JSONFile) error {
var stmt *sql.Stmt
var err error
var insert = "INSERT INTO attrs" +
"(area, grace, dataset, element, os, username, groupname, type," +
" link, mtime, ctime, hash, perms, compressed)" +
" VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)"
stmt, err = tx.Prepare(insert)
if err != nil {
log.Debug("Failed prepare: " + err.Error())
return errorMsg(log, 3, "Cannot save Attributes for element "+section.Name)
}
_, err = stmt.Exec(section.Name, section.Grace, section.Dataset, metadata.Name,
metadata.Os, metadata.User, metadata.Group, metadata.Type, metadata.Link,
metadata.Mtime, metadata.Ctime, metadata.Hash, metadata.Mode, section.Compressed)
if err != nil {
log.Debug("Failed execute: " + err.Error())
return errorMsg(log, 4, "Cannot save Attributes for element "+section.Name)
}
stmt.Close()
return nil
}
func SetEmailTaskDone(emailTask *EmailTask) bool {
var db *sql.DB
var stmt *sql.Stmt
var err error
LogError := func() {
seelog.Errorf("[SetEmailTaskDone Failed] [EmailTask : %v] [ERROR : %v]", *emailTask, err)
}
if db, err = GetDBConnection(); err != nil {
LogError()
return false
}
defer db.Close()
if stmt, err = db.Prepare("UPDATE `email_request` SET `is_done` = 1 WHERE `id` = ?"); err != nil {
LogError()
return false
}
defer stmt.Close()
if _, err = stmt.Exec(emailTask.Id); err != nil {
LogError()
return false
}
return true
}
func ExecuteImpInsert(stmt *sql.Stmt, var_imp map[string]interface{}) error {
var err error
if _, ok := var_imp["pred"].(float64); ok {
_, err = stmt.Exec(var_imp["GLB_OE_ID"].(float64),
var_imp["CLM_BUS_ID"].(string),
var_imp["MODEL"].(string),
var_imp["MODEL_DESC"].(string),
var_imp["VAR"].(string),
var_imp["VALUE"].(string),
var_imp["WEIGHT"].(string),
var_imp["RANK"].(float64),
var_imp["pred"].(float64),
var_imp["CREATE_DT_TS"].(float64))
} else {
_, err = stmt.Exec(var_imp["GLB_OE_ID"].(float64),
var_imp["CLM_BUS_ID"].(string),
var_imp["MODEL"].(string),
var_imp["MODEL_DESC"].(string),
var_imp["VAR"].(string),
var_imp["VALUE"].(string),
nil,
nil,
nil,
var_imp["CREATE_DT_TS"].(float64))
}
check(err)
return err
}
func (follow Follow) Persist() error {
var stmtIns *sql.Stmt
var err error
if follow.id == 0 {
stmtIns, err = database.Prepare("INSERT INTO " + _TABLE_FOLLOW + "(userId, userName, status, followDate, unfollowDate, lastAction) VALUES( ?, ?, ?, ?, ? ,?)")
} else {
stmtIns, err = database.Prepare("UPDATE " + _TABLE_FOLLOW + " SET userId = ?, userName = ?, status = ?, followDate = ?, unfollowDate = ?, lastAction = ? WHERE id = ?")
}
if err != nil {
return err
}
defer stmtIns.Close()
unfollowDate := mysql.NullTime{Time: follow.UnfollowDate, Valid: !follow.UnfollowDate.IsZero()}
if follow.id == 0 {
_, err = stmtIns.Exec(follow.UserId, follow.UserName, follow.Status, follow.FollowDate, unfollowDate, time.Now())
} else {
_, err = stmtIns.Exec(follow.UserId, follow.UserName, follow.Status, follow.FollowDate, unfollowDate, follow.LastAction, follow.id)
}
return err
}
// Exec executes a prepared statement with the given arguments and returns a Result
// summarizing the effect of the statement.
//
// Exec prepares the same statement on another connection and executes it
func (s *Stmt) Exec(ctx context.Context, args ...interface{}) (sql.Result, error) {
if s.err != nil {
return nil, s.err
}
done := make(chan struct{}, 0)
var res sql.Result
var err error
f := func(sqldb *sql.DB) {
defer close(done)
var stmt *sql.Stmt
stmt, err = sqldb.Prepare(s.query)
if err != nil {
return
}
res, err = stmt.Exec(args...)
}
if opErr := s.db.process(ctx, f, done); opErr != nil {
return nil, opErr
}
return res, err
}
/* **
执行删除操作
-----------------------
param1: 要操作的数据库表
param2: 条件语句，当没有条件时填空
param3: 对应条件传的参数
return 成功返回nil，失败返回error
** */
func (self *MDB) execDelete(tableName, condition string, args ...interface{}) error {
if self == nil {
return errors.New("数据库句柄错误。")
}
if tableName == "" {
return errors.New("数据库表名不能为空。")
}
tx, err := self.Begin()
if err != nil {
return err
}
var stmt *sql.Stmt
if condition == "" || len(args) == 0 {
stmt, err = tx.Prepare(fmt.Sprintf("DELETE FROM %s", tableName))
} else {
stmt, err = tx.Prepare(fmt.Sprintf("DELETE FROM %s WHERE %s", tableName, condition))
}
if err != nil {
return err
}
if _, err = stmt.Exec(args...); err != nil {
return err
}
defer stmt.Close()
if err = tx.Commit(); err != nil {
// 回滚？？？？用得着么
tx.Rollback()
return err
}
return nil
}
func RawInsert(b *B) {
var m *Model
var stmt *sql.Stmt
wrapExecute(b, func() {
var err error
initDB()
m = NewModel()
stmt, err = raw.Prepare(rawInsertSQL)
if err != nil {
fmt.Println(err)
b.FailNow()
}
})
defer stmt.Close()
for i := 0; i < b.N; i++ {
res, err := stmt.Exec(m.Name, m.Title, m.Fax, m.Web, m.Age, m.Right, m.Counter)
if err != nil {
fmt.Println(err)
b.FailNow()
}
id, err := res.LastInsertId()
m.Id = int(id)
if err != nil {
fmt.Println(err)
b.FailNow()
}
}
}
func (gkDbCon *GkDbConDef) AddNewUser(userName string, passwordHash string, passwordSalt string, email string) *gkerr.GkErrDef {
var stmt *sql.Stmt
var err error
var id int64
var gkErr *gkerr.GkErrDef
id, gkErr = gkDbCon.getNextUsersId()
if gkErr != nil {
return gkErr
}
stmt, err = gkDbCon.sqlDb.Prepare("insert into users (id, user_name, password_hash, password_salt, email, account_creation_date, last_login_date) values ($1, $2, $3, $4, $5, $6, $7)")
if err != nil {
return gkerr.GenGkErr("stmt.Prepare"+getDatabaseErrorMessage(err), err, ERROR_ID_PREPARE)
}
defer stmt.Close()
var accountCreationDate time.Time = time.Now()
var lastLoginDate time.Time = time.Now()
_, err = stmt.Exec(id, userName, passwordHash, passwordSalt, email, accountCreationDate, lastLoginDate)
if err != nil {
if isUniqueViolation(err) {
return gkerr.GenGkErr("stmt.Exec unique violation", err, ERROR_ID_UNIQUE_VIOLATION)
}
return gkerr.GenGkErr("stmt.Exec"+getDatabaseErrorMessage(err), err, ERROR_ID_EXECUTE)
}
return nil
}
func (p *TxScope) ExecStmt(stmt *sql.Stmt, args ...interface{}) int64 {
if r, err := stmt.Exec(args...); err != nil {
panic(err)
} else {
return p.getAffectedRows(r)
}
}
func (fav Favorite) Persist() error {
var stmtIns *sql.Stmt
var err error
if fav.id == 0 {
stmtIns, err = database.Prepare("INSERT INTO " + _TABLE_FAVORITE + "(userId, userName, tweetId, status, favDate, unfavDate, lastAction) VALUES( ?, ?, ?, ?, ?, ?, ?)")
} else {
stmtIns, err = database.Prepare("UPDATE " + _TABLE_FAVORITE + " SET userId = ?, userName = ?, tweetId = ?, status = ?, favDate = ?, unfavDate = ?, lastAction = ? WHERE id = ?")
}
if err != nil {
return err
}
defer stmtIns.Close()
unfavDate := mysql.NullTime{Time: fav.UnfavDate, Valid: !fav.UnfavDate.IsZero()}
if fav.id == 0 {
_, err = stmtIns.Exec(fav.UserId, fav.UserName, fav.TweetId, fav.Status, fav.FavDate, unfavDate, time.Now())
} else {
_, err = stmtIns.Exec(fav.UserId, fav.UserName, fav.TweetId, fav.Status, fav.FavDate, unfavDate, fav.LastAction, fav.id)
}
return err
}
// Delete will delete all of the configurations whose name is in the list
// of names in the arugment. It will not return an error if the name is not found.
func (cc *ConfigurationController) Delete(names ...string) (err error) {
var (
tx *sql.Tx
stmt *sql.Stmt
)
tx, err = cc.DB.Begin()
if err != nil {
tx.Rollback()
return err
}
stmt, err = tx.Prepare("DELETE FROM configurations where config_name = $1")
if err != nil {
tx.Rollback()
return err
}
for _, name := range names {
_, err := stmt.Exec(name)
if err == sql.ErrNoRows {
err = nil
}
if err != nil {
tx.Rollback()
return err
}
}
return tx.Commit()
}
func (c *Comment) Save(db *sql.DB) error {
var stmt *sql.Stmt
var err error
if c.Id != 0 {
stmt, err = db.Prepare(`
UPDATE comments SET
Email = ?, Name = ?, Body = ?, Created = ?, Url = ?, ClientIp = ?, Approved = ? WHERE Id = ?`)
if err != nil {
return err
}
_, err = stmt.Exec(c.Email, c.Name, c.Body, c.Created, c.Url, c.ClientIp, c.Approved, c.Id)
} else {
stmt, err = db.Prepare(`
INSERT INTO
comments(Email, Name, Body, Created, Url, ClientIp, Approved)
VALUES(?, ?, ?, ?, ?, ?, ?)`)
if err != nil {
return err
}
res, err := stmt.Exec(c.Email, c.Name, c.Body, c.Created, c.Url, c.ClientIp, c.Approved)
if err != nil {
return err
}
lastId, err := res.LastInsertId()
if err != nil {
return err
}
c.Id = lastId
}
if err != nil {
return err
}
return nil
}
func (self *Params) Delete() (res sql.Result, err error) {
db, query := self.getWriteConnect()
sqls, val := query.Delete()
if debug_sql {
Debug.Println("delete ", sqls, val)
}
if OpenSyncDelete {
sqls = strings.Replace(sqls, "?", "%v", -1)
sqls = fmt.Sprintf(sqls, val...)
SqlSyncHook <- sqls
return nil, nil
} else {
var stmt *sql.Stmt
stmt, err = db.Prepare(sqls)
if err == nil {
defer stmt.Close()
} else {
Error.Println(err)
return
}
res, err = stmt.Exec(val...)
if err != nil {
Error.Println(err)
}
}
return
}
// Delete executes DELETE FROM value.TableName where .Where()
// Calling .Where is mandatory. You can pass a nil pointer to value if you just setted
// the tablename with Model.
func (db *Database) Delete(value DBModel) error {
defer db.clear()
// if Model has been called, skip table name inference procedure
if len(db.tables) == 0 {
db.tables = append(db.tables, handleIdentifier(value.TableName()))
db.models = append(db.models, value)
}
// If where is empty, try to infer a primary key by value
// otherwise buildDelete panics (where is mandatory)
db = db.Where(value)
// Compile query
var stmt *sql.Stmt
var err error
if stmt, err = db.db.Prepare(db.buildDelete()); err != nil {
return err
}
// Pass query parameters and executes the query
if _, err = stmt.Exec(db.whereValues...); err != nil {
return err
}
// Clear fields of value after delete, because the object no more exists
value = reflect.Zero(reflect.ValueOf(value).Type()).Interface().(DBModel)
return nil
}
func SaveAcls(log *logging.Logger, tx *sql.Tx, section *common.Section, element string, acls []common.JSONFileAcl) error {
var acl common.JSONFileAcl
var stmt *sql.Stmt
var err error
var insert = "INSERT INTO acls" +
"(area, grace, dataset, element, name, type, perms)" +
" VALUES($1, $2, $3, $4, $5, $6, $7)"
stmt, err = tx.Prepare(insert)
if err != nil {
log.Debug("Failed prepare: " + err.Error())
return errorMsg(log, 1, "Cannot save ACLs for element "+section.Name)
}
for _, acl = range acls {
if acl.User != "" {
_, err = stmt.Exec(section.Name, section.Grace, section.Dataset, element,
acl.User, "user", acl.Mode)
} else {
_, err = stmt.Exec(section.Name, section.Grace, section.Dataset, element,
acl.Group, "group", acl.Mode)
}
if err != nil {
log.Debug("Failed execute: " + err.Error())
return errorMsg(log, 2, "Cannot save ACLs for element "+section.Name)
}
}
stmt.Close()
return nil
}
func (dbSeq *SequenceDB) NextSequence() (sequence uint64, err error) {
var stmt *sql.Stmt
stmt, err = dbSeq.db.Prepare(`REPLACE INTO sequence(stub) VALUES ("sequence")`)
if err != nil {
log.Printf("sequence db prepare error. %v", err)
return 0, err
}
defer stmt.Close()
var res sql.Result
res, err = stmt.Exec()
if err != nil {
log.Printf("sequence db replace into error. %v", err)
return 0, err
}
// 兼容LastInsertId方法的返回值
var lastID int64
lastID, err = res.LastInsertId()
if err != nil {
log.Printf("sequence db get LastInsertId error. %v", err)
return 0, err
} else {
sequence = uint64(lastID)
// mysql sequence will start at 1, we actually want it to be
// started at 0. :)
sequence -= 1
return sequence, nil
}
}
// DBID is the ID of the correspondig metadata record
func (conn *watcherdb) ProtocollCheck(id database.DBID, isnew bool, messages []ogdat.CheckMessage) error {
// This is append only; revise later if it should also delete or update entries.
const insstmt = "INSERT INTO status(datasetid, field_id, status, fieldstatus, reason_text, hittime) VALUES ($1, $2, $3, $4, $5, $6)"
var stmt *sql.Stmt
var err error
if stmt, err = conn.Prepare(insstmt); err != nil {
return err
}
// get time here and not within the loop so we have a grouping possibilitiy
t := time.Now().UTC()
var status string
for _, msg := range messages {
switch {
case (msg.Type & ogdat.Error) != 0:
status = "error"
case (msg.Type & ogdat.Warning) != 0:
status = "warning"
case (msg.Type & ogdat.Info) != 0:
status = "info"
}
if _, err = stmt.Exec(id, msg.OGDID, status, msg.Type, msg.Text, t); err != nil {
return fmt.Errorf("Error inserting status for datasetid %d, fieldid %d: %s", id, msg.OGDID, err)
}
}
return nil
}
//Update ...
func (vs VictualService) Update(victual *Victual) *Victual {
db, err := sql.Open("postgres", "host=localhost port=5432 user=pober dbname=pober sslmode=disable")
checkErr(err)
defer db.Close()
var statement *sql.Stmt
statement, err = db.Prepare("UPDATE dc.victual SET description = $1, brand = $2, source = $3, energyCal = $4, energyJoule = $5, fat = $6, saturatedFat = $7, carbohydrates = $8, sugars = $9, protein = $10, salt = $11, fiber = $12, barCode = $13, weightVolumeRatio = $14 WHERE ID = $1")
checkErr(err)
var result sql.Result
result, err = statement.Exec(victual.ID,
victual.Description,
victual.Brand,
victual.Source,
victual.EnergyCal,
victual.EnergyJoule,
victual.Fat,
victual.SaturatedFat,
victual.Carbohydrates,
victual.Sugars,
victual.Protein,
victual.Salt,
victual.Fiber,
victual.BarCode,
victual.WeightVolumeRatio)
checkErr(err)
var rowsAffected int64
rowsAffected, err = result.RowsAffected()
checkErr(err)
statement.Close()
if rowsAffected == 1 {
return victual
}
return nil
}
func (conn DBConn) HeartBeat() error {
const (
updatestmt = "UPDATE heartbeat SET ts=$1 WHERE who=$2 AND sysid=$3"
insertstmt = "INSERT INTO heartbeat(ts, statuscode, statustext, who) VALUES($1, 0, 'Alive', $2)"
)
var hbstatement *sql.Stmt
var sysid DBID
err := conn.QueryRow("SELECT asi.sysid FROM (SELECT sysid, ts, who, MAX(ts) OVER (PARTITION BY who) max_ts FROM heartbeat) asi WHERE asi.ts = max_ts AND who=$1", conn.Appid).Scan(&sysid)
switch {
case err == sql.ErrNoRows:
hbstatement, err = conn.Prepare(insertstmt)
_, err = hbstatement.Exec(time.Now().UTC(), conn.Appid)
case err != nil:
return fmt.Errorf("Error heartbeating database: %s", err)
default:
hbstatement, err = conn.Prepare(updatestmt)
_, err = hbstatement.Exec(time.Now().UTC(), conn.Appid, sysid)
}
defer hbstatement.Close()
if err != nil {
return fmt.Errorf("Error executing heartbeat: %s", err)
}
return nil
}
func doInitialize(db *sql.DB) error {
var stmt *sql.Stmt
var err error
stmt, err = db.Prepare(initialStatement)
if err != nil {
fmt.Printf("db.Prepare initializing error: %v\n", err)
return err
}
_, err = stmt.Exec()
if err != nil {
fmt.Printf("stmt.Exec error: %v\n", err)
return err
}
defer stmt.Close()
stmt, err = db.Prepare("INSERT INTO contacts(firstname,lastname) VALUES($1, $2)")
if err != nil {
fmt.Printf("stmt.Prepare error: %v\n", err)
}
_, err = stmt.Exec("John", "Smith")
if err != nil {
fmt.Printf("stmt.Exec error: %v\n", err)
}
return nil
}
func parseFileFnc(fName string, stmt *sql.Stmt, wg *sync.WaitGroup) {
defer wg.Done()
fmt.Println("parsing file", fName)
file, err := os.Open(fName)
if err != nil {
log.Fatal(err)
}
defer file.Close()
reader := csv.NewReader(file)
reader.Comma = '\t'
for {
//parse out into needed fields
record, err := reader.Read()
if err == io.EOF {
break
} else if err != nil {
log.Fatal(err)
}
//fmt.Println(record[0], record[1])
_, err = stmt.Exec(record[0], record[2], fName)
if err != nil {
log.Fatal(err)
}
}
}
func (d *DBAlias) BatchExec(sqlbuf string, argsv [][]interface{}) (affectedRows int64, err error) {
var result sql.Result
if argsv == nil || len(argsv) == 0 {
result, err = d.db.Exec(sqlbuf)
if err == nil {
affectedRows, err = result.RowsAffected()
}
return
} else {
var stmt *sql.Stmt
stmt, err = d.db.Prepare(sqlbuf)
if err != nil {
return
}
defer stmt.Close()
affectedRows = 0
for i, _ := range argsv {
r, err1 := stmt.Exec(argsv[i]...)
if err1 != nil {
err = err1
return
}
num, _ := r.RowsAffected()
affectedRows += num
}
}
return
}
func (u *BufferedUpdates) Flush(sessionId int, stream *sql.Stmt) {
if len(u.Entities) == 0 {
return
}
u.WG.Add(1)
go func(entities EntityPropMap) {
defer u.WG.Done()
for index, props := range entities {
for prop, update := range props {
jsonValue, err := json.Marshal(update.Value)
if err != nil {
log.Fatal(err)
}
_, err = stream.Exec(sessionId, index, update.Tick, prop, string(jsonValue))
if err != nil {
log.Fatal(err)
}
}
}
}(u.Entities)
u.Entities = make(EntityPropMap)
}
func insertSalariesRows(stmt *sql.Stmt, rows []map[string]interface{}) (i int, err error) {
for i = 0; i < len(rows); i++ {
if _, err = stmt.Exec(rows[i]["emp_no"], rows[i]["salary"]); err != nil {
return i, err
}
}
return i, err
}
func insertDepartmentRows(stmt *sql.Stmt, rows []map[string]interface{}) (i int, err error) {
for i = 0; i < len(rows); i++ {
if _, err = stmt.Exec(rows[i]["dept_no"], rows[i]["deptName"]); err != nil {
return i, err
}
}
return i, err
}
func insertEmployeeRows(stmt *sql.Stmt, rows []map[string]interface{}) (i int, err error) {
for i = 0; i < 1000000; i++ {
if _, err = stmt.Exec(100, "firstName", "lastName"); err != nil {
return i, err
}
}
return i, err
}
func insertProbeResult(stmt *sql.Stmt, result *types.ProbeResult) {
src := utils.IPToIntString(result.Src)
dest := utils.IPToIntString(result.Dest)
_, e := stmt.Exec(src, dest, result.Delay, result.Stamp, result.Type)
if e != nil {
log.Println(e)
}
}
func execute(stmt *sql.Stmt, args []interface{}) (sql.Result, error) {
res, err := stmt.Exec(args...)
if err != nil {
return nil, err
}
return res, nil
}
//--------------------------------------------------------------------- update operations (del , update )
func excute(stmt *sql.Stmt, args ...interface{}) (int64, error) {
res, err := stmt.Exec(args...)
if nil != err {
return -1, err
}
return res.RowsAffected()
}
func execUpdate(m *model.Group, stmt *sql.Stmt) error {
_, err := stmt.Exec(
m.Name,
m.UserUID,
m.UID,
)
return err
}
