oracle_schema.sql

通过基于Windows的图形化界面

CREATE INDEX phpbb_styles_template_data_tid ON phpbb_styles_template_data (template_id)
/
CREATE INDEX phpbb_styles_template_data_tfn ON phpbb_styles_template_data (template_filename)
/

CREATE SEQUENCE phpbb_styles_template_data_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_styles_template_data
BEFORE INSERT ON phpbb_styles_template_data
FOR EACH ROW WHEN (
	new.template_id IS NULL OR new.template_id = 0
)
BEGIN
	SELECT phpbb_styles_template_data_seq.nextval
	INTO :new.template_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_styles_theme'
*/
CREATE TABLE phpbb_styles_theme (
	theme_id number(4) NOT NULL,
	theme_name varchar2(765) DEFAULT '' ,
	theme_copyright varchar2(765) DEFAULT '' ,
	theme_path varchar2(100) DEFAULT '' ,
	theme_storedb number(1) DEFAULT '0' NOT NULL,
	theme_mtime number(11) DEFAULT '0' NOT NULL,
	theme_data clob DEFAULT '' ,
	CONSTRAINT pk_phpbb_styles_theme PRIMARY KEY (theme_id),
	CONSTRAINT u_phpbb_theme_name UNIQUE (theme_name)
)
/


CREATE SEQUENCE phpbb_styles_theme_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_styles_theme
BEFORE INSERT ON phpbb_styles_theme
FOR EACH ROW WHEN (
	new.theme_id IS NULL OR new.theme_id = 0
)
BEGIN
	SELECT phpbb_styles_theme_seq.nextval
	INTO :new.theme_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_styles_imageset'
