ssh_sso/sql/ssp-server.sql

-- ======================================== Sa-Sso-Pro 系统表 ====================================  

-- 系统角色表 
drop table if exists sp_role; 
CREATE TABLE `sp_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色id，--主键、自增',
  `name` varchar(20) NOT NULL COMMENT '角色名称, 唯一约束',
  `info` varchar(200) DEFAULT NULL COMMENT '角色详细描述',
  `is_lock` int(11) NOT NULL DEFAULT '1' COMMENT '是否锁定(1=是,2=否), 锁定之后不可随意删除, 防止用户误操作',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='系统角色表';

INSERT INTO `sp_role`(`id`, `name`, `info`, `is_lock`) VALUES (1, 'Root 超管', '拥有系统最高权限', 1);
INSERT INTO `sp_role`(`id`, `name`, `info`, `is_lock`) VALUES (2, '管理员', '系统维护管理员', 2);
INSERT INTO `sp_role`(`id`, `name`, `info`, `is_lock`) VALUES (11, '普通账号', '普通账号', 2);
INSERT INTO `sp_role`(`id`, `name`, `info`, `is_lock`) VALUES (12, '测试角色', '测试角色', 2);



-- 菜单表 
drop table if exists sp_menu;
CREATE TABLE `sp_menu` (
  `aid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id [no]',
  `id` varchar(50) NOT NULL COMMENT '菜单id',
  `title` varchar(50) NOT NULL COMMENT '菜单名称',
  `icon` varchar(200) COMMENT '菜单图标',
  `info` varchar(500) COMMENT '菜单介绍',
  `type` varchar(20) default 'com' COMMENT '菜单类型(dir=目录, com=组件, btn=按钮, link=链接)',
  `path` varchar(500) COMMENT '菜单路由',
	`component_path` varchar(500) COMMENT '组件路径',
  `url` varchar(1024) COMMENT '菜单url (如果指定了此值，则通过 iframe 打开页面视图)',
  `is_blank` varchar(1024) COMMENT '是否属于外部链接 (如果为true, 则点击菜单时从新窗口打开url) [j switch=true]',
  `show` varchar(500) COMMENT '是否显示 (yes=永远显示,no=永远不显示,auth=根据权限决定是否显示) [j a-type=3]',
  `auth` varchar(500) COMMENT '是否鉴权 [j a-type=3]',
  `parent_id` varchar(50) COMMENT '父菜单id',
	`sort` bigint(20) COMMENT '排序索引 [num]', 
  `create_time` datetime COMMENT '创建时间',
  `update_time` datetime COMMENT '更新时间',
  PRIMARY KEY (`aid`) USING BTREE,
  UNIQUE KEY `id` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='菜单表';

INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1001, 'bas', '身份相关', '', '', 'dir', '', '', '', '0', 'no', '1', '-1', 1001, '2022-11-09 16:15:27', '2022-11-12 13:20:49');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1002, 'in-system', '允许进入后台管理', '', '', 'btn', '', '', '', '0', 'no', '1', 'bas', 1003, '2022-11-09 16:18:13', '2022-11-30 05:29:24');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1003, 'root', 'Root 权限（最高权限）', '', '当前系统的最高权限标识，请谨慎授权', 'btn', '', '', '', '0', 'no', '1', 'bas', 1002, '2022-11-09 16:16:50', '2022-11-30 03:32:28');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1004, 'auth', '权限控制', 'el-icon-Unlock', '控制 Admin 管理员对后台的访问规则', 'dir', '', '', '', '0', 'auth', '1', '-1', 1004, '2022-11-09 16:18:51', '2022-11-30 03:55:23');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1005, 'role-list', '角色管理', 'el-icon-Unlock', '', 'com', '', '@/sp-views/sp-role/role-list.vue', NULL, '0', 'auth', '1', 'auth', 1005, '2022-11-09 16:24:52', '2022-12-11 22:01:33');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1006, 'menu-list', '菜单管理', 'el-icon-CollectionTag', '', 'com', '', '/@/sp-views/sp-role/menu-list.vue', NULL, '0', 'auth', '1', 'auth', 1006, '2022-11-09 16:25:41', '2022-12-11 22:01:59');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1007, 'admin-list', '管理员列表', 'el-icon-Key', '管理所有可以登录后台的 Admin 账号', 'com', '', '@/sp-views/sp-admin/admin-list.vue', NULL, '0', 'auth', '1', 'auth', 1007, '2022-11-09 17:01:02', '2022-12-11 22:02:09');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1008, 'console', '监控中心', 'el-icon-View', '提供 Redis、SQL、API访问日志等在线监控能力', 'dir', '', '', '', '0', 'auth', '1', '-1', 1008, '2022-11-09 18:11:13', '2022-11-30 03:54:16');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1009, 'redis-console', 'Redis 监控台', 'el-icon-Search', '', 'com', '', '/@/sp-views/sp-console/redis-console.vue', NULL, '0', 'auth', '1', 'console', 1009, '2022-11-09 18:12:32', '2022-11-30 05:12:32');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1010, 'apilog-list', 'API 请求日志', 'el-icon-MostlyCloudy', '', 'com', '', '@/sp-views/sp-apilog/apilog-list.vue', NULL, '0', 'auth', '1', 'console', 1010, '2022-11-09 18:13:35', '2022-11-30 05:12:37');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1015, 'admin-add', '管理员添加', 'el-icon-Key', '按钮权限：决定管理员列表页是否显示 [ 管理员添加 ] 按钮', 'btn', '', '@/sp-views/sp-admin/admin-add.vue', '', '0', 'no', '1', 'auth', 1011, '2022-11-12 18:02:34', '2022-12-11 22:02:15');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1016, 'sp-admin-login', '管理员登录日志', 'el-icon-Mouse', '', 'com', '', '@/sp-views/sp-admin-login/sp-admin-login-list.vue', NULL, '0', 'auth', '1', 'auth', 1012, '2022-11-12 18:03:37', '2022-12-11 22:02:23');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1017, 'sql-console', 'SQL 监控台', 'el-icon-View', '', 'link', '', NULL, '${SERVER_URL}/druid/index.html', '0', 'auth', '1', 'console', 1013, '2022-11-12 18:04:46', '2022-11-30 03:53:46');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1018, 'form-generator', '在线表单构建', 'el-icon-View', '', 'link', '', NULL, 'https://mrhj.gitee.io/form-generator', '0', 'no', '1', 'console', 1014, '2022-11-12 18:05:25', NULL);
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1019, 'sys-client', '应用管理', 'el-icon-Eleme', '管理所有可 SSO 授权的 url 地址', 'dir', '', '', '', '0', 'auth', '1', '-1', 1015, '2022-11-13 11:48:27', '2022-11-30 03:32:56');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1020, 'sys-client-list', '应用列表', '', '', 'com', '', '@/views/sys-client/sys-client-list.vue', NULL, '0', 'auth', '1', 'sys-client', 1016, '2022-11-13 11:48:52', '2022-11-13 11:49:04');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1021, 'sys-client-add', '应用添加', '', '', 'btn', '', '', '', '0', 'no', '1', 'sys-client', 1017, '2022-11-13 11:50:59', NULL);
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1022, 'sys-user', '用户管理', 'el-icon-User', '管理 SSO 统一认证的 User 用户', 'dir', '', '', '', '0', 'auth', '1', '-1', 1018, '2022-11-13 11:51:39', '2022-11-30 03:33:42');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1023, 'sys-user-list', '用户列表', '', '', 'com', '', '@/views/sys-user/sys-user-list.vue', NULL, '0', 'auth', '1', 'sys-user', 1019, '2022-11-13 11:51:59', NULL);
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1024, 'sys-login-log', '登录日志', '', '', 'com', '', '@/views/sys-login-log/sys-login-log-list.vue', NULL, '0', 'auth', '1', 'sys-user', 1021, '2022-11-13 11:52:51', NULL);
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1025, 'console-plate', '数据走势', '', '无此权限的用户无法进入首页大屏', 'com', '', '@/views/sys-user-sta/console-plate.vue', NULL, '0', 'auth', '1', 'sys-user', 1034, '2022-11-13 11:53:15', '2022-11-30 06:18:50');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1026, 'sys-user-online', '在线用户', 'el-icon-CollectionTag', '查看所有正在登录的 User 用户，提供踢人下线操作', 'dir', '', '', '', '0', 'auth', '1', '-1', 1022, '2022-11-13 11:54:19', '2022-11-30 03:38:00');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1027, 'sys-user-online-list', '在线用户', '', '', 'com', '', '@/views/sys-user-online/sys-user-online-list.vue', NULL, '0', 'auth', '1', 'sys-user-online', 1023, '2022-11-13 11:54:52', NULL);
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1028, 'sp-config', '系统配置', 'el-icon-Setting', '维护系统全局参数配置、User 用户同步 等', 'dir', '', '', '', '0', 'auth', '1', '-1', 1024, '2022-11-13 11:55:14', '2022-11-30 03:53:34');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1029, 'config-view-info', '系统信息', 'el-icon-Plus', '', 'com', '', '@/sp-views/sp-config/config-view-info.vue', NULL, '0', 'auth', '1', 'sp-config', 1025, '2022-11-13 11:56:02', NULL);
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1030, 'config-view-server', '全局参数', 'el-icon-Plus', '', 'com', '', '@/sp-views/sp-config/config-view-server.vue', NULL, '0', 'auth', '1', 'sp-config', 1026, '2022-11-13 11:57:34', NULL);
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1031, 'sp-config-list', '表格视图', 'el-icon-Postcard', '', 'com', '', '@/sp-views/sp-config/sp-config-list.vue', NULL, '0', 'auth', '1', 'sp-config', 1035, '2022-11-13 11:58:00', NULL);
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1032, 'test', '组件测试', 'el-icon-Scissor', '提供 UI 表单增删改查的封装写法展示', 'dir', '', '@/sp-views/test/data-info.vue', '', '0', 'yes', '1', '-1', 1028, '2022-11-13 11:58:52', '2022-11-30 03:38:39');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1033, 'data-list', '简单列表', 'el-icon-DocumentRemove', '', 'com', '', '@/sp-views/test/data-list.vue', NULL, '0', 'yes', '1', 'test', 1029, '2022-11-13 12:04:10', NULL);
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1034, 'data-list2', '复杂列表', 'el-icon-DocumentRemove', '', 'com', '', '@/sp-views/test/list-more/data-list2.vue', NULL, '0', 'yes', '1', 'test', 1030, '2022-11-13 12:04:49', NULL);
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1035, 'data-add', '表单提交', 'el-icon-Edit', '', 'com', '', '@/sp-views/test/data-add.vue', NULL, '0', 'yes', '1', 'test', 1031, '2022-11-13 12:05:24', '2022-11-13 12:24:11');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1036, 'data-info', '信息展示', 'el-icon-Finished', '', 'com', '', '@/sp-views/test/data-info.vue', NULL, '0', 'yes', '1', 'test', 1032, '2022-11-13 12:06:00', '2022-11-13 12:26:18');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1050, 'sys-user-list-gc', '用户回收站', '', '', 'com', '', '@/views/sys-user/sys-user-list-gc.vue', NULL, '0', 'auth', '1', 'sys-user', 1020, '2022-11-18 02:48:47', '2022-11-30 03:52:59');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1051, 'config-view-sync-user', '用户同步', 'el-icon-Plus', '', 'com', '', '@/sp-views/sp-config/config-view-sync-user.vue', NULL, '0', 'auth', '1', 'sp-config', 1027, '2022-11-18 10:19:48', '2022-11-30 03:53:21');
INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1052, 'sys-client-visit', '应用访问关系', '', '', 'com', '', '@/views/sys-client-visit/sys-client-visit-list.vue', NULL, '0', 'auth', '1', 'sys-client', 1036, '2022-12-03 11:58:17', NULL);




