server/src/test/resources/sql/create_tables.sql

-- inf 开头的 DB

CREATE TABLE IF NOT EXISTS "inf_config" (
    "id" int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "group" varchar(50) NOT NULL,
    "type" tinyint NOT NULL,
    "name" varchar(100) NOT NULL DEFAULT '',
    "key" varchar(100) NOT NULL DEFAULT '',
    "value" varchar(500) NOT NULL DEFAULT '',
    "sensitive" bit NOT NULL,
    "remark" varchar(500) DEFAULT NULL,
    "create_by" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "update_by" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    PRIMARY KEY ("id")
) COMMENT '参数配置表';

-- sys 开头的 DB

CREATE TABLE IF NOT EXISTS "sys_dept" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "name" varchar(30) NOT NULL DEFAULT '',
    "parent_id" bigint NOT NULL DEFAULT '0',
    "sort" int NOT NULL DEFAULT '0',
    "leader" varchar(20) DEFAULT NULL,
    "phone" varchar(11) DEFAULT NULL,
    "email" varchar(50) DEFAULT NULL,
    "status" tinyint NOT NULL,
    "create_by" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "update_by" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    PRIMARY KEY ("id")
) COMMENT '部门表';

CREATE TABLE IF NOT EXISTS "sys_dict_data" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "sort" int NOT NULL DEFAULT '0',
    "label" varchar(100) NOT NULL DEFAULT '',
    "value" varchar(100) NOT NULL DEFAULT '',
    "dict_type" varchar(100) NOT NULL DEFAULT '',
    "status" tinyint NOT NULL DEFAULT '0',
    "remark" varchar(500) DEFAULT NULL,
    "create_by" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "update_by" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    PRIMARY KEY ("id")
) COMMENT '字典数据表';

CREATE TABLE IF NOT EXISTS "sys_role" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "name" varchar(30) NOT NULL,
    "code" varchar(100) NOT NULL,
    "sort" int NOT NULL,
    "data_scope" tinyint NOT NULL DEFAULT '1',
    "data_scope_dept_ids" varchar(500) NOT NULL DEFAULT '',
    "status" tinyint NOT NULL,
    "type" tinyint NOT NULL,
    "remark" varchar(500) DEFAULT NULL,
    "create_by" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "update_by" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    PRIMARY KEY ("id")
) COMMENT '角色信息表';

CREATE TABLE IF NOT EXISTS "sys_role_menu" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "role_id" bigint NOT NULL,
    "menu_id" bigint NOT NULL,
    "create_by" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "update_by" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    PRIMARY KEY ("id")
) COMMENT '角色和菜单关联表';

CREATE TABLE IF NOT EXISTS "sys_menu" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "name" varchar(50) NOT NULL,
    "permission" varchar(100) NOT NULL DEFAULT '',
    "menu_type" tinyint NOT NULL,
    "sort" int NOT NULL DEFAULT '0',
    "parent_id" bigint NOT NULL DEFAULT '0',
    "path" varchar(200) DEFAULT '',
    "icon" varchar(100) DEFAULT '#',
    "component" varchar(255) DEFAULT NULL,
    "status" tinyint NOT NULL DEFAULT '0',
    "create_by" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "update_by" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    PRIMARY KEY ("id")
) COMMENT '菜单权限表';

CREATE TABLE IF NOT EXISTS `sys_user` (
`id` bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '用户ID',
`username` varchar(30) NOT NULL COMMENT '用户账号',
`password` varchar(100) NOT NULL DEFAULT '' COMMENT '密码',
`nickname` varchar(30) NOT NULL COMMENT '用户昵称',
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
`dept_id` bigint(20) DEFAULT NULL COMMENT '部门ID',
`post_ids` varchar(255) DEFAULT NULL COMMENT '岗位编号数组',
`email` varchar(50) DEFAULT '' COMMENT '用户邮箱',
`mobile` varchar(11) DEFAULT '' COMMENT '手机号码',
`sex` tinyint(4) DEFAULT '0' COMMENT '用户性别',
`avatar` varchar(100) DEFAULT '' COMMENT '头像地址',
`status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '帐号状态（0正常 1停用）',
`login_ip` varchar(50) DEFAULT '' COMMENT '最后登录IP',
`login_date` datetime DEFAULT NULL COMMENT '最后登录时间',
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted` bit NOT NULL DEFAULT FALSE,
PRIMARY KEY (`id`)
) COMMENT='用户信息表';