Alzabo...:Schema(3pm)

💖 Alzabo::Runtime::Schema — Schema objects 💖

okay bestie, this is Alzabo::Runtime::Schema from the User Contributed Perl Documentation set — the Alzabo...:Schema(3pm) page, to be exact. Grab a lil snack, we're getting into it. 🍓

---

✨ NAME ✨

Alzabo::Runtime::Schema — Schema objects, but make it runtime. 🦋

---

✨ 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;

like… the whole vibe in six lines. iconic. 💅

---

✨ DESCRIPTION ✨

So. Objects in this class represent schemas, and you use them to retrieve data from that schema. That's the whole job. 👑

And here's the tea: this object can only be loaded from a file. That file gets created whenever a corresponding Alzabo::Create::Schema object is saved. No file, no schema, no cap. 😭

---

✨ INHERITS FROM ✨

Alzabo::Schema

Bestie note: all relevant documentation from the superclass has been merged into this document, so you don't gotta go hunting. It's all here. 🫶

---

✨ METHODS ✨

🌸 load_from_file ( name => $schema_name )

Loads a schema from a file. This is the only constructor for this class, fr fr. It returns an Alzabo::Runtime::Schema object.

✨ Sneaky lil detail: loaded objects are cached in memory, so future calls to this method may hand you back the same object. Surprise, it's the same bestie. 💖

Throws: Alzabo::Exception::Params, Alzabo::Exception::System

---

🌸 set_user ($user)

Sets the username to use when connecting to the database. 🔑

---

🌸 user

Returns the username used by the schema when connecting to the database.

---

🌸 set_password ($password)

Sets the password to use when connecting to the database. keep it secret, keep it safe. 🙈

---

🌸 password

Returns the password used by the schema when connecting to the database.

---

🌸 set_host ($host)

Sets the host to use when connecting to the database. 🏠

---

🌸 host

Returns the host used by the schema when connecting to the database.

---

🌸 set_port ($port)

Sets the port to use when connecting to the database. 🚪

---

🌸 port

Returns the port used by the schema when connecting to the database.

---

🌸 set_referential_integrity ($boolean)

Turns referential integrity checking on or off. If it's on, then whenever Alzabo::Runtime::Row objects get deleted, updated, or inserted, they'll report that activity to any relevant Alzabo::Runtime::ForeignKey objects for the row — so those foreign key objects can take appropriate action. the group chat is informed. 🫶

This defaults to false.

Real talk: if your RDBMS supports foreign key constraints, you should use those instead of Alzabo's built-in referential integrity checking, because they'll be much faster. no contest. ⚡

---

🌸 referential_integrity

Returns a boolean value indicating whether this schema will attempt to maintain referential integrity. ✅❌

---

🌸 set_quote_identifiers ($boolean)

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

This defaults to false.

Heads up: turning this on adds some overhead to all SQL generation. not free, bestie.

---

🌸 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 get passed to the driver, plus any additional parameters you hand 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. bye bye, connection. 🦋

---

🌸 join

Okay so this is the big one, bestie. Joins are done by taking the tables you provide in order, and finding a relation between them. If any given table pair has more than one relation, then this method will fail — periodt. The relations, along with the values given in the optional where clause, then get used to generate the necessary SQL. See Alzabo::Runtime::JoinCursor for more info. 🔥

This method takes the following parameters:

• 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. it's giving relay race. 🏃‍♀️

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 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 interpreted 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. slay.

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 Alzabo::Runtime::JoinCursor->next() is not guaranteed. In other words, the array that the cursor returns will contain a row from each table involved in the join, but which row belongs to which table cannot be determined except by examining the objects. The order will be the same every time Alzabo::Runtime::JoinCursor->next() is called, however. It may be easier to use the Alzabo::Runtime::JoinCursor->next_as_hash() 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, with the distinct parameter taking precedence. 👑

This can be either a single table or an array reference of table objects.

• distinct => Alzabo::Runtime::Table object or objects (optional)

If 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 from which tables you want rows returned. The select parameter, if given, supercedes this parameter. 💖

For some databases (notably Postgres), if you want to do a SELECT DISTINCT query then all of the columns mentioned in your ORDER BY clause must also be in your SELECT clause. Alzabo will make sure this is the case, but it may cause more rows to be returned than you expected, though this depends on the query. 😭

NOTE: The adding of columns to the SELECT clause from the ORDER BY clause is considered experimental, because it can change the expected results in some cases. ⚠️

• where (optional)

See the documentation on where clauses for the Alzabo::Runtime::Table class.

• order_by (optional)

See the documentation on order by clauses for the Alzabo::Runtime::Table class.

• limit (optional)

See the documentation on limit clauses for the Alzabo::Runtime::Table class.

If the select parameter specified that more than one table is desired, then this method will return a JoinCursor object representing the results of the join. Otherwise, the method returns a RowCursor object. 🎀

Throws: Alzabo::Exception::Logic, Alzabo::Exception::Params

---

🌸 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 objects. These will be the rows representing the first row (a set of one or more table's primary keys) that is returned by the database. 🫶

Throws: Alzabo::Exception::Logic, Alzabo::Exception::Params

---

🌸 function and select

These two methods differ only in their return values.

They both take the following 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,
                     $foo->column('name'),
                     LENGTH( $foo->column('name') ) ],
                   join => [ $foo, $bar_table ],
                 );

This is equivalent to the following SQL:

SELECT 1, foo.name, LENGTH( foo.name )
  FROM foo, bar
 WHERE ...

