1/* Change Id type to BIGINT. */
2set @exist := (SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS    WHERE table_name = 'Events' AND COLUMN_NAME = 'Id' and DATA_TYPE='bigint');
3
4set @sqlstmt := if( @exist = 0, "ALTER TABLE Events MODIFY Id bigint unsigned NOT NULL auto_increment", "SELECT 'Events.Id is already BIGINT'");
5PREPARE stmt FROM @sqlstmt;
6EXECUTE stmt;
7
8/* Add FOREIGN KEYS After deleting lost records */
9set @exist := (select count(*) FROM information_schema.key_column_usage where table_name='Frames' and column_name='EventId' and referenced_table_name='Events' and referenced_column_name='Id');
10
11set @sqlstmt := if( @exist > 1, "SELECT 'You have more than 1 FOREIGN KEY. Please do manual cleanup'", "SELECT 'Ok'");
12set @sqlstmt := if( @exist = 1, "SELECT 'FOREIGN KEY EventId in Frames already exists'", @sqlstmt);
13set @sqlstmt := if( @exist = 0, "SELECT 'Adding foreign key for EventId to Frames'", @sqlstmt);
14PREPARE stmt FROM @sqlstmt;
15EXECUTE stmt;
16
17set @sqlstmt := if( @exist = 0, "SELECT 'Deleting unlinked Frames'", "SELECT '.'");
18PREPARE stmt FROM @sqlstmt;
19EXECUTE stmt;
20set @sqlstmt := if( @exist = 0, "DELETE FROM Frames WHERE EventId NOT IN (SELECT Id FROM Events)", "SELECT '.'");
21PREPARE stmt FROM @sqlstmt;
22EXECUTE stmt;
23set @sqlstmt := if( @exist = 0, "ALTER TABLE Frames ADD FOREIGN KEY (EventId) REFERENCES Events (Id) ON DELETE CASCADE", "SELECT '.'");
24PREPARE stmt FROM @sqlstmt;
25EXECUTE stmt;
26
27
28set @exist := (select count(*) FROM information_schema.key_column_usage where table_name='Stats' and column_name='EventId' and referenced_table_name='Events' and referenced_column_name='Id');
29set @sqlstmt := if( @exist > 1, "SELECT 'You have more than 1 FOREIGN KEY. Please do manual cleanup'", "SELECT 'Ok'");
30set @sqlstmt := if( @exist = 1, "SELECT 'FOREIGN KEY EventId in Stats already exists'", @sqlstmt);
31set @sqlstmt := if( @exist = 0, "SELECT 'Adding FOREIGN KEY for EventId to Stats'", @sqlstmt);
32PREPARE stmt FROM @sqlstmt;
33EXECUTE stmt;
34
35set @sqlstmt := if( @exist = 0, "SELECT 'Deleting unlinked Stats'", "SELECT '.'");
36PREPARE stmt FROM @sqlstmt;
37EXECUTE stmt;
38set @sqlstmt := if( @exist = 0, "DELETE FROM Stats WHERE EventId NOT IN (SELECT Id FROM Events);", "SELECT '.'");
39PREPARE stmt FROM @sqlstmt;
40EXECUTE stmt;
41
42set @sqlstmt := if( @exist = 0, "ALTER TABLE Stats ADD FOREIGN KEY (EventId) REFERENCES Events (Id) ON DELETE CASCADE", "SELECT '.'");
43PREPARE stmt FROM @sqlstmt;
44EXECUTE stmt;
45
46
47set @exist := (select count(*) FROM information_schema.key_column_usage where table_name='Stats' and column_name='MonitorId' and referenced_table_name='Monitors' and referenced_column_name='Id');
48set @sqlstmt := if( @exist > 1, "SELECT 'You have more than 1 FOREIGN KEY. Please do manual cleanup'", "SELECT 'Ok'");
49set @sqlstmt := if( @exist = 1, "SELECT 'FOREIGN KEY for MonitorId in Stats already exists'", @sql_stmt);
50set @sqlstmt := if( @exist = 0, "SELECT 'Adding FOREIGN KEY for MonitorId to Stats'", @sqlstmt);
51PREPARE stmt FROM @sqlstmt;
52EXECUTE stmt;
53
54set @sqlstmt := if( @exist = 0, "SELECT 'Deleting unlinked Stats'", "SELECT '.'");
55PREPARE stmt FROM @sqlstmt;
56EXECUTE stmt;
57set @sqlstmt := if( @exist = 0, "DELETE FROM Stats WHERE MonitorId NOT IN (SELECT Id FROM Monitors);", "SELECT '.'");
58PREPARE stmt FROM @sqlstmt;
59EXECUTE stmt;
60
61set @sqlstmt := if( @exist = 0, "ALTER TABLE Stats ADD FOREIGN KEY (MonitorId) REFERENCES Monitors (Id) ON DELETE CASCADE", "SELECT '.'");
62PREPARE stmt FROM @sqlstmt;
63EXECUTE stmt;
64
65set @exist := (select count(*) FROM information_schema.key_column_usage where table_name='Stats' and column_name='ZoneId' and referenced_table_name='Zones' and referenced_column_name='Id');
66set @sqlstmt := if( @exist > 1, "SELECT 'You have more than 1 FOREIGN KEY. Please do manual cleanup'", "SELECT 'Ok'");
67set @sqlstmt := if( @exist = 1, "SELECT 'FOREIGN KEY for ZoneId in Stats already exists'", @sqlstmt);
68set @sqlstmt := if( @exist = 0, "SELECT 'Adding foreign key for ZoneId to Stats'", @sqlstmt);
69PREPARE stmt FROM @sqlstmt;
70EXECUTE stmt;
71
72set @sqlstmt := if( @exist = 0, "SELECT 'Deleting unlinked Stats'", "SELECT 'Ok'");
73PREPARE stmt FROM @sqlstmt;
74EXECUTE stmt;
75set @sqlstmt := if( @exist = 0, "DELETE FROM Stats WHERE ZoneId NOT IN (SELECT Id FROM Zones);", "SELECT '.'");
76PREPARE stmt FROM @sqlstmt;
77EXECUTE stmt;
78set @sqlstmt := if( @exist = 0, "ALTER TABLE Stats ADD FOREIGN KEY (ZoneId) REFERENCES Zones (Id) ON DELETE CASCADE", "SELECT '.'");
79PREPARE stmt FROM @sqlstmt;
80EXECUTE stmt;
81
82SELECT 'Adding foreign key for MonitorId to Zones';
83set @exist := (select count(*) FROM information_schema.key_column_usage where table_name='Zones' and column_name='MonitorId' and referenced_table_name='Monitors' and referenced_column_name='Id');
84set @sqlstmt := if( @exist > 1, "SELECT 'You have more than 1 FOREIGN KEY. Please do manual cleanup'", "SELECT 'Ok'");
85set @sqlstmt := if( @exist = 1, "SELECT 'FOREIGN KEY for MonitorId in Zones already exists'", @sqlstmnt);
86set @sqlstmt := if( @exist = 0, "SELECT 'Adding foreign key for MonitorId in Zones'", @sqlstmnt);
87
88/*"SELECT 'FOREIGN KEY for MonitorId in Zones does not already exist'");*/
89set @badzones := (select count(*) FROM Zones WHERE MonitorId NOT IN (SELECT Id FROM Monitors));
90set @sqlstmt := if ( @badzones > 0, "SELECT 'You have Zones with no Monitor record in the Monitors table. Please delete them manually'", "ALTER TABLE Zones ADD FOREIGN KEY (MonitorId) REFERENCES Monitors (Id)");
91PREPARE stmt FROM @sqlstmt;
92EXECUTE stmt;
93