<?php
/**
 * -------------------------------------------------------------------------
 * The basis for a system module that uses a database table.
 *
 * -------------------------------------------------------------------------
 *
 * Implemented properties below are:
 *     PROTECTED  $table
 *     PROTECTED  $tableFields
 *                      └──> id  INTEGER (auto incremented)
 *     PROTECTED  $tableKeys
 *                      └──> id  UNIQUE
 *     PROTECTED  $tableOptions
 *                      ├──> auto increment from 1
 *                      └──> default charset UTF-8
 *     PROTECTED  $demoRows
 *
 * Implemented methods below are:
 *     create
 *     add
 *     update
 *     save
 *     remove
 *     get
 *     select
 *     install
 *     PROTECTED  makeSelect
 *     PROTECTED  makeJoin
 *     PROTECTED  makeWhere
 *     PROTECTED  makeOrderBy
 *     PROTECTED  getColumns
 *     PROTECTED  siftRecord
 *     PROTECTED  renameField
 *     PROTECTED  filterField
 *     PROTECTED  createTable
 *     PROTECTED  deleteTable
 *     PROTECTED  clearTable
 *     PROTECTED  addRecord
 *     PROTECTED  updateRecord
 *     PROTECTED  deleteRecord
 *
 * -------------------------------------------------------------------------
 *
 * @package    MimimiFramework
 * @subpackage Core
 * @copyright  2022 MiMiMi Community
 *             https://mimimi.software/
 * @license    GPL-2.0
 *             https://opensource.org/license/gpl-2-0/
 * -------------------------------------------------------------------------
 */

mimimiInclude ( 'Module.php' );

class MimimiModuleWithTable extends MimimiModule {

    /**
     * ---------------------------------------------------------------------
     * Database table name.
     *
     * @var string
     * @access protected
     * ---------------------------------------------------------------------
     */

    protected $table = '';

    /**
     * ---------------------------------------------------------------------
     * Database table columns.
     *
     * @var array
     * @access protected
     * ---------------------------------------------------------------------
     */

    protected $tableFields = [
                  '`id`  BIGINT  NOT NULL  AUTO_INCREMENT  COMMENT "record identifier"'
              ];

    /**
     * ---------------------------------------------------------------------
     * Database table keys.
     *
     * @var array
     * @access protected
     * ---------------------------------------------------------------------
     */

    protected $tableKeys = [
                  'PRIMARY KEY (`id`)'
              ];

    /**
     * ---------------------------------------------------------------------
     * Database table attributes.
     *
     * @var array
     * @access protected
     * ---------------------------------------------------------------------
     */

    protected $tableOptions = [
                  'AUTO_INCREMENT = 1',
                  'DEFAULT CHARACTER SET = utf8'
              ];

    /**
     * ---------------------------------------------------------------------
     * List of rows to install if the database table is missing or empty.
     *
     * @var array
     * @access protected
     * ---------------------------------------------------------------------
     */

    protected $demoRows = [];

    /**
     * ---------------------------------------------------------------------
     * Creates the database table.
     *
     * @public
     * @return  bool  True if success,
     *                False if failure.
     * ---------------------------------------------------------------------
     */

    public function create () {
        return $this->createTable();
    }

    /**
     * ---------------------------------------------------------------------
     * Adds a new record.
     *
     * @public
     * @param   array     $item  The record (list of values indexed by column name).
     * @return  int|bool         The inserted row identifier if success,
     *                           False if failure.
     * ---------------------------------------------------------------------
     */

    public function add ( $item ) {
        $row = $this->siftRecord($item);
        return empty($row)
               ? false
               : $this->addRecord($row);
    }

    /**
     * ---------------------------------------------------------------------
     * Updates a record.
     *
     * @public
     * @param   int|array  $id    INTEGER: The record identifier to update if your table column has name "id".
     *                            ARRAY:   The full record identifier pair like this [ 'ID_column_name' => identifier ].
     * @param   array      $item  The record (list of values indexed by column name).
     * @return  int|bool          The number of rows affected if success,
     *                            False if failure.
     * ---------------------------------------------------------------------
     */

    public function update ( $id, $item ) {
        $row = $this->siftRecord($item);
        return empty($row)
               ? false
               : $this->updateRecord($id, $row);
    }

