/ 
DB-Object-v1.7.0
River stage zero No dependents
/ DB::Object::Mysql::Query

NAME

DB::Object::Mysql::Query - Query Object for MySQL

SYNOPSIS

use DB::Object::Mysql::Query;
my $this = DB::Object::Mysql::Query->new || die( DB::Object::Mysql::Query->error, "\n" );

VERSION

v0.4.0

DESCRIPTION

This is a MySQL specific query object.

METHODS

binded_having

Sets or gets the array object (Module::Generic::Array) for the binded value in HAVING clauses.

format_from_epoch

This takes the parameters bind and value and returns a formatted FROM_UNIXTIME expression.

format_to_epoch

This takes the parameters bind, value and quote and returns a formatted expression to returns the epoch value out of the given field: UNIX_TIMESTAMP

having

Calls "_where_having" in DB::Object::Query to build a having clause.

limit

Build a new DB::Object::Query::Clause clause object by calling "_process_limit" and return it.

on_conflict

Provided with some options, this will build an ON DUPLICATE KEY UPDATE clause (DB::Object::Query::Clause) for MySQL. This feature is available in MySQL since version 4.1.0, making it compatible with virtually all modern installations.

action

Valid values are nothing (or ignore) and update.

If set to nothing or ignore, the query will perform a no-op update (e.g., id = id) to effectively ignore the conflict. Note that MySQL does not natively support a "do nothing" option for ON DUPLICATE KEY UPDATE, so this is emulated by setting a primary key or a default field (id) to its current value.

INSERT INTO mytable (id, a, b) VALUES (1, 'foo', 'bar')
    ON DUPLICATE KEY UPDATE id = id;

If set to update, this will either use the provided fields or set a callback routine to format an update statement using "format_update" in DB::Object::Query based on the original INSERT fields.

INSERT INTO mytable (id, a, b) VALUES (1, 'foo', 'bar')
    ON DUPLICATE KEY UPDATE a = 'foo', b = 'bar';

If the original insert uses placeholders, the ON DUPLICATE KEY UPDATE will reuse those placeholders, and the DB::Object::Statement object will double the bind values to accommodate both the INSERT and UPDATE portions of the query. The callback is triggered by "insert" in DB::Object::Query, as on_conflict relies on the query columns being previously set.

fields

An array (or array object) or hash of fields to use with action set to update.

If an array is provided, each field is automatically mapped to VALUES(field), which references the proposed value from the INSERT:

$q->on_conflict({
    action => 'update',
    fields => [qw(a b)],
});

INSERT INTO mytable (id, a, b) VALUES (1, 'foo', 'bar')
    ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b);

If a hash is provided, the keys are the fields to update, and the values can be literal values or scalar references for raw expressions:

$q->on_conflict({
    action => 'update',
    fields => { a => 'new_val', b => \'b + 1' },
});

INSERT INTO mytable (id, a, b) VALUES (1, 'foo', 'bar')
    ON DUPLICATE KEY UPDATE a = 'new_val', b = b + 1;

If no fields are provided with action = 'update'>, the fields from the original INSERT will be used via a callback.

target

An optional target specification, such as a column name or constraint. However, MySQL’s ON DUPLICATE KEY UPDATE does not allow targeting a specific key constraint or column explicitly—it triggers on any duplicate key violation (primary key or unique index). The target is stored for reference and logged, but it does not alter the query behavior.

$q->on_conflict({
    target => 'id',
    action => 'update',
    fields => { a => 'new' },
});

# Logs: "Target 'id' specified, but MySQL will use any duplicate key constraint."
INSERT INTO mytable (id, a, b) VALUES (1, 'foo', 'bar')
    ON DUPLICATE KEY UPDATE a = 'new';

Value for target can also be a scalar reference (used as-is in logging) or an array (joined with commas), though these have no effect on the MySQL query syntax.

where

Unlike PostgreSQL and SQLite, MySQL’s ON DUPLICATE KEY UPDATE does not support a WHERE clause. If provided, it will be ignored and a debug message logged, but no error will be raised to maintain API consistency across drivers.

$q->on_conflict({
    action => 'update',
    fields => { a => 'new' },
    where  => 'a > 0', # Ignored in MySQL
});

INSERT INTO mytable (id, a, b) VALUES (1, 'foo', 'bar')
    ON DUPLICATE KEY UPDATE a = 'new';

See MySQL documentation for more information.

on_update

This is an alias for on_conflict

replace

This method can take either 1 parameter which would then be a DB::Object::Statement object, or it can also be an hash reference of options.

It can alternatively take an hash of options.

If a statement was provided, it will be stringified calling "as_string" in DB::Object::Statement and used as a select query in the replace statement.

When preparing the replace query, this will be mindful to avoid fields that are null by default and not provided among the options.

If called in void context, this will execute the prepared statement immediately.

It returns the prepared statement handler (DB::Object::Statement).

reset

If the object property query_reset is not already set, this will remove the following properties from the current query object, set "enhance" in DB::Object::Query to true and return the query object.

Properties removed are: alias local binded binded_values binded_where binded_limit binded_group binded_having binded_order where limit group_by order_by reverse from_unixtime unix_timestamp sorted

reset_bind

Reset all the following object properties to an anonymous array: binded binded_where binded_group binded_having binded_order binded_limit

_query_components

This is called by the various query methods like "select" in DB::Object::Query, "insert" in DB::Object::Query, "update" in DB::Object::Query, "delete" in DB::Object::Query

It will get the various query components (group, having, sort, order, limit) that have been set and add them formatted to an array that is returned.

This version of "_query_components" exists here to provide MySQL specific implementation. See also the generic one in "_query_components" in DB::Object::Query

SEE ALSO

perl

AUTHOR

Jacques Deguest <jack@deguest.jp>

COPYRIGHT & LICENSE

Copyright (c) 2019-2021 DEGUEST Pte. Ltd.

You can use, copy, modify and redistribute this package and associated files under the same terms as Perl itself.