代码拉取完成，页面将自动刷新
this is powerful sql orm library for Golang, have some funny features
version go-1.9
import database driver
// if database is mysql
_ "github.com/go-sql-driver/mysql"
// if database is sqlite3
_ "github.com/mattn/go-sqlite3"
// when database is postgres
_ "github.com/lib/pq"
create a toy
// if database is mysql, make sure your mysql have toyorm_example schema
toy, err = toyorm.Open("mysql", "root:@tcp(localhost:3306)/toyorm_example?charset=utf8&parseTime=True")
// if database is sqlite3
toy,err = toyorm.Open("sqlite3", "toyorm_test.db")
// when database is postgres
toy, err = toyorm.Open("postgres", "user=postgres dbname=toyorm sslmode=disable")
type Extra map[string]interface{}
func (e Extra) Scan(value interface{}) error {
switch v := value.(type) {
case string:
return json.Unmarshal([]byte(v), e)
case []byte:
return json.Unmarshal(v, e)
default:
return errors.New("not support type")
}
}
func (e Extra) Value() (driver.Value, error) {
return json.Marshal(e)
}
type UserDetail struct {
ID int `toyorm:"primary key;auto_increment"`
UserID uint `toyorm:"index"`
MainPage string
Extra Extra `toyorm:"type:VARCHAR(1024)"`
}
type Blog struct {
toyorm.ModelDefault
UserID uint `toyorm:"index"`
Title string `toyorm:"index"`
Content string
}
type User struct {
toyorm.ModelDefault
Name string `toyorm:"unique index"`
Age int
Sex string
Detail *UserDetail
Friends []*User
Blog []Blog
}
if sql type is not match, toyorm will ignore it in Sql Operation
you can use <type:sql_type> field tag to specified their sql type
the following type will auto translate to sql type
| Go Type | sql type |
|---|---|
| bool | BOOLEAN |
| int8,int16,int32,uint8,uint16,uint32 | INTEGER |
| int64,uint64,int,uint | BIGINT |
| float32,float64 | FLOAT |
| string | VARCHAR(255) |
| time.Time | TIMESTAMP |
| []byte | VARCHAR(255) |
| sql.NullBool | BOOLEAN |
| sql.NullInt64 | BIGINT |
| sql.NullFloat64 | FLOAT |
| sql.NullString | VARCHAR(255) |
| sql.RawBytes | VARCHAR(255) |
special fields
| Field Name | Type | Description |
|---|---|---|
| CreatedAt | time.Time | generate when element be create |
| UpdatedAt | time.Time | generate when element be update/create |
| DeletedAt | *time.Time | delete mode is soft |
| Cas | int | (not support for sqlite3)save operation will failure when it's value modified by third party e.g in postgres:cas=1 insert xxx conflict(id) update cas = 2 where cas = 1 |
field tags
tag format can be <key:value> or <key>
the following is special tag
| Key | Value | Description |
|---|---|---|
| index | void or string | use for optimization when search condition have this field,if you want make a combined,just set same index name with fields |
| unique index | void or string | have unique limit index, other same as index |
| primary key | void | allow multiple primary key,but some operation not support |
| - | void | ignore this field in sql |
| type | string | sql type |
| column | string | sql column name |
| auto_increment | void | recommend, if your table primary key have auto_increment attribute must add it |
| autoincrement | void | same as auto_increment |
| foreign key | void | to add foreign key feature when create table |
| alias | string | change field name with toyorm |
| join | string | to select related field when call brick.Join |
| belong to | string | to select related field when call brick.Preload with BelongTo container |
| one to one | string | to select related field when call brick.Preload with OneToOne container |
| one to many | string | to select related field when call brick.Preload with OneToMany container |
| default | srring | default value in database |
other custom TAG will append to end of CREATE TABLE field
model kind must be a struct or a point with struct
the model is what information that toyorm know about the table
brick := toy.Model(&User{})
// or
brick := toy.Model(User{})
var err error
_, err = toy.Model(&User{}).Debug().CreateTable()
// CREATE TABLE user (id BIGINT AUTO_INCREMENT,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,deleted_at TIMESTAMP NULL,name VARCHAR(255),age BIGINT ,sex VARCHAR(255) , PRIMARY KEY(id))
// CREATE INDEX idx_user_deletedat ON user(deleted_at)
// CREATE UNIQUE INDEX udx_user_name ON user(name)
_, err =toy.Model(&UserDetail{}).Debug().CreateTable()
// CREATE TABLE user_detail (id BIGINT AUTO_INCREMENT,user_id BIGINT,main_page Text,extra VARCHAR(1024), PRIMARY KEY(id))
// CREATE INDEX idx_user_detail_userid ON user_detail(user_id)
_, err =toy.Model(&Blog{}).Debug().CreateTable()
// CREATE TABLE blog (id BIGINT AUTO_INCREMENT,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,deleted_at TIMESTAMP NULL,user_id BIGINT,title VARCHAR(255),content VARCHAR(255) , PRIMARY KEY(id))
// CREATE INDEX idx_blog_deletedat ON blog(deleted_at)
// CREATE INDEX idx_blog_userid ON blog(user_id)
// CREATE INDEX idx_blog_title ON blog(title)
var err error
_, err =toy.Model(&User{}).Debug().DropTable()
// DROP TABLE user
_, err =toy.Model(&UserDetail{}).Debug().DropTable()
// DROP TABLE user_detail
_, err =toy.Model(&Blog{}).Debug().DropTable()
// DROP TABLE blog
// insert with autoincrement will set id to source data
user := &User{
Name: "bigpigeon",
Age: 18,
Sex: "male",
}
_, err = toy.Model(&User{}).Debug().Insert(&user)
// INSERT INTO user(created_at,updated_at,name,age,sex) VALUES(?,?,?,?,?) , args:[]interface {}{time.Time{wall:0xbe8df5112e7f07c8, ext:210013499, loc:(*time.Location)(0x141af80)}, time.Time{wall:0xbe8df5112e7f1768, ext:210017044, loc:(*time.Location)(0x141af80)}, "bigpigeon", 18, "male"}
// print user format with json
/* {
"ID": 1,
"CreatedAt": "2018-01-11T20:47:00.780077+08:00",
"UpdatedAt": "2018-01-11T20:47:00.780081+08:00",
"DeletedAt": null,
"Name": "bigpigeon",
"Age": 18,
"Sex": "male",
"Detail": null,
"Friends": null,
"Blog": null
}*/
// save data use "REPLACE INTO" when primary key exist
users := []User{
{
ModelDefault: toyorm.ModelDefault{ID: 1},
Name: "bigpigeon",
Age: 18,
Sex: "male",
},
{
Name: "fatpigeon",
Age: 27,
Sex: "male",
},
}
_, err = toy.Model(&User{}).Debug().Save(&user)
// SELECT id,created_at FROM user WHERE id IN (?), args:[]interface {}{0x1}
// REPLACE INTO user(id,created_at,updated_at,name,age,sex) VALUES(?,?,?,?,?,?) , args:[]interface {}{0x1, time.Time{wall:0x0, ext:63651278036, loc:(*time.Location)(nil)}, time.Time{wall:0xbe8dfb5511465918, ext:302600558, loc:(*time.Location)(0x141af80)}, "bigpigeon", 18, "male"}
// INSERT INTO user(created_at,updated_at,name,age,sex) VALUES(?,?,?,?,?) , args:[]interface {}{time.Time{wall:0xbe8dfb551131b7d8, ext:301251230, loc:(*time.Location)(0x141af80)}, time.Time{wall:0xbe8dfb5511465918, ext:302600558, loc:(*time.Location)(0x141af80)}, "fatpigeon", 27, "male"}
toy.Model(&User{}).Debug().Update(&User{
Age: 4,
})
// UPDATE user SET updated_at=?,age=? WHERE deleted_at IS NULL, args:[]interface {}{time.Time{wall:0xbe8df4eb81b6c050, ext:233425327, loc:(*time.Location)(0x141af80)}, 4}
find one
var user User
_, err = toy.Model(&User{}).Debug().Find(&user}
// SELECT id,created_at,updated_at,deleted_at,name,age,sex FROM user WHERE deleted_at IS NULL LIMIT 1, args:[]interface {}(nil)
// print user format with json
/* {
"ID": 1,
"CreatedAt": "2018-01-11T12:47:01Z",
"UpdatedAt": "2018-01-11T12:47:01Z",
"DeletedAt": null,
"Name": "bigpigeon",
"Age": 4,
"Sex": "male",
"Detail": null,
"Friends": null,
"Blog": null
}*/
find multiple
var users []User
_, err = brick.Debug().Find(&users)
fmt.Printf("find users %s\n", JsonEncode(&users))
// SELECT id,created_at,updated_at,deleted_at,name,age,sex FROM user WHERE deleted_at IS NULL, args:[]interface {}(nil)
delete with primary key
_, err = brick.Debug().Delete(&user)
// UPDATE user SET deleted_at=? WHERE id IN (?), args:[]interface {}{(*time.Time)(0xc4200f0520), 0x1}
delete with condition
_, err = brick.Debug().Where(toyorm.ExprEqual, Offsetof(User{}.Name), "bigpigeon").DeleteWithConditions()
// UPDATE user SET deleted_at=? WHERE name = ?, args:[]interface {}{(*time.Time)(0xc4200dbfa0), "bigpigeon"}
use toy.Model will create a ToyBrick, you need use it to build grammar and operate the database
affective update/find/delete operation
where will clean old conditions and make new one
brick.Where(<expr>, <Key>, [value])
whereGroup add multiple condition with same expr
brick.WhereGroup(<expr>, <group>)
conditions will copy conditions and clean old conditions
brick.Conditions(<toyorm.Search>)
or & and condition will use or/and to link new condition when current condition is not nil
brick.Or().Condition(<expr>, <Key>, [value])
brick.Or().ConditionGroup(<expr>, <group>)
brick.And().Condition(<expr>, <Key>, [value])
or & and conditions will use or/and to link new conditions
brick.Or().Conditions(<toyorm.Search>)
brick.And().Conditions(<toyorm.Search>)
| SearchExpr | to sql | example |
|---|---|---|
| ExprAnd | AND | brick.WhereGroup(ExprAnd, Product{Name:"food one", Count: 4}) // WHERE name = "food one" AND Count = 4 |
| ExprOr | OR | brick.WhereGroup(ExprOr, Product{Name:"food one", Count: 4}) // WHERE name = "food one" OR Count = "4" |
| ExprEqual | = | brick.Where(ExprEqual, OffsetOf(Product{}.Name), "food one") // WHERE name = "find one" |
| ExprNotEqual | <> | brick.Where(ExprNotEqual, OffsetOf(Product{}.Name), "food one") // WHERE name <> "find one" |
| ExprGreater | > | brick.Where(ExprGreater, OffsetOf(Product{}.Count), 3) // WHERE count > 3 |
| ExprGreaterEqual | >= | brick.Where(ExprGreaterEqual, OffsetOf(Product{}.Count), 3) // WHERE count >= 3 |
| ExprLess | < | brick.Where(ExprLess, OffsetOf(Product{}.Count), 3) // WHERE count < 3 |
| ExprLessEqual | <= | brick.Where(ExprLessEqual, OffsetOf(Product{}.Count), 3) // WHERE count <= 3 |
| ExprBetween | Between | brick.Where(ExprBetween, OffsetOf(Product{}.Count), [2]int{2,3}) // WHERE count BETWEEN 2 AND 3 |
| ExprNotBetween | NOT Between | brick.Where(ExprNotBetween, OffsetOf(Product{}.Count), [2]int{2,3}) // WHERE count NOT BETWEEN 2 AND 3 |
| ExprIn | IN | brick.Where(ExprIn, OffsetOf(Product{}.Count), []int{1, 2, 3}) // WHERE count IN (1,2,3) |
| ExprNotIn | NOT IN | brick.Where(ExprNotIn, OffsetOf(Product{}.Count), []int{1, 2, 3}) // WHERE count NOT IN (1,2,3) |
| ExprLike | LIKE | brick.Where(ExprLike, OffsetOf(Product{}.Name), "one") // WHERE name LIKE "one" |
| ExprNotLike | NOT LIKE | brick.Where(ExprNotLike, OffsetOf(Product{}.Name), "one") // WHERE name NOT LIKE "one" |
| ExprNull | IS NULL | brick.Where(ExprNull, OffsetOf(Product{}.DeletedAt)) // WHERE DeletedAt IS NULL |
| ExprNotNull | IS NOT NULL | brick.Where(ExprNotNull, OffsetOf(Product{}.DeletedAt)) // WHERE DeletedAt IS NOT NULL |
single condition
brick = brick.Where(toyorm.ExprEqual, Offsetof(Product{}.Tag), "food")
or use string
brick = brick.Where("=", Offsetof(Product{}.Tag), "food")
// WHERE tag = "food"
combination condition
brick = brick.Where(toyorm.ExprEqual, Offsetof(Product{}.Count), 2).And().
Condition(toyorm.ExprGreater, Offsetof(Product{}.Price), 3).Or().
Condition(toyorm.ExprEqual, Offsetof(Product{}.Count), 4)
or use string
brick = brick.Where("=", Offsetof(Product{}.Count), 2).And().
Condition(">", Offsetof(Product{}.Price), 3).Or().
Condition("=", Offsetof(Product{}.Count), 4)
// WHERE count = 2 and price > 3 or count = 4
priority condition
brick.Where(toyorm.ExprGreater, Offsetof(Product{}.Price), 3).And().Conditions(
brick.Where(toyorm.ExprEqual, Offsetof(Product{}.Count), 2).Or().
Condition(toyorm.ExprEqual, Offsetof(Product{}.Count), 1).Search
)
or use string
brick.Where(">", Offsetof(Product{}.Price), 3).And().Conditions(
brick.Where("=", Offsetof(Product{}.Count), 2).Or().
Condition("=", Offsetof(Product{}.Count), 1).Search
)
// WHERE price > 3 and (count = 2 or count = 1)
brick.Conditions(
brick.Where(toyorm.ExprEqual, Offsetof(Product{}.Count), 2).Or().
Condition(toyorm.ExprEqual, Offsetof(Product{}.Count), 1).Search,
).And().Conditions(
brick.Where(toyorm.ExprEqual, Offsetof(Product{}.Price), 3).Or().
Condition(toyorm.ExprEqual, Offsetof(Product{}.Price), 4).Search,
)
or use string
brick.Conditions(
brick.Where("=", Offsetof(Product{}.Count), 2).Or().
Condition("=", Offsetof(Product{}.Count), 1).Search,
).And().Conditions(
brick.Where("=", Offsetof(Product{}.Price), 3).Or().
Condition("=", Offsetof(Product{}.Price), 4).Search,
)
// WHERE (count = ? OR count = ?) AND (price = ? OR price = ?)
limit & offset
brick := brick.Offset(2).Limit(2)
// LIMIT 2 OFFSET 2
order by
brick = brick.OrderBy(Offsetof(Product{}.Name))
// ORDER BY name
order by desc
brick = brick.OrderBy(brick.ToDesc(Offsetof(Product{}.Name)))
// ORDER BY name DESC
sometimes the condition of sql is not a normal field, use TempField to wrapper normal field
brick = brick.Where("=", brick.TempField(Offsetof(Product{}.Name), "LOWER(%s)"), "name")
// WHERE LOWER(name) = ?
start a transaction
brick = brick.Begin()
rollback all sql action
err = brick.Rollback()
commit all sql action
err = brick.Commit()
if Set debug all sql action will have log
brick = brick.Debug()
when I Update or Search with struct that have some zero value, did I update it ?
use IgnoreMode to differentiate what zero value should update
brick = brick.IgnoreMode(toyorm.Mode("Update"), toyorm.IgnoreZero ^ toyorm.IgnoreZeroLen)
// ignore all zeor value but excloud zero len slice
// now field = []int(nil) will ignore when update
// but field = []int{} will update when update
// now field = map[int]int(nil) will ignore when update
// but field = map[int]int{} will update when update
In default
| Operation | Mode | affect |
|---|---|---|
| Insert | IgnoreNo | brick.Insert() |
| Replace | IgnoreNo | brick.Replace() |
| Condition | IgnoreZero | brick.WhereGroup(ExprAnd/ExprOr, ) |
| Update | IgnoreZero | brick.Update() |
All of IgnoreMode
| mode | effective |
|---|---|
| IgnoreFalse | ignore field type is bool and value is false |
| IgnoreZeroInt | ignore field type is int/uint/uintptr(incloud their 16,32,64 bit type) and value is 0 |
| IgnoreZeroFloat | ignore field type is float32/float64 and value is 0.0 |
| IgnoreZeroComplex | ignore field type is complex64/complex128 and value is 0 + 0i |
| IgnoreNilString | ignore field type is string and value is "" |
| IgnoreNilPoint | ignore field type is point/map/slice and value is nil |
| IgnoreZeroLen | ignore field type is map/array/slice and len = 0 |
| IgnoreNullStruct | ignore field type is struct and value is zero value struct e.g type A struct{A string,B int}, A{"", 0} will be ignore |
| IgnoreNil | ignore with IgnoreNilPoint and IgnoreZeroLen |
| IgnoreZero | ignore all of the above |
if bind a not null fields, the IgnoreMode will failure
{
var p Product
result, err := brick.BindDefaultFields(Offsetof(p.Price), Offsetof(p.UpdatedAt)).Update(&Product{
Price: 0,
})
// process error
...
}
var products []Product
result, err = brick.Find(&products)
// process error
...
for _, p := range products {
fmt.Printf("product name %s, price %v\n", p.Name, p.Price)
}
use scope to do some custom operation
// desc all order by fields
brick.Scope(func(t *ToyBrick) *ToyBrick{
newOrderBy := make([]*ModelFields, len(t.orderBy))
for i, f := range t.orderBy {
newOrderBy = append(newOrderBy, t.ToDesc(f))
}
newt := *t
newt.orderBy = newOrderBy
return &newt
})
use template exec to replace default exec
now template only support Insert/Save/Update/Find
data := Product{
Name: "bag",
Price: 9999,
Count: 2,
Tag: "container",
}
result, err := brick.Template("INSERT INTO $ModelName($Columns) Values($Values)").Insert(&data)
// INSERT INTO product(created_at,updated_at,deleted_at,name,price,count,tag) Values(?,?,?,?,?,?,?) args:["2018-04-01T17:05:48.927499+08:00","2018-04-01T17:05:48.927499+08:00",null,"bag",9999,2,"container"]
var data Product
// if driver is mysql use "USE INDEX" replace "INDEXED BY"
result, err := brick.Template("SELECT $Columns FROM $ModelName INDEXED BY idx_product_name $Conditions").
Where("=", Offsetof(Product{}.Name), "bag").Find(&data)
// SELECT id,created_at,updated_at,deleted_at,name,price,count,tag FROM product INDEXED BY idx_product_name WHERE deleted_at IS NULL AND name = ? LIMIT 1 args:["bag"]
set count = count + 2
result, err := brick.Template(fmt.Sprintf("UPDATE $ModelName SET $Values,$FN-Count = $0x%x + ? $Conditions", Offsetof(Product{}.Count)), 2).
Where("=", Offsetof(Product{}.Name), "bag").Update(&Product{Price: 200})
// UPDATE product SET updated_at = ?,price = ?,count = count + ? WHERE deleted_at IS NULL AND name = ? args:["2018-04-01T17:50:35.205377+08:00",200,2,"bag"]
follower placeholder use in template example
two special placeholder
| action \ placeholder | $ModelName | $Columns | $Values | $Conditions |
|---|---|---|---|---|
| Find | product | id,data,... | - | WHERE ... ORDER BY ... GROUP BY ... LIMIT ... OFFSET ... |
| Insert | product | id,data,... | ?,?,... | WHERE ... ORDER BY ... GROUP BY ... LIMIT ... OFFSET ... |
| Save | product | id,data,... | ?,?,... | WHERE ... ORDER BY ... GROUP BY ... LIMIT ... OFFSET ... |
| Update | product | id,data,... | id = ?,data = ?,... | WHERE ... ORDER BY ... GROUP BY ... LIMIT ... OFFSET ... |
Thread safe if you comply with the following agreement
make sure ToyBrick object is read only, if you want to change it, create a new one
do not use append to change ToyBrick's slice data,use make and copy to clone new slice
preload need have relation field and container field
relations field is used to link the main record and sub record
container field is used to hold sub record
relation field at sub model
relation field name must be main model type name + main model primary key name
type User struct {
toyorm.ModelDefault
// container field
Detail *UserDetail
}
type UserDetail struct {
ID int `toyorm:"primary key;auto_increment"`
// relation field
UserID uint `toyorm:"index"`
MainPage string `toyorm:"type:Text"`
}
// load preload
brick = toy.Model(&User{}).Debug().Preload(OffsetOf(User.Detail)).Enter()
relation field at main model
relation field name must be container field name + sub model primary key name
type User struct {
toyorm.ModelDefault
// container field
Detail *UserDetail
// relation field
DetailID int `toyorm:"index"`
}
type UserDetail struct {
ID int `toyorm:"primary key;auto_increment"`
MainPage string `toyorm:"type:Text"`
}
relation field at sub model
relation field name must be main model type name + main model primary key name
type User struct {
toyorm.ModelDefault
// container field
Blog []Blog
}
type Blog struct {
toyorm.ModelDefault
// relation field
UserID uint `toyorm:"index"`
Title string `toyorm:"index"`
Content string
}
many to many not need to specified the relation ship,it relation field at middle model
type User struct {
toyorm.ModelDefault
// container field
Friends []*User
}
when you finish model definition, it time to load preload
// create a main brick
brick = toy.Model(&User{})
// create a sub brick
subBrick := brick.Preload(OffsetOf(User.Blog))
// you can editing any attribute what you want, just like editing it on main model
subBrick = subBrick.Where(ExprEqual, OffsetOf(Blog.Title), "my blog")
// finished change ,use Enter() go back the main brick
brick = subBrick.Enter()
if you not like relation field name rule,use custom module to create it
// one to one custom
brick.CustomOneToOnePreload(<main container>, <sub relation>, [sub model struct])
// belong to custom
brick.CustomBelongToPreload(<main container>, <main relation>, [sub model struct])
// one to many
brick.CustomOneToManyPreload(<main container>, <sub relation>, [sub model struct])
// many to many
brick.CustomManyToManyPreload(<middle model struct>, <main container>, <main relation>, <sub relation>, [sub model struct])
or use tag declaration
type UserDetail struct {
ID int `toyorm:"primary key;auto_increment"`
MainID uint32 `toyorm:"index;one to one:Detail"` // declaration the container field
MainPage string `toyorm:"type:Text"`
Extra Extra `toyorm:"type:VARCHAR(1024)"`
}
type Blog struct {
toyorm.ModelDefault
MainID uint32 `toyorm:"index;one to many:Blog"` // declaration the container field
Title string `toyorm:"index"`
Content string
}
type User struct {
toyorm.ModelDefault
Name string `toyorm:"unique index"`
Age int
Sex string
Detail *UserDetail
Friends []*User
Blog []Blog
}
// now custom relation field is MainID
brick = brick.Preload(Offsetof(User{}.Blog)).Enter()
brick = brick.Preload(Offsetof(User{}.Detail)).Enter()
different association query is join query
use join tag to association related field/ join tag value must same as container field name
type Extra map[string]interface{}
func (e *Extra) Scan(value interface{}) error {
switch v := value.(type) {
case string:
return json.Unmarshal([]byte(v), e)
case []byte:
return json.Unmarshal(v, e)
default:
return errors.New("not support type")
}
}
func (e Extra) Value() (driver.Value, error) {
return json.Marshal(e)
}
type Color struct {
Name string `toyorm:"primary key;join:ColorDetail"`
Code int32
}
type Comment struct {
toyorm.ModelDefault
ProductDetailProductID uint32 `toyorm:"index"`
Data string `toyorm:"type:VARCHAR(1024)"`
}
type ProductDetail struct {
ProductID uint32 `toyorm:"primary key;join:Detail"`
Title string
CustomPage string `toyorm:"type:text"`
Extra Extra `toyorm:"type:VARCHAR(2048)"`
Color string `toyorm:"join:ColorDetail"`
ColorJoin Color `toyorm:"alias:ColorDetail"`
Comment []Comment
}
type Product struct {
ID uint32 `toyorm:"primary key;auto_increment;join:Detail"`
CreatedAt time.Time `toyorm:"NULL"`
DeletedAt *time.Time `toyorm:"NULL"`
Name string
Count int
Price float64
Detail *ProductDetail
}
Swap on Join just like Enter on Preload, can back to previous data
brick := toy.Model(&tab).Debug().
Join(Offsetof(tab.Detail)).
Join(Offsetof(detailTab.ColorJoin)).Swap().Swap()
var scanData []Product
result, err = brick.Find(&scanData)
// SELECT m.id,m.created_at,m.deleted_at,m.name,m.count,m.price,m_0.product_id,m_0.title,m_0.custom_page,m_0.extra,m_0.color,m_0_0.name,m_0_0.code FROM `product` as `m` JOIN `product_detail` AS `m_0` ON m.id = m_0.product_id JOIN `color` AS `m_0_0` ON m_0.color = m_0_0.name WHERE m.deleted_at IS NULL
use Join to switch current model and add conditions
// where Product.Name = "clean stick" or Color.Name = "black"
brick := toy.Model(&tab).Debug().Where("=", Offsetof(tab.Name), "clean stick").
Join(Offsetof(tab.Detail)).
Join(Offsetof(detailTab.ColorJoin)).Or().Condition("=", Offsetof(colorTab.Name), "black").
Swap().Swap()
var scanData []Product
result, err = brick.Find(&scanData)
// SELECT m.id,m.created_at,m.deleted_at,m.name,m.count,m.price,m_0.product_id,m_0.title,m_0.custom_page,m_0.extra,m_0.color,m_0_0.name,m_0_0.code FROM `product` as `m` JOIN `product_detail` AS `m_0` ON m.id = m_0.product_id JOIN `color` AS `m_0_0` ON m_0.color = m_0_0.name WHERE m.deleted_at IS NULL AND (m.name = ? OR m_0_0.name = ?) args:["clean stick","black"]
set order just like add conditions
// where Product.Name = "clean stick" or Color.Name = "black"
brick := toy.Model(&tab).Debug().
Join(Offsetof(tab.Detail)).
Join(Offsetof(detailTab.ColorJoin)).OrderBy(Offsetof(colorTab.Name)).
Swap().Swap()
var scanData []Product
result, err = brick.Find(&scanData)
// SELECT m.id,m.created_at,m.deleted_at,m.name,m.count,m.price,m_0.product_id,m_0.title,m_0.custom_page,m_0.extra,m_0.color,m_0_0.name,m_0_0.code FROM `product` as `m` JOIN `product_detail` AS `m_0` ON m.id = m_0.product_id JOIN `color` AS `m_0_0` ON m_0.color = m_0_0.name WHERE m.deleted_at IS NULL ORDER BY m_0_0.name
also can set GroupBy but here not example
Preload method also work on Join mode
brick := toy.Model(&tab).Debug().
Join(Offsetof(tab.Detail)).Preload(Offsetof(detailTab.Comment)).Enter().
Join(Offsetof(detailTab.ColorJoin)).Swap().Swap()
var scanData []Product
result, err = brick.Find(&scanData)
// SELECT m.id,m.created_at,m.deleted_at,m.name,m.count,m.price,m_0.product_id,m_0.title,m_0.custom_page,m_0.extra,m_0.color,m_0_0.name,m_0_0.code FROM `product` as `m` JOIN `product_detail` AS `m_0` ON m.id = m_0.product_id JOIN `color` AS `m_0_0` ON m_0.color = m_0_0.name WHERE m.deleted_at IS NULL
// SELECT id,created_at,updated_at,deleted_at,product_detail_product_id,data FROM `comment` WHERE deleted_at IS NULL AND product_detail_product_id IN (?,?,?) args:[1,2,3]
custom your table name with different platform
type User struct {
ID uint32 `toyorm:"primary key"`
Name string `toyorm:"index"`
Platform string `toyorm:"-"`
}
func (u *User) TableName() string {
return "user_" + u.Platform
}
brick := toy.Model(&User{Platform:"p1"}).Debug()
brick.CreateTable()
// CREATE TABLE user_p1 (id BIGINT AUTO_INCREMENT,name VARCHAR(255), PRIMARY KEY(id))
brick := toy.Model(&User{Platform:"p2"}).Debug()
brick.CreateTable()
// CREATE TABLE user_p2 (id BIGINT AUTO_INCREMENT,name VARCHAR(255), PRIMARY KEY(id))
table name method also work on Preload and Join
type UserDetail struct {
ID uint32 `toyorm:"primary key"`
UserID uint32
Data string
}
func (u *UserDetail) TableName() string {
return "user_detail_" + u.Platform
}
type User struct {
ID uint32 `toyorm:"primary key"`
Name string `toyorm:"index"`
Detail *UserDetail
Platform string `toyorm:"-"`
}
func (u *User) TableName() string {
return "user_" + u.Platform
}
brick := toy.Model(&User{Platform:"p1", Detail:&UserDetail{Platform:"p1"}}).Debug().
Preload(Offsetof(User{}.UserDetail)).Enter()
brick.CreateTable()
// CREATE TABLE user_p1 (id BIGINT AUTO_INCREMENT,name VARCHAR(255), PRIMARY KEY(id))
// CREATE TABLE user_detail_p1 (id BIGINT AUTO_INCREMENT, user_id BIGINT, data VARCHAR(255), PRIMARY KEY(id))
in one to many or many to many mode , need to set it's first element val
type Address struct {
ID uint32 `toyorm:"primary key"`
UserID uint32
Addr string
Platform string `toyorm:"-"`
}
func (a *Address) TableName() string {
return "address_" + a.Platform
}
type User struct {
ID uint32 `toyorm:"primary key"`
Name string `toyorm:"index"`
Addresses []Address
Platform string `toyorm:"-"`
}
func (u *User) TableName() string {
return "user_" + u.Platform
}
brick := toy.Model(&User{Platform:"p1", Addresses:[]Address{{Platform:"p1"}}}).Debug().
Preload(Offsetof(User{}.Addresses)).Enter()
brick.CreateTable()
// CREATE TABLE user_p1 (id BIGINT AUTO_INCREMENT,name VARCHAR(255), PRIMARY KEY(id))
// CREATE TABLE address_p1 (id BIGINT AUTO_INCREMENT, user_id BIGINT, addr VARCHAR(255), PRIMARY KEY(id))
use Report to view sql action
report format
insert
user := User{
Detail: &UserDetail{
MainPage: "some html code with you page",
Extra: Extra{"title": "my blog"},
},
Blog: []Blog{
{Title: "how to write a blog", Content: "first ..."},
{Title: "blog introduction", Content: "..."},
},
Friends: []*User{
{
Detail: &UserDetail{
MainPage: "some html code with you page",
Extra: Extra{},
},
Blog: []Blog{
{Title: "some python tech", Content: "first ..."},
{Title: "my eleme_union_meal usage", Content: "..."},
},
Name: "fatpigeon",
Age: 18,
Sex: "male",
},
},
Name: "bigpigeon",
Age: 18,
Sex: "male",
}
result, err = brick.Save(&user)
// error process ...
fmt.Printf("report:\n%s\n", result.Report())
/*
// [0, ] means affected the 0 element
// [0-0, ] means affected the 0 element the 0 sub element
report:
[0, ] INSERT INTO user(created_at,updated_at,deleted_at,name,age,sex) VALUES(?,?,?,?,?,?) args:["2018-02-28T17:31:20.012285+08:00","2018-02-28T17:31:20.012285+08:00",null,"bigpigeon",18,"male"]
preload Detail
[0-, ] INSERT INTO user_detail(user_id,main_page,extra) VALUES(?,?,?) args:[1,"some html code with you page",{"title":"my blog"}]
preload Blog
[0-0, ] INSERT INTO blog(created_at,updated_at,deleted_at,user_id,title,content) VALUES(?,?,?,?,?,?) args:["2018-02-28T17:31:20.013968+08:00","2018-02-28T17:31:20.013968+08:00",null,1,"how to write a blog","first ..."]
[0-1, ] INSERT INTO blog(created_at,updated_at,deleted_at,user_id,title,content) VALUES(?,?,?,?,?,?) args:["2018-02-28T17:31:20.013968+08:00","2018-02-28T17:31:20.013968+08:00",null,1,"blog introduction","..."]
preload Friends
[0-0, ] INSERT INTO user(created_at,updated_at,deleted_at,name,age,sex) VALUES(?,?,?,?,?,?) args:["2018-02-28T17:31:20.015207+08:00","2018-02-28T17:31:20.015207+08:00",null,"fatpigeon",18,"male"]
preload Detail
[0-0-, ] INSERT INTO user_detail(user_id,main_page,extra) VALUES(?,?,?) args:[2,"some html code with you page",{}]
preload Blog
[0-0-0, ] INSERT INTO blog(created_at,updated_at,deleted_at,user_id,title,content) VALUES(?,?,?,?,?,?) args:["2018-02-28T17:31:20.016389+08:00","2018-02-28T17:31:20.016389+08:00",null,2,"some python tech","first ..."]
[0-0-1, ] INSERT INTO blog(created_at,updated_at,deleted_at,user_id,title,content) VALUES(?,?,?,?,?,?) args:["2018-02-28T17:31:20.016389+08:00","2018-02-28T17:31:20.016389+08:00",null,2,"my eleme_union_meal usage","..."]
*/
find
brick := brick.Preload(Offsetof(User{}.Friends)).
Preload(Offsetof(User{}.Detail)).Enter().
Preload(Offsetof(User{}.Blog)).Enter().
Enter()
var users []User
result, err = brick.Find(&users)
// some error process
...
// print the report
fmt.Printf("report:\n%s\n", result.Report())
// report log
/*
report:
[0, 1, ] SELECT id,created_at,updated_at,deleted_at,name,age,sex FROM user WHERE deleted_at IS NULL args:null
preload Detail
[0-, 1-, ] SELECT id,user_id,main_page,extra FROM user_detail WHERE user_id IN (?,?) args:[2,1]
preload Blog
[0-0, 0-1, 1-0, 1-1, ] SELECT id,created_at,updated_at,deleted_at,user_id,title,content FROM blog WHERE deleted_at IS NULL AND user_id IN (?,?) args:[1,2]
preload Friends
[0-0, ] SELECT id,created_at,updated_at,deleted_at,name,age,sex FROM user WHERE deleted_at IS NULL AND id IN (?) args:[2]
preload Detail
[0-0-, ] SELECT id,user_id,main_page,extra FROM user_detail WHERE user_id IN (?) args:[2]
preload Blog
[0-0-0, 0-0-1, ] SELECT id,created_at,updated_at,deleted_at,user_id,title,content FROM blog WHERE deleted_at IS NULL AND user_id IN (?) args:[2]
*/
use Err to view sql action error
var users []struct {
ID uint32
Age bool
Detail *UserDetail
Blog []Blog
}
result, err = brick.Find(&users)
if err != nil {
panic(err)
}
if err := result.Err(); err != nil {
fmt.Printf("error:\n%s\n", err)
}
/*
error:
SELECT id,age FROM user WHERE deleted_at IS NULL args:null errors(
[0]sql: Scan error on column index 1: sql/driver: couldn't convert "18" into type bool
[1]sql: Scan error on column index 1: sql/driver: couldn't convert "18" into type bool
)
*/
toyorm support following selector
| operation \ selector | OffsetOf | Name string | map[OffsetOf]interface{} | map[string]interface{} | struct |
|---|---|---|---|---|---|
| Update | no | no | yes | yes | yes |
| Insert | no | no | yes | yes | yes |
| Save | no | no | yes | yes | yes |
| Where & Conditions | yes | yes | no | no | no |
| WhereGroup & ConditionGroup | no | no | yes | yes | yes |
| BindFields | yes | yes | no | no | no |
| Preload & Custom Preload | yes | yes | no | no | no |
| OrderBy | yes | yes | no | no | no |
| Find | no | no | no | no | yes |
collection provide multiple database operation
ToyCollection is basic of the collection , it like Toy
toyCollection, err = toyorm.OpenCollection("sqlite3", []string{"", ""}...)
CollectionBrick use to build grammar and operate the database, like ToyBrick
brick := toyCollection.Model(&User{})
Selector use to select database when Insert/Save
CollectionBrick has default selector dbPrimaryKeySelector
you can custom db selector
func idSelector(n int, keys ...interface{}) int {
sum := 0
for _, k := range keys {
switch val := k.(type) {
case int:
sum += val
case int32:
sum += int(val)
case uint:
sum += int(val)
case uint32:
sum += int(val)
default:
panic("primary key type not match")
}
}
return sum % n
}
brick = brick.Selector(idSelector)
in this mode,Field tag auto_increment was invalid
you need create id generator
// a context id generator
type IDGenerator map[*toyorm.Model]chan int
func (g IDGenerator) CollectionIDGenerate(ctx *toyorm.CollectionContext) error {
if g[ctx.Brick.Model] == nil {
idGenerate := make(chan int)
go func() {
current := 1
for {
// if have redis, use redis-cli
idGenerate <- current
current++
}
}()
g[ctx.Brick.Model] = idGenerate
}
primaryKey := ctx.Brick.Model.GetOnePrimary()
for _, record := range ctx.Result.Records.GetRecords() {
if field := record.Field(primaryKey.Name()); field.IsValid() == false || toyorm.IsZero(field) {
v := <-g[ctx.Brick.Model]
record.SetField(primaryKey.Name(), reflect.ValueOf(v))
}
}
return nil
}
// set id genetor to model context
toy.SetModelHandlers("Save", brick.Model, toyorm.CollectionHandlersChain{idGenerate.CollectionIDGenerate})
// set id genetor to all preload models context
for _, pBrick := range brick.MapPreloadBrick {
toy.SetModelHandlers("Save", pBrick.Model, toyorm.CollectionHandlersChain{idGenerate.CollectionIDGenerate})
}
toy collection sql action is same as Toy
insert
users := []User{
{Name: "Turing"},
{Name: "Shannon"},
{Name: "Ritchie"},
{Name: "Jobs"},
}
result, err = userBrick.Insert(&users)
// error process
// view report log
fmt.Printf("report:\n%s", result.Report())
find
var jobs User
result, err = userBrick.Where(toyorm.ExprEqual, Offsetof(User{}.Name), "Jobs").Find(&jobs)
// error process
// view report log
fmt.Printf("report:\n%s", result.Report())
delete
result, err = userBrick.Delete(&jobs)
// error process
// view report log
fmt.Printf("delete report:\n%s\n", result.Report())
parameter check within ToyBrick method call
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
1. 开源生态
2. 协作、人、软件
3. 评估模型