oracle.sql

其主要的功能就是,进行后台管理模块整合. 1)其可以支持多个应用,多个模块的权限管理. 2)支持Access和MsSql200/2005. 3)采用角色来进行权限的授权,每个用户可以属于多个角色

--Create By Supesoft.com 2008/2/15
--edit by supesoft.com 2008/3/23 增加在线用户表

--创建序列Start
create sequence SEQ_SYS_APPLICATIONS_ID increment by 1 start with 100 maxvalue 999999999;
create sequence SEQ_SYS_EVENT_ID increment by 1 start with 100 maxvalue 999999999;
create sequence SEQ_SYS_FIELD_ID increment by 1 start with 100 maxvalue 999999999;
create sequence SEQ_SYS_FIELDVALUE_ID increment by 1 start with 100 maxvalue 999999999;
create sequence SEQ_SYS_GROUP_ID increment by 1 start with 100 maxvalue 999999999;
create sequence SEQ_SYS_MODULE_ID increment by 1 start with 100 maxvalue 999999999;
create sequence SEQ_SYS_ROLEAPPLICATION_ID increment by 1 start with 100 maxvalue 999999999;
create sequence SEQ_SYS_ROLEPERMISSION_ID increment by 1 start with 100 maxvalue 999999999;
create sequence SEQ_SYS_ROLES_ID increment by 1 start with 100 maxvalue 999999999;
create sequence SEQ_SYS_SYSTEMINFO_ID increment by 1 start with 100 maxvalue 999999999;
create sequence SEQ_SYS_USER_ID increment by 1 start with 100 maxvalue 999999999;
create sequence SEQ_SYS_USERROLES_ID increment by 1 start with 100 maxvalue 999999999;
CREATE SEQUENCE SEQ_SYS_ONLINE_ID  increment by 1 start with 100 maxvalue 999999999;
--创建序列End
--创建表结构Start
  CREATE TABLE "SYS_APPLICATIONS" 
   (	"APPLICATIONID" NUMBER(10,0) NOT NULL ENABLE, 
	"A_APPNAME" VARCHAR2(50 BYTE), 
	"A_APPDESCRIPTION" VARCHAR2(200 BYTE), 
	"A_APPURL" VARCHAR2(50 BYTE), 
	 CONSTRAINT "SYS_APPLICATIONS_PK" PRIMARY KEY ("APPLICATIONID")
   );

  CREATE TABLE "SYS_EVENT" 
   (	"EVENTID" NUMBER(10,0) NOT NULL ENABLE, 
	"E_U_LOGINNAME" VARCHAR2(20 BYTE), 
	"E_USERID" NUMBER(10,0), 
	"E_DATETIME" DATE NOT NULL ENABLE, 
	"E_APPLICATIONID" NUMBER(10,0), 
	"E_A_APPNAME" VARCHAR2(50 BYTE), 
	"E_M_NAME" VARCHAR2(50 BYTE), 
	"E_M_PAGECODE" VARCHAR2(6 BYTE), 
	"E_FROM" VARCHAR2(500 BYTE), 
	"E_TYPE" NUMBER(3,0) NOT NULL ENABLE, 
	"E_IP" VARCHAR2(15 BYTE), 
	"E_RECORD" VARCHAR2(500 BYTE), 
	 CONSTRAINT "SYS_EVENT_PK" PRIMARY KEY ("EVENTID")
	);

 CREATE TABLE "SYS_FIELD" 
   (	"FIELDID" NUMBER(10,0) NOT NULL ENABLE, 
	"F_KEY" VARCHAR2(50 BYTE), 
	"F_CNAME" VARCHAR2(50 BYTE), 
	"F_REMARK" VARCHAR2(200 BYTE), 
	 CONSTRAINT "SYS_FIELD_PK" PRIMARY KEY ("FIELDID")
	);

  CREATE TABLE "SYS_FIELDVALUE" 
   (	"VALUEID" NUMBER(10,0) NOT NULL ENABLE, 
	"V_F_KEY" VARCHAR2(50 BYTE), 
	"V_TEXT" VARCHAR2(100 BYTE), 
	"V_SHOWORDER" NUMBER(10,0) NOT NULL ENABLE, 
	 CONSTRAINT "SYS_FIELDVALUE_PK" PRIMARY KEY ("VALUEID")
	);

  CREATE TABLE "SYS_GROUP" 
   (	"GROUPID" NUMBER(10,0) NOT NULL ENABLE, 
	"G_CNAME" VARCHAR2(50 BYTE), 
	"G_PARENTID" NUMBER(10,0) NOT NULL ENABLE, 
	"G_SHOWORDER" NUMBER(10,0) NOT NULL ENABLE, 
	"G_LEVEL" NUMBER(10,0), 
	"G_CHILDCOUNT" NUMBER(10,0), 
	"G_DELETE" NUMBER(3,0), 
	 CONSTRAINT "SYS_GROUP_PK" PRIMARY KEY ("GROUPID")
	);

  CREATE TABLE "SYS_MODULE" 
   (	"MODULEID" NUMBER(10,0) NOT NULL ENABLE, 
	"M_APPLICATIONID" NUMBER(10,0) NOT NULL ENABLE, 
	"M_PARENTID" NUMBER(10,0) NOT NULL ENABLE, 
	"M_PAGECODE" VARCHAR2(6 BYTE) NOT NULL ENABLE, 
	"M_CNAME" VARCHAR2(50 BYTE), 
	"M_DIRECTORY" VARCHAR2(255 BYTE), 
	"M_ORDERLEVEL" VARCHAR2(4 BYTE), 
	"M_ISSYSTEM" NUMBER(3,0), 
	"M_CLOSE" NUMBER(3,0), 
	 CONSTRAINT "SYS_MODULE_PK" PRIMARY KEY ("M_APPLICATIONID", "M_PAGECODE")
	);

  CREATE TABLE "SYS_ROLEAPPLICATION" 
   (	"A_ROLEID" NUMBER(10,0) NOT NULL ENABLE, 
	"A_APPLICATIONID" NUMBER(10,0) NOT NULL ENABLE, 
	 CONSTRAINT "SYS_ROLEAPPLICATION_PK" PRIMARY KEY ("A_ROLEID", "A_APPLICATIONID")
	);

  CREATE TABLE "SYS_ROLEPERMISSION" 
   (	"PERMISSIONID" NUMBER(10,0) NOT NULL ENABLE, 
	"P_ROLEID" NUMBER(10,0) NOT NULL ENABLE, 
	"P_APPLICATIONID" NUMBER(10,0) NOT NULL ENABLE, 
	"P_PAGECODE" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	"P_VALUE" NUMBER(10,0), 
	 CONSTRAINT "SYS_ROLEPERMISSION_PK" PRIMARY KEY ("P_ROLEID", "P_APPLICATIONID", "P_PAGECODE")
	);

  CREATE TABLE "SYS_ROLES" 
   (	"ROLEID" NUMBER(10,0) NOT NULL ENABLE, 
	"R_ROLENAME" VARCHAR2(50 BYTE), 
	"R_DESCRIPTION" VARCHAR2(255 BYTE), 
	 CONSTRAINT "SYS_ROLES_PK" PRIMARY KEY ("ROLEID")
	);
  CREATE TABLE "SYS_SYSTEMINFO" 
   (	"SYSTEMID" NUMBER(10,0) NOT NULL ENABLE, 
	"S_NAME" VARCHAR2(50 BYTE), 
	"S_VERSION" VARCHAR2(50 BYTE), 
	"S_SYSTEMCONFIGDATA" LONG RAW, 
	"S_LICENSED" VARCHAR2(50 BYTE), 
	 CONSTRAINT "SYS_SYSTEMINFO_PK" PRIMARY KEY ("SYSTEMID")
	);
  CREATE TABLE "SYS_USER" 
   (	"USERID" NUMBER(10,0) NOT NULL ENABLE, 
	"U_LOGINNAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	"U_PASSWORD" VARCHAR2(32 BYTE) NOT NULL ENABLE, 
	"U_CNAME" VARCHAR2(20 BYTE), 
	"U_ENAME" VARCHAR2(50 BYTE), 
	"U_GROUPID" NUMBER(10,0) NOT NULL ENABLE, 
	"U_EMAIL" VARCHAR2(100 BYTE), 
	"U_TYPE" NUMBER(3,0) NOT NULL ENABLE, 
	"U_STATUS" NUMBER(3,0) NOT NULL ENABLE, 
	"U_LICENCE" VARCHAR2(30 BYTE), 
	"U_MAC" VARCHAR2(50 BYTE), 
	"U_REMARK" VARCHAR2(200 BYTE), 
	"U_IDCARD" VARCHAR2(30 BYTE), 
	"U_SEX" NUMBER(3,0), 
	"U_BIRTHDAY" DATE, 
	"U_MOBILENO" VARCHAR2(15 BYTE), 
	"U_USERNO" VARCHAR2(20 BYTE), 
	"U_WORKSTARTDATE" DATE, 
	"U_WORKENDDATE" DATE, 
	"U_COMPANYMAIL" VARCHAR2(255 BYTE), 
	"U_TITLE" NUMBER(10,0), 
	"U_EXTENSION" VARCHAR2(10 BYTE), 
	"U_HOMETEL" VARCHAR2(20 BYTE), 
	"U_PHOTOURL" VARCHAR2(255 BYTE), 
	"U_DATETIME" DATE, 
	"U_LASTIP" VARCHAR2(15 BYTE), 
	"U_LASTDATETIME" DATE, 
	"U_EXTENDFIELD" LONG, 
	 CONSTRAINT "SYS_USER_PK" PRIMARY KEY ("USERID")
	);
  CREATE TABLE "SYS_USERROLES" 
   (	"R_USERID" NUMBER(10,0) NOT NULL ENABLE, 
	"R_ROLEID" NUMBER(10,0) NOT NULL ENABLE, 
	 CONSTRAINT "SYS_USERROLES_PK" PRIMARY KEY ("R_USERID", "R_ROLEID")
	);

  CREATE TABLE "SYS_ONLINE" 
   (	"ONLINEID" NUMBER(10,0) NOT NULL ENABLE, 
	"O_SESSIONID" VARCHAR2(24 BYTE) NOT NULL ENABLE, 
	"O_USERNAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	"O_IP" VARCHAR2(15 BYTE) NOT NULL ENABLE, 
	"O_LOGINTIME" DATE NOT NULL ENABLE, 
	"O_LASTTIME" DATE NOT NULL ENABLE, 
	"O_LASTURL" VARCHAR2(500 BYTE) NOT NULL ENABLE, 
	 CONSTRAINT "SYS_ONLINE_PK" PRIMARY KEY ("O_SESSIONID")
   );
 

--创建表结构End

--插入初始化数据Start
-- INSERTING into SYS_APPLICATIONS
Insert into SYS_APPLICATIONS (APPLICATIONID,A_APPNAME,A_APPDESCRIPTION,A_APPURL) values (1,'基础模组','基础模组成部分','http://framework.web');
-- INSERTING into SYS_FIELD
Insert into SYS_FIELD (FIELDID,F_KEY,F_CNAME,F_REMARK) values (2,'Title','职称','用户职称列表');
-- INSERTING into SYS_FIELDVALUE
Insert into SYS_FIELDVALUE (VALUEID,V_F_KEY,V_TEXT,V_SHOWORDER) values (5,'title','普通员工',5);
Insert into SYS_FIELDVALUE (VALUEID,V_F_KEY,V_TEXT,V_SHOWORDER) values (16,'Title','职业员工',4);
Insert into SYS_FIELDVALUE (VALUEID,V_F_KEY,V_TEXT,V_SHOWORDER) values (17,'Title','高级程序员',3);
Insert into SYS_FIELDVALUE (VALUEID,V_F_KEY,V_TEXT,V_SHOWORDER) values (18,'Title','试用期员工',1);
Insert into SYS_FIELDVALUE (VALUEID,V_F_KEY,V_TEXT,V_SHOWORDER) values (19,'Title','经理员工',2);
-- INSERTING into SYS_MODULE
Insert into SYS_MODULE (MODULEID,M_APPLICATIONID,M_PARENTID,M_PAGECODE,M_CNAME,M_DIRECTORY,M_ORDERLEVEL,M_ISSYSTEM,M_CLOSE) values (1,1,0,'S00','系统应用',null,'0000',1,0);
Insert into SYS_MODULE (MODULEID,M_APPLICATIONID,M_PARENTID,M_PAGECODE,M_CNAME,M_DIRECTORY,M_ORDERLEVEL,M_ISSYSTEM,M_CLOSE) values (2,1,1,'S00M00','应用列表管理','Module/FrameWork/SystemApp/AppManager/list.aspx','0001',1,0);
Insert into SYS_MODULE (MODULEID,M_APPLICATIONID,M_PARENTID,M_PAGECODE,M_CNAME,M_DIRECTORY,M_ORDERLEVEL,M_ISSYSTEM,M_CLOSE) values (4,1,1,'S00M01','部门资料管理','Module/FrameWork/SystemApp/GroupManager/Frame.aspx','0003',1,0);
Insert into SYS_MODULE (MODULEID,M_APPLICATIONID,M_PARENTID,M_PAGECODE,M_CNAME,M_DIRECTORY,M_ORDERLEVEL,M_ISSYSTEM,M_CLOSE) values (5,1,1,'S00M02','角色资料管理','Module/FrameWork/SystemApp/RoleManager/RoleList.aspx','0004',1,0);
Insert into SYS_MODULE (MODULEID,M_APPLICATIONID,M_PARENTID,M_PAGECODE,M_CNAME,M_DIRECTORY,M_ORDERLEVEL,M_ISSYSTEM,M_CLOSE) values (6,1,1,'S00M03','用户资料管理','Module/FrameWork/SystemApp/UserManager/default.aspx','0005',1,0);
Insert into SYS_MODULE (MODULEID,M_APPLICATIONID,M_PARENTID,M_PAGECODE,M_CNAME,M_DIRECTORY,M_ORDERLEVEL,M_ISSYSTEM,M_CLOSE) values (7,1,1,'S00M04','应用字段设定','Module/FrameWork/SystemApp/FieldManager/default.aspx','0006',1,0);
Insert into SYS_MODULE (MODULEID,M_APPLICATIONID,M_PARENTID,M_PAGECODE,M_CNAME,M_DIRECTORY,M_ORDERLEVEL,M_ISSYSTEM,M_CLOSE) values (8,1,1,'S00M05','事件日志管理','Module/FrameWork/SystemApp/EventManager/default.aspx','0007',1,0);
Insert into SYS_MODULE (MODULEID,M_APPLICATIONID,M_PARENTID,M_PAGECODE,M_CNAME,M_DIRECTORY,M_ORDERLEVEL,M_ISSYSTEM,M_CLOSE) values (9,1,1,'S00M06','在线用户列表','Module/FrameWork/SystemApp/OnlineUserManager/default.aspx','0008',1,0);
Insert into SYS_MODULE (MODULEID,M_APPLICATIONID,M_PARENTID,M_PAGECODE,M_CNAME,M_DIRECTORY,M_ORDERLEVEL,M_ISSYSTEM,M_CLOSE) values (26,1,1,'S00M07','应用模块管理','Module/FrameWork/SystemApp/ModuleManager/list.aspx','0002',1,0);
Insert into SYS_MODULE (MODULEID,M_APPLICATIONID,M_PARENTID,M_PAGECODE,M_CNAME,M_DIRECTORY,M_ORDERLEVEL,M_ISSYSTEM,M_CLOSE) values (27,1,0,'S01','系统维护',null,'0100',1,0);
Insert into SYS_MODULE (MODULEID,M_APPLICATIONID,M_PARENTID,M_PAGECODE,M_CNAME,M_DIRECTORY,M_ORDERLEVEL,M_ISSYSTEM,M_CLOSE) values (28,1,27,'S01M00','系统运行状态','Module/FrameWork/SystemMaintenance/SystemState/default.aspx','0101',1,0);
Insert into SYS_MODULE (MODULEID,M_APPLICATIONID,M_PARENTID,M_PAGECODE,M_CNAME,M_DIRECTORY,M_ORDERLEVEL,M_ISSYSTEM,M_CLOSE) values (29,1,27,'S01M01','系统出错日志','Module/FrameWork/SystemMaintenance/SystemErrorLog/default.aspx','0102',1,0);
Insert into SYS_MODULE (MODULEID,M_APPLICATIONID,M_PARENTID,M_PAGECODE,M_CNAME,M_DIRECTORY,M_ORDERLEVEL,M_ISSYSTEM,M_CLOSE) values (30,1,27,'S01M02','系统环境配置','Module/FrameWork/SystemMaintenance/SystemConfig/default.aspx','0103',1,0);
-- INSERTING into SYS_USER
Insert into SYS_USER (USERID,U_LOGINNAME,U_PASSWORD,U_CNAME,U_ENAME,U_GROUPID,U_EMAIL,U_TYPE,U_STATUS,U_LICENCE,U_MAC,U_REMARK,U_IDCARD,U_SEX,U_BIRTHDAY,U_MOBILENO,U_USERNO,U_WORKSTARTDATE,U_WORKENDDATE,U_COMPANYMAIL,U_TITLE,U_EXTENSION,U_HOMETEL,U_PHOTOURL,U_DATETIME,U_LASTIP,U_LASTDATETIME,U_EXTENDFIELD) values (1,'admin','21232F297A57A5A743894A0E4A801FC3','管理员',null,1,null,0,0,null,null,null,null,0,to_date('23-6月 -07','DD-MON-RR'),null,null,to_date('23-6月 -07','DD-MON-RR'),to_date('23-6月 -07','DD-MON-RR'),null,17,null,null,'20080207070532eeeb.jpg',to_date('23-6月 -07','DD-MON-RR'),'127.0.0.1',to_date('10-2月 -08','DD-MON-RR'),'2,20,blue');

--插入初始化数据End