prefix . 'latchbot_visits'; } public static function install() { global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $table = self::table(); $charset = $wpdb->get_charset_collate(); dbDelta( "CREATE TABLE {$table} ( day date NOT NULL, bot varchar(64) NOT NULL, section varchar(40) NOT NULL, hits int(10) unsigned NOT NULL DEFAULT 0, refused int(10) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (day,bot,section) ) {$charset};" ); update_option( 'latchbot_db_version', self::DB_VERSION ); if ( ! wp_next_scheduled( self::CRON_HOOK ) ) { wp_schedule_event( time() + HOUR_IN_SECONDS, 'daily', self::CRON_HOOK ); } } public static function deactivate() { wp_clear_scheduled_hook( self::CRON_HOOK ); } public static function maybe_upgrade() { if ( self::DB_VERSION !== get_option( 'latchbot_db_version' ) ) { self::install(); } } public static function record( $bot, $section, $refused ) { $settings = Latchbot_Settings::get(); if ( empty( $settings['log'] ) ) { return; } global $wpdb; $refused = $refused ? 1 : 0; // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- a counter in the plugin's own table, nothing to cache. $wpdb->query( $wpdb->prepare( 'INSERT INTO %i (day, bot, section, hits, refused) VALUES (%s, %s, %s, 1, %d) ON DUPLICATE KEY UPDATE hits = hits + 1, refused = refused + %d', self::table(), wp_date( 'Y-m-d' ), $bot, $section, $refused, $refused ) ); do_action( 'latchbot_visit', $bot, $section, (bool) $refused ); } public static function prune() { global $wpdb; $cutoff = self::since( self::KEEP_DAYS ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- retention cleanup of the plugin's own table. $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE day < %s', self::table(), $cutoff ) ); } public static function clear() { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- the site owner empties the plugin's own table. $wpdb->query( $wpdb->prepare( 'DELETE FROM %i', self::table() ) ); } /** Totals per crawler over the last $days days, keyed by bot id. */ public static function by_bot( $days = 30 ) { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- report over the plugin's own table, shown once per admin page view. $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT bot, SUM(hits) AS hits, SUM(refused) AS refused, MAX(day) AS last_day FROM %i WHERE day >= %s GROUP BY bot', self::table(), self::since( $days ) ), ARRAY_A ); $out = array(); foreach ( (array) $rows as $row ) { $out[ $row['bot'] ] = array( 'hits' => (int) $row['hits'], 'refused' => (int) $row['refused'], 'last_day' => $row['last_day'], ); } return $out; } /** Totals per content section over the last $days days, busiest first. */ public static function by_section( $days = 30, $limit = 10 ) { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- report over the plugin's own table, shown once per admin page view. $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT section, SUM(hits) AS hits, SUM(refused) AS refused FROM %i WHERE day >= %s GROUP BY section ORDER BY hits DESC LIMIT %d', self::table(), self::since( $days ), $limit ), ARRAY_A ); return array_map( function ( $row ) { return array( 'section' => $row['section'], 'hits' => (int) $row['hits'], 'refused' => (int) $row['refused'], ); }, (array) $rows ); } private static function since( $days ) { return wp_date( 'Y-m-d', time() - ( max( 1, (int) $days ) - 1 ) * DAY_IN_SECONDS ); } }