2027 lines
47 KiB
PHP
2027 lines
47 KiB
PHP
<?php
|
|
/*!
|
|
* Medoo database framework
|
|
* https://medoo.in
|
|
* Version 1.7.10
|
|
*
|
|
* Copyright 2020, Angel Lai
|
|
* Released under the MIT license
|
|
*/
|
|
class Raw
|
|
{
|
|
public $map;
|
|
public $value;
|
|
}
|
|
|
|
class Medoo
|
|
{
|
|
public $pdo;
|
|
|
|
protected $type;
|
|
|
|
protected $prefix;
|
|
|
|
protected $statement;
|
|
|
|
protected $dsn;
|
|
|
|
protected $logs = [];
|
|
|
|
protected $logging = false;
|
|
|
|
protected $debug_mode = false;
|
|
|
|
protected $guid = 0;
|
|
|
|
protected $errorInfo = null;
|
|
|
|
// Dodajemy zmienną time_debug
|
|
protected $time_debug = false;
|
|
|
|
// Zmienne do przechowywania liczby zapytań i łącznego czasu
|
|
protected $query_count = 0;
|
|
protected $total_execution_time = 0.0;
|
|
|
|
// Dodajemy tablicę do przechowywania zapytań, ich czasów i liczby powtórzeń
|
|
protected $query_log = [];
|
|
|
|
// Redis
|
|
protected $redis; // Zmienna do przechowywania instancji Redis
|
|
protected $cache_ttl = 3600; // Domyślny czas życia cache (1 godzina)
|
|
|
|
public function __construct(array $options)
|
|
{
|
|
if (isset($options['time_debug']) && is_bool($options['time_debug']))
|
|
{
|
|
$this->time_debug = $options['time_debug'];
|
|
}
|
|
|
|
// Konfiguracja Redis
|
|
if (isset($options['redis']) && $options['redis'] instanceof Redis)
|
|
{
|
|
$this->redis = $options['redis'];
|
|
}
|
|
|
|
if (isset($options['cache_ttl']))
|
|
{
|
|
$this->cache_ttl = (int)$options['cache_ttl'];
|
|
}
|
|
|
|
if (isset($options['database_type']))
|
|
{
|
|
$this->type = strtolower($options['database_type']);
|
|
|
|
if ($this->type === 'mariadb')
|
|
{
|
|
$this->type = 'mysql';
|
|
}
|
|
}
|
|
|
|
if (isset($options['prefix']))
|
|
{
|
|
$this->prefix = $options['prefix'];
|
|
}
|
|
|
|
if (isset($options['logging']) && is_bool($options['logging']))
|
|
{
|
|
$this->logging = $options['logging'];
|
|
}
|
|
|
|
$option = isset($options['option']) ? $options['option'] : [];
|
|
$commands = (isset($options['command']) && is_array($options['command'])) ? $options['command'] : [];
|
|
|
|
switch ($this->type)
|
|
{
|
|
case 'mysql':
|
|
// Make MySQL using standard quoted identifier
|
|
$commands[] = 'SET SQL_MODE=ANSI_QUOTES';
|
|
|
|
break;
|
|
|
|
case 'mssql':
|
|
// Keep MSSQL QUOTED_IDENTIFIER is ON for standard quoting
|
|
$commands[] = 'SET QUOTED_IDENTIFIER ON';
|
|
|
|
// Make ANSI_NULLS is ON for NULL value
|
|
$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'];
|
|
}
|
|
|
|
$is_port = isset($port);
|
|
|
|
switch ($this->type)
|
|
{
|
|
case 'mysql':
|
|
$attr = [
|
|
'driver' => 'mysql',
|
|
'dbname' => $options['database_name']
|
|
];
|
|
|
|
if (isset($options['socket']))
|
|
{
|
|
$attr['unix_socket'] = $options['socket'];
|
|
}
|
|
else
|
|
{
|
|
$attr['host'] = $options['server'];
|
|
|
|
if ($is_port)
|
|
{
|
|
$attr['port'] = $port;
|
|
}
|
|
}
|
|
|
|
break;
|
|
|
|
case 'pgsql':
|
|
$attr = [
|
|
'driver' => 'pgsql',
|
|
'host' => $options['server'],
|
|
'dbname' => $options['database_name']
|
|
];
|
|
|
|
if ($is_port)
|
|
{
|
|
$attr['port'] = $port;
|
|
}
|
|
|
|
break;
|
|
|
|
case 'sybase':
|
|
$attr = [
|
|
'driver' => 'dblib',
|
|
'host' => $options['server'],
|
|
'dbname' => $options['database_name']
|
|
];
|
|
|
|
if ($is_port)
|
|
{
|
|
$attr['port'] = $port;
|
|
}
|
|
|
|
break;
|
|
|
|
case 'oracle':
|
|
$attr = [
|
|
'driver' => 'oci',
|
|
'dbname' => $options['server'] ?
|
|
'//' . $options['server'] . ($is_port ? ':' . $port : ':1521') . '/' . $options['database_name'] :
|
|
$options['database_name']
|
|
];
|
|
|
|
if (isset($options['charset']))
|
|
{
|
|
$attr['charset'] = $options['charset'];
|
|
}
|
|
|
|
break;
|
|
|
|
case 'mssql':
|
|
if (isset($options['driver']) && $options['driver'] === 'dblib')
|
|
{
|
|
$attr = [
|
|
'driver' => 'dblib',
|
|
'host' => $options['server'] . ($is_port ? ':' . $port : ''),
|
|
'dbname' => $options['database_name']
|
|
];
|
|
|
|
if (isset($options['appname']))
|
|
{
|
|
$attr['appname'] = $options['appname'];
|
|
}
|
|
|
|
if (isset($options['charset']))
|
|
{
|
|
$attr['charset'] = $options['charset'];
|
|
}
|
|
}
|
|
else
|
|
{
|
|
$attr = [
|
|
'driver' => 'sqlsrv',
|
|
'Server' => $options['server'] . ($is_port ? ',' . $port : ''),
|
|
'Database' => $options['database_name']
|
|
];
|
|
|
|
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_file']
|
|
];
|
|
|
|
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,
|
|
isset($options['username']) ? $options['username'] : null,
|
|
isset($options['password']) ? $options['password'] : null,
|
|
$option
|
|
);
|
|
|
|
foreach ($commands as $value)
|
|
{
|
|
$this->pdo->exec($value);
|
|
}
|
|
}
|
|
catch (PDOException $e)
|
|
{
|
|
throw new PDOException($e->getMessage());
|
|
}
|
|
}
|
|
|
|
public function query($query, $map = [])
|
|
{
|
|
$raw = $this->raw($query, $map);
|
|
|
|
$query = $this->buildRaw($raw, $map);
|
|
|
|
return $this->exec($query, $map);
|
|
}
|
|
|
|
// Funkcja do logowania podsumowania na końcu pliku index.php
|
|
public function logSummary()
|
|
{
|
|
global $database;
|
|
|
|
if ($this->time_debug)
|
|
{
|
|
// Tworzenie ścieżki do katalogu logs
|
|
$log_directory = $_SERVER['DOCUMENT_ROOT'] . '/logs';
|
|
|
|
// Plik logu na podstawie daty
|
|
$log_file = $log_directory . '/logs-db-' . date("Y-m-d") . '.log';
|
|
|
|
// Tworzenie wpisu z podsumowaniem
|
|
$summary_entry = "-----------------------------" . PHP_EOL;
|
|
$summary_entry .= "Total Queries: " . $this->query_count . PHP_EOL;
|
|
$summary_entry .= "Total Execution Time: " . number_format($this->total_execution_time, 5) . "s" . PHP_EOL;
|
|
|
|
$summary_entry .= "Repeated Queries:" . PHP_EOL;
|
|
$caller_summary = [];
|
|
$potential_optimization = [];
|
|
|
|
foreach ($this->query_log as $query => $info)
|
|
{
|
|
if ($info['count'] > 1)
|
|
{
|
|
$summary_entry .= "Query: " . $query . " | Count: " . $info['count'] . " | Total Time: " . number_format($info['total_time'], 5) . "s" . PHP_EOL;
|
|
}
|
|
|
|
// Dodajemy do podsumowania miejsc wywołań zapytań
|
|
foreach ($info['callers'] as $caller => $count)
|
|
{
|
|
if (!isset($caller_summary[$caller]))
|
|
{
|
|
$caller_summary[$caller] = 0;
|
|
}
|
|
$caller_summary[$caller] += $count;
|
|
}
|
|
|
|
// Sprawdzamy, czy zapytanie jest obciążające
|
|
if ($info['total_time'] > $database['long_query_time'])
|
|
{
|
|
$potential_optimization[] = [
|
|
'query' => $query,
|
|
'time' => number_format($info['total_time'], 5) . "s"
|
|
];
|
|
}
|
|
}
|
|
|
|
if (!empty($potential_optimization))
|
|
{
|
|
$summary_entry .= "-----------------------------" . PHP_EOL;
|
|
$summary_entry .= "Potentially Heavy Queries:" . PHP_EOL;
|
|
foreach ($potential_optimization as $query_info)
|
|
{
|
|
$summary_entry .= "Query: " . $query_info['query'] . " | Total Time: " . $query_info['time'] . PHP_EOL;
|
|
}
|
|
}
|
|
|
|
// Dodajemy do podsumowania miejsca wywołań zapytań
|
|
$summary_entry .= "-----------------------------" . PHP_EOL;
|
|
$summary_entry .= "Query Callers Summary:" . PHP_EOL;
|
|
foreach ($caller_summary as $caller => $count)
|
|
{
|
|
$summary_entry .= "Caller: " . $caller . " repeats " . $count . " times." . PHP_EOL;
|
|
}
|
|
|
|
$summary_entry .= "-----------------------------" . PHP_EOL;
|
|
|
|
// Zapis do pliku logu
|
|
file_put_contents($log_file, $summary_entry, FILE_APPEND);
|
|
}
|
|
}
|
|
|
|
|
|
protected function getCaller()
|
|
{
|
|
$backtrace = debug_backtrace();
|
|
$caller = isset($backtrace[2]) ? $backtrace[2] : null;
|
|
if ($caller)
|
|
{
|
|
return $caller['file'] . ':' . $caller['line'];
|
|
}
|
|
return 'Unknown';
|
|
}
|
|
|
|
public function exec($query, $map = [])
|
|
{
|
|
$this->statement = null;
|
|
|
|
if ($this->debug_mode)
|
|
{
|
|
$this->generate($query, $map);
|
|
|
|
$this->debug_mode = false;
|
|
|
|
return false;
|
|
}
|
|
|
|
if ($this->logging)
|
|
{
|
|
$this->logs[] = [$query, $map];
|
|
}
|
|
else
|
|
{
|
|
$this->logs = [[$query, $map]];
|
|
}
|
|
|
|
$statement = $this->pdo->prepare($query);
|
|
|
|
if (!$statement)
|
|
{
|
|
$this->errorInfo = $this->pdo->errorInfo();
|
|
$this->statement = null;
|
|
|
|
return false;
|
|
}
|
|
|
|
$this->statement = $statement;
|
|
|
|
foreach ($map as $key => $value)
|
|
{
|
|
$statement->bindValue($key, $value[0], $value[1]);
|
|
}
|
|
|
|
// Rozpocznij pomiar czasu
|
|
$start_time = microtime(true);
|
|
|
|
$execute = $statement->execute();
|
|
|
|
// Zakończ pomiar czasu
|
|
$end_time = microtime(true);
|
|
$execution_time = $end_time - $start_time;
|
|
|
|
// Aktualizacja liczby zapytań i łącznego czasu
|
|
$this->query_count++;
|
|
$this->total_execution_time += $execution_time;
|
|
|
|
// Logowanie zapytania w query_log
|
|
$generated_query = $this->generate($query, $map);
|
|
if (isset($this->query_log[$generated_query]))
|
|
{
|
|
$this->query_log[$generated_query]['count']++;
|
|
$this->query_log[$generated_query]['total_time'] += $execution_time;
|
|
}
|
|
else
|
|
{
|
|
$this->query_log[$generated_query] = [
|
|
'count' => 1,
|
|
'total_time' => $execution_time,
|
|
];
|
|
}
|
|
|
|
// Zapisz log z czasem i ewentualnym miejscem w kodzie
|
|
if ($this->time_debug)
|
|
{
|
|
$caller = $this->getCaller();
|
|
$caller = $this->getCaller();
|
|
if (isset($this->query_log[$generated_query]['callers'][$caller]))
|
|
{
|
|
$this->query_log[$generated_query]['callers'][$caller]++;
|
|
}
|
|
else
|
|
{
|
|
$this->query_log[$generated_query]['callers'][$caller] = 1;
|
|
}
|
|
$log_entry = "[" . date("Y-m-d H:i:s") . "] Query: " . $generated_query .
|
|
" | Time: " . number_format($execution_time, 5) . "s" .
|
|
" | Caller: " . $caller . PHP_EOL;
|
|
|
|
// Tworzenie ścieżki do katalogu logs
|
|
$log_directory = $_SERVER['DOCUMENT_ROOT'] . '/logs';
|
|
|
|
// Upewnij się, że katalog logs istnieje
|
|
if (!is_dir($log_directory))
|
|
{
|
|
mkdir($log_directory, 0777, true);
|
|
}
|
|
|
|
// Plik logu na podstawie daty
|
|
// $log_file = $log_directory . '/logs-db-' . date("Y-m-d") . '.log';
|
|
|
|
// Zapis do pliku logu
|
|
// file_put_contents($log_file, $log_entry, FILE_APPEND);
|
|
}
|
|
|
|
$this->errorInfo = $statement->errorInfo();
|
|
|
|
if (!$execute)
|
|
{
|
|
$this->statement = null;
|
|
}
|
|
|
|
return $statement;
|
|
}
|
|
|
|
protected function generate($query, $map)
|
|
{
|
|
$identifier = [
|
|
'mysql' => '`$1`',
|
|
'mssql' => '[$1]'
|
|
];
|
|
|
|
$query = preg_replace(
|
|
'/"([a-zA-Z0-9_]+)"/i',
|
|
isset($identifier[$this->type]) ? $identifier[$this->type] : '"$1"',
|
|
$query
|
|
);
|
|
|
|
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];
|
|
}
|
|
|
|
$query = str_replace($key, $replace, $query);
|
|
}
|
|
|
|
return $query;
|
|
}
|
|
|
|
public static function raw($string, $map = [])
|
|
{
|
|
$raw = new Raw();
|
|
|
|
$raw->map = $map;
|
|
$raw->value = $string;
|
|
|
|
return $raw;
|
|
}
|
|
|
|
protected function isRaw($object)
|
|
{
|
|
return $object instanceof Raw;
|
|
}
|
|
|
|
protected function buildRaw($raw, &$map)
|
|
{
|
|
if (!$this->isRaw($raw))
|
|
{
|
|
return false;
|
|
}
|
|
|
|
$query = preg_replace_callback(
|
|
'/(([`\']).*?)?((FROM|TABLE|INTO|UPDATE|JOIN)\s*)?\<(([a-zA-Z0-9_]+)(\.[a-zA-Z0-9_]+)?)\>(.*?\2)?/i',
|
|
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
|
|
);
|
|
|
|
$raw_map = $raw->map;
|
|
|
|
if (!empty($raw_map))
|
|
{
|
|
foreach ($raw_map as $key => $value)
|
|
{
|
|
$map[$key] = $this->typeMap($value, gettype($value));
|
|
}
|
|
}
|
|
|
|
return $query;
|
|
}
|
|
|
|
public function quote($string)
|
|
{
|
|
return $this->pdo->quote($string);
|
|
}
|
|
|
|
protected function tableQuote($table)
|
|
{
|
|
if (!preg_match('/^[a-zA-Z0-9_]+$/i', $table))
|
|
{
|
|
throw new InvalidArgumentException("Incorrect table name \"$table\"");
|
|
}
|
|
|
|
return '"' . $this->prefix . $table . '"';
|
|
}
|
|
|
|
protected function mapKey()
|
|
{
|
|
return ':MeDoO_' . $this->guid++ . '_mEdOo';
|
|
}
|
|
|
|
protected function typeMap($value, $type)
|
|
{
|
|
$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 columnQuote($string)
|
|
{
|
|
if (!preg_match('/^[a-zA-Z0-9_]+(\.?[a-zA-Z0-9_]+)?$/i', $string))
|
|
{
|
|
throw new InvalidArgumentException("Incorrect column name \"$string\"");
|
|
}
|
|
|
|
if (strpos($string, '.') !== false)
|
|
{
|
|
return '"' . $this->prefix . str_replace('.', '"."', $string) . '"';
|
|
}
|
|
|
|
return '"' . $string . '"';
|
|
}
|
|
|
|
protected function columnPush(&$columns, &$map, $root, $is_join = false)
|
|
{
|
|
if ($columns === '*')
|
|
{
|
|
return $columns;
|
|
}
|
|
|
|
$stack = [];
|
|
|
|
if (is_string($columns))
|
|
{
|
|
$columns = [$columns];
|
|
}
|
|
|
|
foreach ($columns as $key => $value)
|
|
{
|
|
if (!is_int($key) && is_array($value) && $root && count(array_keys($columns)) === 1)
|
|
{
|
|
$stack[] = $this->columnQuote($key);
|
|
|
|
$stack[] = $this->columnPush($value, $map, false, $is_join);
|
|
}
|
|
elseif (is_array($value))
|
|
{
|
|
$stack[] = $this->columnPush($value, $map, false, $is_join);
|
|
}
|
|
elseif (!is_int($key) && $raw = $this->buildRaw($value, $map))
|
|
{
|
|
preg_match('/(?<column>[a-zA-Z0-9_\.]+)(\s*\[(?<type>(String|Bool|Int|Number))\])?/i', $key, $match);
|
|
|
|
$stack[] = $raw . ' AS ' . $this->columnQuote($match['column']);
|
|
}
|
|
elseif (is_int($key) && is_string($value))
|
|
{
|
|
if ($is_join && strpos($value, '*') !== false)
|
|
{
|
|
throw new InvalidArgumentException('Cannot use table.* to select all columns while joining table');
|
|
}
|
|
|
|
preg_match('/(?<column>[a-zA-Z0-9_\.]+)(?:\s*\((?<alias>[a-zA-Z0-9_]+)\))?(?:\s*\[(?<type>(?:String|Bool|Int|Number|Object|JSON))\])?/i', $value, $match);
|
|
|
|
if (!empty($match['alias']))
|
|
{
|
|
$stack[] = $this->columnQuote($match['column']) . ' AS ' . $this->columnQuote($match['alias']);
|
|
|
|
$columns[$key] = $match['alias'];
|
|
|
|
if (!empty($match['type']))
|
|
{
|
|
$columns[$key] .= ' [' . $match['type'] . ']';
|
|
}
|
|
}
|
|
else
|
|
{
|
|
$stack[] = $this->columnQuote($match['column']);
|
|
}
|
|
}
|
|
}
|
|
|
|
return implode(',', $stack);
|
|
}
|
|
|
|
protected function arrayQuote($array)
|
|
{
|
|
$stack = [];
|
|
|
|
foreach ($array as $value)
|
|
{
|
|
$stack[] = is_int($value) ? $value : $this->pdo->quote($value);
|
|
}
|
|
|
|
return implode(',', $stack);
|
|
}
|
|
|
|
protected function innerConjunct($data, $map, $conjunctor, $outer_conjunctor)
|
|
{
|
|
$stack = [];
|
|
|
|
foreach ($data as $value)
|
|
{
|
|
$stack[] = '(' . $this->dataImplode($value, $map, $conjunctor) . ')';
|
|
}
|
|
|
|
return implode($outer_conjunctor . ' ', $stack);
|
|
}
|
|
|
|
protected function dataImplode($data, &$map, $conjunctor)
|
|
{
|
|
$stack = [];
|
|
|
|
foreach ($data as $key => $value)
|
|
{
|
|
$type = gettype($value);
|
|
|
|
if (
|
|
$type === 'array' &&
|
|
preg_match("/^(AND|OR)(\s+#.*)?$/", $key, $relation_match)
|
|
)
|
|
{
|
|
$relationship = $relation_match[1];
|
|
|
|
$stack[] = $value !== array_keys(array_keys($value)) ?
|
|
'(' . $this->dataImplode($value, $map, ' ' . $relationship) . ')' :
|
|
'(' . $this->innerConjunct($value, $map, ' ' . $relationship, $conjunctor) . ')';
|
|
|
|
continue;
|
|
}
|
|
|
|
$map_key = $this->mapKey();
|
|
|
|
if (
|
|
is_int($key) &&
|
|
preg_match('/([a-zA-Z0-9_\.]+)\[(?<operator>\>\=?|\<\=?|\!?\=)\]([a-zA-Z0-9_\.]+)/i', $value, $match)
|
|
)
|
|
{
|
|
$stack[] = $this->columnQuote($match[1]) . ' ' . $match['operator'] . ' ' . $this->columnQuote($match[3]);
|
|
}
|
|
else
|
|
{
|
|
preg_match('/([a-zA-Z0-9_\.]+)(\[(?<operator>\>\=?|\<\=?|\!|\<\>|\>\<|\!?~|REGEXP)\])?/i', $key, $match);
|
|
$column = $this->columnQuote($match[1]);
|
|
|
|
if (isset($match['operator']))
|
|
{
|
|
$operator = $match['operator'];
|
|
|
|
if (in_array($operator, ['>', '>=', '<', '<=']))
|
|
{
|
|
$condition = $column . ' ' . $operator . ' ';
|
|
|
|
if (is_numeric($value))
|
|
{
|
|
$condition .= $map_key;
|
|
$map[$map_key] = [$value, is_float($value) ? PDO::PARAM_STR : PDO::PARAM_INT];
|
|
}
|
|
elseif ($raw = $this->buildRaw($value, $map))
|
|
{
|
|
$condition .= $raw;
|
|
}
|
|
else
|
|
{
|
|
$condition .= $map_key;
|
|
$map[$map_key] = [$value, PDO::PARAM_STR];
|
|
}
|
|
|
|
$stack[] = $condition;
|
|
}
|
|
elseif ($operator === '!')
|
|
{
|
|
switch ($type)
|
|
{
|
|
case 'NULL':
|
|
$stack[] = $column . ' IS NOT NULL';
|
|
break;
|
|
|
|
case 'array':
|
|
$placeholders = [];
|
|
|
|
foreach ($value as $index => $item)
|
|
{
|
|
$stack_key = $map_key . $index . '_i';
|
|
|
|
$placeholders[] = $stack_key;
|
|
$map[$stack_key] = $this->typeMap($item, gettype($item));
|
|
}
|
|
|
|
$stack[] = $column . ' NOT IN (' . implode(', ', $placeholders) . ')';
|
|
break;
|
|
|
|
case 'object':
|
|
if ($raw = $this->buildRaw($value, $map))
|
|
{
|
|
$stack[] = $column . ' != ' . $raw;
|
|
}
|
|
break;
|
|
|
|
case 'integer':
|
|
case 'double':
|
|
case 'boolean':
|
|
case 'string':
|
|
$stack[] = $column . ' != ' . $map_key;
|
|
$map[$map_key] = $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];
|
|
}
|
|
}
|
|
|
|
$like_clauses = [];
|
|
|
|
foreach ($value as $index => $item)
|
|
{
|
|
$item = strval($item);
|
|
|
|
if (!preg_match('/(\[.+\]|[\*\?\!\%#^-_]|%.+|.+%)/', $item))
|
|
{
|
|
$item = '%' . $item . '%';
|
|
}
|
|
|
|
$like_clauses[] = $column . ($operator === '!~' ? ' NOT' : '') . ' LIKE ' . $map_key . 'L' . $index;
|
|
$map[$map_key . 'L' . $index] = [$item, PDO::PARAM_STR];
|
|
}
|
|
|
|
$stack[] = '(' . implode($connector, $like_clauses) . ')';
|
|
}
|
|
elseif ($operator === '<>' || $operator === '><')
|
|
{
|
|
if ($type === 'array')
|
|
{
|
|
if ($operator === '><')
|
|
{
|
|
$column .= ' NOT';
|
|
}
|
|
|
|
$stack[] = '(' . $column . ' BETWEEN ' . $map_key . 'a AND ' . $map_key . 'b)';
|
|
|
|
$data_type = (is_numeric($value[0]) && is_numeric($value[1])) ? PDO::PARAM_INT : PDO::PARAM_STR;
|
|
|
|
$map[$map_key . 'a'] = [$value[0], $data_type];
|
|
$map[$map_key . 'b'] = [$value[1], $data_type];
|
|
}
|
|
}
|
|
elseif ($operator === 'REGEXP')
|
|
{
|
|
$stack[] = $column . ' REGEXP ' . $map_key;
|
|
$map[$map_key] = [$value, PDO::PARAM_STR];
|
|
}
|
|
}
|
|
else
|
|
{
|
|
switch ($type)
|
|
{
|
|
case 'NULL':
|
|
$stack[] = $column . ' IS NULL';
|
|
break;
|
|
|
|
case 'array':
|
|
$placeholders = [];
|
|
|
|
foreach ($value as $index => $item)
|
|
{
|
|
$stack_key = $map_key . $index . '_i';
|
|
|
|
$placeholders[] = $stack_key;
|
|
$map[$stack_key] = $this->typeMap($item, gettype($item));
|
|
}
|
|
|
|
$stack[] = $column . ' IN (' . implode(', ', $placeholders) . ')';
|
|
break;
|
|
|
|
case 'object':
|
|
if ($raw = $this->buildRaw($value, $map))
|
|
{
|
|
$stack[] = $column . ' = ' . $raw;
|
|
}
|
|
break;
|
|
|
|
case 'integer':
|
|
case 'double':
|
|
case 'boolean':
|
|
case 'string':
|
|
$stack[] = $column . ' = ' . $map_key;
|
|
$map[$map_key] = $this->typeMap($value, $type);
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
return implode($conjunctor . ' ', $stack);
|
|
}
|
|
|
|
protected function whereClause($where, &$map)
|
|
{
|
|
$where_clause = '';
|
|
|
|
if (is_array($where))
|
|
{
|
|
$where_keys = array_keys($where);
|
|
|
|
$conditions = array_diff_key($where, array_flip(
|
|
['GROUP', 'ORDER', 'HAVING', 'LIMIT', 'LIKE', 'MATCH']
|
|
));
|
|
|
|
if (!empty($conditions))
|
|
{
|
|
$where_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 = '';
|
|
|
|
$mode_array = [
|
|
'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'], $mode_array[$MATCH['mode']]))
|
|
{
|
|
$mode = ' ' . $mode_array[$MATCH['mode']];
|
|
}
|
|
|
|
$columns = implode(', ', array_map([$this, 'columnQuote'], $MATCH['columns']));
|
|
$map_key = $this->mapKey();
|
|
$map[$map_key] = [$MATCH['keyword'], PDO::PARAM_STR];
|
|
|
|
$where_clause .= ($where_clause !== '' ? ' AND ' : ' WHERE') . ' MATCH (' . $columns . ') AGAINST (' . $map_key . $mode . ')';
|
|
}
|
|
}
|
|
|
|
if (isset($where['GROUP']))
|
|
{
|
|
$GROUP = $where['GROUP'];
|
|
|
|
if (is_array($GROUP))
|
|
{
|
|
$stack = [];
|
|
|
|
foreach ($GROUP as $column => $value)
|
|
{
|
|
$stack[] = $this->columnQuote($value);
|
|
}
|
|
|
|
$where_clause .= ' GROUP BY ' . implode(',', $stack);
|
|
}
|
|
elseif ($raw = $this->buildRaw($GROUP, $map))
|
|
{
|
|
$where_clause .= ' GROUP BY ' . $raw;
|
|
}
|
|
else
|
|
{
|
|
$where_clause .= ' GROUP BY ' . $this->columnQuote($GROUP);
|
|
}
|
|
|
|
if (isset($where['HAVING']))
|
|
{
|
|
if ($raw = $this->buildRaw($where['HAVING'], $map))
|
|
{
|
|
$where_clause .= ' HAVING ' . $raw;
|
|
}
|
|
else
|
|
{
|
|
$where_clause .= ' HAVING ' . $this->dataImplode($where['HAVING'], $map, ' AND');
|
|
}
|
|
}
|
|
}
|
|
|
|
if (isset($where['ORDER']))
|
|
{
|
|
$ORDER = $where['ORDER'];
|
|
|
|
if (is_array($ORDER))
|
|
{
|
|
$stack = [];
|
|
|
|
foreach ($ORDER as $column => $value)
|
|
{
|
|
if (is_array($value))
|
|
{
|
|
$stack[] = 'FIELD(' . $this->columnQuote($column) . ', ' . $this->arrayQuote($value) . ')';
|
|
}
|
|
elseif ($value === 'ASC' || $value === 'DESC')
|
|
{
|
|
$stack[] = $this->columnQuote($column) . ' ' . $value;
|
|
}
|
|
elseif (is_int($column))
|
|
{
|
|
$stack[] = $this->columnQuote($value);
|
|
}
|
|
}
|
|
|
|
$where_clause .= ' ORDER BY ' . implode(',', $stack);
|
|
}
|
|
elseif ($raw = $this->buildRaw($ORDER, $map))
|
|
{
|
|
$where_clause .= ' ORDER BY ' . $raw;
|
|
}
|
|
else
|
|
{
|
|
$where_clause .= ' ORDER BY ' . $this->columnQuote($ORDER);
|
|
}
|
|
|
|
if (
|
|
isset($where['LIMIT']) &&
|
|
in_array($this->type, ['oracle', 'mssql'])
|
|
)
|
|
{
|
|
$LIMIT = $where['LIMIT'];
|
|
|
|
if (is_numeric($LIMIT))
|
|
{
|
|
$LIMIT = [0, $LIMIT];
|
|
}
|
|
|
|
if (
|
|
is_array($LIMIT) &&
|
|
is_numeric($LIMIT[0]) &&
|
|
is_numeric($LIMIT[1])
|
|
)
|
|
{
|
|
$where_clause .= ' OFFSET ' . $LIMIT[0] . ' ROWS FETCH NEXT ' . $LIMIT[1] . ' ROWS ONLY';
|
|
}
|
|
}
|
|
}
|
|
|
|
if (isset($where['LIMIT']) && !in_array($this->type, ['oracle', 'mssql']))
|
|
{
|
|
$LIMIT = $where['LIMIT'];
|
|
|
|
if (is_numeric($LIMIT))
|
|
{
|
|
$where_clause .= ' LIMIT ' . $LIMIT;
|
|
}
|
|
elseif (
|
|
is_array($LIMIT) &&
|
|
is_numeric($LIMIT[0]) &&
|
|
is_numeric($LIMIT[1])
|
|
)
|
|
{
|
|
$where_clause .= ' LIMIT ' . $LIMIT[1] . ' OFFSET ' . $LIMIT[0];
|
|
}
|
|
}
|
|
}
|
|
elseif ($raw = $this->buildRaw($where, $map))
|
|
{
|
|
$where_clause .= ' ' . $raw;
|
|
}
|
|
|
|
return $where_clause;
|
|
}
|
|
|
|
protected function selectContext($table, &$map, $join, &$columns = null, $where = null, $column_fn = null)
|
|
{
|
|
preg_match('/(?<table>[a-zA-Z0-9_]+)\s*\((?<alias>[a-zA-Z0-9_]+)\)/i', $table, $table_match);
|
|
|
|
if (isset($table_match['table'], $table_match['alias']))
|
|
{
|
|
$table = $this->tableQuote($table_match['table']);
|
|
|
|
$table_query = $table . ' AS ' . $this->tableQuote($table_match['alias']);
|
|
}
|
|
else
|
|
{
|
|
$table = $this->tableQuote($table);
|
|
|
|
$table_query = $table;
|
|
}
|
|
|
|
$is_join = false;
|
|
$join_key = is_array($join) ? array_keys($join) : null;
|
|
|
|
if (
|
|
isset($join_key[0]) &&
|
|
strpos($join_key[0], '[') === 0
|
|
)
|
|
{
|
|
$is_join = true;
|
|
$table_query .= ' ' . $this->buildJoin($table, $join);
|
|
}
|
|
else
|
|
{
|
|
if (is_null($columns))
|
|
{
|
|
if (
|
|
!is_null($where) ||
|
|
(is_array($join) && isset($column_fn))
|
|
)
|
|
{
|
|
$where = $join;
|
|
$columns = null;
|
|
}
|
|
else
|
|
{
|
|
$where = null;
|
|
$columns = $join;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
$where = $columns;
|
|
$columns = $join;
|
|
}
|
|
}
|
|
|
|
if (isset($column_fn))
|
|
{
|
|
if ($column_fn === 1)
|
|
{
|
|
$column = '1';
|
|
|
|
if (is_null($where))
|
|
{
|
|
$where = $columns;
|
|
}
|
|
}
|
|
elseif ($raw = $this->buildRaw($column_fn, $map))
|
|
{
|
|
$column = $raw;
|
|
}
|
|
else
|
|
{
|
|
if (empty($columns) || $this->isRaw($columns))
|
|
{
|
|
$columns = '*';
|
|
$where = $join;
|
|
}
|
|
|
|
$column = $column_fn . '(' . $this->columnPush($columns, $map, true) . ')';
|
|
}
|
|
}
|
|
else
|
|
{
|
|
$column = $this->columnPush($columns, $map, true, $is_join);
|
|
}
|
|
|
|
return 'SELECT ' . $column . ' FROM ' . $table_query . $this->whereClause($where, $map);
|
|
}
|
|
|
|
protected function buildJoin($table, $join)
|
|
{
|
|
$table_join = [];
|
|
|
|
$join_array = [
|
|
'>' => 'LEFT',
|
|
'<' => 'RIGHT',
|
|
'<>' => 'FULL',
|
|
'><' => 'INNER'
|
|
];
|
|
|
|
foreach ($join as $sub_table => $relation)
|
|
{
|
|
preg_match('/(\[(?<join>\<\>?|\>\<?)\])?(?<table>[a-zA-Z0-9_]+)\s?(\((?<alias>[a-zA-Z0-9_]+)\))?/', $sub_table, $match);
|
|
|
|
if ($match['join'] !== '' && $match['table'] !== '')
|
|
{
|
|
if (is_string($relation))
|
|
{
|
|
$relation = 'USING ("' . $relation . '")';
|
|
}
|
|
|
|
if (is_array($relation))
|
|
{
|
|
// For ['column1', 'column2']
|
|
if (isset($relation[0]))
|
|
{
|
|
$relation = 'USING ("' . implode('", "', $relation) . '")';
|
|
}
|
|
else
|
|
{
|
|
$joins = [];
|
|
|
|
foreach ($relation as $key => $value)
|
|
{
|
|
$joins[] = (
|
|
strpos($key, '.') > 0 ?
|
|
// For ['tableB.column' => 'column']
|
|
$this->columnQuote($key) :
|
|
|
|
// For ['column1' => 'column2']
|
|
$table . '."' . $key . '"'
|
|
) .
|
|
' = ' .
|
|
$this->tableQuote(isset($match['alias']) ? $match['alias'] : $match['table']) . '."' . $value . '"';
|
|
}
|
|
|
|
$relation = 'ON ' . implode(' AND ', $joins);
|
|
}
|
|
}
|
|
|
|
$table_name = $this->tableQuote($match['table']) . ' ';
|
|
|
|
if (isset($match['alias']))
|
|
{
|
|
$table_name .= 'AS ' . $this->tableQuote($match['alias']) . ' ';
|
|
}
|
|
|
|
$table_join[] = $join_array[$match['join']] . ' JOIN ' . $table_name . $relation;
|
|
}
|
|
}
|
|
|
|
return implode(' ', $table_join);
|
|
}
|
|
|
|
protected function columnMap($columns, &$stack, $root)
|
|
{
|
|
if ($columns === '*')
|
|
{
|
|
return $stack;
|
|
}
|
|
|
|
foreach ($columns as $key => $value)
|
|
{
|
|
if (is_int($key))
|
|
{
|
|
preg_match('/([a-zA-Z0-9_]+\.)?(?<column>[a-zA-Z0-9_]+)(?:\s*\((?<alias>[a-zA-Z0-9_]+)\))?(?:\s*\[(?<type>(?:String|Bool|Int|Number|Object|JSON))\])?/i', $value, $key_match);
|
|
|
|
$column_key = !empty($key_match['alias']) ?
|
|
$key_match['alias'] :
|
|
$key_match['column'];
|
|
|
|
if (isset($key_match['type']))
|
|
{
|
|
$stack[$value] = [$column_key, $key_match['type']];
|
|
}
|
|
else
|
|
{
|
|
$stack[$value] = [$column_key, 'String'];
|
|
}
|
|
}
|
|
elseif ($this->isRaw($value))
|
|
{
|
|
preg_match('/([a-zA-Z0-9_]+\.)?(?<column>[a-zA-Z0-9_]+)(\s*\[(?<type>(String|Bool|Int|Number))\])?/i', $key, $key_match);
|
|
|
|
$column_key = $key_match['column'];
|
|
|
|
if (isset($key_match['type']))
|
|
{
|
|
$stack[$key] = [$column_key, $key_match['type']];
|
|
}
|
|
else
|
|
{
|
|
$stack[$key] = [$column_key, 'String'];
|
|
}
|
|
}
|
|
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($data, $columns, $column_map, &$stack, $root, &$result)
|
|
{
|
|
if ($root)
|
|
{
|
|
$columns_key = array_keys($columns);
|
|
|
|
if (count($columns_key) === 1 && is_array($columns[$columns_key[0]]))
|
|
{
|
|
$index_key = array_keys($columns)[0];
|
|
$data_key = preg_replace("/^[a-zA-Z0-9_]+\./i", "", $index_key);
|
|
|
|
$current_stack = [];
|
|
|
|
foreach ($data as $item)
|
|
{
|
|
$this->dataMap($data, $columns[$index_key], $column_map, $current_stack, false, $result);
|
|
|
|
$index = $data[$data_key];
|
|
|
|
$result[$index] = $current_stack;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
$current_stack = [];
|
|
|
|
$this->dataMap($data, $columns, $column_map, $current_stack, false, $result);
|
|
|
|
$result[] = $current_stack;
|
|
}
|
|
|
|
return;
|
|
}
|
|
|
|
foreach ($columns as $key => $value)
|
|
{
|
|
$isRaw = $this->isRaw($value);
|
|
|
|
if (is_int($key) || $isRaw)
|
|
{
|
|
$map = $column_map[$isRaw ? $key : $value];
|
|
|
|
$column_key = $map[0];
|
|
|
|
$item = $data[$column_key];
|
|
|
|
if (isset($map[1]))
|
|
{
|
|
if ($isRaw && in_array($map[1], ['Object', 'JSON']))
|
|
{
|
|
continue;
|
|
}
|
|
|
|
if (is_null($item))
|
|
{
|
|
$stack[$column_key] = null;
|
|
continue;
|
|
}
|
|
|
|
switch ($map[1])
|
|
{
|
|
case 'Number':
|
|
$stack[$column_key] = (float) $item;
|
|
break;
|
|
|
|
case 'Int':
|
|
$stack[$column_key] = (int) $item;
|
|
break;
|
|
|
|
case 'Bool':
|
|
$stack[$column_key] = (bool) $item;
|
|
break;
|
|
|
|
case 'Object':
|
|
$stack[$column_key] = unserialize($item);
|
|
break;
|
|
|
|
case 'JSON':
|
|
$stack[$column_key] = json_decode($item, true);
|
|
break;
|
|
|
|
case 'String':
|
|
$stack[$column_key] = $item;
|
|
break;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
$stack[$column_key] = $item;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
$current_stack = [];
|
|
|
|
$this->dataMap($data, $value, $column_map, $current_stack, false, $result);
|
|
|
|
$stack[$key] = $current_stack;
|
|
}
|
|
}
|
|
}
|
|
|
|
public function create($table, $columns, $options = null)
|
|
{
|
|
$stack = [];
|
|
|
|
$tableName = $this->prefix . $table;
|
|
|
|
foreach ($columns as $name => $definition)
|
|
{
|
|
if (is_int($name))
|
|
{
|
|
$stack[] = preg_replace('/\<([a-zA-Z0-9_]+)\>/i', '"$1"', $definition);
|
|
}
|
|
elseif (is_array($definition))
|
|
{
|
|
$stack[] = $name . ' ' . implode(' ', $definition);
|
|
}
|
|
elseif (is_string($definition))
|
|
{
|
|
$stack[] = $name . ' ' . $this->query($definition);
|
|
}
|
|
}
|
|
|
|
$table_option = '';
|
|
|
|
if (is_array($options))
|
|
{
|
|
$option_stack = [];
|
|
|
|
foreach ($options as $key => $value)
|
|
{
|
|
if (is_string($value) || is_int($value))
|
|
{
|
|
$option_stack[] = "$key = $value";
|
|
}
|
|
}
|
|
|
|
$table_option = ' ' . implode(', ', $option_stack);
|
|
}
|
|
elseif (is_string($options))
|
|
{
|
|
$table_option = ' ' . $options;
|
|
}
|
|
|
|
return $this->exec("CREATE TABLE IF NOT EXISTS $tableName (" . implode(', ', $stack) . ")$table_option");
|
|
}
|
|
|
|
public function drop($table)
|
|
{
|
|
$tableName = $this->prefix . $table;
|
|
|
|
return $this->exec("DROP TABLE IF EXISTS $tableName");
|
|
}
|
|
|
|
public function select($table, $join, $columns = null, $where = null)
|
|
{
|
|
$map = [];
|
|
$result = [];
|
|
$column_map = [];
|
|
|
|
$index = 0;
|
|
|
|
$column = $where === null ? $join : $columns;
|
|
|
|
$is_single = (is_string($column) && $column !== '*');
|
|
|
|
$query = $this->exec($this->selectContext($table, $map, $join, $columns, $where), $map);
|
|
|
|
$this->columnMap($columns, $column_map, true);
|
|
|
|
if (!$this->statement)
|
|
{
|
|
return false;
|
|
}
|
|
|
|
if ($columns === '*')
|
|
{
|
|
return $query->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
while ($data = $query->fetch(PDO::FETCH_ASSOC))
|
|
{
|
|
$current_stack = [];
|
|
|
|
$this->dataMap($data, $columns, $column_map, $current_stack, true, $result);
|
|
}
|
|
|
|
if ($is_single)
|
|
{
|
|
$single_result = [];
|
|
$result_key = $column_map[$column][0];
|
|
|
|
foreach ($result as $item)
|
|
{
|
|
$single_result[] = $item[$result_key];
|
|
}
|
|
|
|
return $single_result;
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
public function insert($table, $datas)
|
|
{
|
|
$stack = [];
|
|
$columns = [];
|
|
$fields = [];
|
|
$map = [];
|
|
|
|
if (!isset($datas[0]))
|
|
{
|
|
$datas = [$datas];
|
|
}
|
|
|
|
foreach ($datas as $data)
|
|
{
|
|
foreach ($data as $key => $value)
|
|
{
|
|
$columns[] = $key;
|
|
}
|
|
}
|
|
|
|
$columns = array_unique($columns);
|
|
|
|
foreach ($datas as $data)
|
|
{
|
|
$values = [];
|
|
|
|
foreach ($columns as $key)
|
|
{
|
|
if ($raw = $this->buildRaw($data[$key], $map))
|
|
{
|
|
$values[] = $raw;
|
|
continue;
|
|
}
|
|
|
|
$map_key = $this->mapKey();
|
|
|
|
$values[] = $map_key;
|
|
|
|
if (!isset($data[$key]))
|
|
{
|
|
$map[$map_key] = [null, PDO::PARAM_NULL];
|
|
}
|
|
else
|
|
{
|
|
$value = $data[$key];
|
|
|
|
$type = gettype($value);
|
|
|
|
switch ($type)
|
|
{
|
|
case 'array':
|
|
$map[$map_key] = [
|
|
strpos($key, '[JSON]') === strlen($key) - 6 ?
|
|
json_encode($value) :
|
|
serialize($value),
|
|
PDO::PARAM_STR
|
|
];
|
|
break;
|
|
|
|
case 'object':
|
|
$value = serialize($value);
|
|
|
|
case 'NULL':
|
|
case 'resource':
|
|
case 'boolean':
|
|
case 'integer':
|
|
case 'double':
|
|
case 'string':
|
|
$map[$map_key] = $this->typeMap($value, $type);
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
$stack[] = '(' . implode(', ', $values) . ')';
|
|
}
|
|
|
|
foreach ($columns as $key)
|
|
{
|
|
$fields[] = $this->columnQuote(preg_replace("/(\s*\[JSON\]$)/i", '', $key));
|
|
}
|
|
|
|
return $this->exec('INSERT INTO ' . $this->tableQuote($table) . ' (' . implode(', ', $fields) . ') VALUES ' . implode(', ', $stack), $map);
|
|
}
|
|
|
|
public function update($table, $data, $where = null)
|
|
{
|
|
$fields = [];
|
|
$map = [];
|
|
|
|
foreach ($data as $key => $value)
|
|
{
|
|
$column = $this->columnQuote(preg_replace("/(\s*\[(JSON|\+|\-|\*|\/)\]$)/i", '', $key));
|
|
|
|
if ($raw = $this->buildRaw($value, $map))
|
|
{
|
|
$fields[] = $column . ' = ' . $raw;
|
|
continue;
|
|
}
|
|
|
|
$map_key = $this->mapKey();
|
|
|
|
preg_match('/(?<column>[a-zA-Z0-9_]+)(\[(?<operator>\+|\-|\*|\/)\])?/i', $key, $match);
|
|
|
|
if (isset($match['operator']))
|
|
{
|
|
if (is_numeric($value))
|
|
{
|
|
$fields[] = $column . ' = ' . $column . ' ' . $match['operator'] . ' ' . $value;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
$fields[] = $column . ' = ' . $map_key;
|
|
|
|
$type = gettype($value);
|
|
|
|
switch ($type)
|
|
{
|
|
case 'array':
|
|
$map[$map_key] = [
|
|
strpos($key, '[JSON]') === strlen($key) - 6 ?
|
|
json_encode($value) :
|
|
serialize($value),
|
|
PDO::PARAM_STR
|
|
];
|
|
break;
|
|
|
|
case 'object':
|
|
$value = serialize($value);
|
|
|
|
case 'NULL':
|
|
case 'resource':
|
|
case 'boolean':
|
|
case 'integer':
|
|
case 'double':
|
|
case 'string':
|
|
$map[$map_key] = $this->typeMap($value, $type);
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
return $this->exec('UPDATE ' . $this->tableQuote($table) . ' SET ' . implode(', ', $fields) . $this->whereClause($where, $map), $map);
|
|
}
|
|
|
|
public function delete($table, $where)
|
|
{
|
|
$map = [];
|
|
|
|
return $this->exec('DELETE FROM ' . $this->tableQuote($table) . $this->whereClause($where, $map), $map);
|
|
}
|
|
|
|
public function replace($table, $columns, $where = null)
|
|
{
|
|
if (!is_array($columns) || empty($columns))
|
|
{
|
|
return false;
|
|
}
|
|
|
|
$map = [];
|
|
$stack = [];
|
|
|
|
foreach ($columns as $column => $replacements)
|
|
{
|
|
if (is_array($replacements))
|
|
{
|
|
foreach ($replacements as $old => $new)
|
|
{
|
|
$map_key = $this->mapKey();
|
|
|
|
$stack[] = $this->columnQuote($column) . ' = REPLACE(' . $this->columnQuote($column) . ', ' . $map_key . 'a, ' . $map_key . 'b)';
|
|
|
|
$map[$map_key . 'a'] = [$old, PDO::PARAM_STR];
|
|
$map[$map_key . 'b'] = [$new, PDO::PARAM_STR];
|
|
}
|
|
}
|
|
}
|
|
|
|
if (!empty($stack))
|
|
{
|
|
return $this->exec('UPDATE ' . $this->tableQuote($table) . ' SET ' . implode(', ', $stack) . $this->whereClause($where, $map), $map);
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
public function get($table, $join = null, $columns = null, $where = null)
|
|
{
|
|
$map = [];
|
|
$result = [];
|
|
$column_map = [];
|
|
$current_stack = [];
|
|
|
|
if ($where === null)
|
|
{
|
|
$column = $join;
|
|
unset($columns['LIMIT']);
|
|
}
|
|
else
|
|
{
|
|
$column = $columns;
|
|
unset($where['LIMIT']);
|
|
}
|
|
|
|
$is_single = (is_string($column) && $column !== '*');
|
|
|
|
$query = $this->exec($this->selectContext($table, $map, $join, $columns, $where) . ' LIMIT 1', $map);
|
|
|
|
if (!$this->statement)
|
|
{
|
|
return false;
|
|
}
|
|
|
|
$data = $query->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
if (isset($data[0]))
|
|
{
|
|
if ($column === '*')
|
|
{
|
|
return $data[0];
|
|
}
|
|
|
|
$this->columnMap($columns, $column_map, true);
|
|
|
|
$this->dataMap($data[0], $columns, $column_map, $current_stack, true, $result);
|
|
|
|
if ($is_single)
|
|
{
|
|
return $result[0][$column_map[$column][0]];
|
|
}
|
|
|
|
return $result[0];
|
|
}
|
|
}
|
|
|
|
public function has($table, $join, $where = null)
|
|
{
|
|
$map = [];
|
|
$column = null;
|
|
|
|
if ($this->type === 'mssql')
|
|
{
|
|
$query = $this->exec($this->selectContext($table, $map, $join, $column, $where, Medoo::raw('TOP 1 1')), $map);
|
|
}
|
|
else
|
|
{
|
|
$query = $this->exec('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($table, $join = null, $columns = null, $where = null)
|
|
{
|
|
$type = $this->type;
|
|
|
|
$order = 'RANDOM()';
|
|
|
|
if ($type === 'mysql')
|
|
{
|
|
$order = 'RAND()';
|
|
}
|
|
elseif ($type === 'mssql')
|
|
{
|
|
$order = 'NEWID()';
|
|
}
|
|
|
|
$order_raw = $this->raw($order);
|
|
|
|
if ($where === null)
|
|
{
|
|
if ($columns === null)
|
|
{
|
|
$columns = [
|
|
'ORDER' => $order_raw
|
|
];
|
|
}
|
|
else
|
|
{
|
|
$column = $join;
|
|
unset($columns['ORDER']);
|
|
|
|
$columns['ORDER'] = $order_raw;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
unset($where['ORDER']);
|
|
|
|
$where['ORDER'] = $order_raw;
|
|
}
|
|
|
|
return $this->select($table, $join, $columns, $where);
|
|
}
|
|
|
|
private function aggregate($type, $table, $join = null, $column = null, $where = null)
|
|
{
|
|
$map = [];
|
|
|
|
$query = $this->exec($this->selectContext($table, $map, $join, $column, $where, strtoupper($type)), $map);
|
|
|
|
if (!$this->statement)
|
|
{
|
|
return false;
|
|
}
|
|
|
|
$number = $query->fetchColumn();
|
|
|
|
return is_numeric($number) ? $number + 0 : $number;
|
|
}
|
|
|
|
public function count($table, $join = null, $column = null, $where = null)
|
|
{
|
|
return $this->aggregate('count', $table, $join, $column, $where);
|
|
}
|
|
|
|
public function avg($table, $join, $column = null, $where = null)
|
|
{
|
|
return $this->aggregate('avg', $table, $join, $column, $where);
|
|
}
|
|
|
|
public function max($table, $join, $column = null, $where = null)
|
|
{
|
|
return $this->aggregate('max', $table, $join, $column, $where);
|
|
}
|
|
|
|
public function min($table, $join, $column = null, $where = null)
|
|
{
|
|
return $this->aggregate('min', $table, $join, $column, $where);
|
|
}
|
|
|
|
public function sum($table, $join, $column = null, $where = null)
|
|
{
|
|
return $this->aggregate('sum', $table, $join, $column, $where);
|
|
}
|
|
|
|
public function action($actions)
|
|
{
|
|
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;
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
public function id()
|
|
{
|
|
if ($this->statement == null)
|
|
{
|
|
return null;
|
|
}
|
|
|
|
$type = $this->type;
|
|
|
|
if ($type === 'oracle')
|
|
{
|
|
return 0;
|
|
}
|
|
elseif ($type === 'pgsql')
|
|
{
|
|
return $this->pdo->query('SELECT LASTVAL()')->fetchColumn();
|
|
}
|
|
|
|
$lastId = $this->pdo->lastInsertId();
|
|
|
|
if ($lastId != "0" && $lastId != "")
|
|
{
|
|
return $lastId;
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
public function debug()
|
|
{
|
|
$this->debug_mode = true;
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function error()
|
|
{
|
|
return $this->errorInfo;
|
|
}
|
|
|
|
public function last()
|
|
{
|
|
$log = end($this->logs);
|
|
|
|
return $this->generate($log[0], $log[1]);
|
|
}
|
|
|
|
public function log()
|
|
{
|
|
return array_map(
|
|
function ($log)
|
|
{
|
|
return $this->generate($log[0], $log[1]);
|
|
},
|
|
$this->logs
|
|
);
|
|
}
|
|
|
|
public function info()
|
|
{
|
|
$output = [
|
|
'server' => 'SERVER_INFO',
|
|
'driver' => 'DRIVER_NAME',
|
|
'client' => 'CLIENT_VERSION',
|
|
'version' => 'SERVER_VERSION',
|
|
'connection' => 'CONNECTION_STATUS'
|
|
];
|
|
|
|
foreach ($output as $key => $value)
|
|
{
|
|
$output[$key] = @$this->pdo->getAttribute(constant('PDO::ATTR_' . $value));
|
|
}
|
|
|
|
$output['dsn'] = $this->dsn;
|
|
|
|
return $output;
|
|
}
|
|
}
|