Add user_hidden column to mwuser table for Postgres schema.

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

diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql
index 22929a6..2dd02da 100644 (file)
--- a/maintenance/postgres/tables.sql
+++ b/maintenance/postgres/tables.sql
@@ -25,7 +25,8 @@ CREATE TABLE mwuser ( -- replace reserved word 'user'
   user_options              TEXT,
   user_touched              TIMESTAMPTZ,
   user_registration         TIMESTAMPTZ,
-  user_editcount            INTEGER
+  user_editcount            INTEGER,
+  user_hidden               SMALLINT NOT NULL DEFAULT 0
 );
 CREATE INDEX user_email_token_idx ON mwuser (user_email_token);
 
@@ -40,8 +41,9 @@ CREATE TABLE user_groups (
 CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
 
 CREATE TABLE user_newtalk (
-  user_id  INTEGER NOT NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE,
-  user_ip  TEXT        NULL
+  user_id              INTEGER      NOT NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE,
+  user_ip              TEXT             NULL,
+  user_last_timestamp  TIMESTAMPTZ
 );
 CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
 CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);
@@ -135,6 +137,7 @@ CREATE TABLE archive (
   ar_title       TEXT         NOT NULL,
   ar_text        TEXT, -- technically should be bytea, but not used anymore
   ar_page_id     INTEGER          NULL,
+  ar_parent_id   INTEGER          NULL,
   ar_comment     TEXT,
   ar_user        INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL,
   ar_user_text   TEXT         NOT NULL,
@@ -171,6 +174,7 @@ CREATE TABLE templatelinks (
   tl_title      TEXT     NOT NULL
 );
 CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);
+CREATE INDEX templatelinks_from          ON templatelinks (tl_from);
 
 CREATE TABLE imagelinks (
   il_from  INTEGER  NOT NULL  REFERENCES page(page_id) ON DELETE CASCADE,
@@ -208,9 +212,10 @@ 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_active_users   INTEGER            DEFAULT -1,
   ss_admins         INTEGER            DEFAULT -1,
   ss_images         INTEGER            DEFAULT 0
 );
@@ -237,10 +242,11 @@ CREATE TABLE ipblocks (
   ipb_range_start       TEXT,
   ipb_range_end         TEXT,
   ipb_deleted           SMALLINT     NOT NULL  DEFAULT 0,
-  ipb_block_email       SMALLINT     NOT NULL  DEFAULT 0
+  ipb_block_email       SMALLINT     NOT NULL  DEFAULT 0,
+  ipb_allow_usertalk    SMALLINT     NOT NULL  DEFAULT 0
 
 );
-CREATE INDEX ipb_address ON ipblocks (ipb_address);
+CREATE UNIQUE INDEX ipb_address_unique ON ipblocks (ipb_address,ipb_user,ipb_auto,ipb_anon_only);
 CREATE INDEX ipb_user    ON ipblocks (ipb_user);
 CREATE INDEX ipb_range   ON ipblocks (ipb_range_start,ipb_range_end);
 
@@ -349,6 +355,7 @@ CREATE TABLE recentchanges (
   rc_params          TEXT
 );
 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_cur_id          ON recentchanges (rc_cur_id);
 CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp);
@@ -362,7 +369,7 @@ CREATE TABLE watchlist (
   wl_notificationtimestamp  TIMESTAMPTZ
 );
 CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
-
+CREATE INDEX wl_user ON watchlist (wl_user);
 
 CREATE TABLE math (
   math_inputhash              BYTEA     NOT NULL  UNIQUE,
@@ -513,6 +520,7 @@ $mw$;
 CREATE TABLE profiling (
   pf_count   INTEGER         NOT NULL DEFAULT 0,
   pf_time    NUMERIC(18,10)  NOT NULL DEFAULT 0,
+  pf_memory  NUMERIC(18,10)  NOT NULL DEFAULT 0,
   pf_name    TEXT            NOT NULL,
   pf_server  TEXT            NULL
 );
@@ -529,6 +537,50 @@ CREATE TABLE protected_titles (
 );
 CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title);
 
+
+CREATE TABLE updatelog (
+  ul_key TEXT NOT NULL PRIMARY KEY
+);
+
+
+CREATE SEQUENCE category_id_seq;
+CREATE TABLE category (
+  cat_id       INTEGER  NOT NULL  PRIMARY KEY DEFAULT nextval('category_id_seq'),
+  cat_title    TEXT     NOT NULL,
+  cat_pages    INTEGER  NOT NULL  DEFAULT 0,
+  cat_subcats  INTEGER  NOT NULL  DEFAULT 0,
+  cat_files    INTEGER  NOT NULL  DEFAULT 0,
+  cat_hidden   SMALLINT NOT NULL  DEFAULT 0
+);
+CREATE UNIQUE INDEX category_title ON category(cat_title);
+CREATE INDEX category_pages ON category(cat_pages);
+
+CREATE TABLE change_tag (
+  ct_rc_id   INTEGER      NULL,
+  ct_log_id  INTEGER      NULL,
+  ct_rev_id  INTEGER      NULL,
+  ct_tag     TEXT     NOT NULL,
+  ct_params  TEXT         NULL
+);
+CREATE UNIQUE INDEX change_tag_rc_tag ON change_tag(ct_rc_id,ct_tag);
+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 TABLE tag_summary (
+  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);
+CREATE UNIQUE INDEX tag_summary_rev_id ON tag_summary(ts_rev_id);
+
+CREATE TABLE valid_tag (
+  vt_tag TEXT NOT NULL PRIMARY KEY
+);
+
 CREATE TABLE mediawiki_version (
   type         TEXT         NOT NULL,
   mw_version   TEXT         NOT NULL,