<?php
namespace Code_Snippets\Core;
use const Code_Snippets\CACHE_GROUP;
/**
* Functions used to manage the database tables.
*
* @package Code_Snippets
*/
class DB {
/**
* Unprefixed site-wide table name.
*/
public const TABLE_NAME = 'snippets';
/**
* Unprefixed network-wide table name.
*/
public const MS_TABLE_NAME = 'ms_snippets';
/**
* Side-wide table name.
*
* @var string
*/
public string $table;
/**
* Network-wide table name.
*
* @var string
*/
public string $ms_table;
/**
* Class constructor.
*/
public function __construct() {
$this->set_table_vars();
}
/**
* Register the snippet table names with WordPress.
*
* @since 2.0
*/
public function set_table_vars() {
global $wpdb;
$this->table = $wpdb->prefix . self::TABLE_NAME;
$this->ms_table = $wpdb->base_prefix . self::MS_TABLE_NAME;
// Register the snippet table names with WordPress.
$wpdb->snippets = $this->table;
$wpdb->ms_snippets = $this->ms_table;
$wpdb->tables[] = self::TABLE_NAME;
$wpdb->ms_global_tables[] = self::MS_TABLE_NAME;
}
/**
* Validate a provided 'network' or 'multisite' param, converting it to a boolean.
*
* @param bool|null $network Network argument value.
*
* @return bool Sanitized value.
*/
public static function validate_network_param( ?bool $network = null ): bool {
// If multisite is not active, then assume the value is false.
if ( ! is_multisite() ) {
return false;
}
// If $multisite is null, try to base it on the current admin page.
if ( is_null( $network ) && function_exists( 'is_network_admin' ) ) {
return is_network_admin();
}
return (bool) $network;
}
/**
* Return the appropriate snippet table name
*
* @param bool|null $is_network Whether retrieve the multisite table name (true) or the site table name (false).
*
* @return string The snippet table name
* @since 2.0
*/
public function get_table_name( ?bool $is_network = null ): string {
$is_network = is_bool( $is_network ) ? $is_network : self::validate_network_param( $is_network );
return $is_network ? $this->ms_table : $this->table;
}
/**
* Determine whether a database table exists.
*
* @param string $table_name Name of database table to check.
* @param bool $refresh Rerun the query, instead of using a cached value.
*
* @return bool Whether the database table exists.
*/
public static function table_exists( string $table_name, bool $refresh = false ): bool {
global $wpdb;
static $checked = array();
if ( $refresh || ! isset( $checked[ $table_name ] ) ) {
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching, caching is handled through $checked variable.
$result = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table_name ) ) );
$checked[ $table_name ] = $result === $table_name;
}
return $checked[ $table_name ];
}
/**
* Create the snippet tables, or upgrade them if they already exist
*/
public function create_or_upgrade_tables() {
if ( is_multisite() ) {
$this->create_table( $this->ms_table );
}
$this->create_table( $this->table );
}
/**
* Create a snippet table if it does not already exist
*
* @param string $table_name Name of database table.
*/
public static function create_missing_table( string $table_name ) {
if ( ! self::table_exists( $table_name ) ) {
self::create_table( $table_name );
}
}
/**
* Create a single snippet table.
*
* @param string $table_name The name of the table to create.
*
* @return bool Whether the table creation was successful.
* @since 1.6
* @uses dbDelta() to apply the SQL code
*/
public static function create_table( string $table_name ): bool {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
/* Create the database table */
$sql = "CREATE TABLE $table_name (
id BIGINT(20) NOT NULL AUTO_INCREMENT,
name TINYTEXT NOT NULL,
description TEXT NOT NULL,
code LONGTEXT NOT NULL,
tags LONGTEXT NOT NULL,
scope VARCHAR(15) NOT NULL DEFAULT 'global',
condition_id BIGINT(20) NOT NULL DEFAULT 0,
priority SMALLINT NOT NULL DEFAULT 10,
active TINYINT(1) NOT NULL DEFAULT 0,
modified DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
revision BIGINT(20) NOT NULL DEFAULT 1,
cloud_id VARCHAR(255) NULL,
PRIMARY KEY (id),
KEY scope (scope),
KEY active (active)
) $charset_collate;";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta( $sql );
$success = empty( $wpdb->last_error );
if ( $success ) {
do_action( 'code_snippets/create_table', $table_name );
}
return $success;
}
/**
* Generate the SQL for fetching active snippets from the database.
*
* @param string[] $scopes List of scopes to retrieve in.
*
* @return array{
* id: int,
* code: string,
* scope: string,
* table: string,
* network: bool,
* priority: int,
* condition_id: int,
* }[] List of active snippets.
*/
public function fetch_active_snippets( array $scopes ): array {
$active_snippets = [];
// Fetch the active snippets for the current site, if there are any.
$snippets = $this->fetch_snippets_from_table( $this->table, $scopes, true );
if ( $snippets ) {
foreach ( $snippets as $snippet ) {
$active_snippets[] = [
'id' => intval( $snippet['id'] ),
'code' => $snippet['code'],
'scope' => $snippet['scope'],
'table' => $this->table,
'network' => false,
'priority' => intval( $snippet['priority'] ),
];
}
}
// If multisite is enabled, fetch all snippets from the network table, and filter down to only active snippets.
if ( is_multisite() ) {
$ms_snippets = $this->fetch_snippets_from_table( $this->ms_table, $scopes, false );
if ( $ms_snippets ) {
$active_shared_ids = get_option( 'active_shared_network_snippets', [] );
$active_shared_ids = is_array( $active_shared_ids ) ? array_map( 'intval', $active_shared_ids ) : [];
foreach ( $ms_snippets as $snippet ) {
$id = intval( $snippet['id'] );
$active_value = intval( $snippet['active'] );
if ( ! self::is_network_snippet_enabled( $active_value, $id, $active_shared_ids ) ) {
continue;
}
$active_snippets[] = [
'id' => $id,
'code' => $snippet['code'],
'scope' => $snippet['scope'],
'table' => $this->ms_table,
'network' => true,
'priority' => intval( $snippet['priority'] ),
];
}
$this->sort_active_snippets( $active_snippets );
}
}
return $active_snippets;
}
/**
* Determine whether a network snippet should execute on the current site.
*
* Network snippets execute when active=1, or when the snippet is listed as active-shared for the site.
* Trashed snippets (active=-1) should never execute.
*
* @param int $active_value Raw active value: 1=active, 0=inactive, -1=trashed (can be stored as a string in the database).
* @param int $snippet_id Snippet ID.
* @param int[] $active_shared_ids Active shared network snippet IDs for the current site.
*
* @return bool
*/
public static function is_network_snippet_enabled( int $active_value, int $snippet_id, array $active_shared_ids ): bool {
if ( -1 === $active_value ) {
return false;
}
if ( 1 === $active_value ) {
return true;
}
return in_array( $snippet_id, $active_shared_ids, true );
}
/**
* Sort the active snippets by priority, table, and ID.
*
* @param array $active_snippets List of active snippets to sort.
*/
private function sort_active_snippets( array &$active_snippets ): void {
$comparisons = [
function ( array $a, array $b ) {
return $a['priority'] <=> $b['priority'];
},
function ( array $a, array $b ) {
$a_table = $a['table'] === $this->ms_table ? 0 : 1;
$b_table = $b['table'] === $this->ms_table ? 0 : 1;
return $a_table <=> $b_table;
},
function ( array $a, array $b ) {
return $a['id'] <=> $b['id'];
},
];
usort(
$active_snippets,
static function ( $a, $b ) use ( $comparisons ) {
foreach ( $comparisons as $comparison ) {
$result = $comparison( $a, $b );
if ( 0 !== $result ) {
return $result;
}
}
return 0;
}
);
}
/**
* Fetch a list of active snippets from a database table.
*
* @param string $table_name Name of table to fetch snippets from.
* @param array<string> $scopes List of scopes to include in query.
* @param bool $active_only Whether to only fetch active snippets from the table.
*
* @return array<string, array<string, mixed>>|false List of active snippets, if any could be retrieved.
*/
private static function fetch_snippets_from_table( string $table_name, array $scopes, bool $active_only ) {
global $wpdb;
$cache_key = sprintf( 'active_snippets_%s_%s', sanitize_key( join( '_', $scopes ) ), $table_name );
$cached_snippets = wp_cache_get( $cache_key, CACHE_GROUP );
if ( is_array( $cached_snippets ) ) {
return $cached_snippets;
}
if ( ! self::table_exists( $table_name ) ) {
return false;
}
$scopes_format = implode( ',', array_fill( 0, count( $scopes ), '%s' ) );
$extra_where = $active_only ? 'AND active=1' : '';
$snippets = $wpdb->get_results(
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
$wpdb->prepare(
"
SELECT id, code, scope, active, priority
FROM $table_name
WHERE scope IN ($scopes_format) $extra_where
ORDER BY priority, id",
$scopes
),
'ARRAY_A'
);
// Cache the full list of snippets.
if ( is_array( $snippets ) ) {
wp_cache_set( $cache_key, $snippets, CACHE_GROUP );
return $snippets;
}
return false;
}
}