-- 角色权限对应表  
drop table if exists sp_role_permission; 
CREATE TABLE `sp_role_permission` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id号',
  `role_id` bigint(20) DEFAULT NULL COMMENT '角色ID ',
  `permission_code` varchar(50) DEFAULT NULL COMMENT '菜单项ID',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='角色权限中间表';


INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'bas', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'root', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'in-system', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'auth', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'role-list', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'menu-list', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'admin-list', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'admin-add', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sp-admin-login', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'console', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'redis-console', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'apilog-list', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sql-console', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'form-generator', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-client', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-client-list', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-client-add', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-client-visit', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-user', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-user-list', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-user-list-gc', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-login-log', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'console-plate', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-user-online', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-user-online-list', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sp-config', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'config-view-info', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'config-view-server', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'config-view-sync-user', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sp-config-list', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'in-system', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-client', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-client-list', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-client-add', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-client-visit', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-user', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-user-list', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-user-list-gc', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-login-log', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'console-plate', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-user-online', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-user-online-list', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sp-config', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'config-view-info', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'config-view-server', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'config-view-sync-user', now());
INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sp-config-list', now());


-- 系统管理员表 
drop table if exists sp_admin; 
CREATE TABLE `sp_admin` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id，--主键、自增',
  `name` varchar(100) NOT NULL COMMENT 'admin名称',
  `avatar` varchar(500) DEFAULT NULL COMMENT '头像地址',
  `password` varchar(100) DEFAULT NULL COMMENT '密码',
  `pw` varchar(50) DEFAULT NULL COMMENT '明文密码',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `role_id` int(11) DEFAULT '11' COMMENT '所属角色id',
  `status` int(11) DEFAULT '1' COMMENT '账号状态(1=正常, 2=禁用)',
  `create_by_aid` bigint(20) DEFAULT '-1' COMMENT '创建自哪个管理员',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `login_time` datetime DEFAULT NULL COMMENT '上次登陆时间',
  `login_ip` varchar(50) DEFAULT NULL COMMENT '上次登陆IP',
  `login_count` int(11) DEFAULT '0' COMMENT '登陆次数',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='系统管理员表';

