github.com/hasnat/dolt/go@v0.0.0-20210628190320-9eb5d843fbb7/libraries/doltcore/doltdb/foreign_key_test.go (about)

     1  // Copyright 2020 Dolthub, 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  // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    12  // See the License for the specific language governing permissions and
    13  // limitations under the License.
    14  
    15  package doltdb_test
    16  
    17  import (
    18  	"context"
    19  	"testing"
    20  
    21  	"github.com/stretchr/testify/assert"
    22  	"github.com/stretchr/testify/require"
    23  
    24  	"github.com/dolthub/dolt/go/cmd/dolt/cli"
    25  	"github.com/dolthub/dolt/go/cmd/dolt/commands"
    26  	"github.com/dolthub/dolt/go/libraries/doltcore/doltdb"
    27  	"github.com/dolthub/dolt/go/libraries/doltcore/dtestutils"
    28  )
    29  
    30  func TestForeignKeys(t *testing.T) {
    31  	for _, test := range foreignKeyTests {
    32  		t.Run(test.name, func(t *testing.T) {
    33  			testForeignKeys(t, test)
    34  		})
    35  	}
    36  }
    37  
    38  func TestForeignKeyErrors(t *testing.T) {
    39  	cmds := []testCommand{
    40  		{commands.SqlCmd{}, []string{"-q", `CREATE TABLE test(pk BIGINT PRIMARY KEY, v1 BIGINT, INDEX (v1));`}},
    41  		{commands.SqlCmd{}, []string{"-q", `CREATE TABLE test2(pk BIGINT PRIMARY KEY, v1 BIGINT, INDEX (v1),` +
    42  			`CONSTRAINT child_fk FOREIGN KEY (v1) REFERENCES test(v1));`}},
    43  	}
    44  
    45  	ctx := context.Background()
    46  	dEnv := dtestutils.CreateTestEnv()
    47  
    48  	for _, c := range cmds {
    49  		exitCode := c.cmd.Exec(ctx, c.cmd.Name(), c.args, dEnv)
    50  		require.Equal(t, 0, exitCode)
    51  	}
    52  
    53  	exitCode := commands.SqlCmd{}.Exec(ctx, commands.SqlCmd{}.Name(), []string{"-q", `ALTER TABLE test MODIFY v1 INT;`}, dEnv)
    54  	require.Equal(t, 1, exitCode)
    55  	exitCode = commands.SqlCmd{}.Exec(ctx, commands.SqlCmd{}.Name(), []string{"-q", `ALTER TABLE test2 MODIFY v1 INT;`}, dEnv)
    56  	require.Equal(t, 1, exitCode)
    57  }
    58  
    59  type foreignKeyTest struct {
    60  	name  string
    61  	setup []testCommand
    62  	fks   []doltdb.ForeignKey
    63  }
    64  
    65  type testCommand struct {
    66  	cmd  cli.Command
    67  	args []string
    68  }
    69  
    70  var fkSetupCommon = []testCommand{
    71  	{commands.SqlCmd{}, []string{"-q", "create table parent (" +
    72  		"id int," +
    73  		"v1 int," +
    74  		"v2 int," +
    75  		"index v1_idx (v1)," +
    76  		"index v2_idx (v2)," +
    77  		"primary key(id));"}},
    78  	{commands.SqlCmd{}, []string{"-q", "create table child (" +
    79  		"id int, " +
    80  		"v1 int," +
    81  		"v2 int," +
    82  		"primary key(id));"}},
    83  }
    84  
    85  func testForeignKeys(t *testing.T, test foreignKeyTest) {
    86  	ctx := context.Background()
    87  	dEnv := dtestutils.CreateTestEnv()
    88  
    89  	for _, c := range fkSetupCommon {
    90  		exitCode := c.cmd.Exec(ctx, c.cmd.Name(), c.args, dEnv)
    91  		require.Equal(t, 0, exitCode)
    92  	}
    93  	for _, c := range test.setup {
    94  		exitCode := c.cmd.Exec(ctx, c.cmd.Name(), c.args, dEnv)
    95  		require.Equal(t, 0, exitCode)
    96  	}
    97  
    98  	root, err := dEnv.WorkingRoot(ctx)
    99  	require.NoError(t, err)
   100  	fkc, err := root.GetForeignKeyCollection(ctx)
   101  	require.NoError(t, err)
   102  
   103  	assert.Equal(t, test.fks, fkc.AllKeys())
   104  
   105  	for _, fk := range test.fks {
   106  		// verify parent index
   107  		pt, _, ok, err := root.GetTableInsensitive(ctx, fk.ReferencedTableName)
   108  		require.NoError(t, err)
   109  		require.True(t, ok)
   110  		ps, err := pt.GetSchema(ctx)
   111  		require.NoError(t, err)
   112  		pi, ok := ps.Indexes().GetByNameCaseInsensitive(fk.ReferencedTableIndex)
   113  		require.True(t, ok)
   114  		require.Equal(t, fk.ReferencedTableColumns, pi.IndexedColumnTags())
   115  
   116  		// verify child index
   117  		ct, _, ok, err := root.GetTableInsensitive(ctx, fk.TableName)
   118  		require.NoError(t, err)
   119  		require.True(t, ok)
   120  		cs, err := ct.GetSchema(ctx)
   121  		require.NoError(t, err)
   122  		ci, ok := cs.Indexes().GetByNameCaseInsensitive(fk.TableIndex)
   123  		require.True(t, ok)
   124  		require.Equal(t, fk.TableColumns, ci.IndexedColumnTags())
   125  	}
   126  }
   127  
   128  var foreignKeyTests = []foreignKeyTest{
   129  	{
   130  		name: "create foreign key",
   131  		setup: []testCommand{
   132  			{commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}},
   133  			{commands.SqlCmd{}, []string{"-q", `alter table child add 
   134  				constraint child_fk foreign key (v1) references parent(v1)`}},
   135  		},
   136  		fks: []doltdb.ForeignKey{
   137  			{
   138  				Name:                   "child_fk",
   139  				TableName:              "child",
   140  				TableIndex:             "v1_idx",
   141  				TableColumns:           []uint64{1215},
   142  				ReferencedTableName:    "parent",
   143  				ReferencedTableIndex:   "v1_idx",
   144  				ReferencedTableColumns: []uint64{6269},
   145  			},
   146  		},
   147  	},
   148  	{
   149  		name: "create multi-column foreign key",
   150  		setup: []testCommand{
   151  			{commands.SqlCmd{}, []string{"-q", `alter table parent add index v1v2_idx (v1, v2)`}},
   152  			{commands.SqlCmd{}, []string{"-q", `alter table child add index v1v2_idx (v1, v2)`}},
   153  			{commands.SqlCmd{}, []string{"-q", `alter table child add 
   154  				constraint multi_col foreign key (v1, v2) references parent(v1, v2)`}},
   155  		},
   156  		fks: []doltdb.ForeignKey{
   157  			{
   158  				Name:                   "multi_col",
   159  				TableName:              "child",
   160  				TableIndex:             "v1v2_idx",
   161  				TableColumns:           []uint64{1215, 8734},
   162  				ReferencedTableName:    "parent",
   163  				ReferencedTableIndex:   "v1v2_idx",
   164  				ReferencedTableColumns: []uint64{6269, 7947},
   165  			},
   166  		},
   167  	},
   168  	{
   169  		name: "create multiple foreign keys",
   170  		setup: []testCommand{
   171  			{commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}},
   172  			{commands.SqlCmd{}, []string{"-q", `alter table child add index v2_idx (v2)`}},
   173  			{commands.SqlCmd{}, []string{"-q", `alter table child 
   174  				add constraint fk1 foreign key (v1) references parent(v1)`}},
   175  			{commands.SqlCmd{}, []string{"-q", `alter table child 
   176  				add constraint fk2 foreign key (v2) references parent(v2)`}},
   177  		},
   178  		fks: []doltdb.ForeignKey{
   179  			{
   180  				Name:                   "fk1",
   181  				TableName:              "child",
   182  				TableIndex:             "v1_idx",
   183  				TableColumns:           []uint64{1215},
   184  				ReferencedTableName:    "parent",
   185  				ReferencedTableIndex:   "v1_idx",
   186  				ReferencedTableColumns: []uint64{6269},
   187  			},
   188  			{
   189  				Name:                   "fk2",
   190  				TableName:              "child",
   191  				TableIndex:             "v2_idx",
   192  				TableColumns:           []uint64{8734},
   193  				ReferencedTableName:    "parent",
   194  				ReferencedTableIndex:   "v2_idx",
   195  				ReferencedTableColumns: []uint64{7947},
   196  			},
   197  		},
   198  	},
   199  	{
   200  		name: "create table with foreign key",
   201  		setup: []testCommand{
   202  			{commands.SqlCmd{}, []string{"-q", `create table new_table (
   203  				id int,
   204  				v1 int,
   205  				constraint new_fk foreign key (v1) references parent(v1),
   206  				primary key(id));`}},
   207  		},
   208  		fks: []doltdb.ForeignKey{
   209  			{
   210  				Name:      "new_fk",
   211  				TableName: "new_table",
   212  				// unnamed indexes take the column name
   213  				TableIndex:             "v1",
   214  				TableColumns:           []uint64{7597},
   215  				ReferencedTableName:    "parent",
   216  				ReferencedTableIndex:   "v1_idx",
   217  				ReferencedTableColumns: []uint64{6269},
   218  			},
   219  		},
   220  	},
   221  	{
   222  		name: "create foreign keys with update or delete rules",
   223  		setup: []testCommand{
   224  			{commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}},
   225  			{commands.SqlCmd{}, []string{"-q", `alter table child add index v2_idx (v2)`}},
   226  			{commands.SqlCmd{}, []string{"-q", `alter table child 
   227  				add constraint fk1 foreign key (v1) references parent(v1) on update cascade`}},
   228  			{commands.SqlCmd{}, []string{"-q", `alter table child 
   229  				add constraint fk2 foreign key (v2) references parent(v2) on delete set null`}},
   230  		},
   231  		fks: []doltdb.ForeignKey{
   232  			{
   233  				Name:                   "fk1",
   234  				TableName:              "child",
   235  				TableIndex:             "v1_idx",
   236  				TableColumns:           []uint64{1215},
   237  				ReferencedTableName:    "parent",
   238  				ReferencedTableIndex:   "v1_idx",
   239  				ReferencedTableColumns: []uint64{6269},
   240  				OnUpdate:               doltdb.ForeignKeyReferenceOption_Cascade,
   241  			},
   242  			{
   243  				Name:                   "fk2",
   244  				TableName:              "child",
   245  				TableIndex:             "v2_idx",
   246  				TableColumns:           []uint64{8734},
   247  				ReferencedTableName:    "parent",
   248  				ReferencedTableIndex:   "v2_idx",
   249  				ReferencedTableColumns: []uint64{7947},
   250  				OnDelete:               doltdb.ForeignKeyReferenceOption_SetNull,
   251  			},
   252  		},
   253  	},
   254  	{
   255  		name: "create single foreign key with update and delete rules",
   256  		setup: []testCommand{
   257  			{commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}},
   258  			{commands.SqlCmd{}, []string{"-q", `alter table child 
   259  				add constraint child_fk foreign key (v1) references parent(v1) on update cascade on delete cascade`}},
   260  		},
   261  		fks: []doltdb.ForeignKey{
   262  			{
   263  				Name:                   "child_fk",
   264  				TableName:              "child",
   265  				TableIndex:             "v1_idx",
   266  				TableColumns:           []uint64{1215},
   267  				ReferencedTableName:    "parent",
   268  				ReferencedTableIndex:   "v1_idx",
   269  				ReferencedTableColumns: []uint64{6269},
   270  				OnUpdate:               doltdb.ForeignKeyReferenceOption_Cascade,
   271  				OnDelete:               doltdb.ForeignKeyReferenceOption_Cascade,
   272  			},
   273  		},
   274  	},
   275  	{
   276  		name: "create foreign keys with all update and delete rules",
   277  		setup: []testCommand{
   278  			{commands.SqlCmd{}, []string{"-q", "alter table parent add column v3 int;"}},
   279  			{commands.SqlCmd{}, []string{"-q", "alter table parent add column v4 int;"}},
   280  			{commands.SqlCmd{}, []string{"-q", "alter table parent add column v5 int;"}},
   281  			{commands.SqlCmd{}, []string{"-q", "alter table parent add index v3_idx (v3);"}},
   282  			{commands.SqlCmd{}, []string{"-q", "alter table parent add index v4_idx (v4);"}},
   283  			{commands.SqlCmd{}, []string{"-q", "alter table parent add index v5_idx (v5);"}},
   284  			{commands.SqlCmd{}, []string{"-q", `create table sibling (
   285  					id int,
   286  					v1 int,
   287  					v2 int,
   288  					v3 int,
   289  					v4 int,
   290  					v5 int,
   291  					constraint fk1 foreign key (v1) references parent(v1),
   292  					constraint fk2 foreign key (v2) references parent(v2) on delete restrict on update restrict,
   293  					constraint fk3 foreign key (v3) references parent(v3) on delete cascade on update cascade,
   294  					constraint fk4 foreign key (v4) references parent(v4) on delete set null on update set null,
   295  					constraint fk5 foreign key (v5) references parent(v5) on delete no action on update no action,
   296  					primary key (id));`}},
   297  		},
   298  		fks: []doltdb.ForeignKey{
   299  			{
   300  				Name:                   "fk1",
   301  				TableName:              "sibling",
   302  				TableIndex:             "v1",
   303  				TableColumns:           []uint64{16080},
   304  				ReferencedTableName:    "parent",
   305  				ReferencedTableIndex:   "v1_idx",
   306  				ReferencedTableColumns: []uint64{6269},
   307  			},
   308  			{
   309  				Name:                   "fk2",
   310  				TableName:              "sibling",
   311  				TableIndex:             "v2",
   312  				TableColumns:           []uint64{7576},
   313  				ReferencedTableName:    "parent",
   314  				ReferencedTableIndex:   "v2_idx",
   315  				ReferencedTableColumns: []uint64{7947},
   316  				OnUpdate:               doltdb.ForeignKeyReferenceOption_Restrict,
   317  				OnDelete:               doltdb.ForeignKeyReferenceOption_Restrict,
   318  			},
   319  			{
   320  				Name:                   "fk3",
   321  				TableName:              "sibling",
   322  				TableIndex:             "v3",
   323  				TableColumns:           []uint64{16245},
   324  				ReferencedTableName:    "parent",
   325  				ReferencedTableIndex:   "v3_idx",
   326  				ReferencedTableColumns: []uint64{5237},
   327  				OnUpdate:               doltdb.ForeignKeyReferenceOption_Cascade,
   328  				OnDelete:               doltdb.ForeignKeyReferenceOption_Cascade,
   329  			},
   330  			{
   331  				Name:                   "fk4",
   332  				TableName:              "sibling",
   333  				TableIndex:             "v4",
   334  				TableColumns:           []uint64{9036},
   335  				ReferencedTableName:    "parent",
   336  				ReferencedTableIndex:   "v4_idx",
   337  				ReferencedTableColumns: []uint64{14774},
   338  				OnUpdate:               doltdb.ForeignKeyReferenceOption_SetNull,
   339  				OnDelete:               doltdb.ForeignKeyReferenceOption_SetNull,
   340  			},
   341  			{
   342  				Name:                   "fk5",
   343  				TableName:              "sibling",
   344  				TableIndex:             "v5",
   345  				TableColumns:           []uint64{11586},
   346  				ReferencedTableName:    "parent",
   347  				ReferencedTableIndex:   "v5_idx",
   348  				ReferencedTableColumns: []uint64{8125},
   349  				OnUpdate:               doltdb.ForeignKeyReferenceOption_NoAction,
   350  				OnDelete:               doltdb.ForeignKeyReferenceOption_NoAction,
   351  			},
   352  		},
   353  	},
   354  	{
   355  		name: "create foreign key without preexisting child index",
   356  		setup: []testCommand{
   357  			{commands.SqlCmd{}, []string{"-q", `alter table child add constraint child_fk foreign key (v1) references parent(v1)`}},
   358  		},
   359  		fks: []doltdb.ForeignKey{
   360  			{
   361  				Name:      "child_fk",
   362  				TableName: "child",
   363  				// unnamed indexes take the column name
   364  				TableIndex:             "v1",
   365  				TableColumns:           []uint64{1215},
   366  				ReferencedTableName:    "parent",
   367  				ReferencedTableIndex:   "v1_idx",
   368  				ReferencedTableColumns: []uint64{6269},
   369  			},
   370  		},
   371  	},
   372  	{
   373  		name: "create unnamed foreign key",
   374  		setup: []testCommand{
   375  			{commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}},
   376  			{commands.SqlCmd{}, []string{"-q", `alter table child add foreign key (v1) references parent(v1)`}},
   377  		},
   378  		fks: []doltdb.ForeignKey{
   379  			{
   380  				Name:                   "19eof0mu",
   381  				TableName:              "child",
   382  				TableIndex:             "v1_idx",
   383  				TableColumns:           []uint64{1215},
   384  				ReferencedTableName:    "parent",
   385  				ReferencedTableIndex:   "v1_idx",
   386  				ReferencedTableColumns: []uint64{6269},
   387  			},
   388  		},
   389  	},
   390  	{
   391  		name: "create table with unnamed foreign key",
   392  		setup: []testCommand{
   393  			{commands.SqlCmd{}, []string{"-q", `create table new_table (
   394  				id int,
   395  				v1 int,
   396  				foreign key (v1) references parent(v1),
   397  				primary key(id));`}},
   398  		},
   399  		fks: []doltdb.ForeignKey{
   400  			{
   401  				Name:      "mv9a59oo",
   402  				TableName: "new_table",
   403  				// unnamed indexes take the column name
   404  				TableIndex:             "v1",
   405  				TableColumns:           []uint64{7597},
   406  				ReferencedTableName:    "parent",
   407  				ReferencedTableIndex:   "v1_idx",
   408  				ReferencedTableColumns: []uint64{6269},
   409  			},
   410  		},
   411  	},
   412  	{
   413  		name: "create unnamed multi-column foreign key",
   414  		setup: []testCommand{
   415  			{commands.SqlCmd{}, []string{"-q", `alter table parent add index v1v2_idx (v1, v2)`}},
   416  			{commands.SqlCmd{}, []string{"-q", `alter table child 
   417  				add index v1v2_idx (v1, v2)`}},
   418  			{commands.SqlCmd{}, []string{"-q", `alter table child 
   419  				add foreign key (v1, v2) references parent(v1, v2)`}},
   420  		},
   421  		fks: []doltdb.ForeignKey{
   422  			{
   423  				Name:                   "n4qun7ju",
   424  				TableName:              "child",
   425  				TableIndex:             "v1v2_idx",
   426  				TableColumns:           []uint64{1215, 8734},
   427  				ReferencedTableName:    "parent",
   428  				ReferencedTableIndex:   "v1v2_idx",
   429  				ReferencedTableColumns: []uint64{6269, 7947},
   430  			},
   431  		},
   432  	},
   433  	{
   434  		name: "create multiple unnamed foreign keys",
   435  		setup: []testCommand{
   436  			{commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}},
   437  			{commands.SqlCmd{}, []string{"-q", `alter table child add index v2_idx (v2)`}},
   438  			{commands.SqlCmd{}, []string{"-q", `alter table child 
   439  				add foreign key (v1) references parent(v1)`}},
   440  			{commands.SqlCmd{}, []string{"-q", `alter table child 
   441  				add foreign key (v2) references parent(v2)`}},
   442  		},
   443  		fks: []doltdb.ForeignKey{
   444  			{
   445  				Name:                   "19eof0mu",
   446  				TableName:              "child",
   447  				TableIndex:             "v1_idx",
   448  				TableColumns:           []uint64{1215},
   449  				ReferencedTableName:    "parent",
   450  				ReferencedTableIndex:   "v1_idx",
   451  				ReferencedTableColumns: []uint64{6269},
   452  			},
   453  			{
   454  				Name:                   "p79c8qtq",
   455  				TableName:              "child",
   456  				TableIndex:             "v2_idx",
   457  				TableColumns:           []uint64{8734},
   458  				ReferencedTableName:    "parent",
   459  				ReferencedTableIndex:   "v2_idx",
   460  				ReferencedTableColumns: []uint64{7947},
   461  			},
   462  		},
   463  	},
   464  	{
   465  		name: "create foreign key with pre-existing data",
   466  		setup: []testCommand{
   467  			{commands.SqlCmd{}, []string{"-q", `insert into parent (id,v1,v2) values 
   468  				(1,1,1),
   469  				(2,2,2);`}},
   470  			{commands.SqlCmd{}, []string{"-q", `insert into child (id,v1,v2) values 
   471  				(1,1,1),
   472  				(2,2,2),
   473  				(3,NULL,3);`}},
   474  			{commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}},
   475  			{commands.SqlCmd{}, []string{"-q", `alter table child 
   476  				add constraint fk1 foreign key (v1) references parent(v1)`}},
   477  		},
   478  		fks: []doltdb.ForeignKey{
   479  			{
   480  				Name:                   "fk1",
   481  				TableName:              "child",
   482  				TableIndex:             "v1_idx",
   483  				TableColumns:           []uint64{1215},
   484  				ReferencedTableName:    "parent",
   485  				ReferencedTableIndex:   "v1_idx",
   486  				ReferencedTableColumns: []uint64{6269},
   487  			},
   488  		},
   489  	},
   490  	{
   491  		name: "create multi-col foreign key with pre-existing data",
   492  		setup: []testCommand{
   493  			{commands.SqlCmd{}, []string{"-q", `insert into parent (id,v1,v2) values 
   494  				(1,1,1),
   495  				(2,2,NULL),
   496  				(3,NULL,3),
   497  				(4,NULL,NULL);`}},
   498  			{commands.SqlCmd{}, []string{"-q", `insert into child (id,v1,v2) values 
   499  				(1,1,1),
   500  				(2,2,NULL),
   501  				(3,NULL,3);`}},
   502  			{commands.SqlCmd{}, []string{"-q", `alter table parent add index v1v2 (v1,v2)`}},
   503  			{commands.SqlCmd{}, []string{"-q", `alter table child add index v1v2 (v1,v2)`}},
   504  			{commands.SqlCmd{}, []string{"-q", `alter table child 
   505  				add constraint fk1 foreign key (v1,v2) references parent(v1,v2)`}},
   506  		},
   507  		fks: []doltdb.ForeignKey{
   508  			{
   509  				Name:                   "fk1",
   510  				TableName:              "child",
   511  				TableIndex:             "v1v2",
   512  				TableColumns:           []uint64{1215, 8734},
   513  				ReferencedTableName:    "parent",
   514  				ReferencedTableIndex:   "v1v2",
   515  				ReferencedTableColumns: []uint64{6269, 7947},
   516  			},
   517  		},
   518  	},
   519  }