Rev 245 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed
Rev | Author | Line No. | Line |
---|---|---|---|
150 | daniel-mar | 1 | <?php |
2 | |||
3 | /* |
||
4 | * OIDplus 2.0 |
||
5 | * Copyright 2019 Daniel Marschall, ViaThinkSoft |
||
6 | * |
||
7 | * Licensed under the Apache License, Version 2.0 (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 |
||
10 | * |
||
11 | * http://www.apache.org/licenses/LICENSE-2.0 |
||
12 | * |
||
13 | * Unless required by applicable law or agreed to in writing, software |
||
14 | * distributed under the License is distributed on an "AS IS" BASIS, |
||
15 | * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
||
16 | * See the License for the specific language governing permissions and |
||
17 | * limitations under the License. |
||
18 | */ |
||
19 | |||
20 | if (!defined('IN_OIDPLUS')) die(); |
||
21 | |||
22 | define('OIDPLUS_MYSQL_QUERYLOG', false); |
||
23 | |||
246 | daniel-mar | 24 | class OIDplusDatabasePluginMySQLi extends OIDplusDatabasePlugin { |
150 | daniel-mar | 25 | private $mysqli; |
26 | private $last_query; |
||
27 | private $prepare_cache = array(); |
||
28 | |||
236 | daniel-mar | 29 | public static function getPluginInformation(): array { |
222 | daniel-mar | 30 | $out = array(); |
31 | $out['name'] = 'MySQLi'; |
||
32 | $out['author'] = 'ViaThinkSoft'; |
||
33 | $out['version'] = null; |
||
34 | $out['descriptionHTML'] = null; |
||
35 | return $out; |
||
36 | } |
||
37 | |||
236 | daniel-mar | 38 | public static function name(): string { |
150 | daniel-mar | 39 | return "MySQL"; |
40 | } |
||
41 | |||
239 | daniel-mar | 42 | public function query(string $sql, /*?array*/ $prepared_args=null): OIDplusQueryResult { |
150 | daniel-mar | 43 | $this->last_query = $sql; |
236 | daniel-mar | 44 | if (OIDPLUS_MYSQL_QUERYLOG) file_put_contents(__DIR__."/query.log", "$sql <== ".get_calling_function()."\n", FILE_APPEND); |
150 | daniel-mar | 45 | if (is_null($prepared_args)) { |
236 | daniel-mar | 46 | $res = $this->mysqli->query($sql, MYSQLI_STORE_RESULT); |
47 | |||
48 | if ($res === false) { |
||
49 | throw new OIDplusSQLException($sql, $this->error()); |
||
50 | } else { |
||
51 | return new OIDplusQueryResultMySQL($res); |
||
52 | } |
||
150 | daniel-mar | 53 | } else { |
54 | if (!is_array($prepared_args)) { |
||
55 | throw new Exception("'prepared_args' must be either NULL or an ARRAY."); |
||
56 | } |
||
239 | daniel-mar | 57 | |
58 | foreach ($prepared_args as &$value) { |
||
59 | // MySQLi has problems converting "true/false" to the data type "tinyint(1)" |
||
60 | // It seems to be the same issue like in PDO reported 14 years ago at https://bugs.php.net/bug.php?id=57157 |
||
61 | if (is_bool($value)) $value = $value ? '1' : '0'; |
||
62 | } |
||
63 | |||
150 | daniel-mar | 64 | if (isset($this->prepare_cache[$sql])) { |
65 | $ps = $this->prepare_cache[$sql]; |
||
66 | } else { |
||
67 | $ps = $this->mysqli->prepare($sql); |
||
68 | if (!$ps) { |
||
236 | daniel-mar | 69 | throw new OIDplusSQLException($sql, 'Cannot prepare statement'); |
150 | daniel-mar | 70 | } |
239 | daniel-mar | 71 | |
72 | // Caching the prepared is very risky |
||
73 | // In PDO and ODBC we may not do it, because execute() will |
||
74 | // destroy the existing cursors. |
||
75 | // (test this with ./?goto=oid%3A1.3.6.1.4.1.37553.8.32488192274 |
||
76 | // you will see that 2.999 is missing in the tree) |
||
77 | // But $ps->get_result() seems to "clone" the cursor, |
||
78 | // so that $ps->execute may be called a second time?! |
||
79 | // However, it only works with mysqlnd's get_result, |
||
80 | // not with OIDplusQueryResultMySQLNoNativeDriver |
||
81 | if (self::nativeDriverAvailable()) { |
||
82 | $this->prepare_cache[$sql] = $ps; |
||
83 | } |
||
150 | daniel-mar | 84 | } |
85 | |||
236 | daniel-mar | 86 | self::bind_placeholder_vars($ps,$prepared_args); |
87 | if (!$ps->execute()) { |
||
88 | throw new OIDplusSQLException($sql, $this->error()); |
||
89 | } |
||
208 | daniel-mar | 90 | |
236 | daniel-mar | 91 | if (self::nativeDriverAvailable()) { |
92 | return new OIDplusQueryResultMySQL($ps->get_result()); |
||
93 | } else { |
||
94 | return new OIDplusQueryResultMySQLNoNativeDriver($ps); |
||
95 | } |
||
150 | daniel-mar | 96 | } |
97 | } |
||
236 | daniel-mar | 98 | |
99 | public function insert_id(): int { |
||
150 | daniel-mar | 100 | return $this->mysqli->insert_id; |
101 | } |
||
236 | daniel-mar | 102 | |
103 | public function error(): string { |
||
150 | daniel-mar | 104 | return !empty($this->mysqli->connect_error) ? $this->mysqli->connect_error : $this->mysqli->error; |
105 | } |
||
231 | daniel-mar | 106 | |
245 | daniel-mar | 107 | protected function doConnect(): void { |
150 | daniel-mar | 108 | if (OIDPLUS_MYSQL_QUERYLOG) file_put_contents("query.log", ''); |
109 | |||
110 | // Try connecting to the database |
||
111 | list($hostname,$port) = explode(':', OIDPLUS_MYSQL_HOST.':'.ini_get("mysqli.default_port")); |
||
112 | $this->mysqli = @new mysqli($hostname, OIDPLUS_MYSQL_USERNAME, base64_decode(OIDPLUS_MYSQL_PASSWORD), OIDPLUS_MYSQL_DATABASE, $port); |
||
113 | if (!empty($this->mysqli->connect_error) || ($this->mysqli->connect_errno != 0)) { |
||
245 | daniel-mar | 114 | $message = $this->error(); |
115 | throw new OIDplusConfigInitializationException('Connection to the database failed! '.$message); |
||
150 | daniel-mar | 116 | } |
117 | |||
118 | $this->query("SET NAMES 'utf8'"); |
||
119 | } |
||
120 | |||
245 | daniel-mar | 121 | protected function doDisconnect(): void { |
122 | $this->mysqli->close(); |
||
123 | $this->mysqli = null; |
||
124 | } |
||
125 | |||
150 | daniel-mar | 126 | private $intransaction = false; |
127 | |||
236 | daniel-mar | 128 | public function transaction_begin(): void { |
150 | daniel-mar | 129 | if ($this->intransaction) throw new Exception("Nested transactions are not supported by this database plugin."); |
130 | $this->mysqli->autocommit(true); |
||
131 | $this->intransaction = true; |
||
132 | } |
||
133 | |||
236 | daniel-mar | 134 | public function transaction_commit(): void { |
150 | daniel-mar | 135 | $this->mysqli->commit(); |
136 | $this->mysqli->autocommit(false); |
||
137 | $this->intransaction = false; |
||
138 | } |
||
139 | |||
236 | daniel-mar | 140 | public function transaction_rollback(): void { |
150 | daniel-mar | 141 | $this->mysqli->rollback(); |
142 | $this->mysqli->autocommit(false); |
||
143 | $this->intransaction = false; |
||
144 | } |
||
145 | |||
236 | daniel-mar | 146 | public static function nativeDriverAvailable(): bool { |
147 | return function_exists('mysqli_fetch_all'); |
||
148 | } |
||
150 | daniel-mar | 149 | |
236 | daniel-mar | 150 | private static function bind_placeholder_vars(&$stmt,$params): bool { |
151 | // Credit to: Dave Morgan |
||
152 | // Code taken from: http://www.devmorgan.com/blog/2009/03/27/dydl-part-3-dynamic-binding-with-mysqli-php/ |
||
153 | // https://stackoverflow.com/questions/17219214/how-to-bind-in-mysqli-dynamically |
||
154 | if ($params != null) { |
||
155 | $types = ''; //initial sting with types |
||
156 | foreach ($params as $param) { //for each element, determine type and add |
||
157 | if (is_int($param)) { |
||
158 | $types .= 'i'; //integer |
||
159 | } elseif (is_float($param)) { |
||
160 | $types .= 'd'; //double |
||
161 | } elseif (is_string($param)) { |
||
162 | $types .= 's'; //string |
||
163 | } else { |
||
164 | $types .= 'b'; //blob and unknown |
||
165 | } |
||
208 | daniel-mar | 166 | } |
150 | daniel-mar | 167 | |
236 | daniel-mar | 168 | $bind_names = array(); |
169 | $bind_names[] = $types; //first param needed is the type string, e.g.: 'issss' |
||
150 | daniel-mar | 170 | |
236 | daniel-mar | 171 | for ($i=0; $i<count($params);$i++) { //go through incoming params and added em to array |
172 | $bind_name = 'bind' . $i; //give them an arbitrary name |
||
173 | $$bind_name = $params[$i]; //add the parameter to the variable variable |
||
174 | $bind_names[] = &$$bind_name; //now associate the variable as an element in an array |
||
175 | } |
||
150 | daniel-mar | 176 | |
236 | daniel-mar | 177 | //error_log("better_mysqli has params ".print_r($bind_names, 1)); |
178 | //call the function bind_param with dynamic params |
||
179 | call_user_func_array(array($stmt,'bind_param'),$bind_names); |
||
180 | return true; |
||
181 | } else { |
||
182 | return false; |
||
208 | daniel-mar | 183 | } |
184 | } |
||
150 | daniel-mar | 185 | } |
186 | |||
236 | daniel-mar | 187 | class OIDplusQueryResultMySQL extends OIDplusQueryResult { |
188 | protected $no_resultset; |
||
189 | protected $res; |
||
190 | |||
191 | public function __construct($res) { |
||
239 | daniel-mar | 192 | $this->no_resultset = is_bool($res); |
193 | |||
194 | if (!$this->no_resultset) { |
||
195 | $this->res = $res; |
||
196 | } |
||
208 | daniel-mar | 197 | } |
239 | daniel-mar | 198 | |
199 | public function __destruct() { |
||
200 | if ($this->res) $this->res->close(); |
||
201 | } |
||
150 | daniel-mar | 202 | |
236 | daniel-mar | 203 | public function containsResultSet(): bool { |
204 | return !$this->no_resultset; |
||
205 | } |
||
224 | daniel-mar | 206 | |
236 | daniel-mar | 207 | public function num_rows(): int { |
208 | if ($this->no_resultset) throw new Exception("The query has returned no result set (i.e. it was not a SELECT query)"); |
||
209 | return $this->res->num_rows; |
||
210 | } |
||
150 | daniel-mar | 211 | |
236 | daniel-mar | 212 | public function fetch_array()/*: ?array*/ { |
213 | if ($this->no_resultset) throw new Exception("The query has returned no result set (i.e. it was not a SELECT query)"); |
||
239 | daniel-mar | 214 | return $this->res->fetch_array(MYSQLI_ASSOC); |
236 | daniel-mar | 215 | } |
224 | daniel-mar | 216 | |
236 | daniel-mar | 217 | public function fetch_object()/*: ?object*/ { |
218 | if ($this->no_resultset) throw new Exception("The query has returned no result set (i.e. it was not a SELECT query)"); |
||
219 | return $this->res->fetch_object("stdClass"); |
||
220 | } |
||
221 | } |
||
224 | daniel-mar | 222 | |
236 | daniel-mar | 223 | class OIDplusQueryResultMySQLNoNativeDriver extends OIDplusQueryResult { |
224 | // Based on https://www.php.net/manual/de/mysqli-stmt.get-result.php#113398 |
||
208 | daniel-mar | 225 | |
236 | daniel-mar | 226 | protected $stmt; |
227 | protected $nCols; |
||
228 | protected $no_resultset; |
||
208 | daniel-mar | 229 | |
236 | daniel-mar | 230 | public function __construct($stmt) { |
231 | $metadata = mysqli_stmt_result_metadata($stmt); |
||
208 | daniel-mar | 232 | |
239 | daniel-mar | 233 | $this->no_resultset = is_bool($metadata); |
208 | daniel-mar | 234 | |
236 | daniel-mar | 235 | if (!$this->no_resultset) { |
236 | $this->nCols = mysqli_num_fields($metadata); |
||
237 | $this->stmt = $stmt; |
||
224 | daniel-mar | 238 | |
236 | daniel-mar | 239 | mysqli_free_result($metadata); |
208 | daniel-mar | 240 | } |
236 | daniel-mar | 241 | } |
224 | daniel-mar | 242 | |
236 | daniel-mar | 243 | public function containsResultSet(): bool { |
244 | return !$this->no_resultset; |
||
224 | daniel-mar | 245 | } |
246 | |||
236 | daniel-mar | 247 | public function num_rows(): int { |
248 | if ($this->no_resultset) throw new Exception("The query has returned no result set (i.e. it was not a SELECT query)"); |
||
224 | daniel-mar | 249 | |
236 | daniel-mar | 250 | $this->stmt->store_result(); |
251 | return $this->stmt->num_rows; |
||
252 | } |
||
224 | daniel-mar | 253 | |
239 | daniel-mar | 254 | public function fetch_array()/*: ?array*/ { |
236 | daniel-mar | 255 | if ($this->no_resultset) throw new Exception("The query has returned no result set (i.e. it was not a SELECT query)"); |
256 | |||
257 | // https://stackoverflow.com/questions/10752815/mysqli-get-result-alternative , modified |
||
258 | $stmt = $this->stmt; |
||
259 | $stmt->store_result(); |
||
260 | $resultkeys = array(); |
||
261 | $thisName = ""; |
||
262 | |||
263 | if ($stmt->num_rows==0) return null; |
||
264 | |||
265 | for ($i = 0; $i < $stmt->num_rows; $i++) { |
||
266 | $metadata = $stmt->result_metadata(); |
||
267 | while ($field = $metadata->fetch_field()) { |
||
268 | $thisName = $field->name; |
||
269 | $resultkeys[] = $thisName; |
||
270 | } |
||
208 | daniel-mar | 271 | } |
224 | daniel-mar | 272 | |
236 | daniel-mar | 273 | $ret = array(); |
274 | $args = array(); |
||
275 | for ($i=0; $i<$this->nCols; $i++) { |
||
276 | $ret[$i] = NULL; |
||
277 | $theValue = $resultkeys[$i]; |
||
278 | $ret[$theValue] = NULL; // will be overwritten by mysqli_stmt_bind_result |
||
279 | $args[] = &$ret[$theValue]; |
||
280 | } |
||
281 | if (!mysqli_stmt_bind_result($this->stmt, ...$args)) { |
||
282 | return null; |
||
283 | } |
||
224 | daniel-mar | 284 | |
236 | daniel-mar | 285 | // This should advance the "$stmt" cursor. |
286 | if (!mysqli_stmt_fetch($this->stmt)) { |
||
287 | return null; |
||
288 | } |
||
289 | |||
290 | // Return the array we built. |
||
208 | daniel-mar | 291 | return $ret; |
292 | } |
||
236 | daniel-mar | 293 | |
294 | public function fetch_object()/*: ?object*/ { |
||
295 | if ($this->no_resultset) throw new Exception("The query has returned no result set (i.e. it was not a SELECT query)"); |
||
296 | |||
297 | $ary = $this->fetch_array(); |
||
298 | if (!$ary) return null; |
||
299 | |||
300 | $obj = new stdClass; |
||
301 | foreach ($ary as $name => $val) { |
||
302 | $obj->$name = $val; |
||
303 | } |
||
304 | return $obj; |
||
305 | } |
||
208 | daniel-mar | 306 | } |