/ 
SysAdmin-0.11
River stage zero No dependents
/ SysAdmin::DB

NAME

SysAdmin::DB - Perl DBI/DBD wrapper module..

SYNOPSIS

  ## Example using PostgreSQL Database
	
  use SysAdmin::DB;
	
  my $db = "dbd_test";
  my $username = "dbd_test";
  my $password = "dbd_test";
  my $host = "localhost";
  my $port = '5432';
  my $driver = "pg"; ## Change to "mysql" for MySQL connection
	
  ### Database Table
  ##
  # create table status(
  # id serial primary key,
  # description varchar(25) not null);
  ##
  ###
	
  my $dbd_object = new SysAdmin::DB(db          => $db,
                                    db_username => $username,
                                    db_password => $password,
                                    db_host     => $host,
                                    db_port     => $port,
                                    db_driver   => $driver);
  ###
  ## DB and DB_DRIVER are always required!
  ###
	
  ###
  ## For databases that need username and password (MySQL, PostgreSQL),
  ## DB_USERNAME and DB_PASSWORD are also required
  ###
	
  ### For SQLite, simply declare DB and DB_DRIVER, example:
  ##
  ## my $db = "/tmp/dbd_test.db";
  ## my $dbd_object = new SysAdmin::DB(db        => $db,
  ##                                   db_driver => sqlite);
  ##
  ###
	
	
  ###
  ## Work with "$dbd_object"
  ###
	
  ### Insert Data
	
  ## SQL Insert statement
  my $insert_table = qq(insert into status (description) values (?));
	
  ## Insert Arguments, to subsitute "?"
  my @insert_table_values = ("DATA");
	
  ## Insert data with "insertData"
	
  $dbd_object->insertData("$insert_table",\@insert_table_values);
	
  ## By declaring a variable, it returns the last inserted ID
	
  my $last_insert_id = $dbd_object->insertData("$insert_table",\@insert_table_values);
	
  ## The insertData Method could also be expressed the following ways
  
  my $insert_table_values = ["Data"];
  $dbd_object->insertData("$insert_table",$insert_table_values);
  
  # or
  
  $dbd_object->insertData("$insert_table",["Data"]);
	

  ### Select Table Data
	
  ## SQL select statement
  my $select_table = qq(select id,description from status);
	
  ## Fetch table data with "fetchTable"
  my $table_results = $dbd_object->fetchTable("$select_table");
	
  ## Extract table data from $table_results array reference
  foreach my $row (@$table_results) {
	
	my ($db_id,$db_description) = @$row;
	
	## Print Results
	print "DB_ID $db_id, DB_DESCRIPTION $db_description\n";
	
  }
	
	
  ### Select Table Row
	
  ## SQL Stament to fetch last insert
  my $fetch_last_insert = qq(select description 
                             from status 
                             where id = $last_insert_id);
	
  ## Fetch table row with "fetchRow"
  my $row_results = $object->fetchRow("$fetch_last_insert");
	
  ## Print Results
  print "Last Insert: $row_results\n";

DESCRIPTION

This is a sub class of SysAdmin. It was created to harness Perl Objects and keep code abstraction to a minimum.

SysAdmin::DB uses perl's DBI and DBD to interact with database.

Currently DBD::Pg, DBD::mysql and DBD::SQLite are supported.

METHODS

new()

	my $dbd_object = new SysAdmin::DB(db          => $db,
                                      db_username => $username,
                                      db_password => $password,
                                      db_host     => $host,
                                      db_port     => $port,
                                      db_driver   => $driver);

Creates SysAdmin::DB object instance. Used to declare the database connection information.

db => $db

State database name to connect to

db_username => $username

State a privileged user to connect to the DB database

db_password => $password,

State a privileged user's password to connect to the DB database

db_host => $host

State the IP address/Hostname of the database server

db_port => $port

State the listening port of the database server. PostgreSQL 5432, MySQL 3306

db_driver => $driver

State the database driver to use. Currently supported: Pg, mysql and SQLite

insertData()

## SQL Insert statement
my $insert_table = qq(insert into status (description) values (?));

## Insert Arguments, to subsitute "?"
my @insert_table_values = ("DATA");

## Insert data with "insertData"

$dbd_object->insertData("$insert_table",\@insert_table_values);

## By declaring a variable, it returns the last inserted ID

my $last_insert_id = $dbd_object->insertData("$insert_table",\@insert_table_values);

fetchTable()

## Select Table Data

## SQL select statement

my $select_table = qq(select id,description from status);

## Fetch table data with "fetchTable"

my $table_results = $dbd_object->fetchTable("$select_table");

## Extract table data from $table_results array reference

foreach my $row (@$table_results) {

	my ($db_id,$db_description) = @$row;

	## Print Results
	print "DB_ID $db_id, DB_DESCRIPTION $db_description\n";

}

fetchRow()

	## Select Table Row
	
	## SQL Stament to fetch last insert
	my $fetch_last_insert = qq(select description 
                               from status 
                               where id = $last_insert_id);
	
	## Fetch table row with "fetchRow"
	
	my $row_results = $object->fetchRow("$fetch_last_insert");
	
	## Print Results
	print "Last Insert: $row_results\n";

SEE ALSO

DBI - Database independent interface for Perl

DBD::Pg - PostgreSQL database driver for the DBI module

DBD::MySQL - MySQL driver for the Perl5 Database Interface (DBI)

DBD::SQLite - Self Contained RDBMS in a DBI Driver

AUTHOR

Miguel A. Rivera

COPYRIGHT AND LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.