in reply to Re: Creating a model of a bad database
in thread Creating a model of a bad database

I know DBIx::Class can handle fetching data with complex relationships. Can it handle inserts and updates that span multiple tables, without exposing the actual storage details to the user? What if they require custom SQL or custom perl processing?
  • Comment on Re^2: Creating a model of a bad database

Replies are listed 'Best First'.
Re^3: Creating a model of a bad database
by ph713 (Pilgrim) on Dec 12, 2006 at 19:18 UTC
    I'm guessing by that you mean insert/update to a database view, or something very like a view. DBIx::Class doesn't yet do that, and I don't know that it ever will. In PostgreSQL you can only do it by defining custom rules for the view AFAIK. I haven't had any personal experience with a database that does it with no extra work, if there are any. Topics like this have been discussed on irc.perl.org #dbix-class before, but implementing them in the generic case is tricky at best.

    Along these kinds of lines, the best we could offer at the moment is that you can make a ResultSource class for view just like you could for a table (if your database doesn't support real views, that can be hacked as well, by basically specifying the SQL that generates the view as the table name, IIRC), and then you could override the update and insert methods for this source and include your own custom code that breaks out the data and does the insert/update to the underlying tables (either using DBIC abstractions of those tables, or raw sql via a dbh accessor).

[reply]
      The custom ResultSource doesn't sound too bad. The reason I ask is that it sounds like that's what Ovid will need here, with a database schema that he wants to abstract away. Otherwise, he'd have to make his API deal with the actual tables as they currently are.
[reply]

        Well, as much as possible, I'll simply be denying people accessors and mutators. They'll get the behaviors they need and that's it. Of course, it's not quite that simple, but by focusing on behavior instead of data (which is what OO should be doing anyway), I think I can contain the worst of the problem.

        Cheers,
        Ovid

        New address of my CGI Course.

[reply]

      I haven't used DBIx::Class or another ORM module (this is long due actually...) but from a model perpective views are usually to drive queries. Inserts or Updates on views will be in general very database-dependent or even not supported (or via custom-made procedures that usually defeat the use of an ORM)

      Am I missing something?

      hth --stephan
[reply]
Re^3: Creating a model of a bad database
by rhesa (Vicar) on Dec 12, 2006 at 19:28 UTC
    I was thinking if it might be possible to handle inserts and updates to related tables with triggers. Since you'll have the columns of the other tables defined in this object anyway (in a TEMP column group), that should be relatively painless.
[reply]

      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.

[reply]
        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]

In Section Seekers of Perl Wisdom