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

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

680 lines 25.7 KB

Raw Download Zip

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