228 lines
7.4 KiB
SQL
228 lines
7.4 KiB
SQL
-- OnboardX 入职人员与电子签约业务数据清理脚本
|
|
-- 适用数据库：MySQL 8.0+
|
|
--
|
|
-- 会物理删除：
|
|
-- 1. 正式员工档案；
|
|
-- 2. 入职任务及候选人填写、材料、审核、合同快照等任务数据；
|
|
-- 3. 契约锁流程、文档映射、回调及后台预览/盖章事件。
|
|
--
|
|
-- 会保留：
|
|
-- 公司、部门、岗位、入职模板、合同模板及版本、模板适用范围、
|
|
-- 合同变量字典、模板变量、上传资料类型及其他系统基础配置。
|
|
--
|
|
-- 特别注意：
|
|
-- 1. 执行前请先备份数据库；本脚本提交后不可恢复。
|
|
-- 2. 请先在契约锁作废仍在签署中的测试流程，或临时关闭回调；
|
|
-- 本脚本只清理本地数据库，不能删除或作废契约锁平台上的合同。
|
|
-- 3. 本脚本不删除 sys_file/dev_file 等系统文件记录，也不删除 OSS 对象，
|
|
-- 避免误删合同模板源文件或其他模块共用文件。
|
|
-- 4. DELETE 会同时清理正常数据和已经软删除的数据。
|
|
|
|
USE `OnboardX`;
|
|
SET NAMES utf8mb4;
|
|
|
|
DROP PROCEDURE IF EXISTS `ob_reset_onboarding_personnel_and_sign_data`;
|
|
|
|
DELIMITER $$
|
|
|
|
CREATE PROCEDURE `ob_reset_onboarding_personnel_and_sign_data`()
|
|
BEGIN
|
|
DECLARE old_foreign_key_checks INT DEFAULT 1;
|
|
DECLARE finished INT DEFAULT 0;
|
|
DECLARE target_table_name VARCHAR(64);
|
|
DECLARE before_rows BIGINT DEFAULT 0;
|
|
DECLARE after_rows BIGINT DEFAULT 0;
|
|
|
|
-- 只允许删除明确列出的流程数据表。新版本即使增加 ob_ 基础表，也不会被误删。
|
|
DECLARE target_table_cursor CURSOR FOR
|
|
SELECT `TABLE_NAME`
|
|
FROM `information_schema`.`TABLES`
|
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
|
AND `TABLE_TYPE` = 'BASE TABLE'
|
|
AND `TABLE_NAME` IN (
|
|
'ob_electronic_sign_event',
|
|
'ob_electronic_sign_document',
|
|
'ob_electronic_sign_process',
|
|
'ob_employee_attachment',
|
|
'ob_employee_change_record',
|
|
'ob_employee_contract_snapshot',
|
|
'ob_task_operation_log',
|
|
'ob_task_audit',
|
|
'ob_task_material',
|
|
'ob_task_form_value',
|
|
'ob_task_employee_info',
|
|
'ob_task_contract_variable',
|
|
'ob_task_contract',
|
|
'ob_task_link',
|
|
'ob_task_material_requirement',
|
|
'ob_task_form_requirement',
|
|
'ob_onboarding_task',
|
|
'ob_employee'
|
|
)
|
|
ORDER BY FIELD(
|
|
`TABLE_NAME`,
|
|
'ob_electronic_sign_event',
|
|
'ob_electronic_sign_document',
|
|
'ob_electronic_sign_process',
|
|
'ob_employee_attachment',
|
|
'ob_employee_change_record',
|
|
'ob_employee_contract_snapshot',
|
|
'ob_task_operation_log',
|
|
'ob_task_audit',
|
|
'ob_task_material',
|
|
'ob_task_form_value',
|
|
'ob_task_employee_info',
|
|
'ob_task_contract_variable',
|
|
'ob_task_contract',
|
|
'ob_task_link',
|
|
'ob_task_material_requirement',
|
|
'ob_task_form_requirement',
|
|
'ob_onboarding_task',
|
|
'ob_employee'
|
|
);
|
|
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
|
|
|
|
DECLARE EXIT HANDLER FOR SQLEXCEPTION
|
|
BEGIN
|
|
ROLLBACK;
|
|
SET FOREIGN_KEY_CHECKS = old_foreign_key_checks;
|
|
RESIGNAL;
|
|
END;
|
|
|
|
IF LOWER(DATABASE()) <> 'onboardx' THEN
|
|
SIGNAL SQLSTATE '45000'
|
|
SET MESSAGE_TEXT = '当前数据库不是 OnboardX，已拒绝执行人员与签约数据清理';
|
|
END IF;
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS `tmp_ob_reset_result`;
|
|
CREATE TEMPORARY TABLE `tmp_ob_reset_result` (
|
|
`TableName` VARCHAR(64) NOT NULL,
|
|
`BeforeRows` BIGINT NOT NULL,
|
|
`DeletedRows` BIGINT NOT NULL,
|
|
`AfterRows` BIGINT NOT NULL,
|
|
PRIMARY KEY (`TableName`)
|
|
);
|
|
|
|
SET old_foreign_key_checks = @@FOREIGN_KEY_CHECKS;
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
START TRANSACTION;
|
|
|
|
OPEN target_table_cursor;
|
|
|
|
delete_loop: LOOP
|
|
FETCH target_table_cursor INTO target_table_name;
|
|
IF finished = 1 THEN
|
|
LEAVE delete_loop;
|
|
END IF;
|
|
|
|
SET @ob_reset_row_count = 0;
|
|
SET @ob_reset_count_sql = CONCAT(
|
|
'SELECT COUNT(*) INTO @ob_reset_row_count FROM `',
|
|
REPLACE(target_table_name, '`', '``'),
|
|
'`'
|
|
);
|
|
PREPARE ob_reset_count_statement FROM @ob_reset_count_sql;
|
|
EXECUTE ob_reset_count_statement;
|
|
DEALLOCATE PREPARE ob_reset_count_statement;
|
|
SET before_rows = COALESCE(@ob_reset_row_count, 0);
|
|
|
|
SET @ob_reset_delete_sql = CONCAT(
|
|
'DELETE FROM `',
|
|
REPLACE(target_table_name, '`', '``'),
|
|
'`'
|
|
);
|
|
PREPARE ob_reset_delete_statement FROM @ob_reset_delete_sql;
|
|
EXECUTE ob_reset_delete_statement;
|
|
DEALLOCATE PREPARE ob_reset_delete_statement;
|
|
|
|
SET @ob_reset_row_count = 0;
|
|
PREPARE ob_reset_count_statement FROM @ob_reset_count_sql;
|
|
EXECUTE ob_reset_count_statement;
|
|
DEALLOCATE PREPARE ob_reset_count_statement;
|
|
SET after_rows = COALESCE(@ob_reset_row_count, 0);
|
|
|
|
INSERT INTO `tmp_ob_reset_result` (
|
|
`TableName`,
|
|
`BeforeRows`,
|
|
`DeletedRows`,
|
|
`AfterRows`
|
|
) VALUES (
|
|
target_table_name,
|
|
before_rows,
|
|
before_rows - after_rows,
|
|
after_rows
|
|
);
|
|
END LOOP;
|
|
|
|
CLOSE target_table_cursor;
|
|
|
|
COMMIT;
|
|
SET FOREIGN_KEY_CHECKS = old_foreign_key_checks;
|
|
|
|
SELECT
|
|
`TableName` AS `已清理表`,
|
|
`BeforeRows` AS `清理前`,
|
|
`DeletedRows` AS `删除数量`,
|
|
`AfterRows` AS `清理后`
|
|
FROM `tmp_ob_reset_result`
|
|
ORDER BY FIELD(
|
|
`TableName`,
|
|
'ob_electronic_sign_event',
|
|
'ob_electronic_sign_document',
|
|
'ob_electronic_sign_process',
|
|
'ob_employee_attachment',
|
|
'ob_employee_change_record',
|
|
'ob_employee_contract_snapshot',
|
|
'ob_task_operation_log',
|
|
'ob_task_audit',
|
|
'ob_task_material',
|
|
'ob_task_form_value',
|
|
'ob_task_employee_info',
|
|
'ob_task_contract_variable',
|
|
'ob_task_contract',
|
|
'ob_task_link',
|
|
'ob_task_material_requirement',
|
|
'ob_task_form_requirement',
|
|
'ob_onboarding_task',
|
|
'ob_employee'
|
|
);
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS `tmp_ob_reset_result`;
|
|
END$$
|
|
|
|
DELIMITER ;
|
|
|
|
CALL `ob_reset_onboarding_personnel_and_sign_data`();
|
|
DROP PROCEDURE IF EXISTS `ob_reset_onboarding_personnel_and_sign_data`;
|
|
|
|
-- 最终校验：下面存在的流程表都应为 0 行。
|
|
SELECT
|
|
`TABLE_NAME` AS `流程数据表`,
|
|
`TABLE_ROWS` AS `估算剩余行数`
|
|
FROM `information_schema`.`TABLES`
|
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
|
AND `TABLE_TYPE` = 'BASE TABLE'
|
|
AND `TABLE_NAME` IN (
|
|
'ob_electronic_sign_event',
|
|
'ob_electronic_sign_document',
|
|
'ob_electronic_sign_process',
|
|
'ob_employee_attachment',
|
|
'ob_employee_change_record',
|
|
'ob_employee_contract_snapshot',
|
|
'ob_task_operation_log',
|
|
'ob_task_audit',
|
|
'ob_task_material',
|
|
'ob_task_form_value',
|
|
'ob_task_employee_info',
|
|
'ob_task_contract_variable',
|
|
'ob_task_contract',
|
|
'ob_task_link',
|
|
'ob_task_material_requirement',
|
|
'ob_task_form_requirement',
|
|
'ob_onboarding_task',
|
|
'ob_employee'
|
|
)
|
|
ORDER BY `TABLE_NAME`;
|