OpenJWeb快速开发平台数据库设计(一)(Oracle)

迷途d书童 发布于 2012/03/09 12:19
阅读 353
收藏 0

【DevOps必读】产品经理与程序员之间如何破局?>>>

/** TABLE_NO:0010
  * TABLE_NAME:序列号信息表(COMM_CONFIG)
  * TABLE DESC:为保证序列号生成与数据库无关,本系统单独建表存储不同序列号的当前值。本表只存储一条记录
  * AUTHOR:王保政
  * DATE:2008-12-18
**/

CREATE TABLE COMM_CONFIG   -- 序列号信息表
(
ROW_ID VARCHAR2(40) NOT NULL ENABLE PRIMARY KEY, --记录唯一ID,此ID值在记录生成后不做修改
NEXT_SEQ NUMBER DEFAULT 1 NOT NULL           --下一序列值
);

/** TABLE_NO:0020
  * TABLE_NAME:子系统基本信息表(BASE_SUB_SYSTEM)
  * TABLE DESC:定义子系统的基本信息<本表不设计多语字段>
  * AUTHOR:王保政
  * DATE:2008-12-20
**/
 
 
CREATE TABLE COMM_SUB_SYSTEM
(
ROW_ID   VARCHAR2(40) NOT NULL ENABLE PRIMARY KEY,
SYS_CODE VARCHAR2(10) NOT NULL ENABLE UNIQUE,  --子系统代号(如CRM表示客户关系管理系统,HR表示人力资源系统)
SYS_NM   VARCHAR2(40) NOT NULL ENABLE,  --子系统名称
SYS_DESC VARCHAR2(100) ,--子系统说明
IS_WEB_APP CHAR(1) DEFAULT '1',--是否WEB应用 ,默认为是(1) ,本系统对是和否的定义按1/0区分
CONTEXT_ROOT VARCHAR2(30) ,-- WEB应用根目录的名称
JSP_FILE_PATH VARCHAR2(100), -- 使用平台生成JSP代码时,存储JSP文件的路径
JAVA_FILE_PATH VARCHAR2(100),--使用平台生成代码时,存储JAVA文件的路径
ENITY_PACKAGE_NM VARCHAR2(100), --使用平台生成代码时,实体类的包名。
XML_FILE_PATH VARCHAR2(100) ,--使用平台生成代码时,生成的XML配置文件的包路径名。
TEMPLATE_PATH VARCHAR2(100) ,--使用平台生成代码时,使用的JSP模板文件的路径
CREATE_DT VARCHAR2(23),      --记录创建时间,格式(带格式的年月日时分秒毫秒):2008-12-20 14:13 59 000
UPDATE_DT VARCHAR2(23),      --记录最后修改时间(当创建记录时,最后修改时间和记录创建时间是一样的)
CREATE_UID VARCHAR2(40),     --创建者身份标识(登录人帐号)
UPDATE_UID VARCHAR2(40),     --修改者身份标识(登录人帐号)
SORT_NO  NUMBER,             --记录顺序号,如果需要自定义记录显示顺序,可使用此字段
DATA_FLG CHAR(1)            --是否使用(0:停用 ,1使用)  
);

/** TABLE_NO:0030
  * TABLE_NAME:登录帐号基本信息表(COMM_USER)
  * 设计思路:可作为统一认证的数据源,此表只存储认证必须的最少信息,主要是身份(帐号)、凭证(口令)
  * 及帐号是否可用的信息,更详细的信息可另建子表;对于用户的分类,有企业内部员工,详细信息可与HR的
  * 人员信息共用,对于网上会员,可另建数据库表存储会员基本信息,等等。这些按不同分类的用户表可和本表建立
  * 主外键约束,此表作为主键表。
  * 本表需要在实际应用中不断完善。
  * TABLE DESC:登录帐号表
  * AUTHOR:王保政
  * DATE:2008-12-26
**/
 