INSERT INTO `sp_admin`(`id`, `name`, `avatar`, `password`, `pw`, `role_id`, create_time) 
VALUES (10001, 'sa', 'http://file.dev33.cn/ssp/avatar1.png', 'E4EF2A290589A23EFE1565BB698437F5', '123456', 1, now()); 
INSERT INTO `sp_admin`(`id`, `name`, `avatar`, `password`, `pw`, `role_id`, create_time) 
VALUES (10002, 'admin', 'http://file.dev33.cn/ssp/avatar2.png', '1DE197572C0B23B82BB2F54202E8E00B', 'admin', 2, now()); 
INSERT INTO `sp_admin`(`id`, `name`, `avatar`, `password`, `pw`, `role_id`, create_time) 
VALUES (10003, 'uper', 'http://file.dev33.cn/ssp/avatar3.png', '276AE2077ADA0ACEDA51B9D3432E4764', '123123', 11, now()); 
INSERT INTO `sp_admin`(`id`, `name`, `avatar`, `password`, `pw`, `role_id`, create_time) 
VALUES (10004, 'sky', 'http://file.dev33.cn/ssp/avatar4.png', 'D8E4064CBDDC70E9B54ED85155160F6F', '123123', 11, now()); 


-- 管理员登录日志表 
drop table if exists sp_admin_login;
CREATE TABLE `sp_admin_login` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id号',
  `acc_id` bigint(20) NOT NULL COMMENT '管理员账号id',
  `acc_token` varchar(300) DEFAULT NULL COMMENT '本次登录Token',
  `login_ip` varchar(50) DEFAULT NULL COMMENT '登陆IP',
  `login_address` varchar(127) DEFAULT NULL COMMENT '登录地点',
  `login_device` varchar(127) DEFAULT NULL COMMENT '客户端设备标识',
  `login_system` varchar(127) DEFAULT NULL COMMENT '客户端系统标识',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT 
COMMENT='管理员登录日志表';


-- 配置信息表   
drop table if exists sp_config;
CREATE TABLE `sp_config` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id号',
  `group_name` varchar(100) NOT NULL COMMENT '配置分组',
  `name` varchar(100) NOT NULL COMMENT '配置名称',
  `value` text COMMENT '配置值',
  `remarks` varchar(255) DEFAULT NULL COMMENT '备注',
  `create_time` datetime COMMENT '创建时间',
  `update_time` datetime COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='配置信息表';

