create_tables.sql
7.54 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
-- 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 "sys_dict_type" (
"id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
"name" varchar(100) NOT NULL DEFAULT '',
"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_login_log` (
`id` bigint(20) NOT NULL GENERATED BY DEFAULT AS IDENTITY,
`log_type` bigint(4) 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,
`create_by` varchar(64) DEFAULT '',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_by` varchar(64) DEFAULT '',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted` bit(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) COMMENT ='系统访问记录';
CREATE TABLE `sys_operate_log` (
`id` bigint(20) NOT NULL GENERATED BY DEFAULT AS IDENTITY,
`trace_id` varchar(64) NOT NULL DEFAULT '',
`user_id` bigint(20) NOT NULL,
`module` varchar(50) NOT NULL,
`name` varchar(50) NOT NULL,
`operate_type` bigint(4) NOT NULL DEFAULT '0',
`content` varchar(2000) NOT NULL DEFAULT '',
`exts` varchar(512) NOT NULL DEFAULT '',
`request_method` varchar(16) DEFAULT '',
`request_url` varchar(255) DEFAULT '',
`user_ip` varchar(50) DEFAULT NULL,
`user_agent` varchar(200) DEFAULT NULL,
`java_method` varchar(512) NOT NULL DEFAULT '',
`java_method_args` varchar(8000) DEFAULT '',
`start_time` datetime NOT NULL,
`duration` int(11) NOT NULL,
`result_code` int(11) NOT NULL DEFAULT '0',
`result_msg` varchar(512) DEFAULT '',
`result_data` varchar(4000) DEFAULT '',
`create_by` varchar(64) DEFAULT '',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_by` varchar(64) DEFAULT '',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted` bit(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) COMMENT ='操作日志记录';
create table "sys_user" (
"id" bigint not null GENERATED BY DEFAULT AS IDENTITY,
"username" varchar(30) not null,
"password" varchar(100) not null default '',
"nickname" varchar(30) not null,
"remark" varchar(500) default null,
"dept_id" bigint default null,
"post_ids" varchar(255) default null,
"email" varchar(50) default '',
"mobile" varchar(11) default '',
"sex" tinyint default '0',
"avatar" varchar(100) default '',
"status" tinyint not null default '0',
"login_ip" varchar(50) default '',
"login_date" timestamp 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 '用户信息表';