github.com/kunlun-qilian/sqlx/v3@v3.0.0/database_test.go (about) 1 package sqlx_test 2 3 import ( 4 "context" 5 "database/sql/driver" 6 "fmt" 7 "os" 8 "testing" 9 "time" 10 11 "github.com/go-courier/logr" 12 13 "github.com/go-courier/metax" 14 _ "github.com/go-sql-driver/mysql" 15 "github.com/google/uuid" 16 "github.com/kunlun-qilian/sqlx/v3" 17 "github.com/kunlun-qilian/sqlx/v3/builder" 18 "github.com/kunlun-qilian/sqlx/v3/datatypes" 19 "github.com/kunlun-qilian/sqlx/v3/migration" 20 "github.com/kunlun-qilian/sqlx/v3/mysqlconnector" 21 "github.com/kunlun-qilian/sqlx/v3/postgresqlconnector" 22 . "github.com/onsi/gomega" 23 ) 24 25 var ( 26 mysqlConnector = &mysqlconnector.MysqlConnector{ 27 Host: "root@tcp(0.0.0.0:3306)", 28 Extra: "charset=utf8mb4&parseTime=true&interpolateParams=true&autocommit=true&loc=Local", 29 } 30 31 postgresConnector = &postgresqlconnector.PostgreSQLConnector{ 32 Host: "postgres://postgres@0.0.0.0:5432", 33 Extra: "sslmode=disable", 34 Extensions: []string{"postgis"}, 35 } 36 ) 37 38 func Background() context.Context { 39 return logr.WithLogger(context.Background(), logr.StdLogger()) 40 } 41 42 type TableOperateTime struct { 43 CreatedAt datatypes.MySQLDatetime `db:"f_created_at,default=CURRENT_TIMESTAMP,onupdate=CURRENT_TIMESTAMP"` 44 UpdatedAt int64 `db:"f_updated_at,default='0'"` 45 } 46 47 type Gender int 48 49 const ( 50 GenderMale Gender = iota + 1 51 GenderFemale 52 ) 53 54 func (Gender) EnumType() string { 55 return "Gender" 56 } 57 58 func (Gender) Enums() map[int][]string { 59 return map[int][]string{ 60 int(GenderMale): {"male", "男"}, 61 int(GenderFemale): {"female", "女"}, 62 } 63 } 64 65 func (g Gender) String() string { 66 switch g { 67 case GenderMale: 68 return "male" 69 case GenderFemale: 70 return "female" 71 } 72 return "" 73 } 74 75 type User struct { 76 ID uint64 `db:"f_id,autoincrement"` 77 Name string `db:"f_name,size=255,default=''"` 78 Nickname string `db:"f_nickname,size=255,default=''"` 79 Username string `db:"f_username,default=''"` 80 Gender Gender `db:"f_gender,default='0'"` 81 82 TableOperateTime 83 } 84 85 func (user *User) Comments() map[string]string { 86 return map[string]string{ 87 "Name": "姓名", 88 } 89 } 90 91 func (user *User) TableName() string { 92 return "t_user" 93 } 94 95 func (user *User) PrimaryKey() []string { 96 return []string{"ID"} 97 } 98 99 func (user *User) Indexes() builder.Indexes { 100 return builder.Indexes{ 101 "i_nickname": {"Nickname"}, 102 } 103 } 104 105 func (user *User) UniqueIndexes() builder.Indexes { 106 return builder.Indexes{ 107 "i_name": {"Name"}, 108 } 109 } 110 111 type User2 struct { 112 ID uint64 `db:"f_id,autoincrement"` 113 Nickname string `db:"f_nickname,size=255,default=''"` 114 Gender Gender `db:"f_gender,default='0'"` 115 Name string `db:"f_name,deprecated=f_real_name"` 116 RealName string `db:"f_real_name,size=255,default=''"` 117 Age int32 `db:"f_age,default='0'"` 118 Username string `db:"f_username,deprecated"` 119 } 120 121 func (user *User2) TableName() string { 122 return "t_user" 123 } 124 125 func (user *User2) PrimaryKey() []string { 126 return []string{"ID"} 127 } 128 129 func (user *User2) Indexes() builder.Indexes { 130 return builder.Indexes{ 131 "i_nickname": {"Nickname"}, 132 } 133 } 134 135 func (user *User2) UniqueIndexes() builder.Indexes { 136 return builder.Indexes{ 137 "i_name": {"RealName"}, 138 } 139 } 140 141 func TestMigrate(t *testing.T) { 142 os.Setenv("PROJECT_FEATURE", "test1") 143 defer func() { 144 os.Remove("PROJECT_FEATURE") 145 }() 146 147 dbTest := sqlx.NewFeatureDatabase("test_for_migrate") 148 149 for i, connector := range []driver.Connector{ 150 mysqlConnector, 151 postgresConnector, 152 } { 153 t.Run(fmt.Sprintf("%d", i), func(t *testing.T) { 154 for _, schema := range []string{"import", "public", "backup"} { 155 dbTest.Tables.Range(func(table *builder.Table, idx int) { 156 db := dbTest.OpenDB(connector).WithSchema(schema) 157 _, _ = db.ExecExpr(db.Dialect().DropTable(table)) 158 }) 159 160 t.Run("create table", func(t *testing.T) { 161 dbTest.Register(&User{}) 162 db := dbTest.OpenDB(connector).WithSchema(schema) 163 164 t.Run("first migrate", func(t *testing.T) { 165 err := migration.Migrate(db, nil) 166 NewWithT(t).Expect(err).To(BeNil()) 167 }) 168 169 t.Run("again", func(t *testing.T) { 170 _ = migration.Migrate(db, os.Stdout) 171 err := migration.Migrate(db, nil) 172 NewWithT(t).Expect(err).To(BeNil()) 173 }) 174 }) 175 176 t.Run("no migrate", func(t *testing.T) { 177 dbTest.Register(&User{}) 178 db := dbTest.OpenDB(connector).WithSchema(schema) 179 err := migration.Migrate(db, nil) 180 NewWithT(t).Expect(err).To(BeNil()) 181 182 t.Run("migrate to user2", func(t *testing.T) { 183 dbTest.Register(&User2{}) 184 db := dbTest.OpenDB(connector).WithSchema(schema) 185 err := migration.Migrate(db, nil) 186 NewWithT(t).Expect(err).To(BeNil()) 187 }) 188 189 t.Run("migrate to user2 again", func(t *testing.T) { 190 dbTest.Register(&User2{}) 191 db := dbTest.OpenDB(connector).WithSchema(schema) 192 err := migration.Migrate(db, nil) 193 NewWithT(t).Expect(err).To(BeNil()) 194 }) 195 }) 196 197 t.Run("migrate to user", func(t *testing.T) { 198 db := dbTest.OpenDB(connector).WithSchema(schema) 199 err := migration.Migrate(db, os.Stdout) 200 NewWithT(t).Expect(err).To(BeNil()) 201 err = migration.Migrate(db, nil) 202 NewWithT(t).Expect(err).To(BeNil()) 203 }) 204 205 dbTest.Tables.Range(func(table *builder.Table, idx int) { 206 db := dbTest.OpenDB(connector).WithSchema(schema) 207 _, _ = db.ExecExpr(db.Dialect().DropTable(table)) 208 }) 209 } 210 }) 211 } 212 } 213 214 func TestMysqlDBNameWithReservedWord(t *testing.T) { 215 dbTest := sqlx.NewDatabase("test-name-reserved") 216 d := dbTest.OpenDB(mysqlConnector) 217 218 db := d.WithContext(metax.ContextWithMeta(d.Context(), metax.ParseMeta("_id=11111"))) 219 err := migration.Migrate(db, nil) 220 if err != nil { 221 t.Fatal(err) 222 } 223 224 defer func() { 225 dialect := db.Dialect() 226 exec := func(expr builder.SqlExpr) error { 227 if expr == nil || expr.IsNil() { 228 return nil 229 } 230 231 _, err := db.ExecExpr(expr) 232 return err 233 } 234 235 if err := exec(dialect.DropDatabase(d.Name)); err != nil { 236 t.Fatal(err) 237 } 238 }() 239 } 240 241 func TestCRUD(t *testing.T) { 242 dbTest := sqlx.NewDatabase("test_crud") 243 244 for _, connector := range []driver.Connector{ 245 mysqlConnector, 246 postgresConnector, 247 } { 248 t.Run("", func(t *testing.T) { 249 d := dbTest.OpenDB(connector) 250 251 db := d.WithContext(metax.ContextWithMeta(d.Context(), metax.ParseMeta("_id=11111"))) 252 253 userTable := dbTest.Register(&User{}) 254 255 err := migration.Migrate(db, nil) 256 257 NewWithT(t).Expect(err).To(BeNil()) 258 259 t.Run("insert single", func(t *testing.T) { 260 user := User{ 261 Name: uuid.New().String(), 262 Gender: GenderMale, 263 } 264 265 t.Run("cancel", func(t *testing.T) { 266 ctx, cancel := context.WithCancel(Background()) 267 db2 := db.WithContext(ctx) 268 269 go func() { 270 time.Sleep(5 * time.Millisecond) 271 cancel() 272 }() 273 274 err := sqlx.NewTasks(db2). 275 With( 276 func(db sqlx.DBExecutor) error { 277 _, err := db.ExecExpr(sqlx.InsertToDB(db, &user, nil)) 278 return err 279 }, 280 func(db sqlx.DBExecutor) error { 281 time.Sleep(10 * time.Millisecond) 282 return nil 283 }, 284 ). 285 Do() 286 287 NewWithT(t).Expect(err).NotTo(BeNil()) 288 }) 289 _, err := db.ExecExpr(sqlx.InsertToDB(db, &user, nil)) 290 NewWithT(t).Expect(err).To(BeNil()) 291 292 t.Run("update", func(t *testing.T) { 293 user.Gender = GenderFemale 294 _, err := db.ExecExpr( 295 builder.Update(dbTest.T(&user)). 296 Set(sqlx.AsAssignments(db, &user)...). 297 Where( 298 userTable.F("Name").Eq(user.Name), 299 ), 300 ) 301 NewWithT(t).Expect(err).To(BeNil()) 302 }) 303 t.Run("select", func(t *testing.T) { 304 userForSelect := User{} 305 err := db.QueryExprAndScan( 306 builder.Select(nil).From( 307 userTable, 308 builder.Where(userTable.F("Name").Eq(user.Name)), 309 builder.Comment("FindUser"), 310 ), 311 &userForSelect) 312 313 NewWithT(t).Expect(err).To(BeNil()) 314 315 NewWithT(t).Expect(user.Name).To(Equal(userForSelect.Name)) 316 NewWithT(t).Expect(user.Gender).To(Equal(userForSelect.Gender)) 317 }) 318 t.Run("conflict", func(t *testing.T) { 319 _, err := db.ExecExpr(sqlx.InsertToDB(db, &user, nil)) 320 NewWithT(t).Expect(sqlx.DBErr(err).IsConflict()).To(BeTrue()) 321 }) 322 }) 323 db.(*sqlx.DB).Tables.Range(func(table *builder.Table, idx int) { 324 _, err := db.ExecExpr(db.Dialect().DropTable(table)) 325 NewWithT(t).Expect(err).To(BeNil()) 326 }) 327 }) 328 } 329 } 330 331 type UserSet map[string]*User 332 333 func (UserSet) New() interface{} { 334 return &User{} 335 } 336 337 func (u UserSet) Next(v interface{}) error { 338 user := v.(*User) 339 u[user.Name] = user 340 time.Sleep(500 * time.Microsecond) 341 return nil 342 } 343 344 func TestSelect(t *testing.T) { 345 dbTest := sqlx.NewDatabase("test_for_s") 346 347 for _, connector := range []driver.Connector{ 348 mysqlConnector, 349 postgresConnector, 350 } { 351 t.Run("", func(t *testing.T) { 352 db := dbTest.OpenDB(connector) 353 table := dbTest.Register(&User{}) 354 355 db.Tables.Range(func(t *builder.Table, idx int) { 356 _, _ = db.ExecExpr(db.Dialect().DropTable(t)) 357 }) 358 359 err := migration.Migrate(db, nil) 360 NewWithT(t).Expect(err).To(BeNil()) 361 362 { 363 columns := table.MustFields("Name", "Gender") 364 values := make([]interface{}, 0) 365 366 for i := 0; i < 1000; i++ { 367 values = append(values, uuid.New().String(), GenderMale) 368 } 369 370 _, err := db.ExecExpr(builder.Insert().Into(table).Values(columns, values...)) 371 NewWithT(t).Expect(err).To(BeNil()) 372 } 373 374 t.Run("select to slice", func(t *testing.T) { 375 users := make([]User, 0) 376 err := db.QueryExprAndScan( 377 builder.Select(nil).From(table, builder.Where(table.F("Gender").Eq(GenderMale))), 378 &users, 379 ) 380 NewWithT(t).Expect(err).To(BeNil()) 381 NewWithT(t).Expect(users).To(HaveLen(1000)) 382 }) 383 384 t.Run("select to set", func(t *testing.T) { 385 userSet := UserSet{} 386 err := db.QueryExprAndScan( 387 builder.Select(nil).From(table, builder.Where(table.F("Gender").Eq(GenderMale))), 388 userSet, 389 ) 390 NewWithT(t).Expect(err).To(BeNil()) 391 NewWithT(t).Expect(userSet).To(HaveLen(1000)) 392 }) 393 394 t.Run("not found", func(t *testing.T) { 395 user := User{} 396 err := db.QueryExprAndScan( 397 builder.Select(nil).From( 398 table, 399 builder.Where(table.F("ID").Eq(1001)), 400 ), 401 &user, 402 ) 403 NewWithT(t).Expect(sqlx.DBErr(err).IsNotFound()).To(BeTrue()) 404 }) 405 406 t.Run("count", func(t *testing.T) { 407 count := 0 408 err := db.QueryExprAndScan( 409 builder.Select(builder.Count()).From(table), 410 &count, 411 ) 412 NewWithT(t).Expect(err).To(BeNil()) 413 NewWithT(t).Expect(count).To(Equal(1000)) 414 }) 415 416 t.Run("canceled", func(t *testing.T) { 417 ctx, cancel := context.WithCancel(Background()) 418 db2 := db.WithContext(ctx) 419 420 go func() { 421 time.Sleep(3 * time.Millisecond) 422 cancel() 423 }() 424 425 userSet := UserSet{} 426 err := db2.QueryExprAndScan( 427 builder.Select(nil).From(table, builder.Where(table.F("Gender").Eq(GenderMale))), 428 userSet, 429 ) 430 NewWithT(t).Expect(err).NotTo(BeNil()) 431 }) 432 433 db.Tables.Range(func(tab *builder.Table, idx int) { 434 _, _ = db.ExecExpr(db.Dialect().DropTable(tab)) 435 }) 436 }) 437 } 438 }