-- ============================================= -- WCS �ֿ����ϵͳ - MySQL ��ʼ���ű� -- �������ݿ�: MySQL 5.7+ / 8.0+ -- �ַ���: utf8mb4 -- ˵��: �Ǽܽ׶���С�ֶμ���������������չ -- ============================================= SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- 1. �豸��Ϣ�� -- ---------------------------- DROP TABLE IF EXISTS `wcs_device`; CREATE TABLE `wcs_device` ( `device_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '�豸ID', `device_code` varchar(64) NOT NULL COMMENT '�豸����', `device_name` varchar(128) DEFAULT NULL COMMENT '�豸����', `device_type` varchar(32) DEFAULT NULL COMMENT '�豸���ͣ�conveyor/agv/elevator/stacker�ȣ�', `area_code` varchar(64) DEFAULT NULL COMMENT '�����������', `protocol_type` varchar(32) DEFAULT NULL COMMENT 'ͨ��Э�飨modbus_tcp/opc_ua/s7�ȣ�', `ip_address` varchar(64) DEFAULT NULL COMMENT '�豸IP��ַ', `port` int(11) DEFAULT NULL COMMENT 'ͨ�Ŷ˿�', `status` char(1) DEFAULT '0' COMMENT '�豸״̬��0���� 1ͣ�ã�', `del_flag` char(1) DEFAULT '0' COMMENT 'ɾ����־��0���� 2ɾ����', `create_by` varchar(64) DEFAULT '' COMMENT '������', `create_time` datetime DEFAULT NULL COMMENT '����ʱ��', `update_by` varchar(64) DEFAULT '' COMMENT '������', `update_time` datetime DEFAULT NULL COMMENT '����ʱ��', `remark` varchar(500) DEFAULT NULL COMMENT '��ע', PRIMARY KEY (`device_id`), UNIQUE KEY `uk_device_code` (`device_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='WCS �豸��Ϣ��'; -- ---------------------------- -- 2. �豸��λ�� -- ---------------------------- DROP TABLE IF EXISTS `wcs_device_point`; CREATE TABLE `wcs_device_point` ( `point_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '��λID', `device_id` bigint(20) NOT NULL COMMENT '�����豸ID', `point_code` varchar(64) NOT NULL COMMENT '��λ����', `point_name` varchar(128) DEFAULT NULL COMMENT '��λ����', `point_type` varchar(32) DEFAULT NULL COMMENT '��λ���ͣ�read/write/read_write��', `data_type` varchar(32) DEFAULT NULL COMMENT '�������ͣ�int/float/bool/string��', `address` varchar(128) DEFAULT NULL COMMENT '�Ĵ�����ַ/OPC�ڵ�', `unit` varchar(32) DEFAULT NULL COMMENT '��λ', `status` char(1) DEFAULT '0' COMMENT '״̬��0���� 1ͣ�ã�', `del_flag` char(1) DEFAULT '0' COMMENT 'ɾ����־��0���� 2ɾ����', `create_by` varchar(64) DEFAULT '' COMMENT '������', `create_time` datetime DEFAULT NULL COMMENT '����ʱ��', `update_by` varchar(64) DEFAULT '' COMMENT '������', `update_time` datetime DEFAULT NULL COMMENT '����ʱ��', `remark` varchar(500) DEFAULT NULL COMMENT '��ע', PRIMARY KEY (`point_id`), KEY `idx_device_id` (`device_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='WCS �豸��λ��'; -- ---------------------------- -- 3. �豸��λֵ��¼�� -- ---------------------------- DROP TABLE IF EXISTS `wcs_device_point_value`; CREATE TABLE `wcs_device_point_value` ( `value_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '��¼ID', `point_id` bigint(20) NOT NULL COMMENT '��λID', `device_id` bigint(20) NOT NULL COMMENT '�豸ID', `point_value` varchar(256) DEFAULT NULL COMMENT '��λֵ', `quality` varchar(16) DEFAULT NULL COMMENT '����������good/bad/uncertain��', `collect_time` datetime DEFAULT NULL COMMENT '�ɼ�ʱ��', `create_time` datetime DEFAULT NULL COMMENT '��¼ʱ��', PRIMARY KEY (`value_id`), KEY `idx_point_id` (`point_id`), KEY `idx_collect_time` (`collect_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='WCS �豸��λֵ��¼��'; -- ---------------------------- -- 4. �ӿ����ñ� -- ---------------------------- DROP TABLE IF EXISTS `wcs_api_config`; CREATE TABLE `wcs_api_config` ( `api_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '�ӿ�ID', `api_code` varchar(64) NOT NULL COMMENT '�ӿڱ���', `api_name` varchar(128) DEFAULT NULL COMMENT '�ӿ�����', `api_url` varchar(512) DEFAULT NULL COMMENT '�ӿڵ�ַ', `request_method` varchar(16) DEFAULT 'POST' COMMENT '����ʽ��GET/POST/PUT/DELETE��', `content_type` varchar(64) DEFAULT 'application/json' COMMENT '��������', `timeout` int(11) DEFAULT 30000 COMMENT '��ʱʱ�䣨���룩', `retry_count` int(11) DEFAULT 0 COMMENT '���Դ���', `direction` varchar(16) DEFAULT NULL COMMENT '����inbound/outbound��', `status` char(1) DEFAULT '0' COMMENT '״̬��0���� 1ͣ�ã�', `del_flag` char(1) DEFAULT '0' COMMENT 'ɾ����־��0���� 2ɾ����', `create_by` varchar(64) DEFAULT '' COMMENT '������', `create_time` datetime DEFAULT NULL COMMENT '����ʱ��', `update_by` varchar(64) DEFAULT '' COMMENT '������', `update_time` datetime DEFAULT NULL COMMENT '����ʱ��', `remark` varchar(500) DEFAULT NULL COMMENT '��ע', PRIMARY KEY (`api_id`), UNIQUE KEY `uk_api_code` (`api_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='WCS �ӿ����ñ�'; -- ---------------------------- -- 5. ���̶���� -- ---------------------------- DROP TABLE IF EXISTS `wcs_flow_def`; CREATE TABLE `wcs_flow_def` ( `flow_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '����ID', `flow_code` varchar(64) NOT NULL COMMENT '���̱���', `flow_name` varchar(128) DEFAULT NULL COMMENT '��������', `flow_type` varchar(32) DEFAULT NULL COMMENT '��������', `version` int(11) DEFAULT 1 COMMENT '�汾��', `flow_json` text COMMENT '����JSON���壨�������ݣ�', `status` char(1) DEFAULT '0' COMMENT '״̬��0�ݸ� 1�ѷ��� 2��ͣ�ã�', `del_flag` char(1) DEFAULT '0' COMMENT 'ɾ����־��0���� 2ɾ����', `create_by` varchar(64) DEFAULT '' COMMENT '������', `create_time` datetime DEFAULT NULL COMMENT '����ʱ��', `update_by` varchar(64) DEFAULT '' COMMENT '������', `update_time` datetime DEFAULT NULL COMMENT '����ʱ��', `remark` varchar(500) DEFAULT NULL COMMENT '��ע', PRIMARY KEY (`flow_id`), UNIQUE KEY `uk_flow_code_version` (`flow_code`, `version`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='WCS ���̶����'; -- ---------------------------- -- 6. ���̽ڵ�� -- ---------------------------- DROP TABLE IF EXISTS `wcs_flow_node`; CREATE TABLE `wcs_flow_node` ( `node_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '�ڵ�ID', `flow_id` bigint(20) NOT NULL COMMENT '��������ID', `node_code` varchar(64) NOT NULL COMMENT '�ڵ����', `node_name` varchar(128) DEFAULT NULL COMMENT '�ڵ�����', `node_type` varchar(32) DEFAULT NULL COMMENT '�ڵ����ͣ�start/end/task/condition/parallel�ȣ�', `handler_class` varchar(256) DEFAULT NULL COMMENT '����������', `config_json` text COMMENT '�ڵ�����JSON', `pos_x` int(11) DEFAULT 0 COMMENT '����X����', `pos_y` int(11) DEFAULT 0 COMMENT '����Y����', `sort_order` int(11) DEFAULT 0 COMMENT '����', `create_time` datetime DEFAULT NULL COMMENT '����ʱ��', `update_time` datetime DEFAULT NULL COMMENT '����ʱ��', PRIMARY KEY (`node_id`), KEY `idx_flow_id` (`flow_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='WCS ���̽ڵ��'; -- ---------------------------- -- 7. �������߱� -- ---------------------------- DROP TABLE IF EXISTS `wcs_flow_line`; CREATE TABLE `wcs_flow_line` ( `line_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '����ID', `flow_id` bigint(20) NOT NULL COMMENT '��������ID', `source_node_id` bigint(20) NOT NULL COMMENT 'Դ�ڵ�ID', `target_node_id` bigint(20) NOT NULL COMMENT 'Ŀ��ڵ�ID', `condition_expr` varchar(512) DEFAULT NULL COMMENT '��������ʽ', `line_label` varchar(128) DEFAULT NULL COMMENT '���߱�ǩ', `sort_order` int(11) DEFAULT 0 COMMENT '����', `create_time` datetime DEFAULT NULL COMMENT '����ʱ��', PRIMARY KEY (`line_id`), KEY `idx_flow_id` (`flow_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='WCS �������߱�'; -- ---------------------------- -- 8. ����ʵ���� -- ---------------------------- DROP TABLE IF EXISTS `wcs_flow_instance`; CREATE TABLE `wcs_flow_instance` ( `instance_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ʵ��ID', `flow_id` bigint(20) NOT NULL COMMENT '���̶���ID', `instance_code` varchar(64) DEFAULT NULL COMMENT 'ʵ������', `trigger_source` varchar(64) DEFAULT NULL COMMENT '������Դ', `current_node_id` bigint(20) DEFAULT NULL COMMENT '��ǰ�ڵ�ID', `instance_status` varchar(16) DEFAULT 'running' COMMENT 'ʵ��״̬��running/completed/failed/cancelled��', `context_json` text COMMENT '����ʱ������JSON', `start_time` datetime DEFAULT NULL COMMENT '��ʼʱ��', `end_time` datetime DEFAULT NULL COMMENT '����ʱ��', `error_msg` varchar(2000) DEFAULT NULL COMMENT '������Ϣ', `create_by` varchar(64) DEFAULT '' COMMENT '������', `create_time` datetime DEFAULT NULL COMMENT '����ʱ��', PRIMARY KEY (`instance_id`), KEY `idx_flow_id` (`flow_id`), KEY `idx_instance_status` (`instance_status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='WCS ����ʵ����'; -- ---------------------------- -- 9. ���̽ڵ�ִ����־�� -- ---------------------------- DROP TABLE IF EXISTS `wcs_flow_node_log`; CREATE TABLE `wcs_flow_node_log` ( `log_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '��־ID', `instance_id` bigint(20) NOT NULL COMMENT '����ʵ��ID', `node_id` bigint(20) NOT NULL COMMENT '�ڵ�ID', `node_name` varchar(128) DEFAULT NULL COMMENT '�ڵ�����', `execute_status` varchar(16) DEFAULT NULL COMMENT 'ִ��״̬��success/fail/skip��', `input_data` text COMMENT '��������JSON', `output_data` text COMMENT '�������JSON', `error_msg` varchar(2000) DEFAULT NULL COMMENT '������Ϣ', `start_time` datetime DEFAULT NULL COMMENT '��ʼʱ��', `end_time` datetime DEFAULT NULL COMMENT '����ʱ��', `duration` bigint(20) DEFAULT NULL COMMENT 'ִ�к�ʱ�����룩', `create_time` datetime DEFAULT NULL COMMENT '����ʱ��', PRIMARY KEY (`log_id`), KEY `idx_instance_id` (`instance_id`), KEY `idx_node_id` (`node_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='WCS ���̽ڵ�ִ����־��'; -- ---------------------------- -- WCS �˵����ݳ�ʼ�������������˵����� -- �˵�ID �� 2000 �𣬱�����ϵͳ�˵���ͻ -- ---------------------------- -- WCS һ���˵� INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2000, 'WCS�ֿ����', 0, 5, 'wcs', NULL, '', '', 1, 0, 'M', '0', '0', '', 'server', 'admin', sysdate(), '', NULL, 'WCS�ֿ����ϵͳ'); -- �豸���� INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2001, '�豸����', 2000, 1, 'device', 'wcs/device/index', '', '', 1, 0, 'C', '0', '0', 'wcs:device:list', 'component', 'admin', sysdate(), '', NULL, 'WCS�豸����'); -- ��λ���� INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2002, '��λ����', 2000, 2, 'point', 'wcs/point/index', '', '', 1, 0, 'C', '0', '0', 'wcs:point:list', 'edit', 'admin', sysdate(), '', NULL, 'WCS��λ����'); -- �ӿ����� INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2003, '�ӿ�����', 2000, 3, 'apiconfig', 'wcs/apiconfig/index', '', '', 1, 0, 'C', '0', '0', 'wcs:api:list', 'link', 'admin', sysdate(), '', NULL, 'WCS�ӿ�����'); -- ���̶��� INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2004, '���̶���', 2000, 4, 'flowdef', 'wcs/flowdef/index', '', '', 1, 0, 'C', '0', '0', 'wcs:flow:list', 'cascader', 'admin', sysdate(), '', NULL, 'WCS���̶���'); -- ����ʵ�� INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2005, '����ʵ��', 2000, 5, 'flowinstance', 'wcs/flowinstance/index', '', '', 1, 0, 'C', '0', '0', 'wcs:flowinstance:list', 'list', 'admin', sysdate(), '', NULL, 'WCS����ʵ��'); -- ������־ INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2006, '������־', 2000, 6, 'flowlog', 'wcs/flowlog/index', '', '', 1, 0, 'C', '0', '0', 'wcs:flowlog:list', 'log', 'admin', sysdate(), '', NULL, 'WCS������־'); -- ---------------------------- -- ��ťȨ�ޣ�menu_type='F'�� -- menuId �� 2010 ��ÿ�������˵� 5 ����ť����ѯ/����/�޸�/ɾ��/������ -- ---------------------------- -- �豸������ť insert into sys_menu values('2010', '�豸��ѯ', '2001', '1', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:device:query', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2011', '�豸����', '2001', '2', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:device:add', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2012', '�豸�޸�', '2001', '3', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:device:edit', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2013', '�豸ɾ��', '2001', '4', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:device:remove', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2014', '�豸����', '2001', '5', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:device:export', '#', 'admin', sysdate(), '', null, ''); -- ��λ���ð�ť insert into sys_menu values('2015', '��λ��ѯ', '2002', '1', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:point:query', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2016', '��λ����', '2002', '2', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:point:add', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2017', '��λ�޸�', '2002', '3', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:point:edit', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2018', '��λɾ��', '2002', '4', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:point:remove', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2019', '��λ����', '2002', '5', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:point:export', '#', 'admin', sysdate(), '', null, ''); -- �ӿ����ð�ť insert into sys_menu values('2020', '�ӿڲ�ѯ', '2003', '1', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:api:query', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2021', '�ӿ�����', '2003', '2', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:api:add', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2022', '�ӿ��޸�', '2003', '3', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:api:edit', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2023', '�ӿ�ɾ��', '2003', '4', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:api:remove', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2024', '�ӿڵ���', '2003', '5', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:api:export', '#', 'admin', sysdate(), '', null, ''); -- ���̶��尴ť insert into sys_menu values('2025', '���̲�ѯ', '2004', '1', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flow:query', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2026', '��������', '2004', '2', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flow:add', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2027', '�����޸�', '2004', '3', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flow:edit', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2028', '����ɾ��', '2004', '4', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flow:remove', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2029', '���̵���', '2004', '5', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flow:export', '#', 'admin', sysdate(), '', null, ''); -- ����ʵ����ť insert into sys_menu values('2030', 'ʵ����ѯ', '2005', '1', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flowinstance:query', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2031', 'ʵ������', '2005', '2', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flowinstance:add', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2032', 'ʵ���޸�', '2005', '3', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flowinstance:edit', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2033', 'ʵ��ɾ��', '2005', '4', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flowinstance:remove', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2034', 'ʵ������', '2005', '5', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flowinstance:export', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2040', 'ʵ��ִ��', '2005', '6', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flowinstance:execute', '#', 'admin', sysdate(), '', null, ''); -- ������־��ť insert into sys_menu values('2035', '��־��ѯ', '2006', '1', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flowlog:query', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2036', '��־����', '2006', '2', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flowlog:add', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2037', '��־�޸�', '2006', '3', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flowlog:edit', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2038', '��־ɾ��', '2006', '4', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flowlog:remove', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu values('2039', '��־����', '2006', '5', '', '', '', '', 1, 0, 'F', '0', '0', 'wcs:flowlog:export', '#', 'admin', sysdate(), '', null, ''); SET FOREIGN_KEY_CHECKS = 1;