*/
CREATE TABLE phpbb_styles_imageset (
	imageset_id number(4) NOT NULL,
	imageset_name varchar2(765) DEFAULT '' ,
	imageset_copyright varchar2(765) DEFAULT '' ,
	imageset_path varchar2(100) DEFAULT '' ,
	site_logo varchar2(200) DEFAULT '' ,
	upload_bar varchar2(200) DEFAULT '' ,
	poll_left varchar2(200) DEFAULT '' ,
	poll_center varchar2(200) DEFAULT '' ,
	poll_right varchar2(200) DEFAULT '' ,
	icon_friend varchar2(200) DEFAULT '' ,
	icon_foe varchar2(200) DEFAULT '' ,
	forum_link varchar2(200) DEFAULT '' ,
	forum_read varchar2(200) DEFAULT '' ,
	forum_read_locked varchar2(200) DEFAULT '' ,
	forum_read_subforum varchar2(200) DEFAULT '' ,
	forum_unread varchar2(200) DEFAULT '' ,
	forum_unread_locked varchar2(200) DEFAULT '' ,
	forum_unread_subforum varchar2(200) DEFAULT '' ,
	topic_moved varchar2(200) DEFAULT '' ,
	topic_read varchar2(200) DEFAULT '' ,
	topic_read_mine varchar2(200) DEFAULT '' ,
	topic_read_hot varchar2(200) DEFAULT '' ,
	topic_read_hot_mine varchar2(200) DEFAULT '' ,
	topic_read_locked varchar2(200) DEFAULT '' ,
	topic_read_locked_mine varchar2(200) DEFAULT '' ,
	topic_unread varchar2(200) DEFAULT '' ,
	topic_unread_mine varchar2(200) DEFAULT '' ,
	topic_unread_hot varchar2(200) DEFAULT '' ,
	topic_unread_hot_mine varchar2(200) DEFAULT '' ,
	topic_unread_locked varchar2(200) DEFAULT '' ,
	topic_unread_locked_mine varchar2(200) DEFAULT '' ,
	sticky_read varchar2(200) DEFAULT '' ,
	sticky_read_mine varchar2(200) DEFAULT '' ,
	sticky_read_locked varchar2(200) DEFAULT '' ,
	sticky_read_locked_mine varchar2(200) DEFAULT '' ,
	sticky_unread varchar2(200) DEFAULT '' ,
	sticky_unread_mine varchar2(200) DEFAULT '' ,
	sticky_unread_locked varchar2(200) DEFAULT '' ,
	sticky_unread_locked_mine varchar2(200) DEFAULT '' ,
	announce_read varchar2(200) DEFAULT '' ,
	announce_read_mine varchar2(200) DEFAULT '' ,
	announce_read_locked varchar2(200) DEFAULT '' ,
	announce_read_locked_mine varchar2(200) DEFAULT '' ,
	announce_unread varchar2(200) DEFAULT '' ,
	announce_unread_mine varchar2(200) DEFAULT '' ,
	announce_unread_locked varchar2(200) DEFAULT '' ,
	announce_unread_locked_mine varchar2(200) DEFAULT '' ,
	global_read varchar2(200) DEFAULT '' ,
	global_read_mine varchar2(200) DEFAULT '' ,
	global_read_locked varchar2(200) DEFAULT '' ,
	global_read_locked_mine varchar2(200) DEFAULT '' ,
	global_unread varchar2(200) DEFAULT '' ,
	global_unread_mine varchar2(200) DEFAULT '' ,
	global_unread_locked varchar2(200) DEFAULT '' ,
	global_unread_locked_mine varchar2(200) DEFAULT '' ,
	pm_read varchar2(200) DEFAULT '' ,
	pm_unread varchar2(200) DEFAULT '' ,
	icon_contact_aim varchar2(200) DEFAULT '' ,
	icon_contact_email varchar2(200) DEFAULT '' ,
	icon_contact_icq varchar2(200) DEFAULT '' ,
	icon_contact_jabber varchar2(200) DEFAULT '' ,
	icon_contact_msnm varchar2(200) DEFAULT '' ,
	icon_contact_pm varchar2(200) DEFAULT '' ,
	icon_contact_yahoo varchar2(200) DEFAULT '' ,
	icon_contact_www varchar2(200) DEFAULT '' ,
	icon_post_delete varchar2(200) DEFAULT '' ,
	icon_post_edit varchar2(200) DEFAULT '' ,
	icon_post_info varchar2(200) DEFAULT '' ,
	icon_post_quote varchar2(200) DEFAULT '' ,
	icon_post_report varchar2(200) DEFAULT '' ,
	icon_post_target varchar2(200) DEFAULT '' ,
	icon_post_target_unread varchar2(200) DEFAULT '' ,
	icon_topic_attach varchar2(200) DEFAULT '' ,
	icon_topic_latest varchar2(200) DEFAULT '' ,
	icon_topic_newest varchar2(200) DEFAULT '' ,
	icon_topic_reported varchar2(200) DEFAULT '' ,
	icon_topic_unapproved varchar2(200) DEFAULT '' ,
	icon_user_online varchar2(200) DEFAULT '' ,
	icon_user_offline varchar2(200) DEFAULT '' ,
	icon_user_profile varchar2(200) DEFAULT '' ,
	icon_user_search varchar2(200) DEFAULT '' ,
	icon_user_warn varchar2(200) DEFAULT '' ,
	button_pm_forward varchar2(200) DEFAULT '' ,
	button_pm_new varchar2(200) DEFAULT '' ,
	button_pm_reply varchar2(200) DEFAULT '' ,
	button_topic_locked varchar2(200) DEFAULT '' ,
	button_topic_new varchar2(200) DEFAULT '' ,
	button_topic_reply varchar2(200) DEFAULT '' ,
	user_icon1 varchar2(200) DEFAULT '' ,
	user_icon2 varchar2(200) DEFAULT '' ,
	user_icon3 varchar2(200) DEFAULT '' ,
	user_icon4 varchar2(200) DEFAULT '' ,
	user_icon5 varchar2(200) DEFAULT '' ,
	user_icon6 varchar2(200) DEFAULT '' ,
	user_icon7 varchar2(200) DEFAULT '' ,
	user_icon8 varchar2(200) DEFAULT '' ,
	user_icon9 varchar2(200) DEFAULT '' ,
	user_icon10 varchar2(200) DEFAULT '' ,
	CONSTRAINT pk_phpbb_styles_imageset PRIMARY KEY (imageset_id),
	CONSTRAINT u_phpbb_imgset_nm UNIQUE (imageset_name)
)
/


CREATE SEQUENCE phpbb_styles_imageset_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_styles_imageset
BEFORE INSERT ON phpbb_styles_imageset
FOR EACH ROW WHEN (
	new.imageset_id IS NULL OR new.imageset_id = 0
)
BEGIN
	SELECT phpbb_styles_imageset_seq.nextval
	INTO :new.imageset_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_topics'
