You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1885 lines
57 KiB
1885 lines
57 KiB
create table if not exists t_abn_report_push_strategy
|
|
(
|
|
id serial not null
|
|
constraint t_abn_report_push_strategy_pkey
|
|
primary key,
|
|
user_id integer not null,
|
|
enabled boolean default true not null,
|
|
structures integer[] not null,
|
|
created_org integer
|
|
);
|
|
|
|
create table if not exists t_abn_type
|
|
(
|
|
id serial not null
|
|
constraint t_abn_type_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
description varchar(50) not null
|
|
);
|
|
|
|
create table if not exists t_agg_type
|
|
(
|
|
id integer not null
|
|
constraint t_agg_type_pkey
|
|
primary key,
|
|
type_name varchar(30) not null,
|
|
parent_type_id smallint not null,
|
|
description varchar(30),
|
|
name varchar(10)
|
|
);
|
|
|
|
create unique index if not exists t_agg_type_id_uindex
|
|
on t_agg_type (id);
|
|
|
|
create table if not exists t_alarm_category
|
|
(
|
|
id serial not null
|
|
constraint t_alarm_category_pkey
|
|
primary key,
|
|
name varchar(50) not null
|
|
);
|
|
|
|
create table if not exists t_alarm_code
|
|
(
|
|
id serial not null
|
|
constraint t_alarm_code_pkey
|
|
primary key,
|
|
code varchar(20) not null,
|
|
name varchar(255),
|
|
type_code varchar(20) not null,
|
|
level integer not null,
|
|
upgrade_strategy jsonb,
|
|
enable boolean default true not null
|
|
);
|
|
|
|
create table if not exists t_alarm_custom_message
|
|
(
|
|
id serial not null
|
|
constraint t_alarm_custom_message_pk
|
|
primary key,
|
|
structure integer not null,
|
|
email varchar(255),
|
|
sms varchar(255)
|
|
);
|
|
|
|
create table if not exists t_alarm_type
|
|
(
|
|
id serial not null
|
|
constraint t_alarm_type_pkey
|
|
primary key,
|
|
code varchar(50) not null
|
|
constraint t_alarm_type_code_key
|
|
unique,
|
|
name varchar(50) not null,
|
|
description varchar(100),
|
|
category integer not null
|
|
constraint t_alarm_type_category_fkey
|
|
references t_alarm_category,
|
|
enabled boolean not null,
|
|
upgrade_strategy jsonb
|
|
);
|
|
|
|
create table if not exists t_api_log
|
|
(
|
|
id bigserial not null
|
|
constraint t_api_log_pkey
|
|
primary key,
|
|
log_time timestamp(6) with time zone not null,
|
|
method varchar(20),
|
|
content varchar(256),
|
|
parameter varchar(256),
|
|
parameter_show varchar(256),
|
|
user_agent varchar(512),
|
|
url varchar(256),
|
|
status_code integer,
|
|
cost integer,
|
|
visible boolean not null
|
|
);
|
|
|
|
create index if not exists idx_api_log_status
|
|
on t_api_log (status_code);
|
|
|
|
create index if not exists idx_api_log_time
|
|
on t_api_log (log_time);
|
|
|
|
create table if not exists t_apply
|
|
(
|
|
id serial not null
|
|
constraint t_apply_pkey
|
|
primary key,
|
|
phone varchar(11) not null,
|
|
org varchar(100) not null,
|
|
province varchar(20) not null,
|
|
city varchar(20) not null,
|
|
struct_types varchar(50) not null,
|
|
reg_code uuid not null,
|
|
submit_date timestamp(6) with time zone not null,
|
|
process_date timestamp(6) with time zone,
|
|
approved boolean,
|
|
remarks varchar(200)
|
|
);
|
|
|
|
create table if not exists t_averagerainfall
|
|
(
|
|
id serial not null
|
|
constraint t_averagerainfall_pkey
|
|
primary key,
|
|
"structId" integer not null,
|
|
rainfall numeric(8, 3) not null,
|
|
time timestamp(6) with time zone not null
|
|
);
|
|
|
|
create table if not exists t_bim_rendering_path
|
|
(
|
|
id serial not null
|
|
constraint t_bim_rendering_path_pkey
|
|
primary key,
|
|
structure_id varchar(50) not null,
|
|
bim_rendering_path varchar(200) not null
|
|
);
|
|
|
|
create table if not exists t_bim_stations
|
|
(
|
|
id serial not null
|
|
constraint t_bim_stations_pkey
|
|
primary key,
|
|
structure_id varchar(50) not null,
|
|
stations_id varchar(50) not null,
|
|
component_id varchar(50) not null,
|
|
point varchar(256) not null,
|
|
bbox varchar(256)
|
|
);
|
|
|
|
create table if not exists t_calendar_type
|
|
(
|
|
id serial not null
|
|
constraint t_calendar_type_pkey
|
|
primary key,
|
|
name varchar(50) not null
|
|
);
|
|
|
|
create table if not exists t_component
|
|
(
|
|
id serial not null
|
|
constraint t_component_pkey
|
|
primary key,
|
|
component varchar(255)
|
|
);
|
|
|
|
create table if not exists t_construction
|
|
(
|
|
id serial not null
|
|
constraint t_construction_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
address varchar(100),
|
|
longitude numeric(20, 14),
|
|
latitude numeric(20, 14),
|
|
des varchar(100) not null,
|
|
portrait varchar(200) not null,
|
|
org_built integer not null,
|
|
org_construct integer not null,
|
|
org_owner integer not null,
|
|
org_supervision integer not null,
|
|
area integer,
|
|
height integer,
|
|
design_company varchar(100),
|
|
construct_begin timestamp(6) with time zone,
|
|
construct_end timestamp(6) with time zone,
|
|
project_type integer default 1 not null
|
|
);
|
|
|
|
create table if not exists t_consumable_category
|
|
(
|
|
id integer not null
|
|
constraint t_consumable_category_pkey
|
|
primary key,
|
|
name varchar(50) not null
|
|
);
|
|
|
|
create table if not exists t_consumable
|
|
(
|
|
id serial not null,
|
|
category integer not null
|
|
constraint t_consumable_category_id_fk
|
|
references t_consumable_category,
|
|
name varchar(100) not null,
|
|
vender varchar(200),
|
|
remark varchar(200)
|
|
);
|
|
|
|
create unique index if not exists t_consumable_id_uindex
|
|
on t_consumable (id);
|
|
|
|
create unique index if not exists t_consumable_category_id_uindex
|
|
on t_consumable_category (id);
|
|
|
|
create table if not exists t_data_original
|
|
(
|
|
id bigserial not null
|
|
constraint t_data_original_pkey
|
|
primary key,
|
|
iota_device_id uuid not null,
|
|
original_data jsonb not null,
|
|
collect_time timestamp(6) with time zone not null,
|
|
batch_no varchar(100) not null
|
|
);
|
|
|
|
create index if not exists idx_data_original
|
|
on t_data_original (iota_device_id, collect_time);
|
|
|
|
create table if not exists t_debug_history
|
|
(
|
|
id serial not null
|
|
constraint t_debug_history_pkey
|
|
primary key,
|
|
do_user_id integer,
|
|
do_time timestamp(6) with time zone default now(),
|
|
package_size varchar(10),
|
|
use_time integer,
|
|
result integer,
|
|
iota_thing_id uuid not null,
|
|
dtu_id varchar(50) default NULL::character varying,
|
|
|
|
device_id varchar(50) not null,
|
|
is_success boolean
|
|
);
|
|
comment on column t_debug_history.id is 'id';
|
|
|
|
comment on column t_debug_history.do_user_id is '操作人id';
|
|
|
|
comment on column t_debug_history.do_time is '操作时间';
|
|
|
|
comment on column t_debug_history.package_size is '数据包大小';
|
|
|
|
comment on column t_debug_history.use_time is '耗时';
|
|
|
|
comment on column t_debug_history.result is '结果';
|
|
|
|
comment on column t_debug_history.iota_thing_id is 'iotathingid';
|
|
|
|
comment on column t_debug_history.dtu_id is 'dtuid';
|
|
|
|
comment on column t_debug_history.device_id is '下发设备id';
|
|
|
|
comment on column t_debug_history.is_success is '是否成功';
|
|
|
|
create table if not exists t_dyna_glt_config
|
|
(
|
|
id serial not null
|
|
constraint t_dyna_glt_config_pkey
|
|
primary key,
|
|
scheme_id uuid not null,
|
|
glt integer not null,
|
|
cond jsonb,
|
|
enable boolean
|
|
);
|
|
|
|
create table if not exists t_factor_proto
|
|
(
|
|
code varchar(30) not null
|
|
constraint t_factor_proto_pkey
|
|
primary key,
|
|
name varchar(50)
|
|
);
|
|
|
|
create table if not exists t_agg_proto_config
|
|
(
|
|
id serial not null
|
|
constraint t_agg_proto_config_pkey
|
|
primary key,
|
|
name varchar(255) not null,
|
|
proto varchar(30) not null
|
|
constraint t_agg_proto_config_t_factor_proto_code_fk
|
|
references t_factor_proto,
|
|
aggs jsonb not null,
|
|
enabled boolean default true not null
|
|
);
|
|
|
|
create table if not exists t_factor
|
|
(
|
|
id serial not null
|
|
constraint t_factor_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
proto varchar(30) not null
|
|
constraint t_factor_proto_fkey
|
|
references t_factor_proto,
|
|
org integer,
|
|
item jsonb not null
|
|
);
|
|
|
|
create table if not exists t_factor_proto_item
|
|
(
|
|
id serial not null
|
|
constraint t_factor_proto_item_pkey
|
|
primary key,
|
|
proto varchar(30) not null
|
|
constraint t_factor_proto_item_proto_fkey
|
|
references t_factor_proto,
|
|
name varchar(50) not null,
|
|
field_name varchar(50) not null,
|
|
precision integer
|
|
);
|
|
|
|
create table if not exists t_filter_enviroment_config
|
|
(
|
|
id serial not null
|
|
constraint t_filter_enviroment_config_pkey
|
|
primary key,
|
|
project integer not null,
|
|
params jsonb not null,
|
|
receiver jsonb not null
|
|
);
|
|
|
|
create table if not exists t_filter_method
|
|
(
|
|
id serial not null
|
|
constraint t_filter_method_pkey
|
|
primary key,
|
|
type integer not null,
|
|
name varchar(50) not null,
|
|
params jsonb
|
|
);
|
|
|
|
create table if not exists t_formula
|
|
(
|
|
id serial not null
|
|
constraint t_formula_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
expression_shown varchar(255) not null,
|
|
description varchar(100),
|
|
params jsonb not null,
|
|
ioparams jsonb,
|
|
expression varchar(255),
|
|
ioparams_ext jsonb,
|
|
props jsonb
|
|
);
|
|
|
|
create table if not exists t_factor_proto_device
|
|
(
|
|
id serial not null
|
|
constraint t_factor_proto_device_pkey
|
|
primary key,
|
|
proto varchar(30) not null
|
|
constraint t_factor_proto_device_proto_fkey
|
|
references t_factor_proto,
|
|
iota_product_code uuid not null,
|
|
formula integer
|
|
constraint t_factor_proto_device_formula_fkey
|
|
references t_formula,
|
|
fields jsonb,
|
|
multi boolean default false,
|
|
multi_formula integer,
|
|
multi_fields jsonb,
|
|
input_unitconvert jsonb
|
|
);
|
|
|
|
create table if not exists t_formula_params_ex
|
|
(
|
|
id serial not null
|
|
constraint t_formula_params_ex_pk
|
|
primary key,
|
|
name varchar(255),
|
|
params jsonb not null
|
|
);
|
|
|
|
create table if not exists t_geofence_picture
|
|
(
|
|
id serial not null
|
|
constraint t_geofence_picture_pkey
|
|
primary key,
|
|
point_id integer,
|
|
acquisition_time timestamp(6),
|
|
picture_link varchar(255) default NULL::character varying
|
|
);
|
|
|
|
comment on column t_geofence_picture.point_id is '测点id';
|
|
|
|
comment on column t_geofence_picture.acquisition_time is '采集时间';
|
|
|
|
comment on column t_geofence_picture.picture_link is '图片链接';
|
|
|
|
create table if not exists t_geofence_type
|
|
(
|
|
id serial not null
|
|
constraint t_geofence_type_pkey
|
|
primary key,
|
|
name varchar(60) not null
|
|
);
|
|
|
|
comment on column t_geofence_type.name is '类型名';
|
|
|
|
create table if not exists t_geofence
|
|
(
|
|
id integer not null
|
|
constraint t_geofence_pkey
|
|
primary key,
|
|
name varchar(60) not null,
|
|
typeid integer
|
|
constraint t_geofence_typeid_fkey
|
|
references t_geofence_type,
|
|
locations text,
|
|
projectid integer,
|
|
is_enable boolean,
|
|
limitspeed numeric(5, 2)
|
|
);
|
|
|
|
comment on column t_geofence.name is '围栏名称';
|
|
|
|
comment on column t_geofence.typeid is '围栏类型';
|
|
|
|
comment on column t_geofence.locations is '围栏点数组字符串';
|
|
|
|
comment on column t_geofence.projectid is '项目id';
|
|
|
|
comment on column t_geofence.is_enable is '是否启用';
|
|
|
|
create table if not exists t_group_type
|
|
(
|
|
type_code varchar(50) not null
|
|
constraint t_group_type_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
description varchar(100),
|
|
params jsonb not null,
|
|
present boolean not null
|
|
);
|
|
|
|
create table if not exists t_group_type_factor
|
|
(
|
|
id serial not null
|
|
constraint t_group_type_factor_pkey
|
|
primary key,
|
|
group_type varchar(50) not null
|
|
constraint t_group_type_factor_group_type_fkey
|
|
references t_group_type,
|
|
proto varchar(30) not null
|
|
constraint t_group_type_factor_proto_fkey
|
|
references t_factor_proto
|
|
);
|
|
|
|
create table if not exists t_item_unit
|
|
(
|
|
id serial not null
|
|
constraint t_item_unit_pkey
|
|
primary key,
|
|
name varchar(50),
|
|
item integer not null
|
|
constraint t_item_unit_item_fkey
|
|
references t_factor_proto_item,
|
|
if_default boolean,
|
|
transform numeric(18, 6)
|
|
);
|
|
|
|
create table if not exists t_layout_type
|
|
(
|
|
id serial not null
|
|
constraint t_layout_type_pkey
|
|
primary key,
|
|
name varchar(100)
|
|
);
|
|
|
|
create table if not exists t_message_type
|
|
(
|
|
id serial not null
|
|
constraint t_message_type_pkey
|
|
primary key,
|
|
name varchar(100) not null
|
|
);
|
|
|
|
|
|
|
|
create table if not exists t_phone_validate_code
|
|
(
|
|
id serial not null
|
|
constraint t_phone_validate_code_pkey
|
|
primary key,
|
|
phone varchar(20) not null,
|
|
code varchar(6) not null,
|
|
sig varchar(40) not null,
|
|
expired timestamp(6) with time zone not null
|
|
);
|
|
|
|
create table if not exists t_project_type
|
|
(
|
|
id serial not null
|
|
constraint t_project_type_pkey
|
|
primary key,
|
|
type_name varchar(20) not null,
|
|
description varchar(20) not null
|
|
);
|
|
|
|
create table if not exists t_rational_device
|
|
(
|
|
id serial not null
|
|
constraint t_rational_device_pkey
|
|
primary key,
|
|
iota_device_id uuid not null,
|
|
item integer
|
|
constraint t_rational_device_item_fkey
|
|
references t_factor_proto_item,
|
|
lower numeric(12, 5),
|
|
upper numeric(12, 5),
|
|
enabled boolean default true not null
|
|
);
|
|
|
|
create table if not exists t_recalc_product_formula
|
|
(
|
|
id serial not null
|
|
constraint t_recalc_product_formula_pkey
|
|
primary key,
|
|
iota_product_code uuid not null,
|
|
iota_product_formula uuid,
|
|
formula integer not null
|
|
constraint t_recalc_product_formula_t_formula_id_fk
|
|
references t_formula,
|
|
fields jsonb not null
|
|
);
|
|
|
|
create table if not exists t_resource
|
|
(
|
|
code varchar(50) not null
|
|
constraint t_resource_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
description varchar(100),
|
|
type integer,
|
|
parent_resource varchar(50)
|
|
);
|
|
|
|
create table if not exists t_structure_component
|
|
(
|
|
id serial not null
|
|
constraint t_structure_component_pkey
|
|
primary key,
|
|
structtypeid integer not null,
|
|
componentid integer not null
|
|
);
|
|
|
|
|
|
|
|
|
|
create table if not exists t_structure_type
|
|
(
|
|
id serial not null
|
|
constraint t_structure_type_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
description varchar(100),
|
|
parent_type integer not null,
|
|
portrait varchar(512)
|
|
);
|
|
|
|
create table if not exists t_structure_type_factor
|
|
(
|
|
id serial not null
|
|
constraint t_structure_type_factor_pkey
|
|
primary key,
|
|
structure_type integer not null
|
|
constraint t_structure_type_factor_structure_type_fkey
|
|
references t_structure_type,
|
|
factor integer not null
|
|
constraint t_structure_type_factor_factor_fkey
|
|
references t_factor
|
|
);
|
|
|
|
|
|
|
|
|
|
create table if not exists t_type_alarm_source
|
|
(
|
|
id integer not null
|
|
constraint t_type_alarm_source_pkey
|
|
primary key,
|
|
name varchar(50),
|
|
description varchar(100)
|
|
);
|
|
|
|
create table if not exists t_type_alarm_state
|
|
(
|
|
id integer not null
|
|
constraint t_type_alarm_state_pkey
|
|
primary key,
|
|
name varchar(50),
|
|
description varchar(100)
|
|
);
|
|
|
|
create table if not exists t_type_event_index
|
|
(
|
|
id serial not null
|
|
constraint t_type_event_index_pkey
|
|
primary key,
|
|
name varchar(10) not null,
|
|
description varchar(20)
|
|
);
|
|
|
|
create table if not exists t_type_file_type
|
|
(
|
|
id serial not null
|
|
constraint t_type_file_type_pkey
|
|
primary key,
|
|
name varchar(50) not null
|
|
);
|
|
|
|
create table if not exists t_type_institution_role
|
|
(
|
|
id serial not null
|
|
constraint t_type_institution_role_pkey
|
|
primary key,
|
|
name varchar(50),
|
|
description varchar(100)
|
|
);
|
|
|
|
create table if not exists t_institution
|
|
(
|
|
id serial not null
|
|
constraint t_institution_pkey
|
|
primary key,
|
|
institutionname varchar(50) not null,
|
|
institutionrole integer not null
|
|
constraint t_type_institution_role_fkey
|
|
references t_type_institution_role,
|
|
orgid integer,
|
|
institution_corporation varchar(50),
|
|
institution_corporation_contact varchar(50)
|
|
);
|
|
|
|
create table if not exists t_type_org_type
|
|
(
|
|
id serial not null
|
|
constraint t_type_org_type_pkey
|
|
primary key,
|
|
name varchar(50),
|
|
description varchar(100)
|
|
);
|
|
|
|
create table if not exists t_organization
|
|
(
|
|
id serial not null
|
|
constraint t_organization_pkey
|
|
primary key,
|
|
org_type integer not null
|
|
constraint t_organization_org_type_fkey
|
|
references t_type_org_type,
|
|
name varchar(100) not null,
|
|
domain varchar(100),
|
|
state integer not null,
|
|
aptitude_file varchar(512),
|
|
register_code varchar(50),
|
|
logo varchar(512),
|
|
scale varchar(100),
|
|
region varchar(200),
|
|
app_key char(40),
|
|
app_secret char(40)
|
|
);
|
|
|
|
create table if not exists t_alarm_policy
|
|
(
|
|
id serial not null
|
|
constraint t_alarm_policy_pkey
|
|
primary key,
|
|
structures integer[] not null,
|
|
sms_noticed boolean not null,
|
|
notice_users integer[] not null,
|
|
enabled boolean not null,
|
|
created_organization integer not null
|
|
constraint t_alarm_policy_t_organization_id_fk
|
|
references t_organization,
|
|
alarm_categories integer[] not null,
|
|
email_noticed boolean not null,
|
|
email_alarm_levels integer[],
|
|
sms_alarm_levels integer[],
|
|
wx_noticed boolean default false not null,
|
|
wx_alarm_levels integer[],
|
|
extras jsonb,
|
|
broadcast_noticed boolean default false,
|
|
broadcast_alarm_levels integer[],
|
|
broadcast_device_id varchar(50)
|
|
);
|
|
|
|
create table if not exists t_department
|
|
(
|
|
id serial not null
|
|
constraint t_department_pkey
|
|
primary key,
|
|
org integer not null
|
|
constraint t_department_org_fkey
|
|
references t_organization,
|
|
name varchar(50) not null,
|
|
parent_id integer,
|
|
sort_index integer
|
|
);
|
|
|
|
comment on column t_department.parent_id is '父级部门id';
|
|
|
|
comment on column t_department.sort_index is '排序';
|
|
|
|
create table if not exists t_factor_template
|
|
(
|
|
id serial not null
|
|
constraint t_factor_template_pkey
|
|
primary key,
|
|
structtype integer not null
|
|
constraint t_factor_template_structtype_fkey
|
|
references t_structure_type,
|
|
name varchar(100) not null,
|
|
org integer
|
|
constraint t_factor_template_org_fkey
|
|
references t_organization,
|
|
publish boolean not null,
|
|
description varchar(100)
|
|
);
|
|
|
|
create table if not exists t_factor_template_factor
|
|
(
|
|
id serial not null
|
|
constraint t_factor_template_factor_id_pk
|
|
primary key,
|
|
template_id integer not null
|
|
constraint t_factor_template_factor_t_factor_template_id_fk
|
|
references t_factor_template,
|
|
factor integer not null
|
|
constraint t_factor_template_factor_t_factor_id_fk
|
|
references t_factor
|
|
|
|
);
|
|
|
|
create table if not exists t_structure
|
|
(
|
|
id serial not null
|
|
constraint t_structure_pkey
|
|
primary key,
|
|
iota_thing_id uuid not null,
|
|
structure_type integer not null
|
|
constraint t_structure_structure_type_fkey
|
|
references t_structure_type,
|
|
name varchar(50),
|
|
description varchar(100),
|
|
region_path varchar(100),
|
|
longitude numeric(20, 14),
|
|
latitude numeric(20, 14),
|
|
address varchar(100),
|
|
portrait varchar(512),
|
|
extra_info jsonb,
|
|
org integer not null
|
|
constraint t_structure_org_fkey
|
|
references t_organization,
|
|
create_time timestamp(6) with time zone default now() not null
|
|
);
|
|
|
|
create table if not exists t_agg_config
|
|
(
|
|
id serial not null
|
|
constraint t_agg_config_pkey
|
|
primary key,
|
|
struct_id integer not null
|
|
constraint t_agg_config_struct_id_fkey
|
|
references t_structure,
|
|
factor_id integer not null
|
|
constraint t_agg_config_factor_id_fkey
|
|
references t_factor,
|
|
enabled boolean default true not null,
|
|
category integer not null,
|
|
algorithm integer not null,
|
|
start_day smallint,
|
|
end_day smallint,
|
|
start_hour smallint,
|
|
end_hour smallint,
|
|
delete boolean default false not null,
|
|
start_time integer not null,
|
|
time_range json
|
|
);
|
|
|
|
create unique index if not exists t_agg_config_id_uindex
|
|
on t_agg_config (id);
|
|
|
|
create table if not exists t_agg_threshold_config
|
|
(
|
|
id serial not null
|
|
constraint t_agg_threshold_config_pkey
|
|
primary key,
|
|
struct_id integer not null
|
|
constraint t_agg_threshold_config_struct_id_fkey
|
|
references t_structure,
|
|
factor_id integer not null
|
|
constraint t_agg_threshold_config_factor_id_fkey
|
|
references t_factor,
|
|
agg_category integer not null,
|
|
item_id smallint not null,
|
|
threshold_level smallint not null,
|
|
threshold_lower double precision not null,
|
|
threshold_upper double precision not null,
|
|
start_hour smallint,
|
|
end_hour smallint
|
|
);
|
|
|
|
create unique index if not exists t_agg_threshold_config_id_uindex
|
|
on t_agg_threshold_config (id);
|
|
|
|
create table if not exists t_alarm
|
|
(
|
|
id serial not null
|
|
constraint t_alarm_pkey
|
|
primary key,
|
|
alarm_type integer not null
|
|
constraint t_alarm_alarm_type_fkey
|
|
references t_alarm_type,
|
|
source_type integer not null
|
|
constraint t_alarm_source_type_fkey
|
|
references t_type_alarm_source,
|
|
structure integer not null
|
|
constraint t_alarm_structure_fkey
|
|
references t_structure,
|
|
device uuid,
|
|
sensor integer,
|
|
alarm_info varchar(256) not null,
|
|
alarm_level integer not null,
|
|
alarm_count integer not null,
|
|
begin_time timestamp(6) with time zone not null,
|
|
end_time timestamp(6) with time zone,
|
|
alarm_state integer not null
|
|
constraint t_alarm_alarm_state_fkey
|
|
references t_type_alarm_state
|
|
);
|
|
|
|
create table if not exists t_alarm_detail
|
|
(
|
|
id bigserial not null
|
|
constraint t_alarm_detail_pkey
|
|
primary key,
|
|
alarm integer not null
|
|
constraint t_alarm_detail_alarm_fkey
|
|
references t_alarm,
|
|
alarm_info varchar(256) not null,
|
|
alarm_time timestamp(6) with time zone not null
|
|
);
|
|
|
|
create table if not exists t_calendar
|
|
(
|
|
id serial not null
|
|
constraint t_calendar_pkey
|
|
primary key,
|
|
org integer not null
|
|
constraint t_calendar_org_fkey
|
|
references t_organization,
|
|
type integer not null
|
|
constraint t_calendar_type_fkey
|
|
references t_calendar_type,
|
|
calendar_time timestamp(6) with time zone not null,
|
|
calendar_content varchar(512),
|
|
structure integer
|
|
constraint t_calendar_t_structure_id_fk
|
|
references t_structure
|
|
);
|
|
|
|
create table if not exists t_department_structure
|
|
(
|
|
id serial not null
|
|
constraint t_department_structure_pkey
|
|
primary key,
|
|
department integer not null
|
|
constraint t_department_structure_department_fkey
|
|
references t_department,
|
|
structure integer not null
|
|
constraint t_department_structure_structure_fkey
|
|
references t_structure,
|
|
role_id integer,
|
|
user_id integer
|
|
);
|
|
|
|
create table if not exists t_event_config
|
|
(
|
|
id serial not null
|
|
constraint t_event_config_pkey
|
|
primary key,
|
|
"structId" integer not null
|
|
constraint "structId"
|
|
references t_structure,
|
|
"indexId" integer not null
|
|
constraint "indexId"
|
|
references t_type_event_index,
|
|
weight integer not null
|
|
);
|
|
|
|
comment on column t_event_config."indexId" is 't_type_event_index';
|
|
|
|
comment on column t_event_config.weight is '比重';
|
|
|
|
create table if not exists t_group
|
|
(
|
|
id serial not null
|
|
constraint t_group_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
group_type varchar(50) not null
|
|
constraint t_group_group_type_fkey
|
|
references t_group_type,
|
|
factor integer not null
|
|
constraint t_group_factor_fkey
|
|
references t_factor,
|
|
structure integer not null
|
|
constraint t_group_structure_fkey
|
|
references t_structure,
|
|
params jsonb default '{}'::jsonb not null
|
|
);
|
|
|
|
create table if not exists t_layout_model
|
|
(
|
|
id serial not null
|
|
constraint t_layout_model_pkey
|
|
primary key,
|
|
layout integer not null
|
|
constraint t_layout_model_layout_fkey
|
|
references t_layout_type,
|
|
params jsonb not null,
|
|
structure integer not null
|
|
constraint t_layout_model_structure_fkey
|
|
references t_structure
|
|
);
|
|
|
|
create table if not exists t_sensor
|
|
(
|
|
id serial not null
|
|
constraint t_sensor_pkey
|
|
primary key,
|
|
structure integer not null
|
|
constraint t_sensor_structure_fkey
|
|
references t_structure,
|
|
name varchar(50) not null,
|
|
factor integer not null
|
|
constraint t_sensor_factor_fkey
|
|
references t_factor,
|
|
portrait varchar(512),
|
|
labels varchar(100)[],
|
|
manual_data boolean default false not null,
|
|
extras jsonb
|
|
);
|
|
|
|
create table if not exists t_abn_report_params
|
|
(
|
|
id serial not null
|
|
constraint t_abn_report_params_id_pk
|
|
primary key,
|
|
station_id integer not null
|
|
constraint t_abn_report_params_t_sensor_id_fk
|
|
references t_sensor,
|
|
factor_proto_code varchar(30) not null
|
|
constraint t_abn_report_params_t_factor_proto_code_fk
|
|
references t_factor_proto,
|
|
itemid integer,
|
|
abn_type integer
|
|
constraint t_abn_report_params_t_abn_type_id_fk
|
|
references t_abn_type,
|
|
params jsonb not null,
|
|
enabled boolean default true not null
|
|
);
|
|
|
|
create unique index if not exists t_abn_report_params_id_uindex
|
|
on t_abn_report_params (id);
|
|
|
|
create table if not exists t_data_sensor_latest
|
|
(
|
|
id serial not null
|
|
constraint t_data_sensor_latest_pkey
|
|
primary key,
|
|
sensor integer not null
|
|
constraint t_data_sensor_latest_sensor_key
|
|
unique
|
|
constraint t_data_sensor_latest_sensor_fkey
|
|
references t_sensor,
|
|
latest_data jsonb not null,
|
|
collect_time timestamp(6) with time zone not null,
|
|
violate_rational_times integer not null,
|
|
state integer not null
|
|
);
|
|
|
|
create table if not exists t_device_sensor
|
|
(
|
|
id serial not null
|
|
constraint t_device_sensor_pkey
|
|
primary key,
|
|
iota_device_id uuid not null,
|
|
iota_device_serial integer default 0 not null,
|
|
sensor integer not null
|
|
constraint t_device_sensor_sensor_fkey
|
|
references t_sensor,
|
|
params jsonb
|
|
);
|
|
|
|
create table if not exists t_filter_config
|
|
(
|
|
id serial not null
|
|
constraint t_filter_config_pkey
|
|
primary key,
|
|
sensor integer
|
|
constraint t_filter_config_sensor_fkey
|
|
references t_sensor,
|
|
item integer
|
|
constraint t_filter_config_item_fkey
|
|
references t_factor_proto_item,
|
|
method integer
|
|
constraint t_filter_config_method_fkey
|
|
references t_filter_method,
|
|
window_size integer not null,
|
|
params jsonb,
|
|
enable boolean default true not null,
|
|
updatetime timestamp(6) with time zone not null,
|
|
iswork text default 'false'::text
|
|
);
|
|
|
|
create table if not exists t_group_sensor
|
|
(
|
|
id serial not null
|
|
constraint t_group_sensor_pkey
|
|
primary key,
|
|
group_id integer not null
|
|
constraint t_group_sensor_group_id_fkey
|
|
references t_group,
|
|
sensor integer not null
|
|
constraint t_group_sensor_sensor_fkey
|
|
references t_sensor,
|
|
params_value jsonb
|
|
);
|
|
|
|
create table if not exists t_rational
|
|
(
|
|
id serial not null
|
|
constraint t_rational_pkey
|
|
primary key,
|
|
sensor integer
|
|
constraint t_rational_sensor_fkey
|
|
references t_sensor,
|
|
item integer
|
|
constraint t_rational_item_fkey
|
|
references t_factor_proto_item,
|
|
lower numeric(12, 5),
|
|
upper numeric(12, 5),
|
|
enabled boolean default true not null
|
|
);
|
|
|
|
create table if not exists t_rational_filter
|
|
(
|
|
id serial not null
|
|
constraint t_rational_filter_pkey
|
|
primary key,
|
|
sensor integer not null
|
|
constraint t_rational_filter_sensor_fkey
|
|
references t_sensor,
|
|
item integer not null
|
|
constraint t_rational_filter_item_fkey
|
|
references t_factor_proto_item,
|
|
rationallower numeric(10, 4) not null,
|
|
rationalupper numeric(10, 4) not null,
|
|
enabled boolean not null
|
|
);
|
|
|
|
create table if not exists t_sensor_factor_threshold
|
|
(
|
|
id serial not null
|
|
constraint t_sensor_factor_threshold_pkey
|
|
primary key,
|
|
batch_no varchar(100),
|
|
sensor integer
|
|
constraint t_sensor_factor_threshold_sensor_fkey
|
|
references t_sensor,
|
|
item integer
|
|
constraint t_sensor_factor_threshold_item_fkey
|
|
references t_factor_proto_item,
|
|
level integer,
|
|
lower numeric(12, 5),
|
|
upper numeric(12, 5),
|
|
start_hour integer,
|
|
end_hour integer,
|
|
description varchar(100)
|
|
);
|
|
|
|
create table if not exists t_sensor_filter_config
|
|
(
|
|
id serial not null
|
|
constraint t_sensor_filter_config_pkey
|
|
primary key,
|
|
sensor integer not null
|
|
constraint t_sensor_filter_config_sensor_fkey
|
|
references t_sensor,
|
|
item integer not null
|
|
constraint t_sensor_filter_config_item_fkey
|
|
references t_factor_proto_item,
|
|
method integer not null
|
|
constraint t_sensor_filter_config_method_fkey
|
|
references t_filter_method,
|
|
params_value jsonb not null,
|
|
enabled boolean not null,
|
|
update_time timestamp(6) with time zone
|
|
);
|
|
|
|
create table if not exists t_sensor_formula
|
|
(
|
|
id serial not null
|
|
constraint t_sensor_formula_pkey
|
|
primary key,
|
|
sensor integer not null
|
|
constraint t_sensor_formula_sensor_fkey
|
|
references t_sensor,
|
|
formula integer not null
|
|
constraint t_sensor_formula_formula_fkey
|
|
references t_formula,
|
|
params_value jsonb not null
|
|
);
|
|
|
|
create table if not exists t_sensor_layout
|
|
(
|
|
id serial not null
|
|
constraint t_sensor_layout_pkey
|
|
primary key,
|
|
sensor integer
|
|
constraint t_sensor_layout_sensor_fkey
|
|
references t_sensor,
|
|
model integer not null
|
|
constraint t_sensor_layout_model_fkey
|
|
references t_layout_model,
|
|
position jsonb,
|
|
modal_layout integer
|
|
constraint t_sensor_layout_t_layout_model__fk
|
|
references t_layout_model
|
|
);
|
|
|
|
create table if not exists t_structure_factor
|
|
(
|
|
id serial not null
|
|
constraint t_structure_factor_pkey
|
|
primary key,
|
|
structure integer not null
|
|
constraint t_structure_factor_structure_fkey
|
|
references t_structure,
|
|
factor integer not null
|
|
constraint t_structure_factor_factor_fkey
|
|
references t_factor,
|
|
alias_name varchar(50)
|
|
);
|
|
|
|
|
|
|
|
create table if not exists t_type_project_state
|
|
(
|
|
id integer not null
|
|
constraint t_type_project_state_pkey
|
|
primary key,
|
|
name varchar(50),
|
|
description varchar(100)
|
|
);
|
|
|
|
create table if not exists t_project
|
|
(
|
|
id serial not null
|
|
constraint t_project_pkey
|
|
primary key,
|
|
name varchar(50),
|
|
logo varchar(256),
|
|
theme varchar(256),
|
|
url varchar(256),
|
|
marked boolean,
|
|
create_time timestamp(6) with time zone not null,
|
|
update_time timestamp(6) with time zone,
|
|
project_state integer not null
|
|
constraint t_project_project_state_fkey
|
|
references t_type_project_state,
|
|
describe varchar(512),
|
|
type integer default 0 not null,
|
|
extra jsonb,
|
|
event_state boolean default false
|
|
);
|
|
|
|
create table if not exists t_organization_project
|
|
(
|
|
id serial not null
|
|
constraint t_organization_project_pkey
|
|
primary key,
|
|
org integer not null
|
|
constraint t_organization_project_org_fkey
|
|
references t_organization,
|
|
project integer not null
|
|
constraint t_organization_project_project_fkey
|
|
references t_project
|
|
);
|
|
|
|
create table if not exists t_project_construction
|
|
(
|
|
id serial not null
|
|
constraint t_project_construction_pkey
|
|
primary key,
|
|
project integer not null
|
|
constraint t_construction_t_project_id_fk
|
|
references t_project,
|
|
construction integer not null
|
|
constraint t_construction_t_construction_id_fk
|
|
references t_construction
|
|
);
|
|
|
|
create table if not exists t_project_structure
|
|
(
|
|
id serial not null
|
|
constraint t_project_structure_pkey
|
|
primary key,
|
|
project integer not null
|
|
constraint t_project_structure_project_fkey
|
|
references t_project,
|
|
structure integer not null
|
|
constraint t_project_structure_structure_fkey
|
|
references t_structure
|
|
);
|
|
|
|
|
|
|
|
create table if not exists t_type_report_state
|
|
(
|
|
id serial not null
|
|
constraint t_type_report_state_pkey
|
|
primary key,
|
|
name varchar(50),
|
|
description varchar(100)
|
|
);
|
|
|
|
create table if not exists t_type_report_type
|
|
(
|
|
id serial not null
|
|
constraint t_type_report_type_pkey
|
|
primary key,
|
|
name varchar(50),
|
|
description varchar(100)
|
|
);
|
|
|
|
create table if not exists t_report_generate
|
|
(
|
|
id serial not null
|
|
constraint t_report_generate_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
structure integer not null
|
|
constraint t_report_generate_structure_fkey
|
|
references t_structure,
|
|
report_type integer not null
|
|
constraint t_report_generate_report_type_fkey
|
|
references t_type_report_type,
|
|
template_cfg jsonb not null,
|
|
interval varchar(50) not null,
|
|
confirm boolean not null,
|
|
enabled boolean not null,
|
|
manual_monitoring boolean default false,
|
|
agg_config jsonb,
|
|
doc_type varchar(5)
|
|
);
|
|
|
|
create table if not exists t_report_template
|
|
(
|
|
id serial not null
|
|
constraint t_report_template_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
description varchar(100),
|
|
handler varchar(50) not null,
|
|
file_link varchar(512) not null,
|
|
factor_proto varchar(30),
|
|
report_type integer not null
|
|
constraint t_report_template_report_type_fkey
|
|
references t_type_report_type,
|
|
struct_type integer[] default '{}'::integer[] not null,
|
|
cells jsonb,
|
|
manual boolean default false,
|
|
structs integer[] default '{}'::integer[],
|
|
params jsonb,
|
|
doc_type varchar(5)
|
|
);
|
|
|
|
|
|
|
|
create table if not exists t_type_role_type
|
|
(
|
|
code varchar(2) not null
|
|
constraint t_type_role_type_pkey
|
|
primary key,
|
|
description varchar(20) not null
|
|
);
|
|
|
|
create table if not exists t_unit
|
|
(
|
|
id serial not null
|
|
constraint t_unit_pkey
|
|
primary key,
|
|
name varchar(10) not null
|
|
);
|
|
|
|
|
|
|
|
create unique index if not exists t_unit_id_uindex
|
|
on t_unit (id);
|
|
|
|
create table if not exists t_units
|
|
(
|
|
name varchar(20) not null,
|
|
dimension varchar(20),
|
|
description varchar(255),
|
|
coef numeric(32, 16),
|
|
base boolean default false not null,
|
|
alternative varchar(255)
|
|
);
|
|
|
|
create table if not exists t_upload_comm_http
|
|
(
|
|
id serial not null
|
|
constraint t_upload_comm_http_pkey
|
|
primary key,
|
|
content json not null,
|
|
enable boolean default true not null,
|
|
description varchar(255)
|
|
);
|
|
|
|
comment on table t_upload_comm_http is 'ET数据上报消费者(upload)普通HTTP上报类配置表';
|
|
|
|
create table if not exists t_upload_params
|
|
(
|
|
id serial not null
|
|
constraint t_upload_params_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
params jsonb,
|
|
description varchar(255)
|
|
);
|
|
|
|
create table if not exists t_user_token
|
|
(
|
|
token uuid not null
|
|
constraint t_user_token_copy1_pkey
|
|
primary key,
|
|
user_info jsonb not null,
|
|
expired timestamp(6) with time zone not null
|
|
);
|
|
|
|
comment on column t_user_token.token is '登录令牌';
|
|
|
|
comment on column t_user_token.user_info is '用户信息';
|
|
|
|
create table if not exists t_vehicle_overload
|
|
(
|
|
id serial not null
|
|
constraint t_vehicle_overload_pkey
|
|
primary key,
|
|
axis_num varchar(100) not null,
|
|
overload_standard numeric(18, 6) not null,
|
|
over_heigth numeric(18, 6) not null,
|
|
over_len numeric(18, 6) not null,
|
|
overwidth numeric(18, 6) not null
|
|
);
|
|
|
|
create table if not exists t_video_nvr_vendor
|
|
(
|
|
id serial not null
|
|
constraint t_video_nvr_vendor_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
enabled boolean default false not null
|
|
);
|
|
|
|
create table if not exists t_video_nvr
|
|
(
|
|
id serial not null
|
|
constraint t_video_nvr_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
ip varchar(50) not null,
|
|
port integer not null,
|
|
username varchar(50) not null,
|
|
password varchar(50) not null,
|
|
channels_total integer not null,
|
|
vendor integer not null
|
|
constraint t_video_nvr_t_video_nvr_vendor_id_fk
|
|
references t_video_nvr_vendor,
|
|
structure integer not null
|
|
constraint t_video_nvr_t_structure_id_fk
|
|
references t_structure
|
|
);
|
|
|
|
create table if not exists t_video_push_server
|
|
(
|
|
id serial not null
|
|
constraint t_video_push_server_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
ip varchar(15) not null,
|
|
port integer not null
|
|
);
|
|
|
|
create table if not exists t_video_ipc
|
|
(
|
|
id serial not null
|
|
constraint t_video_ipc_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
channel_no integer,
|
|
push_server integer
|
|
constraint t_video_ipc_t_video_push_server_id_fk
|
|
references t_video_push_server,
|
|
nvr integer
|
|
constraint t_video_ipc_t_video_nvr_id_fk
|
|
references t_video_nvr,
|
|
structure integer not null
|
|
constraint t_video_ipc_t_structure_id_fk
|
|
references t_structure,
|
|
has_ptz boolean default false not null,
|
|
longitude numeric(20, 14),
|
|
latitude numeric(20, 14),
|
|
type varchar(10) default 'original'::character varying not null,
|
|
uid varchar(255),
|
|
username varchar(255),
|
|
password varchar(255),
|
|
serial_no varchar(50),
|
|
rtmp_address varchar(255),
|
|
hls_address varchar(255)
|
|
);
|
|
|
|
create table if not exists t_video_ipc_station
|
|
(
|
|
id serial not null
|
|
constraint t_video_ipc_station_pkey
|
|
primary key,
|
|
ipc integer not null
|
|
constraint t_video_ipc_station_t_video_ipc_id_fk
|
|
references t_video_ipc,
|
|
station integer not null
|
|
constraint t_video_ipc_station_t_sensor_id_fk
|
|
references t_sensor
|
|
);
|
|
|
|
create table if not exists t_weather_history
|
|
(
|
|
id serial not null
|
|
constraint t_weather_history_pkey
|
|
primary key,
|
|
"structId" integer not null,
|
|
weather varchar(20) not null,
|
|
date date not null
|
|
);
|
|
|
|
create table if not exists t_wise_config
|
|
(
|
|
id serial not null
|
|
constraint t_wise_config_pkey
|
|
primary key,
|
|
structid varchar(50) not null,
|
|
config jsonb not null
|
|
);
|
|
|
|
create table if not exists t_workflow_business
|
|
(
|
|
id serial not null
|
|
constraint t_workflow_business_pkey
|
|
primary key,
|
|
procdef_key varchar(100) not null,
|
|
procdef_id varchar(100) not null,
|
|
procinst_id integer,
|
|
created_by varchar(50) not null,
|
|
created_at timestamp(6) with time zone default now() not null
|
|
);
|
|
|
|
comment on column t_workflow_business.created_by is '创建人';
|
|
|
|
comment on column t_workflow_business.created_at is '创建时间';
|
|
|
|
create table if not exists t_workflow_form_proto
|
|
(
|
|
id serial not null
|
|
constraint t_workflow_form_proto_pkey
|
|
primary key,
|
|
name varchar(100) not null,
|
|
form_metas jsonb not null,
|
|
procdef_key varchar(100) not null,
|
|
procdef_id varchar(100) not null,
|
|
actdef_key varchar(100) not null,
|
|
belongto_startevent boolean default false not null
|
|
);
|
|
|
|
create table if not exists t_wx_subscribe
|
|
(
|
|
open_id varchar(50) not null
|
|
constraint t_wx_subscribe_pkey
|
|
primary key,
|
|
union_id varchar(50) not null
|
|
);
|
|
|
|
create table if not exists tmp_role_resource
|
|
(
|
|
role integer,
|
|
resource varchar(50)
|
|
);
|
|
|
|
create table if not exists t_role_group
|
|
(
|
|
id serial not null
|
|
constraint t_role_group_pk
|
|
primary key,
|
|
name varchar(60) not null,
|
|
org integer not null
|
|
constraint t_role_group_t_organization_id_fk
|
|
references t_organization
|
|
);
|
|
|
|
comment on table t_role_group is '角色组表';
|
|
|
|
comment on column t_role_group.name is '角色组名称';
|
|
|
|
comment on column t_role_group.org is '所属组织id';
|
|
|
|
create table if not exists t_role
|
|
(
|
|
id serial not null
|
|
constraint t_role_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
portal varchar(2) default 'AC'::character varying not null,
|
|
description varchar(100),
|
|
group_id integer not null
|
|
constraint t_role_t_role_group_id_fk
|
|
references t_role_group
|
|
);
|
|
|
|
comment on column t_role.group_id is '所属角色组';
|
|
|
|
create table if not exists t_role_resource
|
|
(
|
|
id serial not null
|
|
constraint t_role_resource_pkey
|
|
primary key,
|
|
role integer not null
|
|
constraint t_role_resource_role_fkey
|
|
references t_role,
|
|
resource varchar(50) not null
|
|
constraint t_role_resource_resource_fkey
|
|
references t_resource,
|
|
constraint t_role_resource_role_resource_uindex
|
|
unique (role, resource)
|
|
);
|
|
|
|
create table if not exists t_type_post
|
|
(
|
|
id serial not null
|
|
constraint t_type_post_pk
|
|
primary key,
|
|
name varchar(60) not null,
|
|
allow_set_num integer default 0 not null,
|
|
code varchar(60) not null
|
|
);
|
|
|
|
comment on table t_type_post is '职位固化表';
|
|
|
|
comment on column t_type_post.name is '职位名称';
|
|
|
|
comment on column t_type_post.allow_set_num is '可配置人数，0不限制';
|
|
|
|
create table if not exists t_user
|
|
(
|
|
id serial not null
|
|
constraint t_user_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
name_present varchar(50),
|
|
password varchar(256),
|
|
phone varchar(50),
|
|
email varchar(50),
|
|
avator varchar(512),
|
|
org integer not null
|
|
constraint t_user_org_fkey
|
|
references t_organization,
|
|
register_time timestamp(6) with time zone,
|
|
mail_notice boolean default false not null,
|
|
sms_notice boolean default false not null,
|
|
no_disturb boolean default false not null,
|
|
enabled boolean not null,
|
|
open_id varchar(50),
|
|
nick_name varchar(50),
|
|
wx_notice boolean default false not null,
|
|
union_id varchar(50),
|
|
institution_role varchar(50) default NULL::character varying,
|
|
dep integer not null
|
|
constraint t_user_t_department_id_fk
|
|
references t_department,
|
|
post integer not null
|
|
constraint t_user_t_type_post_id_fk
|
|
references t_type_post,
|
|
sort_index integer
|
|
);
|
|
|
|
comment on column t_user.dep is '直属部门id';
|
|
|
|
comment on column t_user.post is '职位id';
|
|
|
|
comment on column t_user.sort_index is '排序';
|
|
|
|
create table if not exists t_alarm_deal
|
|
(
|
|
id serial not null
|
|
constraint t_alarm_deal_pkey
|
|
primary key,
|
|
alarm integer not null
|
|
constraint t_alarm_deal_alarm_fkey
|
|
references t_alarm,
|
|
deal_user integer not null
|
|
constraint t_alarm_deal_deal_user_fkey
|
|
references t_user,
|
|
deal_info varchar(256),
|
|
deal_time timestamp(6) with time zone not null
|
|
);
|
|
|
|
create table if not exists t_app_message
|
|
(
|
|
id serial not null
|
|
constraint t_app_message_pkey
|
|
primary key,
|
|
project_id integer not null
|
|
constraint t_app_message_project_id_fkey
|
|
references t_project
|
|
on delete cascade,
|
|
title varchar(128) not null,
|
|
content varchar(4096) not null,
|
|
post_time timestamp(6) with time zone not null,
|
|
post_user_id integer not null
|
|
constraint t_app_message_post_user_id_fkey
|
|
references t_user
|
|
on delete cascade,
|
|
expired_time timestamp(6) with time zone not null
|
|
);
|
|
|
|
create table if not exists t_message
|
|
(
|
|
id serial not null
|
|
constraint t_message_pkey
|
|
primary key,
|
|
user_id integer not null
|
|
constraint t_message_user_id_fkey
|
|
references t_user,
|
|
message_type integer not null
|
|
constraint t_message_message_type_fkey
|
|
references t_message_type,
|
|
generate_time timestamp(6) with time zone not null,
|
|
content varchar(512),
|
|
read_time timestamp(6) with time zone,
|
|
readed boolean default false not null
|
|
);
|
|
|
|
create table if not exists t_netdisk_file
|
|
(
|
|
id serial not null
|
|
constraint t_netdisk_file_pkey
|
|
primary key,
|
|
file_type integer not null
|
|
constraint t_netdisk_file_file_type_fkey
|
|
references t_type_file_type,
|
|
file_name varchar(100) not null,
|
|
file_ext varchar(30),
|
|
file_size integer not null,
|
|
file_link varchar(512) not null,
|
|
update_user integer not null
|
|
constraint t_netdisk_file_update_user_fkey
|
|
references t_user,
|
|
update_time timestamp(6) with time zone,
|
|
extra_info jsonb
|
|
);
|
|
|
|
create table if not exists t_project_approve
|
|
(
|
|
id serial not null
|
|
constraint t_project_approve_pkey
|
|
primary key,
|
|
project integer not null
|
|
constraint t_project_approve_project_fkey
|
|
references t_project,
|
|
approve_user integer not null
|
|
constraint t_project_approve_approve_user_fkey
|
|
references t_user,
|
|
approve_time timestamp(6) with time zone not null,
|
|
project_state integer not null,
|
|
approve_info varchar(2048)
|
|
);
|
|
|
|
create index if not exists idx_project_approve
|
|
on t_project_approve (project, approve_time, project_state);
|
|
|
|
create table if not exists t_task_recalculate
|
|
(
|
|
id serial not null
|
|
constraint t_task_recalculate_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
emit_user integer not null
|
|
constraint t_task_recalculate_emit_user_fkey
|
|
references t_user,
|
|
station_id integer not null,
|
|
data_begin_time timestamp(6) with time zone not null,
|
|
data_end_time timestamp(6) with time zone not null,
|
|
task_begin_time timestamp(6) with time zone,
|
|
task_end_time timestamp(6) with time zone,
|
|
task_state integer not null,
|
|
task_result jsonb,
|
|
msg_id uuid not null
|
|
);
|
|
|
|
create index if not exists idx_task_recalculate_d_time
|
|
on t_task_recalculate (data_begin_time, data_end_time);
|
|
|
|
create index if not exists idx_task_recalculate_t_time
|
|
on t_task_recalculate (task_begin_time, task_end_time);
|
|
|
|
create table if not exists t_user_favorite
|
|
(
|
|
id serial not null
|
|
constraint t_user_favorite_pkey
|
|
primary key,
|
|
user_id integer not null
|
|
constraint t_user_favorite_user_id_fkey
|
|
references t_user,
|
|
file_type integer not null
|
|
constraint t_user_favorite_file_type_fkey
|
|
references t_type_file_type,
|
|
path integer not null
|
|
);
|
|
|
|
create table if not exists t_wx_bind_result
|
|
(
|
|
id serial not null
|
|
constraint t_wx_bind_result_pkey
|
|
primary key,
|
|
user_id integer not null
|
|
constraint t_wx_bind_result_user_id_fkey
|
|
references t_user,
|
|
open_id varchar(100) not null,
|
|
union_id varchar(100) not null,
|
|
result boolean not null,
|
|
error varchar(255),
|
|
time timestamp(6) with time zone not null
|
|
);
|
|
|
|
create table if not exists t_user_role
|
|
(
|
|
id serial not null
|
|
constraint t_user_role_pk
|
|
primary key,
|
|
user_id integer not null
|
|
constraint t_user_role_t_user_id_fk
|
|
references t_user,
|
|
role_id integer not null
|
|
constraint t_user_role_t_role_id_fk
|
|
references t_role
|
|
);
|
|
|
|
comment on table t_user_role is '用户角色关联表';
|
|
|
|
comment on column t_user_role.user_id is '用户id';
|
|
|
|
comment on column t_user_role.role_id is '角色id';
|
|
|
|
create unique index if not exists t_type_post_code_uindex
|
|
on t_type_post (code);
|
|
|
|
create table if not exists t_role_structures
|
|
(
|
|
id serial not null
|
|
constraint t_role_structures_pk
|
|
primary key,
|
|
role_id integer not null
|
|
constraint t_role_structures_t_role_id_fk
|
|
references t_role,
|
|
struct_id integer not null
|
|
constraint t_role_structures_t_structure_id_fk
|
|
references t_structure
|
|
);
|
|
|
|
comment on table t_role_structures is '角色关注结构物';
|
|
|
|
comment on column t_role_structures.role_id is '角色id';
|
|
|
|
comment on column t_role_structures.struct_id is '结构物id';
|
|
|
|
create table if not exists t_constant
|
|
(
|
|
id serial not null
|
|
constraint t_constant_pk
|
|
primary key,
|
|
category varchar(30) not null,
|
|
name varchar(60) not null,
|
|
"desc" varchar(60),
|
|
org integer
|
|
);
|
|
|
|
comment on table t_constant is '固化数据表';
|
|
|
|
comment on column t_constant.category is '类型标识';
|
|
|
|
comment on column t_constant.name is '名称';
|
|
|
|
comment on column t_constant.org is '所属组织';
|
|
|
|
create table if not exists t_bridge_part
|
|
(
|
|
id serial not null
|
|
constraint t_bridge_part_pk
|
|
primary key,
|
|
name varchar(30) not null
|
|
);
|
|
|
|
comment on table t_bridge_part is '桥梁部位固化表';
|
|
|
|
comment on column t_bridge_part.name is '名称';
|
|
|
|
create table if not exists t_bridge_component
|
|
(
|
|
id serial not null
|
|
constraint t_bridge_component_pk
|
|
primary key,
|
|
name varchar(60) not null,
|
|
type_id integer not null
|
|
constraint t_bridge_component_t_structure_type_id_fk
|
|
references t_structure_type,
|
|
part_id integer not null
|
|
constraint t_bridge_component_t_bridge_part_id_fk
|
|
references t_bridge_part
|
|
);
|
|
|
|
comment on table t_bridge_component is '桥梁部件固化表';
|
|
|
|
comment on column t_bridge_component.name is '名称';
|
|
|
|
comment on column t_bridge_component.type_id is '结构物类型id';
|
|
|
|
comment on column t_bridge_component.part_id is '部位id';
|
|
|
|
create table if not exists t_bridge_member
|
|
(
|
|
id serial not null
|
|
constraint t_bridge_member_pk
|
|
primary key,
|
|
name varchar(30) not null,
|
|
structure_id integer not null
|
|
constraint t_bridge_member_t_structure_id_fk
|
|
references t_structure,
|
|
part_id integer not null
|
|
constraint t_bridge_member_t_bridge_part_id_fk
|
|
references t_bridge_part,
|
|
component_id integer not null
|
|
constraint t_bridge_member_t_bridge_component_id_fk
|
|
references t_bridge_component,
|
|
isdeleted boolean not null
|
|
);
|
|
|
|
comment on table t_bridge_member is '桥梁构建成员表';
|
|
|
|
comment on column t_bridge_member.name is '名称';
|
|
|
|
comment on column t_bridge_member.structure_id is '结构物id';
|
|
|
|
comment on column t_bridge_member.part_id is '所属部位id';
|
|
|
|
comment on column t_bridge_member.component_id is '所属部件id';
|
|
|
|
comment on column t_bridge_member.isdeleted is '是否删除';
|
|
|
|
|