Debian Codegraph @

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

raw
1 --echo #
2 --echo # Check access to I_S.x_extensions tables
3 --echo #
4
5 CREATE SCHEMA db_datadict;
6 CREATE USER 'testuser1'@'localhost';
7 CREATE USER 'testuser2'@'localhost';
8
9 CREATE TABLE db_datadict.t1
10 (f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT AUTO_INCREMENT,
11 UNIQUE INDEX MUL_IDX(f1,f3), PRIMARY KEY (f4));
12 CREATE VIEW db_datadict.v1 AS SELECT 1 AS f1, 1 AS f2;
13 GRANT SELECT(f1, f2) ON db_datadict.t1 TO 'testuser1'@'localhost';
14 GRANT SELECT(f2) ON db_datadict.v1 TO 'testuser1'@'localhost';
15 CREATE TABLE db_datadict.t2
16 (f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT, PRIMARY KEY (f1,f4));
17 GRANT INSERT(f1, f2) ON db_datadict.t2 TO 'testuser2'@'localhost';
18
19 let $verify_tables_ext= SELECT (SELECT count(*) FROM information_schema.tables WHERE table_schema = 'db_datadict') AS TABLES_ROWS, (SELECT COUNT(*) FROM information_schema.tables_extensions WHERE table_schema = 'db_datadict') AS TABLES_EXT_ROWS;
20
21 let $verify_columns_ext= SELECT (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'db_datadict') AS COLUMNS_ROWS, (SELECT COUNT(*) FROM information_schema.columns_extensions WHERE table_schema = 'db_datadict') AS COLUMNS_EXT_ROWS;
22
23 let $verify_table_constraints_ext= SELECT (SELECT COUNT(*) FROM information_schema.table_constraints WHERE constraint_schema = 'db_datadict') AS CONSTRAINTS_ROWS, (SELECT COUNT(*) FROM information_schema.table_constraints_extensions WHERE constraint_schema = 'db_datadict') AS CONSTRAINTS_EXT_ROWS;
24
25 let $tables_q= SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, '<create time>' AS CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM, CREATE_OPTIONS, TABLE_COMMENT FROM information_schema.tables WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name;
26
27 let $tables_ext_q= SELECT * FROM information_schema.tables_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name;
28
29 let $columns_q= SELECT * FROM information_schema.columns WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name;
30
31 let $columns_ext_q= SELECT * FROM information_schema.columns_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name;
32
33 let $table_constraints_q= SELECT * FROM information_schema.table_constraints WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name;
34
35 let $table_constraints_ext_q= SELECT * FROM information_schema.table_constraints_extensions WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name;
36
37 --echo # Checking tables_extensions as root
38 eval $tables_q;
39 eval $tables_ext_q;
40 eval $verify_tables_ext;
41
42 --echo # Checking columns_extensions as root
43 eval $columns_q;
44 eval $columns_ext_q;
45 eval $verify_columns_ext;
46
47 --echo # Checking table_constraints_extensions as root
48 eval $table_constraints_q;
49 eval $table_constraints_ext_q;
50 eval $verify_table_constraints_ext;
51
52 connect (testuser1, localhost, testuser1, , db_datadict);
53 --echo # Checking tables_extensions as testuser1
54 eval $tables_q;
55 eval $tables_ext_q;
56 eval $verify_tables_ext;
57
58 --echo # Checking columns_extensions as testuser1
59 eval $columns_q;
60 eval $columns_ext_q;
61 eval $verify_columns_ext;
62
63 --echo # Checking table_constraints_extensions as testuser1
64 eval $table_constraints_q;
65 eval $table_constraints_ext_q;
66 eval $verify_table_constraints_ext;
67
68 connect (testuser2, localhost, testuser2, , db_datadict);
69 --echo # Checking tables_extensions as testuser1
70 eval $tables_q;
71 eval $tables_ext_q;
72 eval $verify_tables_ext;
73
74 --echo # Checking columns_extensions as testuser2
75 eval $columns_q;
76 eval $columns_ext_q;
77 eval $verify_columns_ext;
78
79 --echo # Checking table_constraints_extensions as testuser2
80 eval $table_constraints_q;
81 eval $table_constraints_ext_q;
82 eval $verify_table_constraints_ext;
83
84 connection default;
85 disconnect testuser1;
86 disconnect testuser2;
87
88 --echo # Cleanup
89
90 DROP TABLE db_datadict.t2;
91 DROP TABLE db_datadict.t1;
92
93 DROP USER 'testuser2'@'localhost';
94 DROP USER 'testuser1'@'localhost';
95 DROP SCHEMA db_datadict;