    /**
     * ---------------------------------------------------------------------
     * Saves (adds or updates) a record.
     *
     * @public
     * @param   array     $item      The record (list of values indexed by column name).
     * @param   string    $idColumn  (optional) The name of the column containing the record identifier.
     * @return  int|bool             The inserted/affected row identifier if success,
     *                               False if failure.
     * ---------------------------------------------------------------------
     */

    public function save ( $item, $idColumn = 'id' ) {
        $copy = $item;
        $id = 0;
        if ( isset($item[$idColumn]) ) {
            if ( $item[$idColumn] ) {
                $id = [
                    $idColumn => $item[$idColumn]
                ];
            }
            unset($item[$idColumn]);
        }
        return $id
               ? ( $this->update($id, $item)
                   ? $id[$idColumn]
                   : $this->add($copy) )
               : $this->add($item);
    }

    /**
     * ---------------------------------------------------------------------
     * Public method to delete a record.
     *
     * @public
     * @param   int|array  $ids  INTEGER:          The record identifier if your table column has name "id".
     *                           ARRAY OF INTEGER: The list of record identifiers if your table column has name "id".
     *                           ARRAY:            The record identifier pair like this [ 'ID_column_name' => identifier ].
     *                           ARRAY OF ARRAY:   The record identifier pair like this [ 'ID_column_name' => [list of identifiers] ].
     * @return  int|bool         The number of rows affected if success,
     *                           False if failure.
     * ---------------------------------------------------------------------
     */

    public function remove ( $ids ) {
        return $this->deleteRecord($ids);
    }

    /**
     * ---------------------------------------------------------------------
     * Retrieves a record (or list of records).
     *
     * Also note that we use the generic alias T1 below to refer to our
     * database table.
     *
     * @public
     * @param   array       $filter  The filter (list of values indexed by column name).
     *                               Elements under the optional "select" index are combined with a comma to form a SELECT clause.
     *                               Elements under the optional "join" index are combined to form JOIN clauses.
     *                               Elements under the optional "orderby" index are combined with a comma to form a ORDER BY clause.
     *                               Elements under the optional "groupby" index are combined with a comma to form a GROUP BY clause.
     *                               Elements under the optional "having" index are combined with a AND operator to form a HAVING clause (the element syntax is equivalent to the WHERE clause below).
     *                               The rest of the filter elements are combined with a AND operator to form a WHERE clause:
     *                                   If the element is preceded by !, a negative comparison is performed.
     *                                   If the element is preceded by <, a Less comparison is performed.
     *                                   If the element is preceded by <=, a Less-Or-Equal comparison is performed.
     *                                   If the element is preceded by >, a More comparison is performed.
     *                                   If the element is preceded by >=, a More-Or-Equal comparison is performed.
     *                                   If the element is preceded by ~, a LIKE comparison is performed.
     *                                   If the element is preceded by !~, a negative LIKE comparison is performed.
     *                                   If the element is preceded by /, a REGEXP comparison is performed.
     *                                   If the element is preceded by !/, a negative REGEXP comparison is performed.
     *                                   If the element is +, its value is an ending of this clause directly in MySQL syntax.
     * @param   int         $offset  Offset of selection frame.
     * @param   int         $limit   Number if get a list of record,
     *                               True if get one record.
     * @return  array|bool           The record (or list of records) if success,
     *                               False if that record not found.
     * ---------------------------------------------------------------------
     */

