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