postgres_schema.sql

通过基于Windows的图形化界面

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

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

*/

BEGIN;

/*
	Domain definition
*/
CREATE DOMAIN varchar_ci AS varchar(255) NOT NULL DEFAULT ''::character varying;

/*
	Operation Functions
*/
CREATE FUNCTION _varchar_ci_equal(varchar_ci, varchar_ci) RETURNS boolean AS 'SELECT LOWER($1) = LOWER($2)' LANGUAGE SQL STRICT;
CREATE FUNCTION _varchar_ci_not_equal(varchar_ci, varchar_ci) RETURNS boolean AS 'SELECT LOWER($1) != LOWER($2)' LANGUAGE SQL STRICT;
CREATE FUNCTION _varchar_ci_less_than(varchar_ci, varchar_ci) RETURNS boolean AS 'SELECT LOWER($1) < LOWER($2)' LANGUAGE SQL STRICT;
CREATE FUNCTION _varchar_ci_less_equal(varchar_ci, varchar_ci) RETURNS boolean AS 'SELECT LOWER($1) <= LOWER($2)' LANGUAGE SQL STRICT;
CREATE FUNCTION _varchar_ci_greater_than(varchar_ci, varchar_ci) RETURNS boolean AS 'SELECT LOWER($1) > LOWER($2)' LANGUAGE SQL STRICT;
CREATE FUNCTION _varchar_ci_greater_equals(varchar_ci, varchar_ci) RETURNS boolean AS 'SELECT LOWER($1) >= LOWER($2)' LANGUAGE SQL STRICT;

/*
	Operators
*/
CREATE OPERATOR <(
  PROCEDURE = _varchar_ci_less_than,
  LEFTARG = varchar_ci,
  RIGHTARG = varchar_ci,
  COMMUTATOR = >,
  NEGATOR = >=,
  RESTRICT = scalarltsel,
  JOIN = scalarltjoinsel);

CREATE OPERATOR <=(
  PROCEDURE = _varchar_ci_less_equal,
  LEFTARG = varchar_ci,
  RIGHTARG = varchar_ci,
  COMMUTATOR = >=,
  NEGATOR = >,
  RESTRICT = scalarltsel,
  JOIN = scalarltjoinsel);

CREATE OPERATOR >(
  PROCEDURE = _varchar_ci_greater_than,
  LEFTARG = varchar_ci,
  RIGHTARG = varchar_ci,
  COMMUTATOR = <,
  NEGATOR = <=,
  RESTRICT = scalargtsel,
  JOIN = scalargtjoinsel);

CREATE OPERATOR >=(
  PROCEDURE = _varchar_ci_greater_equals,
  LEFTARG = varchar_ci,
  RIGHTARG = varchar_ci,
  COMMUTATOR = <=,
  NEGATOR = <,
  RESTRICT = scalargtsel,
  JOIN = scalargtjoinsel);

CREATE OPERATOR <>(
  PROCEDURE = _varchar_ci_not_equal,
  LEFTARG = varchar_ci,
  RIGHTARG = varchar_ci,
  COMMUTATOR = <>,
  NEGATOR = =,
  RESTRICT = neqsel,
  JOIN = neqjoinsel);

CREATE OPERATOR =(
  PROCEDURE = _varchar_ci_equal,
  LEFTARG = varchar_ci,
  RIGHTARG = varchar_ci,
  COMMUTATOR = =,
  NEGATOR = <>,
  RESTRICT = eqsel,
  JOIN = eqjoinsel,
  HASHES,
  MERGES,
  SORT1= <);

/*
	Table: 'phpbb_attachments'
*/
CREATE SEQUENCE phpbb_attachments_seq;

