219 lines
10 KiB
SQL
219 lines
10 KiB
SQL
-- 资产管理模块数据库初始化脚本
|
||
-- PostgreSQL 15.6 版本
|
||
-- 包含序列、表结构、主键、索引等定义
|
||
-- 注意：本脚本需在edendb数据库中执行
|
||
|
||
-- 创建序列
|
||
CREATE SEQUENCE IF NOT EXISTS SEQ_TB_ASSET_CLASSIFICATION_1
|
||
INCREMENT BY 1
|
||
START WITH 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1
|
||
AS BIGINT;
|
||
|
||
CREATE SEQUENCE IF NOT EXISTS SEQ_TB_ASSET_LOCATION_1
|
||
INCREMENT BY 1
|
||
START WITH 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1
|
||
AS BIGINT;
|
||
|
||
CREATE SEQUENCE IF NOT EXISTS SEQ_TB_ASSET_LABEL_1
|
||
INCREMENT BY 1
|
||
START WITH 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1
|
||
AS BIGINT;
|
||
|
||
CREATE SEQUENCE IF NOT EXISTS SEQ_TB_ASSET_1
|
||
INCREMENT BY 1
|
||
START WITH 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1
|
||
AS BIGINT;
|
||
|
||
-- 创建表结构
|
||
-- 1. 资产分类表
|
||
CREATE TABLE IF NOT EXISTS TB_ASSET_CLASSIFICATION (
|
||
id BIGINT NOT NULL DEFAULT nextval('SEQ_TB_ASSET_CLASSIFICATION_1') CONSTRAINT PK_TB_ASSET_CLASSIFICATION_1 PRIMARY KEY,
|
||
classification_code VARCHAR(50) NOT NULL,
|
||
classification_name VARCHAR(100) NOT NULL,
|
||
parent_id VARCHAR(32),
|
||
status VARCHAR(1) NOT NULL,
|
||
remark VARCHAR(2000),
|
||
create_user_id VARCHAR(50),
|
||
create_time TIMESTAMP NOT NULL,
|
||
del_flag VARCHAR(1) NOT NULL,
|
||
last_mod_user_id VARCHAR(50),
|
||
last_mod_time TIMESTAMP,
|
||
tenant_id VARCHAR(50)
|
||
);
|
||
|
||
COMMENT ON TABLE TB_ASSET_CLASSIFICATION IS '资产分类表';
|
||
COMMENT ON COLUMN TB_ASSET_CLASSIFICATION.classification_code IS '资产分类编码';
|
||
COMMENT ON COLUMN TB_ASSET_CLASSIFICATION.classification_name IS '资产分类名称';
|
||
COMMENT ON COLUMN TB_ASSET_CLASSIFICATION.parent_id IS '上级资产分类ID';
|
||
COMMENT ON COLUMN TB_ASSET_CLASSIFICATION.status IS '状态 1-启用 0-禁用';
|
||
COMMENT ON COLUMN TB_ASSET_CLASSIFICATION.remark IS '备注';
|
||
COMMENT ON COLUMN TB_ASSET_CLASSIFICATION.create_user_id IS '创建人ID';
|
||
COMMENT ON COLUMN TB_ASSET_CLASSIFICATION.create_time IS '创建时间';
|
||
COMMENT ON COLUMN TB_ASSET_CLASSIFICATION.del_flag IS '是否删除 0-未删除 1-已删除';
|
||
COMMENT ON COLUMN TB_ASSET_CLASSIFICATION.last_mod_user_id IS '最后修改人ID';
|
||
COMMENT ON COLUMN TB_ASSET_CLASSIFICATION.last_mod_time IS '最后修改时间';
|
||
COMMENT ON COLUMN TB_ASSET_CLASSIFICATION.tenant_id IS '租户ID';
|
||
|
||
-- 2. 资产位置表
|
||
CREATE TABLE IF NOT EXISTS TB_ASSET_LOCATION (
|
||
id BIGINT NOT NULL DEFAULT nextval('SEQ_TB_ASSET_LOCATION_1') CONSTRAINT PK_TB_ASSET_LOCATION_1 PRIMARY KEY,
|
||
location_code VARCHAR(50) NOT NULL,
|
||
location_name VARCHAR(100) NOT NULL,
|
||
parent_id VARCHAR(32),
|
||
level_code VARCHAR(50),
|
||
status VARCHAR(1) NOT NULL,
|
||
remark VARCHAR(2000),
|
||
create_user_id VARCHAR(50),
|
||
create_time TIMESTAMP NOT NULL,
|
||
del_flag VARCHAR(1) NOT NULL,
|
||
last_mod_user_id VARCHAR(50),
|
||
last_mod_time TIMESTAMP,
|
||
tenant_id VARCHAR(50)
|
||
);
|
||
|
||
COMMENT ON TABLE TB_ASSET_LOCATION IS '资产位置表';
|
||
COMMENT ON COLUMN TB_ASSET_LOCATION.location_code IS '位置编码';
|
||
COMMENT ON COLUMN TB_ASSET_LOCATION.location_name IS '位置名称';
|
||
COMMENT ON COLUMN TB_ASSET_LOCATION.parent_id IS '上级位置ID';
|
||
COMMENT ON COLUMN TB_ASSET_LOCATION.level_code IS '层级';
|
||
COMMENT ON COLUMN TB_ASSET_LOCATION.status IS '状态 1-启用 0-禁用';
|
||
COMMENT ON COLUMN TB_ASSET_LOCATION.remark IS '备注';
|
||
COMMENT ON COLUMN TB_ASSET_LOCATION.create_user_id IS '创建人ID';
|
||
COMMENT ON COLUMN TB_ASSET_LOCATION.create_time IS '创建时间';
|
||
COMMENT ON COLUMN TB_ASSET_LOCATION.del_flag IS '是否删除 0-未删除 1-已删除';
|
||
COMMENT ON COLUMN TB_ASSET_LOCATION.last_mod_user_id IS '最后修改人ID';
|
||
COMMENT ON COLUMN TB_ASSET_LOCATION.last_mod_time IS '最后修改时间';
|
||
COMMENT ON COLUMN TB_ASSET_LOCATION.tenant_id IS '租户ID';
|
||
|
||
-- 3. 资产标签表
|
||
CREATE TABLE IF NOT EXISTS TB_ASSET_LABEL (
|
||
id BIGINT NOT NULL DEFAULT nextval('SEQ_TB_ASSET_LABEL_1') CONSTRAINT PK_TB_ASSET_LABEL_1 PRIMARY KEY,
|
||
template_type VARCHAR(1) NOT NULL,
|
||
label_items VARCHAR(1000),
|
||
paper_type VARCHAR(1) NOT NULL,
|
||
label_width VARCHAR(8),
|
||
label_height VARCHAR(8),
|
||
create_user_id VARCHAR(50),
|
||
create_time TIMESTAMP NOT NULL,
|
||
del_flag VARCHAR(1) NOT NULL,
|
||
last_mod_user_id VARCHAR(50),
|
||
last_mod_time TIMESTAMP,
|
||
tenant_id VARCHAR(50) NOT NULL,
|
||
CONSTRAINT UK_TB_ASSET_LABEL_TENANT UNIQUE (tenant_id, del_flag)
|
||
);
|
||
|
||
COMMENT ON TABLE TB_ASSET_LABEL IS '资产标签表';
|
||
COMMENT ON COLUMN TB_ASSET_LABEL.template_type IS '模板类型 1-模板1 2-模板2 3-模板3 4-模板4，数值代表最多可选择的标签数量';
|
||
COMMENT ON COLUMN TB_ASSET_LABEL.label_items IS '标签集合(以逗号分隔的数字): 1-资产名称,2-资产分类,3-资产编码,4-资产位置,5-品牌,6-型号,7-设备序列号,8-管理员,9-保养到期时间,10-保养说明,11-使用部门';
|
||
COMMENT ON COLUMN TB_ASSET_LABEL.paper_type IS '纸张类型 1-标签专用纸 2-A4(2列标签) 3-A4(3列标签)';
|
||
COMMENT ON COLUMN TB_ASSET_LABEL.label_width IS '标签宽度(mm)，仅当纸张类型为标签专用纸时有效';
|
||
COMMENT ON COLUMN TB_ASSET_LABEL.label_height IS '标签高度(mm)，仅当纸张类型为标签专用纸时有效';
|
||
COMMENT ON COLUMN TB_ASSET_LABEL.create_user_id IS '创建人ID';
|
||
COMMENT ON COLUMN TB_ASSET_LABEL.create_time IS '创建时间';
|
||
COMMENT ON COLUMN TB_ASSET_LABEL.del_flag IS '是否删除 0-未删除 1-已删除';
|
||
COMMENT ON COLUMN TB_ASSET_LABEL.last_mod_user_id IS '最后修改人ID';
|
||
COMMENT ON COLUMN TB_ASSET_LABEL.last_mod_time IS '最后修改时间';
|
||
COMMENT ON COLUMN TB_ASSET_LABEL.tenant_id IS '租户ID';
|
||
|
||
-- 4. 资产表
|
||
CREATE TABLE IF NOT EXISTS TB_ASSET (
|
||
id BIGINT NOT NULL DEFAULT nextval('SEQ_TB_ASSET_1') CONSTRAINT PK_TB_ASSET_1 PRIMARY KEY,
|
||
asset_code VARCHAR(100) NOT NULL,
|
||
asset_name VARCHAR(200) NOT NULL,
|
||
classification_id VARCHAR(32) NOT NULL,
|
||
admin_user_id VARCHAR(50),
|
||
company_id VARCHAR(50) NOT NULL,
|
||
location_id VARCHAR(32) NOT NULL,
|
||
purchase_type VARCHAR(20) NOT NULL,
|
||
brand VARCHAR(100) NOT NULL,
|
||
model VARCHAR(100),
|
||
serial_number VARCHAR(100),
|
||
purchase_amount DECIMAL(18,2),
|
||
expected_useful_life INT,
|
||
remark VARCHAR(2000),
|
||
purchase_date DATE,
|
||
storage_date DATE,
|
||
asset_photo VARCHAR(500),
|
||
maintenance_due_date DATE,
|
||
maintenance_description VARCHAR(2000),
|
||
expected_depreciation_period INT,
|
||
asset_status VARCHAR(20) NOT NULL,
|
||
create_user_id VARCHAR(50),
|
||
create_time TIMESTAMP NOT NULL,
|
||
del_flag VARCHAR(1) NOT NULL,
|
||
last_mod_user_id VARCHAR(50),
|
||
last_mod_time TIMESTAMP,
|
||
tenant_id VARCHAR(50)
|
||
);
|
||
|
||
COMMENT ON TABLE TB_ASSET IS '资产表';
|
||
COMMENT ON COLUMN TB_ASSET.asset_code IS '资产编码';
|
||
COMMENT ON COLUMN TB_ASSET.asset_name IS '资产名称';
|
||
COMMENT ON COLUMN TB_ASSET.classification_id IS '资产分类ID';
|
||
COMMENT ON COLUMN TB_ASSET.admin_user_id IS '管理员ID';
|
||
COMMENT ON COLUMN TB_ASSET.company_id IS '所属公司ID';
|
||
COMMENT ON COLUMN TB_ASSET.location_id IS '所在位置ID';
|
||
COMMENT ON COLUMN TB_ASSET.purchase_type IS '购置方式 采购/租赁';
|
||
COMMENT ON COLUMN TB_ASSET.brand IS '品牌';
|
||
COMMENT ON COLUMN TB_ASSET.model IS '型号';
|
||
COMMENT ON COLUMN TB_ASSET.serial_number IS '设备序列号';
|
||
COMMENT ON COLUMN TB_ASSET.purchase_amount IS '购置金额';
|
||
COMMENT ON COLUMN TB_ASSET.expected_useful_life IS '预计使用期限(月)';
|
||
COMMENT ON COLUMN TB_ASSET.remark IS '备注';
|
||
COMMENT ON COLUMN TB_ASSET.purchase_date IS '购置时间';
|
||
COMMENT ON COLUMN TB_ASSET.storage_date IS '入库时间';
|
||
COMMENT ON COLUMN TB_ASSET.asset_photo IS '资产照片URL';
|
||
COMMENT ON COLUMN TB_ASSET.maintenance_due_date IS '保养到期时间';
|
||
COMMENT ON COLUMN TB_ASSET.maintenance_description IS '保养说明';
|
||
COMMENT ON COLUMN TB_ASSET.expected_depreciation_period IS '预计折旧期限(月)';
|
||
COMMENT ON COLUMN TB_ASSET.asset_status IS '资产状态 空闲/在用/借用/派发中/退库中/借出中/归还中/维修中';
|
||
COMMENT ON COLUMN TB_ASSET.create_user_id IS '创建人ID';
|
||
COMMENT ON COLUMN TB_ASSET.create_time IS '创建时间';
|
||
COMMENT ON COLUMN TB_ASSET.del_flag IS '是否删除 0-未删除 1-已删除';
|
||
COMMENT ON COLUMN TB_ASSET.last_mod_user_id IS '最后修改人ID';
|
||
COMMENT ON COLUMN TB_ASSET.last_mod_time IS '最后修改时间';
|
||
COMMENT ON COLUMN TB_ASSET.tenant_id IS '租户ID';
|
||
|
||
-- 创建索引
|
||
-- 1. 资产分类表索引
|
||
CREATE INDEX IDX_TB_ASSET_CLASSIFICATION_CODE ON TB_ASSET_CLASSIFICATION (classification_code) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_CLASSIFICATION_NAME ON TB_ASSET_CLASSIFICATION (classification_name) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_CLASSIFICATION_PARENT_ID ON TB_ASSET_CLASSIFICATION (parent_id) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_CLASSIFICATION_STATUS ON TB_ASSET_CLASSIFICATION (status) WHERE del_flag = '0';
|
||
|
||
-- 2. 资产位置表索引
|
||
CREATE INDEX IDX_TB_ASSET_LOCATION_CODE ON TB_ASSET_LOCATION (location_code) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_LOCATION_NAME ON TB_ASSET_LOCATION (location_name) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_LOCATION_PARENT_ID ON TB_ASSET_LOCATION (parent_id) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_LOCATION_STATUS ON TB_ASSET_LOCATION (status) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_LOCATION_LEVEL_CODE ON TB_ASSET_LOCATION (level_code) WHERE del_flag = '0';
|
||
|
||
-- 3. 资产标签表索引
|
||
CREATE INDEX IDX_TB_ASSET_LABEL_TEMPLATE_TYPE ON TB_ASSET_LABEL (template_type) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_LABEL_PAPER_TYPE ON TB_ASSET_LABEL (paper_type) WHERE del_flag = '0';
|
||
CREATE UNIQUE INDEX IDX_UNI_TB_ASSET_LABEL_TENANT ON TB_ASSET_LABEL (tenant_id) WHERE del_flag = '0';
|
||
|
||
-- 4. 资产表索引
|
||
CREATE UNIQUE INDEX IDX_UNI_TB_ASSET_CODE ON TB_ASSET (asset_code, tenant_id) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_NAME ON TB_ASSET (asset_name) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_CLASSIFICATION_ID ON TB_ASSET (classification_id) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_LOCATION_ID ON TB_ASSET (location_id) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_ADMIN_USER_ID ON TB_ASSET (admin_user_id) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_COMPANY_ID ON TB_ASSET (company_id) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_STATUS ON TB_ASSET (asset_status) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_BRAND ON TB_ASSET (brand) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_SERIAL_NUMBER ON TB_ASSET (serial_number) WHERE del_flag = '0';
|
||
CREATE INDEX IDX_TB_ASSET_MAINTENANCE_DUE_DATE ON TB_ASSET (maintenance_due_date) WHERE del_flag = '0';
|