oracletables.sql

本书主要介绍了从基础开始建立Portal的快速应用开发过程（RAD）的方法。通过本书你可以了解到关于Oracle 9iAS Portal产品的主要特性、入口的开发和组织、网络开发和管理、实际的关系型数

drop sequence newsnewsid;
drop sequence baseactact_id;
drop sequence baseclassclass_id;
drop sequence basespecspec_id;
drop sequence usersuser_id;
drop sequence groupsgroup_id;
drop sequence operateloglog_id;
drop sequence topictopicid;

drop table news;
drop table baseact;
drop table baseclass;
drop table basespec;
drop table users;
drop table groups;
drop table operatelog;
drop table topic;

create sequence newsnewsid increment by 1 start with 1 maxvalue 9999999 cycle nocache ;
create table news (
newsid number(7)  primary key,
newsname varchar(100),
upname varchar(100),
downname1 varchar(100),
downname2 varchar(100),
newswriter varchar(20),
newsgiver varchar(20),
newscomper varchar(20),
newstranser varchar(20),
newsdate varchar(8),
column1 integer,
column2 integer,
column3 integer,
newstext clob,
newssource varchar(100),
newssourcestr varchar(1000),
pic1 varchar(100) default 0,
pic2 varchar(100) default 0,
pic3 varchar(100) default 0,
pic4 varchar(100) default 0,
pic5 varchar(100) default 0,
uploader char(10),
uploadertime char(14),
del_statue number(1) default 0,
pub_statue number(1) default 0,
clas_statue number(1) default 0,
newslink varchar(300) null,
modle varchar(40),
modle1 varchar(40),
modle2 varchar(40),
newslength integer,
newsclick integer,
guide_default integer default 250,
guide_minister integer default 250,
guide_media integer default 250,
guide_recommend integer default 250,
guide_column integer default 250
);
create table baseact(
act_id 	integer primary key not null,
act_name char(20),
act_description varchar2(200)
);
create sequence baseactact_id increment by 1 start with 1 maxvalue 999 cycle nocache ;

create table baseclass(
class_id integer primary key not null,
class_name char(30),
class_father integer,
class_layer integer,
class_description varchar2(200),
imgfilepath varchar2(80),
class_date date,
tabindex integer,
visible integer
);
create sequence baseclassclass_id increment by 1 start with 1 maxvalue 999 cycle nocache ;

create table basespec(
spec_id integer  primary key not null,
spec_name varchar(30),
spec_father integer,
spec_layer integer,
spec_description varchar2(200),
imgfilepath varchar2(80),
spec_date date,
tabindex integer,
tabindex_all integer,
visible integer
);
create sequence basespecspec_id increment by 1 start with 1 maxvalue 9999999 cycle nocache ;

create table users(
user_id integer  primary key not null,
real_name char(20),
login_name char(20),
login_password char(40),
email char(20),
right_level  char(20),
group_id char(10),
class_right varchar2(200),
special_right varchar2(200),
operate_right varchar(200),
user_description varchar(200)
);
create sequence usersuser_id increment by 1 start with 1 maxvalue 9999 cycle nocache ;

create table groups(
group_id  integer  primary key not null,
group_name char(30),
group_head char(20),
class_right varchar(200), 
special_right varchar(200), 
operate_right varchar(200), 
group_description varchar(200)
);
create sequence groupsgroup_id increment by 1 start with 1 maxvalue 9999 cycle nocache ;

create table operatelog(
log_id  integer  primary key not null,
user_name char(20) ,
group_name char(20),
news_id integer,
news_title varchar2(200),
act_type char(40),
act_time char(25)
);
create sequence operateloglog_id increment by 1 start with 1 maxvalue 9999999 cycle nocache ;

create table topic(
topicid integer primary key,
newsid integer,
spec_id integer,
uploader varchar2(30),
datechar varchar2(30),
tabindex integer,
model varchar2(40)
);
create sequence topictopicid increment by 1 start with 1 maxvalue 9999999 cycle nocache ;

insert into baseact values(baseactact_id.nextval,'新闻管理','');
insert into baseact values(baseactact_id.nextval,'栏目管理','');
insert into baseact values(baseactact_id.nextval,'专题管理','');
insert into baseact values(baseactact_id.nextval,'用户管理','');
insert into baseact values(baseactact_id.nextval,'日志管理','');

insert into basespec(spec_id,spec_name,spec_layer,spec_father) values(basespecspec_id.nextval,'基础专题',1,0);
insert into basespec(spec_id,spec_name,spec_father,spec_layer,visible) values(basespecspec_id.nextval,'2005法网',0,1,1);
insert into basespec(spec_id,spec_name,spec_father,spec_layer,visible) values(basespecspec_id.nextval,'2005法网子专题一',1,2,1);
insert into basespec(spec_id,spec_name,spec_father,spec_layer,visible) values(basespecspec_id.nextval,'2005法网子专题二',1,2,1);

insert into baseclass(class_id,class_name,class_layer,class_father) values(baseclassclass_id.nextval,'新闻频道',1,0);
insert into baseclass(class_id,class_name,class_layer,class_father) values(baseclassclass_id.nextval,'娱乐频道',1,0);
insert into baseclass(class_id,class_name,class_layer,class_father) values(baseclassclass_id.nextval,'体育频道',1,0);
insert into baseclass(class_id,class_name,class_layer,class_father) values(baseclassclass_id.nextval,'国际事务新闻频道',2,0);
insert into baseclass(class_id,class_name,class_layer,class_father) values(baseclassclass_id.nextval,'国内事务新闻频道',2,0);

insert into users(user_id,login_name,login_password,right_level) values(usersuser_id.nextval,'admin','21232f297a57a5a743894a0e4a801fc3','管理员');
insert into users(user_id,login_name,login_password,right_level) values(usersuser_id.nextval,'leader','21232f297a57a5a743894a0e4a801fc3','组长');
insert into users(user_id,login_name,login_password,right_level) values(usersuser_id.nextval,'member','21232f297a57a5a743894a0e4a801fc3','录入员');

insert into groups(group_id,group_name,group_head,class_right,special_right,operate_right,group_description) values(groupsgroup_id.nextval,'新闻频道组','leader','1,2,','1,2,','1,2,','负责新闻频道的新闻管理');

commit;