-- This is the PostgreSQL version.
-- For information about each table, please see the notes in maintenance/tables.sql
-- Please make sure all dollar-quoting uses $mw$ at the start of the line
--- We can't use SERIAL everywhere: the sequence names are hard-coded into the PHP
--- TODO: Change CHAR/SMALLINT to BOOL (still needed as CHAR due to some PHP code)
+-- TODO: Change CHAR/SMALLINT to BOOL (still used in a non-bool fashion in PHP code)
BEGIN;
SET client_min_messages = 'ERROR';
user_options TEXT,
user_touched TIMESTAMPTZ,
user_registration TIMESTAMPTZ,
- user_editcount INTEGER
+ user_editcount INTEGER,
+ user_hidden SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX user_email_token_idx ON mwuser (user_email_token);
CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
CREATE TABLE user_newtalk (
- user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
- user_ip TEXT NULL
+ user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
+ user_ip TEXT NULL,
+ user_last_timestamp TIMESTAMPTZ
);
CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);
);
ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type);
+CREATE TABLE page_props (
+ pp_page INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE,
+ pp_propname TEXT NOT NULL,
+ pp_value TEXT NOT NULL
+);
+ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_propname);
+CREATE INDEX page_props_propname ON page_props (pp_propname);
CREATE TABLE archive (
ar_namespace SMALLINT NOT NULL,
ar_title TEXT NOT NULL,
ar_text TEXT, -- technically should be bytea, but not used anymore
ar_page_id INTEGER NULL,
+ ar_parent_id INTEGER NULL,
ar_comment TEXT,
ar_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
ar_user_text TEXT NOT NULL,
tl_title TEXT NOT NULL
);
CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);
+CREATE INDEX templatelinks_from ON templatelinks (tl_from);
CREATE TABLE imagelinks (
il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
ss_row_id INTEGER NOT NULL UNIQUE,
ss_total_views INTEGER DEFAULT 0,
ss_total_edits INTEGER DEFAULT 0,
- ss_good_articles INTEGER DEFAULT 0,
+ ss_good_articles INTEGER DEFAULT 0,
ss_total_pages INTEGER DEFAULT -1,
ss_users INTEGER DEFAULT -1,
+ ss_active_users INTEGER DEFAULT -1,
ss_admins INTEGER DEFAULT -1,
ss_images INTEGER DEFAULT 0
);
ipb_range_start TEXT,
ipb_range_end TEXT,
ipb_deleted SMALLINT NOT NULL DEFAULT 0,
- ipb_block_email SMALLINT NOT NULL DEFAULT 0
+ ipb_block_email SMALLINT NOT NULL DEFAULT 0,
+ ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0
);
-CREATE INDEX ipb_address ON ipblocks (ipb_address);
+CREATE UNIQUE INDEX ipb_address_unique ON ipblocks (ipb_address,ipb_user,ipb_auto,ipb_anon_only);
CREATE INDEX ipb_user ON ipblocks (ipb_user);
CREATE INDEX ipb_range ON ipblocks (ipb_range_start,ipb_range_end);
CREATE INDEX oi_sha1 ON oldimage (oi_sha1);
+CREATE SEQUENCE filearchive_fa_id_seq;
CREATE TABLE filearchive (
- fa_id SERIAL NOT NULL PRIMARY KEY,
+ fa_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'),
fa_name TEXT NOT NULL,
fa_archive_name TEXT,
fa_storage_group TEXT,
rc_params TEXT
);
CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp);
+CREATE INDEX rc_timestamp_bot ON recentchanges (rc_timestamp) WHERE rc_bot = 0;
CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title);
CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id);
CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp);
wl_notificationtimestamp TIMESTAMPTZ
);
CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
-
+CREATE INDEX wl_user ON watchlist (wl_user);
CREATE TABLE math (
math_inputhash BYTEA NOT NULL UNIQUE,
CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp);
+CREATE SEQUENCE trackbacks_tb_id_seq;
CREATE TABLE trackbacks (
- tb_id SERIAL NOT NULL PRIMARY KEY,
+ tb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'),
tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE,
tb_title TEXT NOT NULL,
tb_url TEXT NOT NULL,
CREATE TABLE profiling (
pf_count INTEGER NOT NULL DEFAULT 0,
pf_time NUMERIC(18,10) NOT NULL DEFAULT 0,
+ pf_memory NUMERIC(18,10) NOT NULL DEFAULT 0,
pf_name TEXT NOT NULL,
pf_server TEXT NULL
);
);
CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title);
+
+CREATE TABLE updatelog (
+ ul_key TEXT NOT NULL PRIMARY KEY
+);
+
+
+CREATE SEQUENCE category_id_seq;
+CREATE TABLE category (
+ cat_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('category_id_seq'),
+ cat_title TEXT NOT NULL,
+ cat_pages INTEGER NOT NULL DEFAULT 0,
+ cat_subcats INTEGER NOT NULL DEFAULT 0,
+ cat_files INTEGER NOT NULL DEFAULT 0,
+ cat_hidden SMALLINT NOT NULL DEFAULT 0
+);
+CREATE UNIQUE INDEX category_title ON category(cat_title);
+CREATE INDEX category_pages ON category(cat_pages);
+
+CREATE TABLE change_tag (
+ ct_rc_id INTEGER NULL,
+ ct_log_id INTEGER NULL,
+ ct_rev_id INTEGER NULL,
+ ct_tag TEXT NOT NULL,
+ ct_params TEXT NULL
+);
+CREATE UNIQUE INDEX change_tag_rc_tag ON change_tag(ct_rc_id,ct_tag);
+CREATE UNIQUE INDEX change_tag_log_tag ON change_tag(ct_log_id,ct_tag);
+CREATE UNIQUE INDEX change_tag_rev_tag ON change_tag(ct_rev_id,ct_tag);
+CREATE INDEX change_tag_tag_id ON change_tag(ct_tag,ct_rc_id,ct_rev_id,ct_log_id);
+
+CREATE TABLE tag_summary (
+ ts_rc_id INTEGER NULL,
+ ts_log_id INTEGER NULL,
+ ts_rev_id INTEGER NULL,
+ ts_tags TEXT NOT NULL
+);
+CREATE UNIQUE INDEX tag_summary_rc_id ON tag_summary(ts_rc_id);
+CREATE UNIQUE INDEX tag_summary_log_id ON tag_summary(ts_log_id);
+CREATE UNIQUE INDEX tag_summary_rev_id ON tag_summary(ts_rev_id);
+
+CREATE TABLE valid_tag (
+ vt_tag TEXT NOT NULL PRIMARY KEY
+);
+
CREATE TABLE mediawiki_version (
type TEXT NOT NULL,
mw_version TEXT NOT NULL,