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 help 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.

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

Create a new SQL object. All arguments are optional:

 * name - Name (DBI connection string) of sql based database
 * user - username to connect to said database
 * pass - password for said username
 * schema - array reference of field names for the table

The arguments can be set (and re-set) via the appropriate function calls.

setName($self, { name })

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

setUser($self, { user })

Sets the username for connectecting to 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.

update($self, { table, wherevalues, updatevalues })

Updates items in the database.

remove($self, { delete })

Removes items from the database.

SYNOPSIS

    use perfSONAR_PS::DB::SQL;

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

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

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

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

    my $result = $db->query({ 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 => $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({ table => "data", argvalues => \%dbSchemaValues });
    if($status == -1) {
      print "Error executing insert statement\n";
    }

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

SEE ALSO

DBI, Log::Log4perl, English, Params::Validate, perfSONAR_PS::Common

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$

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-2008, Internet2 and the University of Delaware

All rights reserved.

cpanm perfSONAR_PS::DB::SQL

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