postgres_schema.sql

通过基于Windows的图形化界面

	Table: 'phpbb_styles_imageset'
*/
CREATE SEQUENCE phpbb_styles_imageset_seq;

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

CREATE UNIQUE INDEX phpbb_styles_imageset_imgset_nm ON phpbb_styles_imageset (imageset_name);

/*
	Table: 'phpbb_topics'
*/
CREATE SEQUENCE phpbb_topics_seq;

CREATE TABLE phpbb_topics (
	topic_id INT4 DEFAULT nextval('phpbb_topics_seq'),
	forum_id INT4 DEFAULT '0' NOT NULL CHECK (forum_id >= 0),
	icon_id INT4 DEFAULT '0' NOT NULL CHECK (icon_id >= 0),
	topic_attachment INT2 DEFAULT '0' NOT NULL CHECK (topic_attachment >= 0),
	topic_approved INT2 DEFAULT '1' NOT NULL CHECK (topic_approved >= 0),
	topic_reported INT2 DEFAULT '0' NOT NULL CHECK (topic_reported >= 0),
	topic_title varchar(100) DEFAULT '' NOT NULL,
	topic_poster INT4 DEFAULT '0' NOT NULL CHECK (topic_poster >= 0),
	topic_time INT4 DEFAULT '0' NOT NULL CHECK (topic_time >= 0),
	topic_time_limit INT4 DEFAULT '0' NOT NULL CHECK (topic_time_limit >= 0),
	topic_views INT4 DEFAULT '0' NOT NULL CHECK (topic_views >= 0),
	topic_replies INT4 DEFAULT '0' NOT NULL CHECK (topic_replies >= 0),
	topic_replies_real INT4 DEFAULT '0' NOT NULL CHECK (topic_replies_real >= 0),
	topic_status INT2 DEFAULT '0' NOT NULL,
	topic_type INT2 DEFAULT '0' NOT NULL,
	topic_first_post_id INT4 DEFAULT '0' NOT NULL CHECK (topic_first_post_id >= 0),
	topic_first_poster_name varchar(255) DEFAULT '' NOT NULL,
	topic_first_poster_colour varchar(6) DEFAULT '' NOT NULL,
	topic_last_post_id INT4 DEFAULT '0' NOT NULL CHECK (topic_last_post_id >= 0),
	topic_last_poster_id INT4 DEFAULT '0' NOT NULL CHECK (topic_last_poster_id >= 0),
	topic_last_poster_name varchar(255) DEFAULT '' NOT NULL,
	topic_last_poster_colour varchar(6) DEFAULT '' NOT NULL,
	topic_last_post_subject varchar(100) DEFAULT '' NOT NULL,
	topic_last_post_time INT4 DEFAULT '0' NOT NULL CHECK (topic_last_post_time >= 0),
	topic_last_view_time INT4 DEFAULT '0' NOT NULL CHECK (topic_last_view_time >= 0),
	topic_moved_id INT4 DEFAULT '0' NOT NULL CHECK (topic_moved_id >= 0),
	topic_bumped INT2 DEFAULT '0' NOT NULL CHECK (topic_bumped >= 0),
	topic_bumper INT4 DEFAULT '0' NOT NULL CHECK (topic_bumper >= 0),
	poll_title varchar(100) DEFAULT '' NOT NULL,
	poll_start INT4 DEFAULT '0' NOT NULL CHECK (poll_start >= 0),
	poll_length INT4 DEFAULT '0' NOT NULL CHECK (poll_length >= 0),
	poll_max_options INT2 DEFAULT '1' NOT NULL,
	poll_last_vote INT4 DEFAULT '0' NOT NULL CHECK (poll_last_vote >= 0),
	poll_vote_change INT2 DEFAULT '0' NOT NULL CHECK (poll_vote_change >= 0),
	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);

/*
	Table: 'phpbb_topics_track'
*/
CREATE TABLE phpbb_topics_track (
	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
	topic_id INT4 DEFAULT '0' NOT NULL CHECK (topic_id >= 0),
	forum_id INT4 DEFAULT '0' NOT NULL CHECK (forum_id >= 0),
	mark_time INT4 DEFAULT '0' NOT NULL CHECK (mark_time >= 0),
	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 INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
	topic_id INT4 DEFAULT '0' NOT NULL CHECK (topic_id >= 0),
	topic_posted INT2 DEFAULT '0' NOT NULL CHECK (topic_posted >= 0),
	PRIMARY KEY (user_id, topic_id)
);


