github.com/artisanhe/tools@v1.0.1-0.20210607022958-19a8fef2eb04/sqlx/builder/obj_table_test.go (about)

     1  package builder
     2  
     3  import (
     4  	"testing"
     5  
     6  	"github.com/stretchr/testify/assert"
     7  )
     8  
     9  func TestTable(t *testing.T) {
    10  	tt := assert.New(t)
    11  
    12  	db := DB("db")
    13  	table := T(db, "t")
    14  
    15  	tableNext := table.Define(
    16  		Col(table, "F_id").Type("bigint(64) unsigned NOT NULL AUTO_INCREMENT"),
    17  		Col(table, "F_name").Type("varchar(128) NOT NULL DEFAULT ''"),
    18  		Col(table, "F_created_at").Type("bigint(64) NOT NULL DEFAULT '0'"),
    19  		Col(table, "F_updated_at").Type("bigint(64) NOT NULL DEFAULT '0'"),
    20  		Index("I_name").WithCols(Col(table, "F_name")),
    21  		UniqueIndex("I_username").WithCols(Col(table, "F_username"), Col(table, "F_id")),
    22  	)
    23  
    24  	table = table.Define(
    25  		Col(table, "F_id").Field("ID"), // skip without type
    26  		Col(table, "F_id").Field("ID").Type("bigint(64) unsigned NOT NULL AUTO_INCREMENT"),
    27  		Col(table, "F_name").Field("Name").Type("varchar(255) NOT NULL DEFAULT ''"),
    28  		Col(table, "F_username").Field("Username").Type("varchar(255) NOT NULL DEFAULT ''"),
    29  		Col(table, "F_created_at").Field("CreatedAt").Type("bigint(64) NOT NULL DEFAULT '0'"),
    30  		Col(table, "F_updated_at").Field("UpdatedAt").Type("bigint(64) NOT NULL DEFAULT '0'"),
    31  		PrimaryKey(), // skip without Columns
    32  		PrimaryKey().WithCols(Col(table, "F_id")),
    33  		Index("I_name").WithCols(Col(table, "F_name")),
    34  		UniqueIndex("I_username").WithCols(Col(table, "F_name"), Col(table, "F_id")),
    35  	)
    36  
    37  	{
    38  		cols, values := table.ColumnsAndValuesByFieldValues(FieldValues{
    39  			"ID":   1,
    40  			"Name": "1",
    41  		})
    42  
    43  		tt.Equal(table.Fields("ID", "Name").Len(), cols.Len())
    44  		if values[0] == 1 {
    45  			tt.Equal([]interface{}{1, "1"}, values)
    46  		} else {
    47  			tt.Equal([]interface{}{"1", 1}, values)
    48  		}
    49  	}
    50  
    51  	{
    52  		assignments := table.AssignsByFieldValues(FieldValues{
    53  			"ID":   1,
    54  			"Name": "1",
    55  		})
    56  
    57  		expr := assignments.Expr()
    58  
    59  		if expr.Args[0] == 1 {
    60  			tt.Equal(Expr("`F_id` = ?, `F_name` = ?", 1, "1"), assignments.Expr())
    61  		} else {
    62  			tt.Equal(Expr("`F_name` = ?, `F_id` = ?", "1", 1), assignments.Expr())
    63  		}
    64  
    65  	}
    66  
    67  	exprCases{
    68  		Case(
    69  			"create Table",
    70  			table.Create(true),
    71  			Expr("CREATE TABLE IF NOT EXISTS `db`.`t` ("+
    72  				"`F_id` bigint(64) unsigned NOT NULL AUTO_INCREMENT, "+
    73  				"`F_name` varchar(255) NOT NULL DEFAULT '', "+
    74  				"`F_username` varchar(255) NOT NULL DEFAULT '', "+
    75  				"`F_created_at` bigint(64) NOT NULL DEFAULT '0', "+
    76  				"`F_updated_at` bigint(64) NOT NULL DEFAULT '0', "+
    77  				"PRIMARY KEY (`F_id`), "+
    78  				"INDEX `I_name` (`F_name`), "+
    79  				"UNIQUE INDEX `I_username` (`F_name`,`F_id`)"+
    80  				") ENGINE=InnoDB CHARSET=utf8"),
    81  		),
    82  		Case(
    83  			"create Table",
    84  			table.Create(false),
    85  			Expr("CREATE TABLE `db`.`t` ("+
    86  				"`F_id` bigint(64) unsigned NOT NULL AUTO_INCREMENT, "+
    87  				"`F_name` varchar(255) NOT NULL DEFAULT '', "+
    88  				"`F_username` varchar(255) NOT NULL DEFAULT '', "+
    89  				"`F_created_at` bigint(64) NOT NULL DEFAULT '0', "+
    90  				"`F_updated_at` bigint(64) NOT NULL DEFAULT '0', "+
    91  				"PRIMARY KEY (`F_id`), "+
    92  				"INDEX `I_name` (`F_name`), "+
    93  				"UNIQUE INDEX `I_username` (`F_name`,`F_id`)"+
    94  				") ENGINE=InnoDB CHARSET=utf8"),
    95  		),
    96  		Case(
    97  			"cond",
    98  			table.Cond("#ID = ? AND #Username = ?"),
    99  			Expr("`F_id` = ? AND `F_username` = ?"),
   100  		),
   101  		Case(
   102  			"cond with unregister col field",
   103  			table.Cond("#ID = ? AND #Usernames = ?"),
   104  			Expr("`F_id` = ? AND #Usernames = ?"),
   105  		),
   106  		Case(
   107  			"diff for migrate",
   108  			table.Diff(tableNext),
   109  			Expr("ALTER TABLE `db`.`t` "+
   110  				"DROP COLUMN `F_username`, "+
   111  				"MODIFY COLUMN `F_name` varchar(128) NOT NULL DEFAULT '', "+
   112  				"DROP PRIMARY KEY, "+
   113  				"DROP INDEX `I_username`, ADD UNIQUE INDEX `I_username` (`F_username`,`F_id`)",
   114  			),
   115  		),
   116  		Case(
   117  			"revert diff for migrate",
   118  			tableNext.Diff(table),
   119  			Expr("ALTER TABLE `db`.`t` "+
   120  				"MODIFY COLUMN `F_name` varchar(255) NOT NULL DEFAULT '', "+
   121  				"ADD COLUMN `F_username` varchar(255) NOT NULL DEFAULT '', "+
   122  				"DROP INDEX `I_username`, ADD UNIQUE INDEX `I_username` (`F_name`,`F_id`), "+
   123  				"ADD PRIMARY KEY (`F_id`)",
   124  			),
   125  		),
   126  		Case(
   127  			"diff without change",
   128  			table.Diff(table),
   129  			nil,
   130  		),
   131  		Case(
   132  			"drop Table",
   133  			table.Drop(),
   134  			Expr("DROP TABLE `db`.`t`"),
   135  		),
   136  		Case(
   137  			"truncate Table",
   138  			table.Truncate(),
   139  			Expr("TRUNCATE TABLE `db`.`t`"),
   140  		),
   141  	}.Run(t, "Table")
   142  }