/*
 Navicat Premium Data Transfer

 Source Server         : 192.168.0.28
 Source Server Type    : MySQL
 Source Server Version : 50634
 Source Host           : 192.168.0.28:3306
 Source Schema         : visual

 Target Server Type    : MySQL
 Target Server Version : 50634
 File Encoding         : 65001

 Date: 23/05/2018 11:13:21
*/

create database if not exists visual DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE visual;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for datastatistics
-- ----------------------------
DROP TABLE IF EXISTS `datastatistics`;
CREATE TABLE `datastatistics`  (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Unit` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Tag` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Type` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Value` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `HistoryUrl` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Direction` int(11) NULL DEFAULT NULL,
  `Description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `PipingId` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `CreatedTime` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
  `StatisticalTime` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`Id`) USING BTREE,
  INDEX `PipingId`(`PipingId`, `Tag`, `Type`, `CreatedTime`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 2515758 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for log
-- ----------------------------
DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Menu` varchar(50) NOT NULL COMMENT '菜单',
  `Operate` varchar(50) DEFAULT NULL COMMENT '操作，如添加、删除等',
  `Content` varchar(200) DEFAULT NULL COMMENT '日志内容',
  `LogTime` datetime NOT NULL COMMENT '日志时间',
  `Manager` varchar(50) NOT NULL COMMENT '操作人',
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=295 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for pipingcurrentdata
-- ----------------------------
DROP TABLE IF EXISTS `pipingcurrentdata`;
CREATE TABLE `pipingcurrentdata`  (
  `Id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `Unit` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Tag` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Type` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Value` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `HistoryUrl` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Direction` int(11) NULL DEFAULT NULL,
  `Description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `PipingId` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `CreatedTime` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`Id`) USING BTREE,
  UNIQUE INDEX `PipingId`(`PipingId`, `Tag`, `Type`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2295997 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Table structure for registered
-- ----------------------------
DROP TABLE IF EXISTS `registered`;
CREATE TABLE `registered`  (
  `Id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `RegId` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `RegCode` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Table structure for template
-- ----------------------------
DROP TABLE IF EXISTS `template`;
CREATE TABLE `template`  (
  `Id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `GroupId` int(11) NULL DEFAULT NULL COMMENT '��id',
  `Title` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Position` int(11) NULL DEFAULT NULL,
  `Showbgpic` tinyint(4) NULL DEFAULT NULL,
  `FlexDirection` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BackgroundImage` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BackgroundColor` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Components` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `CreatedTime` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`Id`) USING BTREE,
  INDEX `IX_GroupId`(`GroupId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 207 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `Id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 34 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;


INSERT INTO `users` VALUES ('1', 'daniel', '202cb962ac59075b964b07152d234b70');

-- ----------------------------
-- Table structure for usertemplate
-- ----------------------------
DROP TABLE IF EXISTS `usertemplate`;
CREATE TABLE `usertemplate`  (
  `Id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `UserId` int(11) NOT NULL,
  `TemplateId` int(11) NOT NULL,
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1449 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Table structure for vgroup
-- ----------------------------
DROP TABLE IF EXISTS `vgroup`;
CREATE TABLE `vgroup`  (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `GroupName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '����',
  `ParentId` int(11) NULL DEFAULT 0 COMMENT '����id',
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;

-- 存储过程
drop procedure if exists retain_last_days_data;  
CREATE PROCEDURE `retain_last_days_data`(IN `retainDay` int)
BEGIN
	DELETE FROM datastatistics WHERE CreatedTime<
(
   SELECT ct FROM (
          SELECT DATE_FORMAT(DATE_SUB(max(createdtime),INTERVAL retainDay DAY),'%Y-%m-%d') ct FROM datastatistics
    )datastatistics_new
);
END;

SET GLOBAL event_scheduler = 1;

-- 事件
drop EVENT if exists retain_last_days_data;  
   CREATE EVENT `retain_last_days_data`
   ON SCHEDULE
   EVERY '1' DAY STARTS concat(date(now()), ' 01:00:00')
   DO call retain_last_days_data(7);
