system.sql
8.41 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
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
/*==============================================================*/
/* DBMS name: MySQL 5.0 */
/* Created on: 2018/3/14 17:22:08 */
/*==============================================================*/
SET FOREIGN_KEY_CHECKS=0;
drop table if exists farm;
drop table if exists resources;
#drop index farm_role_index on role;
drop table if exists role;
drop table if exists role_resources;
drop table if exists user_role;
#drop index farm_user_index on users;
drop table if exists users;
drop table if exists api_interface;
/*==============================================================*/
/* Table: farm */
/*==============================================================*/
create table farm
(
farm_id bigint not null auto_increment,
is_enable bool comment '1 启用；0停用',
farm_name varchar(200),
farm_no varchar(100) comment '以全国地理位置编码为前缀进行编码',
farm_province varchar(20),
farm_city varchar(20),
farm_district varchar(20),
farm_address varchar(300),
farm_all_address varchar(500),
farm_long varchar(20),
farm_lat varchar(20),
farm_describe varchar(500),
farm_img_url varchar(300),
farm_auth_img_url varchar(300),
farmer varchar(20) comment '农场联系人',
farm_phone varchar(50) comment '农场联系人电话',
create_date_time bigint,
modified_date_time bigint,
primary key (farm_id)
);
/*==============================================================*/
/* Table: resources */
/*==============================================================*/
create table resources
(
resources_id bigint not null auto_increment,
res_resources_id bigint,
resources_name varchar(100),
resources_type int comment '1 目录；2菜单；3按钮',
resources_mark varchar(100) comment '如：系统管理 01，农场管理0101',
is_enable bool comment '1 启用；0停用',
create_date_time bigint,
modified_date_time bigint,
primary key (resources_id)
);
/*==============================================================*/
/* Table: role */
/*==============================================================*/
create table role
(
role_id bigint not null auto_increment,
farm_id bigint,
role_name varchar(100),
is_enable bool comment '1 启用；0停用',
create_date_time bigint,
modified_date_time bigint,
role_type int comment '1 超级管理员，2管理员；3农场主，4 农户，5农技人员，6第三方游客',
primary key (role_id)
);
alter table role comment '角色';
/*==============================================================*/
/* Index: farm_role_index */
/*==============================================================*/
create index farm_role_index on role
(
farm_id
);
/*==============================================================*/
/* Table: role_resources */
/*==============================================================*/
create table role_resources
(
resources_id bigint not null,
role_id bigint not null,
primary key (resources_id, role_id)
);
/*==============================================================*/
/* Table: user_role */
/*==============================================================*/
create table user_role
(
user_id bigint not null,
role_id bigint not null,
primary key (user_id, role_id)
);
/*==============================================================*/
/* Table: users */
/*==============================================================*/
create table users
(
user_id bigint not null auto_increment,
farm_id bigint,
password varchar(500) comment 'MD5加密；英文，数字，符号',
phone varchar(20) comment '手机作为登录账户',
realname varchar(20),
create_date_time bigint,
is_enable bool default 0 comment '1启用；0停用；用户没有删除，只有启用停用',
avatar varchar(300) comment '头像',
id_card char(18) comment '身份证',
modified_date_time bigint,
primary key (user_id),
unique key AK_phone_uniquekey (phone)
);
/*==============================================================*/
/* Index: farm_user_index */
/*==============================================================*/
create index farm_user_index on users
(
farm_id
);
/*==============================================================*/
/* Table: api_interface */
/*==============================================================*/
create table api_interface
(
ai_id bigint not null auto_increment,
ai_describe varchar(300),
ai_method varchar(20),
ai_url varchar(300),
primary key (ai_id)
);
drop table if exists resource_api;
/*==============================================================*/
/* Table: resource_api */
/*==============================================================*/
create table resource_api
(
resources_id bigint not null,
ai_id bigint not null,
primary key (resources_id, ai_id)
);
alter table resource_api add constraint FK_resource_api foreign key (resources_id)
references resources (resources_id) on delete restrict on update restrict;
alter table resource_api add constraint FK_resource_api2 foreign key (ai_id)
references api_interface (ai_id) on delete restrict on update restrict;
alter table resources add constraint FK_resources_resources foreign key (res_resources_id)
references resources (resources_id) on delete restrict on update restrict;
alter table role add constraint FK_farm_role foreign key (farm_id)
references farm (farm_id) on delete restrict on update restrict;
alter table role_resources add constraint FK_role_resources foreign key (resources_id)
references resources (resources_id) on delete restrict on update restrict;
alter table role_resources add constraint FK_role_resources2 foreign key (role_id)
references role (role_id) on delete restrict on update restrict;
alter table user_role add constraint FK_user_role foreign key (user_id)
references users (user_id) on delete restrict on update restrict;
alter table user_role add constraint FK_user_role2 foreign key (role_id)
references role (role_id) on delete restrict on update restrict;
alter table users add constraint FK_farm_user foreign key (farm_id)
references farm (farm_id) on delete restrict on update restrict;
DROP TABLE IF EXISTS sequence;
CREATE TABLE sequence (
name VARCHAR(50) NOT NULL,
current_value INT NOT NULL,
increment INT NOT NULL DEFAULT 1,
PRIMARY KEY (name)
)
/*
DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION currval (seq_name VARCHAR(50))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE value INTEGER;
SET value = -1;
SELECT current_value INTO value
FROM sequence
WHERE name = seq_name;
RETURN value;
END
$
DELIMITER ;
DROP FUNCTION IF EXISTS nextval;
DELIMITER $
CREATE FUNCTION nextval (seq_name VARCHAR(50))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
IF currval(seq_name)=-1 THEN
INSERT INTO sequence VALUES (seq_name, 0, 1);
END IF;
UPDATE sequence
SET current_value = current_value + increment
WHERE name = seq_name;
RETURN currval(seq_name);
END
$
DELIMITER ;
DROP FUNCTION IF EXISTS setval;
DELIMITER $
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
UPDATE sequence
SET current_value = value
WHERE name = seq_name;
RETURN currval(seq_name);
END
$
DELIMITER ;
*/