file-collect/sql/power_system.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for ums_admin
-- ----------------------------
DROP TABLE IF EXISTS `ums_admin`;
CREATE TABLE `ums_admin`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `icon` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '头像',
  `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `nick_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '昵称',
  `note` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注信息',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `login_time` datetime(0) NULL DEFAULT NULL COMMENT '最后登录时间',
  `status` int(0) NULL DEFAULT 1 COMMENT '帐号启用状态：0->禁用；1->启用',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '后台用户表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of ums_admin
-- ----------------------------
INSERT INTO `ums_admin` VALUES (1, 'test', '$2a$10$NZ5o7r2E.ayT2ZoxgjlI.eJ6OEYqjH7INR/F.mXDbjZJi9HF0YCVG', '', 'test@qq.com', '测试账号', NULL, '2018-09-29 13:55:30', '2018-09-29 13:55:39', 1);
INSERT INTO `ums_admin` VALUES (2, 'admin', '$2a$10$IRUa4uHgQHsN04TIHl9hVuXZmpVNzK.tGxuxehgy5MBnxLADzRlt6', '', 'admin@163.com', '系统管理员', '系统管理员', '2018-10-08 13:32:47', '2019-04-20 12:45:16', 1);


-- ----------------------------
-- Table structure for ums_admin_login_log
-- ----------------------------
DROP TABLE IF EXISTS `ums_admin_login_log`;
CREATE TABLE `ums_admin_login_log`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `admin_id` bigint(0) NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `ip` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `user_agent` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '浏览器登录类型',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '后台用户登录日志表' ROW_FORMAT = Dynamic;