INSERT INTO `sp_config`() VALUES (0, 'server', 'isAllowRegister', 'true', '是否开放注册', '2022-11-08 08:13:15', NULL);
INSERT INTO `sp_config`() VALUES (0, 'server', 'userDefaultAvatar', 'http://file.dev33.cn/ssp/user-avatar/1.jpg,http://file.dev33.cn/ssp/user-avatar/2.png,http://file.dev33.cn/ssp/user-avatar/3.jpg,http://file.dev33.cn/ssp/user-avatar/4.jpg,http://file.dev33.cn/ssp/user-avatar/5.jpg,http://file.dev33.cn/ssp/user-avatar/6.jpg,http://file.dev33.cn/ssp/user-avatar/7.jpeg', '用户默认头像', '2022-11-08 08:14:21', '2022-11-08 04:18:59');
INSERT INTO `sp_config`() VALUES (0, 'server', 'reserveInfo', '预留信息', '预留信息', '2022-11-08 08:14:58', '2022-11-08 04:19:09');
INSERT INTO `sp_config`() VALUES (0, 'info', 'appName', 'Sa-Sso-Pro 后台', '', '2022-11-08 08:39:40', NULL);
INSERT INTO `sp_config`() VALUES (0, 'info', 'appLogo', 'http://file.dev33.cn/ssp/ssp-logo-480.png', '', '2022-11-08 08:39:40', '2022-12-02 08:43:00');
INSERT INTO `sp_config`() VALUES (0, 'info', 'appVersion', 'v1.6.0', '', '2022-11-08 08:39:40', '2022-12-02 08:30:47');
INSERT INTO `sp_config`() VALUES (0, 'info', 'appIntro', 'Sa-Sso-Pro 后台管理', '', '2022-11-08 08:39:40', '2022-12-02 03:51:52');
INSERT INTO `sp_config`() VALUES (0, 'info', 'isDynamicInfo', 'false', '', '2022-12-02 08:52:04', NULL);
INSERT INTO `sp_config`() VALUES (0, 'info', 'appUpdateTime', '2023-1-13', '', '2022-12-02 08:33:16', NULL);
INSERT INTO `sp_config`() VALUES (0, 'sync-user', 'isListen', 'true', '', '2022-11-20 23:25:51', '2022-11-24 03:46:34');
INSERT INTO `sp_config`() VALUES (0, 'sync-user', 'isBrd', 'false', '', '2022-11-20 23:25:58', '2022-11-24 03:46:28');
INSERT INTO `sp_config`() VALUES (0, 'sync-user', 'brdUrlSync', '', '', '2022-11-20 23:27:02', '2022-11-24 03:46:17');
INSERT INTO `sp_config`() VALUES (0, 'sync-user', 'brdUrl', 'http://localhost:9012', '', '2022-11-20 23:27:04', NULL);
INSERT INTO `sp_config`() VALUES (0, 'sync-user', 'isListenDecrypt', 'true', '', '2022-11-22 20:56:46', '2022-11-24 03:45:32');
INSERT INTO `sp_config`() VALUES (0, 'sync-user', 'isBrdEncrypt', 'true', '', '2022-11-22 20:56:53', '2022-11-24 03:45:11');
INSERT INTO `sp_config`() VALUES (0, 'sync-user', 'listenIpList', '127.0.0.1,192.198.1.102', '', '2022-11-22 21:13:52', '2022-12-17 03:46:17');