    public function get ( $filter, $offset = 0, $limit = true ) {
        if ($this->app->has->db) {
            $select = 'SELECT `t1`.* ';
            $join = '';
            $where = '';
            $groupby = '';
            $having = '';
            $orderby = '';
            $data = [];
            if (is_array($filter)) {
                $dataWhere  = [];
                $dataHaving = [];
                /* Let's generate clauses. */
                if (isset($filter['select'])) {
                    $select = $this->makeSelect($filter['select']);
                    unset($filter['select']);
                }
                if (isset($filter['join'])) {
                    $join = $this->makeJoin($filter['join'], $data);
                    unset($filter['join']);
                }
                if (isset($filter['groupby'])) {
                    $groupby = $this->makeOrderBy($filter['groupby'], true);
                    unset($filter['groupby']);
                }
                if (isset($filter['orderby'])) {
                    $orderby = $this->makeOrderBy($filter['orderby']);
                    unset($filter['orderby']);
                }
                if (isset($filter['having'])) {
                    $groupby = $this->makeWhere($filter['having'], $dataHaving, true);
                    unset($filter['having']);
                }
                $where = $this->makeWhere($filter, $dataWhere);
                if ($dataWhere)  $data = array_merge($data, $dataWhere);
                if ($dataHaving) $data = array_merge($data, $dataHaving);
            }
            /* Let's make a query. */
            $query = $select .
                     'FROM `__' . $this->table . '` AS `t1` ' .
                     $join .
                     $where .
                     $groupby .
                     $having .
                     $orderby .
                     'LIMIT ' . ( $limit  > 0 ? intval($limit) : 1 ) .
                                ( $offset > 0 ? ' OFFSET ' . $offset : '' );
            $query = $this->app->db->query($query, $data);
            /* Let's return the record[s]. */
            if ($query) {
                if (is_numeric($limit)) {
                    $result = [];
                    do {
                        $data = $query->fetch(PDO::FETCH_ASSOC);
                        if ($data) {
                            $result[] = $data;
                        }
                    } while ($data);
                } else {
                    $result = $query->fetch(PDO::FETCH_ASSOC);
                }
                $query->closeCursor();
                return empty($result)
                       ? false
                       : $result;
            }
        }
        return false;
    }

    /**
     * ---------------------------------------------------------------------
     * Fetches a record (or list of records).
     *
     * This method is similar to the "get" method described above. But there
     * is a difference. It tries to install the demo rows on a failed fetch
     * and then retry fetching.
     *
     * @public
     * @param   array       $filter
     * @param   int         $offset
     * @param   int         $limit
     * @return  array|bool
     * ---------------------------------------------------------------------
     */

    public function select ( $filter, $offset = 0, $limit = true ) {
        $result = false;
        if ($this->app->has->db) {
            $result = $this->get($filter, $offset, $limit);
            if ($result === false) {
                if ($this->install($filter)) {
                    $result = $this->get($filter, $offset, $limit);
                }
            }
        }
        return $result;
    }

    /**
     * ---------------------------------------------------------------------
     * Installs the demo table rows.
     *
     * This method is called automatically from the "select" method
     * described above if no result is found when fetching rows.
     *
     * @public
     * @param   mixed  $params  Some parameters if you need.
     * @return  bool            True if at least one new row has been added,
     *                          False if the table has not changed.
     * ---------------------------------------------------------------------
     */

    public function install ( $params = null ) {
        $result = false;
        if ($this->app->has->db) {
            if ($this->createTable()) {
                if (! empty($this->demoRows)) {
                    foreach ($this->demoRows as $index => $row) {
                        $result = $this->save($row)
                                  || $result;
                        unset($this->demoRows[$index]);
                    }
                }
            }
        }
        return $result;
    }

    /**
     * ---------------------------------------------------------------------
     * Builds a SELECT clause of the query.
     *
     * @protected
     * @param   array   $columns  The list of selected columns (they are
     *                            indexed by column name). For example:
     *                            [
     *                                'DISTINCT'            => true,
     *                                'SQL_CALC_FOUND_ROWS' => true,
     *                                't1.*'                => true,
     *                                't3.name'             => true,
     *                                't2.id'               => 'user_id',
     *                                'MAX( `t1`.`sum` )'   => 'total'
     *                            ]
     * @return  string            Generated clause.
     * ---------------------------------------------------------------------
     */

    protected function makeSelect ( $columns ) {
        $clause = '';
        if (is_array($columns)) {
            if (! empty($columns)) {
                $afterModifier = false;
                $name = '[a-z][a-z0-9_]*';
                foreach ($columns as $key => $data) {
                    $key = preg_replace('~^(' . $name . ')\.(' . $name . ')$~ui', '`$1`.`$2`', $key);
                    $key = preg_replace('~^(' . $name . ')\.\*$~ui', '`$1`.*', $key);
                    $key = preg_replace('~^(' . $name . ')$~u', '`$1`', $key);
                    $isModifier = preg_match('~^[A-Z][A-Z0-9_]*$~u', $key);
                    if ($clause) $clause .= $isModifier || $afterModifier
                                            ? ' '
                                            : ', ';
                    $clause .= $key . ( is_string($data)
                                        ? ' AS `' . $data . '`'
                                        : '' );
                    $afterModifier = $isModifier;
                }
                if ($clause) $clause = 'SELECT ' . $clause . ' ';
            }
        }
        return $clause;
    }

