361 lines
20 KiB
SQL
361 lines
20 KiB
SQL
-----------张斌2025-06-16 15:00:00--------------
|
||
|
||
--------------------pgsql创建assets_category表语句---------------------------
|
||
-- 分类主表
|
||
CREATE TABLE IF NOT EXISTS assets_category (
|
||
-- 基础字段
|
||
id BIGINT PRIMARY KEY, -- 从BIGSERIAL改为BIGINT，取消自增
|
||
tenant_id BIGINT NOT NULL DEFAULT 0, -- 租户ID int8
|
||
creator VARCHAR(64) NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updater VARCHAR(64) NOT NULL,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at timestamp(6),
|
||
|
||
-- 分类核心字段（移除bid字段）
|
||
name VARCHAR(128) NOT NULL,
|
||
parent_id VARCHAR(64) DEFAULT '',
|
||
path VARCHAR(512) DEFAULT '',
|
||
level INT NOT NULL DEFAULT 0,
|
||
is_leaf_node BOOLEAN NOT NULL DEFAULT FALSE,
|
||
sort INT NOT NULL DEFAULT 0,
|
||
image VARCHAR(256) DEFAULT '',
|
||
attrs JSONB DEFAULT '[]'::JSONB,
|
||
status SMALLINT NOT NULL DEFAULT 1
|
||
);
|
||
|
||
-- 为分类表添加索引（移除原uk_category_id唯一索引，保留其他索引）
|
||
CREATE INDEX idx_category_tenant_id ON assets_category(tenant_id);
|
||
CREATE INDEX idx_category_parent_id ON assets_category(parent_id);
|
||
CREATE INDEX idx_category_level ON assets_category(level);
|
||
CREATE INDEX idx_category_status ON assets_category(status);
|
||
CREATE INDEX idx_category_is_leaf_node ON assets_category(is_leaf_node);
|
||
|
||
-- 分类表字段注释（移除bid字段注释）
|
||
COMMENT ON TABLE assets_category IS '商品/服务分类表';
|
||
COMMENT ON COLUMN assets_category.id IS '主键ID（非自增）';
|
||
COMMENT ON COLUMN assets_category.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN assets_category.creator IS '创建人';
|
||
COMMENT ON COLUMN assets_category.created_at IS '创建时间';
|
||
COMMENT ON COLUMN assets_category.updater IS '更新人';
|
||
COMMENT ON COLUMN assets_category.updated_at IS '更新时间';
|
||
COMMENT ON COLUMN assets_category.deleted_at IS '删除时间（软删）';
|
||
COMMENT ON COLUMN assets_category.name IS '分类名称';
|
||
COMMENT ON COLUMN assets_category.parent_id IS '父分类ID，为空表示根分类';
|
||
COMMENT ON COLUMN assets_category.path IS '分类路径，如：/root/parent/child';
|
||
COMMENT ON COLUMN assets_category.level IS '分类层级';
|
||
COMMENT ON COLUMN assets_category.is_leaf_node IS '是否叶子节点';
|
||
COMMENT ON COLUMN assets_category.sort IS '排序';
|
||
COMMENT ON COLUMN assets_category.image IS '分类图片';
|
||
COMMENT ON COLUMN assets_category.attrs IS '分类属性列表，结构参考 CategoryAttr 实体';
|
||
COMMENT ON COLUMN assets_category.status IS '状态：1启用/0禁用';
|
||
|
||
--------------------pgsql创建assets_category表语句---------------------------
|
||
|
||
--------------------pgsql创建assets_asset表语句---------------------------
|
||
-- 资产表（asset）
|
||
CREATE TABLE IF NOT EXISTS assets_asset (
|
||
-- 嵌入基础字段（复用 SQLBaseDO 结构）
|
||
id BIGINT PRIMARY KEY, -- 从BIGSERIAL改为BIGINT，取消自增
|
||
tenant_id BIGINT NOT NULL DEFAULT 0, -- 租户ID int8
|
||
creator VARCHAR(64) NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updater VARCHAR(64) NOT NULL,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at timestamp(6),
|
||
|
||
-- 基础信息字段（移除bid字段）
|
||
name VARCHAR(128) NOT NULL,
|
||
description TEXT DEFAULT '',
|
||
type VARCHAR(32) NOT NULL, -- 资产类型：physical实物/virtual虚拟/service服务
|
||
category_id BIGINT NOT NULL, -- 分类ID（对应category分类表id）
|
||
category_path VARCHAR(512) DEFAULT '',
|
||
image_url VARCHAR(512) DEFAULT '',
|
||
images JSONB DEFAULT '[]'::JSONB, -- 图片列表（字符串数组，JSONB存储）
|
||
status SMALLINT NOT NULL DEFAULT 1, -- 资产状态：1启用/0停用
|
||
base_price INT NOT NULL DEFAULT 0, -- 基础价格（分为单位）
|
||
currency VARCHAR(16) DEFAULT 'CNY', -- 货币单位，默认CNY
|
||
unlimited_stock BOOLEAN NOT NULL DEFAULT FALSE, -- 是否无库存限制
|
||
stock_mode SMALLINT NOT NULL DEFAULT 1, -- 库存管理模式：1-明细模式 2-批次模式
|
||
online_time TIMESTAMP, -- 上线时间（可为空）
|
||
offline_time TIMESTAMP, -- 下线时间（可为空）
|
||
|
||
-- 类型专用配置（JSONB存储）
|
||
physical_asset_config JSONB, -- 实物资产配置
|
||
service_asset_config JSONB, -- 服务资产配置
|
||
virtual_asset_config JSONB, -- 虚拟资产配置
|
||
metadata JSONB, -- 扩展字段（动态元数据）
|
||
|
||
-- 租户相关
|
||
tenant_module_type VARCHAR(64) DEFAULT ''
|
||
);
|
||
|
||
-- 为资产表添加索引
|
||
CREATE INDEX idx_asset_tenant_id ON assets_asset(tenant_id);
|
||
CREATE INDEX idx_asset_category_id ON assets_asset(category_id);
|
||
CREATE INDEX idx_asset_type ON assets_asset(type);
|
||
CREATE INDEX idx_asset_status ON assets_asset(status);
|
||
CREATE INDEX idx_asset_online_time ON assets_asset(online_time);
|
||
CREATE INDEX idx_asset_offline_time ON assets_asset(offline_time);
|
||
CREATE INDEX idx_asset_tenant_module_type ON assets_asset(tenant_module_type);
|
||
|
||
-- 为资产表添加注释
|
||
COMMENT ON TABLE assets_asset IS '资产主表';
|
||
COMMENT ON COLUMN assets_asset.id IS '主键ID（非自增）';
|
||
COMMENT ON COLUMN assets_asset.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN assets_asset.creator IS '创建人';
|
||
COMMENT ON COLUMN assets_asset.created_at IS '创建时间';
|
||
COMMENT ON COLUMN assets_asset.updater IS '更新人';
|
||
COMMENT ON COLUMN assets_asset.updated_at IS '更新时间';
|
||
COMMENT ON COLUMN assets_asset.deleted_at IS '删除时间（软删）';
|
||
COMMENT ON COLUMN assets_asset.name IS '资产名称';
|
||
COMMENT ON COLUMN assets_asset.description IS '资产描述';
|
||
COMMENT ON COLUMN assets_asset.type IS '资产类型：physical实物/virtual虚拟/service服务';
|
||
COMMENT ON COLUMN assets_asset.category_id IS '分类ID（关联assets_category.id）';
|
||
COMMENT ON COLUMN assets_asset.category_path IS '分类路径';
|
||
COMMENT ON COLUMN assets_asset.image_url IS '主图URL';
|
||
COMMENT ON COLUMN assets_asset.images IS '图片列表(JSONB)';
|
||
COMMENT ON COLUMN assets_asset.status IS '资产状态：1启用/0停用';
|
||
COMMENT ON COLUMN assets_asset.base_price IS '基础价格（分为单位）';
|
||
COMMENT ON COLUMN assets_asset.currency IS '货币单位，默认CNY';
|
||
COMMENT ON COLUMN assets_asset.unlimited_stock IS '是否无库存限制';
|
||
COMMENT ON COLUMN assets_asset.stock_mode IS '库存管理模式：1-明细模式 2-批次模式';
|
||
COMMENT ON COLUMN assets_asset.online_time IS '上线时间';
|
||
COMMENT ON COLUMN assets_asset.offline_time IS '下线时间';
|
||
COMMENT ON COLUMN assets_asset.physical_asset_config IS '实物资产配置(JSONB)';
|
||
COMMENT ON COLUMN assets_asset.service_asset_config IS '服务资产配置(JSONB)';
|
||
COMMENT ON COLUMN assets_asset.virtual_asset_config IS '虚拟资产配置(JSONB)';
|
||
COMMENT ON COLUMN assets_asset.metadata IS '动态元数据(JSONB)';
|
||
COMMENT ON COLUMN assets_asset.tenant_module_type IS '租户模块类型';
|
||
|
||
--------------------pgsql创建assets_asset表语句---------------------------
|
||
|
||
--------------------pgsql创建assets_asset_sku表语句---------------------------
|
||
-- 资产SKU表
|
||
CREATE TABLE IF NOT EXISTS assets_asset_sku (
|
||
-- 基础字段（继承 SQLBaseDO 通用字段）
|
||
id BIGINT PRIMARY KEY, -- 主键ID（非自增）
|
||
tenant_id BIGINT NOT NULL DEFAULT 0, -- 租户ID int8
|
||
creator VARCHAR(64) NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updater VARCHAR(64) NOT NULL,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at timestamp(6),
|
||
|
||
-- SKU核心字段
|
||
asset_id BIGINT NOT NULL, -- 关联资产ID
|
||
asset_name VARCHAR(128) NOT NULL, -- 资产名称
|
||
sku_name VARCHAR(128) NOT NULL, -- SKU名称
|
||
image_url VARCHAR(256) DEFAULT '', -- SKU主图
|
||
spec_values JSONB NOT NULL DEFAULT '[]'::JSONB, -- 规格值：{"颜色":"红色","尺寸":"L"}
|
||
price INT NOT NULL DEFAULT 0, -- 价格（分为单位）
|
||
unlimited_stock BOOLEAN NOT NULL DEFAULT FALSE, -- 是否无库存限制
|
||
stock INT NOT NULL DEFAULT 0, -- 库存数量
|
||
specs_count INT NOT NULL DEFAULT 0, -- 规格数量
|
||
specs_unit JSONB DEFAULT '{}'::JSONB, -- 规格单位 SpecsUnitKeyValue
|
||
sort INT NOT NULL DEFAULT 0, -- 排序
|
||
status SMALLINT NOT NULL DEFAULT 1, -- 资产状态：1启用/0停用
|
||
stock_mode SMALLINT NOT NULL DEFAULT 1, -- 库存管理模式：1-明细模式 2-批次模式
|
||
category_id BIGINT NOT NULL DEFAULT 0, -- 分类ID
|
||
category_path VARCHAR(512) DEFAULT '', -- 分类路径
|
||
tenant_module_type VARCHAR(32) DEFAULT '' -- 租户模块类型
|
||
);
|
||
|
||
-- 外键约束
|
||
ALTER TABLE assets_asset_sku ADD CONSTRAINT fk_sku_asset_id FOREIGN KEY (asset_id) REFERENCES assets_asset(id) ON DELETE CASCADE;
|
||
ALTER TABLE assets_asset_sku ADD CONSTRAINT fk_sku_category_id FOREIGN KEY (category_id) REFERENCES assets_category(id) ON DELETE SET DEFAULT;
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_sku_tenant_id ON assets_asset_sku(tenant_id);
|
||
CREATE INDEX idx_sku_asset_id ON assets_asset_sku(asset_id);
|
||
CREATE INDEX idx_sku_category_id ON assets_asset_sku(category_id);
|
||
CREATE INDEX idx_sku_status ON assets_asset_sku(status);
|
||
CREATE INDEX idx_sku_stock_mode ON assets_asset_sku(stock_mode);
|
||
CREATE INDEX idx_sku_deleted_at ON assets_asset_sku(deleted_at);
|
||
CREATE INDEX idx_sku_tenant_module_type ON assets_asset_sku(tenant_module_type);
|
||
|
||
-- 表和字段注释
|
||
COMMENT ON TABLE assets_asset_sku IS '资产SKU表';
|
||
COMMENT ON COLUMN assets_asset_sku.id IS '主键ID（非自增）';
|
||
COMMENT ON COLUMN assets_asset_sku.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN assets_asset_sku.creator IS '创建人';
|
||
COMMENT ON COLUMN assets_asset_sku.created_at IS '创建时间';
|
||
COMMENT ON COLUMN assets_asset_sku.updater IS '更新人';
|
||
COMMENT ON COLUMN assets_asset_sku.updated_at IS '更新时间';
|
||
COMMENT ON COLUMN assets_asset_sku.deleted_at IS '删除时间（软删）';
|
||
COMMENT ON COLUMN assets_asset_sku.asset_id IS '关联资产ID';
|
||
COMMENT ON COLUMN assets_asset_sku.asset_name IS '资产名称';
|
||
COMMENT ON COLUMN assets_asset_sku.sku_name IS 'SKU名称';
|
||
COMMENT ON COLUMN assets_asset_sku.image_url IS 'SKU主图';
|
||
COMMENT ON COLUMN assets_asset_sku.spec_values IS '规格值：{"颜色":"红色","尺寸":"L","时长":"1个月","平台":"抖音"}';
|
||
COMMENT ON COLUMN assets_asset_sku.price IS '价格（分为单位）';
|
||
COMMENT ON COLUMN assets_asset_sku.unlimited_stock IS '是否无库存限制';
|
||
COMMENT ON COLUMN assets_asset_sku.stock IS '库存数量';
|
||
COMMENT ON COLUMN assets_asset_sku.specs_count IS '规格数量';
|
||
COMMENT ON COLUMN assets_asset_sku.specs_unit IS '规格单位 SpecsUnitKeyValue';
|
||
COMMENT ON COLUMN assets_asset_sku.sort IS '排序';
|
||
COMMENT ON COLUMN assets_asset_sku.status IS '状态：1启用 0停用';
|
||
COMMENT ON COLUMN assets_asset_sku.stock_mode IS '库存管理模式：1-明细模式 2-批次模式';
|
||
COMMENT ON COLUMN assets_asset_sku.category_id IS '分类ID';
|
||
COMMENT ON COLUMN assets_asset_sku.category_path IS '分类路径';
|
||
COMMENT ON COLUMN assets_asset_sku.tenant_module_type IS '租户模块类型';
|
||
|
||
--------------------pgsql创建assets_asset_sku表语句---------------------------
|
||
|
||
--------------------pgsql创建assets_stock_batch表语句---------------------------
|
||
-- 库存批次表（批次管理模式专用）
|
||
CREATE TABLE IF NOT EXISTS assets_stock_batch (
|
||
-- 基础字段（继承 SQLBaseDO 通用字段）
|
||
id BIGINT PRIMARY KEY, -- 主键ID（非自增）
|
||
tenant_id BIGINT NOT NULL DEFAULT 0, -- 租户ID int8
|
||
creator VARCHAR(64) NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updater VARCHAR(64) NOT NULL,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at timestamp(6),
|
||
|
||
-- 核心关联字段
|
||
asset_id BIGINT NOT NULL, -- 关联资产ID
|
||
asset_sku_id BIGINT NOT NULL, -- 关联资产SKU ID
|
||
batch_no VARCHAR(64) NOT NULL, -- 批次号
|
||
batch_qty INT NOT NULL DEFAULT 0, -- 批次总数量（入库后不可变）
|
||
available_qty INT NOT NULL DEFAULT 0, -- 可用数量（实时变化）
|
||
|
||
-- 批次元数据
|
||
metadata JSONB NOT NULL DEFAULT '[]'::JSONB, -- 其他元数据：[]map[string]interface{}
|
||
|
||
-- 状态
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
|
||
-- 订单关联
|
||
order_id BIGINT DEFAULT 0, -- 关联订单ID（如果有）
|
||
|
||
-- 渠道分配信息
|
||
assigned_channel VARCHAR(64) DEFAULT '', -- 分配的销售渠道
|
||
channel_sku VARCHAR(128) DEFAULT '', -- 渠道商品SKU
|
||
channel_metadata JSONB DEFAULT '{}'::JSONB, -- 渠道专属数据：map[string]interface{}
|
||
allocated_at TIMESTAMP, -- 分配时间（可为空）
|
||
|
||
-- 临期管理
|
||
production_date TIMESTAMP, -- 生产日期（可为空）
|
||
expiry_date TIMESTAMP, -- 过期日期（可为空）
|
||
expiry_warning_date TIMESTAMP, -- 临期预警时间（可为空）
|
||
category_path VARCHAR(512) DEFAULT '' -- 分类路径
|
||
);
|
||
|
||
-- 外键约束
|
||
ALTER TABLE assets_stock_batch ADD CONSTRAINT fk_batch_asset_id FOREIGN KEY (asset_id) REFERENCES assets_asset(id) ON DELETE CASCADE;
|
||
ALTER TABLE assets_stock_batch ADD CONSTRAINT fk_batch_asset_sku_id FOREIGN KEY (asset_sku_id) REFERENCES assets_asset_sku(id) ON DELETE CASCADE;
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_batch_tenant_id ON assets_stock_batch(tenant_id);
|
||
CREATE INDEX idx_batch_asset_id ON assets_stock_batch(asset_id);
|
||
CREATE INDEX idx_batch_asset_sku_id ON assets_stock_batch(asset_sku_id);
|
||
CREATE INDEX idx_batch_batch_no ON assets_stock_batch(batch_no);
|
||
CREATE INDEX idx_batch_status ON assets_stock_batch(status);
|
||
CREATE INDEX idx_batch_order_id ON assets_stock_batch(order_id);
|
||
CREATE INDEX idx_batch_expiry_date ON assets_stock_batch(expiry_date);
|
||
CREATE INDEX idx_batch_assigned_channel ON assets_stock_batch(assigned_channel);
|
||
CREATE INDEX idx_batch_deleted_at ON assets_stock_batch(deleted_at);
|
||
|
||
-- 唯一索引
|
||
CREATE UNIQUE INDEX uk_batch_sku_batch_no ON assets_stock_batch(asset_sku_id, batch_no) WHERE deleted_at IS NULL;
|
||
|
||
-- 注释
|
||
COMMENT ON TABLE assets_stock_batch IS '库存批次表（批次管理模式专用）';
|
||
COMMENT ON COLUMN assets_stock_batch.id IS '主键ID（非自增）';
|
||
COMMENT ON COLUMN assets_stock_batch.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN assets_stock_batch.creator IS '创建人';
|
||
COMMENT ON COLUMN assets_stock_batch.created_at IS '创建时间';
|
||
COMMENT ON COLUMN assets_stock_batch.updater IS '更新人';
|
||
COMMENT ON COLUMN assets_stock_batch.updated_at IS '更新时间';
|
||
COMMENT ON COLUMN assets_stock_batch.deleted_at IS '删除时间（软删）';
|
||
COMMENT ON COLUMN assets_stock_batch.asset_id IS '关联资产ID';
|
||
COMMENT ON COLUMN assets_stock_batch.asset_sku_id IS '关联资产SKU ID';
|
||
COMMENT ON COLUMN assets_stock_batch.batch_no IS '批次号';
|
||
COMMENT ON COLUMN assets_stock_batch.batch_qty IS '批次总数量（入库后不可变）';
|
||
COMMENT ON COLUMN assets_stock_batch.available_qty IS '可用数量（实时变化）';
|
||
COMMENT ON COLUMN assets_stock_batch.metadata IS '其他元数据，结构为[]map[string]interface{}';
|
||
COMMENT ON COLUMN assets_stock_batch.status IS '批次状态（1-活跃/2-临期/3-过期/4-售罄）';
|
||
COMMENT ON COLUMN assets_stock_batch.order_id IS '关联订单ID（如果有）';
|
||
COMMENT ON COLUMN assets_stock_batch.assigned_channel IS '分配的销售渠道';
|
||
COMMENT ON COLUMN assets_stock_batch.channel_sku IS '渠道商品SKU';
|
||
COMMENT ON COLUMN assets_stock_batch.channel_metadata IS '渠道专属数据，结构为map[string]interface{}';
|
||
COMMENT ON COLUMN assets_stock_batch.allocated_at IS '分配时间';
|
||
COMMENT ON COLUMN assets_stock_batch.production_date IS '生产日期';
|
||
COMMENT ON COLUMN assets_stock_batch.expiry_date IS '过期日期';
|
||
COMMENT ON COLUMN assets_stock_batch.expiry_warning_date IS '临期预警时间（有过期日期时建议填写）';
|
||
COMMENT ON COLUMN assets_stock_batch.category_path IS '分类路径';
|
||
|
||
--------------------pgsql创建assets_stock_batch表语句---------------------------
|
||
|
||
--------------------pgsql创建assets_stock_details表语句---------------------------
|
||
-- 库存明细表（单件商品独立ID，适配区块链虚拟资产场景）
|
||
CREATE TABLE IF NOT EXISTS assets_stock_details (
|
||
-- 基础字段（继承 SQLBaseDO 通用字段）
|
||
id BIGINT PRIMARY KEY, -- 主键ID（非自增，单件商品独立ID，适配区块链资产标识）
|
||
tenant_id BIGINT NOT NULL DEFAULT 0, -- 租户ID int8
|
||
creator VARCHAR(64) NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updater VARCHAR(64) NOT NULL,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at timestamp(6),
|
||
|
||
-- 核心关联字段
|
||
asset_id BIGINT NOT NULL, -- 关联资产ID
|
||
asset_sku_id BIGINT NOT NULL, -- 关联资产SKU ID
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
order_id BIGINT DEFAULT 0, -- 关联订单ID（如果有）
|
||
lock_expire TIMESTAMP, -- 锁定过期时间（可为空）
|
||
metadata JSONB NOT NULL DEFAULT '[]'::JSONB, -- 其他元数据：[]map[string]interface{}
|
||
token_id VARCHAR(256) DEFAULT '', -- 区块链TokenID（如果有）
|
||
|
||
-- 渠道分配信息
|
||
assigned_channel VARCHAR(64) DEFAULT '', -- 分配的销售渠道
|
||
channel_sku VARCHAR(128) DEFAULT '', -- 渠道商品SKU
|
||
channel_metadata JSONB DEFAULT '{}'::JSONB, -- 渠道专属数据：map[string]interface{}
|
||
allocated_at TIMESTAMP, -- 分配时间（可为空）
|
||
category_path VARCHAR(512) DEFAULT '' -- 分类路径
|
||
);
|
||
|
||
-- 外键约束
|
||
ALTER TABLE assets_stock_details ADD CONSTRAINT fk_details_asset_id FOREIGN KEY (asset_id) REFERENCES assets_asset(id) ON DELETE CASCADE;
|
||
ALTER TABLE assets_stock_details ADD CONSTRAINT fk_details_asset_sku_id FOREIGN KEY (asset_sku_id) REFERENCES assets_asset_sku(id) ON DELETE CASCADE;
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_details_tenant_id ON assets_stock_details(tenant_id);
|
||
CREATE INDEX idx_details_asset_id ON assets_stock_details(asset_id);
|
||
CREATE INDEX idx_details_asset_sku_id ON assets_stock_details(asset_sku_id);
|
||
CREATE INDEX idx_details_status ON assets_stock_details(status);
|
||
CREATE INDEX idx_details_order_id ON assets_stock_details(order_id);
|
||
CREATE INDEX idx_details_lock_expire ON assets_stock_details(lock_expire);
|
||
CREATE INDEX idx_details_token_id ON assets_stock_details(token_id);
|
||
CREATE INDEX idx_details_assigned_channel ON assets_stock_details(assigned_channel);
|
||
CREATE INDEX idx_details_deleted_at ON assets_stock_details(deleted_at);
|
||
|
||
-- 唯一索引
|
||
CREATE UNIQUE INDEX uk_details_token_id ON assets_stock_details(token_id) WHERE deleted_at IS NULL AND token_id != '';
|
||
|
||
-- 注释
|
||
COMMENT ON TABLE assets_stock_details IS '库存明细表（单件商品独立ID，用于区块链虚拟资产管理）';
|
||
COMMENT ON COLUMN assets_stock_details.id IS '主键ID（非自增，单件商品独立唯一标识）';
|
||
COMMENT ON COLUMN assets_stock_details.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN assets_stock_details.creator IS '创建人';
|
||
COMMENT ON COLUMN assets_stock_details.created_at IS '创建时间';
|
||
COMMENT ON COLUMN assets_stock_details.updater IS '更新人';
|
||
COMMENT ON COLUMN assets_stock_details.updated_at IS '更新时间';
|
||
COMMENT ON COLUMN assets_stock_details.deleted_at IS '删除时间（软删）';
|
||
COMMENT ON COLUMN assets_stock_details.asset_id IS '关联资产ID';
|
||
COMMENT ON COLUMN assets_stock_details.asset_sku_id IS '关联资产SKU ID';
|
||
COMMENT ON COLUMN assets_stock_details.status IS '库存状态（1-可用/2-已售出/3-预留/4-锁定）';
|
||
COMMENT ON COLUMN assets_stock_details.order_id IS '关联订单ID（如果有）';
|
||
COMMENT ON COLUMN assets_stock_details.lock_expire IS '锁定过期时间（锁定状态下有效）';
|
||
COMMENT ON COLUMN assets_stock_details.metadata IS '其他元数据，结构为[]map[string]interface{}';
|
||
COMMENT ON COLUMN assets_stock_details.token_id IS '区块链TokenID（虚拟资产唯一标识，可为空）';
|
||
COMMENT ON COLUMN assets_stock_details.assigned_channel IS '分配的销售渠道';
|
||
COMMENT ON COLUMN assets_stock_details.channel_sku IS '渠道商品SKU';
|
||
COMMENT ON COLUMN assets_stock_details.channel_metadata IS '渠道专属数据，结构为map[string]interface{}';
|
||
COMMENT ON COLUMN assets_stock_details.allocated_at IS '分配时间';
|
||
COMMENT ON COLUMN assets_stock_details.category_path IS '分类路径';
|
||
|
||
--------------------pgsql创建assets_stock_details表语句--------------------------- |