Index: trunk/Nebulous/lib/Nebulous/Server/SQL.pm
===================================================================
--- trunk/Nebulous/lib/Nebulous/Server/SQL.pm	(revision 13027)
+++ 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