func DoTestDeleteSQL(assert *assrt.Assert, info dialectSyntax) {
assert.Logf("Dialect %T\n", info.dialect)
model := structPtrToModel(sqlGenSampleData, true, nil)
criteria := &Criteria{model: model}
criteria.mergePkCondition(info.dialect)
sql, _ := info.dialect.DeleteSql(criteria)
sql = info.dialect.SubstituteMarkers(sql)
assert.Equal(info.deleteSql, sql)
}
func setupDb(assert *assrt.Assert, info dialectInfo) (*Migration, *Qbs) {
db1, err := info.openDbFunc()
assert.MustNil(err)
mg := NewMigration(db1, dbName, info.dialect)
db2, err := info.openDbFunc()
assert.MustNil(err)
q := New(db2, info.dialect)
q.Log = true
return mg, q
}
func DoTestValidation(assert *assrt.Assert, info dialectInfo) {
mg, q := setupDb(assert, info)
valid := new(ValidatorTable)
mg.dropTableIfExists(valid)
mg.CreateTableIfNotExists(valid)
valid.Name = "ok"
q.Save(valid)
valid.Id = 0
_, err := q.Save(valid)
assert.MustNotNil(err)
assert.Equal("name already taken", err.Error())
}
func DoTestBoolType(assert *assrt.Assert, info dialectInfo) {
type BoolType struct {
Id int64
Active bool
}
bt := new(BoolType)
mg, q := setupDb(assert, info)
defer mg.Close()
defer q.Close()
mg.dropTableIfExists(bt)
mg.CreateTableIfNotExists(bt)
bt.Active = true
q.Save(bt)
bt.Active = false
q.WhereEqual("active", true).Find(bt)
assert.True(bt.Active)
}
func DoTestStringPk(assert *assrt.Assert, info dialectInfo) {
type StringPk struct {
Tag string `qbs:"pk,size:16"`
Count int32
}
spk := new(StringPk)
spk.Tag = "health"
spk.Count = 10
mg, q := setupDb(assert, info)
defer mg.Close()
defer q.Close()
mg.dropTableIfExists(spk)
mg.CreateTableIfNotExists(spk)
affected, _ := q.Save(spk)
assert.Equal(1, affected)
spk.Count = 0
q.Find(spk)
assert.Equal(10, spk.Count)
}
func DoTestSelectionSQL(assert *assrt.Assert, info dialectSyntax) {
assert.Logf("Dialect %T\n", info.dialect)
type User struct {
Id int64
Name string
}
type Post struct {
Id int64
AuthorId int64 `qbs:"fk:Author"`
Author *User
Content string
}
model := structPtrToModel(new(Post), true, nil)
criteria := new(Criteria)
criteria.model = model
sql, _ := info.dialect.QuerySql(criteria)
assert.Equal(info.selectionSql, sql)
}
func assertLegitGraph(assert *assrt.Assert, g *Graph) {
assert.NotNil(g)
gstat, _ := os.Stat(filepath.Join(g.dir))
assert.True(gstat.IsDir())
assert.True(g.HasBranch("hroot/init"))
assert.Equal(
"",
g.cmd("ls-tree")("HEAD").Output(),
)
}
func DoTestQuerySQL(assert *assrt.Assert, info dialectSyntax) {
assert.Logf("Dialect %T\n", info.dialect)
type Student struct {
Name string
Grade int
Score int
}
model := structPtrToModel(new(Student), true, nil)
criteria := new(Criteria)
criteria.model = model
condition := NewInCondition("grade", []interface{}{6, 7, 8})
subCondition := NewCondition("score <= ?", 60).Or("score >= ?", 80)
condition.AndCondition(subCondition)
criteria.condition = condition
criteria.orderBys = []order{order{info.dialect.Quote("name"), false}, order{info.dialect.Quote("grade"), true}}
criteria.offset = 3
criteria.limit = 10
sql, _ := info.dialect.QuerySql(criteria)
sql = info.dialect.SubstituteMarkers(sql)
assert.Equal(info.querySql, sql)
}
func DoTestCreateTable(assert *assrt.Assert, info dialectInfo) {
assert.Logf("Dialect %T\n", info.dialect)
mg, _ := setupDb(assert, info)
defer mg.Close()
{
type AddColumn struct {
Prim int64 `qbs:"pk"`
}
table := &AddColumn{}
mg.dropTableIfExists(table)
mg.CreateTableIfNotExists(table)
columns := mg.Dialect.ColumnsInTable(mg, table)
assert.OneLen(columns)
assert.True(columns["prim"])
}
table := &AddColumn{}
mg.CreateTableIfNotExists(table)
assert.True(mg.Dialect.IndexExists(mg, "add_column", "add_column_first_last"))
columns := mg.Dialect.ColumnsInTable(mg, table)
assert.Equal(4, len(columns))
}
func DoTestCreateIndexSql(assert *assrt.Assert, info dialectSyntax) {
assert.Logf("Dialect %T\n", info.dialect)
sql := info.dialect.CreateIndexSql("iname", "itable", true, "a", "b", "c")
assert.Equal(info.createUniqueIndexSql, sql)
sql = info.dialect.CreateIndexSql("iname2", "itable2", false, "d", "e")
assert.Equal(info.createIndexSql, sql)
}
func DoTestCreateTable(assert *assrt.Assert, info dialectInfo) {
assert.Logf("Dialect %T\n", info.dialect)
mg, _ := setupDb(assert, info)
{
type AddColumn struct {
Prim Id
}
table := &AddColumn{}
mg.dropTableIfExists(table)
mg.CreateTableIfNotExists(table)
columns := mg.Dialect.ColumnsInTable(mg, table)
assert.OneLen(columns)
assert.True(columns["prim"])
}
table := &AddColumn{}
mg.CreateTableIfNotExists(table)
columns := mg.Dialect.ColumnsInTable(mg, table)
assert.Equal(4, len(columns))
}
func DoTestCreateTableSql(assert *assrt.Assert, info dialectSyntax) {
assert.Logf("Dialect %T\n", info.dialect)
type withoutPk struct {
First string
Last string
Amount int
}
table := &withoutPk{"a", "b", 5}
model := structPtrToModel(table, true, nil)
sql := info.dialect.CreateTableSql(model, true)
assert.Equal(info.createTableWithoutPkIfExistsSql, sql)
type withPk struct {
Primary int64 `qbs:"pk"`
First string
Last string
Amount int
}
table2 := &withPk{First: "a", Last: "b", Amount: 5}
model = structPtrToModel(table2, true, nil)
sql = info.dialect.CreateTableSql(model, false)
assert.Equal(info.createTableWithPkSql, sql)
}
func DoTestAddColumSQL(assert *assrt.Assert, info dialectSyntax) {
assert.Logf("Dialect %T\n", info.dialect)
sql := info.dialect.AddColumnSql("a", "c", "", 100)
assert.Equal(info.addColumnSql, sql)
}
func DoTestDropTableSQL(assert *assrt.Assert, info dialectSyntax) {
assert.Logf("Dialect %T\n", info.dialect)
sql := info.dialect.DropTableSql("drop_table")
assert.Equal(info.dropTableIfExistsSql, sql)
}
func DoTestSaveAndDelete(assert *assrt.Assert, info dialectInfo) {
x := time.Now()
assert.MustZero(x.Sub(x.UTC()))
now := time.Now()
mg, q := setupDb(assert, info)
type saveModel struct {
Id Id
A string
B int
Updated time.Time
Created time.Time
}
model1 := saveModel{
A: "banana",
B: 5,
}
model2 := saveModel{
A: "orange",
B: 4,
}
mg.dropTableIfExists(&model1)
mg.CreateTableIfNotExists(&model1)
affected, err := q.Save(&model1)
assert.MustNil(err)
assert.Equal(1, affected)
assert.True(model1.Created.Sub(now) > 0)
assert.True(model1.Updated.Sub(now) > 0)
// make sure created/updated values match the db
var model1r []*saveModel
err = q.Where("id = ?", model1.Id).FindAll(&model1r)
assert.MustNil(err)
assert.MustOneLen(model1r)
assert.Equal(model1.Created.Unix(), model1r[0].Created.Unix())
assert.Equal(model1.Updated.Unix(), model1r[0].Updated.Unix())
oldCreate := model1.Created
oldUpdate := model1.Updated
model1.A = "grape"
model1.B = 9
time.Sleep(time.Second * 1) // sleep for 1 sec
affected, err = q.Save(&model1)
assert.MustNil(err)
assert.MustEqual(1, affected)
assert.True(model1.Created.Equal(oldCreate))
assert.True(model1.Updated.Sub(oldUpdate) > 0)
// make sure created/updated values match the db
var model1r2 []*saveModel
err = q.Where("id = ?", model1.Id).FindAll(&model1r2)
assert.MustNil(err)
assert.MustOneLen(model1r2)
assert.True(model1r2[0].Updated.Sub(model1r2[0].Created) >= 1)
assert.Equal(model1.Created.Unix(), model1r2[0].Created.Unix())
assert.Equal(model1.Updated.Unix(), model1r2[0].Updated.Unix())
affected, err = q.Save(&model2)
assert.MustNil(err)
assert.Equal(1, affected)
affected, err = q.Delete(&model2)
assert.MustNil(err)
assert.Equal(1, affected)
}
func DoTestTransaction(assert *assrt.Assert, info dialectInfo) {
mg, q := setupDb(assert, info)
type txModel struct {
Id Id
A string
}
table := txModel{
A: "A",
}
mg.dropTableIfExists(&table)
mg.CreateTableIfNotExists(&table)
q.Begin()
assert.NotNil(q.Tx)
_, err := q.Save(&table)
assert.Nil(err)
err = q.Rollback()
assert.Nil(err)
out := new(txModel)
err = q.Find(out)
assert.Nil(err)
assert.Zero(out.Id)
q.Begin()
table.Id = 0
_, err = q.Save(&table)
assert.Nil(err)
err = q.Commit()
assert.Nil(err)
err = q.Find(out)
assert.Nil(err)
assert.Equal("A", out.A)
}
func DoTestForeignKey(assert *assrt.Assert, info dialectInfo) {
mg, q := setupDb(assert, info)
type user struct {
Id Id
Name string
}
type post struct {
Id Id
Title string
AuthorId int64
Author *user
}
aUser := &user{
Name: "john",
}
aPost := &post{
Title: "A Title",
}
mg.dropTableIfExists(aPost)
mg.dropTableIfExists(aUser)
mg.CreateTableIfNotExists(aUser)
mg.CreateTableIfNotExists(aPost)
uid, err := q.Save(aUser)
assert.Nil(err)
aPost.AuthorId = int64(uid)
affected, err := q.Save(aPost)
assert.Equal(1, affected)
pst := new(post)
pst.Id = aPost.Id
err = q.Find(pst)
assert.MustNil(err)
assert.MustNotNil(pst)
assert.Equal(uid, pst.Id)
assert.Equal("john", pst.Author.Name)
}
func DoTestForeignKey(assert *assrt.Assert, info dialectInfo) {
mg, q := setupDb(assert, info)
defer mg.Close()
defer q.Close()
type user struct {
Id int64
Name string
}
type post struct {
Id int64
Title string
AuthorId int64
Author *user
}
aUser := &user{
Name: "john",
}
aPost := &post{
Title: "A Title",
}
mg.dropTableIfExists(aPost)
mg.dropTableIfExists(aUser)
mg.CreateTableIfNotExists(aUser)
mg.CreateTableIfNotExists(aPost)
affected, err := q.Save(aUser)
assert.Nil(err)
aPost.AuthorId = int64(aUser.Id)
affected, err = q.Save(aPost)
assert.Equal(1, affected)
pst := new(post)
pst.Id = aPost.Id
err = q.Find(pst)
assert.MustNil(err)
assert.Equal(aPost.Id, pst.Id)
assert.Equal("john", pst.Author.Name)
pst.Author = nil
err = q.OmitFields("Author").Find(pst)
assert.MustNil(err)
assert.MustNil(pst.Author)
err = q.OmitJoin().Find(pst)
assert.MustNil(err)
assert.MustNil(pst.Author)
var psts []*post
err = q.FindAll(&psts)
assert.MustNil(err)
assert.OneLen(psts)
assert.Equal("john", psts[0].Author.Name)
}
func DoTestUpdate(assert *assrt.Assert, info dialectInfo) {
mg, q := setupDb(assert, info)
mg.dropTableIfExists(&basic{})
mg.CreateTableIfNotExists(&basic{})
_, err := q.Save(&basic{Name: "a", State: 1})
_, err = q.Save(&basic{Name: "b", State: 1})
_, err = q.Save(&basic{Name: "c", State: 0})
assert.MustNil(err)
{
// define a temporary struct in a block to update partial columns of a table
// as the type is in a block, so it will not conflict with other types with the same name in the same method
type basic struct {
Name string
}
affected, err := q.Where("state = ?", 1).Update(&basic{Name: "d"})
assert.MustNil(err)
assert.Equal(2, affected)
var datas []*basic
q.Where("state = ?", 1).FindAll(&datas)
assert.MustEqual(2, len(datas))
assert.Equal("d", datas[0].Name)
assert.Equal("d", datas[1].Name)
}
// if choose basic table type to update, all zero value in the struct will be updated too.
// this may be cause problems, so define a temporary struct to update table is the recommended way.
affected, err := q.Where("state = ?", 1).Update(&basic{Name: "e"})
assert.MustNil(err)
assert.Equal(2, affected)
var datas []*basic
q.Where("state = ?", 1).FindAll(&datas)
assert.MustEqual(0, len(datas))
}
func DoTestFind(assert *assrt.Assert, info dialectInfo) {
mg, q := setupDb(assert, info)
now := time.Now()
type types struct {
Id Id
Str string
Intgr int64
Flt float64
Bytes []byte
Time time.Time
}
modelData := &types{
Str: "string!",
Intgr: -1,
Flt: 3.8,
Bytes: []byte("bytes!"),
Time: now,
}
mg.dropTableIfExists(modelData)
mg.CreateTableIfNotExists(modelData)
out := new(types)
condition := NewCondition("str = ?", "string!").And("intgr = ?", -1)
err := q.Condition(condition).Find(out)
assert.Nil(err)
assert.Zero(out.Id)
affected, err := q.Save(modelData)
assert.Nil(err)
assert.Equal(1, affected)
err = q.Condition(condition).Find(out)
assert.Nil(err)
assert.Equal(1, out.Id)
assert.Equal("string!", out.Str)
assert.Equal(-1, out.Intgr)
assert.Equal(3.8, out.Flt)
assert.Equal([]byte("bytes!"), out.Bytes)
diff := now.Sub(out.Time)
assert.True(diff < time.Second && diff > -time.Second)
modelData.Id = 5
modelData.Str = "New row"
_, err = q.Save(modelData)
assert.Nil(err)
out = new(types)
condition = NewCondition("str = ?", "New row").And("flt = ?", 3.8)
err = q.Condition(condition).Find(out)
assert.Nil(err)
assert.Equal(5, out.Id)
allOut := []*types{}
err = q.Where("intgr = ?", -1).FindAll(&allOut)
assert.Nil(err)
assert.Equal(2, len(allOut))
}