Re^4: Creating a model of a bad database

If you have groups of related tables, you can present a view and then use triggers to handle inserts and updates spanning multiple tables in the view. Well, you could, but MySQL's views are broken and don't allow you to attach rules or triggers to them (I hear this may change soon, but I'm not sure). Both Postgres and SQLite handle this correctly.

Cheers,
Ovid

New address of my CGI Course.

Comment on Re^4: Creating a model of a bad database

Replies are listed 'Best First'.
Re^5: Creating a model of a bad database by rhesa (Vicar) on Dec 13, 2006 at 12:20 UTC
Exactly, that's why an ORM such as Class::DBI (or the other ones with which I'm not familiar yet) would be a good choice: you could program the triggers in the ORM layer. Of course, that requires that all access to the database goes through your ORM, and I don't know how realistic that is in your situation. With judicious overriding of CDBI's basic SQL snippets, connecting multiple tables is fairly simple. As an example, Retrieve is defined like this: `__PACKAGE__->set_sql(Retrieve => <<''); SELECT __ESSENTIAL__ FROM __TABLE__ WHERE %s` [download] I think it's fairly obvious how to extend that to perform a join. Doing updates or inserts this way seems more difficult though. I don't think MySQL allows multiple statements from a single DBI call (e.g. `$dbh->do('update foo; update bar')`). Besides, it looks like the `%s` placeholders are too limited for that anyway. I'm probably not telling you anything you didn't already know, but I thought I'd write down these hints anyway. Someone is bound to have a use for them :-)	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^5: Creating a model of a bad database
by rhesa (Vicar) on Dec 13, 2006 at 12:20 UTC

With judicious overriding of CDBI's basic SQL snippets, connecting multiple tables is fairly simple. As an example, Retrieve is defined like this:

__PACKAGE__->set_sql(Retrieve => <<'');
SELECT __ESSENTIAL__
FROM   __TABLE__
WHERE  %s
[download]

$dbh->do('update foo; update bar')

%s

I'm probably not telling you anything you didn't already know, but I thought I'd write down these hints anyway. Someone is bound to have a use for them :-)

[reply]
[d/l]
[select]