#######
# dbwide #
#######
# Software : dbwide.pm
# Primary Author : Zohaer Naqvi (zohaer.com@gmail.com)
# Moderators/Improvers : Add your name here if you modify this perl module.
# You are free to copy, alter, distribute or re-distribute this software under the terms of Artistic License 2.0


This document is an introduction and usage documentation for the dbwide perl module.

Table of contents:

1.1 Introduction
1.2 Purpose
1.3 Scope
1.4 Installation
1.5 Syntax
1.6 Case study
1.7 Code Glossary
1.8 Dependencies


1.1 Introduction

dbwide is a perl module for performing SQL operations on all tables in a database, in one shot.
It has been designed to be flexible and highly extensible under the design schemes "Prototype Creational pattern".and semi-"Functional design".
It replaces the traditional new() constructor with prototype constructors that are called by different objects for different purposes.


1.2 Purpose

Sometimes we need to perform one kind of SQL operation across the entire database. This is seen more often when you wish to remove
a user from your portal's db or when you simply wish to load all data that is associated with one user. And not just that, you also need to do this in a secure manner that follows your design protocols, what if your db server doesn't support stored procedures? This is when dbwide comes into the picture to provide a handy interface for performing such SQL operations in the global context. The module itself is written in simplest possible OO perl and well commented so any user may also add his own functionality specific to his code.


1.3 Scope

Currently there are only two prototype constructors in this module, that are: _select and _delete.
_select returns a hash of hashes object in the format [Object]{[table]}->{[field]}
_delete returns a Boolean value 1 for successful deletion and 0 if delete fails.



1.4 Installation

There is no installation or build required, just one file (dbwide.pm) is to be downloaded and included in your program through "use dbwide;"


1.5 Syntax

#1 Instantiation
[object_variable1] = [constructor _select or _delete] dbwide([SQL WHERE Specification]);

#2 Resource allocation
[object_varible2] = [object_variable1]->connection([DB connection object]);

#3.1 Operation (select)
[boolean_variable1] = [object_variable1]->fetchrows();

#4 Catching the result object (specific to _select)
[hash1] = %{[object_variable1]->access("_properties")};

#3.2 Operation (delete)
[boolean_variable1] = [variable1]->delrows();


1.6 Case study

Lets see the usage of dbwide in an admin panel utility where the web administrator needs a fingerprint of all the activities and other data about a user.

#Prerequisite
my $conn = DBI->connect("DBI:mysql:somedb:localhost","someuser","someshit") || die qq{ Cannot connect bacause $DBI::errstr };

#step 1
my $user = _select dbwide("WHERE userkey='2'");

#step 2
my $connection = $user->connection($conn);

#step 3
my $is_fetched = $user->fetchrows();

#step 4
my %userobject = %{$user->access("_properties")};

#step 5 (not a step actually) use the info your way
print $userobject{'users'}->{'username'} . " has sent is first message to : " . $userobject{'privmsgs'}->[0]->{'to'};

The admin finds this user malicious and wants to get rid of him and all his stuff.

#step 1
my $remuser = _delete dbwide("WHERE userkey='1'");

#step 2
my $connection = $remuser->connection($conn);

#step 3
if($remuser->delrows()){
    print "User deleted!";
}
else {
    print "Delete failed!";
}



1.7 Code glossary

1. Globals:

1.1 @tables - [Array] holds the list of all tables in the subject database.
1.2 %resultobj - [Hash] holds the buffer to which the SQL results are written (the end output).
1.3 $fetchrows - [Scalar] lock/unlock switch for the fetchrows method
1.4 $delrows - [Scalar] lock/unlock switch for the delrows method

2. Methods:

2.1 Constructor methods:
 2.1.1 _select - Instantiates the select prototype object - locks: delrows() unlocks: fetchrows() - takes [string "SQL"] - returns [object]
 2.1.2 _delete - Instantiates the delete prototype object - locks: fechrows() unlocks: delrows() - takes [string "SQL"] returns [object]

2.2 Control methods:
 2.2.1 access() - Provides anonymous access to structural elements of the class for non-members. - takes [string keyname] - returns [hash key]
 2.2.2 sub_lock() - Locks the subroutines supplied in an array by setting each one's switch variable to 0. - takes [array subnames] - returns [null]
 2.2.3 sub_unlock() - Unlocks the subroutines supplied in an array by setting each one's switch variable to 1. - takes [array subnames] - returns [null]

2.3 Resource methods:
 2.3.1 connection() - When supplied with a parameter, sets it as the db connection resource - takes [connection object] - returns: [connection object]
 2.3.2 populate() - Populates the @tables global with a list of all tables in the database.- takes [nothing] - returns [boolean]

2.4 Operation methods:
 2.4.1 fetchrows() - fetches all rows that satisfy the SQL "WHERE" condition. Writes it to [Class]{_properties} of the object.
 2.4.2 delrows() - deletes all rows that satisfy the SQL "WHERE" condtion. Writes the operation status to [Class]{_properties}.


1.8 Dependencies

dbwide Requires the DBI perl module to be used parallel to it.

Example:
#!/usr/bin/perl
use strict; #optional
use DBI; #mandatory
use dbwide; #mandatory



Author's note:

Although dbwide is designed to be cross-OS, cross-DB and cross-Server,
it has been tested by me only through CGI and on:

Operating System : Linux
Kernel version  : 2.6.16.16-grsec
Machine Type : i686
Apache version : 1.3.36 (Unix)
PERL version : 5.8.5
MySQL version : 4.1.19-standard

Any bug reporting is greatly appreciated.
-- 
Good architects write the documentation before actually developing the software!