NAME
Relations::Abstract - DBI/DBD::mysql Functions to Save Development Time and Code Space
SYNOPSIS
# DBI/Relations Script that creates a couple tables and adds to them.
use DBI;
use Relations::Abstract;
$dsn = "DBI:mysql:mysql";
$dbh = DBI->connect($dsn,$username,$password,{PrintError => 1, RaiseError => 0});
# Create a Relations::Abstract object using the database handle
$abs = new Relations::Abstract($dbh);
# Drop, create and use a database
$abs->run_query("drop database if exists abs_test");
$abs->run_query("create database abs_test");
$abs->run_query("use abs_test");
# Create a table
$abs->run_query("
create table sizes
(
size_id int unsigned auto_increment,
num int unsigned,
descr varchar(16),
primary key (size_id),
unique descr (descr),
unique num (num),
index (size_id)
)
");
# Retreive size 12 if already within the database, else add
# size 12 information into the database and get its size_id.
$size_id = select_insert_id(-dbh => $dbh,
-id => 'size_id',
-table => "sizes",
-where => {num => 12},
-set => {num => 12,
description => $dbh->quote('Bigfoot')});ABSTRACT
This perl library uses perl5 objects to simplify using the DBI DBD::mysql modules. It takes the most common (in my experience) collection of DBI calls to a MySQL databate, and changes them to one liners. It utilizes a object-oriented programming style.
The current version of Relations is available at
http://www.gaf3.comDESCRIPTION
WHAT IT DOES
All Abstract does is take information about what you want to do to a database and does it by creating and executing SQL statements via DBI. That's it. It's there just to simplify the amount of code one has to write and maintain with respect long and complex database tasks.
The simplest example is the run_query function. It takes a SQL string (and an optional dbh) and prepares, executes, and finishes that SQL string via DBI.
$abs->run_query("drop database if exists abs_test");This puts "drop database if exists abs_test" through the prepare, execute, and finish functions of DBI.
The most complex example is the select_insert_id function. Its used for either looking up a certain record's primary id value if it already exists in the table, or adding that record and retreiving its new primary id value if it does not already exist in the table.
$size_id = $abs->select_insert_id(-dbh => $dbh,
-id => 'size_id',
-table => "sizes",
-where => {num => 12},
-set => {num => 12,
description => $dbh->quote('Bigfoot')});This puts several SQL string through the prepare, execute, and finish functions of DBI.
First using the primary id name, the table name, and the where clause, select_insert_id creates the SQL statement, "select size_id from sizes where num=12", and prepares, executes, and finishes it. If a row is returned, select_insert_id returns the looked up value of size_id.
If a row is not returned, select_insert_id then creates a another SQL statement, "insert into sizes set num=12,description='Bigfoot' " using the table name, and set clause, and puts it through DBI. After that, it runs another SQL statement "select last_insert_id() as id" to retrieve the new primary id value for the new record. Though the function is long, it is certainly shorter than 9 calls to DBI functions, and a few if-else's.
CALLING RELATIONS::ABSTRACT ROUTINES
All standard Abstract routines use both an ordered and named argument calling style. This is because some routines have as many as five arguments, and the code is easier to understand given a named argument style, but since some people, however, prefer the ordered argument style because its smaller, I'm glad to do that too.
If you use the ordered argument calling style, such as
$hash_ref = $abs->select_row('sizes',{num => 10});the order matters, and you should consult the function defintions later in this document to determine the order to use.
If you use the named argument calling style, such as
$hash_ref = $abs->select_row(-table => 'sizes',
-where => {num => 10});the order does not matter, but the names, and minus signs preceeding them, do. You should consult the function defintions later in this document to determine the names to use.
In the named arugment style, each argument name is preceded by a dash. Neither case nor order matters in the argument list. -table, -Table, and -TABLE are all acceptable. In fact, only the first argument needs to begin with a dash. If a dash is present in the first argument, Relations.pm assumes dashes for the subsequent ones.
WHERE AND SET CLAUSES
Many of the Relations functions require arguments named where and set. These arugments are used to populate (respectively) the 'where' and 'set' areas of SQL statements. Since both these areas can require a varying number of entries, each can be sent as a hash, array, or string.
WHERE FUNCTIONALITY
If sent as a hash, a where argument would become a string of $key=$value pairs, concatented with an ' and ' and placed right after the where keyword.
For example,
$hash_ref = $abs->select_row(-table => 'sizes',
-where => {num => 10,
description => $dbh->quote('Momma Bear')});creates and executes the SQL statment "select * from sizes where num=10 and description='Momma Bear'".
If sent as an array, a where argument would become a string of array members, concatented with an ' and '. and placed right after the 'where' keyword.
For example,
$hash_ref = $abs->select_row(-table => 'sizes',
-where => ["num < 8",
"description not in ('Momma Bear','Papa Bear')"]);creates and executes the SQL statment "select * from sizes where num < 8 and description not in ('Momma Bear','Papa Bear')".
If sent as a string, a where is placed as is right after the 'where' keyword.
For example,
$hash_ref = $abs->select_row(-table => 'sizes',
-where => "num > 10 or (num < 5 and num > 0)");creates and executes the SQL statment "select * from sizes where num < 8 or (num < 5 and num > 0)".
SET FUNCTIONALITY
If sent as a hash, a set argument would become a string of $key=$value pairs, concatented with an ',' and placed right after the 'set' keyword.
For example,
$abs->insert_row(-table => 'sizes',
-set => {num => 7,
description => $dbh->quote('Goldilocks')});creates and executes the SQL statment "insert into sizes set num=7, description='Goldilocks'".
If sent as an array, a set argument would become a string of array members, concatented with an ','. and placed right after the 'set' keyword.
For example,
$abs->insert_row(-table => 'sizes',
-set => ["num=7",
"description='Goldilocks'"]);creates and executes the SQL statment "insert into sizes set num=7, description='Goldilocks'".
If sent as a string, a set argument is placed as is right after the 'set' keyword.
For example,
$abs->insert_row(-table => 'sizes',
-set => "num=7,description='Goldilocks'");creates and executes the SQL statment "insert into sizes set num=7, description='Goldilocks'".
I'm not sure if the set argument needs to be so flexible, but I thought I'd make it that way, just in case.
LIST OF RELATIONS::ABSTRACT FUNCTIONS
An example of each function is provided in 'test.pl'.
new
$abs = Relations::Abstract->new($dbh);
$abs = new Relations::Abstract(-dbh => $dbh);delete_rows
$abs->delete_rows($table,$where,$set);
$abs->delete_rows(-table => $table,
-where => $where,
-set => $set);Deletes all records from $table that satisfy the $where clause. Uses an SQL statement in the form:
delete from $table where $where;insert_id
$abs->insert_id($table,$set);
$abs->insert_id(-table => $table,
-set => $set);For tables with auto incrementing primary keys. Inserts $set into $table and returns the new primary key value. Uses SQL statements in the form:
insert into $table set $set;
select last_insert_id() as id;insert_row
$abs->insert_row($table,$set);
$abs->insert_row(-table => $table,
-set => $set);Inserts a row of set into a table. Uses SQL statements in the form:
insert into $table set $set;run_query
$abs->run_query($query);
$abs->run_query(-query => $query);Runs the given query, $query.
select_column
$array_ref = $abs->select_column($field,$table,$where);
$array_ref = $abs->select_column(-field => $field,
-table => $table,
-where => $where);
$array_ref = $abs->select_column(-field => $field,
-query => $query);Returns an array reference of all $field values from $table that satisfy the $where clause. It can also grab all $field's values from the query specified by $query, which can be a string or a Relations::Query object. Uses SQL statements in the form:
select $field from $table where $where; or
$query; select_field
$value = select_field($field,$table,$where);
$value = select_field(-field => $field,
-table => $table,
-where => $where);
$value = select_field(-field => $field,
-query => $query);Returns the first $field value from $table that satisfies the $where clause. It can also grab $field's value from the query specified by $query, which can be a string or a Relations::Query object. Uses SQL statements in the form: Uses SQL statements in the form:
select $field from $table where $where; or
$query; select_insert_id
select_insert_id($id,$table,$where,$set);
select_insert_id(-id => $id,
-table => $table,
-where => $where,
-set => $set);For tables with auto incrementing primary keys. It first tries to return the first $id values from $table that satisfies the criteria defined by $where. If that doesn't work, it then inserts $set into $table, and returns the newly generated primary id. It does not use $id to lookup the primary id value. It uses SQL statements in the form:
select $id from $table where $where;
insert into $table set $set;
select last_insert_id() as id;select_matrix
$array_ref = select_matrix($table,$where);
$array_ref = select_matrix(-table => $table,
-where => $where);Returns an array reference of hash references of all rows $table that satisfy the $where clause. It can also grab all values from the query specified by $query, which can be a string or a Relations::Query object. Uses SQL statements in the form:
select * from $table where $where; or
$query; select_row
$hash_ref = select_row($table,$where);
$hash_ref = select_row(-table => $table,
-where => $where);Returns a hash reference for the first row in $table that satisfies the criteria set by $where. It can also grab the first row from the query specified by $query, which can be a string or a Relations::Query object. Uses SQL statements in the form:
select * from $table where $where; or
$query; set_dbh
set_dbh($dbh);
set_dbh(-dbh => $dbh);Sets the default database handle to use for all DBI calls. This $dbh can be overridden in any of the other functions by sending another $dbh as the last ordered argument, or as the -dbh named argument.
update_rows
update_rows($table,$where,$set);
update_rows(-table => $table,
-where => $where,
-set => $set);Updates all rows in $table that satisfy the $where clause with $set. Uses SQL statements in the form:
update $table set $set where $where;TODO LIST
Object Oriented interface
Add select_row_array, select_row_arrayref, and select_row_hashref.
OTHER RELATED WORK
Relations
This perl library contains functions for dealing with databases. It's mainly used as the the foundation for all the other Relations modules. It may be useful for people that deal with databases in Perl as well.
Relations::Abstract
A DBI/DBD::mysql Perl module. Meant to save development time and code space. It takes the most common (in my experience) collection of DBI calls to a MySQL databate, and changes them to one liner calls to an object.
Relations::Query
An Perl object oriented form of a SQL select query. Takes hash refs, array refs, or strings for different clauses (select,where,limit) and creates a string for each clause. Also allows users to add to existing clauses. Returns a string which can then be sent to a MySQL DBI handle.
Relations.Admin.inc.php
Some generalized PHP classes for creating Web interfaces to relational databases. Allows users to add, view, update, and delete records from different tables. It has functionality to use tables as lookup values for records in other tables.
Relations::Family
A Perl query engine for relational databases. It queries members from any table in a relational database using members selected from any other tables in the relational database. This is especially useful with complex databases; databases with many tables and many connections between tables.
Relations::Display
An Perl module creating GD::Graph objects from database queries. It takes in a query through a Relations::Query object, along with information pertaining to which field values from the query results are to be used in creating the graph title, x axis label and titles, legend label (not used on the graph) and titles, and y axis data. Returns a GD::Graph object built from from the query.
Relations::Choice
An Perl CGI interface for Relations::Family, Reations::Query, and Relations::Display. It creates complex (too complex?) web pages for selecting from the different tables in a Relations::Family object. It also has controls for specifying the grouping and ordering of data with a Relations::Query object, which is also based on selections in the Relations::Family object. That Relations::Query can then be passed to a Relations::Display object, and a graph or table will be displayed. A working model already exists in a production enviroment. I'd like to streamline it, and add some more functionality before releasing it to the world. Shooting for early mid Summer 2001.