code-snippets / php / Core / DB.php

DB.php in Code Snippets 4.0.0-beta.2, at php/Core/DB.php

346 lines 9.3 KB

Raw Download Zip

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