CREATE TABLE COMM_USER
(
USER_ID NUMBER PRIMARY KEY,                    --其他表有存储登录帐号字段时,使用本字段值存储,优点是用户可随意修改自己的登录帐号
LOGIN_ID VARCHAR2(30) NOT NULL ENABLE UNIQUE ,  --登录帐号,在登录页面上使用的帐号,建立唯一约束,使登录帐号不能重复
EMP_NO VARCHAR2(10) ,                         --如果为企业内部用户,此字段可对应HR系统的员工工号,在应用系统中也可以此字段作为登录帐号
USERNAME VARCHAR2(60) NOT NULL ENABLE ,       --登录后在页面上显示的名字,是否为真实姓名还是昵称由具体应用确定
PASSWORD VARCHAR2(60) NOT NULL ENABLE,        --登录用户的口令,为安全起见不允许为空,一般应存储加密的口令
USER_EMAIL VARCHAR2(30) ,                     --用户联系邮箱,主要用于系统自动发邮件使用
USER_TEL VARCHAR2(30) ,                       --用户联系电话
USER_MOBILE VARCHAR2(20),                     --用户手机
USER_TITLE VARCHAR2(20),                      --用户的称谓,具体由应用系统确定,例如先生/女士/局长/...
USER_TYPE VARCHAR2(16) ,                      --用户类型:企业内部用户,网上会员等,具体含义由应用系统确定,对应编码从数据字典表获取
IS_ACCT_NON_EXPIRED CHAR(1) DEFAULT 1,     --帐号是否未过期:1/0:未过期/过期
IS_ACCT_NON_LOCKED CHAR(1) DEFAULT 1,      --帐号是否未锁定1/0:未锁定/锁定
IS_PWD_NON_EXPIRED CHAR(1) DEFAULT 1, --口令是否未过期 1/0:未过期/过期
IS_IN_USE CHAR(1) DEFAULT 1,                 --帐号是否可用信息1/0:可用/不可用
ROW_ID   VARCHAR2(40) NOT NULL ENABLE ,       --唯一行号(UUID)
CREATE_DT VARCHAR2(23),      --记录创建时间,格式(带格式的年月日时分秒毫秒):2008-12-20 14:13 59 000
UPDATE_DT VARCHAR2(23),      --记录最后修改时间(当创建记录时,最后修改时间和记录创建时间是一样的)
CREATE_UID VARCHAR2(40),     --创建者身份标识(登录人帐号)
UPDATE_UID VARCHAR2(40),     --修改者身份标识(登录人帐号)
SORT_NO  NUMBER             --记录顺序号,如果需要自定义记录显示顺序,可使用此字段
);

CREATE INDEX IDX1_COMM_USER ON COMM_USER(USERNAME) ;--TABLESPACE ERP; 因名称经常作为查询条件,所以建立索引
--DROP  INDEX  IDX1_COMM_USER ;

/** TABLE_NO:0040
  * TABLE_NAME:角色信息表(COMM_ROLES)
  * TABLE DESC:角色是与系统有关的,比如财务系统中的角色和销售系统中的角色定义是不同的。
  * 因为系统实现有可能是按树结构录入角色,所以设计了若干树结构相关字段
  * AUTHOR:王保政
  * DATE:2008-12-27
**/
 
CREATE TABLE COMM_ROLES
(
ROLE_ID NUMBER PRIMARY KEY ,            --角色ID
ROLE_NAME VARCHAR2(40) NOT NULL ENABLE, --角色名称
---- 以下是树结构相关字段  ------------
CLS_CODE VARCHAR2(10) ,                 --子系统编号,角色一般与系统相关
TREE_CODE VARCHAR2(100) UNIQUE,         --树节点编码,一般由系统生成(如不使用树结构,则可不使用)
P_TREE_CODE VARCHAR2(100) ,             --上级节点编码 (如不使用树结构,则可不使用)
LVL_NUM NUMBER ,                        --树节点所属层次(如不使用树结构,则可不使用)                                
IS_LEAF CHAR(1),                        --是否末级节点(如不使用树结构,则可不使用)
COMM_CODE VARCHAR2(16) UNIQUE,          --角色编码(企业按照自己的规则设置的角色编码)
NODE_NAME VARCHAR2(80),                 --节点名称(对于树型结构,可能显示一级->二级->三级)
NODE_DESC VARCHAR2(255),                --角色说明
----- 以上是树结构相关字段  -----------
ROW_ID   VARCHAR2(40) NOT NULL ENABLE ,       
CREATE_DT VARCHAR2(23),     
UPDATE_DT VARCHAR2(23),      
CREATE_UID VARCHAR2(40),    
UPDATE_UID VARCHAR2(40),    
SORT_NO  NUMBER ,
DATA_FLG CHAR(1) DEFAULT 1 NOT NULL ENABLE           --是否使用(0:停用 ,1使用)               
);

CREATE INDEX IDX1_COMM_ROLES ON COMM_ROLES(ROLE_NAME) ;

