<?php
/**
* SeekQuarry/Yioop --
* Open Source Pure PHP Search Engine, Crawler, and Indexer
*
* Copyright (C) 2009 - 2022 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 - 2022
* @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
{
/**
* 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);
/**
*
*/
abstract public function closeCursor($result);
/**
* 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'] = $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 (is_file($source_dir)) {
copy($source_dir, $destination_dir);
chmod($destination_dir, 0777);
return;
}
if (!$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 ssql 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\DBMS, "DB_HOST" => C\DB_HOST,
"DB_USER" => C\DB_USER, "DB_PASSWORD" => C\DB_PASSWORD,
"DB_NAME" => C\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
*/
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;
}
}