NAME

DBIx::QuickORM::Manual::SQLBuilder - Custom SQL builders.

DESCRIPTION

A SQL builder is the component that turns a query handle's intent - a source, a where-clause, a field list, ordering, limit, and the data to write - into a SQL statement and its bind values. The ORM never hand-rolls statements inline; every SELECT, INSERT, UPDATE, DELETE, and UPSERT goes through a builder.

Builders consume DBIx::QuickORM::Role::SQLBuilder. The shipped implementation, DBIx::QuickORM::SQLBuilder::SQLAbstract, is built on SQL::Abstract. You can replace it - per handle or per connection - with any object that satisfies the role.

This document covers where a builder fits in the query pipeline, the contract the role defines, the statement-and-bind structure a builder returns, how the SQL::Abstract builder implements that contract, how to plug a custom builder in, and how to write one.

This is part of the DBIx::QuickORM::Manual.

WHERE A BUILDER FITS

A DBIx::QuickORM::Handle collects query state (source, where, limit, order_by, fields, dialect) and, when it is time to run, calls the appropriate qorm_* method on its builder. The builder returns a hashref; the handle prepares the statement, binds the values, and executes:

handle state ---> builder->qorm_select(...) ---> { statement, bind, source }
                                                      |
                                   handle prepares + binds + executes

The handle picks its builder lazily: an explicit per-handle builder wins, otherwise a builder carried by the where object (if it has a sql_builder method), otherwise the connection's default_sql_builder. See "sql_builder" in DBIx::QuickORM::Handle.

The builder's job ends at producing the statement and bind specs. Value deflation (running a column's type/affinity conversion before binding) and binary quoting happen in the handle when it binds, not in the builder. The builder only needs to label each bind with the field it belongs to; the handle does the rest.

THE BUILDER CONTRACT

DBIx::QuickORM::Role::SQLBuilder requires seven methods and provides one.

REQUIRED METHODS

$sql = $builder->qorm_select(%params)
$sql = $builder->qorm_insert(%params)
$sql = $builder->qorm_update(%params)
$sql = $builder->qorm_delete(%params)
$sql = $builder->qorm_where(%params): Build a statement of the named kind. Each returns the statement-and-bind structure described below.
$cond = $builder->qorm_and($a, $b)
$cond = $builder->qorm_or($a, $b): Combine two where-conditions into one. The handle calls these when you chain $h->and(...) / $h->or(...). The return value is whatever your builder accepts as a where param - it is fed straight back into the next build. It need not be SQL; for the SQL::Abstract builder it is a nested hashref.

PROVIDED METHODS

$where = $builder->qorm_where_for_row($row): Returns a where-clause that uniquely identifies a row. The role implements this as $row->primary_key_hashref, which is the right answer for any builder that accepts a plain column/value hashref as a where. Override it if your builder's where format differs.
$orm_row = $builder->qorm_row_to_orm($source, \%row): Returns a new hashref with a fetched row's keys remapped from database column names back to ORM names for the given source. Unknown keys pass through unchanged. The role implements this with $source->field_orm_name; the handle calls it on rows it fetches so the row layer is uniformly ORM-keyed. See "COLUMN NAME TRANSLATION".

OPTIONAL: qorm_upsert

The role does not require qorm_upsert, but the handle calls it when you upsert. A builder that omits it simply cannot be used for upserts. See "UPSERT" below for the contract.

COLUMN NAME TRANSLATION

A column can use one name in the ORM and a different name in the database (its db_name). Everything a caller touches uses the ORM name; everything in generated SQL must use the database name. Translation is the builder's responsibility, and the contract is:

Emit database names in all generated SQL. Before handing field names to your backend, translate them through the source: insert/update data keys, the field list, the returning list, where-clause column references, order-by columns, and (for upsert) the conflict and update-set columns. The source provides $source->field_db_name($name), which maps either an ORM name or a database name to the database name and is idempotent.
Return rows keyed by ORM name. Fetched rows come back keyed by database column name. The handle restores ORM names with qorm_row_to_orm (which uses $source->field_orm_name), so a builder that follows the standard fetch path gets this for free.
Never rewrite literal SQL. A scalar-ref where, a \['sql', @bind] literal, or any column name the source does not recognize is passed through untouched. Callers who write raw SQL use database names themselves.

When ORM and database names are identical (the common case) every translation is an identity, so a builder that ignores aliasing still works for non-aliased schemas - but it will emit wrong SQL the moment a column is aliased. The shipped SQL::Abstract builder implements the full contract; see "HOW THE SQL::ABSTRACT BUILDER WORKS".

Because field binds end up labelled with the database name (see "BIND SPECS"), and the source resolves type and affinity by either name, value deflation at bind time keeps working without any extra handling.

THE PARAMETERS A BUILDER RECEIVES

