postgres_schema.sql

通过基于Windows的图形化界面

	download_mode INT2 DEFAULT '1' NOT NULL CHECK (download_mode >= 0),
	upload_icon varchar(255) DEFAULT '' NOT NULL,
	max_filesize INT4 DEFAULT '0' NOT NULL CHECK (max_filesize >= 0),
	allowed_forums varchar(8000) DEFAULT '' NOT NULL,
	allow_in_pm INT2 DEFAULT '0' NOT NULL CHECK (allow_in_pm >= 0),
	PRIMARY KEY (group_id)
);


/*
	Table: 'phpbb_forums'
*/
CREATE SEQUENCE phpbb_forums_seq;

CREATE TABLE phpbb_forums (
	forum_id INT4 DEFAULT nextval('phpbb_forums_seq'),
	parent_id INT4 DEFAULT '0' NOT NULL CHECK (parent_id >= 0),
	left_id INT4 DEFAULT '0' NOT NULL CHECK (left_id >= 0),
	right_id INT4 DEFAULT '0' NOT NULL CHECK (right_id >= 0),
	forum_parents TEXT DEFAULT '' NOT NULL,
	forum_name varchar(255) DEFAULT '' NOT NULL,
	forum_desc varchar(4000) DEFAULT '' NOT NULL,
	forum_desc_bitfield varchar(255) DEFAULT '' NOT NULL,
	forum_desc_options INT4 DEFAULT '7' NOT NULL CHECK (forum_desc_options >= 0),
	forum_desc_uid varchar(5) DEFAULT '' NOT NULL,
	forum_link varchar(255) DEFAULT '' NOT NULL,
	forum_password varchar(40) DEFAULT '' NOT NULL,
	forum_style INT2 DEFAULT '0' NOT NULL,
	forum_image varchar(255) DEFAULT '' NOT NULL,
	forum_rules varchar(4000) DEFAULT '' NOT NULL,
	forum_rules_link varchar(255) DEFAULT '' NOT NULL,
	forum_rules_bitfield varchar(255) DEFAULT '' NOT NULL,
	forum_rules_options INT4 DEFAULT '7' NOT NULL CHECK (forum_rules_options >= 0),
	forum_rules_uid varchar(5) DEFAULT '' NOT NULL,
	forum_topics_per_page INT2 DEFAULT '0' NOT NULL,
	forum_type INT2 DEFAULT '0' NOT NULL,
	forum_status INT2 DEFAULT '0' NOT NULL,
	forum_posts INT4 DEFAULT '0' NOT NULL CHECK (forum_posts >= 0),
	forum_topics INT4 DEFAULT '0' NOT NULL CHECK (forum_topics >= 0),
	forum_topics_real INT4 DEFAULT '0' NOT NULL CHECK (forum_topics_real >= 0),
	forum_last_post_id INT4 DEFAULT '0' NOT NULL CHECK (forum_last_post_id >= 0),
	forum_last_poster_id INT4 DEFAULT '0' NOT NULL CHECK (forum_last_poster_id >= 0),
	forum_last_post_subject varchar(100) DEFAULT '' NOT NULL,
	forum_last_post_time INT4 DEFAULT '0' NOT NULL CHECK (forum_last_post_time >= 0),
	forum_last_poster_name varchar(255) DEFAULT '' NOT NULL,
	forum_last_poster_colour varchar(6) DEFAULT '' NOT NULL,
	forum_flags INT2 DEFAULT '32' NOT NULL,
	display_on_index INT2 DEFAULT '1' NOT NULL CHECK (display_on_index >= 0),
	enable_indexing INT2 DEFAULT '1' NOT NULL CHECK (enable_indexing >= 0),
	enable_icons INT2 DEFAULT '1' NOT NULL CHECK (enable_icons >= 0),
	enable_prune INT2 DEFAULT '0' NOT NULL CHECK (enable_prune >= 0),
	prune_next INT4 DEFAULT '0' NOT NULL CHECK (prune_next >= 0),
	prune_days INT4 DEFAULT '0' NOT NULL CHECK (prune_days >= 0),
	prune_viewed INT4 DEFAULT '0' NOT NULL CHECK (prune_viewed >= 0),
	prune_freq INT4 DEFAULT '0' NOT NULL CHECK (prune_freq >= 0),
	PRIMARY KEY (forum_id)
);

