Files
fewo-stommer/app/Console/Commands/BackupDatabase.php
Lars StommerandClaude Opus 5.5 4f69b426a4 Booking path 1 (offer + guest acceptance), 2FA for all, privacy hardening, brand graphics in theme
- Offer workflow: "Angebot senden" in the admin (dates, guests, promo code, manual price adjustment,
  validity from the property's hold time, message, guest data for the contract) with live price;
  contract draft PDF; signed, versioned offer page for the guest to accept (acceptance record without
  IP) or decline; expiry job, landlord notifications, editable offer texts, wording on the website.
- 2FA is mandatory for every admin account.
- Privacy: complete anonymisation (notes, attribution, review names, offer messages), shorter
  periods for unbinding requests, portal mail excerpts and attribution (configurable proposals),
  iCal summary not stored, known e-mail no longer overwrites guest data, security headers, bearer
  tokens for automations, metadata stripped from photo originals, review link throttled.
- Daily database backup (backup:database) and scripts/backup-pull.sh for the NAS.
- Theme: brand mark in header/footer, favicon/touch icon, wave divider, icons, illustration as
  image placeholder; navy follows the text colour for dark appearances.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-22 21:38:22 +02:00

118 lines
4.4 KiB
PHP

<?php
namespace App\Console\Commands;
use Illuminate\Console\Attributes\Description;
use Illuminate\Console\Attributes\Signature;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Throwable;
#[Signature('backup:database {--keep= : Anzahl aufbewahrter Sicherungen}')]
#[Description('Write a compressed SQL dump of the database to storage/app/private/backups and keep the newest ones')]
class BackupDatabase extends Command
{
public const DIRECTORY = 'backups';
/** Tables without lasting value; their content is not dumped (structure only). */
private const SKIP_DATA = ['sessions', 'cache', 'cache_locks', 'jobs', 'job_batches', 'failed_jobs'];
/**
* Execute the console command.
*/
public function handle(): int
{
$keep = (int) ($this->option('keep') ?? config('fewo.backup.keep', 14));
$file = self::DIRECTORY.'/fewo-'.now()->format('Ymd-His').'.sql.gz';
$temp = tempnam(sys_get_temp_dir(), 'fewo-backup');
$out = gzopen($temp, 'wb6');
try {
gzwrite($out, '-- FeWo Stommer database backup '.now()->toIso8601String().' ('.DB::getDriverName().")\n");
gzwrite($out, $this->isMysql() ? "SET FOREIGN_KEY_CHECKS=0;\nSET NAMES utf8mb4;\n" : "PRAGMA foreign_keys=OFF;\n");
foreach ($this->tables() as $table) {
gzwrite($out, "\nDROP TABLE IF EXISTS ".$this->quoteName($table).";\n".$this->createStatement($table).";\n");
if (! in_array($table, self::SKIP_DATA, true)) {
$this->dumpRows($out, $table);
}
}
gzwrite($out, $this->isMysql() ? "SET FOREIGN_KEY_CHECKS=1;\n" : "PRAGMA foreign_keys=ON;\n");
gzclose($out);
Storage::disk('local')->put($file, (string) file_get_contents($temp));
} catch (Throwable $exception) {
report($exception);
$this->error('Sicherung fehlgeschlagen: '.$exception->getMessage());
return self::FAILURE;
} finally {
@unlink($temp);
}
$this->prune($keep);
$this->info("Sicherung erstellt: storage/app/private/{$file} (".round(Storage::disk('local')->size($file) / 1024).' KB)');
return self::SUCCESS;
}
private function isMysql(): bool
{
return in_array(DB::getDriverName(), ['mysql', 'mariadb'], true);
}
/**
* @return list<string>
*/
private function tables(): array
{
return $this->isMysql()
? array_map(fn (object $row): string => (string) array_values((array) $row)[0], DB::select('SHOW TABLES'))
: array_map(fn (object $row): string => $row->name, DB::select("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name"));
}
private function createStatement(string $table): string
{
if ($this->isMysql()) {
return (string) array_values((array) DB::selectOne('SHOW CREATE TABLE '.$this->quoteName($table)))[1];
}
return (string) DB::selectOne("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = ?", [$table])->sql;
}
/**
* @param resource $out
*/
private function dumpRows($out, string $table): void
{
$pdo = DB::connection()->getPdo();
$offset = 0;
do {
$rows = DB::table($table)->offset($offset)->limit(500)->get();
foreach ($rows as $row) {
$values = array_map(fn ($value): string => $value === null ? 'NULL' : (is_int($value) || is_float($value) ? (string) $value : $pdo->quote((string) $value)), (array) $row);
gzwrite($out, 'INSERT INTO '.$this->quoteName($table).' ('.implode(', ', array_map($this->quoteName(...), array_keys((array) $row))).') VALUES ('.implode(', ', $values).");\n");
}
$offset += 500;
} while ($rows->count() === 500);
}
private function quoteName(string $name): string
{
return $this->isMysql() ? '`'.str_replace('`', '``', $name).'`' : '"'.str_replace('"', '""', $name).'"';
}
private function prune(int $keep): void
{
collect(Storage::disk('local')->files(self::DIRECTORY))
->filter(fn (string $path): bool => str_ends_with($path, '.sql.gz'))
->sort()
->reverse()
->slice(max(1, $keep))
->each(fn (string $path) => Storage::disk('local')->delete($path));
}
}