ExtAspNet学习-利用AppBox框架快速创建项目(二)-创建Oralce数据库

下边我们首先创建数据库

1、创建数据库

create tablespace test1s datafile 'test1s.dbf' size 100M reuse autoextend on next 50M;

drop user test1 cascade; create user test1 identified by test1 default tablespace test1s;
grant resource,connect,dba to test1;
grant select any table,execute any procedure,create any procedure,create any snapshot,on commit refresh,create any table to test1;

2、创建框架需要的表

CREATE TABLE X_Log(
LogID number(18) primary key,
LogDate date,
THREADInfo varchar2(255),
LogLEVEL varchar2(10),
LogGER varchar2(255),
MESSAGE varchar2(2000),
EXCEPTIONInfo varchar2(2000)
);

CREATE TABLE X_Config(
ConfigId number(3) primary key,
ConfigKey varchar2(50),
ConfigValue varchar2(400),
Remark varchar2(200)
);

CREATE TABLE X_Menu(
MenuId number(3) primary key,
MenuName varchar2(50),
ImageUrl varchar2(200),
NavigateUrl varchar2(200),
IsMainMenu number(1),
Remark varchar2(200),
ParentMenuId number(3),
SortIndex number(3),
CreatedOn date,
CreatedBy varchar2(50),
ModifiedOn date,
ModifiedBy varchar2(50)
);

CREATE TABLE X_JobTitle(
JobTitleId number(9) primary key,
JobTitleName varchar2(50),
Remark varchar2(200),
CreatedOn date,
CreatedBy varchar2(50),
ModifiedOn date,
ModifiedBy varchar2(50)
);

CREATE TABLE X_Dept(
DeptId number(3) primary key,
DeptName varchar2(50),
Remark varchar2(200),
ParentDeptId number(3),
SortIndex number(3),
CreatedOn date,
CreatedBy varchar2(50),
ModifiedOn date,
ModifiedBy varchar2(50)
);

CREATE TABLE X_Role(
RoleId number(3) primary key,
RoleName varchar2(50),
Remark varchar2(200),
CreatedOn date,
CreatedBy varchar2(50),
ModifiedOn date,
ModifiedBy varchar2(50)
);

CREATE TABLE X_User(
UserId number(3) primary key,
UserName varchar2(50) unique,
UserPassword varchar2(50),
IsEnable number(1),
Email varchar2(100),
Gender varchar2(10),
RealName varchar2(50),
CardID varchar2(20),
QQ varchar2(50),
MSN varchar2(100),
CellPhone varchar2(50),
OfficePhone varchar2(50),
HomePhone varchar2(50),
Remark varchar2(200),
DeptId number(3) references X_Dept(DeptID),
RoleId number(3) references X_Role(RoleID),
CreatedOn date,
CreatedBy varchar2(50),
ModifiedOn date,
ModifiedBy varchar2(50)
);

CREATE TABLE X_Online(
OnlineId number(18) primary key,
UserId number(3) references X_User(UserId),
UserName varchar2(50),
UserRealName varchar2(50),
IPAdddress varchar2(50),
LoginTime date,
UpdateTime date
);

commit;

已经测试过了,直接运行即可。

数据初始化

/*数据初始化*/

INSERT INTO X_Config (ConfigId, ConfigKey, ConfigValue, Remark) VALUES(1, 'Title', '企业综合管理系统框架', '网站的标题');
INSERT INTO X_Config (ConfigId, ConfigKey, ConfigValue, Remark) VALUES(5, 'PageSize', '30', '表格数据每页显示的个数');

