Debian Codegraph @

mysql-test/suite/ndbcluster/verify_foreign_keys.inc

raw
1 #
2 # Print the foreign key definitions for the given table and then
3 # verify that they are consistent in DD and NDB
4 #
5 # ==== Usage ====
6 # [--let $ndb_db_name= <database name>]
7 # --let $ndb_table_name= <table name>
8 # --source verify_foreign_keys.inc
9 #
10 # Parameters:
11 # $ndb_db_name, $ndb_table_name
12 # DB and name of the table from which the names of
13 # FKs should to be printed and verified
14 # Note : default db name "test" will be used if
15 # none is passed to $ndb_db_name
16 #
17
18 # Table name is mandatory option
19 if (!$ndb_table_name) {
20 --die ERROR IN TEST: Please set $ndb_table_name before calling verify_foreign_keys.inc script
21 }
22
23 if (!$ndb_db_name) {
24 let $ndb_db_name=test;
25 }
26
27 # Print foreign key definitions using show create table
28 eval SHOW CREATE TABLE $ndb_db_name.$ndb_table_name;
29
30 --disable_query_log
31
32 # Create view which displays all user tables and foreign keys.
33 # NOTE! Uses a format which is backward compatible with the output
34 # from "ndb_show_tables" previously used by this test
35 CREATE VIEW test.ndb_object_list AS
36 SELECT
37 id,
38 /* Hardcoded names for the types selected */
39 CASE type
40 WHEN 2 THEN "UserTable"
41 WHEN 25 THEN "ForeignKey"
42 ELSE "UnexpectedType"
43 END AS type,
44
45 /* Database is first part of fq_name for user tables */
46 CASE type
47 WHEN 2 THEN SUBSTRING_INDEX(fq_name, "/", 1)
48 ELSE ""
49 END AS _database,
50
51 /* Table name is last part of fq_name for user tables and fq_name for fk */
52 CASE type
53 WHEN 2 THEN SUBSTRING_INDEX(fq_name, "/", -1)
54 ELSE fq_name
55 END AS name
56 FROM ndbinfo.dict_obj_info WHERE type IN ( 2 /* User table */,
57 25 /* Foreign key definition */);
58
59 # Find the object id of the table whose FKs we have to list and verify
60 let $ndb_table_id= `SELECT id FROM ndb_object_list
61 WHERE type = "UserTable"
62 AND _database = "$ndb_db_name"
63 AND name = "$ndb_table_name"`;
64
65 # Now list all the foreign key names using this table id.
66 # They will be of form <parent_id>/<$table_id(child)>/fk_name
67 # Also extract the parent table name to verify it with the ones stored in DD
68 CREATE TABLE test.ndb_fk_info (name VARCHAR(255), parent VARCHAR(255)) engine ndb;
69 eval INSERT INTO test.ndb_fk_info
70 SELECT REGEXP_REPLACE(fk_info.name, '[0-9]+/$ndb_table_id/', ''), parent_info.name
71 FROM test.ndb_object_list as fk_info, test.ndb_object_list as parent_info
72 WHERE fk_info.type = 'ForeignKey'
73 AND fk_info.name LIKE '%/$ndb_table_id/%'
74 AND parent_info.type = 'UserTable'
75 AND parent_info.id = REGEXP_REPLACE(fk_info.name, '/$ndb_table_id/.+', '');
76
77 let $ndb_fk_count= `SELECT count(*) FROM ndb_fk_info`;
78
79 # Verify that the same set of FKs are present in NDB and DD
80 # Do left and right outer joins to detect if either DD or NDB has
81 # any foreign key that does not have a match yet.
82 let $fks_in_ndb_but_not_in_dd=
83 `SELECT COUNT(*)
84 FROM test.ndb_fk_info as ndb
85 LEFT JOIN information_schema.referential_constraints as dd
86 ON ndb.name = dd.constraint_name
87 WHERE dd.constraint_name is NULL`;
88 let $fks_in_dd_but_not_in_ndb=
89 `SELECT COUNT(*)
90 FROM test.ndb_fk_info as ndb
91 RIGHT JOIN information_schema.referential_constraints as dd
92 ON ndb.name = dd.constraint_name
93 WHERE dd.constraint_schema='$ndb_db_name'
94 AND dd.table_name='$ndb_table_name'
95 AND ndb.name is NULL`;
96
97 if ($fks_in_ndb_but_not_in_dd != 0 ||
98 $fks_in_dd_but_not_in_ndb != 0) {
99 # There is a foreign key mismatch. Print the list of
100 # foreign keys in DD and NDB to help debug.
101 eval SELECT name
102 AS 'Foreign Keys in NDB on table `$ndb_db_name.$ndb_table_name`'
103 FROM ndb_fk_info ORDER BY name ASC;
104
105 eval SELECT constraint_name
106 AS 'Foreign Keys in DD on table `$ndb_db_name.$ndb_table_name`'
107 FROM information_schema.referential_constraints
108 WHERE constraint_schema='$ndb_db_name'
109 AND table_name='$ndb_table_name'
110 ORDER BY constraint_name ASC;
111 --die Foreign keys mismatch in NDB and DD
112 }
113
114 # Successfully verified
115 if ($ndb_fk_count == 0) {
116 --echo No foreign keys found on table `$ndb_db_name.$ndb_table_name`
117 }
118 if ($ndb_fk_count > 0) {
119 --echo Foreign keys consistent across NDB and DD
120 }
121
122 # Drop the info table and the view
123 DROP TABLE test.ndb_fk_info;
124 DROP VIEW test.ndb_object_list;
125
126 --enable_query_log
127
128 # Reset input parameters
129 --let $ndb_db_name=
130 --let $ndb_table_name=
131