2 files changed, 22 insertions, 70 deletions

diff --git a/mirrors/active.php b/mirrors/active.php
deleted file mode 100644
index a3958b7..0000000
--- a/mirrors/active.php
+++ /dev/null
@@ -1,50 +0,0 @@
-<?php
-require_once "../init.php";
-
-require_once BASE . "/lib/mysql.php";
-require_once BASE . "/lib/format.php";
-
-$result = mysql_run_query(
-  "SELECT " .
-  "`mirror_statuses`.`protocol`," .
-  "`mirror_statuses`.`url`," .
-  "`mirror_statuses`.`country`," .
-  "`mirror_statuses`.`country_code`," .
-  "`mirror_statuses`.`last_sync`," .
-  "`mirror_statuses`.`start`," .
-  "`mirror_statuses`.`isos`," .
-  "`mirror_statuses`.`ipv4`," .
-  "`mirror_statuses`.`ipv6`," .
-  "`mirror_statuses`.`active`," .
-  "(`mirror_statuses`.`active` AND (`mirror_statuses`.`start` > UNIX_TIMESTAMP(NOW()) - 3600)) AS `recently_active`" .
-  " FROM `mirror_statuses`" .
-  " JOIN (" .
-    "SELECT " .
-    "`mirror_statuses`.`url`," .
-    "MAX(`mirror_statuses`.`start`) AS `start`" .
-    " FROM `mirror_statuses` GROUP BY `url`" .
-  ") AS `max_mirror`" .
-  " ON `mirror_statuses`.`url`=`max_mirror`.`url`" .
-  " AND `mirror_statuses`.`start`=`max_mirror`.`start`" .
-  " ORDER BY `mirror_statuses`.`url`"
-);
-
-while($row = $result->fetch_assoc()) {
-  foreach (array(
-    "start",
-    "isos",
-    "ipv4",
-    "ipv6",
-    "active",
-    "recently_active"
-  ) as $key)
-    $row[$key] = floatval($row[$key]);
-  foreach (array(
-    "start",
-    "last_sync"
-  ) as $key)
-    $row[$key] = gmdate("Y-m-d\TH:i:s\Z", $row[$key]);
-  $content[] = $row;
-}
-
-export_as_requested($content);
diff --git a/mirrors/status.php b/mirrors/status.php
index d9f3274..ddf922f 100644
--- a/mirrors/status.php
+++ b/mirrors/status.php
@@ -6,21 +6,6 @@ require_once BASE . "/lib/format.php";
 
 $cutoff = 86400;
 
-mysql_run_query(
-  "CREATE TEMPORARY TABLE `ls` (`id` BIGINT NOT NULL, PRIMARY KEY (`id`))"
-);
-
-mysql_run_query(
-  "INSERT INTO `ls` (`id`)" .
-  " SELECT `ms`.`id`" .
-  " FROM `mirror_statuses` AS `ms`" .
-  " WHERE NOT EXISTS (" .
-    "SELECT 1 FROM `mirror_statuses` AS `n_ms`" .
-    " WHERE `n_ms`.`url`=`ms`.`url`" .
-    " AND `n_ms`.`start`>`ms`.`start`" .
-  ") AND `ms`.`start` > UNIX_TIMESTAMP(NOW())-" . $cutoff
-);
-
 $result = mysql_run_query(
   "SELECT " .
   "`l_ms`.`protocol`," .
@@ -36,11 +21,22 @@ $result = mysql_run_query(
   "`l_ms`.`ipv4`," .
   "`l_ms`.`ipv6`," .
   "`l_ms`.`active`," .
+  "(`l_ms`.`active` AND (`l_ms`.`start` > UNIX_TIMESTAMP(NOW()) - 3600)) AS `recently_active`," .
   "AVG(IF(`a_ms`.`active`,1,0)) AS `completion_pct`," .
   "COUNT(1) AS `count`" .
-  " FROM `ls`" .
-  " JOIN `mirror_statuses` AS `l_ms` ON `ls`.`id`=`l_ms`.`id`" .
-  " JOIN `mirror_statuses` AS `a_ms` ON `a_ms`.`url`=`l_ms`.`url`" .
+  " FROM (" .
+    "SELECT " .
+    "`mirror_statuses`.`url`," .
+    "MAX(`mirror_statuses`.`start`) AS `start`" .
+    " FROM `mirror_statuses`" .
+    " WHERE `mirror_statuses`.`start` > UNIX_TIMESTAMP(NOW())-" . $cutoff .
+    " GROUP BY `mirror_statuses`.`url`" .
+  ") AS `ls`" .
+  " JOIN `mirror_statuses` AS `l_ms`" .
+  " ON `ls`.`url`=`l_ms`.`url`" .
+  " AND `ls`.`start`=`l_ms`.`start`" .
+  " JOIN `mirror_statuses` AS `a_ms`" .
+  " ON `a_ms`.`url`=`l_ms`.`url`" .
   " AND `a_ms`.`start` > UNIX_TIMESTAMP(NOW())-" . $cutoff .
   " GROUP BY `l_ms`.`id`"
 );
@@ -59,7 +55,8 @@ while($row = $result->fetch_assoc()) {
     "isos",
     "ipv4",
     "ipv6",
-    "active"
+    "active",
+    "recently_active"
   ) as $key)
     $row[$key] = floatval($row[$key]);
   $row["last_sync"] = gmdate("Y-m-d\TH:i:s\Z", $row["last_sync"]);
@@ -79,4 +76,9 @@ $content = array(
   "urls" => $urls
 );
 
-export_as_requested($content);
+export_as_requested(
+  array(
+    "json" => $content,
+    "tsv" => $urls
+  )
+);