90 lines
5.3 KiB
SQL
90 lines
5.3 KiB
SQL
CREATE SCHEMA IF NOT EXISTS file;
|
||
|
||
DROP TABLE IF EXISTS file.tb_sys_file CASCADE;
|
||
CREATE TABLE file.tb_sys_file (
|
||
optsn VARCHAR(50) NOT NULL, -- 流水号
|
||
file_id VARCHAR(50) NOT NULL, -- 文件ID
|
||
name VARCHAR(255) NOT NULL, -- 文件名
|
||
path VARCHAR(255) NOT NULL, -- 文件路径
|
||
size BIGINT NOT NULL, -- 文件大小
|
||
type VARCHAR(50) NOT NULL, -- 文件类型
|
||
storage_type VARCHAR(50) NOT NULL, -- 存储类型
|
||
mime_type VARCHAR(255) NOT NULL, -- 文件MIME类型
|
||
url VARCHAR(255) NOT NULL, -- 文件URL
|
||
status VARCHAR(50) NOT NULL, -- 文件状态
|
||
service_type VARCHAR(50), -- 服务类型（bidding/customer_service/internal等）
|
||
dept_path VARCHAR(255) NOT NULL, -- 当前部门路径
|
||
creator VARCHAR(50) DEFAULT NULL, -- 创建者
|
||
updater VARCHAR(50) DEFAULT NULL, -- 更新者
|
||
create_time TIMESTAMPTZ NOT NULL DEFAULT now(), -- 创建时间
|
||
update_time TIMESTAMPTZ DEFAULT NULL, -- 更新时间（由触发器维护）
|
||
delete_time TIMESTAMPTZ DEFAULT NULL, -- 删除时间
|
||
deleted BOOLEAN NOT NULL DEFAULT false, -- 是否删除
|
||
PRIMARY KEY (file_id),
|
||
UNIQUE (optsn)
|
||
);
|
||
CREATE INDEX idx_file_service ON file.tb_sys_file(service_type) WHERE deleted = false;
|
||
|
||
COMMENT ON TABLE file.tb_sys_file IS '文件表';
|
||
COMMENT ON COLUMN file.tb_sys_file.service_type IS '服务类型（用于服务间数据隔离）';
|
||
COMMENT ON COLUMN file.tb_sys_file.optsn IS '流水号';
|
||
COMMENT ON COLUMN file.tb_sys_file.file_id IS '文件ID';
|
||
COMMENT ON COLUMN file.tb_sys_file.name IS '文件名';
|
||
COMMENT ON COLUMN file.tb_sys_file.path IS '文件路径';
|
||
COMMENT ON COLUMN file.tb_sys_file.size IS '文件大小';
|
||
COMMENT ON COLUMN file.tb_sys_file.type IS '文件类型';
|
||
COMMENT ON COLUMN file.tb_sys_file.storage_type IS '存储类型';
|
||
COMMENT ON COLUMN file.tb_sys_file.mime_type IS '文件MIME类型';
|
||
COMMENT ON COLUMN file.tb_sys_file.url IS '文件URL';
|
||
COMMENT ON COLUMN file.tb_sys_file.status IS '文件状态';
|
||
COMMENT ON COLUMN file.tb_sys_file.dept_path IS '当前部门路径';
|
||
COMMENT ON COLUMN file.tb_sys_file.creator IS '创建者';
|
||
COMMENT ON COLUMN file.tb_sys_file.updater IS '更新者';
|
||
COMMENT ON COLUMN file.tb_sys_file.create_time IS '创建时间';
|
||
COMMENT ON COLUMN file.tb_sys_file.update_time IS '更新时间';
|
||
COMMENT ON COLUMN file.tb_sys_file.delete_time IS '删除时间';
|
||
COMMENT ON COLUMN file.tb_sys_file.deleted IS '是否删除';
|
||
|
||
-- =============================
|
||
-- 文件关联表
|
||
-- =============================
|
||
DROP TABLE IF EXISTS file.tb_file_relation CASCADE;
|
||
CREATE TABLE file.tb_file_relation (
|
||
optsn VARCHAR(50) NOT NULL,
|
||
relation_id VARCHAR(50) NOT NULL,
|
||
file_id VARCHAR(50) NOT NULL,
|
||
object_type VARCHAR(50) NOT NULL, -- 对象类型：bidding_project/ticket/document等
|
||
object_id VARCHAR(50) NOT NULL, -- 对象ID
|
||
relation_type VARCHAR(30) DEFAULT 'attachment', -- 关联类型：attachment-附件/avatar-头像/banner-横幅
|
||
order_num INTEGER DEFAULT 0, -- 排序号
|
||
service_type VARCHAR(50), -- 服务类型（继承自object_type）
|
||
creator VARCHAR(50) DEFAULT NULL, -- 创建者
|
||
updater VARCHAR(50) DEFAULT NULL, -- 更新者
|
||
create_time TIMESTAMPTZ NOT NULL DEFAULT now(), -- 创建时间
|
||
update_time TIMESTAMPTZ DEFAULT NULL, -- 更新时间
|
||
delete_time TIMESTAMPTZ DEFAULT NULL, -- 删除时间
|
||
deleted BOOLEAN NOT NULL DEFAULT false, -- 是否删除
|
||
PRIMARY KEY (relation_id),
|
||
UNIQUE (optsn),
|
||
FOREIGN KEY (file_id) REFERENCES file.tb_sys_file(file_id)
|
||
);
|
||
|
||
COMMENT ON TABLE file.tb_file_relation IS '文件关联表';
|
||
COMMENT ON COLUMN file.tb_file_relation.optsn IS '流水号';
|
||
COMMENT ON COLUMN file.tb_file_relation.relation_id IS '关联ID';
|
||
COMMENT ON COLUMN file.tb_file_relation.file_id IS '文件ID';
|
||
COMMENT ON COLUMN file.tb_file_relation.object_type IS '对象类型：bidding_project/ticket/document等';
|
||
COMMENT ON COLUMN file.tb_file_relation.object_id IS '对象ID';
|
||
COMMENT ON COLUMN file.tb_file_relation.relation_type IS '关联类型：attachment-附件/avatar-头像/banner-横幅';
|
||
COMMENT ON COLUMN file.tb_file_relation.order_num IS '排序号';
|
||
COMMENT ON COLUMN file.tb_file_relation.service_type IS '服务类型（继承自object_type）';
|
||
COMMENT ON COLUMN file.tb_file_relation.creator IS '创建者';
|
||
COMMENT ON COLUMN file.tb_file_relation.updater IS '更新者';
|
||
COMMENT ON COLUMN file.tb_file_relation.create_time IS '创建时间';
|
||
COMMENT ON COLUMN file.tb_file_relation.update_time IS '更新时间';
|
||
COMMENT ON COLUMN file.tb_file_relation.delete_time IS '删除时间';
|
||
COMMENT ON COLUMN file.tb_file_relation.deleted IS '是否删除';
|
||
|
||
CREATE INDEX idx_file_relation_object ON file.tb_file_relation(object_type, object_id) WHERE deleted = false;
|
||
CREATE INDEX idx_file_relation_file ON file.tb_file_relation(file_id) WHERE deleted = false;
|
||
CREATE INDEX idx_file_relation_service ON file.tb_file_relation(service_type) WHERE deleted = false; |