145 lines
4.1 KiB
SQL
145 lines
4.1 KiB
SQL
drop table if exists b_clothing_clothes_tag;
|
|
drop table if exists b_clothing_outfit_tag;
|
|
drop table if exists b_clothing_outfit_clothes;
|
|
drop table if exists b_clothing_tag;
|
|
drop table if exists b_clothing_category;
|
|
drop table if exists b_clothing_outfit;
|
|
drop table if exists b_clothing_clothes;
|
|
|
|
create table b_clothing_clothes (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
name varchar(64) not null,
|
|
image_file_key varchar(512) not null,
|
|
category_id bigint,
|
|
owner_id bigint,
|
|
brand varchar(64) default '',
|
|
remark varchar(512) default '',
|
|
sort_number int not null default 0,
|
|
status smallint not null default 1,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
create table b_clothing_outfit (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
name varchar(64) not null,
|
|
cover_file_key varchar(512) default '',
|
|
layout_json jsonb,
|
|
category_id bigint,
|
|
owner_id bigint,
|
|
remark varchar(512) default '',
|
|
sort_number int not null default 0,
|
|
status smallint not null default 1,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
create table b_clothing_outfit_clothes (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
outfit_id bigint not null,
|
|
clothes_id bigint not null,
|
|
x numeric(8, 6) not null default 0,
|
|
y numeric(8, 6) not null default 0,
|
|
width numeric(8, 6) not null default 0,
|
|
height numeric(8, 6) not null default 0,
|
|
z_index int not null default 0,
|
|
sort_number int not null default 0,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
create table b_clothing_category (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
name varchar(32) not null,
|
|
type smallint not null,
|
|
sort_number int not null default 0,
|
|
status smallint not null default 1,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
create table b_clothing_tag (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
parent_id bigint,
|
|
name varchar(32) not null,
|
|
type smallint not null,
|
|
color varchar(7) default '',
|
|
sort_number int not null default 0,
|
|
status smallint not null default 1,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
create table b_clothing_clothes_tag (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
clothes_id bigint not null,
|
|
tag_id bigint not null,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
create table b_clothing_outfit_tag (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
outfit_id bigint not null,
|
|
tag_id bigint not null,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
create index idx_b_clothing_clothes_family_status_category
|
|
on b_clothing_clothes (family_id, status, category_id, sort_number, id);
|
|
|
|
create index idx_b_clothing_clothes_family_owner
|
|
on b_clothing_clothes (family_id, owner_id, id);
|
|
|
|
create index idx_b_clothing_outfit_family_status_category
|
|
on b_clothing_outfit (family_id, status, category_id, sort_number, id);
|
|
|
|
create index idx_b_clothing_outfit_family_owner
|
|
on b_clothing_outfit (family_id, owner_id, id);
|
|
|
|
create index idx_b_clothing_outfit_clothes_outfit
|
|
on b_clothing_outfit_clothes (outfit_id, z_index, sort_number, id);
|
|
|
|
create index idx_b_clothing_outfit_clothes_clothes
|
|
on b_clothing_outfit_clothes (family_id, clothes_id, outfit_id);
|
|
|
|
create index idx_b_clothing_category_family_type
|
|
on b_clothing_category (family_id, status, type, sort_number, id);
|
|
|
|
create index idx_b_clothing_tag_family_parent
|
|
on b_clothing_tag (family_id, status, type, parent_id, sort_number, id);
|
|
|
|
create index idx_b_clothing_clothes_tag_clothes
|
|
on b_clothing_clothes_tag (family_id, clothes_id, tag_id);
|
|
|
|
create index idx_b_clothing_clothes_tag_tag
|
|
on b_clothing_clothes_tag (family_id, tag_id, clothes_id);
|
|
|
|
create index idx_b_clothing_outfit_tag_outfit
|
|
on b_clothing_outfit_tag (family_id, outfit_id, tag_id);
|
|
|
|
create index idx_b_clothing_outfit_tag_tag
|
|
on b_clothing_outfit_tag (family_id, tag_id, outfit_id);
|