· 7 years ago · Sep 21, 2018, 10:10 AM
1<?php
2/**
3 *
4 *
5 * @author Cem Hurturk
6 * @version $Id$
7 * @copyright Powow, 11 November, 2007
8 * @package default
9 **/
10
11/**
12 * Define DocBlock
13 **/
14
15// This file will be executed from shell also. So, check if DOCUMENT_ROOT is set. If not, set it - Start
16if ($_SERVER['DOCUMENT_ROOT'] == '')
17 {
18 /*
19 IMPORTANT:
20 If this CRON script is executed in CLI mode, do not forget that PHP will try to connect your MySQL server
21 through /var/mysql/mysql.sock socket. The path of socket file can not be changed. If mysql.sock is in different
22 location on your server, simply create a symbolic link on /var/mysql/mysql.sock
23
24 This is a limitation of PHP and there's nothing to do. Sleep on it ;)
25 */
26 $_SERVER['DOCUMENT_ROOT'] = str_replace('/cli/correct_subscriber_lists_fields.php', '', __FILE__);
27 }
28// This file will be executed from shell also. So, check if DOCUMENT_ROOT is set. If not, set it - End
29
30$IsCLI = true;
31
32// Include main module - Start
33include_once($_SERVER['DOCUMENT_ROOT'].'/data/config.inc.php');
34// Include main module - End
35
36// Check if this being executed via browser or shell - Start
37if (Core::RunningFromCLI() == false)
38 {
39 Core::DisplayCLIBrowserError('CRON');
40 exit;
41 }
42// Check if this being executed via browser or shell - End
43
44// Check for system partitions - Start
45$ArrayPartitions = array(
46 "/" => array("MinimumSpaceLimit" => 10 * 1024 * 1024 * 1024, "Name" => "Main partition"), // 10 Go
47 "/var/lib/mysql" => array("MinimumSpaceLimit" => 25 * 1024 * 1024 * 1024, "Name" => "MySQL"), // 25 Go
48 "/var/www/esp/data/pmtastats/archive" => array("MinimumSpaceLimit" => 5 * 1024 * 1024 * 1024, "Name" => "Archives PMTA"), // 5 Go
49);
50foreach ($ArrayPartitions as $EachPartition => $EachArrayInfo)
51 {
52 if (@disk_free_space($EachPartition) === false)
53 {
54 unset($ArrayPartitions[$EachPartition]);
55 }
56 }
57// Check for system partitions - End
58
59// Get all lists
60$ArrayLists = array();
61$ArrayTransactionalLists = array();
62$SQLQuery = 'SELECT ListID, Transactional FROM esp_subscriber_lists;';
63$SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
64while ($EachList = mysql_fetch_assoc($SQLResult)) {
65 switch ($EachList['Transactional']) {
66 case 0:
67 $ArrayLists[] = $EachList['ListID'];
68 break;
69 case 1:
70 $ArrayTransactionalLists[] = $EachList['ListID'];
71 break;
72 }
73}
74
75$TotalLists = count($ArrayLists);
76$TotalTransactionalLists = count($ArrayTransactionalLists);
77
78echo "--- Checking fields on lists ---\n";
79$TotalListToConvert = 0;
80foreach ($ArrayLists as $EachListID) {
81 if ($EachListID < 100) {
82 $ArrayTableChange = array();
83
84 // check table engine - Start
85 $SQLQuery = 'SHOW TABLE STATUS WHERE Name = "esp_subscribers_'.$EachListID.'";';
86 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
87 while ($ArrayInformation = mysql_fetch_assoc($SQLResult)) {
88 if ($ArrayInformation['Engine'] == 'MyISAM') {
89 $ArrayTableChange[] = 'ENGINE = InnoDB';
90 }
91 }
92 // check table engine - End
93 // check datetime columns - Start
94 $SQLQuery = 'SHOW COLUMNS FROM esp_subscribers_'.$EachListID.' WHERE Field IN ("SubscriptionDate", "UnsubscriptionDate", "OptInDate") AND Type = "date"';
95 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
96 while ($EachColumn = mysql_fetch_assoc($SQLResult)) {
97 $ArrayTableChange[] = 'CHANGE COLUMN `'.$EachColumn['Field'].'` `'.$EachColumn['Field'].'` DATETIME NOT NULL';
98 }
99 // check datetime columns - End
100 // check MobileCountryCode field - Start
101 $AddColumnQuery = '';
102 $SQLQuery = 'SHOW COLUMNS FROM esp_subscribers_'.$EachListID.' WHERE Field = "MobileCountryCode";';
103 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
104 if (mysql_num_rows($SQLResult) < 1) {
105 $ArrayTableChange[] = "ADD COLUMN `MobileCountryCode` VARCHAR(3) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL DEFAULT '' AFTER `MobilePhoneNumber`";
106 }
107 // check MobileCountryCode field - End
108 // check MobileNetworkCode field - Start
109 $SQLQuery = 'SHOW COLUMNS FROM esp_subscribers_'.$EachListID.' WHERE Field = "MobileNetworkCode";';
110 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
111 if (mysql_num_rows($SQLResult) < 1) {
112 $ArrayTableChange[] = "ADD COLUMN `MobileNetworkCode` VARCHAR(3) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL DEFAULT '' AFTER `MobileCountryCode`";
113 }
114 // check MobileNetworkCode field - End
115
116 if (count($ArrayTableChange) > 0) {
117 echo "List #".$EachListID." - ".implode(' || ', $ArrayTableChange);
118
119 // retrieve table size
120 $SQLQuery = 'SELECT round(((data_length + index_length) / 1024 / 1024), 2) AS `Size` FROM information_schema.TABLES WHERE table_schema = "esp" AND table_name = "esp_subscribers_'.$EachListID.'";';
121 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
122 $ArrayTableSize = mysql_fetch_row($SQLResult);
123 $TableSize = $ArrayTableSize[0];
124 echo " ".$TableSize."Mo";
125 echo "\n";
126
127 if (floatval($TableSize) <= 1024) {
128 // Direct ALTER if table less than 1Go - Start
129 $SQLQuery = "ALTER TABLE esp_subscribers_".$EachListID." ".implode(', ', $ArrayTableChange).";";
130 Database::$Interface->ExecuteQuery($SQLQuery);
131 echo "esp_subscribers_".$EachListID." corrected\n";
132 // Direct ALTER if table less than 1Go - End
133 } else {
134 // SOFT TRANSFORMATION - Start
135 $ExecuteSQLQueries = true;
136 $SleepValue = 0.25;
137 $UpdatePerLoop = 2500;
138
139 // Retrieve table PK - Start
140 $SQLQuery = "SELECT `COLUMN_NAME` FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'esp' AND `TABLE_NAME` = 'esp_subscribers_".$EachListID."' AND `COLUMN_KEY` = 'PRI' AND `DATA_TYPE` IN ('tinyint', 'smallint', 'mediumint', 'int', 'bigint');";
141 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
142 $FirstRow = mysql_fetch_row($SQLResult);
143 $PK = $FirstRow[0];
144 // Retrieve table PK - End
145
146 // Retrieve min PK index - Start
147 $SQLQuery = "SELECT IFNULL(MIN(".$PK."), 0) AS 'MIN' FROM esp_subscribers_".$EachListID.";";
148 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
149 $Result = mysql_fetch_assoc($SQLResult);
150 $Index = $Result['MIN'];
151 if ($Index > 0) {
152 // NOTE - we need to track back in order to not skip the first row
153 $Index = $Index - 1;
154 }
155 // Retrieve min PK index - End
156
157 // Retrieve max PK index - Start
158 $SQLQuery = "SELECT IFNULL(MAX(".$PK."), 0) AS 'MAX' FROM esp_subscribers_".$EachListID.";";
159 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
160 $Result = mysql_fetch_assoc($SQLResult);
161 $MaxIndex = $Result['MAX'];
162 // Retrieve max PK index - End
163
164 // Get table fields without added columns - Start
165 $SQLQuery = "SHOW COLUMNS FROM esp_subscribers_".$EachListID.";";
166 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
167 $ArrayTableColumns = array();
168 while ($EachColumn = mysql_fetch_assoc($SQLResult)) {
169 $ArrayTableColumns[] = $EachColumn['Field'];
170 }
171 // Get table fields without added columns - End
172
173 // Create temporary table - Start
174 $SQLQuery = "CREATE TABLE IF NOT EXISTS esp_subscribers_".$EachListID."_tmpnew LIKE esp_subscribers_".$EachListID.";";
175 if ($ExecuteSQLQueries) {
176 Database::$Interface->ExecuteQuery($SQLQuery);
177 }
178 $SQLQuery = "ALTER TABLE esp_subscribers_".$EachListID."_tmpnew ".implode(', ', $ArrayTableChange).";";
179 if ($ExecuteSQLQueries) {
180 Database::$Interface->ExecuteQuery($SQLQuery);
181 }
182 // Create temporary table - End
183
184 // Forge update query base - Start
185 $UpdateQuery = "INSERT INTO esp_subscribers_".$EachListID."_tmpnew (".implode(', ', $ArrayTableColumns).") SELECT * FROM esp_subscribers_".$EachListID;
186 // Forge update query base - End
187
188 // Convert all datas via soft updates - Start
189 $DiskSpaceIndex = 0;
190 while ($Index < $MaxIndex) {
191 // Log
192 echo "[".date('Y-m-d H:i:s')."] converting \"esp_subscribers_".$EachListID."\" - ".number_format(100.0*$Index/$MaxIndex, 2)."%\n";
193 // Update
194 $SQLQuery = $UpdateQuery." WHERE ".$PK." > ".$Index." AND ".$PK." <= ".strval($Index+$UpdatePerLoop).";";
195 if ($ExecuteSQLQueries) {
196 Database::$Interface->ExecuteQuery($SQLQuery);
197 }
198 usleep($SleepValue * 1000000);
199
200 // Disk space verification every 5 loops - Start
201 if ($DiskSpaceIndex % 5 == 0) {
202 foreach ($ArrayPartitions as $EachPartition => $EachArrayInfo) {
203 if (@disk_free_space($EachPartition) < $EachArrayInfo['MinimumSpaceLimit']) {
204 $ArrayUnits = array('o','Ko','Mo','Go', 'To');
205 $UnitIndex = 0;
206 while ($EachArrayInfo['MinimumSpaceLimit'] >= 1024) {
207 $EachArrayInfo['MinimumSpaceLimit'] = $EachArrayInfo['MinimumSpaceLimit'] / 1024;
208 $UnitIndex++;
209 }
210 echo "[".date('Y-m-d H:i:s')."] Disk space limit of ".$EachArrayInfo['MinimumSpaceLimit'].$ArrayUnits[$UnitIndex]." reach on partition \"".$EachPartition."\" (".$EachArrayInfo['Name'].") stopping execution after query \"".$SQLQuery."\"\n";
211 exit ;
212 }
213 }
214 }
215 $DiskSpaceIndex++;
216 // Disk space verification every 5 loops - End
217
218 // Update max PK - Start
219 $SQLQuery = "SELECT IFNULL(MAX(".$PK."), 0) AS 'MAX' FROM esp_subscribers_".$EachListID.";";
220 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
221 $Result = mysql_fetch_assoc($SQLResult);
222 $MaxIndex = $Result['MAX'];
223 // Update max PK - End
224
225 $Index += $UpdatePerLoop;
226 }
227 // Convert all datas via soft updates - End
228
229 // Swap both tables then delete old one - Start
230 $SQLQuery = "RENAME TABLE esp_subscribers_".$EachListID." TO tmp_table, esp_subscribers_".$EachListID."_tmpnew TO esp_subscribers_".$EachListID.", tmp_table TO esp_subscribers_".$EachListID."_tmpnew;";
231 if ($ExecuteSQLQueries) {
232 Database::$Interface->ExecuteQuery($SQLQuery);
233 }
234 $SQLQuery = "DROP TABLE esp_subscribers_".$EachListID."_tmpnew;";
235 if ($ExecuteSQLQueries) {
236 Database::$Interface->ExecuteQuery($SQLQuery);
237 }
238 // Swap both tables then delete old one - End
239
240 echo "[".date('Y-m-d H:i:s')."] \"esp_subscribers_".$EachListID."\" converted - 100.00%\n";
241 // SOFT TRANSFORMATION - End
242 }
243 $TotalListToConvert++;
244 }
245 // check date fields - End
246 }
247}
248echo "--- DONE => ".$TotalListToConvert."/".$TotalLists." lists to convert --- \n";
249echo "--- Checking fields on transactional lists ---\n";
250$TotalListToConvert = 0;
251foreach ($ArrayTransactionalLists as $EachListID) {
252 $ArrayTableChange = array();
253
254 // check table engine - Start
255 $SQLQuery = 'SHOW TABLE STATUS WHERE Name = "esp_subscribers_'.$EachListID.'";';
256 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
257 while ($ArrayInformation = mysql_fetch_assoc($SQLResult)) {
258 if ($ArrayInformation['Engine'] == 'MyISAM') {
259 $ArrayTableChange[] = 'ENGINE = InnoDB';
260 }
261 }
262 // check table engine - End
263 // check datetime columns - Start
264 $SQLQuery = 'SHOW COLUMNS FROM esp_subscribers_'.$EachListID.' WHERE Field IN ("SubscriptionDate", "UnsubscriptionDate", "OptInDate") AND Type = "date"';
265 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
266 while ($EachColumn = mysql_fetch_assoc($SQLResult)) {
267 $ArrayTableChange[] = 'CHANGE COLUMN `'.$EachColumn['Field'].'` `'.$EachColumn['Field'].'` DATETIME NOT NULL';
268 }
269 // check datetime columns - End
270 // check MobileCountryCode field - Start
271 $AddColumnQuery = '';
272 $SQLQuery = 'SHOW COLUMNS FROM esp_subscribers_'.$EachListID.' WHERE Field = "MobileCountryCode";';
273 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
274 if (mysql_num_rows($SQLResult) < 1) {
275 $ArrayTableChange[] = "ADD COLUMN `MobileCountryCode` VARCHAR(3) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL DEFAULT '' AFTER `MobilePhoneNumber`";
276 }
277 // check MobileCountryCode field - End
278 // check MobileNetworkCode field - Start
279 $SQLQuery = 'SHOW COLUMNS FROM esp_subscribers_'.$EachListID.' WHERE Field = "MobileNetworkCode";';
280 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
281 if (mysql_num_rows($SQLResult) < 1) {
282 $ArrayTableChange[] = "ADD COLUMN `MobileNetworkCode` VARCHAR(3) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL DEFAULT '' AFTER `MobileCountryCode`";
283 }
284 // check MobileNetworkCode field - End
285
286 if (count($ArrayTableChange) > 0) {
287 echo "Transactional list #".$EachListID." - ".implode(' || ', $ArrayTableChange);
288
289 // retrieve table size
290 $SQLQuery = 'SELECT round(((data_length + index_length) / 1024 / 1024), 2) AS `Size` FROM information_schema.TABLES WHERE table_schema = "esp" AND table_name = "esp_subscribers_'.$EachListID.'";';
291 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
292 $ArrayTableSize = mysql_fetch_row($SQLResult);
293 $TableSize = $ArrayTableSize[0];
294 echo " ".$TableSize."Mo";
295 echo "\n";
296
297 if (floatval($TableSize) <= 1024) {
298 // Direct ALTER if table less than 1Go - Start
299 $SQLQuery = "ALTER TABLE esp_subscribers_".$EachListID." ".implode(', ', $ArrayTableChange).";";
300 Database::$Interface->ExecuteQuery($SQLQuery);
301 echo "esp_subscribers_".$EachListID." corrected\n";
302 // Direct ALTER if table less than 1Go - End
303 } else {
304 // SOFT TRANSFORMATION - Start
305 $ExecuteSQLQueries = true;
306 $SleepValue = 0.25;
307 $UpdatePerLoop = 2500;
308
309 // Retrieve table PK - Start
310 $SQLQuery = "SELECT `COLUMN_NAME` FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'esp' AND `TABLE_NAME` = 'esp_subscribers_".$EachListID."' AND `COLUMN_KEY` = 'PRI' AND `DATA_TYPE` IN ('tinyint', 'smallint', 'mediumint', 'int', 'bigint');";
311 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
312 $FirstRow = mysql_fetch_row($SQLResult);
313 $PK = $FirstRow[0];
314 // Retrieve table PK - End
315
316 // Retrieve min PK index - Start
317 $SQLQuery = "SELECT IFNULL(MIN(".$PK."), 0) AS 'MIN' FROM esp_subscribers_".$EachListID.";";
318 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
319 $Result = mysql_fetch_assoc($SQLResult);
320 $Index = $Result['MIN'];
321 // TWEAK - we start from 34000000 for esp_subscribers_892 transactional list - Start
322 if ($EachListID == 892) {
323 $Index = 34000000;
324 }
325 // TWEAK - we start from 34000000 for esp_subscribers_892 transactional list - End
326 if ($Index > 0) {
327 // NOTE - we need to track back in order to not skip the first row
328 $Index = $Index - 1;
329 }
330 // Retrieve min PK index - End
331
332
333 // Retrieve max PK index - Start
334 $SQLQuery = "SELECT IFNULL(MAX(".$PK."), 0) AS 'MAX' FROM esp_subscribers_".$EachListID.";";
335 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
336 $Result = mysql_fetch_assoc($SQLResult);
337 $MaxIndex = $Result['MAX'];
338 // Retrieve max PK index - End
339
340 // Get table fields without added columns - Start
341 $SQLQuery = "SHOW COLUMNS FROM esp_subscribers_".$EachListID.";";
342 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
343 $ArrayTableColumns = array();
344 while ($EachColumn = mysql_fetch_assoc($SQLResult)) {
345 $ArrayTableColumns[] = $EachColumn['Field'];
346 }
347 // Get table fields without added columns - End
348
349 // Create temporary table - Start
350 $SQLQuery = "CREATE TABLE IF NOT EXISTS esp_subscribers_".$EachListID."_tmpnew LIKE esp_subscribers_".$EachListID.";";
351 if ($ExecuteSQLQueries) {
352 Database::$Interface->ExecuteQuery($SQLQuery);
353 }
354 $SQLQuery = "ALTER TABLE esp_subscribers_".$EachListID."_tmpnew ".implode(', ', $ArrayTableChange).";";
355 if ($ExecuteSQLQueries) {
356 Database::$Interface->ExecuteQuery($SQLQuery);
357 }
358 // Create temporary table - End
359
360 // Forge update query base - Start
361 $UpdateQuery = "INSERT INTO esp_subscribers_".$EachListID."_tmpnew (".implode(', ', $ArrayTableColumns).") SELECT * FROM esp_subscribers_".$EachListID;
362 // Forge update query base - End
363
364 // Convert all datas via soft updates - Start
365 while ($Index < $MaxIndex) {
366 // Log
367 echo "[".date('Y-m-d H:i:s')."] converting \"esp_subscribers_".$EachListID."\" - ".number_format(100.0*$Index/$MaxIndex, 2)."%\n";
368 // Update
369 $SQLQuery = $UpdateQuery." WHERE ".$PK." > ".$Index." AND ".$PK." <= ".strval($Index+$UpdatePerLoop).";";
370 if ($ExecuteSQLQueries) {
371 Database::$Interface->ExecuteQuery($SQLQuery);
372 }
373 usleep($SleepValue * 1000000);
374
375 // Disk space verification every 5 loops - Start
376 if ($DiskSpaceIndex % 5 == 0) {
377 foreach ($ArrayPartitions as $EachPartition => $EachArrayInfo) {
378 if (@disk_free_space($EachPartition) < $EachArrayInfo['MinimumSpaceLimit']) {
379 $ArrayUnits = array('o','Ko','Mo','Go', 'To');
380 $UnitIndex = 0;
381 while ($EachArrayInfo['MinimumSpaceLimit'] >= 1024) {
382 $EachArrayInfo['MinimumSpaceLimit'] = $EachArrayInfo['MinimumSpaceLimit'] / 1024;
383 $UnitIndex++;
384 }
385 echo "[".date('Y-m-d H:i:s')."] Disk space limit of ".$EachArrayInfo['MinimumSpaceLimit'].$ArrayUnits[$UnitIndex]." reach on partition \"".$EachPartition."\" (".$EachArrayInfo['Name'].") stopping execution after query \"".$SQLQuery."\"\n";
386 exit ;
387 }
388 }
389 }
390 $DiskSpaceIndex++;
391 // Disk space verification every 5 loops - End
392
393 // Update max PK - Start
394 $SQLQuery = "SELECT IFNULL(MAX(".$PK."), 0) AS 'MAX' FROM esp_subscribers_".$EachListID.";";
395 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
396 $Result = mysql_fetch_assoc($SQLResult);
397 $MaxIndex = $Result['MAX'];
398 // Update max PK - End
399
400 $Index += $UpdatePerLoop;
401 }
402 // Convert all datas via soft updates - End
403
404 // Swap both tables then delete old one - Start
405 $SQLQuery = "RENAME TABLE esp_subscribers_".$EachListID." TO tmp_table, esp_subscribers_".$EachListID."_tmpnew TO esp_subscribers_".$EachListID.", tmp_table TO esp_subscribers_".$EachListID."_tmpnew;";
406 if ($ExecuteSQLQueries) {
407 Database::$Interface->ExecuteQuery($SQLQuery);
408 }
409 $SQLQuery = "DROP TABLE esp_subscribers_".$EachListID."_tmpnew;";
410 if ($ExecuteSQLQueries) {
411 Database::$Interface->ExecuteQuery($SQLQuery);
412 }
413 // Swap both tables then delete old one - End
414
415 echo "[".date('Y-m-d H:i:s')."] \"esp_subscribers_".$EachListID."\" converted - 100.00%\n";
416 // SOFT TRANSFORMATION - End
417 }
418 $TotalListToConvert++;
419 }
420}
421echo "--- DONE => ".$TotalListToConvert."/".$TotalTransactionalLists." transactional lists to convert --- \n";
422?>