1 |
efrain |
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 |
* Moodle will not parse this file correctly if it uses Windows line endings.
|
|
|
35 |
*/
|
|
|
36 |
|
|
|
37 |
CREATE OR REPLACE PACKAGE MOODLELIB AS
|
|
|
38 |
|
|
|
39 |
FUNCTION BITOR (value1 IN INTEGER, value2 IN INTEGER) RETURN INTEGER;
|
|
|
40 |
FUNCTION BITXOR(value1 IN INTEGER, value2 IN INTEGER) RETURN INTEGER;
|
|
|
41 |
|
|
|
42 |
FUNCTION GET_HANDLE (lock_name IN VARCHAR2) RETURN VARCHAR2;
|
|
|
43 |
FUNCTION GET_LOCK (lock_name IN VARCHAR2, lock_timeout IN INTEGER) RETURN INTEGER;
|
|
|
44 |
FUNCTION RELEASE_LOCK(lock_name IN VARCHAR2) RETURN INTEGER;
|
|
|
45 |
|
|
|
46 |
FUNCTION UNDO_DIRTY_HACK(hackedstring IN VARCHAR2) RETURN VARCHAR2;
|
|
|
47 |
FUNCTION UNDO_MEGA_HACK(hackedstring IN VARCHAR2) RETURN VARCHAR2;
|
|
|
48 |
FUNCTION TRICONCAT(string1 IN VARCHAR2, string2 IN VARCHAR2, string3 IN VARCHAR2) RETURN VARCHAR2;
|
|
|
49 |
|
|
|
50 |
END MOODLELIB;
|
|
|
51 |
/
|
|
|
52 |
|
|
|
53 |
CREATE OR REPLACE PACKAGE BODY MOODLELIB AS
|
|
|
54 |
|
|
|
55 |
FUNCTION BITOR(value1 IN INTEGER, value2 IN INTEGER) RETURN INTEGER IS
|
|
|
56 |
|
|
|
57 |
BEGIN
|
|
|
58 |
RETURN value1 + value2 - BITAND(value1,value2);
|
|
|
59 |
END BITOR;
|
|
|
60 |
|
|
|
61 |
FUNCTION BITXOR(value1 IN INTEGER, value2 IN INTEGER) RETURN INTEGER IS
|
|
|
62 |
|
|
|
63 |
BEGIN
|
|
|
64 |
RETURN MOODLELIB.BITOR(value1,value2) - BITAND(value1,value2);
|
|
|
65 |
END BITXOR;
|
|
|
66 |
|
|
|
67 |
FUNCTION GET_HANDLE(lock_name IN VARCHAR2) RETURN VARCHAR2 IS
|
|
|
68 |
PRAGMA AUTONOMOUS_TRANSACTION;
|
|
|
69 |
lock_handle VARCHAR2(128);
|
|
|
70 |
|
|
|
71 |
BEGIN
|
|
|
72 |
DBMS_LOCK.ALLOCATE_UNIQUE (
|
|
|
73 |
lockname => lock_name,
|
|
|
74 |
lockhandle => lock_handle,
|
|
|
75 |
expiration_secs => 864000);
|
|
|
76 |
RETURN lock_handle;
|
|
|
77 |
END GET_HANDLE;
|
|
|
78 |
|
|
|
79 |
FUNCTION GET_LOCK(lock_name IN VARCHAR2, lock_timeout IN INTEGER) RETURN INTEGER IS
|
|
|
80 |
lock_status NUMBER;
|
|
|
81 |
BEGIN
|
|
|
82 |
lock_status := DBMS_LOCK.REQUEST(
|
|
|
83 |
lockhandle => GET_HANDLE(lock_name),
|
|
|
84 |
lockmode => DBMS_LOCK.X_MODE, -- eXclusive
|
|
|
85 |
timeout => lock_timeout,
|
|
|
86 |
release_on_commit => FALSE);
|
|
|
87 |
CASE lock_status
|
|
|
88 |
WHEN 0 THEN NULL;
|
|
|
89 |
WHEN 2 THEN RAISE_APPLICATION_ERROR(-20000,'deadlock detected');
|
|
|
90 |
WHEN 4 THEN RAISE_APPLICATION_ERROR(-20000,'lock already obtained');
|
|
|
91 |
ELSE RAISE_APPLICATION_ERROR(-20000,'request lock failed - ' || lock_status);
|
|
|
92 |
END CASE;
|
|
|
93 |
RETURN 1;
|
|
|
94 |
END GET_LOCK;
|
|
|
95 |
|
|
|
96 |
FUNCTION RELEASE_LOCK(lock_name IN VARCHAR2) RETURN INTEGER IS
|
|
|
97 |
lock_status NUMBER;
|
|
|
98 |
BEGIN
|
|
|
99 |
lock_status := DBMS_LOCK.RELEASE(
|
|
|
100 |
lockhandle => GET_HANDLE(lock_name));
|
|
|
101 |
IF lock_status > 0 THEN
|
|
|
102 |
RAISE_APPLICATION_ERROR(-20000,'release lock failed - ' || lock_status);
|
|
|
103 |
END IF;
|
|
|
104 |
RETURN 1;
|
|
|
105 |
END RELEASE_LOCK;
|
|
|
106 |
|
|
|
107 |
FUNCTION UNDO_DIRTY_HACK(hackedstring IN VARCHAR2) RETURN VARCHAR2 IS
|
|
|
108 |
|
|
|
109 |
BEGIN
|
|
|
110 |
IF hackedstring = ' ' THEN
|
|
|
111 |
RETURN '';
|
|
|
112 |
END IF;
|
|
|
113 |
RETURN hackedstring;
|
|
|
114 |
END UNDO_DIRTY_HACK;
|
|
|
115 |
|
|
|
116 |
FUNCTION UNDO_MEGA_HACK(hackedstring IN VARCHAR2) RETURN VARCHAR2 IS
|
|
|
117 |
|
|
|
118 |
BEGIN
|
|
|
119 |
IF hackedstring IS NULL THEN
|
|
|
120 |
RETURN hackedstring;
|
|
|
121 |
END IF;
|
|
|
122 |
RETURN REPLACE(hackedstring, '*OCISP*', ' ');
|
|
|
123 |
END UNDO_MEGA_HACK;
|
|
|
124 |
|
|
|
125 |
FUNCTION TRICONCAT(string1 IN VARCHAR2, string2 IN VARCHAR2, string3 IN VARCHAR2) RETURN VARCHAR2 IS
|
|
|
126 |
stringresult VARCHAR2(1333);
|
|
|
127 |
BEGIN
|
|
|
128 |
IF string1 IS NULL THEN
|
|
|
129 |
RETURN NULL;
|
|
|
130 |
END IF;
|
|
|
131 |
IF string2 IS NULL THEN
|
|
|
132 |
RETURN NULL;
|
|
|
133 |
END IF;
|
|
|
134 |
IF string3 IS NULL THEN
|
|
|
135 |
RETURN NULL;
|
|
|
136 |
END IF;
|
|
|
137 |
|
|
|
138 |
stringresult := CONCAT(CONCAT(MOODLELIB.UNDO_DIRTY_HACK(string1), MOODLELIB.UNDO_DIRTY_HACK(string2)), MOODLELIB.UNDO_DIRTY_HACK(string3));
|
|
|
139 |
|
|
|
140 |
IF stringresult IS NULL THEN
|
|
|
141 |
RETURN ' ';
|
|
|
142 |
END IF;
|
|
|
143 |
|
|
|
144 |
RETURN stringresult;
|
|
|
145 |
END;
|
|
|
146 |
|
|
|
147 |
END MOODLELIB;
|
|
|
148 |
/
|
|
|
149 |
|
|
|
150 |
SHOW ERRORS
|
|
|
151 |
/
|