Debian Codegraph @

mysql-test/suite/funcs_1/datadict/is_key_column_usage.inc

raw
1 # suite/funcs_1/datadict/is_key_column_usage.inc
2 #
3 # Check the layout of information_schema.key_column_usage and the impact of
4 # CREATE/ALTER/DROP TABLE/VIEW/SCHEMA/COLUMN ... on it.
5 #
6 # Note:
7 # This test is not intended
8 # - to show information about the all time existing tables
9 # within the databases information_schema and mysql
10 # - for checking storage engine properties
11 # Therefore please do not alter $engine_type.
12 #
13 # Author:
14 # 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
15 # testsuite funcs_1
16 # Create this script based on older scripts and new code.
17 # Last Change:
18 # 2008-06-11 mleich Move t/is_key_column_usage.test to this file and
19 # create variants for embedded/non embedded server.
20 #
21
22 let $engine_type = MEMORY;
23
24 let $is_table = KEY_COLUMN_USAGE;
25
26 # The table INFORMATION_SCHEMA.KEY_COLUMN_USAGE must exist
27 eval SHOW TABLES FROM information_schema LIKE '$is_table';
28
29 --echo #######################################################################
30 --echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
31 --echo #######################################################################
32 # Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
33 # statement, just as if it were an ordinary user-defined table.
34 #
35 --source suite/funcs_1/datadict/is_table_query.inc
36
37
38 --echo #########################################################################
39 --echo # Testcase 3.2.7.1: INFORMATION_SCHEMA.KEY_COLUMN_USAGE layout
40 --echo #########################################################################
41 # Ensure that the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table has the following
42 # columns, in the following order:
43 #
44 # CONSTRAINT_CATALOG (always shows NULL),
45 # CONSTRAINT_SCHEMA (shows the database, or schema, in which an accessible
46 # constraint, or index, resides),
47 # CONSTRAINT_NAME (shows the name of the accessible constraint),
48 # TABLE_CATALOG (always shows NULL),
49 # TABLE_SCHEMA (shows the database, or schema, in which the table constrained
50 # by that constraint resides),
51 # TABLE_NAME (shows the name of the table constrained by the constraint),
52 # COLUMN_NAME (shows the name of a column that is the index key, or part of
53 # the index key),
54 # ORDINAL_POSITION (shows the ordinal position of the column within the
55 # constraint index),
56 # POSITION_IN_UNIQUE_CONSTRAINT (shows, for a foreign key column, the ordinal
57 # position of the referenced column within the referenced unique index;
58 # otherwise NULL).
59 # added with 5.0.6:
60 # REFERENCED_TABLE_SCHEMA,
61 # REFERENCED_TABLE_NAME,
62 # REFERENCED_COLUMN_NAME
63 #
64 --source suite/funcs_1/datadict/datadict_bug_12777.inc
65 eval DESCRIBE information_schema.$is_table;
66 --source suite/funcs_1/datadict/datadict_bug_12777.inc
67 eval SHOW CREATE TABLE information_schema.$is_table;
68 --source suite/funcs_1/datadict/datadict_bug_12777.inc
69 eval SHOW COLUMNS FROM information_schema.$is_table;
70
71 # Note: Retrieval of information within information_schema.columns about
72 # information_schema.key_column_usage is in is_columns_is.test.
73
74 # Show that CONSTRAINT_CATALOG and TABLE_CATALOG are always NULL.
75 SELECT constraint_catalog, constraint_schema, constraint_name, table_catalog,
76 table_schema, table_name, column_name
77 FROM information_schema.key_column_usage
78 WHERE constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL
79 ORDER BY BINARY table_schema, BINARY table_name, BINARY column_name, BINARY constraint_name;
80
81
82 --echo ########################################################################################
83 --echo # Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.KEY_COLUMN_USAGE accessible information
84 --echo ########################################################################################
85 # 3.2.7.2: Ensure that the table shows the relevant information on every column, defined to
86 # be part of an index key, which is accessible to the current user or to PUBLIC.
87 # 3.2.7.3: Ensure that the table does not show any information on any indexed column that is
88 # not accessible to the current user or PUBLIC.
89 #
90 --disable_warnings
91 DROP DATABASE IF EXISTS db_datadict;
92 --enable_warnings
93 CREATE DATABASE db_datadict;
94
95 --error 0,ER_CANNOT_USER
96 DROP USER 'testuser1'@'localhost';
97 CREATE USER 'testuser1'@'localhost';
98 --error 0,ER_CANNOT_USER
99 DROP USER 'testuser2'@'localhost';
100 CREATE USER 'testuser2'@'localhost';
101
102 USE db_datadict;
103
104 --replace_result $engine_type <engine_type>
105 eval
106 CREATE TABLE t1_1
107 (f1 INT NOT NULL, PRIMARY KEY(f1),
108 f2 INT, INDEX f2_ind(f2))
109 ENGINE = $engine_type;
110 GRANT SELECT ON t1_1 to 'testuser1'@'localhost';
111
112 --replace_result $engine_type <engine_type>
113 eval
114 CREATE TABLE t1_2
115 (f1 INT NOT NULL, PRIMARY KEY(f1),
116 f2 INT, INDEX f2_ind(f2))
117 ENGINE = $engine_type;
118 GRANT SELECT ON t1_2 to 'testuser2'@'localhost';
119 #FIXME: add foreign keys
120
121 let $select= SELECT * FROM information_schema.key_column_usage
122 WHERE table_name LIKE 't1_%'
123 ORDER BY constraint_catalog, constraint_schema, constraint_name,
124 table_catalog, table_schema, table_name, ordinal_position;
125
126 # show view of user root
127 eval $select;
128
129 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
130 connect (testuser1, localhost, testuser1, , db_datadict);
131 eval $select;
132
133 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
134 connect (testuser2, localhost, testuser2, , db_datadict);
135 eval $select;
136
137 # Cleanup
138 connection default;
139 disconnect testuser1;
140 disconnect testuser2;
141 DROP USER 'testuser1'@'localhost';
142 DROP USER 'testuser2'@'localhost';
143 DROP TABLE t1_1;
144 DROP TABLE t1_2;
145 DROP DATABASE IF EXISTS db_datadict;
146
147
148 --echo ########################################################################################
149 --echo # Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.KEY_COLUMN_USAGE modifications
150 --echo ########################################################################################
151 # 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
152 # column) automatically inserts all relevant information on that
153 # object into every appropriate INFORMATION_SCHEMA table.
154 # 3.2.1.14: Ensure that the alteration of any existing database object
155 # automatically updates all relevant information on that object in
156 # every appropriate INFORMATION_SCHEMA table.
157 # 3.2.1.15: Ensure that the dropping of any existing database object
158 # automatically deletes all relevant information on that object from
159 # every appropriate INFORMATION_SCHEMA table.
160 #
161 --disable_warnings
162 DROP DATABASE IF EXISTS db_datadict;
163 DROP TABLE IF EXISTS test.t1_my_table;
164 --enable_warnings
165 CREATE DATABASE db_datadict;
166
167 SELECT table_name FROM information_schema.key_column_usage
168 WHERE table_name LIKE 't1_my_table%';
169 --replace_result $engine_type <engine_type>
170 eval
171 CREATE TABLE test.t1_my_table
172 (f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1,f2))
173 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
174 ENGINE = $engine_type;
175 # Settings used in CREATE TABLE must be visible
176 # in information_schema.key_column_usage.
177 --vertical_results
178 SELECT * FROM information_schema.key_column_usage
179 WHERE table_name = 't1_my_table';
180 --horizontal_results
181 #
182 # Check modification of TABLE_NAME
183 SELECT DISTINCT table_name FROM information_schema.key_column_usage
184 WHERE table_name LIKE 't1_my_table%';
185 RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
186 SELECT DISTINCT table_name FROM information_schema.key_column_usage
187 WHERE table_name LIKE 't1_my_table%';
188 #
189 # Check modification of TABLE_SCHEMA
190 SELECT DISTINCT table_schema,table_name FROM information_schema.key_column_usage
191 WHERE table_name = 't1_my_tablex';
192 RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
193 SELECT DISTINCT table_schema,table_name FROM information_schema.key_column_usage
194 WHERE table_name = 't1_my_tablex';
195 #
196 # Check modification of COLUMN_NAME
197 SELECT DISTINCT table_name, column_name FROM information_schema.key_column_usage
198 WHERE table_name = 't1_my_tablex'
199 ORDER BY table_name, column_name;
200 ALTER TABLE db_datadict.t1_my_tablex CHANGE COLUMN f1 first_col CHAR(12);
201 SELECT DISTINCT table_name, column_name FROM information_schema.key_column_usage
202 WHERE table_name = 't1_my_tablex'
203 ORDER BY table_name, column_name;
204 #
205 # Note: The size of the column list and the not very selective qualification
206 # is intended. I want to see that the schema names are equal and
207 # all records about 't1_my_tablex'.
208 let $my_select = SELECT constraint_schema, constraint_name, table_schema,
209 table_name, column_name, ordinal_position
210 FROM information_schema.key_column_usage
211 WHERE table_name = 't1_my_tablex'
212 ORDER BY constraint_schema, constraint_name, table_schema,
213 table_name, ordinal_position;
214 #
215 # Check ADD INDEX being not UNIQUE (does not show up in key_column_usage)
216 eval $my_select;
217 CREATE INDEX f2 ON db_datadict.t1_my_tablex(f2);
218 eval $my_select;
219 DROP INDEX f2 ON db_datadict.t1_my_tablex;
220 #
221 # Check ADD UNIQUE INDEX without name explicit assigned
222 eval $my_select;
223 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE (f2);
224 eval $my_select;
225 DROP INDEX f2 ON db_datadict.t1_my_tablex;
226 #
227 # Check ADD UNIQUE INDEX with name explicit assigned
228 eval $my_select;
229 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f2);
230 eval $my_select;
231 DROP INDEX my_idx ON db_datadict.t1_my_tablex;
232 eval $my_select;
233 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f4,first_col);
234 eval $my_select;
235 #
236 # Check impact of DROP TABLE
237 SELECT table_name, column_name
238 FROM information_schema.key_column_usage
239 WHERE table_name = 't1_my_tablex'
240 ORDER BY table_name, column_name;
241 DROP TABLE db_datadict.t1_my_tablex;
242 SELECT table_name, column_name
243 FROM information_schema.key_column_usage
244 WHERE table_name = 't1_my_tablex';
245 #
246 # No UNIQUE CONSTRAINT -> no entry in key_column_usage
247 SELECT table_name FROM information_schema.key_column_usage
248 WHERE table_name = 't1_my_tablex';
249 --replace_result $engine_type <engine_type>
250 eval
251 CREATE TABLE db_datadict.t1_my_tablex
252 ENGINE = $engine_type AS
253 SELECT 1 AS f1;
254 SELECT table_name FROM information_schema.key_column_usage
255 WHERE table_name = 't1_my_tablex';
256 # UNIQUE CONSTRAINT -> entry in key_column_usage
257 ALTER TABLE db_datadict.t1_my_tablex ADD PRIMARY KEY(f1);
258 SELECT table_name FROM information_schema.key_column_usage
259 WHERE table_name = 't1_my_tablex';
260 #
261 # Check impact of DROP SCHEMA
262 SELECT table_name FROM information_schema.key_column_usage
263 WHERE table_name = 't1_my_tablex';
264 DROP DATABASE db_datadict;
265 SELECT table_name FROM information_schema.key_column_usage
266 WHERE table_name = 't1_my_tablex';
267 #
268
269
270 --echo ########################################################################
271 --echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
272 --echo # DDL on INFORMATION_SCHEMA table are not supported
273 --echo ########################################################################
274 # 3.2.1.3: Ensure that no user may execute an INSERT statement on any
275 # INFORMATION_SCHEMA table.
276 # 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
277 # INFORMATION_SCHEMA table.
278 # 3.2.1.5: Ensure that no user may execute a DELETE statement on any
279 # INFORMATION_SCHEMA table.
280 # 3.2.1.8: Ensure that no user may create an index on an
281 # INFORMATION_SCHEMA table.
282 # 3.2.1.9: Ensure that no user may alter the definition of an
283 # INFORMATION_SCHEMA table.
284 # 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
285 # 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
286 # other database.
287 # 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
288 # in an INFORMATION_SCHEMA table.
289 #
290 --disable_warnings
291 DROP DATABASE IF EXISTS db_datadict;
292 DROP TABLE IF EXISTS db_datadict.t1;
293 --enable_warnings
294 CREATE DATABASE db_datadict;
295 --replace_result $engine_type <engine_type>
296 eval
297 CREATE TABLE db_datadict.t1 (f1 BIGINT)
298 ENGINE = $engine_type;
299
300 # Note(mleich):
301 # 1. We can get here different error messages.
302 # 2. We do not want to unify the individual messages to the far to unspecific
303 # 'Got one of the listed errors'.
304 let $my_error_message =
305 ##### The previous statement must fail ######
306 # Server type | expected error name | expected error message
307 # --------------------------------------------------------------------------------------------------------------------
308 # not embedded | ER_DBACCESS_DENIED_ERROR | ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
309 # embedded | ER_NON_INSERTABLE_TABLE | ERROR HY000: The target table schemata of the INSERT is not insertable-into
310 # | or similar | or similar
311 ;
312
313 --disable_abort_on_error
314 INSERT INTO information_schema.key_column_usage
315 (constraint_schema, constraint_name, table_name)
316 VALUES ( 'mysql', 'primary', 'db');
317 if (!$mysql_errno)
318 {
319 --echo $my_error_message
320 exit;
321 }
322 --error ER_DBACCESS_DENIED_ERROR
323 INSERT INTO information_schema.key_column_usage
324 SELECT * FROM information_schema.key_column_usage;
325
326 --error ER_DBACCESS_DENIED_ERROR,ER_NON_UPDATABLE_TABLE
327 UPDATE information_schema.key_column_usage
328 SET table_name = 'db1' WHERE constraint_name = 'primary';
329
330 --error ER_DBACCESS_DENIED_ERROR
331 DELETE FROM information_schema.key_column_usage WHERE table_name = 't1';
332 --error ER_DBACCESS_DENIED_ERROR
333 TRUNCATE information_schema.key_column_usage;
334
335 --error ER_DBACCESS_DENIED_ERROR
336 CREATE INDEX i3 ON information_schema.key_column_usage(table_name);
337
338 --error ER_DBACCESS_DENIED_ERROR
339 ALTER TABLE information_schema.key_column_usage ADD f1 INT;
340
341 --error ER_DBACCESS_DENIED_ERROR
342 DROP TABLE information_schema.key_column_usage;
343
344 --error ER_DBACCESS_DENIED_ERROR
345 ALTER TABLE information_schema.key_column_usage
346 RENAME db_datadict.key_column_usage;
347 --error ER_DBACCESS_DENIED_ERROR
348 ALTER TABLE information_schema.key_column_usage
349 RENAME information_schema.xkey_column_usage;
350 --enable_abort_on_error
351
352 # Cleanup
353 DROP TABLE db_datadict.t1;
354 DROP DATABASE db_datadict;