Artifact dadf9752198db69cedb9aed01aa34a860eac5f5f:


<?php

namespace Garradin;

use KD2\DB_SQLite3;

class DB extends DB_SQLite3
{
    /**
     * Application ID pour SQLite
     * @link https://www.sqlite.org/pragma.html#pragma_application_id
     */
    const APPID = 0x5da2d811;

    static protected $_instance = null;

    static public function getInstance($create = false)
    {
        return self::$_instance ?: self::$_instance = new DB($create);
    }

    private function __clone()
    {
        // Désactiver le clonage, car on ne veut qu'une seule instance
    }

    public function __construct($create = false)
    {
        if (!defined('\SQLITE3_OPEN_READWRITE'))
        {
            throw new \Exception('Module SQLite3 de PHP non présent. Merci de l\'installer.');
        }

        $flags = \SQLITE3_OPEN_READWRITE;

        if ($create)
        {
            $flags |= \SQLITE3_OPEN_CREATE;
        }

        parent::__construct(DB_FILE, $flags);

        // Ne pas se connecter ici, on ne se connectera que quand une requête sera faite
    }

    public function connect()
    {
        if (parent::connect())
        {
            // Activer les contraintes des foreign keys
            $this->exec('PRAGMA foreign_keys = ON;');

            // 10 secondes
            $this->db->busyTimeout(10 * 1000);
            $this->exec('PRAGMA journal_mode = TRUNCATE;');

            $this->db->createFunction('transliterate_to_ascii', ['Garradin\Utils', 'transliterateToAscii']);
        }
    }

    public function close()
    {
        parent::close();
        self::$_instance = null;
    }

    /**
     * Import a file containing SQL commands
     * Allows to use the statement ".read other_file.sql" to load other files
     * @param  string $file Path to file containing SQL commands
     * @return boolean
     */
    public function import($file)
    {
        $sql = file_get_contents($file);

        $dir = dirname($file);

        $sql = preg_replace_callback('/^\.read (.+\.sql)$/m', function ($match) use ($dir) {
            return file_get_contents($dir . DIRECTORY_SEPARATOR . $match[1]) . "\n";
        }, $sql);

        return $this->exec($sql);
    }

    public function deleteUndoTriggers()
    {
        $triggers = $this->getAssoc('SELECT name, name FROM sqlite_master
            WHERE type = \'trigger\' AND name LIKE \'!_%!_log!__t\' ESCAPE \'!\';');

        foreach ($triggers as $trigger)
        {
            $this->exec(sprintf('DROP TRIGGER %s;', $this->quoteIdentifier($trigger)));
        }
    }

    public function createUndoTriggers()
    {
        $this->exec('CREATE TABLE undolog (
            seq INTEGER PRIMARY KEY,
            sql TEXT NOT NULL,
            date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
            type TEXT NOT NULL,
            action TEXT NOT NULL
        );');

        // List all tables except SQLite tables
        $tables = $this->getAssoc('SELECT name, name FROM sqlite_master
            WHERE type = \'table\'
            AND name NOT LIKE \'sqlite!_%\' ESCAPE \'!\' AND name NOT LIKE \'wiki!_recherche%\' ESCAPE \'!\'
            AND name != \'undolog\';');


        $query = 'CREATE TRIGGER _%table_log_it AFTER INSERT ON %table BEGIN
                DELETE FROM undolog WHERE rowid IN (SELECT rowid FROM undolog LIMIT 500,1000);
                INSERT INTO undolog (type, action, sql) VALUES (\'%table\', \'I\', \'DELETE FROM %table WHERE rowid=\'||new.rowid);
            END;
            CREATE TRIGGER _%table_log_ut AFTER UPDATE ON %table BEGIN
                DELETE FROM undolog WHERE rowid IN (SELECT rowid FROM undolog LIMIT 500,1000);
                INSERT INTO undolog (type, action, sql) VALUES (\'%table\', \'U\',  \'UPDATE %table SET %columns_update WHERE rowid = \'||old.rowid);
            END;
            CREATE TRIGGER _%table_log_dt BEFORE DELETE ON %table BEGIN
                DELETE FROM undolog WHERE rowid IN (SELECT rowid FROM undolog LIMIT 500,1000);
                INSERT INTO undolog (type, action, sql) VALUES (\'%table\', \'D\', \'INSERT INTO %table (rowid, %columns_list) VALUES(\'||old.rowid||\', %columns_insert)\');
            END;';

        foreach ($tables as $table)
        {
            $columns = $this->getAssoc(sprintf('PRAGMA table_info(%s);', $this->quoteIdentifier($table)));
            $columns_insert = [];
            $columns_update = [];

            foreach ($columns as &$name)
            {
                $columns_update[] = sprintf('%s = \'||quote(old.%1$s)||\'', $name);
                $columns_insert[] = sprintf('\'||quote(old.%s)||\'', $name);
            }

            $sql = strtr($query, [
                '%table' => $table,
                '%columns_list' => implode(', ', $columns),
                '%columns_update' => implode(', ', $columns_update),
                '%columns_insert' => implode(', ', $columns_insert),
            ]);

            $this->exec($sql);
        }
    }
}