CREATE TABLE phpbb_attachments (
	attach_id INT4 DEFAULT nextval('phpbb_attachments_seq'),
	post_msg_id INT4 DEFAULT '0' NOT NULL CHECK (post_msg_id >= 0),
	topic_id INT4 DEFAULT '0' NOT NULL CHECK (topic_id >= 0),
	in_message INT2 DEFAULT '0' NOT NULL CHECK (in_message >= 0),
	poster_id INT4 DEFAULT '0' NOT NULL CHECK (poster_id >= 0),
	is_orphan INT2 DEFAULT '1' NOT NULL CHECK (is_orphan >= 0),
	physical_filename varchar(255) DEFAULT '' NOT NULL,
	real_filename varchar(255) DEFAULT '' NOT NULL,
	download_count INT4 DEFAULT '0' NOT NULL CHECK (download_count >= 0),
	attach_comment varchar(4000) DEFAULT '' NOT NULL,
	extension varchar(100) DEFAULT '' NOT NULL,
	mimetype varchar(100) DEFAULT '' NOT NULL,
	filesize INT4 DEFAULT '0' NOT NULL CHECK (filesize >= 0),
	filetime INT4 DEFAULT '0' NOT NULL CHECK (filetime >= 0),
	thumbnail INT2 DEFAULT '0' NOT NULL CHECK (thumbnail >= 0),
	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);

