diff options
Diffstat (limited to 'maintenance/postgres/tables.sql')
| -rw-r--r-- | maintenance/postgres/tables.sql | 71 |
1 files changed, 53 insertions, 18 deletions
diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 23e8b596..38b607d9 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -82,9 +82,9 @@ $mw$; CREATE TRIGGER page_deleted AFTER DELETE ON page FOR EACH ROW EXECUTE PROCEDURE page_deleted(); -CREATE SEQUENCE rev_rev_id_val; +CREATE SEQUENCE revision_rev_id_seq; CREATE TABLE revision ( - rev_id INTEGER NOT NULL UNIQUE DEFAULT nextval('rev_rev_id_val'), + rev_id INTEGER NOT NULL UNIQUE DEFAULT nextval('revision_rev_id_seq'), rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE, rev_text_id INTEGER NULL, -- FK rev_comment TEXT, @@ -103,17 +103,17 @@ CREATE INDEX rev_user_idx ON revision (rev_user); CREATE INDEX rev_user_text_idx ON revision (rev_user_text); -CREATE SEQUENCE text_old_id_val; +CREATE SEQUENCE text_old_id_seq; CREATE TABLE pagecontent ( -- replaces reserved word 'text' - old_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('text_old_id_val'), + old_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('text_old_id_seq'), old_text TEXT, old_flags TEXT ); -CREATE SEQUENCE pr_id_val; +CREATE SEQUENCE page_restrictions_pr_id_seq; CREATE TABLE page_restrictions ( - pr_id INTEGER NOT NULL UNIQUE DEFAULT nextval('pr_id_val'), + pr_id INTEGER NOT NULL UNIQUE DEFAULT nextval('page_restrictions_pr_id_seq'), pr_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE, pr_type TEXT NOT NULL, pr_level TEXT NOT NULL, @@ -155,7 +155,9 @@ CREATE INDEX archive_user_text ON archive (ar_user_text); CREATE TABLE redirect ( rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, rd_namespace SMALLINT NOT NULL, - rd_title TEXT NOT NULL + rd_title TEXT NOT NULL, + rd_interwiki TEXT NULL, + rd_fragment TEXT NULL ); CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from); @@ -198,6 +200,13 @@ CREATE TABLE externallinks ( CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to); CREATE INDEX externallinks_index ON externallinks (el_index); +CREATE TABLE external_user ( + eu_local_id INTEGER NOT NULL PRIMARY KEY, + eu_external_id TEXT +); + +CREATE UNIQUE INDEX eu_external_id ON external_user (eu_external_id); + CREATE TABLE langlinks ( ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE, ll_lang TEXT, @@ -224,9 +233,9 @@ CREATE TABLE hitcounter ( ); -CREATE SEQUENCE ipblocks_ipb_id_val; +CREATE SEQUENCE ipblocks_ipb_id_seq; CREATE TABLE ipblocks ( - ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_val'), + ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_seq'), ipb_address TEXT NULL, ipb_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, @@ -288,7 +297,7 @@ CREATE TABLE oldimage ( oi_deleted SMALLINT NOT NULL DEFAULT 0, oi_sha1 TEXT NOT NULL DEFAULT '' ); -ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascade FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE; +ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascaded FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE ON UPDATE CASCADE; CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp); CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name); CREATE INDEX oi_sha1 ON oldimage (oi_sha1); @@ -324,9 +333,9 @@ CREATE INDEX fa_notime ON filearchive (fa_deleted_timestamp); CREATE INDEX fa_nouser ON filearchive (fa_deleted_user); -CREATE SEQUENCE rc_rc_id_seq; +CREATE SEQUENCE recentchanges_rc_id_seq; CREATE TABLE recentchanges ( - rc_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'), + rc_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('recentchanges_rc_id_seq'), rc_timestamp TIMESTAMPTZ NOT NULL, rc_cur_time TIMESTAMPTZ NOT NULL, rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, @@ -426,9 +435,9 @@ CREATE TABLE transcache ( ); -CREATE SEQUENCE log_log_id_seq; +CREATE SEQUENCE logging_log_id_seq; CREATE TABLE logging ( - log_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('log_log_id_seq'), + log_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('logging_log_id_seq'), log_type TEXT NOT NULL, log_action TEXT NOT NULL, log_timestamp TIMESTAMPTZ NOT NULL, @@ -437,12 +446,24 @@ CREATE TABLE logging ( log_title TEXT NOT NULL, log_comment TEXT, log_params TEXT, - log_deleted SMALLINT NOT NULL DEFAULT 0 + log_deleted SMALLINT NOT NULL DEFAULT 0, + log_user_text TEXT NOT NULL DEFAULT '', + log_page INTEGER ); CREATE INDEX logging_type_name ON logging (log_type, log_timestamp); CREATE INDEX logging_user_time ON logging (log_timestamp, log_user); CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp); +CREATE INDEX logging_times ON logging (log_timestamp); +CREATE INDEX logging_user_type_time ON logging (log_user, log_type, log_timestamp); +CREATE INDEX logging_page_id_time ON logging (log_page, log_timestamp); +CREATE TABLE log_search ( + ls_field TEXT NOT NULL, + ls_value TEXT NOT NULL, + ls_log_id INTEGER NOT NULL DEFAULT 0, + PRIMARY KEY (ls_field,ls_value,ls_log_id) +); +CREATE INDEX ls_log_id ON log_search (ls_log_id); CREATE SEQUENCE trackbacks_tb_id_seq; CREATE TABLE trackbacks ( @@ -542,9 +563,9 @@ CREATE TABLE updatelog ( ); -CREATE SEQUENCE category_id_seq; +CREATE SEQUENCE category_cat_id_seq; CREATE TABLE category ( - cat_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('category_id_seq'), + cat_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('category_cat_id_seq'), cat_title TEXT NOT NULL, cat_pages INTEGER NOT NULL DEFAULT 0, cat_subcats INTEGER NOT NULL DEFAULT 0, @@ -580,6 +601,14 @@ CREATE TABLE valid_tag ( vt_tag TEXT NOT NULL PRIMARY KEY ); +CREATE TABLE user_properties ( + up_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, + up_property TEXT NOT NULL, + up_value TEXT +); +CREATE UNIQUE INDEX user_properties_user_property ON user_properties (up_user,up_property); +CREATE INDEX user_properties_property ON user_properties (up_property); + CREATE TABLE mediawiki_version ( type TEXT NOT NULL, mw_version TEXT NOT NULL, @@ -599,5 +628,11 @@ CREATE TABLE mediawiki_version ( ); INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date) - VALUES ('Creation','??','$LastChangedRevision: 48615 $','$LastChangedDate: 2009-03-20 12:15:41 +1100 (Fri, 20 Mar 2009) $'); + VALUES ('Creation','??','$LastChangedRevision: 59842 $','$LastChangedDate: 2009-12-09 06:32:17 +1100 (Wed, 09 Dec 2009) $'); +CREATE TABLE l10n_cache ( + lc_lang TEXT NOT NULL, + lc_key TEXT NOT NULL, + lc_value TEXT NOT NULL +); +CREATE INDEX l10n_cache_lc_lang_key ON l10n_cache (lc_lang, lc_key); |