PD建模图
建模语句
1. alter table SYS_AUTHORITIES_RESOURCES
2. drop constraint FK_SYS_AUTH_REFERENCE_SYS_AUTH;
3.
4. alter table SYS_AUTHORITIES_RESOURCES
5. drop constraint FK_SYS_AUTH_REFERENCE_SYS_RESO;
6.
7. alter table SYS_RESOURCES
8. drop constraint FK_SYS_RESO_REFERENCE_SYS_MODU;
9.
10. alter table SYS_ROLES_AUTHORITIES
11. drop constraint FK_SYS_ROLE_REFERENCE_SYS_ROLE;
12.
13. alter table SYS_ROLES_AUTHORITIES
14. drop constraint FK_SYS_ROLE_REFERENCE_SYS_AUTH;
15.
16. alter table SYS_ROLES_MOUDLES
17. drop constraint FK_SYS_ROLE_REFERENCE_SYS_MODU;
18.
19. alter table SYS_ROLES_MOUDLES
20. drop constraint FK_S_ROLE_REFERENCE_SYS_ROLE;
21.
22. alter table SYS_USERS_ROLES
23. drop constraint FK_SYS_USER_REFERENCE_SYS_USER;
24.
25. alter table SYS_USERS_ROLES
26. drop constraint FK_SYS_USER_REFERENCE_SYS_ROLE;
27.
28. drop table PERSISTENT_LOGINS cascade constraints;
29.
30. drop table SYS_AUTHORITIES cascade constraints;
31.
32. drop table SYS_AUTHORITIES_RESOURCES cascade constraints;
33.
34. drop table SYS_MODULES cascade constraints;
35.
36. drop table SYS_RESOURCES cascade constraints;
37.
38. drop table SYS_ROLES cascade constraints;
39.
40. drop table SYS_ROLES_AUTHORITIES cascade constraints;
41.
42. drop table SYS_ROLES_MOUDLES cascade constraints;
43.
44. drop table SYS_USERS cascade constraints;
45.
46. drop table SYS_USERS_ROLES cascade constraints;
47.
48. /*==============================================================*/
49. /* Table: PERSISTENT_LOGINS */
50. /*==============================================================*/
51. create table PERSISTENT_LOGINS (
52. USERNAME VARCHAR2(64),
53. not null,
54. TOKEN VARCHAR2(64),
55. TIMESTAMP,
56. constraint PK_PERSISTENT_LOGINS primary key (SERIES)
57. );
58.
59. comment on table PERSISTENT_LOGINS is
60. 'Spring Remember me 持久化';
61.
62. /*==============================================================*/
63. /* Table: SYS_AUTHORITIES */
64. /*==============================================================*/
65. create table SYS_AUTHORITIES (
66. not null,
67. AUTHORITY_MARK VARCHAR2(100),
68. not null,
69. AUTHORITY_DESC VARCHAR2(200),
70. MESSAGE VARCHAR2(100),
71. ENABLE NUMBER,
72. ISSYS NUMBER,
73. MODULE_ID VARCHAR2(100),
74. constraint PK_SYS_AUTHORITIES primary key (AUTHORITY_ID)
75. );
76.
77. /*==============================================================*/
78. /* Table: SYS_AUTHORITIES_RESOURCES */
79. /*==============================================================*/
80. create table SYS_AUTHORITIES_RESOURCES (
81. not null,
82. not null,
83. not null,
84. constraint PK_SYS_AUTHORITIES_RESOURCES primary key (ID)
85. );
86.
87. /*==============================================================*/
88. /* Table: SYS_MODULES */
89. /*==============================================================*/
90. create table SYS_MODULES (
91. not null,
92. not null,
93. MODULE_DESC VARCHAR2(200),
94. MODULE_TYPE VARCHAR2(100),
95. PARENT VARCHAR2(100),
96. MODULE_URL VARCHAR2(100),
97. I_LEVEL NUMBER,
98. LEAF NUMBER,
99. APPLICATION VARCHAR2(100),
100. CONTROLLER VARCHAR2(100),
101. ENABLE NUMBER(1),
102. PRIORITY NUMBER,
103. constraint PK_SYS_MODULES primary key (MODULE_ID)
104. );
105.
106. comment on column SYS_MODULES.I_LEVEL is
107. '1';
108.
109. /*==============================================================*/
110. /* Table: SYS_RESOURCES */
111. /*==============================================================*/
112. create table SYS_RESOURCES (
113. not null,
114. RESOURCE_TYPE VARCHAR2(100),
115. RESOURCE_NAME VARCHAR2(100),
116. RESOURCE_DESC VARCHAR2(200),
117. RESOURCE_PATH VARCHAR2(200),
118. PRIORITY VARCHAR2(100),
119. ENABLE NUMBER,
120. ISSYS NUMBER,
121. MODULE_ID VARCHAR2(100),
122. constraint PK_SYS_RESOURCES primary key (RESOURCE_ID)
123. );
124.
125. comment on column SYS_RESOURCES.RESOURCE_TYPE is
126. 'URL,METHOD';
127.
128. /*==============================================================*/
129. /* Table: SYS_ROLES */
130. /*==============================================================*/
131. create table SYS_ROLES (
132. not null,
133. ROLE_NAME VARCHAR2(100),
134. ROLE_DESC VARCHAR2(200),
135. ENABLE NUMBER,
136. ISSYS NUMBER,
137. MODULE_ID VARCHAR2(100),
138. constraint PK_SYS_ROLES primary key (ROLE_ID)
139. );
140.
141. /*==============================================================*/
142. /* Table: SYS_ROLES_AUTHORITIES */
143. /*==============================================================*/
144. create table SYS_ROLES_AUTHORITIES (
145. not null,
146. not null,
147. not null,
148. constraint PK_SYS_ROLES_AUTHORITIES primary key (ID)
149. );
150.
151. /*==============================================================*/
152. /* Table: SYS_ROLES_MOUDLES */
153. /*==============================================================*/
154. create table SYS_ROLES_MOUDLES (
155. not null,
156. not null,
157. not null,
158. constraint PK_SYS_ROLES_MOUDLES primary key (ID)
159. );
160.
161. comment on table SYS_ROLES_MOUDLES is
162. '控制角色对模块的访问权，主要用于生成菜单';
163.
164. /*==============================================================*/
165. /* Table: SYS_USERS */
166. /*==============================================================*/
167. create table SYS_USERS (
168. not null,
169. not null,
170. NAME VARCHAR2(100),
171. PASSWORD VARCHAR2(100) not null,
172. DATE default SYSDATE,
173. DATE,
174. DATE,
175. LOGIN_IP VARCHAR2(100),
176. V_QZJGID VARCHAR2(100),
177. V_QZJGMC VARCHAR2(100),
178. DEP_ID VARCHAR2(100),
179. DEP_NAME VARCHAR2(100),
180. ENABLED NUMBER,
181. ACCOUNT_NON_EXPIRED NUMBER,
182. ACCOUNT_NON_LOCKED NUMBER,
183. CREDENTIALS_NON_EXPIRED NUMBER,
184. constraint PK_SYS_USERS primary key (USER_ID)
185. );
186.
187. /*==============================================================*/
188. /* Table: SYS_USERS_ROLES */
189. /*==============================================================*/
190. create table SYS_USERS_ROLES (
191. not null,
192. not null,
193. not null,
194. constraint PK_SYS_USERS_ROLES primary key (ID)
195. );
196.
197. alter table SYS_AUTHORITIES_RESOURCES
198. add constraint FK_SYS_AUTH_REFERENCE_SYS_AUTH foreign key (AUTHORITY_ID)
199. references SYS_AUTHORITIES (AUTHORITY_ID);
200.
201. alter table SYS_AUTHORITIES_RESOURCES
202. add constraint FK_SYS_AUTH_REFERENCE_SYS_RESO foreign key (RESOURCE_ID)
203. references SYS_RESOURCES (RESOURCE_ID);
204.
205. alter table SYS_RESOURCES
206. add constraint FK_SYS_RESO_REFERENCE_SYS_MODU foreign key (MODULE_ID)
207. references SYS_MODULES (MODULE_ID);
208.
209. alter table SYS_ROLES_AUTHORITIES
210. add constraint FK_SYS_ROLE_REFERENCE_SYS_ROLE foreign key (ROLE_ID)
211. references SYS_ROLES (ROLE_ID);
212.
213. alter table SYS_ROLES_AUTHORITIES
214. add constraint FK_SYS_ROLE_REFERENCE_SYS_AUTH foreign key (AUTHORITY_ID)
215. references SYS_AUTHORITIES (AUTHORITY_ID);
216.
217. alter table SYS_ROLES_MOUDLES
218. add constraint FK_SYS_ROLE_REFERENCE_SYS_MODU foreign key (MODULE_ID)
219. references SYS_MODULES (MODULE_ID);
220.
221. alter table SYS_ROLES_MOUDLES
222. add constraint FK_S_ROLE_REFERENCE_SYS_ROLE foreign key (ROLE_ID)
223. references SYS_ROLES (ROLE_ID);
224.
225. alter table SYS_USERS_ROLES
226. add constraint FK_SYS_USER_REFERENCE_SYS_USER foreign key (USER_ID)
227. references SYS_USERS (USER_ID);
228.
229. alter table SYS_USERS_ROLES
230. add constraint FK_SYS_USER_REFERENCE_SYS_ROLE foreign key (ROLE_ID)
231. references SYS_ROLES (ROLE_ID);
这些都是在后面讲解的过程中要用到的表
PD建模图
建模语句
1. alter table SYS_AUTHORITIES_RESOURCES
2. drop constraint FK_SYS_AUTH_REFERENCE_SYS_AUTH;
3.
4. alter table SYS_AUTHORITIES_RESOURCES
5. drop constraint FK_SYS_AUTH_REFERENCE_SYS_RESO;
6.
7. alter table SYS_RESOURCES
8. drop constraint FK_SYS_RESO_REFERENCE_SYS_MODU;
9.
10. alter table SYS_ROLES_AUTHORITIES
11. drop constraint FK_SYS_ROLE_REFERENCE_SYS_ROLE;
12.
13. alter table SYS_ROLES_AUTHORITIES
14. drop constraint FK_SYS_ROLE_REFERENCE_SYS_AUTH;
15.
16. alter table SYS_ROLES_MOUDLES
17. drop constraint FK_SYS_ROLE_REFERENCE_SYS_MODU;
18.
19. alter table SYS_ROLES_MOUDLES
20. drop constraint FK_S_ROLE_REFERENCE_SYS_ROLE;
21.
22. alter table SYS_USERS_ROLES
23. drop constraint FK_SYS_USER_REFERENCE_SYS_USER;
24.
25. alter table SYS_USERS_ROLES
26. drop constraint FK_SYS_USER_REFERENCE_SYS_ROLE;
27.
28. drop table PERSISTENT_LOGINS cascade constraints;
29.
30. drop table SYS_AUTHORITIES cascade constraints;
31.
32. drop table SYS_AUTHORITIES_RESOURCES cascade constraints;
33.
34. drop table SYS_MODULES cascade constraints;
35.
36. drop table SYS_RESOURCES cascade constraints;
37.
38. drop table SYS_ROLES cascade constraints;
39.
40. drop table SYS_ROLES_AUTHORITIES cascade constraints;
41.
42. drop table SYS_ROLES_MOUDLES cascade constraints;
43.
44. drop table SYS_USERS cascade constraints;
45.
46. drop table SYS_USERS_ROLES cascade constraints;
47.
48. /*==============================================================*/
49. /* Table: PERSISTENT_LOGINS */
50. /*==============================================================*/
51. create table PERSISTENT_LOGINS (
52. USERNAME VARCHAR2(64),
53. not null,
54. TOKEN VARCHAR2(64),
55. TIMESTAMP,
56. constraint PK_PERSISTENT_LOGINS primary key (SERIES)
57. );
58.
59. comment on table PERSISTENT_LOGINS is
60. 'Spring Remember me 持久化';
61.
62. /*==============================================================*/
63. /* Table: SYS_AUTHORITIES */
64. /*==============================================================*/
65. create table SYS_AUTHORITIES (
66. not null,
67. AUTHORITY_MARK VARCHAR2(100),
68. not null,
69. AUTHORITY_DESC VARCHAR2(200),
70. MESSAGE VARCHAR2(100),
71. ENABLE NUMBER,
72. ISSYS NUMBER,
73. MODULE_ID VARCHAR2(100),
74. constraint PK_SYS_AUTHORITIES primary key (AUTHORITY_ID)
75. );
76.
77. /*==============================================================*/
78. /* Table: SYS_AUTHORITIES_RESOURCES */
79. /*==============================================================*/
80. create table SYS_AUTHORITIES_RESOURCES (
81. not null,
82. not null,
83. not null,
84. constraint PK_SYS_AUTHORITIES_RESOURCES primary key (ID)
85. );
86.
87. /*==============================================================*/
88. /* Table: SYS_MODULES */
89. /*==============================================================*/
90. create table SYS_MODULES (
91. not null,
92. not null,
93. MODULE_DESC VARCHAR2(200),
94. MODULE_TYPE VARCHAR2(100),
95. PARENT VARCHAR2(100),
96. MODULE_URL VARCHAR2(100),
97. I_LEVEL NUMBER,
98. LEAF NUMBER,
99. APPLICATION VARCHAR2(100),
100. CONTROLLER VARCHAR2(100),
101. ENABLE NUMBER(1),
102. PRIORITY NUMBER,
103. constraint PK_SYS_MODULES primary key (MODULE_ID)
104. );
105.
106. comment on column SYS_MODULES.I_LEVEL is
107. '1';
108.
109. /*==============================================================*/
110. /* Table: SYS_RESOURCES */
111. /*==============================================================*/
112. create table SYS_RESOURCES (
113. not null,
114. RESOURCE_TYPE VARCHAR2(100),
115. RESOURCE_NAME VARCHAR2(100),
116. RESOURCE_DESC VARCHAR2(200),
117. RESOURCE_PATH VARCHAR2(200),
118. PRIORITY VARCHAR2(100),
119. ENABLE NUMBER,
120. ISSYS NUMBER,
121. MODULE_ID VARCHAR2(100),
122. constraint PK_SYS_RESOURCES primary key (RESOURCE_ID)
123. );
124.
125. comment on column SYS_RESOURCES.RESOURCE_TYPE is
126. 'URL,METHOD';
127.
128. /*==============================================================*/
129. /* Table: SYS_ROLES */
130. /*==============================================================*/
131. create table SYS_ROLES (
132. not null,
133. ROLE_NAME VARCHAR2(100),
134. ROLE_DESC VARCHAR2(200),
135. ENABLE NUMBER,
136. ISSYS NUMBER,
137. MODULE_ID VARCHAR2(100),
138. constraint PK_SYS_ROLES primary key (ROLE_ID)
139. );
140.
141. /*==============================================================*/
142. /* Table: SYS_ROLES_AUTHORITIES */
143. /*==============================================================*/
144. create table SYS_ROLES_AUTHORITIES (
145. not null,
146. not null,
147. not null,
148. constraint PK_SYS_ROLES_AUTHORITIES primary key (ID)
149. );
150.
151. /*==============================================================*/
152. /* Table: SYS_ROLES_MOUDLES */
153. /*==============================================================*/
154. create table SYS_ROLES_MOUDLES (
155. not null,
156. not null,
157. not null,
158. constraint PK_SYS_ROLES_MOUDLES primary key (ID)
159. );
160.
161. comment on table SYS_ROLES_MOUDLES is
162. '控制角色对模块的访问权，主要用于生成菜单';
163.
164. /*==============================================================*/
165. /* Table: SYS_USERS */
166. /*==============================================================*/
167. create table SYS_USERS (
168. not null,
169. not null,
170. NAME VARCHAR2(100),
171. PASSWORD VARCHAR2(100) not null,
172. DATE default SYSDATE,
173. DATE,
174. DATE,
175. LOGIN_IP VARCHAR2(100),
176. V_QZJGID VARCHAR2(100),
177. V_QZJGMC VARCHAR2(100),
178. DEP_ID VARCHAR2(100),
179. DEP_NAME VARCHAR2(100),
180. ENABLED NUMBER,
181. ACCOUNT_NON_EXPIRED NUMBER,
182. ACCOUNT_NON_LOCKED NUMBER,
183. CREDENTIALS_NON_EXPIRED NUMBER,
184. constraint PK_SYS_USERS primary key (USER_ID)
185. );
186.
187. /*==============================================================*/
188. /* Table: SYS_USERS_ROLES */
189. /*==============================================================*/
190. create table SYS_USERS_ROLES (
191. not null,
192. not null,
193. not null,
194. constraint PK_SYS_USERS_ROLES primary key (ID)
195. );
196.
197. alter table SYS_AUTHORITIES_RESOURCES
198. add constraint FK_SYS_AUTH_REFERENCE_SYS_AUTH foreign key (AUTHORITY_ID)
199. references SYS_AUTHORITIES (AUTHORITY_ID);
200.
201. alter table SYS_AUTHORITIES_RESOURCES
202. add constraint FK_SYS_AUTH_REFERENCE_SYS_RESO foreign key (RESOURCE_ID)
203. references SYS_RESOURCES (RESOURCE_ID);
204.
205. alter table SYS_RESOURCES
206. add constraint FK_SYS_RESO_REFERENCE_SYS_MODU foreign key (MODULE_ID)
207. references SYS_MODULES (MODULE_ID);
208.
209. alter table SYS_ROLES_AUTHORITIES
210. add constraint FK_SYS_ROLE_REFERENCE_SYS_ROLE foreign key (ROLE_ID)
211. references SYS_ROLES (ROLE_ID);
212.
213. alter table SYS_ROLES_AUTHORITIES
214. add constraint FK_SYS_ROLE_REFERENCE_SYS_AUTH foreign key (AUTHORITY_ID)
215. references SYS_AUTHORITIES (AUTHORITY_ID);
216.
217. alter table SYS_ROLES_MOUDLES
218. add constraint FK_SYS_ROLE_REFERENCE_SYS_MODU foreign key (MODULE_ID)
219. references SYS_MODULES (MODULE_ID);
220.
221. alter table SYS_ROLES_MOUDLES
222. add constraint FK_S_ROLE_REFERENCE_SYS_ROLE foreign key (ROLE_ID)
223. references SYS_ROLES (ROLE_ID);
224.
225. alter table SYS_USERS_ROLES
226. add constraint FK_SYS_USER_REFERENCE_SYS_USER foreign key (USER_ID)
227. references SYS_USERS (USER_ID);
228.
229. alter table SYS_USERS_ROLES
230. add constraint FK_SYS_USER_REFERENCE_SYS_ROLE foreign key (ROLE_ID)
231. references SYS_ROLES (ROLE_ID);
这些都是在后面讲解的过程中要用到的表
