github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/pkg/sink/cloudstorage/table_definition_test.go (about)

     1  // Copyright 2022 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  package cloudstorage
    14  
    15  import (
    16  	"encoding/json"
    17  	"math"
    18  	"math/rand"
    19  	"testing"
    20  
    21  	"github.com/pingcap/tidb/pkg/parser/charset"
    22  	timodel "github.com/pingcap/tidb/pkg/parser/model"
    23  	"github.com/pingcap/tidb/pkg/parser/mysql"
    24  	"github.com/pingcap/tidb/pkg/types"
    25  	"github.com/pingcap/tiflow/cdc/model"
    26  	"github.com/stretchr/testify/require"
    27  )
    28  
    29  func generateTableDef() (TableDefinition, *model.TableInfo) {
    30  	var columns []*timodel.ColumnInfo
    31  	ft := types.NewFieldType(mysql.TypeLong)
    32  	ft.SetFlag(mysql.PriKeyFlag | mysql.NotNullFlag)
    33  	col := &timodel.ColumnInfo{
    34  		Name:         timodel.NewCIStr("Id"),
    35  		FieldType:    *ft,
    36  		DefaultValue: 10,
    37  	}
    38  	columns = append(columns, col)
    39  
    40  	ft = types.NewFieldType(mysql.TypeVarchar)
    41  	ft.SetFlag(mysql.NotNullFlag)
    42  	ft.SetFlen(128)
    43  	col = &timodel.ColumnInfo{
    44  		Name:         timodel.NewCIStr("LastName"),
    45  		FieldType:    *ft,
    46  		DefaultValue: "Default LastName",
    47  	}
    48  	columns = append(columns, col)
    49  
    50  	ft = types.NewFieldType(mysql.TypeVarchar)
    51  	ft.SetFlen(64)
    52  	col = &timodel.ColumnInfo{
    53  		Name:         timodel.NewCIStr("FirstName"),
    54  		FieldType:    *ft,
    55  		DefaultValue: "Default FirstName",
    56  	}
    57  	columns = append(columns, col)
    58  
    59  	ft = types.NewFieldType(mysql.TypeDatetime)
    60  	col = &timodel.ColumnInfo{
    61  		Name:         timodel.NewCIStr("Birthday"),
    62  		FieldType:    *ft,
    63  		DefaultValue: 12345678,
    64  	}
    65  	columns = append(columns, col)
    66  
    67  	tableInfo := &model.TableInfo{
    68  		TableInfo: &timodel.TableInfo{Columns: columns},
    69  		Version:   100,
    70  		TableName: model.TableName{
    71  			Schema:  "schema1",
    72  			Table:   "table1",
    73  			TableID: 20,
    74  		},
    75  	}
    76  
    77  	var def TableDefinition
    78  	def.FromTableInfo(tableInfo, tableInfo.Version, false)
    79  	return def, tableInfo
    80  }
    81  
    82  func TestTableCol(t *testing.T) {
    83  	t.Parallel()
    84  
    85  	testCases := []struct {
    86  		name      string
    87  		filedType byte
    88  		flen      int
    89  		decimal   int
    90  		flag      uint
    91  		charset   string
    92  		expected  string
    93  	}{
    94  		{
    95  			name:      "time",
    96  			filedType: mysql.TypeDuration,
    97  			flen:      math.MinInt,
    98  			decimal:   5,
    99  			flag:      0,
   100  			expected:  `{"ColumnName":"","ColumnType":"TIME","ColumnScale":"5"}`,
   101  		},
   102  		{
   103  			name:      "int(5) UNSIGNED",
   104  			filedType: mysql.TypeLong,
   105  			flen:      5,
   106  			decimal:   math.MinInt,
   107  			flag:      mysql.UnsignedFlag,
   108  			expected:  `{"ColumnName":"","ColumnType":"INT UNSIGNED","ColumnPrecision":"5"}`,
   109  		},
   110  		{
   111  			name:      "float(12,3)",
   112  			filedType: mysql.TypeFloat,
   113  			flen:      12,
   114  			decimal:   3,
   115  			flag:      0,
   116  			expected:  `{"ColumnName":"","ColumnType":"FLOAT","ColumnPrecision":"12","ColumnScale":"3"}`,
   117  		},
   118  		{
   119  			name:      "float",
   120  			filedType: mysql.TypeFloat,
   121  			flen:      12,
   122  			decimal:   -1,
   123  			flag:      0,
   124  			expected:  `{"ColumnName":"","ColumnType":"FLOAT","ColumnPrecision":"12"}`,
   125  		},
   126  		{
   127  			name:      "float",
   128  			filedType: mysql.TypeFloat,
   129  			flen:      5,
   130  			decimal:   -1,
   131  			flag:      0,
   132  			expected:  `{"ColumnName":"","ColumnType":"FLOAT","ColumnPrecision":"5"}`,
   133  		},
   134  		{
   135  			name:      "float(7,3)",
   136  			filedType: mysql.TypeFloat,
   137  			flen:      7,
   138  			decimal:   3,
   139  			flag:      0,
   140  			expected:  `{"ColumnName":"","ColumnType":"FLOAT","ColumnPrecision":"7","ColumnScale":"3"}`,
   141  		},
   142  		{
   143  			name:      "double(12,3)",
   144  			filedType: mysql.TypeDouble,
   145  			flen:      12,
   146  			decimal:   3,
   147  			flag:      0,
   148  			expected:  `{"ColumnName":"","ColumnType":"DOUBLE","ColumnPrecision":"12","ColumnScale":"3"}`,
   149  		},
   150  		{
   151  			name:      "double",
   152  			filedType: mysql.TypeDouble,
   153  			flen:      12,
   154  			decimal:   -1,
   155  			flag:      0,
   156  			expected:  `{"ColumnName":"","ColumnType":"DOUBLE","ColumnPrecision":"12"}`,
   157  		},
   158  		{
   159  			name:      "double",
   160  			filedType: mysql.TypeDouble,
   161  			flen:      5,
   162  			decimal:   -1,
   163  			flag:      0,
   164  			expected:  `{"ColumnName":"","ColumnType":"DOUBLE","ColumnPrecision":"5"}`,
   165  		},
   166  		{
   167  			name:      "double(7,3)",
   168  			filedType: mysql.TypeDouble,
   169  			flen:      7,
   170  			decimal:   3,
   171  			flag:      0,
   172  			expected:  `{"ColumnName":"","ColumnType":"DOUBLE","ColumnPrecision":"7","ColumnScale":"3"}`,
   173  		},
   174  		{
   175  			name:      "tinyint(5)",
   176  			filedType: mysql.TypeTiny,
   177  			flen:      5,
   178  			decimal:   math.MinInt,
   179  			flag:      0,
   180  			expected:  `{"ColumnName":"","ColumnType":"TINYINT","ColumnPrecision":"5"}`,
   181  		},
   182  		{
   183  			name:      "smallint(5)",
   184  			filedType: mysql.TypeShort,
   185  			flen:      5,
   186  			decimal:   math.MinInt,
   187  			flag:      0,
   188  			expected:  `{"ColumnName":"","ColumnType":"SMALLINT","ColumnPrecision":"5"}`,
   189  		},
   190  		{
   191  			name:      "mediumint(10)",
   192  			filedType: mysql.TypeInt24,
   193  			flen:      10,
   194  			decimal:   math.MinInt,
   195  			flag:      0,
   196  			expected:  `{"ColumnName":"","ColumnType":"MEDIUMINT","ColumnPrecision":"10"}`,
   197  		},
   198  		{
   199  			name:      "int(11)",
   200  			filedType: mysql.TypeLong,
   201  			flen:      math.MinInt,
   202  			decimal:   math.MinInt,
   203  			flag:      mysql.PriKeyFlag,
   204  			expected:  `{"ColumnIsPk":"true", "ColumnName":"", "ColumnPrecision":"11", "ColumnType":"INT"}`,
   205  		},
   206  		{
   207  			name:      "bigint(20)",
   208  			filedType: mysql.TypeLonglong,
   209  			flen:      20,
   210  			decimal:   math.MinInt,
   211  			flag:      0,
   212  			expected:  `{"ColumnName":"","ColumnType":"BIGINT","ColumnPrecision":"20"}`,
   213  		},
   214  		{
   215  			name:      "bit(5)",
   216  			filedType: mysql.TypeBit,
   217  			flen:      5,
   218  			decimal:   math.MinInt,
   219  			flag:      0,
   220  			expected:  `{"ColumnName":"","ColumnType":"BIT","ColumnPrecision":"5"}`,
   221  		},
   222  		{
   223  			name:      "varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin",
   224  			filedType: mysql.TypeVarchar,
   225  			flen:      128,
   226  			decimal:   math.MinInt,
   227  			flag:      0,
   228  			expected:  `{"ColumnName":"","ColumnType":"VARCHAR","ColumnPrecision":"128"}`,
   229  		},
   230  		{
   231  			name:      "char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin",
   232  			filedType: mysql.TypeString,
   233  			flen:      32,
   234  			decimal:   math.MinInt,
   235  			flag:      0,
   236  			expected:  `{"ColumnName":"","ColumnType":"CHAR","ColumnPrecision":"32"}`,
   237  		},
   238  		{
   239  			name:      "var_string(64)",
   240  			filedType: mysql.TypeVarString,
   241  			flen:      64,
   242  			decimal:   math.MinInt,
   243  			flag:      0,
   244  			expected:  `{"ColumnName":"","ColumnType":"VAR_STRING","ColumnPrecision":"64"}`,
   245  		},
   246  		{
   247  			name:      "blob",
   248  			filedType: mysql.TypeBlob,
   249  			flen:      100,
   250  			decimal:   math.MinInt,
   251  			flag:      0,
   252  			expected:  `{"ColumnName":"","ColumnType":"BLOB","ColumnPrecision":"100"}`,
   253  		},
   254  		{
   255  			name:      "text",
   256  			filedType: mysql.TypeBlob,
   257  			flen:      100,
   258  			decimal:   math.MinInt,
   259  			flag:      0,
   260  			charset:   charset.CharsetUTF8MB4,
   261  			expected:  `{"ColumnName":"","ColumnType":"TEXT","ColumnPrecision":"100"}`,
   262  		},
   263  		{
   264  			name:      "tinyblob",
   265  			filedType: mysql.TypeTinyBlob,
   266  			flen:      120,
   267  			decimal:   math.MinInt,
   268  			flag:      0,
   269  			expected:  `{"ColumnName":"","ColumnType":"TINYBLOB","ColumnPrecision":"120"}`,
   270  		},
   271  		{
   272  			name:      "mediumblob",
   273  			filedType: mysql.TypeMediumBlob,
   274  			flen:      100,
   275  			decimal:   math.MinInt,
   276  			flag:      0,
   277  			expected:  `{"ColumnName":"","ColumnType":"MEDIUMBLOB","ColumnPrecision":"100"}`,
   278  		},
   279  		{
   280  			name:      "longblob",
   281  			filedType: mysql.TypeLongBlob,
   282  			flen:      5,
   283  			decimal:   math.MinInt,
   284  			flag:      0,
   285  			expected:  `{"ColumnName":"","ColumnType":"LONGBLOB","ColumnPrecision":"5"}`,
   286  		},
   287  		{
   288  			name:      "enum",
   289  			filedType: mysql.TypeEnum,
   290  			expected:  `{"ColumnName":"","ColumnType":"ENUM"}`,
   291  		},
   292  		{
   293  			name:      "set",
   294  			filedType: mysql.TypeSet,
   295  			expected:  `{"ColumnName":"","ColumnType":"SET"}`,
   296  		},
   297  		{
   298  			name:      "timestamp(2)",
   299  			filedType: mysql.TypeTimestamp,
   300  			flen:      8,
   301  			decimal:   2,
   302  			expected:  `{"ColumnName":"","ColumnType":"TIMESTAMP","ColumnScale":"2"}`,
   303  		},
   304  		{
   305  			name:      "timestamp",
   306  			filedType: mysql.TypeTimestamp,
   307  			flen:      8,
   308  			decimal:   0,
   309  			expected:  `{"ColumnName":"","ColumnType":"TIMESTAMP"}`,
   310  		},
   311  		{
   312  			name:      "datetime(2)",
   313  			filedType: mysql.TypeDatetime,
   314  			flen:      8,
   315  			decimal:   2,
   316  			expected:  `{"ColumnName":"","ColumnType":"DATETIME","ColumnScale":"2"}`,
   317  		},
   318  		{
   319  			name:      "datetime",
   320  			filedType: mysql.TypeDatetime,
   321  			flen:      8,
   322  			decimal:   0,
   323  			expected:  `{"ColumnName":"","ColumnType":"DATETIME"}`,
   324  		},
   325  		{
   326  			name:      "date",
   327  			filedType: mysql.TypeDate,
   328  			flen:      8,
   329  			decimal:   2,
   330  			expected:  `{"ColumnName":"","ColumnType":"DATE"}`,
   331  		},
   332  		{
   333  			name:      "date",
   334  			filedType: mysql.TypeDate,
   335  			flen:      8,
   336  			decimal:   0,
   337  			expected:  `{"ColumnName":"","ColumnType":"DATE"}`,
   338  		},
   339  		{
   340  			name:      "year(4)",
   341  			filedType: mysql.TypeYear,
   342  			flen:      4,
   343  			decimal:   0,
   344  			expected:  `{"ColumnName":"","ColumnType":"YEAR","ColumnPrecision":"4"}`,
   345  		},
   346  		{
   347  			name:      "year(2)",
   348  			filedType: mysql.TypeYear,
   349  			flen:      2,
   350  			decimal:   2,
   351  			expected:  `{"ColumnName":"","ColumnType":"YEAR","ColumnPrecision":"2"}`,
   352  		},
   353  	}
   354  
   355  	for _, tc := range testCases {
   356  		ft := types.NewFieldType(tc.filedType)
   357  		if tc.flen != math.MinInt {
   358  			ft.SetFlen(tc.flen)
   359  		}
   360  		if tc.decimal != math.MinInt {
   361  			ft.SetDecimal(tc.decimal)
   362  		}
   363  		if tc.flag != 0 {
   364  			ft.SetFlag(tc.flag)
   365  		}
   366  		if len(tc.charset) != 0 {
   367  			ft.SetCharset(tc.charset)
   368  		}
   369  		col := &timodel.ColumnInfo{FieldType: *ft}
   370  		var tableCol TableCol
   371  		tableCol.FromTiColumnInfo(col, false)
   372  		encodedCol, err := json.Marshal(tableCol)
   373  		require.Nil(t, err, tc.name)
   374  		require.JSONEq(t, tc.expected, string(encodedCol), tc.name)
   375  
   376  		_, err = tableCol.ToTiColumnInfo(100)
   377  		require.NoError(t, err)
   378  	}
   379  }
   380  
   381  func TestTableDefinition(t *testing.T) {
   382  	t.Parallel()
   383  
   384  	def, tableInfo := generateTableDef()
   385  	encodedDef, err := json.MarshalIndent(def, "", "    ")
   386  	require.NoError(t, err)
   387  	require.JSONEq(t, `{
   388  		"Table": "table1",
   389  		"Schema": "schema1",
   390  		"Version": 1,
   391  		"TableVersion": 100,
   392  		"Query": "",
   393  		"Type": 0,
   394  		"TableColumns": [
   395  			{
   396  				"ColumnName": "Id",
   397  				"ColumnType": "INT",
   398  				"ColumnPrecision": "11",
   399  				"ColumnDefault":10,
   400  				"ColumnNullable": "false",
   401  				"ColumnIsPk": "true"
   402  			},
   403  			{
   404  				"ColumnName": "LastName",
   405  				"ColumnType": "VARCHAR",
   406  				"ColumnDefault":"Default LastName",
   407  				"ColumnPrecision": "128",
   408  				"ColumnNullable": "false"
   409  			},
   410  			{
   411  				"ColumnName": "FirstName",
   412  				"ColumnDefault":"Default FirstName",
   413  				"ColumnType": "VARCHAR",
   414  				"ColumnPrecision": "64"
   415  			},
   416  			{
   417  				"ColumnName": "Birthday",
   418  				"ColumnDefault":1.2345678e+07,
   419  				"ColumnType": "DATETIME"
   420  			}
   421  		],
   422  		"TableColumnsTotal": 4
   423  	}`, string(encodedDef))
   424  
   425  	def = TableDefinition{}
   426  	event := &model.DDLEvent{
   427  		CommitTs:  tableInfo.Version,
   428  		Type:      timodel.ActionAddColumn,
   429  		Query:     "alter table schema1.table1 add Birthday date",
   430  		TableInfo: tableInfo,
   431  	}
   432  	def.FromDDLEvent(event, false)
   433  	encodedDef, err = json.MarshalIndent(def, "", "    ")
   434  	require.NoError(t, err)
   435  	require.JSONEq(t, `{
   436  		"Table": "table1",
   437  		"Schema": "schema1",
   438  		"Version": 1,
   439  		"TableVersion": 100,
   440  		"Query": "alter table schema1.table1 add Birthday date",
   441  		"Type": 5,
   442  		"TableColumns": [
   443  			{
   444  				"ColumnName": "Id",
   445  				"ColumnType": "INT",
   446  				"ColumnPrecision": "11",
   447  				"ColumnDefault":10,
   448  				"ColumnNullable": "false",
   449  				"ColumnIsPk": "true"
   450  			},
   451  			{
   452  				"ColumnName": "LastName",
   453  				"ColumnType": "VARCHAR",
   454  				"ColumnDefault":"Default LastName",
   455  				"ColumnPrecision": "128",
   456  				"ColumnNullable": "false"
   457  			},
   458  			{
   459  				"ColumnName": "FirstName",
   460  				"ColumnDefault":"Default FirstName",
   461  				"ColumnType": "VARCHAR",
   462  				"ColumnPrecision": "64"
   463  			},
   464  			{
   465  				"ColumnName": "Birthday",
   466  				"ColumnDefault":1.2345678e+07,
   467  				"ColumnType": "DATETIME"
   468  			}
   469  		],
   470  		"TableColumnsTotal": 4
   471  	}`, string(encodedDef))
   472  
   473  	tableInfo, err = def.ToTableInfo()
   474  	require.NoError(t, err)
   475  	require.Len(t, tableInfo.Columns, 4)
   476  
   477  	event, err = def.ToDDLEvent()
   478  	require.NoError(t, err)
   479  	require.Equal(t, timodel.ActionAddColumn, event.Type)
   480  	require.Equal(t, uint64(100), event.CommitTs)
   481  }
   482  
   483  func TestTableDefinitionGenFilePath(t *testing.T) {
   484  	t.Parallel()
   485  
   486  	schemaDef := &TableDefinition{
   487  		Schema:       "schema1",
   488  		Version:      defaultTableDefinitionVersion,
   489  		TableVersion: 100,
   490  	}
   491  	schemaPath, err := schemaDef.GenerateSchemaFilePath()
   492  	require.NoError(t, err)
   493  	require.Equal(t, "schema1/meta/schema_100_3233644819.json", schemaPath)
   494  
   495  	def, _ := generateTableDef()
   496  	tablePath, err := def.GenerateSchemaFilePath()
   497  	require.NoError(t, err)
   498  	require.Equal(t, "schema1/table1/meta/schema_100_3752767265.json", tablePath)
   499  }
   500  
   501  func TestTableDefinitionSum32(t *testing.T) {
   502  	t.Parallel()
   503  
   504  	def, _ := generateTableDef()
   505  	checksum1, err := def.Sum32(nil)
   506  	require.NoError(t, err)
   507  	checksum2, err := def.Sum32(nil)
   508  	require.NoError(t, err)
   509  	require.Equal(t, checksum1, checksum2)
   510  
   511  	n := len(def.Columns)
   512  	newCol := make([]TableCol, n)
   513  	copy(newCol, def.Columns)
   514  	newDef := def
   515  	newDef.Columns = newCol
   516  
   517  	for i := 0; i < n; i++ {
   518  		target := rand.Intn(n)
   519  		newDef.Columns[i], newDef.Columns[target] = newDef.Columns[target], newDef.Columns[i]
   520  		newChecksum, err := newDef.Sum32(nil)
   521  		require.NoError(t, err)
   522  		require.Equal(t, checksum1, newChecksum)
   523  	}
   524  }