Alzabo::Intro(3pm)

✨ Alzabo::Intro ✨

okay bestie, welcome to Alzabo::Intro 💖 this is your lil gateway doc into the whole Alzabo universe, and no cap, it's giving "everything you need to know to get started."

---

🌸 NAME 🌸

Alzabo::Intro — the introductory tea on Alzabo, served warm ☕✨

---

🦋 DESCRIPTION 🦋

so like, this document is your intro to the basics of using Alzabo, and it's primarily focused on the RDBMS-OO mapping functionality. that's the whole vibe. that's the assignment. 💅

---

🎀 CREATING A SCHEMA 🎀

first thing you're gonna wanna do — like, the very first thing — is create a schema. and the easiest way to do that? reverse engineer an existing schema. iconic. effortless. we love a shortcut. 🫶

the Mason GUI, which lives in its own separate Alzabo::GUI::Mason distribution, is another way to make a schema. so you've got options, bestie. 🌈

and of course 😌 you can always roll your own through a custom Perl script that uses the various Alzabo::Create::* classes. here's the beginning of a script like that:

use Alzabo::Create::Schema;

eval
{
    my $s = Alzabo::Create::Schema->new( name  => 'foo',
                                         rdbms => 'MySQL' );

    my $table = $s->make_table( name => 'some_table' );

    my $a_col = $table->make_column( name => 'a_column',
                                     type => 'int',
                                     nullable   => 0,
                                     sequenced  => 0,
                                     attributes => [ 'unsigned' ] );

    $table->add_primary_key($a_col);

    my $b_col = $table->make_column( name => 'b_column',
                                     type => 'varchar',
                                     length   => 240,
                                     nullable => 0 );

    $table->make_index( columns => [ { column => $b_col,
                                       prefix => 10 } ] );

    ...

    $s->save_to_file;
};

if ($@) { handle exceptions }

ngl, that ... in the middle is literally in the original, so don't come for me 😭 it's just showing you where the rest of your stuff goes. and the if ($@) { handle exceptions } line is your cue to, well, handle exceptions. 💀

---

✨ USAGE EXAMPLES ✨

okay bestie, so Alzabo is a whole powerhouse but like... powerhouses can be a lot at first, no cap. the easiest way to get the vibe is just by looking at some examples. so let's pretend you've already made this schema:

  TABLE: Movie
  movie_id                 tinyint      -- primary key
  title                    varchar(200)
  release_year             year

  TABLE: Person
  person_id                tinyint      -- primary key
  name                     varchar(200)
  birthdate                date
  birthplace_location_id   tinyint      -- foreign key to location

  TABLE: Job
  job_id                   tinyint      -- primary key
  job                      varchar(200) -- something like 'actor' or 'director'

  TABLE: Credit
  movie_id                 tinyint      -- primary key part 1, foreign key to movie
  person_id                tinyint      -- primary key part 2, foreign key to person
  job_id                   tinyint      -- primary key part 3, foreign key to job

  TABLE: Location
  location_id              tinyint      -- primary key
  location                 varchar(200) -- 'New York City' or 'USA'
  parent_location_id       tinyint      -- foreign key to location

cute, right? okay now let's actually do stuff with it 💅

---

🍓 Fetching data

so in Alzabo, data comes back to you as a row object. that's the thing you use to grab the data for one individual row. iconic.

and unless you're pulling a row by a unique identifier (usually its primary key), you get handed a cursor object instead. it's super similar to how DBI does statement handles, and honestly it's done for basically the same reasons. iykyk.

