server/yudao-core-service/src/test/resources/sql/create_tables.sql

-- inf 开头的 DB

CREATE TABLE IF NOT EXISTS "inf_file" (
    "id" varchar(188) NOT NULL,
    "type" varchar(63) DEFAULT NULL,
    "content" blob NOT NULL,
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint not null default  '0',
    PRIMARY KEY ("id")
) COMMENT '文件表';

-- sys 开头的 DB

CREATE TABLE IF NOT EXISTS `sys_user_session` (
    `id` varchar(32) NOT NULL,
    `user_id` bigint DEFAULT NULL,
    "user_type" tinyint NOT NULL,
    `username` varchar(50) NOT NULL DEFAULT '',
    `user_ip` varchar(50) DEFAULT NULL,
    `user_agent` varchar(512) DEFAULT NULL,
    `session_timeout` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updater` varchar(64) DEFAULT '' ,
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint not null default  '0',
    PRIMARY KEY (`id`)
) COMMENT '用户在线 Session';

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,
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" 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 "inf_api_access_log" (
    "id" bigint not null GENERATED BY DEFAULT AS IDENTITY,
    "trace_id" varchar(64) not null default '',
    "user_id" bigint not null default '0',
    "user_type" tinyint not null default '0',
    "application_name" varchar(50) not null,
    "request_method" varchar(16) not null default '',
    "request_url" varchar(255) not null default '',
    "request_params" varchar(8000) not null default '',
    "user_ip" varchar(50) not null,
    "user_agent" varchar(512) not null,
    "begin_time" timestamp not null,
    "end_time" timestamp not null,
    "duration" integer not null,
    "result_code" integer not null default '0',
    "result_msg" varchar(512) default '',
    "creator" varchar(64) default '',
    "create_time" timestamp not null default current_timestamp,
    "updater" varchar(64) default '',
    "update_time" timestamp not null default current_timestamp,
    "deleted" bit not null default false,
    "tenant_id" bigint not null default  '0',
    primary key ("id")
) COMMENT 'API 访问日志表';

CREATE TABLE IF NOT EXISTS "inf_api_error_log" (
    "id" integer not null GENERATED BY DEFAULT AS IDENTITY,
    "trace_id" varchar(64) not null,
    "user_id" bigint not null default '0',
    "user_type" tinyint not null default '0',
    "application_name" varchar(50) not null,
    "request_method" varchar(16) not null,
    "request_url" varchar(255) not null,
    "request_params" varchar(8000) not null,
    "user_ip" varchar(50) not null,
    "user_agent" varchar(512) not null,
    "exception_time" timestamp not null,
    "exception_name" varchar(128) not null default '',
    "exception_message" clob not null,
    "exception_root_cause_message" clob not null,
    "exception_stack_trace" clob not null,
    "exception_class_name" varchar(512) not null,
    "exception_file_name" varchar(512) not null,
    "exception_method_name" varchar(512) not null,
    "exception_line_number" integer not null,
    "process_status" tinyint not null,
    "process_time" timestamp default null,
    "process_user_id" bigint default '0',
    "creator" varchar(64) default '',
    "create_time" timestamp not null default current_timestamp,
    "updater" varchar(64) default '',
    "update_time" timestamp not null default current_timestamp,
    "deleted" bit not null default false,
    "tenant_id" bigint not null default  '0',
    primary key ("id")
) COMMENT '系统异常日志';

CREATE TABLE IF NOT EXISTS "sys_sms_template" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "type" tinyint NOT NULL,
    "status" tinyint NOT NULL,
    "code" varchar(63) NOT NULL,
    "name" varchar(63) NOT NULL,
    "content" varchar(255) NOT NULL,
    "params" varchar(255) NOT NULL,
    "remark" varchar(255) DEFAULT NULL,
    "api_template_id" varchar(63) NOT NULL,
    "channel_id" bigint NOT NULL,
    "channel_code" varchar(63) NOT NULL,
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" 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_sms_log" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "channel_id" bigint NOT NULL,
    "channel_code" varchar(63) NOT NULL,
    "template_id" bigint NOT NULL,
    "template_code" varchar(63) NOT NULL,
    "template_type" tinyint NOT NULL,
    "template_content" varchar(255) NOT NULL,
    "template_params" varchar(255) NOT NULL,
    "api_template_id" varchar(63) NOT NULL,
    "mobile" varchar(11) NOT NULL,
    "user_id" bigint DEFAULT '0',
    "user_type" tinyint DEFAULT '0',
    "send_status" tinyint NOT NULL DEFAULT '0',
    "send_time" timestamp DEFAULT NULL,
    "send_code" int DEFAULT NULL,
    "send_msg" varchar(255) DEFAULT NULL,
    "api_send_code" varchar(63) DEFAULT NULL,
    "api_send_msg" varchar(255) DEFAULT NULL,
    "api_request_id" varchar(255) DEFAULT NULL,
    "api_serial_no" varchar(255) DEFAULT NULL,
    "receive_status" tinyint NOT NULL DEFAULT '0',
    "receive_time" timestamp DEFAULT NULL,
    "api_receive_code" varchar(63) DEFAULT NULL,
    "api_receive_msg" varchar(255) DEFAULT NULL,
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint not null default  '0',
    PRIMARY KEY ("id")
) COMMENT '短信日志';

CREATE TABLE IF NOT EXISTS `sys_login_log` (
    `id`          bigint(20)   NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    `log_type`    bigint(4)    NOT NULL,
    "user_id" bigint not null default '0',
    "user_type" tinyint NOT NULL,
    `trace_id`    varchar(64)  NOT NULL DEFAULT '',
    `username`    varchar(50)  NOT NULL DEFAULT '',
    `result`      tinyint(4)   NOT NULL,
    `user_ip`     varchar(50)  NOT NULL,
    `user_agent`  varchar(512) NOT NULL,
    `creator`   varchar(64)           DEFAULT '',
    `create_time` datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updater`   varchar(64)           DEFAULT '',
    `update_time` datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted`     bit(1)       NOT NULL DEFAULT '0',
    "tenant_id" bigint not null default  '0',
    PRIMARY KEY (`id`)
) COMMENT ='系统访问记录';

CREATE TABLE IF NOT EXISTS "sys_social_user" (
    "id" number NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "user_id" bigint NOT NULL,
    "user_type" tinyint NOT NULL DEFAULT '0',
    "type" tinyint NOT NULL,
    "openid" varchar(32) NOT NULL,
    "token" varchar(256) DEFAULT NULL,
    "union_id" varchar(32) NOT NULL,
    "raw_token_info" varchar(1024) NOT NULL,
    "nickname" varchar(32) NOT NULL,
    "avatar" varchar(255) DEFAULT NULL,
    "raw_user_info" varchar(1024) NOT NULL,
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    PRIMARY KEY ("id")
) COMMENT '社交用户';