Alzabo...:Table(3pm)

💖 NAME 💖

Alzabo::Runtime::Table — table objects, bestie ✨

(It's from the Perl docs, the User Contributed Perl Documentation, section 3pm. So this is a runtime table object and it is lowkey carrying the whole show.)

---

✨ SYNOPSIS ✨

my $table = $schema->table('foo');

my $row = $table->row_by_pk( pk => 1 );

my $row_cursor =
    $table->rows_where
        ( where =>
          [ Alzabo::Column object, '=', 5 ] );

Like, that's the whole vibe in four lines. 🎀

---

🦋 DESCRIPTION 🦋

This object can make rows for you, two whole ways: by building objects off data that's already in the database, or by inserting brand new data and getting a shiny new row out of it. Both? Both. 💅

It also does this thing called lazy column evaluation, which is genuinely the icon of saving memory and saving your database from all that wear and tear. Go read the "LAZY COLUMN LOADING" section for the details, no cap. 🧚

---

👑 INHERITS FROM 👑

Alzabo::Table

Heads up bestie: every bit of relevant documentation from the superclass has been merged right into this document, so you're not missing anything. All the tea is here. 🫖

---

🔥 METHODS 🔥

🌈 Methods that return an Alzabo::Runtime::Row object

All of these methods take the no_cache parameter, and it gets passed straight on to Alzabo::Runtime::Row->new. Yes, every single one. 😌

💜 insert

Inserts the values you give it into the table. And if you don't hand over a value for a primary key column, and that column is "sequenced", then the primary key will be auto-generated for you. Slay. ✨

It takes the following parameters:

• values => $hashref — this hashref holds column names and values for the new row. It's optional, fr. If you don't specify any values, then the default values get used instead.

This method returns a new Alzabo::Runtime::Row object. 💖

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

💜 row_by_pk

The primary key here can be a simple scalar — that's the case when the table has a single primary key — or a hash reference mapping column names to primary key values, for when you've got multi-column primary keys. Pick your fighter. 🥊

It takes the following parameters:

• pk => $pk_val or \%pk_val

It returns a new Alzabo::Runtime::Row object. And if literally no rows in the database match the value(s) you gave, then you get an empty list or undef back (depending on whether you're in list or scalar context). 😭

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

💜 row_by_id

This one is for regenerating a row that you previously saved by reference to its id — the id that the Alzabo::Runtime::Row->id method hands back. It's way more convenient than saving a whole multi-column primary key when you're trying to maintain state in a web app, for example. Main character convenience. 👑

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

This method takes a single parameter, row_id, which is the string representation of a row's id, as returned by the Alzabo::Runtime::Row->id_as_string() method.

It returns a new Alzabo::Runtime::Row object. If no rows in the database match the value(s) given, then an empty list or undef will be returned (for list or scalar context). 🫶

🌸 Insert Handles

Okay so if you're about to insert a ton of rows at once, it's way more efficient to create an insert handle and then re-use that thing. It's basically the same energy as how DBI lets you create statement handles and execute them over and over. Efficiency queen. ⭐

💜 insert_handle

This method takes the following parameters:

• columns => $arrayref — this should be an array reference containing zero or more Alzabo::Runtime::Column objects. If it's empty, or you just don't provide it, then defaults get used for all the columns. No stress. 🎀
• values => $hashref — this is how you specify values that are gonna be the same for every single row. And these can be actual values or SQL functions.

The return value of this method is an Alzabo::Runtime::InsertHandle object. That object has exactly one method, insert(). Peep the Alzabo::Runtime::InsertHandle docs for the details. 💅

Throws: Alzabo::Exception::NotNullable, Alzabo::Exception::Params

🌸 Common Parameters

A bunch of methods in this class take the same parameters, so they're all documented down here instead of being copy-pasted everywhere. Organized. We love that. 💖

• where => <see below>

This parameter can take a whole variety of values, and the most basic where parameter is a single array reference that looks like this:

[ Alzabo::Column object or SQL function,
  $comparison,
  $value or Alzabo::Column object ]

That $comparison should be a string holding a SQL operator, like >, =, or IN.

The parameter can also be an array reference containing many of those little arrays:

[
  [ Alzabo::Column object or SQL function,
    $comparison,
    $value or Alzabo::Column object ],
  [ Alzabo::Column object or SQL function,
    $comparison,
    $value or Alzabo::Column object ],
  ...
]

If the comparison is BETWEEN, then it should be followed by two values. If it's IN or NOT IN, then it should be followed by a list of one or more values. Don't mix those up bestie, I'm begging. 🙈

By default, each clause represented by an array reference gets joined together with an 'AND'. But — plot twist — you can drop the string 'or' between two array references to make them join with an 'OR', like so:

[ [ $foo_col, '=', 5 ],
  'or',
  [ $foo_col, '>', 10 ] ]

which would generate SQL something like:

WHERE foo = 5 OR foo > 10

And if you want to be explicit about it, you can also use the string 'and'. 👑

If you need to group conditionals, you can use '(' and ')' strings in between array references that represent a conditional. For example:

[ [ $foo_col, '=', 5 ],
  '(',
    [ $foo_col, '>', 10 ]
    'or',
    [ $bar_col, '<', 50, ')' ],
  ')' ]

which would generate SQL something like:

WHERE foo = 5 AND ( foo > 10 OR bar < 50 )

Make sure your parentheses balance out, or an exception will be thrown. Balance them. It's giving self-care. 💅

You can also use the SQL functions (see Using SQL functions) that are exported from whichever SQLMaker subclass you're using. For example:

[ LENGTH($foo_col), '<', 10 ]

would generate something like:

WHERE LENGTH(foo) < 10

• order_by => see below

This parameter can take one of two different values. The simplest form is to just hand it a single column object or SQL function. Alternatively, you can give it an array reference to a list of column objects, SQL functions and strings, like this:

order_by => [ $col1, COUNT('*'), $col2, 'DESC', $col3, 'ASC' ]

Important bestie, do not skip this one: you cannot simply use any arbitrary SQL function as part of your order by clause. You need to use a function that is exactly the same as one that was given as part of the select parameter. Exactly. Character for character. No improvising. 🚨

• group_by => see below

This parameter can take either a single column object or an array of column objects.

• having => same as "where"

This parameter gets specified in the exact same way as the where parameter. Copy-paste energy, but legally. 😌

• limit => $limit or [ $limit, $offset ]

For databases that support LIMIT clauses, this incorporates such a clause into the SQL. For databases that don't, the limit gets implemented programmatically as rows are being requested. And if an offset is given, that's the number of rows skipped in the result set before the first one is returned. 🦋

---

🌈 Methods that return an Alzabo::Runtime::RowCursor object

Both rows_where() and all_rows() return an Alzabo::Runtime::RowCursor object that represents the results of the query. And yes, this is true even for queries that end up returning one row or zero rows — because Alzabo literally cannot know in advance how many rows those queries are gonna return. It's not psychic, it's just careful. ✨

💜 rows_where

This method is a simple way to get a row cursor based on one or more column values.

It takes the following parameters, all of which were already described up in the Common Parameters section:

• where
• order_by
• limit

It returns an Alzabo::Runtime::RowCursor object representing the query. 🎀

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

💜 all_rows

This method just returns all the rows in the table. Every single one. The whole squad. 👑

It takes the following parameters:

• order_by
• limit

It returns an Alzabo::Runtime::RowCursor object representing the query.

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

💜 one_row

This method takes the exact same parameters as the rows_where() method, but instead of handing you back a cursor, it hands you back a single row. And that row is the first row returned by the database. First come, first served. 🌸

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

💜 potential_row

This method is used to create a new Alzabo::Runtime::Row object, in the "potential" state. It's not committed yet, it's just... potential. Manifesting. ✨

It takes the following parameters.

• values => \%values — this should be a hash reference containing column names, exactly like what's given to insert(). It's okay to omit columns that are normally not nullable, but those columns cannot be explicitly set to null. Any values you give will be set in the new potential row object, and if a column has a default and you don't give a value for that column, then the default gets used. Unlike the insert() method, you cannot use SQL functions as values here. Sorry bestie. 😭

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

---

🌈 Other Methods

This method returns a count of the rows in the table. It takes the following parameters: (yes, the count description is sitting up here above the method name in the original page and honestly? iconic. anyway 💅)

💜 row_count

• where

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

💜 function and select

These two methods differ only in their return values. Otherwise they're twins. 👯

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:

$table->function( select =>
                  [ 1,
                    $foo->column('name'),
                    LENGTH( $foo->column('name') ) ] );

This is equivalent to the following SQL:

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

• where
• order_by
• group_by
• limit

This method is used to call arbitrary SQL functions like 'AVG' or 'MAX', or to select arbitrary column data. The function (or functions) should be the return values from the functions exported by the SQLMaker subclass that you're using. Go read Using SQL functions for more details. 🧚

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

🎀 function() return values

The return value of this method is highly context sensitive. Be aware.

If you only requested a single element in your select parameter, such as DISTINCT(foo), then it returns the first value in scalar context and all the values as an array in list context. Context is literally everything here, no cap. 💖

okay so if you asked for multiple functions all at once, like AVG(foo), MAX(foo)? Then it hands you back a single array reference — the first row of values — when you're in scalar context. And in list context it gives you a list of array references. slay. 💅

select() return values 🎀

This one always returns a brand-new Alzabo::DriverStatement object, and that object is holding all your query results. Its interface is very similar to the Alzabo cursor interface — it's giving cousin energy — and it has methods like next(), next_as_hash(), etc. iykyk. ✨

alias 🦋

This returns an object you can use in joins, so a particular table can show up in the join under multiple aliases. That means self-joins are possible, and also way more complex joins where you alias the same table several times. iconic.

The object you get back is more or less identical to a table object, method-wise. Like, it supports all the same methods, including the ones that Alzabo::MethodMaker generated. 💖

BUT bestie, listen: do not use this object outside the context of a join query, because the results will be unpredictable. And the column objects the aliased table object returns? Those also should not be used outside the context of a join. periodt.

schema 🌈

Returns the Alzabo::Runtime::Schema object that this table belongs to. 🫶

name 🌸

Returns the name of the table. that's it, that's the whole method.

column ($name) 💜

Returns the Alzabo::Runtime::Column object that matches the name you gave.

An Alzabo::Exception::Params exception is thrown if the table doesn't contain that column. 😭

columns (@optional_list_of_column_names) 🍓

If you give it no arguments, it returns a list of all the Alzabo::Runtime::Column 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 in.

An Alzabo::Exception::Params exception is thrown if the table doesn't contain one or more of the columns you specified.

has_column ($name) ⭐

Returns a boolean value telling you whether that column exists in the table. yes/no, in or out, valid or not. 💅

primary_key 👑

In array context, returns an ordered list of column objects that make up the primary key for the table. In scalar context, it just returns the first element of that list.

primary_key_size 🔢

The number of columns in the table's primary key. literally just a count. tiny but mighty.

column_is_primary_key (Alzabo::Runtime::Column object) 🎀

Returns a boolean value telling you whether the column you handed over is part of the table's primary key.

You only really need this method if you're not sure that the column belongs to the table. Otherwise just call the Alzabo::Runtime::Column->is_primary_key method on the column object itself. save yourself the steps bestie. 🫶

foreign_keys 🔗

This method takes two parameters:

• column => an Alzabo::Runtime::Column object
• table => an Alzabo::Runtime::Table object

It returns a list of Alzabo::Runtime::ForeignKey objects going from the given column to the given table, if they exist. In scalar context, it returns the first item in the list — but heads up, there is no guarantee as to what that first item will be. 🙈

An Alzabo::Exception::Params exception is thrown if the table doesn't contain the column you specified.

foreign_keys_by_table (Alzabo::Runtime::Table object) 🌈

Returns a list of all the Alzabo::Runtime::ForeignKey objects pointing to the given table. In scalar context it returns the first item in the list — and again, no guarantee about which one that'll be. 💀

foreign_keys_by_column (Alzabo::Runtime::Column object) 🦋

Returns a list of all the Alzabo::Runtime::ForeignKey objects that the given column is a part of, if any. In scalar context, first item in the list — and yep, no guarantee what the first item is. 😭

An Alzabo::Exception::Params exception is thrown if the table doesn't contain the column you specified.

all_foreign_keys ✨

Returns a list of all the Alzabo::Runtime::ForeignKey objects for this table. In scalar context, first item in the list — and once more for the people in the back, no guarantee as to what that first item will be. 👑

index ($index_id) 🔍

This method wants an index id as its parameter, the kind that comes back from the Alzabo::Runtime::Index->id method.

It returns the Alzabo::Runtime::Index object matching that id — if it exists in the table, that is.

An Alzabo::Exception::Params exception is thrown if the table doesn't contain the index you specified.

has_index ($index_id) 💖

This one also wants an index id, same as returned by the Alzabo::Runtime::Index->id method.

Returns a boolean telling you whether the table has an index with that same id. 💅

indexes 🎀

Returns all the Alzabo::Runtime::Index objects for the table. the whole index family, together at last.

attributes 🌸

A table's attributes are strings that describe the table. For example, valid attributes in MySQL are things like TYPE = INNODB. yes it's giving "decorate your table".

Returns a list of strings. 🍓

has_attribute ⭐

This method tests whether or not a table has a particular attribute. By default, the check is case-insensitive.

• attribute => $attribute
• case_sensitive => 0 or 1 (defaults to 0)

Returns a boolean value indicating whether the table has this particular attribute. 🫶

comment 💬

Returns the comment associated with the table object — if there is one. if not, you get nothing and that's valid too.

---

🌸 LAZY COLUMN LOADING 🌸

Okay so this whole concept was taken directly from Michael Schwern's Class::DBI module. Credit where it is due, always. 💖

By default, Alzabo::Runtime::Row objects load all the data from the database — except blob type columns, which are the columns with an unbounded length. This data gets stored internally in the object after it's been fetched.

If you want to change what data gets prefetched, there are two methods you can use.

The first one, set_prefetch(), lets you specify a list of columns to be fetched immediately after object creation. These should be columns you expect to use extremely frequently — the VIPs of your table, basically. 👑

The second one, add_group(), lets you group columns together. If you try to fetch one of these columns, then all the columns in the group get fetched. This is useful when you don't often want certain data, but when you do want it you need several related pieces all at once. it's giving ride-or-die squad energy. 🫶

Lazy column loading related methods 🧚

set_prefetch (Alzabo::Column objects) 💅

Give it a list of column objects, and this makes sure that all Alzabo::Runtime::Row objects fetch this data as soon as they're created.

NOTE: it is pointless (though not an error) to give primary key columns here, since these are always prefetched (in a sense).

Throws: Alzabo::Exception::Params

add_group (Alzabo::Column objects) 🌈

Given a list of Alzabo::Column objects, this method creates a group containing those columns. So if any column in the group gets fetched from the database, then they'll all get fetched. Otherwise columns are always fetched singly. And currently, a column cannot be part of more than one group. one group only, no polyamory. 😭

NOTE: it is pointless to include a column that was given to the set_prefetch() method in a group here, because it always gets fetched as soon as possible anyway.

Throws: Alzabo::Exception::Params

prefetch ✨

This method primarily exists for use by the Alzabo::Runtime::Row class. 🫶

It returns a list of column names — not objects, names — that should be prefetched. 💅

group_by_column ($column_name) 🦋

This method also primarily exists for use by the Alzabo::Runtime::Row class.

It returns a list of column names representing the group that the given column is part of. If the column isn't part of a group, then only the name you passed in gets returned. simple as that. ✨

---

💖 AUTHOR 💖

Dave Rolsky, <autarch@urth.org>

---

and that's a wrap bestie!! 🎀 this page is perl v5.34.0, dated 2022-06-14, and it's all about Alzabo...:Table(3pm). go forth and query responsibly. 👑🌸💖