-- 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 to BOOL
+-- TODO: Change CHAR to BOOL (still needed as CHAR due to some PHP code)
BEGIN;
SET client_min_messages = 'ERROR';
);
ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type);
+
CREATE TABLE archive (
ar_namespace SMALLINT NOT NULL,
ar_title TEXT NOT NULL,
ar_text TEXT,
+ ar_page_id INTEGER NULL,
ar_comment TEXT,
ar_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
ar_user_text TEXT NOT NULL,
ar_flags TEXT,
ar_rev_id INTEGER,
ar_text_id INTEGER,
- ar_deleted INTEGER NOT NULL DEFAULT '0',
+ ar_deleted INTEGER NOT NULL DEFAULT 0,
ar_len INTEGER NULL
);
CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
+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,
cl_timestamp TIMESTAMPTZ NOT NULL
);
CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to);
-CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey);
+CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from);
CREATE TABLE externallinks (
el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
ipb_expiry TIMESTAMPTZ NOT NULL,
ipb_range_start TEXT,
ipb_range_end TEXT,
- ipb_deleted INTEGER NOT NULL DEFAULT '0'
+ ipb_deleted INTEGER NOT NULL DEFAULT 0,
+ ipb_block_email CHAR NOT NULL DEFAULT '0'
+
);
CREATE INDEX ipb_address ON ipblocks (ipb_address);
CREATE INDEX ipb_user ON ipblocks (ipb_user);
img_description TEXT NOT NULL,
img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
img_user_text TEXT NOT NULL,
- img_timestamp TIMESTAMPTZ
+ img_timestamp TIMESTAMPTZ,
+ img_sha1 TEXT NOT NULL DEFAULT ''
);
CREATE INDEX img_size_idx ON image (img_size);
CREATE INDEX img_timestamp_idx ON image (img_timestamp);
+CREATE INDEX img_sha1 ON image (img_sha1);
CREATE TABLE oldimage (
oi_name TEXT NOT NULL REFERENCES image(img_name),
oi_description TEXT,
oi_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
oi_user_text TEXT NOT NULL,
- oi_timestamp TIMESTAMPTZ NOT NULL
+ oi_timestamp TIMESTAMPTZ NOT NULL,
+ oi_metadata BYTEA NOT NULL DEFAULT '',
+ oi_media_type TEXT NULL,
+ oi_major_mime TEXT NOT NULL DEFAULT 'unknown',
+ oi_minor_mime TEXT NOT NULL DEFAULT 'unknown',
+ oi_deleted CHAR NOT NULL DEFAULT '0',
+ oi_sha1 TEXT NOT NULL DEFAULT ''
);
-CREATE INDEX oi_name ON oldimage (oi_name);
+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);
CREATE TABLE filearchive (
fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
fa_user_text TEXT NOT NULL,
fa_timestamp TIMESTAMPTZ,
- fa_deleted INTEGER NOT NULL DEFAULT '0'
+ fa_deleted INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp);
CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key);
rc_ip CIDR,
rc_old_len INTEGER,
rc_new_len INTEGER,
- rc_deleted INTEGER NOT NULL DEFAULT '0',
- rc_logid INTEGER NOT NULL DEFAULT '0',
+ rc_deleted INTEGER NOT NULL DEFAULT 0,
+ rc_logid INTEGER NOT NULL DEFAULT 0,
rc_log_type TEXT,
rc_log_action TEXT,
rc_params TEXT
CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id);
CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp);
CREATE INDEX rc_ip ON recentchanges (rc_ip);
+CREATE INDEX rc_patrolling ON recentchanges (rc_this_oldid, rc_last_oldid, rc_patrolled);
CREATE TABLE watchlist (
CREATE TABLE math (
- math_inputhash TEXT NOT NULL UNIQUE,
- math_outputhash TEXT NOT NULL,
+ math_inputhash BYTEA NOT NULL UNIQUE,
+ math_outputhash BYTEA NOT NULL,
math_html_conservativeness SMALLINT NOT NULL,
math_html TEXT,
math_mathml TEXT
CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,qcc_title);
CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
-
CREATE TABLE objectcache (
keyname CHAR(255) UNIQUE,
value BYTEA NOT NULL DEFAULT '',
CREATE SEQUENCE log_log_id_seq;
CREATE TABLE logging (
+ log_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('log_log_id_seq'),
log_type TEXT NOT NULL,
log_action TEXT NOT NULL,
log_timestamp TIMESTAMPTZ NOT NULL,
log_title TEXT NOT NULL,
log_comment TEXT,
log_params TEXT,
- log_deleted INTEGER NOT NULL DEFAULT '0',
- log_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('log_log_id_seq')
+ log_deleted INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX logging_type_name ON logging (log_type, log_timestamp);
CREATE INDEX logging_user_time ON logging (log_timestamp, log_user);
VALUES ('Creation','??','$LastChangedRevision$','$LastChangedDate$');
-COMMIT;