Db.inc

Go to the documentation of this file.

<?php // -*- coding: utf-8 -*-

/** \file Db.inc
 * (March 14, 2018)
 *
 * \brief
 * Class to connect and deal to the MySQL database.
 *
 * <img src="db_opiquotations_graph.png" width="682" height="771" border="0" alt="[DB opiquotations graph]" />
 *
 * Piece of OPiQuotations.
 * https://bitbucket.org/OPiMedia/opiquotations
 *
 * GPLv3 --- Copyright (C) 2014, 2015, 2016, 2017, 2018 Olivier Pirson
 * http://www.opimedia.be/
 *
 * @package OPiQuotations
 */
namespace OPiQuotations;

require_once 'OPiQuotations/log.inc';
require_once 'OPiQuotations/OPiQuotation.inc';


/**
 * Class to connect and deal to the MySQL database.
 */
class Db {
    /**
     * \brief
     * Open the connection to the database $db_name.
     *
     * If connection fails
     * then write a message in the errors log file.
     *
     * See is_connected() function.
     *
     * @param string $host Host name or IP address of the server
     * @param string $user MySQL user name
     * @param string $password MySQL password
     * @param string $db_name Name of the database
     */
    public function __construct($host, $user, $password, $db_name) {
        #DEBUG
        assert('is_string($host)');
        assert('is_string($user)');
        assert('is_string($password)');
        assert('is_string($db_name)');
        #DEBUG_END

        \mysqli_report(MYSQLI_REPORT_OFF);
        #DEBUG
        assert(\mysqli_report(MYSQLI_REPORT_ALL^MYSQLI_REPORT_STRICT^MYSQLI_REPORT_INDEX) === true);
        #DEBUG_END

        $this->connect = new \mysqli($host, $user, $password, $db_name);

        if ($this->connect->connect_error) {
            to_log('mysqli connect error '.\mysqli_connect_errno().' : '.\mysqli_connect_error().'
'.@print_r($this->connect, true));
            $this->connect = null;
        } elseif (!$this->connect->set_charset('utf8')) {
            to_log('mysqli::set_charset(\'utf8\') failed!
Server:'.$this->connect->server_info.'
Client:'.$this->connect->client_info.'
'.@print_r($this->connect, true));
            $this->connect->close();
            $this->connect = null;
        }
    }


    /**
     * \brief
     * Close the connection to the database.
     */
    public function __destruct() {
        if (!empty($this->connect)) {
            $this->connect->close();
        }
    }



    /**
     * \brief
     * If connection is ok
     * then return true
     * else return false.
     *
     * @return bool
     */
    public function is_connected() {
        return !empty($this->connect);
    }


    /**
     * \brief
     * Return the string with its special characters escaped
     * for use in a SQL statement.
     *
     * @param string $s
     *
     * @return string
     */
    public function escape($s) {
        #DEBUG
        assert('is_string($s)');
        assert('$this->is_connected()');
        #DEBUG_END

        return $this->connect->real_escape_string($s);
    }


    /**
     * \brief
     * Return a associative table id => array(name, number of use)
     * of elements of the table.
     *
     * @param string $table (must be 'author', 'nation', 'subject' or 'work')
     *
     * @return array[array]
     */
    public function list_to_assoc($table) {
        #DEBUG
        assert('is_string($table)');
        assert('in_array($table, array(\'author\', \'nation\', \'subject\' or \'work\'))');
        #DEBUG_END

        $quots = array();

        if (!$this->is_connected()) {
            to_log('Db.list_to_assoc(\''.print_r($table, true).'\') impossible because is NOT connected!');

            return $quots;
        }

        $id_names = array('author' => 'nation_author_id',
                          'nation' => 'nation_author_id');
        $id_name = (isset($id_names[$table])
                    ? $id_names[$table]
                    : $table.'_id');
        $is_maxim = ($table === 'nation'
                     ? ' AND `q`.`is_maxim`=1'
                     : ($table === 'author' || $table === 'work'
                        ? ' AND `q`.`is_maxim`=0'
                        : ''));

        $query = 'SELECT `t`.`id`, `t`.`name`, (SELECT COUNT(*) FROM `quotation` AS `q` WHERE `t`.`id`=`q`.`'.$id_name.'`'.$is_maxim.') AS `nb`
FROM `'.$table.'` AS `t`
ORDER BY `name` COLLATE utf8_unicode_ci;';

        $result = $this->connect->query($query);
        if ($result !== false) {
            while ($row = $result->fetch_assoc()) {
                settype($row['id'], 'int');
                settype($row['nb'], 'int');
                if ($row['nb'] > 0) {
                    $quots[$row['id']] = array($row['name'], $row['nb']);
                }
            }

            $result->free_result();
        }
        else {
            to_log('MySQL Db.list_to_assoc
'.$query.'
failed!
Server:'.$this->connect->server_info.'
Client:'.$this->connect->client_info.'
'.@print_r($this->connect, true));
        }

        return $quots;
    }


    /**
     * \brief
     * Return the numbers of quotations and/or maxims.
     *
     * If $is_maxim === null then return the numbers of quotations/maxims,\n
     * if $is_maxim === false then return the numbers of quotations,\n
     * if $is_maxim === true then return the numbers of maxims.
     *
     * @param null|bool $is_maxim
     *
     * @return int >= 0
     */
    public function nb($is_maxim=null) {
        #DEBUG
        assert('($is_maxim === null) || is_bool($is_maxim)');
        assert('$this->is_connected()');
        #DEBUG_END

        if (!$this->is_connected()) {
            to_log('Db.nb(\''.print_r($is_maxim, true).'\') impossible because is NOT connected!');

            return 0;
        }

        $query = 'SELECT COUNT(*) AS `nb`
FROM `quotation` AS `q`';
        if ($is_maxim !== null) {
            $query .= '
WHERE `q`.`is_maxim`='.($is_maxim
                        ? '1'
                        : '0');
        }
        $query .= ';';

        $result = $this->connect->query($query);
        if ($result !== false) {
            $row = $result->fetch_assoc();

            return (int)$row['nb'];
        }
        else {
            to_log('MySQL query
'.$query.'
failed!
Server:'.$this->connect->server_info.'
Client:'.$this->connect->client_info.'
'.@print_r($this->connect, true));

            return 0;
        }
    }


    /**
     * \brief
     * Execute the MySQL query INSERT.
     *
     * If insertion is ok
     * then return true,
     * else return false.
     *
     * @param string $query Valid MySQL query
     *
     * @return bool
     */
    public function query_insert($query) {
        #DEBUG
        assert('is_string($query)');
        assert('$this->is_connected()');
        #DEBUG_END

        if (!$this->is_connected()) {
            to_log('Db.query_insert(\''.print_r($query, true).'\') impossible because is NOT connected!');

            return $quots;
        }

        $r = $this->connect->query($query);

        if (!$r) {
            to_log('MySQL Db.query_insert
'.$query.'
failed!
Server:'.$this->connect->server_info.'
Client:'.$this->connect->client_info.'
'.@print_r($this->connect, true));
        }

        return $r;
    }


    /**
     * \brief
     * Return an array with all OPiQuotation
     * that match with the SQL $where condition
     * in order specified by $order.
     *
     * If $limit is not null
     * then return only $limit OPiQuotation.
     *
     * If $limit and $offset are not null
     * then return only $limit OPiQuotation from $offset.
     *
     * @warning Use escape() with each string piece of $where if necessary.
     *
     * @param string $where Valid WHERE clause of the SELECT MySQL command used
     *        (see http://dev.mysql.com/doc/refman/5.1/en/select.html )
     * @param string $order Valid ORDER clause of the SELECT MySQL command used
     *        (see http://dev.mysql.com/doc/refman/5.1/en/select.html )
     * @param null|int $limit (must be >= 0)
     * @param null|int $offset (must be >= 0)
     *
     * @return OPiQuotation[]
     */
    public function query_quotations($where='', $order='', $limit=null, $offset=null) {
        #DEBUG
        assert('is_string($where)');
        assert('is_string($order)');
        assert('($limit === null) || (is_int($limit) && ($limit >= 0))');
        assert('($offset === null) || (is_int($offset) && ($offset >= 0))');
        assert('$this->is_connected()');
        #DEBUG_END

        $quots = array();

        if (!$this->is_connected()) {
            to_log('Db.query_quotations(\''.print_r($where, true).'\', \''.print_r($order, true).'\') impossible because is NOT connected!');

            return $quots;
        }


        // Get quotations
        $query = array('SELECT `id`, `text`, `text_lang`, `translation`, `is_maxim`, `is_marked`, `subject`, `nation`, `author`, `work`,',
                       '       `selection_label`, `is_misattributed`',
                       'FROM `vw_quotation_selection`');

        $where = (string)$where;
        if ($where !== '') {
            $query[] = $where;
        }

        unset($where);

        $query[] = 'GROUP BY `id`';

        $order = (string)$order;
        if ($order !== '') {
            $query[] = $order;
        }

        unset($order);

        if ($limit !== null) {
            $limit = max(0, (int)$limit);

            if ($offset === null) {
              $query[] = 'LIMIT '.$limit;
            }
            else {
              $offset = max(0, (int)$offset);
              $query[] = 'LIMIT '.$limit.' OFFSET '.$offset;
            }
        }

        unset($limit);
        unset($offset);

        $query = implode('
', $query).';';

        $result = $this->connect->query($query);
        if ($result !== false) {
            // Fill associative table
            while ($row = $result->fetch_assoc()) {
                settype($row['id'], 'int');
                settype($row['is_maxim'], 'bool');
                settype($row['is_marked'], 'bool');
                settype($row['is_misattributed'], 'bool');

                $id = $row['id'];

                #DEBUG
                assert(!isset($quots[$id]));
                #DEBUG_END

                $quots[$id] = new OPiQuotation($id, $row['text'],
                                               $row['is_maxim'],
                                               $row['is_marked'],
                                               $row['translation'],
                                               $row['subject'],
                                               ($row['is_maxim']
                                                ? $row['nation']
                                                : $row['author']),
                                               $row['work'],
                                               $row['text_lang'],
                                               null,
                                               $row['is_misattributed']);
            }

            $result->free_result();

            unset($id);
            unset($row);
        }
        else {
            to_log('MySQL Db.query_quotations: get quotations
'.$query.'
failed!
Server:'.$this->connect->server_info.'
Client:'.$this->connect->client_info.'
'.@print_r($this->connect, true));
        }

        unset($query);
        unset($result);


        if (!empty($quots)) {
            // Get selections for all these quotations
            $query = 'SELECT `id`, `selection_label`, `selection_datetime_utc`, `selection_url`
FROM `vw_quotation_selection`
WHERE `selection_label` IS NOT NULL AND `id` IN ('.implode(',', array_keys($quots)).')
ORDER BY `selection_datetime_utc`';

            $result = $this->connect->query($query);
            if ($result !== false) {
                while ($row = $result->fetch_assoc()) {
                    settype($row['id'], 'int');

                    $selection = new Selection($row['selection_label'],
                                               ($row['selection_datetime_utc'] === null
                                                ? null
                                                : new \DateTime($row['selection_datetime_utc'])),
                                               $row['selection_url']);
                    $quots[$row['id']]->selections_add($selection);
                }

                $result->free_result();

                unset($selection);
                unset($row);
            }
            else {
                to_log('MySQL Db.query_quotations: get selections
'.$query.'
failed!
Server:'.$this->connect->server_info.'
Client:'.$this->connect->client_info.'
'.@print_r($this->connect, true));
            }

            unset($query);
            unset($result);
        }


        // Convert associative table $quots to an array
        $array = array();

        foreach ($quots as $id => $quot) {
            #DEBUG
            assert($id === $quot->id());
            #DEBUG_END

            $array[] = $quot;
        }

        unset($id);
        unset($quot);

        return $array;
    }


    /**
     * \brief
     * Return the number of all OPiQuotation
     * that match with the SQL $where condition.
     *
     * @warning Use escape() with each string piece of $where if necessary.
     *
     * @param string $where Valid WHERE clause of the SELECT MySQL command used
     *        (see http://dev.mysql.com/doc/refman/5.1/en/select.html )
     *
     * @return int
     */
    public function query_quotations_nb($where='') {
        #DEBUG
        assert('is_string($where)');
        #DEBUG_END

        if (!$this->is_connected()) {
            to_log('Db.query_quotations(\''.print_r($where, true).'\', \''.print_r($order, true).'\') impossible because is NOT connected!');

            return 0;
        }


        // Get quotations
        $query = array('SELECT DISTINCT `id`',
                       'FROM `vw_quotation_selection`');

        $where = (string)$where;
        if ($where !== '') {
            $query[] = $where;
        }

        unset($where);

        $query = implode('
', $query).';';

        $nb = 0;

        $result = $this->connect->query($query);
        if ($result !== false) {
            $nb = $result->num_rows;
        }
        else {
            to_log('MySQL Db.query_quotations: get quotations numbers
'.$query.'
failed!
Server:'.$this->connect->server_info.'
Client:'.$this->connect->client_info.'
'.@print_r($this->connect, true));
        }

        unset($query);
        unset($result);

        return $nb;
    }


    /**
     * \brief
     * Return escaped and quoted $x (converted to string).
     *
     * @param null|mixed $x
     *
     * @return string
     */
    public function to_string($x) {
        return '\''.$this->escape((string)$x).'\'';
    }


    /**
     * \brief
     * If $x === null
     * then return 'NULL'
     * else return to_string($x).
     *
     * @param null|mixed $x
     *
     * @return string
     */
    public function to_string_or_NULL($x) {
        return ($x === null
                ? 'NULL'
                : $this->to_string(''.$x));
    }



    /** @var \mysqli $connect
     * \brief
     * Connection to the MySQL database.
     */
    protected $connect;
}


return true;

?>