（4）—- 数据库表结构的创建

PD建模图  建模语句 ~~~ alter table SYS_AUTHORITIES_RESOURCES drop constraint FK_SYS_AUTH_REFERENCE_SYS_AUTH; alter table SYS_AUTHORITIES_RESOURCES drop constraint FK_SYS_AUTH_REFERENCE_SYS_RESO; alter table SYS_RESOURCES drop constraint FK_SYS_RESO_REFERENCE_SYS_MODU; alter table SYS_ROLES_AUTHORITIES drop constraint FK_SYS_ROLE_REFERENCE_SYS_ROLE; alter table SYS_ROLES_AUTHORITIES drop constraint FK_SYS_ROLE_REFERENCE_SYS_AUTH; alter table SYS_ROLES_MOUDLES drop constraint FK_SYS_ROLE_REFERENCE_SYS_MODU; alter table SYS_ROLES_MOUDLES drop constraint FK_S_ROLE_REFERENCE_SYS_ROLE; alter table SYS_USERS_ROLES drop constraint FK_SYS_USER_REFERENCE_SYS_USER; alter table SYS_USERS_ROLES drop constraint FK_SYS_USER_REFERENCE_SYS_ROLE; drop table PERSISTENT_LOGINS cascade constraints; drop table SYS_AUTHORITIES cascade constraints; drop table SYS_AUTHORITIES_RESOURCES cascade constraints; drop table SYS_MODULES cascade constraints; drop table SYS_RESOURCES cascade constraints; drop table SYS_ROLES cascade constraints; drop table SYS_ROLES_AUTHORITIES cascade constraints; drop table SYS_ROLES_MOUDLES cascade constraints; drop table SYS_USERS cascade constraints; drop table SYS_USERS_ROLES cascade constraints; /*==============================================================*/ /* Table: PERSISTENT_LOGINS */ /*==============================================================*/ create table PERSISTENT_LOGINS ( USERNAME VARCHAR2(64), SERIES VARCHAR2(64) not null, TOKEN VARCHAR2(64), LAST_USED TIMESTAMP, constraint PK_PERSISTENT_LOGINS primary key (SERIES) ); comment on table PERSISTENT_LOGINS is 'Spring Remember me 持久化'; /*==============================================================*/ /* Table: SYS_AUTHORITIES */ /*==============================================================*/ create table SYS_AUTHORITIES ( AUTHORITY_ID VARCHAR2(100) not null, AUTHORITY_MARK VARCHAR2(100), AUTHORITY_NAME VARCHAR2(100) not null, AUTHORITY_DESC VARCHAR2(200), MESSAGE VARCHAR2(100), ENABLE NUMBER, ISSYS NUMBER, MODULE_ID VARCHAR2(100), constraint PK_SYS_AUTHORITIES primary key (AUTHORITY_ID) ); /*==============================================================*/ /* Table: SYS_AUTHORITIES_RESOURCES */ /*==============================================================*/ create table SYS_AUTHORITIES_RESOURCES ( ID VARCHAR2(100) not null, RESOURCE_ID VARCHAR2(100) not null, AUTHORITY_ID VARCHAR2(100) not null, constraint PK_SYS_AUTHORITIES_RESOURCES primary key (ID) ); /*==============================================================*/ /* Table: SYS_MODULES */ /*==============================================================*/ create table SYS_MODULES ( MODULE_ID VARCHAR2(100) not null, MODULE_NAME VARCHAR2(100) not null, MODULE_DESC VARCHAR2(200), MODULE_TYPE VARCHAR2(100), PARENT VARCHAR2(100), MODULE_URL VARCHAR2(100), I_LEVEL NUMBER, LEAF NUMBER, APPLICATION VARCHAR2(100), CONTROLLER VARCHAR2(100), ENABLE NUMBER(1), PRIORITY NUMBER, constraint PK_SYS_MODULES primary key (MODULE_ID) ); comment on column SYS_MODULES.I_LEVEL is '1'; /*==============================================================*/ /* Table: SYS_RESOURCES */ /*==============================================================*/ create table SYS_RESOURCES ( RESOURCE_ID VARCHAR2(100) not null, RESOURCE_TYPE VARCHAR2(100), RESOURCE_NAME VARCHAR2(100), RESOURCE_DESC VARCHAR2(200), RESOURCE_PATH VARCHAR2(200), PRIORITY VARCHAR2(100), ENABLE NUMBER, ISSYS NUMBER, MODULE_ID VARCHAR2(100), constraint PK_SYS_RESOURCES primary key (RESOURCE_ID) ); comment on column SYS_RESOURCES.RESOURCE_TYPE is 'URL,METHOD'; /*==============================================================*/ /* Table: SYS_ROLES */ /*==============================================================*/ create table SYS_ROLES ( ROLE_ID VARCHAR2(100) not null, ROLE_NAME VARCHAR2(100), ROLE_DESC VARCHAR2(200), ENABLE NUMBER, ISSYS NUMBER, MODULE_ID VARCHAR2(100), constraint PK_SYS_ROLES primary key (ROLE_ID) ); /*==============================================================*/ /* Table: SYS_ROLES_AUTHORITIES */ /*==============================================================*/ create table SYS_ROLES_AUTHORITIES ( ID VARCHAR2(100) not null, AUTHORITY_ID VARCHAR2(100) not null, ROLE_ID VARCHAR2(100) not null, constraint PK_SYS_ROLES_AUTHORITIES primary key (ID) ); /*==============================================================*/ /* Table: SYS_ROLES_MOUDLES */ /*==============================================================*/ create table SYS_ROLES_MOUDLES ( ID VARCHAR2(100) not null, MODULE_ID VARCHAR2(100) not null, ROLE_ID VARCHAR2(100) not null, constraint PK_SYS_ROLES_MOUDLES primary key (ID) ); comment on table SYS_ROLES_MOUDLES is '控制角色对模块的访问权，主要用于生成菜单'; /*==============================================================*/ /* Table: SYS_USERS */ /*==============================================================*/ create table SYS_USERS ( USER_ID VARCHAR2(100) not null, USERNAME VARCHAR2(100) not null, NAME VARCHAR2(100), PASSWORD VARCHAR2(100) not null, DT_CREATE DATE default SYSDATE, LAST_LOGIN DATE, DEADLINE DATE, LOGIN_IP VARCHAR2(100), V_QZJGID VARCHAR2(100), V_QZJGMC VARCHAR2(100), DEP_ID VARCHAR2(100), DEP_NAME VARCHAR2(100), ENABLED NUMBER, ACCOUNT_NON_EXPIRED NUMBER, ACCOUNT_NON_LOCKED NUMBER, CREDENTIALS_NON_EXPIRED NUMBER, constraint PK_SYS_USERS primary key (USER_ID) ); /*==============================================================*/ /* Table: SYS_USERS_ROLES */ /*==============================================================*/ create table SYS_USERS_ROLES ( ID VARCHAR2(100) not null, ROLE_ID VARCHAR2(100) not null, USER_ID VARCHAR2(100) not null, constraint PK_SYS_USERS_ROLES primary key (ID) ); alter table SYS_AUTHORITIES_RESOURCES add constraint FK_SYS_AUTH_REFERENCE_SYS_AUTH foreign key (AUTHORITY_ID) references SYS_AUTHORITIES (AUTHORITY_ID); alter table SYS_AUTHORITIES_RESOURCES add constraint FK_SYS_AUTH_REFERENCE_SYS_RESO foreign key (RESOURCE_ID) references SYS_RESOURCES (RESOURCE_ID); alter table SYS_RESOURCES add constraint FK_SYS_RESO_REFERENCE_SYS_MODU foreign key (MODULE_ID) references SYS_MODULES (MODULE_ID); alter table SYS_ROLES_AUTHORITIES add constraint FK_SYS_ROLE_REFERENCE_SYS_ROLE foreign key (ROLE_ID) references SYS_ROLES (ROLE_ID); alter table SYS_ROLES_AUTHORITIES add constraint FK_SYS_ROLE_REFERENCE_SYS_AUTH foreign key (AUTHORITY_ID) references SYS_AUTHORITIES (AUTHORITY_ID); alter table SYS_ROLES_MOUDLES add constraint FK_SYS_ROLE_REFERENCE_SYS_MODU foreign key (MODULE_ID) references SYS_MODULES (MODULE_ID); alter table SYS_ROLES_MOUDLES add constraint FK_S_ROLE_REFERENCE_SYS_ROLE foreign key (ROLE_ID) references SYS_ROLES (ROLE_ID); alter table SYS_USERS_ROLES add constraint FK_SYS_USER_REFERENCE_SYS_USER foreign key (USER_ID) references SYS_USERS (USER_ID); alter table SYS_USERS_ROLES add constraint FK_SYS_USER_REFERENCE_SYS_ROLE foreign key (ROLE_ID) references SYS_ROLES (ROLE_ID); ~~~ 这些都是在后面讲解的过程中要用到的表 [PD建模下载](http://download.csdn.net/detail/jaune161/6854357)