B7Dhp1tZ

1DROP PROCEDURE IF EXISTS delete_synced_events_orphans;
2DELIMITER $$
3CREATE PROCEDURE delete_synced_events_orphans()
4BEGIN 
5
6    DECLARE event_count int(11) DEFAULT 0;
7    DECLARE active_event_id int(11) DEFAULT 0;
8    DECLARE active_venue_id int(11) DEFAULT 0;
9    DECLARE event_to_delete_id int(11) DEFAULT NULL;
10
11    CREATE TEMPORARY TABLE IF NOT EXISTS possible_events_to_delete (
12        event_id int(11) NOT NULL,
13        venue_id_temp int(11) NOT NULL
14    ) engine = memory;
15
16    # create an "array" which is a table that holds the events that might need deleting
17    INSERT INTO possible_events_to_delete (event_id, venue_id_temp) SELECT `events`.`id`, `events`.`venue_id` FROM `events` WHERE `events`.`venue_id` IS NOT NULL AND `events`.`venue_id` <> 0;
18    SELECT COUNT(*) INTO `event_count` FROM `possible_events_to_delete` WHERE 1;
19
20    detector_loop: WHILE `event_count` > 0 DO
21        SELECT event_id INTO active_event_id FROM possible_events_to_delete WHERE 1 LIMIT 1;
22        SELECT venue_id_temp INTO active_venue_id FROM possible_events_to_delete WHERE 1 LIMIT 1;
23
24        # this figures out if there are events that need to be deleted
25        SELECT `events`.`id` INTO event_to_delete_id FROM `events`, `venues` WHERE `events`.`venue_id` <> `venues`.`id` AND `events`.`id` = active_event_id AND `events`.`venue_id` = active_venue_id;
26
27        #if no record meets that query, the active event is safe to delete
28        IF (event_to_delete_id <> 0 AND event_to_delete_id IS NOT NULL) THEN
29            DELETE FROM `events` WHERE `events`.`id` = event_to_delete_id;
30            #INSERT INTO test_table (event_id_test, venue_id_temp_test) SELECT `events`.`id`, `events`.`venue_id` FROM `events` WHERE `events`.`id` =  event_to_delete_id;
31        END IF;
32
33        DELETE FROM possible_events_to_delete WHERE `event_id` = active_event_id AND `venue_id_temp` = active_venue_id;
34        SET `event_count` = `event_count` - 1;
35
36    END WHILE;
37
38END $$
39DELIMITER ;
40	
41CREATE TABLE IF NOT EXISTS events (
42    id int(11) NOT NULL,
43    event_time timestamp NOT NULL,
44    venue_id_temp int(11) NOT NULL
45);
46
47CREATE TABLE IF NOT EXISTS venues (
48    event_id int(11) NOT NULL,
49    venue_id_temp int(11) NOT NULL
50);