oracle_schema.sql

通过基于Windows的图形化界面

/*
 Oracle Schema for phpBB 3.x - (c) phpBB Group, 2005

 $Id: oracle_schema.sql,v 1.83 2007/01/18 10:00:39 acydburn Exp $

*/

/*
  This first section is optional, however its probably the best method
  of running phpBB on Oracle. If you already have a tablespace and user created
  for phpBB you can leave this section commented out!

  The first set of statements create a phpBB tablespace and a phpBB user,
  make sure you change the password of the phpBB user before you run this script!!
*/

/*
CREATE TABLESPACE "PHPBB"
	LOGGING 
	DATAFILE 'E:\ORACLE\ORADATA\LOCAL\PHPBB.ora' 
	SIZE 10M
	AUTOEXTEND ON NEXT 10M
	MAXSIZE 100M;

CREATE USER "PHPBB" 
	PROFILE "DEFAULT" 
	IDENTIFIED BY "phpbb_password" 
	DEFAULT TABLESPACE "PHPBB" 
	QUOTA UNLIMITED ON "PHPBB" 
	ACCOUNT UNLOCK;

GRANT ANALYZE ANY TO "PHPBB";
GRANT CREATE SEQUENCE TO "PHPBB";
GRANT CREATE SESSION TO "PHPBB";
GRANT CREATE TABLE TO "PHPBB";
GRANT CREATE TRIGGER TO "PHPBB";
GRANT CREATE VIEW TO "PHPBB";
GRANT "CONNECT" TO "PHPBB";

COMMIT;
DISCONNECT;

CONNECT phpbb/phpbb_password;
*/
/*
	Table: 'phpbb_attachments'
*/
CREATE TABLE phpbb_attachments (
	attach_id number(8) NOT NULL,
	post_msg_id number(8) DEFAULT '0' NOT NULL,
	topic_id number(8) DEFAULT '0' NOT NULL,
	in_message number(1) DEFAULT '0' NOT NULL,
	poster_id number(8) DEFAULT '0' NOT NULL,
	is_orphan number(1) DEFAULT '1' NOT NULL,
	physical_filename varchar2(255) DEFAULT '' ,
	real_filename varchar2(255) DEFAULT '' ,
	download_count number(8) DEFAULT '0' NOT NULL,
	attach_comment clob DEFAULT '' ,
	extension varchar2(100) DEFAULT '' ,
	mimetype varchar2(100) DEFAULT '' ,
	filesize number(20) DEFAULT '0' NOT NULL,
	filetime number(11) DEFAULT '0' NOT NULL,
	thumbnail number(1) DEFAULT '0' NOT NULL,
	CONSTRAINT pk_phpbb_attachments PRIMARY KEY (attach_id)
)
/

CREATE INDEX phpbb_attachments_filetime ON phpbb_attachments (filetime)
/
CREATE INDEX phpbb_attachments_post_msg_id ON phpbb_attachments (post_msg_id)
/
CREATE INDEX phpbb_attachments_topic_id ON phpbb_attachments (topic_id)
/
CREATE INDEX phpbb_attachments_poster_id ON phpbb_attachments (poster_id)
/
CREATE INDEX phpbb_attachments_is_orphan ON phpbb_attachments (is_orphan)
/

CREATE SEQUENCE phpbb_attachments_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_attachments
BEFORE INSERT ON phpbb_attachments
FOR EACH ROW WHEN (
	new.attach_id IS NULL OR new.attach_id = 0
)
BEGIN
	SELECT phpbb_attachments_seq.nextval
	INTO :new.attach_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_acl_groups'
*/
CREATE TABLE phpbb_acl_groups (
	group_id number(8) DEFAULT '0' NOT NULL,
	forum_id number(8) DEFAULT '0' NOT NULL,
	auth_option_id number(8) DEFAULT '0' NOT NULL,
	auth_role_id number(8) DEFAULT '0' NOT NULL,
	auth_setting number(2) DEFAULT '0' NOT NULL
)
/