*/
CREATE TABLE phpbb_topics (
	topic_id number(8) NOT NULL,
	forum_id number(8) DEFAULT '0' NOT NULL,
	icon_id number(8) DEFAULT '0' NOT NULL,
	topic_attachment number(1) DEFAULT '0' NOT NULL,
	topic_approved number(1) DEFAULT '1' NOT NULL,
	topic_reported number(1) DEFAULT '0' NOT NULL,
	topic_title varchar2(300) DEFAULT '' ,
	topic_poster number(8) DEFAULT '0' NOT NULL,
	topic_time number(11) DEFAULT '0' NOT NULL,
	topic_time_limit number(11) DEFAULT '0' NOT NULL,
	topic_views number(8) DEFAULT '0' NOT NULL,
	topic_replies number(8) DEFAULT '0' NOT NULL,
	topic_replies_real number(8) DEFAULT '0' NOT NULL,
	topic_status number(3) DEFAULT '0' NOT NULL,
	topic_type number(3) DEFAULT '0' NOT NULL,
	topic_first_post_id number(8) DEFAULT '0' NOT NULL,
	topic_first_poster_name varchar2(765) DEFAULT '' ,
	topic_first_poster_colour varchar2(6) DEFAULT '' ,
	topic_last_post_id number(8) DEFAULT '0' NOT NULL,
	topic_last_poster_id number(8) DEFAULT '0' NOT NULL,
	topic_last_poster_name varchar2(765) DEFAULT '' ,
	topic_last_poster_colour varchar2(6) DEFAULT '' ,
	topic_last_post_subject varchar2(300) DEFAULT '' ,
	topic_last_post_time number(11) DEFAULT '0' NOT NULL,
	topic_last_view_time number(11) DEFAULT '0' NOT NULL,
	topic_moved_id number(8) DEFAULT '0' NOT NULL,
	topic_bumped number(1) DEFAULT '0' NOT NULL,
	topic_bumper number(8) DEFAULT '0' NOT NULL,
	poll_title varchar2(300) DEFAULT '' ,
	poll_start number(11) DEFAULT '0' NOT NULL,
	poll_length number(11) DEFAULT '0' NOT NULL,
	poll_max_options number(4) DEFAULT '1' NOT NULL,
	poll_last_vote number(11) DEFAULT '0' NOT NULL,
	poll_vote_change number(1) DEFAULT '0' NOT NULL,
	CONSTRAINT pk_phpbb_topics PRIMARY KEY (topic_id)
)
/

CREATE INDEX phpbb_topics_forum_id ON phpbb_topics (forum_id)
/
CREATE INDEX phpbb_topics_forum_id_type ON phpbb_topics (forum_id, topic_type)
/
CREATE INDEX phpbb_topics_last_post_time ON phpbb_topics (topic_last_post_time)
/
CREATE INDEX phpbb_topics_topic_approved ON phpbb_topics (topic_approved)
/
CREATE INDEX phpbb_topics_fid_time_moved ON phpbb_topics (forum_id, topic_last_post_time, topic_moved_id)
/

CREATE SEQUENCE phpbb_topics_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_topics
BEFORE INSERT ON phpbb_topics
FOR EACH ROW WHEN (
	new.topic_id IS NULL OR new.topic_id = 0
)
BEGIN
	SELECT phpbb_topics_seq.nextval
	INTO :new.topic_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_topics_track'
*/
CREATE TABLE phpbb_topics_track (
	user_id number(8) DEFAULT '0' NOT NULL,
	topic_id number(8) DEFAULT '0' NOT NULL,
	forum_id number(8) DEFAULT '0' NOT NULL,
	mark_time number(11) DEFAULT '0' NOT NULL,
	CONSTRAINT pk_phpbb_topics_track PRIMARY KEY (user_id, topic_id)
)
/

CREATE INDEX phpbb_topics_track_forum_id ON phpbb_topics_track (forum_id)
/

/*
	Table: 'phpbb_topics_posted'
*/
CREATE TABLE phpbb_topics_posted (
	user_id number(8) DEFAULT '0' NOT NULL,
	topic_id number(8) DEFAULT '0' NOT NULL,
	topic_posted number(1) DEFAULT '0' NOT NULL,
	CONSTRAINT pk_phpbb_topics_posted PRIMARY KEY (user_id, topic_id)
)
/


/*
	Table: 'phpbb_topics_watch'
*/
CREATE TABLE phpbb_topics_watch (
	topic_id number(8) DEFAULT '0' NOT NULL,
	user_id number(8) DEFAULT '0' NOT NULL,
	notify_status number(1) DEFAULT '0' NOT NULL
)
/

