| 1 |
<?php |
| 2 |
/** |
| 3 |
* Desktop Mode — Files-on-the-Desktop schema. |
| 4 |
* |
| 5 |
* Three custom tables back the system: |
| 6 |
* |
| 7 |
* - `_desktop_mode_file_placements` — every (user, parent_folder, |
| 8 |
* type, ref, x, y, sort) tuple. Indexed on `(user_id, parent_id)` |
| 9 |
* and `(file_type, file_ref)` for two queries we run constantly: |
| 10 |
* "show me what's on user X's folder Y" and "where else does |
| 11 |
* this entity appear" (used when an entity is deleted to clean |
| 12 |
* up dangling placements). |
| 13 |
* |
| 14 |
* - `_desktop_mode_folders` — folder rows. Owned by one user, with |
| 15 |
* a share mode (`private` | `users` | `roles` | `all`) and a |
| 16 |
* JSON `share_meta` column carrying user/role lists. Folders |
| 17 |
* live independently of where they're placed (a folder placed |
| 18 |
* on user A's desktop root can also appear inside user B's |
| 19 |
* "Projects" folder via a placement). |
| 20 |
* |
| 21 |
* - `_desktop_mode_file_tombstones` — id ledger of removals so the |
| 22 |
* Heartbeat delta sync (Phase 6) can tell connected clients |
| 23 |
* "this placement / folder is gone." Pruned daily. |
| 24 |
* |
| 25 |
* dbDelta is the only safe path for schema migrations against the |
| 26 |
* Core tables environment — Phase 6 re-uses this file by bumping |
| 27 |
* `DESKTOP_MODE_FILES_SCHEMA_VERSION` and adding columns. |
| 28 |
* |
| 29 |
* @package WPDesktopMode |
| 30 |
* @since 0.9.0 |
| 31 |
*/ |
| 32 |
|
| 33 |
defined( 'ABSPATH' ) || exit; |
| 34 |
|
| 35 |
define( 'DESKTOP_MODE_FILES_SCHEMA_VERSION', '10' ); |
| 36 |
define( 'DESKTOP_MODE_FILES_SCHEMA_OPTION', 'desktop_mode_files_schema_version' ); |
| 37 |
|
| 38 |
/** |
| 39 |
* Returns the per-table names with the active prefix applied. |
| 40 |
* |
| 41 |
* @since 0.9.0 |
| 42 |
* |
| 43 |
* @return array{ placements: string, folders: string, tombstones: string } |
| 44 |
*/ |
| 45 |
function desktop_mode_files_table_names() { |
| 46 |
global $wpdb; |
| 47 |
return array( |
| 48 |
'placements' => $wpdb->prefix . 'desktop_mode_file_placements', |
| 49 |
'folders' => $wpdb->prefix . 'desktop_mode_folders', |
| 50 |
'tombstones' => $wpdb->prefix . 'desktop_mode_file_tombstones', |
| 51 |
'shares' => $wpdb->prefix . 'desktop_mode_folder_shares', |
| 52 |
'decisions' => $wpdb->prefix . 'desktop_mode_share_user_decisions', |
| 53 |
); |
| 54 |
} |
| 55 |
|
| 56 |
/** |
| 57 |
* Idempotent `dbDelta` call. Hooked on plugin activation and on |
| 58 |
* `admin_init` (gated by a version-option mismatch) so a manual |
| 59 |
* file copy install still ends up with the tables. |
| 60 |
* |
| 61 |
* @since 0.9.0 |
| 62 |
*/ |
| 63 |
function desktop_mode_files_install_schema() { |
| 64 |
global $wpdb; |
| 65 |
|
| 66 |
require_once ABSPATH . 'wp-admin/includes/upgrade.php'; |
| 67 |
|
| 68 |
$tables = desktop_mode_files_table_names(); |
| 69 |
$charset_collate = $wpdb->get_charset_collate(); |
| 70 |
|
| 71 |
// Schema v2 (since 0.8.0): adds trash columns to both placements |
| 72 |
// and folders so deleted shortcuts and folders land in the |
| 73 |
// recycle bin instead of vanishing. `trashed_at_ms` is the |
| 74 |
// epoch-ms timestamp of the trash event (NULL = active). |
| 75 |
// `trashed_by` records the user that fired it (for permission |
| 76 |
// checks on restore). `trashed_via_folder` on placements is the |
| 77 |
// id of the folder whose trash cascaded the placement, so a |
| 78 |
// folder restore knows exactly which children to bring back — |
| 79 |
// precise round-trip with no time-window heuristics. |
| 80 |
$placements_sql = "CREATE TABLE {$tables['placements']} ( |
| 81 |
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 82 |
user_id BIGINT UNSIGNED NOT NULL, |
| 83 |
parent_id BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 84 |
file_type VARCHAR(64) NOT NULL, |
| 85 |
file_ref VARCHAR(255) NOT NULL DEFAULT '', |
| 86 |
x INT NOT NULL DEFAULT 0, |
| 87 |
y INT NOT NULL DEFAULT 0, |
| 88 |
sort_order INT NOT NULL DEFAULT 0, |
| 89 |
updated_at_ms BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 90 |
meta LONGTEXT NULL, |
| 91 |
trashed_at_ms BIGINT UNSIGNED NULL, |
| 92 |
trashed_by BIGINT UNSIGNED NULL, |
| 93 |
trashed_via_folder BIGINT UNSIGNED NULL, |
| 94 |
trashed_meta LONGTEXT NULL, |
| 95 |
PRIMARY KEY (id), |
| 96 |
KEY user_parent (user_id, parent_id), |
| 97 |
KEY type_ref (file_type, file_ref), |
| 98 |
KEY updated_at_ms (updated_at_ms), |
| 99 |
KEY trashed_at_ms (trashed_at_ms) |
| 100 |
) $charset_collate;"; |
| 101 |
|
| 102 |
$folders_sql = "CREATE TABLE {$tables['folders']} ( |
| 103 |
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 104 |
owner_id BIGINT UNSIGNED NOT NULL, |
| 105 |
name VARCHAR(255) NOT NULL DEFAULT '', |
| 106 |
share_mode VARCHAR(16) NOT NULL DEFAULT 'private', |
| 107 |
share_meta LONGTEXT NULL, |
| 108 |
updated_at_ms BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 109 |
trashed_at_ms BIGINT UNSIGNED NULL, |
| 110 |
trashed_by BIGINT UNSIGNED NULL, |
| 111 |
trashed_meta LONGTEXT NULL, |
| 112 |
PRIMARY KEY (id), |
| 113 |
KEY owner_id (owner_id), |
| 114 |
KEY share_mode (share_mode), |
| 115 |
KEY updated_at_ms (updated_at_ms), |
| 116 |
KEY trashed_at_ms (trashed_at_ms) |
| 117 |
) $charset_collate;"; |
| 118 |
|
| 119 |
$tombstones_sql = "CREATE TABLE {$tables['tombstones']} ( |
| 120 |
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 121 |
kind VARCHAR(16) NOT NULL, |
| 122 |
ref_id BIGINT UNSIGNED NOT NULL, |
| 123 |
removed_at_ms BIGINT UNSIGNED NOT NULL, |
| 124 |
PRIMARY KEY (id), |
| 125 |
KEY kind_removed (kind, removed_at_ms) |
| 126 |
) $charset_collate;"; |
| 127 |
|
| 128 |
// Schema v9 — per-principal grants (`folder_shares` table) + |
| 129 |
// per-user opt-in decisions (`share_user_decisions` table). |
| 130 |
// `share_meta` on the folders row stays as a diagnostic-only |
| 131 |
// column; visibility is computed entirely from the shares |
| 132 |
// table. |
| 133 |
// |
| 134 |
// Shares + decisions are intentionally NOT routed through |
| 135 |
// dbDelta. Their `ensure_*_table()` helpers below are the sole |
| 136 |
// creators. dbDelta uses `DESCRIBE` to detect existing tables |
| 137 |
// and falls back to a bare `CREATE TABLE` (no IF NOT EXISTS) |
| 138 |
// when DESCRIBE returns empty — under certain MySQL / MariaDB |
| 139 |
// configurations (case-folding mismatches, transient connection |
| 140 |
// states, the `lower_case_table_names` quirk on case-sensitive |
| 141 |
// filesystems) DESCRIBE can fail on a table that physically |
| 142 |
// exists, and dbDelta then issues a CREATE that blows up with |
| 143 |
// "Table … already exists" (MySQL error 1050). The `ensure_*` |
| 144 |
// helpers use INFORMATION_SCHEMA + explicit `CREATE TABLE IF NOT |
| 145 |
// EXISTS`, which is bullet-proof; v9 → vN column additions are |
| 146 |
// handled by `ALTER TABLE … ADD COLUMN` inside the same helper. |
| 147 |
|
| 148 |
dbDelta( $placements_sql ); |
| 149 |
dbDelta( $folders_sql ); |
| 150 |
dbDelta( $tombstones_sql ); |
| 151 |
|
| 152 |
// dbDelta has well-documented quirks with `NULL`-only columns |
| 153 |
// (no DEFAULT) — under some MySQL/MariaDB combos it silently |
| 154 |
// skips the ADD COLUMN. Verify the v2 trash columns are |
| 155 |
// physically present and ALTER them in directly when not. |
| 156 |
desktop_mode_files_ensure_trash_columns(); |
| 157 |
|
| 158 |
// v4: clean up duplicate placements created by sessions that |
| 159 |
// hit the auto-orphan-placer while the v2 trash columns were |
| 160 |
// missing — every `WHERE trashed_at_ms IS NULL` precheck |
| 161 |
// returned empty, so each pageload re-inserted every |
| 162 |
// registered shortcut. Collapse runs of identical |
| 163 |
// `(user_id, parent_id, file_type, file_ref)` rows down to the |
| 164 |
// lowest id. |
| 165 |
desktop_mode_files_dedupe_placements(); |
| 166 |
|
| 167 |
// v5: enforce uniqueness at the DB level so a future bug |
| 168 |
// (or a racing pair of REST requests) can never re-create |
| 169 |
// the duplicate shortcuts again. Must run AFTER dedupe — |
| 170 |
// adding a unique key against duplicate rows would fail. |
| 171 |
desktop_mode_files_ensure_unique_placement_index(); |
| 172 |
|
| 173 |
// v9: belt-and-suspenders existence check for the shares + |
| 174 |
// decisions tables. The folder-sharing feature is the |
| 175 |
// canonical source of truth for "who can see this folder" — |
| 176 |
// the `share_meta` JSON column on the folders table remains |
| 177 |
// for diagnostic purposes only and is not consulted by the |
| 178 |
// visibility resolver. |
| 179 |
desktop_mode_files_ensure_shares_table(); |
| 180 |
desktop_mode_files_ensure_decisions_table(); |
| 181 |
|
| 182 |
// v10: `updated_by` column on placements so the If-Match 409 |
| 183 |
// conflict toast names the SESSION that actually won the race, |
| 184 |
// not just whoever currently owns the row. Critical for the |
| 185 |
// shared-write scenario where User B (writer recipient) moves a |
| 186 |
// placement and User C gets the conflict — without this column, |
| 187 |
// the toast would blame User A (owner of the row). |
| 188 |
desktop_mode_files_ensure_updated_by_column(); |
| 189 |
|
| 190 |
update_option( DESKTOP_MODE_FILES_SCHEMA_OPTION, DESKTOP_MODE_FILES_SCHEMA_VERSION ); |
| 191 |
|
| 192 |
/** |
| 193 |
* Fires after the files schema is installed / migrated. |
| 194 |
* |
| 195 |
* @since 0.9.0 |
| 196 |
* |
| 197 |
* @param string $version The version that was installed. |
| 198 |
*/ |
| 199 |
do_action( 'desktop_mode_files_schema_installed', DESKTOP_MODE_FILES_SCHEMA_VERSION ); |
| 200 |
} |
| 201 |
|
| 202 |
/** |
| 203 |
* Belt-and-suspenders verifier for the v2 trash columns. Reads |
| 204 |
* `INFORMATION_SCHEMA.COLUMNS` for the placements + folders tables |
| 205 |
* and `ALTER`s in any column dbDelta missed. Idempotent: each |
| 206 |
* `ALTER` only fires when the column is not already there. |
| 207 |
* |
| 208 |
* @since 0.8.0 |
| 209 |
* @internal |
| 210 |
*/ |
| 211 |
function desktop_mode_files_ensure_trash_columns() { |
| 212 |
global $wpdb; |
| 213 |
$tables = desktop_mode_files_table_names(); |
| 214 |
|
| 215 |
// Two-worker race protection: between the INFORMATION_SCHEMA |
| 216 |
// check and the ALTER, a concurrent worker (cron + admin-init, |
| 217 |
// REST + heartbeat) can run the same check, see the column |
| 218 |
// missing, and both fire ALTER. The second hits MySQL error |
| 219 |
// 1060 ("Duplicate column"). Suppressing wpdb errors around |
| 220 |
// the ALTER swallows that benign log line. The column ends up |
| 221 |
// present either way — we re-verify with a second |
| 222 |
// INFORMATION_SCHEMA query and only surface an error when the |
| 223 |
// column is genuinely missing after the attempt. |
| 224 |
$ensure = static function ( $table, $column, $definition ) use ( $wpdb ) { |
| 225 |
$col_exists = static function () use ( $wpdb, $table, $column ) { |
| 226 |
return (int) $wpdb->get_var( |
| 227 |
$wpdb->prepare( |
| 228 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS |
| 229 |
WHERE TABLE_SCHEMA = DATABASE() |
| 230 |
AND TABLE_NAME = %s |
| 231 |
AND COLUMN_NAME = %s", |
| 232 |
$table, |
| 233 |
$column |
| 234 |
) |
| 235 |
); |
| 236 |
}; |
| 237 |
if ( $col_exists() > 0 ) { |
| 238 |
return; |
| 239 |
} |
| 240 |
$prev_suppress = $wpdb->suppress_errors( true ); |
| 241 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 242 |
$wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}" ); |
| 243 |
$wpdb->suppress_errors( $prev_suppress ); |
| 244 |
// Belt-and-suspenders: if the column STILL isn't there |
| 245 |
// after the ALTER (real schema error, not a race), retry |
| 246 |
// once unsuppressed so WP_DEBUG users see the cause. |
| 247 |
if ( $col_exists() === 0 ) { |
| 248 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 249 |
$wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}" ); |
| 250 |
} |
| 251 |
}; |
| 252 |
|
| 253 |
$ensure( $tables['placements'], 'trashed_at_ms', 'BIGINT UNSIGNED NULL' ); |
| 254 |
$ensure( $tables['placements'], 'trashed_by', 'BIGINT UNSIGNED NULL' ); |
| 255 |
$ensure( $tables['placements'], 'trashed_via_folder', 'BIGINT UNSIGNED NULL' ); |
| 256 |
// v6: ancestry snapshot — JSON capturing every folder in the |
| 257 |
// parent chain at trash time so a restore can resurrect the |
| 258 |
// chain even when a folder was hard-deleted in the meantime. |
| 259 |
$ensure( $tables['placements'], 'trashed_meta', 'LONGTEXT NULL' ); |
| 260 |
$ensure( $tables['folders'], 'trashed_at_ms', 'BIGINT UNSIGNED NULL' ); |
| 261 |
$ensure( $tables['folders'], 'trashed_by', 'BIGINT UNSIGNED NULL' ); |
| 262 |
$ensure( $tables['folders'], 'trashed_meta', 'LONGTEXT NULL' ); |
| 263 |
} |
| 264 |
|
| 265 |
/** |
| 266 |
* Collapse duplicate `(user_id, parent_id, file_type, file_ref)` |
| 267 |
* placement rows down to the lowest id, deleting the rest. Only |
| 268 |
* meaningful for `file_type IN ('shortcut','folder')` — those are |
| 269 |
* the types where two rows for the same ref are redundant. Other |
| 270 |
* types (post / page / attachment / …) might legitimately appear |
| 271 |
* twice on the same desktop, so the dedupe leaves them alone. |
| 272 |
* |
| 273 |
* @since 0.8.0 |
| 274 |
* @internal |
| 275 |
*/ |
| 276 |
function desktop_mode_files_dedupe_placements() { |
| 277 |
global $wpdb; |
| 278 |
$tables = desktop_mode_files_table_names(); |
| 279 |
$tbl = $tables['placements']; |
| 280 |
|
| 281 |
// Once the unique index exists, MySQL prevents duplicate |
| 282 |
// inserts at the DB level — dedupe is a no-op and the |
| 283 |
// table-scanning DELETE is pure waste on every install_schema |
| 284 |
// call. Skip in that case so the cost is paid exactly once, |
| 285 |
// during the v4 → v5 migration. |
| 286 |
$has_unique = (int) $wpdb->get_var( |
| 287 |
$wpdb->prepare( |
| 288 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS |
| 289 |
WHERE TABLE_SCHEMA = DATABASE() |
| 290 |
AND TABLE_NAME = %s |
| 291 |
AND INDEX_NAME = %s", |
| 292 |
$tbl, |
| 293 |
'placement_unique' |
| 294 |
) |
| 295 |
); |
| 296 |
if ( $has_unique > 0 ) { |
| 297 |
return; |
| 298 |
} |
| 299 |
|
| 300 |
// Self-join keeps the minimum id per (user_id, parent_id, |
| 301 |
// file_type, file_ref) and deletes everything else. Restricted |
| 302 |
// to shortcut + folder placements, where duplicates are never |
| 303 |
// intentional. |
| 304 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 305 |
$wpdb->query( |
| 306 |
"DELETE p1 FROM `{$tbl}` p1 |
| 307 |
INNER JOIN `{$tbl}` p2 |
| 308 |
ON p1.user_id = p2.user_id |
| 309 |
AND p1.parent_id = p2.parent_id |
| 310 |
AND p1.file_type = p2.file_type |
| 311 |
AND p1.file_ref = p2.file_ref |
| 312 |
AND p1.id > p2.id |
| 313 |
WHERE p1.file_type IN ( 'shortcut', 'folder' )" |
| 314 |
); |
| 315 |
} |
| 316 |
|
| 317 |
/** |
| 318 |
* Add a UNIQUE index on |
| 319 |
* `(user_id, parent_id, file_type, file_ref)` to make duplicate |
| 320 |
* placements physically impossible. Skipped when the index is |
| 321 |
* already present. |
| 322 |
* |
| 323 |
* Note: `file_ref` is `VARCHAR(255)` — combined with the three |
| 324 |
* other columns this fits comfortably under MySQL's 3072-byte |
| 325 |
* InnoDB index-key limit on `utf8mb4`. |
| 326 |
* |
| 327 |
* @since 0.8.0 |
| 328 |
* @internal |
| 329 |
*/ |
| 330 |
function desktop_mode_files_ensure_unique_placement_index() { |
| 331 |
global $wpdb; |
| 332 |
$tables = desktop_mode_files_table_names(); |
| 333 |
$tbl = $tables['placements']; |
| 334 |
|
| 335 |
$exists = (int) $wpdb->get_var( |
| 336 |
$wpdb->prepare( |
| 337 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS |
| 338 |
WHERE TABLE_SCHEMA = DATABASE() |
| 339 |
AND TABLE_NAME = %s |
| 340 |
AND INDEX_NAME = %s", |
| 341 |
$tbl, |
| 342 |
'placement_unique' |
| 343 |
) |
| 344 |
); |
| 345 |
if ( 0 === $exists ) { |
| 346 |
// Suppress errors on the ADD KEY in case a concurrent |
| 347 |
// worker won the same race (MySQL 1061: "Duplicate key |
| 348 |
// name"). The index ends up present either way; the |
| 349 |
// check-then-add pattern is benign under contention. |
| 350 |
$prev_suppress = $wpdb->suppress_errors( true ); |
| 351 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 352 |
$wpdb->query( |
| 353 |
"ALTER TABLE `{$tbl}` |
| 354 |
ADD UNIQUE KEY `placement_unique` |
| 355 |
(user_id, parent_id, file_type, file_ref)" |
| 356 |
); |
| 357 |
$wpdb->suppress_errors( $prev_suppress ); |
| 358 |
} |
| 359 |
} |
| 360 |
|
| 361 |
/** |
| 362 |
* Add the v10 `updated_by` column to the placements table. |
| 363 |
* |
| 364 |
* Tracks which user last mutated the row (created, moved, restored). |
| 365 |
* Used by `desktop_mode_files_check_if_match()` so the If-Match 409 |
| 366 |
* conflict toast attributes the change to the SESSION that won the |
| 367 |
* race rather than to the row's static owner — critical when a |
| 368 |
* writer recipient of a shared folder rearranges placements and |
| 369 |
* another viewer hits a stale `If-Match`. |
| 370 |
* |
| 371 |
* NULL on legacy rows (pre-v10). The conflict resolver falls back |
| 372 |
* to `user_id` when this column is NULL, matching the old behavior. |
| 373 |
* |
| 374 |
* @since 0.18.x (schema v10) |
| 375 |
* @internal |
| 376 |
*/ |
| 377 |
function desktop_mode_files_ensure_updated_by_column() { |
| 378 |
global $wpdb; |
| 379 |
$tables = desktop_mode_files_table_names(); |
| 380 |
$tbl = $tables['placements']; |
| 381 |
$exists = (int) $wpdb->get_var( |
| 382 |
$wpdb->prepare( |
| 383 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS |
| 384 |
WHERE TABLE_SCHEMA = DATABASE() |
| 385 |
AND TABLE_NAME = %s |
| 386 |
AND COLUMN_NAME = %s", |
| 387 |
$tbl, |
| 388 |
'updated_by' |
| 389 |
) |
| 390 |
); |
| 391 |
if ( 0 === $exists ) { |
| 392 |
// Suppress errors so a concurrent worker that already won |
| 393 |
// the same race doesn't fire a benign MySQL 1060 |
| 394 |
// ("Duplicate column"). The column ends up present either |
| 395 |
// way. |
| 396 |
$prev_suppress = $wpdb->suppress_errors( true ); |
| 397 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 398 |
$wpdb->query( "ALTER TABLE `{$tbl}` ADD COLUMN `updated_by` BIGINT UNSIGNED NULL AFTER `user_id`" ); |
| 399 |
$wpdb->suppress_errors( $prev_suppress ); |
| 400 |
} |
| 401 |
} |
| 402 |
|
| 403 |
/** |
| 404 |
* Belt-and-suspenders verifier for the v8 `shares` table. `dbDelta` |
| 405 |
* has known edge cases where a brand-new table with `UNIQUE KEY` |
| 406 |
* declarations on a non-`utf8mb4` collation gets silently skipped |
| 407 |
* on some MySQL/MariaDB combos; we mirror the trash-columns |
| 408 |
* pattern and `CREATE TABLE IF NOT EXISTS` the row explicitly. |
| 409 |
* |
| 410 |
* @since 0.18.0 |
| 411 |
* @internal |
| 412 |
*/ |
| 413 |
function desktop_mode_files_ensure_shares_table() { |
| 414 |
global $wpdb; |
| 415 |
$tables = desktop_mode_files_table_names(); |
| 416 |
$charset_collate = $wpdb->get_charset_collate(); |
| 417 |
$tbl = $tables['shares']; |
| 418 |
|
| 419 |
$exists = (int) $wpdb->get_var( |
| 420 |
$wpdb->prepare( |
| 421 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES |
| 422 |
WHERE TABLE_SCHEMA = DATABASE() |
| 423 |
AND TABLE_NAME = %s", |
| 424 |
$tbl |
| 425 |
) |
| 426 |
); |
| 427 |
if ( 0 === $exists ) { |
| 428 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 429 |
$wpdb->query( |
| 430 |
"CREATE TABLE IF NOT EXISTS `{$tbl}` ( |
| 431 |
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 432 |
target_type VARCHAR(32) NOT NULL DEFAULT 'folder', |
| 433 |
folder_id BIGINT UNSIGNED NOT NULL, |
| 434 |
principal_type VARCHAR(16) NOT NULL, |
| 435 |
principal_ref VARCHAR(191) NOT NULL, |
| 436 |
capability VARCHAR(8) NOT NULL DEFAULT 'read', |
| 437 |
state VARCHAR(16) NOT NULL DEFAULT 'pending', |
| 438 |
invited_by BIGINT UNSIGNED NOT NULL, |
| 439 |
invited_at_ms BIGINT UNSIGNED NOT NULL, |
| 440 |
decided_at_ms BIGINT UNSIGNED NULL, |
| 441 |
PRIMARY KEY (id), |
| 442 |
UNIQUE KEY uniq_principal (target_type, folder_id, principal_type, principal_ref), |
| 443 |
KEY by_principal (principal_type, principal_ref, state), |
| 444 |
KEY target (target_type, folder_id) |
| 445 |
) $charset_collate" |
| 446 |
); |
| 447 |
} else { |
| 448 |
// Existing table — make sure `target_type` is there for |
| 449 |
// installs that ran a pre-target_type build of v8. |
| 450 |
$has_col = (int) $wpdb->get_var( |
| 451 |
$wpdb->prepare( |
| 452 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS |
| 453 |
WHERE TABLE_SCHEMA = DATABASE() |
| 454 |
AND TABLE_NAME = %s |
| 455 |
AND COLUMN_NAME = %s", |
| 456 |
$tbl, |
| 457 |
'target_type' |
| 458 |
) |
| 459 |
); |
| 460 |
if ( 0 === $has_col ) { |
| 461 |
// Same TOCTOU rationale as the other ensure_* helpers |
| 462 |
// — concurrent worker that already added the column |
| 463 |
// surfaces a benign MySQL 1060 we should swallow. |
| 464 |
$prev_suppress = $wpdb->suppress_errors( true ); |
| 465 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 466 |
$wpdb->query( "ALTER TABLE `{$tbl}` ADD COLUMN `target_type` VARCHAR(32) NOT NULL DEFAULT 'folder' AFTER `id`" ); |
| 467 |
$wpdb->suppress_errors( $prev_suppress ); |
| 468 |
} |
| 469 |
} |
| 470 |
} |
| 471 |
|
| 472 |
/** |
| 473 |
* Belt-and-suspenders verifier for the decisions table. |
| 474 |
* |
| 475 |
* @since 0.18.0 |
| 476 |
* @internal |
| 477 |
*/ |
| 478 |
function desktop_mode_files_ensure_decisions_table() { |
| 479 |
global $wpdb; |
| 480 |
$tables = desktop_mode_files_table_names(); |
| 481 |
$charset_collate = $wpdb->get_charset_collate(); |
| 482 |
$tbl = $tables['decisions']; |
| 483 |
|
| 484 |
$exists = (int) $wpdb->get_var( |
| 485 |
$wpdb->prepare( |
| 486 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES |
| 487 |
WHERE TABLE_SCHEMA = DATABASE() |
| 488 |
AND TABLE_NAME = %s", |
| 489 |
$tbl |
| 490 |
) |
| 491 |
); |
| 492 |
if ( 0 === $exists ) { |
| 493 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 494 |
$wpdb->query( |
| 495 |
"CREATE TABLE IF NOT EXISTS `{$tbl}` ( |
| 496 |
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 497 |
share_id BIGINT UNSIGNED NOT NULL, |
| 498 |
user_id BIGINT UNSIGNED NOT NULL, |
| 499 |
state VARCHAR(16) NOT NULL DEFAULT 'pending', |
| 500 |
decided_at_ms BIGINT UNSIGNED NOT NULL, |
| 501 |
PRIMARY KEY (id), |
| 502 |
UNIQUE KEY uniq_share_user (share_id, user_id), |
| 503 |
KEY by_user (user_id, state) |
| 504 |
) $charset_collate" |
| 505 |
); |
| 506 |
} |
| 507 |
} |
| 508 |
|
| 509 |
/** |
| 510 |
* Lazy migrator — runs on `admin_init` when the stored schema |
| 511 |
* version doesn't match the constant. Idempotent: `dbDelta` |
| 512 |
* itself is a no-op when the table already matches. |
| 513 |
* |
| 514 |
* @since 0.9.0 |
| 515 |
*/ |
| 516 |
function desktop_mode_files_maybe_install_schema() { |
| 517 |
$installed = get_option( DESKTOP_MODE_FILES_SCHEMA_OPTION, '' ); |
| 518 |
if ( $installed === DESKTOP_MODE_FILES_SCHEMA_VERSION ) { |
| 519 |
return; |
| 520 |
} |
| 521 |
desktop_mode_files_install_schema(); |
| 522 |
} |
| 523 |
add_action( 'admin_init', 'desktop_mode_files_maybe_install_schema' ); |
| 524 |
// REST + front-end requests never fire `admin_init` — without these |
| 525 |
// hooks a session that hits a REST endpoint before any admin page |
| 526 |
// load would query the placements / folders tables before the v2 |
| 527 |
// trash columns exist, throwing wpdb errors and blanking the desktop. |
| 528 |
add_action( 'rest_api_init', 'desktop_mode_files_maybe_install_schema' ); |
| 529 |
add_action( 'init', 'desktop_mode_files_maybe_install_schema', 1 ); |
| 530 |
register_activation_hook( DESKTOP_MODE_FILE, 'desktop_mode_files_install_schema' ); |
| 531 |
|
| 532 |
/** |
| 533 |
* Current epoch-ms timestamp. Centralized so the store and the |
| 534 |
* tombstone writer stay in lock-step. |
| 535 |
* |
| 536 |
* @since 0.9.0 |
| 537 |
* |
| 538 |
* @return int |
| 539 |
*/ |
| 540 |
function desktop_mode_files_now_ms() { |
| 541 |
return (int) round( microtime( true ) * 1000 ); |
| 542 |
} |
| 543 |
|
