/ 
DBIO-MySQL-0.900004
/ DBIO::MySQL::SQLMaker

NAME

DBIO::MySQL::SQLMaker - MySQL-specific SQL generation for DBIO

VERSION

version 0.900004

SYNOPSIS

# Used automatically by DBIO::MySQL::Storage — no manual setup needed.

DESCRIPTION

MySQL-specific SQL generation layer for DBIO. Extends DBIO::SQLMaker with the following MySQL adaptations:

  • INSERT with no columns emits INSERT INTO t () VALUES () instead of the standard INSERT INTO t DEFAULT VALUES, which MySQL does not support.

  • Supports STRAIGHT_JOIN as a join type hint via the join_type result source attribute.

  • UPDATE and DELETE statements that reference the modification target table in a subquery are automatically wrapped in a double subquery to work around MySQL's restriction against self-referencing in DML.

  • Implements MySQL's SELECT ... FOR UPDATE / FOR SHARE locking syntax, including the OF tbl_name, NOWAIT, and SKIP LOCKED clauses introduced in MySQL 8.0.1.

  • Renders the atomic upsert (ADR 0036) as ON DUPLICATE KEY UPDATE, in either the VALUES(col) or the AS new ... new.col form depending on the server version. See "_insert_on_conflict".

  • NULLS FIRST / NULLS LAST (the -nulls key of an order_by entry) is emulated, neither MySQL nor MariaDB has the clause. The sort key is preceded by a sort on whether it is NULL: { -asc => 'col', -nulls => 'first' } renders `col` IS NULL DESC, `col` ASC, and -nulls => 'last' renders `col` IS NULL ASC, `col` ASC -- the rows come back in the same order as on PostgreSQL. Always the IS NULL form, whatever the direction. The column expression is rendered twice, so a bind inside it is bound twice, in order. See "_render_op_nulls".

METHODS

_render_op_nulls

Renders the nulls_first / nulls_last ops SQL::Abstract builds for -nulls as expr IS NULL DESC|ASC, expr [ASC|DESC]. The wrapped node is the sort entry expr ASC|DESC (or a bare expr); the leading IS NULL sort puts the NULLs first (DESC: true sorts before false) or last (ASC).

apply_limit

Uses MySQL's LIMIT [offset,] rows syntax instead of the standard LIMIT rows OFFSET offset.

insert

Overrides the standard INSERT to emit INSERT INTO t () VALUES () for rows with no column values, satisfying MySQL's syntax requirements, and to assemble the statement in grammar order:

INSERT INTO ... VALUES ( ... ) [ AS `new` ] [ ON DUPLICATE KEY UPDATE ... ] [ RETURNING ... ]

Both tails are appended here rather than by the base, because the atomic-upsert clause (ADR 0036, see "_insert_on_conflict") has to precede RETURNING and the base appends RETURNING last. $options->{returning} is restored afterwards -- core reads it a second time, after the statement ran, to decide whether to fetch the returned row.

Without either option the output is byte-for-byte what it was before.

_insert_row_alias

The name of the row alias this server understands in INSERT ... VALUES (...) AS alias, or undef when the older VALUES(col) function has to be used instead.

MySQL 8.0.20 deprecated VALUES(col) and introduced the row alias; MariaDB never grew that syntax at all. Which of the two applies is a fact about the connected server, so "sql_maker" in DBIO::MySQL::Storage determines it (from the server version, never from the DBD driver name -- this dist's ADR 0001) and stashes it on the maker instance. A maker built standalone has no server to ask and falls back to VALUES(col), which every MySQL and MariaDB understands.

_insert_on_conflict

my ($tail, @bind) = $sql_maker->_insert_on_conflict(\%on_conflict, \%data);

Renders the ON DUPLICATE KEY UPDATE tail for the normalized on_conflict structure core hands down the insert options transport (ADR 0036 section 2), mirroring how _insert_returning appends the RETURNING tail elsewhere.

Two MySQL facts shape the result:

  • There is no conflict target. ON DUPLICATE KEY UPDATE fires on any primary-key or unique-key collision, so $on_conflict->{target} is not rendered at all. Whether a target the caller demanded can be honoured is decided from the ResultSource's constraint metadata, one layer up, in "insert" in DBIO::MySQL::Storage.

  • There is no DO NOTHING. action => 'nothing' is emulated with a no-op assignment (`col` = `col`), which is the documented idiom. The affected-rows count tells the two outcomes apart (0 means the row was already there, 1 means it was inserted) only on a connection without CLIENT_FOUND_ROWS. DBD::mysql and DBD::MariaDB both set that flag by default (mysql_client_found_rows, mariadb_client_found_rows), and the server -- MySQL and MariaDB alike -- then reports 1 for the already-present row as well. Measured on MySQL 8.0.30 and MariaDB 11.8.8 through both DBDs (t/81-upsert-affected-rows-found-rows-live.t):

                                 default / flag on   flag off
DO NOTHING, new row                  1               1
DO NOTHING, row already there        1               0
upsert, new row                      1               1
upsert, row there, nothing changed   1               0
upsert, row there, changed           2               2

    So on a default connection neither DO NOTHING nor an upsert that changes nothing can be told from an insert by its count. See "insert" in DBIO::MySQL::Storage.

The attempted values (core's { -attempted => $col }) render as VALUES(`col`) or, on a server that has the row alias, as `new`.`col` with AS `new` appended to the VALUES clause. Caller-supplied literals (\'count + 1', \[ 'x + ?', $n ]) pass through verbatim; anything else becomes a placeholder plus bind.

Two private keys the storage may add to the structure are honoured here: identity_column (render `pk` = LAST_INSERT_ID(`pk`), the identity idiom of ADR 0036 section 4) and noop_column (the column the DO NOTHING emulation assigns to itself).

update

delete

Overrides the base update and delete methods. When the modification target table is referenced in a subquery within the same statement (a pattern MySQL rejects), the affected subquery is automatically re-wrapped in an additional SELECT * FROM (...) `_forced_double_subquery` layer to satisfy MySQL's parser.

_lock_dialect

my $dialect = $sql_maker->_lock_dialect;

The locking-read grammar of the connected server, as "sql_maker" in DBIO::MySQL::Storage stashed it on this maker instance: a hashref with share (the keyword for => 'share' renders as), of, nowait and skip_locked (whether this server has those parts of the clause at all -- the two modifiers separately, because MariaDB did not ship them together: NOWAIT in 10.3, SKIP LOCKED in 10.6), and server (a human label for the exception messages).

The storage is the only side that can ask the server, so it decides and this maker only renders -- the same split as "_insert_row_alias". A maker built standalone has no server to ask and assumes a current MySQL: FOR SHARE with OF and the modifiers available, which is what this class emitted unconditionally before.

_lock_select

Generates the locking clause appended to SELECT statements. Accepts either a plain string ('update', 'share') or a hashref for fine-grained control:

$rs->search({}, { for => { type => 'update', of => ['tbl'], modifier => 'nowait' } });

Valid type values: update, share. Valid modifier values: nowait, skip_locked. The of key takes a table name or arrayref of table names.

share renders as FOR SHARE or LOCK IN SHARE MODE, and of and modifier are honoured or refused, according to "_lock_dialect" -- i.e. according to the server, not to the DBD driver the connection came in through. A refusal throws locally, before the statement is assembled, naming the version boundary it hit; see "_lock_dialect" in DBIO::MySQL::Storage.

SEE ALSO

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.