Subversion Repositories oidplus

Rev

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