103 lines
5.5 KiB
SQL
103 lines
5.5 KiB
SQL
-- 海外采购旧流程专属表退役，MySQL 8.0+，目标仅 baiyue_lastleg。
|
|
-- 必须先停止旧版 Lastleg 写入并部署本次分离版本，验证新流程后在维护窗口执行。
|
|
-- 永久删除下面明确列出的 21 张旧采购表及其记录，DDL 隐式提交；禁止 mysql --force。
|
|
-- 保留主单、审核流水、采购附件、分批收货、出库及幂等六表；不改任何共用财务、资金、文件或仓储表。
|
|
-- 旧资金表有记录或旧状态采购未处理时拒绝，不自动清除、回填或伪造资金完成。
|
|
-- 运行者须在同一连接显式 SET @procurement_retirement_release='procurement-six-tables-20261027';
|
|
-- 文件准备不表示数据库已执行。先审核备份及运行版本，按清单整份执行，遇错停止。
|
|
USE baiyue_lastleg;
|
|
DELIMITER $$
|
|
DROP PROCEDURE IF EXISTS retire_procurement_legacy_tables$$
|
|
CREATE PROCEDURE retire_procurement_legacy_tables()
|
|
BEGIN
|
|
IF DATABASE()<>'baiyue_lastleg' OR COALESCE(@procurement_retirement_release,'')<>'procurement-six-tables-20261027' THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Confirm the six-table procurement release and target schema first';
|
|
END IF;
|
|
IF (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name IN (
|
|
'll_overseas_procurement_order','ll_overseas_procurement_status_history','ll_overseas_procurement_file',
|
|
'll_procurement_workflow_receipt','ll_procurement_workflow_outbound','ll_procurement_workflow_command'))<>6 THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Six active procurement tables must exist before retiring legacy tables';
|
|
END IF;
|
|
IF EXISTS(SELECT 1 FROM ll_overseas_procurement_order WHERE status NOT IN
|
|
('PENDING_REVIEW','RECEIVING','WAIT_CUSTOMER_CONFIRM','CONFIRMED','IN_TRANSIT','DELIVERING','COMPLETED','REJECTED','CANCELLED')) THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Legacy procurement orders remain; resolve them before retirement';
|
|
END IF;
|
|
-- 仅只读核对共用资金记录；不删除资金命令或回执，不把已投递当作已结清。
|
|
IF EXISTS(SELECT 1 FROM ll_event_outbox WHERE aggregate_type='PROCUREMENT_FEE')
|
|
OR EXISTS(SELECT 1 FROM ll_wallet_event_inbox
|
|
WHERE JSON_UNQUOTE(JSON_EXTRACT(payload_json,'$.payload.businessType'))='PROCUREMENT_FEE') THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Legacy procurement wallet events exist; financial review is required';
|
|
END IF;
|
|
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='ll_overseas_procurement_fee') THEN
|
|
SET @procurement_retirement_financial_rows=0;
|
|
SET @procurement_retirement_check_sql='SELECT COUNT(*) INTO @procurement_retirement_financial_rows FROM baiyue_lastleg.ll_overseas_procurement_fee';
|
|
PREPARE procurement_retirement_check FROM @procurement_retirement_check_sql;
|
|
EXECUTE procurement_retirement_check;
|
|
DEALLOCATE PREPARE procurement_retirement_check;
|
|
IF @procurement_retirement_financial_rows<>0 THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Legacy procurement financial records remain; do not delete financial evidence';
|
|
END IF;
|
|
END IF;
|
|
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='ll_procurement_wallet_attempt') THEN
|
|
SET @procurement_retirement_financial_rows=0;
|
|
SET @procurement_retirement_check_sql='SELECT COUNT(*) INTO @procurement_retirement_financial_rows FROM baiyue_lastleg.ll_procurement_wallet_attempt';
|
|
PREPARE procurement_retirement_check FROM @procurement_retirement_check_sql;
|
|
EXECUTE procurement_retirement_check;
|
|
DEALLOCATE PREPARE procurement_retirement_check;
|
|
IF @procurement_retirement_financial_rows<>0 THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Legacy procurement financial records remain; do not delete financial evidence';
|
|
END IF;
|
|
END IF;
|
|
-- 不关闭外键校验；来自其他业务表的外键也会使这条精确删除失败。
|
|
DROP TABLE IF EXISTS
|
|
ll_procurement_closure_item,
|
|
ll_procurement_closure,
|
|
ll_procurement_control_note,
|
|
ll_procurement_fee_correction_item,
|
|
ll_procurement_fee_correction,
|
|
ll_procurement_repack,
|
|
ll_procurement_estimate_review,
|
|
ll_procurement_label_item,
|
|
ll_procurement_label_set,
|
|
ll_procurement_shipment_item,
|
|
ll_procurement_shipment,
|
|
ll_procurement_carton_source,
|
|
ll_procurement_carton,
|
|
ll_procurement_manifest,
|
|
ll_procurement_disposition,
|
|
ll_procurement_receipt,
|
|
ll_procurement_budget_authorization,
|
|
ll_procurement_command,
|
|
ll_procurement_flow,
|
|
ll_procurement_wallet_attempt,
|
|
ll_overseas_procurement_fee;
|
|
END$$
|
|
CALL retire_procurement_legacy_tables()$$
|
|
DROP PROCEDURE retire_procurement_legacy_tables$$
|
|
DELIMITER ;
|
|
|
|
-- 若上面任一步失败必须停止。剩余旧表数应为 0；此 SELECT 不代替客户端退出码核对。
|
|
SELECT COUNT(*) AS remaining_legacy_procurement_tables
|
|
FROM information_schema.tables WHERE table_schema='baiyue_lastleg' AND table_name IN (
|
|
'll_overseas_procurement_fee',
|
|
'll_procurement_wallet_attempt',
|
|
'll_procurement_flow',
|
|
'll_procurement_command',
|
|
'll_procurement_budget_authorization',
|
|
'll_procurement_receipt',
|
|
'll_procurement_disposition',
|
|
'll_procurement_manifest',
|
|
'll_procurement_carton',
|
|
'll_procurement_carton_source',
|
|
'll_procurement_shipment',
|
|
'll_procurement_shipment_item',
|
|
'll_procurement_label_set',
|
|
'll_procurement_label_item',
|
|
'll_procurement_estimate_review',
|
|
'll_procurement_repack',
|
|
'll_procurement_fee_correction',
|
|
'll_procurement_fee_correction_item',
|
|
'll_procurement_control_note',
|
|
'll_procurement_closure',
|
|
'll_procurement_closure_item');
|