Debian Codegraph @

mysql-test/suite/parts/inc/partition_methods2.inc

raw
1 ################################################################################
2 # inc/partition_methods2.inc #
3 # #
4 # Purpose: #
5 # Create and check partitioned tables #
6 # The partitioning function uses the columns f_int1 and f_int2 #
7 # For all partitioning methods #
8 # PARTITION BY HASH/KEY/LIST/RANGE #
9 # PARTITION BY RANGE/LIST ... SUBPARTITION BY HASH/KEY ... #
10 # do #
11 # 1. Create the partitioned table #
12 # 2 Insert the content of the table t0_template into t1 #
13 # 3. Execute inc/partition_check.inc #
14 # 4. Drop the table t1 #
15 # done #
16 # #
17 # The parameter #
18 # $unique -- PRIMARY KEY or UNIQUE INDEXes to be created within the #
19 # CREATE TABLE STATEMENT #
20 # has to be set before sourcing this routine. #
21 # Example: #
22 # let $unique= , UNIQUE INDEX uidx1 (f_int1); #
23 # inc/partition_methods2.inc #
24 # #
25 # Attention: The routine inc/partition_methods1.inc is very similar #
26 # to this one. So if something has to be changed here it #
27 # might be necessary to do it also there #
28 # #
29 #------------------------------------------------------------------------------#
30 # Original Author: mleich #
31 # Original Date: 2006-03-05 #
32 # Change Author: #
33 # Change Date: #
34 # Change: #
35 ################################################################################
36
37 --disable_warnings
38 DROP TABLE IF EXISTS t1;
39 --enable_warnings
40
41 let $partitioning= ;
42 #----------- PARTITION BY HASH
43 if ($with_partitioning)
44 {
45 let $partitioning= PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2;
46 if ($with_directories)
47 {
48 let $partitioning=
49 PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2
50 (PARTITION p1
51 $data_directory
52 $index_directory,
53 PARTITION p2
54 $data_directory
55 $index_directory);
56 }
57 }
58 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
59 eval CREATE TABLE t1 (
60 $column_list
61 $unique
62 )
63 $partitioning;
64 eval $insert_all;
65 --source suite/parts/inc/partition_check.inc
66 DROP TABLE t1;
67
68 #----------- PARTITION BY KEY
69 if ($with_partitioning)
70 {
71 let $partitioning= PARTITION BY KEY(f_int1,f_int2) PARTITIONS 5;
72 if ($with_directories)
73 {
74 let $partitioning=
75 PARTITION BY KEY(f_int1,f_int2) PARTITIONS 5
76 (PARTITION p1
77 $data_directory
78 $index_directory,
79 PARTITION p2
80 $data_directory
81 $index_directory,
82 PARTITION p3
83 $data_directory
84 $index_directory,
85 PARTITION p4
86 $data_directory
87 $index_directory,
88 PARTITION p5
89 $data_directory
90 $index_directory);
91 }
92 }
93 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
94 eval CREATE TABLE t1 (
95 $column_list
96 $unique
97 )
98 $partitioning;
99 eval $insert_all;
100 --source suite/parts/inc/partition_check.inc
101 DROP TABLE t1;
102
103 #----------- PARTITION BY LIST
104 if ($with_partitioning)
105 {
106 let $partitioning= PARTITION BY LIST(MOD(f_int1 + f_int2,4))
107 (PARTITION part_3 VALUES IN (-3),
108 PARTITION part_2 VALUES IN (-2),
109 PARTITION part_1 VALUES IN (-1),
110 PARTITION part_N VALUES IN (NULL),
111 PARTITION part0 VALUES IN (0),
112 PARTITION part1 VALUES IN (1),
113 PARTITION part2 VALUES IN (2),
114 PARTITION part3 VALUES IN (3));
115 if ($with_directories)
116 {
117 let $partitioning=
118 PARTITION BY LIST(MOD(f_int1 + f_int2,4))
119 (PARTITION part_3 VALUES IN (-3)
120 $data_directory $index_directory,
121 PARTITION part_2 VALUES IN (-2)
122 $data_directory $index_directory,
123 PARTITION part_1 VALUES IN (-1)
124 $data_directory $index_directory,
125 PARTITION part_N VALUES IN (NULL)
126 $data_directory $index_directory,
127 PARTITION part0 VALUES IN (0)
128 $data_directory $index_directory,
129 PARTITION part1 VALUES IN (1)
130 $data_directory $index_directory,
131 PARTITION part2 VALUES IN (2)
132 $data_directory $index_directory,
133 PARTITION part3 VALUES IN (3)
134 $data_directory $index_directory);
135 }
136 }
137 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
138 eval CREATE TABLE t1 (
139 $column_list
140 $unique
141 )
142 $partitioning;
143 eval $insert_all;
144 --source suite/parts/inc/partition_check.inc
145 DROP TABLE t1;
146
147 #----------- PARTITION BY RANGE
148 if ($with_partitioning)
149 {
150 let $partitioning= PARTITION BY RANGE((f_int1 + f_int2) DIV 2)
151 (PARTITION parta VALUES LESS THAN (0),
152 PARTITION partb VALUES LESS THAN ($max_row_div4),
153 PARTITION partc VALUES LESS THAN ($max_row_div2),
154 PARTITION partd VALUES LESS THAN ($max_row_div2 + $max_row_div4),
155 PARTITION parte VALUES LESS THAN ($max_row),
156 PARTITION partf VALUES LESS THAN $MAX_VALUE);
157 if ($with_directories)
158 {
159 let $partitioning= PARTITION BY RANGE((f_int1 + f_int2) DIV 2)
160 (PARTITION parta VALUES LESS THAN (0)
161 $data_directory
162 $index_directory,
163 PARTITION partb VALUES LESS THAN ($max_row_div4)
164 $data_directory
165 $index_directory,
166 PARTITION partc VALUES LESS THAN ($max_row_div2)
167 $data_directory
168 $index_directory,
169 PARTITION partd VALUES LESS THAN ($max_row_div2 + $max_row_div4)
170 $data_directory
171 $index_directory,
172 PARTITION parte VALUES LESS THAN ($max_row)
173 $data_directory
174 $index_directory,
175 PARTITION partf VALUES LESS THAN $MAX_VALUE
176 $data_directory
177 $index_directory);
178 }
179 }
180 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
181 eval CREATE TABLE t1 (
182 $column_list
183 $unique
184 )
185 $partitioning;
186 eval $insert_all;
187 --source suite/parts/inc/partition_check.inc
188 DROP TABLE t1;
189
190 #----------- PARTITION BY RANGE -- SUBPARTITION BY HASH
191 if ($with_partitioning)
192 {
193 let $partitioning=
194 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int2) SUBPARTITIONS 2
195 (PARTITION parta VALUES LESS THAN (0),
196 PARTITION partb VALUES LESS THAN ($max_row_div4),
197 PARTITION partc VALUES LESS THAN ($max_row_div2),
198 PARTITION partd VALUES LESS THAN $MAX_VALUE);
199 if ($with_directories)
200 {
201 let $partitioning=
202 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int2) SUBPARTITIONS 2
203 (PARTITION parta VALUES LESS THAN (0)
204 $data_directory
205 $index_directory,
206 PARTITION partb VALUES LESS THAN ($max_row_div4)
207 $data_directory
208 $index_directory,
209 PARTITION partc VALUES LESS THAN ($max_row_div2)
210 $data_directory
211 $index_directory,
212 PARTITION partd VALUES LESS THAN $MAX_VALUE
213 $data_directory
214 $index_directory);
215 }
216 }
217 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
218 eval CREATE TABLE t1 (
219 $column_list
220 $unique
221 )
222 $partitioning;
223 eval $insert_all;
224 --source suite/parts/inc/partition_check.inc
225 DROP TABLE t1;
226
227 #----------- PARTITION BY RANGE -- SUBPARTITION BY KEY
228 if ($with_partitioning)
229 {
230 let $partitioning= PARTITION BY RANGE(f_int1) SUBPARTITION BY KEY(f_int2)
231 (PARTITION part1 VALUES LESS THAN (0)
232 (SUBPARTITION subpart11, SUBPARTITION subpart12),
233 PARTITION part2 VALUES LESS THAN ($max_row_div4)
234 (SUBPARTITION subpart21, SUBPARTITION subpart22),
235 PARTITION part3 VALUES LESS THAN ($max_row_div2)
236 (SUBPARTITION subpart31, SUBPARTITION subpart32),
237 PARTITION part4 VALUES LESS THAN $MAX_VALUE
238 (SUBPARTITION subpart41, SUBPARTITION subpart42));
239 if ($with_directories)
240 {
241 let $partitioning= PARTITION BY RANGE(f_int1) SUBPARTITION BY KEY(f_int2)
242 (PARTITION part1 VALUES LESS THAN (0)
243 (SUBPARTITION subpart11 $data_directory $index_directory,
244 SUBPARTITION subpart12 $data_directory $index_directory),
245 PARTITION part2 VALUES LESS THAN ($max_row_div4)
246 (SUBPARTITION subpart21 $data_directory $index_directory,
247 SUBPARTITION subpart22 $data_directory $index_directory),
248 PARTITION part3 VALUES LESS THAN ($max_row_div2)
249 (SUBPARTITION subpart31 $data_directory $index_directory,
250 SUBPARTITION subpart32 $data_directory $index_directory),
251 PARTITION part4 VALUES LESS THAN $MAX_VALUE
252 (SUBPARTITION subpart41 $data_directory $index_directory,
253 SUBPARTITION subpart42 $data_directory $index_directory));
254 }
255 }
256 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
257 eval CREATE TABLE t1 (
258 $column_list
259 $unique
260 )
261 $partitioning;
262 eval $insert_all;
263 --source suite/parts/inc/partition_check.inc
264 DROP TABLE t1;
265
266 #----------- PARTITION BY LIST -- SUBPARTITION BY HASH
267 if ($with_partitioning)
268 {
269 let $partitioning= PARTITION BY LIST(ABS(MOD(f_int1,3))) SUBPARTITION BY HASH(f_int2 + 1)
270 (PARTITION part1 VALUES IN (0)
271 (SUBPARTITION sp11, SUBPARTITION sp12),
272 PARTITION part2 VALUES IN (1)
273 (SUBPARTITION sp21, SUBPARTITION sp22),
274 PARTITION part3 VALUES IN (2)
275 (SUBPARTITION sp31, SUBPARTITION sp32),
276 PARTITION part4 VALUES IN (NULL)
277 (SUBPARTITION sp41, SUBPARTITION sp42));
278 if ($with_directories)
279 {
280 let $partitioning=
281 PARTITION BY LIST(ABS(MOD(f_int1,3))) SUBPARTITION BY HASH(f_int2 + 1)
282 (PARTITION part1 VALUES IN (0)
283 $data_directory
284 $index_directory
285 (SUBPARTITION sp11
286 $data_directory
287 $index_directory,
288 SUBPARTITION sp12
289 $data_directory
290 $index_directory),
291 PARTITION part2 VALUES IN (1)
292 $data_directory
293 $index_directory
294 (SUBPARTITION sp21
295 $data_directory
296 $index_directory,
297 SUBPARTITION sp22
298 $data_directory
299 $index_directory),
300 PARTITION part3 VALUES IN (2)
301 $data_directory
302 $index_directory
303 (SUBPARTITION sp31,
304 SUBPARTITION sp32),
305 PARTITION part4 VALUES IN (NULL)
306 $data_directory
307 $index_directory
308 (SUBPARTITION sp41
309 $data_directory
310 $index_directory,
311 SUBPARTITION sp42
312 $data_directory
313 $index_directory));
314 }
315 }
316 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
317 eval CREATE TABLE t1 (
318 $column_list
319 $unique
320 )
321 $partitioning;
322 eval $insert_all;
323 --source suite/parts/inc/partition_check.inc
324 DROP TABLE t1;
325
326 #----------- PARTITION BY LIST -- SUBPARTITION BY KEY
327 if ($with_partitioning)
328 {
329 let $partitioning=
330 PARTITION BY LIST(ABS(MOD(f_int1,2)))
331 SUBPARTITION BY KEY(f_int2) SUBPARTITIONS $sub_part_no
332 (PARTITION part1 VALUES IN (0),
333 PARTITION part2 VALUES IN (1),
334 PARTITION part3 VALUES IN (NULL));
335 if ($with_directories)
336 {
337 let $partitioning=
338 PARTITION BY LIST(ABS(MOD(f_int1,2)))
339 SUBPARTITION BY KEY(f_int2) SUBPARTITIONS $sub_part_no
340 (PARTITION part1 VALUES IN (0)
341 $data_directory
342 $index_directory,
343 PARTITION part2 VALUES IN (1)
344 $data_directory
345 $index_directory,
346 PARTITION part3 VALUES IN (NULL)
347 $data_directory
348 $index_directory);
349 }
350 }
351 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
352 eval CREATE TABLE t1 (
353 $column_list
354 $unique
355 )
356 $partitioning;
357 eval $insert_all;
358 --source suite/parts/inc/partition_check.inc
359 DROP TABLE t1;