126 lines
5.0 KiB
PHP
126 lines
5.0 KiB
PHP
<?php
|
|
declare(strict_types=1);
|
|
|
|
require_once dirname(__DIR__) . '/database/sqlite_schema.php';
|
|
|
|
$schemaPath = dirname(__DIR__) . '/database/schema.sql';
|
|
$schema = file_get_contents($schemaPath);
|
|
if ($schema === false) {
|
|
fwrite(STDERR, "Datenbankschema konnte nicht gelesen werden.\n");
|
|
exit(1);
|
|
}
|
|
|
|
if (preg_match('/\bAUTOINCREMENT\b/i', $schema)) {
|
|
fail('Das kanonische MySQL/MariaDB-Schema enthält SQLite-only AUTOINCREMENT.');
|
|
}
|
|
|
|
if (!preg_match_all(
|
|
'/CREATE TABLE IF NOT EXISTS\s+`?(\w+)`?\s*\((.*?)\)\s*(?:ENGINE\s*=\s*InnoDB[^;]*)?;/is',
|
|
$schema,
|
|
$tables,
|
|
PREG_SET_ORDER
|
|
)) {
|
|
fwrite(STDERR, "Keine Tabellen im Datenbankschema gefunden.\n");
|
|
exit(1);
|
|
}
|
|
|
|
$definitions = [];
|
|
foreach ($tables as $table) {
|
|
$definitions[$table[1]] = $table[0];
|
|
if (!preg_match('/ENGINE\s*=\s*InnoDB\s+DEFAULT\s+CHARSET\s*=\s*utf8mb4\s*;$/i', trim($table[0]))) {
|
|
fail("Tabellendefinition {$table[1]} verwendet nicht InnoDB/utf8mb4.");
|
|
}
|
|
if (!preg_match_all(
|
|
'/FOREIGN KEY\s*\(\s*`?(\w+)`?\s*\).*?ON DELETE SET NULL/i',
|
|
$table[2],
|
|
$foreignKeys
|
|
)) {
|
|
continue;
|
|
}
|
|
|
|
foreach ($foreignKeys[1] as $column) {
|
|
if (!preg_match('/^\s*`?' . preg_quote($column, '/') . '`?\s+([^,\r\n]+)/mi', $table[2], $definition)) {
|
|
fail("Spalte {$table[1]}.{$column} wurde nicht gefunden.");
|
|
}
|
|
if (preg_match('/\bNOT\s+NULL\b/i', $definition[1])) {
|
|
fail("{$table[1]}.{$column} ist NOT NULL, verwendet aber ON DELETE SET NULL.");
|
|
}
|
|
}
|
|
}
|
|
|
|
foreach (['departments', 'organizations', 'staff', 'users', 'customer_notes', 'ticket_ratings'] as $tableName) {
|
|
if (!isset($definitions[$tableName])) {
|
|
fail("Tabellendefinition {$tableName} fehlt.");
|
|
}
|
|
}
|
|
|
|
$pdo = new PDO('sqlite::memory:');
|
|
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
|
$pdo->exec('PRAGMA foreign_keys = ON');
|
|
|
|
foreach (['departments', 'organizations', 'staff', 'users', 'customer_notes'] as $tableName) {
|
|
$pdo->exec(convert_schema_to_sqlite($definitions[$tableName]));
|
|
}
|
|
|
|
$pdo->exec(
|
|
"INSERT INTO staff (id, name, email, password_hash, role, is_active, created_at)
|
|
VALUES (1, 'Testautor', 'autor@example.test', 'hash', 'agent', 1, CURRENT_TIMESTAMP)"
|
|
);
|
|
$pdo->exec(
|
|
"INSERT INTO users (id, name, email, password_hash, created_at)
|
|
VALUES (1, 'Testkunde', 'kunde@example.test', 'hash', CURRENT_TIMESTAMP)"
|
|
);
|
|
$pdo->exec(
|
|
"INSERT INTO customer_notes (user_id, staff_id, note, created_at)
|
|
VALUES (1, 1, 'Historische Notiz', CURRENT_TIMESTAMP)"
|
|
);
|
|
$pdo->exec('DELETE FROM staff WHERE id = 1');
|
|
|
|
$note = $pdo->query('SELECT staff_id, note FROM customer_notes WHERE user_id = 1')->fetch(PDO::FETCH_ASSOC);
|
|
if ($note === false || $note['staff_id'] !== null || $note['note'] !== 'Historische Notiz') {
|
|
fail('Kundennotizen bleiben beim Löschen eines Mitarbeiters nicht mit staff_id = NULL erhalten.');
|
|
}
|
|
|
|
$pdo->exec('CREATE TABLE tickets (id INTEGER PRIMARY KEY)');
|
|
$pdo->exec(convert_schema_to_sqlite($definitions['ticket_ratings']));
|
|
|
|
$pdo->exec("INSERT INTO users (id, name, email, password_hash, created_at)
|
|
VALUES (2, 'Bewertungskunde', 'rating@example.test', 'hash', CURRENT_TIMESTAMP)");
|
|
$pdo->exec('INSERT INTO tickets (id) VALUES (1)');
|
|
$pdo->exec("INSERT INTO ticket_ratings (ticket_id, user_id, rating, comment, created_at)
|
|
VALUES (1, 2, 5, 'Sehr gut', CURRENT_TIMESTAMP)");
|
|
|
|
$rating = $pdo->query('SELECT id, user_id, rating FROM ticket_ratings WHERE ticket_id = 1')->fetch(PDO::FETCH_ASSOC);
|
|
if ($rating === false || (int) $rating['id'] !== 1 || (int) $rating['user_id'] !== 2 || (int) $rating['rating'] !== 5) {
|
|
fail('Die SQLite-Konvertierung von ticket_ratings erzeugt keine funktionsfähige Tabelle.');
|
|
}
|
|
|
|
$pdo->exec('DELETE FROM users WHERE id = 2');
|
|
$ratingUserId = $pdo->query('SELECT user_id FROM ticket_ratings WHERE ticket_id = 1')->fetchColumn();
|
|
if ($ratingUserId !== null) {
|
|
fail('ticket_ratings.user_id bleibt beim Löschen eines Kunden nicht als NULL erhalten.');
|
|
}
|
|
|
|
$pdo->exec('DELETE FROM tickets WHERE id = 1');
|
|
if ((int) $pdo->query('SELECT COUNT(*) FROM ticket_ratings')->fetchColumn() !== 0) {
|
|
fail('Ticket-Bewertungen werden beim Löschen des Tickets nicht mit entfernt.');
|
|
}
|
|
|
|
$fullSchemaPdo = new PDO('sqlite::memory:');
|
|
$fullSchemaPdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
|
$fullSchemaPdo->exec('PRAGMA foreign_keys = ON');
|
|
$fullSchemaPdo->exec(convert_schema_to_sqlite($schema));
|
|
$organizationColumns = $fullSchemaPdo->query('PRAGMA table_info(organizations)')->fetchAll(PDO::FETCH_ASSOC);
|
|
$createdAt = array_values(array_filter($organizationColumns, fn(array $column): bool => $column['name'] === 'created_at'))[0] ?? null;
|
|
if ($createdAt === null || (int) $createdAt['notnull'] !== 1 || stripos((string) $createdAt['dflt_value'], 'CURRENT_TIMESTAMP') === false) {
|
|
fail('Die SQLite-Konvertierung bewahrt NOT NULL/DEFAULT CURRENT_TIMESTAMP nicht.');
|
|
}
|
|
|
|
echo "Schema-Fremdschlüssel sind SET-NULL-kompatibel.\n";
|
|
|
|
function fail(string $message): never
|
|
{
|
|
fwrite(STDERR, $message . "\n");
|
|
exit(1);
|
|
}
|