|
|
package codeforcesbot
|
|
|
|
|
|
import (
|
|
|
"database/sql"
|
|
|
"fmt"
|
|
|
"strings"
|
|
|
|
|
|
_ "github.com/lib/pq"
|
|
|
)
|
|
|
|
|
|
var db *sql.DB
|
|
|
|
|
|
func SqlOpen() {
|
|
|
var err error
|
|
|
db, err = sql.Open("postgres", "port=5432 user=postgres password=postgres dbname=codeforcesbotdb sslmode=disable")
|
|
|
//port是数据库的端口号，默认是5432，如果改了，这里一定要自定义；
|
|
|
//user就是你数据库的登录帐号;
|
|
|
//dbname就是你在数据库里面建立的数据库的名字;
|
|
|
//sslmode就是安全验证模式;
|
|
|
|
|
|
//还可以是这种方式打开
|
|
|
//db, err := sql.Open("postgres", "postgres://pqgotest:password@localhost/pqgotest?sslmode=verify-full")
|
|
|
CheckErr(err)
|
|
|
}
|
|
|
func SqlInsertCFid(qqid int64, cfid string) string{
|
|
|
//插入数据
|
|
|
stmt, err := db.Prepare("INSERT INTO codeforcesId(qqid, cfid) VALUES($1,$2)")
|
|
|
CheckErr(err)
|
|
|
|
|
|
res, err := stmt.Exec(qqid, cfid)
|
|
|
|
|
|
if err != nil {
|
|
|
return err.Error()
|
|
|
}
|
|
|
|
|
|
affect, err := res.RowsAffected()
|
|
|
CheckErr(err)
|
|
|
|
|
|
return fmt.Sprintf("OK rows affect:", affect)
|
|
|
}
|
|
|
|
|
|
func SqlQueryHandles(qqid int64) (ret []string){
|
|
|
rows, err := db.Query(fmt.Sprintf("SELECT cfid FROM codeforcesid WHERE qqid = %v", qqid))
|
|
|
CheckErr(err)
|
|
|
for rows.Next() {
|
|
|
var handle string
|
|
|
CheckErr(rows.Scan(&handle))
|
|
|
ret = append(ret, strings.TrimSpace(handle))
|
|
|
}
|
|
|
return ret
|
|
|
}
|
|
|
|
|
|
func SqlGetNotifiedGroup() (groups []int64) {
|
|
|
rows, err := db.Query("SELECT groupid FROM codeforcesnotify")
|
|
|
CheckErr(err)
|
|
|
for rows.Next() {
|
|
|
var groupid int64
|
|
|
CheckErr(rows.Scan(&groupid))
|
|
|
groups = append(groups, groupid)
|
|
|
}
|
|
|
return groups
|
|
|
}
|
|
|
/*
|
|
|
func sqlSelect() {
|
|
|
//查询数据
|
|
|
rows, err := db.Query("SELECT * FROM userinfo")
|
|
|
CheckErr(err)
|
|
|
|
|
|
println("-----------")
|
|
|
for rows.Next() {
|
|
|
var uid int
|
|
|
var username string
|
|
|
var department string
|
|
|
var created string
|
|
|
err = rows.Scan(&uid, &username, &department, &created)
|
|
|
CheckErr(err)
|
|
|
fmt.Println("uid = ", uid, "\nname = ", username, "\ndep = ", department, "\ncreated = ", created, "\n-----------")
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
func sqlUpdate() {
|
|
|
//更新数据
|
|
|
stmt, err := db.Prepare("update userinfo set username=$1 where uid=$2")
|
|
|
CheckErr(err)
|
|
|
|
|
|
res, err := stmt.Exec("ficow", 1)
|
|
|
CheckErr(err)
|
|
|
|
|
|
affect, err := res.RowsAffected()
|
|
|
CheckErr(err)
|
|
|
|
|
|
fmt.Println("rows affect:", affect)
|
|
|
}
|
|
|
*/
|
|
|
func sqlClose() {
|
|
|
db.Close()
|
|
|
}
|
|
|
func CheckErr(err error) {
|
|
|
if err != nil {
|
|
|
panic(err)
|
|
|
}
|
|
|
} |
