zhangchuanxi / project-document

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2018/3/13 15:32:37                           */
/*==============================================================*/
SET FOREIGN_KEY_CHECKS=0;

drop index farm_device_index on device;

drop table if exists device;

drop index device_dsr_index on device_state_record;

drop table if exists device_state_record;

drop index farm_event_index on event_record;

drop table if exists event_record;

drop index farm_group_control_index on group_control;

drop table if exists group_control;

drop index group_control_gca_index on group_control_action;

drop table if exists group_control_action;

drop index group_control_cycle_week_index on group_control_cycle_week;

drop table if exists group_control_cycle_week;

drop table if exists group_control_sms_user;

drop index device_lpp_index on local_protocol_param;

drop table if exists local_protocol_param;

drop table if exists message;

drop index user_msg_index on message_user_record;

drop index msg_msg_user_index on message_user_record;

drop table if exists message_user_record;

drop index farm_page_index on monitoring_page;

drop table if exists monitoring_page;

drop table if exists monitoring_page_device;

drop table if exists monitoring_page_sensor;

drop index device_npp_index on networking_protocol_param;

drop table if exists networking_protocol_param;

drop index device_sensor_index on sensor;

drop table if exists sensor;

drop index farm_sms_user_index on sms_user;

drop table if exists sms_user;

drop index farm_ser_index on strategy_execute_record;

drop table if exists strategy_execute_record;

drop table if exists trigger_action_sensor;

drop index trigger_condition_end_index on trigger_condition_end;

drop table if exists trigger_condition_end;

drop index trigger_condition_start_index on trigger_condition_start;

drop table if exists trigger_condition_start;

drop index farm_trigger_index on triggers;

drop table if exists triggers;

drop table if exists triggers_sms_user;

/*==============================================================*/
/* Table: device                                                */
/*==============================================================*/
create table device
(
   device_id            bigint not null auto_increment,
   dev_device_id        bigint,
   device_name          varchar(200),
   device_describe      varchar(500),
   device_no            char(16) comment '设备序列号16位',
   device_password      varchar(100) comment 'MQTT鉴权和访问控制时使用，SHA256加密',
   device_long          varchar(20),
   device_lat           varchar(20),
   device_type          tinyint comment '1 网络设备；2单点设备；3本地设备',
   farm_id              bigint,
   device_protocol_type tinyint comment '1 联网协议；2本地协议',
   device_icon          varchar(300),
   create_date_time     bigint,
   modified_date_time   bigint,
   primary key (device_id)
);

/*==============================================================*/
/* Index: farm_device_index                                     */
/*==============================================================*/
create index farm_device_index on device
(
   farm_id
);

/*==============================================================*/
/* Table: device_state_record                                   */
/*==============================================================*/
create table device_state_record
(
   dsr_id               bigint not null,
   device_id            bigint,
   device_state         tinyint comment '1 在线；2离线',
   record_date_time     bigint,
   primary key (dsr_id)
);

/*==============================================================*/
/* Index: device_dsr_index                                      */
/*==============================================================*/
create index device_dsr_index on device_state_record
(
   device_id
);

/*==============================================================*/
/* Table: event_record                                          */
/*==============================================================*/
create table event_record
(
   event_record_id      bigint not null auto_increment,
   event_name           varchar(300),
   event_date_time      bigint,
   event_type           tinyint comment '1 手动；2策略自动触发',
   event_source         varchar(300) comment 'operate_type=1时记录人名；operate_type=2时记录策略名称',
   event_result         tinyint comment '1待定；2成功；3失败',
   farm_id              bigint,
   primary key (event_record_id)
);

/*==============================================================*/
/* Index: farm_event_index                                      */
/*==============================================================*/
create index farm_event_index on event_record
(
   farm_id
);