/*
	Table: 'phpbb_acl_groups'
*/
CREATE TABLE phpbb_acl_groups (
	group_id INT4 DEFAULT '0' NOT NULL CHECK (group_id >= 0),
	forum_id INT4 DEFAULT '0' NOT NULL CHECK (forum_id >= 0),
	auth_option_id INT4 DEFAULT '0' NOT NULL CHECK (auth_option_id >= 0),
	auth_role_id INT4 DEFAULT '0' NOT NULL CHECK (auth_role_id >= 0),
	auth_setting INT2 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 SEQUENCE phpbb_acl_options_seq;

CREATE TABLE phpbb_acl_options (
	auth_option_id INT4 DEFAULT nextval('phpbb_acl_options_seq'),
	auth_option varchar(50) DEFAULT '' NOT NULL,
	is_global INT2 DEFAULT '0' NOT NULL CHECK (is_global >= 0),
	is_local INT2 DEFAULT '0' NOT NULL CHECK (is_local >= 0),
	founder_only INT2 DEFAULT '0' NOT NULL CHECK (founder_only >= 0),
	PRIMARY KEY (auth_option_id)
);

CREATE INDEX phpbb_acl_options_auth_option ON phpbb_acl_options (auth_option);

/*
	Table: 'phpbb_acl_roles'
*/
CREATE SEQUENCE phpbb_acl_roles_seq;

CREATE TABLE phpbb_acl_roles (
	role_id INT4 DEFAULT nextval('phpbb_acl_roles_seq'),
	role_name varchar(255) DEFAULT '' NOT NULL,
	role_description varchar(4000) DEFAULT '' NOT NULL,
	role_type varchar(10) DEFAULT '' NOT NULL,
	role_order INT2 DEFAULT '0' NOT NULL CHECK (role_order >= 0),
	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);

/*
	Table: 'phpbb_acl_roles_data'
*/
CREATE TABLE phpbb_acl_roles_data (
	role_id INT4 DEFAULT '0' NOT NULL CHECK (role_id >= 0),
	auth_option_id INT4 DEFAULT '0' NOT NULL CHECK (auth_option_id >= 0),
	auth_setting INT2 DEFAULT '0' NOT NULL,
	PRIMARY KEY (role_id, auth_option_id)
);


/*
	Table: 'phpbb_acl_users'
*/
CREATE TABLE phpbb_acl_users (
	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
	forum_id INT4 DEFAULT '0' NOT NULL CHECK (forum_id >= 0),
	auth_option_id INT4 DEFAULT '0' NOT NULL CHECK (auth_option_id >= 0),
	auth_role_id INT4 DEFAULT '0' NOT NULL CHECK (auth_role_id >= 0),
	auth_setting INT2 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 SEQUENCE phpbb_banlist_seq;

CREATE TABLE phpbb_banlist (
	ban_id INT4 DEFAULT nextval('phpbb_banlist_seq'),
	ban_userid INT4 DEFAULT '0' NOT NULL CHECK (ban_userid >= 0),
	ban_ip varchar(40) DEFAULT '' NOT NULL,
	ban_email varchar(100) DEFAULT '' NOT NULL,
	ban_start INT4 DEFAULT '0' NOT NULL CHECK (ban_start >= 0),
	ban_end INT4 DEFAULT '0' NOT NULL CHECK (ban_end >= 0),
	ban_exclude INT2 DEFAULT '0' NOT NULL CHECK (ban_exclude >= 0),
	ban_reason varchar(3000) DEFAULT '' NOT NULL,
	ban_give_reason varchar(3000) DEFAULT '' NOT NULL,
	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);

/*
	Table: 'phpbb_bbcodes'
*/
CREATE TABLE phpbb_bbcodes (
	bbcode_id INT2 DEFAULT '0' NOT NULL,
	bbcode_tag varchar(16) DEFAULT '' NOT NULL,
	bbcode_helpline varchar(255) DEFAULT '' NOT NULL,
	display_on_posting INT2 DEFAULT '0' NOT NULL CHECK (display_on_posting >= 0),
	bbcode_match varchar(4000) DEFAULT '' NOT NULL,
	bbcode_tpl TEXT DEFAULT '' NOT NULL,
	first_pass_match TEXT DEFAULT '' NOT NULL,
	first_pass_replace TEXT DEFAULT '' NOT NULL,
	second_pass_match TEXT DEFAULT '' NOT NULL,
	second_pass_replace TEXT DEFAULT '' NOT NULL,
	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 INT4 DEFAULT '0' NOT NULL CHECK (topic_id >= 0),
	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
	order_id INT4 DEFAULT '0' NOT NULL CHECK (order_id >= 0)
);

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

CREATE TABLE phpbb_bots (
	bot_id INT4 DEFAULT nextval('phpbb_bots_seq'),
	bot_active INT2 DEFAULT '1' NOT NULL CHECK (bot_active >= 0),
	bot_name varchar(255) DEFAULT '' NOT NULL,
	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
	bot_agent varchar(255) DEFAULT '' NOT NULL,
	bot_ip varchar(255) DEFAULT '' NOT NULL,
	PRIMARY KEY (bot_id)
);

CREATE INDEX phpbb_bots_bot_active ON phpbb_bots (bot_active);

/*
	Table: 'phpbb_config'
*/
CREATE TABLE phpbb_config (
	config_name varchar(255) DEFAULT '' NOT NULL,
	config_value varchar(255) DEFAULT '' NOT NULL,
	is_dynamic INT2 DEFAULT '0' NOT NULL CHECK (is_dynamic >= 0),
	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 '' NOT NULL,
	session_id char(32) DEFAULT '' NOT NULL,
	confirm_type INT2 DEFAULT '0' NOT NULL,
	code varchar(8) DEFAULT '' NOT NULL,
	seed INT4 DEFAULT '0' NOT NULL CHECK (seed >= 0),
	PRIMARY KEY (session_id, confirm_id)
);

CREATE INDEX phpbb_confirm_confirm_type ON phpbb_confirm (confirm_type);

/*
	Table: 'phpbb_disallow'
*/
CREATE SEQUENCE phpbb_disallow_seq;

CREATE TABLE phpbb_disallow (
	disallow_id INT4 DEFAULT nextval('phpbb_disallow_seq'),
	disallow_username varchar(255) DEFAULT '' NOT NULL,
	PRIMARY KEY (disallow_id)
);


/*
	Table: 'phpbb_drafts'
*/
CREATE SEQUENCE phpbb_drafts_seq;

CREATE TABLE phpbb_drafts (
	draft_id INT4 DEFAULT nextval('phpbb_drafts_seq'),
	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),
	save_time INT4 DEFAULT '0' NOT NULL CHECK (save_time >= 0),
	draft_subject varchar(100) DEFAULT '' NOT NULL,
	draft_message TEXT DEFAULT '' NOT NULL,
	PRIMARY KEY (draft_id)
);

CREATE INDEX phpbb_drafts_save_time ON phpbb_drafts (save_time);

/*
	Table: 'phpbb_extensions'
*/
CREATE SEQUENCE phpbb_extensions_seq;

CREATE TABLE phpbb_extensions (
	extension_id INT4 DEFAULT nextval('phpbb_extensions_seq'),
	group_id INT4 DEFAULT '0' NOT NULL CHECK (group_id >= 0),
	extension varchar(100) DEFAULT '' NOT NULL,
	PRIMARY KEY (extension_id)
);


/*
	Table: 'phpbb_extension_groups'
*/
CREATE SEQUENCE phpbb_extension_groups_seq;

CREATE TABLE phpbb_extension_groups (
	group_id INT4 DEFAULT nextval('phpbb_extension_groups_seq'),
	group_name varchar(255) DEFAULT '' NOT NULL,
	cat_id INT2 DEFAULT '0' NOT NULL,
	allow_group INT2 DEFAULT '0' NOT NULL CHECK (allow_group >= 0),