/ 
DBIx-QuickORM-0.000022
⭐ Starred 0 / DBIx::QuickORM::Manual::Aliasing

NAME

DBIx::QuickORM::Manual::Aliasing - Using ORM names that differ from database names.

DESCRIPTION

Sometimes the name you want to use in your code is not the name the database uses. The database might use a cryptic, abbreviated, reserved, or legacy name, or the same logical thing might be named differently across database engines. DBIx::QuickORM lets a database, a table, and a column each carry two names:

ORM name

The canonical, user-facing name. It is what you write everywhere you use the ORM: the schema DSL, query handles, where-clauses, order_by, the data you insert and update, and the keys on fetched rows. It is also the in-memory key for row data.

database name (db_name)

The name the database uses. It is what appears in the generated SQL and in the actual database. If you never set it, it defaults to the ORM name, so the two are identical and there is nothing to think about.

You set the database name with the db_name DSL function inside the relevant block. The ORM uses the ORM name everywhere you touch it and translates to the database name only at the SQL boundary; results coming back from the database are translated back to ORM names automatically.

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

WHAT CAN BE ALIASED

DATABASES

A database can be known by one name in the ORM and another on the server. This is handy when the same application uses differently-named databases on different servers or engines.

db theapp => sub {       # name used in the ORM
    db_name 'myapp';     # actual database name on the server
    ...
};

db('theapp') resolves the ORM database; the connection is made against myapp.

TABLES

A table can use one name in the schema and another in the database.

table people => sub {    # name used in the ORM
    db_name 'populace';  # actual table name in the database
    ...
};

$con->handle('people') queries the populace table; every SELECT, INSERT, UPDATE, and DELETE names it populace in the SQL.

A view behaves exactly like a table here - the same db_name rule applies.

COLUMNS

A column can use one name in the ORM and another in the database. Set db_name inside the column block:

table people => sub {
    primary_key 'people_id';

    column people_id => sub { db_name 'id'   };
    column fullname  => sub { db_name 'name' };
    column data      => sub { db_name 'json'; type 'JSON' };
};

Now your code uses the ORM names throughout:

my $h   = $con->handle('people');
my $row = $h->insert({fullname => 'Bob', data => {x => 1}});

my $bob = $h->one({fullname => 'Bob'});
print $bob->field('fullname');
print $bob->people_id;

while the generated SQL uses the database names (id, name, json). The primary key, where-clauses, order_by, the returned column list, and the keys of data_only result hashes are all in ORM names.

Two columns in the same table may not map to the same database name; that is rejected when the schema is built.

HOW IT BEHAVES

Everything you write uses ORM names.

Schema definitions, handle methods, where-clauses, order_by, insert/update data, primary keys, and the keys on fetched rows (including data_only) are all ORM names.

Generated SQL uses database names.

The SQL builder translates ORM names to database names for every statement it builds, and translates fetched rows back to ORM names. You never see database names on a row.

Literal SQL you write is never rewritten.

If you pass a raw SQL string (for example a literal where-clause), the ORM does not parse or rewrite it - write database names in raw SQL yourself. This is the one place the database name leaks into your code, and only because you asked for raw SQL.

The database stays canonical.

When a schema is autofilled from a live database, introspected columns are matched to your aliased columns by their database name, so the database's metadata (types, nullability, identity, keys) fills in while your ORM names and overrides win.

ALIASING AND JOINS

Joins translate aliased names too. Refer to columns by their ORM names, optionally qualified with the component alias:

my $sel = $con->handle('people')
    ->join('orders')
    ->order_by('a.fullname');

my $rows = $sel->where({'a.fullname' => 'Bob'})->all;

The generated SQL uses the database column names under each join alias, and the fetched rows fracture back into per-table rows keyed by ORM names. A bare (unqualified) column resolves to the first joined table that has it; qualify it with the alias ('a.fullname') to choose a specific table when more than one could match.

SEE ALSO

DBIx::QuickORM::Manual

The documentation hub.

DBIx::QuickORM::Manual::Schema

Composing schemas, tables, and columns with the DSL.

DBIx::QuickORM

The DSL reference, including db_name.

DBIx::QuickORM::Manual::SQLBuilder

How the SQL builder performs the ORM-to-database name translation, for anyone writing a custom builder.

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

Copyright Chad Granum <exodist7@gmail.com>.

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/