IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Changeset 13041


Ignore:
Timestamp:
Apr 26, 2007, 10:27:33 AM (19 years ago)
Author:
jhoblitt
Message:

add getmountedvol() stored procedure

Location:
trunk
Files:
3 edited

Legend:

Unmodified
Added
Removed

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

    r13027 r13041  
    11# Copyright (c) 2004  Joshua Hoblitt
    22#
    3 # $Id: SQL.pm,v 1.25 2007-04-25 21:40:29 jhoblitt Exp $
     3# $Id: SQL.pm,v 1.26 2007-04-26 20:27:33 jhoblitt Exp $
    44
    55package Nebulous::Server::SQL;
    … …  
    158158    my @schema;
    159159
    160     local $/ = ';';
     160    local $/ = '###';
    161161
    162162    foreach my $statement (<DATA>) {
    163163        last unless ( $statement =~ /\S+/ );
     164        $statement =~ s/###//g;
    164165        push @schema, $statement;
    165166    }
    … …  
    177178DROP TABLE IF EXISTS mount;
    178179DROP TABLE IF EXISTS class;
    179 DROP TABLE IF EXISTS log
     180DROP TABLE IF EXISTS log;
     181DROP PROCEDURE IF EXISTS getmountedvol
    180182END
    181183    $sql{get_db_clear} = \@clear;
    … …  
    205207    KEY(type)
    206208) ENGINE=innodb;
     209
     210###
    207211
    208212CREATE TABLE storage_object_attr (
    … …  
    217221    KEY(class_id)
    218222) ENGINE=innodb;
     223
     224###
    219225
    220226CREATE TABLE instance (
    … …  
    231237) ENGINE=innodb;
    232238
     239###
     240
    233241CREATE TABLE lock_record (
    234242    so_id BIGINT NOT NULL,
    … …  
    237245    KEY(so_ID)
    238246) ENGINE=innodb;
     247
     248###
    239249
    240250CREATE TABLE volume (
    … …  
    246256) ENGINE=innodb;
    247257
     258###
     259
    248260CREATE TABLE mount (
    249261    mountpoint VARCHAR(255) NOT NULL,
    … …  
    253265) ENGINE=innodb;
    254266
     267###
     268
    255269CREATE TABLE class (
    256270    class_id TINYINT NOT NULL,
    … …  
    260274) ENGINE=innodb;
    261275
     276###
     277
    262278INSERT INTO class VALUES(0,0, 'default class ID');
     279
     280###
    263281
    264282CREATE TABLE log (
    … …  
    270288    PRIMARY KEY(timestamp)
    271289) ENGINE=innodb;
     290
     291###
     292
     293CREATE PROCEDURE getmountedvol() DETERMINISTIC
     294BEGIN
     295    DECLARE done BOOLEAN DEFAULT FALSE;
     296    DECLARE vol_idvar INT;
     297    DECLARE namevar VARCHAR(255);
     298    DECLARE pathvar VARCHAR(255);
     299    DECLARE cur1 CURSOR FOR SELECT vol_id, name, path FROM volume;
     300    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
     301
     302    -- create a temp table to hold the merged results of the volume & mount
     303    -- tables.  One would hope the that the transaction isolation level will
     304    -- stop one session from stomping on another sessions version of this
     305    -- table.
     306
     307    DROP TABLE IF EXISTS mountedvol;
     308    CREATE TEMPORARY TABLE mountedvol(
     309        mountpoint VARCHAR(255) NOT NULL,
     310        total BIGINT NOT NULL,
     311        used BIGINT NOT NULL,
     312        vol_id INT NOT NULL,
     313        name VARCHAR(255) NOT NULL,
     314        path VARCHAR(255) NOT NULL
     315    ) ENGINE=MEMORY;
     316
     317    -- iterator over the volume table finding the coresponding entry in the
     318    -- mount table and inserting union of the volume & mount row into the
     319    -- mountedvol table
     320
     321    OPEN cur1;
     322
     323    myloop: LOOP
     324        FETCH cur1 INTO vol_idvar, namevar, pathvar;
     325        IF `done` THEN LEAVE myloop; END IF;
     326        INSERT INTO mountedvol
     327            SELECT mountpoint, total, used, vol_idvar, namevar, pathvar
     328            FROM
     329                (SELECT *, INSTR(pathvar, mountpoint) = 1 as substring
     330                FROM mount
     331                ORDER BY substring DESC, LENGTH(mountpoint) DESC
     332                LIMIT 1) as bar;
     333    END LOOP myloop;
     334   
     335    CLOSE cur1;
     336END

  • trunk/Nebulous/bin/neb-df

    r13023 r13041  
    33# Copyright (C) 2005  Joshua Hoblitt
    44#
    5 # $Id: neb-df,v 1.1 2007-04-25 19:52:49 jhoblitt Exp $
     5# $Id: neb-df,v 1.2 2007-04-26 20:27:33 jhoblitt Exp $
    66
    77use strict;
    … …  
    4747my $sql = Nebulous::Server::SQL->new();
    4848
    49 # get a list of Nebulous volume names
    50 my $query = $dbh->prepare("SELECT * FROM volume");
     49# ask the db to generate the table of mounted Nebulous volume
     50$dbh->do("call getmountedvol()");
     51
     52# suck that table into an AoH
     53my $query = $dbh->prepare("SELECT * FROM mountedvol");
    5154$query->execute();
    5255
    53 my $mount_query = $dbh->prepare("SELECT *, INSTR(?, mountpoint) = 1 as substring FROM mount ORDER BY substring DESC, LENGTH(mountpoint) DESC LIMIT 1");
    54 
    5556my @stats;
    56 
    57 # use the list of Nebulous volume names to look for the corresponding
    58 # mountpoint
    59 while (my $volume_row = $query->fetchrow_hashref) {
    60     $mount_query->execute($volume_row->{path});
    61     my $mount_row = $mount_query->fetchrow_hashref;
    62 
    63     # assemble a hash that combines the neb volume info with the filesystem
    64     # info
    65     my $vol = {%{$volume_row}, %{$mount_row}};
    66     push @stats, $vol;
     57while (my $mountedvol = $query->fetchrow_hashref) {
     58    push @stats, $mountedvol;
    6759}
    6860

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

    r13027 r13041  
    11# Copyright (c) 2004  Joshua Hoblitt
    22#
    3 # $Id: SQL.pm,v 1.25 2007-04-25 21:40:29 jhoblitt Exp $
     3# $Id: SQL.pm,v 1.26 2007-04-26 20:27:33 jhoblitt Exp $
    44
    55package Nebulous::Server::SQL;
    … …  
    158158    my @schema;
    159159
    160     local $/ = ';';
     160    local $/ = '###';
    161161
    162162    foreach my $statement (<DATA>) {
    163163        last unless ( $statement =~ /\S+/ );
     164        $statement =~ s/###//g;
    164165        push @schema, $statement;
    165166    }
    … …  
    177178DROP TABLE IF EXISTS mount;
    178179DROP TABLE IF EXISTS class;
    179 DROP TABLE IF EXISTS log
     180DROP TABLE IF EXISTS log;
     181DROP PROCEDURE IF EXISTS getmountedvol
    180182END
    181183    $sql{get_db_clear} = \@clear;
    … …  
    205207    KEY(type)
    206208) ENGINE=innodb;
     209
     210###
    207211
    208212CREATE TABLE storage_object_attr (
    … …  
    217221    KEY(class_id)
    218222) ENGINE=innodb;
     223
     224###
    219225
    220226CREATE TABLE instance (
    … …  
    231237) ENGINE=innodb;
    232238
     239###
     240
    233241CREATE TABLE lock_record (
    234242    so_id BIGINT NOT NULL,
    … …  
    237245    KEY(so_ID)
    238246) ENGINE=innodb;
     247
     248###
    239249
    240250CREATE TABLE volume (
    … …  
    246256) ENGINE=innodb;
    247257
     258###
     259
    248260CREATE TABLE mount (
    249261    mountpoint VARCHAR(255) NOT NULL,
    … …  
    253265) ENGINE=innodb;
    254266
     267###
     268
    255269CREATE TABLE class (
    256270    class_id TINYINT NOT NULL,
    … …  
    260274) ENGINE=innodb;
    261275
     276###
     277
    262278INSERT INTO class VALUES(0,0, 'default class ID');
     279
     280###
    263281
    264282CREATE TABLE log (
    … …  
    270288    PRIMARY KEY(timestamp)
    271289) ENGINE=innodb;
     290
     291###
     292
     293CREATE PROCEDURE getmountedvol() DETERMINISTIC
     294BEGIN
     295    DECLARE done BOOLEAN DEFAULT FALSE;
     296    DECLARE vol_idvar INT;
     297    DECLARE namevar VARCHAR(255);
     298    DECLARE pathvar VARCHAR(255);
     299    DECLARE cur1 CURSOR FOR SELECT vol_id, name, path FROM volume;
     300    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
     301
     302    -- create a temp table to hold the merged results of the volume & mount
     303    -- tables.  One would hope the that the transaction isolation level will
     304    -- stop one session from stomping on another sessions version of this
     305    -- table.
     306
     307    DROP TABLE IF EXISTS mountedvol;
     308    CREATE TEMPORARY TABLE mountedvol(
     309        mountpoint VARCHAR(255) NOT NULL,
     310        total BIGINT NOT NULL,
     311        used BIGINT NOT NULL,
     312        vol_id INT NOT NULL,
     313        name VARCHAR(255) NOT NULL,
     314        path VARCHAR(255) NOT NULL
     315    ) ENGINE=MEMORY;
     316
     317    -- iterator over the volume table finding the coresponding entry in the
     318    -- mount table and inserting union of the volume & mount row into the
     319    -- mountedvol table
     320
     321    OPEN cur1;
     322
     323    myloop: LOOP
     324        FETCH cur1 INTO vol_idvar, namevar, pathvar;
     325        IF `done` THEN LEAVE myloop; END IF;
     326        INSERT INTO mountedvol
     327            SELECT mountpoint, total, used, vol_idvar, namevar, pathvar
     328            FROM
     329                (SELECT *, INSTR(pathvar, mountpoint) = 1 as substring
     330                FROM mount
     331                ORDER BY substring DESC, LENGTH(mountpoint) DESC
     332                LIMIT 1) as bar;
     333    END LOOP myloop;
     334   
     335    CLOSE cur1;
     336END
Note: See TracChangeset for help on using the changeset viewer.