Modify confirmation code cleanup script
@@ -11,6 +11,7 @@ BEGIN
DECLARE @rowCount INT
DECLARE @enableLog BIT
DECLARE @backupTables BIT
+ DECLARE @codeCleanupTime bigint;
-- ------------------------------------------
-- CONFIGURABLE VARIABLES
@@ -18,19 +19,29 @@ BEGIN
SET @batchSize = 10000 -- SET BATCH SIZE FOR AVOID TABLE LOCKS [DEFAULT : 10000]
SET @chunkSize = 500000 -- CHUNK WISE DELETE FOR LARGE TABLES [DEFAULT : 500000]
SET @enableLog = 0 -- ENABLE LOGGING [DEFAULT : 0]
- SET @backupTables = 0 -- SET IF TOKEN TABLE NEEDS TO BACKUP BEFORE DELETE [DEFAULT : 0], WILL DROP THE PREVIOUS BACKUP TABLES IN NEXT ITERATION
+ SET @backupTables = 0 -- SET IF RECOVERY DATA TABLE NEEDS TO BACKUP BEFORE DELETE [DEFAULT : 0], WILL DROP THE PREVIOUS BACKUP TABLES IN NEXT ITERATION
+ -- Confirmation codes older than 20160 minutes(14 days) will be removed.
+ SET @codeCleanupTime = cast((DATEDIFF_BIG(millisecond, '1970-01-01 00:00:00', GETUTCDATE()) - (1209600000))AS DECIMAL) * 1000000
SET @rowCount = 0
SET @batchCount = 1
SET @chunkCount = 1
+ IF (@enableLog = 1)
+ BEGIN
SELECT 'WSO2_CONFIRMATION_CODE_CLEANUP() STARTED...!' AS 'INFO LOG'
+ END;
-- ------------------------------------------
-- BACKUP DATA
-- ------------------------------------------
IF (@backupTables = 1)
BEGIN
+ IF (@enableLog = 1)
+ BEGIN
+ SELECT 'TABLE BACKUP STARTED ... !' AS 'INFO LOG';
+ END;
+
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'BAK_IDN_RECOVERY_DATA'))
BEGIN
IF (@enableLog = 1)
@@ -45,12 +56,13 @@ BEGIN
-- ------------------------------------------
-- CLEANUP DATA
-- ------------------------------------------
+
WHILE (@chunkCount > 0)
BEGIN
-- CREATE CHUNK TABLE
DROP TABLE IF EXISTS IDN_RECOVERY_DATA_CHUNK_TMP
CREATE TABLE IDN_RECOVERY_DATA_CHUNK_TMP(CODE VARCHAR (255))
- INSERT INTO IDN_RECOVERY_DATA_CHUNK_TMP(CODE) SELECT TOP (@chunkSize) CODE FROM IDN_RECOVERY_DATA
+ INSERT INTO IDN_RECOVERY_DATA_CHUNK_TMP(CODE) SELECT TOP (@chunkSize) CODE FROM IDN_RECOVERY_DATA where TIME_CREATED < @codeCleanupTime;
SET @chunkCount = @@ROWCOUNT
CREATE INDEX IDN_RECOVERY_DATA_CHUNK_TMP_INDX on IDN_RECOVERY_DATA_CHUNK_TMP (CODE)
IF (@enableLog = 1)
