X-Git-Url: https://git.cyclocoop.org/%242?a=blobdiff_plain;f=maintenance%2Foracle%2Ftables.sql;h=058ef1532137c64cd7c9551514f248ae420a186f;hb=0b2b8e94cd306195a6999c122184b92d4d643f83;hp=d588e3a67cc3951444329d94552a12d296feff39;hpb=e440dc9385cbed23ab7148932b925a1bf8e78785;p=lhc%2Fweb%2Fwiklou.git diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index d588e3a67c..058ef15321 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -60,6 +60,26 @@ INSERT INTO &mw_prefix.mwuser (user_id, user_name, user_options, user_touched, user_registration, user_editcount) VALUES (0,'Anonymous','', current_timestamp, current_timestamp,0); +CREATE SEQUENCE actor_actor_id_seq; +CREATE TABLE &mw_prefix.actor ( + actor_id NUMBER NOT NULL, + actor_user NUMBER, + actor_name VARCHAR2(255) NOT NULL +); + +ALTER TABLE &mw_prefix.actor ADD CONSTRAINT &mw_prefix.actor_pk PRIMARY KEY (actor_id); + +/*$mw$*/ +CREATE TRIGGER &mw_prefix.actor_seq_trg BEFORE INSERT ON &mw_prefix.actor + FOR EACH ROW WHEN (new.actor_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(actor_actor_id_seq.nextval, :new.actor_id); +END; +/*$mw$*/ + +-- Create a dummy actor to satisfy fk contraints +INSERT INTO &mw_prefix.actor (actor_id, actor_name) VALUES (0,'##Anonymous##'); + CREATE TABLE &mw_prefix.user_groups ( ug_user NUMBER DEFAULT 0 NOT NULL, ug_group VARCHAR2(255) NOT NULL, @@ -135,6 +155,25 @@ BEGIN END; /*$mw$*/ +CREATE SEQUENCE comment_comment_id_seq; +CREATE TABLE &mw_prefix."COMMENT" ( + comment_id NUMBER NOT NULL, + comment_hash NUMBER NOT NULL, + comment_text CLOB, + comment_data CLOB +); +CREATE INDEX &mw_prefix.comment_hash ON &mw_prefix."COMMENT" (comment_hash); +/*$mw$*/ +CREATE TRIGGER &mw_prefix.comment_seq_trg BEFORE INSERT ON &mw_prefix."COMMENT" + FOR EACH ROW WHEN (new.comment_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(comment_comment_id_seq.nextval, :new.comment_id); +END; +/*$mw$*/ + +-- dummy row for FKs. Hash is intentionally wrong so CommentStore won't match it. +INSERT INTO &mw_prefix."COMMENT" (comment_hash, comment_text) VALUES (-1, '** dummy **'); + CREATE SEQUENCE revision_rev_id_seq; CREATE TABLE &mw_prefix.revision ( rev_id NUMBER NOT NULL, @@ -169,6 +208,26 @@ BEGIN END; /*$mw$*/ +CREATE TABLE &mw_prefix.revision_comment_temp ( + revcomment_rev NUMBER NOT NULL, + revcomment_comment_id NUMBER NOT NULL +); +ALTER TABLE &mw_prefix.revision_comment_temp ADD CONSTRAINT &mw_prefix.revision_comment_temp_pk PRIMARY KEY (revcomment_rev, revcomment_comment_id); +ALTER TABLE &mw_prefix.revision_comment_temp ADD CONSTRAINT &mw_prefix.revision_comment_temp_fk1 FOREIGN KEY (revcomment_rev) REFERENCES &mw_prefix.revision(rev_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.revision_comment_temp ADD CONSTRAINT &mw_prefix.revision_comment_temp_fk2 FOREIGN KEY (revcomment_comment_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +CREATE UNIQUE INDEX &mw_prefix.revcomment_rev ON &mw_prefix.revision_comment_temp (revcomment_rev); + +CREATE TABLE &mw_prefix.revision_actor_temp ( + revactor_rev NUMBER NOT NULL, + revactor_actor NUMBER NOT NULL, + revactor_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL, + revactor_page NUMBER NOT NULL +); +ALTER TABLE &mw_prefix.revision_actor_temp ADD CONSTRAINT &mw_prefix.revision_actor_temp_pk PRIMARY KEY (revactor_rev, revactor_actor); +CREATE UNIQUE INDEX &mw_prefix.revactor_rev ON &mw_prefix.revision_actor_temp (revactor_rev); +CREATE INDEX &mw_prefix.actor_timestamp ON &mw_prefix.revision_actor_temp (revactor_actor,revactor_timestamp); +CREATE INDEX &mw_prefix.page_actor_timestamp ON &mw_prefix.revision_actor_temp (revactor_page,revactor_actor,revactor_timestamp); + CREATE SEQUENCE text_old_id_seq; CREATE TABLE &mw_prefix.pagecontent ( -- replaces reserved word 'text' old_id NUMBER NOT NULL, @@ -191,8 +250,10 @@ CREATE TABLE &mw_prefix.archive ( ar_title VARCHAR2(255) NOT NULL, ar_text CLOB, ar_comment VARCHAR2(255), + ar_comment_id NUMBER DEFAULT 0 NOT NULL, ar_user NUMBER DEFAULT 0 NOT NULL, - ar_user_text VARCHAR2(255) NOT NULL, + ar_user_text VARCHAR2(255) NULL, + ar_actor NUMBER DEFAULT 0 NOT NULL, ar_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL, ar_minor_edit CHAR(1) DEFAULT '0' NOT NULL, ar_flags VARCHAR2(255), @@ -208,8 +269,10 @@ CREATE TABLE &mw_prefix.archive ( ); ALTER TABLE &mw_prefix.archive ADD CONSTRAINT &mw_prefix.archive_pk PRIMARY KEY (ar_id); ALTER TABLE &mw_prefix.archive ADD CONSTRAINT &mw_prefix.archive_fk1 FOREIGN KEY (ar_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.archive ADD CONSTRAINT &mw_prefix.archive_fk2 FOREIGN KEY (ar_comment_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.archive_i01 ON &mw_prefix.archive (ar_namespace,ar_title,ar_timestamp); CREATE INDEX &mw_prefix.archive_i02 ON &mw_prefix.archive (ar_user_text,ar_timestamp); +CREATE INDEX &mw_prefix.ar_actor_timestamp ON &mw_prefix.archive (ar_actor,ar_timestamp); CREATE INDEX &mw_prefix.archive_i03 ON &mw_prefix.archive (ar_rev_id); /*$mw$*/ CREATE TRIGGER &mw_prefix.archive_seq_trg BEFORE INSERT ON &mw_prefix.archive @@ -219,6 +282,78 @@ BEGIN END; /*$mw$*/ + +CREATE TABLE &mw_prefix.slots ( + slot_revision_id NUMBER NOT NULL, + slot_role_id NUMBER NOT NULL, + slot_content_id NUMBER NOT NULL, + slot_origin NUMBER NOT NULL +); + +ALTER TABLE &mw_prefix.slots ADD CONSTRAINT &mw_prefix.slots_pk PRIMARY KEY (slot_revision_id, slot_role_id); + +CREATE INDEX &mw_prefix.slot_revision_origin_role ON &mw_prefix.slots (slot_revision_id, slot_origin, slot_role_id); + + +CREATE SEQUENCE content_content_id_seq; +CREATE TABLE &mw_prefix.content ( + content_id NUMBER NOT NULL, + content_size NUMBER NOT NULL, + content_sha1 VARCHAR2(32) NOT NULL, + content_model NUMBER NOT NULL, + content_address VARCHAR2(255) NOT NULL +); + +ALTER TABLE &mw_prefix.content ADD CONSTRAINT &mw_prefix.content_pk PRIMARY KEY (content_id); + +/*$mw$*/ +CREATE TRIGGER &mw_prefix.content_seq_trg BEFORE INSERT ON &mw_prefix.content + FOR EACH ROW WHEN (new.content_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(content_content_id_seq.nextval, :new.content_id); +END; +/*$mw$*/ + + +CREATE SEQUENCE slot_roles_role_id_seq; +CREATE TABLE &mw_prefix.slot_roles ( + role_id NUMBER NOT NULL, + role_name VARCHAR2(64) NOT NULL +); + +ALTER TABLE &mw_prefix.slot_roles ADD CONSTRAINT &mw_prefix.slot_roles_pk PRIMARY KEY (role_id); + +CREATE UNIQUE INDEX &mw_prefix.role_name_u01 ON &mw_prefix.slot_roles (role_name); + +/*$mw$*/ +CREATE TRIGGER &mw_prefix.slot_roles_seq_trg BEFORE INSERT ON &mw_prefix.slot_roles + FOR EACH ROW WHEN (new.role_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(slot_roles_role_id_seq.nextval, :new.role_id); +END; +/*$mw$*/ + + +CREATE SEQUENCE content_models_model_id_seq; +CREATE TABLE &mw_prefix.content_models ( + model_id NUMBER NOT NULL, + model_name VARCHAR2(64) NOT NULL +); + + +ALTER TABLE &mw_prefix.content_models ADD CONSTRAINT &mw_prefix.content_models_pk PRIMARY KEY (model_id); + +CREATE UNIQUE INDEX &mw_prefix.model_name_u01 ON &mw_prefix.content_models (model_name); + +/*$mw$*/ +CREATE TRIGGER &mw_prefix.content_models_seq_trg BEFORE INSERT ON &mw_prefix.content_models + FOR EACH ROW WHEN (new.model_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(content_models_model_id_seq.nextval, :new.model_id); +END; +/*$mw$*/ + + CREATE TABLE &mw_prefix.pagelinks ( pl_from NUMBER NOT NULL, pl_namespace NUMBER DEFAULT 0 NOT NULL, @@ -337,7 +472,9 @@ CREATE TABLE &mw_prefix.ipblocks ( ipb_user NUMBER DEFAULT 0 NOT NULL, ipb_by NUMBER DEFAULT 0 NOT NULL, ipb_by_text VARCHAR2(255) NULL, - ipb_reason VARCHAR2(255) NOT NULL, + ipb_by_actor NUMBER DEFUALT 0 NOT NULL, + ipb_reason VARCHAR2(255) NULL, + ipb_reason_id NUMBER DEFAULT 0 NOT NULL, ipb_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL, ipb_auto CHAR(1) DEFAULT '0' NOT NULL, ipb_anon_only CHAR(1) DEFAULT '0' NOT NULL, @@ -354,6 +491,7 @@ CREATE TABLE &mw_prefix.ipblocks ( ALTER TABLE &mw_prefix.ipblocks ADD CONSTRAINT &mw_prefix.ipblocks_pk PRIMARY KEY (ipb_id); ALTER TABLE &mw_prefix.ipblocks ADD CONSTRAINT &mw_prefix.ipblocks_fk1 FOREIGN KEY (ipb_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; ALTER TABLE &mw_prefix.ipblocks ADD CONSTRAINT &mw_prefix.ipblocks_fk2 FOREIGN KEY (ipb_by) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.ipblocks ADD CONSTRAINT &mw_prefix.ipblocks_fk3 FOREIGN KEY (ipb_reason_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE UNIQUE INDEX &mw_prefix.ipblocks_u01 ON &mw_prefix.ipblocks (ipb_address, ipb_user, ipb_auto, ipb_anon_only); CREATE INDEX &mw_prefix.ipblocks_i01 ON &mw_prefix.ipblocks (ipb_user); CREATE INDEX &mw_prefix.ipblocks_i02 ON &mw_prefix.ipblocks (ipb_range_start, ipb_range_end); @@ -380,7 +518,8 @@ CREATE TABLE &mw_prefix.image ( img_minor_mime VARCHAR2(100) DEFAULT 'unknown', img_description VARCHAR2(255), img_user NUMBER DEFAULT 0 NOT NULL, - img_user_text VARCHAR2(255) NOT NULL, + img_user_text VARCHAR2(255) NULL, + img_actor NUMBER DEFAULT 0 NOT NULL, img_timestamp TIMESTAMP(6) WITH TIME ZONE, img_sha1 VARCHAR2(32) ); @@ -390,6 +529,16 @@ CREATE INDEX &mw_prefix.image_i01 ON &mw_prefix.image (img_user_text,img_timesta CREATE INDEX &mw_prefix.image_i02 ON &mw_prefix.image (img_size); CREATE INDEX &mw_prefix.image_i03 ON &mw_prefix.image (img_timestamp); CREATE INDEX &mw_prefix.image_i04 ON &mw_prefix.image (img_sha1); +CREATE INDEX &mw_prefix.img_actor_timestamp ON &mw_prefix.image (img_actor, img_timestamp); + +CREATE TABLE &mw_prefix.image_comment_temp ( + imgcomment_name VARCHAR2(255) NOT NULL, + imgcomment_description_id NUMBER NOT NULL +); +ALTER TABLE &mw_prefix.image_comment_temp ADD CONSTRAINT &mw_prefix.image_comment_temp_pk PRIMARY KEY (imgcomment_name, imgcomment_description_id); +ALTER TABLE &mw_prefix.image_comment_temp ADD CONSTRAINT &mw_prefix.image_comment_temp_fk1 FOREIGN KEY (imgcomment_name) REFERENCES &mw_prefix.image(img_name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.image_comment_temp ADD CONSTRAINT &mw_prefix.image_comment_temp_fk2 FOREIGN KEY (imgcomment_description_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +CREATE UNIQUE INDEX &mw_prefix.imgcomment_name ON &mw_prefix.image_comment_temp (imgcomment_name); CREATE TABLE &mw_prefix.oldimage ( @@ -400,8 +549,10 @@ CREATE TABLE &mw_prefix.oldimage ( oi_height NUMBER DEFAULT 0 NOT NULL, oi_bits NUMBER DEFAULT 0 NOT NULL, oi_description VARCHAR2(255), + oi_description_id NUMBER DEFAULT 0 NOT NULL, oi_user NUMBER DEFAULT 0 NOT NULL, - oi_user_text VARCHAR2(255) NOT NULL, + oi_user_text VARCHAR2(255) NULL, + oi_actor NUMBER DEFAULT 0 NOT NULL, oi_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL, oi_metadata CLOB, oi_media_type VARCHAR2(32) DEFAULT NULL, @@ -412,7 +563,9 @@ CREATE TABLE &mw_prefix.oldimage ( ); ALTER TABLE &mw_prefix.oldimage ADD CONSTRAINT &mw_prefix.oldimage_fk1 FOREIGN KEY (oi_name) REFERENCES &mw_prefix.image(img_name) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; ALTER TABLE &mw_prefix.oldimage ADD CONSTRAINT &mw_prefix.oldimage_fk2 FOREIGN KEY (oi_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.oldimage ADD CONSTRAINT &mw_prefix.oldimage_fk3 FOREIGN KEY (oi_description_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.oldimage_i01 ON &mw_prefix.oldimage (oi_user_text,oi_timestamp); +CREATE INDEX &mw_prefix.oi_actor_timestamp ON &mw_prefix.oldimage (oi_actor,oi_timestamp); CREATE INDEX &mw_prefix.oldimage_i02 ON &mw_prefix.oldimage (oi_name,oi_timestamp); CREATE INDEX &mw_prefix.oldimage_i03 ON &mw_prefix.oldimage (oi_name,oi_archive_name); CREATE INDEX &mw_prefix.oldimage_i04 ON &mw_prefix.oldimage (oi_sha1); @@ -428,6 +581,7 @@ CREATE TABLE &mw_prefix.filearchive ( fa_deleted_user NUMBER DEFAULT 0 NOT NULL, fa_deleted_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL, fa_deleted_reason CLOB, + fa_deleted_reason_id NUMBER DEFAULT 0 NOT NULL, fa_size NUMBER DEFAULT 0 NOT NULL, fa_width NUMBER DEFAULT 0 NOT NULL, fa_height NUMBER DEFAULT 0 NOT NULL, @@ -437,8 +591,10 @@ CREATE TABLE &mw_prefix.filearchive ( fa_major_mime VARCHAR2(32) DEFAULT 'unknown', fa_minor_mime VARCHAR2(100) DEFAULT 'unknown', fa_description VARCHAR2(255), + fa_description_id NUMBER DEFAULT 0 NOT NULL, fa_user NUMBER DEFAULT 0 NOT NULL, - fa_user_text VARCHAR2(255) NOT NULL, + fa_user_text VARCHAR2(255) NULL, + fa_actor NUMBER DEFAULT 0 NOT NULL, fa_timestamp TIMESTAMP(6) WITH TIME ZONE, fa_deleted NUMBER DEFAULT 0 NOT NULL, fa_sha1 VARCHAR2(32) @@ -446,10 +602,13 @@ CREATE TABLE &mw_prefix.filearchive ( ALTER TABLE &mw_prefix.filearchive ADD CONSTRAINT &mw_prefix.filearchive_pk PRIMARY KEY (fa_id); ALTER TABLE &mw_prefix.filearchive ADD CONSTRAINT &mw_prefix.filearchive_fk1 FOREIGN KEY (fa_deleted_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; ALTER TABLE &mw_prefix.filearchive ADD CONSTRAINT &mw_prefix.filearchive_fk2 FOREIGN KEY (fa_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.filearchive ADD CONSTRAINT &mw_prefix.filearchive_fk3 FOREIGN KEY (fa_deleted_reason_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.filearchive ADD CONSTRAINT &mw_prefix.filearchive_fk4 FOREIGN KEY (fa_description_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.filearchive_i01 ON &mw_prefix.filearchive (fa_name, fa_timestamp); CREATE INDEX &mw_prefix.filearchive_i02 ON &mw_prefix.filearchive (fa_storage_group, fa_storage_key); CREATE INDEX &mw_prefix.filearchive_i03 ON &mw_prefix.filearchive (fa_deleted_timestamp); CREATE INDEX &mw_prefix.filearchive_i04 ON &mw_prefix.filearchive (fa_user_text,fa_timestamp); +CREATE INDEX &mw_prefix.fa_actor_timestamp ON &mw_prefix.filearchive (fa_actor,fa_timestamp); CREATE INDEX &mw_prefix.filearchive_i05 ON &mw_prefix.filearchive (fa_sha1); /*$mw$*/ CREATE TRIGGER &mw_prefix.filearchive_seq_trg BEFORE INSERT ON &mw_prefix.filearchive @@ -498,10 +657,12 @@ CREATE TABLE &mw_prefix.recentchanges ( rc_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL, rc_cur_time TIMESTAMP(6) WITH TIME ZONE, rc_user NUMBER DEFAULT 0 NOT NULL, - rc_user_text VARCHAR2(255) NOT NULL, + rc_user_text VARCHAR2(255) NULL, + rc_actor NUMBER DEFAULT 0 NOT NULL, rc_namespace NUMBER DEFAULT 0 NOT NULL, rc_title VARCHAR2(255) NOT NULL, rc_comment VARCHAR2(255), + rc_comment_id NUMBER DEFAULT 0 NOT NULL, rc_minor CHAR(1) DEFAULT '0' NOT NULL, rc_bot CHAR(1) DEFAULT '0' NOT NULL, rc_new CHAR(1) DEFAULT '0' NOT NULL, @@ -523,6 +684,7 @@ CREATE TABLE &mw_prefix.recentchanges ( ALTER TABLE &mw_prefix.recentchanges ADD CONSTRAINT &mw_prefix.recentchanges_pk PRIMARY KEY (rc_id); ALTER TABLE &mw_prefix.recentchanges ADD CONSTRAINT &mw_prefix.recentchanges_fk1 FOREIGN KEY (rc_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; ALTER TABLE &mw_prefix.recentchanges ADD CONSTRAINT &mw_prefix.recentchanges_fk2 FOREIGN KEY (rc_cur_id) REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.recentchanges ADD CONSTRAINT &mw_prefix.recentchanges_fk3 FOREIGN KEY (rc_comment_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.recentchanges_i01 ON &mw_prefix.recentchanges (rc_timestamp); CREATE INDEX &mw_prefix.recentchanges_i02 ON &mw_prefix.recentchanges (rc_namespace, rc_title); CREATE INDEX &mw_prefix.recentchanges_i03 ON &mw_prefix.recentchanges (rc_cur_id); @@ -530,6 +692,8 @@ CREATE INDEX &mw_prefix.recentchanges_i04 ON &mw_prefix.recentchanges (rc_new,rc CREATE INDEX &mw_prefix.recentchanges_i05 ON &mw_prefix.recentchanges (rc_ip); CREATE INDEX &mw_prefix.recentchanges_i06 ON &mw_prefix.recentchanges (rc_namespace, rc_user_text); CREATE INDEX &mw_prefix.recentchanges_i07 ON &mw_prefix.recentchanges (rc_user_text, rc_timestamp); +CREATE INDEX &mw_prefix.rc_ns_actor ON &mw_prefix.recentchanges (rc_namespace, rc_actor); +CREATE INDEX &mw_prefix.rc_actor ON &mw_prefix.recentchanges (rc_actor, rc_timestamp); CREATE INDEX &mw_prefix.recentchanges_i08 ON &mw_prefix.recentchanges (rc_namespace, rc_type, rc_patrolled, rc_timestamp); /*$mw$*/ CREATE TRIGGER &mw_prefix.recentchanges_seq_trg BEFORE INSERT ON &mw_prefix.recentchanges @@ -600,15 +764,18 @@ CREATE TABLE &mw_prefix.logging ( log_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL, log_user NUMBER DEFAULT 0 NOT NULL, log_user_text VARCHAR2(255), + log_actor NUMBER DEFAULT 0 NOT NULL, log_namespace NUMBER DEFAULT 0 NOT NULL, log_title VARCHAR2(255) NOT NULL, log_page NUMBER, log_comment VARCHAR2(255), + log_comment_id NUMBER DEFAULT 0 NOT NULL, log_params CLOB, log_deleted CHAR(1) DEFAULT '0' NOT NULL ); ALTER TABLE &mw_prefix.logging ADD CONSTRAINT &mw_prefix.logging_pk PRIMARY KEY (log_id); ALTER TABLE &mw_prefix.logging ADD CONSTRAINT &mw_prefix.logging_fk1 FOREIGN KEY (log_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.logging ADD CONSTRAINT &mw_prefix.logging_fk2 FOREIGN KEY (log_comment_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.logging_i01 ON &mw_prefix.logging (log_type, log_timestamp); CREATE INDEX &mw_prefix.logging_i02 ON &mw_prefix.logging (log_user, log_timestamp); CREATE INDEX &mw_prefix.logging_i03 ON &mw_prefix.logging (log_namespace, log_title, log_timestamp); @@ -616,6 +783,8 @@ CREATE INDEX &mw_prefix.logging_i04 ON &mw_prefix.logging (log_timestamp); CREATE INDEX &mw_prefix.logging_i05 ON &mw_prefix.logging (log_type, log_action, log_timestamp); CREATE INDEX &mw_prefix.logging_i06 ON &mw_prefix.logging (log_user_text, log_type, log_timestamp); CREATE INDEX &mw_prefix.logging_i07 ON &mw_prefix.logging (log_user_text, log_timestamp); +CREATE INDEX &mw_prefix.actor_time ON &mw_prefix.logging (log_actor, log_timestamp); +CREATE INDEX &mw_prefix.log_actor_type_time ON &mw_prefix.logging (log_actor, log_type, log_timestamp); /*$mw$*/ CREATE TRIGGER &mw_prefix.logging_seq_trg BEFORE INSERT ON &mw_prefix.logging FOR EACH ROW WHEN (new.log_id IS NULL) @@ -718,10 +887,12 @@ CREATE TABLE &mw_prefix.protected_titles ( pt_title VARCHAR2(255) NOT NULL, pt_user NUMBER NOT NULL, pt_reason VARCHAR2(255), + pt_reason_id NUMBER DEFAULT 0 NOT NULL, pt_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL, pt_expiry VARCHAR2(14) NOT NULL, pt_create_perm VARCHAR2(60) NOT NULL ); +ALTER TABLE &mw_prefix.protected_titles ADD CONSTRAINT &mw_prefix.protected_titles_fk1 FOREIGN KEY (pt_reason_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE UNIQUE INDEX &mw_prefix.protected_titles_u01 ON &mw_prefix.protected_titles (pt_namespace,pt_title); CREATE INDEX &mw_prefix.protected_titles_i01 ON &mw_prefix.protected_titles (pt_timestamp);