Golang database-sql.Stmt类(方法)实例源码

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 (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 GeoipLookup(stmt *sql.Stmt, ip string) (*GeoIP, error) {
	IP := net.ParseIP(ip)
	reserved := false
	for _, net := range reservedIPs {
		if net.Contains(IP) {
			reserved = true
			break
		}
	}
	geoip := GeoIP{Ip: ip}
	if reserved {
		geoip.CountryCode = "RD"
		geoip.CountryName = "Reserved"
	} else {
		var uintIP uint32
		b := bytes.NewBuffer(IP.To4())
		binary.Read(b, binary.BigEndian, &uintIP)
		if err := stmt.QueryRow(uintIP).Scan(
			&geoip.CountryCode,
			&geoip.CountryName,
			&geoip.RegionCode,
			&geoip.RegionName,
			&geoip.CityName,
			&geoip.ZipCode,
			&geoip.Latitude,
			&geoip.Longitude,
			&geoip.MetroCode,
			&geoip.AreaCode,
		); err != nil {
			return nil, err
		}
	}
	return &geoip, 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
}

func lookup(stmt *sql.Stmt, IP net.IP, nIP uint32) (*GeoIP, error) {
	var reserved bool
	for _, net := range reservedIPs {
		if net.Contains(IP) {
			reserved = true
			break
		}
	}
	geoip := GeoIP{Ip: IP.String()}
	if reserved {
		geoip.CountryCode = "RD"
		geoip.CountryName = "Reserved"
	} else {
		if err := stmt.QueryRow(nIP).Scan(
			&geoip.CountryCode,
			&geoip.CountryName,
			&geoip.RegionCode,
			&geoip.RegionName,
			&geoip.CityName,
			&geoip.ZipCode,
			&geoip.Latitude,
			&geoip.Longitude,
			&geoip.MetroCode,
			&geoip.AreaCode,
		); err != nil {
			return nil, err
		}
	}
	return &geoip, nil
}

func LookupContributedBrand(stmt *sql.Stmt, brandName string) ([]*CONTRIBUTED_BRAND, error) {
	results := make([]*CONTRIBUTED_BRAND, 0)

	rows, err := stmt.Query(fmt.Sprintf("%s%%", brandName)) // like 'brandName%'
	if err != nil {
		return results, err
	}
	defer rows.Close()

	for rows.Next() {
		var i, n, u, a sql.NullString
		err := rows.Scan(&i, &n, &u, &a)
		if err != nil {
			return results, err
		} else {
			if n.Valid {
				result := new(CONTRIBUTED_BRAND)
				result.Uuid = i.String
				result.Name = n.String
				result.URL = u.String
				result.AccountID = a.String
				results = append(results, result)
			}
		}
	}

	return results, nil
}

// 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 executePopularNamesCheck(stmt *sql.Stmt, fk int64, table_name string, args [20]string) (bool, error) {
	rows, err := stmt.Query(
		fk,
		table_name,
		args[0], args[1], args[2], args[3], args[4],
		args[5], args[6], args[7], args[8], args[9],
		args[10], args[11], args[12], args[13], args[14],
		args[15], args[16], args[17], args[18], args[19])

	if err != nil {
		return false, errors.New("Query failed.")
	}

	defer rows.Close()

	for rows.Next() {
		var popular_name string

		err = rows.Scan(&popular_name)

		if err != nil {
			log.Printf("Checking failed: %v\n", err)
			return false, err
		}

		log.Printf("Name found: %s\n", popular_name)

		return false, nil
	}

	return true, 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
}

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

// QueryRow executes a prepared query statement with the given arguments. If an
// error occurs during the execution of the statement, that error will be returned
// by a call to Scan on the returned *Row, which is always non-nil. If the query
// selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's
// Scan scans the first selected row and discards the rest.
//
// QueryRow prepares the same statement on another connection and queries it
func (s *Stmt) QueryRow(ctx context.Context, args ...interface{}) *Row {
	if s.err != nil {
		return &Row{err: s.err}
	}

	done := make(chan struct{}, 0)

	var res *sql.Row
	f := func(sqldb *sql.DB) {
		defer close(done)

		var stmt *sql.Stmt
		stmt, err := sqldb.Prepare(s.query)
		if err != nil {
			return
		}

		res = stmt.QueryRow(args...)
	}

	sqldb, opErr := s.db.handleWithSQL(ctx, f, done)
	if opErr != nil {
		return &Row{err: opErr}
	}

	return &Row{
		row:   res,
		sqldb: sqldb,
		db:    s.db,
	}
}

// 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 LookupBrandByName(stmt *sql.Stmt, brandName string) ([]*BRAND, error) {
	results := make([]*BRAND, 0)

	rows, err := stmt.Query(fmt.Sprintf("%s%%", brandName)) // like 'brandName%'
	if err != nil {
		return results, err
	}
	defer rows.Close()

	for rows.Next() {
		var i, n, u sql.NullString
		err := rows.Scan(&i, &n, &u)
		if err != nil {
			return results, err
		} else {
			if n.Valid || u.Valid {
				result := new(BRAND)
				result.Id = i.String
				if n.Valid {
					result.Name = n.String
				}
				if u.Valid {
					result.URL = u.String
				}
				results = append(results, result)
			}
		}
	}

	return results, nil
}

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 LookupContributedBarcode(stmt *sql.Stmt, code string) ([]*BARCODE, error) {
	results := make([]*BARCODE, 0)

	rows, err := stmt.Query(code)
	if err != nil {
		return results, err
	}
	defer rows.Close()

	for rows.Next() {
		var (
			i, n, d, a sql.NullString
			e          sql.NullBool
		)
		err := rows.Scan(&i, &n, &d, &e, &a)
		if err != nil {
			return results, err
		} else {
			result := new(BARCODE)
			result.Barcode = code
			result.Uuid = i.String
			result.ProductName = n.String
			result.GtinEdit = e.Bool
			result.AccountID = a.String
			if d.Valid {
				result.ProductDesc = d.String
			}
			results = append(results, result)
		}
	}

	return results, nil
}

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

}

// LookupGtin takes a prepared statment (using the GTIN_LOOKUP string),
// a barcode string, and looks it up in POD, returning a list of matching
// GTIN structs
func LookupGtin(stmt *sql.Stmt, barcode string) ([]*GTIN, error) {
	results := make([]*GTIN, 0)

	rows, err := stmt.Query(barcode)
	if err != nil {
		return results, err
	}
	defer rows.Close()

	for rows.Next() {
		var p, b sql.NullString
		err := rows.Scan(&p, &b)
		if err != nil {
			return results, err
		} else {
			if p.Valid || b.Valid {
				result := new(GTIN)
				result.Id = barcode
				if p.Valid {
					result.ProductName = p.String
				}
				if b.Valid {
					result.BrandId = b.String
				}
				results = append(results, result)
			}
		}
	}

	return results, 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)
		}

	}
}