· 6 years ago · Jan 30, 2020, 12:14 PM
1-- CRS CRS-2018
2-- DocumentCleanupJob Stored procedure
3
4DROP PROCEDURE IF EXISTS DocumentCleanupJob;;
5CREATE PROCEDURE DocumentCleanupJob (ticket_expiration_period INT, ticket_cool_period INT, document_qa_period INT, document_cool_ticket_period INT)
6BEGIN
7SET SESSION tx_isolation='READ-COMMITTED';
8DROP TABLE IF EXISTS DocumentCleanup;
9CREATE TABLE DocumentCleanup (Chng_ID int(10), T_Number int(10),
10 D_Name varchar(66) ,
11 T_Created_TS timestamp null DEFAULT CURRENT_TIMESTAMP,
12 INDEX (`D_Name`)) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_irish_accent_ci;
13INSERT INTO DocumentCleanup
14SELECT THX.Chng_ID AS Chng_ID, THX.T_Number, THX.D_Name,
15THX.T_Created_TS FROM TicketHist THX JOIN
16(SELECT MAX(THP.Chng_ID) AS max_id, THP.T_Number FROM TicketHist THP GROUP BY THP.T_Number) THY
17ON THY.T_Number = THX.T_Number AND THY.max_id = THX.Chng_ID
18-- LEFT JOIN
19-- (SELECT DomainHist.D_Name, DomainHist.D_Holder, MAX(SMBRH.Chng_TS) max_Chng_TS FROM SecondaryMarketBuyRequestHist SMBRH
20-- LEFT JOIN DomainHist on Domain_Chng_ID = DomainHist.Chng_ID
21-- WHERE SMBRH.Status = 'Sold'
22-- GROUP BY DomainHist.D_Name, DomainHist.D_Holder) SMBRHD ON THX.D_Name = SMBRHD.D_Name AND THX.D_Holder = SMBRHD.D_Holder
23WHERE ( (THX.Overall_Status IN ('Cancelled','Expired') AND datediff(NOW(),THX.O_Status_TS) > document_cool_ticket_period)
24 OR (THX.Overall_Status = 'Passed'
25 -- AND datediff(NOW(),IF(max_Chng_TS IS NOT NULL AND SMBRHD.max_Chng_TS > THX.O_Status_TS ,SMBRHD.max_Chng_TS, THX.O_Status_TS) ) > document_qa_period)
26 -- next line is replacement for previous
27 AND datediff(NOW(),THX.O_Status_TS) > document_qa_period)
28 )
29 AND THX.T_Type IN ('Registration', 'Modification');
30-- additional insert orphans
31
32INSERT INTO DocumentCleanup
33SELECT 0, 0, DOMAIN_NAME, DomainHist.D_Reg_TS FROM INCOMING_DOC_DOMAINS, DomainHist
34 LEFT JOIN TicketHist ON (TicketHist.D_Name = DomainHist.D_Name)
35WHERE TicketHist.D_Name is null AND datediff(CURDATE(),DomainHist.D_Reg_TS)> document_qa_period AND
36 INCOMING_DOC_DOMAINS.DOMAIN_NAME = DomainHist.D_Name;
37INSERT INTO DocumentCleanup
38SELECT 0,0, DOMAIN_NAME, now() FROM INCOMING_DOC_DOMAINS WHERE INCOMING_DOC_DOMAINS.DOMAIN_NAME NOT IN (
39 SELECT DISTINCT A.D_Name FROM (
40 SELECT DISTINCT TicketHist.D_Name FROM TicketHist
41 UNION ALL
42 SELECT DISTINCT DomainHist.D_Name FROM DomainHist) AS A
43);
44
45-- pre delete
46
47DELETE DocumentCleanup FROM DocumentCleanup LEFT JOIN INCOMING_DOC_DOMAINS ON DocumentCleanup.D_Name = INCOMING_DOC_DOMAINS.DOMAIN_NAME WHERE INCOMING_DOC_DOMAINS.DOMAIN_NAME IS NULL;
48
49-- multiple Overal_Statuss fix
50
51DELETE DC FROM DocumentCleanup DC JOIN (SELECT T_Number FROM TicketHist
52 WHERE Overall_Status IS NOT NULL
53 GROUP BY T_Number
54 HAVING COUNT(DISTINCT Overall_Status) >1) DB ON DC.T_Number = DB.T_Number;
55
56-- this has to be removed after 2237 fix
57DELETE DocumentCleanup FROM DocumentCleanup
58LEFT JOIN INCOMING_DOC_DOMAINS ON DocumentCleanup.D_Name = INCOMING_DOC_DOMAINS.DOMAIN_NAME
59LEFT JOIN INCOMING_DOC ON INCOMING_DOC.DOC_ID = INCOMING_DOC_DOMAINS.DOC_ID
60WHERE INCOMING_DOC.BuyRequestId IS NOT NULL;
61
62-- deletion table
63DROP TABLE IF EXISTS DocumentCleanup_Delete;
64
65CREATE TABLE DocumentCleanup_Delete(Id int(10), Domain varchar(255));
66INSERT INTO DocumentCleanup_Delete
67 SELECT INCOMING_DOC_DOMAINS.Id, INCOMING_DOC_DOMAINS.DOMAIN_NAME Domain FROM DocumentCleanup, INCOMING_DOC_DOMAINS
68 WHERE DocumentCleanup.D_Name = INCOMING_DOC_DOMAINS.DOMAIN_NAME
69 UNION ALL
70 SELECT IDD.Id, IDD.DOMAIN_NAME Domain FROM INCOMING_DOC_DOMAINS IDD
71 JOIN INCOMING_DOC ID ON IDD.DOC_ID = ID.DOC_ID
72 WHERE datediff(NOW(),Create_TS) > (ticket_expiration_period + document_qa_period)
73 UNION ALL
74 SELECT IDD.Id, IDD.DOMAIN_NAME Domain FROM INCOMING_DOC_DOMAINS IDD
75 JOIN INCOMING_DOC ID ON IDD.DOC_ID = ID.DOC_ID
76 JOIN SecondaryMarketBuyRequestHist SMBRH ON ID.BuyRequestId = SMBRH.id
77 WHERE (SMBRH.Status IN ('Cancelled','Expired') AND datediff(NOW(),SMBRH.Chng_TS) > document_cool_ticket_period)
78 OR (SMBRH.Status = 'Sold' AND datediff(NOW(),SMBRH.Chng_TS) > document_qa_period);
79
80ALTER TABLE DocumentCleanup_Delete ADD INDEX (Id);
81
82
83DELETE INCOMING_DOC_DOMAINS FROM DocumentCleanup_Delete, INCOMING_DOC_DOMAINS
84 WHERE DocumentCleanup_Delete.Id = INCOMING_DOC_DOMAINS.Id ;
85
86-- log
87
88SELECT DISTINCT CONCAT(Id, '-',Domain) Ticket_Domain from DocumentCleanup_Delete;
89
90SET SESSION tx_isolation='REPEATABLE-READ';
91
92END;;
93
94DROP PROCEDURE IF EXISTS BuyAndSellRequestAnonymiseJob;;
95CREATE PROCEDURE BuyAndSellRequestAnonymiseJob (smbr_expiration_period INT, smbr_cool_period INT, smsr_expiration_period INT, smsr_cool_period INT, id_PIID INT, account_chng_PIID INT, secondary_market_authcode_expiration_period INT)
96BEGIN
97SET SESSION tx_isolation='READ-COMMITTED';
98
99-- anonymise unsucessful
100
101SELECT Nic_Handle, NH_Name, NH_Email, NH_Address
102 FROM NicHandleHist where Chng_ID = id_PIID INTO @Nic_Handle, @NH_Name, @NH_Email, @NH_Address;
103
104
105SELECT CONCAT(SMSRH.Id, '-SMSRH') AS Id FROM `SecondaryMarketSellRequestHist` SMSRH
106 INNER JOIN `SecondaryMarketSellRequestHist` SMSRH2 ON SMSRH.Id = SMSRH2.Id
107 WHERE SMSRH2.Status IN ('Cancelled', 'Expired') AND datediff(NOW(), SMSRH2.Chng_TS) > smsr_cool_period AND SMSRH2.`Chng_NH`!=@Nic_Handle
108UNION ALL
109SELECT CONCAT(SMBRH.Id, '-SMBRH') AS Id FROM `SecondaryMarketBuyRequestHist` SMBRH
110 INNER JOIN `SecondaryMarketBuyRequestHist` SMBRH2 ON SMBRH.Id = SMBRH2.Id
111
112 WHERE SMBRH2.Status IN ('Cancelled', 'Expired') AND datediff(NOW(), SMBRH2.Chng_TS) > smbr_cool_period AND SMBRH2.`Chng_NH`!=@Nic_Handle;
113
114 UPDATE `SecondaryMarketSellRequestHist` SMSRH
115 INNER JOIN `SecondaryMarketSellRequestHist` SMSRH2 ON SMSRH.Id = SMSRH2.Id
116 SET
117 `SMSRH`.`Chng_NH`=@Nic_Handle,
118 `SMSRH`.`Creator_NH_Chng_ID`= id_PIID
119 WHERE SMSRH2.Status IN ('Cancelled', 'Expired') AND datediff(NOW(), SMSRH2.Chng_TS) > smsr_cool_period AND SMSRH2.`Chng_NH`!=@Nic_Handle;
120
121
122 UPDATE `SecondaryMarketBuyRequestHist` SMBRH
123 INNER JOIN `SecondaryMarketBuyRequestHist` SMBRH2 ON SMBRH.Id = SMBRH2.Id
124 SET
125 `SMBRH`.`Chng_NH`=@Nic_Handle,
126 `SMBRH`.`D_Holder`='PII Purged',
127 `SMBRH`.`Remark`=NULL,
128 `SMBRH`.`H_Remark`=NULL,
129 `SMBRH`.`Admin_Name`=@NH_Name,
130 `SMBRH`.`Admin_Email`=@NH_Email,
131 `SMBRH`.`Admin_Co_Name`=@NH_Name,
132 `SMBRH`.`Creator_NH_Chng_ID`= id_PIID,
133 `SMBRH`.`Account_Chng_ID`= account_chng_PIID,
134 `SMBRH`.`Admin_Address` = @NH_Address
135 WHERE SMBRH2.Status IN ('Cancelled', 'Expired') AND datediff(NOW(), SMBRH2.Chng_TS) > smbr_cool_period AND SMBRH2.`Chng_NH`!=@Nic_Handle;
136
137
138
139SET SESSION tx_isolation='REPEATABLE-READ';
140END;;
141
142DROP PROCEDURE IF EXISTS BuyAndSellRequestCleanUpJob;;
143CREATE PROCEDURE BuyAndSellRequestCleanUpJob (smbr_expiration_period INT, smbr_cool_period INT, smbr_history_retention_period INT, domain_cool_period INT, secondary_market_authcode_expiration_period INT, smsr_expiration_period INT, smsr_cool_period INT, smsr_history_retention_period INT)
144BEGIN
145SET SESSION tx_isolation='READ-COMMITTED';
146
147DROP TABLE IF EXISTS BuyAndSellRequestCleanUp_Delete;
148
149CREATE TABLE BuyAndSellRequestCleanUp_Delete(Id int(10), Source varchar(5));
150
151INSERT INTO BuyAndSellRequestCleanUp_Delete
152
153SELECT DISTINCT Id, Source FROM (
154SELECT SMSRH.Chng_ID Id, 'SMSRH' Source
155 FROM SecondaryMarketSellRequestHist SMSRH
156 JOIN SecondaryMarketBuyRequestHist SMBRH on BuyRequest_Chng_ID = SMBRH.Chng_ID
157 JOIN DomainHist DH ON Domain_Chng_ID = DH.Chng_ID
158 LEFT JOIN (SELECT MAX(DH.D_Del_Dt) D_Del_Dt, DH.D_Name, MAX(Chng_Ts) max_Chng_Ts FROM DomainHist DH
159 LEFT JOIN Domain D on D.D_Name = DH.D_Name
160 WHERE D.D_Name IS NULL AND DH.D_Del_Dt IS NOT NULL GROUP BY D_Name) D2
161 ON DH.D_Name=D2.D_Name
162 WHERE D2.D_Del_Dt IS NOT NULL AND DATEDIFF(NOW(), D2.max_Chng_Ts) > domain_cool_period
163 UNION ALL
164SELECT SMSRH.Chng_ID Id, 'SMSRH' Source
165 FROM SecondaryMarketSellRequestHist SMSRH
166 LEFT JOIN SecondaryMarketSellRequestHist SMSRH2 ON SMSRH.ID = SMSRH2.ID
167 WHERE SMSRH2.Status = 'Cancelled' AND DATEDIFF(NOW(), SMSRH2.Chng_TS)> smsr_cool_period + smsr_history_retention_period
168) A
169UNION ALL
170SELECT DISTINCT Id, Source FROM (
171SELECT SMBRH.Id, 'SMBRH' Source FROM `SecondaryMarketBuyRequestHist` SMBRH
172 INNER JOIN `SecondaryMarketBuyRequestHist` SMBRH2 ON SMBRH.ID = SMBRH2.ID
173 LEFT JOIN (SELECT ID FROM `SecondaryMarketBuyRequestHist` WHERE Status = 'Passed') SMBRHP ON SMBRH.ID = SMBRHP.ID
174 WHERE SMBRH2.Status in('Cancelled', 'Expired') AND datediff(NOW(),SMBRH2.Chng_TS) > smbr_cool_period + IF(SMBRHP.ID IS NULL, smbr_history_retention_period, secondary_market_authcode_expiration_period)
175 UNION ALL
176 SELECT SMBRH.Id, 'SMBRH' Source FROM SecondaryMarketBuyRequestHist SMBRH
177 LEFT JOIN DomainHist DH on Domain_Chng_ID = DH.Chng_ID
178 LEFT JOIN (SELECT MAX(DH.D_Del_Dt) D_Del_Dt, DH.D_Name, MAX(Chng_Ts) max_Chng_Ts FROM DomainHist DH
179 LEFT JOIN Domain D on D.D_Name = DH.D_Name
180 WHERE D.D_Name IS NULL AND DH.D_Del_Dt IS NOT NULL GROUP BY DH.D_Name) D2
181 ON DH.D_Name=D2.D_Name
182 WHERE D2.D_Del_Dt IS NOT NULL AND DATEDIFF(NOW(), D2.max_Chng_Ts) > domain_cool_period
183) B;
184ALTER TABLE BuyAndSellRequestCleanUp_Delete ADD INDEX (Id,Source);
185
186DELETE SMSRH
187 FROM SecondaryMarketSellRequestHist SMSRH, BuyAndSellRequestCleanUp_Delete
188 WHERE BuyAndSellRequestCleanUp_Delete.Id = SMSRH.Chng_ID AND BuyAndSellRequestCleanUp_Delete.Source = 'SMSRH';
189
190DELETE SMBRH
191 FROM SecondaryMarketBuyRequestHist SMBRH, BuyAndSellRequestCleanUp_Delete
192 WHERE BuyAndSellRequestCleanUp_Delete.Id = SMBRH.Id AND BuyAndSellRequestCleanUp_Delete.Source = 'SMBRH';
193
194-- log
195
196SELECT DISTINCT CONCAT(Id, '-', Source) Id from BuyAndSellRequestCleanUp_Delete;
197
198SET SESSION tx_isolation='REPEATABLE-READ';
199END;;
200
201DROP PROCEDURE IF EXISTS TicketHistCleanupJob;;
202CREATE PROCEDURE TicketHistCleanupJob (ticket_expiration_period INT, ticket_cool_period INT, ticket_retention_period INT, domain_cool_period INT)
203BEGIN
204SET SESSION tx_isolation='READ-COMMITTED';
205DROP TABLE IF EXISTS TicketHistCleanup;
206CREATE TABLE TicketHistCleanup (T_Number int(10), D_Name varchar(66),T_Created_TS timestamp DEFAULT CURRENT_TIMESTAMP, Chng_TS timestamp DEFAULT CURRENT_TIMESTAMP, O_Status_TS timestamp DEFAULT CURRENT_TIMESTAMP, ActionType varchar(9),
207 T_Type varchar(12), Financial_Status longtext, Tech_Status longtext, Overall_Status longtext) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_irish_accent_ci;
208INSERT INTO TicketHistCleanup
209SELECT T_Number, D_Name, T_Created_TS, Chng_TS, O_Status_TS, Overall_Status AS ActionType, T_Type, Financial_Status, Tech_Status, Overall_Status FROM TicketHist TH
210 JOIN (SELECT MAX(Chng_ID) Chng_ID FROM TicketHist GROUP BY T_Number) THP ON THP.Chng_ID = TH.Chng_ID
211 WHERE
212 Overall_Status IN ('Cancelled', 'Expired')
213 -- UNION ALL
214 -- SELECT T_Number, TicketHist.D_Name, T_Created_TS, Chng_TS, 'Sold' AS ActionType, T_Type, 0 AS Financial_Status, 0 AS Tech_Status, 0 AS Overall_Status
215 -- FROM TicketHist
216 -- JOIN Domain D ON D.D_name = TicketHist.D_Name
217 -- WHERE Chng_TS < DATE_SUB(NOW(), INTERVAL ticket_expiration_period DAY)
218 -- AND D.D_Holder != TicketHist.D_Holder
219 UNION ALL
220 SELECT T_Number, TicketHist.D_Name, T_Created_TS, TicketHist.Chng_TS, O_Status_TS, 'Deleted' AS ActionType, T_Type, Financial_Status, Tech_Status, Overall_Status
221 FROM TicketHist
222 JOIN (SELECT MAX(Chng_ID) Chng_ID FROM TicketHist GROUP BY T_Number) THP ON THP.Chng_ID = TicketHist.Chng_ID
223 LEFT JOIN (SELECT MAX(DH.D_Del_Dt) D_Del_Dt, MAX(DH.Chng_Ts) max_Chng_Ts, DH.D_Name
224 FROM DomainHist DH
225 LEFT JOIN Domain D ON D.D_Name = DH.D_Name
226 WHERE D.D_Name IS NULL AND DH.D_Del_Dt IS NOT NULL GROUP BY DH.D_Name) D2
227 ON TicketHist.D_Name=D2.D_Name
228 WHERE Overall_Status IS NOT NULL AND D2.D_Del_Dt IS NOT NULL AND DATEDIFF(NOW(), D2.max_Chng_Ts) > domain_cool_period;
229
230ALTER TABLE TicketHistCleanup ADD INDEX (T_Number);
231ALTER TABLE TicketHistCleanup ADD INDEX (ActionType);
232ALTER TABLE TicketHistCleanup ADD INDEX (T_Type);
233
234DELETE FROM TicketHistCleanup WHERE (ActionType IN ('Cancelled', 'Expired') AND T_Type NOT IN ('Registration','Modification'));
235DELETE FROM TicketHistCleanup WHERE ActionType IN ('Cancelled', 'Expired') AND datediff(CURDATE(), O_Status_TS) <= ticket_cool_period + ticket_retention_period;
236
237DELETE THC FROM TicketHistCleanup THC JOIN (SELECT T_Number FROM TicketHist
238 WHERE Overall_Status IS NOT NULL
239 GROUP BY T_Number
240 HAVING COUNT(DISTINCT Overall_Status) >1) DB ON THC.T_Number = DB.T_Number;
241
242DELETE FROM TicketHistCleanup WHERE ActionType='Sold' AND datediff(CURDATE(),Chng_TS) <= domain_cool_period;
243
244-- DELETE FROM TicketHistCleanup WHERE T_Created_TS < '2019-12-11 17:05:00';
245
246
247DELETE TicketHist,TicketNameserverHist
248FROM (TicketHist LEFT JOIN TicketNameserverHist ON (TicketHist.Chng_ID = TicketNameserverHist.Chng_ID)) ,TicketHistCleanup
249WHERE TicketHist.T_Number=TicketHistCleanup.T_Number;
250
251SELECT * FROM TicketHistCleanup;
252SET SESSION tx_isolation='REPEATABLE-READ';
253END;;
254
255DROP PROCEDURE IF EXISTS TicketAnonymiseJob;;
256CREATE PROCEDURE TicketAnonymiseJob (ticket_expiration_period INT, ticket_cool_period INT, ticket_retention_period INT, dns1Text varchar(66), dns2Text varchar(66), id_PIID INT )
257BEGIN
258SET SESSION tx_isolation='READ-COMMITTED';
259DROP TABLE IF EXISTS TicketAnonymise;
260CREATE TABLE TicketAnonymise (T_Number int(10),
261 D_Name varchar(66),
262 T_Created_TS timestamp DEFAULT CURRENT_TIMESTAMP,
263 Chng_TS timestamp DEFAULT CURRENT_TIMESTAMP,
264 Overall_Status longtext
265 ) ENGINE = InnoDB DEFAULT CHARSET=utf8 COLLATE = utf8_irish_accent_ci;
266
267SELECT Nic_Handle FROM NicHandleHist where Chng_ID = id_PIID INTO @Nic_Handle;
268
269INSERT INTO TicketAnonymise
270 SELECT T_Number, D_Name, T_Created_TS, max(Chng_TS) AS Chng_TS, Overall_Status FROM (
271 SELECT T_Number, TicketHist.D_Name, T_Created_TS, Chng_TS, Overall_Status FROM TicketHist
272 JOIN (SELECT MAX(Chng_ID) Chng_ID FROM TicketHist GROUP BY T_Number) THP ON THP.Chng_ID = TicketHist.Chng_ID
273 WHERE Admin_NH1_Chng_ID != id_PIID AND datediff(NOW(),O_Status_TS) > ticket_cool_period
274 AND Overall_Status IN ('Cancelled', 'Expired') AND T_Type IN ('Registration', 'Modification')
275 ) K
276 GROUP BY T_Number;
277
278
279ALTER TABLE TicketAnonymise ADD INDEX (T_Number);
280ALTER TABLE TicketAnonymise ADD INDEX (D_Name);
281
282DELETE TA FROM TicketAnonymise TA JOIN (SELECT T_Number FROM TicketHist
283 WHERE Overall_Status IS NOT NULL
284 GROUP BY T_Number
285 HAVING COUNT(DISTINCT Overall_Status) >1) DB ON TA.T_Number = DB.T_Number;
286
287
288UPDATE IGNORE TicketNameserverHist SET TN_Name = dns1Text, TN_IPv4 = NULL, TN_IPv6 = NULL
289 WHERE TN_Name != dns1Text AND T_Number IN (SELECT T_Number FROM TicketAnonymise) ;
290
291UPDATE IGNORE TicketNameserverHist set TN_Name = dns2Text, TN_IPv4 = NULL, TN_IPv6 = NULL
292 WHERE TN_Name != dns1text AND T_Number IN (SELECT T_Number FROM TicketAnonymise) ;
293
294DELETE TicketNameserverHist FROM TicketNameserverHist,TicketAnonymise
295 where TN_Name NOT IN (dns1Text,dns2Text) AND TicketNameserverHist.T_Number = TicketAnonymise.T_Number;
296
297
298UPDATE TicketHist set D_Holder='PII Purged', Admin_NH1_Chng_ID = id_PIID, Admin_NH2_Chng_ID=NULL,
299 Tech_NH_Chng_ID = id_PIID, Bill_NH_Chng_ID = id_PIID, Creator_NH_Chng_ID = id_PIID, Chng_NH = @Nic_Handle
300 WHERE T_Number IN (SELECT TicketAnonymise.T_Number FROM TicketAnonymise);
301SELECT * FROM TicketAnonymise;
302SET SESSION tx_isolation='REPEATABLE-READ';
303END;;