Index: /trunk/Nebulous-Server/lib/Nebulous/Server/SQL.pm
===================================================================
--- /trunk/Nebulous-Server/lib/Nebulous/Server/SQL.pm	(revision 13040)
+++ /trunk/Nebulous-Server/lib/Nebulous/Server/SQL.pm	(revision 13041)
@@ -1,5 +1,5 @@
 # Copyright (c) 2004  Joshua Hoblitt
 #
-# $Id: SQL.pm,v 1.25 2007-04-25 21:40:29 jhoblitt Exp $
+# $Id: SQL.pm,v 1.26 2007-04-26 20:27:33 jhoblitt Exp $
 
 package Nebulous::Server::SQL;
@@ -158,8 +158,9 @@
     my @schema;
 
-    local $/ = ';';
+    local $/ = '###';
 
     foreach my $statement (<DATA>) {
         last unless ( $statement =~ /\S+/ );
+        $statement =~ s/###//g;
         push @schema, $statement;
     }
@@ -177,5 +178,6 @@
 DROP TABLE IF EXISTS mount;
 DROP TABLE IF EXISTS class;
-DROP TABLE IF EXISTS log
+DROP TABLE IF EXISTS log;
+DROP PROCEDURE IF EXISTS getmountedvol
 END
     $sql{get_db_clear} = \@clear;
@@ -205,4 +207,6 @@
     KEY(type)
 ) ENGINE=innodb;
+
+###
 
 CREATE TABLE storage_object_attr (
@@ -217,4 +221,6 @@
     KEY(class_id)
 ) ENGINE=innodb;
+
+###
 
 CREATE TABLE instance (
@@ -231,4 +237,6 @@
 ) ENGINE=innodb;
 
+###
+
 CREATE TABLE lock_record (
     so_id BIGINT NOT NULL,
@@ -237,4 +245,6 @@
     KEY(so_ID)
 ) ENGINE=innodb;
+
+###
 
 CREATE TABLE volume (
@@ -246,4 +256,6 @@
 ) ENGINE=innodb;
 
+###
+
 CREATE TABLE mount (
     mountpoint VARCHAR(255) NOT NULL,
@@ -253,4 +265,6 @@
 ) ENGINE=innodb;
 
+###
+
 CREATE TABLE class (
     class_id TINYINT NOT NULL,
@@ -260,5 +274,9 @@
 ) ENGINE=innodb;
 
+###
+
 INSERT INTO class VALUES(0,0, 'default class ID');
+
+###
 
 CREATE TABLE log (
@@ -270,2 +288,49 @@
     PRIMARY KEY(timestamp)
 ) ENGINE=innodb;
+
+###
+
+CREATE PROCEDURE getmountedvol() DETERMINISTIC
+BEGIN
+    DECLARE done BOOLEAN DEFAULT FALSE;
+    DECLARE vol_idvar INT;
+    DECLARE namevar VARCHAR(255);
+    DECLARE pathvar VARCHAR(255);
+    DECLARE cur1 CURSOR FOR SELECT vol_id, name, path FROM volume;
+    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
+
+    -- create a temp table to hold the merged results of the volume & mount
+    -- tables.  One would hope the that the transaction isolation level will
+    -- stop one session from stomping on another sessions version of this
+    -- table.
+
+    DROP TABLE IF EXISTS mountedvol;
+    CREATE TEMPORARY TABLE mountedvol(
+        mountpoint VARCHAR(255) NOT NULL,
+        total BIGINT NOT NULL,
+        used BIGINT NOT NULL,
+        vol_id INT NOT NULL,
+        name VARCHAR(255) NOT NULL,
+        path VARCHAR(255) NOT NULL
+    ) ENGINE=MEMORY;
+
+    -- iterator over the volume table finding the coresponding entry in the
+    -- mount table and inserting union of the volume & mount row into the
+    -- mountedvol table
+
+    OPEN cur1;
+
+    myloop: LOOP
+        FETCH cur1 INTO vol_idvar, namevar, pathvar;
+        IF `done` THEN LEAVE myloop; END IF;
+        INSERT INTO mountedvol
+            SELECT mountpoint, total, used, vol_idvar, namevar, pathvar
+            FROM
+                (SELECT *, INSTR(pathvar, mountpoint) = 1 as substring
+                FROM mount
+                ORDER BY substring DESC, LENGTH(mountpoint) DESC
+                LIMIT 1) as bar;
+    END LOOP myloop;
+    
+    CLOSE cur1;
+END 
Index: /trunk/Nebulous/bin/neb-df
===================================================================
--- /trunk/Nebulous/bin/neb-df	(revision 13040)
+++ /trunk/Nebulous/bin/neb-df	(revision 13041)
@@ -3,5 +3,5 @@
 # Copyright (C) 2005  Joshua Hoblitt
 #
-# $Id: neb-df,v 1.1 2007-04-25 19:52:49 jhoblitt Exp $
+# $Id: neb-df,v 1.2 2007-04-26 20:27:33 jhoblitt Exp $
 
 use strict;
@@ -47,22 +47,14 @@
 my $sql = Nebulous::Server::SQL->new();
 
-# get a list of Nebulous volume names
-my $query = $dbh->prepare("SELECT * FROM volume");
+# ask the db to generate the table of mounted Nebulous volume 
+$dbh->do("call getmountedvol()");
+
+# suck that table into an AoH
+my $query = $dbh->prepare("SELECT * FROM mountedvol");
 $query->execute();
 
-my $mount_query = $dbh->prepare("SELECT *, INSTR(?, mountpoint) = 1 as substring FROM mount ORDER BY substring DESC, LENGTH(mountpoint) DESC LIMIT 1");
-
 my @stats;
-
-# use the list of Nebulous volume names to look for the corresponding
-# mountpoint
-while (my $volume_row = $query->fetchrow_hashref) {
-    $mount_query->execute($volume_row->{path});
-    my $mount_row = $mount_query->fetchrow_hashref;
-
-    # assemble a hash that combines the neb volume info with the filesystem
-    # info
-    my $vol = {%{$volume_row}, %{$mount_row}};
-    push @stats, $vol;
+while (my $mountedvol = $query->fetchrow_hashref) {
+    push @stats, $mountedvol;
 }
 
Index: /trunk/Nebulous/lib/Nebulous/Server/SQL.pm
===================================================================
--- /trunk/Nebulous/lib/Nebulous/Server/SQL.pm	(revision 13040)
+++ /trunk/Nebulous/lib/Nebulous/Server/SQL.pm	(revision 13041)
@@ -1,5 +1,5 @@
 # Copyright (c) 2004  Joshua Hoblitt
 #
-# $Id: SQL.pm,v 1.25 2007-04-25 21:40:29 jhoblitt Exp $
+# $Id: SQL.pm,v 1.26 2007-04-26 20:27:33 jhoblitt Exp $
 
 package Nebulous::Server::SQL;
@@ -158,8 +158,9 @@
     my @schema;
 
-    local $/ = ';';
+    local $/ = '###';
 
     foreach my $statement (<DATA>) {
         last unless ( $statement =~ /\S+/ );
+        $statement =~ s/###//g;
         push @schema, $statement;
     }
@@ -177,5 +178,6 @@
 DROP TABLE IF EXISTS mount;
 DROP TABLE IF EXISTS class;
-DROP TABLE IF EXISTS log
+DROP TABLE IF EXISTS log;
+DROP PROCEDURE IF EXISTS getmountedvol
 END
     $sql{get_db_clear} = \@clear;
@@ -205,4 +207,6 @@
     KEY(type)
 ) ENGINE=innodb;
+
+###
 
 CREATE TABLE storage_object_attr (
@@ -217,4 +221,6 @@
     KEY(class_id)
 ) ENGINE=innodb;
+
+###
 
 CREATE TABLE instance (
@@ -231,4 +237,6 @@
 ) ENGINE=innodb;
 
+###
+
 CREATE TABLE lock_record (
     so_id BIGINT NOT NULL,
@@ -237,4 +245,6 @@
     KEY(so_ID)
 ) ENGINE=innodb;
+
+###
 
 CREATE TABLE volume (
@@ -246,4 +256,6 @@
 ) ENGINE=innodb;
 
+###
+
 CREATE TABLE mount (
     mountpoint VARCHAR(255) NOT NULL,
@@ -253,4 +265,6 @@
 ) ENGINE=innodb;
 
+###
+
 CREATE TABLE class (
     class_id TINYINT NOT NULL,
@@ -260,5 +274,9 @@
 ) ENGINE=innodb;
 
+###
+
 INSERT INTO class VALUES(0,0, 'default class ID');
+
+###
 
 CREATE TABLE log (
@@ -270,2 +288,49 @@
     PRIMARY KEY(timestamp)
 ) ENGINE=innodb;
+
+###
+
+CREATE PROCEDURE getmountedvol() DETERMINISTIC
+BEGIN
+    DECLARE done BOOLEAN DEFAULT FALSE;
+    DECLARE vol_idvar INT;
+    DECLARE namevar VARCHAR(255);
+    DECLARE pathvar VARCHAR(255);
+    DECLARE cur1 CURSOR FOR SELECT vol_id, name, path FROM volume;
+    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
+
+    -- create a temp table to hold the merged results of the volume & mount
+    -- tables.  One would hope the that the transaction isolation level will
+    -- stop one session from stomping on another sessions version of this
+    -- table.
+
+    DROP TABLE IF EXISTS mountedvol;
+    CREATE TEMPORARY TABLE mountedvol(
+        mountpoint VARCHAR(255) NOT NULL,
+        total BIGINT NOT NULL,
+        used BIGINT NOT NULL,
+        vol_id INT NOT NULL,
+        name VARCHAR(255) NOT NULL,
+        path VARCHAR(255) NOT NULL
+    ) ENGINE=MEMORY;
+
+    -- iterator over the volume table finding the coresponding entry in the
+    -- mount table and inserting union of the volume & mount row into the
+    -- mountedvol table
+
+    OPEN cur1;
+
+    myloop: LOOP
+        FETCH cur1 INTO vol_idvar, namevar, pathvar;
+        IF `done` THEN LEAVE myloop; END IF;
+        INSERT INTO mountedvol
+            SELECT mountpoint, total, used, vol_idvar, namevar, pathvar
+            FROM
+                (SELECT *, INSTR(pathvar, mountpoint) = 1 as substring
+                FROM mount
+                ORDER BY substring DESC, LENGTH(mountpoint) DESC
+                LIMIT 1) as bar;
+    END LOOP myloop;
+    
+    CLOSE cur1;
+END