[Dbix-class] App Design RFC & Dynamic/Runtime Schema Queries

Brad Bowman list at bereft.net
Wed Mar 25 11:18:17 GMT 2009


Hello DBIx::Classy people,

I work on an application that is now using DBIx::Class (previously it
used Class::DBI) and I'd like to present the architecture for comment.
I'm relatively new to DBIx::Class and feel like I got in at the deep-end,
so may be doing things in an awkward manner.  However, this is not a
normal database app, so there may be reasons (not necessarily good ones)
for the way things are.

At a high-level, the app is parsing a files in a format as described by
meta-data, and inserting it into a SQLite database.
Various test are run including checking expected relationships
(so foreign keys can't be too strict) and the results are stashed
away in various tables.

Note that the sql schema is currently generated based on the metadata
(not via deploy or SQLT) and contains dynamically defined triggers,
views & more.

Another process (either concurrently or later) opens the .db and does
interactive reporting with it.  It peeks at the database, at one information
row in partiticular, and finds/creates the right DBIC class setup.

This reporting process was originally single-use but now can reattach
to different databases.  This means namespaces need to be distinct for
the DBIC class.  (This change forced the move to DBIC)

This was all working ok with CDBI, and now is working with DBIC, but the
migration may have left things messier than they should be. Especially
since it went via Schema::Loader after a brief flirt with CDBICompat.

So it's all very dynamic, meta-data driven, the .db is more of a relational
cache than a grown up database and is often discarded.
The design is concerned with reporting to people who care in ways
they understand.  Not about the usual database concerns.
(In fact, the data is decanted from the sqlite dbs and loaded into a
more normal postgres database)

There's also many years of history, so design decisions were not made
afresh with DBIC, but could probably benefit from DBIC angled reflections.

Enough preamble...  more than enough...


The schema is DBIx::Class::Schema based, but the namespace for a
"schema instance" is further qualified by a file type and version:
  ...::Schema::$file_type::V$version
to avoid conflicts between concurrently loaded schemas.

There's a natural hierarchy which is followed by the inheritance
relationships of the class:

   ..::Schema::Common
   ..::Schema::A
   ..::Schema::A::V1
   ..::Schema::A::V1::{many classes here}
   ..::Schema::A::V2
   ..::Schema::B
   ..::Schema::B::V1

So overrides for A::V1 would sit in a .pm file under that,
but we also can override at the A level and provide Common classes too.
Override or augment really. These overrides worked fine up to the point
that certain relationship were required.

Initially, the Common classes (Line, Error, Rule) just sat in
the Common namespace and had relationships to each other, while the
other classes existed at the version-specific leaves.

  ..::Common::Line->has_many('errors' => '..::Schema::Common::Error' ...);

  ..::Schema::A::V1::L1->has_many('other_leaf' => '..::Schema::A::V1::L2', ..)

This broken down when, in terms of the example above, a Line needed to
define a relationship to version specific Error (the real case was slightly
different).  The solution I ended up using was to delay calling
table(), add_columns() until the specific namespace was known:

sub define_table {
  my ($namespace, $metadata) = @_;
  $namespace->table(..);
  $namespace->add_columns(.. work them out with $metadata ..);
}

Once this pass is done for all sources, I can resolve the relationships:

sub define_relationships {
  my ($namespace, $metadata) = @_;
  my $schema = $namespace->schema;
  $line_package->has_many('errors' => $schema->class('Error'), ...)
}

The delayed define_table also got me around having to replace the
guessed resultset_class (either by wrapping table as in:
  http://osdir.com/ml/lang.perl.modules.dbix-class/2006-10/msg00113.html
or by some other method).  At least I think that's what's going on.


On irc, mst's initial reaction to a sketch of the above was
"that's horrible", followed by:

     seems to me like you'd be better off just doing
     Class::C3::Componentised->inject_base($new_class, 'DBIx::Class::Core');
     $new_class->table($table);
     $new_class->add_columns(...);
     etc.

(I currently do something like this for the leaf records, as they're
entirely dynamically generated)

So this email is partly to provide the bigger picture to critique.

Is the define_table / define_relationships idea sane?
Is there a better way?
How are other's handling highly dynamic, overridable schemas?
It kinda seems like a delayed evaluation relationship, instead of
a fully-qualified or immediately qualified one, would be useful..?

I'm also interested in general feedback (and have braced for
"this is totally daft").


This email is epic enough already, so I'll just briefly mention that I
had a "search_joining" function under CDBI that tried to work out the
joins from the columns or qualified columns you gave it.
In an intriguing blend of clever and stupid it used a column map and
relationship graph to DWIMMOTT (dwim most of the time).

Some of that functionality has been replaced by "search" with joins,
but it's not as psychic.  One replaced bit is:

     @errors = $schema->resultset('Error')->search(
             \%criteria,
             {
                 join => [ 'rule', 'key_set' ],

                 # these two combine to to override the me. default table alias
                 # (required as criteria doesn't use "me." and sqlite needs
                 #  fully-qualifed column names everywhere on 2+ level joins)
                 from => [{ 'error' => 'error' }],
                 alias => 'error',

                 order_by => 'error.line',
            }
(Any better solution to the from/alias thing in above?)

But it always joins to rule and key_set and is therefore slightly
less efficient (although it hasn't mattered so far).
I also can't throw as many different things into \%criteria.
Is there another psychic joiner out there?  Or is this a dailywtf?

The app runs on both DBIC 0.07006 and 0.08010, Linux & Windows,
under PAR sometimes.  Perl 5.10 after the performance bug bit.

If you read all the way here then thank you,

Brad

-- 
  Last year I went to the Kase Execution Grounds to try my hand at beheading,
  and I found it to be an extremely good feeling.  To think it is unnerving
  is a symptom of cowardice.         -- Hagakure http://bereft.net/hagakure/



More information about the DBIx-Class mailing list