Changeset 24625

Timestamp:

Jun 29, 2009, 1:54:25 PM (17 years ago)

Author:

jhoblitt

Message:

change pending SQL to not use temp tables and be a hair more efficient
tab expansion

File:

• trunk/Nebulous-Server/bin/neb-admin (modified) (5 diffs)

trunk/Nebulous-Server/bin/neb-admin

@@ -91 +91 @@
 {
 # so_id, ext_id, instances, available_instances, need_recovery, recoverable
-    $dbh->do("CREATE TEMPORARY TABLE mymountedvol LIKE mountedvol");
-    $dbh->do("INSERT INTO mymountedvol SELECT * FROM mountedvol");
-    $dbh->do("CREATE TEMPORARY TABLE myvolume LIKE volume");
-    $dbh->do("INSERT INTO myvolume SELECT * FROM volume");
+# XXX don't remove the temp table code -- testing w/o it
+#    $dbh->do("CREATE TEMPORARY TABLE mymountedvol LIKE mountedvol");
+#    $dbh->do("INSERT INTO mymountedvol SELECT * FROM mountedvol");
 
     if (not defined $so_id_start) { $so_id_start = 0; }
@@ -102 +101 @@
     # XXX check if so_id_start is beyond MAX(so_id): if so, exit with exit status 10
     { 
-        my $query = $dbh->prepare("SELECT MAX(so_id) from storage_object");
-        $query->execute;
+        my $query = $dbh->prepare("SELECT MAX(so_id) from storage_object");
+        $query->execute;
         my $answer = $query->fetchrow_arrayref;
         my $max_so_id = $$answer[0];
-        $query->finish;
-
-        if ($so_id_start > $max_so_id) { 
-            print STDERR "at end of so_id range, reset please\n";
-            exit 10;
-        }
-    }
-        
+        $query->finish;
+
+        if ($so_id_start > $max_so_id) { 
+            print STDERR "at end of so_id range, reset please\n";
+            exit 10;
+        }
+    }
+        
     my $query = $dbh->prepare(
-    "        SELECT
-                storage_object.so_id,
-                ext_id,
-                count(ins_id) as instances,
-                myvolume.name as volume_name,
-                myvolume.host as volume_host,
-                count(mymountedvol.vol_id) as available_instances,
-                count(mymountedvol.vol_id) > 0 as recoverable,
-                storage_object_xattr.value as copies
-            FROM storage_object
-            JOIN instance
-                USING(so_id)
-            JOIN myvolume
-                USING(vol_id)
-            LEFT JOIN storage_object_xattr
-                ON storage_object.so_id = storage_object_xattr.so_id
-            JOIN mymountedvol
-                USING(vol_id)
-            WHERE mymountedvol.available = 1
-            AND storage_object_xattr.name = 'user.copies'
-            AND storage_object.so_id >= $so_id_start
-            AND storage_object.so_id <  $so_id_end
-            GROUP BY so_id
-            HAVING available_instances < instances OR instances < copies
-            LIMIT $limit"
+    "
+        SELECT
+            so.so_id,
+            so.ext_id,
+            count(ins_id) as instances,
+            mv.name as volume_name,
+            mv.host as volume_host,
+            count(mv.vol_id) as available_instances,
+            count(mv.vol_id) > 0 as recoverable,
+            xattr.value as copies
+        FROM storage_object AS so
+        JOIN storage_object_xattr AS xattr
+            ON so.so_id = xattr.so_id
+            AND xattr.name = 'user.copies'
+        JOIN instance AS i
+            ON so.so_id = i.so_id
+        JOIN mountedvol AS mv
+            USING(vol_id)
+        WHERE
+            mv.available = 1
+            AND so.so_id >= $so_id_start
+            AND so.so_id <  $so_id_end
+        GROUP BY so_id
+        ORDER BY so_id DESC
+        HAVING available_instances < instances OR instances < copies
+        LIMIT $limit"
     );
     $query->execute;
 
-    $dbh->do("DROP TABLE IF EXISTS mymountedvol");
+#    $dbh->do("DROP TABLE IF EXISTS mymountedvol");
 
     my @rows;
@@ -174 +174 @@
         # if the copies xattr is unset and the object has 2 or more instances, we
         # will assume a target of 2 copies
-        
-        # XXX change this: there should be no default value or we will
-        # have a race condition.  user.copies should never get set by
-        # any client until AFTER a file is no longer in use.
+        
+        # XXX change this: there should be no default value or we will
+        # have a race condition.  user.copies should never get set by
+        # any client until AFTER a file is no longer in use.
         my $copies = $obj->{copies} || 2;
 
@@ -207 +207 @@
     # use a different exit status if we hit the limit (likely more files pending)
     if ($Npending == $limit) {
-        exit 1;
+        exit 1;
     } 
     exit 0;
@@ -225 +225 @@
 
     $dbh->do("DROP TABLE IF EXISTS mymountedvol");
+    $dbh->do("DROP TABLE IF EXISTS myvolume");
 
     my @rows;