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 # This test cannot be used for the embedded server because we check here
20 # privileges.
21 --source include/not_embedded.inc
22 --source include/have_innodb.inc
23
24 let $engine_type = MyISAM;
25
26 let $is_table = TABLE_CONSTRAINTS;
27
28 # The table INFORMATION_SCHEMA.TABLE_CONSTRAINTS must exist
29 eval SHOW TABLES FROM information_schema LIKE '$is_table';
30
31 --echo #######################################################################
32 --echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
33 --echo #######################################################################
34 # Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
35 # statement, just as if it were an ordinary user-defined table.
36 #
37 --source suite/funcs_1/datadict/is_table_query.inc
38
39 --echo #########################################################################
40 --echo # Testcase 3.2.10.1: INFORMATION_SCHEMA.TABLE_CONSTRAINTS layout
41 --echo #########################################################################
42 # Ensure that the INFORMATION_SCHEMA.TABLE_CONSTRAINTS table has the following
43 # columns, in the following order:
44 #
45 # CONSTRAINT_CATALOG (always shows NULL),
46 # CONSTRAINT_SCHEMA (shows the database, or schema, in which
47 # a constraint an accessible table resides),
48 # CONSTRAINT_NAME (shows the name of a constraint defined on
49 # an accessible table),
50 # TABLE_SCHEMA (shows the database, or schema, in which the
51 # table resides),
52 # TABLE_NAME (shows the name of the table),
53 # CONSTRAINT_TYPE (shows the type of the constraint; either
54 # 'primary key', 'foreign key', 'unique', 'check').
55 #
56 --source suite/funcs_1/datadict/datadict_bug_12777.inc
57 eval DESCRIBE information_schema.$is_table;
58 --source suite/funcs_1/datadict/datadict_bug_12777.inc
59 eval SHOW CREATE TABLE information_schema.$is_table;
60 --source suite/funcs_1/datadict/datadict_bug_12777.inc
61 eval SHOW COLUMNS FROM information_schema.$is_table;
62
63 # Note: Retrieval of information within information_schema.columns about
64 # information_schema.table_constraints is in is_columns_is.test.
65
66 # Show that CONSTRAINT_CATALOG IS NULL
67 SELECT constraint_catalog, constraint_schema, constraint_name,
68 table_schema, table_name
69 FROM information_schema.table_constraints
70 WHERE constraint_catalog IS NOT NULL
71 ORDER BY constraint_schema, table_name, constraint_name;
72
73 --echo #########################################################################################
74 --echo # Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.TABLE_CONSTRAINTS accessible information
75 --echo #########################################################################################
76 # 3.2.7.2: Ensure that the table shows the relevant information on every
77 # column, defined to be part of an index key, which is accessible to
78 # the current user or to PUBLIC.
79 # 3.2.7.3: Ensure that the table does not show any information on any indexed
80 # column that is not accessible to the current user or PUBLIC.
81 #
82 # Note: Check of content within table_constraints about tables within
83 # database is checked in
84 # mysql is_table_constraints_mysql
85 # information_schema is_table_constraints_is
86 #
87 CREATE DATABASE db_datadict;
88 --replace_result $engine_type <some_engine_type>
89 eval
90 CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT, f3 BIGINT, f4 BIGINT,
91 f5 BIGINT, f6 BIGINT, PRIMARY KEY (f1,f2))
92 ENGINE = $engine_type;
93 CREATE UNIQUE INDEX my_idx1 ON db_datadict.t1(f6,f1);
94 CREATE UNIQUE INDEX my_idx2 ON db_datadict.t1(f3);
95 --replace_result $engine_type <some_engine_type>
96 eval
97 CREATE TABLE db_datadict.t2 (f1 BIGINT, f2 BIGINT, f3 BIGINT, f4 BIGINT,
98 f5 BIGINT, f6 BIGINT, PRIMARY KEY (f1,f2))
99 ENGINE = $engine_type;
100
101 CREATE USER 'testuser1'@'localhost';
102 GRANT SELECT(f5), UPDATE(f6) ON db_datadict.t1 TO 'testuser1'@'localhost';
103 SHOW GRANTS FOR 'testuser1'@'localhost';
104
105 let $my_select = SELECT * FROM information_schema.table_constraints
106 WHERE table_schema = 'db_datadict'
107 ORDER BY table_schema,table_name, constraint_name;
108 let $my_show1 = SHOW INDEXES FROM db_datadict.t1;
109 let $my_show2 = SHOW INDEXES FROM db_datadict.t2;
110
111 eval $my_select;
112 # 1 Table Note: We intentinally suppress the protocolling of all
113 # 2 Non_unique output being not of interest or depending on
114 # 3 Key_name storage engine used for the table.
115 # 4 Seq_in_index
116 # 5 Column_name
117 # 6 Collation
118 # 7 Cardinality
119 # 8 Sub_part
120 # 9 Packed
121 # 10 Null
122 # 11 Index_type
123 # 12 Comment
124 --replace_column 6 ### 7 ### 8 ### 9 ### 10 ### 11 ### 12 ###
125 eval $my_show1;
126 --replace_column 6 ### 7 ### 8 ### 9 ### 10 ### 11 ### 12 ###
127 eval $my_show2;
128
129 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
130 connect (testuser1, localhost, testuser1, , db_datadict);
131 SHOW GRANTS FOR 'testuser1'@'localhost';
132 eval $my_select;
133 --replace_column 6 ### 7 ### 8 ### 9 ### 10 ### 11 ### 12 ###
134 eval $my_show1;
135 --error ER_TABLEACCESS_DENIED_ERROR
136 eval $my_show2;
137
138 # Cleanup
139 connection default;
140 disconnect testuser1;
141 DROP USER 'testuser1'@'localhost';
142 DROP DATABASE db_datadict;
143
144
145 --echo #########################################################################################
146 --echo # Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TABLE_CONSTRAINTS modifications
147 --echo #########################################################################################
148 # 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
149 # column) automatically inserts all relevant information on that
150 # object into every appropriate INFORMATION_SCHEMA table.
151 # 3.2.1.14: Ensure that the alteration of any existing database object
152 # automatically updates all relevant information on that object in
153 # every appropriate INFORMATION_SCHEMA table.
154 # 3.2.1.15: Ensure that the dropping of any existing database object
155 # automatically deletes all relevant information on that object from
156 # every appropriate INFORMATION_SCHEMA table.
157 #
158 CREATE DATABASE db_datadict;
159
160 SELECT table_name FROM information_schema.table_constraints
161 WHERE table_name LIKE 't1_my_table%';
162 --replace_result $engine_type <engine_type>
163 eval
164 CREATE TABLE test.t1_my_table
165 (f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1))
166 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
167 ENGINE = $engine_type;
168 # Settings used in CREATE TABLE must be visible
169 # in information_schema.table_constraints.
170 SELECT constraint_name, table_schema, table_name, constraint_type
171 FROM information_schema.table_constraints
172 WHERE table_name = 't1_my_table';
173 #
174 # Check modification of TABLE_NAME
175 SELECT table_name FROM information_schema.table_constraints
176 WHERE table_name LIKE 't1_my_table%';
177 RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
178 SELECT table_name FROM information_schema.table_constraints
179 WHERE table_name LIKE 't1_my_table%';
180 #
181 # Check modification of TABLE_SCHEMA
182 SELECT table_schema,table_name FROM information_schema.table_constraints
183 WHERE table_name = 't1_my_tablex';
184 RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
185 SELECT table_schema,table_name FROM information_schema.table_constraints
186 WHERE table_name = 't1_my_tablex';
187 #
188 let $my_select = SELECT constraint_schema, constraint_name, table_schema,
189 table_name, constraint_type
190 FROM information_schema.table_constraints
191 WHERE table_name = 't1_my_tablex'
192 ORDER BY table_schema,table_name, constraint_name;
193 #
194 # Check ADD INDEX being not UNIQUE (does not show up in table_constraints)
195 eval $my_select;
196 CREATE INDEX f2 ON db_datadict.t1_my_tablex(f2);
197 eval $my_select;
198 DROP INDEX f2 ON db_datadict.t1_my_tablex;
199 #
200 # Check ADD UNIQUE INDEX without name explicit assigned
201 eval $my_select;
202 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE (f2);
203 eval $my_select;
204 DROP INDEX f2 ON db_datadict.t1_my_tablex;
205 #
206 # Check ADD UNIQUE INDEX with name explicit assigned
207 eval $my_select;
208 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f4,f1);
209 eval $my_select;
210 DROP INDEX my_idx ON db_datadict.t1_my_tablex;
211 eval $my_select;
212 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f2);
213 eval $my_select;
214 #
215 # Check DROP COLUMN (removing an UNIQUE INDEX)
216 eval $my_select;
217 ALTER TABLE db_datadict.t1_my_tablex
218 DROP COLUMN f2;
219 eval $my_select;
220 #
221 # Check impact of DROP TABLE
222 SELECT table_name
223 FROM information_schema.table_constraints
224 WHERE table_name = 't1_my_tablex';
225 DROP TABLE db_datadict.t1_my_tablex;
226 SELECT table_name
227 FROM information_schema.table_constraints
228 WHERE table_name = 't1_my_tablex';
229 #
230 # No UNIQUE CONSTRAINT -> no entry in key_column_usage
231 SELECT table_name FROM information_schema.table_constraints
232 WHERE table_name = 't1_my_tablex';
233 --replace_result $engine_type <engine_type>
234 eval
235 CREATE TABLE db_datadict.t1_my_tablex
236 ENGINE = $engine_type AS
237 SELECT 1 AS f1;
238 SELECT table_name FROM information_schema.table_constraints
239 WHERE table_name = 't1_my_tablex';
240 # UNIQUE CONSTRAINT -> entry in key_column_usage
241 ALTER TABLE db_datadict.t1_my_tablex ADD PRIMARY KEY(f1);
242 SELECT table_name FROM information_schema.table_constraints
243 WHERE table_name = 't1_my_tablex';
244 #
245 # Check impact of DROP SCHEMA
246 SELECT table_name FROM information_schema.table_constraints
247 WHERE table_name = 't1_my_tablex';
248 DROP DATABASE db_datadict;
249 SELECT table_name FROM information_schema.table_constraints
250 WHERE table_name = 't1_my_tablex';
251 #
252
253
254 --echo ########################################################################
255 --echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
256 --echo # DDL on INFORMATION_SCHEMA tables are not supported
257 --echo ########################################################################
258 # 3.2.1.3: Ensure that no user may execute an INSERT statement on any
259 # INFORMATION_SCHEMA table.
260 # 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
261 # INFORMATION_SCHEMA table.
262 # 3.2.1.5: Ensure that no user may execute a DELETE statement on any
263 # INFORMATION_SCHEMA table.
264 # 3.2.1.8: Ensure that no user may create an index on an
265 # INFORMATION_SCHEMA table.
266 # 3.2.1.9: Ensure that no user may alter the definition of an
267 # INFORMATION_SCHEMA table.
268 # 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
269 # 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
270 # other database.
271 # 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
272 # in an INFORMATION_SCHEMA table.
273 #
274 CREATE DATABASE db_datadict;
275 --replace_result $engine_type <engine_type>
276 eval
277 CREATE TABLE db_datadict.t1 (f1 BIGINT, UNIQUE(f1))
278 ENGINE = $engine_type;
279
280 --error ER_DBACCESS_DENIED_ERROR
281
282 --error ER_DBACCESS_DENIED_ERROR
283 INSERT INTO information_schema.table_constraints
284 (constraint_schema, constraint_name, table_name)
285 VALUES ( 'mysql', 'primary', 'db');
286 --error ER_DBACCESS_DENIED_ERROR
287 INSERT INTO information_schema.table_constraints
288 SELECT * FROM information_schema.table_constraints;
289
290 --error ER_DBACCESS_DENIED_ERROR
291 UPDATE information_schema.table_constraints
292 SET table_name = 'db1' WHERE constraint_name = 'primary';
293
294 --error ER_DBACCESS_DENIED_ERROR
295 DELETE FROM information_schema.table_constraints WHERE table_name = 't1';
296 --error ER_DBACCESS_DENIED_ERROR
297 TRUNCATE information_schema.table_constraints;
298
299 --error ER_DBACCESS_DENIED_ERROR
300 CREATE INDEX i3 ON information_schema.table_constraints(table_name);
301
302 --error ER_DBACCESS_DENIED_ERROR
303 ALTER TABLE information_schema.table_constraints ADD f1 INT;
304
305 --error ER_DBACCESS_DENIED_ERROR
306 DROP TABLE information_schema.table_constraints;
307
308 --error ER_DBACCESS_DENIED_ERROR
309 ALTER TABLE information_schema.table_constraints
310 RENAME db_datadict.table_constraints;
311 --error ER_DBACCESS_DENIED_ERROR
312 ALTER TABLE information_schema.table_constraints
313 RENAME information_schema.xtable_constraints;
314
315 # Cleanup
316 DROP TABLE db_datadict.t1;
317 DROP DATABASE db_datadict;
318