NAME

DB::Object::Query - Query Object

SYNOPSIS

my $q = DB::Object::Query->new;

VERSION

v0.7.2

DESCRIPTION

This is the base class for this DB::Object query formatter.

METHODS

alias

Sets or gets an hash of column name to alias.

as_string

Returns the formatted query as a string.

avoid

Takes a list or array reference of column to avoid in the next query. This returns a Module::Generic::Array object.

binded

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.

binded_group

This returns the values to bind for the group clause of the query. This returns a Module::Generic::Array object.

binded_limit

This returns the values to bind for the limit clause of the query. This returns a Module::Generic::Array object.

binded_order

This returns the values to bind for the order clause of the query. This returns a Module::Generic::Array object.

binded_types

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.

binded_types_as_param

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

binded_values

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.

binded_where

This returns the values to bind for the where clause of the query. This returns a Module::Generic::Array object.

constant

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.

database_object

Returns the current database object, which should be driver specific like DB::Object::Postgres

delete

Takes some optional arguments used to define the where clause, and this will prepare a DELETE query.

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

elements

Sets or gets an DB::Object::Query::Elements object. This object serves to contain all the elements used in creating SQL queries, keeping track of their order, value, dta types, placeholders used, etc.

enhance

Enable or disable enhancement mode.

final

Enables to know the query reached the end, so that when constant is used, all the processing can be skipped.

from_table

The table used, if any, in a FROM clause.

format_statement

Provided with an hash or hash reference of parameters and this will format the sql statement for queries of types select, delete and 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:

data
order: If not provided, this will use the default column order for this table.
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.

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.

format_update

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:

data

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.

If the column type is jsonb, and the value is an hash reference, it will be json encoded and used instead.

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.

from_unixtime

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

format_from_epoch

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

format_to_epoch

This is the driver specific implementation to convert the database timestamp to unix timestamp.

getdefault

Provided with an hash or hash reference of parameters and this will do some preparation work.

Possible parameters are:

arg: An array reference of data which should be a key-value pairs.
as: An hash reference of column to alias pairs. Alternatively, if this is not provided, the value set with "alias" will be used.
avoid: An array reference of column to avoid using. Alternatively, if this is not provided, the value set with "avoid" will be used.
from_unixtime: An array reference of columns to be converted from unix timestamp to the database timestamp.
query_type: The type of query, such as delete, insert, replace, select, update
table: The table name.
time: A unix timestamp. Alternatively, unixtime can be used.
unix_timestamp: 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:

bind: A boolean value whether the use of placeholder is enabled.
query_type: The type of query, such as select, insert, etc...
_args: The arguments provided as an array reference.
_default: The default value as an hash reference.
_extra: Extra parameters as an array reference.
_fields: The columns as an hash reference.
_from_unix: An hash reference
_structure: The table structure which is an hash reference of column name to definition pairs.
_to_unix: An hash reference

It returns a new DB::Object::Tables object with all the data prepared within.

group

Format the group by portion of the query by calling "_group_order"

It returns a new DB::Object::Query::Clause object.

having

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

insert

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

is_upsert

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

join_fields

Sets or gets the join fields. This is a regular string.

join_tables

Sets or gets the table joined. This returns a Module::Generic::Array

left_join

Sets or gets an hash reference of column joint column pairs

limit

Set or get the limit for the future statement, by calling "_process_limit"

It returns a DB::Object::Query::Clause representing the limit clause.

local

Provided with a variable name and value pairs and this will set them.

It returns the formated declaration as a string.

new_clause

This returns a new DB::Object::Query::Clause object.

new_element

Instantiate a new DB::Object::Query::Element object, passing it whatever arguments were provided and sharing wit it the value of the "debug" flag.

new_elements

Instantiate a new DB::Object::Query::Elements object, passing it whatever arguments were provided and sharing wit it the value of the "debug" flag.

order

Provided with a list of parameter and this will format the order clause by calling "_group_order"

It returns a new DB::Object::Query::Clause object.

prepare_options

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

query

Sets or gets the query string. It returns whatever is set as a regular string.

query_reset

Reset the query object to its nominal value so it can be re-used.

query_reset_core_keys

Returns an Module::Generic::Array object of core object properties shared with inheriting package.

Those are used to know what properties to reset.

query_reset_keys

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.

query_type

Sets or gets the query type, such as delete, insert, select, update, etc.

query_values

Sets or gets the query values.

Returns a scalar object

replace

This is unsupported by default and its implementation is driver specific.

reset

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_bind

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

returning

This is unsupported by default and its implementation is driver specific.

reverse

Mark the query to use reverse order. This is used by "order".

select

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).

selected_fields

Sets or gets the string representing the list of columns used in previous select statement.

Returns a regular string.

sort

Set the query to use normal sorting order.

sorted

Sets or gets the list of sorted columns used in statements. This returns a Module::Generic::Array object.

table_alias

Sets an optional alias for this table to be used in statement.

This method should be called by "as" in DB::Object::Tables. If you change this directly, you risk facing some discrepancies between the actual table alias and the one set here.

Returns the current value.

table_object

Sets or gets the table object. This will return a DB::Object::Tables object

tie

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.

unix_timestamp

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.

update

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.

It returns the statement object (DB::Object::Statement).

where

Build the where clause based on the field-value hash provided by calling "_where_having".

It returns a clause object (DB::Object::Query::Clause).

_group_order

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.

_having

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.

Walking through each parameter provided, if a 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.

If "bind" in DB::Object is enabled, it will save the value used and use a placeholder.

It returns a DB::Object::Query::Clause object.

_initiate_clause_object

This instantiate a new DB::Object::Query::Clause object passing it whatever parameters were provided.

_limit

Sets or gets the limit clause object and returns a DB::Object::Query::Clause object.

_process_limit

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)

_query_components

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

_query_type

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

_save_bind

Provided with a query type and this will collect binded values from various clauses.

It returns the current object.

_value2bind

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.

_where_having

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::Expression object, it will be added to the stack of elements.

If the parameter is a DB::Object::Fields::Overloaded object, it will be added as a new DB::Object::Query::Clause to the stack.

If the parameter is a litteral represented as a string or a scalar reference, then it will be added to the list as-is. For example:

$tbl->where(
    $tbl->fo->status eq 'active',
    "LENGTH(?) > 12"
);

$tbl->where(
    $tbl->fo->status eq 'active',
    \"LENGTH(?) > 12"
);

However, make sure to put this expression at the end.

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

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

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 and onto the stack.

If the value looks like some table field embedded inside some SQL function, then it will be added to a new clause object and onto 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.

AUTOLOAD

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.

AUTHOR

Jacques Deguest <jack@deguest.jp>

COPYRIGHT & LICENSE

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.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)