NAME

perfSONAR_PS::DB::SQL - A module that provides methods for dealing with common SQL databases.

DESCRIPTION

This module creates common use cases with the helpf of the DBI module. The module is to be treated as an object, where each instance of the object represents a direct connection to a single database and collection. Each method may then be invoked on the object for the specific database.

SYNOPSIS

    use perfSONAR_PS::DB::SQL;

    my @dbSchema = ("id", "time", "value", "eventtype", "misc");
    my $db = new perfSONAR_PS::DB::SQL(
      "DBI:SQLite:dbname=/home/jason/Netradar/MP/SNMP/netradar.db", 
      "",
      "",
      \@dbSchema
    );

    # or also:
    # 
    # my $db = new perfSONAR_PS::DB::SQL;
    # $db->setName("DBI:SQLite:dbname=/home/jason/netradar/MP/SNMP/netradar.db");
    # $db->setUser("");
    # $db->setPass("");    
    # $db->setSchema(\@dbSchema);     

    if ($db->openDB == -1) {
      print "Error opening database\n";
    }

    my $count = $db->count("select * from data");
    if($count == -1) {
      print "Error executing count statement\n";
    }
    else {
      print "There are " , $db->count("select * from data") , " rows in the database.\n";
    }

    my $result = $db->query("select * from data where time < 1163968390 and time > 1163968360");
    if($#result == -1) {
      print "Error executing query statement\n";
    }   
    else { 
      for(my $a = 0; $a <= $#{$result}; $a++) {
        for(my $b = 0; $b <= $#{$result->[$a]}; $b++) {
          print "-->" , $result->[$a][$b] , "\n";
        }
        print "\n";
      }
    }

    my $delete = "delete from data where id = '192.168.1.4-snmp.1.3.6.1.2.1.2.2.1.16-5'";
    $delete = $delete . " and time = '1163968370'";
    my $status = $db->remove($delete);
    if($status == -1) {
      print "Error executing remove statement\n";
    }

    my %dbSchemaValues = (
      id => "192.168.1.4-snmp.1.3.6.1.2.1.2.2.1.16-5", 
      time => 1163968370, 
      value => 9724592, 
      eventtype => "ifOutOctets",  
      misc => ""
    );  
    $status = $db->insert("data", \%dbSchemaValues);
    if($status == -1) {
      print "Error executing insert statement\n";
    }

    if ($db->closeDB == -1) {
      print "Error closing database\n";
    }
       

DETAILS

The DBI module itself offers a lot of choices, we have constructed this module to simplify the amount of setup and handling that must be done when interacting with an SQL based database. The module is to be treated as an object, where each instance of the object represents a direct connection to an SQL database. Each method may then be invoked on the object for the specific database.

API

The API of perfSONAR_PS::DB::SQL is rather simple, and attempts to mirror the API of the other perfSONAR_PS::DB::* modules.

new($package, $name, $user, $pass, $schema)

The first argument is the 'name' of the database (written as a DBI connection string), and the second and third arguments are the username and password (if any) used to connect to the database. The final argument is the table 'schema' for the database. At current time only a single table is supported. The '$name' must be of the DBI connection format which specifies a 'type' of database (MySQL, SQLite, etc) as well as a path or other connection method. It is important that you have the proper DBI modules installed for the specific database you will be attempting to access.

setName($self, $name)

Sets the name of the database (write as a DBI connection string).

setUser($self, $user)

Sets the user of the database.

setPass($self, $pass)

Sets the password for the database.

setSchema($self, $schema)

Sets the schema of the database (as a table).

openDB($self)

Opens the dabatase.

closeDB($self)

Closes the database.

query($self, $query)

Queries the database.

count($self, $query)

Counts the number of results of a query in the database.

insert($self, $table, $argvalues)

Inserts items in the database.

remove($self, $delete)

Removes items from the database.

SEE ALSO

DBI, perfSONAR_PS::Common, Log::Log4perl

To join the 'perfSONAR-PS' mailing list, please visit:

  https://mail.internet2.edu/wws/info/i2-perfsonar

The perfSONAR-PS subversion repository is located at:

  https://svn.internet2.edu/svn/perfSONAR-PS 
  

Questions and comments can be directed to the author, or the mailing list.

VERSION

$Id: SNMP.pm 227 2007-06-13 12:25:52Z zurawski $

AUTHOR

Jason Zurawski, zurawski@internet2.edu

LICENSE

You should have received a copy of the Internet2 Intellectual Property Framework along with this software. If not, see <http://www.internet2.edu/membership/ip.html>

COPYRIGHT

Copyright (c) 2004-2007, Internet2 and the University of Delaware

All rights reserved.

cpanm perfSONAR_PS::DB::SQL

perl -MCPAN -e shell
install perfSONAR_PS::DB::SQL