Alzabo::Runtime::Schema - Schema objects
use Alzabo::Runtime::Schema qw(some_schema);
my $schema = Alzabo::Runtime::Schema->load_from_file( name => 'foo' ); $schema->set_user( $username ); $schema->set_password( $password );
This object can only be loaded from a file. The file is created whenever a corresponding Alzabo::Create::Schema object is saved.
Note: all relevant documentation from the superclass has been merged into this document.
Loads a schema from a file. This is the only constructor for this class.
The username used by the schema when connecting to the database.
Set the username to use when connecting to the database.
The password used by the schema when connecting to the database.
Set the password to use when connecting to the database.
The host used by the schema when connecting to the database.
The port used by the schema when connecting to the database.
Set the host to use when connecting to the database.
Set the port to use when connecting to the database.
A boolean value indicating whether this schema will attempt to maintain referential integrity.
Sets the value returned by the
referential_integrity method. If true,
Alzabo::Runtime::Row objects are
deleted, updated, or inserted, they will report this activity to any
objects for the row, so that the foreign key objects can take
Defaults to false.
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
Alzabo::Driver->connect method for
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
Alzabo::Runtime::JoinCursor for more
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 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 in that Alzabo should expect Alzabo should expect to find a relationship between each specified pair. 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 ] ]
It should be noted that if you want to join two tables that have more than one foreign key between them, you 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.
If the more complex method of specifying tables is used and no
select parameter is provided, then the order of the rows returned
next on the cursor is not guaranteed. In other words,
the array that the cursor returns will contain a row from each table
involved in the join, but the which row belongs to which table cannot
be determined except by examining each row in turn. The order will be
the same every time
next is called, however.
Alzabo::Runtime::Tableobject or objects (optional)
This can be either a single table or an array reference of table objects.
If you are expecting rows from multiple tables, then it is important that this parameter be set to the table that would have the least repeated rows. Otherwise, you will lose information. For example, if a join would return the following rows:
A B --- --- 1 1 1 1 1 2 2 2 2 3 2 3 3 4 3 4 3 5
If you set A as distinct, it is possible that you could entirely miss certain relevant rows from B.
select parameter (or
tables parameter) specified that
more than one table is desired, then this method will return an
representing the results of the join. Otherwise, the method returns
This method takes the exact same parameters as the
join method but instead of returning
a cursor, it returns a single array of row object. These will be the
rows representing the first ids that are returned by the database.
These two methods differ only in their return values.
$schema->function( select => [ $table->column('name'), LENGTH( $table->column('name') ) ] );
These methods is used to call arbitrary SQL functions such as 'AVG' or 'MAX'. The function (or functions) should be the return values from the functions exported by the SQLMaker subclass that you are using. Please see Using SQL functions for more details.
The return value of this method is highly context sensitive.
If you only requested a single function (
DISTINCT(foo) ), then it
returns the first value in scalar context and all the values in list
If you requested multiple functions ( AVG(foo),
MAX(foo) ) then it
returns a single array reference (the first row of values) in scalar
context and a list of array references in list context.
This method always returns a new
object containing the results of the query.
A string containing the name of the schema.
A list of
Alzabo::Runtime::Table object named in the list
given. If no list is provided, then it returns all table objects in
Alzabo::Runtime::Table object representing the specified
A true or false value depending on whether or not the table exists in the schema.
Starts a transaction. Calls to this function may be nested and it will be handled properly.
Rollback a transaction.
Finishes a transaction with a commit. If you make multiple calls to
start_transaction, make sure to call this method the same number of
This method takes a subroutine reference and wraps it in a transaction.
It will preserve the context of the caller and returns whatever the wrapped code would have returned.
Alzabo::Driver subclass object for the
Alzabo::RDBMSRules subclass object for
Alzabo::SQLMaker subclass object for the
It is possible to join to the same table more than once in a query.
Table objects support a method called
alias that when called, returns an
object that can be used in the same query as the original table
object, but which will be treated as a separate table. This is to
allow starting with something like this:
SELECT ... FROM Foo AS F1, Foo as F2, Bar AS B ...
The object returned from the table functions more or less exactly like a table object. When using this table to set where clause or order by (or any other) conditions, it is important that the column objects for these conditions be retrieved from the alias object.
my $foo_alias = $foo_tab->alias;
my $cursor = $schema->join( select => $foo_tab, join => [ $foo_tab, $bar_tab, $foo_alias ], where => [ [ $bar_tab->column('baz'), '=', 10 ], [ $foo_alias->column('quux'), '=', 100 ] ], order_by => $foo_alias->column('briz') );
If we were to use the
$foo_tab object to retrieve the 'quux' and
'briz' columns then the join would simply not work as expected.
It is also possible to use multiple aliases of the same table in a join, so that this:
my $foo_alias1 = $foo_tab->alas; my $foo_alias2 = $foo_tab->alas;
will work just fine.
This information is never saved to disk. This means that if you're
operating in an environment where the schema object is reloaded from
disk every time it is used, such as a CGI program spanning multiple
requests, then you will have to make a new connection every time. In
a persistent environment, this is not a problem. In a mod_perl
environment, you could load the schema and call the
methods in the server startup file. Then all the mod_perl children
will inherit the schema with the user and password already set.
Otherwise you will have to provide it for each request.
You may ask why you have to go to all this trouble to deal with the user and password information. The basic reason was that I did not feel I could come up with a solution to this problem that was secure, easy to configure and use, and cross-platform compatible. Rather, I think it is best to let each user decide on a security practice with which they feel comfortable. If anybody does come up with such a scheme, then code submissions are more than welcome.
If Alzabo is attempting to maintain referential integrity and you are
not using caching, then situations can arise where objects you are
holding onto in memory can get out of sync with the database and you
will not know this. If you are using one of the cache modules then
attempts to access data from an expired or deleted object will throw
an exception, allowing you to try again (if it is expired) or give up
(if it is deleted). Please see
Alzabo::ObjectCache for more details.
Dave Rolsky, <firstname.lastname@example.org>