| @@ -16,8 +16,36 @@ | ||
| 16 | 16 | */ |
| 17 | 17 | class Database { |
| 18 | 18 | |
| 19 | 19 | /** |
| 20 | + * Cached status of the required Top 10 tables for this request. | |
| 21 | + * | |
| 22 | + * @var array<string,bool> | |
| 23 | + */ | |
| 24 | + private static $table_installation_cache = array(); | |
| 25 | + | |
| 26 | + /** | |
| 27 | + * Database version used to populate the request cache. | |
| 28 | + * | |
| 29 | + * @var string|null | |
| 30 | + */ | |
| 31 | + private static $table_installation_cache_version = null; | |
| 32 | + | |
| 33 | + /** | |
| 34 | + * Cached status of non-standard tables checked during this request. | |
| 35 | + * | |
| 36 | + * @var array<string,bool> | |
| 37 | + */ | |
| 38 | + private static $individual_table_cache = array(); | |
| 39 | + | |
| 40 | + /** | |
| 41 | + * Cached table metadata for this request. | |
| 42 | + * | |
| 43 | + * @var array<string,array<string,array<string,int|string>>> | |
| 44 | + */ | |
| 45 | + private static $table_metadata_cache = array(); | |
| 46 | + | |
| 47 | + /** | |
| 20 | 48 | * Constructor. |
| 21 | 49 | */ |
| 22 | 50 | public function __construct() { |
| 23 | 51 | // No initialization needed for static methods. |
| @@ -79,8 +107,39 @@ | ||
| 79 | 107 | return (int) $wpdb->get_var( $sql ); |
| 80 | 108 | } |
| 81 | 109 | |
| 82 | 110 | /** |
| 111 | + * Get the count for a site-wide request context. | |
| 112 | + * | |
| 113 | + * Site-wide contexts are resolved by the Pro-only fixed-ID map and then | |
| 114 | + * addressed by their reserved numeric ID in the shared count tables. | |
| 115 | + * | |
| 116 | + * @since 4.5.0 | |
| 117 | + * | |
| 118 | + * @param string $context Site-wide context key. | |
| 119 | + * @param int|null $blog_id Blog ID (optional, defaults to current blog). | |
| 120 | + * @param bool $daily Whether to get the daily count. | |
| 121 | + * @param array $date_range Date range array for daily counts. | |
| 122 | + * @return int Site-wide count. | |
| 123 | + */ | |
| 124 | + public static function get_sitewide_count( $context, $blog_id = null, $daily = false, $date_range = array() ) { | |
| 125 | + /** | |
| 126 | + * Filters the view count for a site-wide context. | |
| 127 | + * | |
| 128 | + * Site-wide contexts are a Pro feature; the free plugin always returns 0. | |
| 129 | + * | |
| 130 | + * @since 4.5.0 | |
| 131 | + * | |
| 132 | + * @param int $count The site-wide count. | |
| 133 | + * @param string $context Site-wide context key. | |
| 134 | + * @param int|null $blog_id Blog ID, or null for the current blog. | |
| 135 | + * @param bool $daily Whether a daily count was requested. | |
| 136 | + * @param array $date_range Date range used for daily counts. | |
| 137 | + */ | |
| 138 | + return (int) apply_filters( 'tptn_get_sitewide_count', 0, $context, $blog_id, $daily, $date_range ); | |
| 139 | + } | |
| 140 | + | |
| 141 | + /** | |
| 83 | 142 | * Update count for a post. |
| 84 | 143 | * |
| 85 | 144 | * @since 4.2.0 |
| 86 | 145 | * @deprecated 4.3.0 Use {@see Database::append_to_funnel()} instead. |
| @@ -142,8 +201,18 @@ | ||
| 142 | 201 | $result = $wpdb->query( $sql ); |
| 143 | 202 | |
| 144 | 203 | // Trigger action to clear cache. |
| 145 | 204 | if ( false !== $result ) { |
| 205 | + /** | |
| 206 | + * Fires after a post's view count has been written to the database. | |
| 207 | + * | |
| 208 | + * @since 4.2.0 | |
| 209 | + * | |
| 210 | + * @param int $post_id Post ID. | |
| 211 | + * @param int $count The count that was stored. | |
| 212 | + * @param int $blog_id Blog ID. | |
| 213 | + * @param bool $daily Whether the daily table was updated. | |
| 214 | + */ | |
| 146 | 215 | do_action( 'tptn_set_count', $post_id, $count, $blog_id, $daily ); |
| 147 | 216 | } |
| 148 | 217 | |
| 149 | 218 | return $result; |
| @@ -213,8 +282,15 @@ | ||
| 213 | 282 | $result = $wpdb->query( $sql ); |
| 214 | 283 | |
| 215 | 284 | // Trigger action to clear cache. |
| 216 | 285 | if ( false !== $result ) { |
| 286 | + /** | |
| 287 | + * Fires after view counts have been deleted from the database. | |
| 288 | + * | |
| 289 | + * @since 4.2.0 | |
| 290 | + * | |
| 291 | + * @param array $args Arguments describing which counts were deleted. | |
| 292 | + */ | |
| 217 | 293 | do_action( 'tptn_delete_counts', $args ); |
| 218 | 294 | } |
| 219 | 295 | |
| 220 | 296 | return $result; |
| @@ -220,9 +296,9 @@ | ||
| 220 | 296 | return $result; |
| 221 | 297 | } |
| 222 | 298 | |
| 223 | 299 | /** |
| 224 | - * Get table statistics including entry count and size. | |
| 300 | + * Get estimated table statistics including entry count and size. | |
| 225 | 301 | * |
| 226 | 302 | * @since 4.2.0 |
| 227 | 303 | * |
| 228 | 304 | * @return array Array of table statistics with entry count and size. |
| @@ -231,27 +307,41 @@ | ||
| 231 | 307 | $cache_key = 'tptn_table_statistics'; |
| 232 | 308 | $stats = wp_cache_get( $cache_key, 'top-10' ); |
| 233 | 309 | |
| 234 | 310 | if ( false === $stats ) { |
| 235 | - $stats = array(); | |
| 311 | + $stats = is_multisite() ? get_site_transient( $cache_key ) : get_transient( $cache_key ); | |
| 312 | + } | |
| 236 | 313 | |
| 237 | - $tables = array( | |
| 314 | + if ( false === $stats ) { | |
| 315 | + $tables = array( | |
| 238 | 316 | 'top_ten' => self::get_table( false ), |
| 239 | 317 | 'top_ten_daily' => self::get_table( true ), |
| 240 | 318 | 'top_ten_visits_funnel' => self::get_funnel_table(), |
| 241 | 319 | 'top_ten_visits_log' => self::get_log_table(), |
| 242 | 320 | ); |
| 321 | + $metadata = self::get_table_metadata( array_values( $tables ) ); | |
| 322 | + $stats = array(); | |
| 243 | 323 | |
| 244 | 324 | foreach ( $tables as $key => $table_name ) { |
| 245 | - if ( self::is_table_installed( $table_name ) ) { | |
| 246 | - $stats[ $key ] = self::get_single_table_statistics( $table_name ); | |
| 325 | + if ( isset( $metadata[ $table_name ] ) ) { | |
| 326 | + $stats[ $key ] = array( | |
| 327 | + 'entries' => $metadata[ $table_name ]['table_rows'], | |
| 328 | + 'size' => $metadata[ $table_name ]['data_length'] + $metadata[ $table_name ]['index_length'], | |
| 329 | + 'estimated' => true, | |
| 330 | + ); | |
| 247 | 331 | } |
| 248 | 332 | } |
| 249 | 333 | |
| 250 | - // Cache for 5 minutes. | |
| 251 | - wp_cache_set( $cache_key, $stats, 'top-10', 300 ); | |
| 334 | + // Cache for 5 minutes. Network-wide table metadata is shared by all sites. | |
| 335 | + if ( is_multisite() ) { | |
| 336 | + set_site_transient( $cache_key, $stats, 5 * MINUTE_IN_SECONDS ); | |
| 337 | + } else { | |
| 338 | + set_transient( $cache_key, $stats, 5 * MINUTE_IN_SECONDS ); | |
| 339 | + } | |
| 252 | 340 | } |
| 253 | 341 | |
| 342 | + wp_cache_set( $cache_key, $stats, 'top-10', 5 * MINUTE_IN_SECONDS ); | |
| 343 | + | |
| 254 | 344 | /** |
| 255 | 345 | * Filter the table statistics. |
| 256 | 346 | * |
| 257 | 347 | * @since 4.2.0 |
| @@ -261,70 +351,120 @@ | ||
| 261 | 351 | return apply_filters( 'tptn_table_statistics', $stats ); |
| 262 | 352 | } |
| 263 | 353 | |
| 264 | 354 | /** |
| 265 | - * Get entry count and estimated size for a single table. | |
| 355 | + * Get table metadata for a set of tables. | |
| 266 | 356 | * |
| 267 | - * @since 4.3.0 | |
| 357 | + * @since 4.5.0 | |
| 268 | 358 | * |
| 269 | - * @param string $table_name Table name. | |
| 270 | - * @return array { | |
| 271 | - * @type int $entries Number of entries. | |
| 272 | - * @type float $size Estimated size in bytes. | |
| 273 | - * } | |
| 359 | + * @param string[] $tables Tables to inspect. | |
| 360 | + * @param bool $force Whether to bypass the request cache. | |
| 361 | + * @return array<string,array<string,int|string>> Table metadata keyed by name. | |
| 274 | 362 | */ |
| 275 | - private static function get_single_table_statistics( $table_name ) { | |
| 363 | + private static function get_table_metadata( $tables, $force = false ) { | |
| 276 | 364 | global $wpdb; |
| 277 | 365 | |
| 278 | - // Get row count. | |
| 279 | - if ( is_network_admin() ) { | |
| 280 | - // In network admin, count all entries. | |
| 281 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 282 | - $count = $wpdb->get_var( "SELECT COUNT(*) FROM `{$table_name}`" ); | |
| 366 | + $tables = array_values( array_unique( array_filter( $tables, 'is_string' ) ) ); | |
| 367 | + if ( empty( $tables ) ) { | |
| 368 | + return array(); | |
| 369 | + } | |
| 370 | + | |
| 371 | + $cache_key = implode( '|', $tables ); | |
| 372 | + if ( ! $force && isset( self::$table_metadata_cache[ $cache_key ] ) ) { | |
| 373 | + return self::$table_metadata_cache[ $cache_key ]; | |
| 374 | + } | |
| 375 | + | |
| 376 | + $placeholders = implode( ', ', array_fill( 0, count( $tables ), '%s' ) ); | |
| 377 | + if ( self::is_sqlite() ) { | |
| 378 | + $sql = $wpdb->prepare( | |
| 379 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare | |
| 380 | + "SELECT name AS TABLE_NAME, 0 AS TABLE_ROWS, 0 AS DATA_LENGTH, 0 AS INDEX_LENGTH FROM sqlite_master WHERE type = 'table' AND name IN ({$placeholders})", | |
| 381 | + ...$tables | |
| 382 | + ); | |
| 283 | 383 | } else { |
| 284 | - // In individual site admin, count only entries for this blog. | |
| 285 | - $count = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 286 | - $wpdb->prepare( | |
| 287 | - "SELECT COUNT(*) FROM `{$table_name}` WHERE blog_id = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 288 | - get_current_blog_id() | |
| 289 | - ) | |
| 384 | + $sql = $wpdb->prepare( | |
| 385 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare | |
| 386 | + "SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ({$placeholders})", | |
| 387 | + ...$tables | |
| 290 | 388 | ); |
| 291 | 389 | } |
| 292 | 390 | |
| 293 | - // Refresh InnoDB stats so information_schema reflects the current state. | |
| 294 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 295 | - $wpdb->query( "ANALYZE TABLE `{$table_name}`" ); | |
| 391 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared | |
| 392 | + $rows = $wpdb->get_results( $sql, ARRAY_A ); | |
| 393 | + $metadata = array(); | |
| 394 | + foreach ( $rows as $row ) { | |
| 395 | + $table_name = isset( $row['TABLE_NAME'] ) ? (string) $row['TABLE_NAME'] : ''; | |
| 396 | + if ( '' !== $table_name && in_array( $table_name, $tables, true ) ) { | |
| 397 | + $metadata[ $table_name ] = array( | |
| 398 | + 'table_rows' => absint( $row['TABLE_ROWS'] ?? 0 ), | |
| 399 | + 'data_length' => absint( $row['DATA_LENGTH'] ?? 0 ), | |
| 400 | + 'index_length' => absint( $row['INDEX_LENGTH'] ?? 0 ), | |
| 401 | + ); | |
| 402 | + } | |
| 403 | + } | |
| 296 | 404 | |
| 297 | - // Get table size in bytes (always shows total size across all blogs). | |
| 298 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 299 | - $size = $wpdb->get_var( | |
| 300 | - $wpdb->prepare( | |
| 301 | - 'SELECT (data_length + index_length) FROM information_schema.TABLES WHERE table_schema = %s AND table_name = %s', | |
| 302 | - defined( 'DB_NAME' ) ? DB_NAME : '', // @codingStandardsIgnoreLine - WordPress constant | |
| 303 | - $table_name | |
| 304 | - ) | |
| 305 | - ); | |
| 405 | + // Temporary tables are not exposed through information_schema or sqlite_master. | |
| 406 | + foreach ( array_diff( $tables, array_keys( $metadata ) ) as $table_name ) { | |
| 407 | + $query = $wpdb->prepare( 'SELECT 1 FROM %i LIMIT 0', $table_name ); | |
| 408 | + $suppress_errors = $wpdb->suppress_errors(); | |
| 306 | 409 | |
| 307 | - // Calculate size for individual sites in multisite. | |
| 308 | - $calculated_size = $size ? (int) $size : 0; | |
| 309 | - if ( is_multisite() && ! is_network_admin() && $calculated_size > 0 ) { | |
| 310 | - // Get total entries to calculate ratio. | |
| 311 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 312 | - $total_count = $wpdb->get_var( "SELECT COUNT(*) FROM `{$table_name}`" ); | |
| 410 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared | |
| 411 | + $table_exists = false !== $wpdb->query( $query ); | |
| 412 | + $wpdb->suppress_errors( $suppress_errors ); | |
| 313 | 413 | |
| 314 | - if ( $total_count > 0 && $count > 0 ) { | |
| 315 | - // Estimate size based on entry count ratio. | |
| 316 | - $calculated_size = ( $count / $total_count ) * $calculated_size; | |
| 414 | + if ( $table_exists ) { | |
| 415 | + $metadata[ $table_name ] = array( | |
| 416 | + 'table_rows' => 0, | |
| 417 | + 'data_length' => 0, | |
| 418 | + 'index_length' => 0, | |
| 419 | + ); | |
| 317 | 420 | } |
| 318 | 421 | } |
| 319 | 422 | |
| 320 | - return array( | |
| 321 | - 'entries' => absint( $count ), | |
| 322 | - 'size' => $calculated_size, | |
| 323 | - ); | |
| 423 | + self::$table_metadata_cache[ $cache_key ] = $metadata; | |
| 424 | + | |
| 425 | + return $metadata; | |
| 324 | 426 | } |
| 325 | 427 | |
| 326 | 428 | /** |
| 429 | + * Get installation status for a set of tables. | |
| 430 | + * | |
| 431 | + * @since 4.5.0 | |
| 432 | + * | |
| 433 | + * @param string[] $tables Tables to inspect. | |
| 434 | + * @param bool $force Whether to bypass the request cache. | |
| 435 | + * @return array<string,bool> Table statuses keyed by name. | |
| 436 | + */ | |
| 437 | + private static function get_table_statuses( $tables, $force = false ) { | |
| 438 | + $tables = array_values( array_unique( array_filter( $tables, 'is_string' ) ) ); | |
| 439 | + $metadata = self::get_table_metadata( $tables, $force ); | |
| 440 | + $statuses = array_fill_keys( $tables, false ); | |
| 441 | + | |
| 442 | + foreach ( array_keys( $metadata ) as $table ) { | |
| 443 | + $statuses[ $table ] = true; | |
| 444 | + } | |
| 445 | + | |
| 446 | + return $statuses; | |
| 447 | + } | |
| 448 | + | |
| 449 | + /** | |
| 450 | + * Determine whether the current database is SQLite. | |
| 451 | + * | |
| 452 | + * @since 4.5.0 | |
| 453 | + * | |
| 454 | + * @return bool Whether SQLite is in use. | |
| 455 | + */ | |
| 456 | + private static function is_sqlite() { | |
| 457 | + global $wpdb; | |
| 458 | + | |
| 459 | + if ( defined( 'DATABASE_TYPE' ) && 'sqlite' === strtolower( (string) DATABASE_TYPE ) ) { | |
| 460 | + return true; | |
| 461 | + } | |
| 462 | + | |
| 463 | + return method_exists( $wpdb, 'db_server_info' ) && false !== strpos( strtolower( (string) $wpdb->db_server_info() ), 'sqlite' ); | |
| 464 | + } | |
| 465 | + | |
| 466 | + /** | |
| 327 | 467 | * Clear the table statistics cache. |
| 328 | 468 | * |
| 329 | 469 | * @since 4.2.0 |
| 330 | 470 | */ |
| @@ -329,32 +469,118 @@ | ||
| 329 | 469 | * @since 4.2.0 |
| 330 | 470 | */ |
| 331 | 471 | public static function clear_table_statistics_cache() { |
| 332 | 472 | wp_cache_delete( 'tptn_table_statistics', 'top-10' ); |
| 473 | + | |
| 474 | + if ( is_multisite() ) { | |
| 475 | + delete_site_transient( 'tptn_table_statistics' ); | |
| 476 | + } else { | |
| 477 | + delete_transient( 'tptn_table_statistics' ); | |
| 478 | + } | |
| 333 | 479 | } |
| 334 | 480 | |
| 335 | 481 | /** |
| 482 | + * Invalidate the persistent and request-level table installation caches. | |
| 483 | + * | |
| 484 | + * @since 4.5.0 | |
| 485 | + */ | |
| 486 | + public static function clear_table_installation_cache() { | |
| 487 | + self::$table_installation_cache = array(); | |
| 488 | + self::$table_installation_cache_version = null; | |
| 489 | + self::$individual_table_cache = array(); | |
| 490 | + self::$table_metadata_cache = array(); | |
| 491 | + | |
| 492 | + delete_site_option( 'tptn_tables_installed' ); | |
| 493 | + } | |
| 494 | + | |
| 495 | + /** | |
| 496 | + * Get the installation status of the four required Top 10 tables. | |
| 497 | + * | |
| 498 | + * The status is persisted in a network option so normal admin requests do | |
| 499 | + * not need to enumerate the database tables. Explicit diagnostic requests | |
| 500 | + * can bypass the cache by setting $force to true. | |
| 501 | + * | |
| 502 | + * @since 4.5.0 | |
| 503 | + * | |
| 504 | + * @param bool $force Whether to perform a live check. | |
| 505 | + * @return array<string,bool> Table names mapped to their installation status. | |
| 506 | + */ | |
| 507 | + public static function get_table_installation_status( $force = false ) { | |
| 508 | + global $tptn_db_version; | |
| 509 | + | |
| 510 | + $tables = array( | |
| 511 | + self::get_table( false ), | |
| 512 | + self::get_table( true ), | |
| 513 | + self::get_funnel_table(), | |
| 514 | + self::get_log_table(), | |
| 515 | + ); | |
| 516 | + $version = isset( $tptn_db_version ) ? (string) $tptn_db_version : ''; | |
| 517 | + | |
| 518 | + $has_cached_tables = self::$table_installation_cache_version === $version && count( self::$table_installation_cache ) === count( $tables ) && ! array_diff_key( array_fill_keys( $tables, true ), self::$table_installation_cache ); | |
| 519 | + if ( ! $force && $has_cached_tables ) { | |
| 520 | + return self::$table_installation_cache; | |
| 521 | + } | |
| 522 | + | |
| 523 | + if ( ! $force ) { | |
| 524 | + $cached = get_site_option( 'tptn_tables_installed', array() ); | |
| 525 | + $has_all_tables = is_array( $cached ) && isset( $cached['db_version'], $cached['tables'] ) && (string) $cached['db_version'] === $version && is_array( $cached['tables'] ) && ! array_diff_key( array_fill_keys( $tables, true ), $cached['tables'] ); | |
| 526 | + if ( $has_all_tables ) { | |
| 527 | + self::$table_installation_cache = array(); | |
| 528 | + self::$individual_table_cache = array(); | |
| 529 | + foreach ( $tables as $table ) { | |
| 530 | + self::$table_installation_cache[ $table ] = (bool) $cached['tables'][ $table ]; | |
| 531 | + } | |
| 532 | + self::$table_installation_cache_version = $version; | |
| 533 | + | |
| 534 | + return self::$table_installation_cache; | |
| 535 | + } | |
| 536 | + } | |
| 537 | + | |
| 538 | + $statuses = self::get_table_statuses( $tables, $force ); | |
| 539 | + | |
| 540 | + self::$table_installation_cache = $statuses; | |
| 541 | + self::$table_installation_cache_version = $version; | |
| 542 | + update_site_option( | |
| 543 | + 'tptn_tables_installed', | |
| 544 | + array( | |
| 545 | + 'db_version' => $version, | |
| 546 | + 'tables' => $statuses, | |
| 547 | + ) | |
| 548 | + ); | |
| 549 | + | |
| 550 | + return $statuses; | |
| 551 | + } | |
| 552 | + | |
| 553 | + /** | |
| 336 | 554 | * Check if a table exists. |
| 337 | 555 | * |
| 338 | 556 | * @since 4.2.0 |
| 339 | 557 | * |
| 340 | 558 | * @param string $table Table name to check. |
| 559 | + * @param bool $force Whether to perform a live check. | |
| 341 | 560 | * @return bool True if table exists, false otherwise. |
| 342 | 561 | */ |
| 343 | - public static function is_table_installed( $table ) { | |
| 344 | - global $wpdb; | |
| 562 | + public static function is_table_installed( $table, $force = false ) { | |
| 563 | + $required_tables = array( | |
| 564 | + self::get_table( false ), | |
| 565 | + self::get_table( true ), | |
| 566 | + self::get_funnel_table(), | |
| 567 | + self::get_log_table(), | |
| 568 | + ); | |
| 345 | 569 | |
| 346 | - static $cache = array(); | |
| 570 | + if ( in_array( $table, $required_tables, true ) ) { | |
| 571 | + $statuses = self::get_table_installation_status( $force ); | |
| 572 | + return ! empty( $statuses[ $table ] ); | |
| 573 | + } | |
| 347 | 574 | |
| 348 | - if ( isset( $cache[ $table ] ) ) { | |
| 349 | - return $cache[ $table ]; | |
| 575 | + if ( ! $force && array_key_exists( $table, self::$individual_table_cache ) ) { | |
| 576 | + return self::$individual_table_cache[ $table ]; | |
| 350 | 577 | } |
| 351 | 578 | |
| 352 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 353 | - $result = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ); | |
| 354 | - $cache[ $table ] = ( $result === $table ); | |
| 579 | + $statuses = self::get_table_statuses( array( $table ), $force ); | |
| 580 | + self::$individual_table_cache[ $table ] = ! empty( $statuses[ $table ] ); | |
| 355 | 581 | |
| 356 | - return $cache[ $table ]; | |
| 582 | + return self::$individual_table_cache[ $table ]; | |
| 357 | 583 | } |
| 358 | 584 | |
| 359 | 585 | /** |
| 360 | 586 | * Get counts with post information (JOIN with wp_posts). |
| @@ -402,12 +628,12 @@ | ||
| 402 | 628 | } |
| 403 | 629 | |
| 404 | 630 | if ( $args['daily'] ) { |
| 405 | 631 | if ( ! empty( $args['from_date'] ) ) { |
| 406 | - $where[] = $wpdb->prepare( 'DATE(t.dp_date) >= DATE(%s)', $args['from_date'] ); | |
| 632 | + $where[] = $wpdb->prepare( 't.dp_date >= %s', gmdate( 'Y-m-d 00:00:00', strtotime( $args['from_date'] ) ) ); | |
| 407 | 633 | } |
| 408 | 634 | if ( ! empty( $args['to_date'] ) ) { |
| 409 | - $where[] = $wpdb->prepare( 'DATE(t.dp_date) <= DATE(%s)', $args['to_date'] ); | |
| 635 | + $where[] = $wpdb->prepare( 't.dp_date < %s', gmdate( 'Y-m-d 00:00:00', strtotime( $args['to_date'] . ' +1 day' ) ) ); | |
| 410 | 636 | } |
| 411 | 637 | } |
| 412 | 638 | |
| 413 | 639 | if ( ! empty( $args['post_ids'] ) ) { |
| @@ -543,10 +769,12 @@ | ||
| 543 | 769 | * |
| 544 | 770 | * @return bool True if both tables exist, false otherwise. |
| 545 | 771 | */ |
| 546 | 772 | public static function are_tables_installed() { |
| 547 | - return self::is_table_installed( self::get_table( false ) ) | |
| 548 | - && self::is_table_installed( self::get_table( true ) ); | |
| 773 | + $statuses = self::get_table_installation_status(); | |
| 774 | + | |
| 775 | + return ! empty( $statuses[ self::get_table( false ) ] ) | |
| 776 | + && ! empty( $statuses[ self::get_table( true ) ] ); | |
| 549 | 777 | } |
| 550 | 778 | |
| 551 | 779 | /** |
| 552 | 780 | * Create table SQL for the main top_ten table. |
| @@ -566,9 +794,11 @@ | ||
| 566 | 794 | postnumber bigint(20) NOT NULL, |
| 567 | 795 | cntaccess bigint(20) NOT NULL, |
| 568 | 796 | blog_id bigint(20) NOT NULL DEFAULT '1', |
| 569 | 797 | PRIMARY KEY (postnumber, blog_id), |
| 570 | - KEY idx_blog_id (blog_id) | |
| 798 | + KEY idx_blog_id (blog_id), | |
| 799 | + KEY idx_cntaccess (cntaccess), | |
| 800 | + KEY idx_blog_cntaccess (blog_id, cntaccess) | |
| 571 | 801 | ) $charset_collate;"; |
| 572 | 802 | |
| 573 | 803 | return $sql; |
| 574 | 804 | } |
| @@ -678,8 +908,93 @@ | ||
| 678 | 908 | return $sql; |
| 679 | 909 | } |
| 680 | 910 | |
| 681 | 911 | /** |
| 912 | + * Record a single visit using the configured tracking method. | |
| 913 | + * | |
| 914 | + * Funnel tracking (default) appends the visit to the funnel table which is | |
| 915 | + * drained into the count tables by the aggregation cron. Legacy tracking | |
| 916 | + * writes directly to the count tables on every visit (pre-4.3 behaviour) | |
| 917 | + * and does not populate the visits log. | |
| 918 | + * | |
| 919 | + * @since 4.3.3 | |
| 920 | + * | |
| 921 | + * @param int $post_id Post ID. | |
| 922 | + * @param int $blog_id Blog ID. | |
| 923 | + * @param int $activate_counter Counter flag: 1 = overall, 10 = daily, 11 = both. | |
| 924 | + * @param int $source Traffic source: 0 = web, 1 = feed. Only stored by funnel tracking. | |
| 925 | + * @return int|false Rows inserted/updated or false on error. | |
| 926 | + */ | |
| 927 | + public static function record_view( $post_id, $blog_id, $activate_counter = 11, $source = 0 ) { | |
| 928 | + if ( 'legacy' === \tptn_get_option( 'tracking_method', 'funnel' ) ) { | |
| 929 | + return self::update_counts_direct( $post_id, $blog_id, $activate_counter ); | |
| 930 | + } | |
| 931 | + | |
| 932 | + return self::append_to_funnel( $post_id, $blog_id, $activate_counter, $source ); | |
| 933 | + } | |
| 934 | + | |
| 935 | + /** | |
| 936 | + * Write a single visit directly to the overall and daily count tables. | |
| 937 | + * | |
| 938 | + * This is the legacy (pre-4.3) tracking method: an immediate upsert per view, | |
| 939 | + * bypassing the funnel table and the aggregation cron. The visits log is not | |
| 940 | + * populated by this method. | |
| 941 | + * | |
| 942 | + * @since 4.3.3 | |
| 943 | + * | |
| 944 | + * @param int $post_id Post ID. | |
| 945 | + * @param int $blog_id Blog ID. | |
| 946 | + * @param int $activate_counter Counter flag: 1 = overall, 10 = daily, 11 = both. | |
| 947 | + * @return int|false Rows inserted/updated or false on error. | |
| 948 | + */ | |
| 949 | + public static function update_counts_direct( $post_id, $blog_id, $activate_counter = 11 ) { | |
| 950 | + global $wpdb; | |
| 951 | + | |
| 952 | + $post_id = absint( $post_id ); | |
| 953 | + $blog_id = absint( $blog_id ); | |
| 954 | + $activate_counter = (int) $activate_counter; | |
| 955 | + $rows = 0; | |
| 956 | + | |
| 957 | + // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 958 | + if ( in_array( $activate_counter, array( 1, 11 ), true ) ) { | |
| 959 | + $table = self::get_table( false ); | |
| 960 | + $result = $wpdb->query( | |
| 961 | + $wpdb->prepare( | |
| 962 | + "INSERT INTO {$table} (postnumber, cntaccess, blog_id) VALUES (%d, 1, %d) ON DUPLICATE KEY UPDATE cntaccess = cntaccess + 1", | |
| 963 | + $post_id, | |
| 964 | + $blog_id | |
| 965 | + ) | |
| 966 | + ); | |
| 967 | + if ( false === $result ) { | |
| 968 | + self::clear_table_installation_cache(); | |
| 969 | + return false; | |
| 970 | + } | |
| 971 | + $rows += (int) $result; | |
| 972 | + } | |
| 973 | + | |
| 974 | + if ( in_array( $activate_counter, array( 10, 11 ), true ) ) { | |
| 975 | + $table = self::get_table( true ); | |
| 976 | + $dp_date = current_time( 'Y-m-d H' ) . ':00:00'; | |
| 977 | + $result = $wpdb->query( | |
| 978 | + $wpdb->prepare( | |
| 979 | + "INSERT INTO {$table} (postnumber, cntaccess, dp_date, blog_id) VALUES (%d, 1, %s, %d) ON DUPLICATE KEY UPDATE cntaccess = cntaccess + 1", | |
| 980 | + $post_id, | |
| 981 | + $dp_date, | |
| 982 | + $blog_id | |
| 983 | + ) | |
| 984 | + ); | |
| 985 | + if ( false === $result ) { | |
| 986 | + self::clear_table_installation_cache(); | |
| 987 | + return false; | |
| 988 | + } | |
| 989 | + $rows += (int) $result; | |
| 990 | + } | |
| 991 | + // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 992 | + | |
| 993 | + return $rows; | |
| 994 | + } | |
| 995 | + | |
| 996 | + /** | |
| 682 | 997 | * Append a single visit to the funnel table. |
| 683 | 998 | * |
| 684 | 999 | * @since 4.3.0 |
| 685 | 1000 | * |
| @@ -692,9 +1007,9 @@ | ||
| 692 | 1007 | public static function append_to_funnel( $post_id, $blog_id, $activate_counter = 11, $source = 0 ) { |
| 693 | 1008 | global $wpdb; |
| 694 | 1009 | |
| 695 | 1010 | // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery |
| 696 | - return $wpdb->insert( | |
| 1011 | + $result = $wpdb->insert( | |
| 697 | 1012 | self::get_funnel_table(), |
| 698 | 1013 | array( |
| 699 | 1014 | 'postnumber' => absint( $post_id ), |
| 700 | 1015 | 'blog_id' => absint( $blog_id ), |
| @@ -703,25 +1018,41 @@ | ||
| 703 | 1018 | 'source' => (int) $source, |
| 704 | 1019 | ), |
| 705 | 1020 | array( '%d', '%d', '%s', '%d', '%d' ) |
| 706 | 1021 | ); |
| 1022 | + | |
| 1023 | + if ( false === $result ) { | |
| 1024 | + self::clear_table_installation_cache(); | |
| 1025 | + } | |
| 1026 | + | |
| 1027 | + return $result; | |
| 707 | 1028 | } |
| 708 | 1029 | |
| 709 | 1030 | /** |
| 710 | 1031 | * Drain the funnel into the log and count tables, then empty the funnel. |
| 711 | 1032 | * |
| 712 | - * All four operations (copy to log, aggregate to daily, aggregate to overall, | |
| 713 | - * delete from funnel) run inside one transaction. A failure rolls back cleanly | |
| 714 | - * and the next run retries the same rows with no double-counting risk. | |
| 1033 | + * Each of the four steps (copy to log, aggregate to daily, aggregate to overall, | |
| 1034 | + * delete from funnel) commits independently rather than inside one app-level | |
| 1035 | + * transaction. wpdb silently reconnects and retries a query if the DB connection | |
| 1036 | + * drops mid-request, which would otherwise void an in-flight transaction and let | |
| 1037 | + * later steps (e.g. the funnel delete) commit on a fresh connection while earlier | |
| 1038 | + * ones were rolled back — losing visits with no error. Without a wrapping | |
| 1039 | + * transaction, a crash between steps can at worst cause one batch to be | |
| 1040 | + * re-aggregated (a bounded, self-correcting over-count), never a silent loss. | |
| 715 | 1041 | * |
| 716 | 1042 | * @since 4.3.0 |
| 717 | 1043 | * |
| 718 | - * @param int $batch_size Maximum funnel rows to process per run. | |
| 1044 | + * @param int $batch_size Maximum funnel rows to process per run. | |
| 1045 | + * @param int|null $blog_id Optional blog ID. When set, only that site's buffered visits are processed. | |
| 719 | 1046 | * @return true|false|int|\WP_Error True if rows processed, false if lock not acquired, 0 if funnel empty, WP_Error on DB failure. |
| 720 | 1047 | */ |
| 721 | - public static function aggregate_visit_log( $batch_size = 10000 ) { | |
| 1048 | + public static function aggregate_visit_log( $batch_size = 10000, $blog_id = null ) { | |
| 722 | 1049 | global $wpdb; |
| 723 | 1050 | |
| 1051 | + $batch_size = max( 1, absint( $batch_size ) ); | |
| 1052 | + $blog_id = null === $blog_id ? null : absint( $blog_id ); | |
| 1053 | + $blog_where = null === $blog_id ? '' : $wpdb->prepare( ' AND blog_id = %d', $blog_id ); | |
| 1054 | + | |
| 724 | 1055 | // Detect SQLite (e.g. WordPress Playground) vs MySQL/MariaDB. |
| 725 | 1056 | // DATABASE_TYPE is defined by the WordPress SQLite Database Integration drop-in. |
| 726 | 1057 | $is_sqlite = ( defined( 'DATABASE_TYPE' ) && 'sqlite' === DATABASE_TYPE ) |
| 727 | 1058 | || false !== strpos( strtolower( (string) $wpdb->db_server_info() ), 'sqlite' ); |
| @@ -730,9 +1061,9 @@ | ||
| 730 | 1061 | $log_table = self::get_log_table(); |
| 731 | 1062 | $daily_table = self::get_table( true ); |
| 732 | 1063 | $full_table = self::get_table( false ); |
| 733 | 1064 | |
| 734 | - // MySQL-specific locking and transactions. | |
| 1065 | + // GET_LOCK is a MySQL-only concurrency guard against overlapping cron runs; not needed for correctness. | |
| 735 | 1066 | if ( ! $is_sqlite ) { |
| 736 | 1067 | // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching |
| 737 | 1068 | $lock_acquired = $wpdb->get_var( "SELECT GET_LOCK('tptn_aggregation', 0)" ); |
| 738 | 1069 | if ( '1' !== (string) $lock_acquired ) { |
| @@ -737,27 +1068,18 @@ | ||
| 737 | 1068 | $lock_acquired = $wpdb->get_var( "SELECT GET_LOCK('tptn_aggregation', 0)" ); |
| 738 | 1069 | if ( '1' !== (string) $lock_acquired ) { |
| 739 | 1070 | return false; |
| 740 | 1071 | } |
| 741 | - | |
| 742 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 743 | - if ( false === $wpdb->query( 'START TRANSACTION' ) ) { | |
| 744 | - $wpdb->query( "SELECT RELEASE_LOCK('tptn_aggregation')" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 745 | - return new \WP_Error( 'tptn_transaction_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Could not start transaction.', 'top-10' ) ); | |
| 746 | - } | |
| 747 | 1072 | } |
| 748 | 1073 | |
| 749 | 1074 | try { |
| 750 | 1075 | // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 751 | - $max_id = (int) $wpdb->get_var( "SELECT MAX(id) FROM {$funnel_table}" ); | |
| 1076 | + $max_id = (int) $wpdb->get_var( "SELECT MAX(id) FROM {$funnel_table} WHERE 1=1{$blog_where}" ); | |
| 752 | 1077 | if ( 0 === $max_id ) { |
| 753 | - if ( ! $is_sqlite ) { | |
| 754 | - $wpdb->query( 'ROLLBACK' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 755 | - } | |
| 756 | 1078 | return 0; |
| 757 | 1079 | } |
| 758 | 1080 | |
| 759 | - $cap_id = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$funnel_table} ORDER BY id ASC LIMIT %d, 1", $batch_size ) ); | |
| 1081 | + $cap_id = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$funnel_table} WHERE 1=1{$blog_where} ORDER BY id ASC LIMIT %d, 1", $batch_size ) ); | |
| 760 | 1082 | $was_capped = false; |
| 761 | 1083 | if ( null !== $cap_id ) { |
| 762 | 1084 | $capped_max = (int) $cap_id - 1; |
| 763 | 1085 | if ( $capped_max > 0 ) { |
| @@ -770,16 +1092,13 @@ | ||
| 770 | 1092 | $wpdb->prepare( |
| 771 | 1093 | "INSERT INTO {$log_table} (postnumber, blog_id, visited_at, source) |
| 772 | 1094 | SELECT postnumber, blog_id, visited_at, source |
| 773 | 1095 | FROM {$funnel_table} |
| 774 | - WHERE id <= %d", | |
| 1096 | + WHERE id <= %d{$blog_where}", | |
| 775 | 1097 | $max_id |
| 776 | 1098 | ) |
| 777 | 1099 | ); |
| 778 | 1100 | if ( false === $r ) { |
| 779 | - if ( ! $is_sqlite ) { | |
| 780 | - $wpdb->query( 'ROLLBACK' ); | |
| 781 | - } | |
| 782 | 1101 | return new \WP_Error( 'tptn_log_insert_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Failed to copy visits to log table.', 'top-10' ) ); |
| 783 | 1102 | } |
| 784 | 1103 | |
| 785 | 1104 | $r = $wpdb->query( |
| @@ -787,9 +1106,9 @@ | ||
| 787 | 1106 | "INSERT INTO {$daily_table} (postnumber, cntaccess, dp_date, blog_id) |
| 788 | 1107 | SELECT postnumber, COUNT(*) AS cntaccess, |
| 789 | 1108 | DATE_FORMAT(visited_at, '%%Y-%%m-%%d %%H:00:00') AS dp_date, blog_id |
| 790 | 1109 | FROM {$funnel_table} |
| 791 | - WHERE id <= %d AND activate_counter IN (10, 11) | |
| 1110 | + WHERE id <= %d AND activate_counter IN (10, 11){$blog_where} | |
| 792 | 1111 | GROUP BY postnumber, DATE_FORMAT(visited_at, '%%Y-%%m-%%d %%H:00:00'), blog_id |
| 793 | 1112 | ON DUPLICATE KEY UPDATE cntaccess = {$daily_table}.cntaccess + VALUES(cntaccess)", |
| 794 | 1113 | $max_id |
| 795 | 1114 | ) |
| @@ -794,11 +1113,8 @@ | ||
| 794 | 1113 | $max_id |
| 795 | 1114 | ) |
| 796 | 1115 | ); |
| 797 | 1116 | if ( false === $r ) { |
| 798 | - if ( ! $is_sqlite ) { | |
| 799 | - $wpdb->query( 'ROLLBACK' ); | |
| 800 | - } | |
| 801 | 1117 | return new \WP_Error( 'tptn_daily_insert_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Failed to aggregate visits into daily table.', 'top-10' ) ); |
| 802 | 1118 | } |
| 803 | 1119 | |
| 804 | 1120 | $r = $wpdb->query( |
| @@ -805,9 +1121,9 @@ | ||
| 805 | 1121 | $wpdb->prepare( |
| 806 | 1122 | "INSERT INTO {$full_table} (postnumber, cntaccess, blog_id) |
| 807 | 1123 | SELECT postnumber, COUNT(*) AS cntaccess, blog_id |
| 808 | 1124 | FROM {$funnel_table} |
| 809 | - WHERE id <= %d AND activate_counter IN (1, 11) | |
| 1125 | + WHERE id <= %d AND activate_counter IN (1, 11){$blog_where} | |
| 810 | 1126 | GROUP BY postnumber, blog_id |
| 811 | 1127 | ON DUPLICATE KEY UPDATE cntaccess = {$full_table}.cntaccess + VALUES(cntaccess)", |
| 812 | 1128 | $max_id |
| 813 | 1129 | ) |
| @@ -812,29 +1128,29 @@ | ||
| 812 | 1128 | $max_id |
| 813 | 1129 | ) |
| 814 | 1130 | ); |
| 815 | 1131 | if ( false === $r ) { |
| 816 | - if ( ! $is_sqlite ) { | |
| 817 | - $wpdb->query( 'ROLLBACK' ); | |
| 818 | - } | |
| 819 | 1132 | return new \WP_Error( 'tptn_overall_insert_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Failed to aggregate visits into overall table.', 'top-10' ) ); |
| 820 | 1133 | } |
| 821 | 1134 | |
| 822 | - $r = $wpdb->query( $wpdb->prepare( "DELETE FROM {$funnel_table} WHERE id <= %d", $max_id ) ); | |
| 1135 | + $r = $wpdb->query( $wpdb->prepare( "DELETE FROM {$funnel_table} WHERE id <= %d{$blog_where}", $max_id ) ); | |
| 823 | 1136 | if ( false === $r ) { |
| 824 | - if ( ! $is_sqlite ) { | |
| 825 | - $wpdb->query( 'ROLLBACK' ); | |
| 826 | - } | |
| 827 | 1137 | return new \WP_Error( 'tptn_funnel_delete_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Failed to drain funnel table.', 'top-10' ) ); |
| 828 | 1138 | } |
| 829 | 1139 | // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 830 | 1140 | |
| 831 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 832 | - if ( ! $is_sqlite && false === $wpdb->query( 'COMMIT' ) ) { | |
| 833 | - $wpdb->query( 'ROLLBACK' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 834 | - return new \WP_Error( 'tptn_commit_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Transaction commit failed.', 'top-10' ) ); | |
| 835 | - } | |
| 836 | - | |
| 1141 | + /** | |
| 1142 | + * Fires after view counts change, so caches can be invalidated. | |
| 1143 | + * | |
| 1144 | + * Bulk operations such as funnel aggregation and the daily rollup pass zeros, | |
| 1145 | + * signalling that many posts changed rather than one specific post. | |
| 1146 | + * | |
| 1147 | + * @since 4.2.0 | |
| 1148 | + * | |
| 1149 | + * @param int $post_id Post ID, or 0 after a bulk update. | |
| 1150 | + * @param int $blog_id Blog ID, or 0 after a bulk update. | |
| 1151 | + * @param bool $daily Whether the daily table was updated. | |
| 1152 | + */ | |
| 837 | 1153 | do_action( 'tptn_count_updated', 0, 0, false ); |
| 838 | 1154 | |
| 839 | 1155 | if ( $was_capped && ! wp_next_scheduled( 'tptn_aggregation_cron_hook' ) ) { |
| 840 | 1156 | wp_schedule_single_event( time(), 'tptn_aggregation_cron_hook' ); |
| @@ -1081,8 +1397,268 @@ | ||
| 1081 | 1397 | return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `{$table}` WHERE dp_date <= %s", $to_date ) ); |
| 1082 | 1398 | } |
| 1083 | 1399 | |
| 1084 | 1400 | /** |
| 1401 | + * Get the daily-table row counts before and after a rollup. | |
| 1402 | + * | |
| 1403 | + * The projected row count groups rows by post, blog, and calendar date. | |
| 1404 | + * | |
| 1405 | + * @since 4.5.0 | |
| 1406 | + * | |
| 1407 | + * @param string $before_date Rows before this date are included. | |
| 1408 | + * @param int|null $blog_id Blog ID. Defaults to the current blog. | |
| 1409 | + * @return array|\WP_Error Rollup statistics or an error. | |
| 1410 | + */ | |
| 1411 | + public static function get_daily_rollup_stats( string $before_date, $blog_id = null ) { | |
| 1412 | + global $wpdb; | |
| 1413 | + | |
| 1414 | + $before_date = self::normalize_daily_rollup_date( $before_date ); | |
| 1415 | + if ( is_wp_error( $before_date ) ) { | |
| 1416 | + return $before_date; | |
| 1417 | + } | |
| 1418 | + | |
| 1419 | + $blog_id = null === $blog_id ? get_current_blog_id() : absint( $blog_id ); | |
| 1420 | + $table = self::get_table( true ); | |
| 1421 | + | |
| 1422 | + // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 1423 | + $rows_before = $wpdb->get_var( | |
| 1424 | + $wpdb->prepare( | |
| 1425 | + "SELECT COUNT(*) FROM `{$table}` WHERE blog_id = %d AND dp_date < %s", | |
| 1426 | + $blog_id, | |
| 1427 | + $before_date | |
| 1428 | + ) | |
| 1429 | + ); | |
| 1430 | + if ( null === $rows_before ) { | |
| 1431 | + return new \WP_Error( 'tptn_rollup_count_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Could not count daily rows before the rollup.', 'top-10' ) ); | |
| 1432 | + } | |
| 1433 | + | |
| 1434 | + $rows_after = $wpdb->get_var( | |
| 1435 | + $wpdb->prepare( | |
| 1436 | + "SELECT COUNT(*) FROM ( | |
| 1437 | + SELECT postnumber, blog_id, DATE(dp_date) AS rollup_date | |
| 1438 | + FROM `{$table}` | |
| 1439 | + WHERE blog_id = %d AND dp_date < %s | |
| 1440 | + GROUP BY postnumber, blog_id, DATE(dp_date) | |
| 1441 | + ) AS rollup_groups", | |
| 1442 | + $blog_id, | |
| 1443 | + $before_date | |
| 1444 | + ) | |
| 1445 | + ); | |
| 1446 | + if ( null === $rows_after ) { | |
| 1447 | + return new \WP_Error( 'tptn_rollup_projection_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Could not calculate the projected daily row count.', 'top-10' ) ); | |
| 1448 | + } | |
| 1449 | + | |
| 1450 | + $dates = $wpdb->get_var( | |
| 1451 | + $wpdb->prepare( | |
| 1452 | + "SELECT COUNT(DISTINCT DATE(dp_date)) FROM `{$table}` WHERE blog_id = %d AND dp_date < %s", | |
| 1453 | + $blog_id, | |
| 1454 | + $before_date | |
| 1455 | + ) | |
| 1456 | + ); | |
| 1457 | + if ( null === $dates ) { | |
| 1458 | + return new \WP_Error( 'tptn_rollup_dates_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Could not count daily rollup dates.', 'top-10' ) ); | |
| 1459 | + } | |
| 1460 | + // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 1461 | + | |
| 1462 | + return array( | |
| 1463 | + 'rows_before' => (int) $rows_before, | |
| 1464 | + 'rows_after' => (int) $rows_after, | |
| 1465 | + 'rows_reduced' => max( 0, (int) $rows_before - (int) $rows_after ), | |
| 1466 | + 'dates' => (int) $dates, | |
| 1467 | + ); | |
| 1468 | + } | |
| 1469 | + | |
| 1470 | + /** | |
| 1471 | + * Roll up hourly daily rows older than a date into one midnight row per post. | |
| 1472 | + * | |
| 1473 | + * Each calendar date is processed in its own transaction so an interrupted | |
| 1474 | + * operation can safely resume on the next date. The overall count table is | |
| 1475 | + * never modified. | |
| 1476 | + * | |
| 1477 | + * @since 4.5.0 | |
| 1478 | + * | |
| 1479 | + * @param string $before_date Rows before this date are rolled up. | |
| 1480 | + * @param int|null $blog_id Blog ID. Defaults to the current blog. | |
| 1481 | + * @return array|\WP_Error Rollup statistics or an error. | |
| 1482 | + */ | |
| 1483 | + public static function rollup_daily( string $before_date, $blog_id = null ) { | |
| 1484 | + global $wpdb; | |
| 1485 | + | |
| 1486 | + $before_date = self::normalize_daily_rollup_date( $before_date ); | |
| 1487 | + if ( is_wp_error( $before_date ) ) { | |
| 1488 | + return $before_date; | |
| 1489 | + } | |
| 1490 | + | |
| 1491 | + $blog_id = null === $blog_id ? get_current_blog_id() : absint( $blog_id ); | |
| 1492 | + $table = self::get_table( true ); | |
| 1493 | + $before = self::get_daily_rollup_stats( $before_date, $blog_id ); | |
| 1494 | + if ( is_wp_error( $before ) ) { | |
| 1495 | + return $before; | |
| 1496 | + } | |
| 1497 | + | |
| 1498 | + $last_date = ''; | |
| 1499 | + $dates_processed = 0; | |
| 1500 | + | |
| 1501 | + // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 1502 | + while ( true ) { | |
| 1503 | + // Select one unprocessed date at a time. Existing midnight rows are | |
| 1504 | + // already rolled up and are therefore skipped on subsequent runs. | |
| 1505 | + if ( '' === $last_date ) { | |
| 1506 | + $next_date = $wpdb->get_var( | |
| 1507 | + $wpdb->prepare( | |
| 1508 | + "SELECT DATE(dp_date) FROM `{$table}` WHERE blog_id = %d AND dp_date < %s AND TIME(dp_date) <> '00:00:00' ORDER BY dp_date ASC LIMIT 1", | |
| 1509 | + $blog_id, | |
| 1510 | + $before_date | |
| 1511 | + ) | |
| 1512 | + ); | |
| 1513 | + } else { | |
| 1514 | + $next_date_start = ( new \DateTimeImmutable( $last_date, new \DateTimeZone( 'UTC' ) ) )->modify( '+1 day' )->format( 'Y-m-d 00:00:00' ); | |
| 1515 | + $next_date = $wpdb->get_var( | |
| 1516 | + $wpdb->prepare( | |
| 1517 | + "SELECT DATE(dp_date) FROM `{$table}` WHERE blog_id = %d AND dp_date >= %s AND dp_date < %s AND TIME(dp_date) <> '00:00:00' ORDER BY dp_date ASC LIMIT 1", | |
| 1518 | + $blog_id, | |
| 1519 | + $next_date_start, | |
| 1520 | + $before_date | |
| 1521 | + ) | |
| 1522 | + ); | |
| 1523 | + } | |
| 1524 | + | |
| 1525 | + if ( null === $next_date ) { | |
| 1526 | + if ( ! empty( $wpdb->last_error ) ) { | |
| 1527 | + return new \WP_Error( 'tptn_rollup_date_failed', $wpdb->last_error ); | |
| 1528 | + } | |
| 1529 | + break; | |
| 1530 | + } | |
| 1531 | + | |
| 1532 | + $day_start = $next_date . ' 00:00:00'; | |
| 1533 | + $day_end = ( new \DateTimeImmutable( $next_date, new \DateTimeZone( 'UTC' ) ) )->modify( '+1 day' )->format( 'Y-m-d 00:00:00' ); | |
| 1534 | + $transaction_open = false; | |
| 1535 | + | |
| 1536 | + if ( false === $wpdb->query( 'START TRANSACTION' ) ) { | |
| 1537 | + return new \WP_Error( 'tptn_rollup_start_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Could not start the daily rollup transaction.', 'top-10' ) ); | |
| 1538 | + } | |
| 1539 | + $transaction_open = true; | |
| 1540 | + | |
| 1541 | + try { | |
| 1542 | + $daily_rows = $wpdb->get_results( | |
| 1543 | + $wpdb->prepare( | |
| 1544 | + "SELECT postnumber, cntaccess | |
| 1545 | + FROM `{$table}` | |
| 1546 | + WHERE blog_id = %d AND dp_date >= %s AND dp_date < %s | |
| 1547 | + ORDER BY postnumber ASC | |
| 1548 | + FOR UPDATE", | |
| 1549 | + $blog_id, | |
| 1550 | + $day_start, | |
| 1551 | + $day_end | |
| 1552 | + ), | |
| 1553 | + ARRAY_A | |
| 1554 | + ); | |
| 1555 | + if ( null === $daily_rows ) { | |
| 1556 | + return new \WP_Error( 'tptn_rollup_select_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Could not read the daily rows for the rollup.', 'top-10' ) ); | |
| 1557 | + } | |
| 1558 | + | |
| 1559 | + $rollup_counts = array(); | |
| 1560 | + foreach ( $daily_rows as $daily_row ) { | |
| 1561 | + $postnumber = (int) $daily_row['postnumber']; | |
| 1562 | + if ( ! isset( $rollup_counts[ $postnumber ] ) ) { | |
| 1563 | + $rollup_counts[ $postnumber ] = 0; | |
| 1564 | + } | |
| 1565 | + $rollup_counts[ $postnumber ] += (int) $daily_row['cntaccess']; | |
| 1566 | + } | |
| 1567 | + | |
| 1568 | + foreach ( array_chunk( $rollup_counts, 500, true ) as $rollup_batch ) { | |
| 1569 | + $values = array(); | |
| 1570 | + foreach ( $rollup_batch as $postnumber => $count ) { | |
| 1571 | + $values[] = $wpdb->prepare( | |
| 1572 | + '( %d, %d, %s, %d )', | |
| 1573 | + $postnumber, | |
| 1574 | + $count, | |
| 1575 | + $day_start, | |
| 1576 | + $blog_id | |
| 1577 | + ); | |
| 1578 | + } | |
| 1579 | + | |
| 1580 | + $result = $wpdb->query( | |
| 1581 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared | |
| 1582 | + "INSERT INTO `{$table}` (postnumber, cntaccess, dp_date, blog_id) VALUES " . implode( ',', $values ) . ' ON DUPLICATE KEY UPDATE cntaccess = VALUES(cntaccess)' | |
| 1583 | + ); | |
| 1584 | + if ( false === $result ) { | |
| 1585 | + return new \WP_Error( 'tptn_rollup_insert_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Could not write the daily rollup.', 'top-10' ) ); | |
| 1586 | + } | |
| 1587 | + } | |
| 1588 | + | |
| 1589 | + $result = $wpdb->query( | |
| 1590 | + $wpdb->prepare( | |
| 1591 | + "DELETE FROM `{$table}` WHERE blog_id = %d AND dp_date >= %s AND dp_date < %s AND dp_date <> %s", | |
| 1592 | + $blog_id, | |
| 1593 | + $day_start, | |
| 1594 | + $day_end, | |
| 1595 | + $day_start | |
| 1596 | + ) | |
| 1597 | + ); | |
| 1598 | + if ( false === $result ) { | |
| 1599 | + return new \WP_Error( 'tptn_rollup_delete_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Could not remove the hourly daily rows.', 'top-10' ) ); | |
| 1600 | + } | |
| 1601 | + | |
| 1602 | + if ( false === $wpdb->query( 'COMMIT' ) ) { | |
| 1603 | + return new \WP_Error( 'tptn_rollup_commit_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Could not commit the daily rollup.', 'top-10' ) ); | |
| 1604 | + } | |
| 1605 | + $transaction_open = false; | |
| 1606 | + } finally { | |
| 1607 | + if ( $transaction_open ) { | |
| 1608 | + $wpdb->query( 'ROLLBACK' ); | |
| 1609 | + } | |
| 1610 | + } | |
| 1611 | + ++$dates_processed; | |
| 1612 | + $last_date = $next_date; | |
| 1613 | + } | |
| 1614 | + // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 1615 | + | |
| 1616 | + $after = self::get_daily_rollup_stats( $before_date, $blog_id ); | |
| 1617 | + if ( is_wp_error( $after ) ) { | |
| 1618 | + return $after; | |
| 1619 | + } | |
| 1620 | + | |
| 1621 | + if ( $dates_processed > 0 ) { | |
| 1622 | + /** This action is documented in includes/class-database.php */ | |
| 1623 | + do_action( 'tptn_count_updated', 0, 0, false ); | |
| 1624 | + } | |
| 1625 | + | |
| 1626 | + return array( | |
| 1627 | + 'rows_before' => $before['rows_before'], | |
| 1628 | + 'rows_after' => $after['rows_before'], | |
| 1629 | + 'rows_reduced' => max( 0, $before['rows_before'] - $after['rows_before'] ), | |
| 1630 | + 'dates' => $after['dates'], | |
| 1631 | + 'dates_processed' => $dates_processed, | |
| 1632 | + ); | |
| 1633 | + } | |
| 1634 | + | |
| 1635 | + /** | |
| 1636 | + * Normalize a rollup boundary to midnight. | |
| 1637 | + * | |
| 1638 | + * @since 4.5.0 | |
| 1639 | + * | |
| 1640 | + * @param string $before_date Rollup boundary in Y-m-d or Y-m-d 00:00:00 format. | |
| 1641 | + * @return string|\WP_Error Normalized date or an error. | |
| 1642 | + */ | |
| 1643 | + private static function normalize_daily_rollup_date( string $before_date ) { | |
| 1644 | + $before_date = trim( $before_date, " \t\n\r\0\x0B" ); | |
| 1645 | + $date = preg_replace( '/ 00:00:00$/', '', $before_date ); | |
| 1646 | + | |
| 1647 | + if ( ! is_string( $date ) || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date ) ) { | |
| 1648 | + return new \WP_Error( 'tptn_invalid_rollup_date', __( 'The daily rollup boundary must be a valid date in Y-m-d format.', 'top-10' ) ); | |
| 1649 | + } | |
| 1650 | + | |
| 1651 | + $date_object = \DateTimeImmutable::createFromFormat( '!Y-m-d', $date, new \DateTimeZone( 'UTC' ) ); | |
| 1652 | + $errors = \DateTimeImmutable::getLastErrors(); | |
| 1653 | + if ( false === $date_object || ( is_array( $errors ) && ( $errors['warning_count'] > 0 || $errors['error_count'] > 0 ) ) ) { | |
| 1654 | + return new \WP_Error( 'tptn_invalid_rollup_date', __( 'The daily rollup boundary must be a valid date in Y-m-d format.', 'top-10' ) ); | |
| 1655 | + } | |
| 1656 | + | |
| 1657 | + return $date_object->format( 'Y-m-d 00:00:00' ); | |
| 1658 | + } | |
| 1659 | + | |
| 1660 | + /** | |
| 1085 | 1661 | * Count rows in the visits log table older than a given datetime. |
| 1086 | 1662 | * |
| 1087 | 1663 | * @since 4.3.0 |
| 1088 | 1664 | * |
| @@ -1139,14 +1715,26 @@ | ||
| 1139 | 1715 | */ |
| 1140 | 1716 | public static function count_orphan_counts( string $table_name ): int { |
| 1141 | 1717 | global $wpdb; |
| 1142 | 1718 | $blog_id = get_current_blog_id(); |
| 1719 | + /** | |
| 1720 | + * Filters the reserved post IDs used to store site-wide view counts. | |
| 1721 | + * | |
| 1722 | + * @since 4.5.0 | |
| 1723 | + * | |
| 1724 | + * @param int[] $context_ids Reserved context IDs. Default empty array. | |
| 1725 | + */ | |
| 1726 | + $context_ids = array_map( 'intval', (array) apply_filters( 'tptn_sitewide_context_ids', array() ) ); | |
| 1727 | + $context_where = ''; | |
| 1728 | + if ( $context_ids ) { | |
| 1729 | + $context_where = ' AND t.postnumber NOT IN (' . implode( ',', $context_ids ) . ')'; | |
| 1730 | + } | |
| 1143 | 1731 | // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 1144 | 1732 | return (int) $wpdb->get_var( |
| 1145 | 1733 | $wpdb->prepare( |
| 1146 | 1734 | "SELECT COUNT(*) FROM `{$table_name}` t |
| 1147 | 1735 | LEFT JOIN `{$wpdb->posts}` p ON t.postnumber = p.ID |
| 1148 | - WHERE p.ID IS NULL AND t.blog_id = %d", | |
| 1736 | + WHERE p.ID IS NULL AND t.blog_id = %d{$context_where}", | |
| 1149 | 1737 | $blog_id |
| 1150 | 1738 | ) |
| 1151 | 1739 | ); |
| 1152 | 1740 | // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| @@ -1166,14 +1754,20 @@ | ||
| 1166 | 1754 | */ |
| 1167 | 1755 | public static function delete_orphan_counts( string $table_name, int $batch_size = 1000 ) { |
| 1168 | 1756 | global $wpdb; |
| 1169 | 1757 | $blog_id = get_current_blog_id(); |
| 1758 | + /** This filter is documented in includes/class-database.php */ | |
| 1759 | + $context_ids = array_map( 'intval', (array) apply_filters( 'tptn_sitewide_context_ids', array() ) ); | |
| 1760 | + $context_where = ''; | |
| 1761 | + if ( $context_ids ) { | |
| 1762 | + $context_where = ' AND t.postnumber NOT IN (' . implode( ',', $context_ids ) . ')'; | |
| 1763 | + } | |
| 1170 | 1764 | // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 1171 | 1765 | return $wpdb->query( |
| 1172 | 1766 | $wpdb->prepare( |
| 1173 | 1767 | "DELETE t FROM `{$table_name}` t |
| 1174 | 1768 | LEFT JOIN `{$wpdb->posts}` p ON t.postnumber = p.ID |
| 1175 | - WHERE p.ID IS NULL AND t.blog_id = %d | |
| 1769 | + WHERE p.ID IS NULL AND t.blog_id = %d{$context_where} | |
| 1176 | 1770 | LIMIT %d", |
| 1177 | 1771 | $blog_id, |
| 1178 | 1772 | $batch_size |
| 1179 | 1773 | ) |