CREATE INDEX phpbb_forums_left_right_id ON phpbb_forums (left_id, right_id);
CREATE INDEX phpbb_forums_forum_lastpost_id ON phpbb_forums (forum_last_post_id);

/*
	Table: 'phpbb_forums_access'
*/
CREATE TABLE phpbb_forums_access (
	forum_id INT4 DEFAULT '0' NOT NULL CHECK (forum_id >= 0),
	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
	session_id char(32) DEFAULT '' NOT NULL,
	PRIMARY KEY (forum_id, user_id, session_id)
);


/*
	Table: 'phpbb_forums_track'
*/
CREATE TABLE phpbb_forums_track (
	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_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, forum_id)
);


/*
	Table: 'phpbb_forums_watch'
*/
CREATE TABLE phpbb_forums_watch (
	forum_id INT4 DEFAULT '0' NOT NULL CHECK (forum_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_forums_watch_forum_id ON phpbb_forums_watch (forum_id);
CREATE INDEX phpbb_forums_watch_user_id ON phpbb_forums_watch (user_id);
CREATE INDEX phpbb_forums_watch_notify_stat ON phpbb_forums_watch (notify_status);

/*
	Table: 'phpbb_groups'
*/
CREATE SEQUENCE phpbb_groups_seq;

CREATE TABLE phpbb_groups (
	group_id INT4 DEFAULT nextval('phpbb_groups_seq'),
	group_type INT2 DEFAULT '1' NOT NULL,
	group_founder_manage INT2 DEFAULT '0' NOT NULL CHECK (group_founder_manage >= 0),
	group_name varchar_ci DEFAULT '' NOT NULL,
	group_desc varchar(4000) DEFAULT '' NOT NULL,
	group_desc_bitfield varchar(255) DEFAULT '' NOT NULL,
	group_desc_options INT4 DEFAULT '7' NOT NULL CHECK (group_desc_options >= 0),
	group_desc_uid varchar(5) DEFAULT '' NOT NULL,
	group_display INT2 DEFAULT '0' NOT NULL CHECK (group_display >= 0),
	group_avatar varchar(255) DEFAULT '' NOT NULL,
	group_avatar_type INT2 DEFAULT '0' NOT NULL,
	group_avatar_width INT2 DEFAULT '0' NOT NULL,
	group_avatar_height INT2 DEFAULT '0' NOT NULL,
	group_rank INT4 DEFAULT '0' NOT NULL CHECK (group_rank >= 0),
	group_colour varchar(6) DEFAULT '' NOT NULL,
	group_sig_chars INT4 DEFAULT '0' NOT NULL CHECK (group_sig_chars >= 0),
	group_receive_pm INT2 DEFAULT '0' NOT NULL CHECK (group_receive_pm >= 0),
	group_message_limit INT4 DEFAULT '0' NOT NULL CHECK (group_message_limit >= 0),
	group_legend INT2 DEFAULT '1' NOT NULL CHECK (group_legend >= 0),
	PRIMARY KEY (group_id)
);

CREATE INDEX phpbb_groups_group_legend ON phpbb_groups (group_legend);

/*
	Table: 'phpbb_icons'
*/
CREATE SEQUENCE phpbb_icons_seq;

CREATE TABLE phpbb_icons (
	icons_id INT4 DEFAULT nextval('phpbb_icons_seq'),
	icons_url varchar(255) DEFAULT '' NOT NULL,
	icons_width INT2 DEFAULT '0' NOT NULL,
	icons_height INT2 DEFAULT '0' NOT NULL,
	icons_order INT4 DEFAULT '0' NOT NULL CHECK (icons_order >= 0),
	display_on_posting INT2 DEFAULT '1' NOT NULL CHECK (display_on_posting >= 0),
	PRIMARY KEY (icons_id)
);

CREATE INDEX phpbb_icons_display_on_posting ON phpbb_icons (display_on_posting);

/*
	Table: 'phpbb_lang'
*/
CREATE SEQUENCE phpbb_lang_seq;

CREATE TABLE phpbb_lang (
	lang_id INT2 DEFAULT nextval('phpbb_lang_seq'),
	lang_iso varchar(30) DEFAULT '' NOT NULL,
	lang_dir varchar(30) DEFAULT '' NOT NULL,
	lang_english_name varchar(100) DEFAULT '' NOT NULL,
	lang_local_name varchar(255) DEFAULT '' NOT NULL,
	lang_author varchar(255) DEFAULT '' NOT NULL,
	PRIMARY KEY (lang_id)
);

CREATE INDEX phpbb_lang_lang_iso ON phpbb_lang (lang_iso);

/*
	Table: 'phpbb_log'
*/
CREATE SEQUENCE phpbb_log_seq;

CREATE TABLE phpbb_log (
	log_id INT4 DEFAULT nextval('phpbb_log_seq'),
	log_type INT2 DEFAULT '0' NOT NULL,
	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
	forum_id INT4 DEFAULT '0' NOT NULL CHECK (forum_id >= 0),
	topic_id INT4 DEFAULT '0' NOT NULL CHECK (topic_id >= 0),
	reportee_id INT4 DEFAULT '0' NOT NULL CHECK (reportee_id >= 0),
	log_ip varchar(40) DEFAULT '' NOT NULL,
	log_time INT4 DEFAULT '0' NOT NULL CHECK (log_time >= 0),
	log_operation varchar(4000) DEFAULT '' NOT NULL,
	log_data TEXT DEFAULT '' NOT NULL,
	PRIMARY KEY (log_id)
);

CREATE INDEX phpbb_log_log_type ON phpbb_log (log_type);
CREATE INDEX phpbb_log_forum_id ON phpbb_log (forum_id);
CREATE INDEX phpbb_log_topic_id ON phpbb_log (topic_id);
CREATE INDEX phpbb_log_reportee_id ON phpbb_log (reportee_id);
CREATE INDEX phpbb_log_user_id ON phpbb_log (user_id);

/*
	Table: 'phpbb_moderator_cache'
*/
CREATE TABLE phpbb_moderator_cache (
	forum_id INT4 DEFAULT '0' NOT NULL CHECK (forum_id >= 0),
	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
	username varchar(255) DEFAULT '' NOT NULL,
	group_id INT4 DEFAULT '0' NOT NULL CHECK (group_id >= 0),
	group_name varchar(255) DEFAULT '' NOT NULL,
	display_on_index INT2 DEFAULT '1' NOT NULL CHECK (display_on_index >= 0)
);

CREATE INDEX phpbb_moderator_cache_disp_idx ON phpbb_moderator_cache (display_on_index);
CREATE INDEX phpbb_moderator_cache_forum_id ON phpbb_moderator_cache (forum_id);

/*
	Table: 'phpbb_modules'
*/
CREATE SEQUENCE phpbb_modules_seq;

CREATE TABLE phpbb_modules (
	module_id INT4 DEFAULT nextval('phpbb_modules_seq'),
	module_enabled INT2 DEFAULT '1' NOT NULL CHECK (module_enabled >= 0),
	module_display INT2 DEFAULT '1' NOT NULL CHECK (module_display >= 0),
	module_basename varchar(255) DEFAULT '' NOT NULL,
	module_class varchar(10) DEFAULT '' NOT NULL,
	parent_id INT4 DEFAULT '0' NOT NULL CHECK (parent_id >= 0),
	left_id INT4 DEFAULT '0' NOT NULL CHECK (left_id >= 0),
	right_id INT4 DEFAULT '0' NOT NULL CHECK (right_id >= 0),
	module_langname varchar(255) DEFAULT '' NOT NULL,
	module_mode varchar(255) DEFAULT '' NOT NULL,
	module_auth varchar(255) DEFAULT '' NOT NULL,
	PRIMARY KEY (module_id)
);

CREATE INDEX phpbb_modules_left_right_id ON phpbb_modules (left_id, right_id);
CREATE INDEX phpbb_modules_module_enabled ON phpbb_modules (module_enabled);
CREATE INDEX phpbb_modules_class_left_id ON phpbb_modules (module_class, left_id);

/*
	Table: 'phpbb_poll_options'
*/
CREATE TABLE phpbb_poll_options (
	poll_option_id INT2 DEFAULT '0' NOT NULL,
	topic_id INT4 DEFAULT '0' NOT NULL CHECK (topic_id >= 0),
	poll_option_text varchar(4000) DEFAULT '' NOT NULL,
	poll_option_total INT4 DEFAULT '0' NOT NULL CHECK (poll_option_total >= 0)
);

CREATE INDEX phpbb_poll_options_poll_opt_id ON phpbb_poll_options (poll_option_id);
CREATE INDEX phpbb_poll_options_topic_id ON phpbb_poll_options (topic_id);

/*
	Table: 'phpbb_poll_votes'
*/
CREATE TABLE phpbb_poll_votes (
	topic_id INT4 DEFAULT '0' NOT NULL CHECK (topic_id >= 0),
	poll_option_id INT2 DEFAULT '0' NOT NULL,
	vote_user_id INT4 DEFAULT '0' NOT NULL CHECK (vote_user_id >= 0),
	vote_user_ip varchar(40) DEFAULT '' NOT NULL
);

CREATE INDEX phpbb_poll_votes_topic_id ON phpbb_poll_votes (topic_id);
CREATE INDEX phpbb_poll_votes_vote_user_id ON phpbb_poll_votes (vote_user_id);
CREATE INDEX phpbb_poll_votes_vote_user_ip ON phpbb_poll_votes (vote_user_ip);

/*
	Table: 'phpbb_posts'
*/
CREATE SEQUENCE phpbb_posts_seq;

CREATE TABLE phpbb_posts (
	post_id INT4 DEFAULT nextval('phpbb_posts_seq'),
	topic_id INT4 DEFAULT '0' NOT NULL CHECK (topic_id >= 0),
	forum_id INT4 DEFAULT '0' NOT NULL CHECK (forum_id >= 0),
	poster_id INT4 DEFAULT '0' NOT NULL CHECK (poster_id >= 0),
	icon_id INT4 DEFAULT '0' NOT NULL CHECK (icon_id >= 0),
	poster_ip varchar(40) DEFAULT '' NOT NULL,
	post_time INT4 DEFAULT '0' NOT NULL CHECK (post_time >= 0),
	post_approved INT2 DEFAULT '1' NOT NULL CHECK (post_approved >= 0),
	post_reported INT2 DEFAULT '0' NOT NULL CHECK (post_reported >= 0),
	enable_bbcode INT2 DEFAULT '1' NOT NULL CHECK (enable_bbcode >= 0),
	enable_smilies INT2 DEFAULT '1' NOT NULL CHECK (enable_smilies >= 0),
	enable_magic_url INT2 DEFAULT '1' NOT NULL CHECK (enable_magic_url >= 0),
	enable_sig INT2 DEFAULT '1' NOT NULL CHECK (enable_sig >= 0),
	post_username varchar(255) DEFAULT '' NOT NULL,
	post_subject varchar(100) DEFAULT '' NOT NULL,
	post_text TEXT DEFAULT '' NOT NULL,
	post_checksum varchar(32) DEFAULT '' NOT NULL,
	post_attachment INT2 DEFAULT '0' NOT NULL CHECK (post_attachment >= 0),
	bbcode_bitfield varchar(255) DEFAULT '' NOT NULL,
	bbcode_uid varchar(5) DEFAULT '' NOT NULL,
	post_postcount INT2 DEFAULT '1' NOT NULL CHECK (post_postcount >= 0),
	post_edit_time INT4 DEFAULT '0' NOT NULL CHECK (post_edit_time >= 0),
	post_edit_reason varchar(255) DEFAULT '' NOT NULL,
	post_edit_user INT4 DEFAULT '0' NOT NULL CHECK (post_edit_user >= 0),
	post_edit_count INT2 DEFAULT '0' NOT NULL CHECK (post_edit_count >= 0),
	post_edit_locked INT2 DEFAULT '0' NOT NULL CHECK (post_edit_locked >= 0),
	PRIMARY KEY (post_id)
);

CREATE INDEX phpbb_posts_forum_id ON phpbb_posts (forum_id);
CREATE INDEX phpbb_posts_topic_id ON phpbb_posts (topic_id);
CREATE INDEX phpbb_posts_poster_ip ON phpbb_posts (poster_ip);
CREATE INDEX phpbb_posts_poster_id ON phpbb_posts (poster_id);
CREATE INDEX phpbb_posts_post_approved ON phpbb_posts (post_approved);
CREATE INDEX phpbb_posts_tid_post_time ON phpbb_posts (topic_id, post_time);

/*
	Table: 'phpbb_privmsgs'
*/
CREATE SEQUENCE phpbb_privmsgs_seq;

CREATE TABLE phpbb_privmsgs (
	msg_id INT4 DEFAULT nextval('phpbb_privmsgs_seq'),
	root_level INT4 DEFAULT '0' NOT NULL CHECK (root_level >= 0),
	author_id INT4 DEFAULT '0' NOT NULL CHECK (author_id >= 0),
	icon_id INT4 DEFAULT '0' NOT NULL CHECK (icon_id >= 0),
	author_ip varchar(40) DEFAULT '' NOT NULL,
	message_time INT4 DEFAULT '0' NOT NULL CHECK (message_time >= 0),
	enable_bbcode INT2 DEFAULT '1' NOT NULL CHECK (enable_bbcode >= 0),
	enable_smilies INT2 DEFAULT '1' NOT NULL CHECK (enable_smilies >= 0),
	enable_magic_url INT2 DEFAULT '1' NOT NULL CHECK (enable_magic_url >= 0),
	enable_sig INT2 DEFAULT '1' NOT NULL CHECK (enable_sig >= 0),
	message_subject varchar(100) DEFAULT '' NOT NULL,
	message_text TEXT DEFAULT '' NOT NULL,
	message_edit_reason varchar(255) DEFAULT '' NOT NULL,
	message_edit_user INT4 DEFAULT '0' NOT NULL CHECK (message_edit_user >= 0),
	message_attachment INT2 DEFAULT '0' NOT NULL CHECK (message_attachment >= 0),
	bbcode_bitfield varchar(255) DEFAULT '' NOT NULL,
	bbcode_uid varchar(5) DEFAULT '' NOT NULL,
	message_edit_time INT4 DEFAULT '0' NOT NULL CHECK (message_edit_time >= 0),
	message_edit_count INT2 DEFAULT '0' NOT NULL CHECK (message_edit_count >= 0),
	to_address varchar(4000) DEFAULT '' NOT NULL,
	bcc_address varchar(4000) DEFAULT '' NOT NULL,
	PRIMARY KEY (msg_id)
);

CREATE INDEX phpbb_privmsgs_author_ip ON phpbb_privmsgs (author_ip);
CREATE INDEX phpbb_privmsgs_message_time ON phpbb_privmsgs (message_time);
CREATE INDEX phpbb_privmsgs_author_id ON phpbb_privmsgs (author_id);
CREATE INDEX phpbb_privmsgs_root_level ON phpbb_privmsgs (root_level);

/*
	Table: 'phpbb_privmsgs_folder'
*/
CREATE SEQUENCE phpbb_privmsgs_folder_seq;

CREATE TABLE phpbb_privmsgs_folder (
	folder_id INT4 DEFAULT nextval('phpbb_privmsgs_folder_seq'),
	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),