diff options
author | Meik Sievertsen <acydburn@phpbb.com> | 2005-01-08 12:28:10 +0000 |
---|---|---|
committer | Meik Sievertsen <acydburn@phpbb.com> | 2005-01-08 12:28:10 +0000 |
commit | b7ac18d9f946e0407522c5f8ed45051780cf1ccb (patch) | |
tree | a3796208516e9e80b90313c9addf85a8085118d8 /phpBB/install/schemas | |
parent | 7a1b178fe6454f73c4afd761bb54306d24611287 (diff) | |
download | forums-b7ac18d9f946e0407522c5f8ed45051780cf1ccb.tar forums-b7ac18d9f946e0407522c5f8ed45051780cf1ccb.tar.gz forums-b7ac18d9f946e0407522c5f8ed45051780cf1ccb.tar.bz2 forums-b7ac18d9f946e0407522c5f8ed45051780cf1ccb.tar.xz forums-b7ac18d9f946e0407522c5f8ed45051780cf1ccb.zip |
- postgresql schema, NOT TESTED
git-svn-id: file:///svn/phpbb/trunk@5049 89ea8834-ac86-4346-8a33-228a782c2dd0
Diffstat (limited to 'phpBB/install/schemas')
-rw-r--r-- | phpBB/install/schemas/postgres_schema.sql | 1639 |
1 files changed, 1162 insertions, 477 deletions
diff --git a/phpBB/install/schemas/postgres_schema.sql b/phpBB/install/schemas/postgres_schema.sql index ae236c9d94..d656f2968d 100644 --- a/phpBB/install/schemas/postgres_schema.sql +++ b/phpBB/install/schemas/postgres_schema.sql @@ -5,545 +5,1230 @@ $Id$ */ -CREATE SEQUENCE phpbb_banlist_id_seq start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1; -CREATE SEQUENCE phpbb_categories_id_seq start 2 increment 1 maxvalue 2147483647 minvalue 1 cache 1; -CREATE SEQUENCE phpbb_disallow_id_seq start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1; -CREATE SEQUENCE phpbb_posts_id_seq start 2 increment 1 maxvalue 2147483647 minvalue 1 cache 1; -CREATE SEQUENCE phpbb_privmsgs_id_seq start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1; -CREATE SEQUENCE phpbb_ranks_id_seq start 2 increment 1 maxvalue 2147483647 minvalue 1 cache 1; -CREATE SEQUENCE phpbb_search_wordlist_id_seq start 13 increment 1 maxvalue 2147483647 minvalue 1 cache 1; -CREATE SEQUENCE phpbb_smilies_id_seq start 42 increment 1 maxvalue 2147483647 minvalue 1 cache 1; -CREATE SEQUENCE phpbb_themes_id_seq start 7 increment 1 maxvalue 2147483647 minvalue 1 cache 1; -CREATE SEQUENCE phpbb_topics_id_seq start 2 increment 1 maxvalue 2147483647 minvalue 1 cache 1; -CREATE SEQUENCE phpbb_users_id_seq start 3 increment 1 maxvalue 2147483647 minvalue 1 cache 1; -CREATE SEQUENCE phpbb_words_id_seq start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1; -CREATE SEQUENCE phpbb_groups_id_seq start 3 increment 1 maxvalue 2147483647 minvalue 1 cache 1; -CREATE SEQUENCE phpbb_forum_prune_id_seq start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1; -CREATE SEQUENCE phpbb_vote_desc_id_seq start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1; - -/* -------------------------------------------------------- - Table structure for table phpbb_auth_access --------------------------------------------------------- */ -CREATE TABLE phpbb_auth_access ( - group_id int DEFAULT '0' NOT NULL, - forum_id int2 DEFAULT '0' NOT NULL, - auth_view int2 DEFAULT '0' NOT NULL, - auth_read int2 DEFAULT '0' NOT NULL, - auth_post int2 DEFAULT '0' NOT NULL, - auth_reply int2 DEFAULT '0' NOT NULL, - auth_edit int2 DEFAULT '0' NOT NULL, - auth_delete int2 DEFAULT '0' NOT NULL, - auth_announce int2 DEFAULT '0' NOT NULL, - auth_sticky int2 DEFAULT '0' NOT NULL, - auth_pollcreate int2 DEFAULT '0' NOT NULL, - auth_attachments int2 DEFAULT '0' NOT NULL, - auth_vote int2 DEFAULT '0' NOT NULL, - auth_mod int2 DEFAULT '0' NOT NULL, - CONSTRAINT phpbb_auth_access_pkey PRIMARY KEY (group_id, forum_id) -); - - -/* -------------------------------------------------------- - Table structure for table phpbb_groups --------------------------------------------------------- */ -CREATE TABLE phpbb_groups ( - group_id int DEFAULT nextval('phpbb_groups_id_seq'::text) NOT NULL, - group_name varchar(40) NOT NULL, - group_type int2 DEFAULT '1' NOT NULL, - group_description varchar(255) NOT NULL, - group_moderator int4 DEFAULT '0' NOT NULL, - group_single_user int2 DEFAULT '0' NOT NULL, - CONSTRAINT phpbb_groups_pkey PRIMARY KEY (group_id) +BEGIN; + +/* Table: phpbb_attachments */ + +CREATE SEQUENCE phpbb_attachments_attach_id_; + +CREATE TABLE phpbb_attachments ( + attach_id INT4 DEFAULT nextval('phpbb_attachments_attach_id_'), + post_msg_id INT4 DEFAULT '0' NOT NULL, + topic_id INT4 DEFAULT '0' NOT NULL, + in_message INT2 DEFAULT '0' NOT NULL, + poster_id INT4 DEFAULT '0' NOT NULL, + physical_filename varchar(255) NOT NULL, + real_filename varchar(255) NOT NULL, + download_count INT4 DEFAULT '0' NOT NULL, + comment varchar(255), + extension varchar(100), + mimetype varchar(100), + filesize INT4 NOT NULL, + filetime INT4 DEFAULT '0' NOT NULL, + thumbnail INT2 DEFAULT '0' NOT NULL, + PRIMARY KEY (attach_id), + CHECK (post_msg_id>=0), + CHECK (topic_id>=0), + CHECK (in_message>=0), + CHECK (poster_id>=0), + CHECK (download_count>=0), + CHECK (filesize>=0), + CHECK (filetime>=0) +); + +CREATE INDEX filetime_phpbb_attachments_index ON phpbb_attachments (filetime); +CREATE INDEX post_msg_id_phpbb_attachments_index ON phpbb_attachments (post_msg_id); +CREATE INDEX topic_id_phpbb_attachments_index ON phpbb_attachments (topic_id); +CREATE INDEX poster_id_phpbb_attachments_index ON phpbb_attachments (poster_id); +CREATE INDEX physical_filename_phpbb_attachments_index ON phpbb_attachments (physical_filename); +CREATE INDEX filesize_phpbb_attachments_index ON phpbb_attachments (filesize); + +SELECT SETVAL('phpbb_attachments_attach_id_',(select case when max(attach_id)>0 then max(attach_id)+1 else 1 end from phpbb_attachments)); + +/* Table: phpbb_auth_groups */ +CREATE TABLE phpbb_auth_groups ( + group_id INT4 DEFAULT '0' NOT NULL, + forum_id INT4 DEFAULT '0' NOT NULL, + auth_option_id INT2 DEFAULT '0' NOT NULL, + auth_setting INT2 DEFAULT '0' NOT NULL, +); + +CREATE INDEX group_id_phpbb_auth_groups_index ON phpbb_auth_groups (group_id); +CREATE INDEX auth_option_id_phpbb_auth_groups_index ON phpbb_auth_groups (auth_option_id); + +/* Table: phpbb_auth_options */ +CREATE SEQUENCE phpbb_auth_options_auth_opti; + +CREATE TABLE phpbb_auth_options ( + auth_option_id INT2 DEFAULT nextval('phpbb_auth_options_auth_opti'), + auth_option char(20) NOT NULL, + is_global INT2 DEFAULT '0' NOT NULL, + is_local INT2 DEFAULT '0' NOT NULL, + founder_only INT2 DEFAULT '0' NOT NULL, + PRIMARY KEY (auth_option_id) ); +CREATE INDEX auth_option_phpbb_auth_options_index ON phpbb_auth_options (auth_option); + +SELECT SETVAL('phpbb_auth_options_auth_opti',(select case when max(auth_option_id)>0 then max(auth_option_id)+1 else 1 end from phpbb_auth_options)); + +/* Table: phpbb_auth_presets */ +CREATE SEQUENCE phpbb_auth_presets_preset_id; + +CREATE TABLE phpbb_auth_presets ( + preset_id INT2 DEFAULT nextval('phpbb_auth_presets_preset_id'), + preset_name varchar(50) DEFAULT '' NOT NULL, + preset_user_id INT4 DEFAULT '0' NOT NULL, + preset_type varchar(2) DEFAULT '' NOT NULL, + preset_data text DEFAULT '' NOT NULL, + PRIMARY KEY (preset_id), + CHECK (preset_user_id>=0) +); + +CREATE INDEX preset_type_phpbb_auth_presets_index ON phpbb_auth_presets (preset_type); + +SELECT SETVAL('phpbb_auth_presets_preset_id',(select case when max(preset_id)>0 then max(preset_id)+1 else 1 end from phpbb_auth_presets)); + +/* Table: phpbb_auth_users */ +CREATE TABLE phpbb_auth_users ( + user_id INT4 DEFAULT '0' NOT NULL, + forum_id INT4 DEFAULT '0' NOT NULL, + auth_option_id INT2 DEFAULT '0' NOT NULL, + auth_setting INT2 DEFAULT '0' NOT NULL, +); + +CREATE INDEX user_id_phpbb_auth_users_index ON phpbb_auth_users (user_id); +CREATE INDEX auth_option_id_phpbb_auth_users_index ON phpbb_auth_users (auth_option_id); + +/* Table: phpbb_banlist */ +CREATE SEQUENCE phpbb_banlist_ban_id_seq; -/* -------------------------------------------------------- - Table structure for table phpbb_banlist --------------------------------------------------------- */ CREATE TABLE phpbb_banlist ( - ban_id int4 DEFAULT nextval('phpbb_banlist_id_seq'::text) NOT NULL, - ban_userid int4, - ban_ip char(40), - ban_email varchar(255), - CONSTRAINT phpbb_banlist_pkey PRIMARY KEY (ban_id) + ban_id INT4 DEFAULT nextval('phpbb_banlist_ban_id_seq'), + ban_userid INT4 DEFAULT 0 NOT NULL, + ban_ip varchar(40) DEFAULT '' NOT NULL, + ban_email varchar(50) DEFAULT '' NOT NULL, + ban_start INT4 DEFAULT '0' NOT NULL, + ban_end INT4 DEFAULT '0' NOT NULL, + ban_exclude INT2 DEFAULT '0' NOT NULL, + ban_reason varchar(255) DEFAULT '' NOT NULL, + ban_give_reason varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (ban_id), + CHECK (ban_userid>=0) +); + +SELECT SETVAL('phpbb_banlist_ban_id_seq',(select case when max(ban_id)>0 then max(ban_id)+1 else 1 end from phpbb_banlist)); + +/* Table: phpbb_bbcodes */ +CREATE TABLE phpbb_bbcodes ( + bbcode_id INT2 DEFAULT '0' NOT NULL, + bbcode_tag varchar(16) DEFAULT '' NOT NULL, + bbcode_match varchar(255) DEFAULT '' NOT NULL, + bbcode_tpl text DEFAULT '' NOT NULL, + first_pass_match varchar(255) DEFAULT '' NOT NULL, + first_pass_replace varchar(255) DEFAULT '' NOT NULL, + second_pass_match varchar(255) DEFAULT '' NOT NULL, + second_pass_replace text DEFAULT '' NOT NULL, + PRIMARY KEY (bbcode_id), + CHECK (bbcode_id>=0) +); + +/* Table: phpbb_bookmarks */ +CREATE TABLE phpbb_bookmarks ( + topic_id INT4 DEFAULT '0' NOT NULL, + user_id INT4 DEFAULT '0' NOT NULL, + order_id INT4 DEFAULT '0' NOT NULL, ); -CREATE INDEX ban_userid_phpbb_banlist_index ON phpbb_banlist (ban_userid); +CREATE INDEX order_id_phpbb_bookmarks_index ON phpbb_bookmarks (order_id); +CREATE INDEX topic_user_id_phpbb_bookmarks_index ON phpbb_bookmarks (topic_id, user_id); + +/* Table: phpbb_bots */ +CREATE SEQUENCE phpbb_bots_bot_id_seq; -/* -------------------------------------------------------- - Table structure for table phpbb_categories --------------------------------------------------------- */ -CREATE TABLE phpbb_categories ( - cat_id int4 DEFAULT nextval('phpbb_categories_id_seq'::text) NOT NULL, - cat_title varchar(100), - cat_order int4, - CONSTRAINT phpbb_categories_pkey PRIMARY KEY (cat_id) +CREATE TABLE phpbb_bots ( + bot_id INT2 DEFAULT nextval('phpbb_bots_bot_id_seq'), + bot_active INT2 DEFAULT '1' NOT NULL, + bot_name varchar(255) DEFAULT '' NOT NULL, + user_id INT4 DEFAULT '0' NOT NULL, + bot_agent varchar(255) DEFAULT '' NOT NULL, + bot_ip varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (bot_id), + CHECK (user_id>=0) ); +CREATE INDEX bot_active_phpbb_bots_index ON phpbb_bots (bot_active); + +SELECT SETVAL('phpbb_bots_bot_id_seq',(select case when max(bot_id)>0 then max(bot_id)+1 else 1 end from phpbb_bots)); -/* -------------------------------------------------------- - Table structure for table phpbb_config --------------------------------------------------------- */ +/* Table: phpbb_cache */ +CREATE TABLE phpbb_cache ( + var_name varchar(255) DEFAULT '' NOT NULL, + var_expires INT4 DEFAULT '0' NOT NULL, + var_data TEXT DEFAULT '' NOT NULL, + PRIMARY KEY (var_name), + CHECK (var_expires>=0) +); + +/* Table: phpbb_config */ CREATE TABLE phpbb_config ( - config_name varchar(255) NOT NULL, - config_value varchar(255) NOT NULL, - CONSTRAINT phpbb_config_pkey PRIMARY KEY (config_name) + config_name varchar(255) NOT NULL, + config_value varchar(255) NOT NULL, + is_dynamic INT2 DEFAULT '0' NOT NULL, + PRIMARY KEY (config_name) +); + +CREATE INDEX is_dynamic_phpbb_config_index 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, + code char(6) DEFAULT '' NOT NULL, + PRIMARY KEY (session_id,confirm_id) ); +/* Table: phpbb_disallow */ +CREATE SEQUENCE phpbb_disallow_disallow_id_s; -/* -------------------------------------------------------- - Table structure for table phpbb_disallow --------------------------------------------------------- */ CREATE TABLE phpbb_disallow ( - disallow_id int4 DEFAULT nextval('phpbb_disallow_id_seq'::text) NOT NULL, - disallow_username varchar(25), - CONSTRAINT phpbb_disallow_pkey PRIMARY KEY (disallow_id) + disallow_id INT4 DEFAULT nextval('phpbb_disallow_disallow_id_s'), + disallow_username varchar(30) DEFAULT '' NOT NULL, + PRIMARY KEY (disallow_id) ); +SELECT SETVAL('phpbb_disallow_disallow_id_s',(select case when max(disallow_id)>0 then max(disallow_id)+1 else 1 end from phpbb_disallow)); + +/* Table: phpbb_drafts */ +CREATE SEQUENCE phpbb_drafts_draft_id_seq; + +CREATE TABLE phpbb_drafts ( + draft_id INT4 DEFAULT nextval('phpbb_drafts_draft_id_seq'), + user_id INT4 DEFAULT '0' NOT NULL, + topic_id INT4 DEFAULT '0' NOT NULL, + forum_id INT4 DEFAULT '0' NOT NULL, + save_time INT4 DEFAULT '0' NOT NULL, + draft_subject varchar(60), + draft_message text DEFAULT '' NOT NULL, + PRIMARY KEY (draft_id), + CHECK (user_id>=0), + CHECK (topic_id>=0), + CHECK (forum_id>=0), + CHECK (save_time>=0) +); + +CREATE INDEX save_time_phpbb_drafts_index ON phpbb_drafts (save_time); + +SELECT SETVAL('phpbb_drafts_draft_id_seq',(select case when max(draft_id)>0 then max(draft_id)+1 else 1 end from phpbb_drafts)); + +/* Table: phpbb_extensions */ +CREATE SEQUENCE phpbb_extensions_extension_i; + +CREATE TABLE phpbb_extensions ( + extension_id INT4 DEFAULT nextval('phpbb_extensions_extension_i'), + group_id INT4 DEFAULT '0' NOT NULL, + extension varchar(100) DEFAULT '' NOT NULL, + PRIMARY KEY (extension_id), + CHECK (group_id>=0) +); + +SELECT SETVAL('phpbb_extensions_extension_i',(select case when max(extension_id)>0 then max(extension_id)+1 else 1 end from phpbb_extensions)); + +/* Table: phpbb_extension_groups */ +CREATE SEQUENCE phpbb_extension_groups_group; + +CREATE TABLE phpbb_extension_groups ( + group_id INT4 DEFAULT nextval('phpbb_extension_groups_group'), + group_name char(20) NOT NULL, + cat_id INT2 DEFAULT '0' NOT NULL, + allow_group INT2 DEFAULT '0' NOT NULL, + download_mode INT2 DEFAULT '1' NOT NULL, + upload_icon varchar(100) DEFAULT '' NOT NULL, + max_filesize INT4 DEFAULT '0' NOT NULL, + allowed_forums TEXT DEFAULT '' NOT NULL, + allow_in_pm INT2 DEFAULT '0' NOT NULL, + PRIMARY KEY (group_id), + CHECK (download_mode>=0) +); + +SELECT SETVAL('phpbb_extension_groups_group',(select case when max(group_id)>0 then max(group_id)+1 else 1 end from phpbb_extension_groups)); + +/* Table: phpbb_forums */ +CREATE SEQUENCE phpbb_forums_forum_id_seq; -/* -------------------------------------------------------- - Table structure for table phpbb_forums --------------------------------------------------------- */ CREATE TABLE phpbb_forums ( - forum_id int4 DEFAULT '0' NOT NULL, - cat_id int4, - forum_name varchar(150), + forum_id INT2 DEFAULT nextval('phpbb_forums_forum_id_seq'), + parent_id INT2 NOT NULL, + left_id INT2 NOT NULL, + right_id INT2 NOT NULL, + forum_parents text, + forum_name varchar(150) NOT NULL, forum_desc text, - forum_status int2 DEFAULT '0' NOT NULL, - forum_order int4 DEFAULT '1' NOT NULL, - forum_posts int4 DEFAULT '0' NOT NULL, - forum_topics int4 DEFAULT '0' NOT NULL, - forum_last_post_id int4 DEFAULT '0' NOT NULL, - prune_enable int2 DEFAULT '0' NOT NULL, - prune_next int, - auth_view int2 DEFAULT '0' NOT NULL, - auth_read int2 DEFAULT '0' NOT NULL, - auth_post int2 DEFAULT '0' NOT NULL, - auth_reply int2 DEFAULT '0' NOT NULL, - auth_edit int2 DEFAULT '0' NOT NULL, - auth_delete int2 DEFAULT '0' NOT NULL, - auth_announce int2 DEFAULT '0' NOT NULL, - auth_sticky int2 DEFAULT '0' NOT NULL, - auth_pollcreate int2 DEFAULT '0' NOT NULL, - auth_vote int2 DEFAULT '0' NOT NULL, - auth_attachments int2 DEFAULT '0' NOT NULL, - CONSTRAINT phpbb_forums_pkey PRIMARY KEY (forum_id) -); -CREATE INDEX cat_id_phpbb_forums_index ON phpbb_forums (cat_id); -CREATE INDEX forum_id_phpbb_forums_index ON phpbb_forums (forum_id); -CREATE INDEX forums_order_phpbb_forums_index ON phpbb_forums (forum_order); -CREATE INDEX forum_last_post_id_phpbb_forums_index ON phpbb_forums (forum_last_post_id); - - -/* -------------------------------------------------------- - Table structure for table phpbb_forums_watch --------------------------------------------------------- */ + forum_link varchar(200) DEFAULT '' NOT NULL, + forum_password varchar(32) DEFAULT '' NOT NULL, + forum_style INT2 , + forum_image varchar(50) DEFAULT '' NOT NULL, + forum_rules text DEFAULT '' NOT NULL, + forum_rules_link varchar(200) DEFAULT '' NOT NULL, + forum_rules_flags INT2 DEFAULT '0' NOT NULL, + forum_rules_bbcode_bitfield INT4 DEFAULT '0' NOT NULL, + forum_rules_bbcode_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, + forum_topics INT4 DEFAULT '0' NOT NULL, + forum_topics_real INT4 DEFAULT '0' NOT NULL, + forum_last_post_id INT4 DEFAULT '0' NOT NULL, + forum_last_poster_id INT4 DEFAULT '0' NOT NULL, + forum_last_post_time INT4 DEFAULT '0' NOT NULL, + forum_last_poster_name varchar(30), + forum_flags INT2 DEFAULT '0' NOT NULL, + display_on_index INT2 DEFAULT '1' NOT NULL, + enable_indexing INT2 DEFAULT '1' NOT NULL, + enable_icons INT2 DEFAULT '1' NOT NULL, + enable_prune INT2 DEFAULT '0' NOT NULL, + prune_next INT4 , + prune_days INT2 NOT NULL, + prune_viewed INT2 NOT NULL, + prune_freq INT2 DEFAULT '0' NOT NULL, + PRIMARY KEY (forum_id), + CHECK (parent_id>=0), + CHECK (left_id>=0), + CHECK (right_id>=0), + CHECK (forum_style>=0), + CHECK (forum_rules_flags>=0), + CHECK (forum_rules_bbcode_bitfield>=0), + CHECK (forum_topics_per_page>=0), + CHECK (forum_posts>=0), + CHECK (forum_topics>=0), + CHECK (forum_topics_real>=0), + CHECK (forum_last_post_id>=0), + CHECK (prune_next>=0), + CHECK (prune_days>=0), + CHECK (prune_viewed>=0), + CHECK (prune_freq>=0) +); + +CREATE INDEX left_right_id_phpbb_forums_index ON phpbb_forums (left_id, right_id); +CREATE INDEX forum_last_post_id_phpbb_forums_index ON phpbb_forums (forum_last_post_id); + +SELECT SETVAL('phpbb_forums_forum_id_seq',(select case when max(forum_id)>0 then max(forum_id)+1 else 1 end from phpbb_forums)); + +/* Table: phpbb_forum_access */ +CREATE TABLE phpbb_forum_access ( + forum_id INT4 DEFAULT '0' NOT NULL, + user_id INT4 DEFAULT '0' NOT NULL, + session_id char(32) DEFAULT '' NOT NULL, + PRIMARY KEY (forum_id,user_id,session_id), + CHECK (forum_id>=0), + CHECK (user_id>=0) +); + +/* Table: phpbb_forums_marking */ +CREATE TABLE phpbb_forums_marking ( + user_id INT4 DEFAULT '0' NOT NULL, + forum_id INT4 DEFAULT '0' NOT NULL, + mark_time INT4 DEFAULT '0' NOT NULL, + PRIMARY KEY (user_id,forum_id), + CHECK (user_id>=0), + CHECK (forum_id>=0) +); + +/* Table: phpbb_forums_watch */ CREATE TABLE phpbb_forums_watch ( - forum_id int4, - user_id int4, - notify_status int2 NOT NULL default '0' + forum_id INT2 DEFAULT '0' NOT NULL, + user_id INT4 DEFAULT '0' NOT NULL, + notify_status INT2 DEFAULT '0' NOT NULL, ); -CREATE INDEX forum_id_phpbb_forums_watch_index ON phpbb_forums_watch (forum_id); -CREATE INDEX user_id_phpbb_forums_watch_index ON phpbb_forums_watch (user_id); +CREATE INDEX forum_id_phpbb_forums_watch_index ON phpbb_forums_watch (forum_id); +CREATE INDEX user_id_phpbb_forums_watch_index ON phpbb_forums_watch (user_id); +CREATE INDEX notify_status_phpbb_forums_watch_index ON phpbb_forums_watch (notify_status); + +/* Table: phpbb_groups */ +CREATE SEQUENCE phpbb_groups_group_id_seq; -/* -------------------------------------------------------- - Table structure for table phpbb_forum_prune --------------------------------------------------------- */ -CREATE TABLE phpbb_forum_prune ( - prune_id int4 DEFAULT nextval('phpbb_forum_prune_id_seq'::text) NOT NULL, - forum_id int4 NOT NULL, - prune_days int4 NOT NULL, - prune_freq int4 NOT NULL, - CONSTRAINT phpbb_forum_prune_pkey PRIMARY KEY (prune_id) +CREATE TABLE phpbb_groups ( + group_id INT4 DEFAULT nextval('phpbb_groups_group_id_seq'), + group_type INT2 DEFAULT '1' NOT NULL, + group_name varchar(40) DEFAULT '' NOT NULL, + group_display INT2 DEFAULT '0' NOT NULL, + group_avatar varchar(100) 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 INT2 DEFAULT '-1' NOT NULL, + group_colour varchar(6) DEFAULT '' NOT NULL, + group_sig_chars INT4 DEFAULT '0' NOT NULL, + group_receive_pm INT2 DEFAULT '0' NOT NULL, + group_message_limit INT4 DEFAULT '0' NOT NULL, + group_chgpass INT2 DEFAULT '0' NOT NULL, + group_description varchar(255) DEFAULT '' NOT NULL, + group_legend INT2 DEFAULT '1' NOT NULL, + PRIMARY KEY (group_id), + CHECK (group_avatar_width>=0), + CHECK (group_avatar_height>=0), + CHECK (group_sig_chars>=0), + CHECK (group_message_limit>=0) ); -CREATE INDEX prune_id_phpbb_forum_prune_index ON phpbb_forum_prune (prune_id); -CREATE INDEX forum_id_phpbb_forum_prune_index ON phpbb_forum_prune (forum_id); +CREATE INDEX group_legend_phpbb_groups_index ON phpbb_groups (group_legend); + +SELECT SETVAL('phpbb_groups_group_id_seq',(select case when max(group_id)>0 then max(group_id)+1 else 1 end from phpbb_groups)); + +/* Table: phpbb_icons */ +CREATE SEQUENCE phpbb_icons_icons_id_seq; + +CREATE TABLE phpbb_icons ( + icons_id INT2 DEFAULT nextval('phpbb_icons_icons_id_seq'), + icons_url varchar(50), + icons_width INT2 NOT NULL, + icons_height INT2 NOT NULL, + icons_order INT2 NOT NULL, + display_on_posting INT2 DEFAULT '1' NOT NULL, + PRIMARY KEY (icons_id), + CHECK (icons_width>=0), + CHECK (icons_height>=0), + CHECK (icons_order>=0), + CHECK (display_on_posting>=0) +); + +SELECT SETVAL('phpbb_icons_icons_id_seq',(select case when max(icons_id)>0 then max(icons_id)+1 else 1 end from phpbb_icons)); + +/* Table: phpbb_lang */ +CREATE SEQUENCE phpbb_lang_lang_id_seq; + +CREATE TABLE phpbb_lang ( + lang_id INT2 DEFAULT nextval('phpbb_lang_lang_id_seq'), + lang_iso varchar(5) NOT NULL, + lang_dir varchar(30) NOT NULL, + lang_english_name varchar(30), + lang_local_name varchar(100), + lang_author varchar(100), + PRIMARY KEY (lang_id) +); + +SELECT SETVAL('phpbb_lang_lang_id_seq',(select case when max(lang_id)>0 then max(lang_id)+1 else 1 end from phpbb_lang)); + +/* Table: phpbb_log */ +CREATE SEQUENCE phpbb_log_log_id_seq; + +CREATE TABLE phpbb_log ( + log_id INT4 DEFAULT nextval('phpbb_log_log_id_seq'), + log_type INT2 DEFAULT '0' NOT NULL, + user_id INT4 DEFAULT '0' NOT NULL, + forum_id INT4 DEFAULT '0' NOT NULL, + topic_id INT4 DEFAULT '0' NOT NULL, + reportee_id INT4 DEFAULT '0' NOT NULL, + log_ip varchar(40) NOT NULL, + log_time INT4 NOT NULL, + log_operation text, + log_data text, + PRIMARY KEY (log_id), + CHECK (log_type>=0), + CHECK (forum_id>=0), + CHECK (topic_id>=0), + CHECK (reportee_id>=0) +); + +CREATE INDEX log_type_phpbb_log_index ON phpbb_log (log_type); +CREATE INDEX forum_id_phpbb_log_index ON phpbb_log (forum_id); +CREATE INDEX topic_id_phpbb_log_index ON phpbb_log (topic_id); +CREATE INDEX reportee_id_phpbb_log_index ON phpbb_log (reportee_id); +CREATE INDEX user_id_phpbb_log_index ON phpbb_log (user_id); + +SELECT SETVAL('phpbb_log_log_id_seq',(select case when max(log_id)>0 then max(log_id)+1 else 1 end from phpbb_log)); + +/* Table: phpbb_moderator_cache */ +CREATE TABLE phpbb_moderator_cache ( + forum_id INT4 NOT NULL, + user_id INT4 DEFAULT '0' NOT NULL, + username char(30) DEFAULT '' NOT NULL, + group_id INT4 DEFAULT '0' NOT NULL, + groupname char(30) DEFAULT '' NOT NULL, + display_on_index INT2 DEFAULT '1' NOT NULL, +); + +CREATE INDEX display_on_index_phpbb_moderator_cache_index ON phpbb_moderator_cache (display_on_index); +CREATE INDEX forum_id_phpbb_moderator_cache_index ON phpbb_moderator_cache (forum_id); + +/* Table: phpbb_modules */ +CREATE SEQUENCE phpbb_modules_module_id_seq; + +CREATE TABLE phpbb_modules ( + module_id INT4 DEFAULT nextval('phpbb_modules_module_id_seq'), + module_type char(3) DEFAULT '' NOT NULL, + module_title varchar(50) DEFAULT '' NOT NULL, + module_filename varchar(50) DEFAULT '' NOT NULL, + module_order INT4 DEFAULT '0' NOT NULL, + module_enabled INT2 DEFAULT '1' NOT NULL, + module_subs TEXT DEFAULT '' NOT NULL, + module_acl varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (module_id), + CHECK (module_enabled>=0) +); + +CREATE INDEX module_type_phpbb_modules_index ON phpbb_modules (module_type,module_enabled); + +SELECT SETVAL('phpbb_modules_module_id_seq',(select case when max(module_id)>0 then max(module_id)+1 else 1 end from phpbb_modules)); + +/* Table: phpbb_poll_results */ +CREATE TABLE phpbb_poll_results ( + poll_option_id INT2 DEFAULT '0' NOT NULL, + topic_id INT4 NOT NULL, + poll_option_text varchar(255) NOT NULL, + poll_option_total INT4 DEFAULT '0' NOT NULL, +); + +CREATE INDEX poll_option_id_phpbb_poll_results_index ON phpbb_poll_results (poll_option_id); +CREATE INDEX topic_id_phpbb_poll_results_index ON phpbb_poll_results (topic_id); + +/* Table: phpbb_poll_voters */ +CREATE TABLE phpbb_poll_voters ( + topic_id INT4 DEFAULT '0' NOT NULL, + poll_option_id INT2 DEFAULT '0' NOT NULL, + vote_user_id INT4 DEFAULT '0' NOT NULL, + vote_user_ip varchar(40) NOT NULL, +); + +CREATE INDEX topic_id_phpbb_poll_voters_index ON phpbb_poll_voters (topic_id); +CREATE INDEX vote_user_id_phpbb_poll_voters_index ON phpbb_poll_voters (vote_user_id); +CREATE INDEX vote_user_ip_phpbb_poll_voters_index ON phpbb_poll_voters (vote_user_ip); + +/* Table: phpbb_posts */ +CREATE SEQUENCE phpbb_posts_post_id_seq; -/* -------------------------------------------------------- - Table structure for table phpbb_posts --------------------------------------------------------- */ CREATE TABLE phpbb_posts ( - post_id int4 DEFAULT nextval('phpbb_posts_id_seq'::text) NOT NULL, - topic_id int4 DEFAULT '0' NOT NULL, - forum_id int4 DEFAULT '0' NOT NULL, - poster_id int4 DEFAULT '0' NOT NULL, - post_time int4 DEFAULT '0' NOT NULL, - post_username varchar(25), - poster_ip char(40) DEFAULT '' NOT NULL, - enable_bbcode int2 DEFAULT '1' NOT NULL, - enable_html int2 DEFAULT '0' NOT NULL, - enable_smilies int2 DEFAULT '1' NOT NULL, - enable_sig int2 DEFAULT '1' NOT NULL, - post_edit_time int4, - post_edit_count int2 DEFAULT '0' NOT NULL, - CONSTRAINT phpbb_posts_pkey PRIMARY KEY (post_id) -); -CREATE INDEX forum_id_phpbb_posts_index ON phpbb_posts (forum_id); -CREATE INDEX post_time_phpbb_posts_index ON phpbb_posts (post_time); -CREATE INDEX poster_id_phpbb_posts_index ON phpbb_posts (poster_id); -CREATE INDEX topic_id_phpbb_posts_index ON phpbb_posts (topic_id); - - -/* -------------------------------------------------------- - Table structure for table phpbb_posts_text --------------------------------------------------------- */ -CREATE TABLE phpbb_posts_text ( - post_id int4 DEFAULT '0' NOT NULL, - bbcode_uid varchar(10) DEFAULT '' NOT NULL, + post_id INT4 DEFAULT nextval('phpbb_posts_post_id_seq'), + topic_id INT4 DEFAULT '0' NOT NULL, + forum_id INT2 DEFAULT '0' NOT NULL, + poster_id INT4 DEFAULT '0' NOT NULL, + icon_id INT2 DEFAULT '1' NOT NULL, + poster_ip varchar(40) NOT NULL, + post_time INT4 DEFAULT '0' NOT NULL, + post_approved INT2 DEFAULT '1' NOT NULL, + post_reported INT2 DEFAULT '0' NOT NULL, + enable_bbcode INT2 DEFAULT '1' NOT NULL, + enable_html INT2 DEFAULT '0' NOT NULL, + enable_smilies INT2 DEFAULT '1' NOT NULL, + enable_magic_url INT2 DEFAULT '1' NOT NULL, + enable_sig INT2 DEFAULT '1' NOT NULL, + post_username varchar(30), post_subject varchar(60), post_text text, - CONSTRAINT phpbb_posts_text_pkey PRIMARY KEY (post_id) + post_checksum varchar(32) NOT NULL, + post_encoding varchar(11) DEFAULT 'iso-8859-15' NOT NULL, + post_attachment INT2 DEFAULT '0' NOT NULL, + bbcode_bitfield INT4 DEFAULT '0' NOT NULL, + bbcode_uid varchar(5) DEFAULT '' NOT NULL, + post_edit_time INT4 DEFAULT '0' NOT NULL, + post_edit_reason varchar(100), + post_edit_user INT4 DEFAULT '0' NOT NULL, + post_edit_count INT2 DEFAULT '0' NOT NULL, + post_edit_locked INT2 DEFAULT '0' NOT NULL, + PRIMARY KEY (post_id), + CHECK (topic_id>=0), + CHECK (forum_id>=0), + CHECK (poster_id>=0), + CHECK (icon_id>=0), + CHECK (bbcode_bitfield>=0), + CHECK (post_edit_time>=0), + CHECK (post_edit_user>=0), + CHECK (post_edit_count>=0), + CHECK (post_edit_locked>=0) ); +CREATE INDEX forum_id_phpbb_posts_index ON phpbb_posts (forum_id); +CREATE INDEX topic_id_phpbb_posts_index ON phpbb_posts (topic_id); +CREATE INDEX poster_ip_phpbb_posts_index ON phpbb_posts (poster_ip); +CREATE INDEX poster_id_phpbb_posts_index ON phpbb_posts (poster_id); +CREATE INDEX post_approved_phpbb_posts_index ON phpbb_posts (post_approved); +CREATE INDEX post_time_phpbb_posts_index ON phpbb_posts (post_time); + +SELECT SETVAL('phpbb_posts_post_id_seq',(select case when max(post_id)>0 then max(post_id)+1 else 1 end from phpbb_posts)); + +/* Table: phpbb_privmsgs */ +CREATE SEQUENCE phpbb_privmsgs_msg_id_seq; -/* -------------------------------------------------------- - Table structure for table phpbb_privmsgs --------------------------------------------------------- */ CREATE TABLE phpbb_privmsgs ( - privmsgs_id int4 DEFAULT nextval('phpbb_privmsgs_id_seq'::text) NOT NULL, - privmsgs_type int2 DEFAULT '0' NOT NULL, - privmsgs_subject varchar(255) DEFAULT '0' NOT NULL, - privmsgs_from_userid int4 DEFAULT '0' NOT NULL, - privmsgs_to_userid int4 DEFAULT '0' NOT NULL, - privmsgs_date int4 DEFAULT '0' NOT NULL, - privmsgs_ip char(40) NOT NULL, - privmsgs_enable_bbcode int2 DEFAULT '1' NOT NULL, - privmsgs_enable_html int2 DEFAULT '0' NOT NULL, - privmsgs_enable_smilies int2 DEFAULT '1' NOT NULL, - privmsgs_attach_sig int2 DEFAULT '1' NOT NULL, - CONSTRAINT phpbb_privmsgs_pkey PRIMARY KEY (privmsgs_id) -); -CREATE INDEX privmsgs_from_userid_index ON phpbb_privmsgs (privmsgs_from_userid); -CREATE INDEX privmsgs_to_userid_index ON phpbb_privmsgs (privmsgs_to_userid); - - -/* -------------------------------------------------------- - Table structure for table phpbb_privmsgs_text --------------------------------------------------------- */ -CREATE TABLE phpbb_privmsgs_text ( - privmsgs_text_id int4 DEFAULT '0' NOT NULL, - privmsgs_bbcode_uid char(10) DEFAULT '0' NOT NULL, - privmsgs_text text, - CONSTRAINT phpbb_privmsgs_text_pkey PRIMARY KEY (privmsgs_text_id) -); - - -/* -------------------------------------------------------- - Table structure for table phpbb_ranks --------------------------------------------------------- */ + msg_id INT4 DEFAULT nextval('phpbb_privmsgs_msg_id_seq'), + root_level INT4 DEFAULT '0' NOT NULL, + author_id INT4 DEFAULT '0' NOT NULL, + icon_id INT2 DEFAULT '1' NOT NULL, + author_ip varchar(40) DEFAULT '' NOT NULL, + message_time INT4 DEFAULT '0' NOT NULL, + message_reported INT2 DEFAULT '0' NOT NULL, + enable_bbcode INT2 DEFAULT '1' NOT NULL, + enable_html INT2 DEFAULT '0' NOT NULL, + enable_smilies INT2 DEFAULT '1' NOT NULL, + enable_magic_url INT2 DEFAULT '1' NOT NULL, + enable_sig INT2 DEFAULT '1' NOT NULL, + message_subject varchar(60), + message_text text, + message_edit_reason varchar(100), + message_edit_user INT4 DEFAULT '0' NOT NULL, + message_checksum varchar(32) DEFAULT '' NOT NULL, + message_encoding varchar(11) DEFAULT 'iso-8859-15' NOT NULL, + message_attachment INT2 DEFAULT '0' NOT NULL, + bbcode_bitfield INT4 DEFAULT '0' NOT NULL, + bbcode_uid varchar(5) DEFAULT '' NOT NULL, + message_edit_time INT4 DEFAULT '0' NOT NULL, + message_edit_count INT2 DEFAULT '0' NOT NULL, + to_address text, + bcc_address text, + PRIMARY KEY (msg_id), + CHECK (root_level>=0), + CHECK (author_id>=0), + CHECK (icon_id>=0), + CHECK (message_edit_user>=0), + CHECK (bbcode_bitfield>=0), + CHECK (message_edit_time>=0), + CHECK (message_edit_count>=0) +); + +CREATE INDEX author_ip_phpbb_privmsgs_index ON phpbb_privmsgs (author_ip); +CREATE INDEX message_time_phpbb_privmsgs_index ON phpbb_privmsgs (message_time); +CREATE INDEX author_id_phpbb_privmsgs_index ON phpbb_privmsgs (author_id); +CREATE INDEX root_level_phpbb_privmsgs_index ON phpbb_privmsgs (root_level); + +SELECT SETVAL('phpbb_privmsgs_msg_id_seq',(select case when max(msg_id)>0 then max(msg_id)+1 else 1 end from phpbb_privmsgs)); + +/* Table: phpbb_privmsgs_folder */ +CREATE SEQUENCE phpbb_privmsgs_folder_folder; + +CREATE TABLE phpbb_privmsgs_folder ( + folder_id INT4 DEFAULT nextval('phpbb_privmsgs_folder_folder'), + user_id INT4 DEFAULT '0' NOT NULL, + folder_name varchar(40) DEFAULT '' NOT NULL, + pm_count INT4 DEFAULT '0' NOT NULL, + PRIMARY KEY (folder_id), + CHECK (user_id>=0), + CHECK (pm_count>=0) +); + +CREATE INDEX user_id_phpbb_privmsgs_folder_index ON phpbb_privmsgs_folder (user_id); + +SELECT SETVAL('phpbb_privmsgs_folder_folder',(select case when max(folder_id)>0 then max(folder_id)+1 else 1 end from phpbb_privmsgs_folder)); + +/* Table: phpbb_privmsgs_rules */ +CREATE SEQUENCE phpbb_privmsgs_rules_rule_id; + +CREATE TABLE phpbb_privmsgs_rules ( + rule_id INT4 DEFAULT nextval('phpbb_privmsgs_rules_rule_id'), + user_id INT4 DEFAULT '0' NOT NULL, + rule_check INT4 DEFAULT '0' NOT NULL, + rule_connection INT4 DEFAULT '0' NOT NULL, + rule_string varchar(255) DEFAULT '' NOT NULL, + rule_user_id INT4 DEFAULT '0' NOT NULL, + rule_group_id INT4 DEFAULT '0' NOT NULL, + rule_action INT4 DEFAULT '0' NOT NULL, + rule_folder_id INT4 DEFAULT '0' NOT NULL, + PRIMARY KEY (rule_id), + CHECK (user_id>=0), + CHECK (rule_check>=0), + CHECK (rule_connection>=0), + CHECK (rule_user_id>=0), + CHECK (rule_group_id>=0), + CHECK (rule_action>=0), + CHECK (rule_folder_id>=0) +); + +SELECT SETVAL('phpbb_privmsgs_rules_rule_id',(select case when max(rule_id)>0 then max(rule_id)+1 else 1 end from phpbb_privmsgs_rules)); + +/* Table: phpbb_privmsgs_to */ +CREATE TABLE phpbb_privmsgs_to ( + msg_id INT4 DEFAULT '0' NOT NULL, + user_id INT4 DEFAULT '0' NOT NULL, + author_id INT4 DEFAULT '0' NOT NULL, + deleted INT2 DEFAULT '0' NOT NULL, + new INT2 DEFAULT '1' NOT NULL, + unread INT2 DEFAULT '1' NOT NULL, + replied INT2 DEFAULT '0' NOT NULL, + marked INT2 DEFAULT '0' NOT NULL, + forwarded INT2 DEFAULT '0' NOT NULL, + folder_id INT4 DEFAULT '0' NOT NULL, +); + +CREATE INDEX msg_id_phpbb_privmsgs_to_index ON phpbb_privmsgs_to (msg_id); +CREATE INDEX user_id_phpbb_privmsgs_to_index ON phpbb_privmsgs_to (user_id,folder_id); + +/* Table: phpbb_profile_fields */ +CREATE SEQUENCE phpbb_profile_fields_field_i; + +CREATE TABLE phpbb_profile_fields ( + field_id INT4 DEFAULT nextval('phpbb_profile_fields_field_i'), + field_name varchar(50) DEFAULT '' NOT NULL, + field_desc varchar(255) DEFAULT '' NOT NULL, + field_type INT4 NOT NULL, + field_ident varchar(20) DEFAULT '' NOT NULL, + field_length varchar(20) DEFAULT '' NOT NULL, + field_minlen varchar(255) DEFAULT '' NOT NULL, + field_maxlen varchar(255) DEFAULT '' NOT NULL, + field_novalue varchar(255) DEFAULT '' NOT NULL, + field_DEFAULT_value varchar(255) DEFAULT '0' NOT NULL, + field_validation varchar(20) DEFAULT '' NOT NULL, + field_required INT2 DEFAULT '0' NOT NULL, + field_show_on_reg INT2 DEFAULT '0' NOT NULL, + field_hide INT2 DEFAULT '0' NOT NULL, + field_active INT2 DEFAULT '0' NOT NULL, + field_order INT2 DEFAULT '0' NOT NULL, + PRIMARY KEY (field_id), + CHECK (field_type>=0), + CHECK (field_required>=0), + CHECK (field_show_on_reg>=0), + CHECK (field_hide>=0), + CHECK (field_active>=0), + CHECK (field_order>=0) +); + +CREATE INDEX field_type_phpbb_profile_fields_index ON phpbb_profile_fields (field_type); +CREATE INDEX field_order_phpbb_profile_fields_index ON phpbb_profile_fields (field_order); + +SELECT SETVAL('phpbb_profile_fields_field_i',(select case when max(field_id)>0 then max(field_id)+1 else 1 end from phpbb_profile_fields)); + +/* Table: phpbb_profile_fields_data */ +CREATE TABLE phpbb_profile_fields_data ( + user_id INT4 DEFAULT '0' NOT NULL, + PRIMARY KEY (user_id), + CHECK (user_id>=0) +); + +/* Table: phpbb_profile_fields_lang */ +CREATE TABLE phpbb_profile_fields_lang ( + field_id INT4 DEFAULT '0' NOT NULL, + lang_id INT4 DEFAULT '0' NOT NULL, + option_id INT4 DEFAULT '0' NOT NULL, + field_type INT2 DEFAULT '0' NOT NULL, + value varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (field_id,lang_id,option_id), + CHECK (field_id>=0), + CHECK (lang_id>=0), + CHECK (option_id>=0) +); + +/* Table: phpbb_profile_lang */ +CREATE TABLE phpbb_profile_lang ( + field_id INT4 DEFAULT '0' NOT NULL, + lang_id INT2 DEFAULT '0' NOT NULL, + lang_name varchar(255) DEFAULT '' NOT NULL, + lang_explain TEXT DEFAULT '' NOT NULL, + lang_DEFAULT_value varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (field_id,lang_id), + CHECK (field_id>=0), + CHECK (lang_id>=0) +); + +/* Table: phpbb_ranks */ +CREATE SEQUENCE phpbb_ranks_rank_id_seq; + CREATE TABLE phpbb_ranks ( - rank_id int4 DEFAULT nextval('phpbb_ranks_id_seq'::text) NOT NULL, - rank_title varchar(50) DEFAULT '' NOT NULL, - rank_min int4 DEFAULT '0' NOT NULL, - rank_special int2 DEFAULT '0', - rank_image varchar(255), - CONSTRAINT phpbb_ranks_pkey PRIMARY KEY (rank_id) + rank_id INT2 DEFAULT nextval('phpbb_ranks_rank_id_seq'), + rank_title varchar(50) NOT NULL, + rank_min INT4 DEFAULT '0' NOT NULL, + rank_special INT2 DEFAULT '0', + rank_image varchar(100), + PRIMARY KEY (rank_id) +); + +SELECT SETVAL('phpbb_ranks_rank_id_seq',(select case when max(rank_id)>0 then max(rank_id)+1 else 1 end from phpbb_ranks)); + +/* Table: phpbb_ratings */ +CREATE TABLE phpbb_ratings ( + post_id INT4 DEFAULT '0' NOT NULL, + user_id INT2 UNSIGNED DEFAULT '0' NOT NULL, + rating INT2 DEFAULT '0' NOT NULL, ); +CREATE INDEX post_id_phpbb_ratings_index ON phpbb_ratings (post_id); +CREATE INDEX user_id_phpbb_ratings_index ON phpbb_ratings (user_id); -/* -------------------------------------------------------- - Table structure for table phpbb_search_results --------------------------------------------------------- */ +/* Table: phpbb_reports_reasons */ +CREATE SEQUENCE phpbb_reports_reasons_reason; + +CREATE TABLE phpbb_reports_reasons ( + reason_id INT2 DEFAULT nextval('phpbb_reports_reasons_reason'), + reason_priority INT2 DEFAULT '0' NOT NULL, + reason_name varchar(255) DEFAULT '' NOT NULL, + reason_description TEXT DEFAULT '' NOT NULL, + PRIMARY KEY (reason_id) +); + +SELECT SETVAL('phpbb_reports_reasons_reason',(select case when max(reason_id)>0 then max(reason_id)+1 else 1 end from phpbb_reports_reasons)); + +/* Table: phpbb_reports */ +CREATE SEQUENCE phpbb_reports_report_id_seq; + +CREATE TABLE phpbb_reports ( + report_id INT2 DEFAULT nextval('phpbb_reports_report_id_seq'), + reason_id INT2 DEFAULT '0' NOT NULL, + post_id INT4 DEFAULT '0' NOT NULL, + msg_id INT4 DEFAULT '0' NOT NULL, + user_id INT4 DEFAULT '0' NOT NULL, + user_notify INT2 DEFAULT '0' NOT NULL, + report_time INT4 DEFAULT '0' NOT NULL, + report_text TEXT DEFAULT '' NOT NULL, + PRIMARY KEY (report_id), + CHECK (reason_id>=0), + CHECK (post_id>=0), + CHECK (msg_id>=0), + CHECK (user_id>=0), + CHECK (report_time>=0) +); + +SELECT SETVAL('phpbb_reports_report_id_seq',(select case when max(report_id)>0 then max(report_id)+1 else 1 end from phpbb_reports)); + +/* Table: phpbb_search_results */ CREATE TABLE phpbb_search_results ( - search_id int4 NOT NULL default '0', - session_id char(32) NOT NULL default '', - search_array text NOT NULL, - CONSTRAINT phpbb_search_results_pkey PRIMARY KEY (search_id) + search_id INT4 DEFAULT '0' NOT NULL, + session_id varchar(32) DEFAULT '' NOT NULL, + search_time INT4 DEFAULT '0' NOT NULL, + search_array TEXT DEFAULT '' NOT NULL, + PRIMARY KEY (search_id), + CHECK (search_id>=0) ); -CREATE INDEX session_id_phpbb_search_results ON phpbb_search_results (session_id); +CREATE INDEX session_id_phpbb_search_results_index ON phpbb_search_results (session_id); + +/* Table: phpbb_search_wordlist */ +CREATE SEQUENCE phpbb_search_wordlist_word_i; -/* -------------------------------------------------------- - Table structure for table phpbb_search_wordlist --------------------------------------------------------- */ CREATE TABLE phpbb_search_wordlist ( - word_id int4 DEFAULT nextval('phpbb_search_wordlist_id_seq'::text) NOT NULL, - word_text varchar(50) NOT NULL DEFAULT '', - word_common int2 NOT NULL DEFAULT '0', - CONSTRAINT phpbb_search_wordlist_pkey PRIMARY KEY (word_text) + word_text varchar(50) BINARY DEFAULT '' NOT NULL, + word_id INT4 DEFAULT nextval('phpbb_search_wordlist_word_i'), + word_common INT2 DEFAULT '0' NOT NULL, + PRIMARY KEY (word_text), + CHECK (word_common>=0) ); -CREATE INDEX word_id_phpbb_search_wordlist ON phpbb_search_wordlist (word_id); +CREATE INDEX word_id_phpbb_search_wordlist_index ON phpbb_search_wordlist (word_id); -/* -------------------------------------------------------- - Table structure for table phpbb_search_wordmatch --------------------------------------------------------- */ +SELECT SETVAL('phpbb_search_wordlist_word_i',(select case when max(word_id)>0 then max(word_id)+1 else 1 end from phpbb_search_wordlist)); + +/* Table: phpbb_search_wordmatch */ CREATE TABLE phpbb_search_wordmatch ( - post_id int4 NOT NULL default '0', - word_id int4 NOT NULL default '0', - title_match int2 NOT NULL default '0' + post_id INT4 DEFAULT '0' NOT NULL, + word_id INT4 DEFAULT '0' NOT NULL, + title_match INT2 DEFAULT '0' NOT NULL, ); -CREATE INDEX word_id_phpbb_search_wordmatch ON phpbb_search_wordmatch (word_id); +CREATE INDEX word_id_phpbb_search_wordmatch_index ON phpbb_search_wordmatch (word_id); -/* -------------------------------------------------------- - Table structure for table phpbb_sessions --------------------------------------------------------- */ +/* Table: phpbb_sessions */ CREATE TABLE phpbb_sessions ( - session_id char(32) DEFAULT '0' NOT NULL, - session_user_id int4 DEFAULT '0' NOT NULL, - session_start int4 DEFAULT '0' NOT NULL, - session_time int4 DEFAULT '0' NOT NULL, - session_ip char(40) DEFAULT '0' NOT NULL, - session_page int4 DEFAULT '0' NOT NULL, - session_logged_in int2 DEFAULT '0' NOT NULL, - CONSTRAINT phpbb_session_pkey PRIMARY KEY (session_id) -); -CREATE INDEX session_user_id ON phpbb_sessions (session_user_id); -CREATE INDEX session_id_ip_user_id ON phpbb_sessions (session_id, session_ip, session_user_id); - - -/* -------------------------------------------------------- - Table structure for table phpbb_smilies --------------------------------------------------------- */ + session_id varchar(32) DEFAULT '' NOT NULL, + session_user_id INT4 DEFAULT '0' NOT NULL, + session_last_visit INT4 DEFAULT '0' NOT NULL, + session_start INT4 DEFAULT '0' NOT NULL, + session_time INT4 DEFAULT '0' NOT NULL, + session_ip varchar(40) DEFAULT '0' NOT NULL, + session_browser varchar(100) DEFAULT '' NULL, + session_page varchar(100) DEFAULT '' NOT NULL, + session_viewonline INT2 DEFAULT '1' NOT NULL, + session_admin INT2 DEFAULT '0' NOT NULL, + PRIMARY KEY (session_id), + CHECK (session_user_id>=0) +); + +CREATE INDEX session_time_phpbb_sessions_index ON phpbb_sessions (session_time); +CREATE INDEX session_user_id_phpbb_sessions_index ON phpbb_sessions (session_user_id); + +/* Table: phpbb_sitelist */ +CREATE SEQUENCE phpbb_sitelist_site_id_seq; + +CREATE TABLE phpbb_sitelist ( + site_id INT4 DEFAULT nextval('phpbb_sitelist_site_id_seq'), + site_ip varchar(40) DEFAULT '' NOT NULL, + site_hostname varchar(255) DEFAULT '' NOT NULL, + ip_exclude INT2 DEFAULT '0' NOT NULL, + PRIMARY KEY (site_id) +); + +SELECT SETVAL('phpbb_sitelist_site_id_seq',(select case when max(site_id)>0 then max(site_id)+1 else 1 end from phpbb_sitelist)); + +/* Table: phpbb_smilies */ +CREATE SEQUENCE phpbb_smilies_smile_id_seq; + CREATE TABLE phpbb_smilies ( - smilies_id int4 DEFAULT nextval('phpbb_smilies_id_seq'::text) NOT NULL, - code varchar(50), - smile_url varchar(100), - emoticon varchar(75), - CONSTRAINT phpbb_smilies_pkey PRIMARY KEY (smilies_id) -); - - -/* -------------------------------------------------------- - Table structure for table phpbb_themes --------------------------------------------------------- */ -CREATE TABLE phpbb_themes ( - themes_id int4 DEFAULT nextval('phpbb_themes_id_seq'::text) NOT NULL, - style_name varchar(30), - template_name varchar(30) NOT NULL DEFAULT '', - head_stylesheet varchar(100), - body_background varchar(100), - body_bgcolor char(6), - body_text char(6), - body_link char(6), - body_vlink char(6), - body_alink char(6), - body_hlink char(6), - tr_color1 char(6), - tr_color2 char(6), - tr_color3 char(6), - tr_class1 varchar(25), - tr_class2 varchar(25), - tr_class3 varchar(25), - th_color1 char(6), - th_color2 char(6), - th_color3 char(6), - th_class1 varchar(25), - th_class2 varchar(25), - th_class3 varchar(25), - td_color1 char(6), - td_color2 char(6), - td_color3 char(6), - td_class1 varchar(25), - td_class2 varchar(25), - td_class3 varchar(25), - fontface1 varchar(50), - fontface2 varchar(50), - fontface3 varchar(50), - fontsize1 int2, - fontsize2 int2, - fontsize3 int2, - fontcolor1 char(6), - fontcolor2 char(6), - fontcolor3 char(6), - span_class1 varchar(25), - span_class2 varchar(25), - span_class3 varchar(25), - img_size_poll int2, - img_size_privmsg int2, - CONSTRAINT phpbb_themes_pkey PRIMARY KEY (themes_id) -); - - -/* -------------------------------------------------------- - Table structure for table phpbb_themes_name --------------------------------------------------------- */ -CREATE TABLE phpbb_themes_name ( - themes_id int4 DEFAULT '0' NOT NULL, - tr_color1_name char(50), - tr_color2_name char(50), - tr_color3_name char(50), - tr_class1_name varchar(50), - tr_class2_name varchar(50), - tr_class3_name varchar(50), - th_color1_name char(50), - th_color2_name char(50), - th_color3_name char(50), - th_class1_name varchar(50), - th_class2_name varchar(50), - th_class3_name varchar(50), - td_color1_name char(50), - td_color2_name char(50), - td_color3_name char(50), - td_class1_name varchar(50), - td_class2_name varchar(50), - td_class3_name varchar(50), - fontface1_name varchar(50), - fontface2_name varchar(50), - fontface3_name varchar(50), - fontsize1_name varchar(50), - fontsize2_name varchar(50), - fontsize3_name varchar(50), - fontcolor1_name char(50), - fontcolor2_name char(50), - fontcolor3_name char(50), - span_class1_name varchar(50), - span_class2_name varchar(50), - span_class3_name varchar(50), - CONSTRAINT phpbb_themes_name_pkey PRIMARY KEY (themes_id) -); - - -/* -------------------------------------------------------- - Table structure for table phpbb_topics --------------------------------------------------------- */ + smile_id INT2 DEFAULT nextval('phpbb_smilies_smile_id_seq'), + code char(10), + emoticon char(50), + smile_url char(50), + smile_width INT2 NOT NULL, + smile_height INT2 NOT NULL, + smile_order INT2 NOT NULL, + display_on_posting INT2 DEFAULT '1' NOT NULL, + PRIMARY KEY (smile_id), + CHECK (smile_width>=0), + CHECK (smile_height>=0), + CHECK (smile_order>=0), + CHECK (display_on_posting>=0) +); + +SELECT SETVAL('phpbb_smilies_smile_id_seq',(select case when max(smile_id)>0 then max(smile_id)+1 else 1 end from phpbb_smilies)); + +/* Table: phpbb_styles */ +CREATE SEQUENCE phpbb_styles_style_id_seq; + +CREATE TABLE phpbb_styles ( + style_id INT2 DEFAULT nextval('phpbb_styles_style_id_seq'), + style_name varchar(30) DEFAULT '' NOT NULL, + style_copyright varchar(50) DEFAULT '' NOT NULL, + style_active INT2 DEFAULT '1' NOT NULL, + template_id INT2 NOT NULL, + theme_id INT2 NOT NULL, + imageset_id INT2 NOT NULL, + PRIMARY KEY (style_id), + CHECK (template_id>=0), + CHECK (theme_id>=0), + CHECK (imageset_id>=0) + + KEY (template_id), + KEY (theme_id), + KEY (imageset_id) +); + +CREATE UNIQUE INDEX style_name_phpbb_styles_index ON phpbb_styles (style_name); + +SELECT SETVAL('phpbb_styles_style_id_seq',(select case when max(style_id)>0 then max(style_id)+1 else 1 end from phpbb_styles)); + +/* Table: phpbb_styles_template */ +CREATE SEQUENCE phpbb_styles_template_templa; + +CREATE TABLE phpbb_styles_template ( + template_id INT2 DEFAULT nextval('phpbb_styles_template_templa'), + template_name varchar(30) NOT NULL, + template_copyright varchar(50) NOT NULL, + template_path varchar(30) NOT NULL, + bbcode_bitfield INT4 DEFAULT '0' NOT NULL, + template_storedb INT2 DEFAULT '0' NOT NULL, + PRIMARY KEY (template_id), + CHECK (bbcode_bitfield>=0) +); + +CREATE UNIQUE INDEX template_name_phpbb_styles_template_index ON phpbb_styles_template (template_name); + +SELECT SETVAL('phpbb_styles_template_templa',(select case when max(template_id)>0 then max(template_id)+1 else 1 end from phpbb_styles_template)); + +/* Table: phpbb_styles_template_data */ +CREATE TABLE phpbb_styles_template_data ( + template_id INT2 NOT NULL, + template_filename varchar(50) DEFAULT '' NOT NULL, + template_included TEXT DEFAULT '' NOT NULL, + template_mtime INT4 DEFAULT '0' NOT NULL, + template_data text, + KEY (template_id), + KEY (template_filename) +); + +/* Table: phpbb_styles_theme */ +CREATE SEQUENCE phpbb_styles_theme_theme_id_; + +CREATE TABLE phpbb_styles_theme ( + theme_id INT2 DEFAULT nextval('phpbb_styles_theme_theme_id_'), + theme_name varchar(30) DEFAULT '' NOT NULL, + theme_copyright varchar(50) DEFAULT '' NOT NULL, + theme_path varchar(30) DEFAULT '' NOT NULL, + theme_storedb INT2 DEFAULT '0' NOT NULL, + theme_mtime INT4 DEFAULT '0' NOT NULL, + theme_data text DEFAULT '' NOT NULL, + PRIMARY KEY (theme_id) +); + +CREATE UNIQUE INDEX theme_name_phpbb_styles_theme_index ON phpbb_styles_theme (theme_name); + +SELECT SETVAL('phpbb_styles_theme_theme_id_',(select case when max(theme_id)>0 then max(theme_id)+1 else 1 end from phpbb_styles_theme)); + +/* Table: phpbb_styles_imageset */ +CREATE SEQUENCE phpbb_styles_imageset_images; + +CREATE TABLE phpbb_styles_imageset ( + imageset_id INT2 DEFAULT nextval('phpbb_styles_imageset_images'), + imageset_name varchar(30) DEFAULT '' NOT NULL, + imageset_copyright varchar(50) DEFAULT '' NOT NULL, + imageset_path varchar(30) DEFAULT '' NOT NULL, + site_logo varchar(200) DEFAULT '' NOT NULL, + btn_post varchar(200) DEFAULT '' NOT NULL, + btn_post_pm varchar(200) DEFAULT '' NOT NULL, + btn_reply varchar(200) DEFAULT '' NOT NULL, + btn_reply_pm varchar(200) DEFAULT '' NOT NULL, + btn_locked varchar(200) DEFAULT '' NOT NULL, + btn_profile varchar(200) DEFAULT '' NOT NULL, + btn_pm varchar(200) DEFAULT '' NOT NULL, + btn_delete varchar(200) DEFAULT '' NOT NULL, + btn_info varchar(200) DEFAULT '' NOT NULL, + btn_quote varchar(200) DEFAULT '' NOT NULL, + btn_search varchar(200) DEFAULT '' NOT NULL, + btn_edit varchar(200) DEFAULT '' NOT NULL, + btn_report varchar(200) DEFAULT '' NOT NULL, + btn_email varchar(200) DEFAULT '' NOT NULL, + btn_www varchar(200) DEFAULT '' NOT NULL, + btn_icq varchar(200) DEFAULT '' NOT NULL, + btn_aim varchar(200) DEFAULT '' NOT NULL, + btn_yim varchar(200) DEFAULT '' NOT NULL, + btn_msnm varchar(200) DEFAULT '' NOT NULL, + btn_jabber varchar(200) DEFAULT '' NOT NULL, + btn_online varchar(200) DEFAULT '' NOT NULL, + btn_offline varchar(200) DEFAULT '' NOT NULL, + btn_friend varchar(200) DEFAULT '' NOT NULL, + btn_foe varchar(200) DEFAULT '' NOT NULL, + icon_unapproved varchar(200) DEFAULT '' NOT NULL, + icon_reported varchar(200) DEFAULT '' NOT NULL, + icon_attach varchar(200) DEFAULT '' NOT NULL, + icon_post varchar(200) DEFAULT '' NOT NULL, + icon_post_new varchar(200) DEFAULT '' NOT NULL, + icon_post_latest varchar(200) DEFAULT '' NOT NULL, + icon_post_newest varchar(200) DEFAULT '' NOT NULL, + forum varchar(200) DEFAULT '' NOT NULL, + forum_new varchar(200) DEFAULT '' NOT NULL, + forum_locked varchar(200) DEFAULT '' NOT NULL, + forum_link varchar(200) DEFAULT '' NOT NULL, + sub_forum varchar(200) DEFAULT '' NOT NULL, + sub_forum_new varchar(200) DEFAULT '' NOT NULL, + folder varchar(200) DEFAULT '' NOT NULL, + folder_moved varchar(200) DEFAULT '' NOT NULL, + folder_posted varchar(200) DEFAULT '' NOT NULL, + folder_new varchar(200) DEFAULT '' NOT NULL, + folder_new_posted varchar(200) DEFAULT '' NOT NULL, + folder_hot varchar(200) DEFAULT '' NOT NULL, + folder_hot_posted varchar(200) DEFAULT '' NOT NULL, + folder_hot_new varchar(200) DEFAULT '' NOT NULL, + folder_hot_new_posted varchar(200) DEFAULT '' NOT NULL, + folder_locked varchar(200) DEFAULT '' NOT NULL, + folder_locked_posted varchar(200) DEFAULT '' NOT NULL, + folder_locked_new varchar(200) DEFAULT '' NOT NULL, + folder_locked_new_posted varchar(200) DEFAULT '' NOT NULL, + folder_sticky varchar(200) DEFAULT '' NOT NULL, + folder_sticky_posted varchar(200) DEFAULT '' NOT NULL, + folder_sticky_new varchar(200) DEFAULT '' NOT NULL, + folder_sticky_new_posted varchar(200) DEFAULT '' NOT NULL, + folder_announce varchar(200) DEFAULT '' NOT NULL, + folder_announce_posted varchar(200) DEFAULT '' NOT NULL, + folder_announce_new varchar(200) DEFAULT '' NOT NULL, + folder_announce_new_posted varchar(200) DEFAULT '' NOT NULL, + folder_global varchar(200) DEFAULT '' NOT NULL, + folder_global_posted varchar(200) DEFAULT '' NOT NULL, + folder_global_new varchar(200) DEFAULT '' NOT NULL, + folder_global_new_posted 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, + attach_progress_bar 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 imageset_name_phpbb_styles_imageset_index ON phpbb_styles_imageset (imageset_name); + +SELECT SETVAL('phpbb_styles_imageset_images',(select case when max(imageset_id)>0 then max(imageset_id)+1 else 1 end from phpbb_styles_imageset)); + +/* Table: phpbb_topics */ +CREATE SEQUENCE phpbb_topics_topic_id_seq; + CREATE TABLE phpbb_topics ( - topic_id int4 DEFAULT nextval('phpbb_topics_id_seq'::text) NOT NULL, - forum_id int4 DEFAULT '0' NOT NULL, - topic_title varchar(60) DEFAULT '' NOT NULL, - topic_poster int4 DEFAULT '0' NOT NULL, - topic_time int4 DEFAULT '0' NOT NULL, - topic_views int4 DEFAULT '0' NOT NULL, - topic_replies int4 DEFAULT '0' NOT NULL, - topic_status int2 DEFAULT '0' NOT NULL, - topic_vote int2 DEFAULT '0' NOT NULL, - topic_type int2 DEFAULT '0' NOT NULL, - topic_first_post_id int4 DEFAULT '0' NOT NULL, - topic_last_post_id int4 DEFAULT '0' NOT NULL, - topic_moved_id int4 DEFAULT '0' NOT NULL, - CONSTRAINT phpbb_topics_pkey PRIMARY KEY (topic_id) -); -CREATE INDEX forum_id_phpbb_topics_index ON phpbb_topics (forum_id); -CREATE INDEX topic_moved_id_phpbb_topics_index ON phpbb_topics (topic_moved_id); -CREATE INDEX topic_first_post_id_phpbb_topics_index ON phpbb_topics (topic_first_post_id); -CREATE INDEX topic_last_post_id_phpbb_topics_index ON phpbb_topics (topic_last_post_id); -CREATE INDEX topic_status_phpbb_topics_index ON phpbb_topics (topic_status); -CREATE INDEX topic_type_phpbb_topics_index ON phpbb_topics (topic_type); - - -/* -------------------------------------------------------- - Table structure for table phpbb_topics_watch --------------------------------------------------------- */ + topic_id INT4 DEFAULT nextval('phpbb_topics_topic_id_seq'), + forum_id INT2 DEFAULT '0' NOT NULL, + icon_id INT2 DEFAULT '1' NOT NULL, + topic_attachment INT2 DEFAULT '0' NOT NULL, + topic_approved INT2 DEFAULT '1' NOT NULL, + topic_reported INT2 DEFAULT '0' NOT NULL, + topic_title varchar(60) NOT NULL, + topic_poster INT4 DEFAULT '0' NOT NULL, + topic_time INT4 DEFAULT '0' NOT NULL, + topic_time_limit INT4 DEFAULT '0' NOT NULL, + topic_views INT4 DEFAULT '0' NOT NULL, + topic_replies INT4 DEFAULT '0' NOT NULL, + topic_replies_real INT4 DEFAULT '0' NOT NULL, + topic_status INT2 DEFAULT '0' NOT NULL, + topic_type INT2 DEFAULT '0' NOT NULL, + topic_first_post_id INT4 DEFAULT '0' NOT NULL, + topic_first_poster_name varchar(30), + topic_last_post_id INT4 DEFAULT '0' NOT NULL, + topic_last_poster_id INT4 DEFAULT '0' NOT NULL, + topic_last_poster_name varchar(30), + topic_last_post_time INT4 DEFAULT '0' NOT NULL, + topic_last_view_time INT4 DEFAULT '0' NOT NULL, + topic_moved_id INT4 DEFAULT '0' NOT NULL, + topic_bumped INT2 DEFAULT '0' NOT NULL, + topic_bumper INT4 DEFAULT '0' NOT NULL, + poll_title varchar(255) NOT NULL, + poll_start INT4 DEFAULT '0' NOT NULL, + poll_length INT4 DEFAULT '0' NOT NULL, + poll_max_options INT2 DEFAULT '1' NOT NULL, + poll_last_vote INT4 DEFAULT '0', + poll_vote_change INT2 DEFAULT '0' NOT NULL, + PRIMARY KEY (topic_id), + CHECK (forum_id>=0), + CHECK (icon_id>=0), + CHECK (topic_approved>=0), + CHECK (topic_reported>=0), + CHECK (topic_poster>=0), + CHECK (topic_views>=0), + CHECK (topic_replies>=0), + CHECK (topic_replies_real>=0), + CHECK (topic_first_post_id>=0), + CHECK (topic_last_post_id>=0), + CHECK (topic_last_poster_id>=0), + CHECK (topic_last_post_time>=0), + CHECK (topic_last_view_time>=0), + CHECK (topic_moved_id>=0), + CHECK (topic_bumped>=0), + CHECK (topic_bumper>=0), + CHECK (poll_max_options>=0), + CHECK (poll_last_vote>=0), + CHECK (poll_vote_change>=0) +); + +CREATE INDEX forum_id_phpbb_topics_index ON phpbb_topics (forum_id); +CREATE INDEX forum_id_type_phpbb_topics_index ON phpbb_topics (forum_id, topic_type); +CREATE INDEX topic_last_post_time_phpbb_topics_index ON phpbb_topics (topic_last_post_time); + +SELECT SETVAL('phpbb_topics_topic_id_seq',(select case when max(topic_id)>0 then max(topic_id)+1 else 1 end from phpbb_topics)); + +/* Table: phpbb_topic_marking */ +CREATE TABLE phpbb_topics_marking ( + user_id INT4 DEFAULT '0' NOT NULL, + topic_id INT4 DEFAULT '0' NOT NULL, + mark_type INT2 DEFAULT '0' NOT NULL, + mark_time INT4 DEFAULT '0' NOT NULL, + PRIMARY KEY (user_id,topic_id), + CHECK (user_id>=0), + CHECK (topic_id>=0) +); + +/* Table: phpbb_topics_watch */ CREATE TABLE phpbb_topics_watch ( - topic_id int4, - user_id int4, - notify_status int2 NOT NULL default '0' + topic_id INT4 DEFAULT '0' NOT NULL, + user_id INT4 DEFAULT '0' NOT NULL, + notify_status INT2 DEFAULT '0' NOT NULL, ); -CREATE INDEX topic_id_phpbb_topics_watch_index ON phpbb_topics_watch (topic_id); -CREATE INDEX user_id_phpbb_topics_watch_index ON phpbb_topics_watch (user_id); +CREATE INDEX topic_id_phpbb_topics_watch_index ON phpbb_topics_watch (topic_id); +CREATE INDEX user_id_phpbb_topics_watch_index ON phpbb_topics_watch (user_id); +CREATE INDEX notify_status_phpbb_topics_watch_index ON phpbb_topics_watch (notify_status); -/* -------------------------------------------------------- - Table structure for table phpbb_user_group --------------------------------------------------------- */ +/* Table: phpbb_user_group */ CREATE TABLE phpbb_user_group ( - group_id int DEFAULT '0' NOT NULL, - user_id int DEFAULT '0' NOT NULL, - user_pending int2 + group_id INT4 DEFAULT '0' NOT NULL, + user_id INT4 DEFAULT '0' NOT NULL, + group_leader INT2 DEFAULT '0' NOT NULL, + user_pending INT2, ); -CREATE INDEX group_id_phpbb_user_group_index ON phpbb_user_group (group_id); -CREATE INDEX user_id_phpbb_user_group_index ON phpbb_user_group (user_id); +CREATE INDEX group_id_phpbb_user_group_index ON phpbb_user_group (group_id); +CREATE INDEX user_id_phpbb_user_group_index ON phpbb_user_group (user_id); +CREATE INDEX group_leader_phpbb_user_group_index ON phpbb_user_group (group_leader); + +/* Table: phpbb_users */ +CREATE SEQUENCE phpbb_users_user_id_seq; -/* -------------------------------------------------------- - Table structure for table phpbb_users --------------------------------------------------------- */ CREATE TABLE phpbb_users ( - user_id int4 DEFAULT nextval('phpbb_users_id_seq'::text) NOT NULL, - user_active int2, - username varchar(25) DEFAULT '' NOT NULL, - user_regdate int4 DEFAULT '0' NOT NULL, + user_id INT4 DEFAULT nextval('phpbb_users_user_id_seq'), + user_type INT2 DEFAULT '0' NOT NULL, + group_id INT4 DEFAULT '3' NOT NULL, + user_permissions text DEFAULT '' NOT NULL, + user_ip varchar(40) DEFAULT '' NOT NULL, + user_regdate INT4 DEFAULT '0' NOT NULL, + username varchar(30) DEFAULT '' NOT NULL, user_password varchar(32) DEFAULT '' NOT NULL, - user_session_time int4 DEFAULT '0' NOT NULL, - user_session_page int2 DEFAULT '0' NOT NULL, - user_lastvisit int4 DEFAULT '0' NOT NULL, - user_email varchar(255), - user_icq varchar(15), - user_website varchar(100), - user_occ varchar(100), - user_from varchar(100), - user_interests varchar(255), - user_sig text, - user_sig_bbcode_uid char(10), - user_style int4, - user_aim varchar(255), - user_yim varchar(255), - user_msnm varchar(255), - user_posts int4 DEFAULT '0' 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, - user_emailtime int4, - user_viewemail int2, - user_attachsig int2, - user_allowhtml int2 DEFAULT '1', - user_allowbbcode int2 DEFAULT '1', - user_allowsmile int2 DEFAULT '1', - user_allow_pm int2 DEFAULT '1' NOT NULL, - user_allowavatar int2 DEFAULT '1' NOT NULL, - user_allow_viewonline int2 DEFAULT '1' NOT NULL, - user_rank int4 DEFAULT '0', - user_avatar varchar(100), - user_avatar_type int2 DEFAULT '0' NOT NULL, - user_level int4 DEFAULT '1', - user_lang varchar(255), - user_timezone decimal(4) DEFAULT '0' NOT NULL, - user_dateformat varchar(14) DEFAULT 'd M Y H:m' NOT NULL, - user_notify_pm int2 DEFAULT '1' NOT NULL, - user_popup_pm int2 DEFAULT '0' NOT NULL, - user_notify int2, - user_actkey varchar(32), - user_newpasswd varchar(32), - CONSTRAINT phpbb_users_pkey PRIMARY KEY (user_id) -); - -CREATE INDEX user_session_time_phpbb_users_index ON phpbb_users (user_session_time); - -/* -------------------------------------------------------- - Table structure for table phpbb_vote_desc --------------------------------------------------------- */ -CREATE TABLE phpbb_vote_desc ( - vote_id int4 DEFAULT nextval('phpbb_vote_desc_id_seq'::text) NOT NULL , - topic_id int4 NOT NULL DEFAULT '0', - vote_text text NOT NULL, - vote_start int4 DEFAULT '0' NOT NULL, - vote_length int4 DEFAULT '0' NOT NULL, - CONSTRAINT phpbb_vote_dsc_pkey PRIMARY KEY (vote_id) -); -CREATE INDEX topic_id_phpbb_vote_desc_index ON phpbb_vote_desc (topic_id); - -/* -------------------------------------------------------- - Table structure for table phpbb_vote_results --------------------------------------------------------- */ -CREATE TABLE phpbb_vote_results ( - vote_id int4 NOT NULL DEFAULT '0', - vote_option_id int4 NOT NULL DEFAULT '0', - vote_option_text varchar(255) NOT NULL, - vote_result int4 NOT NULL DEFAULT '0' -); -CREATE INDEX option_id_phpbb_vote_results_index ON phpbb_vote_results (vote_option_id); - -/* -------------------------------------------------------- - Table structure for table phpbb_vote_voters --------------------------------------------------------- */ -CREATE TABLE phpbb_vote_voters ( - vote_id int4 NOT NULL DEFAULT '0', - vote_user_id int4 NOT NULL DEFAULT '0', - vote_user_ip char(40) NOT NULL -); -CREATE INDEX vote_id_phpbb_vote_voters_index ON phpbb_vote_voters (vote_id); -CREATE INDEX vote_user_id_phpbb_vote_voters_index ON phpbb_vote_voters (vote_user_id); -CREATE INDEX vote_user_ip_phpbb_vote_voters_index ON phpbb_vote_voters (vote_user_ip); - -/* -------------------------------------------------------- - Table structure for table phpbb_words --------------------------------------------------------- */ + user_passchg INT4 DEFAULT '0' NOT NULL, + user_email varchar(60) 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, + user_lastpost_time INT4 DEFAULT '0' NOT NULL, + user_lastpage varchar(100) DEFAULT '' NOT NULL, + user_last_confirm_key varchar(10) DEFAULT '' NOT NULL, + user_warnings INT2 DEFAULT '0' NOT NULL, + user_posts INT4 DEFAULT '0' NOT NULL, + user_lang varchar(30) DEFAULT '' NOT NULL, + user_timezone decimal(5,2) DEFAULT '0.0' NOT NULL, + user_dst INT2 DEFAULT '0' NOT NULL, + user_dateformat varchar(15) DEFAULT 'd M Y H:i' NOT NULL, + user_style INT2 DEFAULT '0' NOT NULL, + user_rank INT4 DEFAULT '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, + user_message_rules INT2 DEFAULT '0' NOT NULL, + user_full_folder INT4 DEFAULT '-3' NOT NULL, + user_emailtime INT4 DEFAULT '0' NOT NULL, + user_sortby_type varchar(1) DEFAULT '' NOT NULL, + user_sortby_dir varchar(1) DEFAULT '' NOT NULL, + user_show_days INT2 DEFAULT '' NOT NULL, + user_notify INT2 DEFAULT '0' NOT NULL, + user_notify_pm INT2 DEFAULT '1' NOT NULL, + user_notify_type INT2 DEFAULT '0' NOT NULL, + user_allow_pm INT2 DEFAULT '1' NOT NULL, + user_allow_email INT2 DEFAULT '1' NOT NULL, + user_allow_viewonline INT2 DEFAULT '1' NOT NULL, + user_allow_viewemail INT2 DEFAULT '1' NOT NULL, + user_allow_massemail INT2 DEFAULT '1' NOT NULL, + user_options INT4 DEFAULT '893' NOT NULL, + user_avatar varchar(100) DEFAULT '' NOT NULL, + user_avatar_type INT2 DEFAULT '0' NOT NULL, + user_avatar_width INT2 DEFAULT '0' NOT NULL, + user_avatar_height INT2 DEFAULT '0' NOT NULL, + user_sig text DEFAULT '' NOT NULL, + user_sig_bbcode_uid varchar(5) DEFAULT '' NOT NULL, + user_sig_bbcode_bitfield INT4 DEFAULT '0' 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(100) DEFAULT '' NOT NULL, + user_occ varchar(255) DEFAULT '' NOT NULL, + user_interests varchar(255) DEFAULT '' NOT NULL, + user_actkey varchar(32) DEFAULT '' NOT NULL, + user_newpasswd varchar(32) DEFAULT '' NOT NULL, + PRIMARY KEY (user_id), + CHECK (user_posts>=0), + CHECK (user_new_privmsg>=0), + CHECK (user_unread_privmsg>=0), + CHECK (user_message_rules>=0), + CHECK (user_avatar_width>=0), + CHECK (user_avatar_height>=0) +); + +CREATE INDEX user_birthday_phpbb_users_index ON phpbb_users (user_birthday); +CREATE INDEX user_email_hash_phpbb_users_index ON phpbb_users (user_email_hash); +CREATE INDEX username_phpbb_users_index ON phpbb_users (username); + +SELECT SETVAL('phpbb_users_user_id_seq',(select case when max(user_id)>0 then max(user_id)+1 else 1 end from phpbb_users)); + +/* Table: phpbb_words */ +CREATE SEQUENCE phpbb_words_word_id_seq; + CREATE TABLE phpbb_words ( - word_id int4 DEFAULT nextval('phpbb_words_id_seq'::text) NOT NULL, - word varchar(100) DEFAULT '' NOT NULL, - replacement varchar(100) DEFAULT '' NOT NULL, - CONSTRAINT phpbb_words_pkey PRIMARY KEY (word_id) + word_id INT4 DEFAULT nextval('phpbb_words_word_id_seq'), + word char(100) NOT NULL, + replacement char(100) NOT NULL, + PRIMARY KEY (word_id) ); + +SELECT SETVAL('phpbb_words_word_id_seq',(select case when max(word_id)>0 then max(word_id)+1 else 1 end from phpbb_words)); + +/* Table: phpbb_zebra */ +CREATE TABLE phpbb_zebra ( + user_id INT4 DEFAULT '0' NOT NULL, + zebra_id INT4 DEFAULT '0' NOT NULL, + friend INT2 DEFAULT '0' NOT NULL, + foe INT2 DEFAULT '0' NOT NULL, +); + +CREATE INDEX user_id_phpbb_zebra_index ON phpbb_zebra (user_id); +CREATE INDEX zebra_id_phpbb_zebra_index ON phpbb_zebra (zebra_id); + +COMMIT; |