code-snippets / php / Core / DB.php

DB.php in Code Snippets 3.10.2, at php/Core/DB.php

359 lines 9.7 KB

Raw Download Zip

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