'int', 'url' => 'string', 'url_hash' => 'string', 'scheme' => 'string', 'host' => 'string', 'path' => 'string', 'query_string' => 'string', 'query_params' => 'string', // JSON. 'page_type' => 'string', 'page_id' => 'int', 'visit_count' => 'int', 'date_added' => 'float', 'assets_data' => 'string', // JSON containing JS, CSS, and media files with sizes and URLs. 'assets_collected_at' => 'float', 'pagespeed_data' => 'string', // JSON containing PageSpeed Insights data. 'pagespeed_collected_at' => 'float', ); /** * Holds all the default values for the columns. * * @var array * * @since 4.9.0 */ protected static $fields_values = array( 'id' => 0, 'url' => '', 'url_hash' => '', 'scheme' => 'https', 'host' => '', 'path' => '/', 'query_string' => '', 'query_params' => '{}', 'page_type' => 'other', 'page_id' => 0, 'visit_count' => 0, 'date_added' => '', 'assets_data' => null, 'assets_collected_at' => null, 'pagespeed_data' => null, 'pagespeed_collected_at' => null, ); /** * Creates table functionality. * * @param \wpdb $connection - \wpdb connection to be used for name extraction. * * @since 4.9.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 ); // Defensive validation of table name to avoid unexpected identifier injection if filters/hooks alter it. if ( ! is_string( $table_name ) || ! preg_match( '/^[a-zA-Z0-9_]+$/', $table_name ) ) { return false; } $wp_entity_sql = ' CREATE TABLE `' . $table_name . '` ( id BIGINT unsigned not null auto_increment, url TEXT(2048) NOT NULL, url_hash VARCHAR(32) NOT NULL, scheme VARCHAR(10) NOT NULL DEFAULT "https", host VARCHAR(255) NOT NULL, path TEXT(1024) NOT NULL, query_string TEXT(1024), query_params LONGTEXT, page_type VARCHAR(50) NOT NULL DEFAULT "other", page_id BIGINT unsigned NOT NULL DEFAULT 0, visit_count INT unsigned NOT NULL DEFAULT 0, date_added DOUBLE NOT NULL DEFAULT 0, assets_data LONGTEXT, assets_collected_at DOUBLE, pagespeed_data LONGTEXT, pagespeed_collected_at DOUBLE, PRIMARY KEY (id), UNIQUE KEY `url_hash` (`url_hash`), KEY `visit_count` (`visit_count`), KEY `date_added` (`date_added`), KEY `host` (`host`), KEY `page_type` (`page_type`), KEY `page_id` (`page_id`), KEY `assets_collected_at` (`assets_collected_at`), KEY `pagespeed_collected_at` (`pagespeed_collected_at`) ) ' . $collate . ';'; return self::maybe_create_table( $table_name, $wp_entity_sql, $connection ); } /** * Logs the URL to the database using SQL increment for performance. * * @param array $url_data Parsed URL data. * @return void * * @since 4.9.0 */ public static function log_url( array $url_data ) { global $wpdb; $url = $url_data['url']; // Try to increment existing record using SQL. // This is much faster than SELECT then UPDATE. // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared $updated = self::query_run( $wpdb->prepare( 'UPDATE `' . self::get_table_name() . '` SET visit_count = visit_count + 1 WHERE url_hash = %s LIMIT 1', md5( $url ) ) ); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // If no rows were updated, this is a new URL. if ( 0 === $updated || false === $updated ) { $insert_data = array( 'url' => $url, 'url_hash' => md5( $url ), 'scheme' => $url_data['scheme'], 'host' => $url_data['host'], 'path' => $url_data['path'], 'query_string' => $url_data['query_string'], 'query_params' => $url_data['query_params'], 'page_type' => $url_data['page_type'], 'page_id' => $url_data['page_id'], 'visit_count' => 1, 'date_added' => microtime( true ), ); self::insert( $insert_data ); } /** * Fires after a URL has been logged. * * @param array $url_data The URL data that was logged. * * @since 4.9.0 */ \do_action( 'advan_url_tracked', $url_data ); } /** * Gets all records with optional filters. * * @param array $args Arguments for filtering. * * @return array * * @since 4.9.0 */ public static function get_all( $args = array() ) { global $wpdb; $defaults = array( 'orderby' => 'id', 'order' => 'DESC', 'limit' => 20, 'offset' => 0, 'search' => '', 'date_from' => '', 'date_to' => '', 'page_type' => '', 'host' => '', ); $args = \wp_parse_args( $args, $defaults ); $table = self::get_table_name(); $where = '1=1'; if ( ! empty( $args['search'] ) ) { $search = '%' . $wpdb->esc_like( $args['search'] ) . '%'; $where .= $wpdb->prepare( ' AND (url LIKE %s OR path LIKE %s)', $search, $search ); } if ( ! empty( $args['date_from'] ) ) { $where .= $wpdb->prepare( ' AND date_added >= %f', $args['date_from'] ); } if ( ! empty( $args['date_to'] ) ) { $where .= $wpdb->prepare( ' AND date_added <= %f', $args['date_to'] ); } if ( ! empty( $args['page_type'] ) ) { $where .= $wpdb->prepare( ' AND page_type = %s', $args['page_type'] ); } if ( ! empty( $args['host'] ) ) { $where .= $wpdb->prepare( ' AND host = %s', $args['host'] ); } $orderby = \sanitize_sql_orderby( $args['orderby'] . ' ' . $args['order'] ); $limit = $wpdb->prepare( 'LIMIT %d OFFSET %d', $args['limit'], $args['offset'] ); $query = "SELECT * FROM {$table} WHERE {$where} ORDER BY {$orderby} {$limit}"; return self::get_results( $query, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared } /** * Counts all records with optional filters. * * @param array $args Arguments for filtering. * * @return int * * @since 4.9.0 */ public static function count_all( $args = array() ) { global $wpdb; $defaults = array( 'search' => '', 'date_from' => '', 'date_to' => '', 'page_type' => '', 'host' => '', ); $args = \wp_parse_args( $args, $defaults ); $table = self::get_table_name(); $where = '1=1'; if ( ! empty( $args['search'] ) ) { $search = '%' . $wpdb->esc_like( $args['search'] ) . '%'; $where .= $wpdb->prepare( ' AND (url LIKE %s OR path LIKE %s)', $search, $search ); } if ( ! empty( $args['date_from'] ) ) { $where .= $wpdb->prepare( ' AND date_added >= %f', $args['date_from'] ); } if ( ! empty( $args['date_to'] ) ) { $where .= $wpdb->prepare( ' AND date_added <= %f', $args['date_to'] ); } if ( ! empty( $args['page_type'] ) ) { $where .= $wpdb->prepare( ' AND page_type = %s', $args['page_type'] ); } if ( ! empty( $args['host'] ) ) { $where .= $wpdb->prepare( ' AND host = %s', $args['host'] ); } $query = "SELECT COUNT(*) FROM {$table} WHERE {$where}"; return (int) self::get_var( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared } /** * Get column names for admin display. * * @return array * * @since 4.9.0 */ public static function get_column_names_admin(): array { return array( 'id' => __( 'ID', '0-day-analytics' ), 'url' => __( 'URL', '0-day-analytics' ), //'scheme' => __( 'Scheme', '0-day-analytics' ), 'host' => __( 'Host', '0-day-analytics' ), 'path' => __( 'Path', '0-day-analytics' ), 'page_type' => __( 'Page Type', '0-day-analytics' ), 'page_id' => __( 'ID', '0-day-analytics' ), 'visit_count' => __( 'Visits', '0-day-analytics' ), 'assets' => __( 'Assets', '0-day-analytics' ), 'pagespeed' => __( 'PageSpeed', '0-day-analytics' ), 'date_added' => __( 'Date Added', '0-day-analytics' ), ); } /** * Update assets data for a URL record. * * @param int $id The record ID. * @param array $assets_data Array containing JS, CSS, and media files data. * * @return bool * * @since 4.9.0 */ public static function update_assets_data( int $id, array $assets_data ): bool { $log_entry = self::load( 'id=%d', array( $id ) ); if ( ! $log_entry ) { return false; } $log_entry['assets_data'] = wp_json_encode( $assets_data ); $log_entry['assets_collected_at'] = microtime( true ); return (bool) self::insert( $log_entry ); } /** * Update PageSpeed data for a URL record. * * @param int $id The record ID. * @param array $pagespeed_data PageSpeed Insights API data. * * @return bool * * @since 4.9.0 */ public static function update_pagespeed_data( int $id, array $pagespeed_data ): bool { $log_entry = self::load( 'id=%d', array( $id ) ); if ( ! $log_entry ) { return false; } $log_entry['pagespeed_data'] = wp_json_encode( $pagespeed_data ); $log_entry['pagespeed_collected_at'] = microtime( true ); return (bool) self::insert( $log_entry ); } } }