X-Git-Url: https://git.heureux-cyclage.org/?a=blobdiff_plain;f=maintenance%2Fpostgres%2Ftables.sql;h=227376187f3d884864566138b98638c944b2b38a;hb=4cb9c1a24bde6e29c5e8f05c7cd1de54ffdd342a;hp=61cb19817ef3dee6814eaef93ed8eeb20e2622d3;hpb=99340a1ea8e9a8041661eadb77291f5b1d0cd1cf;p=lhc%2Fweb%2Fwiklou.git

diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql
index 61cb19817e..e19c447a65 100644
--- a/maintenance/postgres/tables.sql
+++ b/maintenance/postgres/tables.sql
@@ -25,6 +25,8 @@ DROP SEQUENCE IF EXISTS category_cat_id_seq CASCADE;
 DROP SEQUENCE IF EXISTS archive_ar_id_seq CASCADE;
 DROP SEQUENCE IF EXISTS externallinks_el_id_seq CASCADE;
 DROP SEQUENCE IF EXISTS sites_site_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS change_tag_ct_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS tag_summary_ts_id_seq CASCADE;
 DROP FUNCTION IF EXISTS page_deleted() CASCADE;
 DROP FUNCTION IF EXISTS ts2_page_title() CASCADE;
 DROP FUNCTION IF EXISTS ts2_page_text() CASCADE;
@@ -56,10 +58,13 @@ INSERT INTO mwuser
   VALUES (DEFAULT,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,now(),now());
 
 CREATE TABLE user_groups (
-  ug_user   INTEGER      NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
-  ug_group  TEXT     NOT NULL
+  ug_user    INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+  ug_group   TEXT         NOT NULL,
+  ug_expiry  TIMESTAMPTZ  NULL,
+  PRIMARY KEY(ug_user, ug_group)
 );
-CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
+CREATE INDEX user_groups_group  ON user_groups (ug_group);
+CREATE INDEX user_groups_expiry ON user_groups (ug_expiry);
 
 CREATE TABLE user_former_groups (
   ufg_user   INTEGER      NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
@@ -252,13 +257,16 @@ CREATE INDEX cl_sortkey     ON categorylinks (cl_to, cl_sortkey, cl_from);
 
 CREATE SEQUENCE externallinks_el_id_seq;
 CREATE TABLE externallinks (
-  el_id     INTEGER  NOT NULL  PRIMARY KEY DEFAULT nextval('externallinks_el_id_seq'),
-  el_from   INTEGER  NOT NULL  REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
-  el_to     TEXT     NOT NULL,
-  el_index  TEXT     NOT NULL
+  el_id       INTEGER     NOT NULL  PRIMARY KEY DEFAULT nextval('externallinks_el_id_seq'),
+  el_from     INTEGER     NOT NULL  REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+  el_to       TEXT        NOT NULL,
+  el_index    TEXT        NOT NULL,
+  el_index_60 BYTEA       NOT NULL  DEFAULT ''
 );
 CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to);
 CREATE INDEX externallinks_index   ON externallinks (el_index);
+CREATE INDEX el_index_60           ON externallinks (el_index_60, el_id);
+CREATE INDEX el_from_index_60      ON externallinks (el_from, el_index_60, el_id);
 
 CREATE TABLE langlinks (
   ll_from    INTEGER  NOT NULL  REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
@@ -446,6 +454,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_name_type_patrolled_timestamp ON recentchanges (rc_namespace, rc_type, rc_patrolled, rc_timestamp);
 
 
 CREATE SEQUENCE watchlist_wl_id_seq;
@@ -652,7 +661,9 @@ CREATE TABLE category (
 CREATE UNIQUE INDEX category_title ON category(cat_title);
 CREATE INDEX category_pages ON category(cat_pages);
 
+CREATE SEQUENCE change_tag_ct_id_seq;
 CREATE TABLE change_tag (
+  ct_id      INTEGER  NOT NULL  PRIMARY KEY DEFAULT nextval('change_tag_ct_id_seq'),
   ct_rc_id   INTEGER      NULL,
   ct_log_id  INTEGER      NULL,
   ct_rev_id  INTEGER      NULL,
@@ -664,11 +675,13 @@ CREATE UNIQUE INDEX change_tag_log_tag ON change_tag(ct_log_id,ct_tag);
 CREATE UNIQUE INDEX change_tag_rev_tag ON change_tag(ct_rev_id,ct_tag);
 CREATE INDEX change_tag_tag_id ON change_tag(ct_tag,ct_rc_id,ct_rev_id,ct_log_id);
 
+CREATE SEQUENCE tag_summary_ts_id_seq;
 CREATE TABLE tag_summary (
-  ts_rc_id   INTEGER     NULL,
-  ts_log_id  INTEGER     NULL,
-  ts_rev_id  INTEGER     NULL,
-  ts_tags    TEXT    NOT NULL
+  ts_id      INTEGER  NOT NULL  PRIMARY KEY DEFAULT nextval('tag_summary_ts_id_seq'),
+  ts_rc_id   INTEGER      NULL,
+  ts_log_id  INTEGER      NULL,
+  ts_rev_id  INTEGER      NULL,
+  ts_tags    TEXT     NOT NULL
 );
 CREATE UNIQUE INDEX tag_summary_rc_id ON tag_summary(ts_rc_id);
 CREATE UNIQUE INDEX tag_summary_log_id ON tag_summary(ts_log_id);