migrations.php

main 307 lines · 7.4 KB Raw
Alex Alex Commit Initial commit 01/10/2026 09:20
1<?php
2/**
3 * ZnoteX database migrations.
4 *
5 * Runs SQL files from SQL/migrations and records successful executions in
6 * znote_migrations, so updates no longer require manual phpMyAdmin imports.
7 */
8
9function znote_migrations_dir(): string {
10 return dirname(__DIR__, 2) . '/SQL/migrations';
11}
12
13function znote_migrations_table_ensure(): bool {
14 return db()->rawExecute("
15 CREATE TABLE IF NOT EXISTS `znote_migrations` (
16 `id` int NOT NULL AUTO_INCREMENT,
17 `migration` varchar(191) NOT NULL,
18 `checksum` char(64) NOT NULL,
19 `executed_at` int NOT NULL,
20 `execution_time_ms` int NOT NULL DEFAULT '0',
21 PRIMARY KEY (`id`),
22 UNIQUE KEY `migration` (`migration`)
23 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
24 ");
25}
26
27function znote_migrations_applied(): array {
28 if (!znote_migrations_table_ensure()) {
29 return array();
30 }
31
32 $rows = db()->fetchAll("SELECT `migration`, `checksum`, `executed_at`, `execution_time_ms` FROM `znote_migrations` ORDER BY `migration` ASC;");
33 $out = array();
34
35 if (is_array($rows)) {
36 foreach ($rows as $row) {
37 $out[(string)$row['migration']] = $row;
38 }
39 }
40
41 return $out;
42}
43
44function znote_migrations_files(): array {
45 $dir = znote_migrations_dir();
46 $files = is_dir($dir) ? glob($dir . '/*.sql') : array();
47 $files = is_array($files) ? $files : array();
48 sort($files, SORT_NATURAL | SORT_FLAG_CASE);
49
50 $out = array();
51 foreach ($files as $file) {
52 $name = basename($file);
53 $out[$name] = array(
54 'name' => $name,
55 'path' => $file,
56 'checksum' => hash_file('sha256', $file) ?: '',
57 'size' => filesize($file) ?: 0,
58 );
59 }
60
61 return $out;
62}
63
64function znote_migrations_status(): array {
65 $applied = znote_migrations_applied();
66 $files = znote_migrations_files();
67 $status = array();
68
69 foreach ($files as $name => $file) {
70 $row = $applied[$name] ?? null;
71 $state = 'pending';
72 if (is_array($row)) {
73 $state = hash_equals((string)$row['checksum'], (string)$file['checksum']) ? 'applied' : 'changed';
74 }
75
76 $status[$name] = $file + array(
77 'state' => $state,
78 'applied' => $row,
79 );
80 }
81
82 foreach ($applied as $name => $row) {
83 if (!isset($status[$name])) {
84 $status[$name] = array(
85 'name' => $name,
86 'path' => '',
87 'checksum' => (string)$row['checksum'],
88 'size' => 0,
89 'state' => 'missing',
90 'applied' => $row,
91 );
92 }
93 }
94
95 ksort($status, SORT_NATURAL | SORT_FLAG_CASE);
96 return $status;
97}
98
99function znote_migrations_pending(): array {
100 return array_filter(znote_migrations_status(), static function (array $migration): bool {
101 return $migration['state'] === 'pending';
102 });
103}
104
105final class ZnoteMigrationSqlSplitter
106{
107 private string $sql;
108 private int $len;
109 private int $i = 0;
110 private string $current = '';
111 private ?string $quote = null;
112 private bool $lineComment = false;
113 private bool $blockComment = false;
114 private array $statements = array();
115
116 public function __construct(string $sql)
117 {
118 $this->sql = $sql;
119 $this->len = strlen($sql);
120 }
121
122 public function split(): array
123 {
124 for ($this->i = 0; $this->i < $this->len; $this->i++) {
125 $this->step();
126 }
127 $this->flush();
128
129 return $this->statements;
130 }
131
132 private function step(): void
133 {
134 if ($this->lineComment) {
135 $this->stepLineComment();
136 return;
137 }
138 if ($this->blockComment) {
139 $this->stepBlockComment();
140 return;
141 }
142 if ($this->quote !== null) {
143 $this->stepQuote();
144 return;
145 }
146 $this->stepDefault();
147 }
148
149 private function char(): string
150 {
151 return $this->sql[$this->i];
152 }
153
154 private function next(): string
155 {
156 return ($this->i + 1 < $this->len) ? $this->sql[$this->i + 1] : '';
157 }
158
159 private function stepLineComment(): void
160 {
161 $char = $this->char();
162 $this->current .= $char;
163 if ($char === "\n") {
164 $this->lineComment = false;
165 }
166 }
167
168 private function stepBlockComment(): void
169 {
170 $char = $this->char();
171 $next = $this->next();
172 $this->current .= $char;
173 if ($char === '*' && $next === '/') {
174 $this->current .= $next;
175 $this->i++;
176 $this->blockComment = false;
177 }
178 }
179
180 private function stepQuote(): void
181 {
182 $char = $this->char();
183 $next = $this->next();
184 $this->current .= $char;
185 if ($char === '\\' && $next !== '') {
186 $this->current .= $next;
187 $this->i++;
188 return;
189 }
190 if ($char === $this->quote) {
191 $this->quote = null;
192 }
193 }
194
195 private function stepDefault(): void
196 {
197 $char = $this->char();
198 $next = $this->next();
199
200 if ($this->startsLineComment()) {
201 $this->lineComment = true;
202 $this->current .= $char;
203 return;
204 }
205 if ($char === '/' && $next === '*') {
206 $this->blockComment = true;
207 $this->current .= $char . $next;
208 $this->i++;
209 return;
210 }
211 if ($char === '\'' || $char === '"' || $char === '`') {
212 $this->quote = $char;
213 $this->current .= $char;
214 return;
215 }
216 if ($char === ';') {
217 $this->flush();
218 return;
219 }
220
221 $this->current .= $char;
222 }
223
224 private function startsLineComment(): bool
225 {
226 $char = $this->char();
227 $next = $this->next();
228 return ($char === '-' && $next === '-' && ($this->i + 2 >= $this->len || preg_match('/\s/', $this->sql[$this->i + 2])))
229 || $char === '#';
230 }
231
232 private function flush(): void
233 {
234 $trimmed = trim($this->current);
235 if ($trimmed !== '') {
236 $this->statements[] = $trimmed;
237 }
238 $this->current = '';
239 }
240}
241
242function znote_migration_split_sql(string $sql): array {
243 return (new ZnoteMigrationSqlSplitter($sql))->split();
244}
245
246function znote_migration_run(string $migration): array {
247 $files = znote_migrations_files();
248 if (!isset($files[$migration])) {
249 return array('ok' => false, 'message' => 'Migration file not found.', 'statements' => 0, 'time_ms' => 0);
250 }
251 if (!znote_migrations_table_ensure()) {
252 return array('ok' => false, 'message' => 'Could not create znote_migrations table.', 'statements' => 0, 'time_ms' => 0);
253 }
254
255 $applied = znote_migrations_applied();
256 if (isset($applied[$migration]) && hash_equals((string)$applied[$migration]['checksum'], (string)$files[$migration]['checksum'])) {
257 return array('ok' => true, 'message' => 'Already applied.', 'statements' => 0, 'time_ms' => 0);
258 }
259 if (isset($applied[$migration])) {
260 return array('ok' => false, 'message' => 'Migration was already applied but the file checksum changed.', 'statements' => 0, 'time_ms' => 0);
261 }
262
263 $sql = (string)file_get_contents($files[$migration]['path']);
264 $statements = znote_migration_split_sql($sql);
265 $started = microtime(true);
266 $done = 0;
267
268 foreach ($statements as $index => $statement) {
269 if (!db()->rawExecute($statement)) {
270 return array(
271 'ok' => false,
272 'message' => 'Statement ' . ($index + 1) . ' failed. Check Admin Panel > Error Log for the database reference.',
273 'statements' => $done,
274 'time_ms' => (int)round((microtime(true) - $started) * 1000),
275 );
276 }
277 $done++;
278 }
279
280 $timeMs = (int)round((microtime(true) - $started) * 1000);
281 $recorded = db()->execute("
282 INSERT INTO `znote_migrations` (`migration`, `checksum`, `executed_at`, `execution_time_ms`)
283 VALUES (?, ?, ?, ?);
284 ", [$migration, $files[$migration]['checksum'], time(), $timeMs]);
285
286 if (!$recorded) {
287 return array('ok' => false, 'message' => 'Migration ran but could not be recorded.', 'statements' => $done, 'time_ms' => $timeMs);
288 }
289
290 return array('ok' => true, 'message' => 'Applied successfully.', 'statements' => $done, 'time_ms' => $timeMs);
291}
292
293function znote_migrations_run_pending(): array {
294 $results = array();
295
296 foreach (array_keys(znote_migrations_pending()) as $migration) {
297 $result = znote_migration_run($migration);
298 $results[$migration] = $result;
299 if (empty($result['ok'])) {
300 break;
301 }
302 }
303
304 return $results;
305}
306?>
307
Top