CREATE INDEX phpbb_acl_groups_group_id ON phpbb_acl_groups (group_id)
/
CREATE INDEX phpbb_acl_groups_auth_opt_id ON phpbb_acl_groups (auth_option_id)
/

/*
	Table: 'phpbb_acl_options'
*/
CREATE TABLE phpbb_acl_options (
	auth_option_id number(8) NOT NULL,
	auth_option varchar2(50) DEFAULT '' ,
	is_global number(1) DEFAULT '0' NOT NULL,
	is_local number(1) DEFAULT '0' NOT NULL,
	founder_only number(1) DEFAULT '0' NOT NULL,
	CONSTRAINT pk_phpbb_acl_options PRIMARY KEY (auth_option_id)
)
/

CREATE INDEX phpbb_acl_options_auth_option ON phpbb_acl_options (auth_option)
/

CREATE SEQUENCE phpbb_acl_options_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_acl_options
BEFORE INSERT ON phpbb_acl_options
FOR EACH ROW WHEN (
	new.auth_option_id IS NULL OR new.auth_option_id = 0
)
BEGIN
	SELECT phpbb_acl_options_seq.nextval
	INTO :new.auth_option_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_acl_roles'
*/
CREATE TABLE phpbb_acl_roles (
	role_id number(8) NOT NULL,
	role_name varchar2(765) DEFAULT '' ,
	role_description clob DEFAULT '' ,
	role_type varchar2(10) DEFAULT '' ,
	role_order number(4) DEFAULT '0' NOT NULL,
	CONSTRAINT pk_phpbb_acl_roles PRIMARY KEY (role_id)
)
/

CREATE INDEX phpbb_acl_roles_role_type ON phpbb_acl_roles (role_type)
/
CREATE INDEX phpbb_acl_roles_role_order ON phpbb_acl_roles (role_order)
/

CREATE SEQUENCE phpbb_acl_roles_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_acl_roles
BEFORE INSERT ON phpbb_acl_roles
FOR EACH ROW WHEN (
	new.role_id IS NULL OR new.role_id = 0
)
BEGIN
	SELECT phpbb_acl_roles_seq.nextval
	INTO :new.role_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_acl_roles_data'
*/
CREATE TABLE phpbb_acl_roles_data (
	role_id number(8) DEFAULT '0' NOT NULL,
	auth_option_id number(8) DEFAULT '0' NOT NULL,
	auth_setting number(2) DEFAULT '0' NOT NULL,
	CONSTRAINT pk_phpbb_acl_roles_data PRIMARY KEY (role_id, auth_option_id)
)
/


/*
	Table: 'phpbb_acl_users'
*/
CREATE TABLE phpbb_acl_users (
	user_id number(8) DEFAULT '0' NOT NULL,
	forum_id number(8) DEFAULT '0' NOT NULL,
	auth_option_id number(8) DEFAULT '0' NOT NULL,
	auth_role_id number(8) DEFAULT '0' NOT NULL,
	auth_setting number(2) DEFAULT '0' NOT NULL
)
/

CREATE INDEX phpbb_acl_users_user_id ON phpbb_acl_users (user_id)
/
CREATE INDEX phpbb_acl_users_auth_option_id ON phpbb_acl_users (auth_option_id)
/

/*
	Table: 'phpbb_banlist'
*/
CREATE TABLE phpbb_banlist (
	ban_id number(8) NOT NULL,
	ban_userid number(8) DEFAULT '0' NOT NULL,
	ban_ip varchar2(40) DEFAULT '' ,
	ban_email varchar2(300) DEFAULT '' ,
	ban_start number(11) DEFAULT '0' NOT NULL,
	ban_end number(11) DEFAULT '0' NOT NULL,
	ban_exclude number(1) DEFAULT '0' NOT NULL,
	ban_reason varchar2(3000) DEFAULT '' ,
	ban_give_reason varchar2(3000) DEFAULT '' ,
	CONSTRAINT pk_phpbb_banlist PRIMARY KEY (ban_id)
)
/

CREATE INDEX phpbb_banlist_ban_end ON phpbb_banlist (ban_end)
/
CREATE INDEX phpbb_banlist_ban_user ON phpbb_banlist (ban_userid, ban_exclude)
/
CREATE INDEX phpbb_banlist_ban_email ON phpbb_banlist (ban_email, ban_exclude)
/
CREATE INDEX phpbb_banlist_ban_ip ON phpbb_banlist (ban_ip, ban_exclude)
/

CREATE SEQUENCE phpbb_banlist_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_banlist
BEFORE INSERT ON phpbb_banlist
FOR EACH ROW WHEN (
	new.ban_id IS NULL OR new.ban_id = 0
)
BEGIN
	SELECT phpbb_banlist_seq.nextval
	INTO :new.ban_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_bbcodes'
*/
CREATE TABLE phpbb_bbcodes (
	bbcode_id number(3) DEFAULT '0' NOT NULL,
	bbcode_tag varchar2(16) DEFAULT '' ,
	bbcode_helpline varchar2(765) DEFAULT '' ,
	display_on_posting number(1) DEFAULT '0' NOT NULL,
	bbcode_match clob DEFAULT '' ,
	bbcode_tpl clob DEFAULT '' ,
	first_pass_match clob DEFAULT '' ,
	first_pass_replace clob DEFAULT '' ,
	second_pass_match clob DEFAULT '' ,
	second_pass_replace clob DEFAULT '' ,
	CONSTRAINT pk_phpbb_bbcodes PRIMARY KEY (bbcode_id)
)
/

CREATE INDEX phpbb_bbcodes_display_on_post ON phpbb_bbcodes (display_on_posting)
/

/*
	Table: 'phpbb_bookmarks'
*/
CREATE TABLE phpbb_bookmarks (
	topic_id number(8) DEFAULT '0' NOT NULL,
	user_id number(8) DEFAULT '0' NOT NULL,
	order_id number(8) DEFAULT '0' NOT NULL
)
/

CREATE INDEX phpbb_bookmarks_order_id ON phpbb_bookmarks (order_id)
/
CREATE INDEX phpbb_bookmarks_topic_user_id ON phpbb_bookmarks (topic_id, user_id)
/

/*
	Table: 'phpbb_bots'
*/
CREATE TABLE phpbb_bots (
	bot_id number(8) NOT NULL,
	bot_active number(1) DEFAULT '1' NOT NULL,
	bot_name varchar2(765) DEFAULT '' ,
	user_id number(8) DEFAULT '0' NOT NULL,
	bot_agent varchar2(255) DEFAULT '' ,
	bot_ip varchar2(255) DEFAULT '' ,
	CONSTRAINT pk_phpbb_bots PRIMARY KEY (bot_id)
)
/

CREATE INDEX phpbb_bots_bot_active ON phpbb_bots (bot_active)
/

CREATE SEQUENCE phpbb_bots_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_bots
BEFORE INSERT ON phpbb_bots
FOR EACH ROW WHEN (
	new.bot_id IS NULL OR new.bot_id = 0
)
BEGIN
	SELECT phpbb_bots_seq.nextval
	INTO :new.bot_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_config'
*/
CREATE TABLE phpbb_config (
	config_name varchar2(255) DEFAULT '' ,
	config_value varchar2(765) DEFAULT '' ,
	is_dynamic number(1) DEFAULT '0' NOT NULL,
	CONSTRAINT pk_phpbb_config PRIMARY KEY (config_name)
)
/

CREATE INDEX phpbb_config_is_dynamic ON phpbb_config (is_dynamic)
/

/*
	Table: 'phpbb_confirm'
*/
CREATE TABLE phpbb_confirm (
	confirm_id char(32) DEFAULT '' ,
	session_id char(32) DEFAULT '' ,
	confirm_type number(3) DEFAULT '0' NOT NULL,
	code varchar2(8) DEFAULT '' ,
	seed number(10) DEFAULT '0' NOT NULL,
	CONSTRAINT pk_phpbb_confirm PRIMARY KEY (session_id, confirm_id)
)
/

CREATE INDEX phpbb_confirm_confirm_type ON phpbb_confirm (confirm_type)
/

