base_prefix . static::$table; } } return static::get_connection()->base_prefix . static::$table; } /** * Returns the current connection (used by the plugin) * * @return \wpdb * * @since 2.4.2.1 */ public static function get_connection() { if ( null === self::$connection ) { global $wpdb; self::$connection = $wpdb; } return self::$connection; } /** * Sets connection to the given value. * * @param \wpdb $connection - The connection to set to. * * @return void * * @since 2.4.2.1 */ public static function set_connection( $connection ) { self::$connection = $connection; } /** * As this is static class, we need to destroy the connection sometimes. * * @return void * * @since 2.4.2.1 */ public static function destroy_connection() { self::$connection = null; } /** * Validates SQL identifiers like table and column names (letters, numbers, underscore). * * @param string $identifier Identifier to validate. * @return bool */ private static function is_valid_identifier( string $identifier ): bool { return (bool) \preg_match( '/^[A-Za-z0-9_]+$/', $identifier ); } /** * Backtick-quote an identifier and escape embedded backticks. * * @param string $identifier Identifier to quote. * @return string */ private static function bt( string $identifier ): string { return '`' . \str_replace( '`', '``', $identifier ) . '`'; } /** * Log and hook when a table is auto-recreated after a missing-table error. * * @param string $table_name Fully-qualified table name. * @return void */ protected static function log_table_recreated( string $table_name ): void { /** * Fires when an ADVAN entity table is auto-recreated after being missing. * * @param string $table_name Table name that was recreated. */ \do_action( 'advan_entity_table_recreated', $table_name ); // if ( \function_exists( 'error_log' ) ) { // \error_log( '[advanced-analytics] Recreated missing table: ' . $table_name ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log // } } /** * Checks if the table needs to be recreated / created * * @param string $table_name - The name of the table to check for. * @param string $create_ddl - The create table syntax. * @param \wpdb $connection - \wpdb connection to be used for name extraction. * * @return bool * * @since 2.4.2.1 */ public static function maybe_create_table( string $table_name, string $create_ddl, $connection = null ): bool { if ( null !== $connection ) { if ( $connection instanceof \wpdb ) { $_wpdb = $connection; } } else { $_wpdb = static::get_connection(); } if ( Common_Table::check_table_exists( $table_name, $_wpdb ) ) { // Table exists, so we don't need to create it. return true; } // Didn't find it, so try to create it. $_wpdb->query( $create_ddl ); // We cannot directly tell that whether this succeeded! if ( Common_Table::check_table_exists( $table_name, $_wpdb ) ) { // Table exists, so we don't need to create it. return true; } return false; } /** * Checks if the given index exists in the table * * @param string $index - The index to check for (text). * * @return boolean * * @since 2.4.2.1 */ public static function check_index_exists( string $index ): bool { $index = \sanitize_text_field( $index ); if ( '' === $index || ! self::is_valid_identifier( $index ) ) { return false; } return self::index_exists( self::get_table_name(), $index ); } /** * Checks and returns last mysql error * * @param \wpdb $_wpdb - The Mysql resource class. * * @return integer * * @since 2.4.2.1 */ public static function get_last_sql_error( $_wpdb ): int { $code = 0; if ( $_wpdb->dbh instanceof \mysqli ) { $code = \mysqli_errno( $_wpdb->dbh ); // phpcs:ignore WordPress.DB.RestrictedFunctions.mysql_mysqli_errno } if ( is_resource( $_wpdb->dbh ) ) { // Please do not report this code as a PHP 7 incompatibility. Observe the surrounding logic. $code = mysql_errno( $_wpdb->dbh ); // phpcs:ignore WordPress.DB.RestrictedFunctions.mysql_mysql_errno } if ( is_a( $_wpdb, '\WP_SQLite_DB' ) ) { // This driver does not provide error codes and it is impossible to tell what is going on here. // So we will just return 1146 (MYSQL error code for table not found and retry give query but first recreate the table). // Maybe can try search for "SQLSTATE[HY000]: General error: 1 no such table" but that is a long shot if someone decides to translate this. $code = 1146; } return (int) $code; } /** * Checks if a table exists for a given connection (if no connection is provided - current connection will be used.) * * @param \wpdb $connection - \wpdb connection to be used for name extraction. * * @return boolean * * @since 2.4.2.1 */ public static function is_installed( $connection = null ): bool { return Common_Table::check_table_exists( self::get_table_name( $connection ), $connection ); } /** * Returns records in the table based on condition * * @param string $cond - The condition. * @param array $args - The arguments (values). * @param \wpdb $connection - \wpdb connection to be used for name extraction. * * @return int * * @since 2.4.2.1 */ public static function count( $cond = '%d', $args = array( 1 ), $connection = null ) { if ( null !== $connection ) { if ( $connection instanceof \wpdb ) { $_wpdb = $connection; } } else { $_wpdb = static::get_connection(); } $sql = $_wpdb->prepare( 'SELECT COUNT(*) FROM ' . self::get_table_name( $_wpdb ) . ' WHERE ' . $cond, $args ); $_wpdb->suppress_errors( true ); $count = (int) $_wpdb->get_var( $sql ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { self::log_table_recreated( self::get_table_name( $_wpdb ) ); $count = 0; } } } $_wpdb->suppress_errors( false ); return $count; } /** * Saves the given data into the table * The data should be in following format: * field name => value * * It checks the given data array against the table fields and determines the types based on that, it stores the values in the table then. * * @param array $data - The data to be saved (check above about the format). * * @return int * * @since 2.4.2.1 */ public static function insert( $data ) { $format = array(); $insert_data = array(); foreach ( $data as $key => $val ) { if ( isset( ( static::class )::$fields[ $key ] ) ) { $insert_data[ $key ] = $val; $format[ $key ] = '%s'; if ( 'int' === ( static::class )::$fields[ $key ] ) { $format[ $key ] = '%d'; } if ( 'float' === ( static::class )::$fields[ $key ] ) { $format[ $key ] = '%f'; } } } if ( ! empty( $format ) ) { $_wpdb = static::get_connection(); $_wpdb->suppress_errors( true ); $_wpdb->replace( self::get_table_name( $_wpdb ), $insert_data, $format ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { self::log_table_recreated( self::get_table_name( $_wpdb ) ); $_wpdb->replace( self::get_table_name( $_wpdb ), $data, $format ); } } } $_wpdb->suppress_errors( false ); return $_wpdb->insert_id; } return 0; // no record is inserted. } /** * Prepares the data array and the format array based on the existing table fields * * @param array $data - The data to make preparation from. * * @return array * * @since 2.4.2.1 */ public static function prepare_data( array $data ): array { $format = array(); $insert_data = array(); foreach ( $data as $key => $val ) { if ( isset( ( static::class )::$fields[ $key ] ) ) { $insert_data[ $key ] = $val; $format[ $key ] = '%s'; if ( 'int' === ( static::class )::$fields[ $key ] ) { $format[ $key ] = '%d'; } if ( 'float' === ( static::class )::$fields[ $key ] ) { $format[ $key ] = '%f'; } } } return array( $insert_data, $format ); } /** * Load record from DB based on full query (Single row). * * @param string $query - The full SQL query without the FROM part. * @param \wpdb $connection - \wpdb connection to be used for name extraction. * * @return array * * @since 4.9.0 */ public static function get_row( string $query, $connection = null ) { if ( null !== $connection ) { if ( $connection instanceof \wpdb ) { $_wpdb = $connection; } } else { $_wpdb = static::get_connection(); } $query .= ' FROM `' . self::get_table_name( $_wpdb ) . '`'; $_wpdb->suppress_errors( true ); $row = $_wpdb->get_row( $query, ARRAY_A ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { self::log_table_recreated( self::get_table_name( $_wpdb ) ); $row = $_wpdb->get_row( $query, ARRAY_A ); } } } $_wpdb->suppress_errors( false ); return $row; } /** * Load record from DB (Single row). * * @param string $cond - (Optional) Load condition. * @param array $args - (Optional) Load condition arguments. * @param \wpdb $connection - \wpdb connection to be used for name extraction. * @param string $extra - The extra SQL string (if needed). * * @return array * * @since 5.0.0 */ public static function load( $cond = 'id=%d', $args = array( 1 ), $connection = null, $extra = '' ) { if ( null !== $connection ) { if ( $connection instanceof \wpdb ) { $_wpdb = $connection; } } else { $_wpdb = static::get_connection(); } $sql = $_wpdb->prepare( 'SELECT * FROM ' . self::get_table_name( $_wpdb ) . ' WHERE ' . $cond, $args ); if ( ! empty( trim( $extra ) ) ) { $sql .= $extra; } $_wpdb->suppress_errors( true ); $row = $_wpdb->get_row( $sql, ARRAY_A ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { self::log_table_recreated( self::get_table_name( $_wpdb ) ); $row = $_wpdb->get_row( $sql, ARRAY_A ); } } } $_wpdb->suppress_errors( false ); return $row; } /** * Load records from DB (Multi rows). * * @param string $cond Load condition. * @param array $args (Optional) Load condition arguments. * @param \wpdb $connection - \wpdb connection to be used for name extraction. * @param string $extra - The extra SQL string (if needed). * * @return array * * @since 5.0.0 */ public static function load_array( $cond, $args = array(), $connection = null, $extra = '' ) { if ( null !== $connection ) { if ( $connection instanceof \wpdb ) { $_wpdb = $connection; } } else { $_wpdb = static::get_connection(); } $sql = $_wpdb->prepare( 'SELECT * FROM ' . self::get_table_name( $_wpdb ) . ' WHERE ' . $cond, $args ); if ( ! empty( trim( $extra ) ) ) { $sql .= $extra; } $_wpdb->suppress_errors( true ); $results = $_wpdb->get_results( $sql, ARRAY_A ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { self::log_table_recreated( self::get_table_name( $_wpdb ) ); $results = array(); } } } $_wpdb->suppress_errors( false ); return $results; } /** * Delete records in DB matching a query. * * @param string $query Full SQL query. * @param array $args (Optional) Query arguments. * @param \wpdb $connection - \wpdb connection to be used for name extraction. * * @return int|bool * * @since 2.4.2.1 */ public static function delete_query( $query, $args = array(), $connection = null ) { if ( null !== $connection ) { if ( $connection instanceof \wpdb ) { $_wpdb = $connection; } } else { $_wpdb = static::get_connection(); } $sql = count( $args ) ? $_wpdb->prepare( $query, $args ) : $query; $_wpdb->suppress_errors( true ); $res = $_wpdb->query( $sql ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { self::log_table_recreated( self::get_table_name( $_wpdb ) ); $res = true; } } } $_wpdb->suppress_errors( false ); return $res; } /** * Default delete method * * @param integer $id - The real id of the table. * @param \wpdb $connection - \wpdb connection to be used for name extraction. * * @return int|bool * * @since 2.4.2.1 */ public static function delete_by_id( int $id, $connection = null ) { if ( null !== $connection ) { if ( $connection instanceof \wpdb ) { $_wpdb = $connection; } } else { $_wpdb = static::get_connection(); } $result = $_wpdb->delete( self::get_table_name( $_wpdb ), array( 'id' => $id ), array( '%d' ) ); return $result; } /** * Duplicates table row by its ID * * @param integer $id - The ID of row to duplicate. * @param \wpdb $connection - The connection which has to be used. * * @return mixed * * @since 5.0.0 */ public static function duplicate_by_id( int $id, $connection ) { if ( null !== $connection ) { if ( $connection instanceof \wpdb ) { $_wpdb = $connection; } } else { $_wpdb = static::get_connection(); } $table_name = self::get_table_name( $_wpdb ); // phpcs:ignore Generic.Formatting.MultipleStatementAlignment.NotSameWarning $all_fields = array_keys( self::get_fields() ); // phpcs:ignore Generic.Formatting.MultipleStatementAlignment.NotSameWarning $insert_list = static::get_duplicate_fields( false ); $select_list = static::get_duplicate_fields( true ); $insert_cols = array(); $select_parts = array(); foreach ( $insert_list as $f ) { if ( is_string( $f ) && self::is_valid_identifier( $f ) && in_array( $f, $all_fields, true ) ) { $insert_cols[] = self::bt( $f ); } } foreach ( $select_list as $f ) { if ( is_numeric( $f ) ) { $select_parts[] = (string) $f; continue; } if ( is_string( $f ) && self::is_valid_identifier( $f ) && in_array( $f, $all_fields, true ) ) { $select_parts[] = self::bt( $f ); } } if ( empty( $insert_cols ) || empty( $select_parts ) || count( $insert_cols ) !== count( $select_parts ) ) { return false; } $sql = 'INSERT INTO ' . self::bt( $table_name ) . ' (' . implode( ',', $insert_cols ) . ') SELECT ' . implode( ',', $select_parts ) . ' FROM ' . self::bt( $table_name ) . ' WHERE id = %d'; $_wpdb->suppress_errors( true ); $result = $_wpdb->query( $_wpdb->prepare( $sql, $id ) ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { self::log_table_recreated( self::get_table_name( $_wpdb ) ); $result = $_wpdb->query( $_wpdb->prepare( $sql, $id ) ); } } } $_wpdb->suppress_errors( false ); return $_wpdb->insert_id; } /** * Returns array with fields to duplicate, gets rid of id and created_on columns. * * @param bool $duplicate_values - When called for duplication, gives the class ability to set fields that must have specific values in the database. * * @return array * * @since 5.0.0 */ public static function get_duplicate_fields( bool $duplicate_values ): array { $fields = self::get_fields(); unset( $fields['id'] ); if ( $duplicate_values && isset( $fields['created_on'] ) ) { $fields = \array_keys( $fields ); $time = \microtime( true ); $key = array_search( 'created_on', $fields, true ); $fields[ $key ] = $time; return $fields; } return array_keys( $fields ); } /** * Default find method * * @param array $data - Must contains formats and data. The array should contain: * 'data' - Associative array of all the fields and values to search for. * 'formats' - array of all the formats for the data we are searching for. * * @return array|bool * * @since 2.4.2.1 */ public static function find( array $data ) { /** * \wpdb has very powerful method called process_fields @see \wpdb::process_fields(). * Unfortunately this method is not accessible, because it is marked protected. The best solution at the moment is to clone the class, lower the visibility and use the method. * * That of course takes resources so possible solution is to add also caching to this method, so that is marked as todo below. * * TODO: Add caching functionality to the method. */ // phpcs:disable $wpdb_clone = new class() extends \wpdb { public function __construct() { $dbuser = defined( 'DB_USER' ) ? DB_USER : ''; $dbpassword = defined( 'DB_PASSWORD' ) ? DB_PASSWORD : ''; $dbname = defined( 'DB_NAME' ) ? DB_NAME : ''; $dbhost = defined( 'DB_HOST' ) ? DB_HOST : ''; parent::__construct( $dbuser, $dbpassword, $dbname, $dbhost ); } public function process_fields( $name, $data, $formats ) { return parent::process_fields( $name, $data, $formats ); } }; // phpcs:enable $_wpdb = static::get_connection(); $where_clause = $wpdb_clone->process_fields( self::get_table_name(), $data['data'], $data['formats'] ); $where_data = self::prepare_full_where( $where_clause ); $conditions = $where_data['conditions']; $values = $where_data['values']; $_wpdb->check_current_query = false; $sql = $_wpdb->prepare( 'SELECT * FROM `' . self::get_table_name( $_wpdb ) . '` WHERE ' . $conditions, $values ); $_wpdb->suppress_errors( true ); $result = $_wpdb->get_results( $sql, ARRAY_A ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { $result = array(); } } } $_wpdb->suppress_errors( false ); return $result; } /** * Executes query. * * Important - query string is not checked nor validated, the calling script is responsible for that. * * @param string $query - The query which needs to be executed. * @param \wpdb $connection - \wpdb connection to be used for name extraction. * @param array|null $args - Optional arguments for the query, if provided the query will be prepared with those arguments before execution. * * @return array * * @since 4.9.0s */ public static function query_run( string $query, $connection = null, ?array $args = null ) { if ( null !== $connection ) { if ( $connection instanceof \wpdb ) { $_wpdb = $connection; } else { global $wpdb; $_wpdb = $wpdb; } } else { global $wpdb; $_wpdb = $wpdb; } $_wpdb->suppress_errors( true ); if ( is_array( $args ) && ! empty( $args ) ) { $prepared = $_wpdb->prepare( $query, $args ); $result = $_wpdb->query( $prepared ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { $result = array(); } } } $_wpdb->suppress_errors( false ); return $result; } $result = $_wpdb->query( $query ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { $result = array(); } } } $_wpdb->suppress_errors( false ); return $result; } /** * Prepares full where clause * * @param array $where_clause - Array prepared based on fields and values from the WP_DB. * @param string $condition - The where clause condition - default AND. * @param string|null $criteria - The criteria to check for. * @param boolean|null $left_pref - For any starting value - partial where clause. * @param boolean|null $right_pref - For any ending value - partial where clause. * * @return array * * @since 2.4.2.1 */ public static function prepare_full_where( array $where_clause, string $condition = ' AND ', ?string $criteria = ' = ', ?bool $left_pref = false, ?bool $right_pref = false ): array { $conditions = array(); $values = array(); foreach ( $where_clause as $field => $value ) { if ( is_null( $value['value'] ) ) { $conditions[] = '`' . self::get_table_name() . '` . `' . $field . '` IS null'; continue; } if ( \is_array( $value['value'] ) ) { $cond_string = '(`' . self::get_table_name() . '` . `' . $field . '` '; $cond_string .= ' BETWEEN '; foreach ( $value['value'] as $val ) { $cond_string .= $value['format'] . ' ' . $condition . ' '; $values[] = ( ( $left_pref ) ? ' % ' : '' ) . $val . ( ( $right_pref ) ? ' % ' : '' ); } $cond_string = rtrim( $cond_string, ' ' . $condition . ' ' ) . ')'; $conditions[] = $cond_string; continue; } $conditions[] = '`' . self::get_table_name() . '` . `' . $field . '` ' . $criteria . ' ' . $value['format']; $values[] = ( ( $left_pref ) ? ' % ' : '' ) . $value['value'] . ( ( $right_pref ) ? ' % ' : '' ); } $conditions = implode( ' ' . $condition . ' ', $conditions ); return array( 'conditions' => $conditions, 'values' => $values, ); } /** * Return the table fields and default values * * @return array * * @since 2.4.2.1 */ public static function get_fields_values(): array { return ( static::class )::$fields_values; } /** * Return the table fields * * @return array * * @since 2.4.2.1 */ public static function get_fields(): array { return ( static::class )::$fields; } /** * Creates array with the full filed names (table name included) for the given table * * @param \wpdb $connection - \wpdb connection to be used for name extraction. * * @return array * * @since 2.4.2.1 */ public static function prepare_full_select_statement( $connection = null ): array { $full_fields = array(); foreach ( \array_keys( ( static::class )::$fields ) as $field ) { $full_fields[ self::get_table_name( $connection ) . '.' . $field ] = self::get_table_name( $connection ) . $field; } return $full_fields; } /** * Returns array with the results from the provided query. * * @param string $query - The SQL query to execute. * @param \wpdb $connection - The DB connection object. * * @return array * * @since 3.0.1 */ public static function get_results( string $query, $connection = null ): array { if ( null !== $connection ) { if ( $connection instanceof \wpdb ) { $_wpdb = $connection; } else { global $wpdb; $_wpdb = $wpdb; } } else { global $wpdb; $_wpdb = $wpdb; } $_wpdb->suppress_errors( true ); $results = $_wpdb->get_results( $query, \ARRAY_A ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { $results = array(); } } } $_wpdb->suppress_errors( false ); return $results; } /** * Returns single variable from the provided query. * * @param string $query - The SQL query to execute. * @param \wpdb $connection - The DB connection object. * @param int $x - The X parameter. * @param int $y - The Y parameter. * * @return mixed * * @since 3.0.1 */ public static function get_var( string $query, $connection = null, $x = 0, $y = 0 ) { if ( null !== $connection ) { if ( $connection instanceof \wpdb ) { $_wpdb = $connection; } else { global $wpdb; $_wpdb = $wpdb; } } else { global $wpdb; $_wpdb = $wpdb; } $_wpdb->suppress_errors( true ); $result = $_wpdb->get_var( $query, $x, $y ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { $result = 0; } } } $_wpdb->suppress_errors( false ); return $result; } /** * Returns all column names which belong to the table * * @return array * * @since 3.8.0 */ public static function get_all_columns(): array { return ( static::class )::$fields; } /** * Helper to check if a column exists in the given table. * * @param string $table Table name (already trusted internal value). * @param string $column Column name to check. * @param null|\wpdb $connection Optional connection. * * @return bool * * @since 4.1.1 */ protected static function column_exists( string $table, string $column, $connection = null ): bool { $wpdb = ( $connection instanceof \wpdb ) ? $connection : self::get_connection(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Internal schema introspection; table name is trusted and cannot be parameterized. $results = $wpdb->get_results( $wpdb->prepare( 'SHOW COLUMNS FROM `' . $table . '` LIKE %s', $column ) ); return ! empty( $results ); } /** * Helper to check if an index exists in the given table. * * @param string $table Table name (already trusted internal value). * @param string $index Index name to check. * @param null|\wpdb $connection Optional connection. * * @return bool * * @since 5.2.0 */ protected static function index_exists( string $table, string $index, $connection = null ): bool { $wpdb = ( $connection instanceof \wpdb ) ? $connection : self::get_connection(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Internal schema introspection; table name is trusted and cannot be parameterized. $results = $wpdb->get_results( $wpdb->prepare( 'SHOW INDEX FROM `' . $table . '` WHERE Key_name = %s', $index ), \ARRAY_A ); return ! empty( $results ); } } }