1// Copyright 2014 beego Author. All Rights Reserved. 2// 3// Licensed under the Apache License, Version 2.0 (the "License"); 4// you may not use this file except in compliance with the License. 5// You may obtain a copy of the License at 6// 7// http://www.apache.org/licenses/LICENSE-2.0 8// 9// Unless required by applicable law or agreed to in writing, software 10// distributed under the License is distributed on an "AS IS" BASIS, 11// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12// See the License for the specific language governing permissions and 13// limitations under the License. 14 15package orm 16 17import ( 18 "context" 19 "database/sql" 20 "reflect" 21 "time" 22) 23 24// Driver define database driver 25type Driver interface { 26 Name() string 27 Type() DriverType 28} 29 30// Fielder define field info 31type Fielder interface { 32 String() string 33 FieldType() int 34 SetRaw(interface{}) error 35 RawValue() interface{} 36} 37 38// Ormer define the orm interface 39type Ormer interface { 40 // read data to model 41 // for example: 42 // this will find User by Id field 43 // u = &User{Id: user.Id} 44 // err = Ormer.Read(u) 45 // this will find User by UserName field 46 // u = &User{UserName: "astaxie", Password: "pass"} 47 // err = Ormer.Read(u, "UserName") 48 Read(md interface{}, cols ...string) error 49 // Like Read(), but with "FOR UPDATE" clause, useful in transaction. 50 // Some databases are not support this feature. 51 ReadForUpdate(md interface{}, cols ...string) error 52 // Try to read a row from the database, or insert one if it doesn't exist 53 ReadOrCreate(md interface{}, col1 string, cols ...string) (bool, int64, error) 54 // insert model data to database 55 // for example: 56 // user := new(User) 57 // id, err = Ormer.Insert(user) 58 // user must be a pointer and Insert will set user's pk field 59 Insert(interface{}) (int64, error) 60 // mysql:InsertOrUpdate(model) or InsertOrUpdate(model,"colu=colu+value") 61 // if colu type is integer : can use(+-*/), string : convert(colu,"value") 62 // postgres: InsertOrUpdate(model,"conflictColumnName") or InsertOrUpdate(model,"conflictColumnName","colu=colu+value") 63 // if colu type is integer : can use(+-*/), string : colu || "value" 64 InsertOrUpdate(md interface{}, colConflitAndArgs ...string) (int64, error) 65 // insert some models to database 66 InsertMulti(bulk int, mds interface{}) (int64, error) 67 // update model to database. 68 // cols set the columns those want to update. 69 // find model by Id(pk) field and update columns specified by fields, if cols is null then update all columns 70 // for example: 71 // user := User{Id: 2} 72 // user.Langs = append(user.Langs, "zh-CN", "en-US") 73 // user.Extra.Name = "beego" 74 // user.Extra.Data = "orm" 75 // num, err = Ormer.Update(&user, "Langs", "Extra") 76 Update(md interface{}, cols ...string) (int64, error) 77 // delete model in database 78 Delete(md interface{}, cols ...string) (int64, error) 79 // load related models to md model. 80 // args are limit, offset int and order string. 81 // 82 // example: 83 // Ormer.LoadRelated(post,"Tags") 84 // for _,tag := range post.Tags{...} 85 //args[0] bool true useDefaultRelsDepth ; false depth 0 86 //args[0] int loadRelationDepth 87 //args[1] int limit default limit 1000 88 //args[2] int offset default offset 0 89 //args[3] string order for example : "-Id" 90 // make sure the relation is defined in model struct tags. 91 LoadRelated(md interface{}, name string, args ...interface{}) (int64, error) 92 // create a models to models queryer 93 // for example: 94 // post := Post{Id: 4} 95 // m2m := Ormer.QueryM2M(&post, "Tags") 96 QueryM2M(md interface{}, name string) QueryM2Mer 97 // return a QuerySeter for table operations. 98 // table name can be string or struct. 99 // e.g. QueryTable("user"), QueryTable(&user{}) or QueryTable((*User)(nil)), 100 QueryTable(ptrStructOrTableName interface{}) QuerySeter 101 // switch to another registered database driver by given name. 102 Using(name string) error 103 // begin transaction 104 // for example: 105 // o := NewOrm() 106 // err := o.Begin() 107 // ... 108 // err = o.Rollback() 109 Begin() error 110 // begin transaction with provided context and option 111 // the provided context is used until the transaction is committed or rolled back. 112 // if the context is canceled, the transaction will be rolled back. 113 // the provided TxOptions is optional and may be nil if defaults should be used. 114 // if a non-default isolation level is used that the driver doesn't support, an error will be returned. 115 // for example: 116 // o := NewOrm() 117 // err := o.BeginTx(context.Background(), &sql.TxOptions{Isolation: sql.LevelRepeatableRead}) 118 // ... 119 // err = o.Rollback() 120 BeginTx(ctx context.Context, opts *sql.TxOptions) error 121 // commit transaction 122 Commit() error 123 // rollback transaction 124 Rollback() error 125 // return a raw query seter for raw sql string. 126 // for example: 127 // ormer.Raw("UPDATE `user` SET `user_name` = ? WHERE `user_name` = ?", "slene", "testing").Exec() 128 // // update user testing's name to slene 129 Raw(query string, args ...interface{}) RawSeter 130 Driver() Driver 131 DBStats() *sql.DBStats 132} 133 134// Inserter insert prepared statement 135type Inserter interface { 136 Insert(interface{}) (int64, error) 137 Close() error 138} 139 140// QuerySeter query seter 141type QuerySeter interface { 142 // add condition expression to QuerySeter. 143 // for example: 144 // filter by UserName == 'slene' 145 // qs.Filter("UserName", "slene") 146 // sql : left outer join profile on t0.id1==t1.id2 where t1.age == 28 147 // Filter("profile__Age", 28) 148 // // time compare 149 // qs.Filter("created", time.Now()) 150 Filter(string, ...interface{}) QuerySeter 151 // add raw sql to querySeter. 152 // for example: 153 // qs.FilterRaw("user_id IN (SELECT id FROM profile WHERE age>=18)") 154 // //sql-> WHERE user_id IN (SELECT id FROM profile WHERE age>=18) 155 FilterRaw(string, string) QuerySeter 156 // add NOT condition to querySeter. 157 // have the same usage as Filter 158 Exclude(string, ...interface{}) QuerySeter 159 // set condition to QuerySeter. 160 // sql's where condition 161 // cond := orm.NewCondition() 162 // cond1 := cond.And("profile__isnull", false).AndNot("status__in", 1).Or("profile__age__gt", 2000) 163 // //sql-> WHERE T0.`profile_id` IS NOT NULL AND NOT T0.`Status` IN (?) OR T1.`age` > 2000 164 // num, err := qs.SetCond(cond1).Count() 165 SetCond(*Condition) QuerySeter 166 // get condition from QuerySeter. 167 // sql's where condition 168 // cond := orm.NewCondition() 169 // cond = cond.And("profile__isnull", false).AndNot("status__in", 1) 170 // qs = qs.SetCond(cond) 171 // cond = qs.GetCond() 172 // cond := cond.Or("profile__age__gt", 2000) 173 // //sql-> WHERE T0.`profile_id` IS NOT NULL AND NOT T0.`Status` IN (?) OR T1.`age` > 2000 174 // num, err := qs.SetCond(cond).Count() 175 GetCond() *Condition 176 // add LIMIT value. 177 // args[0] means offset, e.g. LIMIT num,offset. 178 // if Limit <= 0 then Limit will be set to default limit ,eg 1000 179 // if QuerySeter doesn't call Limit, the sql's Limit will be set to default limit, eg 1000 180 // for example: 181 // qs.Limit(10, 2) 182 // // sql-> limit 10 offset 2 183 Limit(limit interface{}, args ...interface{}) QuerySeter 184 // add OFFSET value 185 // same as Limit function's args[0] 186 Offset(offset interface{}) QuerySeter 187 // add GROUP BY expression 188 // for example: 189 // qs.GroupBy("id") 190 GroupBy(exprs ...string) QuerySeter 191 // add ORDER expression. 192 // "column" means ASC, "-column" means DESC. 193 // for example: 194 // qs.OrderBy("-status") 195 OrderBy(exprs ...string) QuerySeter 196 // set relation model to query together. 197 // it will query relation models and assign to parent model. 198 // for example: 199 // // will load all related fields use left join . 200 // qs.RelatedSel().One(&user) 201 // // will load related field only profile 202 // qs.RelatedSel("profile").One(&user) 203 // user.Profile.Age = 32 204 RelatedSel(params ...interface{}) QuerySeter 205 // Set Distinct 206 // for example: 207 // o.QueryTable("policy").Filter("Groups__Group__Users__User", user). 208 // Distinct(). 209 // All(&permissions) 210 Distinct() QuerySeter 211 // set FOR UPDATE to query. 212 // for example: 213 // o.QueryTable("user").Filter("uid", uid).ForUpdate().All(&users) 214 ForUpdate() QuerySeter 215 // return QuerySeter execution result number 216 // for example: 217 // num, err = qs.Filter("profile__age__gt", 28).Count() 218 Count() (int64, error) 219 // check result empty or not after QuerySeter executed 220 // the same as QuerySeter.Count > 0 221 Exist() bool 222 // execute update with parameters 223 // for example: 224 // num, err = qs.Filter("user_name", "slene").Update(Params{ 225 // "Nums": ColValue(Col_Minus, 50), 226 // }) // user slene's Nums will minus 50 227 // num, err = qs.Filter("UserName", "slene").Update(Params{ 228 // "user_name": "slene2" 229 // }) // user slene's name will change to slene2 230 Update(values Params) (int64, error) 231 // delete from table 232 //for example: 233 // num ,err = qs.Filter("user_name__in", "testing1", "testing2").Delete() 234 // //delete two user who's name is testing1 or testing2 235 Delete() (int64, error) 236 // return a insert queryer. 237 // it can be used in times. 238 // example: 239 // i,err := sq.PrepareInsert() 240 // num, err = i.Insert(&user1) // user table will add one record user1 at once 241 // num, err = i.Insert(&user2) // user table will add one record user2 at once 242 // err = i.Close() //don't forget call Close 243 PrepareInsert() (Inserter, error) 244 // query all data and map to containers. 245 // cols means the columns when querying. 246 // for example: 247 // var users []*User 248 // qs.All(&users) // users[0],users[1],users[2] ... 249 All(container interface{}, cols ...string) (int64, error) 250 // query one row data and map to containers. 251 // cols means the columns when querying. 252 // for example: 253 // var user User 254 // qs.One(&user) //user.UserName == "slene" 255 One(container interface{}, cols ...string) error 256 // query all data and map to []map[string]interface. 257 // expres means condition expression. 258 // it converts data to []map[column]value. 259 // for example: 260 // var maps []Params 261 // qs.Values(&maps) //maps[0]["UserName"]=="slene" 262 Values(results *[]Params, exprs ...string) (int64, error) 263 // query all data and map to [][]interface 264 // it converts data to [][column_index]value 265 // for example: 266 // var list []ParamsList 267 // qs.ValuesList(&list) // list[0][1] == "slene" 268 ValuesList(results *[]ParamsList, exprs ...string) (int64, error) 269 // query all data and map to []interface. 270 // it's designed for one column record set, auto change to []value, not [][column]value. 271 // for example: 272 // var list ParamsList 273 // qs.ValuesFlat(&list, "UserName") // list[0] == "slene" 274 ValuesFlat(result *ParamsList, expr string) (int64, error) 275 // query all rows into map[string]interface with specify key and value column name. 276 // keyCol = "name", valueCol = "value" 277 // table data 278 // name | value 279 // total | 100 280 // found | 200 281 // to map[string]interface{}{ 282 // "total": 100, 283 // "found": 200, 284 // } 285 RowsToMap(result *Params, keyCol, valueCol string) (int64, error) 286 // query all rows into struct with specify key and value column name. 287 // keyCol = "name", valueCol = "value" 288 // table data 289 // name | value 290 // total | 100 291 // found | 200 292 // to struct { 293 // Total int 294 // Found int 295 // } 296 RowsToStruct(ptrStruct interface{}, keyCol, valueCol string) (int64, error) 297} 298 299// QueryM2Mer model to model query struct 300// all operations are on the m2m table only, will not affect the origin model table 301type QueryM2Mer interface { 302 // add models to origin models when creating queryM2M. 303 // example: 304 // m2m := orm.QueryM2M(post,"Tag") 305 // m2m.Add(&Tag1{},&Tag2{}) 306 // for _,tag := range post.Tags{}{ ... } 307 // param could also be any of the follow 308 // []*Tag{{Id:3,Name: "TestTag1"}, {Id:4,Name: "TestTag2"}} 309 // &Tag{Id:5,Name: "TestTag3"} 310 // []interface{}{&Tag{Id:6,Name: "TestTag4"}} 311 // insert one or more rows to m2m table 312 // make sure the relation is defined in post model struct tag. 313 Add(...interface{}) (int64, error) 314 // remove models following the origin model relationship 315 // only delete rows from m2m table 316 // for example: 317 //tag3 := &Tag{Id:5,Name: "TestTag3"} 318 //num, err = m2m.Remove(tag3) 319 Remove(...interface{}) (int64, error) 320 // check model is existed in relationship of origin model 321 Exist(interface{}) bool 322 // clean all models in related of origin model 323 Clear() (int64, error) 324 // count all related models of origin model 325 Count() (int64, error) 326} 327 328// RawPreparer raw query statement 329type RawPreparer interface { 330 Exec(...interface{}) (sql.Result, error) 331 Close() error 332} 333 334// RawSeter raw query seter 335// create From Ormer.Raw 336// for example: 337// sql := fmt.Sprintf("SELECT %sid%s,%sname%s FROM %suser%s WHERE id = ?",Q,Q,Q,Q,Q,Q) 338// rs := Ormer.Raw(sql, 1) 339type RawSeter interface { 340 //execute sql and get result 341 Exec() (sql.Result, error) 342 //query data and map to container 343 //for example: 344 // var name string 345 // var id int 346 // rs.QueryRow(&id,&name) // id==2 name=="slene" 347 QueryRow(containers ...interface{}) error 348 349 // query data rows and map to container 350 // var ids []int 351 // var names []int 352 // query = fmt.Sprintf("SELECT 'id','name' FROM %suser%s", Q, Q) 353 // num, err = dORM.Raw(query).QueryRows(&ids,&names) // ids=>{1,2},names=>{"nobody","slene"} 354 QueryRows(containers ...interface{}) (int64, error) 355 SetArgs(...interface{}) RawSeter 356 // query data to []map[string]interface 357 // see QuerySeter's Values 358 Values(container *[]Params, cols ...string) (int64, error) 359 // query data to [][]interface 360 // see QuerySeter's ValuesList 361 ValuesList(container *[]ParamsList, cols ...string) (int64, error) 362 // query data to []interface 363 // see QuerySeter's ValuesFlat 364 ValuesFlat(container *ParamsList, cols ...string) (int64, error) 365 // query all rows into map[string]interface with specify key and value column name. 366 // keyCol = "name", valueCol = "value" 367 // table data 368 // name | value 369 // total | 100 370 // found | 200 371 // to map[string]interface{}{ 372 // "total": 100, 373 // "found": 200, 374 // } 375 RowsToMap(result *Params, keyCol, valueCol string) (int64, error) 376 // query all rows into struct with specify key and value column name. 377 // keyCol = "name", valueCol = "value" 378 // table data 379 // name | value 380 // total | 100 381 // found | 200 382 // to struct { 383 // Total int 384 // Found int 385 // } 386 RowsToStruct(ptrStruct interface{}, keyCol, valueCol string) (int64, error) 387 388 // return prepared raw statement for used in times. 389 // for example: 390 // pre, err := dORM.Raw("INSERT INTO tag (name) VALUES (?)").Prepare() 391 // r, err := pre.Exec("name1") // INSERT INTO tag (name) VALUES (`name1`) 392 Prepare() (RawPreparer, error) 393} 394 395// stmtQuerier statement querier 396type stmtQuerier interface { 397 Close() error 398 Exec(args ...interface{}) (sql.Result, error) 399 //ExecContext(ctx context.Context, args ...interface{}) (sql.Result, error) 400 Query(args ...interface{}) (*sql.Rows, error) 401 //QueryContext(args ...interface{}) (*sql.Rows, error) 402 QueryRow(args ...interface{}) *sql.Row 403 //QueryRowContext(ctx context.Context, args ...interface{}) *sql.Row 404} 405 406// db querier 407type dbQuerier interface { 408 Prepare(query string) (*sql.Stmt, error) 409 PrepareContext(ctx context.Context, query string) (*sql.Stmt, error) 410 Exec(query string, args ...interface{}) (sql.Result, error) 411 ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error) 412 Query(query string, args ...interface{}) (*sql.Rows, error) 413 QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error) 414 QueryRow(query string, args ...interface{}) *sql.Row 415 QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row 416} 417 418// type DB interface { 419// Begin() (*sql.Tx, error) 420// Prepare(query string) (stmtQuerier, error) 421// Exec(query string, args ...interface{}) (sql.Result, error) 422// Query(query string, args ...interface{}) (*sql.Rows, error) 423// QueryRow(query string, args ...interface{}) *sql.Row 424// } 425 426// transaction beginner 427type txer interface { 428 Begin() (*sql.Tx, error) 429 BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error) 430} 431 432// transaction ending 433type txEnder interface { 434 Commit() error 435 Rollback() error 436} 437 438// base database struct 439type dbBaser interface { 440 Read(dbQuerier, *modelInfo, reflect.Value, *time.Location, []string, bool) error 441 Insert(dbQuerier, *modelInfo, reflect.Value, *time.Location) (int64, error) 442 InsertOrUpdate(dbQuerier, *modelInfo, reflect.Value, *alias, ...string) (int64, error) 443 InsertMulti(dbQuerier, *modelInfo, reflect.Value, int, *time.Location) (int64, error) 444 InsertValue(dbQuerier, *modelInfo, bool, []string, []interface{}) (int64, error) 445 InsertStmt(stmtQuerier, *modelInfo, reflect.Value, *time.Location) (int64, error) 446 Update(dbQuerier, *modelInfo, reflect.Value, *time.Location, []string) (int64, error) 447 Delete(dbQuerier, *modelInfo, reflect.Value, *time.Location, []string) (int64, error) 448 ReadBatch(dbQuerier, *querySet, *modelInfo, *Condition, interface{}, *time.Location, []string) (int64, error) 449 SupportUpdateJoin() bool 450 UpdateBatch(dbQuerier, *querySet, *modelInfo, *Condition, Params, *time.Location) (int64, error) 451 DeleteBatch(dbQuerier, *querySet, *modelInfo, *Condition, *time.Location) (int64, error) 452 Count(dbQuerier, *querySet, *modelInfo, *Condition, *time.Location) (int64, error) 453 OperatorSQL(string) string 454 GenerateOperatorSQL(*modelInfo, *fieldInfo, string, []interface{}, *time.Location) (string, []interface{}) 455 GenerateOperatorLeftCol(*fieldInfo, string, *string) 456 PrepareInsert(dbQuerier, *modelInfo) (stmtQuerier, string, error) 457 ReadValues(dbQuerier, *querySet, *modelInfo, *Condition, []string, interface{}, *time.Location) (int64, error) 458 RowsTo(dbQuerier, *querySet, *modelInfo, *Condition, interface{}, string, string, *time.Location) (int64, error) 459 MaxLimit() uint64 460 TableQuote() string 461 ReplaceMarks(*string) 462 HasReturningID(*modelInfo, *string) bool 463 TimeFromDB(*time.Time, *time.Location) 464 TimeToDB(*time.Time, *time.Location) 465 DbTypes() map[string]string 466 GetTables(dbQuerier) (map[string]bool, error) 467 GetColumns(dbQuerier, string) (map[string][3]string, error) 468 ShowTablesQuery() string 469 ShowColumnsQuery(string) string 470 IndexExists(dbQuerier, string, string) bool 471 collectFieldValue(*modelInfo, *fieldInfo, reflect.Value, bool, *time.Location) (interface{}, error) 472 setval(dbQuerier, *modelInfo, []string) error 473} 474