-- 系统api请求记录表 
-- 如果此段脚本执行报错，请将 datetime(3) 改为 datetime 再次执行
drop table if exists sp_apilog; 
CREATE TABLE `sp_apilog` (
  `id` bigint(50) NOT NULL AUTO_INCREMENT COMMENT '请求id',
  `req_ip` varchar(100) DEFAULT NULL COMMENT '客户端ip',
  `req_api` varchar(512) DEFAULT NULL COMMENT '请求api',
  `req_parame` text COMMENT '请求参数',
  `req_type` varchar(50) DEFAULT NULL COMMENT '请求类型（GET、POST...）',
  `req_token` varchar(50) DEFAULT NULL COMMENT '请求token',
  `req_header` text DEFAULT NULL COMMENT '请求header',
  `res_code` varchar(50) DEFAULT NULL COMMENT '返回-状态码',
  `res_msg` text COMMENT '返回-信息描述',
  `res_string` text COMMENT '返回-整个信息字符串形式',
  `user_id` bigint(20) DEFAULT NULL COMMENT 'user_id',
  `admin_id` bigint(20) DEFAULT NULL COMMENT 'admin_id',
  `start_time` datetime(3) DEFAULT NULL COMMENT '请求开始时间',
  `end_time` datetime(3) DEFAULT NULL COMMENT '请求结束时间',
  `cost_time` bigint(20) DEFAULT NULL COMMENT '花费时间，单位ms',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='api请求记录表';






-- ======================================== 登录相关表 ====================================  

-- 用户表 
drop table if exists sys_user;
CREATE TABLE `sys_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id号 [no]',
  `username` varchar(30) DEFAULT NULL COMMENT '用户昵称 [t j=like]',
  `password` varchar(50) DEFAULT NULL COMMENT '账号密码',
  `pw` varchar(50) DEFAULT NULL COMMENT '明文密码',
  `avatar` varchar(512) DEFAULT NULL COMMENT '用户头像 [img]',
  `intro` varchar(1024) DEFAULT NULL COMMENT '个人介绍(签名) [textarea]',
  `age` int(11) DEFAULT 0 COMMENT '用户年龄 [num]',
  `sex` int(11) DEFAULT '3' COMMENT '用户性别 (1=男,2=女,3=未知) [j]',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号 [num]',
  `email` varchar(50) DEFAULT NULL COMMENT '用户邮箱',
  `status` int(11) DEFAULT '1' COMMENT '账号状态(1=正常,2=禁用) [j]',
  `create_time` datetime NOT NULL COMMENT '创建时间 [date-create]',
  `login_time` datetime DEFAULT NULL COMMENT '上次登陆时间 [date]',
  `login_ip` varchar(50) DEFAULT NULL COMMENT '上次登陆IP',
  `login_count` int(11) DEFAULT '0' COMMENT '登陆次数 [num]',
  `is_del` int(11) not null DEFAULT 1 COMMENT '是否删除(1=否,2=是) [num]',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='用户表'; 

INSERT INTO `sys_user`(`id`, `username`, `password`, `pw`, `avatar`, `age`, `sex`, `phone`, `create_time`, `is_del`) 
VALUES (1000001, 'zhang', 'AEDBE3D6D827FE5624FEF050CA9BD429', '123456', 'http://file.dev33.cn/ssp/user-avatar/1.jpg', 10, 1, '15688889999', now(), 1);

INSERT INTO `sys_user`(`id`, `username`, `password`, `pw`, `avatar`, `age`, `sex`, `phone`, `create_time`, `is_del`) 
VALUES (1000002, 'wangwu', 'F493A5C66A04B4BC7E31718D24B95049', '123456', 'http://file.dev33.cn/ssp/user-avatar/2.png', 10, 1, '13644445555', now(), 1);

INSERT INTO `sys_user`(`id`, `username`, `password`, `pw`, `avatar`, `age`, `sex`, `phone`, `create_time`, `is_del`) 
VALUES (1000003, 'zhaoliu', '0B0261D9F8A649A8B566D2C2633D2224', '123456', 'http://file.dev33.cn/ssp/user-avatar/3.jpg', 10, 1, '13644446666', now(), 2);


 
-- 单点登录日志表 
drop table if exists sys_login_log;
CREATE TABLE `sys_login_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id号 [no]',
  `user_id` bigint(20) NOT NULL COMMENT '用户id [num click=sys_user.id]', 
	`acc_token` varchar(300) DEFAULT NULL COMMENT '本次登录Token', 
  `login_ip` varchar(50) DEFAULT NULL COMMENT '登陆IP', 
  `login_address` varchar(127) DEFAULT NULL COMMENT '登录地点',
  `login_device` varchar(127) DEFAULT NULL COMMENT '客户端设备标识',
  `login_system` varchar(127) DEFAULT NULL COMMENT '客户端系统标识',
	`client_id` bigint(20) DEFAULT NULL COMMENT '所属应用id', 
	`client_domain` varchar(300) DEFAULT NULL COMMENT 'Client端域名', 
  `create_time` datetime NOT NULL COMMENT '创建时间 [date-create]',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT 
COMMENT='用户登录日志表 [fk-s js=(user_id=sys_user.id), show=username.账号昵称.avatar.头像]'; 


-- 应用表 
drop table if exists sys_client;
CREATE TABLE `sys_client` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id号 [no]',
	`name` varchar(50) NOT NULL COMMENT '应用名称', 
	`logo` varchar(512) COMMENT '应用图标 [img]', 
	`intro` varchar(512) COMMENT '应用介绍 [textarea]', 
	`allow_url` varchar(5120) COMMENT '允许授权的url (多个用逗号隔开)', 
  `is_public` int(11) DEFAULT '1' COMMENT '是否公开(1=公开应用, 2=私有应用) [j]',
  `status` int(11) DEFAULT '1' COMMENT '应用状态(1=启用, 2=禁用) [j switch=true]',
  `create_time` datetime NOT NULL COMMENT '创建时间 [date-create]',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='应用表'; 


INSERT INTO `sys_client`(`id`, `name`, `logo`, `intro`, `allow_url`, `status`, `create_time`) 
VALUES (1001, '凉云商城', 'http://file.dev33.cn/ssp/client1.png', '凉云商城，在线百货，生鲜超市', 'http://sa-sso-client.dev33.cn/sso/login', 1, '2022-01-26 22:42:11');
INSERT INTO `sys_client`(`id`, `name`, `logo`, `intro`, `allow_url`, `status`, `create_time`) 
VALUES (1002, '凉云商城（前后台分离版）', 'http://file.dev33.cn/ssp/client2.png', '凉云商城，在线百货', 'http://sa-sso-client-h5.dev33.cn/sso-login.html,http://sa-sso-client-vue2.dev33.cn/*,http://sa-sso-client-vue3.dev33.cn/*', 1, '2022-01-28 20:45:05');
INSERT INTO `sys_client`(`id`, `name`, `logo`, `intro`, `allow_url`, `status`, `create_time`) 
VALUES (1003, '凉云短视频', 'http://file.dev33.cn/ssp/client3.png', '凉云短视频，分享美好生活', 'http://sa-sso-client1.com:9001/*,http://sa-sso-client2.com:9001/*,http://sa-sso-client3.com:9001/*', 1, '2022-01-26 22:44:19');
INSERT INTO `sys_client`(`id`, `name`, `logo`, `intro`, `allow_url`, `status`, `create_time`) 
VALUES (1004, '测试应用', 'http://file.dev33.cn/ssp/client4.png', '测试应用，本地测试', 'http://127.0.0.1*,http://localhost*', 1, '2022-02-02 06:00:39');
INSERT INTO `sys_client`(`id`, `name`, `logo`, `intro`, `allow_url`, `is_public`, `status`, `create_time`) 
VALUES (1005, '测试应用2', 'http://file.dev33.cn/ssp/client5.png', '测试应用，本地测试2', 'http://192.168.1.*', 2, 1, '2022-02-02 06:00:39');



-- 应用访问关系表，决定一个用户是否可以访问一个应用 
drop table if exists sys_client_visit; 
CREATE TABLE `sys_client_visit` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '记录id',
  `client_id` bigint(20) DEFAULT NULL COMMENT '应用ID',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  `visit` int(12) DEFAULT 1 COMMENT '所属关系 (1=允许访问,2=禁止访问, 3=跟随应用) [j]',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT 
COMMENT='应用访问关系表 [fk-s js=(client_id=sys_client.id), show=name.应用名称.logo.应用Logo][fk-s js=(user_id=sys_user.id), show=username.用户昵称.avatar.用户头像]';

insert into sys_client_visit() values (0, 1001, 1000001, 1, now()); 
insert into sys_client_visit() values (0, 1005, 1000001, 1, now());
insert into sys_client_visit() values (0, 1001, 1000002, 2, now());
insert into sys_client_visit() values (0, 1002, 1000002, 2, now()); 
insert into sys_client_visit() values (0, 1003, 1000002, 3, now());