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) { heartbeat(); $region = strtolower($region); $pth = __DIR__ . '/realms2houses_cache'; if (!is_dir($pth)) { DebugMessage('Could not find realms2houses_cache!', E_USER_ERROR); } $cachePath = "{$pth}/{$region}-{$hash}.json";
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 MCHouseUnlock($house = null) { global $MCHousesLocked; if (is_null($house)) { $locked = array_keys($MCHousesLocked); foreach ($locked as $house) { MCHouseUnlock($house); } } else { MCDelete('mchouselock_' . $house); unset($MCHousesLocked[$house]); } }
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 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']); } }