Changeset 13041 for trunk/Nebulous-Server/lib/Nebulous/Server/SQL.pm

Timestamp:

Apr 26, 2007, 10:27:33 AM (19 years ago)

Author:

jhoblitt

Message:

add getmountedvol() stored procedure

File:

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

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

@@ -1 +1 @@
 # 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 +158 @@
     my @schema;
 
-    local $/ = ';';
+    local $/ = '###';
 
     foreach my $statement (<DATA>) {
         last unless ( $statement =~ /\S+/ );
+        $statement =~ s/###//g;
         push @schema, $statement;
     }
@@ -177 +178 @@
 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 +207 @@
     KEY(type)
 ) ENGINE=innodb;
+
+###
 
 CREATE TABLE storage_object_attr (
@@ -217 +221 @@
     KEY(class_id)
 ) ENGINE=innodb;
+
+###
 
 CREATE TABLE instance (
@@ -231 +237 @@
 ) ENGINE=innodb;
 
+###
+
 CREATE TABLE lock_record (
     so_id BIGINT NOT NULL,
@@ -237 +245 @@
     KEY(so_ID)
 ) ENGINE=innodb;
+
+###
 
 CREATE TABLE volume (
@@ -246 +256 @@
 ) ENGINE=innodb;
 
+###
+
 CREATE TABLE mount (
     mountpoint VARCHAR(255) NOT NULL,
@@ -253 +265 @@
 ) ENGINE=innodb;
 
+###
+
 CREATE TABLE class (
     class_id TINYINT NOT NULL,
@@ -260 +274 @@
 ) ENGINE=innodb;
 
+###
+
 INSERT INTO class VALUES(0,0, 'default class ID');
+
+###
 
 CREATE TABLE log (
@@ -270 +288 @@
     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