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