CREATE INDEX phpbb_topics_watch_topic_id ON phpbb_topics_watch (topic_id)
/
CREATE INDEX phpbb_topics_watch_user_id ON phpbb_topics_watch (user_id)
/
CREATE INDEX phpbb_topics_watch_notify_stat ON phpbb_topics_watch (notify_status)
/

/*
	Table: 'phpbb_user_group'
*/
CREATE TABLE phpbb_user_group (
	group_id number(8) DEFAULT '0' NOT NULL,
	user_id number(8) DEFAULT '0' NOT NULL,
	group_leader number(1) DEFAULT '0' NOT NULL,
	user_pending number(1) DEFAULT '1' NOT NULL
)
/

CREATE INDEX phpbb_user_group_group_id ON phpbb_user_group (group_id)
/
CREATE INDEX phpbb_user_group_user_id ON phpbb_user_group (user_id)
/
CREATE INDEX phpbb_user_group_group_leader ON phpbb_user_group (group_leader)
/

/*
	Table: 'phpbb_users'
*/
CREATE TABLE phpbb_users (
	user_id number(8) NOT NULL,
	user_type number(2) DEFAULT '0' NOT NULL,
	group_id number(8) DEFAULT '3' NOT NULL,
	user_permissions clob DEFAULT '' ,
	user_perm_from number(8) DEFAULT '0' NOT NULL,
	user_ip varchar2(40) DEFAULT '' ,
	user_regdate number(11) DEFAULT '0' NOT NULL,
	username varchar2(255) DEFAULT '' ,
	username_clean varchar2(255) DEFAULT '' ,
	user_password varchar2(120) DEFAULT '' ,
	user_passchg number(11) DEFAULT '0' NOT NULL,
	user_pass_convert number(1) DEFAULT '0' NOT NULL,
	user_email varchar2(300) DEFAULT '' ,
	user_email_hash number(20) DEFAULT '0' NOT NULL,
	user_birthday varchar2(10) DEFAULT '' ,
	user_lastvisit number(11) DEFAULT '0' NOT NULL,
	user_lastmark number(11) DEFAULT '0' NOT NULL,
	user_lastpost_time number(11) DEFAULT '0' NOT NULL,
	user_lastpage varchar2(600) DEFAULT '' ,
	user_last_confirm_key varchar2(10) DEFAULT '' ,
	user_last_search number(11) DEFAULT '0' NOT NULL,
	user_warnings number(4) DEFAULT '0' NOT NULL,
	user_last_warning number(11) DEFAULT '0' NOT NULL,
	user_login_attempts number(4) DEFAULT '0' NOT NULL,
	user_inactive_reason number(2) DEFAULT '0' NOT NULL,
	user_inactive_time number(11) DEFAULT '0' NOT NULL,
	user_posts number(8) DEFAULT '0' NOT NULL,
	user_lang varchar2(30) DEFAULT '' ,
	user_timezone number(5, 2) DEFAULT '0' NOT NULL,
	user_dst number(1) DEFAULT '0' NOT NULL,
	user_dateformat varchar2(90) DEFAULT 'd M Y H:i' NOT NULL,
	user_style number(4) DEFAULT '0' NOT NULL,
	user_rank number(8) DEFAULT '0' NOT NULL,
	user_colour varchar2(6) DEFAULT '' ,
	user_new_privmsg number(4) DEFAULT '0' NOT NULL,
	user_unread_privmsg number(4) DEFAULT '0' NOT NULL,
	user_last_privmsg number(11) DEFAULT '0' NOT NULL,
	user_message_rules number(1) DEFAULT '0' NOT NULL,
	user_full_folder number(11) DEFAULT '-3' NOT NULL,
	user_emailtime number(11) DEFAULT '0' NOT NULL,
	user_topic_show_days number(4) DEFAULT '0' NOT NULL,
	user_topic_sortby_type varchar2(1) DEFAULT 't' NOT NULL,
	user_topic_sortby_dir varchar2(1) DEFAULT 'd' NOT NULL,
	user_post_show_days number(4) DEFAULT '0' NOT NULL,
	user_post_sortby_type varchar2(1) DEFAULT 't' NOT NULL,
	user_post_sortby_dir varchar2(1) DEFAULT 'a' NOT NULL,
	user_notify number(1) DEFAULT '0' NOT NULL,
	user_notify_pm number(1) DEFAULT '1' NOT NULL,
	user_notify_type number(4) DEFAULT '0' NOT NULL,
	user_allow_pm number(1) DEFAULT '1' NOT NULL,
	user_allow_viewonline number(1) DEFAULT '1' NOT NULL,
	user_allow_viewemail number(1) DEFAULT '1' NOT NULL,
	user_allow_massemail number(1) DEFAULT '1' NOT NULL,
	user_options number(11) DEFAULT '895' NOT NULL,
	user_avatar varchar2(255) DEFAULT '' ,
	user_avatar_type number(2) DEFAULT '0' NOT NULL,
	user_avatar_width number(4) DEFAULT '0' NOT NULL,
	user_avatar_height number(4) DEFAULT '0' NOT NULL,
	user_sig clob DEFAULT '' ,
	user_sig_bbcode_uid varchar2(5) DEFAULT '' ,
	user_sig_bbcode_bitfield varchar2(255) DEFAULT '' ,
	user_from varchar2(300) DEFAULT '' ,
	user_icq varchar2(15) DEFAULT '' ,
	user_aim varchar2(765) DEFAULT '' ,
	user_yim varchar2(765) DEFAULT '' ,
	user_msnm varchar2(765) DEFAULT '' ,
	user_jabber varchar2(765) DEFAULT '' ,
	user_website varchar2(600) DEFAULT '' ,
	user_occ clob DEFAULT '' ,
	user_interests clob DEFAULT '' ,
	user_actkey varchar2(32) DEFAULT '' ,
	user_newpasswd varchar2(96) DEFAULT '' ,
	CONSTRAINT pk_phpbb_users PRIMARY KEY (user_id)
)
/

