<?php
/**
 * -------------------------------------------------------------------------
 *
 * The module for working with an SQL database using the engine provided by
 * the SQLite library.
 *
 * -------------------------------------------------------------------------
 *
 * It implements the following methods:
 *
 *     PUBLIC     __construct ( [& $owner]                              )  -->  to create an instance of this class object
 *     PROTECTED  create      (                                         )  -->  to create an instance of the SQLite object
 *     PUBLIC     connect     ( $file[, $critical]                      )  -->  to connect to the database file
 *     PUBLIC     disconnect  (                                         )  -->  to close the database connection
 *     PROTECTED  find        (                                         )  -->  to find the database file
 *     PUBLIC     generate    (                                         )  -->  to generate a random database file name
 *     PUBLIC     stop        ( $msg[, $status]                         )  -->  to abort with a message and some HTTP status
 *     PUBLIC     escape      ( $str                                    )  -->  to escape a string for safe use in a query
 *     PUBLIC     bind        ( $value                                  )  -->  to escape a value for safe use in a query
 *     PUBLIC     makeSET     ( $entry[, $names[, $equates[, $values]]] )  -->  to build a query part for the SET or () or VALUES() clause
 *     PUBLIC     exec        ( $query                                  )  -->  to execute a query without waiting for a result
 *     PUBLIC     query       ( $query                                  )  -->  to execute a query with a SQLite3 object result
 *     PUBLIC     queryFirst  ( $query                                  )  -->  to execute a query with the first column result only
 *     PUBLIC     queryRow    ( $query                                  )  -->  to execute a query with the first result only
 *     PUBLIC     queryRows   ( $query                                  )  -->  to execute a query with all results
 *     PROTECTED  autoCreate  (                                         )  -->  to call child modules so that they create their database tables
 *
 * -------------------------------------------------------------------------
 *
 * @package    MimimiFramework
 * @subpackage Modules
 * @license    GPL-2.0
 *             https://opensource.org/license/gpl-2-0/
 * @copyright  2022 MiMiMi Community
 *             https://mimimi.software/
 *
 * -------------------------------------------------------------------------
 */

    /**
     * ---------------------------------------------------------------------
     *
     * Load the class of simplest module that supports calling a child
     * module via its property name.
     *
     * ---------------------------------------------------------------------
     */

    mimimiInclude ( 'NodeModule.php' );

    /**
     * ---------------------------------------------------------------------
     *
     * Declare an appropriate class for the SQLite module by extending it
     * from that node module's class.
     *
     * ---------------------------------------------------------------------
     */

    class MimimiSqlite extends MimimiNodeModule {

        /**
         * -----------------------------------------------------------------
         *
         * Specify the path to the database storage. It must be relative to
         * the application folder. If there is an empty string, the current
         * module's folder name will be used.
         *
         * -----------------------------------------------------------------
         *
         * @access protected
         * @var    string
         *
         * -----------------------------------------------------------------
         */

        protected $path = '';

        /**
         * -----------------------------------------------------------------
         *
         * Specify the file name prefix and extension. That random file name
         * will be generated for each new database. For more details, see
         * the "find()" and "generate()" methods below.
         *
         * -----------------------------------------------------------------
         *
         * @access protected
         * @var    string
         *
         * -----------------------------------------------------------------
         */

        protected $prefix = 'database_';
        protected $ext    = '.sqlite';

        /**
         * -----------------------------------------------------------------
         *
         * Declare a pointer to the future instance of the SQLite object for
         * interacting with the database.
         *
         * -----------------------------------------------------------------
         *
         * @access protected
         * @var    object|false
         *
         * -----------------------------------------------------------------
         */

        protected $db = FALSE;

        /**
         * -----------------------------------------------------------------
         *
         * Creates an instance of this class object.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   object|false  $owner  (by-reference) (optional) Reference to the parent module.
         * @return  void
         *
         * -----------------------------------------------------------------
         */

        public function __construct ( & $owner = FALSE ) {
            parent::__construct ( $owner );
            if ( $this->path == '' ) {
                $this->path = $this->getNodePath ( );
            }
            $this->create ( );
            $auto = FALSE;
            $file = $this->find ( );
            if ( $file === FALSE ) {
                $auto = TRUE;
                $file = $this->generate ( );
            }
            $this->connect ( $file );
            if ( $auto ) {
                $this->autoCreate ( );
            }
        }

        /**
         * -----------------------------------------------------------------
         *
         * Creates an instance of the SQLite object.
         *
         * -----------------------------------------------------------------
         *
         * Note: If PHP on the server is compiled without SQLite3 support,
         *       the application will stop immediately with a text
         *       notification.
         *
         * -----------------------------------------------------------------
         *
         * @protected
         * @return  void
         *
         * -----------------------------------------------------------------
         */

        protected function create ( ) {
            try {
                $this->db = new SQLite3 ( '' );
            } catch ( ErrorException $e ) {
                $this->stop ( 'Sorry, this application does not support the SQLite3 class!' );
            }
        }

        /**
         * -----------------------------------------------------------------
         *
         * Connects to the database file.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   string  $file      Path to the SQLite database file.
         *                             Or :memory: to use in-memory database.
         * @param   bool    $critical  (optional) FALSE if you want to get the result of this operation.
         *                                        TRUE  if you want to stop the application when an error occurs.
         * @return  bool               TRUE  in case of success.
         *                             FALSE if connection to this database failed.
         *
         * -----------------------------------------------------------------
         */

        public function connect ( $file, $critical = TRUE ) {
            $this->disconnect ( );
            try {
                if ( $this->db ) {
                    $this->db->open ( $file );
                    return TRUE;
                }
            } catch ( ErrorException $e ) { }
            if ( $critical ) {
                $this->stop ( 'Sorry, there was an error opening/creating the database file!' );
            }
            return FALSE;
        }

        /**
         * -----------------------------------------------------------------
         *
         * Closes the database connection.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @return  void
         *
         * -----------------------------------------------------------------
         */

        public function disconnect ( ) {
            try {
                if ( $this->db ) {
                    $this->db->close ( );
                }
            } catch ( ErrorException $e ) { }
        }

        /**
         * -----------------------------------------------------------------
         *
         * Finds the first matching database file.
         *
         * -----------------------------------------------------------------
         *
         * @protected
         * @return  string|false  STRING: The name of the file found.
         *                        FALSE:  If the file is not found.
         *
         * -----------------------------------------------------------------
         */

        protected function find ( ) {
            $len1  = mb_strlen ( $this->prefix );
            $len2  = mb_strlen ( $this->ext    );
            $files = mimimiFolders ( $this->path, [ ], TRUE );
            foreach ( $files as $file ) {
                if (        mb_substr ( $file, 0,     $len1 ) == $this->prefix ) {
                    $test = mb_substr ( $file, $len1        );
                    if (    mb_substr ( $test, -$len2       ) == $this->ext    ) {
                        return mimimiBasePath ( $this->path ) . $file;
                    }
                }
            }
            return FALSE;
        }

        /**
         * -----------------------------------------------------------------
         *
         * Generates a random database file name.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @return  string
         *
         * -----------------------------------------------------------------
         */

        public function generate ( ) {
            return mimimiBasePath ( $this->path ) . $this->prefix . mimimiRandomId ( 16 ) . $this->ext;
        }

        /**
         * -----------------------------------------------------------------
         *
         * Aborts with a message and some HTTP status.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   string  $msg     Text explaining the reason for the stop.
         * @param   int     $status  (optional) HTTP status code for the browser.
         *                                      Default is 500 (Internal Server Error).
         * @return  void
         *
         * -----------------------------------------------------------------
         */

        public function stop ( $msg, $status = 500 ) {
            mimimiStop ( $msg, $status );
        }

        /**
         * -----------------------------------------------------------------
         *
         * Calls child modules so that they create their database tables.
         *
         * -----------------------------------------------------------------
         *
         * @protected
         * @return  void
         *
         * -----------------------------------------------------------------
         */

        protected function autoCreate ( ) {
            $path    = $this->getNodePath ( );
            $folders = mimimiFolders ( $path );
            foreach ( $folders as $name ) {
                $name   = preg_replace ( '~[\W_]+~u',        '_',     $name );
                $name   = preg_replace ( '~([^_])([A-Z])~u', '$1_$2', $name );
                $module = strtolower   (                              $name );
                if ( $this->has->$module ) {
                    if ( method_exists ( $this->$module, 'onInstall' ) ) {
                        $this->$module->onInstall ( $this );
                    }
                }
            }
        }

        /**
         * -----------------------------------------------------------------
         *
         * Escapes a string for safe use in an SQL statement.
         *
         * -----------------------------------------------------------------
         *
         * Please note that this method does not escape double quote
         * characters ("). Because double quotes in SQLite3 are equivalent
         * to backticks in MySQL used for table or column names. Therefore,
         * it is highly recommended to use only single quotes when passing
         * a string value in an SQL statement, and be sure to escape such
         * a value. For example:
         *
         *     $query = "SELECT * WHERE name = '" . $this->escape ( $value ) . "'";
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   string  $str  The string to be escaped.
         * @return  string
         *
         * -----------------------------------------------------------------
         */

        public function escape ( $str ) {
            return $this->db ? $this->db->escapeString ( $str )
                             : '';
        }

        /**
         * -----------------------------------------------------------------
         *
         * Binds an escaped value for safe use in an SQL statement.
         *
         * -----------------------------------------------------------------
         *
         * You should use this method like the following example:
         *
         *     $query = 'SELECT * WHERE name = ' . $this->bind ( $value );
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   mixed  $value  The value to be escaped.
         * @return  mixed
         *
         * -----------------------------------------------------------------
         */

        public function bind ( $value ) {
            if ( is_null      ( $value ) ) return 'NULL';
            if ( is_bool      ( $value ) ) return $value ? 'TRUE' : 'FALSE';
            if ( is_int       ( $value ) ) return (string) $value;
            if ( is_float     ( $value ) ) return (string) $value;
            if ( is_numeric   ( $value ) ) return $value;
            if ( is_object    ( $value ) ) return "'[Object]'";
            if ( is_resource  ( $value ) ) return "'[Resource]'";
            if ( is_array     ( $value ) ) $value = @ json_encode ( $value );
                                           if ( $value === FALSE ) return "'[Array]'";
            if ( is_iterable  ( $value ) ) return "'[Iterable]'";
            if ( is_countable ( $value ) ) return "'[Countable]'";
            return $this->db ? ( "'" . $this->db->escapeString ( $value ) . "'" )
                             :   "''";
        }

        /**
         * -----------------------------------------------------------------
         *
         * Builds a query part intended for the UPDATE table SET ...
         *                                   or INSERT INTO table (...)
         *                                   or INSERT INTO table VALUES(...) clause.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   array   $entry     The entry to be processed.
         * @param   bool    $names     (optional) TRUE if you want to build name parts (it is for the SET or () clause).
         * @param   string  $operator  (optional) ' = ' if you want to build equation parts (it is for the SET clause).
         * @param   bool    $values    (optional) TRUE if you want to build value parts (it is for the SET or VALUES() clause).
         * @return  string
         *
         * -----------------------------------------------------------------
         */

        public function makeSET ( $entry, $names = TRUE, $operator = ' = ', $values = TRUE ) {
            $result = '';
            foreach( $entry as $name => $value ) {
                if ( $result != '' ) $result .= ', ';
                if ( $names        ) $result .= "`" . $name . "`" . $operator;
                if ( $values       ) $result .= "'" . $this->escape ( $value ) . "'";
            }
            return $result;
        }

        /**
         * -----------------------------------------------------------------
         *
         * Executes a non-response query to the current database.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   string  $query  The SQL query to execute.
         * @return  bool            TRUE if the query was successful.
         *
         * -----------------------------------------------------------------
         */

        public function exec ( $query ) {
            return $this->db ? $this->db->exec ( $query )
                             : FALSE;
        }

        /**
         * -----------------------------------------------------------------
         *
         * Executes a query to the current database.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   string        $query  The SQL query to execute.
         * @return  object|false          OBJECT SQLite3Result on success.
         *                                FALSE  on failure.
         *
         * -----------------------------------------------------------------
         */

        public function query ( $query ) {
            return $this->db ? $this->db->query ( $query )
                             : FALSE;
        }

        /**
         * -----------------------------------------------------------------
         *
         * Executes a first-column query to the current database.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   string            $query  The SQL query to execute.
         * @return  mixed|null|false  MIXED on success with a result.
         *                            NULL  on success with no result.
         *                            FALSE on failure.
         *
         * -----------------------------------------------------------------
         */

        public function queryFirst ( $query ) {
            return $this->db ? $this->db->querySingle ( $query )
                             : FALSE;
        }

        /**
         * -----------------------------------------------------------------
         *
         * Executes a first-row query to the current database.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   string       $query  The SQL query to execute.
         * @return  array|false          ARRAY on success.
         *                               FALSE on failure.
         *
         * -----------------------------------------------------------------
         */

        public function queryRow ( $query ) {
            $result = $this->query ( $query );
            if ( $result ) {
                $entry = $result->fetchArray ( SQLITE3_ASSOC );
                return $entry ? $entry
                              : [ ];
            }
            return FALSE;
        }

        /**
         * -----------------------------------------------------------------
         *
         * Executes an all-rows query to the current database.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   string       $query  The SQL query to execute.
         * @return  array|false          ARRAY OF ARRAYS on success.
         *                               FALSE           on failure.
         *
         * -----------------------------------------------------------------
         */

        public function queryRows ( $query ) {
            $result = $this->query ( $query );
            if ( $result ) {
                $entries = [ ];
                while ( $entry = $result->fetchArray ( SQLITE3_ASSOC ) ) {
                    $entries[ ] = $entry;
                }
                return $entries;
            }
            return FALSE;
        }

        /**
         * -----------------------------------------------------------------
         *
         * Reset the namespace simulator if you want to provide an ability
         * to extend this module with child modules that perform various
         * functions.
         *
         * Note: If you want to extend this class in a module of your
         *       application, you should also reset this simulator there
         *       in the same way. Otherwise, the path to the database
         *       storage may be calculated incorrectly.
         *
         * -----------------------------------------------------------------
         *
         * @var    string
         * @access protected
         *
         * -----------------------------------------------------------------
         */

        protected $myNodeFile = __FILE__;
    };
