| 1 |
<?php |
| 2 |
|
| 3 |
namespace FluentMailMigrations; |
| 4 |
|
| 5 |
class EmailLogs |
| 6 |
{ |
| 7 |
/** |
| 8 |
* Migrate the table. |
| 9 |
* |
| 10 |
* @return void |
| 11 |
*/ |
| 12 |
public static function migrate() |
| 13 |
{ |
| 14 |
global $wpdb; |
| 15 |
|
| 16 |
$charsetCollate = $wpdb->get_charset_collate(); |
| 17 |
|
| 18 |
$table = $wpdb->prefix . FLUENT_MAIL_DB_PREFIX.'email_logs'; |
| 19 |
|
| 20 |
// Table name is static/hard-coded, so wpdb->prepare() is sufficient |
| 21 |
if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) != $table) { |
| 22 |
$sql = "CREATE TABLE $table ( |
| 23 |
`id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, |
| 24 |
`site_id` INT UNSIGNED NULL, |
| 25 |
`to` VARCHAR(255), |
| 26 |
`from` VARCHAR(255), |
| 27 |
`subject` VARCHAR(255), |
| 28 |
`body` LONGTEXT NULL, |
| 29 |
`headers` LONGTEXT NULL, |
| 30 |
`attachments` LONGTEXT NULL, |
| 31 |
`status` VARCHAR(20) DEFAULT 'pending', |
| 32 |
`response` TEXT NULL, |
| 33 |
`extra` TEXT NULL, |
| 34 |
`retries` INT UNSIGNED NULL DEFAULT 0, |
| 35 |
`resent_count` INT UNSIGNED NULL DEFAULT 0, |
| 36 |
`source` VARCHAR(255) NULL, |
| 37 |
`created_at` TIMESTAMP NULL, |
| 38 |
`updated_at` TIMESTAMP NULL, |
| 39 |
INDEX `created_at_status` (`created_at`, `status`) |
| 40 |
) $charsetCollate;"; |
| 41 |
|
| 42 |
dbDelta($sql); |
| 43 |
} else { |
| 44 |
self::maybeUpgradeIndexes($table); |
| 45 |
} |
| 46 |
} |
| 47 |
|
| 48 |
/** |
| 49 |
* Replace the old single-column `status` index with one that leads on |
| 50 |
* `created_at`. |
| 51 |
* |
| 52 |
* Every query against this table constrains a date range - the dashboard |
| 53 |
* counters pair it with a status, while the report chart, the day/time |
| 54 |
* heatmap and the pruning cron filter on the date alone. Leading with |
| 55 |
* `created_at` therefore serves all of them from one index. Leading with |
| 56 |
* `status` would only serve the first group, and barely: a log is |
| 57 |
* overwhelmingly 'sent', so that column narrows almost nothing. |
| 58 |
* |
| 59 |
* This only runs from migrate(), i.e. on activation and on new-site |
| 60 |
* creation - never on a normal page load. Reindexing a table that can hold |
| 61 |
* millions of rows is not free, so it must stay off the request path. |
| 62 |
* |
| 63 |
* @param string $table |
| 64 |
* @return void |
| 65 |
*/ |
| 66 |
private static function maybeUpgradeIndexes($table) |
| 67 |
{ |
| 68 |
global $wpdb; |
| 69 |
|
| 70 |
if (!self::hasIndex($table, 'created_at_status')) { |
| 71 |
$wpdb->query("ALTER TABLE $table ADD INDEX `created_at_status` (`created_at`, `status`)"); |
| 72 |
} |
| 73 |
|
| 74 |
/* |
| 75 |
* Dropped last, and only once the replacement is confirmed present, so |
| 76 |
* the table is never left with no index covering `status` at all. |
| 77 |
*/ |
| 78 |
if (self::hasIndex($table, 'status') && self::hasIndex($table, 'created_at_status')) { |
| 79 |
$wpdb->query("ALTER TABLE $table DROP INDEX `status`"); |
| 80 |
} |
| 81 |
} |
| 82 |
|
| 83 |
/** |
| 84 |
* @param string $table |
| 85 |
* @param string $indexName |
| 86 |
* @return bool |
| 87 |
*/ |
| 88 |
private static function hasIndex($table, $indexName) |
| 89 |
{ |
| 90 |
global $wpdb; |
| 91 |
|
| 92 |
// Table name is static/hard-coded, so wpdb->prepare() is sufficient |
| 93 |
return (bool)$wpdb->get_var( |
| 94 |
$wpdb->prepare("SHOW INDEX FROM $table WHERE Key_name = %s", $indexName) |
| 95 |
); |
| 96 |
} |
| 97 |
} |
| 98 |
|