CREATE INDEX phpbb_users_user_birthday ON phpbb_users (user_birthday)
/
CREATE INDEX phpbb_users_user_email_hash ON phpbb_users (user_email_hash)
/
CREATE INDEX phpbb_users_user_type ON phpbb_users (user_type)
/
CREATE INDEX phpbb_users_username_clean ON phpbb_users (username_clean)
/

CREATE SEQUENCE phpbb_users_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_users
BEFORE INSERT ON phpbb_users
FOR EACH ROW WHEN (
	new.user_id IS NULL OR new.user_id = 0
)
BEGIN
	SELECT phpbb_users_seq.nextval
	INTO :new.user_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_warnings'
*/
CREATE TABLE phpbb_warnings (
	warning_id number(8) NOT NULL,
	user_id number(8) DEFAULT '0' NOT NULL,
	post_id number(8) DEFAULT '0' NOT NULL,
	log_id number(8) DEFAULT '0' NOT NULL,
	warning_time number(11) DEFAULT '0' NOT NULL,
	CONSTRAINT pk_phpbb_warnings PRIMARY KEY (warning_id)
)
/


CREATE SEQUENCE phpbb_warnings_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_warnings
BEFORE INSERT ON phpbb_warnings
FOR EACH ROW WHEN (
	new.warning_id IS NULL OR new.warning_id = 0
)
BEGIN
	SELECT phpbb_warnings_seq.nextval
	INTO :new.warning_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_words'
*/
CREATE TABLE phpbb_words (
	word_id number(8) NOT NULL,
	word varchar2(765) DEFAULT '' ,
	replacement varchar2(765) DEFAULT '' ,
	CONSTRAINT pk_phpbb_words PRIMARY KEY (word_id)
)
/


CREATE SEQUENCE phpbb_words_seq
/

CREATE OR REPLACE TRIGGER t_phpbb_words
BEFORE INSERT ON phpbb_words
FOR EACH ROW WHEN (
	new.word_id IS NULL OR new.word_id = 0
)
BEGIN
	SELECT phpbb_words_seq.nextval
	INTO :new.word_id
	FROM dual;
END;
/


/*
	Table: 'phpbb_zebra'
*/
CREATE TABLE phpbb_zebra (
	user_id number(8) DEFAULT '0' NOT NULL,
	zebra_id number(8) DEFAULT '0' NOT NULL,
	friend number(1) DEFAULT '0' NOT NULL,
	foe number(1) DEFAULT '0' NOT NULL,
	CONSTRAINT pk_phpbb_zebra PRIMARY KEY (user_id, zebra_id)
)
/