/** TABLE_NO:0050
  * TABLE_NAME:权限定义表(COMM_AUTH)
  * TABLE DESC:权限定义
  * 设计说明:(1)在系统实现中作为Spring Security框架读取的资源;
  *           (2)权限是与子系统相关的,所以不同的子系统使用不同的权限集合。
  *           (3)权限是树型结构,例如如果具有页面上按钮的权限,则应该也有访问页面的权限
  *           (4) 在系统实现上,权限既可直接授权给用户,也可按用户-角色-权限模型设计,视应用需求而定
  *           (5) 本表的权限定义不涉及到ACL,ACL权限具有单独的实现框架。
  *           (6) 本表的权限定义可控制的资源包括:URL,WEB应用具体的目录,功能菜单树,页面按钮及页面其他元素
  *           (7) 使用Spring Security框架的权限标签来控制页面元素,本表是权限标签读取的资源。
  * AUTHOR:王保政
  * DATE:2008-12-27
**/
 
CREATE TABLE COMM_AUTH
(
AUTH_ID NUMBER PRIMARY KEY,             --权限唯一ID
AUTH_NAME VARCHAR2(40) NOT NULL ENABLE, --权限名称
AUTH_TYPE VARCHAR2(16) ,                --权限类型,如菜单、按钮,功能模块,URL权限等,从数据字典获取权限编码
AUTH_RESOURCE VARCHAR2(255) DEFAULT '/*' NOT NULL ENABLE , --权限对应的资源
---- 以下是树结构相关字段  ------------
COMM_CODE VARCHAR2(30) UNIQUE NOT NULL ENABLE,          --权限编码(企业按照自己的规则设置的权限编码,也可由系统根据规则自动生成)
CLS_CODE VARCHAR2(10) ,                 --子系统编号
TREE_CODE VARCHAR2(100) UNIQUE,         --树节点编码,一般由系统生成
P_TREE_CODE VARCHAR2(100) ,             --上级节点编码 
LVL_NUM NUMBER ,                        --树节点所属层次                            
IS_LEAF CHAR(1),                        --是否末级节点
NODE_NAME VARCHAR2(80),                 --节点名称(默认取AUTH_NAME)
NODE_DESC VARCHAR2(255),                --权限说明
----- 以上是树结构相关字段  -----------
ROW_ID   VARCHAR2(40) NOT NULL ENABLE ,       
CREATE_DT VARCHAR2(23),     
UPDATE_DT VARCHAR2(23),      
CREATE_UID VARCHAR2(40),    
UPDATE_UID VARCHAR2(40),    
SORT_NO  NUMBER ,        --在权限表中,需要按照此字段排序              
DATA_FLG CHAR(1) DEFAULT 1 NOT NULL ENABLE           --是否使用(0:停用 ,1使用) 
);

CREATE INDEX IDX1_COMM_AUTH ON COMM_AUTH(AUTH_NAME) ;

/** TABLE_NO:0060
  * TABLE_NAME:用户角色关系表(COMM_USER_ROLE)
  * TABLE DESC:定义用户和角色的关系
  * AUTHOR:王保政
  * DATE:2008-12-27
**/

CREATE TABLE COMM_USER_ROLE
(
SERIAL_NO NUMBER PRIMARY KEY ,
USERID NUMBER,          --主键对应COMM_USER.USER_ID,暂不设置外键约束,根据项目需要设置是否增加级联删除的外键约束
ROLE_ID NUMBER,          --主键对应COMM_ROLES.ROLE_ID,暂不设置外键约束,根据项目需要设置是否增加级联删除的外键约束
CREATE_DT VARCHAR2(23),  --对于关系表而言,不需要修改,所以不设计修改日期和修改人字段    
CREATE_UID VARCHAR2(40)
);

/** TABLE_NO:0070
  * TABLE_NAME:角色权限关系表(COMM_ROLE_AUTH)
  * TABLE DESC:定义角色和权限的关系
  * AUTHOR:王保政
  * DATE:2008-12-27
**/

CREATE TABLE COMM_ROLE_AUTH
(
SERIAL_NO NUMBER PRIMARY KEY ,
AUTH_ID NUMBER,          --主键对应COMM_USER.USER_ID,暂不设置外键约束,根据项目需要设置是否增加级联删除的外键约束
ROLE_ID NUMBER,          --主键对应COMM_ROLES.ROLE_ID,暂不设置外键约束,根据项目需要设置是否增加级联删除的外键约束
CREATE_DT VARCHAR2(23),  --对于关系表而言,不需要修改,所以不设计修改日期和修改人字段    
CREATE_UID VARCHAR2(40)
);

/** TABLE_NO:0080
  * TABLE_NAME:用户权限关系表(COMM_USER_AUTH)
  * TABLE DESC:定义用户和权限的关系
  * 设计说明:对于特定情况下为个别用户授予单独的权限,作为用户-角色-权限模型的例外情况。这在实际应用是很常见的。
  * AUTHOR:王保政
  * DATE:2008-12-27
**/

