view release on metacpan or  search on metacpan

lib/Alzabo/Runtime/Schema.pm  view on Meta::CPAN

If this is true, then all SQL constructed for this schema will have
quoted identifiers (like `Table`.`column` in MySQL).

This defaults to false.  Turning this on adds some overhead to all SQL
generation.

=head2 connect (%params)

Calls the L<C<Alzabo::Driver-E<gt>connect>|Alzabo::Driver/connect>
method for the driver owned by the schema.  The username, password,
host, and port set for the schema will be passed to the driver, as
will any additional parameters given to this method.  See the L<C<<
Alzabo::Driver->connect() method >>|Alzabo::Driver/connect> for more
details.

=head2 disconnect

Calls the L<C<< Alzabo::Driver->disconnect()
>>|Alzabo::Driver/disconnect> method for the driver owned by the
schema.

=head2 join

Joins are done by taking the tables provided in order, and finding a
relation between them.  If any given table pair has more than one
relation, then this method will fail.  The relations, along with the
values given in the optional where clause will then be used to
generate the necessary SQL.  See
L<C<Alzabo::Runtime::JoinCursor>|Alzabo::Runtime::JoinCursor> for more
information.

This method takes the following parameters:

=over 4

=item * join => <see below>

This parameter can either be a simple array reference of tables or an
array reference of array references.  In the latter case, each array
reference should contain two tables.  These array references can also
include an optional modifier specifying a type of join for the two
tables, like 'left_outer_join', an optional foreign key object which
will be used to join the two tables, and an optional where clause used
to restrict the join.

If a simple array reference is given, then the order of these tables
is significant when there are more than 2 tables.  Alzabo expects to
find relationships between tables 1 & 2, 2 & 3, 3 & 4, etc.

For example, given:

  join => [ $table_A, $table_B, $table_C ]

Alzabo would expect that table A has a relationship to table B, which
in turn has a relationship to table C.  If you simply provide a simple
array reference, you cannot include any outer joins, and every element
of the array reference must be a table object.

If you need to specify a more complicated set of relationships, this
can be done with a slightly more complicated data structure, which
looks like this:

  join => [ [ $table_A, $table_B ],
            [ $table_A, $table_C ],
            [ $table_C, $table_D ],
            [ $table_C, $table_E ] ]

This is fairly self explanatory.  Alzabo will expect to find a
relationship between each pair of tables.  This allows for the
construction of arbitrarily complex join clauses.

For even more complex needs, there are more options:

  join => [ [ left_outer_join => $table_A, $table_B ],
            [ $table_A, $table_C, $foreign_key ],
            [ right_outer_join => $table_C, $table_D, $foreign_key ] ]

In this example, we are specifying two types of outer joins, and in
two of the three cases, specifying which foreign key should be used to
join the two tables.

It should be noted that if you want to join two tables that have more
than one foreign key between them, you B<must> provide a foreign key
object when using them as part of your query.

The way an outer join is interpreted is that this:

  [ left_outer_join => $table_A, $table_B ]

is interepreted to mean

  SELECT ... FROM table_A LEFT OUTER JOIN table_B ON ...

Table order is relevant for right and left outer joins, obviously.

However, for regular (inner) joins, table order is not important.

It is also possible to apply restrictions to an outer join, for
example:

  join => [ [ left_outer_join => $table_A, $table_B,
              # outer join restriction
              [ [ $table_B->column('size') > 2 ],
                'and',
                [ $table_B->column('name'), '!=', 'Foo' ] ],
            ] ]

This corresponds to this SQL;

  SELECT ... FROM table_A
  LEFT OUTER JOIN table_B ON ...
              AND (table_B.size > 2 AND table_B.name != 'Foo')

These restrictions are only allowed when performing an outer join,
since there is no point in using them for regular inner joins.  An
inner join restriction has the same effect when included in the
"WHERE" clause.

If the more multiple array reference of specifying tables is used and
no "select" parameter is provided, then the order of the rows returned
from calling L<C<< Alzabo::Runtime::JoinCursor->next()