Revision of remove-old-campaigns.sql

1 file changed, 34 insertions

remove-old-campaigns.sql(file created)

		@@ -0,0 +1,34 @@
1	+	-- Remove campaigns older than 6 months from phpList including all stats.
2	+	-- Start transaction
3	+	START TRANSACTION;
4	+
5	+	-- Delete user-message links for campaigns older than 6 months
6	+	DELETE FROM phplist_usermessage
7	+	WHERE messageid IN (
8	+	SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW()
9	+	);
10	+
11	+	-- Delete link tracking data for campaigns older than 6 months
12	+	DELETE FROM phplist_linktrack
13	+	WHERE messageid IN (
14	+	SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW()
15	+	);
16	+
17	+	-- Delete bounce data for campaigns older than 6 months
18	+	DELETE FROM phplist_bounce
19	+	WHERE messageid IN (
20	+	SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW()
21	+	);
22	+
23	+	-- Delete event log entries for campaigns older than 6 months
24	+	DELETE FROM phplist_eventlog
25	+	WHERE messageid IN (
26	+	SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW()
27	+	);
28	+
29	+	-- Delete the campaigns themselves that are older than 6 months
30	+	DELETE FROM phplist_message
31	+	WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW();
32	+
33	+	-- Commit the transaction
34	+	COMMIT;

Newer Older