| 1 |
<?php |
| 2 |
/** |
| 3 |
* Desktop Mode — Files-on-the-Desktop schema. |
| 4 |
* |
| 5 |
* Five custom tables back the system: |
| 6 |
* |
| 7 |
* - `_desktop_mode_file_placements` — every (user, parent_folder, |
| 8 |
* type, ref, x, y, sort) tuple. Indexed on `(owner_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 |
* - `_desktop_mode_folder_shares` — one row per (folder, principal) |
| 26 |
* grant: user- or role-principal, `read` | `write` capability, |
| 27 |
* `pending` | `accepted` | `denied` state. |
| 28 |
* |
| 29 |
* - `_desktop_mode_share_user_decisions` — per-user opt-ins for |
| 30 |
* role-principal shares (each member of the role accepts or |
| 31 |
* denies individually; the shares row itself stays `pending`). |
| 32 |
* |
| 33 |
* dbDelta is the only safe path for schema migrations against the |
| 34 |
* Core tables environment — Phase 6 re-uses this file by bumping |
| 35 |
* `DESKTOP_MODE_FILES_SCHEMA_VERSION` and adding columns. |
| 36 |
* |
| 37 |
* @package WPDesktopMode |
| 38 |
* @since 0.9.0 |
| 39 |
*/ |
| 40 |
|
| 41 |
defined( 'ABSPATH' ) || exit; |
| 42 |
|
| 43 |
define( 'DESKTOP_MODE_FILES_SCHEMA_VERSION', '13' ); |
| 44 |
define( 'DESKTOP_MODE_FILES_SCHEMA_OPTION', 'desktop_mode_files_schema_version' ); |
| 45 |
|
| 46 |
/** |
| 47 |
* Returns the per-table names with the active prefix applied. |
| 48 |
* |
| 49 |
* @since 0.9.0 |
| 50 |
* |
| 51 |
* @return array{ placements: string, folders: string, tombstones: string, shares: string, decisions: string } |
| 52 |
*/ |
| 53 |
function desktop_mode_files_table_names() { |
| 54 |
global $wpdb; |
| 55 |
return array( |
| 56 |
'placements' => $wpdb->prefix . 'desktop_mode_file_placements', |
| 57 |
'folders' => $wpdb->prefix . 'desktop_mode_folders', |
| 58 |
'tombstones' => $wpdb->prefix . 'desktop_mode_file_tombstones', |
| 59 |
'shares' => $wpdb->prefix . 'desktop_mode_folder_shares', |
| 60 |
'decisions' => $wpdb->prefix . 'desktop_mode_share_user_decisions', |
| 61 |
'stored_files' => $wpdb->prefix . 'desktop_mode_stored_files', |
| 62 |
); |
| 63 |
} |
| 64 |
|
| 65 |
/** |
| 66 |
* Idempotent `dbDelta` call. Hooked on plugin activation and on |
| 67 |
* `admin_init` (gated by a version-option mismatch) so a manual |
| 68 |
* file copy install still ends up with the tables. |
| 69 |
* |
| 70 |
* @since 0.9.0 |
| 71 |
*/ |
| 72 |
function desktop_mode_files_install_schema() { |
| 73 |
global $wpdb; |
| 74 |
|
| 75 |
require_once ABSPATH . 'wp-admin/includes/upgrade.php'; |
| 76 |
|
| 77 |
$tables = desktop_mode_files_table_names(); |
| 78 |
$charset_collate = $wpdb->get_charset_collate(); |
| 79 |
|
| 80 |
// Schema v2 (since 0.8.0): adds trash columns to both placements |
| 81 |
// and folders so deleted shortcuts and folders land in the |
| 82 |
// recycle bin instead of vanishing. `trashed_at_ms` is the |
| 83 |
// epoch-ms timestamp of the trash event (NULL = active). |
| 84 |
// `trashed_by` records the user that fired it (for permission |
| 85 |
// checks on restore). `trashed_via_folder` on placements is the |
| 86 |
// id of the folder whose trash cascaded the placement, so a |
| 87 |
// folder restore knows exactly which children to bring back — |
| 88 |
// precise round-trip with no time-window heuristics. |
| 89 |
$placements_sql = "CREATE TABLE {$tables['placements']} ( |
| 90 |
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 91 |
owner_id BIGINT UNSIGNED NOT NULL, |
| 92 |
parent_id BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 93 |
file_type VARCHAR(64) NOT NULL, |
| 94 |
file_ref VARCHAR(255) NOT NULL DEFAULT '', |
| 95 |
x INT NOT NULL DEFAULT 0, |
| 96 |
y INT NOT NULL DEFAULT 0, |
| 97 |
sort_order INT NOT NULL DEFAULT 0, |
| 98 |
updated_at_ms BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 99 |
meta LONGTEXT NULL, |
| 100 |
trashed_at_ms BIGINT UNSIGNED NULL, |
| 101 |
trashed_by BIGINT UNSIGNED NULL, |
| 102 |
trashed_via_folder BIGINT UNSIGNED NULL, |
| 103 |
trashed_meta LONGTEXT NULL, |
| 104 |
PRIMARY KEY (id), |
| 105 |
KEY owner_parent (owner_id, parent_id), |
| 106 |
KEY type_ref (file_type, file_ref), |
| 107 |
KEY updated_at_ms (updated_at_ms), |
| 108 |
KEY trashed_at_ms (trashed_at_ms) |
| 109 |
) $charset_collate;"; |
| 110 |
|
| 111 |
$folders_sql = "CREATE TABLE {$tables['folders']} ( |
| 112 |
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 113 |
owner_id BIGINT UNSIGNED NOT NULL, |
| 114 |
name VARCHAR(255) NOT NULL DEFAULT '', |
| 115 |
share_mode VARCHAR(16) NOT NULL DEFAULT 'private', |
| 116 |
share_meta LONGTEXT NULL, |
| 117 |
updated_at_ms BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 118 |
trashed_at_ms BIGINT UNSIGNED NULL, |
| 119 |
trashed_by BIGINT UNSIGNED NULL, |
| 120 |
trashed_meta LONGTEXT NULL, |
| 121 |
PRIMARY KEY (id), |
| 122 |
KEY owner_id (owner_id), |
| 123 |
KEY share_mode (share_mode), |
| 124 |
KEY updated_at_ms (updated_at_ms), |
| 125 |
KEY trashed_at_ms (trashed_at_ms) |
| 126 |
) $charset_collate;"; |
| 127 |
|
| 128 |
$tombstones_sql = "CREATE TABLE {$tables['tombstones']} ( |
| 129 |
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 130 |
kind VARCHAR(16) NOT NULL, |
| 131 |
ref_id BIGINT UNSIGNED NOT NULL, |
| 132 |
removed_at_ms BIGINT UNSIGNED NOT NULL, |
| 133 |
PRIMARY KEY (id), |
| 134 |
KEY kind_removed (kind, removed_at_ms) |
| 135 |
) $charset_collate;"; |
| 136 |
|
| 137 |
// Schema v13 (since 0.9.6): real per-user file storage. One row |
| 138 |
// per uploaded file; the bytes live flat on disk under |
| 139 |
// `uploads/desktop-mode-files/<owner_id>/<disk_name>` with a |
| 140 |
// server-generated extensionless `disk_name` (UUID) — hierarchy, |
| 141 |
// naming, and sharing are entirely DB concerns (folders + |
| 142 |
// placements + shares tables), the disk is a dumb blob store. |
| 143 |
// No UNIQUE keys beyond the PK on purpose: dbDelta's UNIQUE-KEY |
| 144 |
// quirks (see the shares-table comment below) don't apply, and |
| 145 |
// disk_name uniqueness is guaranteed by the UUID generator plus |
| 146 |
// a collision check at write time. |
| 147 |
$stored_files_sql = "CREATE TABLE {$tables['stored_files']} ( |
| 148 |
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 149 |
owner_id BIGINT UNSIGNED NOT NULL, |
| 150 |
display_name VARCHAR(255) NOT NULL DEFAULT '', |
| 151 |
disk_name VARCHAR(64) NOT NULL DEFAULT '', |
| 152 |
size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 153 |
mime VARCHAR(100) NOT NULL DEFAULT '', |
| 154 |
created_at_ms BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 155 |
updated_at_ms BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 156 |
PRIMARY KEY (id), |
| 157 |
KEY owner_id (owner_id), |
| 158 |
KEY disk_name (disk_name) |
| 159 |
) $charset_collate;"; |
| 160 |
|
| 161 |
// Schema v9 — per-principal grants (`folder_shares` table) + |
| 162 |
// per-user opt-in decisions (`share_user_decisions` table). |
| 163 |
// `share_meta` on the folders row stays as a diagnostic-only |
| 164 |
// column; visibility is computed entirely from the shares |
| 165 |
// table. |
| 166 |
// |
| 167 |
// Shares + decisions are intentionally NOT routed through |
| 168 |
// dbDelta. Their `ensure_*_table()` helpers below are the sole |
| 169 |
// creators. dbDelta uses `DESCRIBE` to detect existing tables |
| 170 |
// and falls back to a bare `CREATE TABLE` (no IF NOT EXISTS) |
| 171 |
// when DESCRIBE returns empty — under certain MySQL / MariaDB |
| 172 |
// configurations (case-folding mismatches, transient connection |
| 173 |
// states, the `lower_case_table_names` quirk on case-sensitive |
| 174 |
// filesystems) DESCRIBE can fail on a table that physically |
| 175 |
// exists, and dbDelta then issues a CREATE that blows up with |
| 176 |
// "Table … already exists" (MySQL error 1050). The `ensure_*` |
| 177 |
// helpers use INFORMATION_SCHEMA + explicit `CREATE TABLE IF NOT |
| 178 |
// EXISTS`, which is bullet-proof; v9 → vN column additions are |
| 179 |
// handled by `ALTER TABLE … ADD COLUMN` inside the same helper. |
| 180 |
|
| 181 |
// v11: rename `placements.user_id` to `placements.owner_id` so |
| 182 |
// the placements + folders tables use the same column name for |
| 183 |
// the same concept. Must run BEFORE dbDelta — once the |
| 184 |
// `$placements_sql` definition switches from `user_id` to |
| 185 |
// `owner_id`, dbDelta on an existing v≤10 install would see |
| 186 |
// `owner_id` as a missing column and ADD it (leaving the old |
| 187 |
// `user_id` in place + the new `owner_id` NULL). Running the |
| 188 |
// CHANGE COLUMN first means dbDelta sees the table already |
| 189 |
// matches the desired shape. |
| 190 |
desktop_mode_files_rename_user_id_to_owner_id(); |
| 191 |
|
| 192 |
dbDelta( $placements_sql ); |
| 193 |
dbDelta( $folders_sql ); |
| 194 |
dbDelta( $tombstones_sql ); |
| 195 |
dbDelta( $stored_files_sql ); |
| 196 |
|
| 197 |
// dbDelta has well-documented quirks with `NULL`-only columns |
| 198 |
// (no DEFAULT) — under some MySQL/MariaDB combos it silently |
| 199 |
// skips the ADD COLUMN. Verify the v2 trash columns are |
| 200 |
// physically present and ALTER them in directly when not. |
| 201 |
desktop_mode_files_ensure_trash_columns(); |
| 202 |
|
| 203 |
// v4: clean up duplicate placements created by sessions that |
| 204 |
// hit the auto-orphan-placer while the v2 trash columns were |
| 205 |
// missing — every `WHERE trashed_at_ms IS NULL` precheck |
| 206 |
// returned empty, so each pageload re-inserted every |
| 207 |
// registered shortcut. Collapse runs of identical |
| 208 |
// `(owner_id, parent_id, file_type, file_ref)` rows down to |
| 209 |
// the lowest id. |
| 210 |
desktop_mode_files_dedupe_placements(); |
| 211 |
|
| 212 |
// v5: enforce uniqueness at the DB level so a future bug |
| 213 |
// (or a racing pair of REST requests) can never re-create |
| 214 |
// the duplicate shortcuts again. Must run AFTER dedupe — |
| 215 |
// adding a unique key against duplicate rows would fail. |
| 216 |
desktop_mode_files_ensure_unique_placement_index(); |
| 217 |
|
| 218 |
// v9: belt-and-suspenders existence check for the shares + |
| 219 |
// decisions tables. The folder-sharing feature is the |
| 220 |
// canonical source of truth for "who can see this folder" — |
| 221 |
// the `share_meta` JSON column on the folders table remains |
| 222 |
// for diagnostic purposes only and is not consulted by the |
| 223 |
// visibility resolver. |
| 224 |
desktop_mode_files_ensure_shares_table(); |
| 225 |
desktop_mode_files_ensure_decisions_table(); |
| 226 |
|
| 227 |
// v10: `updated_by` column on placements so the If-Match 409 |
| 228 |
// conflict toast names the SESSION that actually won the race, |
| 229 |
// not just whoever currently owns the row. Critical for the |
| 230 |
// shared-write scenario where User B (writer recipient) moves a |
| 231 |
// placement and User C gets the conflict — without this column, |
| 232 |
// the toast would blame User A (owner of the row). |
| 233 |
desktop_mode_files_ensure_updated_by_column(); |
| 234 |
|
| 235 |
update_option( DESKTOP_MODE_FILES_SCHEMA_OPTION, DESKTOP_MODE_FILES_SCHEMA_VERSION ); |
| 236 |
|
| 237 |
/** |
| 238 |
* Fires after the files schema is installed / migrated. |
| 239 |
* |
| 240 |
* @since 0.9.0 |
| 241 |
* |
| 242 |
* @param string $version The version that was installed. |
| 243 |
*/ |
| 244 |
do_action( 'desktop_mode_files_schema_installed', DESKTOP_MODE_FILES_SCHEMA_VERSION ); |
| 245 |
} |
| 246 |
|
| 247 |
/** |
| 248 |
* Belt-and-suspenders verifier for the v2 trash columns. Reads |
| 249 |
* `INFORMATION_SCHEMA.COLUMNS` for the placements + folders tables |
| 250 |
* and `ALTER`s in any column dbDelta missed. Idempotent: each |
| 251 |
* `ALTER` only fires when the column is not already there. |
| 252 |
* |
| 253 |
* @since 0.8.0 |
| 254 |
* @internal |
| 255 |
*/ |
| 256 |
function desktop_mode_files_ensure_trash_columns() { |
| 257 |
global $wpdb; |
| 258 |
$tables = desktop_mode_files_table_names(); |
| 259 |
|
| 260 |
// Two-worker race protection: between the INFORMATION_SCHEMA |
| 261 |
// check and the ALTER, a concurrent worker (cron + admin-init, |
| 262 |
// REST + heartbeat) can run the same check, see the column |
| 263 |
// missing, and both fire ALTER. The second hits MySQL error |
| 264 |
// 1060 ("Duplicate column"). Suppressing wpdb errors around |
| 265 |
// the ALTER swallows that benign log line. The column ends up |
| 266 |
// present either way — we re-verify with a second |
| 267 |
// INFORMATION_SCHEMA query and only surface an error when the |
| 268 |
// column is genuinely missing after the attempt. |
| 269 |
$ensure = static function ( $table, $column, $definition ) use ( $wpdb ) { |
| 270 |
$col_exists = static function () use ( $wpdb, $table, $column ) { |
| 271 |
return (int) $wpdb->get_var( |
| 272 |
$wpdb->prepare( |
| 273 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS |
| 274 |
WHERE TABLE_SCHEMA = DATABASE() |
| 275 |
AND TABLE_NAME = %s |
| 276 |
AND COLUMN_NAME = %s", |
| 277 |
$table, |
| 278 |
$column |
| 279 |
) |
| 280 |
); |
| 281 |
}; |
| 282 |
if ( $col_exists() > 0 ) { |
| 283 |
return; |
| 284 |
} |
| 285 |
$prev_suppress = $wpdb->suppress_errors( true ); |
| 286 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 287 |
$wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}" ); |
| 288 |
$wpdb->suppress_errors( $prev_suppress ); |
| 289 |
// Belt-and-suspenders: if the column STILL isn't there |
| 290 |
// after the ALTER (real schema error, not a race), retry |
| 291 |
// once unsuppressed so WP_DEBUG users see the cause. |
| 292 |
if ( $col_exists() === 0 ) { |
| 293 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 294 |
$wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}" ); |
| 295 |
} |
| 296 |
}; |
| 297 |
|
| 298 |
$ensure( $tables['placements'], 'trashed_at_ms', 'BIGINT UNSIGNED NULL' ); |
| 299 |
$ensure( $tables['placements'], 'trashed_by', 'BIGINT UNSIGNED NULL' ); |
| 300 |
$ensure( $tables['placements'], 'trashed_via_folder', 'BIGINT UNSIGNED NULL' ); |
| 301 |
// v6: ancestry snapshot — JSON capturing every folder in the |
| 302 |
// parent chain at trash time so a restore can resurrect the |
| 303 |
// chain even when a folder was hard-deleted in the meantime. |
| 304 |
$ensure( $tables['placements'], 'trashed_meta', 'LONGTEXT NULL' ); |
| 305 |
$ensure( $tables['folders'], 'trashed_at_ms', 'BIGINT UNSIGNED NULL' ); |
| 306 |
$ensure( $tables['folders'], 'trashed_by', 'BIGINT UNSIGNED NULL' ); |
| 307 |
$ensure( $tables['folders'], 'trashed_meta', 'LONGTEXT NULL' ); |
| 308 |
} |
| 309 |
|
| 310 |
/** |
| 311 |
* Collapse duplicate `(owner_id, parent_id, file_type, file_ref)` |
| 312 |
* placement rows down to the lowest id, deleting the rest. The |
| 313 |
* DELETE is restricted to `file_type IN ('shortcut','folder')` — |
| 314 |
* the types where legacy duplicates were actually observed. Note |
| 315 |
* that the v5 `placement_unique` index added right after this |
| 316 |
* covers EVERY file type, so duplicates of any type within the |
| 317 |
* same (owner, parent) are disallowed at the DB level; if legacy |
| 318 |
* duplicates of another type exist, the (error-suppressed) |
| 319 |
* `ADD UNIQUE` in |
| 320 |
* `desktop_mode_files_ensure_unique_placement_index()` will fail |
| 321 |
* and leave the index absent until those rows are cleaned up. |
| 322 |
* |
| 323 |
* @since 0.8.0 |
| 324 |
* @internal |
| 325 |
*/ |
| 326 |
function desktop_mode_files_dedupe_placements() { |
| 327 |
global $wpdb; |
| 328 |
$tables = desktop_mode_files_table_names(); |
| 329 |
$tbl = $tables['placements']; |
| 330 |
|
| 331 |
// Once the unique index exists, MySQL prevents duplicate |
| 332 |
// inserts at the DB level — dedupe is a no-op and the |
| 333 |
// table-scanning DELETE is pure waste on every install_schema |
| 334 |
// call. Skip in that case so the cost is paid exactly once, |
| 335 |
// during the v4 → v5 migration. |
| 336 |
$has_unique = (int) $wpdb->get_var( |
| 337 |
$wpdb->prepare( |
| 338 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS |
| 339 |
WHERE TABLE_SCHEMA = DATABASE() |
| 340 |
AND TABLE_NAME = %s |
| 341 |
AND INDEX_NAME = %s", |
| 342 |
$tbl, |
| 343 |
'placement_unique' |
| 344 |
) |
| 345 |
); |
| 346 |
if ( $has_unique > 0 ) { |
| 347 |
return; |
| 348 |
} |
| 349 |
|
| 350 |
// Self-join keeps the minimum id per (user_id, parent_id, |
| 351 |
// file_type, file_ref) and deletes everything else. Restricted |
| 352 |
// to shortcut + folder placements, where duplicates are never |
| 353 |
// intentional. |
| 354 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 355 |
$wpdb->query( |
| 356 |
"DELETE p1 FROM `{$tbl}` p1 |
| 357 |
INNER JOIN `{$tbl}` p2 |
| 358 |
ON p1.owner_id = p2.owner_id |
| 359 |
AND p1.parent_id = p2.parent_id |
| 360 |
AND p1.file_type = p2.file_type |
| 361 |
AND p1.file_ref = p2.file_ref |
| 362 |
AND p1.id > p2.id |
| 363 |
WHERE p1.file_type IN ( 'shortcut', 'folder' )" |
| 364 |
); |
| 365 |
} |
| 366 |
|
| 367 |
/** |
| 368 |
* Add a UNIQUE index on |
| 369 |
* `(owner_id, parent_id, file_type, file_ref)` to make duplicate |
| 370 |
* placements physically impossible. Skipped when the index is |
| 371 |
* already present. |
| 372 |
* |
| 373 |
* Note: `file_ref` is `VARCHAR(255)` — combined with the three |
| 374 |
* other columns this fits comfortably under MySQL's 3072-byte |
| 375 |
* InnoDB index-key limit on `utf8mb4`. |
| 376 |
* |
| 377 |
* @since 0.8.0 |
| 378 |
* @internal |
| 379 |
*/ |
| 380 |
function desktop_mode_files_ensure_unique_placement_index() { |
| 381 |
global $wpdb; |
| 382 |
$tables = desktop_mode_files_table_names(); |
| 383 |
$tbl = $tables['placements']; |
| 384 |
|
| 385 |
$exists = (int) $wpdb->get_var( |
| 386 |
$wpdb->prepare( |
| 387 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS |
| 388 |
WHERE TABLE_SCHEMA = DATABASE() |
| 389 |
AND TABLE_NAME = %s |
| 390 |
AND INDEX_NAME = %s", |
| 391 |
$tbl, |
| 392 |
'placement_unique' |
| 393 |
) |
| 394 |
); |
| 395 |
if ( 0 === $exists ) { |
| 396 |
// Suppress errors on the ADD KEY in case a concurrent |
| 397 |
// worker won the same race (MySQL 1061: "Duplicate key |
| 398 |
// name"). The index ends up present either way; the |
| 399 |
// check-then-add pattern is benign under contention. |
| 400 |
$prev_suppress = $wpdb->suppress_errors( true ); |
| 401 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 402 |
$wpdb->query( |
| 403 |
"ALTER TABLE `{$tbl}` |
| 404 |
ADD UNIQUE KEY `placement_unique` |
| 405 |
(owner_id, parent_id, file_type, file_ref)" |
| 406 |
); |
| 407 |
$wpdb->suppress_errors( $prev_suppress ); |
| 408 |
} |
| 409 |
} |
| 410 |
|
| 411 |
/** |
| 412 |
* Add the v10 `updated_by` column to the placements table. |
| 413 |
* |
| 414 |
* Tracks which user last mutated the row (created, moved, restored). |
| 415 |
* Used by `desktop_mode_files_check_if_match()` so the If-Match 409 |
| 416 |
* conflict toast attributes the change to the SESSION that won the |
| 417 |
* race rather than to the row's static owner — critical when a |
| 418 |
* writer recipient of a shared folder rearranges placements and |
| 419 |
* another viewer hits a stale `If-Match`. |
| 420 |
* |
| 421 |
* NULL on legacy rows (pre-v10). The conflict resolver falls back |
| 422 |
* to `owner_id` when this column is NULL, matching the old behavior. |
| 423 |
* |
| 424 |
* @since 0.8.5 (schema v10) |
| 425 |
* @internal |
| 426 |
*/ |
| 427 |
function desktop_mode_files_ensure_updated_by_column() { |
| 428 |
global $wpdb; |
| 429 |
$tables = desktop_mode_files_table_names(); |
| 430 |
$tbl = $tables['placements']; |
| 431 |
$exists = (int) $wpdb->get_var( |
| 432 |
$wpdb->prepare( |
| 433 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS |
| 434 |
WHERE TABLE_SCHEMA = DATABASE() |
| 435 |
AND TABLE_NAME = %s |
| 436 |
AND COLUMN_NAME = %s", |
| 437 |
$tbl, |
| 438 |
'updated_by' |
| 439 |
) |
| 440 |
); |
| 441 |
if ( 0 === $exists ) { |
| 442 |
// Suppress errors so a concurrent worker that already won |
| 443 |
// the same race doesn't fire a benign MySQL 1060 |
| 444 |
// ("Duplicate column"). The column ends up present either |
| 445 |
// way. |
| 446 |
$prev_suppress = $wpdb->suppress_errors( true ); |
| 447 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 448 |
$wpdb->query( "ALTER TABLE `{$tbl}` ADD COLUMN `updated_by` BIGINT UNSIGNED NULL AFTER `owner_id`" ); |
| 449 |
$wpdb->suppress_errors( $prev_suppress ); |
| 450 |
} |
| 451 |
} |
| 452 |
|
| 453 |
/** |
| 454 |
* Rename `placements.user_id` to `placements.owner_id` and the |
| 455 |
* matching `user_parent` index to `owner_parent`. The two |
| 456 |
* desktop-mode-owned tables historically used different names for |
| 457 |
* the same "row's owner" concept — folders carried `owner_id` from |
| 458 |
* day one, placements carried `user_id`. v11 unifies them so SQL |
| 459 |
* and PHP read identically across the two tables. |
| 460 |
* |
| 461 |
* Idempotent: skips when the table doesn't exist (fresh install — |
| 462 |
* dbDelta runs after this and creates the table with the new |
| 463 |
* column name directly) and when the column is already renamed. |
| 464 |
* |
| 465 |
* Must run BEFORE `dbDelta( $placements_sql )` in |
| 466 |
* `desktop_mode_files_install_schema()` — dbDelta does NOT rename |
| 467 |
* columns, so against a v≤10 table whose definition says |
| 468 |
* `owner_id` it would ADD a new `owner_id` column and leave the |
| 469 |
* stale `user_id` in place. Running the CHANGE COLUMN first puts |
| 470 |
* the table in the desired shape so dbDelta sees no diff. |
| 471 |
* |
| 472 |
* Schema version was bumped from 11 to 12 so any install that |
| 473 |
* stamped 11 but never actually renamed the column (a silent |
| 474 |
* partial run during dev iteration) gets a clean retry. The |
| 475 |
* function is idempotent — early-returns when `user_id` is absent, |
| 476 |
* so healthy v11 installs see a cheap no-op on the retry. |
| 477 |
* |
| 478 |
* @since 0.8.9 (schema v11, redelivered at v12) |
| 479 |
* @internal |
| 480 |
*/ |
| 481 |
function desktop_mode_files_rename_user_id_to_owner_id() { |
| 482 |
global $wpdb; |
| 483 |
$tables = desktop_mode_files_table_names(); |
| 484 |
$tbl = $tables['placements']; |
| 485 |
|
| 486 |
// Fresh install — the table doesn't exist yet; dbDelta creates |
| 487 |
// it with `owner_id` directly. Nothing to migrate. |
| 488 |
$table_exists = (int) $wpdb->get_var( |
| 489 |
$wpdb->prepare( |
| 490 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES |
| 491 |
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s", |
| 492 |
$tbl |
| 493 |
) |
| 494 |
); |
| 495 |
if ( 0 === $table_exists ) { |
| 496 |
return; |
| 497 |
} |
| 498 |
|
| 499 |
$has_user_id = (int) $wpdb->get_var( |
| 500 |
$wpdb->prepare( |
| 501 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS |
| 502 |
WHERE TABLE_SCHEMA = DATABASE() |
| 503 |
AND TABLE_NAME = %s |
| 504 |
AND COLUMN_NAME = 'user_id'", |
| 505 |
$tbl |
| 506 |
) |
| 507 |
); |
| 508 |
if ( 0 === $has_user_id ) { |
| 509 |
return; // Already renamed (or column was never there — fresh install via test factory). |
| 510 |
} |
| 511 |
|
| 512 |
// DELIBERATELY NOT suppressing errors here. An earlier draft |
| 513 |
// wrapped the ALTER in `suppress_errors( true )` "in case of |
| 514 |
// concurrent migration race" — there's no realistic race for |
| 515 |
// this ALTER (schema migrations run inside one request) and the |
| 516 |
// suppression hid a real failure mode on at least one local |
| 517 |
// install: the option got stamped at v11 but the CHANGE COLUMN |
| 518 |
// never landed, so every later placements query 500'd silently. |
| 519 |
// Let any wpdb error surface to `debug.log` so the failure is |
| 520 |
// visible the next time this code runs. |
| 521 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 522 |
$wpdb->query( |
| 523 |
"ALTER TABLE `{$tbl}` CHANGE COLUMN `user_id` `owner_id` BIGINT UNSIGNED NOT NULL" |
| 524 |
); |
| 525 |
|
| 526 |
// MySQL/MariaDB auto-update index column references on CHANGE |
| 527 |
// COLUMN, but the index NAMES are baked in. Rename them too so |
| 528 |
// `EXPLAIN`/`SHOW INDEX` output reads consistently with the |
| 529 |
// column. |
| 530 |
$has_user_parent = (int) $wpdb->get_var( |
| 531 |
$wpdb->prepare( |
| 532 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS |
| 533 |
WHERE TABLE_SCHEMA = DATABASE() |
| 534 |
AND TABLE_NAME = %s |
| 535 |
AND INDEX_NAME = 'user_parent'", |
| 536 |
$tbl |
| 537 |
) |
| 538 |
); |
| 539 |
if ( 0 < $has_user_parent ) { |
| 540 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 541 |
$wpdb->query( "ALTER TABLE `{$tbl}` RENAME INDEX `user_parent` TO `owner_parent`" ); |
| 542 |
} |
| 543 |
} |
| 544 |
|
| 545 |
/** |
| 546 |
* Belt-and-suspenders verifier for the v8 `shares` table. `dbDelta` |
| 547 |
* has known edge cases where a brand-new table with `UNIQUE KEY` |
| 548 |
* declarations on a non-`utf8mb4` collation gets silently skipped |
| 549 |
* on some MySQL/MariaDB combos; we mirror the trash-columns |
| 550 |
* pattern and `CREATE TABLE IF NOT EXISTS` the row explicitly. |
| 551 |
* |
| 552 |
* @since 0.8.5 |
| 553 |
* @internal |
| 554 |
*/ |
| 555 |
function desktop_mode_files_ensure_shares_table() { |
| 556 |
global $wpdb; |
| 557 |
$tables = desktop_mode_files_table_names(); |
| 558 |
$charset_collate = $wpdb->get_charset_collate(); |
| 559 |
$tbl = $tables['shares']; |
| 560 |
|
| 561 |
$exists = (int) $wpdb->get_var( |
| 562 |
$wpdb->prepare( |
| 563 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES |
| 564 |
WHERE TABLE_SCHEMA = DATABASE() |
| 565 |
AND TABLE_NAME = %s", |
| 566 |
$tbl |
| 567 |
) |
| 568 |
); |
| 569 |
if ( 0 === $exists ) { |
| 570 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 571 |
$wpdb->query( |
| 572 |
"CREATE TABLE IF NOT EXISTS `{$tbl}` ( |
| 573 |
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 574 |
target_type VARCHAR(32) NOT NULL DEFAULT 'folder', |
| 575 |
folder_id BIGINT UNSIGNED NOT NULL, |
| 576 |
principal_type VARCHAR(16) NOT NULL, |
| 577 |
principal_ref VARCHAR(191) NOT NULL, |
| 578 |
capability VARCHAR(8) NOT NULL DEFAULT 'read', |
| 579 |
state VARCHAR(16) NOT NULL DEFAULT 'pending', |
| 580 |
invited_by BIGINT UNSIGNED NOT NULL, |
| 581 |
invited_at_ms BIGINT UNSIGNED NOT NULL, |
| 582 |
decided_at_ms BIGINT UNSIGNED NULL, |
| 583 |
PRIMARY KEY (id), |
| 584 |
UNIQUE KEY uniq_principal (target_type, folder_id, principal_type, principal_ref), |
| 585 |
KEY by_principal (principal_type, principal_ref, state), |
| 586 |
KEY target (target_type, folder_id) |
| 587 |
) $charset_collate" |
| 588 |
); |
| 589 |
} else { |
| 590 |
// Existing table — make sure `target_type` is there for |
| 591 |
// installs that ran a pre-target_type build of v8. |
| 592 |
$has_col = (int) $wpdb->get_var( |
| 593 |
$wpdb->prepare( |
| 594 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS |
| 595 |
WHERE TABLE_SCHEMA = DATABASE() |
| 596 |
AND TABLE_NAME = %s |
| 597 |
AND COLUMN_NAME = %s", |
| 598 |
$tbl, |
| 599 |
'target_type' |
| 600 |
) |
| 601 |
); |
| 602 |
if ( 0 === $has_col ) { |
| 603 |
// Same TOCTOU rationale as the other ensure_* helpers |
| 604 |
// — concurrent worker that already added the column |
| 605 |
// surfaces a benign MySQL 1060 we should swallow. |
| 606 |
$prev_suppress = $wpdb->suppress_errors( true ); |
| 607 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 608 |
$wpdb->query( "ALTER TABLE `{$tbl}` ADD COLUMN `target_type` VARCHAR(32) NOT NULL DEFAULT 'folder' AFTER `id`" ); |
| 609 |
$wpdb->suppress_errors( $prev_suppress ); |
| 610 |
} |
| 611 |
} |
| 612 |
} |
| 613 |
|
| 614 |
/** |
| 615 |
* Belt-and-suspenders verifier for the decisions table. |
| 616 |
* |
| 617 |
* @since 0.8.5 |
| 618 |
* @internal |
| 619 |
*/ |
| 620 |
function desktop_mode_files_ensure_decisions_table() { |
| 621 |
global $wpdb; |
| 622 |
$tables = desktop_mode_files_table_names(); |
| 623 |
$charset_collate = $wpdb->get_charset_collate(); |
| 624 |
$tbl = $tables['decisions']; |
| 625 |
|
| 626 |
$exists = (int) $wpdb->get_var( |
| 627 |
$wpdb->prepare( |
| 628 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES |
| 629 |
WHERE TABLE_SCHEMA = DATABASE() |
| 630 |
AND TABLE_NAME = %s", |
| 631 |
$tbl |
| 632 |
) |
| 633 |
); |
| 634 |
if ( 0 === $exists ) { |
| 635 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 636 |
$wpdb->query( |
| 637 |
"CREATE TABLE IF NOT EXISTS `{$tbl}` ( |
| 638 |
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 639 |
share_id BIGINT UNSIGNED NOT NULL, |
| 640 |
user_id BIGINT UNSIGNED NOT NULL, |
| 641 |
state VARCHAR(16) NOT NULL DEFAULT 'pending', |
| 642 |
decided_at_ms BIGINT UNSIGNED NOT NULL, |
| 643 |
PRIMARY KEY (id), |
| 644 |
UNIQUE KEY uniq_share_user (share_id, user_id), |
| 645 |
KEY by_user (user_id, state) |
| 646 |
) $charset_collate" |
| 647 |
); |
| 648 |
} |
| 649 |
} |
| 650 |
|
| 651 |
/** |
| 652 |
* Lazy migrator — runs on `admin_init` when the stored schema |
| 653 |
* version doesn't match the constant. Idempotent: `dbDelta` |
| 654 |
* itself is a no-op when the table already matches. |
| 655 |
* |
| 656 |
* @since 0.9.0 |
| 657 |
*/ |
| 658 |
function desktop_mode_files_maybe_install_schema() { |
| 659 |
$installed = get_option( DESKTOP_MODE_FILES_SCHEMA_OPTION, '' ); |
| 660 |
if ( $installed === DESKTOP_MODE_FILES_SCHEMA_VERSION ) { |
| 661 |
return; |
| 662 |
} |
| 663 |
desktop_mode_files_install_schema(); |
| 664 |
} |
| 665 |
add_action( 'admin_init', 'desktop_mode_files_maybe_install_schema' ); |
| 666 |
// REST + front-end requests never fire `admin_init` — without these |
| 667 |
// hooks a session that hits a REST endpoint before any admin page |
| 668 |
// load would query the placements / folders tables before the v2 |
| 669 |
// trash columns exist, throwing wpdb errors and blanking the desktop. |
| 670 |
add_action( 'rest_api_init', 'desktop_mode_files_maybe_install_schema' ); |
| 671 |
add_action( 'init', 'desktop_mode_files_maybe_install_schema', 1 ); |
| 672 |
register_activation_hook( DESKTOP_MODE_FILE, 'desktop_mode_files_install_schema' ); |
| 673 |
|
| 674 |
/** |
| 675 |
* Current epoch-ms timestamp. Centralized so the store and the |
| 676 |
* tombstone writer stay in lock-step. |
| 677 |
* |
| 678 |
* @since 0.9.0 |
| 679 |
* |
| 680 |
* @return int |
| 681 |
*/ |
| 682 |
function desktop_mode_files_now_ms() { |
| 683 |
return (int) round( microtime( true ) * 1000 ); |
| 684 |
} |
| 685 |
|
