Migration_10.php
| 1 | <?php |
| 2 | |
| 3 | namespace IAWP\Migrations; |
| 4 | |
| 5 | use IAWP\Known_Referrers; |
| 6 | use IAWP\Query; |
| 7 | /** @internal */ |
| 8 | class Migration_10 extends \IAWP\Migrations\Migration |
| 9 | { |
| 10 | /** |
| 11 | * @var string |
| 12 | */ |
| 13 | protected $database_version = '10'; |
| 14 | /** |
| 15 | * @return void |
| 16 | */ |
| 17 | protected function migrate() : void |
| 18 | { |
| 19 | global $wpdb; |
| 20 | $views_table = Query::get_table_name(Query::VIEWS); |
| 21 | $wc_orders_table = Query::get_table_name(Query::WC_ORDERS); |
| 22 | $sessions_table = Query::get_table_name(Query::SESSIONS); |
| 23 | $referrer_groups_table = Query::get_table_name(Query::REFERRER_GROUPS); |
| 24 | $referrers_table = Query::get_table_name(Query::REFERRERS); |
| 25 | $wpdb->query("\n UPDATE {$referrers_table} SET domain = SUBSTRING(domain, 1, 255) WHERE LENGTH(domain) > 255;\n "); |
| 26 | $wpdb->query("\n ALTER TABLE {$referrers_table} MODIFY COLUMN domain VARCHAR(255) NOT NULL;\n "); |
| 27 | $wpdb->query("\n UPDATE {$referrer_groups_table} SET name = SUBSTRING(name, 1, 255) WHERE LENGTH(name) > 255;\n "); |
| 28 | $wpdb->query("\n ALTER TABLE {$referrer_groups_table} MODIFY COLUMN name VARCHAR(255) NOT NULL;\n "); |
| 29 | $wpdb->query("\n UPDATE {$referrer_groups_table} SET domain = SUBSTRING(domain, 1, 255) WHERE LENGTH(domain) > 255;\n "); |
| 30 | $wpdb->query("\n ALTER TABLE {$referrer_groups_table} MODIFY COLUMN domain VARCHAR(255) NOT NULL;\n "); |
| 31 | $wpdb->query("\n UPDATE {$referrer_groups_table} SET domain_to_match = SUBSTRING(domain_to_match, 1, 255) WHERE LENGTH(domain_to_match) > 255;\n "); |
| 32 | $wpdb->query("\n ALTER TABLE {$referrer_groups_table} MODIFY COLUMN domain_to_match VARCHAR(255) NOT NULL;\n "); |
| 33 | $wpdb->query("\n CREATE INDEX views_viewed_at_index\n ON {$views_table} (viewed_at);\n "); |
| 34 | $wpdb->query("\n CREATE INDEX views_resource_id_index\n ON {$views_table} (resource_id);\n "); |
| 35 | $wpdb->query("\n CREATE INDEX wc_orders_view_id_index\n ON {$wc_orders_table} (view_id);\n "); |
| 36 | $wpdb->query("\n CREATE INDEX wc_orders_created_at_index\n ON {$wc_orders_table} (created_at);\n "); |
| 37 | $wpdb->query("\n CREATE INDEX sessions_created_at_index\n ON {$sessions_table} (created_at);\n "); |
| 38 | // Remove any duplicate referrer groups where a given domain_to_match already exists |
| 39 | $wpdb->query("\n DELETE referrer_groups FROM {$referrer_groups_table} AS referrer_groups\n INNER JOIN (\n SELECT\n domain_to_match,\n MIN(referrer_group_id) AS min_referrer_group_id\n FROM\n {$referrer_groups_table}\n GROUP BY\n domain_to_match\n HAVING\n COUNT(*) > 1) AS duplicates ON referrer_groups.domain_to_match = duplicates.domain_to_match\n AND referrer_groups.referrer_group_id > duplicates.min_referrer_group_id;\n "); |
| 40 | $wpdb->query("\n CREATE UNIQUE INDEX referrer_groups_domain_to_match_index\n ON {$referrer_groups_table} (domain_to_match);\n "); |
| 41 | $wpdb->query("\n UPDATE\n {$sessions_table} AS sessions\n INNER JOIN {$referrers_table} AS referrers ON sessions.referrer_id = referrers.id\n INNER JOIN (\n SELECT\n MIN(referrers.id) AS min_referrer_id,\n referrers.domain\n FROM\n {$sessions_table} AS sessions\n INNER JOIN {$referrers_table} AS referrers ON sessions.referrer_id = referrers.id\n GROUP BY\n referrers.domain) AS matcher ON referrers.domain = matcher.domain\n AND referrers.id != matcher.min_referrer_id SET sessions.referrer_id = matcher.min_referrer_id;\n "); |
| 42 | $wpdb->query("\n DELETE referrers FROM {$referrers_table} AS referrers\n LEFT JOIN {$sessions_table} AS sessions ON referrers.id = sessions.referrer_id\n WHERE sessions.session_id IS NULL;\n "); |
| 43 | $wpdb->query("\n CREATE UNIQUE INDEX referrers_domain_index\n ON {$referrers_table} (domain);\n "); |
| 44 | $wpdb->query("\n ALTER TABLE {$referrer_groups_table} MODIFY COLUMN type ENUM ('Search', 'Social', 'Referrer') NOT NULL;\n "); |
| 45 | Known_Referrers::replace_known_referrers_table(); |
| 46 | } |
| 47 | } |
| 48 |