<?php
// phpcs:ignore Generic.Commenting.DocComment.MissingShort
/** @noinspection PhpIllegalPsrClassPathInspection */
namespace WPForms\Helpers;
// phpcs:ignore WPForms.PHP.UseStatement.UnusedUseStatement
use WPForms_DB;
use WPForms_Lite;
use WPForms_Pro;
/**
* DB helpers.
*
* @since 1.8.7
*/
class DB {
/**
* Existing tables transient name.
*
* @since 1.8.7
* @since 1.9.0 Changed from 'wpforms_existing_tables' to 'existing_tables'
*
* @var string
*/
const EXISTING_TABLES_TRANSIENT_NAME = 'existing_tables';
/**
* Existing tables transient expiration, sec.
*
* @since 1.8.7
*
* @var int
* @noinspection SummerTimeUnsafeTimeManipulationInspection
*/
const EXISTING_TABLES_TRANSIENT_EXPIRATION = WEEK_IN_SECONDS; // A week.
/**
* Cache key prefix for exact-name lookups, so they never mix with the results of a LIKE pattern.
*
* @since 2.0.3
*/
private const EXACT_TABLE_CACHE_PREFIX = 'exact:';
/**
* Existing tables.
*
* @since 1.8.7
*
* @var array
*/
private static $existing_tables = [];
/**
* Get the list of existing tables and cache the result.
*
* @since 1.8.7
*
* @param string $table_name Table name. Can have SQL wildcard.
*
* @return array List of table names.
*/
public static function get_existing_tables( string $table_name ): array {
global $wpdb;
/**
* Filters existence of a table before a request to the database is executed.
*
* @since 1.8.7
*
* @param array $tables Existing tables with given table name.
* @param string $table_name Table name.
*/
$tables = (array) apply_filters( 'wpforms_helpers_db_pre_get_existing_tables', [], $table_name );
if ( $tables ) {
return $tables;
}
$tables = self::get_existing_tables_cache( $table_name );
if ( $tables ) {
return $tables;
}
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$tables = $wpdb->get_results(
$wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ),
'ARRAY_N'
);
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$tables = ! empty( $tables ) ? wp_list_pluck( $tables, 0 ) : [];
self::set_existing_tables_cache( $tables, $table_name );
return self::$existing_tables[ $table_name ] ?? [];
}
/**
* Get the list of all existing custom tables starting with `wpforms_*` and cache the result.
*
* @since 1.8.7
*
* @return array List of table names.
*/
public static function get_existing_custom_tables(): array {
global $wpdb;
return self::get_existing_tables( "{$wpdb->prefix}wpforms_%" );
}
/**
* Check if the database table exists and cache the result.
*
* Only a found table is cached: a missing one is queried again on every call, so avoid
* calling this on paths that run on every request, such as front-end page loads.
*
* @since 1.8.7
* @since 2.0.3 Finds an existing table with an exact-name lookup first.
*
* @param string $table_name Table name. Can have SQL wildcard.
*
* @return bool
*/
public static function table_exists( string $table_name ): bool {
/**
* Filters existence of a table before a request to the database is executed.
*
* @since 1.8.7
*
* @param integer $exists Table exists.
* @param string $table_name Table name.
*/
if ( apply_filters( 'wpforms_helpers_db_pre_table_exists', false, $table_name ) ) {
return true;
}
// A table with exactly this name also matches it as a LIKE pattern, so this changes the cost, not the answer.
if ( self::exact_table_exists( $table_name ) ) {
return true;
}
foreach ( self::get_existing_tables( $table_name ) as $existing_table ) {
if ( self::wildcard_match( $table_name, $existing_table ) ) {
return true;
}
}
return false;
}
/**
* Whether a table with exactly this name exists, cached like the LIKE lookups.
*
* `SHOW TABLES LIKE` compares the pattern against every table in the schema, so it slows down
* with each table, up to minutes on large multisite networks. An equality match on
* `information_schema.TABLES` is an index lookup. A miss returns false, so the caller still runs the LIKE lookup and a `_` in the
* name keeps its wildcard meaning.
*
* @since 2.0.3
*
* @param string $table_name Table name.
*
* @return bool
*/
private static function exact_table_exists( string $table_name ): bool {
global $wpdb;
if ( strpos( $table_name, '%' ) !== false ) {
return false;
}
// A filtered result keeps precedence, and the LIKE lookup is where it is applied.
/** This filter is documented in wpforms/src/Helpers/DB.php. */
if ( (array) apply_filters( 'wpforms_helpers_db_pre_get_existing_tables', [], $table_name ) ) {
return false;
}
// A cached LIKE result that already contains this exact name answers the question.
if ( in_array( $table_name, self::get_existing_tables_cache( $table_name ), true ) ) {
return true;
}
$cache_key = self::EXACT_TABLE_CACHE_PREFIX . $table_name;
if ( self::get_existing_tables_cache( $cache_key ) ) {
return true;
}
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$found = (string) $wpdb->get_var(
$wpdb->prepare(
'SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s LIMIT 1',
$table_name
)
);
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
if ( $found === '' ) {
return false;
}
self::set_existing_tables_cache( [ $found ], $cache_key );
return true;
}
/**
* Get the list of existing tables from cache.
*
* @since 1.8.7
*
* @param string $table_name Table name. Can have SQL wildcard.
*
* @return array List of table names.
*/
private static function get_existing_tables_cache( string $table_name ): array {
$tables = Transient::get( self::EXISTING_TABLES_TRANSIENT_NAME );
self::$existing_tables = $tables ? $tables : [];
return self::$existing_tables[ $table_name ] ?? [];
}
/**
* Set existing tables cache.
*
* @since 1.8.7
*
* @param array $tables Existing tables with given table name.
* @param string $table_name Table name.
*
* @return void
*/
private static function set_existing_tables_cache( array $tables, string $table_name ) {
if ( empty( $tables ) ) {
return;
}
self::$existing_tables[ $table_name ] = $tables;
/**
* Filters existing tables transient expiration time.
*
* @since 1.8.7
*
* @param integer $expiration Expiration time.
*/
$expiration = apply_filters( 'wpforms_helpers_db_existing_tables_transient_expiration', self::EXISTING_TABLES_TRANSIENT_EXPIRATION );
Transient::set( self::EXISTING_TABLES_TRANSIENT_NAME, self::$existing_tables, $expiration );
}
/**
* Flush existing tables cache.
*
* @since 1.9.0
*
* @return void
*/
public static function flush_existing_tables_cache() {
self::$existing_tables = [];
Transient::delete( self::EXISTING_TABLES_TRANSIENT_NAME );
}
/**
* Wildcard match.
* Works as MySQL LIKE match.
*
* @since 1.8.7
*
* @param string $pattern Pattern.
* @param string $subject String to search into.
*
* @return false|int
*/
private static function wildcard_match( string $pattern, string $subject ) {
$regex = str_replace(
[ '%', '_' ], // MySQL wildcard chars.
[ '.*', '.' ], // Regexp chars.
preg_quote( $pattern, '/' )
);
return preg_match( '/^' . $regex . '$/is', $subject );
}
/**
* Check if all custom tables exist.
*
* @since 1.9.0
*
* @return bool True if all custom tables exist. False if any is missing.
*/
public static function custom_tables_exist(): bool {
global $wpdb;
$existing_tables = self::get_existing_custom_tables();
$custom_tables = wpforms()->is_pro() ? WPForms_Pro::CUSTOM_TABLES : WPForms_Lite::CUSTOM_TABLES;
foreach ( $custom_tables as $table_name => $handler_class ) {
if ( ! in_array( $wpdb->prefix . $table_name, $existing_tables, true ) ) {
return false;
}
}
return true;
}
/**
* Create all custom DB tables.
*
* @since 1.9.0
*
* @param bool $flush_cache Clear existing custom tables cache.
*
* @noinspection PhpPossiblePolymorphicInvocationInspection
*/
public static function create_custom_tables( bool $flush_cache = false ) {
global $wpdb;
if ( $flush_cache ) {
self::flush_existing_tables_cache();
}
$existing_tables = self::get_existing_custom_tables();
$custom_tables = wpforms()->is_pro() ? WPForms_Pro::CUSTOM_TABLES : WPForms_Lite::CUSTOM_TABLES;
$created = false;
foreach ( $custom_tables as $table_name => $handler_class ) {
if ( in_array( $wpdb->prefix . $table_name, $existing_tables, true ) ) {
continue;
}
/**
* Child class of WPForms_DB.
*
* @var $handler WPForms_DB
*/
$handler = new $handler_class();
// Create a table.
$handler->create_table();
$created = true;
}
if ( $created ) {
Transient::delete( self::EXISTING_TABLES_TRANSIENT_NAME );
}
}
/**
* Check whether an index exists on a table.
*
* Always queried live: a cached answer goes stale the moment an `ALTER` runs.
*
* @since 2.0.2
*
* @param string $table Prefixed table name.
* @param string $index Index name.
*
* @return bool
*/
public static function index_exists( string $table, string $index ): bool {
global $wpdb;
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
return (bool) $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM {$table} WHERE Key_name = %s", $index ) );
}
/**
* Add an index to a table via online DDL, unless it already exists.
*
* @since 2.0.2
*
* @param string $table Prefixed table name.
* @param string $index Index name.
* @param string $columns Column list, e.g. `( date, form_id )`.
*
* @return bool Whether the ALTER was executed (false when the index already existed).
*/
public static function add_index_if_missing( string $table, string $index, string $columns ): bool {
global $wpdb;
if ( self::index_exists( $table, $index ) ) {
return false;
}
// Suppress errors: a concurrent request may add the same index between check and ALTER.
$suppressed = $wpdb->suppress_errors( true );
// Prefer online DDL (no table lock) where supported.
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$added = $wpdb->query( "ALTER TABLE {$table} ADD INDEX {$index} {$columns}, ALGORITHM=INPLACE, LOCK=NONE" );
if ( $added === false && ! self::index_exists( $table, $index ) ) {
// Fallback for hosts without online-DDL support.
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$wpdb->query( "ALTER TABLE {$table} ADD INDEX {$index} {$columns}" );
}
$wpdb->suppress_errors( $suppressed );
if ( ! self::index_exists( $table, $index ) ) {
wpforms_log(
'Index build failed',
"Table: {$table}, Index: {$index}",
[ 'type' => [ 'error' ] ]
);
return false;
}
return true;
}
}