NAME
SimpleDB::Class::SQL - SQL generation tools for SimpleDB.
VERSION
version 0.0400
DESCRIPTION
This class is used to generate the SQL needed for the Select operation on SimpleDB's web service.
METHODS
The following methods are available from this class.
new ( params )
Constructor.
params
A hash of options you can pass in to the constructor.
item_class
A SimpleDB::Class::Item subclass name. This is required.
output
Defaults to '*'. Alternatively you can pass a string of 'count(*)' or an attribute. Or you can pass an array ref of attributes.
where
A hash reference containing a series of clauses. Here are some examples and what the resulting queries would be. You can of course combine all these options to create your own queries.
Direct comparison.
{ foo => 1 }
select * from domain where foo=1
{ foo => 1, bar => 2 }
select * from domain where foo=1 and bar=2
{ foo => [ '>', 5 ] } # '=', '!=', '>', '<', '<=', '>='
select * from domain where foo > 5Direct comparison with an or clause.
{ -or => { foo => 1, bar => 2 } }
select * from domain where (foo=1 or bar=2)Find all items where these attributes intersect.
{ -intersection => { foo => 1, bar => 2 } }
select * from domain where (foo=1 intersection bar=2)Combining OR and AND.
{ -or => { foo => 1, -and => { this => 'that', bar => 2 } }
select * from domain where (foo=1 or ( this='that' and bar=2 ))Finding within a range.
{ foo=>['between', 5, 10] }
select * from domain where foo between 5 and 10Finding within a set.
{ foo => ['in', 1, 3, 5, 7 ] }
select * from domain where foo in (1, 3, 5, 7)Finding in a set where every item returned matches all members of the set.
{ foo => ['every', 1, 3, 5, 7 ] }
select * from domain where every(foo) in (1, 3, 5, 7)String comparisons. You can match on either side of the string ('%this', 'that%') or both ('%this%'). Note that matching at the beginning or both sides of the string is a slow operation.
{ foo => [ 'like', 'this%' ] } # 'not like'
select * from domain where foo like 'this%'Null comparisons. These are very slow. Try inserting 'Null' or 'None' into a field and do string comparisons rather than null comparisons.
{ foo => 'is null' } # 'is not null'
select * from domain where foo is nullorder_by
An attribute to order the result set by, defaults to ascending order. Can also pass in an array ref containing an attribute and 'desc' or 'asc'. If an array ref is passed in containing only an attribute name it is an implied descending order.
limit
An integer of a number of items to limit the result set to.
output ()
Returns what was passed into the constructor for the output field.
item_class ()
Returns what was passed into the constructor for the item_class field.
where ()
Returns what was passed into the constructor for the where field.
has_where()
Returns a boolean indicating whether a where clause has been set.
order_by ()
Returns what was passed into the constructor for the output field.
has_order_by ()
Returns a boolean indicating whether an order by clause has been set.
limit ()
Returns what was passed into the constructor for the output field.
has_limit ()
quote_value ( string )
Escapes ' and " in values.
string
The value to escape.
quote_attribute ( string )
Escapes an attribute with so that it can contain spaces and other special characters by wrapping it in backticks `.
string
The attribute name to escape.
parse_datetime ( string )
Parses a date time string and returns a DateTime object.
string
A string in the format of YY-MM-DD HH:MM:SS NNNNNNN +ZZZZ where NNNNNNN represents nanoseconds and +ZZZZ represents an ISO timezone.
parse_hashref ( string )
Parses a JSON formatted string and returns an actual hash reference.
string
A string that is composed of a JSONified hash reference.
parse_int ( string )
Parses an integer formatted string and returns an actual integer.
Warning: SimpleDB::Class only supports 15 digit positive integers and 9 digit negative integers.
string
A string that is composed of an integer + 1000000000 and then padded to have preceding zeros so that it's always 15 characters long.
parse_value ( name, value )
Returns a value that has been passed through one of the parse_* methods in this class.
name
The name of the attribute to parse.
value
The current stringified value to parse.
format_datetime ( value )
Returns a string formatted datetime object. Example: 2009-12-01 10:43:01 04939911 +0600. See parse_datetime, as this is the reverse of that.
value
A DateTime object.
format_hashref ( value )
Returns a json formatted hashref. Example: {"foo":"bar"}. See parse_hashref as this is the reverse of that.
Warning: The total length of your hash reference after it's turned into JSON cannot exceed 1024 characters, as that's the field size limit for SimpleDB. Failing to heed this warning will result in corrupt data.
value
A hash reference.
format_int ( value )
Returns a string formatted integer. Example: 000000003495839. See parse_integer as this is the reverse of that.
Warning: SimpleDB::Class only supports 15 digit positive integers and 9 digit negative integers.
value
An integer.
format_value ( name, value, [ skip_quotes ] )
Formats an attribute as a string using one of the format_* methods in this class. See parse_value, as this is the reverse of that.
name
The name of the attribute to format.
value
The value to format.
skip_quotes
A boolean indicating whether or not to skip calling the quote_value function on the whole thing.
recurese_where ( constraints, [ op ] )
Traverses a where() hierarchy and returns a stringified SQL version of the where clause.
constraints
A portion of a where hierarchy, perhaps broken off from the main for detailed analysis.
op
If it's a chunk broken off, -and, -or, -intersection then the operator will be passed through here.
to_sql ( )
Returns the entire query as a stringified SQL version.
LEGAL
SimpleDB::Class is Copyright 2009 Plain Black Corporation (http://www.plainblack.com/) and is licensed under the same terms as Perl itself.