TwoNav/system/Medoo.php

<?php
/**
* Medoo Database Framework.
* The Lightweight PHP Database Framework to Accelerate Development.
* @version 2.1.12
* @author Angel Lai
* @package Medoo
* @copyright Copyright 2024 Medoo Project,Angel Lai.
* @license https://opensource.org/licenses/MIT
* @link https://medoo.in
*/
declare(strict_types=1);
namespace Medoo;use PDO;use Exception;use PDOException;use PDOStatement;use InvalidArgumentException;class Raw
{public $map;public $value;}
class Medoo
{public $pdo;public $type;protected $prefix;protected $statement;protected $dsn;protected $logs = [];protected $logging = false;protected $testMode = false;public $queryString;protected $debugMode = false;protected $debugLogging = false;protected $debugLogs = [];protected $guid = 0;public $returnId = '';public $error = null;public $errorInfo = null;public function __construct(array $options)
{if (isset($options['prefix'])) {$this->prefix = $options['prefix'];}
if (isset($options['testMode']) && $options['testMode'] == true) {$this->testMode = true;return;}
$options['type'] = $options['type'] ?? $options['database_type'];if (!isset($options['pdo'])) {$options['database'] = $options['database'] ?? $options['database_name'];if (!isset($options['socket'])) {$options['host'] = $options['host'] ?? $options['server'] ?? false;}
}
if (isset($options['type'])) {$this->type = strtolower($options['type']);if ($this->type === 'mariadb') {$this->type = 'mysql';}
}
if (isset($options['logging']) && is_bool($options['logging'])) {$this->logging = $options['logging'];}
$option = $options['option'] ?? [];$commands = [];switch ($this->type) {case 'mysql':
$commands[] = 'SET SQL_MODE=ANSI_QUOTES';break;case 'mssql':
$commands[] = 'SET QUOTED_IDENTIFIER ON';$commands[] = 'SET ANSI_NULLS ON';break;}
if (isset($options['pdo'])) {if (!$options['pdo'] instanceof PDO) {throw new InvalidArgumentException('Invalid PDO object supplied.');}
$this->pdo = $options['pdo'];foreach ($commands as $value) {$this->pdo->exec($value);}
return;}
if (isset($options['dsn'])) {if (is_array($options['dsn']) && isset($options['dsn']['driver'])) {$attr = $options['dsn'];} else {throw new InvalidArgumentException('Invalid DSN option supplied.');}
} else {if (
isset($options['port']) &&
is_int($options['port'] * 1)
) {$port = $options['port'];}
$isPort = isset($port);switch ($this->type) {case 'mysql':
$attr = [
'driver' => 'mysql',
'dbname' => $options['database']
];if (isset($options['socket'])) {$attr['unix_socket'] = $options['socket'];} else {$attr['host'] = $options['host'];if ($isPort) {$attr['port'] = $port;}
}
break;case 'pgsql':
$attr = ['driver' => 'pgsql','host' => $options['host'],'dbname' => $options['database']];if ($isPort) {$attr['port'] = $port;}
break;case 'sybase':
$attr = ['driver' => 'dblib','host' => $options['host'],'dbname' => $options['database']];if ($isPort) {$attr['port'] = $port;}
break;case 'oracle':
$attr = [
'driver' => 'oci',
'dbname' => $options['host'] ?
'//' . $options['host'] . ($isPort ? ':' . $port : ':1521') . '/' . $options['database'] :
$options['database']
];if (isset($options['charset'])) {$attr['charset'] = $options['charset'];}
break;case 'mssql':
if (isset($options['driver']) && $options['driver'] === 'dblib') {$attr = [
'driver' => 'dblib',
'host' => $options['host'] . ($isPort ? ':' . $port : ''),
'dbname' => $options['database']
];if (isset($options['appname'])) {$attr['appname'] = $options['appname'];}
if (isset($options['charset'])) {$attr['charset'] = $options['charset'];}
} else {$attr = [
'driver' => 'sqlsrv',
'Server' => $options['host'] . ($isPort ? ',' . $port : ''),
'Database' => $options['database']
];if (isset($options['appname'])) {$attr['APP'] = $options['appname'];}
$config = ['ApplicationIntent','AttachDBFileName','Authentication','ColumnEncryption','ConnectionPooling','Encrypt','Failover_Partner','KeyStoreAuthentication','KeyStorePrincipalId','KeyStoreSecret','LoginTimeout','MultipleActiveResultSets','MultiSubnetFailover','Scrollable','TraceFile','TraceOn','TransactionIsolation','TransparentNetworkIPResolution','TrustServerCertificate','WSID',];foreach ($config as $value) {$keyname = strtolower(preg_replace(['/([a-z\d])([A-Z])/','/([^_])([A-Z][a-z])/'],'$1_$2',$value));if (isset($options[$keyname])) {$attr[$value] = $options[$keyname];}
}
}
break;case 'sqlite':
$attr = [
'driver' => 'sqlite',
$options['database']
];break;}
}
if (!isset($attr)) {throw new InvalidArgumentException('Incorrect connection options.');}
$driver = $attr['driver'];if (!in_array($driver,PDO::getAvailableDrivers())) {throw new InvalidArgumentException("Unsupported PDO driver: {$driver}.");}
unset($attr['driver']);$stack = [];foreach ($attr as $key => $value) {$stack[] = is_int($key) ? $value : $key . '=' . $value;}
$dsn = $driver . ':' . implode(';',$stack);if (
in_array($this->type,['mysql','pgsql','sybase','mssql']) &&
isset($options['charset'])
) {$commands[] = "SET NAMES '{$options['charset']}'" . (
$this->type === 'mysql' && isset($options['collation']) ?
" COLLATE '{$options['collation']}'" : ''
);}
$this->dsn = $dsn;try {$this->pdo = new PDO(
$dsn,
$options['username'] ?? null,
$options['password'] ?? null,
$option
);if (isset($options['error'])) {$this->pdo->setAttribute(
PDO::ATTR_ERRMODE,
in_array($options['error'],[
PDO::ERRMODE_SILENT,
PDO::ERRMODE_WARNING,
PDO::ERRMODE_EXCEPTION
]) ?
$options['error'] :
PDO::ERRMODE_SILENT
);}
if (isset($options['command']) && is_array($options['command'])) {$commands = array_merge($commands,$options['command']);}
foreach ($commands as $value) {$this->pdo->exec($value);}
} catch (PDOException $e) {throw new PDOException($e->getMessage());}
}
protected function mapKey(): string
{return ':MeD' . $this->guid++ . '_mK';}
public function query(string $statement,array $map = []): ?PDOStatement
{$raw = $this->raw($statement,$map);$statement = $this->buildRaw($raw,$map);return $this->exec($statement,$map);}
public function exec(string $statement,array $map = [],callable $callback = null): ?PDOStatement
{$this->statement = null;$this->errorInfo = null;$this->error = null;if ($this->testMode) {$this->queryString = $this->generate($statement,$map);return null;}
if ($this->debugMode) {if ($this->debugLogging) {$this->debugLogs[] = $this->generate($statement,$map);return null;}
echo $this->generate($statement,$map);$this->debugMode = false;return null;}
if ($this->logging) {$this->logs[] = [$statement,$map];} else {$this->logs = [[$statement,$map]];}
$statement = $this->pdo->prepare($statement);$errorInfo = $this->pdo->errorInfo();if ($errorInfo[0] !== '00000') {$this->errorInfo = $errorInfo;$this->error = $errorInfo[2];return null;}
foreach ($map as $key => $value) {$statement->bindValue($key,$value[0],$value[1]);}
if (is_callable($callback)) {$this->pdo->beginTransaction();$callback($statement);$execute = $statement->execute();$this->pdo->commit();} else {$execute = $statement->execute();}
$errorInfo = $statement->errorInfo();if ($errorInfo[0] !== '00000') {$this->errorInfo = $errorInfo;$this->error = $errorInfo[2];return null;}
if ($execute) {$this->statement = $statement;}
return $statement;}
protected function generate(string $statement,array $map): string
{$identifier = [
'mysql' => '`$1`',
'mssql' => '[$1]'
];$statement = preg_replace(
'/(?!\'[^\s]+\s?)"([\p{L}_][\p{L}\p{N}@$#\-_]*)"(?!\s?[^\s]+\')/u',
$identifier[$this->type] ?? '"$1"',
$statement
);foreach ($map as $key => $value) {if ($value[1] === PDO::PARAM_STR) {$replace = $this->quote("{$value[0]}");} elseif ($value[1] === PDO::PARAM_NULL) {$replace = 'NULL';} elseif ($value[1] === PDO::PARAM_LOB) {$replace = '{LOB_DATA}';} else {$replace = $value[0] . '';}
$statement = str_replace($key,$replace,$statement);}
return $statement;}
public static function raw(string $string,array $map = []): Raw
{$raw = new Raw();$raw->map = $map;$raw->value = $string;return $raw;}
protected function isRaw($object): bool
{return $object instanceof Raw;}
protected function buildRaw($raw,array &$map): ?string
{if (!$this->isRaw($raw)) {return null;}
$query = preg_replace_callback(
'/(([`\'])[\<]*?)?((FROM|TABLE|INTO|UPDATE|JOIN|TABLE IF EXISTS)\s*)?\<(([\p{L}_][\p{L}\p{N}@$#\-_]*)(\.[\p{L}_][\p{L}\p{N}@$#\-_]*)?)\>([^,]*?\2)?/',
function ($matches) {if (!empty($matches[2]) && isset($matches[8])) {return $matches[0];}
if (!empty($matches[4])) {return $matches[1] . $matches[4] . ' ' . $this->tableQuote($matches[5]);}
return $matches[1] . $this->columnQuote($matches[5]);},
$raw->value
);$rawMap = $raw->map;if (!empty($rawMap)) {foreach ($rawMap as $key => $value) {$map[$key] = $this->typeMap($value,gettype($value));}
}
return $query;}
public function quote(string $string): string
{if ($this->type === 'mysql') {return "'" . preg_replace(['/([\'"])/','/(\\\\\\\")/'],["\\\\\${1}",'\\\${1}'],$string) . "'";}
return "'" . preg_replace('/\'/','\'\'',$string) . "'";}
public function tableQuote(string $table): string
{if (preg_match('/^[\p{L}_][\p{L}\p{N}@$#\-_]*$/u',$table)) {return '"' . $this->prefix . $table . '"';}
throw new InvalidArgumentException("Incorrect table name: {$table}.");}
public function columnQuote(string $column): string
{if (preg_match('/^[\p{L}_][\p{L}\p{N}@$#\-_]*(\.?[\p{L}_][\p{L}\p{N}@$#\-_]*)?$/u',$column)) {return strpos($column,'.') !== false ?
'"' . $this->prefix . str_replace('.','"."',$column) . '"' :
'"' . $column . '"';}
throw new InvalidArgumentException("Incorrect column name: {$column}.");}
protected function typeMap($value,string $type): array
{$map = ['NULL' => PDO::PARAM_NULL,'integer' => PDO::PARAM_INT,'double' => PDO::PARAM_STR,'boolean' => PDO::PARAM_BOOL,'string' => PDO::PARAM_STR,'object' => PDO::PARAM_STR,'resource' => PDO::PARAM_LOB];if ($type === 'boolean') {$value = ($value ? '1' : '0');} elseif ($type === 'NULL') {$value = null;}
return [$value,$map[$type]];}
protected function columnPush(&$columns,array &$map,bool $root,bool $isJoin = false): string
{if ($columns === '*') {return $columns;}
$stack = [];$hasDistinct = false;if (is_string($columns)) {$columns = [$columns];}
foreach ($columns as $key => $value) {$isIntKey = is_int($key);$isArrayValue = is_array($value);if (!$isIntKey && $isArrayValue && $root && count(array_keys($columns)) === 1) {$stack[] = $this->columnQuote($key);$stack[] = $this->columnPush($value,$map,false,$isJoin);} elseif ($isArrayValue) {$stack[] = $this->columnPush($value,$map,false,$isJoin);} elseif (!$isIntKey && $raw = $this->buildRaw($value,$map)) {preg_match('/(?<column>[\p{L}_][\p{L}\p{N}@$#\-_\.]*)(\s*\[(?<type>(String|Bool|Int|Number))\])?/u',$key,$match);$stack[] = "{$raw} AS {$this->columnQuote($match['column'])}";} elseif ($isIntKey && is_string($value)) {if ($isJoin && strpos($value,'*') !== false) {throw new InvalidArgumentException('Cannot use table.* to select all columns while joining table.');}
preg_match('/(?<column>[\p{L}_][\p{L}\p{N}@$#\-_\.]*)(?:\s*\((?<alias>[\p{L}_][\p{L}\p{N}@$#\-_]*)\))?(?:\s*\[(?<type>(?:String|Bool|Int|Number|Object|JSON))\])?/u',$value,$match);$columnString = '';if (!empty($match['alias'])) {$columnString = "{$this->columnQuote($match['column'])} AS {$this->columnQuote($match['alias'])}";$columns[$key] = $match['alias'];if (!empty($match['type'])) {$columns[$key] .= ' [' . $match['type'] . ']';}
} else {$columnString = $this->columnQuote($match['column']);}
if (!$hasDistinct && strpos($value,'@') === 0) {$columnString = 'DISTINCT ' . $columnString;$hasDistinct = true;array_unshift($stack,$columnString);continue;}
$stack[] = $columnString;}
}
return implode(',',$stack);}
protected function dataImplode(array $data,array &$map,string $conjunctor): string
{$stack = [];foreach ($data as $key => $value) {$type = gettype($value);if (
$type === 'array' &&
preg_match("/^(AND|OR)(\s+#.*)?$/",$key,$relationMatch)
) {$stack[] = '(' . $this->dataImplode($value,$map,' ' . $relationMatch[1]) . ')';continue;}
$mapKey = $this->mapKey();$isIndex = is_int($key);preg_match(
'/([\p{L}_][\p{L}\p{N}@$#\-_\.]*)(\[(?<operator>.*)\])?([\p{L}_][\p{L}\p{N}@$#\-_\.]*)?/u',
$isIndex ? $value : $key,
$match
);$column = $this->columnQuote($match[1]);$operator = $match['operator'] ?? null;if ($isIndex && isset($match[4]) && in_array($operator,['>','>=','<','<=','=','!='])) {$stack[] = "{$column} {$operator} " . $this->columnQuote($match[4]);continue;}
if ($operator && $operator != '=') {if (in_array($operator,['>','>=','<','<='])) {$condition = "{$column} {$operator} ";if (is_numeric($value)) {$condition .= $mapKey;$map[$mapKey] = [$value,is_float($value) ? PDO::PARAM_STR : PDO::PARAM_INT];} elseif ($raw = $this->buildRaw($value,$map)) {$condition .= $raw;} else {$condition .= $mapKey;$map[$mapKey] = [$value,PDO::PARAM_STR];}
$stack[] = $condition;} elseif ($operator === '!') {switch ($type) {case 'NULL':
$stack[] = $column . ' IS NOT NULL';break;case 'array':
$values = [];foreach ($value as $index => $item) {if ($raw = $this->buildRaw($item,$map)) {$values[] = $raw;} else {$stackKey = $mapKey . $index . '_i';$values[] = $stackKey;$map[$stackKey] = $this->typeMap($item,gettype($item));}
}
$stack[] = $column . ' NOT IN (' . implode(',',$values) . ')';break;case 'object':
if ($raw = $this->buildRaw($value,$map)) {$stack[] = "{$column} != {$raw}";}
break;case 'integer':
case 'double':
case 'boolean':
case 'string':
$stack[] = "{$column} != {$mapKey}";$map[$mapKey] = $this->typeMap($value,$type);break;}
} elseif ($operator === '~' || $operator === '!~') {if ($type !== 'array') {$value = [$value];}
$connector = ' OR ';$data = array_values($value);if (is_array($data[0])) {if (isset($value['AND']) || isset($value['OR'])) {$connector = ' ' . array_keys($value)[0] . ' ';$value = $data[0];}
}
$likeClauses = [];foreach ($value as $index => $item) {$likeKey = "{$mapKey}_{$index}_i";$item = strval($item);if (!preg_match('/((?<!\\\)\[.+(?<!\\\)\]|(?<!\\\)[\*\?\!\%#^_]|%.+|.+%)/',$item)) {$item = '%' . $item . '%';}
$likeClauses[] = $column . ($operator === '!~' ? ' NOT' : '') . " LIKE {$likeKey}";$map[$likeKey] = [$item,PDO::PARAM_STR];}
$stack[] = '(' . implode($connector,$likeClauses) . ')';} elseif ($operator === '<>' || $operator === '><') {if ($type === 'array') {if ($operator === '><') {$column .= ' NOT';}
if ($this->isRaw($value[0]) && $this->isRaw($value[1])) {$stack[] = "({$column} BETWEEN {$this->buildRaw($value[0],$map)} AND {$this->buildRaw($value[1],$map)})";} else {$stack[] = "({$column} BETWEEN {$mapKey}a AND {$mapKey}b)";$dataType = (is_numeric($value[0]) && is_numeric($value[1])) ? PDO::PARAM_INT : PDO::PARAM_STR;$map[$mapKey . 'a'] = [$value[0],$dataType];$map[$mapKey . 'b'] = [$value[1],$dataType];}
}
} elseif ($operator === 'REGEXP') {$stack[] = "{$column} REGEXP {$mapKey}";$map[$mapKey] = [$value,PDO::PARAM_STR];} else {throw new InvalidArgumentException("Invalid operator [{$operator}] for column {$column} supplied.");}
continue;}
switch ($type) {case 'NULL':
$stack[] = $column . ' IS NULL';break;case 'array':
$values = [];foreach ($value as $index => $item) {if ($raw = $this->buildRaw($item,$map)) {$values[] = $raw;} else {$stackKey = $mapKey . $index . '_i';$values[] = $stackKey;$map[$stackKey] = $this->typeMap($item,gettype($item));}
}
$stack[] = $column . ' IN (' . implode(',',$values) . ')';break;case 'object':
if ($raw = $this->buildRaw($value,$map)) {$stack[] = "{$column} = {$raw}";}
break;case 'integer':
case 'double':
case 'boolean':
case 'string':
$stack[] = "{$column} = {$mapKey}";$map[$mapKey] = $this->typeMap($value,$type);break;}
}
return implode($conjunctor . ' ',$stack);}
protected function whereClause($where,array &$map): string
{$clause = '';if (is_array($where)) {$conditions = array_diff_key($where,array_flip(
['GROUP','ORDER','HAVING','LIMIT','LIKE','MATCH']
));if (!empty($conditions)) {$clause = ' WHERE ' . $this->dataImplode($conditions,$map,' AND');}
if (isset($where['MATCH']) && $this->type === 'mysql') {$match = $where['MATCH'];if (is_array($match) && isset($match['columns'],$match['keyword'])) {$mode = '';$options = ['natural' => 'IN NATURAL LANGUAGE MODE','natural+query' => 'IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION','boolean' => 'IN BOOLEAN MODE','query' => 'WITH QUERY EXPANSION'];if (isset($match['mode'],$options[$match['mode']])) {$mode = ' ' . $options[$match['mode']];}
$columns = implode(',',array_map([$this,'columnQuote'],$match['columns']));$mapKey = $this->mapKey();$map[$mapKey] = [$match['keyword'],PDO::PARAM_STR];$clause .= ($clause !== '' ? ' AND ' : ' WHERE') . ' MATCH (' . $columns . ') AGAINST (' . $mapKey . $mode . ')';}
}
if (isset($where['GROUP'])) {$group = $where['GROUP'];if (is_array($group)) {$stack = [];foreach ($group as $column => $value) {$stack[] = $this->columnQuote($value);}
$clause .= ' GROUP BY ' . implode(',',$stack);} elseif ($raw = $this->buildRaw($group,$map)) {$clause .= ' GROUP BY ' . $raw;} else {$clause .= ' GROUP BY ' . $this->columnQuote($group);}
}
if (isset($where['HAVING'])) {$having = $where['HAVING'];if ($raw = $this->buildRaw($having,$map)) {$clause .= ' HAVING ' . $raw;} else {$clause .= ' HAVING ' . $this->dataImplode($having,$map,' AND');}
}
if (isset($where['ORDER'])) {$order = $where['ORDER'];if (is_array($order)) {$stack = [];foreach ($order as $column => $value) {if (is_array($value)) {$valueStack = [];foreach ($value as $item) {$valueStack[] = is_int($item) ? $item : $this->quote($item);}
$valueString = implode(',',$valueStack);$stack[] = "FIELD({$this->columnQuote($column)},{$valueString})";} elseif ($value === 'ASC' || $value === 'DESC') {$stack[] = $this->columnQuote($column) . ' ' . $value;} elseif (is_int($column)) {$stack[] = $this->columnQuote($value);}
}
$clause .= ' ORDER BY ' . implode(',',$stack);} elseif ($raw = $this->buildRaw($order,$map)) {$clause .= ' ORDER BY ' . $raw;} else {$clause .= ' ORDER BY ' . $this->columnQuote($order);}
}
if (isset($where['LIMIT'])) {$limit = $where['LIMIT'];if (in_array($this->type,['oracle','mssql'])) {if ($this->type === 'mssql' && !isset($where['ORDER'])) {$clause .= ' ORDER BY (SELECT 0)';}
if (is_numeric($limit)) {$limit = [0,$limit];}
if (
is_array($limit) &&
is_numeric($limit[0]) &&
is_numeric($limit[1])
) {$clause .= " OFFSET {$limit[0]} ROWS FETCH NEXT {$limit[1]} ROWS ONLY";}
} else {if (is_numeric($limit)) {$clause .= ' LIMIT ' . $limit;} elseif (
is_array($limit) &&
is_numeric($limit[0]) &&
is_numeric($limit[1])
) {$clause .= " LIMIT {$limit[1]} OFFSET {$limit[0]}";}}}} elseif ($raw = $this->buildRaw($where,$map)) {$clause .= ' ' . $raw;}
return $clause;}
protected function selectContext(
string $table,
array &$map,
$join,
&$columns = null,
$where = null,
$columnFn = null
): string {preg_match('/(?<table>[\p{L}_][\p{L}\p{N}@$#\-_]*)\s*\((?<alias>[\p{L}_][\p{L}\p{N}@$#\-_]*)\)/u',$table,$tableMatch);if (isset($tableMatch['table'],$tableMatch['alias'])) {$table = $this->tableQuote($tableMatch['table']);$tableAlias = $this->tableQuote($tableMatch['alias']);$tableQuery = "{$table} AS {$tableAlias}";} else {$table = $this->tableQuote($table);$tableQuery = $table;}
$isJoin = $this->isJoin($join);if ($isJoin) {$tableQuery .= ' ' . $this->buildJoin($tableAlias ?? $table,$join,$map);} else {if (is_null($columns)) {if (
!is_null($where) ||
(is_array($join) && isset($columnFn))
) {$where = $join;$columns = null;} else {$where = null;$columns = $join;}
} else {$where = $columns;$columns = $join;}
}
if (isset($columnFn)) {if ($columnFn === 1) {$column = '1';if (is_null($where)) {$where = $columns;}
} elseif ($raw = $this->buildRaw($columnFn,$map)) {$column = $raw;} else {if (empty($columns) || $this->isRaw($columns)) {$columns = '*';$where = $join;}
$column = $columnFn . '(' . $this->columnPush($columns,$map,true) . ')';}
} else {$column = $this->columnPush($columns,$map,true,$isJoin);}
return 'SELECT ' . $column . ' FROM ' . $tableQuery . $this->whereClause($where,$map);}
protected function isJoin($join): bool
{if (!is_array($join)) {return false;}
$keys = array_keys($join);if (
isset($keys[0]) &&
is_string($keys[0]) &&
strpos($keys[0],'[') === 0
) {return true;}
return false;}
protected function buildJoin(string $table,array $join,array &$map): string
{$tableJoin = [];$type = ['>' => 'LEFT','<' => 'RIGHT','<>' => 'FULL','><' => 'INNER'];foreach ($join as $subtable => $relation) {preg_match('/(\[(?<join>\<\>?|\>\<?)\])?(?<table>[\p{L}_][\p{L}\p{N}@$#\-_]*)\s?(\((?<alias>[\p{L}_][\p{L}\p{N}@$#\-_]*)\))?/u',$subtable,$match);if ($match['join'] === '' || $match['table'] === '') {continue;}
if (is_string($relation)) {$relation = 'USING ("' . $relation . '")';} elseif (is_array($relation)) {if (isset($relation[0])) {$relation = 'USING ("' . implode('","',$relation) . '")';} else {$joins = [];foreach ($relation as $key => $value) {if ($key === 'AND' && is_array($value)) {$joins[] = $this->dataImplode($value,$map,' AND');continue;}
$joins[] = (
strpos($key,'.') > 0 ?
$this->columnQuote($key) :
$table . '.' . $this->columnQuote($key)
) .
' = ' .
$this->tableQuote($match['alias'] ?? $match['table']) . '.' . $this->columnQuote($value);}
$relation = 'ON ' . implode(' AND ',$joins);}
} elseif ($raw = $this->buildRaw($relation,$map)) {$relation = $raw;}
$tableName = $this->tableQuote($match['table']);if (isset($match['alias'])) {$tableName .= ' AS ' . $this->tableQuote($match['alias']);}
$tableJoin[] = $type[$match['join']] . " JOIN {$tableName} {$relation}";}
return implode(' ',$tableJoin);}
protected function columnMap($columns,array &$stack,bool $root): array
{if ($columns === '*') {return $stack;}
foreach ($columns as $key => $value) {if (is_int($key)) {preg_match('/([\p{L}_][\p{L}\p{N}@$#\-_]*\.)?(?<column>[\p{L}_][\p{L}\p{N}@$#\-_]*)(?:\s*\((?<alias>[\p{L}_][\p{L}\p{N}@$#\-_]*)\))?(?:\s*\[(?<type>(?:String|Bool|Int|Number|Object|JSON))\])?/u',$value,$keyMatch);$columnKey = !empty($keyMatch['alias']) ?
$keyMatch['alias'] :
$keyMatch['column'];$stack[$value] = isset($keyMatch['type']) ?
[$columnKey,$keyMatch['type']] :
[$columnKey];} elseif ($this->isRaw($value)) {preg_match('/([\p{L}_][\p{L}\p{N}@$#\-_]*\.)?(?<column>[\p{L}_][\p{L}\p{N}@$#\-_]*)(\s*\[(?<type>(String|Bool|Int|Number))\])?/u',$key,$keyMatch);$columnKey = $keyMatch['column'];$stack[$key] = isset($keyMatch['type']) ?
[$columnKey,$keyMatch['type']] :
[$columnKey];} elseif (!is_int($key) && is_array($value)) {if ($root && count(array_keys($columns)) === 1) {$stack[$key] = [$key,'String'];}
$this->columnMap($value,$stack,false);}
}
return $stack;}
protected function dataMap(
array $data,
array $columns,
array $columnMap,
array &$stack,
bool $root,
array &$result = null
): void {if ($root) {$columnsKey = array_keys($columns);if (count($columnsKey) === 1 && is_array($columns[$columnsKey[0]])) {$indexKey = array_keys($columns)[0];$dataKey = preg_replace("/^[\p{L}_][\p{L}\p{N}@$#\-_]*\./u",'',$indexKey);$currentStack = [];foreach ($data as $item) {$this->dataMap($data,$columns[$indexKey],$columnMap,$currentStack,false,$result);$index = $data[$dataKey];if (isset($result)) {$result[$index] = $currentStack;} else {$stack[$index] = $currentStack;}
}
} else {$currentStack = [];$this->dataMap($data,$columns,$columnMap,$currentStack,false,$result);if (isset($result)) {$result[] = $currentStack;} else {$stack = $currentStack;}
}
return;}
foreach ($columns as $key => $value) {$isRaw = $this->isRaw($value);if (is_int($key) || $isRaw) {$map = $columnMap[$isRaw ? $key : $value];$columnKey = $map[0];$item = $data[$columnKey];if (isset($map[1])) {if ($isRaw && in_array($map[1],['Object','JSON'])) {continue;}
if (is_null($item)) {$stack[$columnKey] = null;continue;}
switch ($map[1]) {case 'Number':
$stack[$columnKey] = (float) $item;break;case 'Int':
$stack[$columnKey] = (int) $item;break;case 'Bool':
$stack[$columnKey] = (bool) $item;break;case 'Object':
$stack[$columnKey] = unserialize($item);break;case 'JSON':
$stack[$columnKey] = json_decode($item,true);break;case 'String':
$stack[$columnKey] = (string) $item;break;}
} else {$stack[$columnKey] = $item;}} else {$currentStack = [];$this->dataMap($data,$value,$columnMap,$currentStack,false,$result);$stack[$key] = $currentStack;}}}
private function returningQuery($query,&$map,&$data): ?PDOStatement
{$returnColumns = array_map(
function ($value) {return $value[0];},
$data
);$query .= ' RETURNING ' .
implode(',',array_map([$this,'columnQuote'],$returnColumns)) .
' INTO ' .
implode(',',array_keys($data));return $this->exec($query,$map,function ($statement) use (&$data) {foreach ($data as $key => $return) {if (isset($return[3])) {$statement->bindParam($key,$data[$key][1],$return[2],$return[3]);} else {$statement->bindParam($key,$data[$key][1],$return[2]);}
}
});}
public function create(string $table,$columns,$options = null): ?PDOStatement
{$stack = [];$tableOption = '';$tableName = $this->tableQuote($table);foreach ($columns as $name => $definition) {if (is_int($name)) {$stack[] = preg_replace('/\<([\p{L}_][\p{L}\p{N}@$#\-_]*)\>/u','"$1"',$definition);} elseif (is_array($definition)) {$stack[] = $this->columnQuote($name) . ' ' . implode(' ',$definition);} elseif (is_string($definition)) {$stack[] = $this->columnQuote($name) . ' ' . $definition;}
}
if (is_array($options)) {$optionStack = [];foreach ($options as $key => $value) {if (is_string($value) || is_int($value)) {$optionStack[] = "{$key} = {$value}";}
}
$tableOption = ' ' . implode(',',$optionStack);} elseif (is_string($options)) {$tableOption = ' ' . $options;}
$command = 'CREATE TABLE';if (in_array($this->type,['mysql','pgsql','sqlite'])) {$command .= ' IF NOT EXISTS';}
return $this->exec("{$command} {$tableName} (" . implode(',',$stack) . "){$tableOption}");}
public function drop(string $table): ?PDOStatement
{return $this->exec('DROP TABLE IF EXISTS ' . $this->tableQuote($table));}
public function select(string $table,$join,$columns = null,$where = null): ?array
{$map = [];$result = [];$columnMap = [];$args = func_get_args();$lastArgs = $args[array_key_last($args)];$callback = is_callable($lastArgs) ? $lastArgs : null;$where = is_callable($where) ? null : $where;$columns = is_callable($columns) ? null : $columns;$column = $where === null ? $join : $columns;$isSingle = (is_string($column) && $column !== '*');$statement = $this->exec($this->selectContext($table,$map,$join,$columns,$where),$map);$this->columnMap($columns,$columnMap,true);if (!$this->statement) {return $result;}
if ($columns === '*') {if (isset($callback)) {while ($data = $statement->fetch(PDO::FETCH_ASSOC)) {$callback($data);}
return null;}
return $statement->fetchAll(PDO::FETCH_ASSOC);}
while ($data = $statement->fetch(PDO::FETCH_ASSOC)) {$currentStack = [];if (isset($callback)) {$this->dataMap($data,$columns,$columnMap,$currentStack,true);$callback(
$isSingle ?
$currentStack[$columnMap[$column][0]] :
$currentStack
);} else {$this->dataMap($data,$columns,$columnMap,$currentStack,true,$result);}
}
if (isset($callback)) {return null;}
if ($isSingle) {$singleResult = [];$resultKey = $columnMap[$column][0];foreach ($result as $item) {$singleResult[] = $item[$resultKey];}
return $singleResult;}
return $result;}
public function insert(string $table,array $values,string $primaryKey = null): ?PDOStatement
{$stack = [];$columns = [];$fields = [];$map = [];$returnings = [];if (!isset($values[0])) {$values = [$values];}
foreach ($values as $data) {foreach ($data as $key => $value) {$columns[] = $key;}
}
$columns = array_unique($columns);foreach ($values as $data) {$values = [];foreach ($columns as $key) {$value = $data[$key];$type = gettype($value);if ($this->type === 'oracle' && $type === 'resource') {$values[] = 'EMPTY_BLOB()';$returnings[$this->mapKey()] = [$key,$value,PDO::PARAM_LOB];continue;}
if ($raw = $this->buildRaw($data[$key],$map)) {$values[] = $raw;continue;}
$mapKey = $this->mapKey();$values[] = $mapKey;switch ($type) {case 'array':
$map[$mapKey] = [
strpos($key,'[JSON]') === strlen($key) - 6 ?
json_encode($value) :
serialize($value),
PDO::PARAM_STR
];break;case 'object':
$value = serialize($value);break;case 'NULL':case 'resource':case 'boolean':case 'integer':case 'double':case 'string':
$map[$mapKey] = $this->typeMap($value,$type);break;}
}
$stack[] = '(' . implode(',',$values) . ')';}
foreach ($columns as $key) {$fields[] = $this->columnQuote(preg_replace("/(\s*\[JSON\]$)/i",'',$key));}
$query = 'INSERT INTO ' . $this->tableQuote($table) . ' (' . implode(',',$fields) . ') VALUES ' . implode(',',$stack);if (
$this->type === 'oracle' && (!empty($returnings) || isset($primaryKey))
) {if ($primaryKey) {$returnings[':RETURNID'] = [$primaryKey,'',PDO::PARAM_INT,8];}
$statement = $this->returningQuery($query,$map,$returnings);if ($primaryKey) {$this->returnId = $returnings[':RETURNID'][1];}
return $statement;}
return $this->exec($query,$map);}
public function update(string $table,$data,$where = null): ?PDOStatement
{$fields = [];$map = [];$returnings = [];foreach ($data as $key => $value) {$column = $this->columnQuote(preg_replace("/(\s*\[(JSON|\+|\-|\*|\/)\]$)/",'',$key));$type = gettype($value);if ($this->type === 'oracle' && $type === 'resource') {$fields[] = "{$column} = EMPTY_BLOB()";$returnings[$this->mapKey()] = [$key,$value,PDO::PARAM_LOB];continue;}
if ($raw = $this->buildRaw($value,$map)) {$fields[] = "{$column} = {$raw}";continue;}
preg_match('/(?<column>[\p{L}_][\p{L}\p{N}@$#\-_]*)(\[(?<operator>\+|\-|\*|\/)\])?/u',$key,$match);if (isset($match['operator'])) {if (is_numeric($value)) {$fields[] = "{$column} = {$column} {$match['operator']} {$value}";}
} else {$mapKey = $this->mapKey();$fields[] = "{$column} = {$mapKey}";switch ($type) {case 'array':
$map[$mapKey] = [
strpos($key,'[JSON]') === strlen($key) - 6 ?
json_encode($value) :
serialize($value),
PDO::PARAM_STR
];break;case 'object':
$value = serialize($value);break;case 'NULL':case 'resource':case 'boolean':case 'integer':case 'double':case 'string':
$map[$mapKey] = $this->typeMap($value,$type);break;}}}
$query = 'UPDATE ' . $this->tableQuote($table) . ' SET ' . implode(',',$fields) . $this->whereClause($where,$map);if ($this->type === 'oracle' && !empty($returnings)) {return $this->returningQuery($query,$map,$returnings);}
return $this->exec($query,$map);}
public function delete(string $table,$where): ?PDOStatement
{$map = [];return $this->exec('DELETE FROM ' . $this->tableQuote($table) . $this->whereClause($where,$map),$map);}
public function replace(string $table,array $columns,$where = null): ?PDOStatement
{$map = [];$stack = [];foreach ($columns as $column => $replacements) {if (is_array($replacements)) {foreach ($replacements as $old => $new) {$mapKey = $this->mapKey();$columnName = $this->columnQuote($column);$stack[] = "{$columnName} = REPLACE({$columnName},{$mapKey}a,{$mapKey}b)";$map[$mapKey . 'a'] = [$old,PDO::PARAM_STR];$map[$mapKey . 'b'] = [$new,PDO::PARAM_STR];}
}
}
if (empty($stack)) {throw new InvalidArgumentException('Invalid columns supplied.');}
return $this->exec('UPDATE ' . $this->tableQuote($table) . ' SET ' . implode(',',$stack) . $this->whereClause($where,$map),$map);}
public function get(string $table,$join = null,$columns = null,$where = null)
{$map = [];$result = [];$columnMap = [];$currentStack = [];if ($where === null) {if ($this->isJoin($join)) {$where['LIMIT'] = 1;} else {$columns['LIMIT'] = 1;}
$column = $join;} else {$column = $columns;$where['LIMIT'] = 1;}
$isSingle = (is_string($column) && $column !== '*');$query = $this->exec($this->selectContext($table,$map,$join,$columns,$where),$map);if (!$this->statement) {return false;}
$data = $query->fetchAll(PDO::FETCH_ASSOC);if (isset($data[0])) {if ($column === '*') {return $data[0];}
$this->columnMap($columns,$columnMap,true);$this->dataMap($data[0],$columns,$columnMap,$currentStack,true,$result);if ($isSingle) {return $result[0][$columnMap[$column][0]];}
return $result[0];}
}
public function has(string $table,$join,$where = null): bool
{$map = [];$column = null;$query = $this->exec(
$this->type === 'mssql' ?
$this->selectContext($table,$map,$join,$column,$where,Medoo::raw('TOP 1 1')) :
'SELECT EXISTS(' . $this->selectContext($table,$map,$join,$column,$where,1) . ')',
$map
);if (!$this->statement) {return false;}
$result = $query->fetchColumn();return $result === '1' || $result === 1 || $result === true;}
public function rand(string $table,$join = null,$columns = null,$where = null): array
{$orderRaw = $this->raw(
$this->type === 'mysql' ? 'RAND()'
: ($this->type === 'mssql' ? 'NEWID()'
: 'RANDOM()')
);if ($where === null) {if ($this->isJoin($join)) {$where['ORDER'] = $orderRaw;} else {$columns['ORDER'] = $orderRaw;}
} else {$where['ORDER'] = $orderRaw;}
return $this->select($table,$join,$columns,$where);}
private function aggregate(string $type,string $table,$join = null,$column = null,$where = null): ?string
{$map = [];$query = $this->exec($this->selectContext($table,$map,$join,$column,$where,$type),$map);if (!$this->statement) {return null;}
return (string) $query->fetchColumn();}
public function count(string $table,$join = null,$column = null,$where = null): ?int
{return (int) $this->aggregate('COUNT',$table,$join,$column,$where);}
public function avg(string $table,$join,$column = null,$where = null): ?string
{return $this->aggregate('AVG',$table,$join,$column,$where);}
public function max(string $table,$join,$column = null,$where = null): ?string
{return $this->aggregate('MAX',$table,$join,$column,$where);}
public function min(string $table,$join,$column = null,$where = null): ?string
{return $this->aggregate('MIN',$table,$join,$column,$where);}
public function sum(string $table,$join,$column = null,$where = null): ?string
{return $this->aggregate('SUM',$table,$join,$column,$where);}
public function action(callable $actions): void
{if (is_callable($actions)) {$this->pdo->beginTransaction();try {$result = $actions($this);if ($result === false) {$this->pdo->rollBack();} else {$this->pdo->commit();}
} catch (Exception $e) {$this->pdo->rollBack();throw $e;}}}
public function id(string $name = null): ?string
{$type = $this->type;if ($type === 'oracle') {return $this->returnId;} elseif ($type === 'pgsql') {$id = $this->pdo->query('SELECT LASTVAL()')->fetchColumn();return (string) $id ?: null;}
return $this->pdo->lastInsertId($name);}
public function debug(): self
{$this->debugMode = true;return $this;}
public function beginDebug(): void
{$this->debugMode = true;$this->debugLogging = true;}
public function debugLog(): array
{$this->debugMode = false;$this->debugLogging = false;return $this->debugLogs;}
public function last(): ?string
{if (empty($this->logs)) {return null;}
$log = $this->logs[array_key_last($this->logs)];return $this->generate($log[0],$log[1]);}
public function log(): array
{return array_map(
function ($log) {return $this->generate($log[0],$log[1]);},
$this->logs
);}
public function info(): array
{$output = ['server' => 'SERVER_INFO','driver' => 'DRIVER_NAME','client' => 'CLIENT_VERSION','version' => 'SERVER_VERSION','connection' => 'CONNECTION_STATUS'];foreach ($output as $key => $value) {try {$output[$key] = $this->pdo->getAttribute(constant('PDO::ATTR_' . $value));} catch (PDOException $e) {$output[$key] = $e->getMessage();}}
$output['dsn'] = $this->dsn;return $output;}}