Artifact 7f184d61e2b415b23c63a748e5586b582ef4fb8b:


<?php

namespace Garradin;

class DB
{
    static protected $_instance = null;

    /**
     * Instance SQLite3
     * @var SQLite3
     */
    protected $db = null;

    /**
     * Options d'initialisation de SQLite3
     * @var null
     */
    protected $flags = null;

    /**
     * Transaction en cours?
     * @var integer
     */
    protected $transaction = 0;

    /**
     * Modes de retour des résultats
     */
    const NUM = \SQLITE3_NUM;
    const ASSOC = \SQLITE3_ASSOC;
    const BOTH = \SQLITE3_BOTH;
    const OBJ = 4; // SQLITE3_ASSOC, NUM and BOTH are 1, 2 and 3, so let's start at 4

    /**
     * Format de date utilisé pour le stockage
     */
    const DATE_FORMAT = 'Y-m-d H:i:s';

    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)
    {
        $this->flags = \SQLITE3_OPEN_READWRITE;

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

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

    public function connect()
    {
        if ($this->db)
        {
            return true;
        }

        $this->db = new \SQLite3(DB_FILE, $this->flags);

        $this->db->enableExceptions(true);

        // Le timeout par défaut est 0, on le met à 1 seconde, si ça ne suffit pas on augmentera plus tard
        $this->db->busyTimeout(1000);

        // Activer les contraintes des foreign keys
        $this->db->exec('PRAGMA foreign_keys = ON;');

        $this->db->createFunction('transliterate_to_ascii', ['Garradin\Utils', 'transliterateToAscii']);
        $this->db->createFunction('base64', 'base64_encode');
        $this->db->createFunction('rank', ['\KD2\DB', 'sqlite_rank']);
        $this->db->createFunction('haversine_distance', ['\KD2\DB', 'sqlite_haversine']);
    }

    public function escape($str)
    {
        // escapeString n'est pas binary safe: https://bugs.php.net/bug.php?id=62361
        $str = str_replace("\0", "\\0", $str);

        $this->connect();
        return $this->db->escapeString($str);
    }

    public function quote($str)
    {
        return '\'' . $this->escape($str) . '\'';
    }

    public function begin()
    {
        if (!$this->transaction)
        {
            $this->connect();
            $this->db->exec('BEGIN;');
        }

        $this->transaction++;

        return $this->transaction == 1 ? true : false;
    }

    public function commit()
    {
        if ($this->transaction == 1)
        {
            $this->connect();
            $this->db->exec('END;');
        }

        if ($this->transaction > 0)
        {
            $this->transaction--;
        }

        return $this->transaction ? false : true;
    }

    public function rollback()
    {
        $this->connect();
        $this->db->exec('ROLLBACK;');
        $this->transaction = 0;
        return true;
    }

    protected function getArgType(&$arg, $name = '')
    {
        switch (gettype($arg))
        {
            case 'double':
                return \SQLITE3_FLOAT;
            case 'integer':
            case 'boolean':
                return \SQLITE3_INTEGER;
            case 'NULL':
                return \SQLITE3_NULL;
            case 'string':
                return \SQLITE3_TEXT;
            case 'array':
                if (count($arg) == 2 
                    && in_array($arg[0], [\SQLITE3_FLOAT, \SQLITE3_INTEGER, \SQLITE3_NULL, \SQLITE3_TEXT, \SQLITE3_BLOB]))
                {
                    $type = $arg[0];
                    $arg = $arg[1];

                    return $type;
                }
            case 'object':
                if ($arg instanceof \DateTime)
                {
                    $arg = $arg->format(self::DATE_FORMAT);
                    return \SQLITE3_TEXT;
                }
            default:
                throw new \InvalidArgumentException('Argument '.$name.' is of invalid type '.gettype($arg));
        }
    }

    public function query($query, Array $args = [])
    {
        $this->connect();
        $statement = $this->db->prepare($query);
        $nb = $statement->paramCount();

        if (!empty($args))
        {
            if (is_array($args) && count($args) == 1 && is_array(current($args)))
            {
                $args = current($args);
            }
            
            if (count($args) != $nb)
            {
                throw new \LengthException('Arguments error: '.count($args).' supplied, but '.$nb.' are required by query.');
            }

            reset($args);

            if (is_int(key($args)))
            {
                foreach ($args as $i=>$arg)
                {
                    if (is_string($i))
                    {
                        throw new \InvalidArgumentException(sprintf('%s requires argument to be a keyed array, but key %s is a string.', __FUNCTION__, $i));
                    }

                    $type = $this->getArgType($arg, $i+1);
                    $statement->bindValue((int)$i+1, $arg, $type);
                }
            }
            else
            {
                foreach ($args as $key=>$value)
                {
                    if (is_int($key))
                    {
                        throw new \InvalidArgumentException(sprintf('%s requires argument to be a named-associative array, but key %s is an integer.', __FUNCTION__, $key));
                    }

                    $type = $this->getArgType($value, $key);
                    $statement->bindValue(':'.$key, $value, $type);
                }
            }
        }

        try {
            // Return a boolean for write queries to avoid accidental duplicate execution
            // see https://bugs.php.net/bug.php?id=64531
            
            $result = $statement->execute();
            return $statement->readOnly() ? $result : (bool) $result;
        }
        catch (\Exception $e)
        {
            throw new \RuntimeException($e->getMessage() . "\n" . $query . "\n" . json_encode($args, true));
        }
    }

    public function get($query)
    {
        $args = array_slice(func_get_args(), 1);
        return $this->fetch($this->query($query, $args), self::OBJ);
    }

    public function getAssoc($query)
    {
        $args = array_slice(func_get_args(), 1);
        return $this->fetchAssoc($this->query($query, $args));
    }

    public function getAssocKey($query)
    {
        $args = array_slice(func_get_args(), 1);
        return $this->fetchAssocKey($this->query($query, $args), self::OBJ);
    }

    public function insert($table, Array $fields)
    {
        $fields_names = array_keys($fields);
        $query = sprintf('INSERT INTO %s (%s) VALUES (:%s);', $table, 
            implode(', ', $fields_names), implode(', :', $fields_names));

        return $this->query($query, $fields);
    }

    public function update($table, Array $fields, $where)
    {
        // No fields to update? no need to do a query
        if (empty($fields))
        {
            return false;
        }

        $args = array_slice(func_get_args(), 3);
        $column_updates = [];
        
        foreach ($fields as $key=>$value)
        {
            // Append to arguments
            $args['field_' . $key] = $value;

            $column_updates[] = sprintf('%s = :field_%s', $key, $key);
        }

        $column_updates = implode(', ', $column_updates);
        $query = sprintf('UPDATE %s SET %s WHERE %s;', $table, $column_updates, $where);

        return $this->query($query, $args);
    }

    public function delete($table, $where)
    {
        $query = sprintf('DELETE FROM %s WHERE %s;', $table, $where);
        return $this->query($query, array_slice(func_get_args(), 2));
    }

    public function exec($query)
    {
        return $this->query($query, array_slice(func_get_args(), 1));
    }

    public function first($query)
    {
        $res = $this->query($query, array_slice(func_get_args(), 1));

        $row = $res->fetchArray(SQLITE3_ASSOC);
        $res->finalize();

        return is_array($row) ? (object) $row : false;
    }

    public function firstColumn($query)
    {
        $res = $this->query($query, array_slice(func_get_args(), 1));

        $row = $res->fetchArray(\SQLITE3_NUM);

        return count($row) > 0 ? $row[0] : false;
    }

    public function fetch(\SQLite3Result $result, $mode = null)
    {
        $as_obj = false;

        if ($mode === self::OBJ)
        {
            $as_obj = true;
            $mode = self::ASSOC;
        }

        $out = [];

        while ($row = $result->fetchArray($mode))
        {
            // FIXME: use generator (PHP 5.6+)
            $out[] = $as_obj ? (object) $row : $row;
        }

        $result->finalize();
        unset($result, $row);

        return $out;
    }

    protected function fetchAssoc(\SQLite3Result $result)
    {
        $out = [];

        while ($row = $result->fetchArray(\SQLITE3_NUM))
        {
            // FIXME: use generator
            $out[$row[0]] = $row[1];
        }

        $result->finalize();
        unset($result, $row);

        return $out;
    }

    protected function fetchAssocKey(\SQLite3Result $result, $mode = null)
    {
        $as_obj = false;

        if ($mode === self::OBJ)
        {
            $as_obj = true;
            $mode = self::ASSOC;
        }

        $out = [];

        while ($row = $result->fetchArray($mode))
        {
            // FIXME: use generator (PHP 5.6+)
            $key = current($row);
            $out[$key] = $as_obj ? (object) $row : $row;
        }

        $result->finalize();
        unset($result, $row);

        return $out;
    }

    public function countRows(\SQLite3Result $result)
    {
        $i = 0;

        while ($result->fetchArray(\SQLITE3_NUM))
        {
            $i++;
        }

        $result->reset();

        return $i;
    }

    public function prepare($query)
    {
        return $this->db->prepare($query);
    }

    /**
     * @deprecated
     */
    public function simpleInsert($table, Array $fields)
    {
        return $this->insert($table, $fields);
    }

    /**
     * @deprecated
     */
    public function simpleUpdate($table, Array $fields, $where)
    {
        return $this->update($table, $fields, $where);
    }

    /**
     * @deprecated
     */
    public function simpleExec($query)
    {
        return $this->simpleStatement($query, array_slice(func_get_args(), 1));
    }

    /**
     * @deprecated
     */
    public function escapeString($str)
    {
        return $this->escape($str);
    }

    /**
     * @deprecated
     */
    public function simpleStatement($query, Array $args = [])
    {
        return $this->statement($query, $args);
    }

    /**
     * @deprecated
     */
    public function simpleStatementFetch($query, $mode = null)
    {
        $args = array_slice(func_get_args(), 2);
        return $this->fetch($this->query($query, $args), $mode);
    }

    /**
     * @deprecated
     */
    public function simpleStatementFetchAssoc($query)
    {
        $args = array_slice(func_get_args(), 1);
        return $this->fetchAssoc($this->query($query, $args));
    }

    /**
     * @deprecated
     */
    public function simpleStatementFetchAssocKey($query, $mode = null)
    {
        $args = array_slice(func_get_args(), 2);
        return $this->fetchAssocKey($this->query($query, $args), $mode);
    }

    /**
     * @deprecated
     */
    public function queryFetch($query, $mode = null)
    {
        return $this->fetch($this->query($query), $mode);
    }

    /**
     * @deprecated
     */
    public function queryFetchAssoc($query)
    {
        return $this->fetchAssoc($this->query($query));
    }

    /**
     * @deprecated
     */
    public function queryFetchAssocKey($query, $mode = null)
    {
        return $this->fetchAssocKey($this->query($query), $mode);
    }

    /**
     * @deprecated
     */
    public function simpleQuerySingle($query, $all_columns = false)
    {
        $res = $this->query($query, array_slice(func_get_args(), 2));

        $row = $res->fetchArray($all_columns ? SQLITE3_ASSOC : SQLITE3_NUM);
        $res->finalize();

        if (!$all_columns)
        {
            if (isset($row[0]))
            {
                return $row[0];
            }

            return false;
        }
        else
        {
            return $row;
        }
    }
}