WebOrbiton
v1.1.0.0

StocketBase

490 lines · 25.2 KB
  1. <?php
  2. ​
  3. declare(strict_types=1);
  4. ​
  5. require_once __DIR__ . '/config.php';
  6. ​
  7. final class Database
  8. {
  9. private static ?PDO $site = null;
  10. private static ?PDO $users = null;
  11. private static bool $schemaChecked = false;
  12. private static bool $usersSchemaChecked = false;
  13. ​
  14. public static function site(): PDO
  15. {
  16. if (self::$site instanceof PDO) {
  17. return self::$site;
  18. }
  19. ​
  20. self::$site = self::connect(
  21. Config::get('DB_SITE_HOST', '127.0.0.1'),
  22. Config::get('DB_SITE_PORT', '3306'),
  23. Config::get('DB_SITE_NAME', ''),
  24. Config::get('DB_SITE_USER', ''),
  25. Config::get('DB_SITE_PASS', '')
  26. );
  27. ​
  28. self::ensureSiteSchema(self::$site);
  29. ​
  30. return self::$site;
  31. }
  32. ​
  33. public static function users(): PDO
  34. {
  35. if (self::$users instanceof PDO) {
  36. return self::$users;
  37. }
  38. ​
  39. self::$users = self::connect(
  40. Config::get('DB_USERS_HOST', '127.0.0.1'),
  41. Config::get('DB_USERS_PORT', '3306'),
  42. Config::get('DB_USERS_NAME', ''),
  43. Config::get('DB_USERS_USER', ''),
  44. Config::get('DB_USERS_PASS', '')
  45. );
  46. ​
  47. self::ensureUsersSchema(self::$users);
  48. ​
  49. return self::$users;
  50. }
  51. ​
  52. private static function ensureUsersSchema(PDO $pdo): void
  53. {
  54. if (self::$usersSchemaChecked) {
  55. return;
  56. }
  57. self::$usersSchemaChecked = true;
  58. ​
  59. try {
  60. $dbName = (string) $pdo->query('SELECT DATABASE()')->fetchColumn();
  61. if ($dbName === '') {
  62. return;
  63. }
  64. ​
  65. $tablesStatement = $pdo->prepare('SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = :db');
  66. $tablesStatement->execute(['db' => $dbName]);
  67. $existingTables = array_map('strtolower', $tablesStatement->fetchAll(PDO::FETCH_COLUMN));
  68. ​
  69. $columnsStatement = $pdo->prepare('SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = :db AND TABLE_NAME IN (\'orders\', \'user_accounts\')');
  70. $columnsStatement->execute(['db' => $dbName]);
  71. $existingColumns = [];
  72. foreach ($columnsStatement->fetchAll() as $row) {
  73. $existingColumns[strtolower((string) $row['TABLE_NAME'])][strtolower((string) $row['COLUMN_NAME'])] = true;
  74. }
  75. ​
  76. $requiredColumns = [
  77. 'orders' => [
  78. 'payment_method' => 'ALTER TABLE orders ADD COLUMN payment_method VARCHAR(30) NULL AFTER provider',
  79. 'shipping_method' => 'ALTER TABLE orders ADD COLUMN shipping_method VARCHAR(160) NULL AFTER shipping_cents',
  80. 'shipping_phone' => 'ALTER TABLE orders ADD COLUMN shipping_phone VARCHAR(40) NULL AFTER shipping_country',
  81. 'billing_company' => 'ALTER TABLE orders ADD COLUMN billing_company VARCHAR(190) NULL AFTER billing_name',
  82. 'billing_tax_id' => 'ALTER TABLE orders ADD COLUMN billing_tax_id VARCHAR(60) NULL AFTER billing_company',
  83. 'prices_include_tax' => 'ALTER TABLE orders ADD COLUMN prices_include_tax TINYINT(1) NOT NULL DEFAULT 0 AFTER tax_cents',
  84. 'tax_rate_bp' => 'ALTER TABLE orders ADD COLUMN tax_rate_bp INT UNSIGNED NOT NULL DEFAULT 0 AFTER prices_include_tax',
  85. 'access_token' => 'ALTER TABLE orders ADD COLUMN access_token CHAR(64) NULL, ADD UNIQUE KEY uq_order_access_token (access_token)',
  86. 'invoice_number' => 'ALTER TABLE orders ADD COLUMN invoice_number VARCHAR(40) NULL',
  87. 'paid_at' => 'ALTER TABLE orders ADD COLUMN paid_at DATETIME NULL',
  88. 'canceled_at' => 'ALTER TABLE orders ADD COLUMN canceled_at DATETIME NULL',
  89. 'stock_reserved' => 'ALTER TABLE orders ADD COLUMN stock_reserved TINYINT(1) NOT NULL DEFAULT 0',
  90. 'cod_fee_cents' => 'ALTER TABLE orders ADD COLUMN cod_fee_cents INT UNSIGNED NOT NULL DEFAULT 0 AFTER shipping_cents',
  91. ],
  92. 'user_accounts' => [
  93. 'phone' => 'ALTER TABLE user_accounts ADD COLUMN phone VARCHAR(40) NULL AFTER display_name',
  94. 'marketing_opt_in' => 'ALTER TABLE user_accounts ADD COLUMN marketing_opt_in TINYINT(1) NOT NULL DEFAULT 0',
  95. ],
  96. ];
  97. ​
  98. foreach ($requiredColumns as $table => $columns) {
  99. if (!in_array($table, $existingTables, true)) {
  100. continue;
  101. }
  102. foreach ($columns as $column => $sql) {
  103. if (isset($existingColumns[$table][$column])) {
  104. continue;
  105. }
  106. try {
  107. $pdo->exec($sql);
  108. } catch (PDOException $e) {
  109. error_log('StocketBase: could not auto-add column ' . $table . '.' . $column . ': ' . $e->getMessage());
  110. }
  111. }
  112. }
  113. ​
  114. foreach (self::usersTableDefinitions() as $table => $sql) {
  115. if (in_array($table, $existingTables, true)) {
  116. continue;
  117. }
  118. try {
  119. $pdo->exec($sql);
  120. } catch (PDOException $e) {
  121. error_log('StocketBase: could not auto-create table ' . $table . ': ' . $e->getMessage());
  122. }
  123. }
  124. } catch (PDOException $e) {
  125. error_log('StocketBase: users schema auto-check failed: ' . $e->getMessage());
  126. }
  127. }
  128. ​
  129. public static function usersTableDefinitions(): array
  130. {
  131. return [
  132. 'user_addresses' => "CREATE TABLE user_addresses (
  133. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  134. user_account_id INT UNSIGNED NOT NULL,
  135. label VARCHAR(60) NULL,
  136. full_name VARCHAR(190) NOT NULL,
  137. company VARCHAR(190) NULL,
  138. tax_id VARCHAR(60) NULL,
  139. address_line1 VARCHAR(255) NOT NULL,
  140. address_line2 VARCHAR(255) NULL,
  141. city VARCHAR(120) NOT NULL,
  142. region VARCHAR(120) NULL,
  143. postal_code VARCHAR(30) NOT NULL,
  144. country CHAR(2) NOT NULL,
  145. phone VARCHAR(40) NULL,
  146. is_default_shipping TINYINT(1) NOT NULL DEFAULT 0,
  147. is_default_billing TINYINT(1) NOT NULL DEFAULT 0,
  148. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  149. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  150. KEY idx_address_account (user_account_id),
  151. CONSTRAINT fk_address_account FOREIGN KEY (user_account_id) REFERENCES user_accounts(id) ON DELETE CASCADE
  152. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  153. 'account_tokens' => "CREATE TABLE account_tokens (
  154. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  155. user_account_id INT UNSIGNED NOT NULL,
  156. purpose VARCHAR(20) NOT NULL,
  157. token_hash CHAR(64) NOT NULL,
  158. expires_at DATETIME NOT NULL,
  159. used_at DATETIME NULL,
  160. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  161. UNIQUE KEY uq_account_token_hash (token_hash),
  162. KEY idx_account_token_user (user_account_id, purpose),
  163. CONSTRAINT fk_account_token_account FOREIGN KEY (user_account_id) REFERENCES user_accounts(id) ON DELETE CASCADE
  164. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  165. 'license_keys' => "CREATE TABLE license_keys (
  166. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  167. product_id INT UNSIGNED NOT NULL,
  168. license_key VARCHAR(255) NOT NULL,
  169. source ENUM('pool','generated') NOT NULL DEFAULT 'pool',
  170. order_id INT UNSIGNED NULL,
  171. order_item_id INT UNSIGNED NULL,
  172. user_account_id INT UNSIGNED NULL,
  173. assigned_at DATETIME NULL,
  174. revoked_at DATETIME NULL,
  175. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  176. UNIQUE KEY uq_license_product_key (product_id, license_key),
  177. KEY idx_license_free (product_id, order_id),
  178. KEY idx_license_order (order_id),
  179. KEY idx_license_account (user_account_id)
  180. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  181. ];
  182. }
  183. ​
  184. private static function connect(?string $host, ?string $port, ?string $name, ?string $user, ?string $pass): PDO
  185. {
  186. $dsn = sprintf('mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4', $host, $port, $name);
  187. ​
  188. return new PDO($dsn, $user, $pass, [
  189. PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  190. PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  191. PDO::ATTR_EMULATE_PREPARES => false,
  192. ]);
  193. }
  194. ​
  195. private static function ensureSettingsKey(PDO $pdo, string $dbName): void
  196. {
  197. $tableCheck = $pdo->prepare("SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = :db AND TABLE_NAME = 'site_settings'");
  198. $tableCheck->execute(['db' => $dbName]);
  199. if ((int) $tableCheck->fetchColumn() === 0) {
  200. return;
  201. }
  202. ​
  203. $keyCheck = $pdo->prepare(
  204. "SELECT COUNT(*) FROM information_schema.STATISTICS
  205. WHERE TABLE_SCHEMA = :db AND TABLE_NAME = 'site_settings' AND COLUMN_NAME = 'setting_key' AND NON_UNIQUE = 0 AND SEQ_IN_INDEX = 1"
  206. );
  207. $keyCheck->execute(['db' => $dbName]);
  208. if ((int) $keyCheck->fetchColumn() > 0) {
  209. return;
  210. }
  211. ​
  212. try {
  213. $helperCheck = $pdo->prepare(
  214. "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = :db AND TABLE_NAME = 'site_settings' AND COLUMN_NAME = 'sb_row_id'"
  215. );
  216. $helperCheck->execute(['db' => $dbName]);
  217. if ((int) $helperCheck->fetchColumn() === 0) {
  218. $pdo->exec('ALTER TABLE site_settings ADD COLUMN sb_row_id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE');
  219. }
  220. $pdo->exec('DELETE older FROM site_settings older JOIN site_settings newer ON newer.setting_key = older.setting_key AND newer.sb_row_id > older.sb_row_id');
  221. $pdo->exec('ALTER TABLE site_settings ADD PRIMARY KEY (setting_key)');
  222. $pdo->exec('ALTER TABLE site_settings DROP COLUMN sb_row_id');
  223. } catch (PDOException $e) {
  224. error_log('StocketBase: could not repair site_settings key: ' . $e->getMessage());
  225. }
  226. }
  227. ​
  228. private static function ensureSiteSchema(PDO $pdo): void
  229. {
  230. if (self::$schemaChecked) {
  231. return;
  232. }
  233. self::$schemaChecked = true;
  234. ​
  235. try {
  236. $dbName = $pdo->query('SELECT DATABASE()')->fetchColumn();
  237. if (!$dbName) {
  238. return;
  239. }
  240. ​
  241. $columnCheck = $pdo->prepare(
  242. 'SELECT COUNT(*) FROM information_schema.COLUMNS
  243. WHERE TABLE_SCHEMA = :db AND TABLE_NAME = :table AND COLUMN_NAME = :column'
  244. );
  245. ​
  246. $requiredColumns = [
  247. 'team_accounts' => [
  248. 'avatar_path' => "ALTER TABLE team_accounts ADD COLUMN avatar_path VARCHAR(255) NULL",
  249. 'totp_secret' => "ALTER TABLE team_accounts ADD COLUMN totp_secret VARCHAR(64) NULL",
  250. 'totp_enabled' => "ALTER TABLE team_accounts ADD COLUMN totp_enabled TINYINT(1) NOT NULL DEFAULT 0",
  251. 'totp_recovery' => "ALTER TABLE team_accounts ADD COLUMN totp_recovery TEXT NULL",
  252. 'totp_last_step' => "ALTER TABLE team_accounts ADD COLUMN totp_last_step BIGINT NULL",
  253. ],
  254. 'products' => [
  255. 'deleted_at' => "ALTER TABLE products ADD COLUMN deleted_at DATETIME NULL",
  256. 'trashed_status' => "ALTER TABLE products ADD COLUMN trashed_status VARCHAR(20) NULL",
  257. 'license_mode' => "ALTER TABLE products ADD COLUMN license_mode VARCHAR(10) NOT NULL DEFAULT 'none'",
  258. 'license_pattern' => "ALTER TABLE products ADD COLUMN license_pattern VARCHAR(80) NULL",
  259. 'low_stock_alerted_at' => "ALTER TABLE products ADD COLUMN low_stock_alerted_at DATETIME NULL",
  260. ],
  261. 'pages' => [
  262. 'is_home' => "ALTER TABLE pages ADD COLUMN is_home TINYINT(1) NOT NULL DEFAULT 0",
  263. ],
  264. 'ads' => [
  265. 'consent_run' => "ALTER TABLE ads ADD COLUMN consent_run VARCHAR(20) NULL AFTER link_url",
  266. ],
  267. 'analytics_scripts' => [
  268. 'consent_category' => "ALTER TABLE analytics_scripts ADD COLUMN consent_category VARCHAR(20) NOT NULL DEFAULT 'analytics' AFTER requires_consent",
  269. ],
  270. 'categories' => [
  271. 'show_in_menu' => "ALTER TABLE categories ADD COLUMN show_in_menu TINYINT(1) NOT NULL DEFAULT 1 AFTER sort_order",
  272. ],
  273. 'product_files' => [
  274. 'product_title' => "ALTER TABLE product_files ADD COLUMN product_title VARCHAR(255) NULL",
  275. 'detached_at' => "ALTER TABLE product_files ADD COLUMN detached_at DATETIME NULL",
  276. 'button_label' => "ALTER TABLE product_files ADD COLUMN button_label VARCHAR(120) NULL",
  277. 'download_count' => "ALTER TABLE product_files ADD COLUMN download_count INT UNSIGNED NOT NULL DEFAULT 0",
  278. ],
  279. 'nav_items' => [
  280. 'icon_key' => "ALTER TABLE nav_items ADD COLUMN icon_key VARCHAR(30) NULL AFTER target",
  281. 'icon_svg' => "ALTER TABLE nav_items ADD COLUMN icon_svg MEDIUMTEXT NULL AFTER icon_key",
  282. ],
  283. ];
  284. ​
  285. foreach ($requiredColumns as $table => $columns) {
  286. $tableCheck = $pdo->prepare(
  287. 'SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = :db AND TABLE_NAME = :table'
  288. );
  289. $tableCheck->execute(['db' => $dbName, 'table' => $table]);
  290. if ((int) $tableCheck->fetchColumn() === 0) {
  291. continue;
  292. }
  293. ​
  294. foreach ($columns as $column => $alterSql) {
  295. $columnCheck->execute(['db' => $dbName, 'table' => $table, 'column' => $column]);
  296. if ((int) $columnCheck->fetchColumn() === 0) {
  297. try {
  298. $pdo->exec($alterSql);
  299. } catch (PDOException $e) {
  300. error_log('StocketBase: could not auto-add column ' . $table . '.' . $column . ': ' . $e->getMessage());
  301. }
  302. }
  303. }
  304. }
  305. ​
  306. self::ensureSettingsKey($pdo, (string) $dbName);
  307. ​
  308. $authorColumns = ['products' => 'created_by', 'pages' => 'author_id', 'ads' => 'created_by', 'discounts' => 'created_by'];
  309. $authorState = $pdo->prepare(
  310. "SELECT c.TABLE_NAME, c.IS_NULLABLE, k.CONSTRAINT_NAME, r.DELETE_RULE
  311. FROM information_schema.COLUMNS c
  312. LEFT JOIN information_schema.KEY_COLUMN_USAGE k
  313. 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'
  314. LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS r
  315. ON r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND r.TABLE_NAME = k.TABLE_NAME
  316. WHERE c.TABLE_SCHEMA = :db
  317. AND ((c.TABLE_NAME = 'products' AND c.COLUMN_NAME = 'created_by')
  318. OR (c.TABLE_NAME = 'pages' AND c.COLUMN_NAME = 'author_id')
  319. OR (c.TABLE_NAME = 'ads' AND c.COLUMN_NAME = 'created_by')
  320. OR (c.TABLE_NAME = 'discounts' AND c.COLUMN_NAME = 'created_by'))"
  321. );
  322. $authorState->execute(['db' => $dbName]);
  323. foreach ($authorState->fetchAll(PDO::FETCH_ASSOC) as $state) {
  324. $table = (string) $state['TABLE_NAME'];
  325. $column = $authorColumns[$table] ?? null;
  326. if ($column === null) {
  327. continue;
  328. }
  329. $constraint = (string) ($state['CONSTRAINT_NAME'] ?? '');
  330. $needsNullable = $state['IS_NULLABLE'] === 'NO';
  331. $needsRule = $constraint !== '' && strtoupper((string) $state['DELETE_RULE']) !== 'SET NULL';
  332. if (!$needsNullable && !$needsRule) {
  333. continue;
  334. }
  335. if (preg_match('/^[A-Za-z0-9_]+$/', $constraint) !== 1 && $constraint !== '') {
  336. continue;
  337. }
  338. try {
  339. if ($needsRule) {
  340. $pdo->exec('ALTER TABLE ' . $table . ' DROP FOREIGN KEY ' . $constraint);
  341. }
  342. if ($needsNullable) {
  343. $pdo->exec('ALTER TABLE ' . $table . ' MODIFY ' . $column . ' INT UNSIGNED NULL');
  344. }
  345. if ($needsRule) {
  346. $pdo->exec('ALTER TABLE ' . $table . ' ADD CONSTRAINT ' . $constraint . ' FOREIGN KEY (' . $column . ') REFERENCES team_accounts(id) ON DELETE SET NULL');
  347. }
  348. if ($table === 'pages') {
  349. $pdo->exec('UPDATE pages SET author_id = NULL WHERE is_home = 1');
  350. }
  351. } catch (PDOException $e) {
  352. error_log('StocketBase: could not update author key on ' . $table . ': ' . $e->getMessage());
  353. }
  354. }
  355. ​
  356. $requiredTables = [
  357. 'site_settings' => "CREATE TABLE site_settings (
  358. setting_key VARCHAR(120) NOT NULL PRIMARY KEY,
  359. setting_value LONGTEXT NULL,
  360. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  361. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  362. 'nav_items' => "CREATE TABLE nav_items (
  363. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  364. item_type ENUM('category','page','custom') NOT NULL,
  365. ref_id INT UNSIGNED NULL,
  366. label VARCHAR(120) NULL,
  367. url VARCHAR(255) NULL,
  368. target ENUM('_self','_blank') NOT NULL DEFAULT '_self',
  369. icon_key VARCHAR(30) NULL,
  370. icon_svg MEDIUMTEXT NULL,
  371. sort_order INT NOT NULL DEFAULT 0,
  372. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  373. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  374. KEY idx_nav_sort (sort_order)
  375. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  376. 'activity_log' => "CREATE TABLE activity_log (
  377. id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  378. user_id INT UNSIGNED NULL,
  379. user_name VARCHAR(120) NULL,
  380. action VARCHAR(60) NOT NULL,
  381. entity_type VARCHAR(30) NULL,
  382. entity_id INT UNSIGNED NULL,
  383. details VARCHAR(500) NULL,
  384. ip_address VARCHAR(45) NULL,
  385. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  386. KEY idx_activity_created (created_at)
  387. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  388. 'product_versions' => "CREATE TABLE product_versions (
  389. id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  390. product_id INT UNSIGNED NOT NULL,
  391. content_hash CHAR(40) NOT NULL,
  392. title VARCHAR(255) NOT NULL,
  393. excerpt TEXT NULL,
  394. content_blocks LONGTEXT NULL,
  395. category_id INT UNSIGNED NULL,
  396. cover_image_path VARCHAR(500) NULL,
  397. seo_title VARCHAR(255) NULL,
  398. seo_description TEXT NULL,
  399. price_cents INT UNSIGNED NULL,
  400. compare_at_price_cents INT UNSIGNED NULL,
  401. saved_by INT UNSIGNED NULL,
  402. saved_by_name VARCHAR(120) NULL,
  403. note VARCHAR(60) NOT NULL DEFAULT 'save',
  404. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  405. KEY idx_version_product (product_id, id)
  406. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  407. 'tags' => "CREATE TABLE tags (
  408. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  409. name VARCHAR(60) NOT NULL,
  410. slug VARCHAR(80) NOT NULL,
  411. UNIQUE KEY uniq_tag_slug (slug)
  412. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  413. 'product_tags' => "CREATE TABLE product_tags (
  414. product_id INT UNSIGNED NOT NULL,
  415. tag_id INT UNSIGNED NOT NULL,
  416. PRIMARY KEY (product_id, tag_id),
  417. KEY idx_product_tags_tag (tag_id)
  418. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  419. 'product_files' => "CREATE TABLE product_files (
  420. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  421. product_id INT UNSIGNED NOT NULL,
  422. original_name VARCHAR(190) NOT NULL,
  423. stored_name CHAR(32) NOT NULL,
  424. mime_type VARCHAR(120) NOT NULL DEFAULT 'application/octet-stream',
  425. size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  426. sha256 CHAR(64) NULL,
  427. sort_order INT NOT NULL DEFAULT 0,
  428. product_title VARCHAR(255) NULL,
  429. detached_at DATETIME NULL,
  430. button_label VARCHAR(120) NULL,
  431. download_count INT UNSIGNED NOT NULL DEFAULT 0,
  432. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  433. UNIQUE KEY uq_product_file_stored (stored_name),
  434. KEY idx_product_files_product (product_id)
  435. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  436. 'login_attempts' => "CREATE TABLE login_attempts (
  437. id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  438. scope VARCHAR(20) NOT NULL,
  439. identifier_hash CHAR(64) NOT NULL,
  440. ip_address VARCHAR(45) NOT NULL,
  441. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  442. KEY idx_attempt_identifier (scope, identifier_hash, created_at),
  443. KEY idx_attempt_ip (scope, ip_address, created_at)
  444. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  445. 'indexnow_log' => "CREATE TABLE indexnow_log (
  446. product_id INT UNSIGNED NOT NULL PRIMARY KEY,
  447. submitted_at DATETIME NOT NULL
  448. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  449. 'site_languages' => "CREATE TABLE site_languages (
  450. code VARCHAR(10) NOT NULL PRIMARY KEY,
  451. name VARCHAR(60) NOT NULL,
  452. sort_order INT NOT NULL DEFAULT 0,
  453. is_active TINYINT(1) NOT NULL DEFAULT 0
  454. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  455. 'content_translations' => "CREATE TABLE content_translations (
  456. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  457. entity_type VARCHAR(12) NOT NULL,
  458. entity_id INT UNSIGNED NOT NULL,
  459. lang VARCHAR(10) NOT NULL,
  460. title VARCHAR(255) NOT NULL DEFAULT '',
  461. excerpt TEXT NULL,
  462. content_blocks LONGTEXT NULL,
  463. seo_title VARCHAR(255) NULL,
  464. seo_description VARCHAR(500) NULL,
  465. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  466. UNIQUE KEY uniq_translation (entity_type, entity_id, lang),
  467. KEY idx_translation_lang (lang, entity_type)
  468. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  469. ];
  470. ​
  471. $tableExistsCheck = $pdo->prepare(
  472. 'SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = :db AND TABLE_NAME = :table'
  473. );
  474. ​
  475. foreach ($requiredTables as $table => $createSql) {
  476. $tableExistsCheck->execute(['db' => $dbName, 'table' => $table]);
  477. if ((int) $tableExistsCheck->fetchColumn() === 0) {
  478. try {
  479. $pdo->exec($createSql);
  480. } catch (PDOException $e) {
  481. error_log('StocketBase: could not auto-create table ' . $table . ': ' . $e->getMessage());
  482. }
  483. }
  484. }
  485. } catch (PDOException $e) {
  486. error_log('StocketBase: schema auto-check failed: ' . $e->getMessage());
  487. }
  488. }
  489. }
  490. ​