desktop-mode / includes / desktop-files / schema.php

schema.php in OpenStation: Desktop Windows, Dock & Virtual Desktops for WP Admin 0.8.9, at includes/desktop-files/schema.php

646 lines 23.6 KB

Raw Download Zip

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', '12' );
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	owner_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 owner_parent (owner_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	// v11: rename `placements.user_id` to `placements.owner_id` so
149	// the placements + folders tables use the same column name for
150	// the same concept. Must run BEFORE dbDelta — once the
151	// `$placements_sql` definition switches from `user_id` to
152	// `owner_id`, dbDelta on an existing v≤10 install would see
153	// `owner_id` as a missing column and ADD it (leaving the old
154	// `user_id` in place + the new `owner_id` NULL). Running the
155	// CHANGE COLUMN first means dbDelta sees the table already
156	// matches the desired shape.
157	desktop_mode_files_rename_user_id_to_owner_id();
158
159	dbDelta( $placements_sql );
160	dbDelta( $folders_sql );
161	dbDelta( $tombstones_sql );
162
163	// dbDelta has well-documented quirks with `NULL`-only columns
164	// (no DEFAULT) — under some MySQL/MariaDB combos it silently
165	// skips the ADD COLUMN. Verify the v2 trash columns are
166	// physically present and ALTER them in directly when not.
167	desktop_mode_files_ensure_trash_columns();
168
169	// v4: clean up duplicate placements created by sessions that
170	// hit the auto-orphan-placer while the v2 trash columns were
171	// missing — every `WHERE trashed_at_ms IS NULL` precheck
172	// returned empty, so each pageload re-inserted every
173	// registered shortcut. Collapse runs of identical
174	// `(owner_id, parent_id, file_type, file_ref)` rows down to
175	// the lowest id.
176	desktop_mode_files_dedupe_placements();
177
178	// v5: enforce uniqueness at the DB level so a future bug
179	// (or a racing pair of REST requests) can never re-create
180	// the duplicate shortcuts again. Must run AFTER dedupe —
181	// adding a unique key against duplicate rows would fail.
182	desktop_mode_files_ensure_unique_placement_index();
183
184	// v9: belt-and-suspenders existence check for the shares +
185	// decisions tables. The folder-sharing feature is the
186	// canonical source of truth for "who can see this folder" —
187	// the `share_meta` JSON column on the folders table remains
188	// for diagnostic purposes only and is not consulted by the
189	// visibility resolver.
190	desktop_mode_files_ensure_shares_table();
191	desktop_mode_files_ensure_decisions_table();
192
193	// v10: `updated_by` column on placements so the If-Match 409
194	// conflict toast names the SESSION that actually won the race,
195	// not just whoever currently owns the row. Critical for the
196	// shared-write scenario where User B (writer recipient) moves a
197	// placement and User C gets the conflict — without this column,
198	// the toast would blame User A (owner of the row).
199	desktop_mode_files_ensure_updated_by_column();
200
201	update_option( DESKTOP_MODE_FILES_SCHEMA_OPTION, DESKTOP_MODE_FILES_SCHEMA_VERSION );
202
203	/**
204	* Fires after the files schema is installed / migrated.
205	*
206	* @since 0.9.0
207	*
208	* @param string $version The version that was installed.
209	*/
210	do_action( 'desktop_mode_files_schema_installed', DESKTOP_MODE_FILES_SCHEMA_VERSION );
211	}
212
213	/**
214	* Belt-and-suspenders verifier for the v2 trash columns. Reads
215	* `INFORMATION_SCHEMA.COLUMNS` for the placements + folders tables
216	* and `ALTER`s in any column dbDelta missed. Idempotent: each
217	* `ALTER` only fires when the column is not already there.
218	*
219	* @since 0.8.0
220	* @internal
221	*/
222	function desktop_mode_files_ensure_trash_columns() {
223	global $wpdb;
224	$tables = desktop_mode_files_table_names();
225
226	// Two-worker race protection: between the INFORMATION_SCHEMA
227	// check and the ALTER, a concurrent worker (cron + admin-init,
228	// REST + heartbeat) can run the same check, see the column
229	// missing, and both fire ALTER. The second hits MySQL error
230	// 1060 ("Duplicate column"). Suppressing wpdb errors around
231	// the ALTER swallows that benign log line. The column ends up
232	// present either way — we re-verify with a second
233	// INFORMATION_SCHEMA query and only surface an error when the
234	// column is genuinely missing after the attempt.
235	$ensure = static function ( $table, $column, $definition ) use ( $wpdb ) {
236	$col_exists = static function () use ( $wpdb, $table, $column ) {
237	return (int) $wpdb->get_var(
238	$wpdb->prepare(
239	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
240	WHERE TABLE_SCHEMA = DATABASE()
241	AND TABLE_NAME = %s
242	AND COLUMN_NAME = %s",
243	$table,
244	$column
245	)
246	);
247	};
248	if ( $col_exists() > 0 ) {
249	return;
250	}
251	$prev_suppress = $wpdb->suppress_errors( true );
252	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
253	$wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}" );
254	$wpdb->suppress_errors( $prev_suppress );
255	// Belt-and-suspenders: if the column STILL isn't there
256	// after the ALTER (real schema error, not a race), retry
257	// once unsuppressed so WP_DEBUG users see the cause.
258	if ( $col_exists() === 0 ) {
259	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
260	$wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}" );
261	}
262	};
263
264	$ensure( $tables['placements'], 'trashed_at_ms', 'BIGINT UNSIGNED NULL' );
265	$ensure( $tables['placements'], 'trashed_by', 'BIGINT UNSIGNED NULL' );
266	$ensure( $tables['placements'], 'trashed_via_folder', 'BIGINT UNSIGNED NULL' );
267	// v6: ancestry snapshot — JSON capturing every folder in the
268	// parent chain at trash time so a restore can resurrect the
269	// chain even when a folder was hard-deleted in the meantime.
270	$ensure( $tables['placements'], 'trashed_meta', 'LONGTEXT NULL' );
271	$ensure( $tables['folders'], 'trashed_at_ms', 'BIGINT UNSIGNED NULL' );
272	$ensure( $tables['folders'], 'trashed_by', 'BIGINT UNSIGNED NULL' );
273	$ensure( $tables['folders'], 'trashed_meta', 'LONGTEXT NULL' );
274	}
275
276	/**
277	* Collapse duplicate `(owner_id, parent_id, file_type, file_ref)`
278	* placement rows down to the lowest id, deleting the rest. Only
279	* meaningful for `file_type IN ('shortcut','folder')` — those are
280	* the types where two rows for the same ref are redundant. Other
281	* types (post / page / attachment / …) might legitimately appear
282	* twice on the same desktop, so the dedupe leaves them alone.
283	*
284	* @since 0.8.0
285	* @internal
286	*/
287	function desktop_mode_files_dedupe_placements() {
288	global $wpdb;
289	$tables = desktop_mode_files_table_names();
290	$tbl = $tables['placements'];
291
292	// Once the unique index exists, MySQL prevents duplicate
293	// inserts at the DB level — dedupe is a no-op and the
294	// table-scanning DELETE is pure waste on every install_schema
295	// call. Skip in that case so the cost is paid exactly once,
296	// during the v4 → v5 migration.
297	$has_unique = (int) $wpdb->get_var(
298	$wpdb->prepare(
299	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
300	WHERE TABLE_SCHEMA = DATABASE()
301	AND TABLE_NAME = %s
302	AND INDEX_NAME = %s",
303	$tbl,
304	'placement_unique'
305	)
306	);
307	if ( $has_unique > 0 ) {
308	return;
309	}
310
311	// Self-join keeps the minimum id per (user_id, parent_id,
312	// file_type, file_ref) and deletes everything else. Restricted
313	// to shortcut + folder placements, where duplicates are never
314	// intentional.
315	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
316	$wpdb->query(
317	"DELETE p1 FROM `{$tbl}` p1
318	INNER JOIN `{$tbl}` p2
319	ON p1.owner_id = p2.owner_id
320	AND p1.parent_id = p2.parent_id
321	AND p1.file_type = p2.file_type
322	AND p1.file_ref = p2.file_ref
323	AND p1.id > p2.id
324	WHERE p1.file_type IN ( 'shortcut', 'folder' )"
325	);
326	}
327
328	/**
329	* Add a UNIQUE index on
330	* `(owner_id, parent_id, file_type, file_ref)` to make duplicate
331	* placements physically impossible. Skipped when the index is
332	* already present.
333	*
334	* Note: `file_ref` is `VARCHAR(255)` — combined with the three
335	* other columns this fits comfortably under MySQL's 3072-byte
336	* InnoDB index-key limit on `utf8mb4`.
337	*
338	* @since 0.8.0
339	* @internal
340	*/
341	function desktop_mode_files_ensure_unique_placement_index() {
342	global $wpdb;
343	$tables = desktop_mode_files_table_names();
344	$tbl = $tables['placements'];
345
346	$exists = (int) $wpdb->get_var(
347	$wpdb->prepare(
348	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
349	WHERE TABLE_SCHEMA = DATABASE()
350	AND TABLE_NAME = %s
351	AND INDEX_NAME = %s",
352	$tbl,
353	'placement_unique'
354	)
355	);
356	if ( 0 === $exists ) {
357	// Suppress errors on the ADD KEY in case a concurrent
358	// worker won the same race (MySQL 1061: "Duplicate key
359	// name"). The index ends up present either way; the
360	// check-then-add pattern is benign under contention.
361	$prev_suppress = $wpdb->suppress_errors( true );
362	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
363	$wpdb->query(
364	"ALTER TABLE `{$tbl}`
365	ADD UNIQUE KEY `placement_unique`
366	(owner_id, parent_id, file_type, file_ref)"
367	);
368	$wpdb->suppress_errors( $prev_suppress );
369	}
370	}
371
372	/**
373	* Add the v10 `updated_by` column to the placements table.
374	*
375	* Tracks which user last mutated the row (created, moved, restored).
376	* Used by `desktop_mode_files_check_if_match()` so the If-Match 409
377	* conflict toast attributes the change to the SESSION that won the
378	* race rather than to the row's static owner — critical when a
379	* writer recipient of a shared folder rearranges placements and
380	* another viewer hits a stale `If-Match`.
381	*
382	* NULL on legacy rows (pre-v10). The conflict resolver falls back
383	* to `owner_id` when this column is NULL, matching the old behavior.
384	*
385	* @since 0.18.x (schema v10)
386	* @internal
387	*/
388	function desktop_mode_files_ensure_updated_by_column() {
389	global $wpdb;
390	$tables = desktop_mode_files_table_names();
391	$tbl = $tables['placements'];
392	$exists = (int) $wpdb->get_var(
393	$wpdb->prepare(
394	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
395	WHERE TABLE_SCHEMA = DATABASE()
396	AND TABLE_NAME = %s
397	AND COLUMN_NAME = %s",
398	$tbl,
399	'updated_by'
400	)
401	);
402	if ( 0 === $exists ) {
403	// Suppress errors so a concurrent worker that already won
404	// the same race doesn't fire a benign MySQL 1060
405	// ("Duplicate column"). The column ends up present either
406	// way.
407	$prev_suppress = $wpdb->suppress_errors( true );
408	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
409	$wpdb->query( "ALTER TABLE `{$tbl}` ADD COLUMN `updated_by` BIGINT UNSIGNED NULL AFTER `owner_id`" );
410	$wpdb->suppress_errors( $prev_suppress );
411	}
412	}
413
414	/**
415	* Rename `placements.user_id` to `placements.owner_id` and the
416	* matching `user_parent` index to `owner_parent`. The two
417	* desktop-mode-owned tables historically used different names for
418	* the same "row's owner" concept — folders carried `owner_id` from
419	* day one, placements carried `user_id`. v11 unifies them so SQL
420	* and PHP read identically across the two tables.
421	*
422	* Idempotent: skips when the table doesn't exist (fresh install —
423	* dbDelta runs after this and creates the table with the new
424	* column name directly) and when the column is already renamed.
425	*
426	* Must run BEFORE `dbDelta( $placements_sql )` in
427	* `desktop_mode_files_install_schema()` — dbDelta does NOT rename
428	* columns, so against a v≤10 table whose definition says
429	* `owner_id` it would ADD a new `owner_id` column and leave the
430	* stale `user_id` in place. Running the CHANGE COLUMN first puts
431	* the table in the desired shape so dbDelta sees no diff.
432	*
433	* Schema version was bumped from 11 to 12 so any install that
434	* stamped 11 but never actually renamed the column (a silent
435	* partial run during dev iteration) gets a clean retry. The
436	* function is idempotent — early-returns when `user_id` is absent,
437	* so healthy v11 installs see a cheap no-op on the retry.
438	*
439	* @since 0.22.0 (schema v11, redelivered at v12)
440	* @internal
441	*/
442	function desktop_mode_files_rename_user_id_to_owner_id() {
443	global $wpdb;
444	$tables = desktop_mode_files_table_names();
445	$tbl = $tables['placements'];
446
447	// Fresh install — the table doesn't exist yet; dbDelta creates
448	// it with `owner_id` directly. Nothing to migrate.
449	$table_exists = (int) $wpdb->get_var(
450	$wpdb->prepare(
451	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
452	WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s",
453	$tbl
454	)
455	);
456	if ( 0 === $table_exists ) {
457	return;
458	}
459
460	$has_user_id = (int) $wpdb->get_var(
461	$wpdb->prepare(
462	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
463	WHERE TABLE_SCHEMA = DATABASE()
464	AND TABLE_NAME = %s
465	AND COLUMN_NAME = 'user_id'",
466	$tbl
467	)
468	);
469	if ( 0 === $has_user_id ) {
470	return; // Already renamed (or column was never there — fresh install via test factory).
471	}
472
473	// DELIBERATELY NOT suppressing errors here. An earlier draft
474	// wrapped the ALTER in `suppress_errors( true )` "in case of
475	// concurrent migration race" — there's no realistic race for
476	// this ALTER (schema migrations run inside one request) and the
477	// suppression hid a real failure mode on at least one local
478	// install: the option got stamped at v11 but the CHANGE COLUMN
479	// never landed, so every later placements query 500'd silently.
480	// Let any wpdb error surface to `debug.log` so the failure is
481	// visible the next time this code runs.
482	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
483	$wpdb->query(
484	"ALTER TABLE `{$tbl}` CHANGE COLUMN `user_id` `owner_id` BIGINT UNSIGNED NOT NULL"
485	);
486
487	// MySQL/MariaDB auto-update index column references on CHANGE
488	// COLUMN, but the index NAMES are baked in. Rename them too so
489	// `EXPLAIN`/`SHOW INDEX` output reads consistently with the
490	// column.
491	$has_user_parent = (int) $wpdb->get_var(
492	$wpdb->prepare(
493	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
494	WHERE TABLE_SCHEMA = DATABASE()
495	AND TABLE_NAME = %s
496	AND INDEX_NAME = 'user_parent'",
497	$tbl
498	)
499	);
500	if ( 0 < $has_user_parent ) {
501	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
502	$wpdb->query( "ALTER TABLE `{$tbl}` RENAME INDEX `user_parent` TO `owner_parent`" );
503	}
504	}
505
506	/**
507	* Belt-and-suspenders verifier for the v8 `shares` table. `dbDelta`
508	* has known edge cases where a brand-new table with `UNIQUE KEY`
509	* declarations on a non-`utf8mb4` collation gets silently skipped
510	* on some MySQL/MariaDB combos; we mirror the trash-columns
511	* pattern and `CREATE TABLE IF NOT EXISTS` the row explicitly.
512	*
513	* @since 0.18.0
514	* @internal
515	*/
516	function desktop_mode_files_ensure_shares_table() {
517	global $wpdb;
518	$tables = desktop_mode_files_table_names();
519	$charset_collate = $wpdb->get_charset_collate();
520	$tbl = $tables['shares'];
521
522	$exists = (int) $wpdb->get_var(
523	$wpdb->prepare(
524	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
525	WHERE TABLE_SCHEMA = DATABASE()
526	AND TABLE_NAME = %s",
527	$tbl
528	)
529	);
530	if ( 0 === $exists ) {
531	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
532	$wpdb->query(
533	"CREATE TABLE IF NOT EXISTS `{$tbl}` (
534	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
535	target_type VARCHAR(32) NOT NULL DEFAULT 'folder',
536	folder_id BIGINT UNSIGNED NOT NULL,
537	principal_type VARCHAR(16) NOT NULL,
538	principal_ref VARCHAR(191) NOT NULL,
539	capability VARCHAR(8) NOT NULL DEFAULT 'read',
540	state VARCHAR(16) NOT NULL DEFAULT 'pending',
541	invited_by BIGINT UNSIGNED NOT NULL,
542	invited_at_ms BIGINT UNSIGNED NOT NULL,
543	decided_at_ms BIGINT UNSIGNED NULL,
544	PRIMARY KEY (id),
545	UNIQUE KEY uniq_principal (target_type, folder_id, principal_type, principal_ref),
546	KEY by_principal (principal_type, principal_ref, state),
547	KEY target (target_type, folder_id)
548	) $charset_collate"
549	);
550	} else {
551	// Existing table — make sure `target_type` is there for
552	// installs that ran a pre-target_type build of v8.
553	$has_col = (int) $wpdb->get_var(
554	$wpdb->prepare(
555	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
556	WHERE TABLE_SCHEMA = DATABASE()
557	AND TABLE_NAME = %s
558	AND COLUMN_NAME = %s",
559	$tbl,
560	'target_type'
561	)
562	);
563	if ( 0 === $has_col ) {
564	// Same TOCTOU rationale as the other ensure_* helpers
565	// — concurrent worker that already added the column
566	// surfaces a benign MySQL 1060 we should swallow.
567	$prev_suppress = $wpdb->suppress_errors( true );
568	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
569	$wpdb->query( "ALTER TABLE `{$tbl}` ADD COLUMN `target_type` VARCHAR(32) NOT NULL DEFAULT 'folder' AFTER `id`" );
570	$wpdb->suppress_errors( $prev_suppress );
571	}
572	}
573	}
574
575	/**
576	* Belt-and-suspenders verifier for the decisions table.
577	*
578	* @since 0.18.0
579	* @internal
580	*/
581	function desktop_mode_files_ensure_decisions_table() {
582	global $wpdb;
583	$tables = desktop_mode_files_table_names();
584	$charset_collate = $wpdb->get_charset_collate();
585	$tbl = $tables['decisions'];
586
587	$exists = (int) $wpdb->get_var(
588	$wpdb->prepare(
589	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
590	WHERE TABLE_SCHEMA = DATABASE()
591	AND TABLE_NAME = %s",
592	$tbl
593	)
594	);
595	if ( 0 === $exists ) {
596	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
597	$wpdb->query(
598	"CREATE TABLE IF NOT EXISTS `{$tbl}` (
599	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
600	share_id BIGINT UNSIGNED NOT NULL,
601	user_id BIGINT UNSIGNED NOT NULL,
602	state VARCHAR(16) NOT NULL DEFAULT 'pending',
603	decided_at_ms BIGINT UNSIGNED NOT NULL,
604	PRIMARY KEY (id),
605	UNIQUE KEY uniq_share_user (share_id, user_id),
606	KEY by_user (user_id, state)
607	) $charset_collate"
608	);
609	}
610	}
611
612	/**
613	* Lazy migrator — runs on `admin_init` when the stored schema
614	* version doesn't match the constant. Idempotent: `dbDelta`
615	* itself is a no-op when the table already matches.
616	*
617	* @since 0.9.0
618	*/
619	function desktop_mode_files_maybe_install_schema() {
620	$installed = get_option( DESKTOP_MODE_FILES_SCHEMA_OPTION, '' );
621	if ( $installed === DESKTOP_MODE_FILES_SCHEMA_VERSION ) {
622	return;
623	}
624	desktop_mode_files_install_schema();
625	}
626	add_action( 'admin_init', 'desktop_mode_files_maybe_install_schema' );
627	// REST + front-end requests never fire `admin_init` — without these
628	// hooks a session that hits a REST endpoint before any admin page
629	// load would query the placements / folders tables before the v2
630	// trash columns exist, throwing wpdb errors and blanking the desktop.
631	add_action( 'rest_api_init', 'desktop_mode_files_maybe_install_schema' );
632	add_action( 'init', 'desktop_mode_files_maybe_install_schema', 1 );
633	register_activation_hook( DESKTOP_MODE_FILE, 'desktop_mode_files_install_schema' );
634
635	/**
636	* Current epoch-ms timestamp. Centralized so the store and the
637	* tombstone writer stay in lock-step.
638	*
639	* @since 0.9.0
640	*
641	* @return int
642	*/
643	function desktop_mode_files_now_ms() {
644	return (int) round( microtime( true ) * 1000 );
645	}
646