    /**
     * ---------------------------------------------------------------------
     * Builds JOIN clauses of the query.
     *
     * @protected
     * @param   array   $columns  The list of selected columns (they are
     *                            indexed by column name). For example:
     *                            [
     *                                'categories' => [
     *                                    't2.id' => 't1.category_id'
     *                                ],
     *                                'products' => [
     *                                    't3.id'      => 't1.category_id',
     *                                    '! t3.id'    => 't1.parent_id',
     *                                    '> t3.date'  => 't1.announced',
     *                                    '< t3.date'  => 't1.modified',
     *                                    '>= t3.cost' => 't1.min_price',
     *                                    '<= t3.cost' => 't1.max_price'
     *                                ],
     *                                'vendors' => [
     *                                    't4.name'            => 'Example',
     *                                    't4.visible'         => TRUE,
     *                                    '! t4.region'        => 'North',
     *                                    '> t4.date'          => '2022-01-25 18:55:32',
     *                                    '< t4.modified'      => '2022-02-01 10:00:00',
     *                                    '>= t4.browsed'      => 100,
     *                                    '<= t4.browsed'      => 1000,
     *                                    't2.id_in_SET'       => [35, 36, 45],
     *                                    '! t3.id_not_in_SET' => [35, 36, 45]
     *                                ],
     *                            ]
     * @param   array   $data     Reference to the list of column values.
     * @return  string            Generated clause.
     * ---------------------------------------------------------------------
     */

    protected function makeJoin ( $columns, & $data ) {
        $clause = '';
        $data = [];
        if (is_array($columns)) {
            if (! empty($columns)) {
                $num = 2;
                foreach ($columns as $key => $join) {
                    $clause .= 'LEFT JOIN `__' . $key . '` AS `t' . $num . '` ';
                    if (is_array($join)) {
                        if (! empty($join)) {
                            $glue = '';
                            $clause .= 'ON ';
                            foreach ($join as $key => $value) {
                                $key = preg_replace('~\.~u', '`.`', $key);
                                if (is_array($value)) {
                                    $markers = [];
                                    foreach ($value as $v) {
                                        $data[] = $v;
                                        $markers[] = '?';
                                    }
                                    $clause .= $glue . '`' . $key . '` IN( ' . implode(', ', $markers) . ' ) ';
                                } else if (is_string($value)
                                       &&  preg_match('~^t\d+\.[a-z][a-z0-9_]*$~ui', $value)) {
                                    $value = preg_replace('~\.~u', '`.`', $value);
                                    $clause .= $glue . '`' . $key . '` = `' . $value . '` ';
                                } else {
                                    $data[] = $value;
                                    $clause .= $glue . '`' . $key . '` = ? ';
                                }
                                $glue = 'AND ';
                            }
                        }
                    }
                    $num++;
                }
                $clause = preg_replace('~ `<=\s*(t\d+`\.[^\s]+) = (`|\?)~ui', ' `$1 <= $2', $clause);
                $clause = preg_replace('~ `>=\s*(t\d+`\.[^\s]+) = (`|\?)~ui', ' `$1 >= $2', $clause);
                $clause = preg_replace('~ `!\s*(t\d+`\.[^\s]+) = (`|\?)~ui',  ' `$1 != $2', $clause);
                $clause = preg_replace('~ `!\s*(t\d+`\.[^\s]+) IN\(~ui',  ' `$1 NOT IN(', $clause);
                $clause = preg_replace('~ `<\s*(t\d+`\.[^\s]+) = (`|\?)~ui',  ' `$1 < $2', $clause);
                $clause = preg_replace('~ `>\s*(t\d+`\.[^\s]+) = (`|\?)~ui',  ' `$1 > $2', $clause);
            }
        }
        return $clause;
    }

