github.com/eden-framework/sqlx@v0.0.2/database_test.go (about)

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