github.com/kunlun-qilian/sqlx/v3@v3.0.0/builder/def_table_test.go (about)

     1  package builder_test
     2  
     3  import (
     4  	"context"
     5  	"testing"
     6  
     7  	"github.com/kunlun-qilian/sqlx/v3/connectors/postgresql"
     8  
     9  	. "github.com/kunlun-qilian/sqlx/v3/builder"
    10  	"github.com/kunlun-qilian/sqlx/v3/builder/buidertestingutils"
    11  	"github.com/onsi/gomega"
    12  )
    13  
    14  func TestTable_Expr(t *testing.T) {
    15  	tUser := T("t_user",
    16  		Col("f_id").Field("ID").Type(uint64(0), ",autoincrement"),
    17  		Col("f_name").Field("Name").Type("", ",size=128,default=''"),
    18  	)
    19  
    20  	tUserRole := T("t_user_role",
    21  		Col("f_id").Field("ID").Type(uint64(0), ",autoincrement"),
    22  		Col("f_user_id").Field("UserID").Type(uint64(0), ""),
    23  	)
    24  
    25  	t.Run("replace table", func(t *testing.T) {
    26  		gomega.NewWithT(t).Expect(tUser.Expr("#.*")).To(buidertestingutils.BeExpr("t_user.*"))
    27  	})
    28  	t.Run("replace table col by field", func(t *testing.T) {
    29  		gomega.NewWithT(t).Expect(tUser.Expr("#ID = #ID + 1")).To(buidertestingutils.BeExpr("f_id = f_id + 1"))
    30  	})
    31  	t.Run("replace table col by field for function", func(t *testing.T) {
    32  		gomega.NewWithT(t).Expect(tUser.Expr("COUNT(#ID)")).To(buidertestingutils.BeExpr("COUNT(f_id)"))
    33  	})
    34  	t.Run("could handle context", func(t *testing.T) {
    35  		gomega.NewWithT(t).Expect(
    36  			Select(nil).
    37  				From(
    38  					tUser,
    39  					Where(
    40  						AsCond(tUser.Expr("#ID > 1")),
    41  					),
    42  					Join(tUserRole).On(AsCond(tUser.Expr("#ID = ?", tUserRole.Expr("#UserID")))),
    43  				),
    44  		).To(buidertestingutils.BeExpr(`
    45  SELECT * FROM t_user
    46  JOIN t_user_role ON t_user.f_id = t_user_role.f_user_id
    47  WHERE t_user.f_id > 1
    48  `))
    49  	})
    50  
    51  	t.Run("diff", func(t *testing.T) {
    52  		tUser := T("t_user",
    53  			Col("f_id").Field("ID").Type(uint64(0), ",autoincrement"),
    54  			Col("f_name").Field("Name").Type("", ",size=128,default=''"),
    55  		)
    56  
    57  		tUser2 := T("t_user",
    58  			Col("f_id").Field("ID").Type(uint64(0), ",autoincrement"),
    59  			Col("f_name").Field("Name").Type("", ",size=128,default=''"),
    60  			Col("f_nickname").Field("Nickname").Type("", ",size=128,default=''"),
    61  			PrimaryKey(Cols("f_id")),
    62  			Index("f_name", nil, "(#Name DESC NULLS LAST)"),
    63  		)
    64  
    65  		t.Run("from user to user2", func(t *testing.T) {
    66  			exprList := tUser2.Diff(tUser, &postgresql.PostgreSQLConnector{})
    67  
    68  			exprs := make([]string, len(exprList))
    69  			for i, expr := range exprList {
    70  				exprs[i] = expr.Ex(context.Background()).Query()
    71  			}
    72  
    73  			gomega.NewWithT(t).Expect(exprs).To(gomega.Equal([]string{
    74  				"ALTER TABLE t_user ADD COLUMN f_nickname character varying(128) NOT NULL DEFAULT ''::character varying;",
    75  				"ALTER TABLE t_user ADD PRIMARY KEY (f_id);",
    76  				"CREATE INDEX t_user_f_name ON t_user (f_name DESC NULLS LAST);",
    77  			}))
    78  		})
    79  
    80  		t.Run("from user2 to user1", func(t *testing.T) {
    81  			exprList := tUser.Diff(tUser2, &postgresql.PostgreSQLConnector{})
    82  
    83  			exprs := make([]string, len(exprList))
    84  			for i, expr := range exprList {
    85  				exprs[i] = expr.Ex(context.Background()).Query()
    86  			}
    87  
    88  			gomega.NewWithT(t).Expect(exprs).To(gomega.Equal([]string{
    89  				"ALTER TABLE t_user DROP CONSTRAINT t_user_pkey;",
    90  				"DROP INDEX IF EXISTS t_user_f_name;",
    91  			}))
    92  		})
    93  	})
    94  }