/*
	Table: 'phpbb_topics_watch'
*/
CREATE TABLE phpbb_topics_watch (
	topic_id INT4 DEFAULT '0' NOT NULL CHECK (topic_id >= 0),
	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
	notify_status INT2 DEFAULT '0' NOT NULL CHECK (notify_status >= 0)
);

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 INT4 DEFAULT '0' NOT NULL CHECK (group_id >= 0),
	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
	group_leader INT2 DEFAULT '0' NOT NULL CHECK (group_leader >= 0),
	user_pending INT2 DEFAULT '1' NOT NULL CHECK (user_pending >= 0)
);

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 SEQUENCE phpbb_users_seq;

CREATE TABLE phpbb_users (
	user_id INT4 DEFAULT nextval('phpbb_users_seq'),
	user_type INT2 DEFAULT '0' NOT NULL,
	group_id INT4 DEFAULT '3' NOT NULL CHECK (group_id >= 0),
	user_permissions TEXT DEFAULT '' NOT NULL,
	user_perm_from INT4 DEFAULT '0' NOT NULL CHECK (user_perm_from >= 0),
	user_ip varchar(40) DEFAULT '' NOT NULL,
	user_regdate INT4 DEFAULT '0' NOT NULL CHECK (user_regdate >= 0),
	username varchar_ci DEFAULT '' NOT NULL,
	username_clean varchar_ci DEFAULT '' NOT NULL,
	user_password varchar(40) DEFAULT '' NOT NULL,
	user_passchg INT4 DEFAULT '0' NOT NULL CHECK (user_passchg >= 0),
	user_pass_convert INT2 DEFAULT '0' NOT NULL CHECK (user_pass_convert >= 0),
	user_email varchar(100) DEFAULT '' NOT NULL,
	user_email_hash INT8 DEFAULT '0' NOT NULL,
	user_birthday varchar(10) DEFAULT '' NOT NULL,
	user_lastvisit INT4 DEFAULT '0' NOT NULL CHECK (user_lastvisit >= 0),
	user_lastmark INT4 DEFAULT '0' NOT NULL CHECK (user_lastmark >= 0),
	user_lastpost_time INT4 DEFAULT '0' NOT NULL CHECK (user_lastpost_time >= 0),
	user_lastpage varchar(200) DEFAULT '' NOT NULL,
	user_last_confirm_key varchar(10) DEFAULT '' NOT NULL,
	user_last_search INT4 DEFAULT '0' NOT NULL CHECK (user_last_search >= 0),
	user_warnings INT2 DEFAULT '0' NOT NULL,
	user_last_warning INT4 DEFAULT '0' NOT NULL CHECK (user_last_warning >= 0),
	user_login_attempts INT2 DEFAULT '0' NOT NULL,
	user_inactive_reason INT2 DEFAULT '0' NOT NULL,
	user_inactive_time INT4 DEFAULT '0' NOT NULL CHECK (user_inactive_time >= 0),
	user_posts INT4 DEFAULT '0' NOT NULL CHECK (user_posts >= 0),
	user_lang varchar(30) DEFAULT '' NOT NULL,
	user_timezone decimal(5,2) DEFAULT '0' NOT NULL,
	user_dst INT2 DEFAULT '0' NOT NULL CHECK (user_dst >= 0),
	user_dateformat varchar(30) DEFAULT 'd M Y H:i' NOT NULL,
	user_style INT2 DEFAULT '0' NOT NULL,
	user_rank INT4 DEFAULT '0' NOT NULL CHECK (user_rank >= 0),
	user_colour varchar(6) DEFAULT '' NOT NULL,
	user_new_privmsg INT2 DEFAULT '0' NOT NULL,
	user_unread_privmsg INT2 DEFAULT '0' NOT NULL,
	user_last_privmsg INT4 DEFAULT '0' NOT NULL CHECK (user_last_privmsg >= 0),
	user_message_rules INT2 DEFAULT '0' NOT NULL CHECK (user_message_rules >= 0),
	user_full_folder INT4 DEFAULT '-3' NOT NULL,
	user_emailtime INT4 DEFAULT '0' NOT NULL CHECK (user_emailtime >= 0),
	user_topic_show_days INT2 DEFAULT '0' NOT NULL CHECK (user_topic_show_days >= 0),
	user_topic_sortby_type varchar(1) DEFAULT 't' NOT NULL,
	user_topic_sortby_dir varchar(1) DEFAULT 'd' NOT NULL,
	user_post_show_days INT2 DEFAULT '0' NOT NULL CHECK (user_post_show_days >= 0),
	user_post_sortby_type varchar(1) DEFAULT 't' NOT NULL,
	user_post_sortby_dir varchar(1) DEFAULT 'a' NOT NULL,
	user_notify INT2 DEFAULT '0' NOT NULL CHECK (user_notify >= 0),
	user_notify_pm INT2 DEFAULT '1' NOT NULL CHECK (user_notify_pm >= 0),
	user_notify_type INT2 DEFAULT '0' NOT NULL,
	user_allow_pm INT2 DEFAULT '1' NOT NULL CHECK (user_allow_pm >= 0),
	user_allow_viewonline INT2 DEFAULT '1' NOT NULL CHECK (user_allow_viewonline >= 0),
	user_allow_viewemail INT2 DEFAULT '1' NOT NULL CHECK (user_allow_viewemail >= 0),
	user_allow_massemail INT2 DEFAULT '1' NOT NULL CHECK (user_allow_massemail >= 0),
	user_options INT4 DEFAULT '895' NOT NULL CHECK (user_options >= 0),
	user_avatar varchar(255) DEFAULT '' NOT NULL,
	user_avatar_type INT2 DEFAULT '0' NOT NULL,
	user_avatar_width INT2 DEFAULT '0' NOT NULL CHECK (user_avatar_width >= 0),
	user_avatar_height INT2 DEFAULT '0' NOT NULL CHECK (user_avatar_height >= 0),
	user_sig TEXT DEFAULT '' NOT NULL,
	user_sig_bbcode_uid varchar(5) DEFAULT '' NOT NULL,
	user_sig_bbcode_bitfield varchar(255) DEFAULT '' NOT NULL,
	user_from varchar(100) DEFAULT '' NOT NULL,
	user_icq varchar(15) DEFAULT '' NOT NULL,
	user_aim varchar(255) DEFAULT '' NOT NULL,
	user_yim varchar(255) DEFAULT '' NOT NULL,
	user_msnm varchar(255) DEFAULT '' NOT NULL,
	user_jabber varchar(255) DEFAULT '' NOT NULL,
	user_website varchar(200) DEFAULT '' NOT NULL,
	user_occ varchar(4000) DEFAULT '' NOT NULL,
	user_interests varchar(4000) DEFAULT '' NOT NULL,
	user_actkey varchar(32) DEFAULT '' NOT NULL,
	user_newpasswd varchar(32) DEFAULT '' NOT NULL,
	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);