The handle passes a flat key/value list. Which keys are present depends on the operation; the relevant ones:

source: The source object (a table, view, join, or literal). Required for every build. It consumes DBIx::QuickORM::Role::Source; the builder uses $source->source_db_moniker to name it in SQL, and (for upsert) $source->primary_key. The source is also passed straight back out in the result so the handle can deflate binds against it.
where: The where-clause, in whatever format your builder accepts. Optional.
fields: The list of columns to fetch. Required for qorm_select.
order_by: Ordering, builder-specific format. Optional.
limit: A row limit. Optional. See "LIMIT".
insert / update: The row data to write, a column-to-value hashref. qorm_insert reads insert; qorm_update reads update.
returning: Columns to return from a writing statement (for dialects that support RETURNING). Optional.
dialect: The active DBIx::QuickORM::Dialect. A builder mostly ignores it, but upsert uses it for the dialect-specific conflict clause.

THE STATEMENT-AND-BIND STRUCTURE

Every qorm_* build method returns a hashref:

{
    statement => "SELECT ... WHERE ... LIMIT ?",
    bind      => [ \%spec, \%spec, ... ],
    source    => $source,
}

statement: The SQL string, with ? placeholders.
source: The same source object that came in. The handle needs it to look up each field's type and affinity when binding.
bind: An ordered arrayref of bind specs, one per placeholder. Order is not what positions a bind - the param key does - but keeping them in order is good manners.

BIND SPECS

Each bind spec is a hashref. There are two kinds, distinguished by type.

A field bind carries a value that belongs to a column:

{
    param => 1,           # 1-based placeholder position
    value => $value,      # the value to bind
    type  => 'field',
    field => 'name',      # the column this value is for
}

The handle treats field binds specially: it looks up the column's affinity and type on the source and deflates the value (e.g. encoding JSON, packing a UUID, formatting a DateTime) before binding, and applies binary quoting when the affinity is binary. This is why a builder must label each value with its field rather than deflating values itself.

The field here is the database column name (what the builder emitted into the statement); the source resolves type and affinity by either ORM or database name, so deflation works regardless of aliasing. See "COLUMN NAME TRANSLATION".

A limit bind carries a raw scalar bound as-is, with no field, no deflation:

{
    param => 5,
    value => $limit,
    type  => 'limit',
}

Any type other than field is bound verbatim. If your builder needs to emit a placeholder for something that must not go through column deflation (a computed value, a literal you have already prepared), give it a non-field type.

LIMIT

The SQL::Abstract builder does not delegate LIMIT to SQL::Abstract; it appends " LIMIT ?" to the finished statement and pushes a limit bind. A custom builder is free to handle limit however its backend prefers, as long as the placeholder count in the statement matches the bind specs.

UPSERT

When you upsert, the handle calls qorm_upsert with the same params as an insert, plus the dialect. The contract:

Build the underlying INSERT (the SQL::Abstract builder calls its own qorm_insert).
Split the data on the source's primary key: the key columns identify the conflict, the non-key columns become the update set.
Ask the dialect for the conflict clause with $dialect->upsert_statement($pk) - this yields ON CONFLICT(...) DO UPDATE SET on SQLite/Postgres, ON DUPLICATE KEY UPDATE on MySQL - then append "col = ?" assignments and their binds for each non-key column.
Preserve any trailing RETURNING clause: pull it off before appending the conflict clause and re-attach it at the end.

Croak if the source has no primary key - there is nothing to conflict on.

HOW THE SQL::ABSTRACT BUILDER WORKS

DBIx::QuickORM::SQLBuilder::SQLAbstract inherits from SQL::Abstract and consumes the role. Reading it is the fastest way to understand the contract in practice; the shape:

Construction

new forces SQL::Abstract's bindtype to 'columns', so SQL::Abstract hands back each bind as a [$field, $value] pair. That field name is exactly what the builder needs to label field bind specs.

Generated build methods

qorm_insert, qorm_update, qorm_select, qorm_delete, and qorm_where are generated at compile time from a common template. Each one:

Pulls source out of the params and resolves it to its db moniker via $source->source_db_moniker (a blessed source is checked for the Source role first; a plain string is passed through, which is handy in tests).
Translates ORM column names to database names (_translate_params): the insert/update data keys, field list, returning list, where-clause, and order-by are each rewritten through the source. The where-walker translates a hash key only when the source recognizes it as a field, so logic and comparison operators pass through untouched and the rule survives new SQL::Abstract operators; literal SQL is left alone. See "COLUMN NAME TRANSLATION".
Translates the ORM params into the positional argument list SQL::Abstract's matching method wants, via a per-operation _*_args helper (_select_args, _insert_args, etc.). These helpers enforce per-operation rules - e.g. insert and delete confess on limit/order_by, since SQL::Abstract has nowhere to put them.
Calls the inherited SQL::Abstract method to get ($statement, @bind).
Rewrites the @bind pairs into field bind specs with sequential param numbers.
Appends LIMIT ? and a limit bind if a limit param was given.
Returns { statement, bind, source }.