CREATE TABLE COMM_USER_AUTH
(
SERIAL_NO NUMBER PRIMARY KEY ,
USER_ID NUMBER,          --主键对应COMM_USER.USER_ID,暂不设置外键约束,根据项目需要设置是否增加级联删除的外键约束
AUTH_ID NUMBER,          --主键对应COMM_USER.USER_ID,暂不设置外键约束,根据项目需要设置是否增加级联删除的外键约束
CREATE_DT VARCHAR2(23),  --对于关系表而言,不需要修改,所以不设计修改日期和修改人字段    
CREATE_UID VARCHAR2(40)
);

---下面创建一个用户权限关系的视图,合并了用户-权限,及用户-角色-权限
create view v_user_auth
as
select a.user_id,a.login_id,b.comm_code from
comm_user a,comm_auth b ,comm_user_auth c
where c.user_id = a.user_id
and c.auth_id = b.auth_id
union  
select a.user_id,a.login_id,b.comm_code from
comm_user a,comm_auth b , comm_user_role d,comm_role_auth e
where a.user_id = d.user_id
and d.role_id = e.role_id
and e.auth_id = b.auth_id;


/** TABLE_NO:0090
  * TABLE_NAME:语种代码表(COMM_LOCALE)
  * TABLE DESC:语种代码定义(区分国家和地区)
  * 设计说明:因此表记录很少,可直接在初始化过程中一次性导入,不提供维护功能
  * AUTHOR:王保政
  * DATE:2008-12-28
**/

CREATE TABLE COMM_LOCALE
(
LOCALE_ID VARCHAR2(10) PRIMARY KEY ,       --语种代码,格式:语言代码(小写)-国家代码(大写),如zh-CN,en-US
LOCALE_NAME VARCHAR2(30) NOT NULL ENABLE,  --语种名称(如简体中文,英文(美国))
LOCALE_EN_NAME VARCHAR2(30),          --语种名称英文
IS_IN_USE CHAR(1) DEFAULT 1          --是否使用1/0(使用/不使用)
);

/** TABLE_NO:0100
  * TABLE_NAME:功能菜单树(COMM_MENU)
  * TABLE DESC:功能菜单树
  * 设计说明:因考虑到多语,所以功能菜单树设计语种字段,系统维护时,除功能名不同,其他字段修改应保持多语种的同步,
  * 系统中也可设计一个方法实现语种之间记录复制(多语字段,时间戳字段不复制),开发时考虑如何给出一个通用的实现方式。
  * AUTHOR:王保政
  * DATE:2008-12-28
**/
 
CREATE TABLE COMM_MENU
(
SERIAL_NO NUMBER PRIMARY KEY,
MENU_NAME VARCHAR2(80) NOT NULL ENABLE,             --菜单文字(在界面上显示的功能菜单文字)
MENU_URL VARCHAR2(200) DEFAULT '#' NOT NULL ,       --菜单对应的URL,如果每个菜单需生成对应权限ID,则与权限表对应权限的auth_resource一致。
MENU_AUTH_ID NUMBER,                                --菜单对应的权限,新建菜单可自动往COMM_AUTH表插入记录。此字段对应COMM_AUTH.AUTH_ID
---- 以下是树结构相关字段  ------------
COMM_CODE VARCHAR2(30) UNIQUE NOT NULL ENABLE,      --菜单编号,例如CRM0101<编码规则可以为:子系统编号+树节点编码>
CLS_CODE VARCHAR2(10) ,                 --子菜单所属的系统编号
TREE_CODE VARCHAR2(100) UNIQUE,         --树节点编码,一般由系统生成
P_TREE_CODE VARCHAR2(100) ,             --上级节点编码 
LVL_NUM NUMBER ,                        --树节点所属层次                            
IS_LEAF CHAR(1),                        --是否末级节点
NODE_NAME VARCHAR2(80),                 --节点名称
NODE_DESC VARCHAR2(255),                --
----- 以上是树结构相关字段  -----------
ROW_ID   VARCHAR2(40) NOT NULL ENABLE ,       
CREATE_DT VARCHAR2(23),     
UPDATE_DT VARCHAR2(23),      
CREATE_UID VARCHAR2(40),    
UPDATE_UID VARCHAR2(40),    
SORT_NO  NUMBER ,                        --利用此字段调整菜单显示顺序
DATA_FLG CHAR(1) DEFAULT 1 NOT NULL ENABLE           --是否使用(0:停用 ,1使用) 

);

