DB::Object::Query - Query Object
my $q = DB::Object::Query->new;
v0.5.0
This is the base class for this DB::Object query formatter.
Sets or gets an hash of column name to alias.
Returns the formatted query as a string.
Takes a list or array reference of column to avoid in the next query. This returns a Module::Generic::Array object.
Takes a list or array reference of values to bind in the next query in "execute" in DB::Object::Statement. This returns a Module::Generic::Array object.
This returns the values to bind for the group clause of the query. This returns a Module::Generic::Array object.
group
This returns the values to bind for the limit clause of the query. This returns a Module::Generic::Array object.
limit
This returns the values to bind for the order clause of the query. This returns a Module::Generic::Array object.
order
Takes a list or array reference of value types to bind in the next query in "execute" in DB::Object::Statement. This returns a Module::Generic::Array object.
This does nothing and must be implemented by the driver package. So, see "binded_types_as_param" in DB::Object::Mysql::Query, "binded_types_as_param" in DB::Object::Postgres::Query, and "binded_types_as_param" in DB::Object::SQLite::Query
This returns the values to bind for the where clause of the query. This returns a Module::Generic::Array object.
where
If any argument is provided, this expects an hash reference of constants to value pairs.
It returns the currently set constants as a hash reference.
Returns the current database object, which should be driver specific like DB::Object::Postgres
Takes some optional arguments used to define the where clause, and this will prepare a DELETE query.
DELETE
It will refuse to prepare the query if no where clause has been defined, as this would be a very unsafe query. You would need to execute such query yourself using "do" in DB::Object.
If this method is called in void, this will execute the query.
It returns the newly created statement handler as a DB::Object::Statement
Enable or disable enhancement mode.
Enables to know the query reached the end, so that when constant is used, all the processing can be skipped.
The table used, if any, in a FROM clause.
FROM
Provided with an hash or hash reference of parameters and this will format the sql statement for queries of types select, delete and insert
select
delete
insert
In list context, it returns 2 strings: one comma-separated list of fields and one comma-separated list of values. In scalar context, it only returns a comma-separated string of fields.
Accepted parameters are:
If not provided, this will use the default column order for this table.
The table name to use, or, if not specified, this will be set to a value set with "qualified_name" in DB::Object::Tables
If any values were set by "from_unixtime" or "unix_timestamp", the associated columns will be formatted accordingly in the sql statement.
It will go through each of the parameter passed to the original insert, delete, or select query and if a column is one set earlier by "from_unixtime" or "unix_timestamp", it will format it.
If a parameter provided is a DB::Object::Statement it will stringify the query and add it surrounded by parenthesis.
If a parameter is actually a scalar reference, this means to us to use the underlying string as is.
If a parameter is ?, this will be treated as a placeholder.
?
If a parameter is a blob, it will be transformed into a parameter as a placeholder with its value saved to be bound in "execute" in DB::Object::Statement
If "bind" in DB::Object is not enabled, then the value provided with this parameter will be added after being possibly surrounded by quotes using "quote" in DB::Object::Tables.
If the column type for this parameter is ENUM and the query is of type INSERT or UPDATE, then the parameter value is surrounded with single quote.
ENUM
INSERT
UPDATE
If "bind" in DB::Object is enabled, then a placeholder ? will be added and the parameter value will be saed to be passed during "execute" in DB::Object::Statement
If nothing else matches it will add the value, possible quoted, using "quote" in DB::Object::Tables, whose implementation can be driver specific.
If column prefix is required, then the necessary prefix will be prepended to columns.
In list context, this returns the formatted columns and their values, and in scalar context it will returns only the formatted columns.
Provided with a list of parameters either as a key-value pairs, as an hash reference or even as an array reference and this will format update query based on the following arguments provided:
An array of key-value pairs to be used in the update query. This array can be provided as the prime argument as a reference to an array, an array, or as the data element of a hash or a reference to a hash provided.
Why an array if eventually we build a list of key-value pair? Because the order of the fields may be important, and if the key-value pair list is provided, format_update honors the order in which the fields are provided.
If no data is provided, this will return an error.
"format_update" will then iterate through each field-value pair, and perform some work:
If the field being reviewed was provided to "from_unixtime", then "format_update" will enclose it in the function suitable for the driver to convert it into a database datetime. For example, for Mysql, this would be:
FROM_UNIXTIME(field_name)
If the the given value is a reference to a scalar, it will be used as-is, ie. it will not be enclosed in quotes or anything. This is useful if you want to control which function to use around that field.
If the value is ? it will be used as a placeholder and the value will be saved to be bound later in "execute" in DB::Object::Statement. Its associated type will be added as blank, so it can be guessed later. However, if the column data type is bytea, the the PostgreSQL data type DBD::Pg::PG_BYTEA will be used when binding the value.
bytea
DBD::Pg::PG_BYTEA
If the column type is jsonb, and the value is an hash reference, it will be json encoded and used instead.
jsonb
If the given value is another field or looks like a function having parenthesis, or if the value is a question mark, the value will be used as-is.
If "bind" in DB::Object is off, the value will be escaped and the pair field='value' created.
If the field is a SET data type and the value is a number, the value will be used as-is without surrounding single quote.
If "bind" in DB::Object is enabled, a question mark will be used as the value and the original value will be saved as value to bind upon executing the query.
Finally, otherwise the value is escaped and surrounded by single quotes.
"format_update" returns a regular string representing the comma-separated list of columns with their value assigment that will be used.
Takes a list or array reference of columns that needs to be treated as unix timestamp and will be converted into a database timestamp.
It returns that list as a Module::Generic::Array
This is the driver specific implementation to convert unix timestamp to the database timestamp.
This is superseded in driver specific implementation, so see "format_from_epoch" in DB::Object::Mysql::Query, "format_from_epoch" in DB::Object::Postgres::Query and "format_from_epoch" in DB::Object::SQLite:::Query
This is the driver specific implementation to convert the database timestamp to unix timestamp.
Provided with an hash or hash reference of parameters and this will do some preparation work.
Possible parameters are:
An array reference of data which should be a key-value pairs.
An hash reference of column to alias pairs. Alternatively, if this is not provided, the value set with "alias" will be used.
An array reference of column to avoid using. Alternatively, if this is not provided, the value set with "avoid" will be used.
An array reference of columns to be converted from unix timestamp to the database timestamp.
The type of query, such as delete, insert, replace, select, update
replace
update
The table name.
A unix timestamp. Alternatively, unixtime can be used.
An array reference of columns to be converted into unix timestamp.
Does some preparation work such as :
the date/time field to use the FROM_UNIXTIME and UNIX_TIMESTAMP functions
removing from the query the fields to avoid, ie the ones set with the avoid method.
set the fields alias based on the information provided with the alias method.
if a field last_name and first_name exist, it will also create an alias name based on the concatenation of the 2.
it will set the default values provided. This is used for UPDATE queries.
It sets the following properties of the current object:
A boolean value whether the use of placeholder is enabled.
The type of query, such as select, insert, etc...
The arguments provided as an array reference.
The default value as an hash reference.
Extra parameters as an array reference.
The columns as an hash reference.
An hash reference
The table structure which is an hash reference of column name to definition pairs.
It returns a new DB::Object::Tables object with all the data prepared within.
Format the group by portion of the query by calling "_group_order"
group by
It returns a new DB::Object::Query::Clause object.
This must be superseded by driver specific implementation of this class. Check out "having" in DB::Object::Mysql::Query, "having" in DB::Object::Postgres::Query, "having" in DB::Object::SQLite::Query
$tbl->insert( col1 => $val1, col2 => $val 2 ); # or $other_tbl->where( user => 'joe' ); my $sth = $other_tbl->select; $tbl->insert( $sth ); # will become INSERT INTO some_table SELECT col1, col2 FROM other_table WHERE user = 'joe'
Provided with an array reference or an hash reference or a statement object (DB::Object::Statement) or a list of parameters and this will prepares an insert query using the field-value pairs provided.
If a DB::Object::Statement object is provided as first argument, it will be considered as a SELECT query to be used in the INSERT query, as in: INSERT INTO my table SELECT FROM another_table
Otherwise, "insert" will build the query based on the fields provided.
In void context, it will execute the query by calling of "execute" in DB::Object::Statement.
It returns the statement object.
Sets or gets the boolean value if the query is an upsert, which means a insert or update query that uses an ON CONFLICT clause. See "on_conflict" in DB::Object::Postgres::Query
upsert
ON CONFLICT
Sets or gets the join fields. This is a regular string.
Sets or gets the table joined. This returns a Module::Generic::Array
Sets or gets an hash reference of column joint column pairs
Set or get the limit for the future statement, by calling "_process_limit"
It returns a DB::Object::Query::Clause representing the limit clause.
Provided with a variable name and value pairs and this will set them.
It returns the formated declaration as a string.
This returns a new DB::Object::Query::Clause object.
Provided with a list of parameter and this will format the order clause by calling "_group_order"
Sets or gets the options that will be used in "_cache_this" in DB::Object, which is taked with preparing statement when they are not already cached.
This method basically handles an hash reference of properties set by DB::Object::Query and their inheriting packages. Currently only PostgreSQL makes use of this with "dollar_placeholder" in DB::Object::Postgres::Query and "server_prepare" in DB::Object::Postgres::Query
Sets or gets the query string. It returns whatever is set as a regular string.
Reset the query object to its nominal value so it can be re-used.
Returns an Module::Generic::Array object of core object properties shared with inheriting package.
Those are used to know what properties to reset.
Returns an Module::Generic::Array object of object properties shared with inheriting package.
This contains driver specific properties and together with the ones provided with "query_reset_core_keys", they form a whole.
Sets or gets the query type, such as delete, insert, select, update, etc.
Sets or gets the query values.
This is unsupported by default and its implementation is driver specific.
Reset the query object.
What it does is remove the following object properties: alias local binded binded_group binded_limit binded_order binded_types binded_values binded_where where limit group_by order_by reverse from_unixtime unix_timestamp sorted
Reset the bind values by setting the following object property to an empty anonymous array: binded binded_group binded_limit binded_order binded_types binded_where
Mark the query to use reverse order. This is used by "order".
Provided with a list or an array reference of columns, and this will format the select statement.
If the parameters provided is actually a scalar reference, it will be used as is.
If the parameters provided is an array or array reference, it will be joined using comma to get the list of columns to get, or, if the array is empty, the special * will be used to get all the columns.
*
Otherwise, it will use the data parameter provided as is.
If any alias have been set using "alias", they will be added to the list of columns to get.
if a table alias was set using "table_alias" it will be set here.
If the method was called in void context, it will execute immediately the statement object prepared.
It returns the statement object (DB::Object::Statement).
Sets or gets the string representing the list of columns used in previous select statement.
Set the query to use normal sorting order.
Sets or gets the list of sorted columns used in statements. This returns a Module::Generic::Array object.
Sets an optional alias for this table to be used in statement.
Sets or gets the table object. This will return a DB::Object::Tables object
If provided a hash or a hash ref, it sets the list of fields and their corresponding perl variable to bind their values to.
In list context, it returns the list of those field-variable pair, or a reference to it in scalar context.
Provided a list or an array reference of columns, and this sets the columns to be treated for seamless conversion from and to unix time.
It returns a Module::Generic::Array object.
Provided with a list, an array reference, or an hash or hash reference of key-value pairs and this will format the update statement.
If no parameter is provided, this will return an error.
This will call "format_update" to format the parameter provided and use the resulting string in the update statement.
If any clauses have been defined such as where, limit, etc, they will be properly formatted and added to the statement.
The resulting formatted query will be saved as the object property "query".
The formatted update columns and values will be saved in the current object property "query_values"
If "update" is called in void context, this will execute the query immediately.
Build the where clause based on the field-value hash provided by calling "_where_having".
It returns a clause object (DB::Object::Query::Clause).
This support method is called by "group" and "order" to format those clauses.
Provided with an object, or a list or an array reference of parameters and this will format the relevant clause.
it will go through each parameter and if the parameter provided is an DB::Object::Fields::Field object, it will collect its various attribute.
If the parameter is a scalar reference, it will be used as is.
If the parameter looks like it contains some field, it will be prepended by an appropriate prefix of table and possible database and schema name, if necessary.
Otherwise, it will use whatever value was provided as a column and use it.
It returns a DB::Object::Query::Clause object.
This is called by "where" and "having"
Provided with some data as a list or an array reference and this will format the where or having clause.
having
Walking through each parameter provided, if a parameter is a scalar reference, it will be used as is.
If "bind" in DB::Object is enabled, it will save the value used and use a placeholder.
This instantiate a new DB::Object::Query::Clause object passing it whatever parameters were provided.
Sets or gets the limit clause object and returns a DB::Object::Query::Clause object.
Provided with some parameters and this will format the limit clause of the query.
If one parameter was provided, then this will only define the ending limit. The start will be set as "undef" in perlfunc
If two parameters are provided, then this will set the start offset and the limit.
It check each of the start offset and end limit thus set, and if it is a scalar reference, it will be used as is. However, if the parameter is a ? it will be used as a placeholder.
Otherwise, if "bind" in DB::Object is enabled, this will save the parameter value as a binded value to be passed to "execute" in DN::Object::Statement
It returns the limit clause object (DB::Object::Query::Clause)
This returns an array reference of formatted clause, in their proper order to be added to query.
This method is called by "delete", "insert", "replace", "select" and "update"
This method is overriden by driver packages, so check "_query_components" in DB::Object::Mysql::Query, "_query_components" in DB::Object::Postgres::Query and "_query_components" in DB::Object::SQLite::Query
Based on the latest formatted query and the object property "query", this will return the type of query this is. This can be possibly one of the following: alter, create, drop, grant, listen, notify, insert, update, delete, select, truncate
alter
create
drop
grant
listen
notify
truncate
Provided with a query type and this will collect binded values from various clauses.
It returns the current object.
If "use_bind" in DB::Object is enabled, and this will modify the query passed to replace value with placeholders.
Actually this method is not used anymore and really qui dangerous because parsing sql is quite challenging.
This is used to format where and having clause.
Provided with a query type and clause property name such as having or where and other parameters and this will format the where or having clause and return a new DB::Object::Query::Clause object.
It checks each parameter passed.
if the first parameter is a DB::Object::Operator object, it will take it embedded values by calling "value" in DB::Object::Query::Clause. However, if there are any unknown fields, they will be ignored.
If the parameter is a scalar reference, it will use it as is.
If the parameter is a DB::Object::Operator object like DB::Object::AND, DB::Object::OR or DB::Object::NOT, it will recursively process its embedded elements.
If the parameter is a DB::Object::Query::Clause object, it will be added to the stack of elements.
If the parameter is a DB::Object::Fields::Field::Overloaded object, it will be added as a new DB::Object::Query::Clause to the stack.
It then checks parameters two by two, the first one being the column and the second being its value.
If the value is the operator object DB::Object::NOT, it adds to the stack a new clause object of type column IS NOT something, such as column IS NOT NULL
column IS NOT something
column IS NOT NULL
if the value is undefined or that the value is equal to NULL, then it adds to the stack a new clause object DB::Object::Query::Clause of type column IS NULL
NULL
column IS NULL
If the value is a scalar reference, it will be added as is in a new clause object that is added to the stack.
If the value is a DB::Object::Statement, "fetchrow" in DB::Object::Statement will be called and the value fetched will be added as a new clause object to the stack.
If the value is a perl Regexp object, then it will be formatted in a way suitable to the driver and added to a new clause object to the stack.
See Postgres documentation for more information
See MySQL documentation for more information
See SQLite documentation for more information
If the column is of type bytea, then a new clause object will be added to the stack with the column value being quoted properly using "quote" in DB::Object
All the clause objects in the stack will be merged into one new clause object using "merge" in DB::Object::Query::Clause
The resulting final clause object (DB::Object::Query::Clause) is returned.
When the AUTOLOAD is called, it will check if the value of the method used corresponds to an existing database table, and if it does, it returns the value returned by calling "table" with the table name.
AUTOLOAD
DBI
Jacques Deguest <jack@deguest.jp>
Copyright (c) 2018-2021 DEGUEST Pte. Ltd.
You can use, copy, modify and redistribute this package and associated files under the same terms as Perl itself.
To install DB::Object, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DB::Object
CPAN shell
perl -MCPAN -e shell install DB::Object
For more information on module installation, please visit the detailed CPAN module installation guide.