/*==============================================================*/
/* Table: group_control                                         */
/*==============================================================*/
create table group_control
(
   group_control_id     bigint not null auto_increment,
   group_name           varchar(200),
   group_type           tinyint comment '1 手动（手动控制动作开/关），2 半自动（手动打开动作，到时间后自动关闭动作），3 自动（定时自动打开动作，定时自动关闭动作）',
   start_time           varchar(8) comment '自动类型填',
   continued_minutes    smallint comment '半自动，自动类型填',
   is_sms               bool comment '1 短信通知，0不短信通知',
   current_state        tinyint comment '10待执行，20执行中，30暂停中，31暂停， 40失败， 50 结束中，51结束，60执行完成，70  不可执行',
   countdown_minutes    smallint,
   start_execute_date_time bigint,
   farm_id              bigint,
   is_deleted           bool default 0 comment '1表示删除， 0表示未删除。',
   is_enable            bool default 1 comment '1表示使能开启，0表示使能关闭',
   create_date_time     bigint,
   modified_date_time   bigint,
   primary key (group_control_id)
);

/*==============================================================*/
/* Index: farm_group_control_index                              */
/*==============================================================*/
create index farm_group_control_index on group_control
(
   farm_id
);

/*==============================================================*/
/* Table: group_control_action                                  */
/*==============================================================*/
create table group_control_action
(
   gca_id               bigint not null auto_increment,
   group_control_id     bigint,
   device_id            bigint,
   device_no            char(16),
   sensor_id            bigint,
   sensor_no            char(16),
   sensor_value         varchar(20),
   primary key (gca_id)
);

/*==============================================================*/
/* Index: group_control_gca_index                               */
/*==============================================================*/
create index group_control_gca_index on group_control_action
(
   group_control_id
);

/*==============================================================*/
/* Table: group_control_cycle_week                              */
/*==============================================================*/
create table group_control_cycle_week
(
   gccw_id              bigint not null auto_increment,
   group_control_id     bigint,
   cycle_week           char(3) comment 'SUN，MON，TUE，WED，THU，FRI，SAT',
   current_state        tinyint comment '10待执行，20执行中，30暂停中，31暂停， 40失败， 50 结束中，51结束，60执行完成，70  不可执行',
   is_cycle             bool comment '1 循环；0 一次',
   primary key (gccw_id)
);

/*==============================================================*/
/* Index: group_control_cycle_week_index                        */
/*==============================================================*/
create index group_control_cycle_week_index on group_control_cycle_week
(
   group_control_id
);

/*==============================================================*/
/* Table: group_control_sms_user                                */
/*==============================================================*/
create table group_control_sms_user
(
   group_control_id     bigint not null,
   sms_user_id          bigint not null,
   primary key (group_control_id, sms_user_id)
);

/*==============================================================*/
/* Table: local_protocol_param                                  */
/*==============================================================*/
create table local_protocol_param
(
   lpp_id               bigint not null auto_increment,
   device_id            bigint,
   sub_connect_url      varchar(300),
   port_type            tinyint comment '1 串口；2网口',
   serial_port_no       varchar(100) comment '串口使用',
   verify_way           tinyint comment '串口使用',
   baud_rate            float comment '串口使用',
   connect_ip           varchar(20) comment '网口使用',
   connect_port         smallint comment '网口使用',
   connect_time_out     int comment '网口使用',
   acquisition_cycle    tinyint,
   connect_protocol_type tinyint comment '1Modbus-TCP；2Modbus-RTU；3Wisfarm-lora',
   primary key (lpp_id)
);

/*==============================================================*/
/* Index: device_lpp_index                                      */
/*==============================================================*/
create index device_lpp_index on local_protocol_param
(
   device_id
);

/*==============================================================*/
/* Table: message                                               */
/*==============================================================*/
create table message
(
   msg_id               bigint not null auto_increment,
   msg_title            varchar(200),
   msg_content          varchar(500),
   msg_type             tinyint comment '1 通知，2告警',
   msg_source           tinyint comment '1触发器，2分组控制；3 人工推送，4第三方推送',
   create_date_time     bigint,
   primary key (msg_id)
);

/*==============================================================*/
/* Table: message_user_record                                   */
/*==============================================================*/
create table message_user_record
(
   mur_id               bigint not null auto_increment,
   msg_id               bigint,
   user_id              bigint,
   is_view              bool default 0 comment '1 查看，0未查看',
   view_date_time       bigint,
   primary key (mur_id)
);