/*
	Table: 'phpbb_warnings'
*/
CREATE SEQUENCE phpbb_warnings_seq;

CREATE TABLE phpbb_warnings (
	warning_id INT4 DEFAULT nextval('phpbb_warnings_seq'),
	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
	post_id INT4 DEFAULT '0' NOT NULL CHECK (post_id >= 0),
	log_id INT4 DEFAULT '0' NOT NULL CHECK (log_id >= 0),
	warning_time INT4 DEFAULT '0' NOT NULL CHECK (warning_time >= 0),
	PRIMARY KEY (warning_id)
);


/*
	Table: 'phpbb_words'
*/
CREATE SEQUENCE phpbb_words_seq;

CREATE TABLE phpbb_words (
	word_id INT4 DEFAULT nextval('phpbb_words_seq'),
	word varchar(255) DEFAULT '' NOT NULL,
	replacement varchar(255) DEFAULT '' NOT NULL,
	PRIMARY KEY (word_id)
);


/*
	Table: 'phpbb_zebra'
*/
CREATE TABLE phpbb_zebra (
	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
	zebra_id INT4 DEFAULT '0' NOT NULL CHECK (zebra_id >= 0),
	friend INT2 DEFAULT '0' NOT NULL CHECK (friend >= 0),
	foe INT2 DEFAULT '0' NOT NULL CHECK (foe >= 0),
	PRIMARY KEY (user_id, zebra_id)
);



COMMIT;