func connect() *sql.DB {
db, err := sql.Open("mysql", "gomysql_test:abc123@localhost:3306/gomysql_test")
if err != nil {
panic("Error connecting to db: " + err.Error())
}
return db
}
func main() {
os.Remove("./foo.db")
db, err := sql.Open("sqlite3", "./foo.db")
if err != nil {
fmt.Println(err)
return
}
sqls := []string{
"create table foo (id integer not null primary key, name text)",
"delete from foo",
}
for _, sql := range sqls {
_, err = db.Exec(sql)
if err != nil {
fmt.Printf("%q: %s\n", err, sql)
return
}
}
tx, err := db.Begin()
if err != nil {
fmt.Println(err)
return
}
stmt, err := tx.Prepare("insert into foo(id, name) values(?, ?)")
if err != nil {
fmt.Println(err)
return
}
defer stmt.Close()
for i := 0; i < 100; i++ {
_, err = stmt.Exec(i, fmt.Sprintf("こんにちわ世界%03d", i))
if err != nil {
fmt.Println(err)
return
}
}
tx.Commit()
rows, err := db.Query("select id, name from foo")
if err != nil {
fmt.Println(err)
return
}
defer rows.Close()
for rows.Next() {
var id int
var name string
rows.Scan(&id, &name)
println(id, name)
}
}
func TestSqlSimple(t *testing.T) {
cn, err := sql.Open("postgres", cs)
assert.Equalf(t, nil, err, "%v", err)
rows, err := cn.Query("SELECT length($1) AS foo", "testing")
assert.Equalf(t, nil, err, "%v", err)
ok := rows.Next()
assert.T(t, ok)
var length int
err = rows.Scan(&length)
assert.Equalf(t, nil, err, "%v", err)
assert.Equal(t, 7, length)
}
func TestOpen(t *testing.T) {
db, err := sql.Open("sqlite3", "./foo.db")
if err != nil {
t.Errorf("Failed to open database:", err)
return
}
defer os.Remove("./foo.db")
_, err = db.Exec("drop table foo")
_, err = db.Exec("create table foo (id integer)")
if err != nil {
t.Errorf("Failed to create table:", err)
return
}
if stat, err := os.Stat("./foo.db"); err != nil || stat.IsDir() {
t.Errorf("Failed to create ./foo.db")
}
}
func TestInsert(t *testing.T) {
db, err := sql.Open("sqlite3", "./foo.db")
if err != nil {
t.Errorf("Failed to open database:", err)
return
}
defer os.Remove("./foo.db")
_, err = db.Exec("drop table foo")
_, err = db.Exec("create table foo (id integer)")
if err != nil {
t.Errorf("Failed to create table:", err)
return
}
res, err := db.Exec("insert into foo(id) values(123)")
if err != nil {
t.Errorf("Failed to insert record:", err)
return
}
affected, _ := res.RowsAffected()
if affected != 1 {
t.Errorf("Expected %d for affected rows, but %d:", 1, affected)
return
}
rows, err := db.Query("select id from foo")
if err != nil {
t.Errorf("Failed to select records:", err)
return
}
defer rows.Close()
rows.Next()
var result int
rows.Scan(&result)
if result != 123 {
t.Errorf("Fetched %q; expected %q", 123, result)
}
}
func dbSetup() {
if _, e := os.Stat(dbfile); e != nil {
db, e := sql.Open("sqlite3", dbfile)
if e != nil {
logger.Print(e)
return
}
for _, s := range []string{
"create table User (userid varchar(16), password varchar(20), realname varchar(20), age integer)",
"insert into User values('go', 'lang', 'golang', 3)",
"insert into User values('perl', 'monger', 'perlmonger', 20)",
"insert into User values('japan', 'hello', '日本', 10)",
} {
if _, e := db.Exec(s); e != nil {
logger.Print(e)
return
}
}
db.Close()
}
}
func main() {
//------------------------------------------------
// initialize session manager
manager.OnStart(func(session *session.Session) {
logger.Printf("Start session(\"%s\")", session.Id)
})
manager.OnEnd(func(session *session.Session) {
logger.Printf("End session(\"%s\")", session.Id)
})
manager.SetTimeout(10)
//------------------------------------------------
// initialize database
dbSetup()
//------------------------------------------------
// go to web
web.Config.CookieSecret = "7C19QRmwf3mHZ9CPAaPQ0hsWeufKd"
s := "select userid, password, realname, age from User where userid = ? and password = ?"
web.Get("/", func(ctx *web.Context) {
session := getSession(ctx, manager)
tmpl.Execute(ctx, map[string]interface{}{
"Value": session.Value, "Msg": "",
})
})
web.Post("/login", func(ctx *web.Context) {
session := getSession(ctx, manager)
userid := getParam(ctx, "userid")
password := getParam(ctx, "password")
if userid != "" && password != "" {
// find user
db, e := sql.Open("sqlite3", dbfile)
defer db.Close()
st, _ := db.Prepare(s)
r, e := st.Query(userid, password)
if e != nil {
logger.Print(e)
return
}
if !r.Next() {
// not found
tmpl.Execute(ctx, map[string]interface{}{
"Value": nil, "Msg": "User not found",
})
return
}
var userid, password, realname string
var age int64
e = r.Scan(&userid, &password, &realname, &age)
if e != nil {
logger.Print(e)
return
}
// store User object to sessino
session.Value = &User{userid, password, realname, age}
logger.Printf("User \"%s\" login", session.Value.(*User).UserId)
}
ctx.Redirect(302, "/")
})
web.Post("/logout", func(ctx *web.Context) {
session := getSession(ctx, manager)
if session.Value != nil {
// abandon
logger.Printf("User \"%s\" logout", session.Value.(*User).UserId)
session.Abandon()
}
ctx.Redirect(302, "/")
})
web.Run(":6061")
}
func NewConnection(driverName, dataSource string) (*Conn, error) {
db, err := sql.Open(driverName, dataSource)
return &Conn{db}, err
}
func TestDelete(t *testing.T) {
db, err := sql.Open("sqlite3", "./foo.db")
if err != nil {
t.Errorf("Failed to select records:", err)
return
}
defer os.Remove("./foo.db")
_, err = db.Exec("drop table foo")
_, err = db.Exec("create table foo (id integer)")
if err != nil {
t.Errorf("Failed to create table:", err)
return
}
res, err := db.Exec("insert into foo(id) values(123)")
if err != nil {
t.Errorf("Failed to insert record:", err)
return
}
expected, err := res.LastInsertId()
if err != nil {
t.Errorf("Failed to get LastInsertId:", err)
return
}
affected, err := res.RowsAffected()
if err != nil {
t.Errorf("Failed to get RowsAffected:", err)
return
}
if affected != 1 {
t.Errorf("Expected %d for cout of affected rows, but %q:", 1, affected)
}
res, err = db.Exec("delete from foo where id = 123")
if err != nil {
t.Errorf("Failed to delete record:", err)
return
}
lastId, err := res.LastInsertId()
if err != nil {
t.Errorf("Failed to get LastInsertId:", err)
return
}
if expected != lastId {
t.Errorf("Expected %q for last Id, but %q:", expected, lastId)
}
affected, err = res.RowsAffected()
if err != nil {
t.Errorf("Failed to get RowsAffected:", err)
return
}
if affected != 1 {
t.Errorf("Expected %d for cout of affected rows, but %q:", 1, affected)
}
rows, err := db.Query("select id from foo")
if err != nil {
t.Errorf("Failed to select records:", err)
return
}
defer rows.Close()
if rows.Next() {
t.Errorf("Fetched row but expected not rows")
}
}