<?php

/**
 * Very stable interface for MySQL - object-oriented at last :)
 * Select builder is inspired by MediaWiki's one.
 * Also usable for querying SphinxQL.
 * Version: 2018-09-10
 * (c) Vitaliy Filippov, 2012-2018
 */

require_once __DIR__.'/MysqlQueryBuilder.php';

if (!defined('MS_HASH'))
{
    define('MS_HASH', 0);
    define('MS_LIST', 1);
    define('MS_ROW', 2);
    define('MS_ONE', 2);
    define('MS_COL', 4);
    define('MS_VALUE', 6);
    define('MS_RESULT', 8);
}

if (!class_exists('DatabaseException'))
{
    class DatabaseException extends Exception {}
}

class DatabaseMysqlException extends DatabaseException
{
    function isDuplicateKey()
    {
        return $this->getCode() == 1062;
    }
}

if (!interface_exists('Database'))
{
    interface Database {}
}

class DatabaseMysql extends MysqlQueryBuilder implements Database
{
    var $host, $port, $socket, $username, $password, $dbname, $isSphinx;

    var $tableNames = array();
    var $init = array();
    var $queryLogFile, $loggedQueries = '';
    var $reconnect = true;
    var $autoBegin;
    var $ondestroy = 'commit';

    var $queryCount = 0;
    var $link;
    var $transactions = array();

    /**
     * Creates a MySQL connection object.
     *
     * @param array $options Possible options:
     *     host         Host name or IP address to connect to   [localhost]
     *     socket       Path to UNIX socket to connect to       [/var/run/mysqld/mysqld.sock]
     *     port         TCP port to connect to                  [3306]
     *     dbname       DB name to use
     *     username     Username
     *     password     Password
     *     tableNames   Table name mappings (virtual => real)
     *     queryLogFile Path to query log file
     *     reconnect    Whether to reconnect on idle timeout    [true]
     *     autoBegin    Whether to automatically begin a transaction on first query [false]
     *     ondestroy    commit/rollback/none during destruction [commit]
     *     init         Initialisation queries (array)
     */
    function __construct($options)
    {
        $defOpts = array(
            'host' => 'localhost',
            'port' => 3306,
            'socket' => '/var/run/mysqld/mysqld.sock',
            'dbname' => '',
            'username' => '',
            'password' => '',
            'reconnect' => true,
            'tableNames' => array(),
            'queryLogFile' => '',
            'autoBegin' => false,
            'ondestroy' => 'commit',
            'init' => array(),
        );
        $options += $defOpts;
        if ($options['socket'])
        {
            $options['host'] = 'localhost';
        }
        foreach ($defOpts as $k => $v)
        {
            $this->$k = $options[$k];
        }
        $this->isSphinx = !$this->username && !$this->password && !$this->dbname;
        if (!$this->isSphinx)
        {
            // READ COMMITTED is more consistent for average usage
            $this->init[] = 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED';
        }
    }

    function __destruct()
    {
        $o = $this->ondestroy;
        if (($o === 'commit' || $o === 'rollback') && $this->transactions)
        {
            $this->transactions = array(false);
            $this->$o();
        }
        if ($this->queryLogFile)
        {
            file_put_contents($this->queryLogFile, $this->loggedQueries, FILE_APPEND);
        }
    }

    function connect()
    {
        if ($this->socket !== NULL)
            $this->link = new mysqli($this->host, $this->username, $this->password, $this->dbname, $this->port, $this->socket);
        elseif ($this->port !== NULL)
            $this->link = new mysqli($this->host, $this->username, $this->password, $this->dbname, $this->port);
        else
            $this->link = new mysqli($this->host, $this->username, $this->password, $this->dbname);
        $errno = $this->link->connect_errno;
        $error = $this->link->connect_error;
        if ($errno)
        {
            $this->link = NULL;
            throw new DatabaseMysqlException($error, $errno);
        }
        else
        {
            $this->transactions = array();
            $this->link->set_charset('utf8');
            foreach ($this->init as $q)
                $this->link->query($q);
        }
    }

    function getDBName()
    {
        return $this->dbname;
    }

    function quote($value)
    {
        if ($value === NULL)
            return "NULL";
        if (!$this->link)
            $this->connect();
        return "'" . $this->link->real_escape_string($value) . "'";
    }

