migrate_to_100(); update_option( 'wpab_db_version', WPAB_DB_VERSION, true ); } /** * Initial schema. */ private function migrate_to_100(): void { global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $charset = $wpdb->get_charset_collate(); $tables = $this->statements( $wpdb->prefix, $charset ); foreach ( $tables as $sql ) { dbDelta( $sql ); } } /** * CREATE TABLE statements. dbDelta requires two spaces before PRIMARY KEY. * * @param string $prefix Table prefix. * @param string $charset Charset clause. * @return string[] */ public function statements( string $prefix, string $charset ): array { return array( "CREATE TABLE {$prefix}wpab_experiments ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, name varchar(191) NOT NULL DEFAULT '', description text NULL, target_type varchar(20) NOT NULL DEFAULT 'post', test_scope varchar(32) NOT NULL DEFAULT 'title_content', status varchar(20) NOT NULL DEFAULT 'draft', post_id bigint(20) unsigned NOT NULL DEFAULT 0, post_type varchar(20) NOT NULL DEFAULT 'post', audience varchar(20) NOT NULL DEFAULT 'all', allocation_mode varchar(20) NOT NULL DEFAULT 'manual', settings longtext NULL, start_at datetime NULL, end_at datetime NULL, winner_variant_id bigint(20) unsigned NULL, assignment_epoch int(10) unsigned NOT NULL DEFAULT 0, keep_assignments tinyint(1) NOT NULL DEFAULT 1, created_by bigint(20) unsigned NOT NULL DEFAULT 0, created_at datetime NOT NULL, updated_at datetime NOT NULL, PRIMARY KEY (id), KEY status (status), KEY post_id (post_id), KEY schedule (status, start_at, end_at) ) {$charset};", "CREATE TABLE {$prefix}wpab_variants ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, experiment_id bigint(20) unsigned NOT NULL, name varchar(191) NOT NULL DEFAULT '', description text NULL, traffic_percentage decimal(7,2) NOT NULL DEFAULT 0.00, is_control tinyint(1) NOT NULL DEFAULT 0, title text NULL, content longtext NULL, excerpt text NULL, featured_image_id bigint(20) unsigned NOT NULL DEFAULT 0, environment longtext NULL, status varchar(20) NOT NULL DEFAULT 'active', sort_order int(11) NOT NULL DEFAULT 0, created_at datetime NOT NULL, updated_at datetime NOT NULL, PRIMARY KEY (id), KEY experiment_id (experiment_id) ) {$charset};", "CREATE TABLE {$prefix}wpab_goals ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, experiment_id bigint(20) unsigned NOT NULL, name varchar(191) NOT NULL DEFAULT '', type varchar(32) NOT NULL DEFAULT 'custom', configuration longtext NULL, is_primary tinyint(1) NOT NULL DEFAULT 0, created_at datetime NOT NULL, PRIMARY KEY (id), KEY experiment_id (experiment_id) ) {$charset};", "CREATE TABLE {$prefix}wpab_visitors ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, anonymous_id char(32) NOT NULL, created_at datetime NOT NULL, last_seen datetime NOT NULL, metadata longtext NULL, PRIMARY KEY (id), UNIQUE KEY anonymous_id (anonymous_id) ) {$charset};", "CREATE TABLE {$prefix}wpab_assignments ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, experiment_id bigint(20) unsigned NOT NULL, variant_id bigint(20) unsigned NOT NULL, visitor_id bigint(20) unsigned NOT NULL, assigned_at datetime NOT NULL, PRIMARY KEY (id), UNIQUE KEY experiment_visitor (experiment_id, visitor_id), KEY variant_id (variant_id) ) {$charset};", "CREATE TABLE {$prefix}wpab_events ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, experiment_id bigint(20) unsigned NOT NULL, variant_id bigint(20) unsigned NOT NULL, visitor_id bigint(20) unsigned NOT NULL, session_id char(32) NOT NULL DEFAULT '', event_type varchar(32) NOT NULL DEFAULT '', event_name varchar(64) NOT NULL DEFAULT '', object_id bigint(20) unsigned NOT NULL DEFAULT 0, created_at datetime NOT NULL, metadata longtext NULL, aggregated tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY experiment_created (experiment_id, created_at), KEY variant_created (variant_id, created_at), KEY aggregated (aggregated, experiment_id), KEY visitor_id (visitor_id) ) {$charset};", "CREATE TABLE {$prefix}wpab_conversions ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, experiment_id bigint(20) unsigned NOT NULL, variant_id bigint(20) unsigned NOT NULL, visitor_id bigint(20) unsigned NOT NULL, goal_id bigint(20) unsigned NOT NULL, created_at datetime NOT NULL, metadata longtext NULL, aggregated tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY one_per_goal (experiment_id, visitor_id, goal_id), KEY experiment_created (experiment_id, created_at), KEY goal_variant (goal_id, variant_id) ) {$charset};", "CREATE TABLE {$prefix}wpab_daily_stats ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, stat_date date NOT NULL, experiment_id bigint(20) unsigned NOT NULL, variant_id bigint(20) unsigned NOT NULL, visitors int(10) unsigned NOT NULL DEFAULT 0, views int(10) unsigned NOT NULL DEFAULT 0, conversions int(10) unsigned NOT NULL DEFAULT 0, engage_ms bigint(20) unsigned NOT NULL DEFAULT 0, engage_samples int(10) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY daily_variant (stat_date, experiment_id, variant_id), KEY experiment_id (experiment_id) ) {$charset};", "CREATE TABLE {$prefix}wpab_daily_uniques ( stat_date date NOT NULL, experiment_id bigint(20) unsigned NOT NULL, variant_id bigint(20) unsigned NOT NULL, visitor_id bigint(20) unsigned NOT NULL, PRIMARY KEY (stat_date, experiment_id, variant_id, visitor_id) ) {$charset};", "CREATE TABLE {$prefix}wpab_history ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, experiment_id bigint(20) unsigned NOT NULL, variant_id bigint(20) unsigned NOT NULL, post_id bigint(20) unsigned NOT NULL, snapshot longtext NOT NULL, action varchar(20) NOT NULL DEFAULT 'applied', created_at datetime NOT NULL, PRIMARY KEY (id), KEY experiment_id (experiment_id) ) {$charset};", ); } }