Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
64.84% |
83 / 128 |
|
56.25% |
9 / 16 |
CRAP | |
0.00% |
0 / 1 |
| sqlite3 | |
64.84% |
83 / 128 |
|
56.25% |
9 / 16 |
297.25 | |
0.00% |
0 / 1 |
| sql_connect | |
64.29% |
9 / 14 |
|
0.00% |
0 / 1 |
4.73 | |||
| sql_server_info | |
100.00% |
7 / 7 |
|
100.00% |
1 / 1 |
7 | |||
| _sql_transaction | |
85.71% |
6 / 7 |
|
0.00% |
0 / 1 |
4.05 | |||
| sql_query | |
75.00% |
24 / 32 |
|
0.00% |
0 / 1 |
27.89 | |||
| _sql_query_limit | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
3 | |||
| sql_affectedrows | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
2 | |||
| sql_fetchrow | |
100.00% |
7 / 7 |
|
100.00% |
1 / 1 |
5 | |||
| sql_last_inserted_id | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
2 | |||
| sql_freeresult | |
77.78% |
7 / 9 |
|
0.00% |
0 / 1 |
5.27 | |||
| sql_escape | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| sql_like_expression | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| sql_not_like_expression | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| _sql_error | |
60.00% |
6 / 10 |
|
0.00% |
0 / 1 |
3.58 | |||
| _sql_close | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| _sql_report | |
0.00% |
0 / 24 |
|
0.00% |
0 / 1 |
132 | |||
| sql_quote | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
| 1 | <?php |
| 2 | /** |
| 3 | * |
| 4 | * This file is part of the phpBB Forum Software package. |
| 5 | * |
| 6 | * @copyright (c) phpBB Limited <https://www.phpbb.com> |
| 7 | * @license GNU General Public License, version 2 (GPL-2.0) |
| 8 | * |
| 9 | * For full copyright and license information, please see |
| 10 | * the docs/CREDITS.txt file. |
| 11 | * |
| 12 | */ |
| 13 | |
| 14 | namespace phpbb\db\driver; |
| 15 | |
| 16 | /** |
| 17 | * SQLite3 Database Abstraction Layer |
| 18 | * Minimum Requirement: 3.6.15+ |
| 19 | */ |
| 20 | class sqlite3 extends \phpbb\db\driver\driver |
| 21 | { |
| 22 | /** |
| 23 | * @var string Stores errors during connection setup in case the driver is not available |
| 24 | */ |
| 25 | protected $connect_error = ''; |
| 26 | |
| 27 | /** |
| 28 | * @var \SQLite3 The SQLite3 database object to operate against |
| 29 | */ |
| 30 | protected $dbo = null; |
| 31 | |
| 32 | /** |
| 33 | * {@inheritDoc} |
| 34 | */ |
| 35 | public function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false) |
| 36 | { |
| 37 | $this->persistency = false; |
| 38 | $this->user = $sqluser; |
| 39 | $this->server = $sqlserver . (($port) ? ':' . $port : ''); |
| 40 | $this->dbname = $database; |
| 41 | |
| 42 | if (!class_exists('SQLite3', false)) |
| 43 | { |
| 44 | $this->connect_error = 'SQLite3 not found, is the extension installed?'; |
| 45 | return $this->sql_error(''); |
| 46 | } |
| 47 | |
| 48 | try |
| 49 | { |
| 50 | $this->dbo = new \SQLite3($this->server, SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE); |
| 51 | $this->dbo->busyTimeout(60000); |
| 52 | $this->db_connect_id = true; |
| 53 | } |
| 54 | catch (\Exception $e) |
| 55 | { |
| 56 | $this->connect_error = $e->getMessage(); |
| 57 | return array('message' => $this->connect_error); |
| 58 | } |
| 59 | |
| 60 | return true; |
| 61 | } |
| 62 | |
| 63 | /** |
| 64 | * {@inheritDoc} |
| 65 | */ |
| 66 | public function sql_server_info($raw = false, $use_cache = true) |
| 67 | { |
| 68 | global $cache; |
| 69 | |
| 70 | if (!$use_cache || empty($cache) || ($this->sql_server_version = $cache->get('sqlite_version')) === false) |
| 71 | { |
| 72 | $version = \SQLite3::version(); |
| 73 | |
| 74 | $this->sql_server_version = $version['versionString']; |
| 75 | |
| 76 | if (!empty($cache) && $use_cache) |
| 77 | { |
| 78 | $cache->put('sqlite_version', $this->sql_server_version); |
| 79 | } |
| 80 | } |
| 81 | |
| 82 | return ($raw) ? $this->sql_server_version : 'SQLite ' . $this->sql_server_version; |
| 83 | } |
| 84 | |
| 85 | /** |
| 86 | * {@inheritDoc} |
| 87 | */ |
| 88 | protected function _sql_transaction(string $status = 'begin'): bool |
| 89 | { |
| 90 | switch ($status) |
| 91 | { |
| 92 | case 'begin': |
| 93 | return $this->dbo->exec('BEGIN IMMEDIATE'); |
| 94 | |
| 95 | case 'commit': |
| 96 | return $this->dbo->exec('COMMIT'); |
| 97 | |
| 98 | case 'rollback': |
| 99 | return @$this->dbo->exec('ROLLBACK'); |
| 100 | } |
| 101 | |
| 102 | return true; |
| 103 | } |
| 104 | |
| 105 | /** |
| 106 | * {@inheritDoc} |
| 107 | */ |
| 108 | public function sql_query($query = '', $cache_ttl = 0) |
| 109 | { |
| 110 | if ($query != '') |
| 111 | { |
| 112 | global $cache; |
| 113 | |
| 114 | if ($this->debug_sql_explain) |
| 115 | { |
| 116 | $this->sql_report('start', $query); |
| 117 | } |
| 118 | else if ($this->debug_load_time) |
| 119 | { |
| 120 | $this->curtime = microtime(true); |
| 121 | } |
| 122 | |
| 123 | $this->last_query_text = $query; |
| 124 | $this->query_result = ($cache && $cache_ttl) ? $cache->sql_load($query) : false; |
| 125 | $this->sql_add_num_queries($this->query_result); |
| 126 | |
| 127 | if ($this->query_result === false) |
| 128 | { |
| 129 | if ($this->transaction === true && strpos($query, 'INSERT') === 0) |
| 130 | { |
| 131 | $query = preg_replace('/^INSERT INTO/', 'INSERT OR ROLLBACK INTO', $query); |
| 132 | } |
| 133 | |
| 134 | try |
| 135 | { |
| 136 | $this->query_result = @$this->dbo->query($query); |
| 137 | } |
| 138 | catch (\Error $e) |
| 139 | { |
| 140 | // Do nothing as SQL driver will report the error |
| 141 | } |
| 142 | |
| 143 | if ($this->query_result === false) |
| 144 | { |
| 145 | // Try to recover a lost database connection |
| 146 | if ($this->dbo && !@$this->dbo->lastErrorMsg()) |
| 147 | { |
| 148 | if ($this->sql_connect($this->server, $this->user, '', $this->dbname)) |
| 149 | { |
| 150 | $this->query_result = @$this->dbo->query($query); |
| 151 | } |
| 152 | } |
| 153 | |
| 154 | if ($this->query_result === false) |
| 155 | { |
| 156 | $this->sql_error($query); |
| 157 | } |
| 158 | } |
| 159 | |
| 160 | if ($this->debug_sql_explain) |
| 161 | { |
| 162 | $this->sql_report('stop', $query); |
| 163 | } |
| 164 | else if ($this->debug_load_time) |
| 165 | { |
| 166 | $this->sql_time += microtime(true) - $this->curtime; |
| 167 | } |
| 168 | |
| 169 | if (!$this->query_result) |
| 170 | { |
| 171 | return false; |
| 172 | } |
| 173 | |
| 174 | if ($cache && $cache_ttl) |
| 175 | { |
| 176 | $this->query_result = $cache->sql_save($this, $query, $this->query_result, $cache_ttl); |
| 177 | } |
| 178 | } |
| 179 | else if ($this->debug_sql_explain) |
| 180 | { |
| 181 | $this->sql_report('fromcache', $query); |
| 182 | } |
| 183 | } |
| 184 | else |
| 185 | { |
| 186 | return false; |
| 187 | } |
| 188 | |
| 189 | return $this->query_result; |
| 190 | } |
| 191 | |
| 192 | /** |
| 193 | * {@inheritDoc} |
| 194 | */ |
| 195 | protected function _sql_query_limit(string $query, int $total, int $offset = 0, int $cache_ttl = 0) |
| 196 | { |
| 197 | $this->query_result = false; |
| 198 | |
| 199 | // if $total is set to 0 we do not want to limit the number of rows |
| 200 | if ($total == 0) |
| 201 | { |
| 202 | $total = -1; |
| 203 | } |
| 204 | |
| 205 | $query .= "\n LIMIT " . ((!empty($offset)) ? $offset . ', ' . $total : $total); |
| 206 | |
| 207 | return $this->sql_query($query, $cache_ttl); |
| 208 | } |
| 209 | |
| 210 | /** |
| 211 | * {@inheritDoc} |
| 212 | */ |
| 213 | public function sql_affectedrows() |
| 214 | { |
| 215 | return ($this->db_connect_id) ? $this->dbo->changes() : false; |
| 216 | } |
| 217 | |
| 218 | /** |
| 219 | * {@inheritDoc} |
| 220 | */ |
| 221 | public function sql_fetchrow($query_id = false) |
| 222 | { |
| 223 | global $cache; |
| 224 | |
| 225 | if ($query_id === false) |
| 226 | { |
| 227 | /** @var \SQLite3Result $query_id */ |
| 228 | $query_id = $this->query_result; |
| 229 | } |
| 230 | |
| 231 | $safe_query_id = $this->clean_query_id($query_id); |
| 232 | if ($cache && $cache->sql_exists($safe_query_id)) |
| 233 | { |
| 234 | return $cache->sql_fetchrow($safe_query_id); |
| 235 | } |
| 236 | |
| 237 | return is_object($query_id) ? @$query_id->fetchArray(SQLITE3_ASSOC) : false; |
| 238 | } |
| 239 | |
| 240 | /** |
| 241 | * {@inheritdoc} |
| 242 | */ |
| 243 | public function sql_last_inserted_id() |
| 244 | { |
| 245 | return ($this->db_connect_id) ? $this->dbo->lastInsertRowID() : false; |
| 246 | } |
| 247 | |
| 248 | /** |
| 249 | * {@inheritDoc} |
| 250 | */ |
| 251 | public function sql_freeresult($query_id = false) |
| 252 | { |
| 253 | global $cache; |
| 254 | |
| 255 | if ($query_id === false) |
| 256 | { |
| 257 | $query_id = $this->query_result; |
| 258 | } |
| 259 | |
| 260 | $safe_query_id = $this->clean_query_id($query_id); |
| 261 | if ($cache && $cache->sql_exists($safe_query_id)) |
| 262 | { |
| 263 | $cache->sql_freeresult($safe_query_id); |
| 264 | return; |
| 265 | } |
| 266 | |
| 267 | if ($query_id) |
| 268 | { |
| 269 | @$query_id->finalize(); |
| 270 | } |
| 271 | } |
| 272 | |
| 273 | /** |
| 274 | * {@inheritDoc} |
| 275 | */ |
| 276 | public function sql_escape($msg) |
| 277 | { |
| 278 | return \SQLite3::escapeString($msg); |
| 279 | } |
| 280 | |
| 281 | /** |
| 282 | * {@inheritDoc} |
| 283 | * |
| 284 | * For SQLite an underscore is an unknown character. |
| 285 | */ |
| 286 | public function sql_like_expression($expression) |
| 287 | { |
| 288 | // Unlike LIKE, GLOB is unfortunately case sensitive. |
| 289 | // We only catch * and ? here, not the character map possible on file globbing. |
| 290 | $expression = str_replace(array(chr(0) . '_', chr(0) . '%'), array(chr(0) . '?', chr(0) . '*'), $expression); |
| 291 | |
| 292 | $expression = str_replace(array('?', '*'), array("\?", "\*"), $expression); |
| 293 | $expression = str_replace(array(chr(0) . "\?", chr(0) . "\*"), array('?', '*'), $expression); |
| 294 | |
| 295 | return 'GLOB \'' . $this->sql_escape($expression) . '\''; |
| 296 | } |
| 297 | |
| 298 | /** |
| 299 | * {@inheritDoc} |
| 300 | * |
| 301 | * For SQLite an underscore is an unknown character. |
| 302 | */ |
| 303 | public function sql_not_like_expression($expression) |
| 304 | { |
| 305 | // Unlike NOT LIKE, NOT GLOB is unfortunately case sensitive |
| 306 | // We only catch * and ? here, not the character map possible on file globbing. |
| 307 | $expression = str_replace(array(chr(0) . '_', chr(0) . '%'), array(chr(0) . '?', chr(0) . '*'), $expression); |
| 308 | |
| 309 | $expression = str_replace(array('?', '*'), array("\?", "\*"), $expression); |
| 310 | $expression = str_replace(array(chr(0) . "\?", chr(0) . "\*"), array('?', '*'), $expression); |
| 311 | |
| 312 | return 'NOT GLOB \'' . $this->sql_escape($expression) . '\''; |
| 313 | } |
| 314 | |
| 315 | /** |
| 316 | * {@inheritDoc} |
| 317 | */ |
| 318 | protected function _sql_error(): array |
| 319 | { |
| 320 | if (class_exists('SQLite3', false) && isset($this->dbo)) |
| 321 | { |
| 322 | $error = array( |
| 323 | 'message' => $this->dbo->lastErrorMsg(), |
| 324 | 'code' => $this->dbo->lastErrorCode(), |
| 325 | ); |
| 326 | } |
| 327 | else |
| 328 | { |
| 329 | $error = array( |
| 330 | 'message' => $this->connect_error, |
| 331 | 'code' => '', |
| 332 | ); |
| 333 | } |
| 334 | |
| 335 | return $error; |
| 336 | } |
| 337 | |
| 338 | /** |
| 339 | * {@inheritDoc} |
| 340 | */ |
| 341 | protected function _sql_close(): bool |
| 342 | { |
| 343 | return $this->dbo->close(); |
| 344 | } |
| 345 | |
| 346 | /** |
| 347 | * Build db-specific report |
| 348 | * |
| 349 | * @param string $mode Available modes: display, start, stop, |
| 350 | * add_select_row, fromcache, record_fromcache |
| 351 | * @param string $query The Query that should be explained |
| 352 | * |
| 353 | * @return void Either writes HTML to html_hold or outputs a full HTML page |
| 354 | */ |
| 355 | protected function _sql_report(string $mode, string $query = ''): void |
| 356 | { |
| 357 | switch ($mode) |
| 358 | { |
| 359 | case 'start': |
| 360 | |
| 361 | $explain_query = $query; |
| 362 | if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m)) |
| 363 | { |
| 364 | $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2]; |
| 365 | } |
| 366 | else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m)) |
| 367 | { |
| 368 | $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2]; |
| 369 | } |
| 370 | |
| 371 | if (preg_match('/^SELECT/', $explain_query)) |
| 372 | { |
| 373 | $html_table = false; |
| 374 | |
| 375 | if ($result = @$this->dbo->query("EXPLAIN QUERY PLAN $explain_query")) |
| 376 | { |
| 377 | while ($row = $result->fetchArray(SQLITE3_ASSOC)) |
| 378 | { |
| 379 | $html_table = $this->sql_report('add_select_row', $query, $html_table, $row); |
| 380 | } |
| 381 | } |
| 382 | |
| 383 | if ($html_table) |
| 384 | { |
| 385 | $this->html_hold .= '</table>'; |
| 386 | } |
| 387 | } |
| 388 | |
| 389 | break; |
| 390 | |
| 391 | case 'fromcache': |
| 392 | $endtime = explode(' ', microtime()); |
| 393 | $endtime = $endtime[0] + $endtime[1]; |
| 394 | |
| 395 | $result = $this->dbo->query($query); |
| 396 | if ($result) |
| 397 | { |
| 398 | while ($void = $result->fetchArray(SQLITE3_ASSOC)) |
| 399 | { |
| 400 | // Take the time spent on parsing rows into account |
| 401 | } |
| 402 | } |
| 403 | |
| 404 | $splittime = explode(' ', microtime()); |
| 405 | $splittime = $splittime[0] + $splittime[1]; |
| 406 | |
| 407 | $this->sql_report('record_fromcache', $query, $endtime, $splittime); |
| 408 | |
| 409 | break; |
| 410 | } |
| 411 | } |
| 412 | |
| 413 | /** |
| 414 | * {@inheritDoc} |
| 415 | */ |
| 416 | function sql_quote($msg) |
| 417 | { |
| 418 | return '\'' . $msg . '\''; |
| 419 | } |
| 420 | } |