init.sql 3.87 KB
-- 字幕集表
create table if not exists caption_set
(
    id          int8         not null primary key AUTO_INCREMENT,
    unid        varchar(36)  not null default RANDOM_UUID(),
    name        varchar(128) not null,
    intro       text,
    create_time timestamp    not null default current_timestamp
);
comment on table caption_set is '字幕集表,每行数据对应多个字幕数据';
comment on column caption_set.name is '名称';
comment on column caption_set.intro is '描述';
comment on column caption_set.create_time is '创建时间';
create index if not exists caption_set_unid_idx on caption_set (unid);

-- 字幕表
create table if not exists caption
(
    id              int8        not null primary key AUTO_INCREMENT,
    unid            varchar(36) not null default RANDOM_UUID(),
    caption_set_id  int8        not null,
    enabled         boolean     not null default true,
    pic_type        int4,
    bg_color        int4,
    bg_transparency int4        not null default 100,
    font            int4,
    font_size       int4,
    fg_color        int4,
    date_format     varchar(48),
    context         text,
    create_time     timestamp   not null default current_timestamp,
    foreign key (caption_set_id) references caption_set (id) on delete cascade on update no action
);
comment on table caption is '字幕表';
comment on column caption.caption_set_id is '字幕集ID';
comment on column caption.enabled is '是否启用文字叠加';
comment on column caption.pic_type is '图片类型:全景图,车辆特写,人脸特写等';
comment on column caption.bg_color is '背景颜色';
comment on column caption.bg_transparency is '背景颜色透明度';
comment on column caption.font is '字体';
comment on column caption.font_size is '字体大小';
comment on column caption.fg_color is '字体颜色';
comment on column caption.date_format is '日期格式';
comment on column caption.context is '叠加信息(JSON)';
create index if not exists caption_caption_set_id_idx on caption (caption_set_id);
create index if not exists caption_unid_idx on caption (unid);

-- 图片合成配置表
create table if not exists pic_config
(
    id              int8         not null primary key AUTO_INCREMENT,
    unid            varchar(36)  not null default RANDOM_UUID(),
    name            varchar(128) not null,
    enabled         boolean      not null default true,
    caption_set_id  int8,
    picture_quality int4,
    type            int4,
    context         text,
    create_time     timestamp    not null default current_timestamp,
    foreign key (caption_set_id) references caption_set (id) on delete set null on update no action
);
comment on table pic_config is '图片合成配置表';
comment on column pic_config.name is '名称';
comment on column pic_config.enabled is '是否启用图片合成';
comment on column pic_config.caption_set_id is '对应字幕集';
comment on column pic_config.picture_quality is '图片质量,KB';
comment on column pic_config.type is '图片排列类型';
comment on column pic_config.context is '排列方式(JSON)';
comment on column pic_config.create_time is '创建时间';
create index if not exists pic_config_caption_set_id_idx on pic_config (caption_set_id);
create index if not exists pic_config_unid_idx on pic_config (unid);

-- 存储方案表
create table if not exists storage_config
(
    id          int8         not null primary key AUTO_INCREMENT,
    unid        varchar(36)  not null default RANDOM_UUID(),
    name        varchar(128) not null,
    context     text,
    create_time timestamp    not null default current_timestamp
);
comment on table storage_config is '存储方案表';
comment on column storage_config.name is '名称';
comment on column storage_config.context is '排列方式(JSON)';
comment on column storage_config.create_time is '创建时间';
create index if not exists storage_config_unid_idx on storage_config (unid);