Rev 788 | Rev 1086 | Go to most recent revision | Only display areas with differences | Regard whitespace | Details | Blame | Last modification | View Log | RSS feed
Rev 788 | Rev 1050 | ||
---|---|---|---|
1 | <?php |
1 | <?php |
2 | 2 | ||
3 | /* |
3 | /* |
4 | * OIDplus 2.0 |
4 | * OIDplus 2.0 |
5 | * Copyright 2019 - 2022 Daniel Marschall, ViaThinkSoft |
5 | * Copyright 2019 - 2022 Daniel Marschall, ViaThinkSoft |
6 | * |
6 | * |
7 | * Licensed under the Apache License, Version 2.0 (the "License"); |
7 | * Licensed under the Apache License, Version 2.0 (the "License"); |
8 | * you may not use this file except in compliance with the License. |
8 | * you may not use this file except in compliance with the License. |
9 | * You may obtain a copy of the License at |
9 | * You may obtain a copy of the License at |
10 | * |
10 | * |
11 | * http://www.apache.org/licenses/LICENSE-2.0 |
11 | * http://www.apache.org/licenses/LICENSE-2.0 |
12 | * |
12 | * |
13 | * Unless required by applicable law or agreed to in writing, software |
13 | * Unless required by applicable law or agreed to in writing, software |
14 | * distributed under the License is distributed on an "AS IS" BASIS, |
14 | * distributed under the License is distributed on an "AS IS" BASIS, |
15 | * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
15 | * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
16 | * See the License for the specific language governing permissions and |
16 | * See the License for the specific language governing permissions and |
17 | * limitations under the License. |
17 | * limitations under the License. |
18 | */ |
18 | */ |
19 | 19 | ||
20 | if (!defined('INSIDE_OIDPLUS')) die(); |
20 | namespace ViaThinkSoft\OIDplus; |
21 | 21 | ||
22 | class OIDplusSqlSlangPluginOracle extends OIDplusSqlSlangPlugin { |
22 | class OIDplusSqlSlangPluginOracle extends OIDplusSqlSlangPlugin { |
23 | 23 | ||
24 | public static function id(): string { |
24 | public static function id(): string { |
25 | return 'oracle'; |
25 | return 'oracle'; |
26 | } |
26 | } |
27 | 27 | ||
28 | public function natOrder($fieldname, $order='asc'): string { |
28 | public function natOrder($fieldname, $order='asc'): string { |
29 | 29 | ||
30 | $order = strtolower($order); |
30 | $order = strtolower($order); |
31 | if (($order != 'asc') && ($order != 'desc')) { |
31 | if (($order != 'asc') && ($order != 'desc')) { |
32 | throw new OIDplusException(_L('Invalid order "%1" (needs to be "asc" or "desc")',$order)); |
32 | throw new OIDplusException(_L('Invalid order "%1" (needs to be "asc" or "desc")',$order)); |
33 | } |
33 | } |
34 | 34 | ||
35 | $out = array(); |
35 | $out = array(); |
36 | 36 | ||
37 | $max_arc_len = OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_ARC_SIZE') > 65 ? 65 : OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_ARC_SIZE'); // Limit of "decimal()" type |
37 | $max_arc_len = OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_ARC_SIZE') > 65 ? 65 : OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_ARC_SIZE'); // Limit of "decimal()" type |
38 | 38 | ||
39 | // 1. sort by namespace (oid, guid, ...) |
39 | // 1. sort by namespace (oid, guid, ...) |
40 | $out[] = "regexp_substr($fieldname, '(.*?)(:|\$)', 1, 1, NULL, 1) $order"; |
40 | $out[] = "regexp_substr($fieldname, '(.*?)(:|\$)', 1, 1, NULL, 1) $order"; |
41 | 41 | ||
42 | // 2. sort by first arc (0,1,2) |
42 | // 2. sort by first arc (0,1,2) |
43 | $tmp = "regexp_substr($fieldname, '(.*?)(:|\$)', 1, 2, NULL, 1)"; |
43 | $tmp = "regexp_substr($fieldname, '(.*?)(:|\$)', 1, 2, NULL, 1)"; |
44 | $i = 1; |
44 | $i = 1; |
45 | $out[] = "lpad(regexp_substr($tmp, '(.*?)(\\.|\$)', 1, $i, NULL, 1),$max_arc_len,'0') $order"; |
45 | $out[] = "lpad(regexp_substr($tmp, '(.*?)(\\.|\$)', 1, $i, NULL, 1),$max_arc_len,'0') $order"; |
46 | 46 | ||
47 | for ($i=2; $i<=OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_DEPTH'); $i++) { |
47 | for ($i=2; $i<=OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_DEPTH'); $i++) { |
48 | // 3. Sort by the rest arcs one by one, not that MySQL can only handle decimal(65), not decimal($max_arc_len) |
48 | // 3. Sort by the rest arcs one by one, not that MySQL can only handle decimal(65), not decimal($max_arc_len) |
49 | $out[] = "lpad(regexp_substr($fieldname, '(.*?)(\\.|\$)', 1, $i, NULL, 1),$max_arc_len,'0') $order"; |
49 | $out[] = "lpad(regexp_substr($fieldname, '(.*?)(\\.|\$)', 1, $i, NULL, 1),$max_arc_len,'0') $order"; |
50 | } |
50 | } |
51 | 51 | ||
52 | // 4. as last resort, sort by the identifier itself, e.g. if the casts above did fail (happens if it is not an OID) |
52 | // 4. as last resort, sort by the identifier itself, e.g. if the casts above did fail (happens if it is not an OID) |
53 | $out[] = "$fieldname $order"; |
53 | $out[] = "$fieldname $order"; |
54 | 54 | ||
55 | return implode(', ', $out); |
55 | return implode(', ', $out); |
56 | 56 | ||
57 | } |
57 | } |
58 | 58 | ||
59 | public function sqlDate(): string { |
59 | public function sqlDate(): string { |
60 | return 'SYSDATE'; |
60 | return 'SYSDATE'; |
61 | } |
61 | } |
62 | 62 | ||
63 | public function detect(OIDplusDatabaseConnection $db): bool { |
63 | public function detect(OIDplusDatabaseConnection $db): bool { |
64 | try { |
64 | try { |
65 | $vers = $db->query("SELECT banner FROM v\$version WHERE banner LIKE 'Oracle%'")->fetch_object()->banner; |
65 | $vers = $db->query("SELECT banner FROM v\$version WHERE banner LIKE 'Oracle%'")->fetch_object()->banner; |
66 | $vers = strtolower($vers); |
66 | $vers = strtolower($vers); |
67 | return (strpos($vers, 'oracle') !== false); |
67 | return (strpos($vers, 'oracle') !== false); |
68 | } catch (Exception $e) { |
68 | } catch (\Exception $e) { |
69 | return false; |
69 | return false; |
70 | } |
70 | } |
71 | } |
71 | } |
72 | 72 | ||
73 | private $last_insert_table = null; |
73 | private $last_insert_table = null; |
74 | 74 | ||
75 | public function insert_id(OIDplusDatabaseConnection $db): int { |
75 | public function insert_id(OIDplusDatabaseConnection $db): int { |
76 | if (!$this->last_insert_table) return 0; |
76 | if (!$this->last_insert_table) return 0; |
77 | $res = $db->query("select sequence_name from user_tab_identity_cols where table_name = '".strtoupper($this->last_insert_table)."'"); |
77 | $res = $db->query("select sequence_name from user_tab_identity_cols where table_name = '".strtoupper($this->last_insert_table)."'"); |
78 | $row = $res->fetch_array(); |
78 | $row = $res->fetch_array(); |
79 | 79 | ||
80 | if (!isset($row['sequence_name'])) return 0; |
80 | if (!isset($row['sequence_name'])) return 0; |
81 | $res = $db->query("select ".$row['sequence_name'].".currval from dual"); |
81 | $res = $db->query("select ".$row['sequence_name'].".currval from dual"); |
82 | $row = $res->fetch_array(); |
82 | $row = $res->fetch_array(); |
83 | return (int)$row['CURRVAL']; |
83 | return (int)$row['CURRVAL']; |
84 | } |
84 | } |
85 | 85 | ||
86 | 86 | ||
87 | public function setupSetTablePrefix($cont, $table, $prefix): string { |
87 | public function setupSetTablePrefix($cont, $table, $prefix): string { |
88 | $table = strtoupper($table); |
88 | $table = strtoupper($table); |
89 | $prefix = strtoupper($prefix); |
89 | $prefix = strtoupper($prefix); |
90 | $cont = str_replace('"'.$table.'"', '"'.$prefix.$table.'"', $cont); |
90 | $cont = str_replace('"'.$table.'"', '"'.$prefix.$table.'"', $cont); |
91 | return $cont; |
91 | return $cont; |
92 | } |
92 | } |
93 | 93 | ||
94 | public function setupCreateDbIfNotExists($database): string { |
94 | public function setupCreateDbIfNotExists($database): string { |
95 | // TODO! Implement |
95 | // TODO! Implement |
96 | return ""; |
96 | return ""; |
97 | } |
97 | } |
98 | 98 | ||
99 | public function setupUseDatabase($database): string { |
99 | public function setupUseDatabase($database): string { |
100 | // TODO! Implement |
100 | // TODO! Implement |
101 | return ""; |
101 | return ""; |
102 | } |
102 | } |
103 | 103 | ||
104 | public function isNullFunction($expr1, $expr2): string { |
104 | public function isNullFunction($expr1, $expr2): string { |
105 | // Test via "SELECT NVL(null, 'foo') FROM DUAL;" |
105 | // Test via "SELECT NVL(null, 'foo') FROM DUAL;" |
106 | return "NVL($expr1, $expr2)"; |
106 | return "NVL($expr1, $expr2)"; |
107 | } |
107 | } |
108 | 108 | ||
109 | public function filterQuery($sql): string { |
109 | public function filterQuery($sql): string { |
110 | 110 | ||
111 | // "select 1" is not valid. You need to add "from dual" |
111 | // "select 1" is not valid. You need to add "from dual" |
112 | if ((stripos($sql,'select') !== false) && (stripos($sql,'from') === false)) { |
112 | if ((stripos($sql,'select') !== false) && (stripos($sql,'from') === false)) { |
113 | $sql .= ' from dual'; |
113 | $sql .= ' from dual'; |
114 | } |
114 | } |
115 | 115 | ||
116 | // SQL-Queries MUST NOT end with a ";", otherwise error "SQL command not property ended" |
116 | // SQL-Queries MUST NOT end with a ";", otherwise error "SQL command not property ended" |
117 | $sql = rtrim(trim($sql), "; \n\r\t\v\x00"); |
117 | $sql = rtrim(trim($sql), "; \n\r\t\v\x00"); |
118 | // SQL/PL-Programs MUST end with a ";" |
118 | // SQL/PL-Programs MUST end with a ";" |
119 | if (strtolower(substr($sql,-3)) == 'end') $sql .= ';'; |
119 | if (strtolower(substr($sql,-3)) == 'end') $sql .= ';'; |
120 | 120 | ||
121 | // Dirty hack!!! We need the name of the last inserted table so that insert_id() |
121 | // Dirty hack!!! We need the name of the last inserted table so that insert_id() |
122 | // works. This is a dirty hack, because the invokation of filterQuery() does |
122 | // works. This is a dirty hack, because the invokation of filterQuery() does |
123 | // not guarantee that the query was actually executed... |
123 | // not guarantee that the query was actually executed... |
124 | if (preg_match("@insert into (.+) @ismU", $sql, $m)) { |
124 | if (preg_match("@insert into (.+) @ismU", $sql, $m)) { |
125 | $this->last_insert_table = $m[1]; |
125 | $this->last_insert_table = $m[1]; |
126 | } else { |
126 | } else { |
127 | $this->last_insert_table = null; |
127 | $this->last_insert_table = null; |
128 | } |
128 | } |
129 | 129 | ||
130 | // Comment is a keyword and cannot be used as column name |
130 | // Comment is a keyword and cannot be used as column name |
131 | $sql = str_ireplace('comment', '"COMMENT"', $sql); |
131 | $sql = str_ireplace('comment', '"COMMENT"', $sql); |
132 | 132 | ||
133 | return $sql; |
133 | return $sql; |
134 | } |
134 | } |
135 | 135 | ||
136 | public function getSQLBool($bool): string { |
136 | public function getSQLBool($bool): string { |
137 | return $bool ? '1' : '0'; |
137 | return $bool ? '1' : '0'; |
138 | } |
138 | } |
139 | 139 | ||
140 | public function escapeString($str): string { |
140 | public function escapeString($str): string { |
141 | return str_replace("'", "''", $str); |
141 | return str_replace("'", "''", $str); |
142 | } |
142 | } |
143 | } |
143 | } |
144 | 144 |