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
}
func getCustomerPrice(personId int, productId int, number int) ([]*model.CustomerPrice, error) {
var conn *sql.DB
var stmt *sql.Stmt
var err error
if conn, err = db.Connect(); err != nil {
return nil, err
}
defer conn.Close()
_sql := "select * from customer_special_price where person_id = ? and product_id = ? order by create_time DESC limit ?"
if stmt, err = conn.Prepare(_sql); err != nil {
return nil, err
}
defer stmt.Close()
rows, err := stmt.Query(personId, productId, number)
if err != nil {
return nil, err
}
defer rows.Close()
models := []*model.CustomerPrice{}
for rows.Next() {
m := new(model.CustomerPrice)
var blackhole sql.NullInt64
err := rows.Scan(&m.Id, &m.PersonId, &m.ProductId, &m.Price, &m.CreateTime, &blackhole /*&m.LastUsedTime */)
if err != nil {
panic(err)
}
models = append(models, m)
}
return models, nil
}
func (gkDbCon *GkDbConDef) GetPodsList() ([]DbPodDef, *gkerr.GkErrDef) {
var stmt *sql.Stmt
var err error
var dbPodsList []DbPodDef = make([]DbPodDef, 0, 0)
stmt, err = gkDbCon.sqlDb.Prepare("select id, pod_title from pods")
if err != nil {
return nil, gkerr.GenGkErr("sql.Prepare"+getDatabaseErrorMessage(err), err, ERROR_ID_PREPARE)
}
defer stmt.Close()
var rows *sql.Rows
rows, err = stmt.Query()
if err != nil {
return nil, gkerr.GenGkErr("stmt.Query"+getDatabaseErrorMessage(err), err, ERROR_ID_QUERY)
}
defer rows.Close()
for rows.Next() {
var dbPod DbPodDef
err = rows.Scan(&dbPod.Id, &dbPod.Title)
if err != nil {
return nil, gkerr.GenGkErr("rows.Scan"+getDatabaseErrorMessage(err), err, ERROR_ID_ROWS_SCAN)
}
dbPodsList = append(dbPodsList, dbPod)
}
return dbPodsList, 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
}
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 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 RawRead(b *B) {
var m *Model
var stmt *sql.Stmt
wrapExecute(b, func() {
var err error
initDB()
m = NewModel()
rawInsert(m)
stmt, err = raw.Prepare(rawSelectSQL)
if err != nil {
fmt.Println(err)
b.FailNow()
}
})
defer stmt.Close()
for i := 0; i < b.N; i++ {
var mout Model
err := stmt.QueryRow(m.Id).Scan(
&mout.Id,
&mout.Name,
&mout.Title,
&mout.Fax,
&mout.Web,
&mout.Age,
&mout.Right,
&mout.Counter,
)
if err != nil {
fmt.Println(err)
b.FailNow()
}
}
}
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
}
func sexDomain(dom string) (sexAnswer bool, err error) {
var stmt *sql.Stmt
stmt, err = db.Prepare(`SELECT dom FROM domains WHERE dom = $1 AND sex = true LIMIT 1`)
if err != nil {
fmt.Printf("db.Prepare error: %v\n", err)
return false, err
}
var rows *sql.Rows
rows, err = stmt.Query(dom)
if err != nil {
fmt.Printf("stmt.Query error: %v\n", err)
return false, err
}
defer stmt.Close()
if rows.Next() {
var domain string
err = rows.Scan(&domain)
if err != nil {
fmt.Printf("rows.Scan error: %v\n", err)
return false, err
}
if configuration.Debug == "Yes" {
fmt.Println("domain => \"" + domain + "\"")
}
return true, err
}
return false, nil
}
/* **
执行删除操作
-----------------------
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 (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
}
}
// no error return if row is not found
func (gkDbCon *GkDbConDef) GetContextUser(userName string) (*DbContextUserDef, bool, *gkerr.GkErrDef) {
var stmt *sql.Stmt
var err error
var dbContextUser *DbContextUserDef = new(DbContextUserDef)
var rowFound bool
stmt, err = gkDbCon.sqlDb.Prepare("select context_users.id, context_users.last_position_x, context_users.last_position_y, context_users.last_position_z, context_users.last_pod from users, context_users where users.id = context_users.id and users.user_name = $1")
if err != nil {
return nil, false, gkerr.GenGkErr("sql.Prepare"+getDatabaseErrorMessage(err), err, ERROR_ID_PREPARE)
}
defer stmt.Close()
var rows *sql.Rows
rows, err = stmt.Query(userName)
if err != nil {
return nil, false, gkerr.GenGkErr("stmt.Query"+getDatabaseErrorMessage(err), err, ERROR_ID_QUERY)
}
defer rows.Close()
if rows.Next() {
err = rows.Scan(&dbContextUser.id, &dbContextUser.LastPositionX, &dbContextUser.LastPositionY, &dbContextUser.LastPositionZ, &dbContextUser.LastPod)
if err != nil {
return nil, false, gkerr.GenGkErr("rows.Scan"+getDatabaseErrorMessage(err), err, ERROR_ID_ROWS_SCAN)
}
rowFound = true
} else {
rowFound = false
}
return dbContextUser, rowFound, nil
}
// return error if row not found
func (gkDbCon *GkDbConDef) GetUser(userName string) (*DbUserDef, *gkerr.GkErrDef) {
var stmt *sql.Stmt
var err error
var dbUser *DbUserDef = new(DbUserDef)
stmt, err = gkDbCon.sqlDb.Prepare("select id, user_name, password_hash, password_salt, email, account_creation_date, last_login_date from users where user_name = $1")
if err != nil {
return nil, gkerr.GenGkErr("sql.Prepare"+getDatabaseErrorMessage(err), err, ERROR_ID_PREPARE)
}
defer stmt.Close()
var rows *sql.Rows
rows, err = stmt.Query(userName)
if err != nil {
return nil, gkerr.GenGkErr("stmt.Query"+getDatabaseErrorMessage(err), err, ERROR_ID_QUERY)
}
defer rows.Close()
if rows.Next() {
err = rows.Scan(&dbUser.id, &dbUser.UserName, &dbUser.PasswordHash, &dbUser.PasswordSalt, &dbUser.Email, &dbUser.accountCreationDate, &dbUser.lastLoginDate)
if err != nil {
return nil, gkerr.GenGkErr("rows.Scan"+getDatabaseErrorMessage(err), err, ERROR_ID_ROWS_SCAN)
}
} else {
return nil, gkerr.GenGkErr("select users", nil, ERROR_ID_NO_ROWS_FOUND)
}
return dbUser, nil
}
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 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 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 LoadEnvVarsOf(appName string) (envVars map[string]string, err error) {
envVars = make(map[string]string)
sq := "select k, v from env where app_name = ?"
var stmt *sql.Stmt
stmt, err = DB.Prepare(sq)
if err != nil {
log.Printf("[ERROR] prepare sql: %s fail: %v, params: [%s]", sq, err, appName)
return
}
defer stmt.Close()
var rows *sql.Rows
rows, err = stmt.Query(appName)
if err != nil {
log.Printf("[ERROR] exec sql: %s fail: %v, params: [%s]", sq, err, appName)
return
}
for rows.Next() {
var k, v string
err = rows.Scan(&k, &v)
if err != nil {
log.Printf("[ERROR] %s scan fail: %s", sq, err)
return
}
envVars[k] = v
}
return
}
func (gkDbCon *GkDbConDef) getMaxChatId() (int32, *gkerr.GkErrDef) {
var stmt *sql.Stmt
var maxId int32 = 0
var err error
stmt, err = gkDbCon.sqlDb.Prepare("select max(id) from chat_archives")
if err != nil {
return 0, gkerr.GenGkErr("sql.Prepare"+getDatabaseErrorMessage(err), err, ERROR_ID_PREPARE)
}
defer stmt.Close()
var rows *sql.Rows
rows, err = stmt.Query()
if err != nil {
return 0, gkerr.GenGkErr("stmt.Query"+getDatabaseErrorMessage(err), err, ERROR_ID_QUERY)
}
defer rows.Close()
if rows.Next() {
err = rows.Scan(&maxId)
if err != nil {
return 0, gkerr.GenGkErr("rows.Scan"+getDatabaseErrorMessage(err), err, ERROR_ID_ROWS_SCAN)
}
}
return maxId, nil
}
//获取所有任务的任务列表
func FetchTaskList() (int, *list.List) {
var stmtIns *sql.Stmt
var err error
stmtIns, err = db.Prepare("select task_id, create_time from rdb_parse_task")
if err != nil {
logs.Log("fetch task list error in dbutil db.Prepare : " + err.Error())
return -1, list.New()
}
defer stmtIns.Close()
rows, errs := stmtIns.Query()
if errs != nil {
logs.Log("fetch task list error in dbutil stmtIns.Query : " + errs.Error())
return -2, list.New()
}
taskList := list.New()
var taskId string
var createTime string
for rows.Next() {
var task Task
rows.Scan(&taskId, &createTime)
task.TaskId = taskId
task.CreateTime = createTime
taskList.PushBack(task)
}
return 1, taskList
}
func (gkDbCon *GkDbConDef) getNextChatArchivesId() (int32, *gkerr.GkErrDef) {
var stmt *sql.Stmt
var err error
stmt, err = gkDbCon.sqlDb.Prepare("select nextval('chat_archives_seq')")
if err != nil {
return 0, gkerr.GenGkErr("sql.Prepare"+getDatabaseErrorMessage(err), err, ERROR_ID_PREPARE)
}
defer stmt.Close()
var rows *sql.Rows
rows, err = stmt.Query()
if err != nil {
return 0, gkerr.GenGkErr("stmt.Query"+getDatabaseErrorMessage(err), err, ERROR_ID_QUERY)
}
defer rows.Close()
var id int32
if rows.Next() {
err = rows.Scan(&id)
if err != nil {
return 0, gkerr.GenGkErr("rows.Scan"+getDatabaseErrorMessage(err), err, ERROR_ID_ROWS_SCAN)
}
} else {
return 0, gkerr.GenGkErr("select users", nil, ERROR_ID_NO_ROWS_FOUND)
}
return id, nil
}
//释放坏的资源
func (self *StmtPool) ReleaseBroken(conn *sql.Stmt) error {
self.mutex.Lock()
defer self.mutex.Unlock()
if nil != conn {
conn.Close()
conn = nil
}
var err error
//只有当前的存活链接和当前工作链接大于0的时候才会去销毁
if self.numActive > 0 && self.numWork > 0 {
self.numWork--
self.numActive--
} else {
err = errors.New("POOL|RELEASE BROKEN|INVALID CONN")
}
//判断当前是否连接不是最小连接
incrCount := self.minPoolSize - self.numActive
if incrCount < 0 {
//如果不够最小连接则创建
err = self.enhancedPool(incrCount)
}
return err
}
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
}
// Deliberately use no stored procedures
func (conn DBConn) LogMessage(message string, code State, replacelatest bool) error {
const (
updatestmt = "UPDATE heartbeat SET ts=$1, statuscode=$2, statustext=$3 WHERE who=$4 AND sysid=$5"
insertstmt = "INSERT INTO heartbeat(ts, statuscode, statustext, who) VALUES($1, $2, $3, $4)"
)
var hbstatement *sql.Stmt
var statuscode State
var sysid DBID
err := conn.QueryRow("SELECT asi.statuscode, asi.sysid FROM (SELECT sysid, ts, statuscode, who, MAX(ts) OVER (PARTITION BY who) max_ts FROM heartbeat) asi WHERE asi.ts = max_ts AND who=$1", conn.Appid).Scan(&statuscode, &sysid)
switch {
case err == sql.ErrNoRows:
hbstatement, err = conn.Prepare(insertstmt)
_, err = hbstatement.Exec(time.Now().UTC(), code, message, conn.Appid)
case err != nil:
return fmt.Errorf("Error reading last DBLog status code: %s", err)
case statuscode != StateOk && replacelatest:
return fmt.Errorf("Last DBLog caused a non-ok state and update requested, doing nothing")
case replacelatest:
hbstatement, err = conn.Prepare(updatestmt)
_, err = hbstatement.Exec(time.Now().UTC(), code, message, conn.Appid, sysid)
default:
hbstatement, err = conn.Prepare(insertstmt)
_, err = hbstatement.Exec(time.Now().UTC(), code, message, conn.Appid)
}
defer hbstatement.Close()
if err != nil {
return fmt.Errorf("Error executing DBLog: %s", 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 doSelect(res http.ResponseWriter, db *sql.DB) error {
var stmt *sql.Stmt
var err error
stmt, err = db.Prepare(selectStatement)
if err != nil {
fmt.Printf("db.Prepare error: %v\n", err)
return err
}
var rows *sql.Rows
rows, err = stmt.Query()
if err != nil {
fmt.Printf("stmt.Query error: %v\n", err)
return err
}
defer stmt.Close()
for rows.Next() {
var firstname string
var lastname string
err = rows.Scan(&firstname, &lastname)
if err != nil {
fmt.Printf("rows.Scan error: %v\n", err)
return err
}
fmt.Fprintln(res, "firstname: "+firstname+" lastname: "+lastname+"\n")
}
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 (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 RawReadSlice(b *B) {
var m *Model
var stmt *sql.Stmt
wrapExecute(b, func() {
var err error
initDB()
m = NewModel()
for i := 0; i < 100; i++ {
err = rawInsert(m)
if err != nil {
fmt.Println(err)
b.FailNow()
}
}
stmt, err = raw.Prepare(rawSelectMultiSQL)
if err != nil {
fmt.Println(err)
b.FailNow()
}
})
defer stmt.Close()
for i := 0; i < b.N; i++ {
var j int
models := make([]Model, 100)
rows, err := stmt.Query()
if err != nil {
fmt.Println(err)
b.FailNow()
}
for j = 0; rows.Next() && j < len(models); j++ {
err = rows.Scan(
&models[j].Id,
&models[j].Name,
&models[j].Title,
&models[j].Fax,
&models[j].Web,
&models[j].Age,
&models[j].Right,
&models[j].Counter,
)
if err != nil {
fmt.Println(err)
b.FailNow()
}
}
models = models[:j]
if err = rows.Err(); err != nil {
fmt.Println(err)
b.FailNow()
}
if err = rows.Close(); err != nil {
fmt.Println(err)
b.FailNow()
}
}
}
func (mysql *Mysql) Query(args ...interface{}) ([]interface{}, error) {
// Parse sql
q, param, err := mysql.parseQuery(args...)
if err != nil {
return nil, err
}
var stmt *sql.Stmt
// Prepare
if mysql.Tx != nil {
stmt, err = mysql.Tx.Prepare(q)
} else {
stmt, err = mysql.Db.Prepare(q)
}
if err != nil {
return nil, err
}
defer stmt.Close()
// -------------
// Scan
// -------------
rows, err := stmt.Query(param...)
if err != nil {
return nil, err
}
defer rows.Close()
// Get all col name
cols, err := rows.Columns()
if err != nil {
return nil, err
}
// Scan value
out := make([]interface{}, 0)
for rows.Next() {
rbs := make([]sql.RawBytes, len(cols))
vals := make([]interface{}, len(cols))
for i := range vals {
vals[i] = &rbs[i]
}
if err = rows.Scan(vals...); err != nil {
return nil, err
}
// Make map
r := make(map[string]interface{}, len(cols))
for i, val := range rbs {
r[cols[i]] = string(val)
}
out = append(out, r)
}
return out, nil
}
func DailySalesData_alldata(startTime string, excludeYangYi bool) (model.ProductSales, error) {
var conn *sql.DB
var stmt *sql.Stmt
var err error
if conn, err = db.Connect(); err != nil {
return nil, err
}
defer conn.Close()
_sql := `
select
DATE_FORMAT(o.create_time, '%Y-%m-%d'), sum(od.quantity)
from
order_detail od
left join ` + "`" + `order` + "`" + ` o on od.order_track_number = o.track_number
where
o.type in (?,?)
and o.status in (?,?,?,?)
and o.create_time >= ?
and od.product_id <> ?
group by
DATE_FORMAT(o.create_time, '%Y-%m-%d')
order by
DATE_FORMAT(o.create_time, '%Y-%m-%d') asc
`
if stmt, err = conn.Prepare(_sql); err != nil {
return nil, err
}
defer stmt.Close()
var excluded_product_id = 0
if excludeYangYi {
excluded_product_id = base.STAT_EXCLUDED_PRODUCT
}
rows, err := stmt.Query(
model.Wholesale, model.SubOrder, // model.ShippingInstead, // 查子订单
"toprint", "todeliver", "delivering", "done",
startTime, excluded_product_id,
)
if db.Err(err) {
return nil, err
}
defer rows.Close() // db.CloseRows(rows) // use db.CloseRows or rows.Close()? Is rows always nun-nil?
ps := model.ProductSales{}
for rows.Next() {
p := new(model.SalesNode)
rows.Scan(&p.Key, &p.Value)
ps = append(ps, p)
}
return ps, nil
}