[Summary view] [Print] [Text view]

   1  -- This file is part of Moodle - http://moodle.org/
   2  --
   3  -- Moodle is free software: you can redistribute it and/or modify
   4  -- it under the terms of the GNU General Public License as published by
   5  -- the Free Software Foundation, either version 3 of the License, or
   6  -- (at your option) any later version.
   7  --
   8  -- Moodle is distributed in the hope that it will be useful,
   9  -- but WITHOUT ANY WARRANTY; without even the implied warranty of
  10  -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  11  -- GNU General Public License for more details.
  12  --
  13  -- You should have received a copy of the GNU General Public License
  14  -- along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  15  
  16  /**
  17   * @package    core_dml
  18   * @copyright  2009 onwards Eloy Lafuente (stronk7) {@link http://stronk7.com}
  19   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  20   * @version    20091010 (plz, keep this updated for easier reference)
  21   */
  22  
  23  /**
  24   * This sql script generates various PL/SQL packages needed to provide
  25   * cross-db compatibility in the Moodle 2.x DB API with some operations
  26   * not natively supported by Oracle, namely:
  27   *  - locking: Application locks used by Moodle DB sessions. It uses
  28   *             the DBMS_LOCK package so execution must be granted
  29   *             to the Moodle DB user by SYS to work properly.
  30   *  - bit ops: To provide cross-db bitwise operations to be used by the
  31   *             sql_bitXXX() helper functions
  32   *  - one space hacks: One space empty string substitute hacks.
  33   */
  34  
  35  CREATE OR REPLACE PACKAGE MOODLELIB AS
  36  
  37  FUNCTION BITOR (value1 IN INTEGER, value2 IN INTEGER) RETURN INTEGER;
  38  FUNCTION BITXOR(value1 IN INTEGER, value2 IN INTEGER) RETURN INTEGER;
  39  
  40  FUNCTION GET_HANDLE  (lock_name IN VARCHAR2) RETURN VARCHAR2;
  41  FUNCTION GET_LOCK    (lock_name IN VARCHAR2, lock_timeout IN INTEGER) RETURN INTEGER;
  42  FUNCTION RELEASE_LOCK(lock_name IN VARCHAR2) RETURN INTEGER;
  43  
  44  FUNCTION UNDO_DIRTY_HACK(hackedstring IN VARCHAR2) RETURN VARCHAR2;
  45  FUNCTION UNDO_MEGA_HACK(hackedstring IN VARCHAR2) RETURN VARCHAR2;
  46  FUNCTION TRICONCAT(string1 IN VARCHAR2, string2 IN VARCHAR2, string3 IN VARCHAR2) RETURN VARCHAR2;
  47  
  48  END MOODLELIB;
  49  /
  50  
  51  CREATE OR REPLACE PACKAGE BODY MOODLELIB AS
  52  
  53  FUNCTION BITOR(value1 IN INTEGER, value2 IN INTEGER) RETURN INTEGER IS
  54  
  55  BEGIN
  56      RETURN value1 + value2 - BITAND(value1,value2);
  57  END BITOR;
  58  
  59  FUNCTION BITXOR(value1 IN INTEGER, value2 IN INTEGER) RETURN INTEGER IS
  60  
  61  BEGIN
  62      RETURN MOODLELIB.BITOR(value1,value2) - BITAND(value1,value2);
  63  END BITXOR;
  64  
  65  FUNCTION GET_HANDLE(lock_name IN VARCHAR2) RETURN VARCHAR2 IS
  66      PRAGMA AUTONOMOUS_TRANSACTION;
  67      lock_handle VARCHAR2(128);
  68  
  69  BEGIN
  70      DBMS_LOCK.ALLOCATE_UNIQUE (
  71          lockname => lock_name,
  72          lockhandle => lock_handle,
  73          expiration_secs => 864000);
  74      RETURN lock_handle;
  75  END GET_HANDLE;
  76  
  77  FUNCTION GET_LOCK(lock_name IN VARCHAR2, lock_timeout IN INTEGER) RETURN INTEGER IS
  78      lock_status NUMBER;
  79  BEGIN
  80      lock_status := DBMS_LOCK.REQUEST(
  81                         lockhandle => GET_HANDLE(lock_name),
  82                         lockmode => DBMS_LOCK.X_MODE, -- eXclusive
  83                         timeout => lock_timeout,
  84                         release_on_commit => FALSE);
  85      CASE lock_status
  86          WHEN 0 THEN NULL;
  87          WHEN 2 THEN RAISE_APPLICATION_ERROR(-20000,'deadlock detected');
  88          WHEN 4 THEN RAISE_APPLICATION_ERROR(-20000,'lock already obtained');
  89          ELSE RAISE_APPLICATION_ERROR(-20000,'request lock failed - ' || lock_status);
  90      END CASE;
  91      RETURN 1;
  92  END GET_LOCK;
  93  
  94  FUNCTION RELEASE_LOCK(lock_name IN VARCHAR2) RETURN INTEGER IS
  95      lock_status NUMBER;
  96  BEGIN
  97      lock_status := DBMS_LOCK.RELEASE(
  98                        lockhandle => GET_HANDLE(lock_name));
  99      IF lock_status > 0 THEN
 100          RAISE_APPLICATION_ERROR(-20000,'release lock failed - ' || lock_status);
 101      END IF;
 102      RETURN 1;
 103  END RELEASE_LOCK;
 104  
 105  FUNCTION UNDO_DIRTY_HACK(hackedstring IN VARCHAR2) RETURN VARCHAR2 IS
 106  
 107  BEGIN
 108      IF hackedstring = ' ' THEN
 109          RETURN '';
 110      END IF;
 111      RETURN hackedstring;
 112  END UNDO_DIRTY_HACK;
 113  
 114  FUNCTION UNDO_MEGA_HACK(hackedstring IN VARCHAR2) RETURN VARCHAR2 IS
 115  
 116  BEGIN
 117      IF hackedstring IS NULL THEN
 118          RETURN hackedstring;
 119      END IF;
 120      RETURN REPLACE(hackedstring, '*OCISP*', ' ');
 121  END UNDO_MEGA_HACK;
 122  
 123  FUNCTION TRICONCAT(string1 IN VARCHAR2, string2 IN VARCHAR2, string3 IN VARCHAR2) RETURN VARCHAR2 IS
 124      stringresult VARCHAR2(1333);
 125  BEGIN
 126      IF string1 IS NULL THEN
 127          RETURN NULL;
 128      END IF;
 129      IF string2 IS NULL THEN
 130          RETURN NULL;
 131      END IF;
 132      IF string3 IS NULL THEN
 133          RETURN NULL;
 134      END IF;
 135  
 136      stringresult := CONCAT(CONCAT(MOODLELIB.UNDO_DIRTY_HACK(string1), MOODLELIB.UNDO_DIRTY_HACK(string2)), MOODLELIB.UNDO_DIRTY_HACK(string3));
 137  
 138      IF stringresult IS NULL THEN
 139          RETURN ' ';
 140      END IF;
 141  
 142      RETURN stringresult;
 143  END;
 144  
 145  END MOODLELIB;
 146  /
 147  
 148  SHOW ERRORS
 149  /