-- 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';
user_real_name TEXT,
user_password TEXT,
user_newpassword TEXT,
+ user_newpass_time TIMESTAMPTZ,
user_token CHAR(32),
user_email TEXT,
user_email_token CHAR(32),
user_email_authenticated TIMESTAMPTZ,
user_options TEXT,
user_touched TIMESTAMPTZ,
- user_registration TIMESTAMPTZ
+ user_registration TIMESTAMPTZ,
+ user_editcount INTEGER
);
CREATE INDEX user_email_token_idx ON mwuser (user_email_token);
CREATE TABLE user_newtalk (
user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
- user_ip CIDR NULL
+ user_ip TEXT NULL
);
CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);
rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE,
rev_text_id INTEGER NULL, -- FK
rev_comment TEXT,
- rev_user INTEGER NOT NULL REFERENCES mwuser(user_id),
+ 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_deleted CHAR NOT NULL DEFAULT '0',
+ rev_len INTEGER NULL,
+ rev_parent_id INTEGER NULL
);
CREATE UNIQUE INDEX revision_unique ON revision (rev_page, rev_id);
+CREATE INDEX rev_text_id_idx ON revision (rev_text_id);
CREATE INDEX rev_timestamp_idx ON revision (rev_timestamp);
CREATE INDEX rev_user_idx ON revision (rev_user);
CREATE INDEX rev_user_text_idx ON revision (rev_user_text);
);
-CREATE TABLE archive2 (
+CREATE SEQUENCE pr_id_val;
+CREATE TABLE page_restrictions (
+ pr_id INTEGER NOT NULL UNIQUE DEFAULT nextval('pr_id_val'),
+ pr_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE,
+ pr_type TEXT NOT NULL,
+ pr_level TEXT NOT NULL,
+ pr_cascade SMALLINT NOT NULL,
+ pr_user INTEGER NULL,
+ pr_expiry TIMESTAMPTZ NULL
+);
+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_minor_edit CHAR NOT NULL DEFAULT '0',
ar_flags TEXT,
ar_rev_id INTEGER,
- ar_text_id INTEGER
+ ar_text_id INTEGER,
+ ar_deleted INTEGER NOT NULL DEFAULT 0,
+ ar_len INTEGER NULL
);
-CREATE INDEX archive_name_title_timestamp ON archive2 (ar_namespace,ar_title,ar_timestamp);
+CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
+CREATE INDEX archive_user_text ON archive (ar_user_text);
--- This is the easiest way to work around the char(15) timestamp hack without modifying PHP code
-CREATE VIEW archive AS
-SELECT
- ar_namespace, ar_title, ar_text, ar_comment, ar_user, ar_user_text,
- ar_minor_edit, ar_flags, ar_rev_id, ar_text_id,
- TO_CHAR(ar_timestamp, 'YYYYMMDDHH24MISS') AS ar_timestamp
-FROM archive2;
-CREATE RULE archive_insert AS ON INSERT TO archive
-DO INSTEAD INSERT INTO archive2 VALUES (
- NEW.ar_namespace, NEW.ar_title, NEW.ar_text, NEW.ar_comment, NEW.ar_user, NEW.ar_user_text,
- TO_DATE(NEW.ar_timestamp, 'YYYYMMDDHH24MISS'),
- NEW.ar_minor_edit, NEW.ar_flags, NEW.ar_rev_id, NEW.ar_text_id
+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
);
+CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from);
CREATE TABLE pagelinks (
pl_namespace SMALLINT NOT NULL,
pl_title TEXT NOT NULL
);
-CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_namespace,pl_title,pl_from);
+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,
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,
CREATE SEQUENCE ipblocks_ipb_id_val;
CREATE TABLE ipblocks (
- ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_val'),
- ipb_address CIDR 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,
- 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_expiry TIMESTAMPTZ NOT NULL,
- ipb_range_start TEXT,
- ipb_range_end TEXT
+ ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_val'),
+ 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,
+ 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_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'
+
);
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_archive_name TEXT NOT NULL,
- oi_size SMALLINT NOT NULL,
- oi_width SMALLINT NOT NULL,
- oi_height SMALLINT NOT NULL,
+ oi_size INTEGER NOT NULL,
+ oi_width INTEGER NOT NULL,
+ oi_height INTEGER NOT NULL,
oi_bits SMALLINT NOT NULL,
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_description TEXT NOT NULL,
fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
fa_user_text TEXT NOT NULL,
- fa_timestamp TIMESTAMPTZ
+ fa_timestamp TIMESTAMPTZ,
+ 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_moved_to_ns SMALLINT,
rc_moved_to_title TEXT,
rc_patrolled CHAR NOT NULL DEFAULT '0',
- rc_ip CIDR
+ 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_log_type TEXT,
+ rc_log_action TEXT,
+ rc_params TEXT
);
CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp);
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);
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
qci_timestamp TIMESTAMPTZ NULL
);
+CREATE TABLE querycachetwo (
+ qcc_type TEXT NOT NULL,
+ qcc_value SMALLINT NOT NULL DEFAULT 0,
+ qcc_namespace INTEGER NOT NULL DEFAULT 0,
+ qcc_title TEXT NOT NULL DEFAULT '',
+ qcc_namespacetwo INTEGER NOT NULL DEFAULT 0,
+ qcc_titletwo TEXT NOT NULL DEFAULT ''
+);
+CREATE INDEX querycachetwo_type_value ON querycachetwo (qcc_type, qcc_value);
+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_namespace SMALLINT NOT NULL,
log_title TEXT NOT NULL,
log_comment TEXT,
- log_params TEXT
+ log_params TEXT,
+ 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);
-- Tsearch2 2 stuff. Will fail if we don't have proper access to the tsearch2 tables
ALTER TABLE page ADD titlevector tsvector;
-CREATE INDEX ts2_page_title ON page USING gist(titlevector);
CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS
$mw$
BEGIN
IF TG_OP = 'INSERT' THEN
- NEW.titlevector = to_tsvector(NEW.page_title);
+ NEW.titlevector = to_tsvector('default',NEW.page_title);
ELSIF NEW.page_title != OLD.page_title THEN
- NEW.titlevector := to_tsvector(NEW.page_title);
+ NEW.titlevector := to_tsvector('default',NEW.page_title);
END IF;
RETURN NEW;
END;
ALTER TABLE pagecontent ADD textvector tsvector;
-CREATE INDEX ts2_page_text ON pagecontent USING gist(textvector);
CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS
$mw$
BEGIN
IF TG_OP = 'INSERT' THEN
- NEW.textvector = to_tsvector(NEW.old_text);
+ NEW.textvector = to_tsvector('default',NEW.old_text);
ELSIF NEW.old_text != OLD.old_text THEN
- NEW.textvector := to_tsvector(NEW.old_text);
+ NEW.textvector := to_tsvector('default',NEW.old_text);
END IF;
RETURN NEW;
END;
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);
+
CREATE FUNCTION add_interwiki (TEXT,INT,CHAR) RETURNS INT LANGUAGE SQL AS
$mw$
INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
pf_count INTEGER NOT NULL DEFAULT 0,
pf_time NUMERIC(18,10) NOT NULL DEFAULT 0,
pf_name TEXT NOT NULL,
- pf_server TEXT NOT NULL
+ pf_server TEXT NULL
);
CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);
CREATE TABLE mediawiki_version (
type TEXT NOT NULL,
mw_version TEXT NOT NULL,
+ notes TEXT NULL,
pg_version TEXT NULL,
pg_dbname TEXT NULL,
pg_port TEXT NULL,
mw_schema TEXT NULL,
ts2_schema TEXT NULL,
+ ctype TEXT NULL,
sql_version TEXT NULL,
sql_date TEXT NULL,
VALUES ('Creation','??','$LastChangedRevision$','$LastChangedDate$');
-COMMIT;
dépôts / lhc / web / wiklou.git / blobdiff