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
14namespace ComboStrap;
15
16
17use dokuwiki\plugin\sqlite\SQLiteDB;
18use helper_plugin_sqlite;
19
20class 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