code-snippets / php / class-db.php

class-db.php in Code Snippets 4.0.0-beta.1, at php/class-db.php

375 lines 10.1 KB

Raw Download Zip

1	<?php
2
3	namespace Code_Snippets;
4
5	/**
6	* Functions used to manage the database tables.
7	*
8	* @package Code_Snippets
9	*/
10	class DB {
11
12	/**
13	* Unprefixed site-wide table name.
14	*/
15	public const TABLE_NAME = 'snippets';
16
17	/**
18	* Unprefixed network-wide table name.
19	*/
20	public const MS_TABLE_NAME = 'ms_snippets';
21
22	/**
23	* Side-wide table name.
24	*
25	* @var string
26	*/
27	public string $table;
28
29	/**
30	* Network-wide table name.
31	*
32	* @var string
33	*/
34	public string $ms_table;
35
36	/**
37	* Class constructor.
38	*/
39	public function __construct() {
40	$this->set_table_vars();
41	}
42
43	/**
44	* Register the snippet table names with WordPress.
45	*
46	* @since 2.0
47	*/
48	public function set_table_vars() {
49	global $wpdb;
50
51	$this->table = $wpdb->prefix . self::TABLE_NAME;
52	$this->ms_table = $wpdb->base_prefix . self::MS_TABLE_NAME;
53
54	// Register the snippet table names with WordPress.
55	$wpdb->snippets = $this->table;
56	$wpdb->ms_snippets = $this->ms_table;
57
58	$wpdb->tables[] = self::TABLE_NAME;
59	$wpdb->ms_global_tables[] = self::MS_TABLE_NAME;
60	}
61
62	/**
63	* Validate a provided 'network' or 'multisite' param, converting it to a boolean.
64	*
65	* @param bool\|null $network Network argument value.
66	*
67	* @return bool Sanitized value.
68	*/
69	public static function validate_network_param( ?bool $network = null ): bool {
70
71	// If multisite is not active, then assume the value is false.
72	if ( ! is_multisite() ) {
73	return false;
74	}
75
76	// If $multisite is null, try to base it on the current admin page.
77	if ( is_null( $network ) && function_exists( 'is_network_admin' ) ) {
78	return is_network_admin();
79	}
80
81	return (bool) $network;
82	}
83
84	/**
85	* Return the appropriate snippet table name
86	*
87	* @param bool\|null $is_network Whether retrieve the multisite table name (true) or the site table name (false).
88	*
89	* @return string The snippet table name
90	* @since 2.0
91	*/
92	public function get_table_name( ?bool $is_network = null ): string {
93	$is_network = is_bool( $is_network ) ? $is_network : self::validate_network_param( $is_network );
94	return $is_network ? $this->ms_table : $this->table;
95	}
96
97	/**
98	* Determine whether a database table exists.
99	*
100	* @param string $table_name Name of database table to check.
101	* @param boolean $refresh Rerun the query, instead of using a cached value.
102	*
103	* @return bool Whether the database table exists.
104	*/
105	public static function table_exists( string $table_name, bool $refresh = false ): bool {
106	global $wpdb;
107	static $checked = array();
108
109	if ( $refresh \|\| ! isset( $checked[ $table_name ] ) ) {
110	// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching, caching is handled through $checked variable.
111	$result = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table_name ) ) );
112	$checked[ $table_name ] = $result === $table_name;
113	}
114
115	return $checked[ $table_name ];
116	}
117
118	/**
119	* Create the snippet tables if they do not already exist
120	*/
121	public function create_missing_tables() {
122
123	// Create the network snippets table if it doesn't exist.
124	if ( is_multisite() && ! self::table_exists( $this->ms_table ) ) {
125	$this->create_table( $this->ms_table );
126	}
127
128	// Create the table if it doesn't exist.
129	if ( ! self::table_exists( $this->table ) ) {
130	$this->create_table( $this->table );
131	}
132	}
133
134	/**
135	* Create the snippet tables, or upgrade them if they already exist
136	*/
137	public function create_or_upgrade_tables() {
138	if ( is_multisite() ) {
139	$this->create_table( $this->ms_table );
140	}
141
142	$this->create_table( $this->table );
143	}
144
145	/**
146	* Create a snippet table if it does not already exist
147	*
148	* @param string $table_name Name of database table.
149	*/
150	public static function create_missing_table( string $table_name ) {
151	if ( ! self::table_exists( $table_name ) ) {
152	self::create_table( $table_name );
153	}
154	}
155
156	/**
157	* Create a single snippet table.
158	*
159	* @param string $table_name The name of the table to create.
160	*
161	* @return bool Whether the table creation was successful.
162	* @since 1.6
163	* @uses dbDelta() to apply the SQL code
164	*/
165	public static function create_table( string $table_name ): bool {
166	global $wpdb;
167	$charset_collate = $wpdb->get_charset_collate();
168
169	/* Create the database table */
170	$sql = "CREATE TABLE $table_name (
171	id BIGINT(20) NOT NULL AUTO_INCREMENT,
172	name TINYTEXT NOT NULL,
173	description TEXT NOT NULL,
174	code LONGTEXT NOT NULL,
175	tags LONGTEXT NOT NULL,
176	scope VARCHAR(15) NOT NULL DEFAULT 'global',
177	condition_id BIGINT(20) NOT NULL DEFAULT 0,
178	priority SMALLINT NOT NULL DEFAULT 10,
179	active TINYINT(1) NOT NULL DEFAULT 0,
180	modified DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
181	revision BIGINT(20) NOT NULL DEFAULT 1,
182	cloud_id VARCHAR(255) NULL,
183	PRIMARY KEY (id),
184	KEY scope (scope),
185	KEY active (active)
186	) $charset_collate;";
187
188	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
189	dbDelta( $sql );
190
191	$success = empty( $wpdb->last_error );
192
193	if ( $success ) {
194	do_action( 'code_snippets/create_table', $table_name );
195	}
196
197	return $success;
198	}
199
200	/**
201	* Generate the SQL for fetching active snippets from the database.
202	*
203	* @param string[] $scopes List of scopes to retrieve in.
204	*
205	* @return array{
206	* id: int,
207	* code: string,
208	* scope: string,
209	* table: string,
210	* network: bool,
211	* priority: int,
212	* } List of active snippets.
213	*/
214	public function fetch_active_snippets( array $scopes ): array {
215	$active_snippets = [];
216
217	// Fetch the active snippets for the current site, if there are any.
218	$snippets = $this->fetch_snippets_from_table( $this->table, $scopes, true );
219	if ( $snippets ) {
220	foreach ( $snippets as $snippet ) {
221	$active_snippets[] = [
222	'id' => intval( $snippet['id'] ),
223	'code' => $snippet['code'],
224	'scope' => $snippet['scope'],
225	'table' => $this->table,
226	'network' => false,
227	'priority' => intval( $snippet['priority'] ),
228	];
229	}
230	}
231
232	// If multisite is enabled, fetch all snippets from the network table, and filter down to only active snippets.
233	if ( is_multisite() ) {
234	$ms_snippets = $this->fetch_snippets_from_table( $this->ms_table, $scopes, false );
235
236	if ( $ms_snippets ) {
237	$active_shared_ids = get_option( 'active_shared_network_snippets', [] );
238	$active_shared_ids = is_array( $active_shared_ids )
239	? array_map( 'intval', $active_shared_ids )
240	: [];
241
242	foreach ( $ms_snippets as $snippet ) {
243	$id = intval( $snippet['id'] );
244	$active_value = intval( $snippet['active'] );
245
246	if ( ! self::is_network_snippet_enabled( $active_value, $id, $active_shared_ids ) ) {
247	continue;
248	}
249
250	$active_snippets[] = [
251	'id' => $id,
252	'code' => $snippet['code'],
253	'scope' => $snippet['scope'],
254	'table' => $this->ms_table,
255	'network' => true,
256	'priority' => intval( $snippet['priority'] ),
257	];
258	}
259
260	$this->sort_active_snippets( $active_snippets );
261	}
262	}
263
264	return $active_snippets;
265	}
266
267	/**
268	* Determine whether a network snippet should execute on the current site.
269	*
270	* Network snippets execute when active=1, or when the snippet is listed as active-shared for the site.
271	* Trashed snippets (active=-1) should never execute.
272	*
273	* @param int $active_value Raw active value: 1=active, 0=inactive, -1=trashed (can be stored as a string in the database).
274	* @param int $snippet_id Snippet ID.
275	* @param int[] $active_shared_ids Active shared network snippet IDs for the current site.
276	*
277	* @return bool
278	*/
279	public static function is_network_snippet_enabled( int $active_value, int $snippet_id, array $active_shared_ids ): bool {
280	if ( -1 === $active_value ) {
281	return false;
282	}
283
284	if ( 1 === $active_value ) {
285	return true;
286	}
287
288	return in_array( $snippet_id, $active_shared_ids, true );
289	}
290
291	/**
292	* Sort the active snippets by priority, table, and ID.
293	*
294	* @param array $active_snippets List of active snippets to sort.
295	*/
296	private function sort_active_snippets( array &$active_snippets ): void {
297	$comparisons = [
298	function ( array $a, array $b ) {
299	return $a['priority'] <=> $b['priority'];
300	},
301	function ( array $a, array $b ) {
302	$a_table = $a['table'] === $this->ms_table ? 0 : 1;
303	$b_table = $b['table'] === $this->ms_table ? 0 : 1;
304	return $a_table <=> $b_table;
305	},
306	function ( array $a, array $b ) {
307	return $a['id'] <=> $b['id'];
308	},
309	];
310
311	usort(
312	$active_snippets,
313	static function ( $a, $b ) use ( $comparisons ) {
314	foreach ( $comparisons as $comparison ) {
315	$result = $comparison( $a, $b );
316	if ( 0 !== $result ) {
317	return $result;
318	}
319	}
320
321	return 0;
322	}
323	);
324	}
325
326	/**
327	* Fetch a list of active snippets from a database table.
328	*
329	* @param string $table_name Name of table to fetch snippets from.
330	* @param array<string> $scopes List of scopes to include in query.
331	* @param boolean $active_only Whether to only fetch active snippets from the table.
332	*
333	* @return array<string, array<string, mixed>>\|false List of active snippets, if any could be retrieved.
334	*
335	* @phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
336	*/
337	private static function fetch_snippets_from_table( string $table_name, array $scopes, bool $active_only = true ) {
338	global $wpdb;
339
340	$cache_key = sprintf( 'active_snippets_%s_%s', sanitize_key( join( '_', $scopes ) ), $table_name );
341	$cached_snippets = wp_cache_get( $cache_key, CACHE_GROUP );
342
343	if ( is_array( $cached_snippets ) ) {
344	return $cached_snippets;
345	}
346
347	if ( ! self::table_exists( $table_name ) ) {
348	return false;
349	}
350
351	$scopes_format = implode( ',', array_fill( 0, count( $scopes ), '%s' ) );
352	$extra_where = $active_only ? 'AND active=1' : '';
353
354	$snippets = $wpdb->get_results(
355	$wpdb->prepare(
356	"
357	SELECT id, code, scope, active, priority
358	FROM $table_name
359	WHERE scope IN ($scopes_format) $extra_where
360	ORDER BY priority, id",
361	$scopes
362	),
363	'ARRAY_A'
364	);
365
366	// Cache the full list of snippets.
367	if ( is_array( $snippets ) ) {
368	wp_cache_set( $cache_key, $snippets, CACHE_GROUP );
369	return $snippets;
370	}
371
372	return false;
373	}
374	}
375