From ff93094cd08e8486242e60e9f076ebfd66b83e9c Mon Sep 17 00:00:00 2001 From: xuxiuxi <xuxiuxi@454eff88-639b-444f-9e54-f578c98de674> Date: 星期五, 31 三月 2017 17:23:59 +0800 Subject: [PATCH] --- VisitFace/script/face_discern.mysql.sql | 288 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 288 insertions(+), 0 deletions(-) diff --git a/VisitFace/script/face_discern.mysql.sql b/VisitFace/script/face_discern.mysql.sql new file mode 100644 index 0000000..3c73c48 --- /dev/null +++ b/VisitFace/script/face_discern.mysql.sql @@ -0,0 +1,288 @@ +/* +Navicat MySQL Data Transfer + +Source Server : 192.168.1.51 +Source Server Version : 50714 +Source Host : 192.168.1.51:3306 +Source Database : face_discern + +Target Server Type : MYSQL +Target Server Version : 50714 +File Encoding : 65001 + +Date: 2017-03-16 10:54:29 +*/ + +SET FOREIGN_KEY_CHECKS=0; + +-- ---------------------------- +-- Table structure for calling +-- ---------------------------- +DROP TABLE IF EXISTS `calling`; +CREATE TABLE `calling` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '绛惧埌浜嬩欢涓婚敭', + `call_dt` datetime DEFAULT NULL COMMENT '鍛煎彨鏃堕棿', + `caller_person_id` int(10) DEFAULT NULL COMMENT '鍛煎彨浜哄憳涓婚敭', + `callee_person_id` int(10) DEFAULT NULL COMMENT '琚懠鍙汉鍛樹富閿�', + `place_id` int(10) DEFAULT NULL COMMENT '鍛煎彨鍙戠敓鎵�鍦ㄨ澶�(鍦扮偣)', + PRIMARY KEY (`id`), + KEY `caller_person_id` (`caller_person_id`), + KEY `callee_person_id` (`callee_person_id`), + KEY `calling_ibfk_3` (`place_id`), + CONSTRAINT `calling_ibfk_1` FOREIGN KEY (`caller_person_id`) REFERENCES `person` (`id`), + CONSTRAINT `calling_ibfk_2` FOREIGN KEY (`callee_person_id`) REFERENCES `person` (`id`), + CONSTRAINT `calling_ibfk_3` FOREIGN KEY (`place_id`) REFERENCES `place` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for checking +-- ---------------------------- +DROP TABLE IF EXISTS `checking`; +CREATE TABLE `checking` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '绛惧埌浜嬩欢涓婚敭', + `status_id` tinyint(2) DEFAULT NULL COMMENT '绛惧埌鐘舵��', + `status_dt` datetime DEFAULT NULL COMMENT '绛惧埌鏃堕棿', + `person_id` int(10) DEFAULT NULL COMMENT '浜哄憳涓婚敭', + `place_id` int(10) DEFAULT NULL COMMENT '绛惧埌浣嶇疆', + PRIMARY KEY (`id`), + KEY `person_id` (`person_id`), + KEY `place_id` (`place_id`), + CONSTRAINT `checking_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`), + CONSTRAINT `checking_ibfk_2` FOREIGN KEY (`place_id`) REFERENCES `place` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for checking_company +-- ---------------------------- +DROP TABLE IF EXISTS `checking_company`; +CREATE TABLE `checking_company` ( + `person_id` int(10) NOT NULL COMMENT '浜哄憳涓婚敭', + `company_id` int(10) NOT NULL COMMENT '鍏徃涓婚敭', + PRIMARY KEY (`person_id`,`company_id`), + KEY `company_id` (`company_id`), + CONSTRAINT `checking_company_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`), + CONSTRAINT `checking_company_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for company +-- ---------------------------- +DROP TABLE IF EXISTS `company`; +CREATE TABLE `company` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '鍏徃涓婚敭', + `name` varchar(50) DEFAULT NULL COMMENT '鍏徃鍚嶇О', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for department +-- ---------------------------- +DROP TABLE IF EXISTS `department`; +CREATE TABLE `department` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '閮ㄩ棬涓婚敭', + `label` varchar(40) DEFAULT NULL COMMENT '閮ㄩ棬鍚嶇О', + `label_spell` char(20) DEFAULT NULL COMMENT '閮ㄩ棬棣栧瓧姣�', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for face_image +-- ---------------------------- +DROP TABLE IF EXISTS `face_image`; +CREATE TABLE `face_image` ( + `face_id` int(10) NOT NULL COMMENT '鍛樺伐鍥剧墖涓婚敭', + `register_company_id` int(10) NOT NULL COMMENT '娉ㄥ唽鍏徃涓婚敭', + `person_id` int(10) DEFAULT NULL COMMENT '浜哄憳涓婚敭', + `image_path` varchar(255) DEFAULT NULL COMMENT '鍥剧墖鍦板潃', + `image_type_id` int(10) DEFAULT NULL COMMENT '鍥剧墖绫诲瀷', + PRIMARY KEY (`face_id`,`register_company_id`), + KEY `register_company_id` (`register_company_id`), + KEY `person_id` (`person_id`), + CONSTRAINT `face_image_ibfk_1` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`), + CONSTRAINT `face_image_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for image +-- ---------------------------- +DROP TABLE IF EXISTS `image`; +CREATE TABLE `image` ( + `person_id` int(10) DEFAULT NULL COMMENT '浜哄憳涓婚敭', + `image_type` int(10) DEFAULT NULL COMMENT '鍥剧墖绫诲瀷', + `image_path` varchar(255) DEFAULT NULL COMMENT '鍥剧墖鍦板潃', + KEY `person_id` (`person_id`), + CONSTRAINT `image_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for person +-- ---------------------------- +DROP TABLE IF EXISTS `person`; +CREATE TABLE `person` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '浜哄憳涓婚敭', + `name` varchar(40) DEFAULT NULL COMMENT '浜哄憳鍚嶅瓧', + `name_spell` char(2) DEFAULT NULL COMMENT '浜哄憳鍚嶉瀛楁瘝', + `gender` tinyint(1) DEFAULT NULL COMMENT '鎬у埆', + `country` char(3) DEFAULT NULL COMMENT '鍥界睄', + `province` char(20) DEFAULT NULL COMMENT '鐪�', + `city` char(20) DEFAULT NULL COMMENT '甯�', + `birthday` date DEFAULT NULL COMMENT '鍑虹敓鏃ユ湡', + `phone` char(12) DEFAULT NULL COMMENT '搴ф満鐢佃瘽', + `mobile_phone` char(15) DEFAULT NULL COMMENT '绉诲姩鐢佃瘽', + `identity_type` int(10) DEFAULT NULL COMMENT '璇佷欢绫诲瀷', + `identify` varchar(100) DEFAULT NULL COMMENT '璇佷欢缂栧彿', + `company_id` int(10) DEFAULT NULL COMMENT '鍏徃涓婚敭', + `department_id` int(10) DEFAULT NULL COMMENT '閮ㄩ棬涓婚敭', + `post_id` int(10) DEFAULT NULL COMMENT '鑱屽姟涓婚敭', + `remark` varchar(255) DEFAULT NULL COMMENT '浜哄憳澶囨敞娑堟伅', + `warning` tinyint(1) DEFAULT NULL COMMENT '鍗遍櫓浜哄憳璀﹀憡娑堟伅', + PRIMARY KEY (`id`), + KEY `company_id` (`company_id`), + KEY `post_id` (`post_id`), + KEY `department_id` (`department_id`), + CONSTRAINT `person_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`), + CONSTRAINT `person_ibfk_3` FOREIGN KEY (`post_id`) REFERENCES `post` (`id`), + CONSTRAINT `person_ibfk_4` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for person_ext +-- ---------------------------- +DROP TABLE IF EXISTS `person_ext`; +CREATE TABLE `person_ext` ( + `person_id` int(10) DEFAULT NULL COMMENT '浜哄憳涓婚敭', + KEY `person_id` (`person_id`), + CONSTRAINT `person_ext_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for place +-- ---------------------------- +DROP TABLE IF EXISTS `place`; +CREATE TABLE `place` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '璁惧涓婚敭', + `company_id` int(10) DEFAULT NULL COMMENT '鍏徃涓婚敭', + `label` varchar(40) DEFAULT NULL COMMENT '璁惧鍦扮偣', + `authorization_id` varchar(100) NOT NULL COMMENT '璁惧鎺堟潈鐮�', + PRIMARY KEY (`id`), + KEY `company_id` (`company_id`), + CONSTRAINT `place_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for post +-- ---------------------------- +DROP TABLE IF EXISTS `post`; +CREATE TABLE `post` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '鑱屼綅涓婚敭', + `label` varchar(40) DEFAULT NULL COMMENT '鑱屼綅鍚嶇О', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for visit +-- ---------------------------- +DROP TABLE IF EXISTS `visit`; +CREATE TABLE `visit` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '璁块棶浜嬩欢涓婚敭', + `visitor_person_id` int(10) DEFAULT NULL COMMENT '璁块棶浜哄憳涓婚敭', + `visitee_person_id` int(10) DEFAULT NULL COMMENT '琚闂汉鍛樹富閿�', + `matter_id` int(10) DEFAULT NULL COMMENT '璁块棶浜嬬敱涓婚敭', + `remark` varchar(255) DEFAULT NULL COMMENT '璁块棶澶囨敞', + `last_status` int(10) DEFAULT NULL COMMENT '鏈�鏂扮殑璁块棶鐘舵��', + `start_time` datetime DEFAULT NULL COMMENT '鏉ヨ鏃堕棿', + `last_time` datetime DEFAULT NULL COMMENT '绛剧鏃堕棿', + `visitor_company_id` int(10) DEFAULT NULL, + `visitee_company_id` int(10) DEFAULT NULL, + `visitor_type_id` int(10) DEFAULT NULL COMMENT '璁垮绫诲瀷', + `warning` tinyint(1) DEFAULT NULL COMMENT '褰撳墠璁块棶鐨勮鍛婄姸鎬�', + PRIMARY KEY (`id`), + KEY `visitor_person_id` (`visitor_person_id`), + KEY `visitee_person_id` (`visitee_person_id`), + KEY `matter_id` (`matter_id`), + KEY `visit_ibfk_4` (`visitor_type_id`), + KEY `visitor_company_id` (`visitor_company_id`), + KEY `visitee_company_id` (`visitee_company_id`), + CONSTRAINT `visit_ibfk_1` FOREIGN KEY (`visitor_person_id`) REFERENCES `person` (`id`), + CONSTRAINT `visit_ibfk_2` FOREIGN KEY (`visitee_person_id`) REFERENCES `person` (`id`), + CONSTRAINT `visit_ibfk_3` FOREIGN KEY (`matter_id`) REFERENCES `visit_matter` (`id`), + CONSTRAINT `visit_ibfk_4` FOREIGN KEY (`visitor_type_id`) REFERENCES `visitor_type` (`id`), + CONSTRAINT `visit_ibfk_5` FOREIGN KEY (`visitor_company_id`) REFERENCES `company` (`id`), + CONSTRAINT `visit_ibfk_6` FOREIGN KEY (`visitee_company_id`) REFERENCES `company` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for visiting +-- ---------------------------- +DROP TABLE IF EXISTS `visiting`; +CREATE TABLE `visiting` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '璁块棶浜嬩欢娴佹按id', + `visit_id` int(10) DEFAULT NULL COMMENT '璁块棶浜嬩欢id', + `status_id` tinyint(2) DEFAULT NULL COMMENT '褰撳墠璁块棶鐘舵��', + `status_dt` datetime DEFAULT NULL COMMENT '鏇存柊鐘舵�佹椂闂�', + `place_id` int(10) DEFAULT NULL COMMENT '璁惧浣嶇疆涓婚敭', + PRIMARY KEY (`id`), + KEY `visit_id` (`visit_id`), + KEY `place_id` (`place_id`), + CONSTRAINT `visiting_ibfk_1` FOREIGN KEY (`visit_id`) REFERENCES `visit` (`id`), + CONSTRAINT `visiting_ibfk_2` FOREIGN KEY (`place_id`) REFERENCES `place` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for visitor_type +-- ---------------------------- +DROP TABLE IF EXISTS `visitor_type`; +CREATE TABLE `visitor_type` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '璁垮绫诲瀷涓婚敭', + `label` varchar(32) DEFAULT NULL COMMENT '璁垮绫诲瀷', + `register_company_id` int(10) DEFAULT NULL COMMENT '娉ㄥ唽鍏徃涓婚敭', + `available` tinyint(1) DEFAULT NULL COMMENT '鏄惁鏄彲鐢�', + PRIMARY KEY (`id`), + KEY `register_company_id` (`register_company_id`), + CONSTRAINT `visitor_type_ibfk_1` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for visit_matter +-- ---------------------------- +DROP TABLE IF EXISTS `visit_matter`; +CREATE TABLE `visit_matter` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '璁块棶浜嬬敱涓婚敭', + `company_id` int(10) DEFAULT NULL COMMENT '鍏徃涓婚敭', + `label` varchar(40) NOT NULL COMMENT '璁块棶浜嬬敱', + `available` tinyint(1) DEFAULT NULL COMMENT '鏄惁鏄彲鐢�', + PRIMARY KEY (`id`), + KEY `company_id` (`company_id`), + CONSTRAINT `visit_matter_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for visit_person_type +-- ---------------------------- +DROP TABLE IF EXISTS `visit_person_type`; +CREATE TABLE `visit_person_type` ( + `register_company_id` int(10) NOT NULL, + `person_id` int(10) NOT NULL, + `visitor_type_id` int(10) NOT NULL, + PRIMARY KEY (`register_company_id`,`person_id`,`visitor_type_id`), + KEY `person_id` (`person_id`) USING BTREE, + KEY `visitor_type_id` (`visitor_type_id`) USING BTREE, + CONSTRAINT `visit_person_type_ibfk_1` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`), + CONSTRAINT `visit_person_type_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`), + CONSTRAINT `visit_person_type_ibfk_3` FOREIGN KEY (`visitor_type_id`) REFERENCES `visitor_type` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for visit_stat +-- ---------------------------- +DROP TABLE IF EXISTS `visit_stat`; +CREATE TABLE `visit_stat` ( + `person_id` int(10) NOT NULL COMMENT '浜哄憳涓婚敭', + `register_company_id` int(10) NOT NULL COMMENT '娉ㄥ唽鍏徃涓婚敭', + `total_count` int(10) DEFAULT NULL COMMENT '鏉ヨ鍏徃娆℃暟', + PRIMARY KEY (`person_id`,`register_company_id`), + KEY `person_id` (`person_id`), + KEY `register_company_id` (`register_company_id`), + CONSTRAINT `visit_stat_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`), + CONSTRAINT `visit_stat_ibfk_2` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Gitblit v1.8.0