Debian Codegraph @

mysql-test/suite/funcs_1/t/is_table_constraints.test

raw
1 # suite/funcs_1/t/is_table_constraints.test
2 #
3 # Check the layout of information_schema.table_constraints 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_typee.
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 #
18
19 # Result differences depending on FS case sensitivity.
20 if (!$require_case_insensitive_file_system)
21 {
22 --source include/have_case_sensitive_file_system.inc
23 }
24
25
26 let $engine_type = InnoDB;
27
28 let $is_table = TABLE_CONSTRAINTS;
29
30 # The table INFORMATION_SCHEMA.TABLE_CONSTRAINTS must exist
31 eval SHOW TABLES FROM information_schema LIKE '$is_table';
32
33 --echo #######################################################################
34 --echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
35 --echo #######################################################################
36 # Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
37 # statement, just as if it were an ordinary user-defined table.
38 #
39 --source suite/funcs_1/datadict/is_table_query.inc
40
41
42 --echo #########################################################################
43 --echo # Testcase 3.2.10.1: INFORMATION_SCHEMA.TABLE_CONSTRAINTS layout
44 --echo #########################################################################
45 # Ensure that the INFORMATION_SCHEMA.TABLE_CONSTRAINTS table has the following
46 # columns, in the following order:
47 #
48 # CONSTRAINT_CATALOG (always shows NULL),
49 # CONSTRAINT_SCHEMA (shows the database, or schema, in which
50 # a constraint an accessible table resides),
51 # CONSTRAINT_NAME (shows the name of a constraint defined on
52 # an accessible table),
53 # TABLE_SCHEMA (shows the database, or schema, in which the
54 # table resides),
55 # TABLE_NAME (shows the name of the table),
56 # CONSTRAINT_TYPE (shows the type of the constraint; either
57 # 'primary key', 'foreign key', 'unique', 'check').
58 #
59 --source suite/funcs_1/datadict/datadict_bug_12777.inc
60 eval DESCRIBE information_schema.$is_table;
61 --source suite/funcs_1/datadict/datadict_bug_12777.inc
62 eval SHOW CREATE TABLE information_schema.$is_table;
63 --source suite/funcs_1/datadict/datadict_bug_12777.inc
64 eval SHOW COLUMNS FROM information_schema.$is_table;
65
66 # Note: Retrieval of information within information_schema.columns about
67 # information_schema.table_constraints is in is_columns_is.test.
68
69 # Show that CONSTRAINT_CATALOG IS NULL
70 SELECT constraint_catalog, constraint_schema, constraint_name,
71 table_schema, table_name
72 FROM information_schema.table_constraints
73 WHERE constraint_catalog IS NOT NULL AND
74 table_name != 'ndb_binlog_index'
75 ORDER BY table_schema,table_name, constraint_name;
76
77
78 --echo #########################################################################################
79 --echo # Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.TABLE_CONSTRAINTS accessible information
80 --echo #########################################################################################
81 # 3.2.7.2: Ensure that the table shows the relevant information on every
82 # column, defined to be part of an index key, which is accessible to
83 # the current user or to PUBLIC.
84 # 3.2.7.3: Ensure that the table does not show any information on any indexed
85 # column that is not accessible to the current user or PUBLIC.
86 #
87 # Note: Check of content within table_constraints about tables within
88 # database is checked in
89 # mysql is_table_constraints_mysql
90 # information_schema is_table_constraints_is
91 #
92 --disable_warnings
93 DROP DATABASE IF EXISTS db_datadict;
94 --enable_warnings
95 CREATE DATABASE db_datadict;
96 --replace_result $engine_type <some_engine_type>
97 eval
98 CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT, f3 BIGINT, f4 BIGINT,
99 f5 BIGINT, f6 BIGINT, PRIMARY KEY (f1,f2))
100 ENGINE = $engine_type;
101 CREATE UNIQUE INDEX my_idx1 ON db_datadict.t1(f6,f1);
102 CREATE UNIQUE INDEX my_idx2 ON db_datadict.t1(f3);
103 --replace_result $engine_type <some_engine_type>
104 eval
105 CREATE TABLE db_datadict.t2 (f1 BIGINT, f2 BIGINT, f3 BIGINT, f4 BIGINT,
106 f5 BIGINT, f6 BIGINT, PRIMARY KEY (f1,f2))
107 ENGINE = $engine_type;
108
109 --error 0,ER_CANNOT_USER
110 DROP USER 'testuser1'@'localhost';
111 CREATE USER 'testuser1'@'localhost';
112 GRANT SELECT(f5) ON db_datadict.t1 TO 'testuser1'@'localhost';
113 SHOW GRANTS FOR 'testuser1'@'localhost';
114
115 let $my_select = SELECT * FROM information_schema.table_constraints
116 WHERE table_schema = 'db_datadict'
117 ORDER BY table_schema,table_name, constraint_name;
118 let $my_show1 = SHOW INDEXES FROM db_datadict.t1;
119 let $my_show2 = SHOW INDEXES FROM db_datadict.t2;
120
121 eval $my_select;
122 # 1 Table Note: We intentinally suppress the protocolling of all
123 # 2 Non_unique output being not of interest or depending on
124 # 3 Key_name storage engine used for the table.
125 # 4 Seq_in_index
126 # 5 Column_name
127 # 6 Collation
128 # 7 Cardinality
129 # 8 Sub_part
130 # 9 Packed
131 # 10 Null
132 # 11 Index_type
133 # 12 Comment
134 --replace_column 6 ### 7 ### 8 ### 9 ### 10 ### 11 ### 12 ###
135 eval $my_show1;
136 --replace_column 6 ### 7 ### 8 ### 9 ### 10 ### 11 ### 12 ###
137 eval $my_show2;
138
139 --echo # Establish connection testuser1 (user=testuser1)
140 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
141 connect (testuser1, localhost, testuser1, , db_datadict);
142 SHOW GRANTS FOR 'testuser1'@'localhost';
143 eval $my_select;
144 --replace_column 6 ### 7 ### 8 ### 9 ### 10 ### 11 ### 12 ###
145 eval $my_show1;
146 --error ER_TABLEACCESS_DENIED_ERROR
147 eval $my_show2;
148
149 # Cleanup
150 --echo # Switch to connection default and close connection testuser1
151 connection default;
152 disconnect testuser1;
153 DROP USER 'testuser1'@'localhost';
154 DROP DATABASE db_datadict;
155
156
157 --echo #########################################################################################
158 --echo # Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TABLE_CONSTRAINTS modifications
159 --echo #########################################################################################
160 # 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
161 # column) automatically inserts all relevant information on that
162 # object into every appropriate INFORMATION_SCHEMA table.
163 # 3.2.1.14: Ensure that the alteration of any existing database object
164 # automatically updates all relevant information on that object in
165 # every appropriate INFORMATION_SCHEMA table.
166 # 3.2.1.15: Ensure that the dropping of any existing database object
167 # automatically deletes all relevant information on that object from
168 # every appropriate INFORMATION_SCHEMA table.
169 #
170 --disable_warnings
171 DROP DATABASE IF EXISTS db_datadict;
172 DROP TABLE IF EXISTS test.t1_my_table;
173 --enable_warnings
174 CREATE DATABASE db_datadict;
175
176 SELECT table_name FROM information_schema.table_constraints
177 WHERE table_name LIKE 't1_my_table%';
178 --replace_result $engine_type <engine_type>
179 eval
180 CREATE TABLE test.t1_my_table
181 (f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1,f2))
182 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
183 ENGINE = $engine_type;
184 # Settings used in CREATE TABLE must be visible
185 # in information_schema.table_constraints.
186 SELECT constraint_name, table_schema, table_name, constraint_type
187 FROM information_schema.table_constraints
188 WHERE table_name = 't1_my_table';
189 #
190 # Check modification of TABLE_NAME
191 SELECT table_name FROM information_schema.table_constraints
192 WHERE table_name LIKE 't1_my_table%';
193 RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
194 SELECT table_name FROM information_schema.table_constraints
195 WHERE table_name LIKE 't1_my_table%';
196 #
197 # Check modification of TABLE_SCHEMA
198 SELECT table_schema,table_name FROM information_schema.table_constraints
199 WHERE table_name = 't1_my_tablex';
200 RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
201 SELECT table_schema,table_name FROM information_schema.table_constraints
202 WHERE table_name = 't1_my_tablex';
203 #
204 let $my_select = SELECT constraint_schema, constraint_name, table_schema,
205 table_name, constraint_type
206 FROM information_schema.table_constraints
207 WHERE table_name = 't1_my_tablex'
208 ORDER BY table_schema,table_name, constraint_name;
209 #
210 # Check ADD INDEX being not UNIQUE (does not show up in table_constraints)
211 eval $my_select;
212 CREATE INDEX f2 ON db_datadict.t1_my_tablex(f2);
213 eval $my_select;
214 DROP INDEX f2 ON db_datadict.t1_my_tablex;
215 #
216 # Check ADD UNIQUE INDEX without name explicit assigned
217 eval $my_select;
218 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE (f2);
219 eval $my_select;
220 DROP INDEX f2 ON db_datadict.t1_my_tablex;
221 #
222 # Check ADD UNIQUE INDEX with name explicit assigned
223 eval $my_select;
224 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f4,f1);
225 eval $my_select;
226 DROP INDEX my_idx ON db_datadict.t1_my_tablex;
227 eval $my_select;
228 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f2);
229 eval $my_select;
230 #
231 # Check DROP COLUMN (removing an UNIQUE INDEX)
232 eval $my_select;
233 ALTER TABLE db_datadict.t1_my_tablex
234 DROP COLUMN f2;
235 eval $my_select;
236 #
237 # Check impact of DROP TABLE
238 SELECT table_name
239 FROM information_schema.table_constraints
240 WHERE table_name = 't1_my_tablex';
241 DROP TABLE db_datadict.t1_my_tablex;
242 SELECT table_name
243 FROM information_schema.table_constraints
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.table_constraints
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.table_constraints
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.table_constraints
259 WHERE table_name = 't1_my_tablex';
260 #
261 # Check impact of DROP SCHEMA
262 SELECT table_name FROM information_schema.table_constraints
263 WHERE table_name = 't1_my_tablex';
264 DROP DATABASE db_datadict;
265 SELECT table_name FROM information_schema.table_constraints
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 tables 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, UNIQUE(f1))
298 ENGINE = $engine_type;
299
300 --error ER_DBACCESS_DENIED_ERROR
301
302 --error ER_DBACCESS_DENIED_ERROR
303 INSERT INTO information_schema.table_constraints
304 (constraint_schema, constraint_name, table_name)
305 VALUES ( 'mysql', 'primary', 'db');
306 --error ER_DBACCESS_DENIED_ERROR
307 INSERT INTO information_schema.table_constraints
308 SELECT * FROM information_schema.table_constraints;
309
310 --error ER_DBACCESS_DENIED_ERROR
311 UPDATE information_schema.table_constraints
312 SET table_name = 'db1' WHERE constraint_name = 'primary';
313
314 --error ER_DBACCESS_DENIED_ERROR
315 DELETE FROM information_schema.table_constraints WHERE table_name = 't1';
316 --error ER_DBACCESS_DENIED_ERROR
317 TRUNCATE information_schema.table_constraints;
318
319 --error ER_DBACCESS_DENIED_ERROR
320 CREATE INDEX i3 ON information_schema.table_constraints(table_name);
321
322 --error ER_DBACCESS_DENIED_ERROR
323 ALTER TABLE information_schema.table_constraints ADD f1 INT;
324
325 --error ER_DBACCESS_DENIED_ERROR
326 DROP TABLE information_schema.table_constraints;
327
328 --error ER_DBACCESS_DENIED_ERROR
329 ALTER TABLE information_schema.table_constraints
330 RENAME db_datadict.table_constraints;
331 --error ER_DBACCESS_DENIED_ERROR
332 ALTER TABLE information_schema.table_constraints
333 RENAME information_schema.xtable_constraints;
334
335 # Cleanup
336 DROP TABLE db_datadict.t1;
337 DROP DATABASE db_datadict;
338