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);
	
	
	## 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.

cpanm SysAdmin

perl -MCPAN -e shell
install SysAdmin