PHP 音乐资料 CRUD：数据库、媒体上传与安全删除

CREATE TABLE tracks (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(160) NOT NULL,
    artist VARCHAR(120) NOT NULL,
    audio_key VARCHAR(255) NOT NULL,
    cover_key VARCHAR(255) NULL,
    owner_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

<?php
$statement = $pdo->prepare(
    'INSERT INTO tracks (title, artist, audio_key, cover_key, owner_id)
     VALUES (:title, :artist, :audio_key, :cover_key, :owner_id)'
);
$statement->execute([
    'title' => $title,
    'artist' => $artist,
    'audio_key' => $audioKey,
    'cover_key' => $coverKey,
    'owner_id' => $currentUserId,
]);
?>

<form method="post" enctype="multipart/form-data">
  <input type="hidden" name="csrf_token" value="<?= escapeHtml($_SESSION['csrf_token']) ?>">
  <label for="title">标题</label>
  <input id="title" name="title" maxlength="160" required>
  <label for="artist">歌手</label>
  <input id="artist" name="artist" maxlength="120" required>
  <label for="audio">音频文件</label>
  <input id="audio" name="audio" type="file" accept="audio/mpeg,audio/ogg,audio/wav" required>
  <label for="cover">封面图片</label>
  <input id="cover" name="cover" type="file" accept="image/jpeg,image/png,image/webp">
  <button type="submit">保存</button>
</form>

<?php
$upload = $_FILES['audio'] ?? null;
$maxBytes = 20 * 1024 * 1024;
$allowedTypes = [
    'audio/mpeg' => 'mp3',
    'audio/ogg' => 'ogg',
    'audio/wav' => 'wav',
];

if (!is_array($upload) || ($upload['error'] ?? null) !== UPLOAD_ERR_OK) {
    throw new RuntimeException('音频上传失败。');
}

$size = $upload['size'] ?? null;
$tempPath = $upload['tmp_name'] ?? null;
if (!is_int($size) || $size < 1 || $size > $maxBytes) {
    throw new RuntimeException('音频大小不符合限制。');
}
if (!is_string($tempPath) || !is_uploaded_file($tempPath)) {
    throw new RuntimeException('上传来源无效。');
}

$mime = (new finfo(FILEINFO_MIME_TYPE))->file($tempPath);
if (!is_string($mime) || !isset($allowedTypes[$mime])) {
    throw new RuntimeException('不支持的音频类型。');
}

$storedName = bin2hex(random_bytes(16)) . '.' . $allowedTypes[$mime];
$audioKey = $storedName;
$destination = $privateMediaDirectory . DIRECTORY_SEPARATOR . $storedName;
if (!move_uploaded_file($tempPath, $destination)) {
    throw new RuntimeException('无法保存音频文件。');
}
?>

<?php
try {
    $pdo->beginTransaction();
    $statement->execute($trackParameters);
    $pdo->commit();
} catch (Throwable $error) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    if (is_file($destination)) {
        unlink($destination);
    }
    throw $error;
}
?>

<?php
$statement = $pdo->prepare(
    'SELECT id, title, artist, audio_key, cover_key
     FROM tracks
     WHERE owner_id = :owner_id
     ORDER BY created_at DESC
     LIMIT :limit OFFSET :offset'
);
$statement->bindValue('owner_id', $currentUserId, PDO::PARAM_INT);
$statement->bindValue('limit', 20, PDO::PARAM_INT);
$statement->bindValue('offset', $offset, PDO::PARAM_INT);
$statement->execute();
$tracks = $statement->fetchAll();
?>

<?php foreach ($tracks as $track): ?>
  <article>
    <h2><?= escapeHtml((string) $track['title']) ?></h2>
    <p><?= escapeHtml((string) $track['artist']) ?></p>
    <audio controls preload="none" src="<?= escapeHtml(mediaUrl((string) $track['audio_key'])) ?>"></audio>
  </article>
<?php endforeach; ?>

<form method="post" action="/admin/tracks/delete">
  <input type="hidden" name="csrf_token" value="<?= escapeHtml($_SESSION['csrf_token']) ?>">
  <input type="hidden" name="track_id" value="<?= (int) $track['id'] ?>">
  <button type="submit">删除</button>
</form>