ALTER TABLE COMM_MENU ADD  CONSTRAINT FK1_COMM_MENU  FOREIGN KEY (MENU_AUTH_ID) REFERENCES COMM_AUTH;
CREATE INDEX IDX1_COMM_MENU ON COMM_MENU(MENU_NAME) ;
 
--初始化

DELETE FROM  COMM_CONFIG;

INSERT INTO COMM_CONFIG(ROW_ID,NEXT_SEQ) VALUES('10',1);

--初始化子系统信息表
INSERT INTO COMM_SUB_SYSTEM(ROW_ID,SYS_CODE,SYS_NM) VALUES('1','COMM','基础平台'); --基础平台
INSERT INTO COMM_SUB_SYSTEM(ROW_ID,SYS_CODE,SYS_NM) VALUES('2','CRM','客户关系管理(医院版)'); --CRM


-- 初始化两个用户,口令为maximo,md5加密
INSERT INTO COMM_USER(USER_ID,LOGIN_ID,USERNAME,PASSWORD,ROW_ID)
VALUES(1,'admin','超级管理员','d822eb65cbd84945f427dc08f4571694','f7d6872cfa32415894d70a41d04066ed');
INSERT INTO COMM_USER(USER_ID, LOGIN_ID,USERNAME,PASSWORD,ROW_ID)
VALUES(2,'openjweb','一般用户','d822eb65cbd84945f427dc08f4571694','f68a284e7f8b4c31a12eb3f34bc68187');
--INSERT INTO COMM_USER(USER_ID, LOGIN_ID,USERNAME,PASSWORD,ROW_ID)
--VALUES(3,'roleAnonymous','匿名用户','d822eb65cbd84945f427dc08f4571694','f68a284e7f8b4c31a12eb3f34bc68187');
 
 
--初始化两个角色
INSERT INTO COMM_ROLES(ROLE_ID,ROLE_NAME,ROW_ID) VALUES(1,'超级用户','1');
INSERT INTO COMM_ROLES(ROLE_ID,ROLE_NAME,ROW_ID) VALUES(2,'一般用户','2');
INSERT INTO COMM_ROLES(ROLE_ID,ROLE_NAME,ROW_ID) VALUES(3,'CRM用户','3');
 
--初始化权限表
 
--INSERT INTO COMM_AUTH(AUTH_ID,COMM_CODE,AUTH_NAME,AUTH_RESOURCE,ROW_ID) VALUES(9999,'IS_AUTHENTICATED_ANONYMOUSLY','匿名权限','/**','1'); 
INSERT INTO COMM_AUTH(AUTH_ID,COMM_CODE,AUTH_NAME,AUTH_RESOURCE,ROW_ID) VALUES(2,'AUTH_BASIC','匿名权限1','/login1.jsp','1'); 
INSERT INTO COMM_AUTH(AUTH_ID,COMM_CODE,AUTH_NAME,AUTH_RESOURCE,ROW_ID) VALUES(11,'AUTH_TEST','测试页面访问权限','/test/*','1');
 
--初始化用户权限关系表
 
INSERT INTO COMM_USER_AUTH(SERIAL_NO,USER_ID,AUTH_ID) VALUES(1,1,1); --为超级用户授予权限1
INSERT INTO COMM_USER_AUTH(SERIAL_NO,USER_ID,AUTH_ID) VALUES(2,1,2); --为超级用户授予权限2
INSERT INTO COMM_USER_AUTH(SERIAL_NO,USER_ID,AUTH_ID) VALUES(3,2,1); --为一般拥护授予权限1

--初始化语种名称

INSERT INTO COMM_LOCALE(LOCALE_ID,LOCALE_NAME,IS_IN_USE) VALUES('zh-CN','简体中文',1);
INSERT INTO COMM_LOCALE(LOCALE_ID,LOCALE_NAME,IS_IN_USE) VALUES('zh-TW','繁体中文(台湾)',0);
INSERT INTO COMM_LOCALE(LOCALE_ID,LOCALE_NAME,IS_IN_USE) VALUES('en-GB','英语(英国)',0);
INSERT INTO COMM_LOCALE(LOCALE_ID,LOCALE_NAME,IS_IN_USE) VALUES('en-US','英语(美国)',1);
INSERT INTO COMM_LOCALE(LOCALE_ID,LOCALE_NAME,IS_IN_USE) VALUES('ja-JP','日语',0);
INSERT INTO COMM_LOCALE(LOCALE_ID,LOCALE_NAME,IS_IN_USE) VALUES('ko-KR','韩语',0);

 

 

 


 
 


原文链接:http://blog.csdn.net/baozhengw/article/details/3628688
加载中
返回顶部
顶部