George A. Fitch III > Relations-0.95 > Relations

Download:
Relations-0.95.tar.gz

Dependencies

Annotate this POD

View/Report Bugs
Module Version: 0.95   Source  

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\tbug","\t");

  if ($avoid->{bug}) {

    print "Avoiding the bug...";

  }

  unless ($avoid->{code}) {

    print "Not avoiding the code...";

  }

ABSTRACT ^

This perl library contains functions for dealing with databases. It's mainly used as 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.com

DESCRIPTION ^

WHAT IT DOES

Relations has functions for creating SQL clauses (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, with the exception of the configure_settings() and get_input() functions which use an ordered, named, and hashed, argument calling style. This is because most routines have only a few arguments, and the code is easier to read with an ordered argument style. With the functions that have many arguments, the code is easier to understand given a named or hashed argument style.

If you use the ordered argument calling style, such as

  $answer = get_input($question,$default);

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

  $answer = get_input(-question => $question,
                      -default  => $default);

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. -question, -Question, and -QUESTION 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 assumes dashes for the subsequent ones.

If you use the hashed argument calling style, such as

  $answer = get_input({question => $question,
                       default  => $default});

or

  $answer = get_input({-question => $question,
                       -default  => $default});

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. -question, -Question, and -QUESTION are all acceptable. If a hash is the first argument, Relations assumes that is the only argument that matters, and ignores any other arguments after the {}'s.

LIST OF RELATIONS FUNCTIONS ^

An example of each of these functions is provided in 'test.pl'.

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 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 Ordered:

  example('one','two','three');

Calling Named:

  example(-first  => 'one',
          -second => 'two',
          -third  => 'three');

Calling Hashed:

  example({first  => 'one',
           second => 'two',
           third  => '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, an array ref, or string. If sent a hash ref, the key-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 key-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='found','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 key-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. Yes, you can send a hash but the order won't be guarranteed, so don't do that.

$comma - Info to parse into a clause. Can be an array ref, or string.

EXAMPLES

Array:

comma_clause(['fee','fie','foe','fum'])

returns: "fee,fie,foe,fum"

String:

comma_clause("age desc,date")

returns: "age desc,date"

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 key-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. 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. 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 - Comma clause to add. Can be a hash ref, array ref or string.

See comma_clause for more info.

add_assign_clause

  add_assign_clause($assign,$add_assign);

Adds more assign clause info onto an existing assign clause, or creates a new assign clause from what's to be added.

$assign - Existing assign clause to add to. Must be a string.

$add_assign - Assign clause to add. Can be a hash ref, array ref or string.

See assign_clause for more info.

set_as_clause

  set_as_clause($as,$set_as);

Writes as clause info over an existing as clause, only if the over writing clause is not empty.

$as - Existing as clause to overwrite. 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);

Writes equals clause info over an existing equals clause, only if the over writing clause is not empty.

$equals - Existing equals clause to overwrite. 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);

Writes comma clause info over an existing comma clause, only if the over writing clause is not empty.

$comma - Existing comma clause to overwrite. 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.

set_assign_clause

  set_assign_clause($assign,$set_assign);

Writes assign clause info over an existing assign clause, only if the over writing clause is not empty.

$assign - Existing assign clause to overwrite. Must be a string.

$set_assign - Assign clause to set. Can be a hash ref, array ref or string.

See assign_clause for more info.

to_array

  to_array($value);

  to_array($value,$split);

Takes a delimitted string or array ref and returns an array ref. If a delimitted string is sent, it splits the string by the $split. If $split is not sent, it splits by a comma.

$value - Value to convert or just copy. Can be an array ref or delimitted string.

$split - String to split $value by. If this is not sent a comma is assumed.

to_hash

  to_hash($value);

  to_hash($value,$split);

Takes a 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 delimitted string is sent, it splits the string by $split 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 copied and returned.

$value - Value to convert or just copy. Can be a hash ref, array ref or delimitted string.

$split - String to split $value by. If this is not sent a comma is assumed.

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. Does not take strings or arrays!

$value - Hash ref to be added to.

$adder - Hash ref to add.

get_input

  $answer = get_input($question,$default);

  $answer = get_input(-question => $question,
                      -default  => $default);

Asks the user a question, cleans what the typed in, and returns the value typed in if there is one, or the default value is the user just hit return.

$question - The question to ask the user.

$default - The default answer to the question.

$answer - If the user typed anything in, it'll be that, minus the newline. If the user didn't type anything in, it'll be the default value.

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.

$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.

CHANGES ^

Now to_array() and to_hash() make copies of sent arrays and hashes. This was done because the more complex modules, Relations-Display and Relations-Report were sending references to their own arrays and those arrays were getting modified. Rather than inject a ton of special code to get around this, I figured I'd change just two functions.

You can also specify a delimitter for to_array() and to_hash(). I did this mostly because I was bored. :)

TODO LIST ^

Think of more things to do. :)

OTHER RELATED WORK ^

Relations (Perl)

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.

Relations-Query (Perl)

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.

Relations-Abstract (Perl)

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.

Relations-Admin (PHP)

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.

Relations-Family (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 (Perl)

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.

Relations-Report (Perl)

A 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.

Relations-Structure (XML)

An XML standard for Relations configuration data. With future goals being implmentations of Relations in different languages (current targets are Perl, PHP and Java), there should be some way of sharing configuration data so that one can switch application languages seamlessly. That's the goal of Relations-Structure A standard so that Relations objects can export/import their configuration through XML.

syntax highlighting: