Changeset 20175

Timestamp:

Oct 15, 2008, 10:45:51 AM (18 years ago)

Author:

jhoblitt

Message:

add find_objects_with_extra_instances query
change get_mounted_volumes to sort by host then name

File:

• trunk/Nebulous-Server/lib/Nebulous/Server/SQL.pm (modified) (2 diffs)

trunk/Nebulous-Server/lib/Nebulous/Server/SQL.pm

@@ -1 +1 @@
 # Copyright (c) 2004  Joshua Hoblitt
 #
-# $Id: SQL.pm,v 1.74 2008-10-15 01:56:00 jhoblitt Exp $
+# $Id: SQL.pm,v 1.75 2008-10-15 20:45:51 jhoblitt Exp $
 
 package Nebulous::Server::SQL;
@@ -324 +324 @@
         HAVING available_instances < instances OR instances < copies
     },
+    find_objects_with_extra_instances => qq{
+        SELECT
+            storage_object.so_id,
+            ext_id,
+            count(ins_id) as instances,
+            volume.name as volume_name,
+            volume.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 volume
+            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'
+        GROUP BY so_id
+        HAVING available_instances > copies
+    },
     get_mounted_volumes => qq{
-        SELECT * FROM mountedvol ORDER BY vol_id
+        SELECT * FROM mountedvol ORDER BY host, name
     },
 );