/*==============================================================*/
/* Index: msg_msg_user_index                                    */
/*==============================================================*/
create index msg_msg_user_index on message_user_record
(
   msg_id
);

/*==============================================================*/
/* Index: user_msg_index                                        */
/*==============================================================*/
create index user_msg_index on message_user_record
(
   user_id
);

/*==============================================================*/
/* Table: monitoring_page                                       */
/*==============================================================*/
create table monitoring_page
(
   page_id              bigint not null auto_increment,
   page_no              varchar(20),
   page_title           varchar(200),
   page_url             varchar(300),
   page_img_url         varchar(300),
   farm_id              bigint,
   create_date_time     bigint,
   modified_date_time   bigint,
   primary key (page_id)
);

/*==============================================================*/
/* Index: farm_page_index                                       */
/*==============================================================*/
create index farm_page_index on monitoring_page
(
   farm_id
);

/*==============================================================*/
/* Table: monitoring_page_device                                */
/*==============================================================*/
create table monitoring_page_device
(
   page_id              bigint not null,
   device_id            bigint not null,
   primary key (page_id, device_id)
);

/*==============================================================*/
/* Table: monitoring_page_sensor                                */
/*==============================================================*/
create table monitoring_page_sensor
(
   page_id              bigint not null,
   sensor_id            bigint not null,
   primary key (page_id, sensor_id)
);

/*==============================================================*/
/* Table: networking_protocol_param                             */
/*==============================================================*/
create table networking_protocol_param
(
   npp_id               bigint not null auto_increment,
   device_id            bigint,
   connect_url          varchar(300),
   communication_time_out int,
   connect_time_out     int,
   api_token            varchar(100),
   connect_protocol_type tinyint comment '1 MQTT；2RPC；3HTTP；4RabbitMQ',
   primary key (npp_id)
);

/*==============================================================*/
/* Index: device_npp_index                                      */
/*==============================================================*/
create index device_npp_index on networking_protocol_param
(
   device_id
);

/*==============================================================*/
/* Table: sensor                                                */
/*==============================================================*/
create table sensor
(
   sensor_id            bigint not null auto_increment,
   device_id            bigint,
   sensor_name          varchar(200),
   sensor_no            char(16),
   sensor_icon          varchar(300),
   sensor_long          varchar(20),
   sensor_lat           varchar(20),
   sensor_type          tinyint comment '1 只读数值型；2读写数值型；3只读开关型；4读写开关；5定位型',
   sensor_value_type    tinyint comment '1 bit；2 byte；3 short；4 integer；5 long；6 float；7double',
   read_register_location smallint,
   write_register_location smallint,
   value_calc_way       tinyint comment '1系数计算；2混合计算；3原始计算',
   coefficient_a        float comment '计算方式为系数计算和混合计算时有效',
   coefficient_b        float comment '计算方式为系数计算和混合计算时有效',
   high_range           float comment '一次值最高值，当值计算方式为混合计算时，用于计算A、B系数',
   low_range            float comment '一次值最低值，当值计算方式为混合计算时，用于计算A、B系数',
   max_code_value       float comment '二次值最高值，当值计算方式为混合计算时，用于计算A、B系数',
   min_code_value       float comment '二次值最低值，当值计算方式为混合计算时，用于计算A、B系数',
   value_decimal_places tinyint comment '0-4',
   value_unit           varchar(10),
   value_change_zone    float comment '当传感器类型为数值型时有效',
   high_limit           float comment '当传感器类型为数值型时有效，用于UI展示使用',
   low_limit            float comment '当传感器类型为数值型时有效，用于UI展示使用',
   create_date_time     bigint,
   modified_date_time   bigint,
   primary key (sensor_id)
);

/*==============================================================*/
/* Index: device_sensor_index                                   */
/*==============================================================*/
create index device_sensor_index on sensor
(
   device_id
);

/*==============================================================*/
/* Table: sms_user                                              */
/*==============================================================*/
create table sms_user
(
   sms_user_id          bigint not null auto_increment,
   farm_id              bigint,
   sms_user_name        varchar(10),
   sms_user_phone       varchar(20),
   sms_user_describe    varchar(100),
   create_date_time     bigint,
   modified_date_time   bigint,
   primary key (sms_user_id)
);

/*==============================================================*/
/* Index: farm_sms_user_index                                   */
/*==============================================================*/
create index farm_sms_user_index on sms_user
(
   farm_id
);

/*==============================================================*/
/* Table: strategy_execute_record                               */
/*==============================================================*/
create table strategy_execute_record
(
   ser_id               bigint not null auto_increment,
   record_date_time     bigint,
   source_id            bigint comment 'record_type=1时 表示触发器id；record_type=2时 表示分组id',
   record_name          varchar(300),
   record_type          tinyint comment '1触发器执行记录，2分组执行记录',
   record_result        tinyint comment '1待定；2  成功；3失败 ',
   farm_id              bigint,
   primary key (ser_id)
);

/*==============================================================*/
/* Index: farm_ser_index                                        */
/*==============================================================*/
create index farm_ser_index on strategy_execute_record
(
   farm_id
);

/*==============================================================*/
/* Table: trigger_action_sensor                                 */
/*==============================================================*/
create table trigger_action_sensor
(
   action_id            bigint not null auto_increment,
   trgger_id            bigint,
   device_id            bigint,
   device_no            char(16),
   sensor_id            bigint,
   sensor_no            char(16),
   sensor_value         varchar(20),
   primary key (action_id)
);

alter table trigger_action_sensor comment '触发器动作-传感器';

/*==============================================================*/
/* Table: trigger_condition_end                                 */
/*==============================================================*/
create table trigger_condition_end
(
   condition_end_id     bigint not null auto_increment,
   trgger_id            bigint,
   device_id            bigint,
   device_no            char(16) comment '设备序列号16位',
   sensor_id            bigint,
   sensor_no            char(16),
   value1               float,
   value2               float,
   level_no             tinyint,
   condition_relation   tinyint comment '0表示无关系，1表示与前一个条件之间是且关系，2表示与前一个条件之间是或关系。',
   condition_type       tinyint comment '1 数值高于x ；2 数值低于y；3 开关开启；4 开关关闭；5 数值在x和y之间；6 数值低于X或者高于Y；7 数值高于x超过m分钟；8 数值低于x超过M分钟；',
   primary key (condition_end_id)
);

alter table trigger_condition_end comment '结束触发条件';

/*==============================================================*/
/* Index: trigger_condition_end_index                           */
/*==============================================================*/
create index trigger_condition_end_index on trigger_condition_end
(
   trgger_id
);

/*==============================================================*/
/* Table: trigger_condition_start                               */
/*==============================================================*/
create table trigger_condition_start
(
   condition_start_id   bigint not null auto_increment,
   trgger_id            bigint,
   device_id            bigint,
   device_no            char(16),
   sensor_id            bigint,
   sensor_no            char(16),
   value1               float,
   value2               float,
   level_no             tinyint,
   condition_relation   tinyint comment '0表示无关系，1表示与前一个条件之间是且关系，2表示与前一个条件之间是或关系。',
   condition_type       tinyint comment '1 数值高于x ；2 数值低于y；3 开关开启；4 开关关闭；5 数值在x和y之间；6 数值低于X或者高于Y；7 数值高于x超过m分钟；8 数值低于x超过M分钟；',
   primary key (condition_start_id)
);

alter table trigger_condition_start comment '开始触发条件';

/*==============================================================*/
/* Index: trigger_condition_start_index                         */
/*==============================================================*/
create index trigger_condition_start_index on trigger_condition_start
(
   trgger_id
);

/*==============================================================*/
/* Table: triggers                                              */
/*==============================================================*/
create table triggers
(
   trgger_id            bigint not null,
   trgger_name          varchar(200),
   is_deleted           bool default 0 comment '1表示删除， 0表示未删除。',
   is_enable            bool default 1 comment '1表示使能开启，0表示使能关闭',
   current_state        tinyint comment '10待执行，20执行中，30待恢复，40已触发，50  不可执行（使能关闭）',
   trgger_type          tinyint comment '1 时间触发，2条件触发',
   trigger_describe     varchar(500),
   trigger_date_time    bigint comment 'trgger_type=1时的时间触发条件，其他为空',
   continued_minutes    smallint comment 'trgger_type=1时的触发后的持续分钟数，其他为空',
   is_sms               bool comment '1 短信通知；0不短信通知',
   farm_id              bigint,
   is_alarm             bool comment '1 告警；0不告警',
   create_date_time     bigint,
   modified_date_time   bigint,
   primary key (trgger_id)
);

alter table triggers comment '触发器';

/*==============================================================*/
/* Index: farm_trigger_index                                    */
/*==============================================================*/
create index farm_trigger_index on triggers
(
   farm_id
);

/*==============================================================*/
/* Table: triggers_sms_user                                     */
/*==============================================================*/
create table triggers_sms_user
(
   trgger_id            bigint not null,
   sms_user_id          bigint not null,
   primary key (trgger_id, sms_user_id)
);

alter table device add constraint FK_device_device foreign key (dev_device_id)
      references device (device_id) on delete restrict on update restrict;

alter table device_state_record add constraint FK_device_device_state_record foreign key (device_id)
      references device (device_id) on delete restrict on update restrict;

alter table group_control_action add constraint FK_group_control_group_control_action foreign key (group_control_id)
      references group_control (group_control_id) on delete restrict on update restrict;

alter table group_control_cycle_week add constraint FK_group_control_cycle_week foreign key (group_control_id)
      references group_control (group_control_id) on delete restrict on update restrict;

alter table group_control_sms_user add constraint FK_group_control_sms_user foreign key (group_control_id)
      references group_control (group_control_id) on delete restrict on update restrict;

alter table group_control_sms_user add constraint FK_sms_user_group_control foreign key (sms_user_id)
      references sms_user (sms_user_id) on delete restrict on update restrict;

alter table local_protocol_param add constraint FK_device_local_protocol_param foreign key (device_id)
      references device (device_id) on delete restrict on update restrict;

alter table message_user_record add constraint FK_msg_msg_user foreign key (msg_id)
      references message (msg_id) on delete restrict on update restrict;

alter table monitoring_page_device add constraint FK_monitoring_page_device foreign key (page_id)
      references monitoring_page (page_id) on delete restrict on update restrict;

alter table monitoring_page_device add constraint FK_monitoring_page_device2 foreign key (device_id)
      references device (device_id) on delete restrict on update restrict;

alter table monitoring_page_sensor add constraint FK_monitoring_page_sensor foreign key (page_id)
      references monitoring_page (page_id) on delete restrict on update restrict;

alter table monitoring_page_sensor add constraint FK_monitoring_page_sensor2 foreign key (sensor_id)
      references sensor (sensor_id) on delete restrict on update restrict;

alter table networking_protocol_param add constraint FK_device_network_protocol_param foreign key (device_id)
      references device (device_id) on delete restrict on update restrict;

alter table sensor add constraint FK_device_sensor foreign key (device_id)
      references device (device_id) on delete restrict on update restrict;

alter table trigger_action_sensor add constraint FK_triggers_trigger_action_sensor foreign key (trgger_id)
      references triggers (trgger_id) on delete restrict on update restrict;

alter table trigger_condition_end add constraint FK_triggers_trigger_condition_end foreign key (trgger_id)
      references triggers (trgger_id) on delete restrict on update restrict;

alter table trigger_condition_start add constraint FK_triggers_trigger_condition_start foreign key (trgger_id)
      references triggers (trgger_id) on delete restrict on update restrict;

alter table triggers_sms_user add constraint FK_triggers_sms_user foreign key (trgger_id)
      references triggers (trgger_id) on delete restrict on update restrict;

alter table triggers_sms_user add constraint FK_triggers_sms_user2 foreign key (sms_user_id)
      references sms_user (sms_user_id) on delete restrict on update restrict;