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 push(db *sql.DB, iter, routine int, queryStr string) {
defer timeTracker(time.Now(), "pump", routine)
//fmt.Printf("StartTime: %v\n", time.Now())
stmt, prepErr := db.Prepare(queryStr)
if prepErr != nil {
log.Fatal(prepErr)
}
defer stmt.Close()
//var rowIds = []int64{}
var rowNumCount int64
for i := 0; i < iter; i++ {
res, resErr := stmt.Exec("tester", time.Now(), i)
if resErr != nil || res == nil {
log.Fatal(resErr)
}
// count number of rows efected
numRow, _ := res.RowsAffected()
rowNumCount += numRow
//lins, _ := res.LastInsertId()
//rowIds = append(rowIds, lins)
//lins, errLin := res.LastInsertId()
//numRow, errRow := res.RowsAffected()
}
//fmt.Printf("Last ID: %d Number of rows: %d\n", rowIds[:len(rowIds)-1], rowNumCount)
fmt.Printf("Number of rows: %d\n", rowNumCount)
//fmt.Printf("StopTime: %v\n", time.Now())
// cant do this cause we'll exit when first goroutine is done!
//os.Exit(0)
}
func insert_session(db *sql.DB, user_id int, sid string) {
row := db.QueryRow("select count(id) from auth_session where user_id=?", user_id)
var count int
err := row.Scan(&count)
if err != nil {
fmt.Println(err)
}
if count > 0 {
stmt, err := db.Prepare("update auth_session set sid=? where user_id=?")
if err != nil {
fmt.Println(err)
return
}
stmt.Exec(sid, user_id)
defer stmt.Close()
return
}
stmt, err := db.Prepare("insert into auth_session(user_id, sid) values (?,?)")
if err != nil {
fmt.Println(err)
}
defer stmt.Close()
if _, err := stmt.Exec(user_id, sid); err != nil {
fmt.Println("smt.Exec failed: ", err)
}
}
func FundHandler(rw http.ResponseWriter, r *http.Request, db *sql.DB, params martini.Params) {
id := params["id"]
sqlStr := "SELECT * FROM funds WHERE id=?"
stmt, err := db.Prepare(sqlStr)
if err != nil {
log.Fatal(err)
}
defer stmt.Close()
fund := new(Fund)
err = stmt.QueryRow(id).Scan(&fund.Id, &fund.Name)
if err != nil {
if err == sql.ErrNoRows {
rw.Header().Set("Status", "404")
rw.Header().Set("Content-Type", "application/json; charset=utf-8")
notFound := map[string]string{"error": "Not Found"}
b, _ := json.Marshal(notFound)
rw.Write(b)
} else {
log.Fatal(err)
}
} else {
rw.Header().Set("Content-Type", "application/json; charset=utf-8")
b, err := json.Marshal(fund)
if err != nil {
log.Fatal(err)
}
rw.Write(b)
}
}
// Save the User data to the DB
func SaveUser(db *sql.DB, u User) (id int64, err error) {
// Prepare Insert Statement
stmt, err := db.Prepare(`
INSERT
tbl_users
SET
steam64=?,
steam32=?,
name=?
`)
if err != nil {
fmt.Println(err)
}
defer stmt.Close()
res, err := stmt.Exec(
u.Steam64,
u.Steam32,
u.Name,
)
if err != nil {
fmt.Println(err)
}
id, err = res.LastInsertId()
if err != nil {
fmt.Println(err)
}
return id, err
}
func mustPrepare(db *sql.DB, query string) *sql.Stmt {
stmt, err := db.Prepare(query)
if err != nil {
log.Fatalf("Error when preparing statement %q: %s", query, err)
}
return stmt
}
//tries to create the schema and ignores failures to do so.
//versions after Postgres 9.3 support the "IF NOT EXISTS" sql syntax
func tryCreateSchema(db *sql.DB, importSchema string) {
createSchema, err := db.Prepare(fmt.Sprintf("CREATE SCHEMA %s", importSchema))
if err == nil {
createSchema.Exec()
}
}
func returnRideHistory(db *sql.DB, u int) (map[string]int, map[string]int, error) {
result := make(map[string]int)
result2 := make(map[string]int)
stmt, err := db.Prepare(`
SELECT year, sum(rides_given), sum(rides_taken)
FROM ride_history
WHERE user_id = ?
GROUP BY year;
`)
if err != nil {
return result, result2, util.NewError(err, "Error de la base de datos", 500)
}
defer stmt.Close()
rows, err := stmt.Query(u)
if err != nil {
return result, result2, util.NewError(err, "Error de la base de datos", 500)
}
defer rows.Close()
for rows.Next() {
year := ""
var given, taken int
err := rows.Scan(&year, &given, &taken)
if err != nil {
return result, result2, util.NewError(err, "Error de la base de datos", 500)
}
result[year] = given
result2[year] = taken
}
return result, result2, nil
}
func returnUserComments(db *sql.DB, u int) ([]Comment, error) {
results := make([]Comment, 0)
stmt, err := db.Prepare(`
SELECT positive, DATE_FORMAT(date,'%d/%m/%Y'), comment
FROM comments
WHERE user = ?
AND public = true;
`)
if err != nil {
return results, util.NewError(err, "Error de la base de datos", 500)
}
defer stmt.Close()
rows, err := stmt.Query(u)
if err != nil {
return results, util.NewError(err, "Error de la base de datos", 500)
}
defer rows.Close()
for rows.Next() {
comment := Comment{}
err := rows.Scan(&comment.Positive, &comment.Date, &comment.Text)
if err != nil {
return results, util.NewError(err, "Error de la base de datos", 500)
}
results = append(results, comment)
}
return results, nil
}
//create table with a single JSON or JSONB column data
func createJSONTable(db *sql.DB, schema string, tableName string, column string, dataType string) (*sql.Stmt, error) {
fullyQualifiedTable := fmt.Sprintf("%s.%s", schema, tableName)
tableSchema := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s (%s %s)", fullyQualifiedTable, column, dataType)
statement, err := db.Prepare(tableSchema)
return statement, err
}
func dbInit(db *sql.DB) error {
var err error
_, err = db.Exec(initSQL)
if err != nil {
glog.Errorln("error intializing database")
return err
}
q = map[string]*sql.Stmt{}
prepare := map[string]string{
"getUser": "******",
"getUserIdByName": "SELECT id FROM users WHERE name = $1",
"getGroup": "SELECT name, userGroup FROM groups WHERE id = $1",
"getGroupIdByName": "SELECT id FROM groups WHERE name = $1",
"checkToken": "SELECT EXISTS (SELECT 1 FROM session WHERE id = $1 AND token = $2 AND expire > current_timestamp);",
"checkChallenge": "SELECT EXISTS (SELECT 1 FROM session WHERE id = $1 AND challenge = $2 AND expire > current_timestamp);",
}
for name, query := range prepare {
x, err := db.Prepare(query)
if err != nil {
glog.Errorf("error preparing: %s\t%s\n", name, query)
return err
}
q[name] = x
}
return nil
}
func LoadAllGroup(db *sql.DB) (map[int64]*Group, error) {
stmtIns, err := db.Prepare("SELECT id, appid, super FROM `group`")
if err != nil {
log.Info("error:", err)
return nil, nil
}
defer stmtIns.Close()
groups := make(map[int64]*Group)
rows, err := stmtIns.Query()
for rows.Next() {
var id int64
var appid int64
var super int8
rows.Scan(&id, &appid, &super)
members, err := LoadGroupMember(db, id)
if err != nil {
log.Info("error:", err)
continue
}
if super != 0 {
group := NewSuperGroup(id, appid, members)
groups[group.gid] = group
} else {
group := NewGroup(id, appid, members)
groups[group.gid] = group
}
}
return groups, nil
}
func WritePsql(username string, db *sql.DB) {
statement, err := db.Prepare("INSERT INTO entry(username,created) VALUES($1,$2)")
checkErr(err)
_, err = statement.Exec(username, time.Now())
checkErr(err)
counts.Add("sql write", 1)
}
func (self *catalogResource) executeInsert(db *sql.DB, query *string, args ...interface{}) (id int64, err error) {
l := NewLogger("catalog_resource")
defer func() {
if r := recover(); r != nil {
id = 0
err = r.(error)
l.Error("Could not insert record", "err", err)
}
}()
stmt, err := db.Prepare(*query)
if err != nil {
panic(err)
}
res, err := stmt.Exec(args...)
if err != nil {
panic(err)
}
id, err = res.LastInsertId()
if err != nil {
panic(err)
}
return id, nil
}
// Link a Server's channels together
func populateChannelLinkInfo(server *Server, db *sql.DB) (err error) {
stmt, err := db.Prepare("SELECT channel_id, link_id FROM channel_links WHERE server_id=?")
if err != nil {
return err
}
rows, err := stmt.Query(server.Id)
if err != nil {
return err
}
for rows.Next() {
var (
ChannelId int
LinkId int
)
if err := rows.Scan(&ChannelId, &LinkId); err != nil {
return err
}
channel, exists := server.Channels[ChannelId]
if !exists {
return errors.New("Attempt to perform link operation on non-existant channel.")
}
other, exists := server.Channels[LinkId]
if !exists {
return errors.New("Attempt to perform link operation on non-existant channel.")
}
server.LinkChannels(channel, other)
}
return nil
}
func ReturnUserPicture(db *sql.DB, user int, size string) (string, error) {
picture := ""
stmt, err := db.Prepare(`
SELECT custom_picture, name FROM users WHERE id = ?;
`)
if err != nil {
return picture, util.NewError(err, "Error de la base de datos", 500)
}
defer stmt.Close()
customPicture := false
name := ""
err = stmt.QueryRow(user).Scan(&customPicture, &name)
if err != nil {
return picture, util.NewError(err, "Usuario no encontrado", 500)
}
sizeSuffix := ""
if size != "100" {
sizeSuffix = "_" + size
}
if customPicture {
picture = "https://5sur.com/images/" + name + sizeSuffix + ".png"
} else {
picture = "https://5sur.com/default" + sizeSuffix + ".png"
}
return picture, nil
}
func Getusers(username string, data map[string]map[string]string, db *sql.DB) map[string]map[string]string {
stmt, _ := db.Prepare("SELECT * from hostcontrol_users WHERE owned_by = ? and system_username != owned_by")
rows, _ := stmt.Query(username)
stmt.Close()
for rows.Next() {
var hostcontrol_id string
var system_username string
var privileges string
var owned_by string
var login_token string
var email_address string
rows.Scan(&hostcontrol_id, &system_username, &privileges, &owned_by, &login_token, &email_address)
data[system_username] = make(map[string]string)
data[system_username]["hostcontrol_id"] = hostcontrol_id
data[system_username]["system_username"] = system_username
data[system_username]["privileges"] = privileges
data[system_username]["owned_by"] = owned_by
data[system_username]["login_token"] = login_token
data[system_username]["email_address"] = email_address
data = Getusers(system_username, data, db)
}
return data
}
func JssdkDispatch(db *sql.DB) Dlm {
return Dlm{
// 获得js-sdk信息
"getConfig": func(r *http.Request) (string, interface{}) {
url := GetParameter(r, "url")
// url := "http://develop.zsgd.com:11002/votes/wechat/index.html?vote_id=11"
state, weixin := getOauth2Info(strings.Split(url, "&")[0], db)
stmt, err := db.Prepare("select weixin, appid, access_token, jsapi_ticket from weixin where weixin = ?")
defer stmt.Close()
if nil != err {
log.Println(err)
panic("获取js-sdk数据失败")
}
var w Wechat
err = stmt.QueryRow(weixin).Scan(&w.Weixin, &w.Appid, &w.AccessToken, &w.JsapiTicket)
if nil != err {
log.Println(err)
panic("获取js-sdk数据失败")
}
var wx WXConfig
wx.State = state
wx.Timestamp = time.Now().Unix()
wx.Appid = w.Appid
wx.NonceStr = string(GenerateRandomString(16, KC_RAND_KIND_ALL))
wx.Signature = generateSign(wx.NonceStr, w.JsapiTicket, url, wx.Timestamp)
return "获取js-sdk数据成功", wx
},
}
}
//向sqlCode添加"插入1行数据"的语句，执行前须保证Create()、AddRow()已经执行
//insert into table1(field1,field2) values(rowValues[0],rowValues[1])
func (self *MyTable) Update(db *sql.DB) {
if self.tableName != "" {
self.sqlCode = `insert into ` + self.tableName + `(`
if self.columnNames != nil {
for _, v1 := range self.columnNames {
self.sqlCode += "`" + v1 + "`" + `,`
}
self.sqlCode = string(self.sqlCode[:len(self.sqlCode)-1])
self.sqlCode += `)values(`
}
if self.rowValues != nil {
for _, v2 := range self.rowValues {
v2 = strings.Replace(v2, `"`, `\"`, -1)
self.sqlCode += `"` + v2 + `"` + `,`
}
self.sqlCode = string(self.sqlCode[:len(self.sqlCode)-1])
self.sqlCode += `);`
}
}
stmt, err := db.Prepare(self.sqlCode)
util.CheckErr(err)
_, err = stmt.Exec()
util.CheckErr(err)
// 清空临时数据
self.rowValues = []string{}
}
func InsertPrepareStatement(db *sql.DB, tableName string, rows map[string]interface{}) (stmt *sql.Stmt, err error) {
stmt, err = db.Prepare("insert into " + tableName + "(" + getFields(rows) + ") values (" + getFieldValues(rows) + ")")
if err != nil {
return
}
return
}
func handleAddVirginUnitMsg(msg AddVirginUnitMsg, db *sql.DB) {
resp_msg := AddVirginUnitRspMsg{}
stmt_str :=
`INSERT INTO VirginUnits(VirginUnitId, Filepath, Hash, Format)
VALUES(?, ?, ?, ?)`
stmt, err := db.Prepare(stmt_str)
if err != nil {
resp_msg.Err = err
msg.ResponseChannel <- resp_msg
return
}
defer stmt.Close()
_, err = stmt.Exec(
msg.VirginUnitInfo.VirginUnitId,
msg.VirginUnitInfo.Filepath,
msg.VirginUnitInfo.Hash,
msg.VirginUnitInfo.Format)
if err != nil {
resp_msg.Err = err
msg.ResponseChannel <- resp_msg
return
}
resp_msg.Err = nil
msg.ResponseChannel <- resp_msg
}
// assertRecord returns true if the table contains a record with the specified properties
func assertRecord(session *sql.DB, table string, props map[string]interface{}) bool {
if session == nil {
panic("database session is not established")
}
var buff bytes.Buffer
var vals = make([]interface{}, len(props))
var i = 0
var count int
buff.WriteString("select count(*) from ")
buff.WriteString(table)
buff.WriteString(" where ")
for k, v := range props {
if i != 0 {
buff.WriteString(" and ")
}
vals[i] = v
i++
buff.WriteString(fmt.Sprintf("%s = $%d", k, i))
}
stmt, err := session.Prepare(buff.String())
if err != nil {
panic(err)
}
row := stmt.QueryRow(vals...)
err = row.Scan(&count)
if err != nil {
panic(err)
}
return (count != 0)
}
//doSelect a poniter to db.Open() object that we have obtained in main()
// if all goes well it will return nil otherwise it will return an error
func doSelect(db *sql.DB) error {
//use the db.Open() to return a prepared statement to be used at later stage
stmt, err := db.Prepare(selectStatement)
checkError(err)
//Query executes a prepared query statement with the given arguments
// and returns the query results as a *Rows. [this is direct from Go docs]
rows, err := stmt.Query()
checkError(err)
//once done with statement close it.
defer stmt.Close()
for rows.Next() {
var id string
var name string
// to avoid caller having copies of values, pointers are used
err = rows.Scan(&id, &name)
// first %v is replaced by value of id and second %v by value of name
fmt.Printf("firstname: %v lastname: %v \n", id, name)
}
return nil
}
// AddRoundResult add round result to database
func AddRoundResult(db *sql.DB, res RoundResult) (id int, err error) {
addRoundResultMutex.Lock()
defer addRoundResultMutex.Unlock()
if res.Round > 1 { // if not first round
previous, err := GetLastResult(db, res.TeamID)
if err != nil {
return id, err
}
res.AttackScore += previous.AttackScore
res.DefenceScore += previous.DefenceScore
}
stmt, err := db.Prepare("INSERT INTO round_result " +
"(team_id, round, attack_score, defence_score) " +
"VALUES ($1, $2, $3, $4) RETURNING id")
if err != nil {
return
}
defer stmt.Close()
err = stmt.QueryRow(res.TeamID, res.Round, res.AttackScore,
res.DefenceScore).Scan(&id)
if err != nil {
return
}
return
}
func qlCompile(db *sql.DB) (map[string]*sql.Stmt, error) {
stmts := map[string]string{
"cleanAddress": `DELETE FROM Addresses WHERE Seen < now() - duration("2h")`,
"cleanRelay": `DELETE FROM Relays WHERE Seen < now() - duration("2h")`,
"cleanDevice": `DELETE FROM Devices WHERE Seen < now() - duration("24h")`,
"countAddress": "SELECT count(*) FROM Addresses",
"countDevice": "SELECT count(*) FROM Devices",
"countRelay": "SELECT count(*) FROM Relays",
"insertAddress": "INSERT INTO Addresses (DeviceID, Seen, Address) VALUES ($1, now(), $2)",
"insertRelay": "INSERT INTO Relays (DeviceID, Seen, Address, Latency) VALUES ($1, now(), $2, $3)",
"insertDevice": "INSERT INTO Devices (DeviceID, Seen) VALUES ($1, now())",
"selectAddress": `SELECT Address from Addresses WHERE DeviceID==$1 AND Seen > now() - duration("1h") LIMIT 16`,
"selectRelay": `SELECT Address, Latency from Relays WHERE DeviceID==$1 AND Seen > now() - duration("1h") LIMIT 16`,
"updateAddress": "UPDATE Addresses Seen=now() WHERE DeviceID==$1 AND Address==$2",
"updateDevice": "UPDATE Devices Seen=now() WHERE DeviceID==$1",
"deleteRelay": "DELETE FROM Relays WHERE DeviceID==$1",
}
res := make(map[string]*sql.Stmt, len(stmts))
for key, stmt := range stmts {
prep, err := db.Prepare(stmt)
if err != nil {
log.Println("Failed to compile", stmt)
return nil, err
}
res[key] = prep
}
return res, nil
}
func storeSites(db *sql.DB, sites []OpmlOutline) {
sql_additem := `
INSERT INTO sites(
XmlUrl,
Title,
Type,
Text,
HtmlUrl,
Favicon
) values(?, ?, ?, ?, ?, ?)
`
stmt, err := db.Prepare(sql_additem)
if err != nil {
panic(err)
}
defer stmt.Close()
for _, site := range sites {
_, err2 := stmt.Exec(site.XmlUrl, site.Title, site.Type,
site.Text, site.HtmlUrl, site.Favicon)
if err2 != nil {
panic(err2)
}
}
}
/**
* GetDevicesByIdUser
* Query function getDevicesUserIdi with prototype:
* FUNCTION getDevicesByUserId(iduser integer) RETURNS TABLE(macaddr varchar(18))
* Return a pointer on new Device list created
**/
func (Lusr *All_users) GetDevicesByIdUser(idUser int64, Db *sql.DB) *list.List {
lDevice := list.New()
stm, err := Db.Prepare("SELECT getDevicesByUserId($1)")
if err != nil {
Lusr.Logger.Println("GetDevicesByIdUser query error: ", err)
return lDevice
}
defer stm.Close()
rows, err := stm.Query(idUser)
if err != nil {
Lusr.Logger.Println("GetDevicesByIdUser query error: ", err)
return lDevice
}
defer rows.Close()
for rows.Next() {
var idDevice string
err = rows.Scan(&idDevice)
if err != nil {
Lusr.Logger.Println("GetDevicesByIdUser scan error: ", err)
} else {
lDevice.PushBack(idDevice)
}
}
return lDevice
}
func doImportantWork(db *sql.DB) error {
stmt, err := db.Prepare("SELECT 'something important'")
if err != nil {
return err
}
rows, err := stmt.Query()
if err != nil {
return err
}
defer rows.Close()
for rows.Next() {
var something string
err := rows.Scan(&something)
if err != nil {
return err
}
// throw away the result
}
err = rows.Err()
if err != nil {
return err
}
return nil
}
// CreateDeductionsTable takes the string value of "deductions" for every person
// in the people table, parses it, and creates an entry in the deductions table
// for each deduction
func CreateDeductionsTable(db *sql.DB) {
// OK, we're connected to the database. On with the work. First thing to do is
// to put all the deductions into a separate multivalued Deductions table organized
// by employee id
rows, err := db.Query("select uid, deductions from people")
errcheck(err)
defer rows.Close()
InsrtDeduct, err := db.Prepare("INSERT INTO deductions (uid,deduction) VALUES(?,?)")
errcheck(err)
var (
uid int
deductions string
)
for rows.Next() {
errcheck(rows.Scan(&uid, &deductions))
if len(deductions) > 0 {
da := strings.Split(deductions, ",")
for i := 0; i < len(da); i++ {
d := deductionStringToInt(strings.Trim(da[i], " \n\r"))
_, err := InsrtDeduct.Exec(uid, d)
errcheck(err)
}
}
}
errcheck(rows.Err())
RemovePeopleDeductCol, err := db.Prepare("alter table people drop column deductions")
errcheck(err)
_, err = RemovePeopleDeductCol.Exec()
errcheck(err)
createDeductionsList(db)
}
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
}