    function query($sql, $streamResult = false)
    {
        if (!$this->link)
            $this->connect();
        if ($this->autoBegin && !$this->transactions)
            $this->begin();
        $this->queryCount++;
        if ($this->queryLogFile)
        {
            $begin = explode(' ', microtime(), 2);
        }
        $r = $this->link->query($sql, $streamResult ? MYSQLI_USE_RESULT : MYSQLI_STORE_RESULT);
        if (!$r)
            $r = $this->check_reconnect('query', [ $sql, $streamResult ]);
        if ($this->queryLogFile)
        {
            $end = explode(' ', microtime(), 2);
            $this->loggedQueries .= date("[Y-m-d H:i:s.").substr($end[0], 2, 6)."] [".
                sprintf("%.05fs", $end[1]-$begin[1]+$end[0]-$begin[0])."] $sql\n";
        }
        return $r;
    }

    protected function check_reconnect($f, $args)
    {
        $r = false;
        if ($this->link->errno == 2006 && $this->reconnect && (!$this->transactions || $args[0] == "BEGIN"))
        {
            // "MySQL server has gone away"
            $this->connect();
            $r = call_user_func_array([ $this->link, $f ], $args);
            if (!$r && $this->link->errno == 2006)
                $this->link = false;
            if (!$r)
                $st = " (reconnect failed)";
        }
        elseif ($this->link->errno != 2006)
            $st = "";
        elseif (!$this->reconnect)
            $st = " (reconnect disabled";
        elseif ($this->transactions)
            $st = " (not reconnecting because of active transactions)";
        if (!$r)
            throw new DatabaseMysqlException('#'.$this->link->errno.': '.$this->link->error . $st . "\nQuery: ".$args[0], $this->link->errno);
        return $r;
    }

    function multi_select(array $queries, $format = 0)
    {
        if (!$this->link)
            $this->connect();
        if ($this->autoBegin && !$this->transactions)
            $this->begin();
        $this->queryCount += count($queries);
        $log = '';
        foreach ($queries as &$sql)
        {
            if (!is_string($sql))
            {
                $sql = $this->select_builder($sql[0], $sql[1], $sql[2], @$sql[3]);
            }
            $log .= date("[Y-m-d H:i:s] ").$sql."\n";
        }
        unset($sql);
        if ($this->queryLogFile)
        {
            file_put_contents($this->queryLogFile, $log, FILE_APPEND);
        }
        $sql = implode('; ', $queries);
        $r = $this->link->multi_query($sql);
        if (!$r)
            $this->check_reconnect('multi_query', [ $sql ]);
        $results = array();
        $i = 0;
        foreach ($queries as $k => $q)
        {
            if ($i++)
            {
                $this->link->next_result();
            }
            $r = $this->link->store_result();
            $results[$k] = $this->fetch_all($r, $format);
        }
        return $results;
    }

    /**
     * Starts a transaction, supports nested calls and savepoints.
     * @param boolean $savepoint Creates savepoints only this parameter is true.
     */
    function begin($savepoint = false)
    {
        $n = count($this->transactions)+1;
        $this->transactions[] = $savepoint ? "sp$n" : false;
        if ($n == 1)
            return $this->query("BEGIN");
        elseif ($savepoint)
            return $this->query("SAVEPOINT sp$n");
        return true;
    }

    /**
     * Commits transaction or releases last savepoint.
     * If there is no last savepoint, just returns true.
     */
    function commit()
    {
        $r = true;
        $savepoint = array_pop($this->transactions);
        if (!$this->transactions)
            $r = $this->query("COMMIT");
        elseif ($savepoint)
            $r = $this->query("RELEASE SAVEPOINT $savepoint");
        return $r;
    }

    /**
     * Commits transaction
     */
    function commitAll()
    {
        $r = true;
        if ($this->transactions)
        {
            $r = $this->query("COMMIT");
            $this->transactions = [];
        }
        return $r;
    }

    /**
     * Rollbacks transaction
     */
    function rollbackAll()
    {
        $r = true;
        if ($this->transactions)
        {
            $r = $this->query("ROLLBACK");
            $this->transactions = [];
        }
        return $r;
    }

    /**
     * Rollbacks transaction or last savepoint.
     * If there is no savepoint, returns false.
     */
    function rollback()
    {
        $r = false;
        $savepoint = array_pop($this->transactions);
        if (!$this->transactions)
            $r = $this->query("ROLLBACK");
        elseif ($savepoint)
            $r = $this->query("ROLLBACK TO SAVEPOINT $savepoint");
        return $r;
    }

    function errno()
    {
        return $this->link->errno;
    }

    function error()
    {
        return $this->link->error;
    }

