prefix}improveab_ and binds every value, so the direct-query // and caching sniffs do not apply. The prepared-SQL sniffs are not switched // off for the file, so a query someone adds later that interpolates anything // else is reported. // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom tables, read and written directly on purpose; the prepared-SQL sniffs stay on. /** * Handles activation, table creation, scheduled rollup and pruning. */ class ImproveAB_Install { const RETENTION_DAYS = 90; // Seconds one nightly rollup may spend before leaving the rest for the // next night, well inside a typical 30 s max_execution_time. const ROLLUP_BUDGET = 20; // Past this many rows, a day is compacted an hour at a time: a window's // transaction cannot stop half way, so its size bounds how far a run can // go past its budget (the rows of an old day can lie far apart in the // table, where each costs a read). const ROLLUP_WINDOW_ROWS = 10000; // Every table the plugin owns, by the name after "{prefix}improveab_": // created by create_tables(), checked by ensure_tables(), dropped with a // deleted network site (drop_tables()) and by the opt-in wipe. "rl" // holds the /event rate limiter's counters; without it the limiter lets // every event through, so ensure_tables() rebuilds it like the others. const TABLES = array( 'tests', 'variants', 'events', 'daily', 'tally', 'buckets', 'rl' ); // The tally job (tally()): every ten minutes, at most this many seconds a // run, and this many events a transaction. const TALLY_INTERVAL = 600; const TALLY_BUDGET = 20; const TALLY_CHUNK = 50000; // An event id is tallied only once this many seconds have passed since // it was seen as the newest: by then every insert that took a lower id // has committed (see tally_horizon()). const TALLY_SETTLE = 300; // A reader on an admin screen runs the job itself, for this many // seconds, when no run has finished for this long (cron off or late). const TALLY_STALE = 1800; const TALLY_CATCH_UP = 3; // Seconds after which a lock row left by a run that died is taken over. const LOCK_STALE = 120; // The device classes /event stores; each is tallied beside the // every-device row, as the rollup does in daily. const DEVICES = array( 'desktop', 'mobile', 'tablet' ); /** * Activation hook. */ public static function activate( $network_wide = false ) { // Network activation on multisite: install per site, since tables, // options and cron are all site-scoped. A site created later sets // itself up on its first page load (maybe_upgrade()), and so does // every site of a large network: one request creating tables on // thousands of sites runs out of time half way, so only the site the // activation runs on is set up there. if ( $network_wide && is_multisite() && function_exists( 'get_sites' ) ) { if ( wp_is_large_network() || count( get_sites( array( 'fields' => 'ids', 'number' => 101 ) ) ) > 100 ) { self::activate_single(); return; } $site_ids = get_sites( array( 'fields' => 'ids', 'number' => 0 ) ); foreach ( $site_ids as $improveab_site ) { switch_to_blog( (int) $improveab_site ); self::activate_single(); restore_current_blog(); } return; } self::activate_single(); } /** * Install for the current site. */ private static function activate_single() { // Reactivating after a file update must run the same data migrations // maybe_upgrade() would: stamping the new version without them would // skip every version-guarded block for this site forever. $installed = get_option( 'improveab_db_version' ); // Before the schema is written: a site with no stored schema yet is // a new install, and there is nothing of an earlier one to look at. self::note_edition( is_string( $installed ) && '' !== $installed ); if ( self::schema_is_newer( $installed ) ) { // See maybe_upgrade(): only what is missing is created. self::ensure_tables(); } else { self::create_tables(); self::run_migrations( is_string( $installed ) ? $installed : '0' ); update_option( 'improveab_db_version', IMPROVEAB_DB_VERSION ); } self::flush_test_cache(); self::ensure_schedules(); self::ensure_capability(); } /** * Whether a later release of the plugin has already upgraded this site's * schema: an automatic update rolled back after the new version's first * request ran, a rollback plugin, an older zip uploaded again. * * The later schema is a superset of this one, and this version's dbDelta * would narrow every column it widened, silently cutting the data in it. * So this version then leaves the schema and the stored version alone, and * the later release finds both as it left them when it comes back. * * @param mixed $installed The stored improveab_db_version. * @return bool */ public static function schema_is_newer( $installed ) { return is_string( $installed ) && '' !== $installed && version_compare( $installed, IMPROVEAB_DB_VERSION, '>' ); } /** * Make sure administrators carry the manage-tests capability. Role caps * persist in the options table, so this runs on activation and upgrade * (and is cheap enough to be safe to call more often). * * @return void */ public static function ensure_capability() { $role = get_role( 'administrator' ); if ( $role && ! $role->has_cap( IMPROVEAB_CAP ) ) { $role->add_cap( IMPROVEAB_CAP ); // The current user's capability set was computed before the // grant; refresh it so the menu shows up on this very request. $user = wp_get_current_user(); if ( $user && $user->exists() ) { $user->get_role_caps(); } } } /** * Ensure the nightly rollup is scheduled and, on an admin page load, * that nothing else of the plugin's is scheduled with nobody to run it. * * The sweep runs on admin_init only, when every handler this copy of * the plugin has is registered: an event left by a version or a build * whose code is no longer here would otherwise wake WP-Cron for * nothing, every hour, for as long as the site exists. */ public static function ensure_schedules() { // Each site at its own moment of the day (and of the ten minutes): // on a network or a host with many sites, every rollup scheduled an // hour after one activation ran at the same instant every night. $jitter = absint( crc32( (string) get_current_blog_id() . '|' . home_url() ) ); if ( ! wp_next_scheduled( 'improveab_rollup' ) ) { wp_schedule_event( time() + HOUR_IN_SECONDS + $jitter % DAY_IN_SECONDS, 'daily', 'improveab_rollup' ); } if ( ! wp_next_scheduled( 'improveab_tally' ) ) { wp_schedule_event( time() + MINUTE_IN_SECONDS + $jitter % self::TALLY_INTERVAL, 'improveab_tally', 'improveab_tally' ); } if ( doing_action( 'admin_init' ) ) { self::clear_schedules( true ); } } /** * The tally job's ten-minute schedule (cron_schedules). * * @param array $schedules Schedules. * @return array */ public static function cron_schedules( $schedules ) { // Asked for as early as plugins_loaded (scheduling the job during an // upgrade), before translations may load; the name is only shown by // cron-viewing tools, which ask later. $schedules['improveab_tally'] = array( 'interval' => self::TALLY_INTERVAL, 'display' => did_action( 'init' ) ? __( 'Every ten minutes (ImproveAB)', 'improveab-ab-testing' ) : 'Every ten minutes (ImproveAB)', ); return $schedules; } /** * The plugin's tables of a network site being deleted (wpmu_drop_tables), * so they go with the site's own tables instead of staying behind. * * @param string[] $tables Tables core drops. * @param int $site_id The site being deleted. * @return string[] */ public static function drop_tables( $tables, $site_id = 0 ) { global $wpdb; $prefix = $site_id ? $wpdb->get_blog_prefix( (int) $site_id ) : $wpdb->prefix; foreach ( self::TABLES as $improveab_short ) { $tables[] = $prefix . 'improveab_' . $improveab_short; } return array_values( array_unique( (array) $tables ) ); } /** * Clear the schedule stored with a test when the test starts: its start * time, and an end time that has already passed (both are site-local * times). They belong to the run they were set for, not to the one * starting now. An end still ahead stays. Called by * ImproveAB_Tests::set_status() on every start. * * @param int $id Test ID. * @return void */ public static function consume_schedule( $id ) { global $wpdb; $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->prefix}improveab_tests SET schedule_start = NULL WHERE id = %d AND schedule_start IS NOT NULL", absint( $id ) ) ); $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->prefix}improveab_tests SET schedule_end = NULL WHERE id = %d AND schedule_end <= %s", absint( $id ), current_time( 'mysql' ) ) ); } /** * Unschedule the plugin's recurring events: every event whose hook * starts with the plugin's prefix, or with $orphans only those that no * code on this request handles. Hooks are cleared whatever arguments * they were scheduled with. * * @param bool $orphans Only the events nothing handles. * @return void */ public static function clear_schedules( $orphans = false ) { $hooks = array(); foreach ( (array) get_option( 'cron', array() ) as $events ) { if ( ! is_array( $events ) ) { continue; } foreach ( array_keys( $events ) as $hook ) { if ( is_string( $hook ) && 0 === strpos( $hook, 'improveab_' ) && ( ! $orphans || ! has_action( $hook ) ) ) { $hooks[ $hook ] = true; } } } foreach ( array_keys( $hooks ) as $hook ) { wp_unschedule_hook( $hook ); } } /** * Deactivation hook. Clears every event the plugin scheduled. * Cron is site-scoped, so a network deactivation walks every site the * same way activate() does; otherwise the other sites keep a recurring * event that fires into nothing. */ public static function deactivate( $network_wide = false ) { if ( $network_wide && is_multisite() && function_exists( 'get_sites' ) ) { $site_ids = get_sites( array( 'fields' => 'ids', 'number' => 0 ) ); foreach ( $site_ids as $improveab_site ) { switch_to_blog( (int) $improveab_site ); self::deactivate_single(); restore_current_blog(); } return; } self::deactivate_single(); } /** * Unschedule the plugin's events for the current site, and delete the * visitors' request counters, which only the nightly rollup would * otherwise sweep. */ private static function deactivate_single() { // Every occurrence, not just the next: two first admin requests racing // through ensure_schedules() can leave a duplicate behind. self::clear_schedules(); ImproveAB_REST::sweep_rate_counters( true ); } /** * Run schema upgrades when the stored DB version is behind. * * create_tables() is idempotent and additive (dbDelta), so it is safe to * run on every version change. run_migrations() handles anything dbDelta * cannot — data backfills and transforms — keyed on the previous version. */ public static function maybe_upgrade() { // Missing tables are recreated when one of the plugin's own screens // loads (ImproveAB_Admin::menu() hooks ensure_tables()). // A site moves between the editions by replacing the plugin's files, // so nothing runs at the switch itself: the first request after it // finds another stamp (note_edition()). $installed = get_option( 'improveab_db_version' ); if ( IMPROVEAB_EDITION !== get_option( 'improveab_edition' ) ) { self::note_edition( is_string( $installed ) && '' !== $installed ); } if ( IMPROVEAB_DB_VERSION === $installed || self::schema_is_newer( $installed ) ) { return; } // Advisory lock: right after the plugin files update, every request // sees the version mismatch at once. The lock is a plain INSERT // IGNORE against the unique option_name key, decided by the database // alone - add_option() is not atomic (it pre-checks with get_option() // and then upserts, so two requests can both "win") - and the lock // state is read straight from the table, since a persistent object // cache may still remember the option as missing. Exactly one request // runs the upgrade; the rest skip and simply serve with the old // schema for a moment. dbDelta and the migrations are idempotent // anyway - the lock just avoids a stampede of identical ALTER/UPDATE // work on busy sites. A crashed upgrade leaves a stale lock that is // taken over after 5 minutes. global $wpdb; $wpdb->query( $wpdb->prepare( "INSERT IGNORE INTO {$wpdb->options} (option_name, option_value, autoload) VALUES (%s, %s, 'no')", 'improveab_upgrading', (string) time() ) ); if ( 1 !== (int) $wpdb->rows_affected ) { $started = (int) $wpdb->get_var( $wpdb->prepare( "SELECT option_value FROM {$wpdb->options} WHERE option_name = %s", 'improveab_upgrading' ) ); if ( $started && ( time() - $started ) < 5 * MINUTE_IN_SECONDS ) { return; } $wpdb->update( $wpdb->options, array( 'option_value' => (string) time() ), array( 'option_name' => 'improveab_upgrading' ), array( '%s' ), array( '%s' ) ); } self::create_tables(); self::run_migrations( is_string( $installed ) ? $installed : '0' ); self::flush_test_cache(); update_option( 'improveab_db_version', IMPROVEAB_DB_VERSION ); self::ensure_schedules(); self::ensure_capability(); delete_option( 'improveab_upgrading' ); } /** * Record the edition that runs, and act on a move between the editions. * * Both editions read the same tables and options, so tests, results and * settings need nothing. What does change at a move: the visitor runtime * and its cached config differ between the editions, so every cached * test set is dropped and the page caches are asked to let go; and the * visitors counted so far were assigned by the code that ran before, so * every running, paused or stopped test's traffic split check counts * from the move on (ImproveAB_Tests::split_checks_from_now()). A site whose stored * schema predates the stamp is treated the same way, minus the page * caches: what ran there before is unknown. A new install has nothing to * restart or purge. * * The stamp is switched with a compare-and-swap on the options table, so * of the requests that arrive together after a move exactly one acts. * * @param bool $existing Whether the site held the plugin's schema before * this request. * @return void */ public static function note_edition( $existing ) { global $wpdb; $was = $wpdb->get_var( $wpdb->prepare( "SELECT option_value FROM {$wpdb->options} WHERE option_name = %s", 'improveab_edition' ) ); if ( IMPROVEAB_EDITION !== $was ) { if ( null === $was ) { $wpdb->query( $wpdb->prepare( "INSERT IGNORE INTO {$wpdb->options} (option_name, option_value, autoload) VALUES (%s, %s, 'yes')", 'improveab_edition', IMPROVEAB_EDITION ) ); } else { $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->options} SET option_value = %s WHERE option_name = %s AND option_value = %s", IMPROVEAB_EDITION, 'improveab_edition', $was ) ); } $won = 1 === (int) $wpdb->rows_affected; } else { $won = false; } // The options were written around the cache: a copy of the old stamp // would have this site ask again on every request. wp_cache_delete( 'alloptions', 'options' ); wp_cache_delete( 'notoptions', 'options' ); wp_cache_delete( 'improveab_edition', 'options' ); if ( ! $won ) { return; } $moved = in_array( $was, array( 'free', 'pro' ), true ); if ( ! $moved && ! $existing ) { return; } ImproveAB_Tests::split_checks_from_now(); ImproveAB_Tests::flush_cache(); if ( $moved ) { ImproveAB_Cache::purge_pages( 'edition' ); } } /** * Version-aware data migrations. * * The schema of DB 1.0.0, the first release's, is the whole of * create_tables(), so there is nothing to move yet. A later release that * needs to transform existing data adds a guarded block here, e.g.: * * if ( version_compare( $from, '1.1.0', '<' ) ) { ...backfill a column... } * * Each block must be idempotent: maybe_upgrade() runs this once per * version change under its lock, but a crashed or concurrent upgrade can * run it again. dbDelta has already added any new columns by the time * this runs, so blocks only move/derive data. * * @param string $from Previously installed DB version ('0' on first run). * @return void */ public static function run_migrations( $from ) { // phpcs:ignore Generic.CodeAnalysis.UnusedFunctionParameter.Found -- the hook point a later release's blocks use. // No step yet: every site runs the DB 1.0.0 schema or a later one. } /** * Recreate any plugin table that has gone missing. dbDelta only adds * what is absent, so existing tables and their data are untouched, and * the front-end sets are flushed because a failed read may have cached * an empty result while the table was gone. * * The tally's bookkeeping follows what came back empty. An events table * put back hands out ids from 1 again, under every watermark and the * horizon: the horizon starts afresh and every test's watermark goes to * 0, while the tally keeps the sums of the events that were lost, and * the new ones are counted on top. A tally or its slots put back hold * nothing of what the watermarks say was summed: every test is summed * again from its events, both tables emptied together so neither keeps * a share the other lost. * * @return bool True when every table was already present. */ public static function ensure_tables() { global $wpdb; $want = array(); foreach ( self::TABLES as $improveab_short ) { $want[ $improveab_short ] = strtolower( $wpdb->prefix . 'improveab_' . $improveab_short ); } $found = $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $wpdb->prefix . 'improveab_' ) . '%' ) ); // A read that failed lists nothing, which would read as every table // gone and reset the tally's bookkeeping below, on a screen view. It // says nothing about the tables: the next screen looks again. if ( '' !== $wpdb->last_error ) { return true; } // Compared in lower case: with lower_case_table_names=1 (Windows, some // managed hosts) SHOW TABLES reports lowercase names whatever the // prefix's case, and a mismatch here would re-run dbDelta on every // plugin screen. $have = array_intersect( $want, array_map( 'strtolower', (array) $found ) ); if ( count( $have ) === count( $want ) ) { return true; } $missing = array_keys( array_diff( $want, $have ) ); self::create_tables( self::schema_is_newer( get_option( 'improveab_db_version' ) ) ? array_values( $have ) : array() ); if ( in_array( 'tally', $missing, true ) || in_array( 'buckets', $missing, true ) ) { $wpdb->query( 'START TRANSACTION' ); $wpdb->query( "UPDATE {$wpdb->prefix}improveab_tests SET tallied_id = 0" ); $wpdb->query( "DELETE FROM {$wpdb->prefix}improveab_tally" ); $wpdb->query( "DELETE FROM {$wpdb->prefix}improveab_buckets" ); $wpdb->query( 'COMMIT' ); } if ( in_array( 'events', $missing, true ) ) { $wpdb->query( "UPDATE {$wpdb->prefix}improveab_tests SET tallied_id = 0" ); delete_option( 'improveab_tally_horizon' ); } if ( class_exists( 'ImproveAB_Tests' ) ) { ImproveAB_Tests::flush_cache(); ImproveAB_Tests::flush_data(); } return false; } /** * Drop the cached copy of the runtime's tests after the schema or the data * underneath it has moved. * * Migrations write to the tests table directly, so nothing goes through * ImproveAB_Tests and nothing invalidates its cache. On a site without a * persistent object cache that cache is an option with no expiry, so * without this a data migration would keep serving visitors the rows as * they were before the update - until the next time an administrator * happened to save a test. * * @return void */ private static function flush_test_cache() { if ( class_exists( 'ImproveAB_Tests' ) ) { ImproveAB_Tests::flush_cache(); } } /** * Create or update the plugin tables using dbDelta. * * @param string[] $leave Tables (lower case) to leave untouched: when a * later release's schema is in place, only a * missing table is created (ensure_tables()). */ public static function create_tables( $leave = array() ) { global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $charset_collate = $wpdb->get_charset_collate(); // `meta` is a free-form JSON slot for per-test state that belongs to a // feature rather than to the test model, so a feature that keeps // per-test state needs no schema change. NULL until something writes, // deleted with the test, and carried through a duplicate // (ImproveAB_Tests::sanitize_meta()). $sql_tests = "CREATE TABLE {$wpdb->prefix}improveab_tests ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, uuid char(36) NOT NULL DEFAULT '', name varchar(191) NOT NULL DEFAULT '', slug varchar(191) NOT NULL DEFAULT '', description text NULL, notes text NULL, type varchar(20) NOT NULL DEFAULT 'element', status varchar(20) NOT NULL DEFAULT 'draft', goal_type varchar(40) NOT NULL DEFAULT 'url', goal_config longtext NULL, weights longtext NULL, targeting longtext NULL, winner_variant varchar(40) NULL, serve_winner tinyint(1) NOT NULL DEFAULT 0, schedule_start datetime NULL, schedule_end datetime NULL, notified_winner tinyint(1) NOT NULL DEFAULT 0, notified_srm tinyint(1) NOT NULL DEFAULT 0, started_at datetime NULL, ended_at datetime NULL, meta longtext NULL, created_at datetime NOT NULL DEFAULT '1970-01-01 00:00:00', tallied_id bigint(20) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY slug (slug), KEY uuid (uuid), KEY status (status) ) {$charset_collate};"; $sql_variants = "CREATE TABLE {$wpdb->prefix}improveab_variants ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, test_id bigint(20) unsigned NOT NULL DEFAULT 0, variant_key varchar(40) NOT NULL DEFAULT '', label varchar(191) NOT NULL DEFAULT '', class_name varchar(191) NULL, url text NULL, edits longtext NULL, sort_order int(11) NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY test_id (test_id) ) {$charset_collate};"; // event_type is deliberately wider than the two values it holds today. // It is a column of uniq_event, so it is the one dimension a future // metric (a funnel step, a guardrail) can use - as "conversion:trial", // "step:checkout" - without that index ever having to change, and // widening a column that sits inside two indexes is a rebuild of the // largest table once sites carry data. value matches daily.revenue: // decimal(12,2) topped out just under ten billion, which a single // order reaches in IDR, VND or IRR, and WordPress runs MySQL without // strict mode, so the overflow clamped silently instead of failing. // 14,2 is the ceiling that keeps the amounts exact in PHP floats // (its cents are 9.99e13, still well inside 2^53). $sql_events = "CREATE TABLE {$wpdb->prefix}improveab_events ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, test_id bigint(20) unsigned NOT NULL DEFAULT 0, variant_key varchar(40) NOT NULL DEFAULT '', event_type varchar(40) NOT NULL DEFAULT '', visitor_token varchar(64) NOT NULL DEFAULT '', segment varchar(20) NOT NULL DEFAULT '', value decimal(14,2) NOT NULL DEFAULT 0, url varchar(255) NULL, created_at datetime NOT NULL DEFAULT '1970-01-01 00:00:00', PRIMARY KEY (id), UNIQUE KEY uniq_event (test_id,visitor_token,event_type), KEY lookup (test_id,variant_key,event_type), KEY created_at (created_at), KEY test_time (test_id,created_at), KEY test_tail (test_id,id) ) {$charset_collate};"; // The tally: every counted event of a test up to its `tallied_id` // (an events.id), summed once by tally() so no report reads a test's // whole history again. `tally` holds the lifetime sums, `buckets` the // same split into fifteen-minute UTC slots for the chart: every // time zone's offset and every clock change falls on a quarter hour, // so a slot never straddles a local midnight. Both carry daily's two // key dimensions with its meaning: metric '' is the primary goal, // segment '' every device, and a device class has its own row beside // that one. Revenue is lifetime here, so it gets more digits. $sql_tally = "CREATE TABLE {$wpdb->prefix}improveab_tally ( test_id bigint(20) unsigned NOT NULL DEFAULT 0, metric varchar(40) NOT NULL DEFAULT '', segment varchar(20) NOT NULL DEFAULT '', variant_key varchar(40) NOT NULL DEFAULT '', impressions bigint(20) unsigned NOT NULL DEFAULT 0, conversions bigint(20) unsigned NOT NULL DEFAULT 0, revenue decimal(20,2) NOT NULL DEFAULT 0, PRIMARY KEY (test_id,metric,segment,variant_key) ) {$charset_collate};"; $sql_buckets = "CREATE TABLE {$wpdb->prefix}improveab_buckets ( test_id bigint(20) unsigned NOT NULL DEFAULT 0, metric varchar(40) NOT NULL DEFAULT '', segment varchar(20) NOT NULL DEFAULT '', bucket datetime NOT NULL DEFAULT '1970-01-01 00:00:00', variant_key varchar(40) NOT NULL DEFAULT '', impressions bigint(20) unsigned NOT NULL DEFAULT 0, conversions bigint(20) unsigned NOT NULL DEFAULT 0, revenue decimal(20,2) NOT NULL DEFAULT 0, PRIMARY KEY (test_id,metric,segment,bucket,variant_key) ) {$charset_collate};"; // metric and segment are dimensions of the rollup key, not of the row's // numbers: '' in both means "this test's primary goal, all devices", // which is the only combination written today and the canonical total // every report sums. They exist from the first release because dbDelta // can add a column but cannot rewrite an index that already exists on // a site, and a per-step or per-segment daily aggregate has nowhere to // go without them. $sql_daily = "CREATE TABLE {$wpdb->prefix}improveab_daily ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, test_id bigint(20) unsigned NOT NULL DEFAULT 0, variant_key varchar(40) NOT NULL DEFAULT '', day date NOT NULL DEFAULT '1970-01-01', metric varchar(40) NOT NULL DEFAULT '', segment varchar(20) NOT NULL DEFAULT '', impressions bigint(20) unsigned NOT NULL DEFAULT 0, conversions bigint(20) unsigned NOT NULL DEFAULT 0, revenue decimal(14,2) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uniq_daily (test_id,variant_key,day,metric,segment) ) {$charset_collate};"; // The /event rate limiter's counters: one row per address, k // the keyed hash's first 60 bits, m the clock minute counted, n the // events in it. Rewritten in place each minute, swept nightly. $sql_rl = "CREATE TABLE {$wpdb->prefix}improveab_rl ( k bigint(20) unsigned NOT NULL DEFAULT 0, m int(10) unsigned NOT NULL DEFAULT 0, n int(10) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (k) ) {$charset_collate};"; $leave = array_map( 'strtolower', (array) $leave ); foreach ( array( 'tests' => $sql_tests, 'variants' => $sql_variants, 'events' => $sql_events, 'daily' => $sql_daily, 'tally' => $sql_tally, 'buckets' => $sql_buckets, 'rl' => $sql_rl ) as $improveab_short => $improveab_sql ) { if ( ! in_array( strtolower( $wpdb->prefix . 'improveab_' . $improveab_short ), $leave, true ) ) { dbDelta( $improveab_sql ); } } } /** * Roll up the old events of stopped tests into daily aggregates and prune * them, after clearing what deleted tests left behind. Totals stay exact: * every report reads the tally, the events past it and the daily rows * (CONTRACTS §2), and a window moved into daily takes its share of the * tally with it (rollup_window()). * * One run at a time: a second run started while one works (WP-Cron's own * lock lapses after a minute, and a site can hold the event twice) finds * the lock row and returns. A lock left by a run that died is taken over * after two minutes; the row lock in rollup_window() keeps even two runs * at once from counting a window twice. */ public static function rollup() { if ( ! self::lock( 'improveab_rollup_lock' ) ) { return; } self::rollup_run(); // The compacted windows (or a deleted test's rows) can have been the // newest events. self::clamp_watermarks(); self::unlock( 'improveab_rollup_lock' ); } /** * The work of one rollup() run. * * @return void */ private static function rollup_run() { global $wpdb; // One time budget for the whole run. A time budget stops the run // before PHP's own limit would kill it mid-day; the next night // carries on where this one stopped. $deadline = time() + self::ROLLUP_BUDGET; // Every counter of a past minute, however many addresses the day // saw: a fixed number of batches left the rest to pile up night after // night. if ( class_exists( 'ImproveAB_REST' ) ) { ImproveAB_REST::sweep_rate_counters( false, $deadline ); } $days = self::RETENTION_DAYS; if ( class_exists( 'ImproveAB_Settings' ) ) { $settings = ImproveAB_Settings::get(); $days = isset( $settings['retention_days'] ) ? (int) $settings['retention_days'] : self::RETENTION_DAYS; } // On a quarter hour, as every tally slot is: a window's slots then go // with it whole (rollup_window()). $cutoff = gmdate( 'Y-m-d H:i:s', self::quarter( time() - ( $days * DAY_IN_SECONDS ) ) ); // Only finished tests are compacted. A running (or paused, so // resumable) test needs its raw rows for correctness: a conversion is // accepted only against the visitor's stored impression, and the // unique index de-duplicates by visitor. Compacting those rows would // silently drop every late conversion from a visitor who first saw // the test before the window, and long tests on quiet sites are // exactly the ones that need every conversion. A stopped test takes // no beacon (/event counts running tests only), so its old rows can // be summarised. Two things still reach it, both from WooCommerce: // the conversion of an order placed while it ran and paid since (a // new row, matched to the visitor's impression; one the window has // compacted is no longer there to match, so only an order waiting // longer than the retention period goes uncounted), and a credited // order's revenue following the order (set_revenue(), which finds a // compacted conversion in its day). $stopped = array_map( 'intval', (array) $wpdb->get_col( "SELECT id FROM {$wpdb->prefix}improveab_tests WHERE status = 'stopped' ORDER BY id ASC" ) ); // One stopped test at a time, walked on its own (test_id, created_at) // index, so the nightly cost is the rows being compacted and never // the running tests' rows beside them, and every lock stays on one // test's rows. A test with nothing old left costs one index probe. // Rows of deleted tests that deleting did not finish (a very big // test), cleared first within the same budget. if ( ! self::clear_orphans( $deadline ) ) { return; } foreach ( $stopped as $test_id ) { // Day by day from the oldest row, each day found by one probe of // the index (its first row past the last day done). No event type // is named here: the index holds none, and a day of rows this // version does not count is simply passed on the way. $cursor = '1000-01-01 00:00:00'; while ( true ) { if ( time() >= $deadline ) { return; } $first = $wpdb->get_var( $wpdb->prepare( "SELECT MIN(created_at) FROM {$wpdb->prefix}improveab_events WHERE test_id = %d AND created_at >= %s AND created_at < %s", $test_id, $cursor, $cutoff ) ); if ( ! is_string( $first ) || ! preg_match( '/^(\d{4}-\d{2}-\d{2}) /', $first, $m ) ) { break; } $day = $m[1]; // Half-open [day 00:00, next day 00:00) bounds, capped at the // cutoff, so the aggregate and the deletes walk the index // instead of evaluating DATE() on every row. All UTC, like // created_at itself (gmdate + a fixed 86400 s, so no DST drift). $from = $day . ' 00:00:00'; $to = gmdate( 'Y-m-d 00:00:00', strtotime( $from . ' UTC' ) + DAY_IN_SECONDS ); if ( $to > $cutoff ) { $to = $cutoff; } // A day too big for one transaction is taken an hour at a // time, all into the same daily row: a single transaction // that could not finish inside the time limit would roll // back every night and never converge. Whether it is that big // is one walk of the index to its 10,001st entry, not a count // of every row. $windows = array( array( $from, $to ) ); if ( $wpdb->get_var( $wpdb->prepare( "SELECT 1 FROM {$wpdb->prefix}improveab_events WHERE test_id = %d AND created_at >= %s AND created_at < %s LIMIT 1 OFFSET %d", $test_id, $from, $to, self::ROLLUP_WINDOW_ROWS ) ) ) { $windows = array(); for ( $h = strtotime( $from . ' UTC' ); $h < strtotime( $to . ' UTC' ); $h += HOUR_IN_SECONDS ) { $windows[] = array( gmdate( 'Y-m-d H:i:s', $h ), min( $to, gmdate( 'Y-m-d H:i:s', $h + HOUR_IN_SECONDS ) ) ); } } foreach ( $windows as $w ) { if ( time() >= $deadline || ! self::rollup_window( $test_id, $day, $w[0], $w[1] ) ) { return; } } $cursor = $to; } } } /** * Clear the rows of deleted tests (events and tally slots, which a big * test's deletion leaves to be cleared in steps, and any tally row) until * none are left or the deadline passes. * * Each test id that holds rows is found by one seek of an index that * starts with test_id (the next id above the last one seen), and the ids * are compared with the tests table in PHP: joining every event with the * tests table read the whole table every night. * * @param int $deadline Unix time to stop at. * @return bool True when nothing is left. */ public static function clear_orphans( $deadline ) { global $wpdb; $have = array_flip( array_map( 'intval', (array) $wpdb->get_col( "SELECT id FROM {$wpdb->prefix}improveab_tests" ) ) ); if ( '' !== $wpdb->last_error ) { return false; } foreach ( array( 'tally', 'buckets', 'events' ) as $improveab_short ) { $id = -1; while ( true ) { if ( 'tally' === $improveab_short ) { $next = $wpdb->get_var( $wpdb->prepare( "SELECT MIN(test_id) FROM {$wpdb->prefix}improveab_tally WHERE test_id > %d", $id ) ); } elseif ( 'buckets' === $improveab_short ) { $next = $wpdb->get_var( $wpdb->prepare( "SELECT MIN(test_id) FROM {$wpdb->prefix}improveab_buckets WHERE test_id > %d", $id ) ); } else { $next = $wpdb->get_var( $wpdb->prepare( "SELECT MIN(test_id) FROM {$wpdb->prefix}improveab_events WHERE test_id > %d", $id ) ); } if ( null === $next || '' !== $wpdb->last_error ) { break; } $id = (int) $next; // Read again: a test created while this ran is not an orphan. if ( isset( $have[ $id ] ) || $wpdb->get_var( $wpdb->prepare( "SELECT 1 FROM {$wpdb->prefix}improveab_tests WHERE id = %d", $id ) ) ) { continue; } if ( 'tally' === $improveab_short ) { $wpdb->delete( $wpdb->prefix . 'improveab_tally', array( 'test_id' => $id ), array( '%d' ) ); } elseif ( ! ImproveAB_Tests::delete_events( $id, $deadline ) ) { return false; } if ( time() >= $deadline ) { return false; } } } delete_option( 'improveab_orphans' ); return true; } /** * Compact one stopped test's events in [from, to) into its daily rows * for $day and delete them, inside one transaction: either the window's * events are both added to daily and deleted, or neither happened and * the next run redoes it, so a crash can never double count. (On a * storage engine without transactions this simply degrades to the * statements running one by one.) * * The window's events the tally job has already summed (an id up to the * test's tallied_id) leave the tally in the same transaction: their * numbers come off the test's tally rows and the window's fifteen-minute * slots are deleted (a window starts and ends on a quarter hour, so its * slots hold its events and nothing else), so a report reading tally, * events and daily counts each event once before, during and after. * * The database may pick the window's batched DELETE as a deadlock's * victim (it meets /event inserts on a busy site) or give up waiting * for a lock there. That is the database asking for the transaction * again, so the window is rolled back and run again, up to three * times, and those refusals are not logged as errors; a window still * refused after the third try is logged once, as WordPress logs a * database error, and left for the next run. Only that DELETE runs * with wpdb's error output held back: every other statement of the * window (the transaction's own START, COMMIT and ROLLBACK included), * and the DELETE failing for any other reason, is logged by wpdb as it * happens, and the window is left for the next run. * * @param int $test_id Stopped test. * @param string $day Y-m-d the window belongs to. * @param string $from Window start (inclusive), UTC. * @param string $to Window end (exclusive), UTC. * @return bool False when a statement failed and the window was rolled back. */ private static function rollup_window( $test_id, $day, $from, $to ) { global $wpdb; for ( $try = 1; ; $try++ ) { self::$window_failure = null; if ( self::rollup_window_once( $test_id, $day, $from, $to ) ) { return true; } // Set only when the DELETE was refused for a lock and its error // held back; any other failure was logged as it happened. $failure = self::$window_failure; if ( ! $failure ) { return false; } if ( $try < 3 ) { usleep( 100000 * $try ); continue; } self::log_failure( $failure ); return false; } } /** * The DELETE's lock refusal held back by the window's last try (its * error, its query and, on MySQL / MariaDB, the error number), read * before the rollback, which clears them; null when the try did not * end on one. * * @var array|null */ private static $window_failure = null; /** * The statement that just failed: its error, its query and the * connection's own error number where it has one (mysqli does). * * @return array */ private static function statement_failure() { global $wpdb; $dbh = $wpdb->dbh; return array( 'error' => (string) $wpdb->last_error, 'query' => (string) $wpdb->last_query, 'errno' => is_object( $dbh ) && isset( $dbh->errno ) ? (int) $dbh->errno : 0, ); } /** * Log a failure whose error was held back, as wpdb would have logged * it, with the statement that failed (print_error() honours a caller's * own suppress_errors()). * * @param array $failure As statement_failure() noted it. */ private static function log_failure( $failure ) { global $wpdb; $wpdb->last_query = $failure['query']; $wpdb->print_error( $failure['error'] ); } /** * Whether a failure is the database asking for the transaction again: a * deadlock (1213) or a lock wait that timed out (1205), read by number * where the connection has one (the message can be in the server's * language) and by MySQL's words otherwise; SQLite's busy database too. * * @param array $failure As statement_failure() noted it. * @return bool */ private static function lock_conflict( $failure ) { if ( in_array( $failure['errno'], array( 1205, 1213 ), true ) ) { return true; } return 0 === $failure['errno'] && 1 === preg_match( '/Deadlock found|Lock wait timeout|database is locked/i', $failure['error'] ); } /** * One try at rollup_window(). * * @param int $test_id Stopped test. * @param string $day Y-m-d the window belongs to. * @param string $from Window start (inclusive), UTC. * @param string $to Window end (exclusive), UTC. * @return bool False when a statement failed and the window was rolled back. */ private static function rollup_window_once( $test_id, $day, $from, $to ) { global $wpdb; // Every statement but the batched DELETE below is logged by wpdb as // it fails; a failure leaves the window for the next run. A window // run without its transaction could be half done when a later // statement failed, so a refused START ends it here. if ( false === $wpdb->query( 'START TRANSACTION' ) ) { return false; } // The test's row is locked before anything is read. A second rollup // (two runs at once, or one a dead run's lock was taken from) or the // tally job working on the same test waits here until this window is // committed, and then reads it as committed: the window's events // gone, the tally moved. Read one after the other without it, two // runs both summed the same rows and both added them to daily. $ok = false !== $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->prefix}improveab_tests SET tallied_id = tallied_id WHERE id = %d", $test_id ) ); $tallied = $ok ? (int) $wpdb->get_var( $wpdb->prepare( "SELECT tallied_id FROM {$wpdb->prefix}improveab_tests WHERE id = %d", $test_id ) ) : 0; // A failed read answers null, read as 0: nothing tallied, so the // tally would keep the window's events that daily is given too. if ( $ok && '' !== $wpdb->last_error ) { $ok = false; } // Only the two types this version counts are summed and deleted. The // event_type column is wide for a reason (CONTRACTS reserves // "conversion:" and "step:"): rows a later release wrote, met // here after a rollback, stay for the release that understands them, // and deleting a test still removes them. // Grouped by device class as well: each group adds to the canonical // '' row (every device) and, when it names a class, is written once // more as that class's own row beside it. The raw rows are deleted // below, so this is the only moment a finished test's device split // can be kept. The t_ sums are the part the tally job has counted. $rows = $ok ? $wpdb->get_results( $wpdb->prepare( "SELECT variant_key, segment, SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END) AS impressions, SUM(CASE WHEN event_type = 'conversion' THEN 1 ELSE 0 END) AS conversions, SUM(CASE WHEN event_type = 'conversion' THEN value ELSE 0 END) AS revenue, SUM(CASE WHEN id <= %d AND event_type = 'impression' THEN 1 ELSE 0 END) AS t_impressions, SUM(CASE WHEN id <= %d AND event_type = 'conversion' THEN 1 ELSE 0 END) AS t_conversions, SUM(CASE WHEN id <= %d AND event_type = 'conversion' THEN value ELSE 0 END) AS t_revenue FROM {$wpdb->prefix}improveab_events WHERE test_id = %d AND created_at >= %s AND created_at < %s AND event_type IN ('impression','conversion') GROUP BY variant_key, segment", $tallied, $tallied, $tallied, $test_id, $from, $to ), ARRAY_A ) : array(); // get_results() returns an empty array on failure too; the error // buffer is the only reliable signal. if ( $ok && '' !== $wpdb->last_error ) { $ok = false; } foreach ( (array) $rows as $row ) { if ( ! $ok ) { break; } // The canonical row ('' metric, '' segment: the primary goal // across every device) is what every headline number reads, so // every group adds to it. A known device class is stored once // more as its own row, which only a reader pinned to that class // ever sums. ON DUPLICATE KEY UPDATE matches on the whole // five-column unique key. $segment = (string) $row['segment']; $targets = in_array( $segment, self::DEVICES, true ) ? array( '', $segment ) : array( '' ); foreach ( $targets as $seg ) { $inserted = $wpdb->query( $wpdb->prepare( "INSERT INTO {$wpdb->prefix}improveab_daily (test_id, variant_key, day, metric, segment, impressions, conversions, revenue) VALUES (%d, %s, %s, %s, %s, %d, %d, %f) ON DUPLICATE KEY UPDATE impressions = impressions + VALUES(impressions), conversions = conversions + VALUES(conversions), revenue = revenue + VALUES(revenue)", $test_id, $row['variant_key'], $day, '', $seg, $row['impressions'], $row['conversions'], $row['revenue'] ) ); if ( false === $inserted ) { $ok = false; break; } if ( (int) $row['t_impressions'] > 0 || (int) $row['t_conversions'] > 0 || (float) $row['t_revenue'] > 0 ) { // Never below zero, so a hand-edited table cannot make // the statement fail and hold the window back forever. $taken = $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->prefix}improveab_tally SET impressions = CASE WHEN impressions > %d THEN impressions - %d ELSE 0 END, conversions = CASE WHEN conversions > %d THEN conversions - %d ELSE 0 END, revenue = CASE WHEN revenue > %f THEN revenue - %f ELSE 0 END WHERE test_id = %d AND metric = '' AND segment = %s AND variant_key = %s", $row['t_impressions'], $row['t_impressions'], $row['t_conversions'], $row['t_conversions'], $row['t_revenue'], $row['t_revenue'], $test_id, $seg, $row['variant_key'] ) ); if ( false === $taken ) { $ok = false; break; } } } } if ( $ok && $tallied > 0 ) { $ok = false !== $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}improveab_buckets WHERE test_id = %d AND metric = '' AND segment IN ('','desktop','mobile','tablet') AND bucket >= %s AND bucket < %s", $test_id, $from, $to ) ); } // The one statement whose error is held back: a deadlock or a lock // wait here is rolled back and run again (rollup_window()), and // logged only when it keeps coming; any other failure is logged // at once, as wpdb would have. while ( $ok ) { $quiet = $wpdb->suppress_errors( true ); $deleted = $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}improveab_events WHERE test_id = %d AND created_at >= %s AND created_at < %s AND event_type IN ('impression','conversion') LIMIT 10000", $test_id, $from, $to ) ); $wpdb->suppress_errors( $quiet ); if ( false === $deleted ) { $ok = false; $failure = self::statement_failure(); if ( self::lock_conflict( $failure ) ) { self::$window_failure = $failure; } else { self::log_failure( $failure ); } } elseif ( $deleted < 10000 ) { break; } } // A failed statement (a lock-wait timeout, say) only rolls back that // statement, so committing here would keep the window's rows in // daily while its events survive - double counting from then on. // Roll the window back instead and leave the backlog for the next // run. if ( ! $ok ) { $wpdb->query( 'ROLLBACK' ); return false; } // A refused COMMIT has kept nothing: the window is rolled back and // left for the next run, never reported as compacted. if ( false === $wpdb->query( 'COMMIT' ) ) { $wpdb->query( 'ROLLBACK' ); return false; } return true; } /* --------------------------------------------------------------------- */ /* The tally */ /* --------------------------------------------------------------------- */ /** * The tally job (cron `improveab_tally`, every ten minutes): sum each * test's events past its tallied_id into the tally, in transactions of * at most TALLY_CHUNK events, so a report reads a few tally rows and the * last minutes of events instead of the test's whole history. * * Events are never changed or deleted here, so de-duplication and late * conversions work as before; the tally is a running sum beside them. * Only ids under the settled horizon are summed (tally_horizon()): an * insert still in flight below it would otherwise be skipped for good. * One run at a time (a lock row, taken over after two minutes), and * each window claims the test's watermark before it adds anything * (tally_window()), so even two runs at once never add a window twice. * * @param int $budget Seconds this run may spend (the default when empty). * @return void */ public static function tally( $budget = self::TALLY_BUDGET ) { if ( ! self::lock( 'improveab_tally_lock' ) ) { return; } global $wpdb; // The cron hook passes an empty argument. $deadline = time() + ( (int) $budget > 0 ? (int) $budget : self::TALLY_BUDGET ); $safe = self::tally_horizon(); if ( $safe > 0 ) { // Drafts record nothing. Every other test is one probe when it has // nothing new, and the most behind go first. $rows = $wpdb->get_results( $wpdb->prepare( "SELECT id, tallied_id FROM {$wpdb->prefix}improveab_tests WHERE status <> 'draft' AND tallied_id < %d ORDER BY tallied_id ASC", $safe ), ARRAY_A ); foreach ( (array) $rows as $row ) { if ( time() >= $deadline ) { break; } self::tally_test( (int) $row['id'], (int) $row['tallied_id'], $safe, $deadline ); } // Every test with nothing left to sum up to the horizon (drafts, // stopped tests, quiet ones) moves its watermark there too, so // the lowest watermark stays near the newest events and a report // on many tests reads only the events past it (one range of the // primary key) instead of joining every event with its test. $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->prefix}improveab_tests SET tallied_id = %d WHERE tallied_id < %d AND NOT EXISTS (SELECT 1 FROM {$wpdb->prefix}improveab_events e WHERE e.test_id = {$wpdb->prefix}improveab_tests.id AND e.id > {$wpdb->prefix}improveab_tests.tallied_id AND e.id <= %d)", $safe, $safe, $safe ) ); } // A deletion while this run worked can have taken the newest events // after the run read its horizon, and the moves above then put // watermarks past the newest id there is: brought back down here, // before a server that restarts hands those ids out again. self::clamp_watermarks(); // A deletion that left rows behind: cleared with what is left of the // run, so a big test's rows go every ten minutes rather than only in // the nightly rollup's twenty seconds. if ( time() < $deadline && get_option( 'improveab_orphans' ) ) { self::clear_orphans( $deadline ); } self::unlock( 'improveab_tally_lock' ); } /** * Keep every tally watermark, and the horizon, at or under the newest * event there is, after events were deleted (a test deleted, a rollup * window compacted, what a deletion left cleared). * * When the deleted rows were the newest, the ids above the newest one * left can be handed out again: MySQL 5.7 and older, and MariaDB before * 10.2.4, set the counter to MAX(id) + 1 when they restart. A watermark * left above MAX(id) would hide every visit that gets one of those ids, * for good. No row exists between MAX(id) and such a watermark, so * bringing it down to MAX(id) counts nothing twice and loses nothing. * The horizon keeps its high-water mark (`high`, the highest id ever * seen), so the next tally run puts the counter back above it * (raise_counter()). * * @return void */ public static function clamp_watermarks() { global $wpdb; $before = (string) $wpdb->get_var( 'SELECT UTC_TIMESTAMP()' ); $newest = $wpdb->get_var( "SELECT MAX(id) FROM {$wpdb->prefix}improveab_events" ); if ( '' !== $wpdb->last_error ) { return; } $newest = (int) $newest; // Looked for first, in one read of a small table: the update walks // every test's row and locks it, and almost always has nothing to do. if ( (int) $wpdb->get_var( "SELECT MAX(tallied_id) FROM {$wpdb->prefix}improveab_tests" ) > $newest ) { self::lower_watermarks( $newest, $before ); } $horizon = self::horizon_stored(); if ( $horizon['safe'] > $newest || $horizon['pending'] > $newest ) { $horizon['safe'] = min( $horizon['safe'], $newest ); $horizon['pending'] = $horizon['pending'] > $newest ? 0 : $horizon['pending']; update_option( 'improveab_tally_horizon', $horizon, false ); } } /** * Bring every watermark above $newest down to it, where the ids above it * are really gone: only a test with no event between $newest and its * watermark written before $before (the database's clock, read before * $newest was) is lowered. * * $newest is one read, and a read can be wrong without failing: a * replica behind the primary (HyperDB, LudicrousDB and the like send * reads there) answers an older id while the events above it are stored * and summed. Lowering a watermark under summed events would have the * job sum them again and every report count them twice, for good. The * update and its look at the events run on the primary together, so an * event older than the read that holds an id above $newest proves the * read wrong and keeps the watermark. A real reset leaves no such * event: the rows above $newest were deleted, and the ones written * since (a restarted server handing their ids out again) are newer * than $before. * * Each test is lowered by its own key, so an update touches its one row * and looks at the events above $newest along the primary key (named: * MySQL 8 planned KEY created_at instead, a range over every event older * than $before, which a real reset reads to the end to find none). Which * tests to look at comes from one read of the small tests table; the * update checks again. * * @param int $newest The newest event id read. * @param string $before The database's time (UTC) read before it. * @return void */ private static function lower_watermarks( $newest, $before ) { global $wpdb; // Without the time no event can be judged: nothing is lowered, and // the next run looks again. if ( ! preg_match( '/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $before ) ) { return; } foreach ( (array) $wpdb->get_results( "SELECT id, tallied_id FROM {$wpdb->prefix}improveab_tests", ARRAY_A ) as $row ) { if ( (int) $row['tallied_id'] > $newest ) { $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->prefix}improveab_tests SET tallied_id = %d WHERE id = %d AND tallied_id > %d AND NOT EXISTS (SELECT 1 FROM {$wpdb->prefix}improveab_events e USE INDEX (PRIMARY) WHERE e.id > %d AND e.id <= {$wpdb->prefix}improveab_tests.tallied_id AND e.created_at < %s)", $newest, (int) $row['id'], $newest, $newest, $before ) ); } } } /** * The stored horizon, with defaults: `high` (the highest id the job has * seen) is at least the settled and the pending id. * * @return array { safe, pending, at, high, ran } */ private static function horizon_stored() { $stored = get_option( 'improveab_tally_horizon' ); $horizon = array(); foreach ( array( 'safe', 'pending', 'at', 'high', 'ran' ) as $key ) { $horizon[ $key ] = is_array( $stored ) && isset( $stored[ $key ] ) ? (int) $stored[ $key ] : 0; } $horizon['high'] = max( $horizon['high'], $horizon['safe'], $horizon['pending'] ); return $horizon; } /** * Make the events table's counter hand out ids above $high again, after * a server that restarted set it back to MAX(id) + 1 (see * clamp_watermarks()). A row with the id $high + 1 is written and taken * away in one transaction, which no reader ever sees: every engine moves * its counter past an id written explicitly and never moves it back * (MySQL 8 lowers its counter on ALTER TABLE ... AUTO_INCREMENT, which * could hand out again an id an insert still in flight holds), and the * row is ignored when an event already holds that id. * * @param int $high The highest id ever handed out. * @return void */ private static function raise_counter( $high ) { global $wpdb; $wpdb->query( 'START TRANSACTION' ); $wpdb->query( $wpdb->prepare( "INSERT IGNORE INTO {$wpdb->prefix}improveab_events (id, test_id, variant_key, event_type, visitor_token, segment, value, url, created_at) VALUES (%d, 0, '', '', 'improveab-counter', '', 0, '', UTC_TIMESTAMP())", (int) $high + 1 ) ); $wpdb->query( "DELETE FROM {$wpdb->prefix}improveab_events WHERE test_id = 0 AND visitor_token = 'improveab-counter'" ); $wpdb->query( 'COMMIT' ); } /** * Run the tally job from a report when no run has finished for half an * hour (WP-Cron switched off, or a site too quiet to trigger it), for a * few seconds, once a request. Only on admin screens, REST calls, cron * and WP-CLI: a visitor's page never pays for it. * * @return void */ public static function catch_up() { static $done = false; if ( $done ) { return; } $done = true; if ( ! ( is_admin() || wp_doing_cron() || ( defined( 'REST_REQUEST' ) && REST_REQUEST ) || ( defined( 'WP_CLI' ) && WP_CLI ) ) ) { return; } $horizon = get_option( 'improveab_tally_horizon' ); $ran = is_array( $horizon ) && isset( $horizon['ran'] ) ? (int) $horizon['ran'] : 0; if ( time() - $ran < self::TALLY_STALE && $ran <= time() ) { return; } self::tally( self::TALLY_CATCH_UP ); } /** * The highest event id every insert below has committed by: the newest * id seen at least TALLY_SETTLE seconds ago. An id is taken when its * insert starts and is visible only once it commits, so the newest id * visible now can have lower ids still in flight; by TALLY_SETTLE later * every insert that took a lower one has finished. Each run promotes the * id it saw last time once it is old enough and notes the newest one * for the next. Times come from the database's clock, so several web * servers with clocks apart agree. * * Event ids going backwards are a reset. The horizon keeps the highest * id it has seen (`high`); a newest id under it means the newest events * are gone (a deleted test held them, the rollup compacted them, rows * were removed by hand or a table restored) and their ids can be handed * out again. Nothing may then stay above the newest id: every watermark * and the horizon come down to it, as clamp_watermarks() does after the * plugin's own deletions, and the counter is put back above `high` * (raise_counter()), so a server that restarted since hands out ids * under it no longer than until this run. * * Only a read that answered is a reset. A read that failed (a lost * connection, a killed query, a proxy's error) answers nothing, not an * empty table: taken for 0 it lowered every watermark to 0 while the * tally kept its sums, and every event was counted twice from then on. * It changes nothing and the stored horizon stands. A read that answers * an older id than there is (a replica behind) lowers no watermark over * events that exist (lower_watermarks()). * * @return int The settled id (0 until the first one has settled). */ private static function tally_horizon() { global $wpdb; $before = (string) $wpdb->get_var( 'SELECT UTC_TIMESTAMP()' ); $now = (int) strtotime( $before . ' UTC' ); $now = $now > 0 ? $now : time(); $newest = $wpdb->get_var( "SELECT MAX(id) FROM {$wpdb->prefix}improveab_events" ); // Taken at once: reading the stored horizon can query the options // table, and every query clears the last error. $failed = '' !== $wpdb->last_error; $horizon = self::horizon_stored(); if ( $failed ) { return $horizon['safe']; } $newest = (int) $newest; if ( $newest < $horizon['high'] ) { self::lower_watermarks( $newest, $before ); $horizon['safe'] = min( $horizon['safe'], $newest ); $horizon['pending'] = $horizon['pending'] > $newest ? 0 : $horizon['pending']; self::raise_counter( $horizon['high'] ); } if ( $horizon['pending'] > 0 && ( $now - $horizon['at'] >= self::TALLY_SETTLE || $horizon['at'] > $now ) ) { $horizon['safe'] = max( $horizon['safe'], $horizon['pending'] ); $horizon['pending'] = 0; } if ( 0 === $horizon['pending'] && $newest > $horizon['safe'] ) { $horizon['pending'] = $newest; $horizon['at'] = $now; } $horizon['high'] = max( $horizon['high'], $newest ); $horizon['ran'] = time(); update_option( 'improveab_tally_horizon', $horizon, false ); return $horizon['safe']; } /** * Tally one test's events past its watermark, up to the settled id, a * window of at most TALLY_CHUNK events at a time. * * @param int $test_id Test ID. * @param int $from Its tallied_id. * @param int $safe The settled id (tally_horizon()). * @param int $deadline Unix time to stop at. * @return bool True when the test is tallied up to $safe. */ private static function tally_test( $test_id, $from, $safe, $deadline ) { global $wpdb; while ( time() < $deadline ) { // Most tests have nothing new: one probe of (test_id, id). if ( ! $wpdb->get_var( $wpdb->prepare( "SELECT 1 FROM {$wpdb->prefix}improveab_events WHERE test_id = %d AND id > %d AND id <= %d LIMIT 1", $test_id, $from, $safe ) ) ) { return true; } // The window's last id: TALLY_CHUNK entries along (test_id, id) // from the watermark, capped at the horizon in PHP. Ordered by // both columns and bounded on one side only, so the database // walks that index from the watermark; bounded on both, over a // test's whole history it read another index and sorted every id. // Named, because MySQL 8 with current statistics picked KEY // lookup (a skip scan) and sorted every remaining row of the test // instead, a cost growing with the square of the test's size. $end = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}improveab_events USE INDEX (test_tail) WHERE test_id = %d AND id > %d ORDER BY test_id ASC, id ASC LIMIT 1 OFFSET %d", $test_id, $from, self::TALLY_CHUNK - 1 ) ); $end = $end > $from ? min( $end, $safe ) : $safe; if ( ! self::tally_window( $test_id, $from, $end ) ) { return false; } if ( $end >= $safe ) { return true; } $from = $end; } return false; } /** * Add one test's events with an id in (from, end] to its tally and its * fifteen-minute slots, and move its watermark to end, in one * transaction: a report, which reads the watermark, the tally and the * events past it in one statement, counts every event once whether it * reads before or after the commit. * * The watermark is claimed first, with an update that only matches the * value this run started from: a second run working on the same test * waits for this one, then finds the watermark moved and adds nothing. * * @param int $test_id Test ID. * @param int $from Watermark this window starts after. * @param int $end Last id of the window. * @return bool */ private static function tally_window( $test_id, $from, $end ) { global $wpdb; $wpdb->query( 'START TRANSACTION' ); $claimed = $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->prefix}improveab_tests SET tallied_id = %d WHERE id = %d AND tallied_id = %d", $end, $test_id, $from ) ); if ( 1 !== (int) $claimed ) { $wpdb->query( 'ROLLBACK' ); return false; } // Grouped by quarter hour with string functions MySQL, MariaDB and // the SQLite drop-in all answer alike, as the chart's own day cuts // are (created_at reads 'Y-m-d H:i:s'). $rows = $wpdb->get_results( $wpdb->prepare( "SELECT variant_key, segment, SUBSTR(created_at, 1, 14) AS hr, CASE WHEN SUBSTR(created_at, 15, 2) < '15' THEN '00' WHEN SUBSTR(created_at, 15, 2) < '30' THEN '15' WHEN SUBSTR(created_at, 15, 2) < '45' THEN '30' ELSE '45' END AS qt, SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END) AS imps, SUM(CASE WHEN event_type = 'conversion' THEN 1 ELSE 0 END) AS convs, SUM(CASE WHEN event_type = 'conversion' THEN value ELSE 0 END) AS rev FROM {$wpdb->prefix}improveab_events WHERE test_id = %d AND id > %d AND id <= %d AND event_type IN ('impression','conversion') GROUP BY variant_key, segment, hr, qt", $test_id, $from, $end ), ARRAY_A ); if ( '' !== $wpdb->last_error ) { $wpdb->query( 'ROLLBACK' ); return false; } // Revenue in whole cents, summed as integers, so the sums are exact. $totals = array(); $slots = array(); foreach ( (array) $rows as $r ) { $bucket = (string) $r['hr'] . (string) $r['qt'] . ':00'; if ( ! preg_match( '/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:00$/', $bucket ) ) { continue; } $segs = in_array( (string) $r['segment'], self::DEVICES, true ) ? array( '', (string) $r['segment'] ) : array( '' ); $add = array( (int) $r['imps'], (int) $r['convs'], (int) round( (float) $r['rev'] * 100 ) ); $vkey = (string) $r['variant_key']; foreach ( $segs as $seg ) { foreach ( array( 'totals' => array( $seg, $vkey ), 'slots' => array( $seg, $bucket, $vkey ) ) as $which => $key ) { $k = implode( '|', $key ); if ( 'totals' === $which ) { $totals[ $k ] = isset( $totals[ $k ] ) ? array( $key, $totals[ $k ][1] + $add[0], $totals[ $k ][2] + $add[1], $totals[ $k ][3] + $add[2] ) : array( $key, $add[0], $add[1], $add[2] ); } else { $slots[ $k ] = isset( $slots[ $k ] ) ? array( $key, $slots[ $k ][1] + $add[0], $slots[ $k ][2] + $add[1], $slots[ $k ][3] + $add[2] ) : array( $key, $add[0], $add[1], $add[2] ); } } } } $ok = self::add_counts( 'tally', $test_id, $totals ) && self::add_counts( 'buckets', $test_id, $slots ); if ( $ok ) { $wpdb->query( 'COMMIT' ); } else { $wpdb->query( 'ROLLBACK' ); } return $ok; } /** * Add counts to tally or slot rows, creating the rows that are missing, * a few hundred rows a statement. Each row is bound in the one * prepare() of its statement. * * @param string $short 'tally' (keyed by segment and variant_key) or * 'buckets' (by segment, bucket and variant_key). * @param int $test_id Test ID. * @param array $counts Entries of [ key values in that order, impressions, conversions, cents ]. * @return bool */ private static function add_counts( $short, $test_id, $counts ) { global $wpdb; foreach ( array_chunk( array_values( $counts ), 400 ) as $chunk ) { $args = array(); foreach ( $chunk as $c ) { $args[] = $test_id; $args[] = ''; foreach ( $c[0] as $key ) { $args[] = $key; } $args[] = $c[1]; $args[] = $c[2]; $args[] = sprintf( '%d.%02d', intdiv( $c[3], 100 ), $c[3] % 100 ); } // One row of placeholders per entry, every value bound from $args: // the sniff counts the placeholders of one row only. // phpcs:disable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber if ( 'buckets' === $short ) { $done = $wpdb->query( $wpdb->prepare( "INSERT INTO {$wpdb->prefix}improveab_buckets (test_id, metric, segment, bucket, variant_key, impressions, conversions, revenue) VALUES " . implode( ', ', array_fill( 0, count( $chunk ), '(%d, %s, %s, %s, %s, %d, %d, %s)' ) ) . ' ON DUPLICATE KEY UPDATE impressions = impressions + VALUES(impressions), conversions = conversions + VALUES(conversions), revenue = revenue + VALUES(revenue)', $args ) ); } else { $done = $wpdb->query( $wpdb->prepare( "INSERT INTO {$wpdb->prefix}improveab_tally (test_id, metric, segment, variant_key, impressions, conversions, revenue) VALUES " . implode( ', ', array_fill( 0, count( $chunk ), '(%d, %s, %s, %s, %d, %d, %s)' ) ) . ' ON DUPLICATE KEY UPDATE impressions = impressions + VALUES(impressions), conversions = conversions + VALUES(conversions), revenue = revenue + VALUES(revenue)', $args ) ); } // phpcs:enable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber if ( false === $done ) { return false; } } return true; } /** * Forget one test's tally, so the job sums its events again from the * first: for a table edited by hand (a backdated event moves to another * slot only this way). One transaction, so a report counts the test's * events in full before and after. * * @param int $test_id Test ID. * @return bool */ public static function retally( $test_id ) { global $wpdb; $wpdb->query( 'START TRANSACTION' ); $ok = false !== $wpdb->update( $wpdb->prefix . 'improveab_tests', array( 'tallied_id' => 0 ), array( 'id' => absint( $test_id ) ), array( '%d' ), array( '%d' ) ) && false !== $wpdb->delete( $wpdb->prefix . 'improveab_tally', array( 'test_id' => absint( $test_id ) ), array( '%d' ) ) && false !== $wpdb->delete( $wpdb->prefix . 'improveab_buckets', array( 'test_id' => absint( $test_id ) ), array( '%d' ) ); if ( $ok ) { $wpdb->query( 'COMMIT' ); } else { $wpdb->query( 'ROLLBACK' ); } return $ok; } /** * Bring the revenue of one conversion credited from an order to what * the order brings now (decision 22): down by a refund, back up by a * deleted one, to nothing while the order is cancelled or failed and * back once it is paid again. The conversion stays. The event row and * every sum that holds it move by the same amount, in one transaction, * so a report counts the conversion's revenue once, before or after. * * Nothing to move costs one read. Else the test's row is locked first, * as the tally job and the rollup lock it, so neither moves the row into * a sum while this works. A row the tally has summed (an id up to the * test's tallied_id) moves the tally rows and its fifteen-minute slot * (every device, and its own device class) by the same amount; a row the * rollup has compacted (a stopped test past retention) is gone, and the * daily rows of the day the order's record names move instead, from the * revenue the record says the conversion carries. A sum is never taken * below 0. * * @param int $test_id Test ID. * @param string $vid The converted visitor's id. * @param float $to The revenue the conversion is to carry. * @param array $record The order's credit record ({ variant, segment, at, value }). * @return float|false The conversion's revenue now, or false when a * statement failed and nothing changed. */ public static function set_revenue( $test_id, $vid, $to, $record ) { global $wpdb; $to = min( 999999999999.99, max( 0, round( (float) $to, 2 ) ) ); $noted = isset( $record['value'] ) ? (float) $record['value'] : 0.0; // Every change to the row is made holding the order's lock, so what // this read finds stays until the transaction below reads it again. $now = $wpdb->get_var( $wpdb->prepare( "SELECT value FROM {$wpdb->prefix}improveab_events WHERE test_id = %d AND visitor_token = %s AND event_type = 'conversion'", $test_id, $vid ) ); if ( '' !== $wpdb->last_error ) { return false; } if ( abs( ( null !== $now ? (float) $now : $noted ) - $to ) < 0.005 ) { return $to; } $wpdb->query( 'START TRANSACTION' ); $ok = false !== $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->prefix}improveab_tests SET tallied_id = tallied_id WHERE id = %d", $test_id ) ); $test = $ok ? $wpdb->get_row( $wpdb->prepare( "SELECT status, tallied_id FROM {$wpdb->prefix}improveab_tests WHERE id = %d", $test_id ), ARRAY_A ) : null; $row = $ok && $test ? $wpdb->get_row( $wpdb->prepare( "SELECT id, variant_key, segment, value, created_at FROM {$wpdb->prefix}improveab_events WHERE test_id = %d AND visitor_token = %s AND event_type = 'conversion'", $test_id, $vid ), ARRAY_A ) : null; if ( ! $ok || '' !== $wpdb->last_error ) { $wpdb->query( 'ROLLBACK' ); return false; } $was = $row ? (float) $row['value'] : $noted; $move = round( $to - $was, 2 ); // Every sum moves by the same amount, up or down, never below 0. if ( abs( $move ) >= 0.005 && $row ) { $ok = false !== $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->prefix}improveab_events SET value = %f WHERE id = %d", $to, (int) $row['id'] ) ); if ( $ok && (int) $row['id'] <= (int) $test['tallied_id'] ) { // Summed already: the test's sums and the row's slot, which // hold it. $segs = in_array( (string) $row['segment'], self::DEVICES, true ) ? array( '', (string) $row['segment'] ) : array( '' ); $slot = gmdate( 'Y-m-d H:i:s', self::quarter( (int) strtotime( (string) $row['created_at'] . ' UTC' ) ) ); foreach ( $segs as $seg ) { $ok = $ok && false !== $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->prefix}improveab_tally SET revenue = CASE WHEN revenue + %f > 0 THEN revenue + %f ELSE 0 END WHERE test_id = %d AND metric = '' AND segment = %s AND variant_key = %s", $move, $move, $test_id, $seg, $row['variant_key'] ) ) && false !== $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->prefix}improveab_buckets SET revenue = CASE WHEN revenue + %f > 0 THEN revenue + %f ELSE 0 END WHERE test_id = %d AND metric = '' AND segment = %s AND bucket = %s AND variant_key = %s", $move, $move, $test_id, $seg, $slot, $row['variant_key'] ) ); } } } elseif ( abs( $move ) >= 0.005 && $test && 'stopped' === $test['status'] && isset( $record['at'], $record['variant'] ) && preg_match( '/^(\d{4}-\d{2}-\d{2}) \d{2}:\d{2}:\d{2}$/', (string) $record['at'], $m ) ) { // Compacted into its UTC day, as the rollup keeps it: the // canonical row and the device class's own. $seg = isset( $record['segment'] ) ? (string) $record['segment'] : ''; $segs = in_array( $seg, self::DEVICES, true ) ? array( '', $seg ) : array( '' ); foreach ( $segs as $seg ) { $ok = $ok && false !== $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->prefix}improveab_daily SET revenue = CASE WHEN revenue + %f > 0 THEN revenue + %f ELSE 0 END WHERE test_id = %d AND variant_key = %s AND day = %s AND metric = '' AND segment = %s", $move, $move, $test_id, (string) $record['variant'], $m[1], $seg ) ); } } if ( ! $ok ) { $wpdb->query( 'ROLLBACK' ); return false; } $wpdb->query( 'COMMIT' ); return $to; } /** * A Unix time rounded down to its quarter hour. * * @param int $time Unix time. * @return int */ public static function quarter( $time ) { return (int) $time - ( (int) $time % 900 ); } /** * Take a lock row in the options table: an INSERT IGNORE against the * unique option_name, decided by the database alone, read straight from * the table (a persistent object cache may still remember it missing). * A row older than LOCK_STALE (or stamped in the future) belongs to a * run that died and is taken over, by an update that only one request * finding the same stamp can win. * * @param string $name Option name. * @return bool Whether this request holds it now. */ public static function lock( $name ) { global $wpdb; $now = time(); $wpdb->query( $wpdb->prepare( "INSERT IGNORE INTO {$wpdb->options} (option_name, option_value, autoload) VALUES (%s, %s, 'no')", $name, (string) $now ) ); if ( 1 === (int) $wpdb->rows_affected ) { return true; } $held = (string) $wpdb->get_var( $wpdb->prepare( "SELECT option_value FROM {$wpdb->options} WHERE option_name = %s", $name ) ); if ( '' === $held || ( (int) $held <= $now && $now - (int) $held < self::LOCK_STALE ) ) { return false; } return 1 === (int) $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->options} SET option_value = %s WHERE option_name = %s AND option_value = %s", (string) $now, $name, $held ) ); } /** * Release a lock row taken by lock(). * * @param string $name Option name. * @return void */ public static function unlock( $name ) { global $wpdb; $wpdb->delete( $wpdb->options, array( 'option_name' => $name ), array( '%s' ) ); } }