Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
89.25% |
83 / 93 |
|
40.00% |
2 / 5 |
CRAP | |
0.00% |
0 / 1 |
| UpdateUTCTimestamps | |
91.21% |
83 / 91 |
|
40.00% |
2 / 5 |
14.13 | |
0.00% |
0 / 1 |
| __construct | |
100.00% |
12 / 12 |
|
100.00% |
1 / 1 |
1 | |||
| execute | |
91.67% |
22 / 24 |
|
0.00% |
0 / 1 |
2.00 | |||
| validateTimezones | |
20.00% |
1 / 5 |
|
0.00% |
0 / 1 |
12.19 | |||
| updateBatch | |
95.12% |
39 / 41 |
|
0.00% |
0 / 1 |
6 | |||
| getUpdateTimeConditional | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
1 | |||
| 1 | <?php |
| 2 | |
| 3 | declare( strict_types=1 ); |
| 4 | |
| 5 | namespace MediaWiki\Extension\CampaignEvents\Maintenance; |
| 6 | |
| 7 | // @codeCoverageIgnoreStart |
| 8 | $IP = getenv( 'MW_INSTALL_PATH' ); |
| 9 | if ( $IP === false ) { |
| 10 | $IP = __DIR__ . '/../../..'; |
| 11 | } |
| 12 | require_once "$IP/maintenance/Maintenance.php"; |
| 13 | // @codeCoverageIgnoreEnd |
| 14 | |
| 15 | use DateTime; |
| 16 | use DateTimeZone; |
| 17 | use Exception; |
| 18 | use Maintenance; |
| 19 | use MediaWiki\Extension\CampaignEvents\CampaignEventsServices; |
| 20 | use Wikimedia\Rdbms\IDatabase; |
| 21 | |
| 22 | /** |
| 23 | * This script can be used to update UTC timestamps stored in the campaign_events table to make sure |
| 24 | * that they reflect the latest timezone rules as published in the Olson database. One important assumption here |
| 25 | * is that the Olson data read here is the same that is available for normal web request. This allows us to not worry |
| 26 | * about concurrent updates: if someone updates an event while the script is running, we will recompute the correct |
| 27 | * UTC timestamp on save anyway, and so we don't have to do it here. |
| 28 | */ |
| 29 | class UpdateUTCTimestamps extends Maintenance { |
| 30 | private ?IDatabase $dbw; |
| 31 | private ?IDatabase $dbr; |
| 32 | private ?DateTimeZone $utcTimezone; |
| 33 | |
| 34 | public function __construct() { |
| 35 | parent::__construct(); |
| 36 | $this->addDescription( 'Recompute UTC timestamps in the campaign_events table' ); |
| 37 | $this->setBatchSize( 500 ); |
| 38 | $this->requireExtension( 'CampaignEvents' ); |
| 39 | $this->addOption( |
| 40 | 'timezone', |
| 41 | 'Names of the timezones to update', |
| 42 | false, |
| 43 | true, |
| 44 | false, |
| 45 | true |
| 46 | ); |
| 47 | } |
| 48 | |
| 49 | /** |
| 50 | * @inheritDoc |
| 51 | */ |
| 52 | public function execute() { |
| 53 | $this->output( "Updating UTC timestamps in the campaign_events table...\n" ); |
| 54 | $dbHelper = CampaignEventsServices::getDatabaseHelper(); |
| 55 | $this->dbr = $dbHelper->getDBConnection( DB_REPLICA ); |
| 56 | $this->dbw = $dbHelper->getDBConnection( DB_PRIMARY ); |
| 57 | $batchSize = $this->getBatchSize(); |
| 58 | $updateTimezones = $this->getOption( 'timezone' ); |
| 59 | $this->validateTimezones( $updateTimezones ); |
| 60 | |
| 61 | $maxRowID = (int)$this->dbr->newSelectQueryBuilder() |
| 62 | ->select( 'MAX(event_id)' ) |
| 63 | ->from( 'campaign_events' ) |
| 64 | ->caller( __METHOD__ ) |
| 65 | ->fetchField(); |
| 66 | if ( $maxRowID === 0 ) { |
| 67 | $this->output( "Table is empty.\n" ); |
| 68 | return; |
| 69 | } |
| 70 | |
| 71 | $prevID = 0; |
| 72 | $curID = $batchSize; |
| 73 | $this->utcTimezone = new DateTimeZone( 'UTC' ); |
| 74 | do { |
| 75 | $this->updateBatch( $prevID, $curID, $updateTimezones ); |
| 76 | $prevID = $curID; |
| 77 | $curID += $batchSize; |
| 78 | $dbHelper->waitForReplication(); |
| 79 | } while ( $prevID < $maxRowID ); |
| 80 | |
| 81 | $this->output( "Done.\n" ); |
| 82 | } |
| 83 | |
| 84 | /** |
| 85 | * @param string[]|null $timezones |
| 86 | */ |
| 87 | private function validateTimezones( ?array $timezones ): void { |
| 88 | if ( $timezones !== null ) { |
| 89 | foreach ( $timezones as $tz ) { |
| 90 | try { |
| 91 | // @phan-suppress-next-line PhanNoopNew |
| 92 | new DateTimeZone( $tz ); |
| 93 | } catch ( Exception $_ ) { |
| 94 | $this->fatalError( "'$tz' is not a valid time zone.\n" ); |
| 95 | } |
| 96 | } |
| 97 | } |
| 98 | } |
| 99 | |
| 100 | /** |
| 101 | * @param int $prevID |
| 102 | * @param int $curID |
| 103 | * @param string[]|null $updateTimezones |
| 104 | */ |
| 105 | private function updateBatch( int $prevID, int $curID, ?array $updateTimezones ): void { |
| 106 | $where = [ |
| 107 | $this->dbr->expr( 'event_id', '>', $prevID ), |
| 108 | $this->dbr->expr( 'event_id', '<=', $curID ), |
| 109 | ]; |
| 110 | if ( $updateTimezones ) { |
| 111 | $where['event_timezone'] = $updateTimezones; |
| 112 | } |
| 113 | $res = $this->dbr->newSelectQueryBuilder() |
| 114 | ->select( '*' ) |
| 115 | ->from( 'campaign_events' ) |
| 116 | ->where( $where ) |
| 117 | ->caller( __METHOD__ ) |
| 118 | ->fetchResultSet(); |
| 119 | |
| 120 | $newRows = []; |
| 121 | foreach ( $res as $row ) { |
| 122 | $tz = new DateTimeZone( $row->event_timezone ); |
| 123 | $localStartDateTime = new DateTime( $row->event_start_local, $tz ); |
| 124 | $utcStartTime = $localStartDateTime->setTimezone( $this->utcTimezone )->getTimestamp(); |
| 125 | $newStartTS = wfTimestamp( TS_MW, $utcStartTime ); |
| 126 | $localEndDateTime = new DateTime( $row->event_end_local, $tz ); |
| 127 | $utcEndTime = $localEndDateTime->setTimezone( $this->utcTimezone )->getTimestamp(); |
| 128 | $newEndTS = wfTimestamp( TS_MW, $utcEndTime ); |
| 129 | |
| 130 | if ( $newStartTS !== $row->event_start_utc || $newEndTS !== $row->event_end_utc ) { |
| 131 | $newRows[] = [ |
| 132 | 'event_start_utc' => $this->dbw->timestamp( $newStartTS ), |
| 133 | 'event_end_utc' => $this->dbw->timestamp( $newEndTS ), |
| 134 | ] + get_object_vars( $row ); |
| 135 | } |
| 136 | } |
| 137 | if ( $newRows ) { |
| 138 | // Use INSERT ODKU to update all rows at once. This will never insert, only update. |
| 139 | // @phan-suppress-next-line SecurityCheck-SQLInjection False positive due to use of get_object_vars |
| 140 | $this->dbw->newInsertQueryBuilder() |
| 141 | ->insertInto( 'campaign_events' ) |
| 142 | ->rows( $newRows ) |
| 143 | ->onDuplicateKeyUpdate() |
| 144 | ->uniqueIndexFields( 'event_id' ) |
| 145 | ->set( [ |
| 146 | 'event_start_utc = ' . $this->getUpdateTimeConditional( 'event_start_utc' ), |
| 147 | 'event_end_utc = ' . $this->getUpdateTimeConditional( 'event_end_utc' ) |
| 148 | ] ) |
| 149 | ->caller( __METHOD__ ) |
| 150 | ->execute(); |
| 151 | |
| 152 | // TODO: Ideally we would use affectedRows here, but our implementation does not distinguish between |
| 153 | // matched and changed rows (T304680); additionally, MySQL counts updated rows as 2 (T314100). |
| 154 | $affectedRows = '~' . count( $newRows ); |
| 155 | } else { |
| 156 | $affectedRows = 0; |
| 157 | } |
| 158 | |
| 159 | $this->output( "Batch $prevID-$curID: $affectedRows updated.\n" ); |
| 160 | } |
| 161 | |
| 162 | /** |
| 163 | * Returns an SQL fragment that conditionally updates the given field if the other fields haven't changed |
| 164 | * since we read the row. |
| 165 | * |
| 166 | * @param string $fieldName |
| 167 | * @return string SQL |
| 168 | */ |
| 169 | private function getUpdateTimeConditional( string $fieldName ): string { |
| 170 | return $this->dbw->conditional( |
| 171 | [ |
| 172 | 'event_timezone = ' . $this->dbw->buildExcludedValue( 'event_timezone' ), |
| 173 | 'event_start_local = ' . $this->dbw->buildExcludedValue( 'event_start_local' ), |
| 174 | 'event_end_local = ' . $this->dbw->buildExcludedValue( 'event_end_local' ) |
| 175 | ], |
| 176 | $this->dbw->buildExcludedValue( $fieldName ), |
| 177 | // Fall back to identity |
| 178 | $fieldName |
| 179 | ); |
| 180 | } |
| 181 | } |
| 182 | |
| 183 | $maintClass = UpdateUTCTimestamps::class; |
| 184 | require_once RUN_MAINTENANCE_IF_MAIN; |