Removing duplicate events from the database

This section is to be considered experimental / work in progress. No guarantees are given about it's accuracy. !!! CONTINUE AT YOUR OWN RISK !!!

From time to time duplicate events can occur. The exact cause has not yet been identified, however. Until such time when the cause has been addressed the only option is to remove the duplicate entries either manually or via the commands below. A report showing potential duplicate events is available under the reporting section.

Before running any of the commands below take a backup of your database. After completing the backup process access the database using the instructions from here Accessing H2 Database. Create a view of all possible duplicate events

CREATE VIEW DUPLICATEEVENTS AS SELECT SUMMARY, STARTDATE, COUNT(*) AS COUNT, MIN (IDENTIFIER) AS FIRSTID FROM EVENTS
WHERE CHANGETYPE!=2
GROUP BY SUMMARY,STARTDATE
HAVING COUNT > 1
ORDER BY COUNT DESC;

Display the view

SELECT * FROM DUPLICATEEVENTS;

Verify that you happy that the duplications are correct continue setting the deleted status (changetype = 2) on all linked events. This statement will take a several minutes to run depending on the number of records in your database.During testing this required close to 30 minutes to execute on the test system.

UPDATE EVENTS SET CHANGETYPE=2 WHERE IDENTIFIER IN (SELECT IDENTIFIER FROM EVENTS 
JOIN DUPLICATEEVENTS ON
DUPLICATEEVENTS.SUMMARY = EVENTS.SUMMARY AND 
DUPLICATEEVENTS.STARTDATE = EVENTS.STARTDATE AND
DUPLICATEEVENTS.FIRSTID != EVENTS.IDENTIFIER 
WHERE EVENTS.CHANGETYPE!=2);

If you only want to delete events with more then X number of duplicate entries use the following statement

UPDATE EVENTS SET CHANGETYPE=2 WHERE IDENTIFIER IN (SELECT IDENTIFIER FROM EVENTS 
JOIN DUPLICATEEVENTS ON
DUPLICATEEVENTS.SUMMARY = EVENTS.SUMMARY AND 
DUPLICATEEVENTS.STARTDATE = EVENTS.STARTDATE AND
DUPLICATEEVENTS.FIRSTID != EVENTS.IDENTIFIER 
WHERE EVENTS.CHANGETYPE!=2 AND DUPLICATEEVENTS.COUNT>X);

Replace X with the maximum number of duplications allowed.

Then update any related records linked to the deleted events as well

UPDATE ATTENDEE SET CHANGETYPE = 2 WHERE ATTENDEE.EVENTIDENTIFIER IN (SELECT IDENTIFIER FROM EVENTS WHERE CHANGETYPE=2) AND CHANGETYPE != 2;
UPDATE AUDIOALARM SET CHANGETYPE = 2 WHERE AUDIOALARM.EVENTIDENTIFIER IN (SELECT IDENTIFIER FROM EVENTS WHERE CHANGETYPE=2) AND CHANGETYPE != 2;
UPDATE MAILALARM SET CHANGETYPE = 2 WHERE MAILALARM.EVENTIDENTIFIER IN (SELECT IDENTIFIER FROM EVENTS WHERE CHANGETYPE=2) AND CHANGETYPE != 2;
UPDATE RECURRENCE SET CHANGETYPE = 2 WHERE RECURRENCE.EVENTIDENTIFIER IN (SELECT IDENTIFIER FROM EVENTS WHERE CHANGETYPE=2) AND CHANGETYPE != 2;
UPDATE DISPLAYALARM SET CHANGETYPE = 2 WHERE DISPLAYALARM.EVENTIDENTIFIER IN (SELECT IDENTIFIER FROM EVENTS WHERE CHANGETYPE=2) AND CHANGETYPE != 2;
UPDATE ORGANIZER SET CHANGETYPE = 2 WHERE ORGANIZER.EVENTIDENTIFIER IN (SELECT IDENTIFIER FROM EVENTS WHERE CHANGETYPE=2) AND CHANGETYPE != 2;

Delete the previously created view

DROP VIEW DUPLICATEEVENTS;