code-snippets / php / class-db.php

class-db.php in Code Snippets 3.9.1, at php/class-db.php

350 lines 9.2 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	* Fetch a list of active snippets from a database table.
202	*
203	* @param string $table_name Name of table to fetch snippets from.
204	* @param array<string> $scopes List of scopes to include in query.
205	* @param boolean $active_only Whether to only fetch active snippets from the table.
206	*
207	* @return array<string, array<string, mixed>>\|false List of active snippets, if any could be retrieved.
208	*
209	* @phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
210	*/
211	private static function fetch_snippets_from_table( string $table_name, array $scopes, bool $active_only = true ) {
212	global $wpdb;
213
214	$cache_key = sprintf( 'active_snippets_%s_%s', sanitize_key( join( '_', $scopes ) ), $table_name );
215	$cached_snippets = wp_cache_get( $cache_key, CACHE_GROUP );
216
217	if ( is_array( $cached_snippets ) ) {
218	return $cached_snippets;
219	}
220
221	if ( ! self::table_exists( $table_name ) ) {
222	return false;
223	}
224
225	$scopes_format = implode( ',', array_fill( 0, count( $scopes ), '%s' ) );
226	$extra_where = $active_only ? 'AND active=1' : '';
227
228	$snippets = $wpdb->get_results(
229	$wpdb->prepare(
230	"
231	SELECT id, code, scope, active, priority
232	FROM $table_name
233	WHERE scope IN ($scopes_format) $extra_where
234	ORDER BY priority, id",
235	$scopes
236	),
237	'ARRAY_A'
238	);
239
240	// Cache the full list of snippets.
241	if ( is_array( $snippets ) ) {
242	wp_cache_set( $cache_key, $snippets, CACHE_GROUP );
243	return $snippets;
244	}
245
246	return false;
247	}
248
249	/**
250	* Sort the active snippets by priority, table, and ID.
251	*
252	* @param array $active_snippets List of active snippets to sort.
253	*/
254	private function sort_active_snippets( array &$active_snippets ): void {
255	$comparisons = [
256	function ( array $a, array $b ) {
257	return $a['priority'] <=> $b['priority'];
258	},
259	function ( array $a, array $b ) {
260	$a_table = $a['table'] === $this->ms_table ? 0 : 1;
261	$b_table = $b['table'] === $this->ms_table ? 0 : 1;
262	return $a_table <=> $b_table;
263	},
264	function ( array $a, array $b ) {
265	return $a['id'] <=> $b['id'];
266	},
267	];
268
269	usort(
270	$active_snippets,
271	static function ( $a, $b ) use ( $comparisons ) {
272	foreach ( $comparisons as $comparison ) {
273	$result = $comparison( $a, $b );
274	if ( 0 !== $result ) {
275	return $result;
276	}
277	}
278
279	return 0;
280	}
281	);
282	}
283
284	/**
285	* Generate the SQL for fetching active snippets from the database.
286	*
287	* @param string[] $scopes List of scopes to retrieve in.
288	*
289	* @return array{
290	* id: int,
291	* code: string,
292	* scope: string,
293	* table: string,
294	* network: bool,
295	* priority: int,
296	* } List of active snippets.
297	*/
298	public function fetch_active_snippets( array $scopes ): array {
299	$active_snippets = [];
300
301	// Fetch the active snippets for the current site, if there are any.
302	$snippets = $this->fetch_snippets_from_table( $this->table, $scopes, true );
303	if ( $snippets ) {
304	foreach ( $snippets as $snippet ) {
305	$active_snippets[] = [
306	'id' => intval( $snippet['id'] ),
307	'code' => $snippet['code'],
308	'scope' => $snippet['scope'],
309	'table' => $this->table,
310	'network' => false,
311	'priority' => intval( $snippet['priority'] ),
312	];
313	}
314	}
315
316	// If multisite is enabled, fetch all snippets from the network table, and filter down to only active snippets.
317	if ( is_multisite() ) {
318	$ms_snippets = $this->fetch_snippets_from_table( $this->ms_table, $scopes, false );
319
320	if ( $ms_snippets ) {
321	$active_shared_ids = get_option( 'active_shared_network_snippets', [] );
322	$active_shared_ids = is_array( $active_shared_ids )
323	? array_map( 'intval', $active_shared_ids )
324	: [];
325
326	foreach ( $ms_snippets as $snippet ) {
327	$id = intval( $snippet['id'] );
328
329	if ( ! $snippet['active'] && ! in_array( $id, $active_shared_ids, true ) ) {
330	continue;
331	}
332
333	$active_snippets[] = [
334	'id' => $id,
335	'code' => $snippet['code'],
336	'scope' => $snippet['scope'],
337	'table' => $this->ms_table,
338	'network' => true,
339	'priority' => intval( $snippet['priority'] ),
340	];
341	}
342
343	$this->sort_active_snippets( $active_snippets );
344	}
345	}
346
347	return $active_snippets;
348	}
349	}
350