maintenance/oracle/archives/patch-auto_increment_triggers.sql

   1 define mw_prefix='{$wgDBprefix}';
   2
   3 -- Package to help with making Oracle more like other DBs with respect to
   4 -- auto-incrementing columns.
   5 /*$mw$*/
   6 CREATE PACKAGE &mw_prefix.lastval_pkg IS
   7   lastval NUMBER;
   8   PROCEDURE setLastval(val IN NUMBER, field OUT NUMBER);
   9   FUNCTION getLastval RETURN NUMBER;
  10 END;
  11 /*$mw$*/
  12
  13 /*$mw$*/
  14 CREATE PACKAGE BODY &mw_prefix.lastval_pkg IS
  15   PROCEDURE setLastval(val IN NUMBER, field OUT NUMBER) IS BEGIN
  16     lastval := val;
  17     field := val;
  18   END;
  19
  20   FUNCTION getLastval RETURN NUMBER IS BEGIN
  21     RETURN lastval;
  22   END;
  23 END;
  24 /*$mw$*/
  25
  26 /*$mw$*/
  27 CREATE TRIGGER &mw_prefix.mwuser_default_user_id BEFORE INSERT ON &mw_prefix.mwuser
  28         FOR EACH ROW WHEN (new.user_id IS NULL)
  29 BEGIN
  30         &mw_prefix.lastval_pkg.setLastval(user_user_id_seq.nextval, :new.user_id);
  31 END;
  32 /*$mw$*/
  33
  34 /*$mw$*/
  35 CREATE TRIGGER &mw_prefix.page_default_page_id BEFORE INSERT ON &mw_prefix.page
  36         FOR EACH ROW WHEN (new.page_id IS NULL)
  37 BEGIN
  38         &mw_prefix.lastval_pkg.setLastval(page_page_id_seq.nextval, :new.page_id);
  39 END;
  40 /*$mw$*/
  41
  42 /*$mw$*/
  43 CREATE TRIGGER &mw_prefix.revision_default_rev_id BEFORE INSERT ON &mw_prefix.revision
  44         FOR EACH ROW WHEN (new.rev_id IS NULL)
  45 BEGIN
  46         &mw_prefix.lastval_pkg.setLastval(revision_rev_id_seq.nextval, :new.rev_id);
  47 END;
  48 /*$mw$*/
  49
  50 /*$mw$*/
  51 CREATE TRIGGER &mw_prefix.text_default_old_id BEFORE INSERT ON &mw_prefix.text
  52         FOR EACH ROW WHEN (new.old_id IS NULL)
  53 BEGIN
  54         &mw_prefix.lastval_pkg.setLastval(text_old_id_seq.nextval, :new.old_id);
  55 END;
  56 /*$mw$*/
  57
  58 /*$mw$*/
  59 CREATE TRIGGER &mw_prefix.archive_default_ar_id BEFORE INSERT ON &mw_prefix.archive
  60         FOR EACH ROW WHEN (new.ar_id IS NULL)
  61 BEGIN
  62         &mw_prefix.lastval_pkg.setLastval(archive_ar_id_seq.nextval, :new.ar_id);
  63 END;
  64 /*$mw$*/
  65
  66 /*$mw$*/
  67 CREATE TRIGGER &mw_prefix.category_default_cat_id BEFORE INSERT ON &mw_prefix.category
  68         FOR EACH ROW WHEN (new.cat_id IS NULL)
  69 BEGIN
  70         &mw_prefix.lastval_pkg.setLastval(category_cat_id_seq.nextval, :new.cat_id);
  71 END;
  72 /*$mw$*/
  73
  74 /*$mw$*/
  75 CREATE TRIGGER &mw_prefix.externallinks_default_el_id BEFORE INSERT ON &mw_prefix.externallinks
  76         FOR EACH ROW WHEN (new.el_id IS NULL)
  77 BEGIN
  78         &mw_prefix.lastval_pkg.setLastval(externallinks_el_id_seq.nextval, :new.el_id);
  79 END;
  80 /*$mw$*/
  81
  82 /*$mw$*/
  83 CREATE TRIGGER &mw_prefix.ipblocks_default_ipb_id BEFORE INSERT ON &mw_prefix.ipblocks
  84         FOR EACH ROW WHEN (new.ipb_id IS NULL)
  85 BEGIN
  86         &mw_prefix.lastval_pkg.setLastval(ipblocks_ipb_id_seq.nextval, :new.ipb_id);
  87 END;
  88 /*$mw$*/
  89
  90 /*$mw$*/
  91 CREATE TRIGGER &mw_prefix.filearchive_default_fa_id BEFORE INSERT ON &mw_prefix.filearchive
  92         FOR EACH ROW WHEN (new.fa_id IS NULL)
  93 BEGIN
  94         &mw_prefix.lastval_pkg.setLastval(filearchive_fa_id_seq.nextval, :new.fa_id);
  95 END;
  96 /*$mw$*/
  97
  98 /*$mw$*/
  99 CREATE TRIGGER &mw_prefix.uploadstash_default_us_id BEFORE INSERT ON &mw_prefix.uploadstash
 100         FOR EACH ROW WHEN (new.us_id IS NULL)
 101 BEGIN
 102         &mw_prefix.lastval_pkg.setLastval(uploadstash_us_id_seq.nextval, :new.us_id);
 103 END;
 104 /*$mw$*/
 105
 106 /*$mw$*/
 107 CREATE TRIGGER &mw_prefix.recentchanges_default_rc_id BEFORE INSERT ON &mw_prefix.recentchanges
 108         FOR EACH ROW WHEN (new.rc_id IS NULL)
 109 BEGIN
 110         &mw_prefix.lastval_pkg.setLastval(recentchanges_rc_id_seq.nextval, :new.rc_id);
 111 END;
 112 /*$mw$*/
 113
 114 /*$mw$*/
 115 CREATE TRIGGER &mw_prefix.logging_default_log_id BEFORE INSERT ON &mw_prefix.logging
 116         FOR EACH ROW WHEN (new.log_id IS NULL)
 117 BEGIN
 118         &mw_prefix.lastval_pkg.setLastval(logging_log_id_seq.nextval, :new.log_id);
 119 END;
 120 /*$mw$*/
 121
 122 /*$mw$*/
 123 CREATE TRIGGER &mw_prefix.job_default_job_id BEFORE INSERT ON &mw_prefix.job
 124         FOR EACH ROW WHEN (new.job_id IS NULL)
 125 BEGIN
 126         &mw_prefix.lastval_pkg.setLastval(job_job_id_seq.nextval, :new.job_id);
 127 END;
 128 /*$mw$*/
 129
 130 /*$mw$*/
 131 CREATE TRIGGER &mw_prefix.page_restrictions_default_pr_id BEFORE INSERT ON &mw_prefix.page_restrictions
 132         FOR EACH ROW WHEN (new.pr_id IS NULL)
 133 BEGIN
 134         &mw_prefix.lastval_pkg.setLastval(page_restrictions_pr_id_seq.nextval, :new.pr_id);
 135 END;
 136 /*$mw$*/
 137
 138 /*$mw$*/
 139 CREATE TRIGGER &mw_prefix.sites_default_site_id BEFORE INSERT ON &mw_prefix.sites
 140         FOR EACH ROW WHEN (new.site_id IS NULL)
 141 BEGIN
 142         &mw_prefix.lastval_pkg.setLastval(sites_site_id_seq.nextval, :new.site_id);
 143 END;
 144 /*$mw$*/