/*
 Navicat Premium Data Transfer

 Source Server         : dev-192.168.202.90
 Source Server Type    : MySQL
 Source Server Version : 50536
 Source Host           : 192.168.202.90
 Source Database       : data_manager

 Target Server Type    : MySQL
 Target Server Version : 50536
 File Encoding         : utf-8

 Date: 07/04/2017 16:55:29 PM
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `config`
-- ----------------------------
DROP TABLE IF EXISTS `config`;
CREATE TABLE `config` (
  `config_id` int(11) NOT NULL DEFAULT '0' COMMENT '系统配置id',
  `config_title` varchar(128) NOT NULL DEFAULT '' COMMENT '系统配置项的标题',
  `config_schema` text NOT NULL COMMENT '系统配置的Json schema定义',
  `config_data` text NOT NULL COMMENT '生成的配置',
  `creater` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
  `writers` varchar(2048) DEFAULT NULL,
  `readers` varchar(2048) DEFAULT NULL,
  `ctime` datetime NOT NULL COMMENT '创建时间',
  `mtime` datetime NOT NULL COMMENT '修改时间',
  `last_author` varchar(64) NOT NULL DEFAULT '' COMMENT '最后一次修改人',
  PRIMARY KEY (`config_id`),
  KEY `creater_index` (`creater`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `favorites`
-- ----------------------------
DROP TABLE IF EXISTS `favorites`;
CREATE TABLE `favorites` (
  `favorite_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '收藏id',
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `data_type` int(11) NOT NULL DEFAULT '0' COMMENT '类型: 1 数据表, 2 配置项',
  `data_id` int(11) NOT NULL DEFAULT '0' COMMENT '收场的数据id',
  `ctime` datetime NOT NULL COMMENT '创建时间',
  `data_args` varchar(1024) NOT NULL DEFAULT '',
  PRIMARY KEY (`favorite_id`),
  UNIQUE KEY `user_id` (`user_id`,`data_type`,`data_id`)
) ENGINE=InnoDB AUTO_INCREMENT=100012 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `table_op_history`
-- ----------------------------
DROP TABLE IF EXISTS `table_op_history`;
CREATE TABLE `table_op_history` (
  `table_id` int(11) NOT NULL DEFAULT '0' COMMENT '操作的表id',
  `table_key` varchar(128) NOT NULL DEFAULT '' COMMENT 'key的值, 主键字段排序后以:分隔拼接',
  `optype` tinyint(4) NOT NULL DEFAULT '0' COMMENT '操作类型: 1 INSERT; 2 UPDATE; 3 DELETE',
  `author` varchar(64) NOT NULL DEFAULT '' COMMENT '操作者帐号',
  `mtime` datetime NOT NULL COMMENT '修改时间',
  `new_content` mediumblob NOT NULL COMMENT '修改后的值; Json结构保存; DELETE 为空',
  `old_content` mediumblob NOT NULL COMMENT '修改前的值; Json结构保存; INSERT 为空',
  KEY `table_id` (`table_id`,`table_key`),
  KEY `author_index` (`author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `tableconfig`
-- ----------------------------
DROP TABLE IF EXISTS `tableconfig`;
CREATE TABLE `tableconfig` (
  `table_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '配置表id, 自增值',
  `title` varchar(128) NOT NULL DEFAULT '' COMMENT '标题',
  `description` varchar(1024) NOT NULL DEFAULT '' COMMENT '配置描述',
  `dbname` varchar(64) NOT NULL DEFAULT '' COMMENT '数据库名',
  `tablename` varchar(64) NOT NULL DEFAULT '' COMMENT '数据表名',
  `passwd` varchar(64) NOT NULL DEFAULT '' COMMENT '数据库用户密码',
  `username` varchar(64) NOT NULL DEFAULT '' COMMENT '数据库用户名',
  `host_type` int(11) NOT NULL DEFAULT '0' COMMENT 'host配置类型: 0 ip地址; 1 DNS ...',
  `port` int(11) NOT NULL DEFAULT '0' COMMENT '数据库端口',
  `host` varchar(128) NOT NULL DEFAULT '' COMMENT '数据库地址',
  `fields` text NOT NULL COMMENT '数据库字段配置, Json结构: { "name":{"name":"","title":"","order":1,"def":"","desc":"","ishide":false,"islist":false,"need":false,"dict":[{"key":"", "name":""}],"type":1}}',
  `keyfield` varchar(128) NOT NULL DEFAULT '' COMMENT '数据表的主键字段，或者联合主键字段, 字符串数组["xx","xxx"], 如果不设置不能做删除修改操作',
  `sortfield` varchar(128) NOT NULL DEFAULT '' COMMENT '默认排序的字段列表: ["xxx"]',
  `groupfield` varchar(128) NOT NULL DEFAULT '' COMMENT '分组字段字段列表: ["xxx"]',
  `creater` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
  `writers` varchar(256) NOT NULL DEFAULT '' COMMENT '具有写权限的用户列表',
  `readers` varchar(2048) DEFAULT NULL,
  `ctime` datetime NOT NULL COMMENT '创建时间',
  `mtime` datetime NOT NULL COMMENT '修改时间',
  `last_author` varchar(64) NOT NULL DEFAULT '' COMMENT '最后一次修改人',
  `adv_config` text NOT NULL,
  PRIMARY KEY (`table_id`),
  KEY `creater_index` (`creater`)
) ENGINE=InnoDB AUTO_INCREMENT=1034 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Triggers structure for table config
-- ----------------------------
DROP TRIGGER IF EXISTS `config_berfor_insert`;
delimiter ;;
CREATE TRIGGER `config_berfor_insert` BEFORE INSERT ON `config` FOR EACH ROW SET NEW.ctime = NOW(), NEW.mtime = NOW()
 ;;
delimiter ;

delimiter ;;
-- ----------------------------
--  Triggers structure for table tableconfig
-- ----------------------------
 ;;
delimiter ;
DROP TRIGGER IF EXISTS `tableconfig_berfor_insert`;
delimiter ;;
CREATE TRIGGER `tableconfig_berfor_insert` BEFORE INSERT ON `tableconfig` FOR EACH ROW SET NEW.ctime = NOW(), NEW.mtime = NOW();
 ;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;