NAME
Relations - Functions to Use with Databases and Queries
SYNOPSIS
use Relations;
$as_clause = as_clause({full_name => "concat(f_name,' ',l_name)",
{status => "if(married,'Married','Single')"})
$query = "select $as_clause from person";
$avoid = to_hash('virus,bug');
if ($avoid->{bug}) {
print "Avoiding the bug...";
}
unless ($avoid->{code}) {
print "Not avoiding the code...";
}
configure_settings(-database => 'relations',
-username => 'root',
-password => '',
-host => 'localhost',
-port => 3306);ABSTRACT
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.
The current version of Relations is available at
http://www.gaf3.comDESCRIPTION
WHAT IT DOES
Relations has functions for creating clauses of queries (like where, from etc.) from hashes, arrays and strings. It also has functions for converting strings to arrays or hashes, if they're not hashes or arrays already. It even has an argument parser, which is used quite heavily by the other Relations modules.
CALLING RELATIONS ROUTINES
All standard Relations routines use an ordered argument calling style. This is because the routines have very few arguments, and they're mainly used bu other modules with far friendlier objects and functions. So the argument order matters for all functions, and you should consult the function defintions later in this document to determine the proper order to use.
LIST OF RELATIONS FUNCTIONS
An example of each function is provided in 'test.pl'. When you run it, don't be surprised if a bunch of stuff appears on the screen appearing to ask then answer questions. That's just test.pl pretending to be you while it talks to itself. :)
rearrange
my (@arg_list) = rearrange($order,@param]);Rearranges arguments from either the straight ordered format, or named format, into their respective variables.
$order - Array ref to of argument names in their proper order. Names must be capitalized.
@param - Array of values to parse.
EXAMPLES
Using:
sub example {
# Get the defaults sent.
my ($first,
$second,
$third) = rearrange(['FIRST',
'SECOND',
'THIRD'],@_);
}Calling:
example('one','two','three');
example(-first => 'one',
-second => 'two',
-third => 'three');delimit_clause
delimit_clause($minor,$major,$reverse,$clause);Creates a clause for a query from a hash ref,0 an array ref, or string. If sent a hash ref, the field-value pairs will be concatentated with the minor string and those pairs will be concatenated with the major string, and that string returned. If an array ref is sent, the members of the array with will be concatenated with the major string, and that string returned. If a string is sent, that string will be returned.
$minor - String to use to concatenate between the $clause hash ref key and value.
$major - String to use as the key-value pair if $clause is a hash ref, or array members if $clause is an array ref.
$reverse - Value indicating whether to concatenate keys and values if $clause is a hash ref in key-value order ($reverse is false), or value-key order ($reverse is true).
$clause - Info to parse into a clause. Can be a hash ref, array ref, or string.
as_clause
as_clause($as);Creates a 'select' or 'from' clause for a query from a hash ref, an array ref, or string. If sent a hash ref, the field-value pairs will be concatentated with an ' as ' between each value-key pair and those pairs will be concatenated with a ',' , and that string returned. If an array ref is sent, the members of the array with will be concatenated with a ',' and that string returned. If a string is sent, that string will be returned.
$as - Info to parse into a clause. Can be a hash ref, array ref, or string.
EXAMPLES
Hash:
as_clause({full_name => "concat(f_name,' ',l_name)", {status => "if(married,'Married','Single')"})
returns: "concat(f_name,' ',l_name) as full_name,if(married,'Married','Single') as status"
Array:
as_clause(['phone_num','address'])
returns: "phone_num,address"
String:
as_clause("if(car='blue','sweet','ug') as dude,sweet")
returns: "if(car='found','sweet','ug') as dude,sweet"
equals_clause
equals_clause($equals);Creates a 'where' or 'having' clause for a query from a hash ref, array ref, or string. If sent a hash ref, the field-value pairs will be concatentated with an '=' between each value-key pair and those pairs will be concatenated with a ' and ' , and that string returned. If an array ref is sent, the members of the array with will be concatenated with a ' and ' and that string returned. If a string is sent, that string will be returned.
$equals - Info to parse into a clause. Can be a hash ref, array ref, or string.
EXAMPLES
Hash:
equals_clause({man => "'strong'", {woman => "'confident'"})
returns: "man='strong' and woman='confident'"
Array:
equals_clause(["Age > 40","Hair='grey'"])
returns: "Age > 40 and Hair='grey'"
String:
equals_clause("reason is not null or intuition > 25")
returns: "reason is not null or intuition > 25"
comma_clause
comma_clause($equals);Creates a 'group by', 'order by' or 'limit' clause for a query from an array ref or string. If an array is sent, the members of the array with will be concatenated with a ',' and that string returned. If a string is sent, that string will be returned.
$comma - Info to parse into a clause. Can be an array ref, or string.
EXAMPLES
Array:
comma_clause(["Age > 40","Hair='grey'"])
returns: "Age > 40 and Hair='grey'"
String:
comma_clause("reason is not null or intuition > 25")
returns: "reason is not null or intuition > 25"
assign_clause
assign_clause($assign);Creates a 'set' clause for a query from a hash ref, array ref, or string. If sent a hash ref, the field-value pairs will be concatentated with an '=' between each value-key pair and those pairs will be concatenated with a ',' , and that string returned. If an array ref is sent, the members of the array with will be concatenated with a ',' and that string returned. If a string is sent, that string will be returned.
$assign - Info to parse into a clause. Can be a hash ref, array ref, or string.
EXAMPLES
Hash:
assign_clause({boy => "'testing'", {girl => "'trying'"})
returns: "boy='testing',girl='trying'"
Array:
assign_clause(["Age=floor(12.34)","Hair='black'"])
returns: "Age=floor(12.34),Hair='black'"
String:
assign_clause("reason=.5")
returns: "reason=.5"
add_as_clause
add_as_clause($as,$add_as);Adds more as clause info onto an existing as clause, or creates a new as clause from what's to be added.
$as - Existing as clause to add to. Must be a string.
$add_as - As clause to add to. Can be a hash ref, array ref or string. See as_clause for more info.
add_equals_clause
add_equals_clause($equals,$add_equals);Adds more equals clause info onto an existing equals clause, or creates a new equals clause from what's to be added.
$equals - Existing equals clause to add to. Must be a string.
$add_equals - Equals clause to add to. Can be a hash ref, array ref or string. See equals_clause for more info.
add_comma_clause
add_comma_clause($comma,$add_comma);Adds more comma clause info onto an existing comma clause, or creates a new comma clause from what's to be added.
$comma - Existing comma clause to add to. Must be a string.
$add_comma - Equals comma to add to. Can be a hash ref, array ref or string. See comma_clause for more info.
set_as_clause
set_as_clause($as,$set_as);Overwrites as clause info over an existing as clause, only if the over writing info is not empty.
$as - Existing as clause to add to. Must be a string.
$set_as - As clause to set. Can be a hash ref, array ref or string. See as_clause for more info.
set_equals_clause
set_equals_clause($equals,$set_equals);Overwrites equals clause info over an existing equals clause, only if the over writing info is not empty.
$equals - Existing equals clause to add to. Must be a string.
$set_equals - Equals clause to set. Can be a hash ref, array ref or string. See equals_clause for more info.
set_comma_clause
set_comma_clause($comma,$set_comma);Overwrites comma clause info over an existing comma clause, only if the over writing info is not empty.
$comma - Existing comma clause to add to. Must be a string.
$set_comma - Comma clause to set. Can be a hash ref, array ref or string. See comma_clause for more info.
to_array
to_array($value);Takes a comma delimitted string or array ref and returns an array ref. If a comma delimitted string is sent, it splits the string by the commas.
$value - Value to convert or just send back. Can be an array ref or comma delimitted string.
to_hash
to_hash($value);Takes a comma delimitted string, array ref or hash ref and returns a hash ref. The hash ref returned will have keys based on the string, array ref, or hash ref, with the keyed values being 1. If a comma delimitted string is sent, it splits the string by the commas into an array, and that array is used to add keys to a hash, with the values being 1 and the hash ref returned. If an array is sent, that array is used to add keys to a hash, with the values being 1 and the hash ref returned. If a hash ref is sent, its just returned.
$value - Value to convert or just send back. Can be a hash ref, array ref or comma delimitted string.
add_array
add_array($value,$adder);Takes two array refs and places one onto the end of the other. Does not take strings!
$value - Array ref to be added to.
$adder - Array ref to add.
add_hash
add_hash($value,$adder);Takes two hash ref and adds the key value pairs from one to the other.
$value - Hash ref to be added to.
$adder - Hash ref to add.
configure_settings
configure_settings($database,
$username,
$password,
$host,
$port)
configure_settings(-database => $database,
-username => $username,
-password => $password,
-host => $host,
-port => $port) Creates a default database settings module. Takes in the defaults, prompts the user for info. If the user sends info, that's used. Once the settings a determine, it creates a 'Settings.pm' file in the current direfctory.
$database - Default database name to use for test.pl or demo.pl
$username and $password - Default MySQL account to use to connect to the database.
$host and $port - Default MySQL host and access port to use to connect to the database.
TODO LIST
Create add_assign_clause and set_assign_clause.
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.