zhangchuanxi / project-document

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2018/3/14 17:19:54                           */
/*==============================================================*/

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;

drop table if exists tx800_farm;

drop table if exists tx800_platform_sensor;

drop table if exists tx800_virtual_device;

/*==============================================================*/
/* 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(500) comment 'MQTT鉴权和访问控制时使用，SHA256加密',
   device_long          varchar(20),
   device_lat           varchar(20),
   device_type          int comment '1 网络设备；2单点设备；3本地设备',
   farm_id              bigint,
   device_protocol_type int comment '1 联网协议；2本地协议',
   device_icon          varchar(300),
   create_date_time     bigint,
   modified_date_time   bigint,
    issued_state         int comment '1 新增未下发；2 修改未下发；3 已下发',
   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         int comment '0：离线，1：在线',
   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           int comment '1 手动；2策略自动触发',
   event_source         varchar(300) comment 'operate_type=1时记录人名；operate_type=2时记录策略名称',
   event_result         int 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    int comment '半自动，自动类型填',
   is_sms               bool comment '1 短信通知，0不短信通知',
   current_state        int comment '10待执行，20执行中，30暂停中，31暂停， 40失败， 50 结束中，51结束，60执行完成，70  不可执行',
   countdown_minutes    int,
   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        int 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) comment '范围是1~255',
   serial_port_no       varchar(100) comment '串口使用（COM0-7,/dev/ttyS0-S3,其他输入值）',
   verify_way           int comment '串口使用（1奇校验,2偶校验,3无校验）',
   stop_position        int comment '串口使用 1 位，2位',
   baud_rate            float comment '串口使用（9600,12800,57600）',
   connect_ip           varchar(20) comment '网口使用',
   connect_port         int comment '网口使用（默认502）',
   connect_time_out     int comment '网口使用（最大不能超过30秒）',
   acquisition_cycle    int comment '单位是秒，最大不能超过60分钟',
   connect_protocol_type int 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             int comment '1 通知，2告警',
   msg_source           int 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_serial        varchar(64) not null,
   location_x           varchar(10),
   location_y           varchar(10),
   primary key (page_id, device_serial)
);


/*==============================================================*/
/* Table: monitoring_page_sensor                                */
/*==============================================================*/
create table monitoring_page_sensor
(
   sensor_id            bigint not null,
   page_id              bigint not null,
   location_x           varchar(10) comment '页面Y坐标',
   location_y           varchar(10) comment '页面X坐标',
   is_banner            bool not null comment '是否banner显示，0 否，1是',
   primary key (page_id, sensor_id, is_banner)
);


/*==============================================================*/
/* Table: networking_protocol_param                             */
/*==============================================================*/
create table networking_protocol_param
(
   npp_id               bigint not null auto_increment,
   device_id            bigint,
   connect_url          varchar(300) comment 'http 使用',
   communication_time_out int comment 'http 使用',
   connect_time_out     int comment 'http 使用',
   api_token            varchar(100) comment 'http 使用',
   connect_protocol_type int comment '1 MQTT；2RPC；3HTTP；4RabbitMQ',
   client_id            varchar(100) comment 'mqtt 使用',
   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          int comment '1 只读数值型；2读写数值型；3只读开关型；4读写开关；5定位型',
   sensor_value_type    int comment '1 bit；2 byte；3 short；4 integer；5 long；6 float；7double',
   read_register_location int,
   write_register_location int,
   value_calc_way       int 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 int comment '0-4',
   value_unit           varchar(10),
   value_change_zone    float comment '当传感器类型为数值型时有效',
   high_limit           float default -32767 comment '当传感器类型为数值型时有效，用于UI展示使用',
   low_limit            float default -32767 comment '当传感器类型为数值型时有效，用于UI展示使用',
   create_date_time     bigint,
   modified_date_time   bigint,
    farm_id              bigint,
   primary key (sensor_id)
);

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

