File scripts/setup_database.php

1:	#!/usr/bin/env php
2:
3:	<?php
4:
5:	define('APP_PATH', dirname(dirname(__FILE__)).'/');
6:	define('VENDOR_PATH', APP_PATH.'vendor/');
7:	define('MIGRATIONS_PATH', APP_PATH.'database/migrations');
8:
9:	require VENDOR_PATH.'autoload.php';
10:	require APP_PATH.'lib/framework.php';
11:
12:	$environment = Hm_Environment::getInstance();
13:	$environment->load();
14:
15:	/* get config object */
16:	$config = new Hm_Site_Config_File();
17:	$environment->define_default_constants($config);
18:
19:	$session_type = $config->get('session_type');
20:	$auth_type = $config->get('auth_type');
21:	$user_config_type = $config->get('user_config_type');
22:	$db_driver = $config->get('db_driver');
23:	define('SCHEMA_PATH', APP_PATH.'database/'.$db_driver.'_schema.sql');
24:
25:	$connected = false;
26:
27:	// NOTE: these sql commands could be db agnostic if we change the blobs to text
28:	// Check required extensions for the DB driver
29:	checkRequiredExtensions($db_driver);
30:
31:	$connection_tries = 0;
32:	$max_tries = 10;
33:
34:	while (!$connected) {
35:	$connection_tries++;
36:
37:	$conn = Hm_DB::connect($config);
38:
39:	if ($conn !== false) {
40:	printf("Database connection successful ...\n");
41:	$connected = true;
42:	} else {
43:	printf("Attempting to connect to database ... ({$connection_tries}/{$max_tries})\n");
44:	sleep(1);
45:	}
46:
47:	if ($connection_tries >= $max_tries) {
48:	error_log('Unable to connect to database');
49:	exit(1);
50:	}
51:	}
52:
53:	// Setup database and run migrations
54:	setupDatabase($conn, SCHEMA_PATH, MIGRATIONS_PATH);
55:
56:	print("\nDb setup finished\n");
57:
58:	/**
59:	* Checks for required extensions based on the DB driver.
60:	*/
61:	function checkRequiredExtensions(string $db_driver) {
62:	$extensions = match ($db_driver) {
63:	'mysql' => ['mysqli', 'mysqlnd', 'pdo_mysql'],
64:	'pgsql' => ['pgsql', 'pdo_pgsql'],
65:	'sqlite' => [],
66:	default => [],
67:	};
68:
69:	$missing_extensions = array_filter($extensions, fn($ext) => !extension_loaded($ext));
70:
71:	if (!empty($missing_extensions)) {
72:	error_log('Missing required extensions: ' . implode(', ', $missing_extensions));
73:	exit(1);
74:	}
75:	}
76:
77:	/**
78:	* Initializes the database and runs migrations.
79:	*/
80:	function setupDatabase(PDO $pdo, string $schemaFile, string $migrationDir) {
81:	if (isDatabaseEmpty($pdo)) {
82:	echo "Database is empty. Initializing...\n";
83:	initializeDatabase($pdo, $schemaFile, $migrationDir);
84:	} else {
85:	echo "Database detected. Running migrations...\n";
86:	ensureMigrationsTable($pdo);
87:	runMigrations($pdo, $migrationDir);
88:	}
89:	}
90:
91:	/**
92:	* Checks if the database is empty (no tables exist).
93:	*/
94:	function isDatabaseEmpty(PDO $pdo): bool {
95:	global $db_driver;
96:
97:	$checkTablesSql = match ($db_driver) {
98:	'mysql' => "SHOW TABLES;",
99:	'pgsql' => "SELECT table_name FROM information_schema.tables WHERE table_schema='public';",
100:	'sqlite' => "SELECT name FROM sqlite_master WHERE type='table';",
101:	default => throw new Exception("Unsupported database driver: " . $db_driver),
102:	};
103:
104:	$tables = $pdo->query($checkTablesSql)->fetchAll(PDO::FETCH_COLUMN);
105:
106:	return empty($tables);
107:	}
108:
109:	/**
110:	* Ensures the `migrations` table exists for existing databases.
111:	*/
112:	function ensureMigrationsTable(PDO $pdo) {
113:	global $db_driver;
114:
115:	try {
116:	$pdo->query("SELECT 1 FROM migrations LIMIT 1");
117:	} catch (PDOException $e) {
118:	echo "Migrations table not found. Creating it...\n";
119:
120:	$createTableSql = match ($db_driver) {
121:	'mysql' => "
122:	CREATE TABLE migrations (
123:	id INT AUTO_INCREMENT PRIMARY KEY,
124:	migration VARCHAR(255) NOT NULL,
125:	batch INT NOT NULL,
126:	applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
127:	);
128:	",
129:	'pgsql' => "
130:	CREATE TABLE migrations (
131:	id SERIAL PRIMARY KEY,
132:	migration VARCHAR(255) NOT NULL,
133:	batch INT NOT NULL,
134:	applied_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
135:	);
136:	",
137:	'sqlite' => "
138:	CREATE TABLE migrations (
139:	id INTEGER PRIMARY KEY AUTOINCREMENT,
140:	migration TEXT NOT NULL,
141:	batch INTEGER NOT NULL,
142:	applied_at TEXT DEFAULT CURRENT_TIMESTAMP
143:	);
144:	",
145:	default => throw new Exception("Unsupported database driver: " . $db_driver),
146:	};
147:
148:	$pdo->exec($createTableSql);
149:	echo "Migrations table created.\n";
150:	}
151:	}
152:
153:	/**
154:	* Initializes the database schema and populates migrations for new installations.
155:	*/
156:	function initializeDatabase(PDO $pdo, string $schemaFile, string $migrationDir) {
157:	global $db_driver;
158:	$schemaSql = file_get_contents($schemaFile);
159:	$pdo->exec($schemaSql);
160:	echo "Database schema initialized.\n";
161:
162:	ensureMigrationsTable($pdo);
163:
164:	$migrationFiles = glob($migrationDir .'/'.$db_driver.'/*.sql');
165:	$stmt = $pdo->prepare("INSERT INTO migrations (migration, batch) VALUES (:migration, :batch)");
166:	foreach ($migrationFiles as $file) {
167:	$stmt->execute([
168:	'migration' => basename($file),
169:	'batch' => 0, // Mark as pre-applied
170:	]);
171:	}
172:
173:	echo "Migrations table populated for new installation.\n";
174:	}
175:
176:	/**
177:	* Executes pending migrations for existing databases.
178:	*/
179:	function runMigrations(PDO $pdo, string $migrationDir) {
180:	echo "Running migrations...\n";
181:	global $db_driver;
182:
183:	$executed = $pdo->query("SELECT migration FROM migrations")->fetchAll(PDO::FETCH_COLUMN);
184:	$migrationFiles = glob($migrationDir .'/'.$db_driver.'/*.sql');
185:	$stmt = $pdo->prepare("INSERT INTO migrations (migration, batch) VALUES (:migration, :batch)");
186:
187:	foreach ($migrationFiles as $file) {
188:	$migrationName = basename($file);
189:	if (in_array($migrationName, $executed)) {
190:	continue;
191:	}
192:
193:	try {
194:	$sql = file_get_contents($file);
195:	$pdo->exec($sql);
196:
197:	$stmt->execute([
198:	'migration' => $migrationName,
199:	'batch' => 1
200:	]);
201:
202:	echo "Migrated: $migrationName\n";
203:	} catch (PDOException $e) {
204:	die("Migration failed for $migrationName: " . $e->getMessage());
205:	}
206:	}
207:
208:	echo "Migrations completed.\n";
209:	}
210:

Namespaces

Classes

Traits

Functions