Value wrapping

_format_insert_and_update_data wraps each insert/update value in { -value => $v } so SQL::Abstract treats it as a literal bind rather than trying to interpret a hash- or array-ref value as an operator/sub-query.

qorm_and / qorm_or

Return { '-and' => [$a, $b] } / { '-or' => [$a, $b] } - the SQL::Abstract spellings for combining conditions.

qorm_upsert

Implements the "UPSERT" contract on top of qorm_insert and $dialect->upsert_statement.

USING A CUSTOM BUILDER

Per handle

sql_builder on a handle is a clone-setter: pass a builder and you get a new handle that uses it. The original is unchanged.

my $h2 = $h->sql_builder(My::SQLBuilder->new);
my @rows = $h2->all;

This is the narrowest scope - one chain of queries.

Per connection

A connection takes a default_sql_builder at construction; every handle made from that connection falls back to it. If none is given, the connection lazily builds a DBIx::QuickORM::SQLBuilder::SQLAbstract.

my $con = DBIx::QuickORM::Connection->new(
    orm                 => $orm,
    default_sql_builder => My::SQLBuilder->new,
);

WRITING A CUSTOM BUILDER

A custom builder is any class that consumes the role and implements the seven required methods. Optionally add qorm_upsert for upsert support, and override qorm_where_for_row if your where format is not a plain hashref.

The skeleton below ignores SQL::Abstract entirely and emits SQL directly, to show the bare contract. A where here is a { column => value } hashref.

package My::SQLBuilder;
use strict;
use warnings;

use Carp qw/croak/;

use Role::Tiny::With qw/with/;
with 'DBIx::QuickORM::Role::SQLBuilder';

# Build a "col = ? AND col = ?" fragment plus its field binds, starting
# placeholders at $next. Column names are translated to database names.
sub _where_sql {
    my ($self, $source, $where, $next) = @_;
    return ("", []) unless $where && keys %$where;

    my (@parts, @bind);
    for my $field (sort keys %$where) {
        my $db = $source->field_db_name($field);
        push @parts => "$db = ?";
        push @bind  => {
            param => $$next++,
            value => $where->{$field},
            type  => 'field',
            field => $db,
        };
    }

    return (" WHERE " . join(" AND " => @parts), \@bind);
}

sub qorm_select {
    my $self   = shift;
    my %params = @_;

    my $source = $params{source} or croak "No source provided";
    my $fields = $params{fields} or croak "'fields' is required";

    my $moniker = $source->source_db_moniker;
    my $cols    = join(", " => map { $source->field_db_name($_) } @$fields);

    my $param = 1;
    my ($where_sql, $bind) = $self->_where_sql($source, $params{where}, \$param);

    my $stmt = "SELECT $cols FROM $moniker$where_sql";

    if (my $limit = $params{limit}) {
        $stmt .= " LIMIT ?";
        push @$bind => {param => $param++, value => $limit, type => 'limit'};
    }

    return {statement => $stmt, bind => $bind, source => $source};
}

# qorm_insert / qorm_update / qorm_delete / qorm_where follow the same
# shape: name the source via source_db_moniker, translate column names to
# database names via $source->field_db_name, build the statement, emit one
# bind spec per placeholder (type => 'field' for column values so the handle
# deflates them), and return { statement, bind, source }.

sub qorm_and {
    my ($self, $a, $b) = @_;
    return {%$a, %$b};   # naive: merge two hashref wheres
}

sub qorm_or { croak "OR not supported by this builder" }

1;

Points the example makes concrete:

Resolve the source through source_db_moniker - never interpolate a source object directly.
Translate column names through $source->field_db_name so the SQL uses database names; a non-aliased schema makes this a no-op. See "COLUMN NAME TRANSLATION".
Number placeholders with param, starting at 1, and keep the count in sync between the statement and the bind list.
Tag column values type => 'field' with their field, so the handle deflates and (if binary) quotes them. Use any other type for values that must bind raw.
Always return the source in the result; the handle needs it at bind time.

For a production-grade implementation that handles ordering, complex where-clauses, and every dialect, model on DBIx::QuickORM::SQLBuilder::SQLAbstract rather than this skeleton.

SOURCE

The source code repository for DBIx::QuickORM can be found at https://github.com/exodist/DBIx-QuickORM.

MAINTAINERS

Chad Granum <exodist@cpan.org>

AUTHORS

Chad Granum <exodist@cpan.org>

COPYRIGHT

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

See https://dev.perl.org/licenses/

To install DBIx::QuickORM, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBIx::QuickORM

CPAN shell

perl -MCPAN -e shell
install DBIx::QuickORM

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)