desktop-mode / includes / games / schema.php

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

183 lines 5.8 KB

Raw Download Zip

1	<?php
2	/**
3	* Desktop Mode — 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 WPDesktopMode
23	* @since 0.9.6
24	*/
25
26	defined( 'ABSPATH' ) \|\| exit;
27
28	define( 'DESKTOP_MODE_GAMES_SCHEMA_VERSION', '1' );
29	define( 'DESKTOP_MODE_GAMES_SCHEMA_OPTION', 'desktop_mode_games_schema_version' );
30
31	/**
32	* Returns the per-table names with the active prefix applied.
33	*
34	* @since 0.9.6
35	*
36	* @return array{ scores: string, challenges: string }
37	*/
38	function desktop_mode_games_table_names() {
39	global $wpdb;
40	return array(
41	'scores' => $wpdb->prefix . 'desktop_mode_game_scores',
42	'challenges' => $wpdb->prefix . 'desktop_mode_game_challenges',
43	);
44	}
45
46	/**
47	* Idempotent `dbDelta` call. Hooked on plugin activation and lazily
48	* on `admin_init` / `rest_api_init` / `init` (gated by a
49	* version-option mismatch) so a manual file-copy install still ends
50	* up with the tables.
51	*
52	* @since 0.9.6
53	*/
54	function desktop_mode_games_install_schema() {
55	global $wpdb;
56
57	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
58
59	$tables = desktop_mode_games_table_names();
60	$charset_collate = $wpdb->get_charset_collate();
61
62	$scores_sql = "CREATE TABLE {$tables['scores']} (
63	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
64	game VARCHAR(64) NOT NULL,
65	user_id BIGINT UNSIGNED NOT NULL,
66	score BIGINT NOT NULL DEFAULT 0,
67	meta LONGTEXT NULL,
68	created_at_ms BIGINT UNSIGNED NOT NULL DEFAULT 0,
69	PRIMARY KEY (id),
70	KEY game_score (game, score),
71	KEY game_user (game, user_id),
72	KEY created_at_ms (created_at_ms)
73	) $charset_collate;";
74
75	$challenges_sql = "CREATE TABLE {$tables['challenges']} (
76	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
77	game VARCHAR(64) NOT NULL,
78	challenger_id BIGINT UNSIGNED NOT NULL,
79	recipient_id BIGINT UNSIGNED NOT NULL,
80	score_to_beat BIGINT NOT NULL DEFAULT 0,
81	score_meta LONGTEXT NULL,
82	state VARCHAR(16) NOT NULL DEFAULT 'pending',
83	result VARCHAR(16) NULL,
84	result_score BIGINT NULL,
85	result_meta LONGTEXT NULL,
86	created_at_ms BIGINT UNSIGNED NOT NULL DEFAULT 0,
87	decided_at_ms BIGINT UNSIGNED NULL,
88	completed_at_ms BIGINT UNSIGNED NULL,
89	updated_at_ms BIGINT UNSIGNED NOT NULL DEFAULT 0,
90	PRIMARY KEY (id),
91	KEY recipient_state (recipient_id, state),
92	KEY challenger_state (challenger_id, state),
93	KEY updated_at_ms (updated_at_ms)
94	) $charset_collate;";
95
96	dbDelta( $scores_sql );
97	dbDelta( $challenges_sql );
98
99	// Belt-and-suspenders: dbDelta's DESCRIBE-based table detection has
100	// documented failure modes (see the files schema for the full
101	// rationale) — verify both tables physically exist and CREATE them
102	// explicitly when not.
103	desktop_mode_games_ensure_table( $tables['scores'], $scores_sql );
104	desktop_mode_games_ensure_table( $tables['challenges'], $challenges_sql );
105
106	update_option( DESKTOP_MODE_GAMES_SCHEMA_OPTION, DESKTOP_MODE_GAMES_SCHEMA_VERSION );
107
108	/**
109	* Fires after the games schema is installed / migrated.
110	*
111	* @since 0.9.6
112	*
113	* @param string $version The version that was installed.
114	*/
115	do_action( 'desktop_mode_games_schema_installed', DESKTOP_MODE_GAMES_SCHEMA_VERSION );
116	}
117
118	/**
119	* Create a table via `CREATE TABLE IF NOT EXISTS` when
120	* INFORMATION_SCHEMA says dbDelta left it missing. Errors are
121	* suppressed around the CREATE so a concurrent worker that won the
122	* same race doesn't log a benign "already exists".
123	*
124	* @since 0.9.6
125	* @internal
126	*
127	* @param string $table Fully-prefixed table name.
128	* @param string $sql The dbDelta CREATE TABLE statement for it.
129	*/
130	function desktop_mode_games_ensure_table( $table, $sql ) {
131	global $wpdb;
132
133	$exists = (int) $wpdb->get_var(
134	$wpdb->prepare(
135	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
136	WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s",
137	$table
138	)
139	);
140	if ( 0 < $exists ) {
141	return;
142	}
143	$create = str_replace( 'CREATE TABLE ', 'CREATE TABLE IF NOT EXISTS ', $sql );
144	$prev_suppress = $wpdb->suppress_errors( true );
145	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
146	$wpdb->query( $create );
147	$wpdb->suppress_errors( $prev_suppress );
148	}
149
150	/**
151	* Lazy migrator — runs when the stored schema version doesn't match
152	* the constant. Idempotent: `dbDelta` itself is a no-op when the
153	* tables already match.
154	*
155	* @since 0.9.6
156	*/
157	function desktop_mode_games_maybe_install_schema() {
158	$installed = get_option( DESKTOP_MODE_GAMES_SCHEMA_OPTION, '' );
159	if ( $installed === DESKTOP_MODE_GAMES_SCHEMA_VERSION ) {
160	return;
161	}
162	desktop_mode_games_install_schema();
163	}
164	add_action( 'admin_init', 'desktop_mode_games_maybe_install_schema' );
165	// REST + Heartbeat requests never fire `admin_init` — without these a
166	// session hitting the scores endpoint before any admin page load
167	// would query missing tables.
168	add_action( 'rest_api_init', 'desktop_mode_games_maybe_install_schema' );
169	add_action( 'init', 'desktop_mode_games_maybe_install_schema', 1 );
170	register_activation_hook( DESKTOP_MODE_FILE, 'desktop_mode_games_install_schema' );
171
172	/**
173	* Current epoch-ms timestamp. Centralized so the store and the
174	* Heartbeat channel stay in lock-step.
175	*
176	* @since 0.9.6
177	*
178	* @return int
179	*/
180	function desktop_mode_games_now_ms() {
181	return (int) round( microtime( true ) * 1000 );
182	}
183