- use google maps for location, improve messages

1 files changed, 44 insertions, 18 deletions

diff --git a/lib/MGA/Mirrors/DB.pm b/lib/MGA/Mirrors/DB.pm
index 31eb4cc..8f66ac1 100644
--- a/lib/MGA/Mirrors/DB.pm
+++ b/lib/MGA/Mirrors/DB.pm
@@ -85,6 +85,7 @@ sub protocol_list {
 
 sub bandwidth_name {
     my ($self, $value) = @_;
+    $value or return;
     my $select = $self->db->prepare(q{
         select name from bandwidth where value = ?
     });
@@ -103,6 +104,17 @@ sub bandwidth_list {
     return $list->fetchall_arrayref({});
 }
 
+sub country_info {
+    my ($self, $code) = @_;
+    my $list = $self->db->prepare(q{
+        select * from countries where code = ?
+    });
+    $list->execute($code);
+    my $res = $list->fetchrow_hashref;
+    $list->finish;
+    $res
+}
+
 sub country_list {
     my ($self) = @_;
     my $list = $self->db->prepare(q{
@@ -151,31 +163,39 @@ sub check_distributions {
     my ($self) = @_;
 
     my $uneeded_check = $self->db->prepare(q{
-        select * from mirrors_distributions where
+        select * from distributions_validity where
         lastcheck > now() - '6 hours'::interval
         });
     $uneeded_check->execute();
     my $uch = $uneeded_check->fetchall_hashref([ qw(urlskey distributionkey) ]);
 
     my $listd = $self->db->prepare(q{
-        select * from urls, distributions
-        where urls.valid = true
+        select * from toplevel_urls, distributions
+        where toplevel_urls.valid = true
         });
 
     my $addstatus = $self->db->prepare(q{
-        insert into mirrors_distributions (urlskey, distributionkey, exists)
+        insert into distributions_validity (urlskey, distributionkey, exists)
         values (?,?,?)
         });
 
     my $updstatus = $self->db->prepare(q{
-        update mirrors_distributions set lastcheck = now(), exists = ?
+        update distributions_validity set lastcheck = now(), exists = ?
+        where urlskey = ? and distributionkey = ?
+        });
+    my $upd_lastok = $self->db->prepare(q{
+        update distributions_validity set lastok = now()
         where urlskey = ? and distributionkey = ?
         });
 
     my %urls_status = ();
 
     my $updurl = $self->db->prepare(q{
-        update urls set lastcheck = now(), valid = ?
+        update toplevel_urls set lastcheck = now(), valid = ?
+        where key = ?
+    });
+    my $upd_url_lastok = $self->db->prepare(q{
+        update toplevel_urls set lastok = now()
         where key = ?
     });
 
@@ -184,8 +204,10 @@ sub check_distributions {
         $uch->{$res->{key}}{$res->{dkey}} and next;
         my $url = $self->fmt_url($res);
         if (!exists($urls_status{$res->{key}})) {
+            $self->update_host_ips($res->{hostname});
             my $ok = $self->mirror_validity($url);
             $updurl->execute($ok ? 1 : 0, $res->{key});
+            $upd_url_lastok->execute($res->{key}) if ($ok);
             $urls_status{$res->{key}} = $ok;
         }
         $urls_status{$res->{key}} or next;
@@ -194,6 +216,7 @@ sub check_distributions {
         if ($updstatus->execute($exists, $res->{key}, $res->{dkey}) == 0) {
             $addstatus->execute($res->{key}, $res->{dkey}, $exists);
         }
+        $upd_lastok->execute($res->{key}, $res->{dkey}) if ($exists);
         $self->db->commit;
     }
 }
@@ -225,9 +248,12 @@ sub find_mirrors {
     my ($self, $filters, $key) = @_;
 
     my $query = q{
-        select * from hosts
+        select *,
+        coalesce(hosts.latitude, countries.latitude) as latitude,
+        coalesce(hosts.longitude, countries.longitude) as longitude
+        from hosts
         left join countries on countries.code = hosts.country
-        where hosts.hostname in (select hostname from urls %s)
+        where hosts.hostname in (select hostname from toplevel_urls %s)
         %s
     };
 
@@ -266,8 +292,8 @@ sub _find_urls {
     my ($self, $filters, $key) = @_;
 
     my $query = q{
-        select urls.* from urls join
-        hosts on hosts.hostname = urls.hostname
+        select toplevel_urls.* from toplevel_urls join
+        hosts on hosts.hostname = toplevel_urls.hostname
     };
     my @vals;
     if (keys %{ $filters || {} }) {
@@ -276,7 +302,7 @@ sub _find_urls {
             $filters->{$_} or next;
             my $field = {
                 hostname => 'hosts.hostname',
-                protocol => 'urls.protocol',
+                protocol => 'toplevel_urls.protocol',
             }->{$_} or next;
 
             push(@w, sprintf('%s = any(?)', $field));
@@ -301,10 +327,10 @@ sub _find_distributions {
     my ($self, $filters, $key) = @_;
 
     my $query = q{
-    select *, urls.path || distributions.relpath as path from hosts
-    join urls on urls.hostname = hosts.hostname
-    join mirrors_distributions on urls.key = mirrors_distributions.urlskey
-    join distributions on distributions.dkey = mirrors_distributions.distributionkey
+    select *, toplevel_urls.path || distributions.relpath as path from hosts
+    join toplevel_urls on toplevel_urls.hostname = hosts.hostname
+    join distributions_validity on toplevel_urls.key = distributions_validity.urlskey
+    join distributions on distributions.dkey = distributions_validity.distributionkey
     };
 
     my @vals;
@@ -314,7 +340,7 @@ sub _find_distributions {
             $filters->{$_} or next;
             my $field = {
                 hostname => 'hosts.hostname',
-                protocol => 'urls.protocol',
+                protocol => 'topelvel_urls.protocol',
                 version => 'distributions.version',
                 arch => 'distributions.arch',
                 country => 'mirrors.country',
@@ -382,7 +408,7 @@ sub add_or_update_url {
     }
 
     my $update = $self->db->prepare(q{
-        update urls set path = ?, port = ?, valid = true
+        update toplevel_urls set path = ?, port = ?, valid = true
         where hostname = ? and protocol = ?
     });
 
@@ -391,7 +417,7 @@ sub add_or_update_url {
             $uri->host, $uri->scheme
         ) == 0) {
         my $add = $self->db->prepare(q{
-            insert into urls (path, port, hostname, protocol)
+            insert into toplevel_urls (path, port, hostname, protocol)
             values (?,?,?,?)
             });
         $add->execute($uri->path, $uri->port == $uri->default_port ? undef : $uri->port,