/*==============================================================*/
/* Index: farm_sensor_Index                                     */
/*==============================================================*/
create index farm_sensor_Index on sensor
(
   farm_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_content       varchar(300) comment '如：创建成功',
   record_type          int comment '1触发器执行记录，2分组执行记录',
   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,
   trigger_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,
   trigger_id            bigint,
   device_id            bigint,
   device_no            char(16) comment '设备序列号16位',
   sensor_id            bigint,
   sensor_no            char(16),
   value1               float,
   value2               float,
   level_no             int,
   condition_relation   int comment '0表示无关系，1表示与前一个条件之间是且关系，2表示与前一个条件之间是或关系。',
   condition_type       int 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
(
   trigger_id
);

/*==============================================================*/
/* Table: trigger_condition_start                               */
/*==============================================================*/
create table trigger_condition_start
(
   condition_start_id   bigint not null auto_increment,
   trigger_id            bigint,
   device_id            bigint,
   device_no            char(16),
   sensor_id            bigint,
   sensor_no            char(16),
   value1               float,
   value2               float,
   level_no             int,
   condition_relation   int comment '0表示无关系，1表示与前一个条件之间是且关系，2表示与前一个条件之间是或关系。',
   condition_type       int 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
(
   trigger_id
);

/*==============================================================*/
/* Table: triggers                                              */
/*==============================================================*/
create table triggers
(
    trigger_id           bigint not null auto_increment,
   trigger_name          varchar(200),
   is_deleted           bool default 0 comment '1表示删除， 0表示未删除。',
   is_enable            bool default 1 comment '1表示使能开启，0表示使能关闭',
   current_state        int comment '10待执行，20执行中，30待恢复，40已触发，50  不可执行（使能关闭）',
   trigger_type          int comment '1 时间触发，2条件触发',
   trigger_describe     varchar(500),
   trigger_date_time    bigint comment 'trigger_type=1时的时间触发条件，其他为空',
   continued_minutes    int comment 'trigger_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 (trigger_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
(
   trigger_id            bigint not null,
   sms_user_id          bigint not null,
   primary key (trigger_id, sms_user_id)
);

/*==============================================================*/
/* Table: tx800_farm                                            */
/*==============================================================*/
create table tx800_farm
(
   tx_farm_no           varchar(6) not null,
   tx_farm_name         varchar(255),
   primary key (tx_farm_no)
);

alter table tx800_farm comment 'Tx800农场信息表';

/*==============================================================*/
/* Table: tx800_platform_sensor                                 */
/*==============================================================*/
create table tx800_platform_sensor
(
   txpts_id             bigint not null auto_increment,
   vd_id                bigint,
   tx_farm_no           varchar(6),
   tx_device_id         varchar(6),
   tx_device_no         varchar(32),
   tx_device_name       varchar(255),
   tx_sensor_id         varchar(6),
   tx_sensor_name       varchar(64),
   tx_sensor_type       int,
   pt_device_id         bigint,
   pt_sensor_id         bigint,
   pt_farm_id           bigint,
   analyze_state        int comment '0 不解析，1解析',
   primary key (txpts_id)
);

alter table tx800_platform_sensor comment 'tx800与平台传感器映射表';

/*==============================================================*/
/* Table: tx800_virtual_device                                  */
/*==============================================================*/
create table tx800_virtual_device
(
   vd_id                bigint not null auto_increment,
   tx_farm_no           varchar(6),
   vd_name              varchar(200),
   control_lock         int default 0 comment '0 未锁，1锁定',
   control_access       int default 0 comment '0  tx800，1平台',
   invalid              int default 0 comment '0生效，1作废',
   sync_redis           int default 0 comment '0 未同步，1已同步',
   pt_farm_id           bigint,
   primary key (vd_id)
);

alter table tx800_virtual_device comment 'tx800虚拟设备';

alter table tx800_platform_sensor add constraint FK_tx800_farm_txpf_sensor foreign key (tx_farm_no)
      references tx800_farm (tx_farm_no) on delete restrict on update restrict;

alter table tx800_platform_sensor add constraint FK_tx800_virtual_device_txpf_sensor foreign key (vd_id)
      references tx800_virtual_device (vd_id) on delete restrict on update restrict;

alter table tx800_virtual_device add constraint FK_tx800_farm_virtual_device foreign key (tx_farm_no)
      references tx800_farm (tx_farm_no) on delete restrict on update restrict;

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_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 (trigger_id)
      references triggers (trigger_id) on delete restrict on update restrict;

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

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

alter table triggers_sms_user add constraint FK_triggers_sms_user foreign key (trigger_id)
      references triggers (trigger_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;