SQLStruct - Create logical SQL statements from a structure of LISTS and HASHES.
use SQLStruct;
my $sstruct=SQLStruct->new();
my @s=( 'OR',
{ 'Creator' => 'Bård',
'Created' => { '>' => '1990', '<' => '2000' }
},
{ 'Creator' => 'Jan Frode*',
},
{ 'Religion' => { '!' => "NULL", '-' => "NULL" }
},
'1',
[ 'not',
{ 'Group' => 'Whatever',
},
'11'
],
);
my $sql=$sstruct->convert(\@s);
print $sql;This module creates logical SQL statements from a structure containing LIST(s) and HASH(es). The created logical SQL-statements are meant to be used after the WHERE-clause in a SQL-query.
Required input is none. Optional parameters are:
The structure to create SQL from. It should be either an ARRAY or a HASH. The structure can be set after instantiation by calling the struct()-method.
Sets the DBI-instance used by the caller so that one can perform proper SQL quoting, both for identifiers and values. If no DBI-instance is specified it will use internal, unsafe and simplified quoting functions.
Toggles that all values are to be quoted no matter what type they might be or what flags have been set on them.
Sets the identifier table name in case the identifier resides in one table and the value in another (see ivname parameter). This should typically be in the format:
TABLE.FIELD
The conversion process will assume that the field names in the structure are to be checked against the value in TABLE.FIELD.
Sets the identifier value name in the the identifier resides in on table and the value in another (see iname parameter). This should typially be in the format:
TABLE.FIELD
The conversion process will assume that the values specified in the structure are to be checked against the value in TABLE.FIELD.
Sets the reference to the function that clean the identifier. This cleans the identifier specified in the structure, not the identifier specified in the parameter iname.
Sets the reference to the function that clean the value of the identifier. This cleans the value of the identifier in the structure.
Sets the prepended SQL before the paranthesis that contains the logical sub query statement. Can allow for checking of values that would normally come on multiple rows. Eg. prepar=>"id in" (see complimentary option example in prelog below).
Sets the prepended SQL before the logical sub query statement. Can allow for checking of values that would normally come on multiple rows. Eg. prelog=>"SELECT id FROM T1 LEFT JOIN T2 on T1.id=T2.id LEFT JOIN T3 on T2.key=T3.key WHERE".
Return instance upon success.
Sets or gets the structure used by the class.
The function takes one optional parameter when setting the structure.
When setting the structure the type must be a reference to a HASH or an ARRAY. When retrieving the structure returned will be a reference to a HASH or LIST.
The main function of the class that converts the structure into standard SQL.
Takes one optional parameter with the structure to be used for conversion instead of using the one set at instantiation or through the struct()-method.
The structure is either a LIST or HASH-reference. The structure must be in the form:
( "LOP",
{ FIELDx => { "COP" => "VALUE", "COP" => "VALUE", },
{ FIELDy => { "COP" => "VALUE" },
{ FIELDz => { "COP" => "VALUE" },
) Where the first element of every LIST/ARRAY is the LOP or logical operator for the rest of the elements in the LIST. Default operator is "OR" if an invalid one is specified. It always assumes that the first element is the logical operator and will skip this when handling the lists elements. If the logical operator in a LISt is a unary the elements in that list are comma-separated.
The COP is the comparison operator that are to be used. All hash key/value pairs in a specific hash are always AND'ed together.
Valid LOPs are: ALL, AND, ANY, BETWEEN, EXISTS, IN, LIKE, NOT, OR and SOME. Accepted prefix'es to unary LOPs are whatever you want to (usual SQL engines only accepts ones like NOT, +, - etc.). The convert function only looks at the last word after the last space to know which operator is requested. Please also note that not all these LOPs might be supported by the SQL-engine being used.
Valid COPs are: > (greater than), < (lesser than), <> (not equal to), = (equal to), >= (greater than or equal), <= (lesser than or equal), ! (is not), - (not), & (bitwise and), | (bitwise or) and ^ (bitwise xor).
If the value of a comparison contains a wildcard "*" it is converted to "%" and if the comparator is "=" or "<>", the comparator is converted to "LIKE" or "NOT LIKE" accordingly. If "*" is to be used as the value itself it must be escaped.
When setting option iname and/or ivname (see new()-method) the conversion process also allows wildcards in the field name, since both the field-name and the value resides in columns in tables.
One can force quoting of a value by either prefix'ing the value with a ' or by setting the forcequote option in the constructor to 1. If a value is neither prefixed to be quoted or forcequote is turned on, it will decide to quote or not based upon if the value is a purely decimal value or not? If it is a purely decimal value it will not be quoted.
It should also be noted that if force quoting is disabled, the convert()-method will handle comparators with INTEGER-values in a special manner. INTEGER values are specified as consisting of "-", "." and numbers 0-9. In this scenario, if the search value for a field consists of an INTEGER, it will follow a logic that ensures typecasting in such a manner that even VARCHAR data fields will be able to understand that we are dealing with INTEGER comparisons and not STRING or VARCHAR. It is especially important to handle comparisons with zero correctly as how this is interpreted depends on the SQL engine in question and how it typecasts the field and value. It has been attempted to keep this logic working on the following SQL-engines: MySQL, SQLite, Oracle and PostgreSQL. We have not done extensive testing with SQLStruct on these engines, but we have checked and tested type cast logic on: >= MySQL 5.6, >= SQLite 3.22.0, >= Oracle 11g R2, >= PostgreSQL 9.6 (thanks to SQL Fiddle - sqlfiddle.com). When checking against zero values, this forced typecasting will understand "0" as well as "0.0" being the same value.
Returns the type of quoting used. Two types exists: SIMPLE and DBI. The SIMPLE type is the built-in, simple quoting functions, while DBI uses the DBI instance's quoting routines (quote()- and quote_identifier()-methods).
The return value is a SCALAR.