github.com/gogf/gf@v1.16.9/database/gdb/gdb_z_mysql_time_maintain_test.go (about) 1 // Copyright GoFrame Author(https://goframe.org). All Rights Reserved. 2 // 3 // This Source Code Form is subject to the terms of the MIT License. 4 // If a copy of the MIT was not distributed with this file, 5 // You can obtain one at https://github.com/gogf/gf. 6 7 package gdb_test 8 9 import ( 10 "fmt" 11 "github.com/gogf/gf/os/gtime" 12 "testing" 13 "time" 14 15 "github.com/gogf/gf/frame/g" 16 17 "github.com/gogf/gf/test/gtest" 18 ) 19 20 // CreateAt/UpdateAt/DeleteAt 21 func Test_SoftCreateUpdateDeleteTime(t *testing.T) { 22 table := "time_test_table_" + gtime.TimestampNanoStr() 23 if _, err := db.Exec(fmt.Sprintf(` 24 CREATE TABLE %s ( 25 id int(11) NOT NULL, 26 name varchar(45) DEFAULT NULL, 27 create_at datetime DEFAULT NULL, 28 update_at datetime DEFAULT NULL, 29 delete_at datetime DEFAULT NULL, 30 PRIMARY KEY (id) 31 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 32 `, table)); err != nil { 33 gtest.Error(err) 34 } 35 defer dropTable(table) 36 37 gtest.C(t, func(t *gtest.T) { 38 // Insert 39 dataInsert := g.Map{ 40 "id": 1, 41 "name": "name_1", 42 } 43 r, err := db.Model(table).Data(dataInsert).Insert() 44 t.AssertNil(err) 45 n, _ := r.RowsAffected() 46 t.Assert(n, 1) 47 48 oneInsert, err := db.Model(table).FindOne(1) 49 t.AssertNil(err) 50 t.Assert(oneInsert["id"].Int(), 1) 51 t.Assert(oneInsert["name"].String(), "name_1") 52 t.Assert(oneInsert["delete_at"].String(), "") 53 t.AssertGE(oneInsert["create_at"].GTime().Timestamp(), gtime.Timestamp()-2) 54 t.AssertGE(oneInsert["update_at"].GTime().Timestamp(), gtime.Timestamp()-2) 55 56 // For time asserting purpose. 57 time.Sleep(2 * time.Second) 58 59 // Save 60 dataSave := g.Map{ 61 "id": 1, 62 "name": "name_10", 63 } 64 r, err = db.Model(table).Data(dataSave).Save() 65 t.AssertNil(err) 66 n, _ = r.RowsAffected() 67 t.Assert(n, 2) 68 69 oneSave, err := db.Model(table).FindOne(1) 70 t.AssertNil(err) 71 t.Assert(oneSave["id"].Int(), 1) 72 t.Assert(oneSave["name"].String(), "name_10") 73 t.Assert(oneSave["delete_at"].String(), "") 74 t.Assert(oneSave["create_at"].GTime().Timestamp(), oneInsert["create_at"].GTime().Timestamp()) 75 t.AssertNE(oneSave["update_at"].GTime().Timestamp(), oneInsert["update_at"].GTime().Timestamp()) 76 t.AssertGE(oneSave["update_at"].GTime().Timestamp(), gtime.Timestamp()-2) 77 78 // For time asserting purpose. 79 time.Sleep(2 * time.Second) 80 81 // Update 82 dataUpdate := g.Map{ 83 "name": "name_1000", 84 } 85 r, err = db.Model(table).Data(dataUpdate).WherePri(1).Update() 86 t.AssertNil(err) 87 n, _ = r.RowsAffected() 88 t.Assert(n, 1) 89 90 oneUpdate, err := db.Model(table).FindOne(1) 91 t.AssertNil(err) 92 t.Assert(oneUpdate["id"].Int(), 1) 93 t.Assert(oneUpdate["name"].String(), "name_1000") 94 t.Assert(oneUpdate["delete_at"].String(), "") 95 t.Assert(oneUpdate["create_at"].GTime().Timestamp(), oneInsert["create_at"].GTime().Timestamp()) 96 t.AssertGE(oneUpdate["update_at"].GTime().Timestamp(), gtime.Timestamp()-2) 97 98 // Replace 99 dataReplace := g.Map{ 100 "id": 1, 101 "name": "name_100", 102 } 103 r, err = db.Model(table).Data(dataReplace).Replace() 104 t.AssertNil(err) 105 n, _ = r.RowsAffected() 106 t.Assert(n, 2) 107 108 oneReplace, err := db.Model(table).FindOne(1) 109 t.AssertNil(err) 110 t.Assert(oneReplace["id"].Int(), 1) 111 t.Assert(oneReplace["name"].String(), "name_100") 112 t.Assert(oneReplace["delete_at"].String(), "") 113 t.AssertGE(oneReplace["create_at"].GTime().Timestamp(), oneInsert["create_at"].GTime().Timestamp()) 114 t.AssertGE(oneReplace["update_at"].GTime().Timestamp(), oneInsert["update_at"].GTime().Timestamp()) 115 116 // For time asserting purpose. 117 time.Sleep(2 * time.Second) 118 119 // Delete 120 r, err = db.Model(table).Delete("id", 1) 121 t.AssertNil(err) 122 n, _ = r.RowsAffected() 123 t.Assert(n, 1) 124 // Delete Select 125 one4, err := db.Model(table).FindOne(1) 126 t.AssertNil(err) 127 t.Assert(len(one4), 0) 128 one5, err := db.Model(table).Unscoped().FindOne(1) 129 t.AssertNil(err) 130 t.Assert(one5["id"].Int(), 1) 131 t.AssertGE(one5["delete_at"].GTime().Timestamp(), gtime.Timestamp()-2) 132 // Delete Count 133 i, err := db.Model(table).FindCount() 134 t.AssertNil(err) 135 t.Assert(i, 0) 136 i, err = db.Model(table).Unscoped().FindCount() 137 t.AssertNil(err) 138 t.Assert(i, 1) 139 140 // Delete Unscoped 141 r, err = db.Model(table).Unscoped().Delete("id", 1) 142 t.AssertNil(err) 143 n, _ = r.RowsAffected() 144 t.Assert(n, 1) 145 one6, err := db.Model(table).Unscoped().FindOne(1) 146 t.AssertNil(err) 147 t.Assert(len(one6), 0) 148 i, err = db.Model(table).Unscoped().FindCount() 149 t.AssertNil(err) 150 t.Assert(i, 0) 151 }) 152 } 153 154 // CreatedAt/UpdatedAt/DeletedAt 155 func Test_SoftCreatedUpdatedDeletedTime_Map(t *testing.T) { 156 table := "time_test_table_" + gtime.TimestampNanoStr() 157 if _, err := db.Exec(fmt.Sprintf(` 158 CREATE TABLE %s ( 159 id int(11) NOT NULL, 160 name varchar(45) DEFAULT NULL, 161 created_at datetime DEFAULT NULL, 162 updated_at datetime DEFAULT NULL, 163 deleted_at datetime DEFAULT NULL, 164 PRIMARY KEY (id) 165 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 166 `, table)); err != nil { 167 gtest.Error(err) 168 } 169 defer dropTable(table) 170 171 gtest.C(t, func(t *gtest.T) { 172 // Insert 173 dataInsert := g.Map{ 174 "id": 1, 175 "name": "name_1", 176 } 177 r, err := db.Model(table).Data(dataInsert).Insert() 178 t.AssertNil(err) 179 n, _ := r.RowsAffected() 180 t.Assert(n, 1) 181 182 oneInsert, err := db.Model(table).FindOne(1) 183 t.AssertNil(err) 184 t.Assert(oneInsert["id"].Int(), 1) 185 t.Assert(oneInsert["name"].String(), "name_1") 186 t.Assert(oneInsert["deleted_at"].String(), "") 187 t.AssertGE(oneInsert["created_at"].GTime().Timestamp(), gtime.Timestamp()-2) 188 t.AssertGE(oneInsert["updated_at"].GTime().Timestamp(), gtime.Timestamp()-2) 189 190 // For time asserting purpose. 191 time.Sleep(2 * time.Second) 192 193 // Save 194 dataSave := g.Map{ 195 "id": 1, 196 "name": "name_10", 197 } 198 r, err = db.Model(table).Data(dataSave).Save() 199 t.AssertNil(err) 200 n, _ = r.RowsAffected() 201 t.Assert(n, 2) 202 203 oneSave, err := db.Model(table).FindOne(1) 204 t.AssertNil(err) 205 t.Assert(oneSave["id"].Int(), 1) 206 t.Assert(oneSave["name"].String(), "name_10") 207 t.Assert(oneSave["deleted_at"].String(), "") 208 t.Assert(oneSave["created_at"].GTime().Timestamp(), oneInsert["created_at"].GTime().Timestamp()) 209 t.AssertNE(oneSave["updated_at"].GTime().Timestamp(), oneInsert["updated_at"].GTime().Timestamp()) 210 t.AssertGE(oneSave["updated_at"].GTime().Timestamp(), gtime.Timestamp()-2) 211 212 // For time asserting purpose. 213 time.Sleep(2 * time.Second) 214 215 // Update 216 dataUpdate := g.Map{ 217 "name": "name_1000", 218 } 219 r, err = db.Model(table).Data(dataUpdate).WherePri(1).Update() 220 t.AssertNil(err) 221 n, _ = r.RowsAffected() 222 t.Assert(n, 1) 223 224 oneUpdate, err := db.Model(table).FindOne(1) 225 t.AssertNil(err) 226 t.Assert(oneUpdate["id"].Int(), 1) 227 t.Assert(oneUpdate["name"].String(), "name_1000") 228 t.Assert(oneUpdate["deleted_at"].String(), "") 229 t.Assert(oneUpdate["created_at"].GTime().Timestamp(), oneInsert["created_at"].GTime().Timestamp()) 230 t.AssertGE(oneUpdate["updated_at"].GTime().Timestamp(), gtime.Timestamp()-2) 231 232 // Replace 233 dataReplace := g.Map{ 234 "id": 1, 235 "name": "name_100", 236 } 237 r, err = db.Model(table).Data(dataReplace).Replace() 238 t.AssertNil(err) 239 n, _ = r.RowsAffected() 240 t.Assert(n, 2) 241 242 oneReplace, err := db.Model(table).FindOne(1) 243 t.AssertNil(err) 244 t.Assert(oneReplace["id"].Int(), 1) 245 t.Assert(oneReplace["name"].String(), "name_100") 246 t.Assert(oneReplace["deleted_at"].String(), "") 247 t.AssertGE(oneReplace["created_at"].GTime().Timestamp(), oneInsert["created_at"].GTime().Timestamp()) 248 t.AssertGE(oneReplace["updated_at"].GTime().Timestamp(), oneInsert["updated_at"].GTime().Timestamp()) 249 250 // For time asserting purpose. 251 time.Sleep(2 * time.Second) 252 253 // Delete 254 r, err = db.Model(table).Delete("id", 1) 255 t.AssertNil(err) 256 n, _ = r.RowsAffected() 257 t.Assert(n, 1) 258 // Delete Select 259 one4, err := db.Model(table).FindOne(1) 260 t.AssertNil(err) 261 t.Assert(len(one4), 0) 262 one5, err := db.Model(table).Unscoped().FindOne(1) 263 t.AssertNil(err) 264 t.Assert(one5["id"].Int(), 1) 265 t.AssertGE(one5["deleted_at"].GTime().Timestamp(), gtime.Timestamp()-2) 266 // Delete Count 267 i, err := db.Model(table).FindCount() 268 t.AssertNil(err) 269 t.Assert(i, 0) 270 i, err = db.Model(table).Unscoped().FindCount() 271 t.AssertNil(err) 272 t.Assert(i, 1) 273 274 // Delete Unscoped 275 r, err = db.Model(table).Unscoped().Delete("id", 1) 276 t.AssertNil(err) 277 n, _ = r.RowsAffected() 278 t.Assert(n, 1) 279 one6, err := db.Model(table).Unscoped().FindOne(1) 280 t.AssertNil(err) 281 t.Assert(len(one6), 0) 282 i, err = db.Model(table).Unscoped().FindCount() 283 t.AssertNil(err) 284 t.Assert(i, 0) 285 }) 286 } 287 288 // CreatedAt/UpdatedAt/DeletedAt 289 func Test_SoftCreatedUpdatedDeletedTime_Struct(t *testing.T) { 290 table := "time_test_table_" + gtime.TimestampNanoStr() 291 if _, err := db.Exec(fmt.Sprintf(` 292 CREATE TABLE %s ( 293 id int(11) NOT NULL, 294 name varchar(45) DEFAULT NULL, 295 created_at datetime DEFAULT NULL, 296 updated_at datetime DEFAULT NULL, 297 deleted_at datetime DEFAULT NULL, 298 PRIMARY KEY (id) 299 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 300 `, table)); err != nil { 301 gtest.Error(err) 302 } 303 defer dropTable(table) 304 305 type User struct { 306 Id int 307 Name string 308 CreatedAT *gtime.Time 309 UpdatedAT *gtime.Time 310 DeletedAT *gtime.Time 311 } 312 gtest.C(t, func(t *gtest.T) { 313 // Insert 314 dataInsert := User{ 315 Id: 1, 316 Name: "name_1", 317 } 318 r, err := db.Model(table).Data(dataInsert).Insert() 319 t.AssertNil(err) 320 n, _ := r.RowsAffected() 321 t.Assert(n, 1) 322 323 oneInsert, err := db.Model(table).FindOne(1) 324 t.AssertNil(err) 325 t.Assert(oneInsert["id"].Int(), 1) 326 t.Assert(oneInsert["name"].String(), "name_1") 327 t.Assert(oneInsert["deleted_at"].String(), "") 328 t.AssertGE(oneInsert["created_at"].GTime().Timestamp(), gtime.Timestamp()-2) 329 t.AssertGE(oneInsert["updated_at"].GTime().Timestamp(), gtime.Timestamp()-2) 330 331 // For time asserting purpose. 332 time.Sleep(2 * time.Second) 333 334 // Save 335 dataSave := User{ 336 Id: 1, 337 Name: "name_10", 338 } 339 r, err = db.Model(table).Data(dataSave).OmitEmpty().Save() 340 t.AssertNil(err) 341 n, _ = r.RowsAffected() 342 t.Assert(n, 2) 343 344 oneSave, err := db.Model(table).FindOne(1) 345 t.AssertNil(err) 346 t.Assert(oneSave["id"].Int(), 1) 347 t.Assert(oneSave["name"].String(), "name_10") 348 t.Assert(oneSave["deleted_at"].String(), "") 349 t.Assert(oneSave["created_at"].GTime().Timestamp(), oneInsert["created_at"].GTime().Timestamp()) 350 t.AssertNE(oneSave["updated_at"].GTime().Timestamp(), oneInsert["updated_at"].GTime().Timestamp()) 351 t.AssertGE(oneSave["updated_at"].GTime().Timestamp(), gtime.Timestamp()-2) 352 353 // For time asserting purpose. 354 time.Sleep(2 * time.Second) 355 356 // Update 357 dataUpdate := User{ 358 Name: "name_1000", 359 } 360 r, err = db.Model(table).Data(dataUpdate).OmitEmpty().WherePri(1).Update() 361 t.AssertNil(err) 362 n, _ = r.RowsAffected() 363 t.Assert(n, 1) 364 365 oneUpdate, err := db.Model(table).FindOne(1) 366 t.AssertNil(err) 367 t.Assert(oneUpdate["id"].Int(), 1) 368 t.Assert(oneUpdate["name"].String(), "name_1000") 369 t.Assert(oneUpdate["deleted_at"].String(), "") 370 t.Assert(oneUpdate["created_at"].GTime().Timestamp(), oneInsert["created_at"].GTime().Timestamp()) 371 t.AssertGE(oneUpdate["updated_at"].GTime().Timestamp(), gtime.Timestamp()-4) 372 373 // Replace 374 dataReplace := User{ 375 Id: 1, 376 Name: "name_100", 377 } 378 r, err = db.Model(table).Data(dataReplace).OmitEmpty().Replace() 379 t.AssertNil(err) 380 n, _ = r.RowsAffected() 381 t.Assert(n, 2) 382 383 oneReplace, err := db.Model(table).FindOne(1) 384 t.AssertNil(err) 385 t.Assert(oneReplace["id"].Int(), 1) 386 t.Assert(oneReplace["name"].String(), "name_100") 387 t.Assert(oneReplace["deleted_at"].String(), "") 388 t.AssertGE(oneReplace["created_at"].GTime().Timestamp(), oneInsert["created_at"].GTime().Timestamp()) 389 t.AssertGE(oneReplace["updated_at"].GTime().Timestamp(), oneInsert["updated_at"].GTime().Timestamp()) 390 391 // For time asserting purpose. 392 time.Sleep(2 * time.Second) 393 394 // Delete 395 r, err = db.Model(table).Delete("id", 1) 396 t.AssertNil(err) 397 n, _ = r.RowsAffected() 398 t.Assert(n, 1) 399 // Delete Select 400 one4, err := db.Model(table).FindOne(1) 401 t.AssertNil(err) 402 t.Assert(len(one4), 0) 403 one5, err := db.Model(table).Unscoped().FindOne(1) 404 t.AssertNil(err) 405 t.Assert(one5["id"].Int(), 1) 406 t.AssertGE(one5["deleted_at"].GTime().Timestamp(), gtime.Timestamp()-2) 407 // Delete Count 408 i, err := db.Model(table).FindCount() 409 t.AssertNil(err) 410 t.Assert(i, 0) 411 i, err = db.Model(table).Unscoped().FindCount() 412 t.AssertNil(err) 413 t.Assert(i, 1) 414 415 // Delete Unscoped 416 r, err = db.Model(table).Unscoped().Delete("id", 1) 417 t.AssertNil(err) 418 n, _ = r.RowsAffected() 419 t.Assert(n, 1) 420 one6, err := db.Model(table).Unscoped().FindOne(1) 421 t.AssertNil(err) 422 t.Assert(len(one6), 0) 423 i, err = db.Model(table).Unscoped().FindCount() 424 t.AssertNil(err) 425 t.Assert(i, 0) 426 }) 427 } 428 429 func Test_SoftUpdateTime(t *testing.T) { 430 table := "time_test_table_" + gtime.TimestampNanoStr() 431 if _, err := db.Exec(fmt.Sprintf(` 432 CREATE TABLE %s ( 433 id int(11) NOT NULL, 434 num int(11) DEFAULT NULL, 435 create_at datetime DEFAULT NULL, 436 update_at datetime DEFAULT NULL, 437 delete_at datetime DEFAULT NULL, 438 PRIMARY KEY (id) 439 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 440 `, table)); err != nil { 441 gtest.Error(err) 442 } 443 defer dropTable(table) 444 445 gtest.C(t, func(t *gtest.T) { 446 // Insert 447 dataInsert := g.Map{ 448 "id": 1, 449 "num": 10, 450 } 451 r, err := db.Model(table).Data(dataInsert).Insert() 452 t.AssertNil(err) 453 n, _ := r.RowsAffected() 454 t.Assert(n, 1) 455 456 oneInsert, err := db.Model(table).FindOne(1) 457 t.AssertNil(err) 458 t.Assert(oneInsert["id"].Int(), 1) 459 t.Assert(oneInsert["num"].Int(), 10) 460 461 // Update. 462 r, err = db.Model(table).Data("num=num+1").Where("id=?", 1).Update() 463 t.AssertNil(err) 464 n, _ = r.RowsAffected() 465 t.Assert(n, 1) 466 }) 467 } 468 469 func Test_SoftDelete(t *testing.T) { 470 table := "time_test_table_" + gtime.TimestampNanoStr() 471 if _, err := db.Exec(fmt.Sprintf(` 472 CREATE TABLE %s ( 473 id int(11) NOT NULL, 474 name varchar(45) DEFAULT NULL, 475 create_at datetime DEFAULT NULL, 476 update_at datetime DEFAULT NULL, 477 delete_at datetime DEFAULT NULL, 478 PRIMARY KEY (id) 479 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 480 `, table)); err != nil { 481 gtest.Error(err) 482 } 483 defer dropTable(table) 484 // db.SetDebug(true) 485 gtest.C(t, func(t *gtest.T) { 486 for i := 1; i <= 10; i++ { 487 data := g.Map{ 488 "id": i, 489 "name": fmt.Sprintf("name_%d", i), 490 } 491 r, err := db.Model(table).Data(data).Insert() 492 t.AssertNil(err) 493 n, _ := r.RowsAffected() 494 t.Assert(n, 1) 495 } 496 }) 497 gtest.C(t, func(t *gtest.T) { 498 one, err := db.Model(table).FindOne(1) 499 t.AssertNil(err) 500 t.AssertNE(one["create_at"].String(), "") 501 t.AssertNE(one["update_at"].String(), "") 502 t.Assert(one["delete_at"].String(), "") 503 }) 504 gtest.C(t, func(t *gtest.T) { 505 one, err := db.Model(table).FindOne(10) 506 t.AssertNil(err) 507 t.AssertNE(one["create_at"].String(), "") 508 t.AssertNE(one["update_at"].String(), "") 509 t.Assert(one["delete_at"].String(), "") 510 }) 511 gtest.C(t, func(t *gtest.T) { 512 ids := g.SliceInt{1, 3, 5} 513 r, err := db.Model(table).Where("id", ids).Delete() 514 t.AssertNil(err) 515 n, _ := r.RowsAffected() 516 t.Assert(n, 3) 517 518 count, err := db.Model(table).FindCount(ids) 519 t.AssertNil(err) 520 t.Assert(count, 0) 521 522 all, err := db.Model(table).Unscoped().FindAll(ids) 523 t.AssertNil(err) 524 t.Assert(len(all), 3) 525 t.AssertNE(all[0]["create_at"].String(), "") 526 t.AssertNE(all[0]["update_at"].String(), "") 527 t.AssertNE(all[0]["delete_at"].String(), "") 528 t.AssertNE(all[1]["create_at"].String(), "") 529 t.AssertNE(all[1]["update_at"].String(), "") 530 t.AssertNE(all[1]["delete_at"].String(), "") 531 t.AssertNE(all[2]["create_at"].String(), "") 532 t.AssertNE(all[2]["update_at"].String(), "") 533 t.AssertNE(all[2]["delete_at"].String(), "") 534 }) 535 } 536 537 func Test_SoftDelete_Join(t *testing.T) { 538 table1 := "time_test_table1" 539 if _, err := db.Exec(fmt.Sprintf(` 540 CREATE TABLE %s ( 541 id int(11) NOT NULL, 542 name varchar(45) DEFAULT NULL, 543 create_at datetime DEFAULT NULL, 544 update_at datetime DEFAULT NULL, 545 delete_at datetime DEFAULT NULL, 546 PRIMARY KEY (id) 547 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 548 `, table1)); err != nil { 549 gtest.Error(err) 550 } 551 defer dropTable(table1) 552 553 table2 := "time_test_table2" 554 if _, err := db.Exec(fmt.Sprintf(` 555 CREATE TABLE %s ( 556 id int(11) NOT NULL, 557 name varchar(45) DEFAULT NULL, 558 createat datetime DEFAULT NULL, 559 updateat datetime DEFAULT NULL, 560 deleteat datetime DEFAULT NULL, 561 PRIMARY KEY (id) 562 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 563 `, table2)); err != nil { 564 gtest.Error(err) 565 } 566 defer dropTable(table2) 567 568 gtest.C(t, func(t *gtest.T) { 569 //db.SetDebug(true) 570 dataInsert1 := g.Map{ 571 "id": 1, 572 "name": "name_1", 573 } 574 r, err := db.Model(table1).Data(dataInsert1).Insert() 575 t.AssertNil(err) 576 n, _ := r.RowsAffected() 577 t.Assert(n, 1) 578 579 dataInsert2 := g.Map{ 580 "id": 1, 581 "name": "name_2", 582 } 583 r, err = db.Model(table2).Data(dataInsert2).Insert() 584 t.AssertNil(err) 585 n, _ = r.RowsAffected() 586 t.Assert(n, 1) 587 588 one, err := db.Model(table1, "t1").LeftJoin(table2, "t2", "t2.id=t1.id").Fields("t1.name").FindOne() 589 t.AssertNil(err) 590 t.Assert(one["name"], "name_1") 591 592 // Soft deleting. 593 r, err = db.Model(table1).Delete() 594 t.AssertNil(err) 595 n, _ = r.RowsAffected() 596 t.Assert(n, 1) 597 598 one, err = db.Model(table1, "t1").LeftJoin(table2, "t2", "t2.id=t1.id").Fields("t1.name").FindOne() 599 t.AssertNil(err) 600 t.Assert(one.IsEmpty(), true) 601 602 one, err = db.Model(table2, "t2").LeftJoin(table1, "t1", "t2.id=t1.id").Fields("t2.name").FindOne() 603 t.AssertNil(err) 604 t.Assert(one.IsEmpty(), true) 605 }) 606 } 607 608 func Test_SoftDelete_WhereAndOr(t *testing.T) { 609 table := "time_test_table_" + gtime.TimestampNanoStr() 610 if _, err := db.Exec(fmt.Sprintf(` 611 CREATE TABLE %s ( 612 id int(11) NOT NULL, 613 name varchar(45) DEFAULT NULL, 614 create_at datetime DEFAULT NULL, 615 update_at datetime DEFAULT NULL, 616 delete_at datetime DEFAULT NULL, 617 PRIMARY KEY (id) 618 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 619 `, table)); err != nil { 620 gtest.Error(err) 621 } 622 defer dropTable(table) 623 //db.SetDebug(true) 624 // Add datas. 625 gtest.C(t, func(t *gtest.T) { 626 for i := 1; i <= 10; i++ { 627 data := g.Map{ 628 "id": i, 629 "name": fmt.Sprintf("name_%d", i), 630 } 631 r, err := db.Model(table).Data(data).Insert() 632 t.AssertNil(err) 633 n, _ := r.RowsAffected() 634 t.Assert(n, 1) 635 } 636 }) 637 gtest.C(t, func(t *gtest.T) { 638 ids := g.SliceInt{1, 3, 5} 639 r, err := db.Model(table).Where("id", ids).Delete() 640 t.AssertNil(err) 641 n, _ := r.RowsAffected() 642 t.Assert(n, 3) 643 644 count, err := db.Model(table).Where("id", 1).Or("id", 3).Count() 645 t.AssertNil(err) 646 t.Assert(count, 0) 647 }) 648 } 649 650 func Test_CreateUpdateTime_Struct(t *testing.T) { 651 table := "time_test_table_" + gtime.TimestampNanoStr() 652 if _, err := db.Exec(fmt.Sprintf(` 653 CREATE TABLE %s ( 654 id int(11) NOT NULL, 655 name varchar(45) DEFAULT NULL, 656 create_at datetime DEFAULT NULL, 657 update_at datetime DEFAULT NULL, 658 delete_at datetime DEFAULT NULL, 659 PRIMARY KEY (id) 660 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 661 `, table)); err != nil { 662 gtest.Error(err) 663 } 664 defer dropTable(table) 665 666 //db.SetDebug(true) 667 //defer db.SetDebug(false) 668 669 type Entity struct { 670 Id uint64 `orm:"id,primary" json:"id"` 671 Name string `orm:"name" json:"name"` 672 CreateAt *gtime.Time `orm:"create_at" json:"create_at"` 673 UpdateAt *gtime.Time `orm:"update_at" json:"update_at"` 674 DeleteAt *gtime.Time `orm:"delete_at" json:"delete_at"` 675 } 676 gtest.C(t, func(t *gtest.T) { 677 // Insert 678 dataInsert := &Entity{ 679 Id: 1, 680 Name: "name_1", 681 CreateAt: nil, 682 UpdateAt: nil, 683 DeleteAt: nil, 684 } 685 r, err := db.Model(table).Data(dataInsert).OmitEmpty().Insert() 686 t.AssertNil(err) 687 n, _ := r.RowsAffected() 688 t.Assert(n, 1) 689 690 oneInsert, err := db.Model(table).FindOne(1) 691 t.AssertNil(err) 692 t.Assert(oneInsert["id"].Int(), 1) 693 t.Assert(oneInsert["name"].String(), "name_1") 694 t.Assert(oneInsert["delete_at"].String(), "") 695 t.AssertGE(oneInsert["create_at"].GTime().Timestamp(), gtime.Timestamp()-2) 696 t.AssertGE(oneInsert["update_at"].GTime().Timestamp(), gtime.Timestamp()-2) 697 698 time.Sleep(2 * time.Second) 699 700 // Save 701 dataSave := &Entity{ 702 Id: 1, 703 Name: "name_10", 704 CreateAt: nil, 705 UpdateAt: nil, 706 DeleteAt: nil, 707 } 708 r, err = db.Model(table).Data(dataSave).OmitEmpty().Save() 709 t.AssertNil(err) 710 n, _ = r.RowsAffected() 711 t.Assert(n, 2) 712 713 oneSave, err := db.Model(table).FindOne(1) 714 t.AssertNil(err) 715 t.Assert(oneSave["id"].Int(), 1) 716 t.Assert(oneSave["name"].String(), "name_10") 717 t.Assert(oneSave["delete_at"].String(), "") 718 t.Assert(oneSave["create_at"].GTime().Timestamp(), oneInsert["create_at"].GTime().Timestamp()) 719 t.AssertNE(oneSave["update_at"].GTime().Timestamp(), oneInsert["update_at"].GTime().Timestamp()) 720 t.AssertGE(oneSave["update_at"].GTime().Timestamp(), gtime.Timestamp()-2) 721 722 time.Sleep(2 * time.Second) 723 724 // Update 725 dataUpdate := &Entity{ 726 Id: 1, 727 Name: "name_1000", 728 CreateAt: nil, 729 UpdateAt: nil, 730 DeleteAt: nil, 731 } 732 r, err = db.Model(table).Data(dataUpdate).WherePri(1).OmitEmpty().Update() 733 t.AssertNil(err) 734 n, _ = r.RowsAffected() 735 t.Assert(n, 1) 736 737 oneUpdate, err := db.Model(table).FindOne(1) 738 t.AssertNil(err) 739 t.Assert(oneUpdate["id"].Int(), 1) 740 t.Assert(oneUpdate["name"].String(), "name_1000") 741 t.Assert(oneUpdate["delete_at"].String(), "") 742 t.Assert(oneUpdate["create_at"].GTime().Timestamp(), oneInsert["create_at"].GTime().Timestamp()) 743 t.AssertGE(oneUpdate["update_at"].GTime().Timestamp(), gtime.Timestamp()-2) 744 745 // Replace 746 dataReplace := &Entity{ 747 Id: 1, 748 Name: "name_100", 749 CreateAt: nil, 750 UpdateAt: nil, 751 DeleteAt: nil, 752 } 753 r, err = db.Model(table).Data(dataReplace).OmitEmpty().Replace() 754 t.AssertNil(err) 755 n, _ = r.RowsAffected() 756 t.Assert(n, 2) 757 758 oneReplace, err := db.Model(table).FindOne(1) 759 t.AssertNil(err) 760 t.Assert(oneReplace["id"].Int(), 1) 761 t.Assert(oneReplace["name"].String(), "name_100") 762 t.Assert(oneReplace["delete_at"].String(), "") 763 t.AssertGE(oneReplace["create_at"].GTime().Timestamp(), oneInsert["create_at"].GTime().Timestamp()) 764 t.AssertGE(oneReplace["update_at"].GTime().Timestamp(), oneInsert["update_at"].GTime().Timestamp()) 765 766 time.Sleep(2 * time.Second) 767 768 // Delete 769 r, err = db.Model(table).Delete("id", 1) 770 t.AssertNil(err) 771 n, _ = r.RowsAffected() 772 t.Assert(n, 1) 773 // Delete Select 774 one4, err := db.Model(table).FindOne(1) 775 t.AssertNil(err) 776 t.Assert(len(one4), 0) 777 one5, err := db.Model(table).Unscoped().FindOne(1) 778 t.AssertNil(err) 779 t.Assert(one5["id"].Int(), 1) 780 t.AssertGE(one5["delete_at"].GTime().Timestamp(), gtime.Timestamp()-2) 781 // Delete Count 782 i, err := db.Model(table).FindCount() 783 t.AssertNil(err) 784 t.Assert(i, 0) 785 i, err = db.Model(table).Unscoped().FindCount() 786 t.AssertNil(err) 787 t.Assert(i, 1) 788 789 // Delete Unscoped 790 r, err = db.Model(table).Unscoped().Delete("id", 1) 791 t.AssertNil(err) 792 n, _ = r.RowsAffected() 793 t.Assert(n, 1) 794 one6, err := db.Model(table).Unscoped().FindOne(1) 795 t.AssertNil(err) 796 t.Assert(len(one6), 0) 797 i, err = db.Model(table).Unscoped().FindCount() 798 t.AssertNil(err) 799 t.Assert(i, 0) 800 }) 801 }