settle_down_management/doc/update.sql

-- 2022年10月18日 frame 0.5.3 版本字段调整
SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE `biz_user` ADD COLUMN `is_lock` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '0-未锁定，1-已锁定' AFTER `is_init`;

ALTER TABLE `biz_user` ADD COLUMN `reset_at` datetime(0) NULL DEFAULT NULL COMMENT '密码最新重置时间' AFTER `is_lock`;

ALTER TABLE `frame_user` ADD COLUMN `reset_at` datetime(0) NULL DEFAULT NULL COMMENT '密码最新重置时间' AFTER `comment`;

ALTER TABLE `frame_user` ADD COLUMN `is_lock` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '0-未锁定，1-已锁定' AFTER `is_init`;

ALTER TABLE `frame_user` MODIFY COLUMN `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户账号' AFTER `department_code`;

ALTER TABLE `frame_user` MODIFY COLUMN `phone` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '联系电话' AFTER `gender`;

ALTER TABLE `frame_user` MODIFY COLUMN `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '联系邮箱' AFTER `phone`;

ALTER TABLE `frame_user` MODIFY COLUMN `leader` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '上级领导ID' AFTER `position`;

ALTER TABLE `frame_user` MODIFY COLUMN `posts` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '岗位，逗号分隔' AFTER `leader`;

ALTER TABLE `frame_user` MODIFY COLUMN `status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态：0-禁用， 1-启用' AFTER `is_lock`;

ALTER TABLE `frame_user` MODIFY COLUMN `flag` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标识，用于不同系统定义不同内容\r\n本系统中：\r\n0或空-非销售，1-销售人员' AFTER `status`;

ALTER TABLE `frame_user` MODIFY COLUMN `del_flag` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '删除标志（0代表存在 2代表删除）' AFTER `flag`;

SET FOREIGN_KEY_CHECKS=1;

-- 2023年9月20日
SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE `idea_base`.`flow_definition` ADD COLUMN `deploy_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部署ID，部署之后产生' AFTER `deploy_key`;

ALTER TABLE `idea_base`.`frame_data` ADD COLUMN `thumbnail_path` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL AFTER `created_at`;

ALTER TABLE `idea_base`.`frame_permission` ADD COLUMN `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '权限描述' AFTER `name`;

ALTER TABLE `idea_base`.`frame_permission` DROP COLUMN `descripion`;

CREATE TABLE `idea_base`.`frame_permission_favor`  (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `pid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'pemission id, 权限id',
  `uid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'user id 系统用户id',
  `path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'menu path',
  `created_at` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

ALTER TABLE `idea_base`.`frame_role` COMMENT = '菜单角色表';

ALTER TABLE `idea_base`.`frame_role` ADD COLUMN `comments` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '角色说明' AFTER `halfChecks`;

ALTER TABLE `idea_base`.`frame_role` ADD COLUMN `updated_at` datetime(0) NULL DEFAULT NULL AFTER `comments`;

ALTER TABLE `idea_base`.`frame_role_data` COMMENT = '数据角色表';

ALTER TABLE `idea_base`.`frame_role_data` ADD COLUMN `comments` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '说明' AFTER `status`;

ALTER TABLE `idea_base`.`frame_role_mobile` COMMENT = '移动端菜单角色表';

ALTER TABLE `idea_base`.`frame_role_mobile` ADD COLUMN `status` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态， 0正常 1停用' AFTER `role_key`;

ALTER TABLE `idea_base`.`frame_role_mobile` ADD COLUMN `comments` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '说明' AFTER `status`;

CREATE TABLE `idea_base`.`frame_role_mobile_user`  (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `role_mobile_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '移动端角色ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '移动端菜单角色-用户关联表' ROW_FORMAT = Dynamic;

ALTER TABLE `idea_base`.`frame_user` ADD COLUMN `level` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '职级（字典）' AFTER `position`;

ALTER TABLE `idea_base`.`frame_user_dept` ADD COLUMN `level` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '职级，字典' AFTER `posts`;

ALTER TABLE `idea_base`.`ops_server_record` ADD INDEX `server_id_index`(`server_id`) USING BTREE;

ALTER TABLE `idea_base`.`sys_dept` ADD COLUMN `charged_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门负责人' AFTER `dept_attr`;

ALTER TABLE `idea_base`.`sys_dept` DROP COLUMN `changed_by`;

CREATE TABLE `idea_base`.`sys_message_info`  (
  `id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键',
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主题',
  `version_no` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '版本号',
  `push_by` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '发布人id',
  `push_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '发布人名称',
  `push_time` datetime(0) NULL DEFAULT NULL COMMENT '发布时间',
  `update_by` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人id',
  `update_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人name',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `create_by` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人id',
  `create_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人name',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `state` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态(0=已发布，1=待发布，2=已撤回)',
  `annex` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '附件',
  `push_state` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '发布状态(0=发布，1=暂存)',
  `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '内容',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `idea_base`.`sys_message_read`  (
  `id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键',
  `msg_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '消息id',
  `user_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '阅读人id',
  `read_time` datetime(0) NULL DEFAULT NULL COMMENT '阅读时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `idea_base`.`sys_my_menu`  (
  `id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户ID',
  `menu_id` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '菜单ID',
  `created_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建',
  `created_at` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `updated_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `updated_at` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS=1;