GSP
Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Support
Contact Us
Online Help
Handbooks
Domain Status
Man Pages

FAQ
Virtual Servers
Pricing
Billing
Technical

Network
Facilities
Connectivity
Topology Map

Miscellaneous
Server Agreement
Year 2038
Credits
 

USA Flag

 

 

Man Pages


Manual Reference Pages  -  RELATIONS (3)

.ds Aq ’

NAME

Relations - Functions to Use with Databases and Queries

CONTENTS

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.

<B>B>$order<B>B> - Array ref of argument names in their proper order. Names must be capitalized.

<B>B>@param<B>B> - Array of values to parse.

EXAMPLES

<B>Using:B>



  sub example {

    # Get the defaults sent.

    my ($first,
        $second,
        $third) = rearrange([FIRST,
                             SECOND,
                             THIRD],@_);
  }



<B>Calling Ordered:B>



  example(one,two,three);



<B>Calling Named:B>



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



<B>Calling Hashed:B>



  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.

<B>B>$minor<B>B> - String to use to concatenate between the $clause hash ref key and value.

<B>B>$major<B>B> - String to use as the key-value pair if $clause is a hash ref, or array members if $clause is an array ref.

<B>B>$reverse<B>B> - 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).

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

<B>B>$as<B>B> - Info to parse into a clause. Can be a hash ref, array ref, or string.

EXAMPLES

<B>Hash:B>

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

<B>Array:B>

as_clause([’phone_num’,’address’])

returns: phone_num,address

<B>String:B>

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.

<B>B>$equals<B>B> - Info to parse into a clause. Can be a hash ref, array ref, or string.

EXAMPLES

<B>Hash:B>

equals_clause({man => ’strong’,
{woman => ’confident’})

returns: man=’strong’ and woman=’confident’

<B>Array:B>

equals_clause([Age > 40,Hair=’grey’])

returns: Age > 40 and Hair=’grey’

<B>String:B>

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.

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

EXAMPLES

<B>Array:B>

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

returns: fee,fie,foe,fum

<B>String:B>

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.

<B>B>$assign<B>B> - Info to parse into a clause. Can be a hash ref, array ref, or string.

EXAMPLES

<B>Hash:B>

assign_clause({boy => ’testing’,
{girl => ’trying’})

returns: boy=’testing’,girl=’trying’

<B>Array:B>

assign_clause([Age=floor(12.34),Hair=’black’])

returns: Age=floor(12.34),Hair=’black’

<B>String:B>

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.

<B>B>$as<B>B> - Existing as clause to add to. Must be a string.

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

<B>B>$equals<B>B> - Existing equals clause to add to. Must be a string.

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

<B>B>$comma<B>B> - Existing comma clause to add to. Must be a string.

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

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

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

<B>B>$as<B>B> - Existing as clause to overwrite. Must be a string.

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

<B>B>$equals<B>B> - Existing equals clause to overwrite. Must be a string.

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

<B>B>$comma<B>B> - Existing comma clause to overwrite. Must be a string.

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

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

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

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

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

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

<B>B>$split<B>B> - 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!

<B>B>$value<B>B> - Array ref to be added to.

<B>B>$adder<B>B> - 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!

<B>B>$value<B>B> - Hash ref to be added to.

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

<B>B>$question<B>B> - The question to ask the user.

<B>B>$default<B>B> - The default answer to the question.

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

<B>B>$database<B>B> - Default database name to use.

<B>B>$username<B>B> and <B>B>$password<B>B> - Default MySQL account to use to connect to the database.

<B>B>$host<B>B> and <B>B>$port<B>B> - 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.
Search for    or go to Top of page |  Section 3 |  Main Index


perl v5.20.3 RELATIONS (3) 2001-11-24

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with manServer 1.07.