1<?php 2 3/** 4 * @noinspection SqlNoDataSourceInspection 5 * @noinspection SqlDialectInspection 6 * @noinspection PhpComposerExtensionStubsInspection 7 */ 8 9namespace dokuwiki\plugin\sqlite; 10 11use dokuwiki\Extension\Event; 12use dokuwiki\Logger; 13 14/** 15 * Helpers to access a SQLite Database with automatic schema migration 16 */ 17class SQLiteDB 18{ 19 const FILE_EXTENSION = '.sqlite3'; 20 21 /** @var \PDO */ 22 protected $pdo; 23 24 /** @var string */ 25 protected $schemadir; 26 27 /** @var string */ 28 protected $dbname; 29 30 /** @var \helper_plugin_sqlite */ 31 protected $helper; 32 33 34 /** 35 * Constructor 36 * 37 * @param string $dbname Database name 38 * @param string $schemadir directory with schema migration files 39 * @param \helper_plugin_sqlite $sqlitehelper for backwards compatibility 40 * @throws \Exception 41 */ 42 public function __construct($dbname, $schemadir, $sqlitehelper = null) 43 { 44 if (!class_exists('pdo') || !in_array('sqlite', \PDO::getAvailableDrivers())) { 45 throw new \Exception('SQLite PDO driver not available'); 46 } 47 48 // backwards compatibility, circular dependency 49 $this->helper = $sqlitehelper; 50 if (!$this->helper) { 51 $this->helper = new \helper_plugin_sqlite(); 52 } 53 $this->helper->setAdapter($this); 54 55 $this->schemadir = $schemadir; 56 $this->dbname = $dbname; 57 $file = $this->getDbFile(); 58 59 $this->pdo = new \PDO( 60 'sqlite:' . $file, 61 null, 62 null, 63 [ 64 \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, 65 \PDO::ATTR_TIMEOUT => 10, // wait for locks up to 10 seconds 66 ] 67 ); 68 69 try { 70 // See https://www.sqlite.org/wal.html 71 $this->exec('PRAGMA journal_mode=WAL'); 72 } catch (\Exception $e) { 73 // this is not critical, but we log it as error. FIXME might be degraded to debug later 74 Logger::error('SQLite: Could not set WAL mode.', $e, $e->getFile(), $e->getLine()); 75 } 76 77 if ($schemadir !== '') { 78 // schema dir is empty, when accessing the DB from Admin interface instead of plugin context 79 $this->applyMigrations(); 80 } 81 Functions::register($this->pdo); 82 } 83 84 /** 85 * Try optimizing the database before closing the connection. 86 * 87 * @see https://www.sqlite.org/pragma.html#pragma_optimize 88 */ 89 public function __destruct() 90 { 91 try { 92 $this->exec("PRAGMA analysis_limit=400"); 93 $this->exec('PRAGMA optimize;'); 94 } catch (\Exception $e) { 95 // ignore failures, this is not essential and not available until 3.18.0. 96 } 97 } 98 99 /** 100 * Do not serialize the DB connection 101 * 102 * @return array 103 */ 104 public function __sleep() 105 { 106 $this->pdo = null; 107 return array_keys(get_object_vars($this)); 108 } 109 110 /** 111 * On deserialization, reinit database connection 112 */ 113 public function __wakeup() 114 { 115 $this->__construct($this->dbname, $this->schemadir, $this->helper); 116 } 117 118 // region public API 119 120 /** 121 * Direct access to the PDO object 122 * @return \PDO 123 */ 124 public function getPdo() 125 { 126 return $this->pdo; 127 } 128 129 /** 130 * Execute a statement and return it 131 * 132 * @param string $sql 133 * @param ...mixed|array $parameters 134 * @return \PDOStatement Be sure to close the cursor yourself 135 * @throws \PDOException 136 */ 137 public function query($sql, ...$parameters) 138 { 139 $start = microtime(true); 140 141 if ($parameters && is_array($parameters[0])) $parameters = $parameters[0]; 142 143 // Statement preparation sometime throws ValueErrors instead of PDOExceptions, we streamline here 144 try { 145 $stmt = $this->pdo->prepare($sql); 146 } catch (\Throwable $e) { 147 throw new \PDOException($e->getMessage(), (int)$e->getCode(), $e); 148 } 149 $eventData = [ 150 'sqlitedb' => $this, 151 'sql' => &$sql, 152 'parameters' => &$parameters, 153 'stmt' => $stmt 154 ]; 155 $event = new Event('PLUGIN_SQLITE_QUERY_EXECUTE', $eventData); 156 if ($event->advise_before()) { 157 $stmt->execute($parameters); 158 } 159 $event->advise_after(); 160 161 $time = microtime(true) - $start; 162 if ($time > 0.2) { 163 Logger::debug('[sqlite] slow query: (' . $time . 's)', [ 164 'sql' => $sql, 165 'parameters' => $parameters, 166 'backtrace' => explode("\n", dbg_backtrace()) 167 ]); 168 } 169 170 return $stmt; 171 } 172 173 /** 174 * Execute a statement and return metadata 175 * 176 * Returns the last insert ID on INSERTs or the number of affected rows 177 * 178 * @param string $sql 179 * @param ...mixed|array $parameters 180 * @return int 181 * @throws \PDOException 182 */ 183 public function exec($sql, ...$parameters) 184 { 185 $stmt = $this->query($sql, ...$parameters); 186 187 $count = $stmt->rowCount(); 188 $stmt->closeCursor(); 189 if ($count && preg_match('/^INSERT /i', $sql)) { 190 return $this->queryValue('SELECT last_insert_rowid()'); 191 } 192 193 return $count; 194 } 195 196 /** 197 * Simple query abstraction 198 * 199 * Returns all data 200 * 201 * @param string $sql 202 * @param ...mixed|array $params 203 * @return array 204 * @throws \PDOException 205 */ 206 public function queryAll($sql, ...$params) 207 { 208 $stmt = $this->query($sql, ...$params); 209 $data = $stmt->fetchAll(\PDO::FETCH_ASSOC); 210 $stmt->closeCursor(); 211 return $data; 212 } 213 214 /** 215 * Query one single row 216 * 217 * @param string $sql 218 * @param ...mixed|array $params 219 * @return array|null 220 * @throws \PDOException 221 */ 222 public function queryRecord($sql, ...$params) 223 { 224 $stmt = $this->query($sql, ...$params); 225 $row = $stmt->fetch(\PDO::FETCH_ASSOC); 226 $stmt->closeCursor(); 227 if (is_array($row) && count($row)) { 228 return $row; 229 } 230 return null; 231 } 232 233 /** 234 * Insert or replace the given data into the table 235 * 236 * @param string $table 237 * @param array $data 238 * @param bool $replace Conflict resolution, replace or ignore 239 * @return array|null Either the inserted row or null if nothing was inserted 240 * @throws \PDOException 241 */ 242 public function saveRecord($table, $data, $replace = true) 243 { 244 $columns = array_map(function ($column) { 245 return '"' . $column . '"'; 246 }, array_keys($data)); 247 $values = array_values($data); 248 $placeholders = array_pad([], count($columns), '?'); 249 250 if ($replace) { 251 $command = 'REPLACE'; 252 } else { 253 $command = 'INSERT OR IGNORE'; 254 } 255 256 /** @noinspection SqlResolve */ 257 $sql = $command . ' INTO "' . $table . '" (' . join(',', $columns) . ') VALUES (' . join(',', 258 $placeholders) . ')'; 259 $stm = $this->query($sql, $values); 260 $success = $stm->rowCount(); 261 $stm->closeCursor(); 262 263 if ($success) { 264 $sql = 'SELECT * FROM "' . $table . '" WHERE rowid = last_insert_rowid()'; 265 return $this->queryRecord($sql); 266 } 267 return null; 268 } 269 270 /** 271 * Execute a query that returns a single value 272 * 273 * @param string $sql 274 * @param ...mixed|array $params 275 * @return mixed|null 276 * @throws \PDOException 277 */ 278 public function queryValue($sql, ...$params) 279 { 280 $result = $this->queryAll($sql, ...$params); 281 if (is_array($result) && count($result)) { 282 return array_values($result[0])[0]; 283 } 284 return null; 285 } 286 287 /** 288 * Execute a query that returns a list of key-value pairs 289 * 290 * The first column is used as key, the second as value. Any additional colums are ignored. 291 * 292 * @param string $sql 293 * @param ...mixed|array $params 294 * @return array 295 */ 296 public function queryKeyValueList($sql, ...$params) 297 { 298 $result = $this->queryAll($sql, ...$params); 299 if (!$result) return []; 300 if (count(array_keys($result[0])) != 2) { 301 throw new \RuntimeException('queryKeyValueList expects a query that returns exactly two columns'); 302 } 303 [$key, $val] = array_keys($result[0]); 304 305 return array_combine( 306 array_column($result, $key), 307 array_column($result, $val) 308 ); 309 } 310 311 // endregion 312 313 // region meta handling 314 315 /** 316 * Get a config value from the opt table 317 * 318 * @param string $opt Config name 319 * @param mixed $default What to return if the value isn't set 320 * @return mixed 321 * @throws \PDOException 322 */ 323 public function getOpt($opt, $default = null) 324 { 325 $value = $this->queryValue("SELECT val FROM opts WHERE opt = ?", [$opt]); 326 if ($value === null) { 327 return $default; 328 } 329 return $value; 330 } 331 332 /** 333 * Set a config value in the opt table 334 * 335 * @param $opt 336 * @param $value 337 * @throws \PDOException 338 */ 339 public function setOpt($opt, $value) 340 { 341 $this->exec('REPLACE INTO opts (opt,val) VALUES (?,?)', [$opt, $value]); 342 } 343 344 /** 345 * @return string 346 */ 347 public function getDbName() 348 { 349 return $this->dbname; 350 } 351 352 /** 353 * @return string 354 */ 355 public function getDbFile() 356 { 357 global $conf; 358 return $conf['metadir'] . '/' . $this->dbname . self::FILE_EXTENSION; 359 } 360 361 /** 362 * Create a dump of the database and its contents 363 * 364 * @return string 365 * @throws \Exception 366 */ 367 public function dumpToFile($filename) 368 { 369 $fp = fopen($filename, 'w'); 370 if (!$fp) { 371 throw new \Exception('Could not open file ' . $filename . ' for writing'); 372 } 373 374 $tables = $this->queryAll("SELECT name,sql FROM sqlite_master WHERE type='table'"); 375 $indexes = $this->queryAll("SELECT name,sql FROM sqlite_master WHERE type='index'"); 376 377 foreach ($tables as $table) { 378 fwrite($fp, "DROP TABLE IF EXISTS '{$table['name']}';\n"); 379 } 380 381 foreach ($tables as $table) { 382 fwrite($fp, $table['sql'] . ";\n"); 383 } 384 385 foreach ($tables as $table) { 386 $sql = "SELECT * FROM " . $table['name']; 387 $res = $this->query($sql); 388 while ($row = $res->fetch(\PDO::FETCH_ASSOC)) { 389 $values = join(',', array_map(function ($value) { 390 if ($value === null) return 'NULL'; 391 return $this->pdo->quote($value); 392 }, $row)); 393 fwrite($fp, "INSERT INTO '{$table['name']}' VALUES ({$values});\n"); 394 } 395 $res->closeCursor(); 396 } 397 398 foreach ($indexes as $index) { 399 fwrite($fp, $index['sql'] . ";\n"); 400 } 401 fclose($fp); 402 return $filename; 403 } 404 405 // endregion 406 407 // region migration handling 408 409 /** 410 * Apply all pending migrations 411 * 412 * Each migration is executed in a transaction which is rolled back on failure 413 * Migrations can be files in the schema directory or event handlers 414 * 415 * @throws \Exception 416 */ 417 protected function applyMigrations() 418 { 419 $currentVersion = $this->currentDbVersion(); 420 $latestVersion = $this->latestDbVersion(); 421 422 if ($currentVersion === $latestVersion) return; 423 424 for ($newVersion = $currentVersion + 1; $newVersion <= $latestVersion; $newVersion++) { 425 $data = [ 426 'dbname' => $this->dbname, 427 'from' => $currentVersion, 428 'to' => $newVersion, 429 'file' => $this->getMigrationFile($newVersion), 430 'sqlite' => $this->helper, 431 'adapter' => $this, 432 ]; 433 $event = new \Doku_Event('PLUGIN_SQLITE_DATABASE_UPGRADE', $data); 434 435 $this->pdo->beginTransaction(); 436 try { 437 if ($event->advise_before()) { 438 // standard migration file 439 $sql = Tools::SQLstring2array(file_get_contents($data['file'])); 440 foreach ($sql as $query) { 441 $this->pdo->exec($query); 442 } 443 } else { 444 if (!$event->result) { 445 // advise before returned false, but the result was false 446 throw new \PDOException('Plugin event did not signal success'); 447 } 448 } 449 $this->setOpt('dbversion', $newVersion); 450 $this->pdo->commit(); 451 $event->advise_after(); 452 } catch (\Exception $e) { 453 // something went wrong, rollback 454 $this->pdo->rollBack(); 455 throw $e; 456 } 457 } 458 459 // vacuum the database to free up unused space 460 $this->pdo->exec('VACUUM'); 461 } 462 463 /** 464 * Read the current version from the opt table 465 * 466 * The opt table is created here if not found 467 * 468 * @return int 469 * @throws \PDOException 470 */ 471 protected function currentDbVersion() 472 { 473 try { 474 $version = $this->getOpt('dbversion', 0); 475 return (int)$version; 476 } catch (\PDOException $e) { 477 if (!preg_match('/no such table/', $e->getMessage())) { 478 // if this is not a "no such table" error, there is something wrong see #80 479 Logger::error( 480 'SQLite: Could not read dbversion from opt table due to unexpected error', 481 [ 482 'dbname' => $this->dbname, 483 'exception' => get_class($e), 484 'message' => $e->getMessage(), 485 'code' => $e->getCode(), 486 ], 487 __FILE__, 488 __LINE__ 489 ); 490 } 491 492 // add the opt table - if this fails too, let the exception bubble up 493 $sql = "CREATE TABLE IF NOT EXISTS opts (opt TEXT NOT NULL PRIMARY KEY, val NOT NULL DEFAULT '')"; 494 $this->exec($sql); 495 return 0; 496 } 497 } 498 499 /** 500 * Get the version this db should have 501 * 502 * @return int 503 * @throws \PDOException 504 */ 505 protected function latestDbVersion() 506 { 507 if (!file_exists($this->schemadir . '/latest.version')) { 508 throw new \PDOException('No latest.version in schema dir'); 509 } 510 return (int)trim(file_get_contents($this->schemadir . '/latest.version')); 511 } 512 513 /** 514 * Get the migrartion file for the given version 515 * 516 * @param int $version 517 * @return string 518 */ 519 protected function getMigrationFile($version) 520 { 521 return sprintf($this->schemadir . '/update%04d.sql', $version); 522 } 523 // endregion 524} 525