50 lines
3.3 KiB
SQL
50 lines
3.3 KiB
SQL
-- 本地四流程结构补齐；目标 baiyue_lastleg；MySQL 8；不删除或回填业务数据。
|
||
USE `baiyue_lastleg`;
|
||
SET NAMES utf8mb4;
|
||
DROP PROCEDURE IF EXISTS ensure_local_flow_column;
|
||
DELIMITER $$
|
||
CREATE PROCEDURE ensure_local_flow_column(IN t VARCHAR(64), IN n VARCHAR(64), IN d TEXT)
|
||
BEGIN
|
||
IF NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name=t AND column_name=n) THEN
|
||
SET @local_flow_ddl=CONCAT('ALTER TABLE `',t,'` ADD COLUMN `',n,'` ',d);
|
||
PREPARE local_flow_stmt FROM @local_flow_ddl; EXECUTE local_flow_stmt; DEALLOCATE PREPARE local_flow_stmt;
|
||
END IF;
|
||
END$$
|
||
DELIMITER ;
|
||
CALL ensure_local_flow_column('ll_inbound_fba_appointment','appointment_end_at','DATETIME(3) NULL COMMENT ''预约结束时间，未明确时为空''');
|
||
|
||
CALL ensure_local_flow_column('ll_inbound_fba_appointment','version','INT NOT NULL DEFAULT 0 COMMENT ''并发版本''');
|
||
|
||
CALL ensure_local_flow_column('ll_warehouse_transfer_relabel_binding','source_allocation_id','BIGINT NULL COMMENT ''已有库存转运的原始来源分配ID''');
|
||
|
||
CALL ensure_local_flow_column('ll_warehouse_transfer_relabel_binding','relabel_item_id','BIGINT NULL COMMENT ''本票关联换标明细ID''');
|
||
|
||
CALL ensure_local_flow_column('ll_warehouse_transfer_inventory_allocation','sku_code','VARCHAR(128) NULL COMMENT ''本次来源分配的实际SKU快照''');
|
||
|
||
CREATE TABLE IF NOT EXISTS ll_warehouse_transfer_receipt_command (
|
||
id BIGINT NOT NULL COMMENT '收货命令主键',
|
||
customer_id VARCHAR(128) NOT NULL COMMENT '客户标识',
|
||
transfer_order_id BIGINT NOT NULL COMMENT '转运单标识',
|
||
source_inventory_position_id BIGINT NOT NULL COMMENT '本次收货来源库存位置',
|
||
idempotency_key VARCHAR(255) NOT NULL COMMENT '原始收货幂等键',
|
||
request_hash CHAR(64) NOT NULL COMMENT '完整原始收货请求摘要',
|
||
request_json JSON NOT NULL COMMENT '完整原始收货请求快照',
|
||
target_warehouse_id BIGINT NOT NULL COMMENT '实际目标海外收货仓',
|
||
target_pallet_id BIGINT NOT NULL COMMENT '目标实物托盘',
|
||
target_location_id BIGINT NOT NULL COMMENT '目标实际库位',
|
||
target_hash CHAR(64) NOT NULL COMMENT '共享目标资源绑定摘要',
|
||
target_json JSON NOT NULL COMMENT '权威目标仓托盘库位及目的仓快照',
|
||
reservation_key VARCHAR(128) NOT NULL COMMENT '同转运同目标托盘共享资源占用键',
|
||
resource_lock_id BIGINT NULL COMMENT '已确认有效的海外资源锁标识',
|
||
status VARCHAR(32) NOT NULL COMMENT '命令状态PREPARED准备完成RESERVED资源占用COMPLETED收货完成',
|
||
created_at DATETIME(3) NOT NULL COMMENT '命令创建时间',
|
||
updated_at DATETIME(3) NOT NULL COMMENT '命令最后更新时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_ll_wh_receipt_cmd_source (transfer_order_id, source_inventory_position_id),
|
||
UNIQUE KEY uk_ll_wh_receipt_cmd_key (customer_id, idempotency_key),
|
||
KEY idx_ll_wh_receipt_cmd_target (customer_id, transfer_order_id, target_pallet_id),
|
||
CONSTRAINT ck_ll_wh_receipt_cmd_status CHECK (status IN ('PREPARED','RESERVED','COMPLETED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='转运逐来源收货原请求与共享目标资源占用命令';
|
||
DROP PROCEDURE IF EXISTS ensure_local_flow_column;
|
||
SELECT 'LOCAL_FLOW_SCHEMA_READY' AS migration_status, DATABASE() AS target_database;
|