Take me over?
NAME
Alzabo::Runtime::Schema - Schema objects
SYNOPSIS
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 );
$schema->connect;
DESCRIPTION
This object can only be loaded from a file. The file is created whenever a corresponding Alzabo::Create::Schema object is saved.
INHERITS FROM
Alzabo::Schema
METHODS
load_from_file
Loads a schema from a file. This is the only constructor for this class.
Parameters
name => $schema_name
Returns
An Alzabo::Runtime::Schema
object.
Throws
user
Returns
The username used by the schema when connecting to the database.
set_user ($user)
Set the username to use when connecting to the database.
password
Returns
The password used by the schema when connecting to the database.
set_password ($password)
Set the password to use when connecting to the database.
host
Returns
The host used by the schema when connecting to the database.
port
Returns
The port used by the schema when connecting to the database.
set_host ($host)
Set the host to use when connecting to the database.
set_port ($port)
Set the port to use when connecting to the database.
referential_integrity
Returns
A boolean value indicating whether this schema will attempt to maintain referential integrity.
set_referential_integrity ($boolean)
Sets the value returned by the referential_integrity
method. If true, then when Alzabo::Runtime::Row
objects are deleted, updated, or inserted, they will report this activity to any relevant Alzabo::Runtime::ForeignKey
objects for the row, so that the foreign key objects can take appropriate action.
Defaults to false.
set_quote_identifiers ($boolean)
If this is true, then all SQL constructed for this schema will have quoted identifiers (like `Table`.`column` in MySQL).
Defaults to false.
connect (%params)
Calls the 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 Alzabo::Driver->connect
method for more details.
disconnect
Calls the Alzabo::Driver->disconnect
method for the driver owned by the schema. See the Alzabo::Driver->disconnect
method for more details.
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 Alzabo::Runtime::JoinCursor
for more information.
Parameters
join => <see below>
This parameter can either be a simple array reference of tables or a reference to an array containing more arrays, each of which contain two tables, plus an optional modifier specifying a type of join for those two tables, like 'left_outer_join', and an optional foreign key object which will be used to join the two tables.
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.
It is also possible to apply restrictions to an outer join, for example:
join => [ [ left_outer_join => $table_A, $table_B, $foreign_key, [ [ $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')
Again, the foreign key object is only mandatory when there is more than one foreign key between the two tables being joined.
If the more complex method of specifying tables is used and no
select
parameter is provided, then the order of the rows returned from callingnext
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 timenext
is called, however. It may be easier to use thenext_as_hash
cursor method in this case.select =>
Alzabo::Runtime::Table
object or objects (optional)This parameter specifies from which tables you would like rows returned. If this parameter is not given, then the distinct or join parameter will be used instead, in that order.
This can be either a single table or an array reference of table objects.
distinct =>
Alzabo::Runtime::Table
object or objectsIf this parameter is given, it indicates that results from the join should never contain repeated rows.
This can be used in place of the select parameter to indicate which tables are being selected, though the select parameter always takes first precedence.
where
See the documentation on where clauses for the Alzabo::Runtime::Table class.
order_by
See the documentation on order by clauses for the Alzabo::Runtime::Table class.
limit
See the documentation on limit clauses for the Alzabo::Runtime::Table class.
Returns
If the select
parameter (or tables
parameter) specified that more than one table is desired, then this method will return an Alzabo::Runtime::JoinCursor
object representing the results of the join. Otherwise, the method returns an Alzabo::Runtime::RowCursor
object.
Throws
one_row
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.
function and select
These two methods differ only in their return values.
Parameters
select => $function or [ scalars, SQL functions and/or
Alzabo::Column
objects ]If you pass an array reference for this parameter, it may contain scalars, SQL functions, or column objects. For example:
$schema->function( select => [ 1, $table->column('name'), LENGTH( $table->column('name') ) ], join => [ $table, $other_table ], );
join
See the documentation on the join parameter for the join method.
where
See the documentation on where clauses for the Alzabo::Runtime::Table class.
order_by
See the documentation on order by clauses for the Alzabo::Runtime::Table class.
group_by
See the documentation on group by clauses for the Alzabo::Runtime::Table class.
having => same as "where"
This parameter is specified in the same way as the "where" parameter.
limit
See the documentation on limit clauses for the Alzabo::Runtime::Table class.
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.
Returns
function
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 context.
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.
select
This method always returns a new Alzabo::DriverStatement
object containing the results of the query.
row_count
This method is simply a shortcut to get the result of COUNT('*') for a join.
prefetch_all
This method will set all the tables in the schema to prefetch all their columns. See the lazy column loading section in Alzabo::Runtime::Table
for more details.
prefetch_all_but_blobs
This method will set all the tables in the schema to prefetch all their non-blob-type columns.
JOINING A TABLE MORE THAN ONCE
It is possible to join to the same table more than once in a query. Table objects contain 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.
For example:
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.
USER AND PASSWORD INFORMATION
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 set_user
and set_password
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.
CAVEATS
Refential Integrity
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.
AUTHOR
Dave Rolsky, <autarch@urth.org>