system.sql
5.79 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
/*==============================================================*/
/* 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;
/*==============================================================*/
/* 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),
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_url varchar(300),
request_method varchar(20) comment 'post；get；put；delete；',
is_enable bool comment '1 启用；0停用',
create_date_time bigint,
modified_date_time bigint,
type_order int comment '类别排序号',
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
);
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;