src/DatabaseUtils.php
<?php
declare(strict_types = 1);
namespace mheinzerling\commons\database;
use mheinzerling\commons\ArrayUtils;
use mheinzerling\commons\StringUtils;
class DatabaseUtils
{
const DUPLICATE_FAIL = 0;
const DUPLICATE_IGNORE = 1;
const DUPLICATE_UPDATE = 2;
public static function insertAssoc(\PDO $pdo, string $table, array $data, int $onDuplicate = self::DUPLICATE_FAIL): int
{
$keys = array_keys($data);
$sql = self::startInsert($table, $keys, $onDuplicate);
$sql .= '(' . ':' . implode(',:', $keys) . ')';
return self::onDuplicateAndExec($pdo, $onDuplicate, $keys, $sql, $data);
}
public static function insertMultiple(\PDO $pdo, string $table, array $keys, array $datas, int $onDuplicate = self::DUPLICATE_FAIL): int
{
$sql = self::startInsert($table, $keys, $onDuplicate);
$row = "(" . substr(str_repeat("?, ", count($keys)), 0, -2) . "), ";
$sql .= substr(str_repeat($row, count($datas)), 0, -2);
foreach ($datas as &$data) ArrayUtils::fixOrderByKey($keys, $data);
return self::onDuplicateAndExec($pdo, $onDuplicate, $keys, $sql, array_values(ArrayUtils::flatten($datas)));
}
private static function startInsert(string $table, array $keys, int $onDuplicate): string
{
$sql = 'INSERT ';
if ($onDuplicate == self::DUPLICATE_IGNORE) $sql .= "IGNORE ";
$sql .= 'INTO `' . $table . '`(`' . implode('`,`', $keys) . '`) ';
$sql .= 'VALUES ';
return $sql;
}
private static function onDuplicateAndExec(\PDO $pdo, int $onDuplicate, array $keys, string $sql, array $params): int
{
if ($onDuplicate == self::DUPLICATE_UPDATE) {
/** @noinspection PhpUnusedParameterInspection */
$updateAssignment = StringUtils::implode(", ", $keys, function ($_, $key) {
return "`" . $key . "`=VALUES(`" . $key . "`)";
});
$sql .= ' ON DUPLICATE KEY UPDATE ' . $updateAssignment;
}
$stmt = self::exec($pdo, $sql, $params);
return $stmt->rowCount();
}
private static function prepareObjects(array &$data): void
{
foreach ($data as $key => &$value) {
if ($value instanceof \DateTime) {
$value = $value->format("Y-m-d H:i:s");
}
}
}
public static function exec(\PDO $pdo, string $query, array $values): \PDOStatement
{
$stmt = $pdo->prepare($query);
self::prepareObjects($values);
$stmt->execute($values);
return $stmt;
}
public static function importDump(\PDO $pdo, string $sqlFile): bool
{
$content = file_get_contents($sqlFile);
$queries = explode(";\n", str_replace("\r", "", $content));
$pdo->beginTransaction();
foreach ($queries as $query) {
$query = trim($query);
try {
if (StringUtils::startsWith($query, "/*!50503 select", true)) continue;
if (StringUtils::startsWith($query, "SELECT", true)) continue;
if (StringUtils::startsWith($query, "--", true)) continue;
$pdo->exec($query);
} catch (\PDOException $e) {
if ($e->getMessage() == "SQLSTATE[HY000]: General error: trying to execute an empty query") continue;
throw $e;
}
}
return $pdo->commit();
}
}