Rev 274 | 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 | |||
246 | daniel-mar | 22 | class OIDplusDatabasePluginMySQLi extends OIDplusDatabasePlugin { |
261 | daniel-mar | 23 | private $conn = null; // only with MySQLnd |
150 | daniel-mar | 24 | private $prepare_cache = array(); |
261 | daniel-mar | 25 | private $last_error = null; // we need that because MySQL divides prepared statement errors and normal query errors, but we have only one "error()" method |
150 | daniel-mar | 26 | |
236 | daniel-mar | 27 | public static function getPluginInformation(): array { |
222 | daniel-mar | 28 | $out = array(); |
29 | $out['name'] = 'MySQLi'; |
||
30 | $out['author'] = 'ViaThinkSoft'; |
||
31 | $out['version'] = null; |
||
32 | $out['descriptionHTML'] = null; |
||
33 | return $out; |
||
34 | } |
||
35 | |||
275 | daniel-mar | 36 | public static function id(): string { |
150 | daniel-mar | 37 | return "MySQL"; |
38 | } |
||
39 | |||
261 | daniel-mar | 40 | public function doQuery(string $sql, /*?array*/ $prepared_args=null): OIDplusQueryResult { |
41 | $this->last_error = null; |
||
150 | daniel-mar | 42 | if (is_null($prepared_args)) { |
257 | daniel-mar | 43 | $res = $this->conn->query($sql, MYSQLI_STORE_RESULT); |
236 | daniel-mar | 44 | |
45 | if ($res === false) { |
||
261 | daniel-mar | 46 | $this->last_error = $this->conn->error; |
236 | daniel-mar | 47 | throw new OIDplusSQLException($sql, $this->error()); |
48 | } else { |
||
49 | return new OIDplusQueryResultMySQL($res); |
||
50 | } |
||
150 | daniel-mar | 51 | } else { |
52 | if (!is_array($prepared_args)) { |
||
250 | daniel-mar | 53 | throw new OIDplusException("'prepared_args' must be either NULL or an ARRAY."); |
150 | daniel-mar | 54 | } |
260 | daniel-mar | 55 | |
239 | daniel-mar | 56 | foreach ($prepared_args as &$value) { |
57 | // MySQLi has problems converting "true/false" to the data type "tinyint(1)" |
||
58 | // It seems to be the same issue like in PDO reported 14 years ago at https://bugs.php.net/bug.php?id=57157 |
||
59 | if (is_bool($value)) $value = $value ? '1' : '0'; |
||
60 | } |
||
260 | daniel-mar | 61 | |
150 | daniel-mar | 62 | if (isset($this->prepare_cache[$sql])) { |
63 | $ps = $this->prepare_cache[$sql]; |
||
64 | } else { |
||
257 | daniel-mar | 65 | $ps = $this->conn->prepare($sql); |
150 | daniel-mar | 66 | if (!$ps) { |
266 | daniel-mar | 67 | $this->last_error = $this->conn->error; |
68 | throw new OIDplusSQLException($sql, 'Cannot prepare statement: '.$this->error()); |
||
150 | daniel-mar | 69 | } |
260 | daniel-mar | 70 | |
239 | daniel-mar | 71 | // Caching the prepared is very risky |
72 | // In PDO and ODBC we may not do it, because execute() will |
||
73 | // destroy the existing cursors. |
||
74 | // (test this with ./?goto=oid%3A1.3.6.1.4.1.37553.8.32488192274 |
||
75 | // you will see that 2.999 is missing in the tree) |
||
76 | // But $ps->get_result() seems to "clone" the cursor, |
||
77 | // so that $ps->execute may be called a second time?! |
||
78 | // However, it only works with mysqlnd's get_result, |
||
79 | // not with OIDplusQueryResultMySQLNoNativeDriver |
||
80 | if (self::nativeDriverAvailable()) { |
||
81 | $this->prepare_cache[$sql] = $ps; |
||
82 | } |
||
150 | daniel-mar | 83 | } |
84 | |||
236 | daniel-mar | 85 | self::bind_placeholder_vars($ps,$prepared_args); |
86 | if (!$ps->execute()) { |
||
261 | daniel-mar | 87 | $this->last_error = mysqli_stmt_error($ps); |
236 | daniel-mar | 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 { |
||
257 | daniel-mar | 100 | return $this->conn->insert_id; |
150 | daniel-mar | 101 | } |
236 | daniel-mar | 102 | |
103 | public function error(): string { |
||
261 | daniel-mar | 104 | $err = $this->last_error; |
105 | if ($err == null) $err = ''; |
||
106 | return $err; |
||
150 | daniel-mar | 107 | } |
231 | daniel-mar | 108 | |
269 | daniel-mar | 109 | protected function doConnect()/*: void*/ { |
260 | daniel-mar | 110 | if (!function_exists('mysqli_connect')) throw new OIDplusException('PHP extension "MySQLi" not installed'); |
150 | daniel-mar | 111 | |
112 | // Try connecting to the database |
||
261 | daniel-mar | 113 | $host = OIDplus::baseConfig()->getValue('MYSQL_HOST', 'localhost'); |
114 | $username = OIDplus::baseConfig()->getValue('MYSQL_USERNAME', 'root'); |
||
115 | $password = OIDplus::baseConfig()->getValue('MYSQL_PASSWORD', ''); |
||
116 | $database = OIDplus::baseConfig()->getValue('MYSQL_DATABASE', 'oidplus'); |
||
117 | list($hostname,$port) = explode(':', $host.':'.ini_get("mysqli.default_port")); |
||
118 | $this->conn = @new mysqli($hostname, $username, $password, $database, $port); |
||
257 | daniel-mar | 119 | if (!empty($this->conn->connect_error) || ($this->conn->connect_errno != 0)) { |
261 | daniel-mar | 120 | $message = $this->conn->connect_error; |
245 | daniel-mar | 121 | throw new OIDplusConfigInitializationException('Connection to the database failed! '.$message); |
150 | daniel-mar | 122 | } |
123 | |||
264 | daniel-mar | 124 | $this->prepare_cache = array(); |
125 | $this->last_error = null; |
||
126 | |||
150 | daniel-mar | 127 | $this->query("SET NAMES 'utf8'"); |
274 | daniel-mar | 128 | |
129 | $this->slang = self::getHardcodedSlangById('mysql'); |
||
130 | if (is_null($this->slang)) { |
||
131 | throw new OIDplusConfigInitializationException("Slang plugin 'mysql' is missing"); |
||
132 | } |
||
150 | daniel-mar | 133 | } |
134 | |||
269 | daniel-mar | 135 | protected function doDisconnect()/*: void*/ { |
261 | daniel-mar | 136 | $this->prepare_cache = array(); |
264 | daniel-mar | 137 | if (!is_null($this->conn)) { |
138 | $this->conn->close(); |
||
139 | $this->conn = null; |
||
140 | } |
||
245 | daniel-mar | 141 | } |
142 | |||
150 | daniel-mar | 143 | private $intransaction = false; |
144 | |||
269 | daniel-mar | 145 | public function transaction_begin()/*: void*/ { |
250 | daniel-mar | 146 | if ($this->intransaction) throw new OIDplusException("Nested transactions are not supported by this database plugin."); |
264 | daniel-mar | 147 | $this->conn->autocommit(false); |
148 | $this->conn->begin_transaction(); |
||
150 | daniel-mar | 149 | $this->intransaction = true; |
150 | } |
||
151 | |||
269 | daniel-mar | 152 | public function transaction_commit()/*: void*/ { |
257 | daniel-mar | 153 | $this->conn->commit(); |
264 | daniel-mar | 154 | $this->conn->autocommit(true); |
150 | daniel-mar | 155 | $this->intransaction = false; |
156 | } |
||
157 | |||
269 | daniel-mar | 158 | public function transaction_rollback()/*: void*/ { |
257 | daniel-mar | 159 | $this->conn->rollback(); |
264 | daniel-mar | 160 | $this->conn->autocommit(true); |
150 | daniel-mar | 161 | $this->intransaction = false; |
162 | } |
||
163 | |||
264 | daniel-mar | 164 | public function sqlDate(): string { |
165 | return 'now()'; |
||
166 | } |
||
167 | |||
236 | daniel-mar | 168 | public static function nativeDriverAvailable(): bool { |
261 | daniel-mar | 169 | return function_exists('mysqli_fetch_all') && (OIDplus::baseConfig()->getValue('MYSQL_FORCE_MYSQLND_SUPPLEMENT', false) === false); |
236 | daniel-mar | 170 | } |
150 | daniel-mar | 171 | |
236 | daniel-mar | 172 | private static function bind_placeholder_vars(&$stmt,$params): bool { |
173 | // Credit to: Dave Morgan |
||
174 | // Code taken from: http://www.devmorgan.com/blog/2009/03/27/dydl-part-3-dynamic-binding-with-mysqli-php/ |
||
175 | // https://stackoverflow.com/questions/17219214/how-to-bind-in-mysqli-dynamically |
||
176 | if ($params != null) { |
||
177 | $types = ''; //initial sting with types |
||
178 | foreach ($params as $param) { //for each element, determine type and add |
||
179 | if (is_int($param)) { |
||
180 | $types .= 'i'; //integer |
||
181 | } elseif (is_float($param)) { |
||
182 | $types .= 'd'; //double |
||
183 | } elseif (is_string($param)) { |
||
184 | $types .= 's'; //string |
||
185 | } else { |
||
186 | $types .= 'b'; //blob and unknown |
||
187 | } |
||
208 | daniel-mar | 188 | } |
150 | daniel-mar | 189 | |
236 | daniel-mar | 190 | $bind_names = array(); |
191 | $bind_names[] = $types; //first param needed is the type string, e.g.: 'issss' |
||
150 | daniel-mar | 192 | |
236 | daniel-mar | 193 | for ($i=0; $i<count($params);$i++) { //go through incoming params and added em to array |
194 | $bind_name = 'bind' . $i; //give them an arbitrary name |
||
195 | $$bind_name = $params[$i]; //add the parameter to the variable variable |
||
196 | $bind_names[] = &$$bind_name; //now associate the variable as an element in an array |
||
197 | } |
||
150 | daniel-mar | 198 | |
236 | daniel-mar | 199 | //error_log("better_mysqli has params ".print_r($bind_names, 1)); |
200 | //call the function bind_param with dynamic params |
||
201 | call_user_func_array(array($stmt,'bind_param'),$bind_names); |
||
202 | return true; |
||
203 | } else { |
||
204 | return false; |
||
208 | daniel-mar | 205 | } |
206 | } |
||
150 | daniel-mar | 207 | } |
208 | |||
236 | daniel-mar | 209 | class OIDplusQueryResultMySQL extends OIDplusQueryResult { |
210 | protected $no_resultset; |
||
211 | protected $res; |
||
212 | |||
213 | public function __construct($res) { |
||
239 | daniel-mar | 214 | $this->no_resultset = is_bool($res); |
260 | daniel-mar | 215 | |
239 | daniel-mar | 216 | if (!$this->no_resultset) { |
217 | $this->res = $res; |
||
218 | } |
||
208 | daniel-mar | 219 | } |
260 | daniel-mar | 220 | |
239 | daniel-mar | 221 | public function __destruct() { |
222 | if ($this->res) $this->res->close(); |
||
223 | } |
||
150 | daniel-mar | 224 | |
236 | daniel-mar | 225 | public function containsResultSet(): bool { |
226 | return !$this->no_resultset; |
||
227 | } |
||
224 | daniel-mar | 228 | |
236 | daniel-mar | 229 | public function num_rows(): int { |
250 | daniel-mar | 230 | if ($this->no_resultset) throw new OIDplusException("The query has returned no result set (i.e. it was not a SELECT query)"); |
236 | daniel-mar | 231 | return $this->res->num_rows; |
232 | } |
||
150 | daniel-mar | 233 | |
236 | daniel-mar | 234 | public function fetch_array()/*: ?array*/ { |
250 | daniel-mar | 235 | if ($this->no_resultset) throw new OIDplusException("The query has returned no result set (i.e. it was not a SELECT query)"); |
239 | daniel-mar | 236 | return $this->res->fetch_array(MYSQLI_ASSOC); |
236 | daniel-mar | 237 | } |
224 | daniel-mar | 238 | |
236 | daniel-mar | 239 | public function fetch_object()/*: ?object*/ { |
250 | daniel-mar | 240 | if ($this->no_resultset) throw new OIDplusException("The query has returned no result set (i.e. it was not a SELECT query)"); |
236 | daniel-mar | 241 | return $this->res->fetch_object("stdClass"); |
242 | } |
||
243 | } |
||
224 | daniel-mar | 244 | |
236 | daniel-mar | 245 | class OIDplusQueryResultMySQLNoNativeDriver extends OIDplusQueryResult { |
246 | // Based on https://www.php.net/manual/de/mysqli-stmt.get-result.php#113398 |
||
208 | daniel-mar | 247 | |
236 | daniel-mar | 248 | protected $stmt; |
249 | protected $nCols; |
||
250 | protected $no_resultset; |
||
208 | daniel-mar | 251 | |
236 | daniel-mar | 252 | public function __construct($stmt) { |
253 | $metadata = mysqli_stmt_result_metadata($stmt); |
||
208 | daniel-mar | 254 | |
239 | daniel-mar | 255 | $this->no_resultset = is_bool($metadata); |
208 | daniel-mar | 256 | |
236 | daniel-mar | 257 | if (!$this->no_resultset) { |
258 | $this->nCols = mysqli_num_fields($metadata); |
||
259 | $this->stmt = $stmt; |
||
224 | daniel-mar | 260 | |
236 | daniel-mar | 261 | mysqli_free_result($metadata); |
208 | daniel-mar | 262 | } |
236 | daniel-mar | 263 | } |
224 | daniel-mar | 264 | |
236 | daniel-mar | 265 | public function containsResultSet(): bool { |
266 | return !$this->no_resultset; |
||
224 | daniel-mar | 267 | } |
268 | |||
236 | daniel-mar | 269 | public function num_rows(): int { |
250 | daniel-mar | 270 | if ($this->no_resultset) throw new OIDplusException("The query has returned no result set (i.e. it was not a SELECT query)"); |
224 | daniel-mar | 271 | |
236 | daniel-mar | 272 | $this->stmt->store_result(); |
273 | return $this->stmt->num_rows; |
||
274 | } |
||
224 | daniel-mar | 275 | |
239 | daniel-mar | 276 | public function fetch_array()/*: ?array*/ { |
250 | daniel-mar | 277 | if ($this->no_resultset) throw new OIDplusException("The query has returned no result set (i.e. it was not a SELECT query)"); |
236 | daniel-mar | 278 | |
279 | // https://stackoverflow.com/questions/10752815/mysqli-get-result-alternative , modified |
||
280 | $stmt = $this->stmt; |
||
281 | $stmt->store_result(); |
||
282 | $resultkeys = array(); |
||
283 | $thisName = ""; |
||
284 | |||
285 | if ($stmt->num_rows==0) return null; |
||
286 | |||
287 | for ($i = 0; $i < $stmt->num_rows; $i++) { |
||
288 | $metadata = $stmt->result_metadata(); |
||
289 | while ($field = $metadata->fetch_field()) { |
||
290 | $thisName = $field->name; |
||
291 | $resultkeys[] = $thisName; |
||
292 | } |
||
208 | daniel-mar | 293 | } |
224 | daniel-mar | 294 | |
236 | daniel-mar | 295 | $ret = array(); |
296 | $args = array(); |
||
297 | for ($i=0; $i<$this->nCols; $i++) { |
||
298 | $ret[$i] = NULL; |
||
299 | $theValue = $resultkeys[$i]; |
||
300 | $ret[$theValue] = NULL; // will be overwritten by mysqli_stmt_bind_result |
||
301 | $args[] = &$ret[$theValue]; |
||
302 | } |
||
303 | if (!mysqli_stmt_bind_result($this->stmt, ...$args)) { |
||
304 | return null; |
||
305 | } |
||
224 | daniel-mar | 306 | |
236 | daniel-mar | 307 | // This should advance the "$stmt" cursor. |
308 | if (!mysqli_stmt_fetch($this->stmt)) { |
||
309 | return null; |
||
310 | } |
||
311 | |||
312 | // Return the array we built. |
||
208 | daniel-mar | 313 | return $ret; |
314 | } |
||
236 | daniel-mar | 315 | |
316 | public function fetch_object()/*: ?object*/ { |
||
260 | daniel-mar | 317 | if ($this->no_resultset) throw new OIDplusConfigInitializationException("The query has returned no result set (i.e. it was not a SELECT query)"); |
236 | daniel-mar | 318 | |
319 | $ary = $this->fetch_array(); |
||
320 | if (!$ary) return null; |
||
321 | |||
322 | $obj = new stdClass; |
||
323 | foreach ($ary as $name => $val) { |
||
324 | $obj->$name = $val; |
||
325 | } |
||
326 | return $obj; |
||
327 | } |
||
208 | daniel-mar | 328 | } |