WebOrbiton
v1.0.0.0

StocketBase

← Changes All versions
260 lines · 12.5 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. ​
  13. public static function site(): PDO
  14. {
  15. if (self::$site instanceof PDO) {
  16. return self::$site;
  17. }
  18. ​
  19. self::$site = self::connect(
  20. Config::get('DB_SITE_HOST', '127.0.0.1'),
  21. Config::get('DB_SITE_PORT', '3306'),
  22. Config::get('DB_SITE_NAME', ''),
  23. Config::get('DB_SITE_USER', ''),
  24. Config::get('DB_SITE_PASS', '')
  25. );
  26. ​
  27. self::ensureSiteSchema(self::$site);
  28. ​
  29. return self::$site;
  30. }
  31. ​
  32. public static function users(): PDO
  33. {
  34. if (self::$users instanceof PDO) {
  35. return self::$users;
  36. }
  37. ​
  38. self::$users = self::connect(
  39. Config::get('DB_USERS_HOST', '127.0.0.1'),
  40. Config::get('DB_USERS_PORT', '3306'),
  41. Config::get('DB_USERS_NAME', ''),
  42. Config::get('DB_USERS_USER', ''),
  43. Config::get('DB_USERS_PASS', '')
  44. );
  45. ​
  46. return self::$users;
  47. }
  48. ​
  49. private static function connect(?string $host, ?string $port, ?string $name, ?string $user, ?string $pass): PDO
  50. {
  51. $dsn = sprintf('mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4', $host, $port, $name);
  52. ​
  53. return new PDO($dsn, $user, $pass, [
  54. PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  55. PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  56. PDO::ATTR_EMULATE_PREPARES => false,
  57. ]);
  58. }
  59. ​
  60. private static function ensureSiteSchema(PDO $pdo): void
  61. {
  62. if (self::$schemaChecked) {
  63. return;
  64. }
  65. self::$schemaChecked = true;
  66. ​
  67. try {
  68. $dbName = $pdo->query('SELECT DATABASE()')->fetchColumn();
  69. if (!$dbName) {
  70. return;
  71. }
  72. ​
  73. $columnCheck = $pdo->prepare(
  74. 'SELECT COUNT(*) FROM information_schema.COLUMNS
  75. WHERE TABLE_SCHEMA = :db AND TABLE_NAME = :table AND COLUMN_NAME = :column'
  76. );
  77. ​
  78. $requiredColumns = [
  79. 'team_accounts' => [
  80. 'avatar_path' => "ALTER TABLE team_accounts ADD COLUMN avatar_path VARCHAR(255) NULL",
  81. 'totp_secret' => "ALTER TABLE team_accounts ADD COLUMN totp_secret VARCHAR(64) NULL",
  82. 'totp_enabled' => "ALTER TABLE team_accounts ADD COLUMN totp_enabled TINYINT(1) NOT NULL DEFAULT 0",
  83. 'totp_recovery' => "ALTER TABLE team_accounts ADD COLUMN totp_recovery TEXT NULL",
  84. 'totp_last_step' => "ALTER TABLE team_accounts ADD COLUMN totp_last_step BIGINT NULL",
  85. ],
  86. 'products' => [
  87. 'deleted_at' => "ALTER TABLE products ADD COLUMN deleted_at DATETIME NULL",
  88. 'trashed_status' => "ALTER TABLE products ADD COLUMN trashed_status VARCHAR(20) NULL",
  89. ],
  90. 'categories' => [
  91. 'show_in_menu' => "ALTER TABLE categories ADD COLUMN show_in_menu TINYINT(1) NOT NULL DEFAULT 1 AFTER sort_order",
  92. ],
  93. 'product_files' => [
  94. 'product_title' => "ALTER TABLE product_files ADD COLUMN product_title VARCHAR(255) NULL",
  95. 'detached_at' => "ALTER TABLE product_files ADD COLUMN detached_at DATETIME NULL",
  96. 'button_label' => "ALTER TABLE product_files ADD COLUMN button_label VARCHAR(120) NULL",
  97. 'download_count' => "ALTER TABLE product_files ADD COLUMN download_count INT UNSIGNED NOT NULL DEFAULT 0",
  98. ],
  99. 'nav_items' => [
  100. 'icon_key' => "ALTER TABLE nav_items ADD COLUMN icon_key VARCHAR(30) NULL AFTER target",
  101. 'icon_svg' => "ALTER TABLE nav_items ADD COLUMN icon_svg MEDIUMTEXT NULL AFTER icon_key",
  102. ],
  103. ];
  104. ​
  105. foreach ($requiredColumns as $table => $columns) {
  106. $tableCheck = $pdo->prepare(
  107. 'SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = :db AND TABLE_NAME = :table'
  108. );
  109. $tableCheck->execute(['db' => $dbName, 'table' => $table]);
  110. if ((int) $tableCheck->fetchColumn() === 0) {
  111. continue;
  112. }
  113. ​
  114. foreach ($columns as $column => $alterSql) {
  115. $columnCheck->execute(['db' => $dbName, 'table' => $table, 'column' => $column]);
  116. if ((int) $columnCheck->fetchColumn() === 0) {
  117. try {
  118. $pdo->exec($alterSql);
  119. } catch (PDOException $e) {
  120. error_log('StocketBase: could not auto-add column ' . $table . '.' . $column . ': ' . $e->getMessage());
  121. }
  122. }
  123. }
  124. }
  125. ​
  126. $requiredTables = [
  127. 'site_settings' => "CREATE TABLE site_settings (
  128. setting_key VARCHAR(120) NOT NULL PRIMARY KEY,
  129. setting_value LONGTEXT NULL,
  130. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  131. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  132. 'nav_items' => "CREATE TABLE nav_items (
  133. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  134. item_type ENUM('category','page','custom') NOT NULL,
  135. ref_id INT UNSIGNED NULL,
  136. label VARCHAR(120) NULL,
  137. url VARCHAR(255) NULL,
  138. target ENUM('_self','_blank') NOT NULL DEFAULT '_self',
  139. icon_key VARCHAR(30) NULL,
  140. icon_svg MEDIUMTEXT NULL,
  141. sort_order INT NOT NULL DEFAULT 0,
  142. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  143. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  144. KEY idx_nav_sort (sort_order)
  145. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  146. 'activity_log' => "CREATE TABLE activity_log (
  147. id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  148. user_id INT UNSIGNED NULL,
  149. user_name VARCHAR(120) NULL,
  150. action VARCHAR(60) NOT NULL,
  151. entity_type VARCHAR(30) NULL,
  152. entity_id INT UNSIGNED NULL,
  153. details VARCHAR(500) NULL,
  154. ip_address VARCHAR(45) NULL,
  155. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  156. KEY idx_activity_created (created_at)
  157. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  158. 'product_versions' => "CREATE TABLE product_versions (
  159. id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  160. product_id INT UNSIGNED NOT NULL,
  161. content_hash CHAR(40) NOT NULL,
  162. title VARCHAR(255) NOT NULL,
  163. excerpt TEXT NULL,
  164. content_blocks LONGTEXT NULL,
  165. category_id INT UNSIGNED NULL,
  166. cover_image_path VARCHAR(500) NULL,
  167. seo_title VARCHAR(255) NULL,
  168. seo_description TEXT NULL,
  169. price_cents INT UNSIGNED NULL,
  170. compare_at_price_cents INT UNSIGNED NULL,
  171. saved_by INT UNSIGNED NULL,
  172. saved_by_name VARCHAR(120) NULL,
  173. note VARCHAR(60) NOT NULL DEFAULT 'save',
  174. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  175. KEY idx_version_product (product_id, id)
  176. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  177. 'tags' => "CREATE TABLE tags (
  178. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  179. name VARCHAR(60) NOT NULL,
  180. slug VARCHAR(80) NOT NULL,
  181. UNIQUE KEY uniq_tag_slug (slug)
  182. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  183. 'product_tags' => "CREATE TABLE product_tags (
  184. product_id INT UNSIGNED NOT NULL,
  185. tag_id INT UNSIGNED NOT NULL,
  186. PRIMARY KEY (product_id, tag_id),
  187. KEY idx_product_tags_tag (tag_id)
  188. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  189. 'product_files' => "CREATE TABLE product_files (
  190. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  191. product_id INT UNSIGNED NOT NULL,
  192. original_name VARCHAR(190) NOT NULL,
  193. stored_name CHAR(32) NOT NULL,
  194. mime_type VARCHAR(120) NOT NULL DEFAULT 'application/octet-stream',
  195. size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  196. sha256 CHAR(64) NULL,
  197. sort_order INT NOT NULL DEFAULT 0,
  198. product_title VARCHAR(255) NULL,
  199. detached_at DATETIME NULL,
  200. button_label VARCHAR(120) NULL,
  201. download_count INT UNSIGNED NOT NULL DEFAULT 0,
  202. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  203. UNIQUE KEY uq_product_file_stored (stored_name),
  204. KEY idx_product_files_product (product_id)
  205. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  206. 'login_attempts' => "CREATE TABLE login_attempts (
  207. id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  208. scope VARCHAR(20) NOT NULL,
  209. identifier_hash CHAR(64) NOT NULL,
  210. ip_address VARCHAR(45) NOT NULL,
  211. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  212. KEY idx_attempt_identifier (scope, identifier_hash, created_at),
  213. KEY idx_attempt_ip (scope, ip_address, created_at)
  214. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  215. 'indexnow_log' => "CREATE TABLE indexnow_log (
  216. product_id INT UNSIGNED NOT NULL PRIMARY KEY,
  217. submitted_at DATETIME NOT NULL
  218. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  219. 'site_languages' => "CREATE TABLE site_languages (
  220. code VARCHAR(10) NOT NULL PRIMARY KEY,
  221. name VARCHAR(60) NOT NULL,
  222. sort_order INT NOT NULL DEFAULT 0,
  223. is_active TINYINT(1) NOT NULL DEFAULT 0
  224. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  225. 'content_translations' => "CREATE TABLE content_translations (
  226. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  227. entity_type VARCHAR(12) NOT NULL,
  228. entity_id INT UNSIGNED NOT NULL,
  229. lang VARCHAR(10) NOT NULL,
  230. title VARCHAR(255) NOT NULL DEFAULT '',
  231. excerpt TEXT NULL,
  232. content_blocks LONGTEXT NULL,
  233. seo_title VARCHAR(255) NULL,
  234. seo_description VARCHAR(500) NULL,
  235. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  236. UNIQUE KEY uniq_translation (entity_type, entity_id, lang),
  237. KEY idx_translation_lang (lang, entity_type)
  238. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  239. ];
  240. ​
  241. $tableExistsCheck = $pdo->prepare(
  242. 'SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = :db AND TABLE_NAME = :table'
  243. );
  244. ​
  245. foreach ($requiredTables as $table => $createSql) {
  246. $tableExistsCheck->execute(['db' => $dbName, 'table' => $table]);
  247. if ((int) $tableExistsCheck->fetchColumn() === 0) {
  248. try {
  249. $pdo->exec($createSql);
  250. } catch (PDOException $e) {
  251. error_log('StocketBase: could not auto-create table ' . $table . ': ' . $e->getMessage());
  252. }
  253. }
  254. }
  255. } catch (PDOException $e) {
  256. error_log('StocketBase: schema auto-check failed: ' . $e->getMessage());
  257. }
  258. }
  259. }
  260. ​