Changeset 704 for trunk/DNSDB.pm

Timestamp:

03/04/16 14:10:53 (8 years ago)

Author:

Kris Deugau

Message:

/trunk

Refine a couple of segments of location handling

• resurrect a missing dropdown on new revzone page
• update DB structure and code to allow duplicate zone names so long as each is in a different location/view
• show the location/view in the zone lists

File:

• trunk/DNSDB.pm (modified) (6 diffs)

trunk/DNSDB.pm

@@ -2316 +2316 @@
 # Add a domain
 # Takes a database handle, domain name, numeric group, boolean(ish) state (active/inactive),
-# and user info hash (for logging).
+# and a default location indicator
 # Returns a status code and message
 sub addDomain {
@@ -2341 +2341 @@
   return ('FAIL', "Invalid characters in domain") if $domain !~ /^[a-zA-Z0-9_.-]+$/;
 
-  my $sth = $dbh->prepare("SELECT domain_id FROM domains WHERE lower(domain) = lower(?)");
+  my $sth = $dbh->prepare("SELECT domain_id FROM domains WHERE lower(domain) = lower(?) AND location = ?");
   my $dom_id;
 
 # quick check to start to see if we've already got one
-  $sth->execute($domain);
+  $sth->execute($domain, $defloc);
   ($dom_id) = $sth->fetchrow_array;
 
@@ -2362 +2362 @@
 
     # get the ID...
-    ($dom_id) = $dbh->selectrow_array("SELECT domain_id FROM domains WHERE lower(domain) = lower(?)",
-        undef, ($domain));
+    ($dom_id) = $dbh->selectrow_array("SELECT domain_id FROM domains WHERE lower(domain) = lower(?) AND location = ?",
+        undef, ($domain, $defloc));
 
     $self->_log(domain_id => $dom_id, group_id => $group,
@@ -2602 +2602 @@
 
 # quick check to start to see if we've already got one
-  my ($rdns_id) = $dbh->selectrow_array("SELECT rdns_id FROM revzones WHERE revnet=?", undef, ("$zone"));
+  my ($rdns_id) = $dbh->selectrow_array("SELECT rdns_id FROM revzones WHERE revnet = ? AND default_location = ?",
+        undef, ("$zone", $defloc));
 
   return ('FAIL', "Zone already exists") if $rdns_id;
@@ -2843 +2844 @@
   if ($args{revrec} eq 'n') {
     $args{sortby} = 'domain' if !$args{sortby} || !grep /^$args{sortby}$/, ('domain','group','status');
-    $sql = "SELECT domain_id AS zoneid,domain AS zone,status,groups.group_name AS group FROM domains".
-        " INNER JOIN groups ON domains.group_id=groups.group_id".
-        " WHERE domains.group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
+    $sql = q(SELECT
+                domain_id AS zoneid,
+                domain AS zone,
+                status,
+                groups.group_name AS group,
+                l.description AS location
+        FROM domains
+        LEFT JOIN locations l ON domains.default_location=l.location
+        INNER JOIN groups ON domains.group_id=groups.group_id ).
+        "WHERE domains.group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
         ($args{startwith} ? " AND domain ~* ?" : '').
         ($args{filter} ? " AND domain ~* ?" : '');
@@ -2851 +2859 @@
 ##fixme:  arguably startwith here is irrelevant.  depends on the UI though.
     $args{sortby} = 'revnet' if !$args{sortby} || !grep /^$args{sortby}$/, ('revnet','group','status');
-    $sql = "SELECT rdns_id AS zoneid,revnet AS zone,status,groups.group_name AS group FROM revzones".
-        " INNER JOIN groups ON revzones.group_id=groups.group_id".
+    $sql = q(SELECT
+                rdns_id AS zoneid,
+                revnet AS zone,
+                status,
+                groups.group_name AS group,
+                l.description AS location
+        FROM revzones
+        LEFT JOIN locations l ON revzones.default_location=l.location
+        INNER JOIN groups ON revzones.group_id=groups.group_id ).
         " WHERE revzones.group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
         ($args{startwith} ? " AND CAST(revnet AS VARCHAR) ~* ?" : '');