Trigger name changes

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

diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql
index fc3c696..d588e3a 100644 (file)
--- a/maintenance/oracle/tables.sql
+++ b/maintenance/oracle/tables.sql
@@ -1,6 +1,28 @@
 -- defines must comply with ^define\s*([^\s=]*)\s*=\s?'\{\$([^\}]*)\}';
 define mw_prefix='{$wgDBprefix}';
 
+-- Package to help with making Oracle more like other DBs with respect to
+-- auto-incrementing columns.
+/*$mw$*/
+CREATE PACKAGE &mw_prefix.lastval_pkg IS
+  lastval NUMBER;
+  PROCEDURE setLastval(val IN NUMBER, field OUT NUMBER);
+  FUNCTION getLastval RETURN NUMBER;
+END;
+/*$mw$*/
+
+/*$mw$*/
+CREATE PACKAGE BODY &mw_prefix.lastval_pkg IS
+  PROCEDURE setLastval(val IN NUMBER, field OUT NUMBER) IS BEGIN
+    lastval := val;
+    field := val;
+  END;
+
+  FUNCTION getLastval RETURN NUMBER IS BEGIN
+    RETURN lastval;
+  END;
+END;
+/*$mw$*/
 
 CREATE SEQUENCE user_user_id_seq;
 CREATE TABLE &mw_prefix.mwuser ( -- replace reserved word 'user'
@@ -25,6 +47,13 @@ ALTER TABLE &mw_prefix.mwuser ADD CONSTRAINT &mw_prefix.mwuser_pk PRIMARY KEY (u
 CREATE UNIQUE INDEX &mw_prefix.mwuser_u01 ON &mw_prefix.mwuser (user_name);
 CREATE INDEX &mw_prefix.mwuser_i01 ON &mw_prefix.mwuser (user_email_token);
 CREATE INDEX &mw_prefix.mwuser_i02 ON &mw_prefix.mwuser (user_email, user_name);
+/*$mw$*/
+CREATE TRIGGER &mw_prefix.mwuser_seq_trg BEFORE INSERT ON &mw_prefix.mwuser
+       FOR EACH ROW WHEN (new.user_id IS NULL)
+BEGIN
+       &mw_prefix.lastval_pkg.setLastval(user_user_id_seq.nextval, :new.user_id);
+END;
+/*$mw$*/
 
 -- Create a dummy user to satisfy fk contraints especially with revisions
 INSERT INTO &mw_prefix.mwuser
@@ -86,6 +115,13 @@ CREATE UNIQUE INDEX &mw_prefix.page_u01 ON &mw_prefix.page (page_namespace,page_
 CREATE INDEX &mw_prefix.page_i01 ON &mw_prefix.page (page_random);
 CREATE INDEX &mw_prefix.page_i02 ON &mw_prefix.page (page_len);
 CREATE INDEX &mw_prefix.page_i03 ON &mw_prefix.page (page_is_redirect, page_namespace, page_len);
+/*$mw$*/
+CREATE TRIGGER &mw_prefix.page_seq_trg BEFORE INSERT ON &mw_prefix.page
+       FOR EACH ROW WHEN (new.page_id IS NULL)
+BEGIN
+       &mw_prefix.lastval_pkg.setLastval(page_page_id_seq.nextval, :new.page_id);
+END;
+/*$mw$*/
 
 -- Create a dummy page to satisfy fk contraints especially with revisions
 INSERT INTO &mw_prefix.page
@@ -125,6 +161,13 @@ CREATE INDEX &mw_prefix.revision_i02 ON &mw_prefix.revision (rev_page,rev_timest
 CREATE INDEX &mw_prefix.revision_i03 ON &mw_prefix.revision (rev_user,rev_timestamp);
 CREATE INDEX &mw_prefix.revision_i04 ON &mw_prefix.revision (rev_user_text,rev_timestamp);
 CREATE INDEX &mw_prefix.revision_i05 ON &mw_prefix.revision (rev_page,rev_user,rev_timestamp);
+/*$mw$*/
+CREATE TRIGGER &mw_prefix.revision_seq_trg BEFORE INSERT ON &mw_prefix.revision
+       FOR EACH ROW WHEN (new.rev_id IS NULL)
+BEGIN
+       &mw_prefix.lastval_pkg.setLastval(revision_rev_id_seq.nextval, :new.rev_id);
+END;
+/*$mw$*/
 
 CREATE SEQUENCE text_old_id_seq;
 CREATE TABLE &mw_prefix.pagecontent ( -- replaces reserved word 'text'
@@ -133,6 +176,13 @@ CREATE TABLE &mw_prefix.pagecontent ( -- replaces reserved word 'text'
   old_flags  VARCHAR2(255)
 );
 ALTER TABLE &mw_prefix.pagecontent ADD CONSTRAINT &mw_prefix.pagecontent_pk PRIMARY KEY (old_id);
+/*$mw$*/
+CREATE TRIGGER &mw_prefix.pagecontent_seq_trg BEFORE INSERT ON &mw_prefix.pagecontent
+       FOR EACH ROW WHEN (new.old_id IS NULL)
+BEGIN
+       &mw_prefix.lastval_pkg.setLastval(text_old_id_seq.nextval, :new.old_id);
+END;
+/*$mw$*/
 
 CREATE SEQUENCE archive_ar_id_seq;
 CREATE TABLE &mw_prefix.archive (
@@ -161,6 +211,13 @@ ALTER TABLE &mw_prefix.archive ADD CONSTRAINT &mw_prefix.archive_fk1 FOREIGN KEY
 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.archive_i03 ON &mw_prefix.archive (ar_rev_id);
+/*$mw$*/
+CREATE TRIGGER &mw_prefix.archive_seq_trg BEFORE INSERT ON &mw_prefix.archive
+       FOR EACH ROW WHEN (new.ar_id IS NULL)
+BEGIN
+       &mw_prefix.lastval_pkg.setLastval(archive_ar_id_seq.nextval, :new.ar_id);
+END;
+/*$mw$*/
 
 CREATE TABLE &mw_prefix.pagelinks (
   pl_from       NUMBER   NOT NULL,
@@ -215,6 +272,13 @@ CREATE TABLE &mw_prefix.category (
 ALTER TABLE &mw_prefix.category ADD CONSTRAINT &mw_prefix.category_pk PRIMARY KEY (cat_id);
 CREATE UNIQUE INDEX &mw_prefix.category_u01 ON &mw_prefix.category (cat_title);
 CREATE INDEX &mw_prefix.category_i01 ON &mw_prefix.category (cat_pages);
+/*$mw$*/
+CREATE TRIGGER &mw_prefix.category_seq_trg BEFORE INSERT ON &mw_prefix.category
+       FOR EACH ROW WHEN (new.cat_id IS NULL)
+BEGIN
+       &mw_prefix.lastval_pkg.setLastval(category_cat_id_seq.nextval, :new.cat_id);
+END;
+/*$mw$*/
 
 CREATE SEQUENCE externallinks_el_id_seq;
 CREATE TABLE &mw_prefix.externallinks (
@@ -222,7 +286,7 @@ CREATE TABLE &mw_prefix.externallinks (
   el_from   NUMBER  NOT NULL,
   el_to     VARCHAR2(2048) NOT NULL,
   el_index  VARCHAR2(2048) NOT NULL,
-  el_index_60  VARBINARY(60) NOT NULL DEFAULT ''
+  el_index_60  VARCHAR2(60)
 );
 ALTER TABLE &mw_prefix.externallinks ADD CONSTRAINT &mw_prefix.externallinks_pk PRIMARY KEY (el_id);
 ALTER TABLE &mw_prefix.externallinks ADD CONSTRAINT &mw_prefix.externallinks_fk1 FOREIGN KEY (el_from) REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
@@ -231,6 +295,13 @@ CREATE INDEX &mw_prefix.externallinks_i02 ON &mw_prefix.externallinks (el_to, el
 CREATE INDEX &mw_prefix.externallinks_i03 ON &mw_prefix.externallinks (el_index);
 CREATE INDEX &mw_prefix.externallinks_i04 ON &mw_prefix.externallinks (el_index_60, el_id);
 CREATE INDEX &mw_prefix.externallinks_i05 ON &mw_prefix.externallinks (el_from, el_index_60, el_id);
+/*$mw$*/
+CREATE TRIGGER &mw_prefix.externallinks_seq_trg BEFORE INSERT ON &mw_prefix.externallinks
+       FOR EACH ROW WHEN (new.el_id IS NULL)
+BEGIN
+       &mw_prefix.lastval_pkg.setLastval(externallinks_el_id_seq.nextval, :new.el_id);
+END;
+/*$mw$*/
 
 CREATE TABLE &mw_prefix.langlinks (
   ll_from    NUMBER  NOT NULL,
@@ -250,7 +321,7 @@ CREATE UNIQUE INDEX &mw_prefix.iwlinks_ui01 ON &mw_prefix.iwlinks (iwl_from, iwl
 CREATE UNIQUE INDEX &mw_prefix.iwlinks_ui02 ON &mw_prefix.iwlinks (iwl_prefix, iwl_title, iwl_from);
 
 CREATE TABLE &mw_prefix.site_stats (
-  ss_row_id         NUMBER  NOT NULL ,
+  ss_row_id         NUMBER  NOT NULL PRIMARY KEY,
   ss_total_edits    NUMBER            DEFAULT 0,
   ss_good_articles  NUMBER            DEFAULT 0,
   ss_total_pages    NUMBER            DEFAULT -1,
@@ -258,7 +329,6 @@ CREATE TABLE &mw_prefix.site_stats (
   ss_active_users   NUMBER            DEFAULT -1,
   ss_images         NUMBER            DEFAULT 0
 );
-CREATE UNIQUE INDEX &mw_prefix.site_stats_u01 ON &mw_prefix.site_stats (ss_row_id);
 
 CREATE SEQUENCE ipblocks_ipb_id_seq;
 CREATE TABLE &mw_prefix.ipblocks (
@@ -290,6 +360,13 @@ CREATE INDEX &mw_prefix.ipblocks_i02 ON &mw_prefix.ipblocks (ipb_range_start, ip
 CREATE INDEX &mw_prefix.ipblocks_i03 ON &mw_prefix.ipblocks (ipb_timestamp);
 CREATE INDEX &mw_prefix.ipblocks_i04 ON &mw_prefix.ipblocks (ipb_expiry);
 CREATE INDEX &mw_prefix.ipblocks_i05 ON &mw_prefix.ipblocks (ipb_parent_block_id);
+/*$mw$*/
+CREATE TRIGGER &mw_prefix.ipblocks_seq_trg BEFORE INSERT ON &mw_prefix.ipblocks
+       FOR EACH ROW WHEN (new.ipb_id IS NULL)
+BEGIN
+       &mw_prefix.lastval_pkg.setLastval(ipblocks_ipb_id_seq.nextval, :new.ipb_id);
+END;
+/*$mw$*/
 
 CREATE TABLE &mw_prefix.image (
   img_name         VARCHAR2(255)      NOT NULL,
@@ -374,6 +451,13 @@ CREATE INDEX &mw_prefix.filearchive_i02 ON &mw_prefix.filearchive (fa_storage_gr
 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.filearchive_i05 ON &mw_prefix.filearchive (fa_sha1);
+/*$mw$*/
+CREATE TRIGGER &mw_prefix.filearchive_seq_trg BEFORE INSERT ON &mw_prefix.filearchive
+       FOR EACH ROW WHEN (new.fa_id IS NULL)
+BEGIN
+       &mw_prefix.lastval_pkg.setLastval(filearchive_fa_id_seq.nextval, :new.fa_id);
+END;
+/*$mw$*/
 
 CREATE SEQUENCE uploadstash_us_id_seq;
 CREATE TABLE &mw_prefix.uploadstash (
@@ -400,6 +484,13 @@ ALTER TABLE &mw_prefix.uploadstash ADD CONSTRAINT &mw_prefix.uploadstash_fk1 FOR
 CREATE INDEX &mw_prefix.uploadstash_i01 ON &mw_prefix.uploadstash (us_user);
 CREATE INDEX &mw_prefix.uploadstash_i02 ON &mw_prefix.uploadstash (us_timestamp);
 CREATE UNIQUE INDEX &mw_prefix.uploadstash_u01 ON &mw_prefix.uploadstash (us_key);
+/*$mw$*/
+CREATE TRIGGER &mw_prefix.uploadstash_seq_trg BEFORE INSERT ON &mw_prefix.uploadstash
+       FOR EACH ROW WHEN (new.us_id IS NULL)
+BEGIN
+       &mw_prefix.lastval_pkg.setLastval(uploadstash_us_id_seq.nextval, :new.us_id);
+END;
+/*$mw$*/
 
 CREATE SEQUENCE recentchanges_rc_id_seq;
 CREATE TABLE &mw_prefix.recentchanges (
@@ -440,6 +531,13 @@ 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.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
+       FOR EACH ROW WHEN (new.rc_id IS NULL)
+BEGIN
+       &mw_prefix.lastval_pkg.setLastval(recentchanges_rc_id_seq.nextval, :new.rc_id);
+END;
+/*$mw$*/
 
 CREATE TABLE &mw_prefix.watchlist (
   wl_id                     NUMBER     NOT NULL,
@@ -518,6 +616,13 @@ 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);
+/*$mw$*/
+CREATE TRIGGER &mw_prefix.logging_seq_trg BEFORE INSERT ON &mw_prefix.logging
+       FOR EACH ROW WHEN (new.log_id IS NULL)
+BEGIN
+       &mw_prefix.lastval_pkg.setLastval(logging_log_id_seq.nextval, :new.log_id);
+END;
+/*$mw$*/
 
 CREATE TABLE &mw_prefix.log_search (
   ls_field VARCHAR2(32) NOT NULL,
@@ -548,6 +653,13 @@ CREATE INDEX &mw_prefix.job_i02 ON &mw_prefix.job (job_timestamp);
 CREATE INDEX &mw_prefix.job_i03 ON &mw_prefix.job (job_sha1);
 CREATE INDEX &mw_prefix.job_i04 ON &mw_prefix.job (job_cmd,job_token,job_random);
 CREATE INDEX &mw_prefix.job_i05 ON &mw_prefix.job (job_attempts);
+/*$mw$*/
+CREATE TRIGGER &mw_prefix.job_seq_trg BEFORE INSERT ON &mw_prefix.job
+       FOR EACH ROW WHEN (new.job_id IS NULL)
+BEGIN
+       &mw_prefix.lastval_pkg.setLastval(job_job_id_seq.nextval, :new.job_id);
+END;
+/*$mw$*/
 
 CREATE TABLE &mw_prefix.querycache_info (
   qci_type       VARCHAR2(32) NOT NULL,
@@ -593,6 +705,13 @@ CREATE UNIQUE INDEX &mw_prefix.page_restrictions_u01 ON &mw_prefix.page_restrict
 CREATE INDEX &mw_prefix.page_restrictions_i01 ON &mw_prefix.page_restrictions (pr_type,pr_level);
 CREATE INDEX &mw_prefix.page_restrictions_i02 ON &mw_prefix.page_restrictions (pr_level);
 CREATE INDEX &mw_prefix.page_restrictions_i03 ON &mw_prefix.page_restrictions (pr_cascade);
+/*$mw$*/
+CREATE TRIGGER &mw_prefix.page_restrictions_seq_trg BEFORE INSERT ON &mw_prefix.page_restrictions
+       FOR EACH ROW WHEN (new.pr_id IS NULL)
+BEGIN
+       &mw_prefix.lastval_pkg.setLastval(page_restrictions_pr_id_seq.nextval, :new.pr_id);
+END;
+/*$mw$*/
 
 CREATE TABLE &mw_prefix.protected_titles (
   pt_namespace   NUMBER           DEFAULT 0 NOT NULL,
@@ -701,6 +820,13 @@ CREATE INDEX &mw_prefix.sites_i04 ON &mw_prefix.sites (site_language);
 CREATE INDEX &mw_prefix.sites_i05 ON &mw_prefix.sites (site_protocol);
 CREATE INDEX &mw_prefix.sites_i06 ON &mw_prefix.sites (site_domain);
 CREATE INDEX &mw_prefix.sites_i07 ON &mw_prefix.sites (site_forward);
+/*$mw$*/
+CREATE TRIGGER &mw_prefix.sites_seq_trg BEFORE INSERT ON &mw_prefix.sites
+       FOR EACH ROW WHEN (new.site_id IS NULL)
+BEGIN
+       &mw_prefix.lastval_pkg.setLastval(sites_site_id_seq.nextval, :new.site_id);
+END;
+/*$mw$*/
 
 CREATE TABLE &mw_prefix.site_identifiers (
   si_site NUMBER NOT NULL,