X-Git-Url: http://git.cyclocoop.org/%22%20.%20generer_url_ecrire%28%22sites_tous%22%29%20.%20%22?a=blobdiff_plain;f=maintenance%2Fpostgres%2Ftables.sql;h=271071b78d36fdcb7da3c3d4504cf1856f24db2a;hb=a0dffb08cec2a2f0d6645852a6526b2742edbcb4;hp=34de2cb2a6a99fa8e5434b288a42cd3583d2900b;hpb=1a21a63d52b9ebf940cd35f041d675d39c9d474a;p=lhc%2Fweb%2Fwiklou.git diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 34de2cb2a6..271071b78d 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -10,6 +10,7 @@ BEGIN; SET client_min_messages = 'ERROR'; DROP SEQUENCE IF EXISTS user_user_id_seq CASCADE; +DROP SEQUENCE IF EXISTS actor_actor_id_seq CASCADE; DROP SEQUENCE IF EXISTS page_page_id_seq CASCADE; DROP SEQUENCE IF EXISTS revision_rev_id_seq CASCADE; DROP SEQUENCE IF EXISTS comment_comment_id_seq CASCADE; @@ -58,6 +59,15 @@ CREATE INDEX user_email_token_idx ON mwuser (user_email_token); INSERT INTO mwuser VALUES (DEFAULT,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,now(),now()); +CREATE SEQUENCE actor_actor_id_seq; +CREATE TABLE actor ( + actor_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('actor_actor_id_seq'), + actor_user INTEGER, + actor_name TEXT NOT NULL +); +CREATE UNIQUE INDEX actor_user ON actor (actor_user); +CREATE UNIQUE INDEX actor_name ON actor (actor_name); + CREATE TABLE user_groups ( ug_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, ug_group TEXT NOT NULL, @@ -134,8 +144,8 @@ CREATE TABLE revision ( rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, rev_text_id INTEGER NULL, -- FK rev_comment TEXT NOT NULL DEFAULT '', - rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED, - rev_user_text TEXT NOT NULL, + rev_user INTEGER NOT NULL DEFAULT 0 REFERENCES mwuser(user_id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED, + rev_user_text TEXT NOT NULL DEFAULT '', rev_timestamp TIMESTAMPTZ NOT NULL, rev_minor_edit SMALLINT NOT NULL DEFAULT 0, rev_deleted SMALLINT NOT NULL DEFAULT 0, @@ -158,6 +168,17 @@ CREATE TABLE revision_comment_temp ( ); CREATE UNIQUE INDEX revcomment_rev ON revision_comment_temp (revcomment_rev); +CREATE TABLE revision_actor_temp ( + revactor_rev INTEGER NOT NULL, + revactor_actor INTEGER NOT NULL, + revactor_timestamp TIMESTAMPTZ NOT NULL, + revactor_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + PRIMARY KEY (revactor_rev, revactor_actor) +); +CREATE UNIQUE INDEX revactor_rev ON revision_actor_temp (revactor_rev); +CREATE INDEX rev_actor_timestamp ON revision_actor_temp (revactor_actor,revactor_timestamp); +CREATE INDEX rev_page_actor_timestamp ON revision_actor_temp (revactor_page,revactor_actor,revactor_timestamp); + CREATE SEQUENCE ip_changes_ipc_rev_id_seq; CREATE TABLE ip_changes ( @@ -221,12 +242,13 @@ CREATE TABLE archive ( ar_sha1 TEXT NOT NULL DEFAULT '', ar_comment TEXT NOT NULL DEFAULT '', ar_comment_id INTEGER NOT NULL DEFAULT 0, - ar_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, - ar_user_text TEXT NOT NULL, + ar_user INTEGER NOT NULL DEFAULT 0 REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + ar_user_text TEXT NOT NULL DEFAULT '', + ar_actor INTEGER NOT NULL DEFAULT 0, 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, @@ -235,17 +257,18 @@ CREATE TABLE archive ( ); 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 INDEX archive_actor ON archive (ar_actor); 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; @@ -347,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 ); @@ -362,8 +385,9 @@ CREATE TABLE ipblocks ( ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_seq'), ipb_address TEXT NULL, ipb_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, - ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + ipb_by INTEGER NOT NULL DEFAULT 0 REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, ipb_by_text TEXT NOT NULL DEFAULT '', + ipb_by_actor INTEGER NOT NULL DEFAULT 0, ipb_reason TEXT NOT NULL DEFAULT '', ipb_reason_id INTEGER NOT NULL DEFAULT 0, ipb_timestamp TIMESTAMPTZ NOT NULL, @@ -397,8 +421,10 @@ CREATE TABLE image ( img_major_mime TEXT DEFAULT 'unknown', img_minor_mime TEXT DEFAULT 'unknown', img_description TEXT NOT NULL DEFAULT '', - img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, - img_user_text TEXT NOT NULL, + 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, img_timestamp TIMESTAMPTZ, img_sha1 TEXT NOT NULL DEFAULT '' ); @@ -422,8 +448,9 @@ CREATE TABLE oldimage ( oi_bits SMALLINT NULL, oi_description TEXT NOT NULL DEFAULT '', oi_description_id INTEGER NOT NULL DEFAULT 0, - oi_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, - oi_user_text TEXT NOT NULL, + oi_user INTEGER NOT NULL DEFAULT 0 REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + oi_user_text TEXT NOT NULL DEFAULT '', + oi_actor INTEGER NOT NULL DEFAULT 0, oi_timestamp TIMESTAMPTZ NULL, oi_metadata BYTEA NOT NULL DEFAULT '', oi_media_type TEXT NULL, @@ -459,8 +486,9 @@ CREATE TABLE filearchive ( fa_minor_mime TEXT DEFAULT 'unknown', fa_description TEXT NOT NULL DEFAULT '', fa_description_id INTEGER NOT NULL DEFAULT 0, - fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, - fa_user_text TEXT NOT NULL, + fa_user INTEGER NOT NULL DEFAULT 0 REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + fa_user_text TEXT NOT NULL DEFAULT '', + fa_actor INTEGER NOT NULL DEFAULT 0, fa_timestamp TIMESTAMPTZ, fa_deleted SMALLINT NOT NULL DEFAULT 0, fa_sha1 TEXT NOT NULL DEFAULT '' @@ -504,8 +532,9 @@ CREATE TABLE recentchanges ( rc_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('recentchanges_rc_id_seq'), rc_timestamp TIMESTAMPTZ NOT NULL, rc_cur_time TIMESTAMPTZ NULL, - rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, - rc_user_text TEXT NOT NULL, + rc_user INTEGER NOT NULL DEFAULT 0 REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + rc_user_text TEXT NOT NULL DEFAULT '', + rc_actor INTEGER NOT NULL DEFAULT 0, rc_namespace SMALLINT NOT NULL, rc_title TEXT NOT NULL, rc_comment TEXT NOT NULL DEFAULT '', @@ -530,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); @@ -605,7 +634,8 @@ CREATE TABLE logging ( log_type TEXT NOT NULL, log_action TEXT NOT NULL, log_timestamp TIMESTAMPTZ NOT NULL, - log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + log_user INTEGER NOT NULL DEFAULT 0 REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + log_actor INTEGER NOT NULL DEFAULT 0, log_namespace SMALLINT NOT NULL, log_title TEXT NOT NULL, log_comment TEXT NOT NULL DEFAULT '', @@ -617,12 +647,15 @@ CREATE TABLE logging ( ); CREATE INDEX logging_type_name ON logging (log_type, log_timestamp); CREATE INDEX logging_user_time ON logging (log_timestamp, log_user); +CREATE INDEX logging_actor_time_backwards ON logging (log_timestamp, log_actor); CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp); CREATE INDEX logging_times ON logging (log_timestamp); CREATE INDEX logging_user_type_time ON logging (log_user, log_type, log_timestamp); +CREATE INDEX logging_actor_type_time ON logging (log_actor, log_type, log_timestamp); CREATE INDEX logging_page_id_time ON logging (log_page, log_timestamp); CREATE INDEX logging_user_text_type_time ON logging (log_user_text, log_type, log_timestamp); CREATE INDEX logging_user_text_time ON logging (log_user_text, log_timestamp); +CREATE INDEX logging_actor_time ON logging (log_actor, log_timestamp); CREATE TABLE log_search ( ls_field TEXT NOT NULL,