    /**
     * ---------------------------------------------------------------------
     * Builds a WHERE/HAVING clause of the query.
     *
     * @protected
     * @param   array   $columns  The list of filtered columns (they are
     *                            indexed by column name). For example:
     *                            [
     *                                't1.group'                     => 25,
     *                                '! t1.disabled'                => 1,
     *                                '> t1.date'                    => '2022-01-25 18:55:32',
     *                                '< t1.modified'                => '2022-02-01 10:00:00',
     *                                '>= t1.cost'                   => 10,
     *                                '<= t1.cost'                   => 100,
     *                                '~ t1.matches_LIKE_expression' => '%, mp3, %',
     *                                '!~ t1.does_not_match_LIKE'    => '%, wav, %',
     *                                '/ t1.matches_REGEXP'          => '^hello\sworld',
     *                                '!/ t1.does_not_match_REGEXP'  => '^hello\sworld',
     *                                't1.id_in_SET'                 => [35, 36, 45],
     *                                '! t2.id_not_in_SET'           => [35, 36, 45],
     *                                '- t1.it_is_NULL'              => true,
     *                                '!- t1.it_is_not_NULL'         => true,
     *                                '+'                            => 'SOME ENDING OF THIS CLAUSE DIRECTLY IN MYSQL SYNTAX'
     *                            ]. The notation of "+"-item can be represented as an array, for example:
     *                            [
     *                                ... => ..,
     *                                ... => ..,
     *                                ... => ..,
     *                                '+' => ['SOME ENDING WITH ?, ? AND ? PARAMETERS', $param1, $param2, $param3]
     *                            ].
     * @param   array   $data     Reference to the list of column values.
     * @param   bool    $having   True if build a HAVING clause,
     *                            False if build a WHERE clause.
     * @return  string            Generated clause.
     * ---------------------------------------------------------------------
     */

    protected function makeWhere ( $columns, & $data, $having = false ) {
        $clause = '';
        $ending = '';
        $data = [];
        if (is_array($columns)) {
            if (! empty($columns)) {
                $keys = [];
                foreach ($columns as $key => $value) {
                    if ($key == '+') {
                        if (is_array($value)) {
                            if (! empty($value)) {
                                $ending = array_shift($value);
                                if (! empty($value)) {
                                    $data = array_merge($data, $value);
                                }
                            }
                        } else {
                            $ending = $value;
                        }
                    } else if (is_array($value)) {
                        $markers = [];
                        foreach ($value as $v) {
                            $data[] = $v;
                            $markers[] = '?';
                        }
                        $keys[] = '`' . preg_replace('~\.~u', '`.`', $key) . '` IN( ' . implode(', ', $markers) . ' )';
                    } else {
                        $data[] = $value;
                        $keys[] = '`' . preg_replace('~\.~u', '`.`', $key) . '` = ?';
                    }
                }
                $clause = ( $having
                            ? 'HAVING '
                            : 'WHERE ' ) . implode(' AND ', $keys) . ' ';
                $clause = preg_replace('~ `!\~\s*([^\s]+) = \? ~u', ' `$1 NOT LIKE ? ', $clause);
                $clause = preg_replace('~ `!/\s*([^\s]+) = \? ~u',  ' `$1 NOT REGEXP ? ', $clause);
                $clause = preg_replace('~ `<=\s*([^\s]+) = \? ~u',  ' `$1 <= ? ', $clause);
                $clause = preg_replace('~ `>=\s*([^\s]+) = \? ~u',  ' `$1 >= ? ', $clause);
                $clause = preg_replace('~ `!\-\s*([^\s]+) = \? ~u', ' (`$1 IS NOT NULL OR ? IS NULL) ', $clause);
                $clause = preg_replace('~ `!\s*([^\s]+) = \? ~u',   ' `$1 != ? ', $clause);
                $clause = preg_replace('~ `!\s*([^\s]+) IN\( ~u',   ' `$1 NOT IN( ', $clause);
                $clause = preg_replace('~ `<\s*([^\s]+) = \? ~u',   ' `$1 < ? ', $clause);
                $clause = preg_replace('~ `>\s*([^\s]+) = \? ~u',   ' `$1 > ? ', $clause);
                $clause = preg_replace('~ `\~\s*([^\s]+) = \? ~u',  ' `$1 LIKE ? ', $clause);
                $clause = preg_replace('~ `/\s*([^\s]+) = \? ~u',   ' `$1 REGEXP ? ', $clause);
                $clause = preg_replace('~ `\-\s*([^\s]+) = \? ~u',  ' (`$1 IS NULL OR ? IS NULL) ', $clause);
            }
        }
        return $clause . $ending;
    }

