97 lines
3.2 KiB
PHP
97 lines
3.2 KiB
PHP
<?php
|
|
|
|
namespace App\Libraries;
|
|
|
|
use App\Models\QueryCacheModel;
|
|
use PDO;
|
|
use RuntimeException;
|
|
|
|
class QueryExecutor
|
|
{
|
|
/**
|
|
* @param array<string, mixed> $dataSource
|
|
* @param array<int, mixed> $bindings
|
|
* @return array{rows:array<int, array<string, mixed>>, row_count:int, execution_ms:float, cache_hit:bool}
|
|
*/
|
|
public function executeSql(array $dataSource, string $sql, array $bindings = [], int $cacheTtl = 0): array
|
|
{
|
|
$cacheKey = md5((string) ($dataSource['id'] ?? 0) . '|' . $sql . '|' . json_encode($bindings));
|
|
if ($cacheTtl > 0) {
|
|
$cached = (new QueryCacheModel())->findValid($cacheKey);
|
|
if (is_array($cached)) {
|
|
$rows = json_decode((string) ($cached['result_data'] ?? '[]'), true);
|
|
if (! is_array($rows)) {
|
|
$rows = [];
|
|
}
|
|
return [
|
|
'rows' => $rows,
|
|
'row_count' => (int) ($cached['row_count'] ?? count($rows)),
|
|
'execution_ms' => 0.0,
|
|
'cache_hit' => true,
|
|
];
|
|
}
|
|
}
|
|
|
|
$pdo = $this->makePdo($dataSource);
|
|
$started = microtime(true);
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute($bindings);
|
|
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
|
|
$executionMs = (microtime(true) - $started) * 1000;
|
|
|
|
if ($cacheTtl > 0) {
|
|
$cacheModel = new QueryCacheModel();
|
|
$cacheModel->where('cache_key', $cacheKey)->delete();
|
|
$cacheModel->insert([
|
|
'cache_key' => $cacheKey,
|
|
'data_source_id' => (int) ($dataSource['id'] ?? 0),
|
|
'result_data' => json_encode($rows, JSON_UNESCAPED_UNICODE),
|
|
'row_count' => count($rows),
|
|
'expires_at' => date('Y-m-d H:i:s', time() + $cacheTtl),
|
|
]);
|
|
}
|
|
|
|
return [
|
|
'rows' => $rows,
|
|
'row_count' => count($rows),
|
|
'execution_ms' => round($executionMs, 2),
|
|
'cache_hit' => false,
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @param array<string, mixed> $dataSource
|
|
*/
|
|
private function makePdo(array $dataSource): PDO
|
|
{
|
|
$type = (string) ($dataSource['type'] ?? '');
|
|
if ($type === 'mysql') {
|
|
$dsn = sprintf(
|
|
'mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
|
|
$dataSource['host'] ?? '',
|
|
(int) ($dataSource['port'] ?? 3306),
|
|
$dataSource['database_name'] ?? ''
|
|
);
|
|
} elseif ($type === 'postgresql') {
|
|
$dsn = sprintf(
|
|
'pgsql:host=%s;port=%d;dbname=%s',
|
|
$dataSource['host'] ?? '',
|
|
(int) ($dataSource['port'] ?? 5432),
|
|
$dataSource['database_name'] ?? ''
|
|
);
|
|
} else {
|
|
throw new RuntimeException('SQL execution is supported only for MySQL and PostgreSQL data sources.');
|
|
}
|
|
|
|
return new PDO(
|
|
$dsn,
|
|
(string) ($dataSource['username'] ?? ''),
|
|
(string) ($dataSource['password'] ?? ''),
|
|
[
|
|
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
|
PDO::ATTR_TIMEOUT => 30,
|
|
]
|
|
);
|
|
}
|
|
}
|