<?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
 *     PROTECTED  $alreadyInstalled
 *
 * Implemented methods below are:
 *     create
 *     add
 *     update
 *     save
 *     remove
 *     get
 *     select
 *     install
 *     PROTECTED  makeSelect
 *     PROTECTED  makeJoin
 *     PROTECTED      makeClauseColumns
 *     PROTECTED      makeComparisonOperators
 *     PROTECTED  makeWhere
 *     PROTECTED      makeIdForWhere
 *     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         = [ ];
    protected $alreadyInstalled = FALSE;

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

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

        /**
         * -----------------------------------------------------------------
         *
         * Adds a new entry.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   array     $item  The entry (list of values indexed by column name).
         * @return  int|bool         INTEGER: The inserted entry identifier if success.
         *                           FALSE:   If failure.
         *
         * -----------------------------------------------------------------
         */

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

        /**
         * -----------------------------------------------------------------
         *
         * Updates an entry by its ID.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   int|array  $ids        INTEGER:          The entry identifier if your table column has name "id".
         *                                 ARRAY OF INTEGER: The list of entry identifiers if your table column has name "id".
         *                                 ARRAY:            The entry identifier pair like this [ 'ID_column_name' => identifier ].
         *                                 ARRAY OF ARRAY:   The entry identifier pair like this [ 'ID_column_name' => [list of identifiers] ].
         * @param   array      $item       The entry (list of values indexed by column name).
         * @param   string     $increaser  (optional) Some increase operator if you want to increment only the requested columns.
         *                                            It can be one of the following characters:
         *                                                &       Bitwise AND
         *                                                >>      Right shift
         *                                                <<      Left shift
         *                                                %, MOD  Modulo operator
         *                                                *       Multiplication operator
         *                                                +       Addition operator
         *                                                -       Minus operator
         *                                                /       Division operator
         *                                                ^       Bitwise XOR
         *                                                AND, && Logical AND
         *                                                DIV     Integer division
         *                                                OR, ||  Logical OR
         *                                                XOR     Logical XOR
         *                                                |       Bitwise OR
         * @return  int|bool               INTEGER: The number of entries affected if success,
         *                                 FALSE:   If failure.
         *
         * -----------------------------------------------------------------
         */

        public function update ( $ids, $item, $increaser = '' ) {
            $row = $this->siftRecord ( $item );
            return empty ( $row ) ? FALSE
                                  : $this->updateRecord ( $ids, $row, $increaser );
        }

    /**
     * ---------------------------------------------------------------------
     * 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 );
    }

        /**
         * -----------------------------------------------------------------
         *
         * Removes an entry by its ID.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   int|array  $ids  INTEGER:          The entry identifier if your table column has name "id".
         *                           ARRAY OF INTEGER: The list of entry identifiers if your table column has name "id".
         *                           ARRAY:            The entry identifier pair like this [ 'ID_column_name' => identifier ].
         *                           ARRAY OF ARRAY:   The entry identifier pair like this [ 'ID_column_name' => [list of identifiers] ].
         * @return  int|bool         INTEGER: The number of entries 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.
     *                                   Each element describes one of the database tables being joined.
     *                                       The syntax of its subelements is equivalent to the WHERE clause below.
     *                               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.
     *                                   Their 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 an element is preceded by:
     *                                       |   = the OR operator will be used instead of AND,
     *                                       (   = an opening parenthesis will be written after the preceding AND/OR operator,
     *                                       )   = a closing parenthesis will be written after that element,
     *                                       !   = a negative comparison is performed,
     *                                       <   = a Less comparison is performed,
     *                                       <=  = a Less-Or-Equal comparison is performed,
     *                                       >   = a More comparison is performed,
     *                                       >=  = a More-Or-Equal comparison is performed,
     *                                       ~   = a LIKE comparison is performed,
     *                                       !~  = a negative LIKE comparison is performed,
     *                                       /   = a REGEXP comparison is performed,
     *                                       !/  = 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 ( empty ( $this->alreadyInstalled ) ) {
            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 ] );
                        }
                    }
                }
            }
            $this->alreadyInstalled = TRUE;
        }
        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 (please do not use "`" for table/column names):
         *                            [
         *                                't1.*'    => TRUE,        // SELECT t1.*,
         *                                't3.name' => TRUE,                  t3.name,
         *                                't2.id'   => 'user_id'              r2.id AS user_id
         *                            ].
         *                            Also you can use here any modifiers or expressions (please use "`" for table/column names there).
         *                            For example:
         *                            [
         *                                'DISTINCT'            => TRUE,      // SELECT DISTINCT
         *                                'SQL_CALC_FOUND_ROWS' => TRUE,      //        SQL_CALC_FOUND_ROWS
         *                                't1.*'                => TRUE,      //        t1.*,
         *                                'MAX(`t1`.`sum`)'     => 'total'    //        MAX(t1.sum) AS 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 ( '~(^\s+|\s+$)~u', '', $key );
                        $column = preg_replace ( '~^(' . $name . ')\.(' . $name . ')$~ui', '`$1`.`$2`', $key    );
                        $column = preg_replace ( '~^(' . $name . ')\.\*$~ui',              '`$1`.*',    $column );
                        $column = preg_replace ( '~^(' . $name . ')$~u',                   '`$1`',      $column );
                        $isModifier = preg_match ( '~^[A-Z][A-Z0-9_]*$~u', $key );
                        if ( $clause ) $clause .= $isModifier || $afterModifier ? ' '
                                                                                : ', ';
                        $clause .= $column . ( is_string ( $data ) ? ' AS `' . $data . '`'
                                                                   : '' );
                        $afterModifier = $isModifier;
                    }
                    if ( $clause ) $clause = 'SELECT ' . $clause . ' ';
                }
            }
            return $clause;
        }

        /**
         * -----------------------------------------------------------------
         *
         * Builds JOIN clauses of the query.
         *
         * -----------------------------------------------------------------
         *
         * @protected
         * @param   array   $tables  The list of tables being joined (they are indexed by table name).
         *                           For example:
         *                           [
         *                               'categories' => [                                // LEFT JOIN categories AS t2
         *                                   't2.id' => 't1.category_id'                  //                      ON t2.id = t1.category_id
         *                               ],                                               //
         *                               'products' => [                                  // LEFT JOIN products AS t3
         *                                   't3.id'      => 't1.category_id',            //                       ON  t3.id    = t1.category_id
         *                                   '! t3.id'    => 't1.parent_id',              //                       AND t3.id   != t1.parent_id
         *                                   '> t3.date'  => 't1.announced',              //                       AND t3.date  > t1.announced
         *                                   '< t3.date'  => 't1.modified',               //                       AND t3.date  < t1.modified
         *                                   '>= t3.cost' => 't1.min_price',              //                       AND t3.cost >= t1.min_price
         *                                   '<= t3.cost' => 't1.max_price'               //                       AND t3.cost <= t1.max_price
         *                               ],                                               //
         *                               'vendors' => [                                   // LEFT JOIN vendors AS t4
         *                                   't4.name'       => 'Example',                //                      ON  t4.name      = "Example"
         *                                   't4.visible'    => TRUE,                     //                      AND t4.visible   = TRUE
         *                                   '! t4.region'   => 'North',                  //                      AND t4.region   != "North"
         *                                   '> t4.date'     => '2022-01-25 18:55:32',    //                      AND t4.date      > "2022-01-25 18:55:32"
         *                                   '< t4.modified' => '2022-02-01 10:00:00',    //                      AND t4.modified  < "2022-02-01 10:00:00"
         *                                   '>= t4.browsed' => 100,                      //                      AND t4.browsed  >= 100
         *                                   '<= t4.browsed' => 1000,                     //                      AND t4.browsed  <= 1000
         *                                   't2.id'         => [35, 36, 45],             //                      AND t2.id       IN( 35, 36, 45 )
         *                                   '! t3.id'       => [35, 36, 45]              //                      AND t3.id       NOT IN( 35, 36, 45 )
         *                               ]
         *                           ].
         *                           You can write the preceding ">" character to use the RIGHT JOIN instead of the default LEFT JOIN.
         *                           For example:
         *                           [
         *                               '> categories' => [                // RIGHT JOIN categories AS t2
         *                                   't2.id' => 't1.category_id'    //                       ON t2.id = t1.category_id
         *                               ]                                  //
         *                           ].
         *                           The preceding "|" character will be recognized as the OR operator instead of the default AND.
         *                           For example:
         *                           [
         *                               'categories' => [                          // LEFT JOIN categories AS t2
         *                                   '  t2.id'      => 't1.category_id',    //                      ON  t2.id      = t1.category_id
         *                                   '  t2.visible' => TRUE,                //                      AND t2.visible = TRUE
         *                                   '| t2.id'      => 25                   //                      OR  t2.id      = 25
         *                               ]
         *                           ].
         *                           The next preceding "(" or ")" character will be recognized as the opening or closing parenthesis.
         *                           For example:
         *                           [
         *                               'categories' => [                            // LEFT JOIN categories AS t2
         *                                   '    t2.id'      => 't1.category_id',    //                      ON  t2.id = t1.category_id
         *                                   '  ( t2.visible' => 1,                   //                      AND (  t2.visible = 1
         *                                   '| ) t2.id'      => 25                   //                          OR t2.id      = 25 )
         *                               ]
         *                           ].
         *                           Also you can use spaces to duplicate any table or column name with another condition if you need.
         *                           For example:
         *                           [
         *                               'categories' => [                        // LEFT JOIN categories AS t2
         *                                   't2.id'   => 't1.category_id',       //                      ON  t2.id = t1.category_id
         *                                   't2.id '  => 't4.external_id',       //                      AND t2.id = t4.external_id
         *                                   't2.id  ' => 25                      //                      AND t2.id = 25
         *                               ],                                       //
         *                               'categories ' => [                       // LEFT JOIN categories AS t3
         *                                   't3.id' => 't1.category_id',         //                      ON  t3.id = t1.category_id
         *                                   't4.id' => 125                       //                      AND t4.id = 125
         *                               ]
         *                           ].
         *                           Also you can use "+" element(s) to directly write MySQL syntax.
         *                           Note that these elements will always be added to the end of clause.
         *                           For example:
         *                           [
         *                               'categories' => [                                    // LEFT JOIN categories AS t2
         *                                   't2.id'      => 't1.category_id',                //                      ON  t2.id           = t1.category_id
         *                                   '+'          => 'OR LENGTH("Hello") = 5',        //                      AND t2.enabled      = TRUE
         *                                   '+ '         => 'AND LENGTH(`t2`.`id`) = 3',     //                      OR  LENGTH("Hello") = 5
         *                                   't2.enabled' => TRUE                             //                      AND LENGTH(t2.id)   = 3
         *                               ]
         *                           ].
         *                            Any "+" element can also be represented as an array with the "?" placeholders.
         *                            For example:
         *                            [
         *                               'categories' => [                                             // LEFT JOIN categories AS t2
         *                                   't2.id' => 't1.category_id',                              //                      ON  t2.id             = t1.category_id
         *                                    '+'    =>  [ 'LENGTH(?) = ?', $myString, $myLength ],    //                      AND LENGTH($myString) = $myLength
         *                                    '+ '   => 'AND LENGTH("Hello") = 5'                      //                      AND LENGTH("Hello")   = 5
         *                               ]
         *                            ].
         *                           Also you can use an expression instead of column name.
         *                           For example:
         *                           [
         *                               'categories' => [                             // LEFT JOIN categories AS t2
         *                                   't2.id'           => 't1.category_id',    //                      ON  t2.id           = t1.category_id
         *                                   'LENGTH("Hello")' => 5                    //                      AND LENGTH("Hello") = 5
         *                               ]
         *                           ].
         * @param   array   $data    Reference to the list of column values.
         * @return  string           Generated clause.
         *
         * -----------------------------------------------------------------
         */

        protected function makeJoin ( $tables, & $data ) {
            $result = '';
            $data   = [ ];
            if ( is_array ( $tables ) ) {
                if ( ! empty ( $tables ) ) {
                    $num = 2;
                    foreach ( $tables as $key => $columns ) {
                        $key    = preg_replace ( '~(^\s+|\s+$)~u', '', $key );
                        $table  = preg_replace ( '~^>\s*~u',       '', $key );
                        $dir    = $key != $table ? 'RIGHT '
                                                 : 'LEFT ';
                        $clause = $dir . 'JOIN `__' . $table . '` AS `t' . $num . '` ';
                        if ( is_array ( $columns ) ) {
                            if ( ! empty ( $columns ) ) {
                                $ending  = '';
                                $clause .= 'ON ' . $this->makeClauseColumns       ( $columns, $data, $ending );
                                $clause  =         $this->makeComparisonOperators ( $clause ) . $ending;
                            }
                        }
                        $result .= $clause;
                        $num++;
                    }
                }
            }
            return $result;
        }

        /**
         * -----------------------------------------------------------------
         *
         * Builds columns for the JOIN, WHERE and HAVING clauses.
         *
         * -----------------------------------------------------------------
         *
         * @param   array   $columns  The list of filter directives (they are indexed by column name).
         * @param   array   $data     Reference to the list of column values.
         * @param   string  $ending   (optional) Reference to some ending of this clause directly in MySQL syntax.
         * @return  string            Generated clause.
         *
         * -----------------------------------------------------------------
         */

        protected function makeClauseColumns ( $columns, & $data, & $ending = '' ) {
            $result = '';
            $ending = '';
            if ( is_array ( $columns ) ) {
                if ( ! empty ( $columns ) ) {
                    $isColumnName = '~^\s*t\d+\.[a-z][a-z0-9_]*\s*$~ui';

                    /**
                     * -----------------------------------------------------
                     *
                     * Walk through the filter directives.
                     *
                     * -----------------------------------------------------
                     */

                    $operator = '';
                    foreach ( $columns as $key => $value ) {
                        $closing = '';
                        $key     = preg_replace ( '~(^\s+|\s+$)~u', '', $key );

                        /**
                         * -------------------------------------------------
                         *
                         * Parse the USE_OR_CONCATENATION symbol.
                         *
                         * -------------------------------------------------
                         */

                        $column = preg_replace ( '~^\|\s*~u', '', $key );
                        if ( $key != $column ) {
                            $operator = $operator != '' ? 'OR '
                                                        : $operator;
                        }

                        /**
                         * -------------------------------------------------
                         *
                         * Parse the OPEN_CONCATENATION_PARENTHESIS symbol.
                         *
                         * -------------------------------------------------
                         */

                        $key = preg_replace ( '~^\(\s*~u', '', $column );
                        if ( $key != $column ) {
                            $column    = $key;
                            $operator .= '( ';
                        }

                        /**
                         * -------------------------------------------------
                         *
                         * Parse the CLOSE_CONCATENATION_PARENTHESIS symbol.
                         *
                         * -------------------------------------------------
                         */

                        $key = preg_replace ( '~^\)\s*~u', '', $column );
                        if ( $key != $column ) {
                            $column  = $key;
                            $closing = ') ';
                        }

                        /**
                         * -------------------------------------------------
                         *
                         * Enclose the TABLE.COLUMN identifier in quotation marks.
                         *
                         * -------------------------------------------------
                         */

                        if ( preg_match ( $isColumnName, $column ) ) {
                            $column = '`' . preg_replace ( '~\.~u', '`.`', $column ) . '`';
                        }

                        /**
                         * -------------------------------------------------
                         *
                         * If it is the DIRECT_MYSQL_SYNTAX directive.
                         *
                         * -------------------------------------------------
                         */

                        if ($column == '+') {
                            if ( is_array ( $value ) ) {
                                if ( ! empty ( $value ) ) {
                                    $ending .= array_shift ( $value ) . ' ';
                                    if ( ! empty ( $value ) ) {
                                        $data = array_merge ( $data, $value );
                                    }
                                }
                            } else {
                                if ( ! empty ( $value ) ) {
                                    $ending .= $value . ' ';
                                }
                            }

                        /**
                         * -------------------------------------------------
                         *
                         * If it is a directive like COLUMN IN(...).
                         *
                         * -------------------------------------------------
                         */

                        } else if ( is_array ( $value ) ) {
                            $markers = [ ];
                            foreach ( $value as $v ) {
                                $data[    ] = $v;
                                $markers[ ] = '?';
                            }
                            $result .= $operator .
                                           $column . ' IN( ' . implode ( ', ', $markers ) . ' ) ' .
                                       $closing;

                        /**
                         * -------------------------------------------------
                         *
                         * If it is a directive like COLUMN = COLUMN.
                         *
                         * -------------------------------------------------
                         */

                        } else if ( is_string  (                $value )
                               &&   preg_match ( $isColumnName, $value ) ) {
                            $value   = preg_replace ( '~(^\s+|\s+$)~u', '',    $value );
                            $value   = preg_replace ( '~\.~u',          '`.`', $value );
                            $result .= $operator .
                                           $column . ' = `' . $value . '` ' .
                                       $closing;

                        /**
                         * -------------------------------------------------
                         *
                         * Otherwise, it is a directive like COLUMN = VALUE.
                         *
                         * -------------------------------------------------
                         */

                        } else {
                            $data[ ] = $value;
                            $result .= $operator .
                                           $column . ' = ? ' .
                                       $closing;
                        }

                        /**
                         * -------------------------------------------------
                         *
                         * Return to the default concatenation operator.
                         *
                         * -------------------------------------------------
                         */

                        $operator = 'AND ';
                    }
                }
            }
            return $result;
        }

        /**
         * -----------------------------------------------------------------
         *
         * Builds comparison operators for the JOIN, WHERE and HAVING clauses.
         *
         * -----------------------------------------------------------------
         *
         * @param   string  $clause  The clause containing pseudo-operators.
         * @return  string           The same as above, but all pseudo-operators are converted to real comparison operators.
         *
         * -----------------------------------------------------------------
         */

        protected function makeComparisonOperators ( $clause ) {
            $clause = preg_replace ( '~(^WHERE|^HAVING| ON| AND| OR)( \()? `!\~\s*([^\s]+) = (`|\?)~u',       '$1$2 `$3 NOT LIKE $4',                 $clause );
            $clause = preg_replace ( '~(^WHERE|^HAVING| ON| AND| OR)( \()? `!/\s*([^\s]+) = (`|\?)~u',        '$1$2 `$3 NOT REGEXP $4',               $clause );
            $clause = preg_replace ( '~(^WHERE|^HAVING| ON| AND| OR)( \()? `<=\s*([^\s]+) = (`|\?)~u',        '$1$2 `$3 <= $4',                       $clause );
            $clause = preg_replace ( '~(^WHERE|^HAVING| ON| AND| OR)( \()? `>=\s*([^\s]+) = (`|\?)~u',        '$1$2 `$3 >= $4',                       $clause );
            $clause = preg_replace ( '~(^WHERE|^HAVING| ON| AND| OR)( \()? `!\-\s*([^\s]+) = (`[^`]+`|\?)~u', '$1$2 (`$3 IS NOT NULL OR $4 IS NULL)', $clause );
            $clause = preg_replace ( '~(^WHERE|^HAVING| ON| AND| OR)( \()? `!\s*([^\s]+) = (`|\?)~u',         '$1$2 `$3 != $4',                       $clause );
            $clause = preg_replace ( '~(^WHERE|^HAVING| ON| AND| OR)( \()? `!\s*([^\s]+) (IN\( \?)~u',        '$1$2 `$3 NOT $4',                      $clause );
            $clause = preg_replace ( '~(^WHERE|^HAVING| ON| AND| OR)( \()? `<\s*([^\s]+) = (`|\?)~u',         '$1$2 `$3 < $4',                        $clause );
            $clause = preg_replace ( '~(^WHERE|^HAVING| ON| AND| OR)( \()? `>\s*([^\s]+) = (`|\?)~u',         '$1$2 `$3 > $4',                        $clause );
            $clause = preg_replace ( '~(^WHERE|^HAVING| ON| AND| OR)( \()? `\~\s*([^\s]+) = (`|\?)~u',        '$1$2 `$3 LIKE $4',                     $clause );
            $clause = preg_replace ( '~(^WHERE|^HAVING| ON| AND| OR)( \()? `/\s*([^\s]+) = (`|\?)~u',         '$1$2 `$3 REGEXP $4',                   $clause );
            return    preg_replace ( '~(^WHERE|^HAVING| ON| AND| OR)( \()? `\-\s*([^\s]+) = (`[^`]+`|\?)~u',  '$1$2 (`$3 IS NULL OR $4 IS NULL)',     $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,                       // WHERE t1.group       =  25
         *                                '! t1.disabled'     => 1,                        //   AND t1.disabled    != 1
         *                                '> t1.date'         => '2022-01-25 18:55:32',    //   AND t1.date        >  "2022-01-25 18:55:32"
         *                                '< t1.modified'     => '2022-02-01 10:00:00',    //   AND t1.modified    <  "2022-02-01 10:00:00"
         *                                '>= t1.cost'        => 10,                       //   AND t1.cost        >= 10
         *                                '<= t1.cost'        => 100,                      //   AND t1.cost        <= 100
         *                                '~ t1.description'  => '%, mp3, %',              //   AND t1.description LIKE       "%, mp3, %"
         *                                '!~ t1.description' => '%, wav, %',              //   AND t1.description NOT LIKE   "%, wav, %"
         *                                '/ t1.name'         => '^hello\sworld',          //   AND t1.name        REGEXP     "^hello\sworld"
         *                                '!/ t1.name'        => '^hello\sworld',          //   AND t1.name        NOT REGEXP "^hello\sworld"
         *                                't1.id'             => [35, 36, 45],             //   AND t1.id          IN( 35, 36, 45 )
         *                                '! t2.id'           => [35, 36, 45],             //   AND t2.id          NOT IN( 35, 36, 45 )
         *                                '- t1.size'         => TRUE,                     //   AND (t1.size       IS NULL     OR TRUE IS NULL)
         *                                '!- t1.size'        => TRUE,                     //   AND (t1.size       IS NOT NULL OR TRUE IS NULL)
         *                                't1.name'           => 't2.author'               //   AND t1.name        = t2.author
         *                            ].
         *                            The preceding "|" character will be recognized as the OR operator (instead of the default AND).
         *                            For example:
         *                            [
         *                                't1.group'      => 25,    // WHERE t1.group    =  25
         *                                '! t1.disabled' => 1,     //   AND t1.disabled != 1
         *                                '| >= t1.cost'  => 10     //    OR t1.cost     >= 10
         *                            ].
         *                            The next preceding "(" or ")" character will be recognized as an opening or closing parenthesis.
         *                            For example:
         *                            [
         *                                't1.group'        => 25,    // WHERE t1.group    =  25
         *                                '!   t1.disabled' => 1,     //   AND t1.disabled != 1
         *                                '| ( > t1.cost'   => 5      //    OR (   t1.cost    > 5
         *                                '  ) t1.visible'  => 1      //       AND t1.visible = 1 )
         *                            ].
         *                            Also you can use spaces to duplicate any column name with another condition if you need.
         *                            For example:
         *                            [
         *                                't1.group'  => 25,     // WHERE t1.group = 25
         *                                't1.group ' => '25'    //   AND t1.group = "25"
         *                            ].
         *                            Also you can use "+" element(s) to directly write MySQL syntax.
         *                            Note that these elements will always be added to the end of clause.
         *                            For example:
         *                            [
         *                                't1.group'   => 25,                              // WHERE t1.group         = 25
         *                                '+'          => 'AND LENGTH("Hello") = 5',       //   AND t1.enabled       = TRUE
         *                                '+ '         => 'OR LENGTH(`t1`.`group`) = 2'    //   AND LENGTH("Hello")  = 5
         *                                't1.enabled' => TRUE                             //   AND LENGTH(t1.hroup) = 2
         *                            ].
         *                            Any "+" element can also be represented as an array with the "?" placeholders.
         *                            For example:
         *                            [
         *                                '+' =>  [ 'LENGTH(?) = ?', $myString, $myLength ],    // WHERE LENGTH($myString) = $myLength
         *                                '+ ' => 'AND LENGTH("Hello") = 5'                     //   AND LENGTH("Hello")   = 5
         *                            ].
         *                            Also you can use an expression instead of column name.
         *                            For example:
         *                            [
         *                                't1.group'        => 25,    // WHERE t1.group        = 25
         *                                'LENGTH("Hello")' => 5      //   AND LENGTH("Hello") = 5
         *                            ].
         * @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 ) {
            $data   = [ ];
            $ending = '';
            $clause = $this->makeClauseColumns ( $columns, $data, $ending );
            if ( $clause != '' ) {
                $clause = ( $having ? 'HAVING '
                                    : 'WHERE ' ) . $clause;
                $clause = $this->makeComparisonOperators ( $clause );
            }
            return $clause . $ending;
        }

            /**
             * -------------------------------------------------------------
             *
             * Builds the ID column for the (UPDATE/DELETE) WHERE clause.
             *
             * -------------------------------------------------------------
             *
             * @param   int|array  $ids  (by-reference) INTEGER:          The entry identifier if your table column has name "id".
             *                                          ARRAY OF INTEGER: The list of entry identifiers if your table column has name "id".
             *                                          ARRAY:            The entry identifier pair like this [ 'ID_column_name' => identifier ].
             *                                          ARRAY OF ARRAY:   The entry identifier pair like this [ 'ID_column_name' => [list of identifiers] ].
             * @return  string           The ID column name.
             *
             * -------------------------------------------------------------
             */

            protected function makeIdForWhere ( & $ids ) {
                $column = 'id';
                if ( is_array ( $ids ) ) {
                    foreach ( $ids as $key => $id ) {
                        if ( is_string ( $key ) ) {
                            $column = $key;
                            if ( count ( $ids ) == 1 ) {
                                $ids = $id;
                            }
                        }
                        break;
                    }
                }
                $ids = ( array ) $ids;
                return $column;
            }

    /**
     * ---------------------------------------------------------------------
     * 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 entries by their identifiers.
         *
         * -----------------------------------------------------------------
         *
         * @protected
         * @param   int|array  $ids        INTEGER:          The entry identifier if your table column has name "id".
         *                                 ARRAY OF INTEGER: The list of entry identifiers if your table column has name "id".
         *                                 ARRAY:            The entry identifier pair like this [ 'ID_column_name' => identifier ].
         *                                 ARRAY OF ARRAY:   The entry identifier pair like this [ 'ID_column_name' => [list of identifiers] ].
         * @param   array      $item       The entry (list of values indexed by column name).
         * @param   string     $increaser  (optional) Some increase operator if you want to increment only the requested columns.
         *                                            It can be one of the following characters:
         *                                                &       Bitwise AND
         *                                                >>      Right shift
         *                                                <<      Left shift
         *                                                %, MOD  Modulo operator
         *                                                *       Multiplication operator
         *                                                +       Addition operator
         *                                                -       Minus operator
         *                                                /       Division operator
         *                                                ^       Bitwise XOR
         *                                                AND, && Logical AND
         *                                                DIV     Integer division
         *                                                OR, ||  Logical OR
         *                                                XOR     Logical XOR
         *                                                |       Bitwise OR
         * @return  int|bool               INTEGER: The number of rows affected if success,
         *                                 FALSE:   If failure.
         *
         * -----------------------------------------------------------------
         */

        protected function updateRecord ( $ids, $item, $increaser = '' ) {
            if ( $this->app->has->db ) {
                if ( is_array ( $item ) ) {
                    if ( ! empty ( $item ) ) {
                        if ( ! empty ( $ids ) ) {

                            /**
                             * ---------------------------------------------
                             *
                             * Make data for the ID column condition.
                             *
                             * ---------------------------------------------
                             */

                            $column  = $this->makeIdForWhere ( $ids );
                            $holders = array_fill ( 0, count ( $ids ), '?' );
                            $markers = implode    ( ', ', $holders         );

                            /**
                             * ---------------------------------------------
                             *
                             * Make the requested increase operator.
                             *
                             * ---------------------------------------------
                             */

                            $names = array_keys ( $item );

                            switch ( $increaser ) {
                                case '&':
                                case '>>':
                                case '<<':
                                case '%':
                                case 'MOD':
                                case '*':
                                case '+':
                                case '-':
                                case '/':
                                case '^':
                                case 'AND':
                                case '&&':
                                case 'DIV':
                                case 'OR':
                                case '||':
                                case 'XOR':
                                case '|':
                                     $operator = $increaser;
                                     foreach ( $names as & $name ) {
                                         $name = $name . '` = `' . $name;
                                     }
                                     break;
                                default:
                                     $operator = '=';
                            }

                            /**
                             * ---------------------------------------------
                             *
                             * Make data for other columns.
                             *
                             * ---------------------------------------------
                             */

                            $fields = implode ( '` ' . $operator . ' ?, `', $names );
                            $values = array_values ( $item         );
                            $values = array_merge  ( $values, $ids );

                            /**
                             * ---------------------------------------------
                             *
                             * Execute query.
                             *
                             * ---------------------------------------------
                             */

                            $query  = 'UPDATE `__' . $this->table . '` ' .
                                      'SET `' . $fields . '` ' . $operator . ' ? ' .
                                      'WHERE `' . $column . '` IN ( ' . $markers . ' )';
                            $object = $this->app->db->query ( $query, $values );

                            /**
                             * ---------------------------------------------
                             *
                             * Return operation status.
                             *
                             * ---------------------------------------------
                             */

                            return $object ? $object->rowCount ( )
                                           : FALSE;
                        }
                    }
                }
            }
            return FALSE;
        }

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

        protected function deleteRecord ( $ids ) {
            if ( $this->app->has->db ) {
                if ( ! empty ( $ids ) ) {

                    /**
                     * -----------------------------------------------------
                     *
                     * Make data for the ID column condition.
                     *
                     * -----------------------------------------------------
                     */

                    $column  = $this->makeIdForWhere ( $ids );
                    $holders = array_fill ( 0, count ( $ids ), '?' );
                    $markers = implode    ( ', ', $holders         );

                    /**
                     * -----------------------------------------------------
                     *
                     * Execute query.
                     *
                     * -----------------------------------------------------
                     */

                    $query  = 'DELETE ' .
                              'FROM `__' . $this->table . '` ' .
                              'WHERE `' . $column . '` IN ( ' . $markers . ' )';
                    $object = $this->app->db->query ( $query, $ids );

                    /**
                     * -----------------------------------------------------
                     *
                     * Return operation status.
                     *
                     * -----------------------------------------------------
                     */

                    return $object ? $object->rowCount ( )
                                   : FALSE;
                }
            }
            return FALSE;
        }
    };