INSERT INTO X_Dept (DeptId, DeptName, Remark, ParentDeptId, SortIndex, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(1, '房屋测量所', '', 0, 80, NULL, NULL, to_date('2010-07-18','yyyy-mm-dd'), 'admin');

INSERT INTO X_JobTitle (JobTitleId, JobTitleName, Remark, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(1, '主任', NULL, NULL, NULL, NULL, NULL);
INSERT INTO X_JobTitle (JobTitleId, JobTitleName, Remark, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(2, '副主任', NULL, NULL, NULL, NULL, NULL);
INSERT INTO X_JobTitle (JobTitleId, JobTitleName, Remark, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(3, '组长', NULL, NULL, NULL, NULL, NULL);
INSERT INTO X_JobTitle (JobTitleId, JobTitleName, Remark, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(4, '工作人员', NULL, NULL, NULL, to_date('2010-07-18','yyyy-mm-dd'), 'admin');


INSERT INTO X_Log (LOGID, LOGDATE, THREADInfo, LOGLEVEL, LOGGER, MESSAGE, EXCEPTIONInfo)VALUES(13, to_date('2010-07-18','yyyy-mm-dd'), '1', 'INFO', 'AppBox._default', '登录成功:用户“admin”', '');
INSERT INTO X_Log (LOGID, LOGDATE, THREADInfo, LOGLEVEL, LOGGER, MESSAGE, EXCEPTIONInfo)VALUES(14, to_date('2010-07-18','yyyy-mm-dd'), '1', 'INFO', 'AppBox._default', '登录成功:用户“admin”', '');
INSERT INTO X_Log (LOGID, LOGDATE, THREADInfo, LOGLEVEL, LOGGER, MESSAGE, EXCEPTIONInfo)VALUES(15, to_date('2010-07-18','yyyy-mm-dd'), '11', 'INFO', 'AppBox._default', '登录成功:用户“admin”', '');
INSERT INTO X_Log (LOGID, LOGDATE, THREADInfo, LOGLEVEL, LOGGER, MESSAGE, EXCEPTIONInfo)VALUES(16, to_date('2010-07-18','yyyy-mm-dd'), '10', 'INFO', 'AppBox._default', '登录成功:用户“admin”', '');
INSERT INTO X_Log (LOGID, LOGDATE, THREADInfo, LOGLEVEL, LOGGER, MESSAGE, EXCEPTIONInfo)VALUES(17, to_date('2010-07-18','yyyy-mm-dd'), '14', 'INFO', 'AppBox._default', '登录成功:用户“admin”', '');
INSERT INTO X_Log (LOGID, LOGDATE, THREADInfo, LOGLEVEL, LOGGER, MESSAGE, EXCEPTIONInfo)VALUES(18, to_date('2010-07-18','yyyy-mm-dd'), '12', 'INFO', 'AppBox._default', '登录成功:用户“admin”', '');
INSERT INTO X_Log (LOGID, LOGDATE, THREADInfo, LOGLEVEL, LOGGER, MESSAGE, EXCEPTIONInfo)VALUES(19, to_date('2010-07-18','yyyy-mm-dd'), '12', 'INFO', 'AppBox._default', '登录成功:用户“admin”', '');
INSERT INTO X_Log (LOGID, LOGDATE, THREADInfo, LOGLEVEL, LOGGER, MESSAGE, EXCEPTIONInfo)VALUES(20, to_date('2010-07-18','yyyy-mm-dd'), '12', 'INFO', 'AppBox._default', '登录成功:用户“admin”', '');
INSERT INTO X_Log (LOGID, LOGDATE, THREADInfo, LOGLEVEL, LOGGER, MESSAGE, EXCEPTIONInfo)VALUES(21, to_date('2010-07-18','yyyy-mm-dd'), '7', 'INFO', 'AppBox._default', '登录成功:用户“admin”', '');
INSERT INTO X_Log (LOGID, LOGDATE, THREADInfo, LOGLEVEL, LOGGER, MESSAGE, EXCEPTIONInfo)VALUES(22, to_date('2010-07-18','yyyy-mm-dd'), '1', 'INFO', 'AppBox._default', '登录成功:用户“admin”', '');
INSERT INTO X_Log (LOGID, LOGDATE, THREADInfo, LOGLEVEL, LOGGER, MESSAGE, EXCEPTIONInfo)VALUES(23, to_date('2010-07-18','yyyy-mm-dd'), '7', 'INFO', 'AppBox._default', '登录成功:用户“admin”', '');
INSERT INTO X_Log (LOGID, LOGDATE, THREADInfo, LOGLEVEL, LOGGER, MESSAGE, EXCEPTIONInfo)VALUES(24, to_date('2010-07-18','yyyy-mm-dd'), '5', 'INFO', 'AppBox._default', '登录成功:用户“admin”', '');


INSERT INTO X_Menu (MenuId, MenuName, ImageUrl, NavigateUrl, IsMainMenu, Remark, ParentMenuId, SortIndex, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(2, '系统管理', '', '', 1, NULL, 0, 10, NULL, NULL, to_date('2010-07-18','yyyy-mm-dd'), 'admin');
INSERT INTO X_Menu (MenuId, MenuName, ImageUrl, NavigateUrl, IsMainMenu, Remark, ParentMenuId, SortIndex, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(4, '职务管理', '', '~/admin/jobtitle.aspx', 1, NULL, 2, 30, NULL, NULL, to_date('2010-07-18','yyyy-mm-dd'), 'admin');
INSERT INTO X_Menu (MenuId, MenuName, ImageUrl, NavigateUrl, IsMainMenu, Remark, ParentMenuId, SortIndex, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(5, '部门管理', '', '~/admin/dept.aspx', 1, NULL, 2, 40, NULL, NULL, to_date('2010-07-18','yyyy-mm-dd'), 'admin');
INSERT INTO X_Menu (MenuId, MenuName, ImageUrl, NavigateUrl, IsMainMenu, Remark, ParentMenuId, SortIndex, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(6, '菜单管理', '', '~/admin/menu.aspx', 1, NULL, 2, 20, NULL, NULL, to_date('2010-07-18','yyyy-mm-dd'), 'admin');
INSERT INTO X_Menu (MenuId, MenuName, ImageUrl, NavigateUrl, IsMainMenu, Remark, ParentMenuId, SortIndex, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(7, '角色管理', '', '~/admin/role.aspx', 1, NULL, 2, 60, NULL, NULL, to_date('2010-07-18','yyyy-mm-dd'), 'admin');
INSERT INTO X_Menu (MenuId, MenuName, ImageUrl, NavigateUrl, IsMainMenu, Remark, ParentMenuId, SortIndex, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(8, '用户管理', '', '~/admin/user.aspx', 1, NULL, 2, 70, NULL, NULL, to_date('2010-07-18','yyyy-mm-dd'), 'admin');
INSERT INTO X_Menu (MenuId, MenuName, ImageUrl, NavigateUrl, IsMainMenu, Remark, ParentMenuId, SortIndex, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(10, '日志管理', '', '~/admin/log.aspx', 1, NULL, 2, 90, NULL, NULL, NULL, NULL);
INSERT INTO X_Menu (MenuId, MenuName, ImageUrl, NavigateUrl, IsMainMenu, Remark, ParentMenuId, SortIndex, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(11, '业务平台', '', '', 1, NULL, 0, 100, NULL, NULL, to_date('2010-07-18','yyyy-mm-dd'), 'admin');
INSERT INTO X_Menu (MenuId, MenuName, ImageUrl, NavigateUrl, IsMainMenu, Remark, ParentMenuId, SortIndex, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(12, '文件发布', '', '~/admin/file.aspx', 1, NULL, 11, 110, NULL, NULL, to_date('2010-07-18','yyyy-mm-dd'), 'admin');
INSERT INTO X_Menu (MenuId, MenuName, ImageUrl, NavigateUrl, IsMainMenu, Remark, ParentMenuId, SortIndex, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(24, '测试目录', '', '~/admin/test.aspx', 1, NULL, 11, 135, NULL, NULL, NULL, NULL);
INSERT INTO X_Menu (MenuId, MenuName, ImageUrl, NavigateUrl, IsMainMenu, Remark, ParentMenuId, SortIndex, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(25, '测试子目录', '', '~/admin/test2.aspx', 1, NULL, 24, 136, NULL, NULL, NULL, NULL);
INSERT INTO X_Menu (MenuId, MenuName, ImageUrl, NavigateUrl, IsMainMenu, Remark, ParentMenuId, SortIndex, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(29, '系统配置', '', '~/admin/config.aspx', 1, NULL, 2, 91, NULL, NULL, NULL, NULL);
INSERT INTO X_Menu (MenuId, MenuName, ImageUrl, NavigateUrl, IsMainMenu, Remark, ParentMenuId, SortIndex, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(30, '修改密码', '', '~/admin/changepassword.aspx', 1, NULL, 2, 92, to_date('2010-07-18','yyyy-mm-dd'), 'admin', to_date('2010-07-18','yyyy-mm-dd'), 'admin');
INSERT INTO X_Menu (MenuId, MenuName, ImageUrl, NavigateUrl, IsMainMenu, Remark, ParentMenuId, SortIndex, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(34, '在线统计', NULL, '~/admin/online.aspx', 1, '', 2, 93, NULL, NULL, to_date('2010-07-18','yyyy-mm-dd'), 'admin');



INSERT INTO X_Role (RoleId, RoleName, Remark, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(1, '系统管理员', '', NULL, NULL, to_date('2010-07-18','yyyy-mm-dd'), 'admin');
INSERT INTO X_Role (RoleId, RoleName, Remark, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(2, '部门审核', NULL, NULL, NULL, NULL, NULL);
INSERT INTO X_Role (RoleId, RoleName, Remark, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(3, '质量检查', NULL, NULL, NULL, NULL, NULL);
INSERT INTO X_Role (RoleId, RoleName, Remark, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(4, '受理人员', NULL, NULL, NULL, NULL, NULL);

INSERT INTO X_User (UserId, UserName, UserPassword, IsEnable, Email, Gender, RealName, QQ, MSN, CellPhone, OfficePhone, HomePhone, Remark, DeptId, RoleId, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(1, 'admin', '/e0oVLJSOz+iEXnVM0smhzjLOdLuCXTj', 1, 'test1@gmail.com', '', '张三', NULL, NULL, '', '', '', '管理员', NULL, NULL, to_date('2010-07-18','yyyy-mm-dd'), null, to_date('2010-07-18','yyyy-mm-dd'), 'admin');
INSERT INTO X_User (UserId, UserName, UserPassword, IsEnable, Email, Gender, RealName, QQ, MSN, CellPhone, OfficePhone, HomePhone, Remark, DeptId, RoleId, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(2, 'lisi', 'zvNkwj6gA+TUAMoKH/8OhdVyjkxXDFWn', 1, 'test2@gmail.com', '', '李四', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, to_date('2010-07-18','yyyy-mm-dd'), 'admin');
INSERT INTO X_User (UserId, UserName, UserPassword, IsEnable, Email, Gender, RealName, QQ, MSN, CellPhone, OfficePhone, HomePhone, Remark, DeptId, RoleId, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)VALUES(3, 'user1', 'zvNkwj6gA+TUAMoKH/8OhdVyjkxXDFWn', 1, 'test3@gmail.com', '', '王二, '', '', '', '', '', '', NULL, NULL, NULL, NULL, NULL, NULL);

INSERT INTO X_Online (OnlineId, UserId, UserName, UserRealName, IPAdddress, LoginTime, UpdateTime)VALUES(1, 3, 'admin', '系统管理员', '127.0.0.1', to_date('2010-07-18','yyyy-mm-dd'), to_date('2010-07-18','yyyy-mm-dd'));

 

 

转载于:https://www.cnblogs.com/rongyi/archive/2012/03/06/2381626.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值