tuxRT4uf

1DELIMITER $$
2
3DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS$$
4
5CREATE
6PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`(
7  checked_database_name VARCHAR(64),
8  checked_table_name VARCHAR(64),
9  temporary_result_table ENUM('Y', 'N'))
10
11LANGUAGE SQL
12NOT DETERMINISTIC
13READS SQL DATA
14
15  BEGIN
16    DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
17    DECLARE TABLE_NAME_VAR VARCHAR(64);
18    DECLARE COLUMN_NAME_VAR VARCHAR(64);
19    DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);
20    DECLARE REFERENCED_TABLE_SCHEMA_VAR VARCHAR(64);
21    DECLARE REFERENCED_TABLE_NAME_VAR VARCHAR(64);
22    DECLARE REFERENCED_COLUMN_NAME_VAR VARCHAR(64);
23    DECLARE KEYS_SQL_VAR VARCHAR(1024);
24
25    DECLARE done INT DEFAULT 0;
26
27    DECLARE foreign_key_cursor CURSOR FOR
28      SELECT
29             `TABLE_SCHEMA`,
30             `TABLE_NAME`,
31             `COLUMN_NAME`,
32             `CONSTRAINT_NAME`,
33             `REFERENCED_TABLE_SCHEMA`,
34             `REFERENCED_TABLE_NAME`,
35             `REFERENCED_COLUMN_NAME`
36      FROM
37           information_schema.KEY_COLUMN_USAGE
38      WHERE
39          `CONSTRAINT_SCHEMA` LIKE checked_database_name AND
40          `TABLE_NAME` LIKE checked_table_name AND
41          `REFERENCED_TABLE_SCHEMA` IS NOT NULL;
42
43    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
44
45    IF temporary_result_table = 'N' THEN
46      DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
47      DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
48
49      CREATE TABLE INVALID_FOREIGN_KEYS(
50        `TABLE_SCHEMA` VARCHAR(64),
51        `TABLE_NAME` VARCHAR(64),
52        `COLUMN_NAME` VARCHAR(64),
53        `CONSTRAINT_NAME` VARCHAR(64),
54        `REFERENCED_TABLE_SCHEMA` VARCHAR(64),
55        `REFERENCED_TABLE_NAME` VARCHAR(64),
56        `REFERENCED_COLUMN_NAME` VARCHAR(64),
57        `INVALID_KEY_COUNT` INT,
58        `INVALID_KEY_SQL` VARCHAR(1024)
59      );
60    ELSEIF temporary_result_table = 'Y' THEN
61      DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
62      DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
63
64      CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS(
65        `TABLE_SCHEMA` VARCHAR(64),
66        `TABLE_NAME` VARCHAR(64),
67        `COLUMN_NAME` VARCHAR(64),
68        `CONSTRAINT_NAME` VARCHAR(64),
69        `REFERENCED_TABLE_SCHEMA` VARCHAR(64),
70        `REFERENCED_TABLE_NAME` VARCHAR(64),
71        `REFERENCED_COLUMN_NAME` VARCHAR(64),
72        `INVALID_KEY_COUNT` INT,
73        `INVALID_KEY_SQL` VARCHAR(1024)
74      );
75    END IF;
76
77
78    OPEN foreign_key_cursor;
79    foreign_key_cursor_loop: LOOP
80      FETCH foreign_key_cursor INTO
81        TABLE_SCHEMA_VAR,
82        TABLE_NAME_VAR,
83        COLUMN_NAME_VAR,
84        CONSTRAINT_NAME_VAR,
85        REFERENCED_TABLE_SCHEMA_VAR,
86        REFERENCED_TABLE_NAME_VAR,
87        REFERENCED_COLUMN_NAME_VAR;
88      IF done THEN
89        LEAVE foreign_key_cursor_loop;
90      END IF;
91
92
93      SET @from_part = CONCAT('FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' AS REFERRING ',
94                              'LEFT JOIN `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' AS REFERRED ',
95                              'ON (REFERRING', '.`', COLUMN_NAME_VAR, '`', ' = ', 'REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ') ',
96                              'WHERE REFERRING', '.`', COLUMN_NAME_VAR, '`', ' IS NOT NULL ',
97                              'AND REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ' IS NULL');
98      SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' INTO @invalid_key_count;');
99      PREPARE stmt FROM @full_query;
100
101      EXECUTE stmt;
102      IF @invalid_key_count > 0 THEN
103        INSERT INTO
104        INVALID_FOREIGN_KEYS
105        SET
106            `TABLE_SCHEMA` = TABLE_SCHEMA_VAR,
107            `TABLE_NAME` = TABLE_NAME_VAR,
108            `COLUMN_NAME` = COLUMN_NAME_VAR,
109            `CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR,
110            `REFERENCED_TABLE_SCHEMA` = REFERENCED_TABLE_SCHEMA_VAR,
111            `REFERENCED_TABLE_NAME` = REFERENCED_TABLE_NAME_VAR,
112            `REFERENCED_COLUMN_NAME` = REFERENCED_COLUMN_NAME_VAR,
113            `INVALID_KEY_COUNT` = @invalid_key_count,
114            `INVALID_KEY_SQL` = CONCAT('SELECT ',
115                                       'REFERRING.', '`', COLUMN_NAME_VAR, '` ', 'AS "Invalid: ', COLUMN_NAME_VAR, '", ',
116                                       'REFERRING.* ',
117                                       @from_part, ';');
118      END IF;
119      DEALLOCATE PREPARE stmt;
120
121    END LOOP foreign_key_cursor_loop;
122  END$$
123
124DELIMITER ;
125
126CALL ANALYZE_INVALID_FOREIGN_KEYS('wordpress', 'wordpress.worker_aliexpressoffer', 'N');
127
128
129SELECT * FROM INVALID_FOREIGN_KEYS;