Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
Alzabo::Intro(3)      User Contributed Perl Documentation     Alzabo::Intro(3)

       Alzabo::Intro - Introductory information	about Alzabo

       This document provides an introduction to the basics of using Alzabo,
       primarily focused on the	RDBMS-OO mapping functionality.

       The first thing you'll want to do is create a schema.  The easiest way
       to do this is to	reverse	engineer an existing schema.  The Mason	GUI,
       available in the	separate "Alzabo::GUI::Mason" distribution, provides
       another means of	creating a schema.

       And of course, you can create one through a custom Perl script which
       uses the	various	"Alzabo::Create::*" classes.  Here's the beginning of
       such a script:

	 use Alzabo::Create::Schema;

	     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' ] );


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

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



	 if ($@) { handle exceptions }

       Alzabo is a powerful tool but as	with many powerful tools it can	also
       be a bit	overwhelming at	first.	The easiest way	to understand some of
       its basic capabilities is through some examples.	 Let's first assume
       that you've created the following 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

   Fetching data
       In Alzabo, data is returned in the form of a row	object.	 This object
       can be used to access the data for an individual	row.

       Unless you are retrieving a row via a unique identifier (usually	its
       primary key), you will be given a cursor	object.	 This is quite similar
       to how "DBI" uses statement handles and is done for similar reasons.

       First of	all, let's do something	simple.	Let's assume I have a
       person_id value and I want to find all the movies that they were	in and
       print the title,	year of	release, and the job they did in the movie.
       Here's what it looks like:

	 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 =
		 ( 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 =
		     ( foreign_key =>
		       $credit_t->foreign_keys_by_table($movie_t) )->next;

	     my	$job =
		     ( 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";

       A more sophisticated version of this code would take into account that
       a person	can do more than one job in the	same movie.

       The method names	are quite verbose, so let's redo the example using

	 # 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";

   Updating data
       Updates are done	by calling the "update()" method on a row object:

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

       If you are using	"Alzabo::MethodMaker", the per-column accessors	it
       generates for row objects can be	used to	set a column's value:

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

       Be careful with this, though, because updates are done immediately
       against the RDBMS, meaning each call to a setter	method issues an
       "UPDATE"	query.	It's much more efficient to call the "update()"	method
       once with multiple values.

   Deleting data
       To delete a row,	just call it's "delete()" method:


   Validating data
       Let's assume that we've been passed a hash of values representing an
       update to the location table. Here's a way of making sure that that
       this update won't lead to a loop	in terms of the	parent/child

	 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');

       Once again, let's rewrite the code to use "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};

   Using SQL functions
       Each subclass of	Alzabo::SQLMaker is capable of exporting functions
       that allow you to use all the SQL functions that	your RDBMS provides.
       These functions are normal Perl functions.  They	take as	arguments
       normal scalars (strings and numbers), "Alzabo::Column" objects, or the
       return value of another SQL function.  They may be used to select data
       via the "select()" and "function()" methods in both the
       "Alzabo::Runtime::Table"	and "Alzabo::Runtime::Schema" classes.	They
       may also	be used	as part	of updates, inserts, and where clauses in any
       place that is valid SQL.


	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 documentation for the Alzabo::SQLMaker subclass for your RDBMS will
       contain a detailed list of all exportable functions.

   Row Objects Not in the Database
       Sometimes you'll	want to	create an object with the row object API, but
       which does not represent	a row in the database.	See the
       "Alzabo::Runtime::Row" documentation for	details	on how these objects
       can be created.

   Changing the	schema
       In MySQL, there are a number of various types of	integers.  The type
       "TINYINT" can hold values from -128 to 127.  But	what if	have more than
       127 movies?  And	if that's the case we might have more than 127 people

       For safety's sake, it might be best to make all of the primary key
       integer columns "INT" columns instead.  And while we're at it we	want
       to make them "UNSIGNED" as well,	as we don't need to insert negative
       numbers into these columns.

       You could break out the RDBMS manual (because you probably forgot the
       exact "ALTER TABLE" syntax you'll need).	 Or you	could use Alzabo.
       Note that this time we use a "Alzabo::Create::Schema" object, not

	 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' )

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

       Because Alzabo keeps track of the schema's state	the last time it was
       created in the RDBMS, the "create()" method here	will generate the
       appropriate SQL to alter	the RDBMS schema so that it matches the	schema
       as defined in Alzabo.

       Alzabo uses transactions	internally in order to guarantee consistency.
       Obviously, if you are using a database such as MySQL (without InnoDB)
       that does not support transactions, this	is not possible.

       If you would like to use	transactions explicitly	in your	code, please
       make sure to use	the "Alzabo::Schema" class's "begin_work()",
       "commit()", and "rollback()" methods.

       Alzabo uses exceptions as its error reporting mechanism.	 This means
       that all	calls to its methods should be wrapped in "eval{}".  This is
       less onerous than it sounds.  In	general, there's no reason not to wrap
       all of your calls in one	large eval block.  Then	at the end of the
       block simply check the value of $@.

       Also see	the "Alzabo::Exceptions" documentation,	which lists all	of the
       different exception used	by Alzabo.

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

       Its important to	note that some methods (such as	the driver's
       "rollback()" method) may	use "eval" internally.	This means that	if you
       intend to use them as part of the cleanup after an exception, you may
       need to store the original exception in another variable, as $@ will be
       overwritten at the next "eval".

       In addition, some methods you might use during cleanup can throw
       exceptions of their own.

       This is the point where I start wishing Perl had	a real exception
       handling	mechanism built	into the language.

       Because Alzabo saves the	schema objects to disk as raw data structures
       using the "Storable" module, it is possible for a new version of	Alzabo
       to be incompatible with a saved schema.

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

       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.

       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.

       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.

       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.

       It is possible to use the same schema definition	with multiple copies
       of that schema in the RDBMS.  This can be done by setting 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

       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.

       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.

       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.

       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.

       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.

       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.

       Alzabo enforces these referential integrity rules:

       o   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

	   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.

       o   Updates

	   Updates follow the same rules as inserts.

       o   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.

       Dave Rolsky, <>

perl v5.32.1			  2021-02-28		      Alzabo::Intro(3)


Want to link to this manual page? Use this URL:

home | help