PluginProbe
Fluent Booking – The Ultimate Appointments Scheduling, Events Booking, Events Calendar Solution / trunk
Fluent Booking – The Ultimate Appointments Scheduling, Events Booking, Events Calendar Solution vtrunk
2.4.0 2.3.0 2.2.5 2.2.0 2.1.2 2.1.1 trunk 1.10.0 1.10.01 1.10.02 1.5.0 1.5.01 1.5.02 1.5.1 1.5.10 1.5.20 1.5.21 1.5.22 1.5.23 1.5.24 1.5.25 1.6.0 1.7.0 1.7.1 1.7.2 All 33 releases
← All changes | vendor/wpfluent/framework/src/WPFluent/Database/Schema.php +693 -83 1.5.21 → trunk View file →
@@ -1,14 +1,25 @@
1 1 <?php
2 2
3 3 namespace FluentBooking\Framework\Database;
4 4
5 +use FluentBooking\Framework\Database\Concerns\MaintainsDatabase;
6 +
5 7 class Schema
6 8 {
9 + use MaintainsDatabase;
10 +
7 11 /**
12 + * Keep track of custom tables when unit testing
13 + *
14 + * @var array
15 + */
16 + public static $customTempTables = [];
17 +
18 + /**
8 19 * Get the global $wpdb instance
9 20 *
10 - * @return global $wpdb instance
21 + * @return \wpdb The global $wpdb
11 22 */
12 23 public static function db()
13 24 {
14 25 return $GLOBALS['wpdb'];
@@ -22,17 +33,36 @@
22 33 public static function getInfo($key = null)
23 34 {
24 35 $db = static::db();
25 36
37 + if (static::isSqlite()) {
38 + $info = [
39 + 'dbname' => 'sqlite',
40 + 'prefix' => $db->prefix,
41 + 'dbhost' => '',
42 + 'username' => '',
43 + 'password' => '',
44 + 'charset' => 'utf8',
45 + 'collation' => '',
46 + 'tables' => static::getTableList(),
47 + ];
48 +
49 + return $key ? ($info[$key] ?? null) : $info;
50 + }
51 +
26 52 $info = [
27 - 'dbname' => $db->dbname,
28 - 'prefix' => $db->prefix,
29 - 'dbhost' => $db->dbhost,
53 + // @phpstan-ignore-next-line
54 + 'dbname' => $db->dbname,
55 + 'prefix' => $db->prefix,
56 + // @phpstan-ignore-next-line
57 + 'dbhost' => $db->dbhost,
58 + // @phpstan-ignore-next-line
30 59 'username' => $db->dbuser,
60 + // @phpstan-ignore-next-line
31 61 'password' => $db->dbpassword,
32 62 'charset' => $db->charset,
33 63 'collation' => $db->collate,
34 - 'tables' => static::getTableList()
64 + 'tables' => static::getTableList(),
35 65 ];
36 66
37 67 return $key ? $info[$key] : $info;
38 68 }
@@ -49,11 +79,11 @@
49 79 public static function migrate($table, $sql = null)
50 80 {
51 81 if (!$sql && is_array($table)) {
52 82 $result = [];
53 - foreach ($table as $t => $sql) {
83 + foreach ($table as $t => $s) {
54 84 $result = array_merge(
55 - $result, (array) static::createTable($t, $sql)
85 + $result, (array) static::createTable($t, $s)
56 86 );
57 87 }
58 88 return $result;
59 89 } else {
@@ -120,16 +150,18 @@
120 150 // error and then turn off the error from being shown, so
121 151 // error will be not shown if there is no temporary
122 152 // table, then restore the error state.
123 153 $isErrorSuppressed = $wpdb->suppress_errors;
124 -
154 +
125 155 $wpdb->suppress_errors = true;
126 156
127 - $result = static::query("SELECT 1 FROM %{$table}% WHERE 0");
157 + static::query("SELECT 1 FROM %{$table}% WHERE 0");
128 158
159 + $hasError = !empty($wpdb->last_error);
160 +
129 161 $wpdb->suppress_errors = $isErrorSuppressed;
130 162
131 - return $result === 0;
163 + return !$hasError;
132 164 }
133 165
134 166 /**
135 167 * Resolves the table prefix and makes the table name with prefix
@@ -149,8 +181,59 @@
149 181
150 182 return isset($wpdb->{$table}) ? $wpdb->{$table} : ($wpdb->prefix.$table);
151 183 }
152 184
185 +
186 + /**
187 + * Resolves the sql prefix
188 + *
189 + * @param string $sql The file name or the raw sql
190 + * @return string The resolved sql
191 + */
192 + public static function sql($sql = '')
193 + {
194 + $allowedSqlFileFormats = [
195 + 'sql'
196 + ];
197 +
198 + foreach ($allowedSqlFileFormats as $format) {
199 + if (str_ends_with($sql, '.' . $format)) {
200 + $sql = @file_exists($sql) ? file_get_contents($sql) : $sql;
201 + break;
202 + }
203 + }
204 +
205 + return $sql;
206 + }
207 +
208 + /**
209 + * Clean the sql string by removing the comments.
210 + *
211 + * @param string $sql
212 + * @return string
213 + */
214 + public static function cleanUp($sql)
215 + {
216 + $sql = static::sql($sql);
217 +
218 + // Remove inline -- comments
219 + $sql = preg_replace('/--.*$/m', '', $sql);
220 +
221 + // Remove inline # comments
222 + $sql = preg_replace('/#.*$/m', '', $sql);
223 +
224 + // Remove block /* ... */
225 + $sql = preg_replace('/\/\*.*?\*\//s', '', $sql);
226 +
227 + // Collapse multiple spaces & newlines
228 + // $sql = preg_replace('/\s+/', ' ', $sql);
229 +
230 + // Remove dangling commas before closing parenthesis
231 + $sql = preg_replace('/,\s*\)/', ')', $sql);
232 +
233 + return trim($sql);
234 + }
235 +
153 236 /**
154 237 * Creates a new table using dbDelta function or alters the table if exists.
155 238 *
156 239 * @param string $table The table name without the prefix
@@ -162,18 +245,21 @@
162 245 public static function createTable($table, $sql)
163 246 {
164 247 $table = static::table($table);
165 248
166 - $sql = @file_exists($sql) ? file_get_contents($sql) : $sql;
249 + $sql = static::cleanUp($sql);
167 250
251 + if (static::isSqlite()) {
252 + $sql = preg_replace('/\bjson\b/i', 'longtext', $sql);
253 + }
254 +
168 255 $collate = static::db()->get_charset_collate();
169 256
170 - if ($sql && !str_contains(basename($sql), '.')) {
171 - return static::callDBDelta(
172 - $table,
173 - "CREATE TABLE $table (".PHP_EOL.trim(trim($sql), ',').PHP_EOL.") $collate;"
174 - );
175 - }
257 + return static::callDBDelta(
258 + "CREATE TABLE $table (
259 + ".PHP_EOL.trim(trim($sql), ',').PHP_EOL."
260 + ) $collate;"
261 + );
176 262 }
177 263
178 264 /**
179 265 * Alters an existing table if exists
@@ -192,13 +278,13 @@
192 278 }
193 279
194 280 /**
195 281 * Alters an existing table
196 - *
282 + *
197 283 * @param string $table The table name without the prefix
198 284 * @param string $sql The sql to create table or an absolute path of a
199 285 * .sql file containing the column definations for creating the new table.
200 - *
286 + *
201 287 * @return string message
202 288 */
203 289 public static function alterTable($table, $sql)
204 290 {
@@ -203,31 +289,80 @@
203 289 public static function alterTable($table, $sql)
204 290 {
205 291 $table = static::table($table);
206 292
207 - $sql = @file_exists($sql) ? file_get_contents($sql) : $sql;
293 + $sql = static::cleanUp($sql);
208 294
209 - $sql = array_map(function($i) { return trim($i);}, explode(',', $sql));
210 -
211 - $sql = "ALTER TABLE $table ".PHP_EOL.rtrim(trim(implode(','.PHP_EOL, $sql)), ';').";";
295 + if (static::isSqlite()) {
296 + $sql = preg_replace('/\bjson\b/i', 'longtext', $sql);
297 + }
212 298
299 + $parts = array_filter(array_map('trim', static::splitAlterClauses($sql)));
300 +
301 + if (static::isSqlite()) {
302 + // SQLite only supports one ALTER TABLE operation per statement.
303 + $result = null;
304 + foreach ($parts as $part) {
305 + $result = static::query("ALTER TABLE $table {$part};");
306 + }
307 + return $result;
308 + }
309 +
310 + $sql = "ALTER TABLE $table ".PHP_EOL.rtrim(
311 + trim(implode(','.PHP_EOL, $parts)), ';'
312 + ).";";
313 +
213 314 return static::query($sql);
214 315 }
215 316
216 317 /**
217 - * Alters an existing table using dbDelta function if exists, otherwise creates it.
318 + * Split a comma-separated ALTER TABLE clause list, respecting parentheses
319 + * so that DECIMAL(10,2) and similar types are not split mid-definition.
320 + *
321 + * @param string $sql
322 + * @return string[]
323 + */
324 + protected static function splitAlterClauses($sql)
325 + {
326 + $parts = [];
327 + $depth = 0;
328 + $current = '';
329 +
330 + for ($i = 0, $len = strlen($sql); $i < $len; $i++) {
331 + $char = $sql[$i];
332 + if ($char === '(') {
333 + $depth++;
334 + } elseif ($char === ')') {
335 + $depth--;
336 + } elseif ($char === ',' && $depth === 0) {
337 + $parts[] = $current;
338 + $current = '';
339 + continue;
340 + }
341 + $current .= $char;
342 + }
343 +
344 + if ($current !== '') {
345 + $parts[] = $current;
346 + }
347 +
348 + return $parts;
349 + }
350 +
351 + /**
352 + * Alters an existing table using dbDelta function if exists, otherwise creates
353 + * it. Alters an existing table but takes the table creation column defination.
354 + * This is because, the dbDelta functioin can create or update a table using
355 + * the table creation defination. In this, case, if a table exists and the
356 + * columns are matched then nothing happens but if there's any difference
357 + * in the new sql then the dbDelta alters the table using the new sql
358 + * defination but doesn't delete any columns. So, after the dbDelta
359 + * finishes it's job, any non-existing columns in the new sql
360 + * defination will be deleted from the existing table. if
361 + * table is not there then the table gets created.
218 362 *
219 - * Alters an existing table but takes the table's creation column defination. This is
220 - * because, the dbDelta functioin can create or update a table using the table same
221 - * creation defination. In this, case, if a table exists and columns are matched
222 - * then nothing happens but if there's any difference in the new sql then the
223 - * dbDelta alters the table using the new defination but doesn't delete any
224 - * columns. so, after the dbDelta finishes it's job, any non-existing
225 - * columns in the new defination will be deleted from the existing
226 - * table. if table is not there then the table gets created.
227 - *
228 363 * @param string $table The table name without the prefix
229 - * @param string $sql The sql to create table or an absolute path of a
364 + * @param string $sql The sql to create table or an absolute path of a
230 365 * .sql file containing the column definations for creating the new table.
231 366 *
232 367 * @return string message
233 368 */
@@ -232,47 +367,141 @@
232 367 * @return string message
233 368 */
234 369 public static function updateTable($table, $sql)
235 370 {
236 - $result = static::createTable(
237 - $table, implode(",\n", array_map('trim', explode(',', $sql)))
238 - );
371 + $sql = static::cleanUp($sql);
239 372
240 - if ($existingColumns = static::getColumns($table)) {
373 + if (static::isSqlite()) {
374 + $sql = preg_replace('/\bjson\b/i', 'longtext', $sql);
375 + }
241 376
242 - $columns = array_map(function($l) {
243 - if (preg_match('/(\S+)/', $l, $matches)) return $matches[1];
244 - }, array_map('trim', explode(',', $sql)));
377 + $columnsDefinitions = array_map('trim', static::splitAlterClauses($sql));
378 + $schemaSql = implode(",\n", $columnsDefinitions);
245 379
246 - foreach ($existingColumns as $column) {
247 - if (!in_array($column, $columns)) {
248 - $tbl = static::table($table);
249 - static::query("alter table $tbl drop column $column");
250 - $tblColumn = $tbl.'.'.$column;
251 - $result[$tblColumn] = "Dropped column {$tblColumn}";
252 - }
253 - }
254 - }
380 + // Extract desired column names (skip constraint/index clauses)
381 + $columns = [];
382 + foreach ($columnsDefinitions as $definition) {
383 + if (preg_match('/^(?:PRIMARY\s+KEY|UNIQUE(?:(?:\s+KEY|\s+INDEX))?|KEY|INDEX|CONSTRAINT|FOREIGN\s+KEY|FULLTEXT|SPATIAL)\b/i', ltrim($definition))) {
384 + continue;
385 + }
255 386
256 - return $result;
387 + if (preg_match('/^`?(\w+)`?\s+/i', $definition, $matches)) {
388 + $columns[$matches[1]] = $definition;
389 + }
390 + }
391 +
392 + $tbl = static::table($table);
393 +
394 + // SQLite cannot drop PRIMARY KEY columns or columns referenced by indexes
395 + // via native ALTER TABLE DROP COLUMN.
396 + //
397 + // Workarounds:
398 + // - Indexed columns: drop the index first with DROP INDEX,
399 + // then drop the column.
400 + // - PRIMARY KEY columns: use CHANGE COLUMN to rebuild the table without
401 + // the PK attribute (renaming the column to a temp name), then drop
402 + // the renamed column.
403 + if (static::isSqlite()) {
404 + // Collect PRIMARY KEY columns and per-column index key names.
405 + $indexRows = (array) static::db()->get_results("SHOW INDEX FROM {$tbl}");
406 + $pkColumns = [];
407 + $columnIndexes = []; // column_name => [key_name, ...]
408 + foreach ($indexRows as $row) {
409 + if ($row->Key_name === 'PRIMARY') {
410 + $pkColumns[] = $row->Column_name;
411 + } else {
412 + $columnIndexes[$row->Column_name][] = $row->Key_name;
413 + }
414 + }
415 +
416 + $existingColumns = static::getColumns($table) ?: [];
417 +
418 + // 1. Add any desired columns not yet in the table.
419 + foreach ($columns as $colName => $definition) {
420 + if (!in_array($colName, $existingColumns)) {
421 + static::db()->query("ALTER TABLE {$tbl} ADD COLUMN {$definition}");
422 + }
423 + }
424 +
425 + // 2. Drop every column that is not in the desired schema.
426 + foreach ($existingColumns as $column) {
427 + if (isset($columns[$column])) {
428 + continue; // desired — keep it
429 + }
430 +
431 + // Drop non-primary indexes referencing this column first, otherwise
432 + // native SQLite ALTER TABLE DROP COLUMN fails.
433 + foreach ($columnIndexes[$column] ?? [] as $keyName) {
434 + static::db()->query("ALTER TABLE {$tbl} DROP INDEX {$keyName}");
435 + }
436 +
437 + if (in_array($column, $pkColumns)) {
438 + // Native SQLite ALTER TABLE DROP COLUMN rejects PRIMARY KEY columns.
439 + // Use CHANGE COLUMN to trigger an internal table rebuild that strips
440 + // the PK attribute, then drop the (now ordinary) renamed column.
441 + $tmpCol = $column . '_wpf_drop';
442 + static::db()->query("ALTER TABLE {$tbl} CHANGE COLUMN {$column} {$tmpCol} INT NULL");
443 + static::db()->query("ALTER TABLE {$tbl} DROP COLUMN {$tmpCol}");
444 + } else {
445 + static::db()->query("ALTER TABLE {$tbl} DROP COLUMN {$column}");
446 + }
447 + }
448 +
449 + return [$tbl => "Updated table structure"];
450 + }
451 +
452 + // MySQL path: use dbDelta to create/update, then drop extra columns.
453 + $result = static::createTable($table, $schemaSql);
454 + $existingColumns = static::getColumns($table) ?: [];
455 +
456 + // Add missing columns
457 + foreach ($columns as $colName => $definition) {
458 + if (!in_array($colName, $existingColumns)) {
459 + static::query("ALTER TABLE $tbl ADD COLUMN $definition");
460 + $result[$tbl.'.'.$colName] = "Added column {$tbl}.{$colName}";
461 + }
462 + }
463 +
464 + // Drop extra columns
465 + foreach ($existingColumns as $column) {
466 + if (!isset($columns[$column])) {
467 + static::query("ALTER TABLE $tbl DROP COLUMN $column");
468 + $result[$tbl.'.'.$column] = "Dropped column {$tbl}.{$column}";
469 + }
470 + }
471 +
472 + return $result;
257 473 }
258 474
259 475 /**
260 - * Drops/deletes an existing table if exists
476 + * Drops/deletes an existing table.
261 477 *
262 - * @param string $table The table name without the prefix
263 - * @return bool
478 + * @param string $table The table name without the prefix
479 + * @param bool $disableForeignKeyCheck Optional.
480 + * @return bool
264 481 */
265 - public static function dropTableIfExists($table)
266 - {
267 - if (static::hasTable($table)) {
268 - return static::db()->query('DROP TABLE ' . static::table($table));
269 - }
270 - }
482 + public static function dropTable($table, $disableForeignKeyCheck = true)
483 + {
484 + return static::db()->query('DROP TABLE ' . static::table($table));
485 + }
271 486
487 + /**
488 + * Drops/deletes an existing table if exists
489 + *
490 + * @param string $table The table name without the prefix
491 + * @param bool $disableForeignKeyCheck Optional.
492 + * @return bool
493 + */
494 + public static function dropTableIfExists($table, $disableForeignKeyCheck = true)
495 + {
496 + if (static::hasTable($table)) {
497 + return static::dropTable($table, $disableForeignKeyCheck);
498 + }
499 + }
500 +
272 501 /**
273 502 * Truncate a table.
274 - *
503 + *
275 504 * @param string $table
276 505 * @return bool
277 506 */
278 507 public static function truncate($table)
@@ -278,14 +507,23 @@
278 507 public static function truncate($table)
279 508 {
280 509 $table = static::table($table);
281 510
282 - return static::db()->query("TRUNCATE TABLE $table");
511 + $result = static::db()->query("TRUNCATE TABLE $table");
512 +
513 + if (static::isSqlite()) {
514 + // SQLite translates TRUNCATE TABLE to DELETE FROM, which does NOT reset
515 + // the auto-increment counter. Manually clear the sqlite_sequence row so
516 + // that the next INSERT starts at id=1 (matching MySQL TRUNCATE behaviour).
517 + static::db()->query("DELETE FROM sqlite_sequence WHERE name='{$table}'");
518 + }
519 +
520 + return $result;
283 521 }
284 522
285 523 /**
286 524 * Truncate a table if exists.
287 - *
525 + *
288 526 * @param string $table
289 527 * @return bool
290 528 */
291 529 public static function truncateTableIfExists($table)
@@ -295,39 +533,122 @@
295 533 }
296 534 }
297 535
298 536 /**
537 + * Adds a new index to a column of given table.
538 + *
539 + * @param string $table Table name
540 + * @param string $index Columns name
541 + * @return bool
542 + * @see https://developer.wordpress.org/reference/functions/add_clean_index
543 + */
544 + public static function addIndex($table, $index)
545 + {
546 + return add_clean_index(static::table($table), $index);
547 + }
548 +
549 + /**
550 + * Drops an index from a column of given table.
551 + *
552 + * @param string $table Table name
553 + * @param string $index Columns name
554 + * @return bool
555 + * @see https://developer.wordpress.org/reference/functions/drop_index
556 + */
557 + public static function dropIndex($table, $index)
558 + {
559 + if (static::isSqlite()) {
560 + $tbl = static::table($table);
561 + return static::db()->query("ALTER TABLE {$tbl} DROP INDEX {$index}");
562 + }
563 +
564 + return drop_index(static::table($table), $index);
565 + }
566 +
567 + /**
568 + * Add a FULLTEXT index to the given columns, required by the query
569 + * builder's relevance ranking (selectRelevance/orderByRelevance and the
570 + * Searchable relevanceSearch scope). No-ops on SQLite, which has no
571 + * native FULLTEXT support and degrades to LIKE-based matching.
572 + *
573 + * @param string $table Table name without prefix
574 + * @param string|array $columns
575 + * @param string|null $name Optional index name
576 + * @return mixed
577 + */
578 + public static function fullText($table, $columns, $name = null)
579 + {
580 + if (static::isSqlite()) {
581 + return false;
582 + }
583 +
584 + $columns = is_array($columns)
585 + ? $columns
586 + : array_map('trim', explode(',', $columns));
587 +
588 + $tbl = static::table($table);
589 + $name = $name ?: ('ft_' . implode('_', $columns));
590 + $list = implode(', ', $columns);
591 +
592 + return static::db()->query(
593 + "ALTER TABLE {$tbl} ADD FULLTEXT {$name} ({$list})"
594 + );
595 + }
596 +
597 + /**
598 + * Drop a FULLTEXT index by name.
599 + *
600 + * @param string $table Table name without prefix
601 + * @param string $name Index name
602 + * @return mixed
603 + */
604 + public static function dropFullText($table, $name)
605 + {
606 + if (static::isSqlite()) {
607 + return false;
608 + }
609 +
610 + $tbl = static::table($table);
611 +
612 + return static::db()->query("ALTER TABLE {$tbl} DROP INDEX {$name}");
613 + }
614 +
615 + /**
299 616 * Makes raw query and can resolve the table name from the query
300 617 * and can form a full table name including the table prefix if
301 618 * the table name is wrapped like: %table_name% in the query.
302 619 *
303 - * @param straing $query
620 + * @param string $query
304 621 * @return mixed
305 622 */
306 623 public static function query($query)
307 624 {
308 - if (preg_match('/%.*%/', $query, $m)) {
309 - $query = str_replace(
310 - $m[0], static::table(trim($m[0], '%')), $query
311 - );
312 - }
625 + $query = preg_replace_callback('/(?<![\'"])%([a-zA-Z0-9_-]+)%(?![\'"])/', function ($matches) {
626 + return static::table($matches[1]);
627 + }, $query);
313 628
629 + if (preg_match('/^(SELECT|SHOW|DESCRIBE|EXPLAIN)\s+/i', trim($query))) {
630 + return static::db()->get_results($query, OBJECT);
631 + }
632 +
314 633 return static::db()->query($query);
315 634 }
316 635
317 636 /**
318 637 * Get a list of all columns from the given table name.
319 - *
638 + *
320 639 * @param string $table The table name without the prefix
321 - * @return array
640 + * @return array|null
322 641 */
323 642 public static function getColumns($table)
324 643 {
325 - if (static::hasTable($table)) {
326 - return static::db()->get_col(
327 - 'DESC ' . static::table($table), 0
328 - );
644 + if (!static::hasTable($table)) {
645 + return null;
329 646 }
647 +
648 + return static::db()->get_col(
649 + 'DESCRIBE ' . static::table($table), 0
650 + );
330 651 }
331 652
332 653 /**
333 654 * Gets a list of all columns including column information
@@ -334,14 +655,52 @@
334 655 *
335 656 * @param string $table The table name without the prefix
336 657 * @return array
337 658 */
338 - public static function getColumnsWithTypes($table)
659 + protected static function protectedGetColumnsWithTypes($table)
339 660 {
340 661 if (!static::hasTable($table)) return;
341 -
662 +
663 + $table = static::table($table);
664 +
665 + if (static::isSqlite()) {
666 + // INFORMATION_SCHEMA is not available in SQLite.
667 + // Use DESCRIBE which the WP SQLite plugin translates with
668 + // proper MySQL type mapping from its data type cache.
669 + $columns = (array) static::db()->get_results("DESCRIBE {$table}");
670 + return array_map(function ($col, $pos) {
671 + $extra = $col->Extra ?? '';
672 + // SQLite INTEGER PRIMARY KEY is auto_increment but DESCRIBE
673 + // doesn't report it in Extra — detect from Key + Type.
674 + if (empty($extra) && ($col->Key ?? '') === 'PRI') {
675 + $type = strtoupper($col->Type ?? '');
676 + if (in_array($type, ['INTEGER', 'BIGINT', 'BIGINT(20)', 'BIGINT(20) UNSIGNED', 'INT'])) {
677 + $extra = 'auto_increment';
678 + }
679 + }
680 +
681 + return [
682 + 'column_name' => $col->Field,
683 + 'ordinal_position' => $pos + 1,
684 + 'column_default' => $col->Default,
685 + 'is_nullable' => ($col->Null === 'YES' ? 'YES' : 'NO'),
686 + 'data_type' => strtolower(
687 + preg_replace('/\(.*/', '', $col->Type)
688 + ),
689 + 'character_maximum_length' => preg_match(
690 + '/\((\d+)\)/', $col->Type, $matches
691 + ) ? (int)$matches[1] : null,
692 + 'numeric_precision' => null,
693 + 'numeric_scale' => null,
694 + 'column_key' => $col->Key ?? '',
695 + 'extra' => $extra,
696 + ];
697 + }, $columns, array_keys($columns));
698 + }
699 +
700 + // @phpstan-ignore-next-line
342 701 $db = static::db()->dbname;
343 - $table = static::table($table);
702 +
344 703 $fields = [
345 704 'COLUMN_NAME',
346 705 'ORDINAL_POSITION',
347 706 'COLUMN_DEFAULT',
@@ -352,9 +711,9 @@
352 711 'NUMERIC_SCALE',
353 712 'COLUMN_KEY',
354 713 'EXTRA',
355 714 ];
356 -
715 +
357 716 $sql = "SELECT " . implode(',', $fields) . " FROM INFORMATION_SCHEMA.COLUMNS";
358 717 $sql .= " WHERE TABLE_NAME = '".$table."' AND TABLE_SCHEMA = '".$db."'";
359 718
360 719 return array_map(function($i) {
@@ -362,12 +721,139 @@
362 721 foreach ((array) $i as $key => $value) {
363 722 $item[strtolower($key)] = $value;
364 723 }
365 724 return $item;
366 - }, static::db()->get_results($sql));
725 + }, (array) static::db()->get_results($sql));
367 726 }
368 727
728 + public static function getColumnsWithTypes($table)
729 + {
730 + $columns = static::protectedGetColumnsWithTypes($table);
731 +
732 + if (!empty($columns)) {
733 + return $columns;
734 + }
735 +
736 + if (static::isSqlite()) {
737 + return [];
738 + }
739 +
740 + $columns = static::db()->get_results(
741 + 'SHOW COLUMNS FROM `'.static::table($table).'`'
742 + );
743 +
744 + return array_map(function ($col) {
745 + return [
746 + 'column_name' => $col->Field,
747 + 'ordinal_position' => null,
748 + 'column_default' => $col->Default,
749 + 'is_nullable' => ($col->Null === 'YES' ? 'YES' : 'NO'),
750 + 'data_type' => strtolower(
751 + preg_replace('/\(.*/', '', $col->Type)
752 + ),
753 + 'character_maximum_length' => preg_match(
754 + '/\((\d+)\)/', $col->Type, $matches
755 + ) ? (int)$matches[1] : null,
756 + 'numeric_precision' => null,
757 + 'numeric_scale' => null,
758 + 'column_key' => $col->Key,
759 + 'extra' => $col->Extra,
760 + ];
761 + }, (array) $columns);
762 + }
763 +
369 764 /**
765 + * Gets a list of all columns including column information
766 + *
767 + * @param string $table The table name without the prefix
768 + * @return array
769 + */
770 + public static function describeTable($table)
771 + {
772 + return static::getColumnsWithTypes($table);
773 + }
774 +
775 + /**
776 + * Gets a list of all foreign keys from the given table name.
777 + *
778 + * @param string $table
779 + * @return array
780 + */
781 + public static function getTableForeignKeys($table)
782 + {
783 + $table = static::table($table);
784 +
785 + if (static::isSqlite()) {
786 + // Parse foreign keys from the CREATE TABLE statement in sqlite_master
787 + // since PRAGMA queries cannot go through $wpdb.
788 + $row = static::db()->get_row(
789 + "SELECT sql FROM sqlite_master WHERE type='table' AND name='{$table}'"
790 + );
791 + if (!$row || empty($row->sql)) {
792 + return [];
793 + }
794 + $fks = [];
795 + if (preg_match_all(
796 + '/FOREIGN\s+KEY\s*\(\s*[`"]?(\w+)[`"]?\s*\)\s*REFERENCES\s+[`"]?(\w+)[`"]?\s*\(\s*[`"]?(\w+)[`"]?\s*\)/i',
797 + $row->sql, $matches, PREG_SET_ORDER
798 + )) {
799 + foreach ($matches as $m) {
800 + $fks[] = [
801 + 'column_name' => $m[1],
802 + 'referenced_table' => $m[2],
803 + 'referenced_column' => $m[3],
804 + ];
805 + }
806 + }
807 + return $fks;
808 + }
809 +
810 + // @phpstan-ignore-next-line
811 + $db = static::db()->dbname;
812 +
813 + $sql = "SELECT COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
814 + FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
815 + WHERE TABLE_NAME = '{$table}'
816 + AND TABLE_SCHEMA = '{$db}'
817 + AND REFERENCED_TABLE_NAME IS NOT NULL";
818 +
819 + return array_map(function ($i) {
820 + return [
821 + 'column_name' => $i->COLUMN_NAME,
822 + 'referenced_table' => $i->REFERENCED_TABLE_NAME,
823 + 'referenced_column' => $i->REFERENCED_COLUMN_NAME,
824 + ];
825 + }, (array) static::db()->get_results($sql));
826 + }
827 +
828 + /**
829 + * Retrieves the list of all available built-in tables from
830 + * the database using WordPress' $wpdb->tables native method.
831 + *
832 + * @param string $scope
833 + * @param boolean $prefix
834 + * @param integer $blogId
835 + * @return string[] WP Table names. When a prefix is requested,
836 + * the key is the unprefixed table name.
837 + * @see https://developer.wordpress.org/reference/classes/wpdb/tables/
838 + */
839 + public static function tables($scope = 'all', $prefix = true, $blogId = 0)
840 + {
841 + return static::db()->tables($scope, $prefix, $blogId);
842 + }
843 +
844 + /**
845 + * Retrieves the list of all available tables from the database.
846 + *
847 + * @param string $dbname optional
848 + * @return array
849 + */
850 + public static function getTables($dbname = null)
851 + {
852 + return static::getTableList($dbname);
853 + }
854 +
855 + /**
370 856 * Retrieves the list of all available tables in the database.
371 857 *
372 858 * @param string $dbname optional
373 859 * @return array
@@ -373,27 +859,151 @@
373 859 * @return array
374 860 */
375 861 public static function getTableList($dbname = null)
376 862 {
863 + if (static::isSqlite()) {
864 + return array_map(function ($i) {
865 + return $i->name;
866 + }, (array) static::db()->get_results(
867 + "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'"
868 + ));
869 + }
870 +
871 + // @phpstan-ignore-next-line
377 872 $dbname = $dbname ?: static::db()->dbname;
378 873 $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
379 874 $sql .= " WHERE TABLE_SCHEMA = '".$dbname."'";
875 +
380 876 return array_map(function($i) {
381 877 return $i->TABLE_NAME;
382 - }, static::db()->get_results($sql));
878 + }, (array) static::db()->get_results($sql));
383 879 }
384 880
385 881 /**
882 + * Retrieves the list of all available views in the database.
883 + *
884 + * @param string $dbname optional
885 + * @return array
886 + */
887 + public static function getViews($dbname = null)
888 + {
889 + if (static::isSqlite()) {
890 + return array_map(function ($i) {
891 + return $i->name;
892 + }, (array) static::db()->get_results(
893 + "SELECT name FROM sqlite_master WHERE type='view'"
894 + ));
895 + }
896 +
897 + // @phpstan-ignore-next-line
898 + $dbname = $dbname ?: static::db()->dbname;
899 + $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS";
900 + $sql .= " WHERE TABLE_SCHEMA = '".$dbname."'";
901 +
902 + return array_map(function ($i) {
903 + return $i->TABLE_NAME;
904 + }, (array) static::db()->get_results($sql));
905 + }
906 +
907 + /**
908 + * Retrieves the SQL definition of a specific view.
909 + *
910 + * @param string $name The view name
911 + * @param string $dbname optional
912 + * @return string|null
913 + */
914 + public static function getView($name, $dbname = null)
915 + {
916 + if (static::isSqlite()) {
917 + $result = static::db()->get_row(
918 + "SELECT sql FROM sqlite_master WHERE type='view' AND name='".$name."'"
919 + );
920 +
921 + return $result ? $result->sql : null;
922 + }
923 +
924 + // @phpstan-ignore-next-line
925 + $dbname = $dbname ?: static::db()->dbname;
926 + $result = static::db()->get_row(
927 + "SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS"
928 + . " WHERE TABLE_SCHEMA = '".$dbname."'"
929 + . " AND TABLE_NAME = '".$name."'"
930 + );
931 +
932 + return $result ? $result->VIEW_DEFINITION : null;
933 + }
934 +
935 + /**
936 + * Determine if the connected database is a sqlite database.
937 + *
938 + * @return bool
939 + */
940 + public static function isSqlite()
941 + {
942 + return defined('DB_ENGINE') && DB_ENGINE === 'sqlite';
943 + }
944 +
945 + /**
946 + * Determine if the connected database is a mariadb database.
947 + *
948 + * @return bool
949 + */
950 + public static function isMaria()
951 + {
952 + if (static::isSqlite()) {
953 + return false;
954 + }
955 +
956 + return str_contains(
957 + static::db()->get_var('SELECT VERSION()'), 'MariaDB'
958 + );
959 + }
960 +
961 + /**
962 + * Retrieve the current database engine name.
963 + *
964 + * @param string $table
965 + * @return string
966 + */
967 + public static function getEngine($table)
968 + {
969 + if (static::isSqlite()) {
970 + return 'sqlite';
971 + }
972 +
973 + return static::db()->get_var(
974 + 'SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = "' . static::table($table) . '"'
975 + );
976 + }
977 +
978 + /**
386 979 * The wrapper for calling dbDelta function
387 980 *
388 981 * @param string $sql
389 982 * @return mixed
390 983 */
391 - protected static function callDBDelta($table, $sql)
984 + public static function callDBDelta($sql)
392 985 {
393 986 if (!function_exists('dbDelta')) {
394 987 require (ABSPATH . 'wp-admin/includes/upgrade.php');
395 988 }
396 989
397 - return dbDelta($sql);
990 + $result = dbDelta($sql);
991 +
992 + if (php_sapi_name() === 'cli') {
993 + $key = array_key_first($result);
994 + if ($key && !str_contains($key, '.')) {
995 + static::$customTempTables[] = $key;
996 + }
997 + }
998 +
999 + return $result;
398 1000 }
1001 +
1002 + /**
1003 + * Helper to get the driver-specific JSON type.
1004 + */
1005 + public static function jsonType()
1006 + {
1007 + return static::isSqlite() ? 'longtext' : 'json';
1008 + }
399 1009 }