rdbms: Remove support for PostgreSQL < 9.2, and improve INSERT IGNORE for 9.5

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

diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql
index 01177d8..d9429bc 100644 (file)
--- a/maintenance/postgres/tables.sql
+++ b/maintenance/postgres/tables.sql
@@ -248,7 +248,7 @@ CREATE TABLE archive (
   ar_timestamp      TIMESTAMPTZ  NOT NULL,
   ar_minor_edit     SMALLINT     NOT NULL  DEFAULT 0,
   ar_flags          TEXT,
-  ar_rev_id         INTEGER,
+  ar_rev_id         INTEGER      NOT NULL,
   ar_text_id        INTEGER,
   ar_deleted        SMALLINT     NOT NULL  DEFAULT 0,
   ar_len            INTEGER          NULL,
@@ -264,11 +264,11 @@ CREATE TABLE slots (
   slot_revision_id INTEGER   NOT NULL,
   slot_role_id     SMALLINT  NOT NULL,
   slot_content_id  INTEGER   NOT NULL,
-  slot_inherited   SMALLINT  NOT NULL  DEFAULT 0,
+  slot_origin      INTEGER   NOT NULL,
   PRIMARY KEY (slot_revision_id, slot_role_id)
 );
 
-CREATE INDEX slot_role_inherited ON slots (slot_revision_id, slot_role_id, slot_inherited);
+CREATE INDEX slot_revision_origin_role ON slots (slot_revision_id, slot_origin, slot_role_id);
 
 
 CREATE SEQUENCE content_content_id_seq;
@@ -370,13 +370,13 @@ CREATE INDEX langlinks_lang_title    ON langlinks (ll_lang,ll_title);
 
 CREATE TABLE site_stats (
   ss_row_id         INTEGER  NOT NULL  PRIMARY KEY DEFAULT 0,
-  ss_total_edits    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
+  ss_total_edits    INTEGER            DEFAULT NULL,
+  ss_good_articles  INTEGER            DEFAULT NULL,
+  ss_total_pages    INTEGER            DEFAULT NULL,
+  ss_users          INTEGER            DEFAULT NULL,
+  ss_active_users   INTEGER            DEFAULT NULL,
+  ss_admins         INTEGER            DEFAULT NULL,
+  ss_images         INTEGER            DEFAULT NULL
 );
 
 
@@ -421,6 +421,7 @@ CREATE TABLE image (
   img_major_mime   TEXT                DEFAULT 'unknown',
   img_minor_mime   TEXT                DEFAULT 'unknown',
   img_description  TEXT      NOT NULL  DEFAULT '',
+  img_description_id INTEGER NOT NULL  DEFAULT 0,
   img_user         INTEGER   NOT NULL  DEFAULT 0 REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
   img_user_text    TEXT      NOT NULL  DEFAULT '',
   img_actor        INTEGER   NOT NULL  DEFAULT 0,
@@ -558,7 +559,7 @@ CREATE TABLE recentchanges (
 );
 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_namespace_title_timestamp ON recentchanges (rc_namespace, rc_title, rc_timestamp);
 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);
@@ -686,7 +687,6 @@ CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
 CREATE INDEX job_timestamp_idx ON job (job_timestamp);
 
 -- Tsearch2 2 stuff. Will fail if we don't have proper access to the tsearch2 tables
--- Version 8.3 or higher only. Previous versions would need another parmeter for to_tsvector.
 -- Make sure you also change patch-tsearch2funcs.sql if the funcs below change.
 
 ALTER TABLE page ADD titlevector tsvector;
@@ -722,9 +722,6 @@ $mw$;
 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, we 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);