v1.0.0.1

StocketBase

← Changes All versions

311 lines · 15.5 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",
],
'products' => [
'deleted_at' => "ALTER TABLE products ADD COLUMN deleted_at DATETIME NULL",
'trashed_status' => "ALTER TABLE products ADD COLUMN trashed_status VARCHAR(20) NULL",
],
'pages' => [
'is_home' => "ALTER TABLE pages ADD COLUMN is_home TINYINT(1) NOT NULL DEFAULT 0",
],
'categories' => [
'show_in_menu' => "ALTER TABLE categories ADD COLUMN show_in_menu TINYINT(1) NOT NULL DEFAULT 1 AFTER sort_order",
],
'product_files' => [
'product_title' => "ALTER TABLE product_files ADD COLUMN product_title VARCHAR(255) NULL",
'detached_at' => "ALTER TABLE product_files ADD COLUMN detached_at DATETIME NULL",
'button_label' => "ALTER TABLE product_files ADD COLUMN button_label VARCHAR(120) NULL",
'download_count' => "ALTER TABLE product_files ADD COLUMN download_count INT UNSIGNED NOT NULL DEFAULT 0",
],
'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('StocketBase: could not auto-add column ' . $table . '.' . $column . ': ' . $e->getMessage());
}
}
}
}
$authorColumns = ['products' => 'created_by', 'pages' => 'author_id', 'ads' => 'created_by', 'discounts' => 'created_by'];
$authorState = $pdo->prepare(
"SELECT c.TABLE_NAME, c.IS_NULLABLE, k.CONSTRAINT_NAME, r.DELETE_RULE
FROM information_schema.COLUMNS c
LEFT JOIN information_schema.KEY_COLUMN_USAGE k
ON k.TABLE_SCHEMA = c.TABLE_SCHEMA AND k.TABLE_NAME = c.TABLE_NAME AND k.COLUMN_NAME = c.COLUMN_NAME AND k.REFERENCED_TABLE_NAME = 'team_accounts'
LEFT 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 c.TABLE_SCHEMA = :db
AND ((c.TABLE_NAME = 'products' AND c.COLUMN_NAME = 'created_by')
OR (c.TABLE_NAME = 'pages' AND c.COLUMN_NAME = 'author_id')
OR (c.TABLE_NAME = 'ads' AND c.COLUMN_NAME = 'created_by')
OR (c.TABLE_NAME = 'discounts' AND c.COLUMN_NAME = 'created_by'))"
);
$authorState->execute(['db' => $dbName]);
foreach ($authorState->fetchAll(PDO::FETCH_ASSOC) as $state) {
$table = (string) $state['TABLE_NAME'];
$column = $authorColumns[$table] ?? null;
if ($column === null) {
continue;
}
$constraint = (string) ($state['CONSTRAINT_NAME'] ?? '');
$needsNullable = $state['IS_NULLABLE'] === 'NO';
$needsRule = $constraint !== '' && strtoupper((string) $state['DELETE_RULE']) !== 'SET NULL';
if (!$needsNullable && !$needsRule) {
continue;
}
if (preg_match('/^[A-Za-z0-9_]+$/', $constraint) !== 1 && $constraint !== '') {
continue;
}
try {
if ($needsRule) {
$pdo->exec('ALTER TABLE ' . $table . ' DROP FOREIGN KEY ' . $constraint);
}
if ($needsNullable) {
$pdo->exec('ALTER TABLE ' . $table . ' MODIFY ' . $column . ' INT UNSIGNED NULL');
}
if ($needsRule) {
$pdo->exec('ALTER TABLE ' . $table . ' ADD CONSTRAINT ' . $constraint . ' FOREIGN KEY (' . $column . ') REFERENCES team_accounts(id) ON DELETE SET NULL');
}
if ($table === 'pages') {
$pdo->exec('UPDATE pages SET author_id = NULL WHERE is_home = 1');
}
} catch (PDOException $e) {
error_log('StocketBase: could not update author key on ' . $table . ': ' . $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",
'product_versions' => "CREATE TABLE product_versions (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product_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,
price_cents INT UNSIGNED NULL,
compare_at_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_product (product_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",
'product_tags' => "CREATE TABLE product_tags (
product_id INT UNSIGNED NOT NULL,
tag_id INT UNSIGNED NOT NULL,
PRIMARY KEY (product_id, tag_id),
KEY idx_product_tags_tag (tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
'product_files' => "CREATE TABLE product_files (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product_id INT UNSIGNED NOT NULL,
original_name VARCHAR(190) NOT NULL,
stored_name CHAR(32) NOT NULL,
mime_type VARCHAR(120) NOT NULL DEFAULT 'application/octet-stream',
size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
sha256 CHAR(64) NULL,
sort_order INT NOT NULL DEFAULT 0,
product_title VARCHAR(255) NULL,
detached_at DATETIME NULL,
button_label VARCHAR(120) NULL,
download_count INT UNSIGNED NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uq_product_file_stored (stored_name),
KEY idx_product_files_product (product_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",
'indexnow_log' => "CREATE TABLE indexnow_log (
product_id INT UNSIGNED NOT NULL PRIMARY KEY,
submitted_at DATETIME NOT NULL
) 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('StocketBase: could not auto-create table ' . $table . ': ' . $e->getMessage());
}
}
}
} catch (PDOException $e) {
error_log('StocketBase: schema auto-check failed: ' . $e->getMessage());
}
}
}