<?php
/**
 * SeekQuarry/Yioop --
 * Open Source Pure PHP Search Engine, Crawler, and Indexer
 *
 * Copyright (C) 2009 - 2026  Chris Pollett chris@pollett.org
 *
 * LICENSE:
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 * END LICENSE
 *
 * @author Chris Pollett chris@pollett.org
 * @license https://www.gnu.org/licenses/ GPL3
 * @link https://www.seekquarry.com/
 * @copyright 2009 - 2026
 * @filesource
 */
namespace seekquarry\yioop\models\datasources;

use seekquarry\yioop\configs as C;
use seekquarry\yioop\library as L;

/**
 * For timer function, if debug level set to include query statistics
 * and Yioop constants
 */
require_once __DIR__."/../../library/Utility.php";
/**
 *
 * This abstract class defines the interface through which
 * the seek_quarry program communicates with a database and the
 * filesystem.
 *
 * @author Chris Pollett
 */
abstract class DatasourceManager
{
    /**
     * Message from before query was attempted that is printed if an
     * exception occurs
     * @var string
     */
    public $pre_message;
    /**
     * Used to store statistics about what queries have been run depending on
     * the debug level
     * @var string
     */
    public $query_log;
    /**
     * Used to store the total time taken to execute queries
     * @var int
     */
    public $total_time;
    /**
     * Sets up the query_log for query statistics
     */
    public function __construct()
    {
        $this->query_log = [];
        $this->total_time = 0;
    }
    /**
     * Connects to a database on a DBMS using data provided or from config.php
     *
     * @param string $db_host the hostname of where the database is located
     *     (not used in all dbms's)
     * @param string $db_user the user to connect as
     * @param string $db_password the password of the user to connect as
     * @param string $db_name the name of the database on host we are
     * connecting to
     * @return mixed return false if not successful and some kind of
     *     connection object/identifier otherwise
     */
    abstract public function connect($db_host = C\DB_HOST,
        $db_user = C\DB_USER,
        $db_password = C\DB_PASSWORD, $db_name = C\DB_NAME);
    /**
     * Closes connections to DBMS
     *
     */
    abstract public function disconnect();
    /**
     * Hook Method for execute(). Executes the sql command on the database
     *
     * This method operates on either query or data manipulation statements
     *
     * @param string $sql  SQL statement to execute
     * @param array $params bind_name => value values to interpolate into
     *      the $sql to be executes
     * @return mixed false if query fails, resource or true otherwise
     */
    abstract public function exec($sql, $params = []);
    /**
     * Returns the number of rows affected by the last sql statement
     *
     * @return int the number of rows affected by the last
     * insert, update, delete
     */
    abstract public function affectedRows();
    /**
     * Returns the ID generated by the last insert statement
     * if table has an auto increment key column
     *
     * @param string $table_name of table of last insert
     * @return string  the ID of the insert
     */
    abstract public function insertID($table_name = "");
    /**
     * Returns the next row from the provided result set
     *
     * @param resource $result   result set reference of a query
     * @return array the next row from the result set as an
     *      associative array in the form column_name => value
     */
    abstract public function fetchArray($result);
    /**
     * Begins a transaction on the underlying database connection.
     */
    abstract public function beginTransaction();
    /**
     * Releases the cursor / result handle bound to $result.
     *
     * @param mixed $result result handle previously returned by
     *      execute(); driver-specific type
     */
    abstract public function closeCursor($result);
    /**
     * Commits the current transaction on the underlying database
     * connection.
     */
    abstract public function commit();
    /**
     * Used to escape strings before insertion in the
     * database to avoid SQL injection
     *
     * @param string $str  string to escape
     * @return string a string which is safe to insert into the db
     */
    abstract public function escapeString($str);
    /**
     * Executes the supplied sql command on the database, depending on debug
     * levels computes query statistics
     *
     * This method operates either query or data manipulation statements
     *
     * @param string $sql  SQL statement to execute
     * @param array $params bind_name => value values to interpolate into
     *      the $sql to be executes
     * @return mixed false if query fails, resource or true otherwise
     */
    public function execute($sql, $params = [])
    {
        if (C\QUERY_STATISTICS) {
            $query_info = [];
            $query_info['QUERY'] = htmlentities($sql);
            if ($params != []) {
                $query_info['QUERY'] .= "<br>" . print_r($params, true);
            }
            $start_time = microtime(true);
        }
        $result = $this->exec($sql, $params);
        if (C\QUERY_STATISTICS) {
            $query_info['ELAPSED_TIME'] = L\changeInMicrotime($start_time);
            $this->total_time += $query_info['ELAPSED_TIME'];
            $this->query_log[] = $query_info;
        }
        return $result;
    }
    /**
     * Recursively delete a file or directory
     *
     * @param string $dir file or directory name
     * @param boolean $delete_root_too Delete specified top directory as well
     */
    public function unlinkRecursive($dir, $delete_root_too = true)
    {
        $this->traverseDirectory($dir, C\NS_LIB . "deleteFileOrDir",
            $delete_root_too);
    }
    /**
     * Recursively chmod a directory to 0777
     *
     * @param string $dir Directory name
     * @param boolean $chmod_root_too chmod specified top-level directory as
     *  well
     */
    public function setWorldPermissionsRecursive($dir, $chmod_root_too = true)
    {
        $this->traverseDirectory($dir, C\NS_LIB . "setWorldPermissions",
            $chmod_root_too);
    }
    /**
     * Returns arrays of filesizes and file modifications times of files in
     * a directory
     *
     * @param string $dir directory to get filesizes for
     * @param bool $root_too whether to have an entry for the whole directory
     *      too
     * @return array of fileinfo information for each file in the folder in
     *      question
     */
    public function fileInfoRecursive($dir, $root_too = true)
    {
        return $this->traverseDirectory($dir, C\NS_LIB . "fileInfo", $root_too);
    }
    /**
     * Recursively copies a source directory to a destination directory
     *
     * It would have been cool to use traverseDirectory to implement this, but
     * it was a little bit too much of a stretch to shoehorn the code to match
     *
     * @param string $source_dir the name of the source directory
     * @param string $destination_dir the name of the destination directory
     */
    public function copyRecursive($source_dir, $destination_dir)
    {
        if (!empty($source_dir) && !empty($destination_dir) &&
            is_file($source_dir)) {
            copy($source_dir, $destination_dir);
            chmod($destination_dir, 0777);
            return;
        }
        if (empty($source_dir) || !$dh = @opendir($source_dir)) {
            return;
        }
        if (!file_exists($destination_dir)) {
            @mkdir($destination_dir);
            if (!file_exists($destination_dir)) {
                return;
            }
            chmod($destination_dir, 0777);
        }
        while (false !== ($obj = readdir($dh))) {
            if (($obj != '.') && ($obj != '..')) {
                if (is_dir($source_dir . '/' . $obj)) {
                    $this->copyRecursive($source_dir . '/' .
                        $obj, $destination_dir . '/' . $obj);
                } else {
                    copy($source_dir . '/' .
                        $obj, $destination_dir . '/' . $obj);
                    chmod($destination_dir . '/' . $obj, 0777);
                }
            }
        }
        closedir($dh);
    }
    /**
     * Recursively traverse a directory structure and call a callback function
     *
     * @param string $dir Directory name
     * @param function $callback Function to call as traverse structure
     * @param bool $root_too whether to have an apply the callback to the
     *      whole directory as well
     * @return array results computed by performing the traversal
     */
    public function traverseDirectory($dir, $callback, $root_too = true)
    {
        // single not directory case
        if (!is_dir($dir)) {
            return @$callback($dir);
        }
        // directory case
        $results = [];
        if (!$dh = @opendir($dir)) {
            return $results;
        }
        while (false !== ($obj = readdir($dh))) {
            if ($obj == '.' || $obj == '..') {
                continue;
            }
            if (is_dir($dir . '/' . $obj)) {
                $subdir_results =
                    $this->traverseDirectory($dir.'/'.$obj, $callback, true);
                $results = array_merge($results, $subdir_results);
            }
            $obj_results = @$callback($dir . '/' . $obj);
            if (is_array($obj_results)) {
                $results = array_merge($results, $obj_results);
            }
        }
        closedir($dh);
        if ($root_too) {
            $obj_results = @$callback($dir);
            if (is_array($obj_results)) {
                $results = array_merge($results, $obj_results);
            }
        }
        return $results;
    }
    /**
     * Returns string for given DBMS CREATE TABLE equivalent to auto_increment
     * (at least as far as Yioop requires).
     *
     * @param array $dbinfo contains strings DBMS, DB_HOST, DB_USER, DB_PASSWORD
     * @return string to achieve auto_increment function for the given DBMS
     */
    public function autoIncrement($dbinfo)
    {
        $auto_increment = "AUTOINCREMENT";
        $dbinfo['DBMS'] = strtolower($dbinfo['DBMS']);
        if (in_array($dbinfo['DBMS'], ["mysql"])) {
            $auto_increment = "AUTO_INCREMENT";
        }
        if (in_array($dbinfo['DBMS'], ["sqlite"])) {
            $auto_increment = "";
                /* in sqlite2 a primary key column will act
                   as auto_increment if don't give value
                 */
        }
        if (stristr($dbinfo['DBMS'], 'pdo')) {
            if (stristr($dbinfo['DB_HOST'], 'SQLITE')) {
                $auto_increment = "";
            } elseif (stristr($dbinfo['DB_HOST'], 'PGSQL')) { //POSTGRES
                $auto_increment = "";
            } elseif (stristr($dbinfo['DB_HOST'], 'OCI')) { // ORACLE
                $auto_increment = "DEFAULT SYS_GUID()";
            } elseif (stristr($dbinfo['DB_HOST'], 'IBM')) { //DB2
                $auto_increment = "GENERATED ALWAYS AS IDENTITY ".
                    "(START WITH 1 INCREMENT BY 1)";
            } elseif (stristr($dbinfo['DB_HOST'], 'DBLIB')) { //MS SQL
                $auto_increment = "IDENTITY (1,1)";
            }
        }
        return $auto_increment;
    }
    /**
     * Used to return the database column type for a column that can be
     * used to do autoincrementing. Usually this will just be INTEGER,
     * however, for Postgres is SERIAL.
     *
     * @param array $dbinfo containing fields for the current DBMS
     *     (pdo for Postgres) and DB_HOST (DSN connection string to database)
     * @return string what to use for serial data type
     */
    public function serialType($dbinfo)
    {
        $serial = "INTEGER"; //ONLY POSTGRES IS WEIRD
        if (strtolower($dbinfo['DBMS']) == 'pdo' &&
            stristr($dbinfo['DB_HOST'], 'PGSQL')) {
            $serial = "SERIAL"; //POSTGRES
        }
        return $serial;
    }
    /**
     * Used when creating databases to set what an integer is
     * If the DBMS is sqlite, there is not BIGINT type, only INTEGER
     * which can be 8 bytes
     * @param array $dbinfo containing fields for the current DBMS
     * @return string to use for column type corresponding to an 64bit int
     */
    public function integerType($dbinfo)
    {
        $integer = "BIGINT";
        $dbms = strtolower($dbinfo['DBMS']);
        if (in_array($dbms, ['sqlite']) || (stristr($dbinfo['DBMS'], 'pdo') &&
            stristr($dbinfo['DB_HOST'], 'SQLITE'))) {
            $integer = "INTEGER";
        }
        return $integer;
    }
    /**
     * Used when creating databases to set what a blob is
     * If the DBMS is Postgres, there is not BLOB type, only BYTEA
     *
     * @param array $dbinfo containing fields for the current DBMS
     * @return string to use for column type corresponding to a BLOB
     */
    public function blobType($dbinfo)
    {
        $blob = "BLOB";
        $dbms = strtolower($dbinfo['DBMS']);
        if (strtolower($dbinfo['DBMS']) == 'pdo' &&
            stristr($dbinfo['DB_HOST'], 'PGSQL')) {
            $blob = "BYTEA";
        }
        return $blob;
    }
    /**
     * Used when creating databases to set what type should be used for
     * wiki pages
     * @param array $dbinfo containing fields for the current DBMS
     * @return string to use for column type corresponding to wiki page
     */
    public function pageType($dbinfo)
    {
        $page_type = "VARCHAR(" . C\MAX_GROUP_PAGE_LEN . ")";
        $dbms = strtolower($dbinfo['DBMS']);
        if (in_array($dbms, ['mysql']) || (stristr($dbinfo['DBMS'], 'pdo') &&
            stristr($dbinfo['DB_HOST'], 'mysql'))) {
            $page_type = "MEDIUMTEXT";
        }
        return $page_type;
    }
    /**
     * Used to convert a sql string that does an insert statement
     * into a sql string where a duplicate insert
     * is ignored
     * @param string $sql original SQL insert statement
     * @param array $dbinfo containing fields for the current DBMS
     *     (pdo for Postgres) and DB_HOST (DSN connection string to database)
     * @return string what to use for insert ignore expression
     */
    public function insertIgnore($sql, $dbinfo = null)
    {
        $len_insert = strlen("INSERT");
        $sql = ltrim($sql);
        if (substr($sql, 0, $len_insert) != "INSERT") {
            return $sql;
        }
        $insert_ignore_sql = $sql;
        $lower_dbms = empty($dbinfo['DBMS']) ? (empty($this->to_upper_dbms) ?
            "pdo" : strtolower($this->to_upper_dbms) ) :
            strtolower($dbinfo['DBMS']);
        $db_host = $dbinfo['DB_HOST'] ?? $this->db_host;
        if (!C\nsdefined("OLD_DBMS") && (in_array($lower_dbms,
            ['pdo', 'pgsql']) || stristr($db_host, 'PGSQL') !== false)) {
            $insert_ignore_sql =  $sql . " ON CONFLICT DO NOTHING";
        } else  if (in_array($lower_dbms, ["mysql", "sqlite",
            "sqlite3"]) || stristr($db_host, 'SQLITE')) {
            $ignore = ($lower_dbms == "mysql") ? " IGNORE " : " OR IGNORE ";
            $insert_ignore_sql = "INSERT $ignore ". substr($sql, $len_insert);
        }
        return $insert_ignore_sql;
    }
    /**
     * How a daabase can perform LIMIT OFFSET queries is not standardized
     * between DBMS's. This method take a $start, a $num, and a $dbinfo
     * and returns the string that would restrict a query to show $num
     * many rows starting at $start. In the case that $num is not provided
     * then it returns the string represent returning the first $start many
     * rows. In the case that $dbinfo is not provided the current Yioop
     * default database is used.
     *
     * @param int $start starting row to return from if ($num == -1, i.e.,
     *      not supplied in the call) then this is is the number of rows desired
     * @param int $num number of rows to return
     * @param array $dbinfo containing fields for how to connect to a database
     * @return string what to use for for LIMIT OFFSET query with input $limit
     *     and $num for the DBMS in question
     */
    public function limitOffset($start, $num = -1, $dbinfo = null)
    {
        if (!$dbinfo) {
            $dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
                "DB_USER" => C\p('DB_USER'),
                    "DB_PASSWORD" => C\p('DB_PASSWORD'),
                "DB_NAME" => C\p('DB_NAME')];
        }
        $bounds = ($num == -1) ? "LIMIT $start" : "LIMIT $start , $num";
        if (strtolower($dbinfo['DBMS']) == 'pdo') {
            if (stristr($dbinfo['DB_HOST'], 'PGSQL')) {
                $bounds = (($num == -1)) ? "LIMIT $start" :
                "LIMIT $num OFFSET $start"; //POSTGRES
            }
        }
        return $bounds;
    }
    /**
     * Copies the contents of from_table in the first database into the
     * to a to_table of suitable schema in a second database. It assumes the
     * table exists in both databases, both have the same number of columns,
     * etc.
     *
     * @param string $from_table name of the table to be copied from
     * @param resource $from_dbm database resource for the from table
     * @param resource $to_table name of the table to be copied to
     * @param resource $to_dbm database resource for the to table
     * @return bool true when the source SELECT failed and the copy was
     *      skipped; void on the normal path (after the per-row insert
     *      loop)
     */
    public static function copyTable($from_table, $from_dbm, $to_table, $to_dbm)
    {
        $sql = "SELECT * FROM $from_table";
        if (($result = $from_dbm->execute($sql)) === false) {
            return true;
        }
        while ($row = $from_dbm->fetchArray($result)) {
            $statement = "INSERT INTO $to_table VALUES (";
            $comma ="";
            foreach ($row as $col => $value) {
                $statement .= $comma . " '" . $to_dbm->escapeString($value) .
                    "'";
                $comma = ",";
            }
            $statement .= ")";
            if (($to_dbm->execute($statement)) === false) {
                return false;
            }
        }
        return true;
    }
}
