github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/pkg/sink/codec/simple/message_test.go (about)

     1  // Copyright 2023 PingCAP, Inc.
     2  //
     3  // Licensed under the Apache License, Version 2.0 (the "License");
     4  // you may not use this file except in compliance with the License.
     5  // You may obtain a copy of the License at
     6  //
     7  //     http://www.apache.org/licenses/LICENSE-2.0
     8  //
     9  // Unless required by applicable law or agreed to in writing, software
    10  // distributed under the License is distributed on an "AS IS" BASIS,
    11  // See the License for the specific language governing permissions and
    12  // limitations under the License.
    13  
    14  package simple
    15  
    16  import (
    17  	"testing"
    18  
    19  	"github.com/pingcap/tiflow/cdc/entry"
    20  	"github.com/stretchr/testify/require"
    21  )
    22  
    23  func TestNewTableSchema(t *testing.T) {
    24  	helper := entry.NewSchemaTestHelper(t)
    25  	defer helper.Close()
    26  
    27  	// case 1: test for primary key is not explicitly constraint
    28  	sql := `create table test.t1(
    29  		id int primary key,
    30  		name varchar(64) not null,
    31  		age int,
    32  		email varchar(255) not null,
    33  		unique index idx_name(name),
    34  		index idx_age_email(age,email)
    35  	);`
    36  	tableInfo := helper.DDL2Event(sql).TableInfo
    37  	want := &TableSchema{
    38  		Schema:  tableInfo.TableName.Schema,
    39  		Table:   tableInfo.TableName.Table,
    40  		TableID: tableInfo.TableName.TableID,
    41  		Version: tableInfo.UpdateTS,
    42  		Columns: []*columnSchema{
    43  			{
    44  				Name: "id",
    45  				DataType: dataType{
    46  					MySQLType: "int",
    47  					Charset:   "binary",
    48  					Collate:   "binary",
    49  					Length:    11,
    50  				},
    51  				Nullable: false,
    52  			},
    53  			{
    54  				Name: "name",
    55  				DataType: dataType{
    56  					MySQLType: "varchar",
    57  					Charset:   "utf8mb4",
    58  					Collate:   "utf8mb4_bin",
    59  					Length:    64,
    60  				},
    61  				Nullable: false,
    62  			},
    63  			{
    64  				Name: "age",
    65  				DataType: dataType{
    66  					MySQLType: "int",
    67  					Charset:   "binary",
    68  					Collate:   "binary",
    69  					Length:    11,
    70  				},
    71  				Nullable: true,
    72  			},
    73  			{
    74  				Name: "email",
    75  				DataType: dataType{
    76  					MySQLType: "varchar",
    77  					Charset:   "utf8mb4",
    78  					Collate:   "utf8mb4_bin",
    79  					Length:    255,
    80  				},
    81  				Nullable: false,
    82  			},
    83  		},
    84  		Indexes: []*IndexSchema{
    85  			{
    86  				Name:     "idx_name",
    87  				Unique:   true,
    88  				Primary:  false,
    89  				Nullable: false,
    90  				Columns:  []string{"name"},
    91  			},
    92  			{
    93  				Name:     "idx_age_email",
    94  				Unique:   false,
    95  				Primary:  false,
    96  				Nullable: true,
    97  				Columns:  []string{"age", "email"},
    98  			},
    99  			{
   100  				Name:     "primary",
   101  				Unique:   true,
   102  				Primary:  true,
   103  				Nullable: false,
   104  				Columns:  []string{"id"},
   105  			},
   106  		},
   107  	}
   108  	got := newTableSchema(tableInfo)
   109  	require.Equal(t, want, got)
   110  
   111  	// case 2: test for primary key is explicitly constraint
   112  	sql = `create table test.t2(
   113  		id int,
   114  		name varchar(64) not null,
   115  		age int,
   116  		email varchar(255) not null,
   117  		primary key(id),
   118  		unique index idx_name(name),
   119  		index idx_age_email(age,email)
   120  	);`
   121  	tableInfo = helper.DDL2Event(sql).TableInfo
   122  	want = &TableSchema{
   123  		Schema:  tableInfo.TableName.Schema,
   124  		Table:   tableInfo.TableName.Table,
   125  		TableID: tableInfo.TableName.TableID,
   126  		Version: tableInfo.UpdateTS,
   127  		Columns: []*columnSchema{
   128  			{
   129  				Name: "id",
   130  				DataType: dataType{
   131  					MySQLType: "int",
   132  					Charset:   "binary",
   133  					Collate:   "binary",
   134  					Length:    11,
   135  				},
   136  				Nullable: false,
   137  			},
   138  			{
   139  				Name: "name",
   140  				DataType: dataType{
   141  					MySQLType: "varchar",
   142  					Charset:   "utf8mb4",
   143  					Collate:   "utf8mb4_bin",
   144  					Length:    64,
   145  				},
   146  				Nullable: false,
   147  			},
   148  			{
   149  				Name: "age",
   150  				DataType: dataType{
   151  					MySQLType: "int",
   152  					Charset:   "binary",
   153  					Collate:   "binary",
   154  					Length:    11,
   155  				},
   156  				Nullable: true,
   157  			},
   158  			{
   159  				Name: "email",
   160  				DataType: dataType{
   161  					MySQLType: "varchar",
   162  					Charset:   "utf8mb4",
   163  					Collate:   "utf8mb4_bin",
   164  					Length:    255,
   165  				},
   166  				Nullable: false,
   167  			},
   168  		},
   169  		Indexes: []*IndexSchema{
   170  			{
   171  				Name:     "idx_name",
   172  				Unique:   true,
   173  				Primary:  false,
   174  				Nullable: false,
   175  				Columns:  []string{"name"},
   176  			},
   177  			{
   178  				Name:     "idx_age_email",
   179  				Unique:   false,
   180  				Primary:  false,
   181  				Nullable: true,
   182  				Columns:  []string{"age", "email"},
   183  			},
   184  			{
   185  				Name:     "primary",
   186  				Unique:   true,
   187  				Primary:  true,
   188  				Nullable: false,
   189  				Columns:  []string{"id"},
   190  			},
   191  		},
   192  	}
   193  	got = newTableSchema(tableInfo)
   194  	require.Equal(t, want, got)
   195  
   196  	// case 3: test for all data types in TiDB
   197  	sql = `create table test.t3(
   198  		t tinyint primary key,
   199  		tu1 tinyint unsigned,
   200  		s smallint,
   201  		su1 smallint unsigned,
   202  		m mediumint,
   203  		mu1 mediumint unsigned,
   204  		i int default 100,
   205  		iu1 int unsigned,
   206  		bi bigint,
   207  		biu1 bigint unsigned,
   208  		floatT float,
   209  		doubleT double,
   210  		decimalT decimal,
   211  		floatTu float unsigned,
   212  		doubleTu double unsigned,
   213  		decimalTu decimal unsigned,
   214  		varcharT varchar(255),
   215  		charT char(255),
   216  		binaryT binary(255),
   217  		 varbinaryT varbinary(255),
   218  		 tinytextT tinytext,
   219  		 textT text,
   220  		 mediumtextT mediumtext,
   221  		 longtextT longtext,
   222  		 tinyblobT tinyblob,
   223  		 blobT blob,
   224  		 mediumblobT mediumblob,
   225  		 longblobT longblob,
   226  		 dateT date,
   227  		 datetimeT datetime,
   228  		 timestampT timestamp,
   229  		 timeT time,
   230  		 yearT year,
   231  		 enumT enum('a', 'b', 'c') default 'b',
   232  		 setT set('a', 'b', 'c'),
   233  		 bitT bit(10),
   234  		 jsonT json,
   235  		 tgen tinyint AS (t+1))` // 38
   236  	tableInfo = helper.DDL2Event(sql).TableInfo
   237  	want = &TableSchema{
   238  		Schema:  tableInfo.TableName.Schema,
   239  		Table:   tableInfo.TableName.Table,
   240  		TableID: tableInfo.TableName.TableID,
   241  		Version: tableInfo.UpdateTS,
   242  		Columns: []*columnSchema{
   243  			{
   244  				Name: "t",
   245  				DataType: dataType{
   246  					MySQLType: "tinyint",
   247  					Charset:   "binary",
   248  					Collate:   "binary",
   249  					Length:    4,
   250  				},
   251  				Nullable: false,
   252  			},
   253  			{
   254  				Name: "tu1",
   255  				DataType: dataType{
   256  					MySQLType: "tinyint",
   257  					Charset:   "binary",
   258  					Collate:   "binary",
   259  					Length:    3,
   260  					Unsigned:  true,
   261  				},
   262  				Nullable: true,
   263  			},
   264  			{
   265  				Name: "s",
   266  				DataType: dataType{
   267  					MySQLType: "smallint",
   268  					Charset:   "binary",
   269  					Collate:   "binary",
   270  					Length:    6,
   271  				},
   272  				Nullable: true,
   273  			},
   274  			{
   275  				Name: "su1",
   276  				DataType: dataType{
   277  					MySQLType: "smallint",
   278  					Charset:   "binary",
   279  					Collate:   "binary",
   280  					Length:    5,
   281  					Unsigned:  true,
   282  				},
   283  				Nullable: true,
   284  			},
   285  			{
   286  				Name: "m",
   287  				DataType: dataType{
   288  					MySQLType: "mediumint",
   289  					Charset:   "binary",
   290  					Collate:   "binary",
   291  					Length:    9,
   292  				},
   293  				Nullable: true,
   294  			},
   295  			{
   296  				Name: "mu1",
   297  				DataType: dataType{
   298  					MySQLType: "mediumint",
   299  					Charset:   "binary",
   300  					Collate:   "binary",
   301  					Length:    8,
   302  					Unsigned:  true,
   303  				},
   304  				Nullable: true,
   305  			},
   306  			{
   307  				Name: "i",
   308  				DataType: dataType{
   309  					MySQLType: "int",
   310  					Charset:   "binary",
   311  					Collate:   "binary",
   312  					Length:    11,
   313  				},
   314  				Nullable: true,
   315  				Default:  "100",
   316  			},
   317  			{
   318  				Name: "iu1",
   319  				DataType: dataType{
   320  					MySQLType: "int",
   321  					Charset:   "binary",
   322  					Collate:   "binary",
   323  					Length:    10,
   324  					Unsigned:  true,
   325  				},
   326  				Nullable: true,
   327  			},
   328  			{
   329  				Name: "bi",
   330  				DataType: dataType{
   331  					MySQLType: "bigint",
   332  					Charset:   "binary",
   333  					Collate:   "binary",
   334  					Length:    20,
   335  				},
   336  				Nullable: true,
   337  			},
   338  			{
   339  				Name: "biu1",
   340  				DataType: dataType{
   341  					MySQLType: "bigint",
   342  					Charset:   "binary",
   343  					Collate:   "binary",
   344  					Length:    20,
   345  					Unsigned:  true,
   346  				},
   347  				Nullable: true,
   348  			},
   349  			{
   350  				Name: "floatT",
   351  				DataType: dataType{
   352  					MySQLType: "float",
   353  					Charset:   "binary",
   354  					Collate:   "binary",
   355  					Length:    12,
   356  				},
   357  				Nullable: true,
   358  			},
   359  			{
   360  				Name: "doubleT",
   361  				DataType: dataType{
   362  					MySQLType: "double",
   363  					Charset:   "binary",
   364  					Collate:   "binary",
   365  					Length:    22,
   366  				},
   367  				Nullable: true,
   368  			},
   369  			{
   370  				Name: "decimalT",
   371  				DataType: dataType{
   372  					MySQLType: "decimal",
   373  					Charset:   "binary",
   374  					Collate:   "binary",
   375  					Length:    10,
   376  				},
   377  				Nullable: true,
   378  			},
   379  			{
   380  				Name: "floatTu",
   381  				DataType: dataType{
   382  					MySQLType: "float",
   383  					Charset:   "binary",
   384  					Collate:   "binary",
   385  					Length:    12,
   386  					Unsigned:  true,
   387  				},
   388  				Nullable: true,
   389  			},
   390  			{
   391  				Name: "doubleTu",
   392  				DataType: dataType{
   393  					MySQLType: "double",
   394  					Charset:   "binary",
   395  					Collate:   "binary",
   396  					Length:    22,
   397  					Unsigned:  true,
   398  				},
   399  				Nullable: true,
   400  			},
   401  			{
   402  				Name: "decimalTu",
   403  				DataType: dataType{
   404  					MySQLType: "decimal",
   405  					Charset:   "binary",
   406  					Collate:   "binary",
   407  					Length:    10,
   408  					Unsigned:  true,
   409  				},
   410  				Nullable: true,
   411  			},
   412  			{
   413  				Name: "varcharT",
   414  				DataType: dataType{
   415  					MySQLType: "varchar",
   416  					Charset:   "utf8mb4",
   417  					Collate:   "utf8mb4_bin",
   418  					Length:    255,
   419  				},
   420  				Nullable: true,
   421  			},
   422  			{
   423  				Name: "charT",
   424  				DataType: dataType{
   425  					MySQLType: "char",
   426  					Charset:   "utf8mb4",
   427  					Collate:   "utf8mb4_bin",
   428  					Length:    255,
   429  				},
   430  				Nullable: true,
   431  			},
   432  			{
   433  				Name: "binaryT",
   434  				DataType: dataType{
   435  					MySQLType: "binary",
   436  					Charset:   "binary",
   437  					Collate:   "binary",
   438  					Length:    255,
   439  				},
   440  				Nullable: true,
   441  			},
   442  			{
   443  				Name: "varbinaryT",
   444  				DataType: dataType{
   445  					MySQLType: "varbinary",
   446  					Charset:   "binary",
   447  					Collate:   "binary",
   448  					Length:    255,
   449  				},
   450  				Nullable: true,
   451  			},
   452  			{
   453  				Name: "tinytextT",
   454  				DataType: dataType{
   455  					MySQLType: "tinytext",
   456  					Charset:   "utf8mb4",
   457  					Collate:   "utf8mb4_bin",
   458  					Length:    255,
   459  				},
   460  				Nullable: true,
   461  			},
   462  			{
   463  				Name: "textT",
   464  				DataType: dataType{
   465  					MySQLType: "text",
   466  					Charset:   "utf8mb4",
   467  					Collate:   "utf8mb4_bin",
   468  					Length:    65535,
   469  				},
   470  				Nullable: true,
   471  			},
   472  			{
   473  				Name: "mediumtextT",
   474  				DataType: dataType{
   475  					MySQLType: "mediumtext",
   476  					Charset:   "utf8mb4",
   477  					Collate:   "utf8mb4_bin",
   478  					Length:    16777215,
   479  				},
   480  				Nullable: true,
   481  			},
   482  			{
   483  				Name: "longtextT",
   484  				DataType: dataType{
   485  					MySQLType: "longtext",
   486  					Charset:   "utf8mb4",
   487  					Collate:   "utf8mb4_bin",
   488  					Length:    4294967295,
   489  				},
   490  				Nullable: true,
   491  			},
   492  			{
   493  				Name: "tinyblobT",
   494  				DataType: dataType{
   495  					MySQLType: "tinyblob",
   496  					Charset:   "binary",
   497  					Collate:   "binary",
   498  					Length:    255,
   499  				},
   500  				Nullable: true,
   501  			},
   502  			{
   503  				Name: "blobT",
   504  				DataType: dataType{
   505  					MySQLType: "blob",
   506  					Charset:   "binary",
   507  					Collate:   "binary",
   508  					Length:    65535,
   509  				},
   510  				Nullable: true,
   511  			},
   512  			{
   513  				Name: "mediumblobT",
   514  				DataType: dataType{
   515  					MySQLType: "mediumblob",
   516  					Charset:   "binary",
   517  					Collate:   "binary",
   518  					Length:    16777215,
   519  				},
   520  				Nullable: true,
   521  			},
   522  			{
   523  				Name: "longblobT",
   524  				DataType: dataType{
   525  					MySQLType: "longblob",
   526  					Charset:   "binary",
   527  					Collate:   "binary",
   528  					Length:    4294967295,
   529  				},
   530  				Nullable: true,
   531  			},
   532  			{
   533  				Name: "dateT",
   534  				DataType: dataType{
   535  					MySQLType: "date",
   536  					Charset:   "binary",
   537  					Collate:   "binary",
   538  					Length:    10,
   539  				},
   540  				Nullable: true,
   541  			},
   542  			{
   543  				Name: "datetimeT",
   544  				DataType: dataType{
   545  					MySQLType: "datetime",
   546  					Charset:   "binary",
   547  					Collate:   "binary",
   548  					Length:    19,
   549  				},
   550  				Nullable: true,
   551  			},
   552  			{
   553  				Name: "timestampT",
   554  				DataType: dataType{
   555  					MySQLType: "timestamp",
   556  					Charset:   "binary",
   557  					Collate:   "binary",
   558  					Length:    19,
   559  				},
   560  				Nullable: true,
   561  			},
   562  			{
   563  				Name: "timeT",
   564  				DataType: dataType{
   565  					MySQLType: "time",
   566  					Charset:   "binary",
   567  					Collate:   "binary",
   568  					Length:    10,
   569  				},
   570  				Nullable: true,
   571  			},
   572  			{
   573  				Name: "yearT",
   574  				DataType: dataType{
   575  					MySQLType: "year",
   576  					Charset:   "binary",
   577  					Collate:   "binary",
   578  					Length:    4,
   579  					Unsigned:  true,
   580  					Zerofill:  true,
   581  				},
   582  				Nullable: true,
   583  			},
   584  			{
   585  				Name: "enumT",
   586  				DataType: dataType{
   587  					MySQLType: "enum",
   588  					Charset:   "utf8mb4",
   589  					Collate:   "utf8mb4_bin",
   590  					Length:    1,
   591  					Elements:  []string{"a", "b", "c"},
   592  				},
   593  				Nullable: true,
   594  				Default:  "b",
   595  			},
   596  			{
   597  				Name: "setT",
   598  				DataType: dataType{
   599  					MySQLType: "set",
   600  					Charset:   "utf8mb4",
   601  					Collate:   "utf8mb4_bin",
   602  					Length:    5,
   603  					Elements:  []string{"a", "b", "c"},
   604  				},
   605  				Nullable: true,
   606  			},
   607  			{
   608  				Name: "bitT",
   609  				DataType: dataType{
   610  					MySQLType: "bit",
   611  					Charset:   "binary",
   612  					Collate:   "binary",
   613  					Length:    10,
   614  					Unsigned:  true,
   615  				},
   616  				Nullable: true,
   617  			},
   618  			{
   619  				Name: "jsonT",
   620  				DataType: dataType{
   621  					MySQLType: "json",
   622  					Charset:   "binary",
   623  					Collate:   "binary",
   624  					Length:    4294967295,
   625  				},
   626  				Nullable: true,
   627  			},
   628  			{
   629  				Name: "tgen",
   630  				DataType: dataType{
   631  					MySQLType: "tinyint",
   632  					Charset:   "binary",
   633  					Collate:   "binary",
   634  					Length:    4,
   635  				},
   636  				Nullable: true,
   637  			},
   638  		},
   639  		Indexes: []*IndexSchema{
   640  			{
   641  				Name:     "primary",
   642  				Unique:   true,
   643  				Primary:  true,
   644  				Nullable: false,
   645  				Columns:  []string{"t"},
   646  			},
   647  		},
   648  	}
   649  	got = newTableSchema(tableInfo)
   650  	require.Equal(t, want, got)
   651  }