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
}
Beispiel #2
0
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)
	}
}
Beispiel #3
0
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")
}
Beispiel #8
0
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")
	}
}