summaryrefslogtreecommitdiff
path: root/maintenance/postgres/tables.sql
diff options
context:
space:
mode:
Diffstat (limited to 'maintenance/postgres/tables.sql')
-rw-r--r--maintenance/postgres/tables.sql112
1 files changed, 62 insertions, 50 deletions
diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql
index 8f99c84a..dc1d7e92 100644
--- a/maintenance/postgres/tables.sql
+++ b/maintenance/postgres/tables.sql
@@ -5,7 +5,7 @@
-- 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 (still needed as CHAR due to some PHP code)
+-- TODO: Change CHAR/SMALLINT to BOOL (still needed as CHAR due to some PHP code)
BEGIN;
SET client_min_messages = 'ERROR';
@@ -18,9 +18,9 @@ CREATE TABLE mwuser ( -- replace reserved word 'user'
user_password TEXT,
user_newpassword TEXT,
user_newpass_time TIMESTAMPTZ,
- user_token CHAR(32),
+ user_token TEXT,
user_email TEXT,
- user_email_token CHAR(32),
+ user_email_token TEXT,
user_email_token_expires TIMESTAMPTZ,
user_email_authenticated TIMESTAMPTZ,
user_options TEXT,
@@ -55,8 +55,8 @@ CREATE TABLE page (
page_title TEXT NOT NULL,
page_restrictions TEXT,
page_counter BIGINT NOT NULL DEFAULT 0,
- page_is_redirect CHAR NOT NULL DEFAULT 0,
- page_is_new CHAR NOT NULL DEFAULT 0,
+ page_is_redirect SMALLINT NOT NULL DEFAULT 0,
+ page_is_new SMALLINT NOT NULL DEFAULT 0,
page_random NUMERIC(15,14) NOT NULL DEFAULT RANDOM(),
page_touched TIMESTAMPTZ,
page_latest INTEGER NOT NULL, -- FK?
@@ -91,8 +91,8 @@ CREATE TABLE revision (
rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT,
rev_user_text TEXT NOT NULL,
rev_timestamp TIMESTAMPTZ NOT NULL,
- rev_minor_edit CHAR NOT NULL DEFAULT '0',
- rev_deleted CHAR NOT NULL DEFAULT '0',
+ rev_minor_edit SMALLINT NOT NULL DEFAULT 0,
+ rev_deleted SMALLINT NOT NULL DEFAULT 0,
rev_len INTEGER NULL,
rev_parent_id INTEGER NULL
);
@@ -127,17 +127,17 @@ ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (p
CREATE TABLE archive (
ar_namespace SMALLINT NOT NULL,
ar_title TEXT NOT NULL,
- ar_text TEXT,
+ ar_text TEXT, -- technically should be bytea, but not used anymore
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_timestamp TIMESTAMPTZ NOT NULL,
- ar_minor_edit CHAR NOT NULL DEFAULT '0',
+ ar_minor_edit SMALLINT NOT NULL DEFAULT 0,
ar_flags TEXT,
ar_rev_id INTEGER,
ar_text_id INTEGER,
- ar_deleted INTEGER NOT NULL DEFAULT 0,
+ ar_deleted SMALLINT NOT NULL DEFAULT 0,
ar_len INTEGER NULL
);
CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
@@ -161,7 +161,7 @@ CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title)
CREATE TABLE templatelinks (
tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
- tl_namespace TEXT NOT NULL,
+ tl_namespace SMALLINT NOT NULL,
tl_title TEXT NOT NULL
);
CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);
@@ -202,7 +202,7 @@ CREATE TABLE site_stats (
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_admins INTEGER DEFAULT -1,
@@ -222,15 +222,15 @@ CREATE TABLE ipblocks (
ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
ipb_reason TEXT NOT NULL,
ipb_timestamp TIMESTAMPTZ NOT NULL,
- ipb_auto CHAR NOT NULL DEFAULT '0',
- ipb_anon_only CHAR NOT NULL DEFAULT '0',
- ipb_create_account CHAR NOT NULL DEFAULT '1',
- ipb_enable_autoblock CHAR NOT NULL DEFAULT '1',
+ ipb_auto SMALLINT NOT NULL DEFAULT 0,
+ ipb_anon_only SMALLINT NOT NULL DEFAULT 0,
+ ipb_create_account SMALLINT NOT NULL DEFAULT 1,
+ ipb_enable_autoblock SMALLINT NOT NULL DEFAULT 1,
ipb_expiry TIMESTAMPTZ NOT NULL,
ipb_range_start TEXT,
ipb_range_end TEXT,
- ipb_deleted INTEGER NOT NULL DEFAULT 0,
- ipb_block_email CHAR NOT NULL DEFAULT '0'
+ ipb_deleted SMALLINT NOT NULL DEFAULT 0,
+ ipb_block_email SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX ipb_address ON ipblocks (ipb_address);
@@ -243,7 +243,7 @@ CREATE TABLE image (
img_size INTEGER NOT NULL,
img_width INTEGER NOT NULL,
img_height INTEGER NOT NULL,
- img_metadata TEXT,
+ img_metadata BYTEA NOT NULL DEFAULT '',
img_bits SMALLINT,
img_media_type TEXT,
img_major_mime TEXT DEFAULT 'unknown',
@@ -259,7 +259,7 @@ 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_name TEXT NOT NULL,
oi_archive_name TEXT NOT NULL,
oi_size INTEGER NOT NULL,
oi_width INTEGER NOT NULL,
@@ -273,9 +273,10 @@ CREATE TABLE oldimage (
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_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;
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);
@@ -285,15 +286,15 @@ CREATE TABLE filearchive (
fa_id SERIAL NOT NULL PRIMARY KEY,
fa_name TEXT NOT NULL,
fa_archive_name TEXT,
- fa_storage_group VARCHAR(16),
- fa_storage_key CHAR(64),
+ fa_storage_group TEXT,
+ fa_storage_key TEXT,
fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
fa_deleted_timestamp TIMESTAMPTZ NOT NULL,
fa_deleted_reason TEXT,
- fa_size SMALLINT NOT NULL,
- fa_width SMALLINT NOT NULL,
- fa_height SMALLINT NOT NULL,
- fa_metadata TEXT,
+ fa_size INTEGER NOT NULL,
+ fa_width INTEGER NOT NULL,
+ fa_height INTEGER NOT NULL,
+ fa_metadata BYTEA NOT NULL DEFAULT '',
fa_bits SMALLINT,
fa_media_type TEXT,
fa_major_mime TEXT DEFAULT 'unknown',
@@ -302,7 +303,7 @@ 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 SMALLINT 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);
@@ -320,20 +321,20 @@ CREATE TABLE recentchanges (
rc_namespace SMALLINT NOT NULL,
rc_title TEXT NOT NULL,
rc_comment TEXT,
- rc_minor CHAR NOT NULL DEFAULT '0',
- rc_bot CHAR NOT NULL DEFAULT '0',
- rc_new CHAR NOT NULL DEFAULT '0',
+ rc_minor SMALLINT NOT NULL DEFAULT 0,
+ rc_bot SMALLINT NOT NULL DEFAULT 0,
+ rc_new SMALLINT NOT NULL DEFAULT 0,
rc_cur_id INTEGER NULL REFERENCES page(page_id) ON DELETE SET NULL,
rc_this_oldid INTEGER NOT NULL,
rc_last_oldid INTEGER NOT NULL,
- rc_type CHAR NOT NULL DEFAULT '0',
+ rc_type SMALLINT NOT NULL DEFAULT 0,
rc_moved_to_ns SMALLINT,
rc_moved_to_title TEXT,
- rc_patrolled CHAR NOT NULL DEFAULT '0',
+ rc_patrolled SMALLINT NOT NULL DEFAULT 0,
rc_ip CIDR,
rc_old_len INTEGER,
rc_new_len INTEGER,
- rc_deleted INTEGER NOT NULL DEFAULT 0,
+ rc_deleted SMALLINT NOT NULL DEFAULT 0,
rc_logid INTEGER NOT NULL DEFAULT 0,
rc_log_type TEXT,
rc_log_action TEXT,
@@ -365,16 +366,16 @@ CREATE TABLE math (
CREATE TABLE interwiki (
- iw_prefix TEXT NOT NULL UNIQUE,
- iw_url TEXT NOT NULL,
- iw_local CHAR NOT NULL,
- iw_trans CHAR NOT NULL DEFAULT '0'
+ iw_prefix TEXT NOT NULL UNIQUE,
+ iw_url TEXT NOT NULL,
+ iw_local SMALLINT NOT NULL,
+ iw_trans SMALLINT NOT NULL DEFAULT 0
);
CREATE TABLE querycache (
qc_type TEXT NOT NULL,
- qc_value SMALLINT NOT NULL,
+ qc_value INTEGER NOT NULL,
qc_namespace SMALLINT NOT NULL,
qc_title TEXT NOT NULL
);
@@ -387,7 +388,7 @@ CREATE TABLE querycache_info (
CREATE TABLE querycachetwo (
qcc_type TEXT NOT NULL,
- qcc_value SMALLINT NOT NULL DEFAULT 0,
+ qcc_value INTEGER NOT NULL DEFAULT 0,
qcc_namespace INTEGER NOT NULL DEFAULT 0,
qcc_title TEXT NOT NULL DEFAULT '',
qcc_namespacetwo INTEGER NOT NULL DEFAULT 0,
@@ -398,7 +399,7 @@ CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,q
CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
CREATE TABLE objectcache (
- keyname CHAR(255) UNIQUE,
+ keyname TEXT UNIQUE,
value BYTEA NOT NULL DEFAULT '',
exptime TIMESTAMPTZ NOT NULL
);
@@ -422,7 +423,7 @@ CREATE TABLE logging (
log_title TEXT NOT NULL,
log_comment TEXT,
log_params TEXT,
- log_deleted INTEGER NOT NULL DEFAULT 0
+ log_deleted SMALLINT 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);
@@ -451,15 +452,16 @@ CREATE TABLE job (
CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
-- Tsearch2 2 stuff. Will fail if we don't have proper access to the tsearch2 tables
+-- Note: if version 8.3 or higher, we remove the 'default' arg
ALTER TABLE page ADD titlevector tsvector;
CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS
$mw$
BEGIN
IF TG_OP = 'INSERT' THEN
- NEW.titlevector = to_tsvector('default',NEW.page_title);
+ NEW.titlevector = to_tsvector('default',REPLACE(NEW.page_title,'/',' '));
ELSIF NEW.page_title != OLD.page_title THEN
- NEW.titlevector := to_tsvector('default',NEW.page_title);
+ NEW.titlevector := to_tsvector('default',REPLACE(NEW.page_title,'/',' '));
END IF;
RETURN NEW;
END;
@@ -486,11 +488,12 @@ CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent
FOR EACH ROW EXECUTE PROCEDURE ts2_page_text();
-- These are added by the setup script due to version compatibility issues
--- If using 8.1, switch from "gin" to "gist"
--- CREATE INDEX ts2_page_title ON page USING gin(titlevector);
--- CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector);
+-- If using 8.1, we switch from "gin" to "gist"
-CREATE FUNCTION add_interwiki (TEXT,INT,CHAR) RETURNS INT LANGUAGE SQL AS
+CREATE INDEX ts2_page_title ON page USING gin(titlevector);
+CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector);
+
+CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS
$mw$
INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
SELECT 1;
@@ -505,6 +508,16 @@ CREATE TABLE profiling (
);
CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);
+CREATE TABLE protected_titles (
+ pt_namespace SMALLINT NOT NULL,
+ pt_title TEXT NOT NULL,
+ pt_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
+ pt_reason TEXT NULL,
+ pt_timestamp TIMESTAMPTZ NOT NULL,
+ pt_expiry TIMESTAMPTZ NULL,
+ pt_create_perm TEXT NOT NULL DEFAULT ''
+);
+CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title);
CREATE TABLE mediawiki_version (
type TEXT NOT NULL,
@@ -525,6 +538,5 @@ CREATE TABLE mediawiki_version (
);
INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date)
- VALUES ('Creation','??','$LastChangedRevision: 25527 $','$LastChangedDate: 2007-09-05 04:14:18 -0400 (Wed, 05 Sep 2007) $');
-
+ VALUES ('Creation','??','$LastChangedRevision: 30800 $','$LastChangedDate: 2008-02-10 08:50:38 -0800 (Sun, 10 Feb 2008) $');