Debian Codegraph @

mysql-test/suite/test_services/t/test_table_access.test

raw
1 # Test the table_access_service
2
3 INSTALL COMPONENT 'file://component_test_table_access';
4
5 # INSERT
6
7 SELECT test_table_access_driver("unknown test");
8
9 SELECT test_table_access_driver("INSERT-CUSTOMER-STRESS-1");
10 SELECT test_table_access_driver("INSERT-CUSTOMER-STRESS-2");
11 SELECT test_table_access_driver("INSERT-CUSTOMER");
12
13 CREATE SCHEMA shop;
14
15 SELECT test_table_access_driver("INSERT-CUSTOMER");
16
17 CREATE TABLE shop.customer (x integer);
18
19 SELECT test_table_access_driver("INSERT-CUSTOMER-STRESS-3");
20 SELECT test_table_access_driver("INSERT-CUSTOMER");
21
22 DROP TABLE shop.customer;
23
24 CREATE TABLE shop.customer(x integer, y integer, z integer);
25
26 SELECT test_table_access_driver("INSERT-CUSTOMER");
27
28 DROP TABLE shop.customer;
29
30 CREATE TABLE shop.customer(
31 `ID` integer not null,
32 `NAME` varchar(50) not null,
33 `ADDRESS` varchar(255));
34
35 SELECT test_table_access_driver("INSERT-CUSTOMER-NO-COMMIT");
36
37 SELECT * from shop.customer;
38
39 SELECT test_table_access_driver("INSERT-CUSTOMER-ROLLBACK");
40
41 SELECT * from shop.customer;
42
43 SELECT test_table_access_driver("INSERT-CUSTOMER");
44
45 SELECT * from shop.customer;
46
47 # FETCH
48
49 SELECT test_table_access_driver("FETCH-ORDER");
50
51 CREATE TABLE `shop`.`order` (x integer);
52
53 SELECT test_table_access_driver("FETCH-ORDER");
54
55 DROP TABLE `shop`.`order`;
56
57 CREATE TABLE `shop`.`order` (
58 CUSTOMER_ID INTEGER not null,
59 ORDER_ID INTEGER not null,
60 ORDER_COMMENT VARCHAR(50),
61 DATE_CREATED DATE);
62
63 SELECT test_table_access_driver("FETCH-ORDER");
64
65 CREATE TABLE `shop`.`order_line` (y integer);
66
67 SELECT test_table_access_driver("FETCH-ORDER");
68
69 DROP TABLE `shop`.`order_line`;
70
71 CREATE TABLE `shop`.`order_line` (
72 ORDER_ID INTEGER not null,
73 LINE_NUM INTEGER not null,
74 ITEM_ID INTEGER not null,
75 UNIT_PRICE DECIMAL(5,2) not null,
76 QTY INTEGER not null);
77
78 SELECT test_table_access_driver("FETCH-ORDER");
79
80 ALTER TABLE `shop`.`order` ADD INDEX index_date_created(DATE_CREATED);
81
82 SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, IS_VISIBLE
83 FROM INFORMATION_SCHEMA.STATISTICS
84 WHERE TABLE_SCHEMA = 'shop'
85 ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;
86
87 SHOW CREATE TABLE `shop`.`order`;
88
89 SELECT test_table_access_driver("FETCH-ORDER");
90
91 ALTER TABLE `shop`.`order` ADD KEY(ORDER_ID);
92
93 SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, IS_VISIBLE
94 FROM INFORMATION_SCHEMA.STATISTICS
95 WHERE TABLE_SCHEMA = 'shop'
96 ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;
97
98 SELECT test_table_access_driver("FETCH-ORDER");
99
100 ALTER TABLE `shop`.`order` DROP KEY ORDER_ID;
101 ALTER TABLE `shop`.`order` ADD PRIMARY KEY(ORDER_ID);
102
103 SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, IS_VISIBLE
104 FROM INFORMATION_SCHEMA.STATISTICS
105 WHERE TABLE_SCHEMA = 'shop'
106 ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;
107
108 SELECT test_table_access_driver("FETCH-ORDER");
109
110 INSERT INTO `shop`.`order` VALUES(1, 1000, 'This is order 1000', '2023-04-01');
111 INSERT INTO `shop`.`order` VALUES(1, 1002, 'This is order 1002', '2048-04-01');
112
113 SELECT test_table_access_driver("FETCH-ORDER");
114
115 INSERT INTO `shop`.`order` VALUES(1, 1001, 'This is order 1001', '2020-12-31');
116
117 SELECT test_table_access_driver("FETCH-ORDER");
118
119 ALTER TABLE `shop`.`order_line` ADD PRIMARY KEY(ORDER_ID, LINE_NUM);
120
121 SELECT test_table_access_driver("FETCH-ORDER");
122
123 INSERT INTO `shop`.`order_line` VALUES(1000, 1, 888, 12.23, 10);
124 INSERT INTO `shop`.`order_line` VALUES(1000, 2, 777, 34.45, 20);
125 INSERT INTO `shop`.`order_line` VALUES(1000, 3, 666, 8.99, 5);
126 INSERT INTO `shop`.`order_line` VALUES(1002, 1, 555, 100, 1);
127 INSERT INTO `shop`.`order_line` VALUES(1002, 2, 444, 0.99, 1000);
128
129 SELECT test_table_access_driver("FETCH-ORDER");
130
131 INSERT INTO `shop`.`order_line` VALUES(1001, 1, 333, 12, 1);
132
133 SELECT test_table_access_driver("FETCH-ORDER");
134
135 INSERT INTO `shop`.`order_line` VALUES(1001, 2, 222, 24, 5);
136
137 SELECT test_table_access_driver("FETCH-ORDER");
138
139 # INDEX SCAN
140
141 SELECT test_table_access_driver("INDEX-SCAN");
142
143 CREATE TABLE `shop`.`warehouse` (BUILDING_ID integer);
144
145 SELECT test_table_access_driver("INDEX-SCAN");
146
147 DROP TABLE `shop`.`warehouse`;
148
149 CREATE TABLE `shop`.`warehouse` (
150 BUILDING_ID integer not null,
151 FLOOR_NUMBER integer not null,
152 ALLEY_NUMBER integer not null,
153 SHELVE_NUMBER integer not null,
154 CAPACITY integer not null,
155 DESCRIPTION VARCHAR(100));
156
157 SELECT test_table_access_driver("INDEX-SCAN");
158
159 ALTER TABLE `shop`.`warehouse` ADD INDEX some_index(DESCRIPTION);
160
161 SELECT test_table_access_driver("INDEX-SCAN");
162
163 ALTER TABLE `shop`.`warehouse` ADD INDEX `SHELVES`(SHELVE_NUMBER);
164
165 SELECT test_table_access_driver("INDEX-SCAN");
166
167 ALTER TABLE `shop`.`warehouse` DROP INDEX `SHELVES`;
168 ALTER TABLE `shop`.`warehouse` ADD INDEX `SHELVES`(
169 SHELVE_NUMBER, ALLEY_NUMBER, FLOOR_NUMBER, BUILDING_ID);
170
171 SELECT test_table_access_driver("INDEX-SCAN");
172
173 ALTER TABLE `shop`.`warehouse` DROP INDEX `SHELVES`;
174 ALTER TABLE `shop`.`warehouse` ADD INDEX `SHELVES`(
175 BUILDING_ID, ALLEY_NUMBER, FLOOR_NUMBER, SHELVE_NUMBER);
176
177 SELECT test_table_access_driver("INDEX-SCAN");
178
179 ALTER TABLE `shop`.`warehouse` DROP INDEX `SHELVES`;
180 ALTER TABLE `shop`.`warehouse` ADD INDEX `SHELVES`(
181 BUILDING_ID, FLOOR_NUMBER, ALLEY_NUMBER, SHELVE_NUMBER, DESCRIPTION);
182
183 SELECT test_table_access_driver("INDEX-SCAN");
184
185 ALTER TABLE `shop`.`warehouse` DROP INDEX `SHELVES`;
186 ALTER TABLE `shop`.`warehouse` ADD INDEX `SHELVES`(
187 BUILDING_ID, FLOOR_NUMBER, ALLEY_NUMBER, SHELVE_NUMBER);
188
189 SELECT test_table_access_driver("INDEX-SCAN");
190 SELECT test_table_access_driver("INDEX-FETCH-B");
191 SELECT test_table_access_driver("INDEX-FETCH-BF");
192 SELECT test_table_access_driver("INDEX-FETCH-BFA");
193 SELECT test_table_access_driver("INDEX-FETCH-BFAS");
194
195 INSERT INTO `shop`.`warehouse` VALUES(1007, 4, 3, 12, 50, "no comments");
196
197 SELECT test_table_access_driver("INDEX-SCAN");
198 SELECT test_table_access_driver("INDEX-FETCH-B");
199 SELECT test_table_access_driver("INDEX-FETCH-BF");
200 SELECT test_table_access_driver("INDEX-FETCH-BFA");
201 SELECT test_table_access_driver("INDEX-FETCH-BFAS");
202
203 INSERT INTO `shop`.`warehouse` VALUES(1007, 4, 3, 11, 150, "no comments");
204
205 SELECT test_table_access_driver("INDEX-SCAN");
206 SELECT test_table_access_driver("INDEX-FETCH-B");
207 SELECT test_table_access_driver("INDEX-FETCH-BF");
208 SELECT test_table_access_driver("INDEX-FETCH-BFA");
209 SELECT test_table_access_driver("INDEX-FETCH-BFAS");
210
211 INSERT INTO `shop`.`warehouse` VALUES(1003, 4, 3, 11, 250, "no comments");
212 INSERT INTO `shop`.`warehouse` VALUES(1005, 6, 3, 11, 6311, "no comments");
213 INSERT INTO `shop`.`warehouse` VALUES(1005, 4, 3, 11, 4311, "no comments");
214
215 SELECT test_table_access_driver("INDEX-SCAN");
216 SELECT test_table_access_driver("INDEX-FETCH-B");
217 SELECT test_table_access_driver("INDEX-FETCH-BF");
218 SELECT test_table_access_driver("INDEX-FETCH-BFA");
219 SELECT test_table_access_driver("INDEX-FETCH-BFAS");
220
221 INSERT INTO `shop`.`warehouse` VALUES(1003, 4, 3, 11, 250, "no comments");
222 INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 7, 18, 5718, "no comments");
223 INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 2, 19, 5219, "no comments");
224
225 SELECT test_table_access_driver("INDEX-SCAN");
226 SELECT test_table_access_driver("INDEX-FETCH-B");
227 SELECT test_table_access_driver("INDEX-FETCH-BF");
228 SELECT test_table_access_driver("INDEX-FETCH-BFA");
229 SELECT test_table_access_driver("INDEX-FETCH-BFAS");
230
231 INSERT INTO `shop`.`warehouse` VALUES(1002, 2, 2, 2, 20, "no comments");
232 INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 5, 33, 5533, "no comments");
233 INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 5, 22, 5522, "no comments");
234 INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 5, 11, 11, "no comments");
235
236 SELECT test_table_access_driver("INDEX-SCAN");
237 SELECT test_table_access_driver("INDEX-FETCH-B");
238 SELECT test_table_access_driver("INDEX-FETCH-BF");
239 SELECT test_table_access_driver("INDEX-FETCH-BFA");
240 SELECT test_table_access_driver("INDEX-FETCH-BFAS");
241
242 INSERT INTO `shop`.`warehouse` VALUES(1001, 5, 5, 5, 12, "no comments");
243 INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 5, 2, 552, "no comments");
244 INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 5, 7, 557, "no comments");
245 INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 5, 5, 5555, "no comments");
246
247 SELECT test_table_access_driver("INDEX-SCAN");
248 SELECT test_table_access_driver("INDEX-FETCH-B");
249 SELECT test_table_access_driver("INDEX-FETCH-BF");
250 SELECT test_table_access_driver("INDEX-FETCH-BFA");
251 SELECT test_table_access_driver("INDEX-FETCH-BFAS");
252
253 DROP TABLE shop.warehouse;
254 DROP TABLE shop.customer;
255 DROP SCHEMA shop;
256
257 UNINSTALL COMPONENT 'file://component_test_table_access';
258