so first, let's keep it simple. say i have a person_id value and i wanna find all the movies they were in, and print the title, the year it came out, and the job they did in that movie. here's the vibe:

  my $schema = Alzabo::Runtime::Schema->load_from_file( name => 'movies' );

  my $person_t = $schema->table('Person');
  my $credit_t = $schema->table('Credit');
  my $movie_t  = $schema->table('Movie');
  my $job_t    = $schema->table('Job');

  # returns a row representing this person.
  my $person = $person_t->row_by_pk( pk => 42 );

  # all the rows in the credit table that have the person_id of 42.
  my $cursor =
      $person->rows_by_foreign_key
          ( foreign_key =>
            $person_t->foreign_keys_by_table($credit_t) );

  print $person->select('name'), " was in the following films:\n\n";

  while (my $credit = $cursor->next)
  {
      # rows_by_foreign_key returns a RowCursor object.  We immediately
      # call its next method, knowing it will only have one row (if
      # it doesn't then our referential integrity is in trouble!)
      my $movie =
          $credit->rows_by_foreign_key
              ( foreign_key =>
                $credit_t->foreign_keys_by_table($movie_t) )->next;

      my $job =
          $credit->rows_by_foreign_key
              ( foreign_key =>
                $credit_t->foreign_keys_by_table($job_t) )->next;

      print $movie->select('title'), " released in ", $movie->select('release_year'), "\n";
      print '  ', $job->('job'), "\n";
  }

okay real talk — a fancier version of this code would also account for the fact that a person can do more than one job in the same movie. just saying 👀

anyway the method names are so verbose it's giving dictionary, so let's redo it using Alzabo::MethodMaker:

  # Assume that the method_namer() subroutine pluralizes things as one
  # would expect.
  use Alzabo::MethodMaker( schema      => 'movies',
                           all         => 1,
                           name_maker  => \&method_namer );

  my $schema = Alzabo::Runtime::Schema->load_from_file( name => 'movies' );

  # instantiates a row representing this person.
  my $person = $schema->Person->row_by_pk( pk => 42 );

  # all the rows in the credit table that have the person_id of 42.
  my $cursor = $person->Credits;

  print $person->name, " was in the following films:\n\n";

  while (my $credit = $cursor->next)
  {
      my $movie = $credit->Movie;

      my $job = $credit->Job;

      print $movie->title, " released in ", $movie->release_year, "\n";
      print '  ', $job->job, "\n";
  }

look how clean that is. slay. ✨

---

🦋 Updating data

updates happen when you call the update() method on a row object:

  $movie->update( title => 'Chungking Express',
                  year  => 1994 );

and if you're using Alzabo::MethodMaker, the per-column accessors it generates for row objects can set a column's value too:

  $movie->title('Chungking Express');

⚠️ okay but be careful with this one bestie!! updates are done immediately against the RDBMS, which means every single call to a setter method fires off its own UPDATE query. it is way, way more efficient to just call update() once with multiple values. trust. 💀

---

🎀 Deleting data

to delete a row, you just call its delete() method:

  $movie->delete;

gone. deleted. not me being dramatic but that's literally it. 💅

---

🌸 Validating data

okay so let's say someone handed us a hash of values representing an update to the location table. here's one way to make sure that update won't create a loop in the parent/child relationships:

  sub update_location
  {
      my $self = shift; # this is the row object

      my %data = @_;

      if ( $data{parent_location_id} )
      {
          my $parent_location_id = $data{parent_location_id};
          my $location_t = $schema->table('Location');

          while ( my $location =
                  $location_t->row_by_pk( pk => $parent_location_id ) )
          {
              die "Insert into location would create loop"
                  if $location->select('parent_location_id') == $data{location_id};

              $parent_location_id = $location->select('parent_location_id');
          }
      }
  }

and once again, let's rewrite it with Alzabo::MethodMaker:

  sub update_location
  {
      my $self = shift; # this is the row object

      my %data = @_;

      if ( $data{parent_location_id} )
      {
          my $location = $self;
          while ( my $location = $location->parent )
          {
              die "Insert into location would create loop"
                  if $location->parent_location_id == $data{location_id};
          }
      }
  }

so much shorter, we love to see it 🫶

---

⭐ Using SQL functions

every subclass of Alzabo::SQLMaker can export functions that let you use all the SQL functions your RDBMS provides. and these are just normal Perl functions, fr fr. they take normal scalars (strings and numbers), Alzabo::Column objects, or the return value of another SQL function as their arguments. you can use them to select data via the select() and function() methods in both the Alzabo::Runtime::Table and Alzabo::Runtime::Schema classes. you can also use them as part of updates, inserts, and where clauses — basically anywhere that's valid SQL.

