Aaron Brown > perfSONAR_PS-DB-SQL-0.09 > perfSONAR_PS::DB::SQL

Download:
perfSONAR_PS-DB-SQL-0.09.tar.gz

Dependencies

Annotate this POD

View/Report Bugs
Module Version: 0.09   Source  

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.

syntax highlighting: