NAME
EasyDB::Query - EasyDB query routines
SYNOPSIS
This file is used by EasyDB.
DESCRIPTION
Provides SELECT query functionality for the EasyDB package.
CONSTRUCTOR
Takes as it's parameters a DBI database handle. Will return a SELECT query object.
METHODS
debug ( [level] )
Sets the debugging level of this object. The standard debug level would normally be 1, and that would tell you what the program is up to. If you want you can go as high as 5, which spurts out reams and reams of useless debugging information.
$easyDB->debug('1');would be sufficient.
table ( [table name] )
When called with a table name this function will set the table that the query will execute on. Without a table name it will return the current table it is looking at.
$easyDB->find->table('table1');If you have changed the table then the new table is returned.
criteria ( hash of criteria )
This function defines what data you want to find in the given table. The language is partly functional and partly SQL based. You start by expressing what you want to find:
Find all people with names that have an 'a' in, who are
between 21 and 28 but don't have blue eyes. I also want
to know what height they are.We then break this down a little more into some basic facts that we want to know:
Name must have an 'a' in it
Age must be greater then 21
Age must be less than 28
Eyes must not be blue
They can be any height, we just want to know what it is.We then convert this into some functional statements. You should use these symbols to convert your facts into functional facts:
< Less than
> Greater than
= Equal to
! Is NotSome of these can be combined as well:
<= Less than or equal to
>= Greater than or equal toWe now re-write out facts like this:
Name must have an 'a' in it
Age > 21
Age < 28
Eyes ! 'blue'
Any heightFor the final step we need to use the wildcard, '%'. The wildcard means 'anything', that is anything can replace the wildcard. If we had the words 'ball', 'bat', 'bag', and 'hag', we can say:
Word has 'll' at the end
Word is like '%ll'
(This would return 'ball')
Word has 'ba' at the start
Word is like 'ba%'
(This would return 'ball', 'bat' and 'bag')
Word has an 'a' in the middle
Word is like '%a%'
(This would return all the words)
Any word at all
Word is like '%'
(This would also return all the words)
So now our set of facts would look like:
Name is like '%a'
Age > 21
Age < 28
Eyes ! 'blue'
Height is like '%'Now we need to convert this into our criteria hash. This step is quite simple once you've got the functional section:
$easydb->find->criteria(
Name => '%a',
Age => ['> 21', '< 28'],
Eyes => '! blue',
Height => '%',
);Note that we can have more than one criteria for Age. Simply put the list into an array by enclosing it in square brackets.
IMPORTANT
If you want to get a particular bit of data then you must specify it with a % sign in the criteria. See the Height field above. We do not care what the height is but we want to get that data anyway so we say that it can be 'anything', or '%'. This makes sure that that bit of data comes back in the results.
sql ( )
This function will either return the current SQL string, if one has been generated, or nothing if there is no SQL statment stored.
my $string = $easyDB->find->sql();string now holds the SQL statement.
how_many ( )
Function to return the number of records fetched. Calls a utility function in EasyDB::Util to count the number of records that are affected by the query.
my $rows = $easyDB->find->how_many();For more information view the EasyDB::Util documentation.
as_array ( )
Will return the results of the query as a reference to an array. Each element of the array will contain a reference to a hash that contains one row of the query, with the keys of the hash being the individual search fields. To print out all the results from a query in rows:
# Get the result into an array
my $ref = $easydb->find->as_array();
my @array = @{ $ref };
# For each result row returned
for ( @array ) {
# Make it a proper hash
my %row = %{ $_ };
# Print out in rows
for $item ( sort keys %row ) {
print $row{$item} . "\t";
}
print "\n";
}
}You will only be able use this function if you have defined some search criteria using the criteria function and have selected a table using the table function.
as_hash ( )
This function is only available for find queries.
Will return the results of the query as a reference to a hash. Each key of the hash is a reference to an array storing the rows of the results from the query. This query is best used with the how_many function. To print out all the results from the query in nice rows:
# Get the result into a hash
my $ref = $easydb->find->as_hash();
my %hash = %{ $ref };
# Get the number of rows returned
my $count = $easydb->find->how_many();
# Counting from 0 to $count
for ( my $i = 0 ; $i < $count ; $i ++ ) {
# For each key in the hash print out
# the i'th item
foreach $item ( sort keys %hash ) {
print $hash{$item}[$i];
}
print "\n";
}You will only be able use this function if you have defined some search criteria using the criteria function and have selected a table using the table function.
INTERNAL FUNCTIONS
_do_select_query ( $self object )
This function will, when given a valid query object, extract and execute the SQL statement. This function is only called by the SELECT query object.
_debug ( error level, debug message[s] )
Internal function used to report error messages. When you specify the level of this message it is checked against the current debug level. If the debug level is equal or greater than the level of this message, it is displayed.
CAVEATS
Unsure as to how stable the SQL parsing engine is. I don't know its tolerance for bad syntax.
Does not currently support:
SELECT *type queries. Relies on there actually being some column names supplied or it will have problems generating the hashes.
KNOWN BUGS
I'll have to get back to you on that one
SEE ALSO
EasyDB EasyDB::Query::Insert EasyDB::Query::Update EasyDB::Query::Delete EasyDB::Query::Select
ABOUT
This is part of Gaby Vanhegan's third year project for the University Of Leeds.
AUTHOR
Gaby Vanhegan <gaby@vanhegan.com>