Fixups for page id field in the archive table:

[lhc/web/wiklou.git] / maintenance / postgres / tables.sql

diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql
index c6bd87d..6f91efa 100644 (file)
--- a/maintenance/postgres/tables.sql
+++ b/maintenance/postgres/tables.sql
@@ -128,6 +128,7 @@ 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,
@@ -140,6 +141,7 @@ CREATE TABLE archive (
   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 (
@@ -177,7 +179,7 @@ CREATE TABLE categorylinks (
   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,
@@ -249,10 +251,12 @@ CREATE TABLE image (
   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),
@@ -265,14 +269,16 @@ CREATE TABLE oldimage (
   oi_user          INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL,
   oi_user_text     TEXT         NOT NULL,
   oi_timestamp     TIMESTAMPTZ  NOT NULL,
-  oi_metadata      BYTEA        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_deleted       CHAR         NOT NULL DEFAULT '0',
+  oi_sha1          TEXT         NOT NULL DEFAULT ''
 );
-CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp);
+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 (
@@ -338,6 +344,7 @@ 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 (