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;
user_editcount INTEGER,
user_password_expires TIMESTAMPTZ NULL
);
+ALTER SEQUENCE user_user_id_seq OWNED BY mwuser.user_id;
CREATE INDEX user_email_token_idx ON mwuser (user_email_token);
-- Create a dummy user to satisfy fk contraints especially with revisions
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
+);
+ALTER SEQUENCE actor_actor_id_seq OWNED BY actor.actor_id;
+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,
page_content_model TEXT,
page_lang TEXT DEFAULT NULL
);
+ALTER SEQUENCE page_page_id_seq OWNED BY page.page_id;
CREATE UNIQUE INDEX page_unique_name ON page (page_namespace, page_title);
CREATE INDEX page_main_title ON page (page_title text_pattern_ops) WHERE page_namespace = 0;
CREATE INDEX page_talk_title ON page (page_title text_pattern_ops) WHERE page_namespace = 1;
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,
rev_content_model TEXT,
rev_content_format TEXT
);
+ALTER SEQUENCE revision_rev_id_seq OWNED BY revision.rev_id;
CREATE UNIQUE INDEX revision_unique ON revision (rev_page, rev_id);
CREATE INDEX rev_text_id_idx ON revision (rev_text_id);
CREATE INDEX rev_timestamp_idx ON revision (rev_timestamp);
);
CREATE UNIQUE INDEX revcomment_rev ON revision_comment_temp (revcomment_rev);
-CREATE SEQUENCE ip_changes_ipc_rev_id_seq;
+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 (
ipc_rev_id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('ip_changes_ipc_rev_id_seq'),
ipc_rev_timestamp TIMESTAMPTZ NOT NULL,
ipc_hex BYTEA NOT NULL DEFAULT ''
);
-
+ALTER SEQUENCE ip_changes_ipc_rev_id_seq OWNED BY ip_changes.ipc_rev_id;
CREATE INDEX ipc_rev_timestamp ON ip_changes (ipc_rev_timestamp);
CREATE INDEX ipc_hex_time ON ip_changes (ipc_hex,ipc_rev_timestamp);
old_text TEXT,
old_flags TEXT
);
+ALTER SEQUENCE text_old_id_seq OWNED BY pagecontent.old_id;
CREATE SEQUENCE comment_comment_id_seq;
comment_text TEXT NOT NULL,
comment_data TEXT
);
+ALTER SEQUENCE comment_comment_id_seq OWNED BY comment.comment_id;
CREATE INDEX comment_hash ON comment (comment_hash);
pr_user INTEGER NULL,
pr_expiry TIMESTAMPTZ NULL
);
+ALTER SEQUENCE page_restrictions_pr_id_seq OWNED BY page_restrictions.pr_id;
ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type);
CREATE TABLE page_props (
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,
ar_content_model TEXT,
ar_content_format TEXT
);
+ALTER SEQUENCE archive_ar_id_seq OWNED BY archive.ar_id;
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;
content_model SMALLINT NOT NULL,
content_address TEXT NOT NULL
);
+ALTER SEQUENCE content_content_id_seq OWNED BY content.content_id;
CREATE SEQUENCE slot_roles_role_id_seq;
role_id SMALLINT NOT NULL PRIMARY KEY DEFAULT nextval('slot_roles_role_id_seq'),
role_name TEXT NOT NULL
);
+ALTER SEQUENCE slot_roles_role_id_seq OWNED BY slot_roles.role_id;
CREATE UNIQUE INDEX role_name ON slot_roles (role_name);
model_id SMALLINT NOT NULL PRIMARY KEY DEFAULT nextval('content_models_model_id_seq'),
model_name TEXT NOT NULL
);
+ALTER SEQUENCE content_models_model_id_seq OWNED BY content_models.model_id;
CREATE UNIQUE INDEX model_name ON content_models (model_name);
el_index TEXT NOT NULL,
el_index_60 BYTEA NOT NULL DEFAULT ''
);
+ALTER SEQUENCE externallinks_el_id_seq OWNED BY externallinks.el_id;
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 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
);
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,
ipb_block_email SMALLINT NOT NULL DEFAULT 0,
ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0,
ipb_parent_block_id INTEGER NULL REFERENCES ipblocks(ipb_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
-
);
+ALTER SEQUENCE ipblocks_ipb_id_seq OWNED BY ipblocks.ipb_id;
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);
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 ''
);
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,
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 ''
);
+ALTER SEQUENCE filearchive_fa_id_seq OWNED BY filearchive.fa_id;
CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp);
CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key);
CREATE INDEX fa_notime ON filearchive (fa_deleted_timestamp);
CREATE SEQUENCE uploadstash_us_id_seq;
CREATE TYPE media_type AS ENUM ('UNKNOWN','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE','3D');
-
CREATE TABLE uploadstash (
us_id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('uploadstash_us_id_seq'),
us_user INTEGER,
us_image_height INTEGER,
us_image_bits SMALLINT
);
+ALTER SEQUENCE uploadstash_us_id_seq OWNED BY uploadstash.us_id;
CREATE INDEX us_user_idx ON uploadstash (us_user);
CREATE UNIQUE INDEX us_key_idx ON uploadstash (us_key);
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 '',
rc_log_action TEXT,
rc_params TEXT
);
+ALTER SEQUENCE recentchanges_rc_id_seq OWNED BY recentchanges.rc_id;
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);
wl_title TEXT NOT NULL,
wl_notificationtimestamp TIMESTAMPTZ
);
+ALTER SEQUENCE watchlist_wl_id_seq OWNED BY watchlist.wl_id;
CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
CREATE INDEX wl_user ON watchlist (wl_user);
CREATE INDEX wl_user_notificationtimestamp ON watchlist (wl_user, wl_notificationtimestamp);
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 '',
log_user_text TEXT NOT NULL DEFAULT '',
log_page INTEGER
);
+ALTER SEQUENCE logging_log_id_seq OWNED BY logging.log_id;
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,
job_token_timestamp TIMESTAMPTZ,
job_sha1 TEXT NOT NULL DEFAULT ''
);
+ALTER SEQUENCE job_job_id_seq OWNED BY job.job_id;
CREATE INDEX job_sha1 ON job (job_sha1);
CREATE INDEX job_cmd_token ON job (job_cmd, job_token, job_random);
CREATE INDEX job_cmd_token_id ON job (job_cmd, job_token, job_id);
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;
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);
cat_files INTEGER NOT NULL DEFAULT 0,
cat_hidden SMALLINT NOT NULL DEFAULT 0
);
+ALTER SEQUENCE category_cat_id_seq OWNED BY category.cat_id;
CREATE UNIQUE INDEX category_title ON category(cat_title);
CREATE INDEX category_pages ON category(cat_pages);
ct_tag TEXT NOT NULL,
ct_params TEXT NULL
);
+ALTER SEQUENCE change_tag_ct_id_seq OWNED BY change_tag.ct_id;
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);
ts_rev_id INTEGER NULL,
ts_tags TEXT NOT NULL
);
+ALTER SEQUENCE tag_summary_ts_id_seq OWNED BY tag_summary.ts_id;
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);
site_forward SMALLINT NOT NULL,
site_config TEXT NOT NULL
);
+ALTER SEQUENCE sites_site_id_seq OWNED BY sites.site_id;
CREATE UNIQUE INDEX site_global_key ON sites (site_global_key);
CREATE INDEX site_type ON sites (site_type);
CREATE INDEX site_group ON sites (site_group);
dépôts / lhc / web / wiklou.git / blobdiff