v2.0.0.1

Publisium

← Changes All versions

372 lines · 19.3 KB

<?php
declare(strict_types=1);
require_once __DIR__ . '/config.php';
final class Database
{
private static ?PDO $site = null;
private static ?PDO $users = null;
private static bool $schemaChecked = false;
public static function site(): PDO
{
if (self::$site instanceof PDO) {
return self::$site;
}
self::$site = self::connect(
Config::get('DB_SITE_HOST', '127.0.0.1'),
Config::get('DB_SITE_PORT', '3306'),
Config::get('DB_SITE_NAME', ''),
Config::get('DB_SITE_USER', ''),
Config::get('DB_SITE_PASS', '')
);
self::ensureSiteSchema(self::$site);
return self::$site;
}
public static function users(): PDO
{
if (self::$users instanceof PDO) {
return self::$users;
}
self::$users = self::connect(
Config::get('DB_USERS_HOST', '127.0.0.1'),
Config::get('DB_USERS_PORT', '3306'),
Config::get('DB_USERS_NAME', ''),
Config::get('DB_USERS_USER', ''),
Config::get('DB_USERS_PASS', '')
);
return self::$users;
}
private static function connect(?string $host, ?string $port, ?string $name, ?string $user, ?string $pass): PDO
{
$dsn = sprintf('mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4', $host, $port, $name);
return new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
}
private static function ensureSiteSchema(PDO $pdo): void
{
if (self::$schemaChecked) {
return;
}
self::$schemaChecked = true;
try {
$dbName = $pdo->query('SELECT DATABASE()')->fetchColumn();
if (!$dbName) {
return;
}
$columnCheck = $pdo->prepare(
'SELECT COUNT(*) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = :db AND TABLE_NAME = :table AND COLUMN_NAME = :column'
);
$requiredColumns = [
'team_accounts' => [
'avatar_path' => "ALTER TABLE team_accounts ADD COLUMN avatar_path VARCHAR(255) NULL",
'totp_secret' => "ALTER TABLE team_accounts ADD COLUMN totp_secret VARCHAR(64) NULL",
'totp_enabled' => "ALTER TABLE team_accounts ADD COLUMN totp_enabled TINYINT(1) NOT NULL DEFAULT 0",
'totp_recovery' => "ALTER TABLE team_accounts ADD COLUMN totp_recovery TEXT NULL",
'totp_last_step' => "ALTER TABLE team_accounts ADD COLUMN totp_last_step BIGINT NULL",
],
'articles' => [
'deleted_at' => "ALTER TABLE articles ADD COLUMN deleted_at DATETIME NULL",
'trashed_status' => "ALTER TABLE articles ADD COLUMN trashed_status VARCHAR(20) NULL",
'read_aloud' => "ALTER TABLE articles ADD COLUMN read_aloud TINYINT(1) NOT NULL DEFAULT 0",
'read_aloud_source' => "ALTER TABLE articles ADD COLUMN read_aloud_source VARCHAR(10) NOT NULL DEFAULT 'auto'",
'read_aloud_audio' => "ALTER TABLE articles ADD COLUMN read_aloud_audio VARCHAR(255) NULL",
'read_aloud_hash' => "ALTER TABLE articles ADD COLUMN read_aloud_hash CHAR(40) NULL",
'read_aloud_status' => "ALTER TABLE articles ADD COLUMN read_aloud_status VARCHAR(12) NULL",
'read_aloud_error' => "ALTER TABLE articles ADD COLUMN read_aloud_error VARCHAR(255) NULL",
'read_aloud_started' => "ALTER TABLE articles ADD COLUMN read_aloud_started DATETIME NULL",
'author_name' => "ALTER TABLE articles ADD COLUMN author_name VARCHAR(120) NULL AFTER author_id",
'noindex' => "ALTER TABLE articles ADD COLUMN noindex TINYINT(1) NOT NULL DEFAULT 0 AFTER seo_description",
],
'analytics_scripts' => [
'consent_category' => "ALTER TABLE analytics_scripts ADD COLUMN consent_category VARCHAR(20) NOT NULL DEFAULT 'analytics' AFTER requires_consent",
],
'pages' => [
'author_name' => "ALTER TABLE pages ADD COLUMN author_name VARCHAR(120) NULL AFTER author_id",
'noindex' => "ALTER TABLE pages ADD COLUMN noindex TINYINT(1) NOT NULL DEFAULT 0 AFTER show_in_menu",
],
'ads' => [
'hide_for_subscribers' => "ALTER TABLE ads ADD COLUMN hide_for_subscribers TINYINT(1) NOT NULL DEFAULT 0 AFTER is_active",
'audience' => "ALTER TABLE ads ADD COLUMN audience VARCHAR(10) NOT NULL DEFAULT 'all' AFTER hide_for_subscribers",
'excluded_items' => "ALTER TABLE ads ADD COLUMN excluded_items TEXT NULL AFTER audience",
'consent_category' => "ALTER TABLE ads ADD COLUMN consent_category VARCHAR(20) NOT NULL DEFAULT 'none' AFTER excluded_items",
],
'categories' => [
'show_in_menu' => "ALTER TABLE categories ADD COLUMN show_in_menu TINYINT(1) NOT NULL DEFAULT 1 AFTER sort_order",
],
'article_comments' => [
'author_email' => "ALTER TABLE article_comments ADD COLUMN author_email VARCHAR(190) NULL AFTER author_name",
'team_account_id' => "ALTER TABLE article_comments ADD COLUMN team_account_id INT UNSIGNED NULL AFTER user_account_id",
],
'author_profiles' => [
'schema_type' => "ALTER TABLE author_profiles ADD COLUMN schema_type VARCHAR(12) NOT NULL DEFAULT 'Person' AFTER website_url",
],
'nav_items' => [
'icon_key' => "ALTER TABLE nav_items ADD COLUMN icon_key VARCHAR(30) NULL AFTER target",
'icon_svg' => "ALTER TABLE nav_items ADD COLUMN icon_svg MEDIUMTEXT NULL AFTER icon_key",
],
];
foreach ($requiredColumns as $table => $columns) {
$tableCheck = $pdo->prepare(
'SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = :db AND TABLE_NAME = :table'
);
$tableCheck->execute(['db' => $dbName, 'table' => $table]);
if ((int) $tableCheck->fetchColumn() === 0) {
continue;
}
foreach ($columns as $column => $alterSql) {
$columnCheck->execute(['db' => $dbName, 'table' => $table, 'column' => $column]);
if ((int) $columnCheck->fetchColumn() === 0) {
try {
$pdo->exec($alterSql);
} catch (PDOException $e) {
error_log('Publisium: could not auto-add column ' . $table . '.' . $column . ': ' . $e->getMessage());
}
}
}
}
$requiredTables = [
'site_settings' => "CREATE TABLE site_settings (
setting_key VARCHAR(120) NOT NULL PRIMARY KEY,
setting_value LONGTEXT NULL,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
'nav_items' => "CREATE TABLE nav_items (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
item_type ENUM('category','page','custom') NOT NULL,
ref_id INT UNSIGNED NULL,
label VARCHAR(120) NULL,
url VARCHAR(255) NULL,
target ENUM('_self','_blank') NOT NULL DEFAULT '_self',
icon_key VARCHAR(30) NULL,
icon_svg MEDIUMTEXT NULL,
sort_order INT NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY idx_nav_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
'activity_log' => "CREATE TABLE activity_log (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NULL,
user_name VARCHAR(120) NULL,
action VARCHAR(60) NOT NULL,
entity_type VARCHAR(30) NULL,
entity_id INT UNSIGNED NULL,
details VARCHAR(500) NULL,
ip_address VARCHAR(45) NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY idx_activity_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
'author_profiles' => "CREATE TABLE author_profiles (
account_id INT UNSIGNED NOT NULL PRIMARY KEY,
slug VARCHAR(120) NOT NULL,
headline VARCHAR(160) NOT NULL DEFAULT '',
bio TEXT NULL,
website_url VARCHAR(255) NOT NULL DEFAULT '',
schema_type VARCHAR(12) NOT NULL DEFAULT 'Person',
draft_headline VARCHAR(160) NULL,
draft_bio TEXT NULL,
draft_website_url VARCHAR(255) NULL,
is_public TINYINT(1) NOT NULL DEFAULT 0,
status ENUM('draft','pending','approved','rejected') NOT NULL DEFAULT 'draft',
rejection_reason VARCHAR(500) NULL,
reviewed_by INT UNSIGNED NULL,
reviewed_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uniq_author_slug (slug),
KEY idx_author_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
'article_versions' => "CREATE TABLE article_versions (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
article_id INT UNSIGNED NOT NULL,
content_hash CHAR(40) NOT NULL,
title VARCHAR(255) NOT NULL,
excerpt TEXT NULL,
content_blocks LONGTEXT NULL,
category_id INT UNSIGNED NULL,
cover_image_path VARCHAR(500) NULL,
seo_title VARCHAR(255) NULL,
seo_description TEXT NULL,
access_type VARCHAR(10) NOT NULL DEFAULT 'free',
price_cents INT UNSIGNED NULL,
saved_by INT UNSIGNED NULL,
saved_by_name VARCHAR(120) NULL,
note VARCHAR(60) NOT NULL DEFAULT 'save',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY idx_version_article (article_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
'tags' => "CREATE TABLE tags (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
slug VARCHAR(80) NOT NULL,
UNIQUE KEY uniq_tag_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
'article_tags' => "CREATE TABLE article_tags (
article_id INT UNSIGNED NOT NULL,
tag_id INT UNSIGNED NOT NULL,
PRIMARY KEY (article_id, tag_id),
KEY idx_article_tags_tag (tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
'login_attempts' => "CREATE TABLE login_attempts (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
scope VARCHAR(20) NOT NULL,
identifier_hash CHAR(64) NOT NULL,
ip_address VARCHAR(45) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY idx_attempt_identifier (scope, identifier_hash, created_at),
KEY idx_attempt_ip (scope, ip_address, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
'article_comments' => "CREATE TABLE article_comments (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
article_id INT UNSIGNED NOT NULL,
parent_id INT UNSIGNED NULL,
user_account_id INT UNSIGNED NULL,
team_account_id INT UNSIGNED NULL,
author_name VARCHAR(80) NOT NULL,
author_email VARCHAR(190) NULL,
body TEXT NOT NULL,
status VARCHAR(10) NOT NULL DEFAULT 'pending',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY idx_comments_article (article_id, status, created_at),
KEY idx_comments_status (status, created_at),
KEY idx_comments_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
'indexnow_log' => "CREATE TABLE indexnow_log (
article_id INT UNSIGNED NOT NULL PRIMARY KEY,
submitted_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
'clean_urls' => "CREATE TABLE clean_urls (
slug VARCHAR(191) NOT NULL PRIMARY KEY,
entity_type VARCHAR(10) NOT NULL,
entity_id INT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_clean_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
'site_languages' => "CREATE TABLE site_languages (
code VARCHAR(10) NOT NULL PRIMARY KEY,
name VARCHAR(60) NOT NULL,
sort_order INT NOT NULL DEFAULT 0,
is_active TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
'content_translations' => "CREATE TABLE content_translations (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
entity_type VARCHAR(12) NOT NULL,
entity_id INT UNSIGNED NOT NULL,
lang VARCHAR(10) NOT NULL,
title VARCHAR(255) NOT NULL DEFAULT '',
excerpt TEXT NULL,
content_blocks LONGTEXT NULL,
seo_title VARCHAR(255) NULL,
seo_description VARCHAR(500) NULL,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uniq_translation (entity_type, entity_id, lang),
KEY idx_translation_lang (lang, entity_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
];
$tableExistsCheck = $pdo->prepare(
'SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = :db AND TABLE_NAME = :table'
);
foreach ($requiredTables as $table => $createSql) {
$tableExistsCheck->execute(['db' => $dbName, 'table' => $table]);
if ((int) $tableExistsCheck->fetchColumn() === 0) {
try {
$pdo->exec($createSql);
} catch (PDOException $e) {
error_log('Publisium: could not auto-create table ' . $table . ': ' . $e->getMessage());
}
}
}
self::detachContentFromAccounts($pdo, (string) $dbName);
} catch (PDOException $e) {
error_log('Publisium: schema auto-check failed: ' . $e->getMessage());
}
}
// Content must survive account deletion: owner columns become nullable (NULL = system) and
// their foreign keys switch from ON DELETE CASCADE to ON DELETE SET NULL.
private static function detachContentFromAccounts(PDO $pdo, string $dbName): void
{
$ownerColumns = [
'articles' => 'author_id',
'pages' => 'author_id',
'ads' => 'created_by',
'article_revisions' => 'editor_id',
];
$foreignKeys = $pdo->prepare(
"SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, r.DELETE_RULE
FROM information_schema.KEY_COLUMN_USAGE k
JOIN information_schema.REFERENTIAL_CONSTRAINTS r
ON r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND r.TABLE_NAME = k.TABLE_NAME
WHERE k.TABLE_SCHEMA = :db AND k.REFERENCED_TABLE_NAME = 'team_accounts'"
);
$foreignKeys->execute(['db' => $dbName]);
$keysByColumn = [];
foreach ($foreignKeys->fetchAll() as $foreignKey) {
$keysByColumn[$foreignKey['TABLE_NAME'] . '.' . $foreignKey['COLUMN_NAME']][] = $foreignKey;
}
$columnInfo = $pdo->prepare(
'SELECT IS_NULLABLE, COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = :db AND TABLE_NAME = :table AND COLUMN_NAME = :column'
);
foreach ($ownerColumns as $table => $column) {
$columnInfo->execute(['db' => $dbName, 'table' => $table, 'column' => $column]);
$info = $columnInfo->fetch();
if (!$info) {
continue;
}
$keys = $keysByColumn[$table . '.' . $column] ?? [];
$cascadingKeys = array_filter($keys, static fn(array $key): bool => strtoupper((string) $key['DELETE_RULE']) !== 'SET NULL');
$isNullable = strtoupper((string) $info['IS_NULLABLE']) === 'YES';
if ($isNullable && empty($cascadingKeys)) {
continue;
}
$columnType = preg_match('/^[a-z]+($\d+$)?( unsigned)?$/i', (string) $info['COLUMN_TYPE']) === 1 ? (string) $info['COLUMN_TYPE'] : 'INT UNSIGNED';
try {
foreach ($keys as $key) {
$pdo->exec('ALTER TABLE `' . $table . '` DROP FOREIGN KEY `' . str_replace('`', '', (string) $key['CONSTRAINT_NAME']) . '`');
}
if (!$isNullable) {
$pdo->exec('ALTER TABLE `' . $table . '` MODIFY `' . $column . '` ' . $columnType . ' NULL');
}
if (!empty($keys)) {
$constraint = str_replace('`', '', (string) $keys[0]['CONSTRAINT_NAME']);
$pdo->exec('ALTER TABLE `' . $table . '` ADD CONSTRAINT `' . $constraint . '` FOREIGN KEY (`' . $column . '`) REFERENCES team_accounts(id) ON DELETE SET NULL');
}
} catch (PDOException $e) {
error_log('Publisium: could not detach ' . $table . '.' . $column . ' from team accounts: ' . $e->getMessage());
}
}
}
}