    /**
     * Run a SELECT query and return results.
     *
     * Usage: either
     * $this->select($tables, $fields, $where, $options, $format)
     *   using $this->select_builder() or
     * $this->select($sql_text, $format)
     *   using query text.
     *
     * @param int $format Return format, bitmask of MS_XX constants:
     *   MS_RESULT = return mysqli result object to manually fetch from
     *   MS_LIST   = return rows as indexed arrays
     *   MS_HASH   = return rows as associative arrays (default)
     *   MS_ROW    = only return the first row
     *   MS_COL    = only return the first column
     *   MS_VALUE  = only return the first cell (just 1 value)
     */
    function select($tables, $fields = '*', $where = 1, $options = NULL, $format = 0)
    {
        if (is_int($fields))
        {
            $sql = $tables;
            $format = $fields;
        }
        else
            $sql = $this->select_builder($tables, $fields, $where, $options);
        if ($format & MS_RESULT)
            return $this->query($sql, MYSQLI_USE_RESULT);
        $res = $this->query($sql);
        return $this->fetch_all($res, $format);
    }

    function fetch_all($res, $format = 0)
    {
        if ($format & MS_LIST)
            $r = $this->get_rows($res);
        else
            $r = $this->get_assocs($res);
        if (!$r)
            $r = array();
        if ($format & MS_ROW)
        {
            if (!count($r))
                return NULL;
            if ($format & MS_COL)
            {
                $r = $r[0];
                $k = array_keys($r);
                $r = $r[$k[0]];
            }
            else
                $r = $r[0];
        }
        elseif ($format & MS_COL)
        {
            $k = false;
            foreach ($r as $i => $v)
            {
                if (!$k)
                {
                    $k = array_keys($v);
                    $k = $k[0];
                }
                $r[$i] = $v[$k];
            }
        }
        return $r;
    }

    function found_rows()
    {
        return $this->select('SELECT FOUND_ROWS()', MS_VALUE);
    }

    /**
     * Delete a set of rows.
     * @param mixed $tables see $this->tables_builder()
     * @param mixed $where see $this->where_builder()
     * @param array $options Options for query:
     *   'LIMIT' => array($limit, $offset) or array($limit) or just $limit
     *   'OFFSET' => $offset, for the case when 'LIMIT' is just $limit
     */
    function delete($tables, $where, $options = NULL)
    {
        $this->query($this->delete_builder($tables, $where, $options));
        return $this->link->affected_rows;
    }

    /**
     * Insert a single row into $table and return inserted ID.
     * @param string $table Table name to insert row to.
     * @param array $rows Row to be inserted.
     * @return int $insert_id Autoincrement ID of inserted row (if appropriate).
     */
    function insert_row($table, $row)
    {
        $sql = $this->insert_builder($table, array($row));
        if ($this->query($sql))
            return $this->link->insert_id;
        return NULL;
    }

    function insert_id()
    {
        return $this->link->insert_id;
    }

    /**
     * Update row(s) in $table.
     * $this->update($table, $set, $where, $options);
     *
     * @param string $table Table name to update.
     * @param array $set Assoc array with values for update query.
     * @param array $where Conditions for update query, see $this->where_builder().
     * @param array $options Options for update query:
     *   'LIMIT' => array($limit, $offset) or array($limit) or just $limit
     *   'OFFSET' => $offset, for the case when 'LIMIT' is just $limit
     */
    function update($tables, $rows, $where = NULL, $options = NULL)
    {
        if (!$rows)
            return false;
        if ($this->query($this->update_builder($tables, $rows, $where, $options)))
            return $this->link->affected_rows;
        return false;
    }

    /**
     * INSERT / REPLACE / INSERT IGNORE / INSERT OR UPDATE
     */
    function insert($table, $rows, $onConflict = NULL, $uniqueKey = NULL)
    {
        if (!$rows || !is_array($rows))
            return false;
        if ($this->query($this->insert_builder($table, $rows, $onConflict, $uniqueKey)))
            return $this->link->affected_rows;
        return false;
    }

    function insert_ignore($table, $rows, $uniqueKey = NULL)
    {
        return $this->insert($table, $rows, 'IGNORE', $uniqueKey);
    }

    function upsert($table, $rows, $uniqueKey = NULL, $updateCols = NULL)
    {
        return $this->insert($table, $rows, 'UPDATE', $uniqueKey, $updateCols);
    }

    function replace($table, $rows, $uniqueKey = NULL)
    {
        return $this->insert($table, $rows, 'REPLACE', $uniqueKey);
    }

    protected function get_rows($res)
    {
        $r = array();
        if ($res)
        {
            while ($row = $res->fetch_row())
                $r[] = $row;
            $res->free();
        }
        return $r;
    }

    protected function get_assocs($res)
    {
        $r = array();
        if ($res)
        {
            while ($row = $res->fetch_assoc())
                $r[] = $row;
            $res->free();
        }
        return $r;
    }
}