desktop-mode / includes / games / schema.php

schema.php in OpenStation: Desktop Windows, Dock & Virtual Desktops for WP Admin 1.1.9, at includes/games/schema.php

184 lines 6.3 KB

Raw Download Zip

1	<?php
2	/**
3	* OpenStation — Games schema.
4	*
5	* Two custom tables back the game system:
6	*
7	* - `_desktop_mode_game_scores` — one row per finished game run.
8	* `score` is the sort key for the leaderboard; the flexible
9	* per-game fields (words typed, accuracy, level, …) live in the
10	* `meta` JSON column, mirroring how file placements store their
11	* flexible payload. Indexed on `(game, score)` for the
12	* leaderboard query and `(game, user_id)` for per-player views.
13	*
14	* - `_desktop_mode_game_challenges` — one row per score-to-beat
15	* challenge between two users. `updated_at_ms` is the Heartbeat
16	* high-water mark: clients send the highest value they've seen
17	* and the server returns only rows that moved past it.
18	*
19	* `state` columns are VARCHAR rather than ENUM, matching the
20	* folder-shares table (house style — dbDelta and ENUM don't mix).
21	*
22	* @package OpenStation
23	*/
24
25	defined( 'ABSPATH' ) \|\| exit;
26
27	define( 'OPENSTATION_GAMES_SCHEMA_VERSION', '1' );
28	/**
29	* The VALUE keeps its pre-rebrand spelling on purpose: it is a
30	* persisted or externally-visible identifier, so renaming it would
31	* orphan data already written by live installs (or break a live
32	* URL). The mismatch between this constant's name and its value is
33	* deliberate — it is NOT a half-finished rename.
34	*/
35	define( 'OPENSTATION_GAMES_SCHEMA_OPTION', 'desktop_mode_games_schema_version' );
36
37	/**
38	* Returns the per-table names with the active prefix applied.
39	*
40	* The `desktop_mode_` segment is the pre-rebrand spelling and is frozen:
41	* these are real tables holding real rows on live installs. Renaming
42	* them silently creates a second, empty set and every score and
43	* challenge disappears. The mismatch against the `openstation_*`
44	* function name is deliberate.
45	*
46	* @return array{ scores: string, challenges: string }
47	*/
48	function openstation_games_table_names() {
49	global $wpdb;
50	return array(
51	'scores' => $wpdb->prefix . 'desktop_mode_game_scores',
52	'challenges' => $wpdb->prefix . 'desktop_mode_game_challenges',
53	);
54	}
55
56	/**
57	* Idempotent `dbDelta` call. Hooked on plugin activation and lazily
58	* on `admin_init` / `rest_api_init` / `init` (gated by a
59	* version-option mismatch) so a manual file-copy install still ends
60	* up with the tables.
61	*/
62	function openstation_games_install_schema() {
63	global $wpdb;
64
65	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
66
67	$tables = openstation_games_table_names();
68	$charset_collate = $wpdb->get_charset_collate();
69
70	$scores_sql = "CREATE TABLE {$tables['scores']} (
71	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
72	game VARCHAR(64) NOT NULL,
73	user_id BIGINT UNSIGNED NOT NULL,
74	score BIGINT NOT NULL DEFAULT 0,
75	meta LONGTEXT NULL,
76	created_at_ms BIGINT UNSIGNED NOT NULL DEFAULT 0,
77	PRIMARY KEY (id),
78	KEY game_score (game, score),
79	KEY game_user (game, user_id),
80	KEY created_at_ms (created_at_ms)
81	) $charset_collate;";
82
83	$challenges_sql = "CREATE TABLE {$tables['challenges']} (
84	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
85	game VARCHAR(64) NOT NULL,
86	challenger_id BIGINT UNSIGNED NOT NULL,
87	recipient_id BIGINT UNSIGNED NOT NULL,
88	score_to_beat BIGINT NOT NULL DEFAULT 0,
89	score_meta LONGTEXT NULL,
90	state VARCHAR(16) NOT NULL DEFAULT 'pending',
91	result VARCHAR(16) NULL,
92	result_score BIGINT NULL,
93	result_meta LONGTEXT NULL,
94	created_at_ms BIGINT UNSIGNED NOT NULL DEFAULT 0,
95	decided_at_ms BIGINT UNSIGNED NULL,
96	completed_at_ms BIGINT UNSIGNED NULL,
97	updated_at_ms BIGINT UNSIGNED NOT NULL DEFAULT 0,
98	PRIMARY KEY (id),
99	KEY recipient_state (recipient_id, state),
100	KEY challenger_state (challenger_id, state),
101	KEY updated_at_ms (updated_at_ms)
102	) $charset_collate;";
103
104	dbDelta( $scores_sql );
105	dbDelta( $challenges_sql );
106
107	// Belt-and-suspenders: dbDelta's DESCRIBE-based table detection has
108	// documented failure modes (see the files schema for the full
109	// rationale) — verify both tables physically exist and CREATE them
110	// explicitly when not.
111	openstation_games_ensure_table( $tables['scores'], $scores_sql );
112	openstation_games_ensure_table( $tables['challenges'], $challenges_sql );
113
114	update_option( OPENSTATION_GAMES_SCHEMA_OPTION, OPENSTATION_GAMES_SCHEMA_VERSION );
115
116	/**
117	* Fires after the games schema is installed / migrated.
118	*
119	* @param string $version The version that was installed.
120	*/
121	do_action( 'openstation_games_schema_installed', OPENSTATION_GAMES_SCHEMA_VERSION );
122	}
123
124	/**
125	* Create a table via `CREATE TABLE IF NOT EXISTS` when
126	* INFORMATION_SCHEMA says dbDelta left it missing. Errors are
127	* suppressed around the CREATE so a concurrent worker that won the
128	* same race doesn't log a benign "already exists".
129	*
130	* @internal
131	*
132	* @param string $table Fully-prefixed table name.
133	* @param string $sql The dbDelta CREATE TABLE statement for it.
134	*/
135	function openstation_games_ensure_table( $table, $sql ) {
136	global $wpdb;
137
138	$exists = (int) $wpdb->get_var(
139	$wpdb->prepare(
140	'SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
141	WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s',
142	$table
143	)
144	);
145	if ( 0 < $exists ) {
146	return;
147	}
148	$create = str_replace( 'CREATE TABLE ', 'CREATE TABLE IF NOT EXISTS ', $sql );
149	$prev_suppress = $wpdb->suppress_errors( true );
150	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
151	$wpdb->query( $create );
152	$wpdb->suppress_errors( $prev_suppress );
153	}
154
155	/**
156	* Lazy migrator — runs when the stored schema version doesn't match
157	* the constant. Idempotent: `dbDelta` itself is a no-op when the
158	* tables already match.
159	*/
160	function openstation_games_maybe_install_schema() {
161	$installed = get_option( OPENSTATION_GAMES_SCHEMA_OPTION, '' );
162	if ( OPENSTATION_GAMES_SCHEMA_VERSION === $installed ) {
163	return;
164	}
165	openstation_games_install_schema();
166	}
167	add_action( 'admin_init', 'openstation_games_maybe_install_schema' );
168	// REST + Heartbeat requests never fire `admin_init` — without these a
169	// session hitting the scores endpoint before any admin page load
170	// would query missing tables.
171	add_action( 'rest_api_init', 'openstation_games_maybe_install_schema' );
172	add_action( 'init', 'openstation_games_maybe_install_schema', 1 );
173	register_activation_hook( OPENSTATION_FILE, 'openstation_games_install_schema' );
174
175	/**
176	* Current epoch-ms timestamp. Centralized so the store and the
177	* Heartbeat channel stay in lock-step.
178	*
179	* @return int
180	*/
181	function openstation_games_now_ms() {
182	return (int) round( microtime( true ) * 1000 );
183	}
184