The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
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.