examples, bestie:

 use Alzabo::SQLMaker::MySQL qw(MAX NOW PI);

 my $max =
     $table->function( select => MAX( $table->column('budget') ),
                       where  => [ $table->column('country'), '=', 'USA' ] );

 $table->insert( values => { create_date => NOW() } );

 $row->update( pi => PI() );

 my $cursor =
     $table->rows_where( where =>
                         [ $table->column('expire_date'), '<=', NOW() ] );

 my $cursor =
     $table->rows_where( where =>
                         [ LENGTH( $table->column('password') ), '<=', 5 ] );

the docs for the Alzabo::SQLMaker subclass for your RDBMS have a full detailed list of every exportable function. go look 👑

---

🧚 Row Objects Not in the Database

sometimes you wanna make an object that uses the row object API but does not actually represent a row in the database. it's giving imaginary friend energy and we respect it. check the Alzabo::Runtime::Row documentation for the details on how to create these.

---

👑 Changing the schema

so in MySQL there's a whole bunch of different integer types. TINYINT can hold values from -128 to 127. but like... what if you have more than 127 movies?? and if that's the case you probably have more than 127 people too 😭

for safety's sake, it might be best to make all the primary key integer columns INT columns instead. and while we're at it, let's make them UNSIGNED too, since we don't need to insert negative numbers into those columns.

you could crack open the RDBMS manual (because you definitely forgot the exact ALTER TABLE syntax you'll need, no shame). OR you could use Alzabo. and note: this time we use an Alzabo::Create::Schema object, not Alzabo::Runtime::Schema:

  my $schema = Alzabo::Create::Schema->load_from_file( name => 'movies' );

  foreach my $t ( $schema->tables )
  {
      foreach my $c ( $t->columns )
      {
           if ( $c->is_primary_key and lc $c->type eq 'tinyint' )
           {
                $c->set_type('int');
                $c->add_attribute('unsigned');
           }
      }
  }

  $schema->create( user => 'user', password => 'password' );
  $schema->save_to_file;

and here's the magic: because Alzabo keeps track of the schema's state from the last time it was created in the RDBMS, the create() method here will generate the appropriate SQL to alter the RDBMS schema so it matches the schema as defined in Alzabo. chef's kiss. 🔥

---

💜 TRANSACTIONS 💜

Alzabo uses transactions internally to guarantee consistency. obviously if you're using a database like MySQL (without InnoDB) that doesn't support transactions, that's just... not possible. 😭

if you wanna use transactions explicitly in your own code, please please please make sure to use the Alzabo::Schema class's begin_work(), commit(), and rollback() methods. 🫶

---

😭 EXCEPTIONS 😭

Alzabo uses exceptions as its error reporting mechanism. that means every call to its methods should be wrapped in eval{}. and honestly? it's less annoying than it sounds. in general there's no reason not to just wrap all your calls in one big eval block, then at the end of the block just check the value of $@. easy.

also check the Alzabo::Exceptions documentation, it lists all the different exceptions Alzabo uses. it's giving encyclopedia but it's useful.

this is similar to using DBI with the RaiseError attribute set to a true value.

⚠️ important important important: some methods (like the driver's rollback() method) may use eval internally. so if you're planning to use them as part of the cleanup after an exception, you might need to stash the original exception in another variable, because $@ will get overwritten at the next eval. 💀

plus, some methods you might use during cleanup can throw exceptions of their own. yeah. fun.

and this right here is the exact point where i start wishing Perl had a real exception handling mechanism built into the language. 🙈

💾 BACKWARDS COMPATIBILITY 💾

okay so here's the tea: Alzabo saves your schema objects to disk as raw data structures using the Storable module. which is fine and all, but it means a new version of Alzabo can be incompatible with a schema you saved with an old version. yeah. it's giving "surprise".

As of Alzabo version 0.65, Alzabo can now detect older schemas and will attempt to update them if possible. bestie is trying, okay?

When you attempt to load a schema — whether of the Alzabo::Create::Schema or Alzabo::Runtime::Schema classes — Alzabo will determine what version of Alzabo created that schema. detective mode: activated 🔍

If updates are necessary, Alzabo will first back up your existing files with the extension .bak.v{version}, where {version} is the version of Alzabo which created the schema. so you're covered, mostly. 🫶

Then it will alter the schema as necessary and save it to disk.

This will all happen transparently, as long as the process which initiated this process can write to the schema files and the directory they are in. (yes, write permissions, the eternal villain 😭)

Alzabo will need the Alzabo::Create::* classes to update the schema. If these have not been loaded already, Alzabo will do so and issue a warning to say that this has happened — in case you would like to restart the process without these classes loaded. the warning is literally just Alzabo being like "hey, heads up, I loaded these, in case you wanted to not have that happen."

---

👯 MULTIPLE COPIES OF THE SAME SCHEMA 👯

It is possible to use the same schema definition with multiple copies of that schema in the RDBMS. no cap. here's how:

Set the schema_name parameter whenever you call a method that connects to the RDBMS, such as Alzabo::Create::Schema->create or Alzabo::Runtime::Schema->connect.

This will override the default, the schema's name as given when it was first created via Alzabo::Create::Schema->new.

⚠️ now listen up because this next bit is a certified trap. Every time you call create() or sync_backend(), the schema will consider itself to have been instantiated. This means that if you call create() twice, each time with a different schema_name parameter, then you will probably not be able to generate useful diffs via the make_sql() method in the future.

This is a bug that is unlikely to be fixed. fr fr. it's just gonna live there. 💀

---

🌈 MULTIPLE RDBMS SUPPORT 🌈

Alzabo aims to be as cross-platform as possible. To that end, RDBMS specific operations are contained in several module hierarchies. The goal here is to isolate RDBMS-specific behavior and try to provide generic wrappers around it, inasmuch as is possible. slay, separation of concerns, we love to see it ✨

🚗 Alzabo::Driver::* 🚗

The first, the Alzabo::Driver::* hierarchy, is used to handle communication with the database. It uses DBI and the appropriate DBD::* module to handle communications. It provides a higher level of abstraction than DBI, requiring that the RDBMS specific modules implement methods to do such things as create databases or return the next value in a sequence.

📜 Alzabo::RDBMSRules::* 📜

The second, the Alzabo::RDBMSRules::* hierarchy, is used during schema creation in order to validate user input such as schema and table names. It also generates DDL SQL to create the database or turn one schema into another (sort of a SQL diff). Finally, it also handles reverse engineering of an existing database. icon behavior honestly.

🧮 Alzabo::SQLMaker::* 🧮

The Alzabo::SQLMaker::* hierarchy is used to generate DML SQL and handle bound parameters.

---

The RDBMS to be used is specified when creating the schema. Currently, there is no easy way to convert a schema from one RDBMS to another, though this is a future goal. manifesting it for them 🙏

---

🔗 REFERENTIAL INTEGRITY 🔗

Alzabo can maintain referential integrity in your database based on the relationships you have defined. This can be toggled via the Alzabo::Runtime::Schema->set_referential_integrity() method. on/off, your call bestie 🎀

Alzabo enforces these referential integrity rules:

• Inserts 💌
  • An attempt to insert a value into a table's foreign key column(s) will fail if the value does not exist in the foreign table.
  • If a table is dependent on another table, any columns from the dependent table involved in the relationship will be treated as not nullable.
  • If the relationship is one-to-one, all columns involved in the foreign key will be treated as if they had a unique constraint on them (as a group if there is more than one) unless any of the columns being inserted are NULL.
  • The exception to this rule is that no attempt is made to enforce constraints on a table's primary key, as that could conceivably make it impossible to insert a row into the table. so like, the PK gets a free pass, for good reason.
• Updates ✏️
  • Updates follow the same rules as inserts.
• Deletes 🗑️
  • When a row is deleted, foreign tables which are dependent on the one being deleted will have the relevant rows deleted. Otherwise, the foreign table's related column(s) will simply be set to NULL.

---

✍️ AUTHOR ✍️

Dave Rolsky, <autarch@urth.org>

---

and that's a wrap on the whole page, bestie!! 🦋 this was documented for perl v5.34.0, dated 2022-06-14, and it's the Alzabo::Intro(3pm) manual page — go forth and schema responsibly 💅👑