-- ----------------------------
-- Table structure for ums_admin_role_relation
-- ----------------------------
DROP TABLE IF EXISTS `ums_admin_role_relation`;
CREATE TABLE `ums_admin_role_relation`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `admin_id` bigint(0) NULL DEFAULT NULL,
  `role_id` bigint(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '后台用户和角色关系表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of ums_admin_role_relation
-- ----------------------------
INSERT INTO `ums_admin_role_relation` VALUES (1, 1, 1);
INSERT INTO `ums_admin_role_relation` VALUES (2, 2, 1);


-- ----------------------------
-- Table structure for ums_menu
-- ----------------------------
DROP TABLE IF EXISTS `ums_menu`;
CREATE TABLE `ums_menu`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(0) NULL DEFAULT NULL COMMENT '父级ID',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `title` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜单名称',
  `level` int(0) NULL DEFAULT NULL COMMENT '菜单级数',
  `sort` int(0) NULL DEFAULT NULL COMMENT '菜单排序',
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '前端名称',
  `icon` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '前端图标',
  `hidden` int(0) NULL DEFAULT NULL COMMENT '前端隐藏',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '后台菜单表' ROW_FORMAT = Dynamic;


-- ----------------------------
-- Table structure for ums_resource
-- ----------------------------
DROP TABLE IF EXISTS `ums_resource`;
CREATE TABLE `ums_resource`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资源名称',
  `url` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资源URL',
  `description` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '描述',
  `category_id` bigint(0) NULL DEFAULT NULL COMMENT '资源分类ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '后台资源表' ROW_FORMAT = Dynamic;

INSERT INTO `ums_resource`(`id`, `create_time`, `name`, `url`, `description`, `category_id`) VALUES (1, '2020-02-07 16:47:34', '后台用户管理', '/admin/**', '', 4);
INSERT INTO `ums_resource`(`id`, `create_time`, `name`, `url`, `description`, `category_id`) VALUES (2, '2020-02-07 16:48:24', '后台用户角色管理', '/role/**', '', 4);
INSERT INTO `ums_resource`(`id`, `create_time`, `name`, `url`, `description`, `category_id`) VALUES (3, '2020-02-07 16:49:45', '后台资源管理', '/resource/**', '', 4);


-- ----------------------------
-- Table structure for ums_resource_category
-- ----------------------------
DROP TABLE IF EXISTS `ums_resource_category`;
CREATE TABLE `ums_resource_category`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分类名称',
  `sort` int(0) NULL DEFAULT NULL COMMENT '排序',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '资源分类表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of ums_resource_category
-- ----------------------------
INSERT INTO `ums_resource_category` VALUES (1, '2020-02-05 10:23:04', '权限模块', 0);
INSERT INTO `ums_resource_category` VALUES (2, '2020-02-07 16:35:49', '其他模块', 0);


-- ----------------------------
-- Table structure for ums_role
-- ----------------------------
DROP TABLE IF EXISTS `ums_role`;
CREATE TABLE `ums_role`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  `description` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '描述',
  `admin_count` int(0) NULL DEFAULT NULL COMMENT '后台用户数量',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `status` int(0) NULL DEFAULT 1 COMMENT '启用状态：0->禁用；1->启用',
  `sort` int(0) NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '后台用户角色表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of ums_role
-- ----------------------------
INSERT INTO `ums_role` VALUES (1, '超级管理员', '拥有所有查看和操作功能', 0, '2020-02-02 15:11:05', 1, 0);


-- ----------------------------
-- Table structure for ums_role_menu_relation
-- ----------------------------
DROP TABLE IF EXISTS `ums_role_menu_relation`;
CREATE TABLE `ums_role_menu_relation`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `role_id` bigint(0) NULL DEFAULT NULL COMMENT '角色ID',
  `menu_id` bigint(0) NULL DEFAULT NULL COMMENT '菜单ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '后台角色菜单关系表' ROW_FORMAT = Dynamic;


-- ----------------------------
-- Table structure for ums_role_resource_relation
-- ----------------------------
DROP TABLE IF EXISTS `ums_role_resource_relation`;
CREATE TABLE `ums_role_resource_relation`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `role_id` bigint(0) NULL DEFAULT NULL COMMENT '角色ID',
  `resource_id` bigint(0) NULL DEFAULT NULL COMMENT '资源ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '后台角色资源关系表' ROW_FORMAT = Dynamic;

INSERT INTO `ums_role_resource_relation`(`id`, `role_id`, `resource_id`) VALUES (1, 1, 1);
INSERT INTO `ums_role_resource_relation`(`id`, `role_id`, `resource_id`) VALUES (2, 1, 2);
INSERT INTO `ums_role_resource_relation`(`id`, `role_id`, `resource_id`) VALUES (3, 1, 3);
INSERT INTO `ums_role_resource_relation`(`id`, `role_id`, `resource_id`) VALUES (4, 2, 1);
INSERT INTO `ums_role_resource_relation`(`id`, `role_id`, `resource_id`) VALUES (5, 2, 2);
INSERT INTO `ums_role_resource_relation`(`id`, `role_id`, `resource_id`) VALUES (6, 2, 3);

-- ----------------------------
-- Table structure for b_province
-- ----------------------------
DROP TABLE IF EXISTS `b_province`;
CREATE TABLE `b_province`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `province` varchar(20) NOT NULL COMMENT '省份名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '省份表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of b_province
-- ----------------------------
INSERT INTO `b_province` VALUES (1, '北京市');
INSERT INTO `b_province` VALUES (2, '天津市');
INSERT INTO `b_province` VALUES (3, '上海市');
INSERT INTO `b_province` VALUES (4, '重庆市');
INSERT INTO `b_province` VALUES (5, '河北省');
INSERT INTO `b_province` VALUES (6, '山西省');
INSERT INTO `b_province` VALUES (7, '台湾省');
INSERT INTO `b_province` VALUES (8, '辽宁省');
INSERT INTO `b_province` VALUES (9, '吉林省');
INSERT INTO `b_province` VALUES (10, '黑龙江省');
INSERT INTO `b_province` VALUES (11, '江苏省');
INSERT INTO `b_province` VALUES (12, '浙江省');
INSERT INTO `b_province` VALUES (13, '安徽省');
INSERT INTO `b_province` VALUES (14, '福建省');
INSERT INTO `b_province` VALUES (15, '江西省');
INSERT INTO `b_province` VALUES (16, '山东省');
INSERT INTO `b_province` VALUES (17, '河南省');
INSERT INTO `b_province` VALUES (18, '湖北省');
INSERT INTO `b_province` VALUES (19, '湖南省');
INSERT INTO `b_province` VALUES (20, '广东省');
INSERT INTO `b_province` VALUES (21, '甘肃省');
INSERT INTO `b_province` VALUES (22, '四川省');
INSERT INTO `b_province` VALUES (23, '贵州省');
INSERT INTO `b_province` VALUES (24, '海南省');
INSERT INTO `b_province` VALUES (25, '云南省');
INSERT INTO `b_province` VALUES (26, '青海省');
INSERT INTO `b_province` VALUES (27, '陕西省');
INSERT INTO `b_province` VALUES (28, '广西壮族自治区');
INSERT INTO `b_province` VALUES (29, '西藏自治区');
INSERT INTO `b_province` VALUES (30, '宁夏回族自治区');
INSERT INTO `b_province` VALUES (31, '新疆维吾尔自治区');
INSERT INTO `b_province` VALUES (32, '内蒙古自治区');
INSERT INTO `b_province` VALUES (33, '澳门特别行政区');
INSERT INTO `b_province` VALUES (34, '香港特别行政区');

-- ----------------------------
-- Table structure for b_line
-- ----------------------------
DROP TABLE IF EXISTS `b_line`;
CREATE TABLE `b_line`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '线路ID',
  `name` varchar(50) NOT NULL COMMENT '线路名称',
  `province_id` int(0) NOT NULL COMMENT '线路所属省份',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '线路表' ROW_FORMAT = Dynamic;

INSERT INTO `b_line`(`id`, `name`, `province_id`) VALUES (1, '京珠线', 20);
INSERT INTO `b_line`(`id`, `name`, `province_id`) VALUES (2, '桃李线', 20);

-- ----------------------------
-- Table structure for b_tower
-- ----------------------------
DROP TABLE IF EXISTS `b_tower`;
CREATE TABLE `b_tower`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '杆塔表',
  `name` varchar(50) NOT NULL COMMENT '杆塔号',
  `shape` varchar(50) NOT NULL COMMENT '塔形',
  `hardware_type` varchar(50) NOT NULL COMMENT '金具串型号',
  `line_id` int(0) NOT NULL COMMENT '线路ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '塔表' ROW_FORMAT = Dynamic;

INSERT INTO `b_tower`(`id`, `name`, `shape`, `hardware_type`, `line_id`) VALUES (1, '#1', '塔型A', '金具A', 1);
INSERT INTO `b_tower`(`id`, `name`, `shape`, `hardware_type`, `line_id`) VALUES (2, '#2', '塔型B', '金具A', 1);
INSERT INTO `b_tower`(`id`, `name`, `shape`, `hardware_type`, `line_id`) VALUES (3, '#3', '塔型C', '金具B', 1);
INSERT INTO `b_tower`(`id`, `name`, `shape`, `hardware_type`, `line_id`) VALUES (4, '#4', '塔型B', '金具C', 1);
INSERT INTO `b_tower`(`id`, `name`, `shape`, `hardware_type`, `line_id`) VALUES (5, '#1', '塔型D', '金具A', 2);
INSERT INTO `b_tower`(`id`, `name`, `shape`, `hardware_type`, `line_id`) VALUES (6, '#2', '塔型C', '金具B', 2);
INSERT INTO `b_tower`(`id`, `name`, `shape`, `hardware_type`, `line_id`) VALUES (7, '#3', '塔型A', '金具D', 2);
INSERT INTO `b_tower`(`id`, `name`, `shape`, `hardware_type`, `line_id`) VALUES (8, '#4', '塔型E', '金具A', 2);


SET FOREIGN_KEY_CHECKS = 1;