    /**
     * ---------------------------------------------------------------------
     * Builds a ORDER/GROUP BY clause of the query.
     *
     * @protected
     * @param   array   $columns  The list of ordered columns (they are
     *                            indexed by column name). For example:
     *                            [
     *                                't1.id'   => 'desc',
     *                                't1.date' => 'asc',
     *                                'rollup'  => true
     *                            ]
     * @param   bool    $groupBy  True if build a GROUP BY clause,
     *                            False if build a ORDER BY clause.
     * @return  string            Generated clause.
     * ---------------------------------------------------------------------
     */

    protected function makeOrderBy ( $columns, $groupBy = false ) {
        $clause = '';
        if (is_array($columns)) {
            if (! empty($columns)) {
                $rollup = false;
                foreach ($columns as $key => $data) {
                    if ($key == 'rollup') {
                        $rollup = true;
                        continue;
                    }
                    $key = preg_replace('~\.~u', '`.`', $key);
                    if ($clause) $clause .= ', ';
                    $clause .= '`' . $key . '` ' . ( strtolower($data) == 'desc'
                                                     ? 'DESC'
                                                     : 'ASC' );
                }
                if ($clause) {
                    $clause = ( $groupBy
                                ? 'GROUP BY '
                                : 'ORDER BY ' ) . $clause . ' ';
                    if ($rollup) $clause .= 'WITH ROLLUP ';
                }
            }
        }
        return $clause;
    }

    /**
     * ---------------------------------------------------------------------
     * Retrieves database table names.
     *
     * @protected
     * @return  array  The list of names indexed by its name.
     * ---------------------------------------------------------------------
     */

    protected function getColumns () {
        $result = [];
        foreach ($this->tableFields as $value) {
            $field = preg_replace('~^[^a-z0-9]*([a-z0-9][a-z0-9_]*)[^a-z0-9_].*$~uis', '$1', $value);
            if ($field != $value) {
                $field = mb_strtolower($field, 'UTF-8');
                $result[$field] = $field;
            }
        }
        return $result;
    }

    /**
     * ---------------------------------------------------------------------
     * Removes alien fields from the record.
     *
     * @protected
     * @param   array  $item  The record (list of indexed values) to be sifted.
     * @return  array         The sifted record.
     * ---------------------------------------------------------------------
     */

    protected function siftRecord ( $item ) {
        $columns = $this->getColumns();
        $result = [];
        foreach ($item as $field => $value) {
            $field = mb_strtolower($field, 'UTF-8');
            $this->renameField($field, $value);
            if (isset($columns[$field])) {
                $this->filterField($result, $field, $value);
            }
        }
        return $result;
    }

    /**
     * ---------------------------------------------------------------------
     * Renames a field or changes its value.
     *
     * To understand this logic, please see the SIFTRECORD method above.
     *
     * @protected
     * @param  string  $name   The field name to be renamed.
     * @param  mixed   $value  The field value to be changed.
     * ---------------------------------------------------------------------
     */

    protected function renameField ( & $name, & $value ) {
    }

    /**
     * ---------------------------------------------------------------------
     * Filters a field.
     *
     * To understand this logic, please see the SIFTRECORD method above.
     *
     * @protected
     * @param  array   $item   The constructed record to be changed.
     * @param  string  $name   The field name to be filtered.
     * @param  mixed   $value  The field value.
     * ---------------------------------------------------------------------
     */

    protected function filterField ( & $item, $name, $value ) {
        $item[$name] = $value;
    }

    /**
     * ---------------------------------------------------------------------
     * Creates the table.
     *
     * @protected
     * @return  bool  True if success,
     *                False if failure.
     * ---------------------------------------------------------------------
     */

    protected function createTable () {
        if ($this->app->has->db) {
            $separator = ',';
            $query = 'CREATE TABLE `__' . $this->table . '` (' .
                         implode($separator, $this->tableFields) .
                         $separator .
                         implode($separator, $this->tableKeys) .
                     ') ' . implode(' ', $this->tableOptions);
            $result = $this->app->db->query($query);
            return $result != false;
        }
        return false;
    }

    /**
     * ---------------------------------------------------------------------
     * Removes the table.
     *
     * @protected
     * @return  bool  True if success,
     *                False if failure.
     * ---------------------------------------------------------------------
     */

