53 lines
3.6 KiB
SQL
53 lines
3.6 KiB
SQL
CREATE TABLE IF NOT EXISTS wh_location_block (
|
|
id BIGINT UNSIGNED NOT NULL COMMENT '整票库位块ID',
|
|
tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '平台公共仓储资源域，固定为0',
|
|
warehouse_id BIGINT UNSIGNED NOT NULL COMMENT '仓库ID',
|
|
source_service VARCHAR(40) NOT NULL COMMENT '来源服务',
|
|
business_id BIGINT UNSIGNED NOT NULL COMMENT '当前归属入库单业务ID',
|
|
inbound_code VARCHAR(40) NOT NULL COMMENT '当前归属入仓码',
|
|
slot_count INT UNSIGNED NOT NULL COMMENT '整票独占槽位数',
|
|
idempotency_key VARCHAR(128) NOT NULL COMMENT '预占幂等键',
|
|
block_status VARCHAR(20) NOT NULL COMMENT 'RESERVED、OCCUPIED或RELEASED',
|
|
expires_at DATETIME(3) NOT NULL COMMENT '预占过期时间',
|
|
last_operation_key VARCHAR(128) NULL COMMENT '最近确认、转移或释放幂等键',
|
|
created_by BIGINT UNSIGNED NOT NULL COMMENT '创建人或系统主体ID',
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
|
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
|
|
version INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
|
|
active_business_block TINYINT GENERATED ALWAYS AS
|
|
(CASE WHEN block_status IN ('RESERVED','OCCUPIED') THEN 1 ELSE NULL END) STORED
|
|
COMMENT '同一来源入库单仅允许一个活动整票块',
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uk_location_block_idempotency (tenant_id, source_service, idempotency_key),
|
|
UNIQUE KEY uk_location_block_active_business (tenant_id, source_service, business_id, active_business_block),
|
|
KEY idx_location_block_business (tenant_id, source_service, business_id, block_status),
|
|
CONSTRAINT fk_location_block_warehouse FOREIGN KEY (warehouse_id) REFERENCES wh_warehouse (id),
|
|
CONSTRAINT ck_location_block_slot_count CHECK (slot_count BETWEEN 1 AND 200)
|
|
) ENGINE=InnoDB COMMENT='一票入仓码原子持有的稳定库位块';
|
|
|
|
CREATE TABLE IF NOT EXISTS wh_location_block_slot (
|
|
id BIGINT UNSIGNED NOT NULL COMMENT '槽位预占ID，对外作为reservationId',
|
|
tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '平台公共仓储资源域，固定为0',
|
|
block_id BIGINT UNSIGNED NOT NULL COMMENT '整票库位块ID',
|
|
slot_no INT UNSIGNED NOT NULL COMMENT '块内稳定槽位序号，从1开始',
|
|
location_id BIGINT UNSIGNED NOT NULL COMMENT '库位ID',
|
|
location_code VARCHAR(50) NOT NULL COMMENT '库位编码快照',
|
|
shared_overflow TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否落入同仓共享A0',
|
|
resource_status VARCHAR(20) NOT NULL COMMENT 'RESERVED、OCCUPIED或RELEASED',
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
|
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
|
|
version INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
|
|
active_normal_slot TINYINT GENERATED ALWAYS AS
|
|
(CASE WHEN resource_status IN ('RESERVED','OCCUPIED') AND shared_overflow = 0 THEN 1 ELSE NULL END) STORED
|
|
COMMENT '普通库位活动块槽唯一标记；A0返回NULL',
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uk_location_block_slot_no (tenant_id, block_id, slot_no),
|
|
UNIQUE KEY uk_location_block_active_normal (tenant_id, location_id, active_normal_slot),
|
|
KEY idx_location_block_slot_block (tenant_id, block_id, resource_status),
|
|
CONSTRAINT fk_location_block_slot_block FOREIGN KEY (block_id) REFERENCES wh_location_block (id),
|
|
CONSTRAINT fk_location_block_slot_location FOREIGN KEY (location_id) REFERENCES wh_location (id)
|
|
) ENGINE=InnoDB COMMENT='整票库位块槽位；空槽也由当前入仓码独占';
|
|
|
|
-- 头程入库单属于其他数据库，不建立跨库外键。
|
|
|