oss/update.sql

-----------张斌2025-06-16 15:00:00--------------

--------------------pgsql创建file表语句---------------------------

-- 存储文件表
CREATE TABLE IF NOT EXISTS file (
    -- 基础字段
    id BIGINT PRIMARY KEY,
    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),

    -- 文件核心字段
    tenant_id BIGINT NOT NULL,
    file_url VARCHAR(512) NOT NULL,
    file_size INT NOT NULL DEFAULT 0
);
-- 为文件表添加索引
CREATE INDEX idx_file_tenant_id ON file(tenant_id);
CREATE INDEX idx_file_file_size ON file(file_size);

-- 文件表字段注释
COMMENT ON TABLE file IS '存储文件表';
COMMENT ON COLUMN file.id IS '主键ID';
COMMENT ON COLUMN file.creator IS '创建人';
COMMENT ON COLUMN file.created_at IS '创建时间';
COMMENT ON COLUMN file.updater IS '更新人';
COMMENT ON COLUMN file.updated_at IS '更新时间';
COMMENT ON COLUMN file.deleted_at IS '删除时间（软删）';
COMMENT ON COLUMN file.tenant_id IS '租户ID';
COMMENT ON COLUMN file.file_url IS '文件URL';
COMMENT ON COLUMN file.file_size IS '文件大小（字节）';

--------------------pgsql创建file表语句---------------------------


--------------------pgsql创建tenant_oss_total表语句---------------------------
-- 租户存储服务总计表
CREATE TABLE IF NOT EXISTS tenant_oss_total (
    -- 基础字段
    id BIGINT PRIMARY KEY,  -- 保留id作为主键
    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),

    -- 租户存储字段
    tenant_id BIGINT NOT NULL,
    used_oss_size INT NOT NULL DEFAULT 0,
    total_oss_size INT NOT NULL DEFAULT 0,

    -- 唯一索引（仅约束tenant_id唯一性，不替代主键）
    CONSTRAINT uk_tenant_oss_total_tenant_id UNIQUE (tenant_id)
);

-- 为租户存储表添加索引
CREATE INDEX idx_tenant_oss_total_used_size ON tenant_oss_total(used_oss_size);
CREATE INDEX idx_tenant_oss_total_total_size ON tenant_oss_total(total_oss_size);

-- 租户存储表字段注释
COMMENT ON TABLE tenant_oss_total IS '租户存储服务总计表';
COMMENT ON COLUMN tenant_oss_total.id IS '主键ID';
COMMENT ON COLUMN tenant_oss_total.creator IS '创建人';
COMMENT ON COLUMN tenant_oss_total.created_at IS '创建时间';
COMMENT ON COLUMN tenant_oss_total.updater IS '更新人';
COMMENT ON COLUMN tenant_oss_total.updated_at IS '更新时间';
COMMENT ON COLUMN tenant_oss_total.deleted_at IS '删除时间（软删）';
COMMENT ON COLUMN tenant_oss_total.tenant_id IS '租户ID（唯一）';
COMMENT ON COLUMN tenant_oss_total.used_oss_size IS '已使用存储大小（字节）';
COMMENT ON COLUMN tenant_oss_total.total_oss_size IS '总存储大小（字节）';

--------------------pgsql创建tenant_oss_total表语句---------------------------