-
Notifications
You must be signed in to change notification settings - Fork 4k
/
Copy pathpartition_alter1_2.inc
175 lines (165 loc) · 8.1 KB
/
partition_alter1_2.inc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
################################################################################
# inc/partition_alter1_2.inc #
# #
# Purpose: #
# ADD/DROP PRIMARY KEYs and/or UNIQUE INDEXes tests on partitioned tables #
# This routine is only useful for the partition_<feature>_<engine> tests. #
# #
#------------------------------------------------------------------------------#
# Original Author: mleich #
# Original Date: 2006-03-05 #
# Change Author: debanerj #
# Change Date: 2015-02-18 #
# Change: Added tests for alter table drop + add primary/unique key #
################################################################################
#
--echo
--echo #========================================================================
--echo # 2 DROP PRIMARY KEY or UNIQUE INDEX
--echo #========================================================================
# Rule: The table must have a PRIMARY KEY or UNIQUE INDEX.
# ---> $unique must not be empty
# ---> The PRIMARY KEY or UNIQUE INDEX to be dropped must contain
# the columns used for partitioning.
--echo #------------------------------------------------------------------------
--echo # 2.1 Partitioning function contains one column(f_int1)
--echo #------------------------------------------------------------------------
# Rule: Only f_int1 is used within the partitioning function
# ---> inc/partition_alter_11.inc
# The value of the following test is maybe covered by 2.1.5.
if ($more_pk_ui_tests)
{
if ($do_pk_tests)
{
--echo # 2.1.1 DROP PRIMARY KEY consisting of one column
let $unique= , PRIMARY KEY(f_int1);
let $alter= ALTER TABLE t1 DROP PRIMARY KEY;
--source suite/parts/inc/partition_alter_11.inc
}
#
--echo # 2.1.2 DROP UNIQUE INDEX consisting of one column
let $unique= , UNIQUE INDEX uidx1 (f_int1);
let $alter= ALTER TABLE t1 DROP INDEX uidx1;
--source suite/parts/inc/partition_alter_11.inc
#
if ($do_pk_tests)
{
--echo # 2.1.3 DROP PRIMARY KEY consisting of two columns
let $alter= ALTER TABLE t1 DROP PRIMARY KEY;
let $unique= , PRIMARY KEY(f_int1,f_int2);
--source suite/parts/inc/partition_alter_11.inc
let $unique= , PRIMARY KEY(f_int2,f_int1);
--source suite/parts/inc/partition_alter_11.inc
}
#
--echo # 2.1.4 DROP UNIQUE INDEX consisting of two columns
let $alter= ALTER TABLE t1 DROP INDEX uidx1;
let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2);
--source suite/parts/inc/partition_alter_11.inc
let $unique= , UNIQUE INDEX uidx1 (f_int2,f_int1);
--source suite/parts/inc/partition_alter_11.inc
}
#
if ($do_pk_tests)
{
--echo # 2.1.5 DROP PRIMARY KEY + UNIQUE INDEX consisting of two columns
let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2), PRIMARY KEY(f_int2,f_int1);
let $alter= ALTER TABLE t1 DROP PRIMARY KEY, DROP INDEX uidx1;
--source suite/parts/inc/partition_alter_11.inc
let $unique= , UNIQUE INDEX uidx1 (f_int2,f_int1), PRIMARY KEY(f_int1,f_int2);
let $alter= ALTER TABLE t1 DROP PRIMARY KEY, DROP INDEX uidx1;
--source suite/parts/inc/partition_alter_11.inc
}
let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2), UNIQUE INDEX uidx2 (f_int2,f_int1);
let $alter= ALTER TABLE t1 DROP INDEX uidx1, DROP INDEX uidx2;
--source suite/parts/inc/partition_alter_11.inc
#
--echo #------------------------------------------------------------------------
--echo # 2.2 Partitioning function contains two columns (f_int1,f_int2)
--echo #------------------------------------------------------------------------
# Rule: f_int1 and f_int2 is used within the partitioning function
# ---> inc/partition_alter_13.inc
if ($do_pk_tests)
{
--echo # 2.2.1 DROP PRIMARY KEY consisting of two columns
let $alter= ALTER TABLE t1 DROP PRIMARY KEY;
let $unique= , PRIMARY KEY(f_int1,f_int2);
--source suite/parts/inc/partition_alter_13.inc
let $unique= , PRIMARY KEY(f_int2,f_int1);
--source suite/parts/inc/partition_alter_13.inc
}
#
--echo # 2.2.2 DROP UNIQUE INDEX consisting of two columns
let $alter= ALTER TABLE t1 DROP INDEX uidx1;
let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2);
--source suite/parts/inc/partition_alter_13.inc
let $unique= , UNIQUE INDEX uidx1 (f_int2,f_int1);
--source suite/parts/inc/partition_alter_13.inc
#
if ($do_pk_tests)
{
--echo # 2.2.3 DROP PRIMARY KEY + UNIQUE INDEX consisting of two columns
let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2), PRIMARY KEY(f_int2,f_int1);
let $alter= ALTER TABLE t1 DROP PRIMARY KEY, DROP INDEX uidx1;
--source suite/parts/inc/partition_alter_13.inc
let $unique= , UNIQUE INDEX uidx1 (f_int2,f_int1), PRIMARY KEY(f_int1,f_int2);
let $alter= ALTER TABLE t1 DROP PRIMARY KEY, DROP INDEX uidx1;
--source suite/parts/inc/partition_alter_13.inc
}
let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2), UNIQUE INDEX uidx2 (f_int2,f_int1);
let $alter= ALTER TABLE t1 DROP INDEX uidx1, DROP INDEX uidx2;
--source suite/parts/inc/partition_alter_13.inc
if ($do_pk_tests)
{
--echo
--echo #========================================================================
--echo # 3. ALTER TABLE "ALTER"(DROP + ADD) PRIMARY KEY
--echo # mleich: I think that an ALTER TABLE statement where a PRIMARY
--echo # KEY is dropped and recreated (with different layout) might
--echo # be of interest, if the tree containing the table data has
--echo # to be reorganized during this operation.
--echo #========================================================================
# Rule: The table must have a PRIMARY KEY or UNIQUE KEY
# ---> $unique must not be empty
# ---> The KEY to be dropped must contain the columns
# used for partitioning.
# Override column list to add NOT NULL constraint for partition by key()
# tests. May be it is better to add more columns in partition.pre but would
# impact a lot more tests.
let $column_list= f_int1 INTEGER NOT NULL,
f_int2 INTEGER NOT NULL,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000);
let $expect_inplace_error= 0;
--echo #------------------------------------------------------------------------
--echo # 3.1 Partitioning function uses the PRIMARY/UNIQUE KEY
--echo #------------------------------------------------------------------------
# Rule: PRIMARY/UNIQUE KEY is used in partitioning function
# ---> inc/partition_alter_12.inc
--echo # 3.1.1 DROP and CREATE PRIMARY KEY on different column
let $alter= ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(f_int2, f_int1);
let $unique= , PRIMARY KEY(f_int1);
--source suite/parts/inc/partition_alter_12.inc
--echo # 3.1.2 Negative test: Try DROP and CREATE PRIMARY KEY INPLACE
--echo # Expect error for INPLACE ALTER
let $expect_inplace_error= 1;
let $alter= ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(f_int2, f_int1),
ALGORITHM = INPLACE;
let $unique= , PRIMARY KEY(f_int1);
--source suite/parts/inc/partition_alter_12.inc
let $expect_inplace_error= 0;
--echo # 3.1.3 DROP and CREATE UNIQUE KEY on different column
let $alter= ALTER TABLE t1 DROP KEY ukey1, ADD UNIQUE KEY
ukey2(f_int2, f_int1);
let $unique= , UNIQUE KEY ukey1(f_int1);
--source suite/parts/inc/partition_alter_12.inc
--echo # 3.1.4 Negative test: Try DROP and CREATE UNIQUE KEY INPLACE
--echo # Expect error for INPLACE ALTER
let $expect_inplace_error= 1;
let $alter= ALTER TABLE t1 DROP KEY ukey1, ADD UNIQUE KEY
ukey2(f_int2, f_int1), ALGORITHM = INPLACE;
let $unique= , UNIQUE KEY ukey1(f_int1);
--source suite/parts/inc/partition_alter_12.inc
let $expect_inplace_error= 0;
}