1 <?php /** @noinspection SpellCheckingInspection */
2 
3 /**
4  * Copyright (c) 2021. ComboStrap, Inc. and its affiliates. All Rights Reserved.
5  *
6  * This source code is licensed under the GPL license found in the
7  * COPYING  file in the root directory of this source tree.
8  *
9  * @license  GPL 3 (https://www.gnu.org/licenses/gpl-3.0.en.html)
10  * @author   ComboStrap <support@combostrap.com>
11  *
12  */
13 
14 namespace ComboStrap;
15 
16 
17 use dokuwiki\plugin\sqlite\SQLiteDB;
18 use helper_plugin_sqlite;
19 
20 class Sqlite
21 {
22 
23 
24     /**
25      * Principal database
26      * (Backup)
27      */
28     private const  MAIN_DATABASE_NAME = "combo";
29     /**
30      * Backend Databse
31      * (Log, Pub/Sub,...)
32      */
33     private const  SECONDARY_DB = "combo-secondary";
34 
35     private static $sqliteVersion;
36 
37 
38     private helper_plugin_sqlite $sqlitePlugin;
39 
40     /**
41      * @var SqliteRequest the actual request. If not closed, it will be close.
42      * Otherwise, it's not possible to delete the database file. See {@link self::deleteDatabasesFile()}
43      */
44     private SqliteRequest $actualRequest;
45 
46 
47     /**
48      * Sqlite constructor.
49      * @var helper_plugin_sqlite $sqlitePlugin
50      */
51     public function __construct(helper_plugin_sqlite $sqlitePlugin)
52     {
53         $this->sqlitePlugin = $sqlitePlugin;
54     }
55 
56 
57     /**
58      *
59      * @return Sqlite $sqlite
60      * @throws ExceptionSqliteNotAvailable
61      */
62     public static function createOrGetSqlite($databaseName = self::MAIN_DATABASE_NAME): Sqlite
63     {
64 
65         $sqliteExecutionObjectIdentifier = Sqlite::class . "-$databaseName";
66         $executionContext = ExecutionContext::getActualOrCreateFromEnv();
67 
68         try {
69             /**
70              * @var Sqlite $sqlite
71              *
72              *
73              * sqlite is stored globally
74              * because when we create a new instance, it will open the
75              * sqlite file.
76              *
77              * In a {@link cli_plugin_combo} run, you will run in the error:
78              * ``
79              * failed to open stream: Too many open files
80              * ``
81              * As there is by default a limit of 1024 open files
82              * which means that if there is more than 1024 pages
83              * that you replicate using a new sqlite instance each time,
84              * you fail.
85              *
86              */
87             $sqlite = $executionContext->getRuntimeObject($sqliteExecutionObjectIdentifier);
88         } catch (ExceptionNotFound $e) {
89             $sqlite = null;
90         }
91 
92         if ($sqlite !== null) {
93             $res = $sqlite->doWeNeedToCreateNewInstance();
94             if ($res === false) {
95                 return $sqlite;
96             }
97         }
98 
99         /**
100          * Init
101          * @var helper_plugin_sqlite $sqlitePlugin
102          */
103         $sqlitePlugin = plugin_load('helper', 'sqlite');
104         /**
105          * Not enabled / loaded
106          */
107         if ($sqlitePlugin === null) {
108 
109             $sqliteMandatoryMessage = "The Sqlite Plugin is mandatory. Some functionalities of the ComboStrap Plugin may not work.";
110             throw new ExceptionSqliteNotAvailable($sqliteMandatoryMessage);
111         }
112 
113         $adapter = $sqlitePlugin->getAdapter();
114         if ($adapter == null) {
115             self::sendMessageAsNotAvailable();
116         }
117 
118         $adapter->setUseNativeAlter(true);
119 
120         list($databaseName, $databaseDefinitionDir) = self::getDatabaseNameAndDefinitionDirectory($databaseName);
121         $init = $sqlitePlugin->init($databaseName, $databaseDefinitionDir);
122         if (!$init) {
123             $message = "Unable to initialize Sqlite";
124             throw new ExceptionSqliteNotAvailable($message);
125         }
126         // regexp implementation
127         // https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query/18484596#18484596
128         $adapter = $sqlitePlugin->getAdapter();
129         $regexFunctioName = 'regexp';
130         $regexpClosure = function ($pattern, $data, $delimiter = '~', $modifiers = 'isuS') {
131             if (isset($pattern, $data) === true) {
132                 return (preg_match(sprintf('%1$s%2$s%1$s%3$s', $delimiter, $pattern, $modifiers), $data) > 0);
133             }
134             return null;
135         };
136         $regexArgCount = 4;
137         if (!self::isJuneVersion($adapter)) {
138             /** @noinspection PhpUndefinedMethodInspection */
139             $adapter->create_function($regexFunctioName, $regexpClosure, $regexArgCount);
140         } else {
141             $adapter->getPdo()->sqliteCreateFunction($regexFunctioName, $regexpClosure, $regexArgCount);
142         }
143 
144         $sqlite = new Sqlite($sqlitePlugin);
145         $executionContext->setRuntimeObject($sqliteExecutionObjectIdentifier, $sqlite);
146         return $sqlite;
147 
148     }
149 
150     /**
151      * @throws ExceptionSqliteNotAvailable
152      */
153     public static function createOrGetBackendSqlite(): ?Sqlite
154     {
155         return self::createOrGetSqlite(self::SECONDARY_DB);
156     }
157 
158 
159     public static function createSelectFromTableAndColumns(string $tableName, array $columns = null): string
160     {
161         if ($columns === null) {
162             $columnStatement = "*";
163         } else {
164             $columnsStatement = [];
165             foreach ($columns as $columnName) {
166                 $columnsStatement[] = "$columnName as \"$columnName\"";
167             }
168             $columnStatement = implode(", ", $columnsStatement);
169         }
170         /**
171          * TODO: We had added the `rowid` on all query
172          *  but the underlining code was not supporting it,
173          *  adding it in the next release to be able to locate the row
174          */
175         return "select $columnStatement from $tableName";
176 
177     }
178 
179     /**
180      * Used in test to delete the database file
181      * @return void
182      * @throws ExceptionFileSystem - if we can delete the databases
183      */
184     public static function deleteDatabasesFile()
185     {
186         /**
187          * The plugin does not give you the option to
188          * where to create the database file
189          * See {@link \helper_plugin_sqlite_adapter::initdb()}
190          * $this->dbfile = $conf['metadir'].'/'.$dbname.$this->fileextension;
191          *
192          * If error on delete, see {@link self::close()}
193          */
194         $metadatDirectory = ExecutionContext::getActualOrCreateFromEnv()
195             ->getConfig()
196             ->getMetaDataDirectory();
197         $fileChildren = FileSystems::getChildrenLeaf($metadatDirectory);
198         foreach ($fileChildren as $child) {
199             try {
200                 $extension = $child->getExtension();
201             } catch (ExceptionNotFound $e) {
202                 // ok no extension
203                 continue;
204             }
205             if (in_array($extension, ["sqlite", "sqlite3"])) {
206                 FileSystems::delete($child);
207             }
208 
209         }
210     }
211 
212     private static function getDatabaseNameAndDefinitionDirectory($databaseName): array
213     {
214         global $conf;
215 
216         if ($databaseName === self::MAIN_DATABASE_NAME) {
217             $oldDbName = '404manager';
218             $oldDbFile = $conf['metadir'] . "/{$oldDbName}.sqlite";
219             $oldDbFileSqlite3 = $conf['metadir'] . "/{$oldDbName}.sqlite3";
220             if (file_exists($oldDbFile) || file_exists($oldDbFileSqlite3)) {
221                 $databaseName = $oldDbName;
222             }
223         }
224 
225         $databaseDir = DOKU_PLUGIN . PluginUtility::PLUGIN_BASE_NAME . "/db/$databaseName";
226         return [$databaseName, $databaseDir];
227 
228     }
229 
230     /**
231      * Print debug info to the console in order to resolve
232      * RuntimeException: HY000 8 attempt to write a readonly database
233      * https://phpunit.readthedocs.io/en/latest/writing-tests-for-phpunit.html#error-output
234      */
235     public function printDbInfoAtConsole()
236     {
237         $dbFile = $this->sqlitePlugin->getAdapter()->getDbFile();
238         fwrite(STDERR, "Stderr DbFile: " . $dbFile . "\n");
239         if (file_exists($dbFile)) {
240             fwrite(STDERR, "File does exists\n");
241             fwrite(STDERR, "Permission " . substr(sprintf('%o', fileperms($dbFile)), -4) . "\n");
242         } else {
243             fwrite(STDERR, "File does not exist\n");
244         }
245 
246         global $conf;
247         $metadir = $conf['metadir'];
248         fwrite(STDERR, "MetaDir: " . $metadir . "\n");
249         $subdir = strpos($dbFile, $metadir) === 0;
250         if ($subdir) {
251             fwrite(STDERR, "Meta is a subdirectory of the db \n");
252         } else {
253             fwrite(STDERR, "Meta is a not subdirectory of the db \n");
254         }
255 
256     }
257 
258     /**
259      * Json support
260      */
261     public function supportJson(): bool
262     {
263 
264 
265         $res = $this->sqlitePlugin->query("PRAGMA compile_options");
266         $isJsonEnabled = false;
267         foreach ($this->sqlitePlugin->res2arr($res) as $row) {
268             if ($row["compile_option"] === "ENABLE_JSON1") {
269                 $isJsonEnabled = true;
270                 break;
271             }
272         };
273         $this->sqlitePlugin->res_close($res);
274         return $isJsonEnabled;
275     }
276 
277 
278     /**
279      * @throws ExceptionSqliteNotAvailable
280      */
281     public
282     static function sendMessageAsNotAvailable(): void
283     {
284         $sqliteMandatoryMessage = "The Sqlite Php Extension is mandatory. It seems that it's not available on this installation.";
285         throw new ExceptionSqliteNotAvailable($sqliteMandatoryMessage);
286     }
287 
288     /**
289      *
290      * Old check when there was no {@link ExecutionContext}
291      * to reset the Sqlite variable
292      * TODO: delete ?
293      *
294      *
295      */
296     private function doWeNeedToCreateNewInstance(): bool
297     {
298 
299         global $conf;
300         $metaDir = $conf['metadir'];
301 
302         /**
303          * Adapter may be null
304          * when the SQLite & PDO SQLite
305          * are not installed
306          * ie: SQLite & PDO SQLite support missing
307          */
308         $adapter = $this->sqlitePlugin->getAdapter();
309         if ($adapter === null) {
310             return true;
311         }
312 
313         /**
314          * When the database is {@link \helper_plugin_sqlite_adapter::closedb()}
315          */
316         if (!self::isJuneVersion($adapter)) {
317             /** @noinspection PhpUndefinedMethodInspection */
318             $db = $adapter->getDb();
319         } else {
320             $db = $adapter->getPdo();
321         }
322         if ($db === null) {
323             /**
324              * We may also open it again
325              * {@link \helper_plugin_sqlite_adapter::opendb()}
326              * for now, reinit
327              */
328             return true;
329         }
330 
331         /**
332          * In test, we are running in different context (ie different root
333          * directory for DokuWiki and therefore different $conf
334          * and therefore different metadir where sqlite is stored)
335          * Because a sql file may be deleted, we may get:
336          * ```
337          * RuntimeException: HY000 8 attempt to write a readonly database:
338          * ```
339          * To avoid this error, we check that we are still in the same metadir
340          * where the sqlite database is stored. If not, we create a new instance
341          */
342         $dbFile = $adapter->getDbFile();
343         if (!file_exists($dbFile)) {
344             $this->close();
345             return true;
346         }
347         // the file is in the meta directory
348         if (strpos($dbFile, $metaDir) === 0) {
349             // we are still in a class run
350             return false;
351         }
352         $this->close();
353         return true;
354     }
355 
356     public function close()
357     {
358 
359         /**
360          * https://www.php.net/manual/en/pdo.connections.php#114822
361          * You put the variable connection on null
362          * the {@link \helper_plugin_sqlite_adapter::closedb() function} do that
363          *
364          * If we don't do that, the file is still locked
365          * by the sqlite process and the clean up process
366          * of dokuwiki test cannot delete it
367          *
368          * ie to avoid
369          * RuntimeException: Unable to delete the file
370          * (C:/Users/GERARD~1/AppData/Local/Temp/dwtests-1676813655.6773/data/meta/combo-secondary.sqlite3) in D:\dokuwiki\_test\core\TestUtils.php on line 58
371          * {@link TestUtils::rdelete}
372          *
373          * Windows sort of handling/ bug explained here
374          * https://bugs.php.net/bug.php?id=78930&edit=3
375          *
376          * Null to close the db explanation and bug
377          * https://bugs.php.net/bug.php?id=62065
378          *
379          */
380 
381         $this->closeActualRequestIfNotClosed();
382 
383         $adapter = $this->sqlitePlugin->getAdapter();
384         if ($adapter !== null) {
385 
386             if (!$this->isJuneVersion($adapter)) {
387                 /** @noinspection PhpUndefinedMethodInspection */
388                 $adapter->closedb();
389             } else {
390                 $adapter->__sleep();
391             }
392 
393             unset($adapter);
394 
395             gc_collect_cycles();
396 
397         }
398 
399     }
400 
401     public function getDbName(): string
402     {
403         return $this->sqlitePlugin->getAdapter()->getName();
404     }
405 
406 
407     public function getSqlitePlugin(): helper_plugin_sqlite
408     {
409         return $this->sqlitePlugin;
410     }
411 
412     public function createRequest(): SqliteRequest
413     {
414         $this->closeActualRequestIfNotClosed();
415         $this->actualRequest = new SqliteRequest($this);
416         return $this->actualRequest;
417     }
418 
419     public function getVersion()
420     {
421         if (self::$sqliteVersion === null) {
422             $request = $this->createRequest()
423                 ->setQuery("select sqlite_version()");
424             try {
425                 self::$sqliteVersion = $request
426                     ->execute()
427                     ->getFirstCellValue();
428             } catch (ExceptionCompile $e) {
429                 self::$sqliteVersion = "unknown";
430             } finally {
431                 $request->close();
432             }
433         }
434         return self::$sqliteVersion;
435     }
436 
437     /**
438      * @param string $option
439      * @return bool - true if the option is available
440      */
441     public function hasOption(string $option): bool
442     {
443         try {
444             $present = $this->createRequest()
445                 ->setQueryParametrized("select count(1) from pragma_compile_options() where compile_options = ?", [$option])
446                 ->execute()
447                 ->getFirstCellValueAsInt();
448             return $present === 1;
449         } catch (ExceptionCompile $e) {
450             LogUtility::msg("Error while trying to see if the sqlite option is available");
451             return false;
452         }
453 
454     }
455 
456     /**
457      * Internal function that closes the actual request
458      * This is to be able to close all resources even if the developer
459      * forget.
460      *
461      * This is needed to be able to delete the database file.
462      * See {@link self::close()} for more information
463      *
464      * @return void
465      */
466     private function closeActualRequestIfNotClosed()
467     {
468         if (isset($this->actualRequest)) {
469             $this->actualRequest->close();
470             unset($this->actualRequest);
471         }
472     }
473 
474     /**
475      * Version 2023-06-21 brings error
476      * https://github.com/cosmocode/sqlite/releases/tag/2023-06-21
477      * https://www.dokuwiki.org/plugin:sqlite#changes_from_earlier_releases
478      * @param $adapter
479      * @return bool
480      */
481     public static function isJuneVersion($adapter): bool
482     {
483         return get_class($adapter) === SQLiteDB::class;
484     }
485 
486 }
487