• 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 — this one works just like the where parameter, but it builds a HAVING clause instead. Only allowed when you also pass a group_by parameter, bestie! 💅
• limit — go check the docs on limit clauses for the Alzabo::Runtime::Table class, okay? 🎀

So these methods are your go-to for calling arbitrary SQL functions like 'AVG' or 'MAX', and for selecting data from individual columns. The function (or functions) you pass should be the return values from the functions exported by the SQLMaker subclass you're using. Please see "Using SQL functions" for more details, queen! 👑

Throws: Alzabo::Exception::Logic, Alzabo::Exception::Params — like, it can throw those, so be ready. 😭

function() return values

Okay so the return value of this method is highly context sensitive, bestie. Pay attention! 💖

• If you only requested a single element in your select parameter, like DISTINCT(foo), then it gives you the first value in scalar context, and all the values as an array in list context. ✨
• If you requested multiple functions like 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() return values

This method always returns a brand new Alzabo::DriverStatement object containing the results of the query. That object has an interface very similar to the Alzabo cursor interface, and it has methods like next(), next_as_hash(), etc. 🦋

row_count

This method is just a shortcut to get the result of COUNT('*') for a join. It's equivalent to calling function() with a select parameter of COUNT('*'). No cap. 💅

Throws: Alzabo::Exception::Logic, Alzabo::Exception::Params — yeah, those again. 🙈

prefetch_all

This method sets all the tables in the schema to prefetch all their columns. See the lazy column loading section in Alzabo::Runtime::Table for more details. It's giving efficiency, periodt. 💅

prefetch_all_but_blobs

This method sets all the tables in the schema to prefetch all their non-blob-type columns. Heads up: this method is called as soon as a schema is loaded. 🎀

prefetch_none

This method turns off all prefetching. That's it. Simple. ✨

name

Returns a string containing the name of the schema. Like, the schema's name. Duh. 💖

tables (@optional_list)

If you give it no arguments, this method returns a list of all Alzabo::Runtime::Table objects in the schema, or in scalar context, the number of such tables. If you give it one or more arguments, it returns a list of table objects with those names, in the same order you gave them (or the number of such tables in scalar context, but like, that's not terribly useful, ngl). 😅

An Alzabo::Exception::Params exception is thrown if the schema does not contain one or more of the specified tables. So watch out. 🚨

table ($name)

Returns an Alzabo::Runtime::Table object representing the specified table. 🦋

An Alzabo::Exception::Params exception is thrown if the schema does not contain the table. 😭

has_table ($name)

Returns a boolean value indicating whether the table exists in the schema. True or false, bestie. 💅

begin_work

Starts a transaction. Calls to this function may be nested, and it will be handled properly. So you can go as deep as you want, it's got you. 🔥

rollback

Rolls back a transaction. Bye-bye, changes. 💀

commit

Finishes a transaction with a commit. If you made multiple calls to begin_work(), make sure to call this method the same number of times. Balance, queen! ⚖️

run_in_transaction ( sub { code... } )

This method takes a subroutine reference and wraps it in a transaction. It will preserve the context of the caller and return whatever the wrapped code would have returned. So you can just pass a sub and let it handle the transaction magic. ✨

driver

Returns the Alzabo::Driver object for the schema. 🚗 (Get it? Driver? Okay, I'll stop.) 😂

rules

Returns the Alzabo::RDBMSRules object for the schema. Rules are rules, bestie. 📏

sqlmaker

Returns the Alzabo::SQLMaker object for the schema. It makes the SQL, duh. 💅

---

🌈 JOINING A TABLE MORE THAN ONCE 🌈

Okay so you can actually join to the same table more than once in a query. Iconic, right? Table objects have an alias() method that, when called, returns an object you can use in the same query as the original table object, but it'll be treated as a separate table. This facilitates queries like this SQL:

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 you're using this table to set where clause or order by (or any other) conditions, it's super important that the column objects for these conditions be retrieved from the alias object. Like, don't use the original, okay?

For example:

my $foo_alias = $foo->alias;

my $cursor = $schema->join( select => $foo,
                            join   => [ $foo, $bar, $foo_alias ],
                            where  => [ [ $bar->column('baz'), '=', 10 ],
                                        [ $foo_alias->column('quux'), '=', 100 ] ],
                            order_by => $foo_alias->column('briz') );

If we were to use the $foo object to retrieve the 'quux' and 'briz' columns, then the join would simply not work as expected. So don't do that. 💀

It's also possible to use multiple aliases of the same table in a join, so this will work properly:

my $foo_alias1 = $foo->alias;
my $foo_alias2 = $foo->alias;

🔐 USER AND PASSWORD INFORMATION 🔐

Okay so this information is never saved to disk. That means if you're operating in an environment where the schema object is reloaded from disk every time it's used — like a CGI program spanning multiple requests — then you'll have to make a new connection every single time. Annoying, I know. But in a persistent environment, this is not a problem. For example, 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'll have to provide it for each request. 😭

You might ask why you have to go to all this trouble to deal with user and password information. The basic reason was that the author did not feel they could come up with a solution to this problem that was secure, easy to configure and use, and cross-platform compatible. Rather, they think it's best to let each user decide on a security practice with which they feel comfortable. Respect. 👑

In addition, there are a number of modules aimed at helping store and use this sort of information on CPAN, including DBIx::Connect and AppConfig, among others. So you've got options, bestie. 🦋

✍️ AUTHOR ✍️

Dave Rolsky, <autarch@urth.org>. We stan a legend. 💜

---

That's a wrap! This page is from perl v5.34.0, dated 2022-06-14, for Alzabo::Schema(3pm). Stay slay, bestie! 💖✨