'string', 'visitors' => 'int', 'pageviews' => 'int', ); /** * Holds all the default values for the columns. * * @var array * * @since 5.1.0 */ protected static $fields_values = array( 'date' => '', 'visitors' => 0, 'pageviews' => 0, ); /** * Creates table functionality. * * @param \wpdb $connection - \wpdb connection to be used for name extraction. * * @return bool * * @since 5.1.0 */ public static function create_table( $connection = null ): bool { if ( null !== $connection ) { if ( $connection instanceof \wpdb ) { $collate = $connection->get_charset_collate(); } } else { $collate = self::get_connection()->get_charset_collate(); } $table_name = self::get_table_name( $connection ); if ( ! is_string( $table_name ) || ! preg_match( '/^[a-zA-Z0-9_]+$/', $table_name ) ) { return false; } $wp_entity_sql = ' CREATE TABLE `' . $table_name . '` ( date DATE NOT NULL, visitors INT UNSIGNED NOT NULL DEFAULT 0, pageviews INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (date) ) ENGINE=InnoDB ' . $collate . ';'; return self::maybe_create_table( $table_name, $wp_entity_sql, $connection ); } /** * Upsert site stats for a given date. * * Uses INSERT ... ON DUPLICATE KEY UPDATE for atomic increment. * * @param string $date Date in Y-m-d format. * @param int $visitors Number of new visitors to add. * @param int $pageviews Number of new pageviews to add. * * @return void * * @since 5.1.0 */ public static function upsert( string $date, int $visitors, int $pageviews ): void { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared self::query_run( $_wpdb->prepare( "INSERT INTO `{$table}` (date, visitors, pageviews) VALUES (%s, %d, %d) ON DUPLICATE KEY UPDATE visitors = visitors + VALUES(visitors), pageviews = pageviews + VALUES(pageviews)", $date, $visitors, $pageviews ) ); } /** * Get stats for a date range. * * @param string $start_date Start date (Y-m-d). * @param string $end_date End date (Y-m-d). * * @return array * * @since 5.1.0 */ public static function get_stats( string $start_date, string $end_date ): array { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $sql = $_wpdb->prepare( "SELECT date, visitors, pageviews FROM `{$table}` WHERE date BETWEEN %s AND %s ORDER BY date ASC", $start_date, $end_date ); $_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 ? $results : array(); } /** * Get total stats for a date range. * * @param string $start_date Start date (Y-m-d). * @param string $end_date End date (Y-m-d). * * @return array{visitors: int, pageviews: int} * * @since 5.1.0 */ public static function get_totals( string $start_date, string $end_date ): array { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); $defaults = array( 'visitors' => 0, 'pageviews' => 0, ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $sql = $_wpdb->prepare( "SELECT COALESCE(SUM(visitors), 0) AS visitors, COALESCE(SUM(pageviews), 0) AS pageviews FROM `{$table}` WHERE date BETWEEN %s AND %s", $start_date, $end_date ); $_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 ) ); return $defaults; } } } $_wpdb->suppress_errors( false ); return $row ? array( 'visitors' => (int) $row['visitors'], 'pageviews' => (int) $row['pageviews'], ) : $defaults; } } }