sqlmock

package privatedb

import (
   "fmt"
   "github.com/DATA-DOG/go-sqlmock"
   _ "github.com/mattn/go-sqlite3"
   "github.com/stretchr/testify/assert"
   "testing"
)

func TestName2(t *testing.T) {
   //db, err := sql.Open("sqlite3", "./foo.db")
   //checkErr(err)

   //r, err := db.Exec("CREATE TABLE `userinfo` (\n    `uid` INTEGER PRIMARY KEY AUTOINCREMENT,\n    `username` VARCHAR(64) NULL,\n    `departname` VARCHAR(64) NULL,\n    `created` DATE NULL\n)")
   //assert.Nil(t, err)
   //fmt.Println(r)

   db, mock, err := sqlmock.New()
   if err != nil {
      panic(mock)
   }
   mock.ExpectPrepare("INSERT INTO userinfo")
   mock.ExpectExec("INSERT INTO userinfo").WillReturnResult(sqlmock.NewResult(1, 1))
   mock.ExpectPrepare("UPDATE userinfo")
   mock.ExpectExec("UPDATE userinfo").WillReturnResult(sqlmock.NewResult(1, 1))
   mock.ExpectQuery("SELECT *").WillReturnRows(sqlmock.NewRows([]string{"uid", "username", "department", "created"}).
      AddRow(3,"astaxie", "研发部门", "2012-12-09").
      AddRow("4","astaxie", "研发部门", "2012-12-09"))
   mock.ExpectPrepare("delete from userinfo")
   mock.ExpectExec("delete from userinfo").WillReturnResult(sqlmock.NewResult(1, 1))

   //插入数据
   stmt, err := db.Prepare("INSERT INTO userinfo(username, departname, created) values(?,?,?)")
   checkErr(err)

   res, err := stmt.Exec("astaxie", "研发部门", "2012-12-09")
   checkErr(err)

   id, err := res.LastInsertId()
   checkErr(err)

   fmt.Println(id)
   //更新数据
   stmt, err = db.Prepare("UPDATE userinfo set username=? where uid=?")
   checkErr(err)

   res, err = stmt.Exec("astaxieupdate", id)
   checkErr(err)

   affect, err := res.RowsAffected()
   checkErr(err)

   fmt.Println(affect)

   var uid int
   var username string
   var department string
   var created string
   rows, err := db.Query("SELECT * FROM userinfo")
   assert.Nil(t, err)
   for rows.Next() {
      err = rows.Scan(&uid, &username, &department, &created)
      checkErr(err)
      fmt.Println(uid)
      fmt.Println(username)
      fmt.Println(department)
      fmt.Println(created)
   }

   //row := db.QueryRow("SELECT * FROM userinfo")
   //err = row.Scan(&uid, &username, &department, &created)
   //assert.Nil(t, err)


   //删除数据
   stmt, err = db.Prepare("delete from userinfo where uid=?")
   checkErr(err)

   res, err = stmt.Exec(id)
   checkErr(err)

   affect, err = res.RowsAffected()
   checkErr(err)

   fmt.Println(affect)

   db.Close()
   //os.Remove("./foo.db")
}