    protected function deleteTable () {
        if ($this->app->has->db) {
            $result = $this->app->db->query(
                'DROP TABLE `__' . $this->table . '`'
            );
            return $result != false;
        }
        return false;
    }

    /**
     * ---------------------------------------------------------------------
     * Empties the table completely.
     *
     * @protected
     * @return  bool  True if success,
     *                False if failure.
     * ---------------------------------------------------------------------
     */

    protected function clearTable () {
        if ($this->app->has->db) {
            $result = $this->app->db->query(
                'TRUNCATE TABLE `__' . $this->table . '`'
            );
            return $result != false;
        }
        return false;
    }

    /**
     * ---------------------------------------------------------------------
     * Appends a new record.
     *
     * @protected
     * @param   array     $item  The record (list of values indexed by column name).
     * @return  int|bool         The inserted row identifier if success,
     *                           False if failure.
     * ---------------------------------------------------------------------
     */

    protected function addRecord ( $item ) {
        if ($this->app->has->db) {
            if (is_array($item)) {
                if (! empty($item)) {
                    $fields  = implode('`, `', array_keys($item));
                    $markers = implode(', ',   array_fill(0, count($item), '?'));
                    $values  = array_values($item);
                    $object = $this->app->db->query(
                        'INSERT INTO `__' . $this->table . '` ' .
                                     '( `' . $fields . '` ) ' .
                                     'VALUES ( ' . $markers . ' )',
                        $values
                    );
                    return $object
                           ? $this->app->db->lastInsertId()
                           : false;
                }
            }
        }
        return false;
    }

    /**
     * ---------------------------------------------------------------------
     * Updates a record by its identifier.
     *
     * @protected
     * @param   int|array  $id    INTEGER: The record identifier to update if your table column has name "id".
     *                            ARRAY:   The record identifier pair like this [ 'ID_column_name' => identifier ].
     * @param   array      $item  The record (list of values indexed by column name).
     * @return  int|bool          The number of rows affected if success,
     *                            False if failure.
     * ---------------------------------------------------------------------
     */

    protected function updateRecord ( $id, $item ) {
        if ($this->app->has->db) {
            if (is_array($item)) {
                if (! empty($item)) {
                    if (! empty($id)) {
                        $keyname = 'id';
                        if (is_array($id)) {
                            foreach ($id as $keyname => $v) {
                                $id = $v;
                                break;
                            }
                        }
                        $fields = implode('` = ?, `', array_keys($item));
                        $values = array_values($item);
                        $values[] = $id;
                        $query = 'UPDATE `__' . $this->table . '` ' .
                                 'SET `' . $fields . '` = ? ' .
                                 'WHERE `' . $keyname . '` = ?';
                        $object = $this->app->db->query($query, $values);
                        return $object
                               ? $object->rowCount()
                               : false;
                    }
                }
            }
        }
        return false;
    }

    /**
     * ---------------------------------------------------------------------
     * Deletes records by their identifiers.
     *
     * @protected
     * @param   int|array  $ids  INTEGER:          The record identifier if your table column has name "id".
     *                           ARRAY OF INTEGER: The list of record identifiers if your table column has name "id".
     *                           ARRAY:            The record identifier pair like this [ 'ID_column_name' => identifier ].
     *                           ARRAY OF ARRAY:   The record identifier pair like this [ 'ID_column_name' => [list of identifiers] ].
     * @return  int|bool         The number of rows affected if success,
     *                           False if failure.
     * ---------------------------------------------------------------------
     */

    protected function deleteRecord ( $ids ) {
        if ($this->app->has->db) {
            if (! empty($ids)) {
                $keyname = 'id';
                if (is_array($ids)) {
                    foreach ($ids as $k => $v) {
                        if (is_string($k)) {
                            if (count($ids) == 1) {
                                $keyname = $k;
                                $ids = $v;
                            }
                        }
                        break;
                    }
                }
                $ids = ( array ) $ids;
                $markers = implode(', ', array_fill(0, count($ids), '?'));
                $query = 'DELETE ' .
                         'FROM `__' . $this->table . '` ' .
                         'WHERE `' . $keyname . '` IN ( ' . $markers . ' )';
                $object = $this->app->db->query($query, $ids);
                return $object
                       ? $object->rowCount()
                       : false;
            }
        }
        return false;
    }
};