}
if ($bySlug[$slug]['house'] == $curHouse) {
$bySlug[$slug]['house'] = null;
}
}
foreach ($slugs as $slug) {
if ($bySlug[$slug]['house'] != $curHouse) {
if (!MCHouseLock($curHouse) || !MCHouseLock($bySlug[$slug]['house'])) {
break;
}
PrintImportantMessage("{$region} {$slug} changing from " . (is_null($bySlug[$slug]['house']) ? 'null' : $bySlug[$slug]['house']) . " to {$curHouse}");
DBQueryWithError($db, sprintf('UPDATE tblRealm SET house = %d WHERE region = \'%s\' AND slug = \'%s\'', $curHouse, $db->escape_string($region), $db->escape_string($slug)));
$bySlug[$slug]['house'] = $curHouse;
}
}
if (MCHouseLock($curHouse)) {
DBQueryWithError($db, sprintf('UPDATE tblRealm SET canonical = NULL WHERE house = %d', $curHouse));
DBQueryWithError($db, sprintf('UPDATE tblRealm SET canonical = \'%s\' WHERE house = %d AND region = \'%s\' AND slug = \'%s\'', $db->escape_string($canon), $curHouse, $db->escape_string($region), $db->escape_string($rep)));
MCHouseUnlock($curHouse);
} else {
PrintImportantMessage("Could not lock {$curHouse} to set canonical to {$canon}");
}
MCHouseUnlock();
}
$memcache->delete('realms_' . $region);
}
//CleanOldHouses();
//DebugMessage('Skipped cleaning old houses!');
PrintImportantMessage('Done! Started ' . TimeDiff($startTime));
function GetDataRealms($region, $hash)
{
function CleanOldData()
{
global $db, $caughtKill;
if ($caughtKill) {
return;
}
DebugMessage("Starting, getting houses");
$house = null;
$houses = [];
$stmt = $db->prepare('SELECT DISTINCT house FROM tblRealm WHERE house is not null');
$stmt->execute();
$stmt->bind_result($house);
while ($stmt->fetch()) {
$houses[] = $house;
}
$stmt->close();
// clean tblItemHistoryHourly, tblPetHistoryHourly, tblItemHistoryDaily
$sqlPatternHourly = 'delete from tbl%sHistoryHourly where house = %d and `when` < \'%s\'';
$sqlPatternDaily = 'delete from tblItemHistoryDaily where house = %d and `when` < \'%s\'';
for ($hx = 0; $hx < count($houses); $hx++) {
heartbeat();
if ($caughtKill) {
return;
}
$house = $houses[$hx];
if (!MCHouseLock($house)) {
continue;
}
$ssDate = '';
$cutoffDateHourly = date('Y-m-d', strtotime('' . HISTORY_DAYS . ' days ago'));
$cutOffDateDaily = date('Y-m-d', strtotime('' . HISTORY_DAYS_DEEP . ' days ago'));
$stmt = $db->prepare('SELECT date(min(`updated`)) FROM (SELECT `updated` FROM tblSnapshot WHERE house = ? AND `flags` & 1 = 0 ORDER BY updated DESC LIMIT ?) aa');
$maxSnapshots = 24 * HISTORY_DAYS;
$stmt->bind_param('ii', $house, $maxSnapshots);
$stmt->execute();
$stmt->bind_result($ssDate);
$gotDate = $stmt->fetch() === true;
$stmt->close();
if (!$gotDate || is_null($ssDate)) {
DebugMessage("{$house} has no snapshots, skipping item history!");
MCHouseUnlock($house);
continue;
}
if (strtotime($ssDate) < strtotime($cutoffDateHourly)) {
$cutoffDateHourly = $ssDate;
}
if (!$caughtKill) {
$rowCount = DeleteLimitLoop($db, sprintf($sqlPatternHourly, 'Item', $house, $cutoffDateHourly));
DebugMessage("{$rowCount} item hourly history rows deleted from house {$house} since {$cutoffDateHourly}");
}
if (!$caughtKill) {
$rowCount = DeleteLimitLoop($db, sprintf($sqlPatternHourly, 'Pet', $house, $cutoffDateHourly));
DebugMessage("{$rowCount} pet hourly history rows deleted from house {$house} since {$cutoffDateHourly}");
}
if (!$caughtKill) {
$rowCount = DeleteLimitLoop($db, sprintf($sqlPatternDaily, $house, $cutOffDateDaily));
DebugMessage("{$rowCount} item history daily rows deleted from house {$house} since {$cutOffDateDaily}");
}
MCHouseUnlock($house);
}
if ($caughtKill) {
return;
}
// clean tblItemExpired
for ($hx = 0; $hx < count($houses); $hx++) {
heartbeat();
if ($caughtKill) {
return;
}
$house = $houses[$hx];
if (!MCHouseLock($house)) {
continue;
}
$cutoffDate = date('Y-m-d H:i:s', strtotime('' . (HISTORY_DAYS * 2 + 3) . ' days ago'));
$sql = sprintf('delete from tblItemExpired where house = %d and `when` < \'%s\'', $house, $cutoffDate);
$rowCount = DeleteLimitLoop($db, $sql);
DebugMessage(sprintf('%d expired item rows removed from house %d since %s', $rowCount, $house, $cutoffDate));
MCHouseUnlock($house);
}
if ($caughtKill) {
return;
}
$rowCount = 0;
DebugMessage('Clearing out old seller history');
$cutoffDateHourly = date('Y-m-d', strtotime('' . HISTORY_DAYS . ' days ago'));
$sql = sprintf('delete from tblSellerHistoryHourly where `when` < \'%s\'', $cutoffDateHourly);
$rowCount += DeleteLimitLoop($db, $sql);
DebugMessage("{$rowCount} seller history rows deleted in total");
$rowCount = 0;
DebugMessage('Clearing out old seller item history');
$sql = 'delete from tblSellerItemHistory where snapshot < timestampadd(day, -' . HISTORY_DAYS . ', now())';
$rowCount += DeleteLimitLoop($db, $sql);
DebugMessage("{$rowCount} seller item history rows deleted in total");
$rowCount = 0;
$old = date('Y-m-d H:i:s', time() - SUBSCRIPTION_SESSION_LENGTH - 172800);
// 2 days older than oldest cookie age
DebugMessage('Clearing out user sessions older than ' . $old);
$sql = "delete from tblUserSession where lastseen < '{$old}'";
$rowCount += DeleteLimitLoop($db, $sql, 500);
DebugMessage("{$rowCount} user session rows deleted in total");
for ($hx = 0; $hx < count($houses); $hx++) {
heartbeat();
if ($caughtKill) {
return;
}
$house = $houses[$hx];
if (!MCHouseLock($house)) {
continue;
}
$cutoffDate = date('Y-m-d H:i:s', strtotime('' . (HISTORY_DAYS + 3) . ' days ago'));
$sql = sprintf('DELETE FROM tblSnapshot WHERE house = %d AND updated < \'%s\'', $house, $cutoffDate);
$db->query($sql);
DebugMessage(sprintf('%d snapshot rows removed from house %d since %s', $db->affected_rows, $house, $cutoffDate));
MCHouseUnlock($house);
}
}
function NextDataFile()
{
$dir = scandir(substr(SNAPSHOT_PATH, 0, -1), SCANDIR_SORT_ASCENDING);
$lockFail = false;
$gotFile = false;
foreach ($dir as $fileName) {
if (preg_match('/^(\\d+)-(\\d+)\\.json$/', $fileName, $res)) {
if (($handle = fopen(SNAPSHOT_PATH . $fileName, 'rb')) === false) {
continue;
}
if (!flock($handle, LOCK_EX | LOCK_NB)) {
$lockFail = true;
fclose($handle);
continue;
}
if (feof($handle)) {
fclose($handle);
unlink(SNAPSHOT_PATH . $fileName);
continue;
}
$snapshot = intval($res[1], 10);
$house = intval($res[2], 10);
if (!MCHouseLock($house, 3)) {
$lockFail = true;
continue;
}
$gotFile = $fileName;
break;
}
}
unset($dir);
if (!$gotFile) {
return $lockFail ? 3 : 10;
}
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " data file from " . TimeDiff($snapshot, array('parts' => 2, 'precision' => 'second')));
$json = json_decode(fread($handle, filesize(SNAPSHOT_PATH . $fileName)), true);
ftruncate($handle, 0);
fclose($handle);
unlink(SNAPSHOT_PATH . $fileName);
if (json_last_error() != JSON_ERROR_NONE) {
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " {$snapshot} data file corrupted! " . json_last_error_msg(), E_USER_WARNING);
MCHouseUnlock($house);
return 0;
}
ParseAuctionData($house, $snapshot, $json);
MCHouseUnlock($house);
return 0;
}
function AddDailyData()
{
global $db, $caughtKill;
if ($caughtKill) {
return;
}
$sql = <<<'EOF'
select distinct hc.house, date(sn.updated) dt
from tblHouseCheck hc
join tblSnapshot sn on sn.house = hc.house
where ifnull(hc.lastdaily, '2000-01-01') < date(timestampadd(day, -1, now()))
and sn.updated > timestampadd(day, 1, ifnull(hc.lastdaily, '2000-01-01'))
and sn.updated < date(now())
and sn.flags & 1 = 0
order by 1, 2
EOF;
$stmt = $db->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
$houses = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
DebugMessage(count($houses) . " houses need updates");
$sqlPattern = <<<'EOF'
replace into tblItemHistoryDaily
(SELECT `ihh`.item, `ihh`.house, `ihh`.`when`,
nullif(least(
ifnull(silver00, 4294967295),ifnull(silver01, 4294967295),ifnull(silver02, 4294967295),ifnull(silver03, 4294967295),
ifnull(silver04, 4294967295),ifnull(silver05, 4294967295),ifnull(silver06, 4294967295),ifnull(silver07, 4294967295),
ifnull(silver08, 4294967295),ifnull(silver09, 4294967295),ifnull(silver10, 4294967295),ifnull(silver11, 4294967295),
ifnull(silver12, 4294967295),ifnull(silver13, 4294967295),ifnull(silver14, 4294967295),ifnull(silver15, 4294967295),
ifnull(silver16, 4294967295),ifnull(silver17, 4294967295),ifnull(silver18, 4294967295),ifnull(silver19, 4294967295),
ifnull(silver20, 4294967295),ifnull(silver21, 4294967295),ifnull(silver22, 4294967295),ifnull(silver23, 4294967295)),4294967295) pricemin,
round((
ifnull(silver00, 0)+ifnull(silver01, 0)+ifnull(silver02, 0)+ifnull(silver03, 0)+
ifnull(silver04, 0)+ifnull(silver05, 0)+ifnull(silver06, 0)+ifnull(silver07, 0)+
ifnull(silver08, 0)+ifnull(silver09, 0)+ifnull(silver10, 0)+ifnull(silver11, 0)+
ifnull(silver12, 0)+ifnull(silver13, 0)+ifnull(silver14, 0)+ifnull(silver15, 0)+
ifnull(silver16, 0)+ifnull(silver17, 0)+ifnull(silver18, 0)+ifnull(silver19, 0)+
ifnull(silver20, 0)+ifnull(silver21, 0)+ifnull(silver22, 0)+ifnull(silver23, 0)
) / (24 -
isnull(silver00)-isnull(silver01)-isnull(silver02)-isnull(silver03)-
isnull(silver04)-isnull(silver05)-isnull(silver06)-isnull(silver07)-
isnull(silver08)-isnull(silver09)-isnull(silver10)-isnull(silver11)-
isnull(silver12)-isnull(silver13)-isnull(silver14)-isnull(silver15)-
isnull(silver16)-isnull(silver17)-isnull(silver18)-isnull(silver19)-
isnull(silver20)-isnull(silver21)-isnull(silver22)-isnull(silver23))) priceavg,
nullif(greatest(
ifnull(silver00, 0),ifnull(silver01, 0),ifnull(silver02, 0),ifnull(silver03, 0),
ifnull(silver04, 0),ifnull(silver05, 0),ifnull(silver06, 0),ifnull(silver07, 0),
ifnull(silver08, 0),ifnull(silver09, 0),ifnull(silver10, 0),ifnull(silver11, 0),
ifnull(silver12, 0),ifnull(silver13, 0),ifnull(silver14, 0),ifnull(silver15, 0),
ifnull(silver16, 0),ifnull(silver17, 0),ifnull(silver18, 0),ifnull(silver19, 0),
ifnull(silver20, 0),ifnull(silver21, 0),ifnull(silver22, 0),ifnull(silver23, 0)),0) pricemax,
coalesce(
silver00, silver01, silver02, silver03, silver04, silver05,
silver06, silver07, silver08, silver09, silver10, silver11,
silver12, silver13, silver14, silver15, silver16, silver17,
silver18, silver19, silver20, silver21, silver22, silver23) pricestart,
coalesce(
silver23, silver22, silver21, silver20, silver19, silver18,
silver17, silver16, silver15, silver14, silver13, silver12,
silver11, silver10, silver09, silver08, silver07, silver06,
silver05, silver04, silver03, silver02, silver01, silver00) priceend,
nullif(least(
ifnull(quantity00, 4294967295),ifnull(quantity01, 4294967295),ifnull(quantity02, 4294967295),ifnull(quantity03, 4294967295),
ifnull(quantity04, 4294967295),ifnull(quantity05, 4294967295),ifnull(quantity06, 4294967295),ifnull(quantity07, 4294967295),
ifnull(quantity08, 4294967295),ifnull(quantity09, 4294967295),ifnull(quantity10, 4294967295),ifnull(quantity11, 4294967295),
ifnull(quantity12, 4294967295),ifnull(quantity13, 4294967295),ifnull(quantity14, 4294967295),ifnull(quantity15, 4294967295),
ifnull(quantity16, 4294967295),ifnull(quantity17, 4294967295),ifnull(quantity18, 4294967295),ifnull(quantity19, 4294967295),
ifnull(quantity20, 4294967295),ifnull(quantity21, 4294967295),ifnull(quantity22, 4294967295),ifnull(quantity23, 4294967295)),4294967295) quantitymin,
round((
ifnull(quantity00, 0)+ifnull(quantity01, 0)+ifnull(quantity02, 0)+ifnull(quantity03, 0)+
ifnull(quantity04, 0)+ifnull(quantity05, 0)+ifnull(quantity06, 0)+ifnull(quantity07, 0)+
ifnull(quantity08, 0)+ifnull(quantity09, 0)+ifnull(quantity10, 0)+ifnull(quantity11, 0)+
ifnull(quantity12, 0)+ifnull(quantity13, 0)+ifnull(quantity14, 0)+ifnull(quantity15, 0)+
ifnull(quantity16, 0)+ifnull(quantity17, 0)+ifnull(quantity18, 0)+ifnull(quantity19, 0)+
ifnull(quantity20, 0)+ifnull(quantity21, 0)+ifnull(quantity22, 0)+ifnull(quantity23, 0)
) / (24 -
isnull(quantity00)-isnull(quantity01)-isnull(quantity02)-isnull(quantity03)-
isnull(quantity04)-isnull(quantity05)-isnull(quantity06)-isnull(quantity07)-
isnull(quantity08)-isnull(quantity09)-isnull(quantity10)-isnull(quantity11)-
isnull(quantity12)-isnull(quantity13)-isnull(quantity14)-isnull(quantity15)-
isnull(quantity16)-isnull(quantity17)-isnull(quantity18)-isnull(quantity19)-
isnull(quantity20)-isnull(quantity21)-isnull(quantity22)-isnull(quantity23))) quantityavg,
nullif(greatest(
ifnull(quantity00, 0),ifnull(quantity01, 0),ifnull(quantity02, 0),ifnull(quantity03, 0),
ifnull(quantity04, 0),ifnull(quantity05, 0),ifnull(quantity06, 0),ifnull(quantity07, 0),
ifnull(quantity08, 0),ifnull(quantity09, 0),ifnull(quantity10, 0),ifnull(quantity11, 0),
ifnull(quantity12, 0),ifnull(quantity13, 0),ifnull(quantity14, 0),ifnull(quantity15, 0),
ifnull(quantity16, 0),ifnull(quantity17, 0),ifnull(quantity18, 0),ifnull(quantity19, 0),
ifnull(quantity20, 0),ifnull(quantity21, 0),ifnull(quantity22, 0),ifnull(quantity23, 0)),0) quantitymax
FROM `tblItemHistoryHourly` ihh
JOIN `tblDBCItem` `i` ON `i`.`id` = `ihh`.`item`
WHERE i.stacksize > 1 and ihh.house = ? and ihh.`when` = ?)
EOF;
foreach ($houses as $houseRow) {
heartbeat();
if ($caughtKill) {
return;
}
if (!MCHouseLock($houseRow['house'])) {
continue;
}
$stmt = $db->prepare($sqlPattern);
$stmt->bind_param('is', $houseRow['house'], $houseRow['dt']);
$queryOk = $stmt->execute();
$rowCount = $db->affected_rows;
$stmt->close();
if (!$queryOk) {
DebugMessage("SQL error: " . $db->errno . ' ' . $db->error . " - " . substr(preg_replace('/[\\r\\n]/', ' ', $sqlPattern), 0, 500), E_USER_WARNING);
$rowCount = -1;
} else {
DebugMessage("{$rowCount} item daily rows updated for house {$houseRow['house']} for date {$houseRow['dt']}");
}
if ($rowCount >= 0) {
$stmt = $db->prepare('INSERT INTO tblHouseCheck (house, lastdaily) VALUES (?, ?) ON DUPLICATE KEY UPDATE lastdaily = values(lastdaily)');
$stmt->bind_param('is', $houseRow['house'], $houseRow['dt']);
$stmt->execute();
$stmt->close();
}
MCHouseUnlock($houseRow['house']);
}
}
