[jifty-devel] Referential integrity

[Edmund von der Burg]

On 09/03/07, Thomas Sibley <trs at bestpractical.com> wrote:
> Making all references mandatory now will probably break current Jifty
> apps.  Plus, I think it's better for the developer to ask for it
> explicitly so they know they have to code for it.

I agree that we don't want to break existing apps.

Take the following setup for example:

    # In MyApp::Model::Book
    column author =>            #
      references MyApp::Model::Author,    #
      is mandatory;                       #

    # In MyApp::Model::Author
    column books =>                       #
      references MyApp::Model::BookCollection by 'author';

In this case as the 'author' field is mandatory I would interpret it
as meaning that the author muts exists, not just as an id in the books
table but also as a corresponding entry in the authors table.

If the 'author' field was not mandatory I would allow it to be NULL,
but I would not want it to be an integer that did not correspond to an
entry in the authors table.

Deleting the entry in authors should trigger a search in all
referenced tables for related rows. If any are found then their 'is
mandatory' flag is checked. If any are set to true then the deletion
is aborted. If there are related rows which are not mandatory then
they are set to NULL.

Doing it this way does not rely on database support for foreign keys
which keeps the abstraction simple, although it may well be slower
than catching exceptions thrown by the database. I'd still like the
referential integrity baked into the database schema though so that it
is enforced even when access to it does not occur through the jifty
DBI code.

I note that in the DBIx::DBSchema docs it says 'Foreign keys and other
constraints are not yet supported.'. This implies that the 'do it all
in Jifty' method is probably the way to go for now.

Any thoughts?

Cheers,
  Edmund.

PS Another way to go is to alter the schema after it is created and
then get jifty to detect and gracefully handle exceptions thrown by
the database. This does not seem to elegant though....