sql.patch.1.1.4.sql Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of springboot-faster-system Show documentation
springboot-faster系统管理模块
The newest version!
DROP TABLE IF EXISTS `sys_user_token`;
CREATE TABLE IF NOT EXISTS `sys_user_token` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `user_id` bigint(20) NOT NULL COMMENT '用户ID',
    `token_name` varchar(50) DEFAULT NULL COMMENT '凭证名称',
    `token_value` varchar(200) DEFAULT NULL COMMENT '凭证值',
    `business_key` varchar(200) DEFAULT NULL COMMENT '业务名称',
    `expire_time` datetime DEFAULT NULL COMMENT '过期时间',
    `is_del` tinyint(1) DEFAULT NULL COMMENT '是否删除(0正常，1已删除)',
    `create_time` datetime DEFAULT NULL COMMENT '创建时间',
    `create_user` bigint(20) DEFAULT NULL COMMENT '创建人',
    `update_time` datetime DEFAULT NULL COMMENT '修改时间',
    `update_user` bigint(20) DEFAULT NULL COMMENT '修改人',
    PRIMARY KEY (`id`),
    INDEX `idx_user`(`user_id`,`is_del`) USING BTREE COMMENT '按用户查询',
    INDEX `idx_token`(`token_name`,`token_value`,`is_del`) USING BTREE COMMENT '按凭证值查询',
    INDEX `idx_bk`(`business_key`,`is_del`) USING BTREE COMMENT '按业务查询'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户凭证表';

DROP TABLE IF EXISTS `sys_user_notice`;
DROP TABLE IF EXISTS `sys_notice`;

CREATE TABLE IF NOT EXISTS `sys_notice` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '公告ID',
    `title` varchar(200) DEFAULT NULL COMMENT '公告标题',
    `type` varchar(50) DEFAULT NULL COMMENT '类别',
    `content` longtext DEFAULT NULL COMMENT '公告内容',
    `end_time` datetime DEFAULT NULL COMMENT '公告结束时间',
    `status` int(11) DEFAULT NULL COMMENT '状态(0正常，1停用)',
    `is_del` tinyint(1) DEFAULT NULL COMMENT '是否删除(0正常，1已删除)',
    `create_time` datetime DEFAULT NULL COMMENT '创建时间',
    `create_user` bigint(20) DEFAULT NULL COMMENT '创建人',
    `update_time` datetime DEFAULT NULL COMMENT '修改时间',
    `update_user` bigint(20) DEFAULT NULL COMMENT '修改人',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='公告表';

DROP TABLE IF EXISTS `sys_notice_user`;
CREATE TABLE IF NOT EXISTS `sys_notice_user` (
     `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
     `notice_id` bigint(20) DEFAULT NULL COMMENT '公告ID',
     `content` longtext DEFAULT NULL COMMENT '非公告的消息内容,notice_id需为空',
     `content_batch` varchar(50) DEFAULT NULL COMMENT '内容批次',
     `target_user` bigint(20) NOT NULL COMMENT '接收人',
     `read_state` tinyint(1) DEFAULT NULL COMMENT '是否已读(0未读，1已读)',
     `read_time` datetime DEFAULT NULL COMMENT '已读时间',
     `create_time` datetime DEFAULT NULL COMMENT '创建时间',
     `create_user` bigint(20) DEFAULT NULL COMMENT '创建人',
     `update_time` datetime DEFAULT NULL COMMENT '修改时间',
     `update_user` bigint(20) DEFAULT NULL COMMENT '修改人',
     PRIMARY KEY (`id`),
     INDEX `idx_user`(`target_user`,`notice_id`) USING BTREE COMMENT '按用户查询',
     INDEX `idx_notice`(`notice_id`) USING BTREE COMMENT '按公告查询',
     INDEX `idx_batch`(`content_batch`) USING BTREE COMMENT '按批次查询'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='公告接收人表';

ALTER TABLE `sys_locale_source`
    MODIFY COLUMN `table_name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '表名' AFTER `en_field`,
    MODIFY COLUMN `table_condition`  varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '查询条件' AFTER `table_name`,
    MODIFY COLUMN `locale_category`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '字典类型' AFTER `table_condition`,
    ADD COLUMN `invalid_condition`  varchar(500) NULL DEFAULT NULL COMMENT '无效数据的条件，避免关联表仍使用' AFTER `table_condition`;

UPDATE `sys_locale_source` SET `table_condition`='`status`=0 and `is_del`=0',`invalid_condition`='not(`status`=0 and `is_del`=0)'  WHERE `id` in (1,2,3,4);

update `sys_user` set `avatar`=null;
ALTER TABLE `sys_user` MODIFY COLUMN `avatar`  mediumtext NULL COMMENT '头像' AFTER `sex`;