Paweł Domański
115 lines
3.0 KiB
SQL
115 lines
3.0 KiB
SQL
DELIMITER $$
|
|
|
|
CREATE PROCEDURE archive_last_days_dynamic(
|
|
IN p_source_table VARCHAR(64),
|
|
IN p_archive_table VARCHAR(64),
|
|
IN p_date_column VARCHAR(64),
|
|
IN p_days_back INT,
|
|
IN p_batch_size INT
|
|
)
|
|
BEGIN
|
|
DECLARE done INT DEFAULT FALSE;
|
|
DECLARE rows_affected INT DEFAULT 0;
|
|
DECLARE total_rows INT DEFAULT 0;
|
|
|
|
DECLARE insert_sql VARCHAR(4000);
|
|
DECLARE delete_sql VARCHAR(4000);
|
|
|
|
-- Validation
|
|
IF p_source_table IS NULL OR p_source_table = '' THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Source table name is required';
|
|
END IF;
|
|
|
|
IF p_archive_table IS NULL OR p_archive_table = '' THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Archive table name is required';
|
|
END IF;
|
|
|
|
IF p_date_column IS NULL OR p_date_column = '' THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Date column name is required';
|
|
END IF;
|
|
|
|
IF p_days_back IS NULL OR p_days_back <= 0 THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Days back must be greater than 0';
|
|
END IF;
|
|
|
|
-- If batch size is NULL or <= 0, run once without LIMIT
|
|
IF p_batch_size IS NULL OR p_batch_size <= 0 THEN
|
|
|
|
SET insert_sql = CONCAT(
|
|
'INSERT INTO `', p_archive_table, '` ',
|
|
'SELECT * FROM `', p_source_table, '` ',
|
|
'WHERE `', p_date_column, '` <= NOW() - INTERVAL ', p_days_back, ' DAY'
|
|
);
|
|
|
|
SET delete_sql = CONCAT(
|
|
'DELETE FROM `', p_source_table, '` ',
|
|
'WHERE `', p_date_column, '` <= NOW() - INTERVAL ', p_days_back, ' DAY'
|
|
);
|
|
|
|
START TRANSACTION;
|
|
|
|
SET @insert_sql = insert_sql;
|
|
PREPARE stmt_insert FROM @insert_sql;
|
|
EXECUTE stmt_insert;
|
|
SET rows_affected = ROW_COUNT();
|
|
SET total_rows = rows_affected;
|
|
DEALLOCATE PREPARE stmt_insert;
|
|
|
|
SET @delete_sql = delete_sql;
|
|
PREPARE stmt_delete FROM @delete_sql;
|
|
EXECUTE stmt_delete;
|
|
DEALLOCATE PREPARE stmt_delete;
|
|
|
|
COMMIT;
|
|
|
|
ELSE
|
|
REPEAT
|
|
SET insert_sql = CONCAT(
|
|
'INSERT INTO `', p_archive_table, '` ',
|
|
'SELECT * FROM `', p_source_table, '` ',
|
|
'WHERE `', p_date_column, '` <= NOW() - INTERVAL ', p_days_back, ' DAY ',
|
|
'LIMIT ', p_batch_size
|
|
);
|
|
|
|
SET delete_sql = CONCAT(
|
|
'DELETE FROM `', p_source_table, '` ',
|
|
'WHERE `', p_date_column, '` <= NOW() - INTERVAL ', p_days_back, ' DAY ',
|
|
'LIMIT ', p_batch_size
|
|
);
|
|
|
|
START TRANSACTION;
|
|
|
|
SET @insert_sql = insert_sql;
|
|
PREPARE stmt_insert FROM @insert_sql;
|
|
EXECUTE stmt_insert;
|
|
SET rows_affected = ROW_COUNT();
|
|
SET total_rows = total_rows + rows_affected;
|
|
DEALLOCATE PREPARE stmt_insert;
|
|
|
|
IF rows_affected > 0 THEN
|
|
SET @delete_sql = delete_sql;
|
|
PREPARE stmt_delete FROM @delete_sql;
|
|
EXECUTE stmt_delete;
|
|
DEALLOCATE PREPARE stmt_delete;
|
|
END IF;
|
|
|
|
COMMIT;
|
|
|
|
IF rows_affected < p_batch_size THEN
|
|
SET done = TRUE;
|
|
END IF;
|
|
|
|
UNTIL done END REPEAT;
|
|
|
|
END IF;
|
|
|
|
SELECT 'ok' AS status, total_rows AS total_rows_moved;
|
|
|
|
END $$
|
|
|
|
DELIMITER ;
|
|
|
|
|
|
//USE
|
|
CALL archive_last_days_dynamic('source_table', 'archive_table', 'created_at', 30, 500);
|