Relations::Abstract - DBI/DBD::mysql Functions to Save Development Time and Code Space
use DBI; use Relations::Abstract; $dsn = "DBI:mysql:mysql"; $abstract = 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 $abstract->run_query("drop database if exists abs_test"); $abstract->run_query("create database abs_test"); $abstract->run_query("use abs_test"); # Create a table $abstract->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 = $abstract->select_insert_id(-id => 'size_id', -table => "sizes", -where => {num => 12}, -set => {num => 12, descr => $abstract->{dbh}->quote('Bigfoot')}); $abstract->report_error("Nothing left to demo!");
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.com
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 prepares, executes, and finishes that SQL string via DBI.
$abstract->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 = $abstract->select_insert_id(-id => 'size_id', -table => "sizes", -where => {num => 12}, -set => {num => 12, descr => $abstract->{dbh}->quote('Bigfoot')});
This puts several SQL strings 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,descr='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.
Some Abstract routines, those with more than one argument, use an ordered, named and hashed argument calling style, and some, those with only one argument, just use an ordered 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. Furthermore, if you want to name a single argument in a function, you're a bit loopy.
If you use the ordered argument calling style, such as
$hash_ref = $abstract->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 = $abstract->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::Abstract assumes dashes for the subsequent ones.
If you use the hashed argument calling style, such as
$hash_ref = $abstract->select_row({table => 'sizes', where => {num => 10}});
or
$hash_ref = $abstract->select_row({-table => 'sizes', -where => {num => 10}});
the order does not matter, but the names, and curly braces do, (minus signs are optional). You should consult the function defintions later in this document to determine the names to use.
In the hashed arugment style, no dashes are needed, but they won't cause problems if you put them in. Neither case nor order matters in the argument list. table, Table, and TABLE are all acceptable. If a hash is the first argument, Relations::Abstract assumes that is the only argument that matters, and ignores any other arguments after the {}'s.
Many of the Relations functions recognize arguments named where and set. These arguments 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 = $abstract->select_row(-table => 'sizes', -where => {num => 10, descr => $dbh->quote('Momma Bear')});
creates and executes the SQL statment "select * from sizes where num=10 and descr='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.
$hash_ref = $abstract->select_row(-table => 'sizes', -where => ["num < 8", "descr not in ('Momma Bear','Papa Bear')"]);
creates and executes the SQL statment "select * from sizes where num < 8 and descr not in ('Momma Bear','Papa Bear')".
If sent as a string, a where argument is placed as is right after the 'where' keyword.
$hash_ref = $abstract->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.
$abstract->insert_row(-table => 'sizes', -set => {num => 7, descr => $dbh->quote('Goldilocks')});
creates and executes the SQL statment "insert into sizes set num=7, descr='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.
$abstract->insert_row(-table => 'sizes', -set => ["num=7", "descr='Goldilocks'"]);
If sent as a string, a set argument is placed as is right after the 'set' keyword.
$abstract->insert_row(-table => 'sizes', -set => "num=7,descr='Goldilocks'");
creates and executes the SQL statment "insert into sizes set num=7,descr='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.
Many of the Relations functions recognize an argument named query. This argument can either be a string, hash or Relations::Query object.
The following calls are all equivalent
$object->function("select nothing from void"); $object->function({select => 'nothing', from => 'void'}); $object->function(Relations::Query->new(-select => 'nothing', -from => 'void'));
Since whatever query value is sent to Relations::Query's to_string() function, consult the to_string() function in the Relations::Query documentation for more (just a little really) information.
An example of each function is provided in 'test.pl'.
$abstract = Relations::Abstract->new($dbh);
Creates a new abstract object using the DBI database handle. This handle will be used for all DBI interactions.
$abstract->set_dbh($dbh);
Sets the default database handle to use for all DBI calls.
$abstract->run_query($query);
Runs the given query, $query.
$value = $abstract->select_field($field,$table,$where); $value = $abstract->select_field(-field => $field, -table => $table, -where => $where); $value = $abstract->select_field(-field => $field, -query => $query);
Returns the first $field value from $table that satisfies $where. It can also grab $field's value from the query specified by $query. Uses SQL statements in the form:
select $field from $table where $where or $query
$hash_ref = $abstract->select_row($table,$where); $hash_ref = $abstract->select_row(-table => $table, -where => $where); $hash_ref = $abstract->select_row(-query => $query);
Returns a hash reference for the first row in $table that satisfies $where. It can also grab the first row from the query specified by $query. Uses SQL statements in the form:
select * from $table where $where or $query
$array_ref = $abstract->select_column($field,$table,$where); $array_ref = $abstract->select_column(-field => $field, -table => $table, -where => $where); $array_ref = $abstract->select_column(-field => $field, -query => $query);
Returns an array reference of all $field values from $table that satisfy $where. It can also grab all $field's values from the query specified by $query. Uses SQL statements in the form:
$array_ref = $abstract->select_matrix($table,$where); $array_ref = $abstract->select_matrix(-table => $table, -where => $where); $array_ref = $abstract->select_matrix(-query => $query);
Returns an array reference of hash references of all rows in $table that satisfy $where. It can also grab all values from the query specified by $query. Uses SQL statements in the form:
select * from $table where $where or $query;
$abstract->insert_row($table,$set); $abstract->insert_row(-table => $table, -set => $set);
Inserts $set into $table. Uses SQL statements in the form:
insert into $table set $set
$abstract->insert_id($table,$set); $abstract->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 and select last_insert_id() as id
$abstract->select_insert_id($id,$table,$where,$set); $abstract->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 value from $table that satisfies $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, but instead last_insert_id(). It uses SQL statements in the form:
select $id from $table where $where and (if the first returns nothing) { insert into $table set $set and select last_insert_id() as id }
$abstract->update_rows($table,$where,$set); $abstract->update_rows(-table => $table, -where => $where, -set => $set);
Updates all rows in $table that satisfy $where with $set. Uses an SQL statements in the form:
update $table set $set where $where
$abstract->delete_rows($table,$where); $abstract->delete_rows(-table => $table, -where => $where);
Deletes all records from $table that satisfy $where. Uses an SQL statement in the form:
delete from $table where $where
$abstract->report_error($message);
Reports an error if the dbh PrintError is set to true. Always returns nothing with "return;" so you can use it to return a a null value when something fails.
The DBI database handle.
Contains functions for dealing with databases. It's mainly used as the foundation for the other Relations modules. It may be useful for people that deal with databases as well.
An object oriented form of a SQL select query. Takes hashes. arrays, 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 database.
Meant to save development time and code space. It takes the most common (in my experience) collection of calls to a MySQL database, and changes them to one liner calls to an object.
Some generalized objects for creating Web interfaces to relational databases. Allows users to insert, select, update, and delete records from different tables. It has functionality to use tables as lookup values for records in other tables.
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.
Module creating graphs 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 graph and/or table built from from the query.
An Web 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 and/or table will be displayed.
To install Relations::Abstract, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Relations::Abstract
CPAN shell
perl -MCPAN -e shell install Relations::Abstract
For more information on module installation, please visit the detailed CPAN module installation guide.