/*
	Table: 'phpbb_disallow'
*/
CREATE TABLE phpbb_disallow (
	disallow_id number(8) NOT NULL,
	disallow_username varchar2(765) DEFAULT '' ,
	CONSTRAINT pk_phpbb_disallow PRIMARY KEY (disallow_id)
)
/


CREATE SEQUENCE phpbb_disallow_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_disallow
BEFORE INSERT ON phpbb_disallow
FOR EACH ROW WHEN (
	new.disallow_id IS NULL OR new.disallow_id = 0
)
BEGIN
	SELECT phpbb_disallow_seq.nextval
	INTO :new.disallow_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_drafts'
*/
CREATE TABLE phpbb_drafts (
	draft_id number(8) NOT NULL,
	user_id number(8) DEFAULT '0' NOT NULL,
	topic_id number(8) DEFAULT '0' NOT NULL,
	forum_id number(8) DEFAULT '0' NOT NULL,
	save_time number(11) DEFAULT '0' NOT NULL,
	draft_subject varchar2(300) DEFAULT '' ,
	draft_message clob DEFAULT '' ,
	CONSTRAINT pk_phpbb_drafts PRIMARY KEY (draft_id)
)
/

CREATE INDEX phpbb_drafts_save_time ON phpbb_drafts (save_time)
/

CREATE SEQUENCE phpbb_drafts_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_drafts
BEFORE INSERT ON phpbb_drafts
FOR EACH ROW WHEN (
	new.draft_id IS NULL OR new.draft_id = 0
)
BEGIN
	SELECT phpbb_drafts_seq.nextval
	INTO :new.draft_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_extensions'
*/
CREATE TABLE phpbb_extensions (
	extension_id number(8) NOT NULL,
	group_id number(8) DEFAULT '0' NOT NULL,
	extension varchar2(100) DEFAULT '' ,
	CONSTRAINT pk_phpbb_extensions PRIMARY KEY (extension_id)
)
/


CREATE SEQUENCE phpbb_extensions_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_extensions
BEFORE INSERT ON phpbb_extensions
FOR EACH ROW WHEN (
	new.extension_id IS NULL OR new.extension_id = 0
)
BEGIN
	SELECT phpbb_extensions_seq.nextval
	INTO :new.extension_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_extension_groups'
*/
CREATE TABLE phpbb_extension_groups (
	group_id number(8) NOT NULL,
	group_name varchar2(765) DEFAULT '' ,
	cat_id number(2) DEFAULT '0' NOT NULL,
	allow_group number(1) DEFAULT '0' NOT NULL,
	download_mode number(1) DEFAULT '1' NOT NULL,
	upload_icon varchar2(255) DEFAULT '' ,
	max_filesize number(20) DEFAULT '0' NOT NULL,
	allowed_forums clob DEFAULT '' ,
	allow_in_pm number(1) DEFAULT '0' NOT NULL,
	CONSTRAINT pk_phpbb_extension_groups PRIMARY KEY (group_id)
)
/


CREATE SEQUENCE phpbb_extension_groups_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_extension_groups
BEFORE INSERT ON phpbb_extension_groups
FOR EACH ROW WHEN (
	new.group_id IS NULL OR new.group_id = 0
)
BEGIN
	SELECT phpbb_extension_groups_seq.nextval
	INTO :new.group_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_forums'
*/
CREATE TABLE phpbb_forums (
	forum_id number(8) NOT NULL,
	parent_id number(8) DEFAULT '0' NOT NULL,
	left_id number(8) DEFAULT '0' NOT NULL,
	right_id number(8) DEFAULT '0' NOT NULL,
	forum_parents clob DEFAULT '' ,
	forum_name varchar2(765) DEFAULT '' ,
	forum_desc clob DEFAULT '' ,
	forum_desc_bitfield varchar2(255) DEFAULT '' ,
	forum_desc_options number(11) DEFAULT '7' NOT NULL,
	forum_desc_uid varchar2(5) DEFAULT '' ,
	forum_link varchar2(765) DEFAULT '' ,
	forum_password varchar2(120) DEFAULT '' ,