> > My applications tend to have a DBI wrapper that provides two pieces of
> > functionality:
>
> 0) Cataloging your database connection information,
> e.g. DBIx::Connect, Config::DBI, DBIx::DBH, DBIx::Password
Config::DBI is something I would use in the future, and those modules
are useful in their own right and can be delegated to.
> > > 1) Business-level functions for accessing datastores (databases and other
> > > stuff)
>
> that sounds like DBIx::AnyDBD to me:
>
> use DBIx::AnyDBD;
>
> my $db = DBIx::AnyDBD->connect("dbi:Oracle:sid1",
> "user", "pass", {}, "MyClass");
>
> my $foo = $db->sales_to_date('2005-03');
> my $blee = $db->month_to_date(2004-12');
You're still writing the MyClass::Oracle and MyClass::MySQL. And,
that's not the point of encapsulating at this level. The point is to
write your business-level code in a business-speak. DBIx::AnyDBD
doesn't facilitate that. In fact, it somewhat complicates that effort.
> > > 2) Functionality for generating SQL on the fly, using somewhat complex
> > graph theory
>
> I remember Ovid mentioning he did a similar thing at his previous
> company. I don't believe either of you have released these powerful
> modules to CPAN.
I have a few problems:
1) API
How should this be designed to be used? I know how we used it in the
one place I wrote it for. Once I write it for somewhere else, maybe
I'll have a better idea of general usage. But, this isn't a simple API
task.
2) Schema normalization
My code allowed for selectively denormalized tables, but the general
solution requires normalizations to at least 3rd normal, if not
Boyce-Codd. Otherwise, you cannot guarantee that the correct joins
will be made. (It's a relative of the halting problem.)
3) Schema discovery
I wrote mine to require that the tables and joins be listed in some
HoH. But, schema discovery (including joins based on FK's) would be
preferable. How should this be done? How should the user be able to
override it?
In other words, I whipped something out and never wrote any
general-purpose requirements for it. It did what I needed it to do,
but requires more infrastructure than a CPAN module should rightly
have. If you want to help me figure out the correct API / user
interface, I would love to release my code.
> > The first is for things like "Given this username and password, can
> > the person log in?". The second is for reports. My reports aren't your
> > grandma's reports - you have a list of options to choose from and the
> > app figures out what SQL to run from there, including all the table
> > joins.
>
> any code samples to look at?
Kinda-sorta. Basically, you would have the following structure:
1) Report base class
2) Report child class that implements the actual report
3) Parameters that the report child class declares as needed, each
with its own class
The report child class would specify the invariants that made this the
"Sales Report" - the select clauses and the minimally-sufficient where
clauses. Maybe a few groupbys. Then, the base class would handle the
parameters. This is where it would become the "Sales Report for Green
Widgets in the Foo Region grouped by Branch for the months of Janurary
and February sorted by total sales ascending". The parameters would
simply be added to the SQL statement as select, where, and group by
clauses. Then, the fancy-shmancy SQL builder would run, pulling in
tables and join statements as necessary into the FROM clauses. It
would run, then it would call an inflater function in the child class.
That inflater would take the flat dataset from the SQL statement and
convert it into whatever it needed to. This included calling other SQL
statements, maybe a few id->name functions, or whatever. It would then
call the parent's sorting routine, so it would sorted correctly.
Finally, it goes to the base-class's print function, which determines
if it should be HTML, PDF, or XLS, and outputs it appropriately.
The code for that is about 2000 lines long, across roughly 50 classes.
What would you like to look at?
> > So, what need do I have for a phrasebook? I can understand, to some
> > degree, putting the statements from the first grouping into a
> > phrasebook, but they're already behind a layer of abstraction and in
> > one place. So, doesn't that mean I've satisfied the benefits of a
> > phrasebook?
>
> yes, I see your point. You custom-generate SQL
> dyanmically. SQL::Phrasebooks are for storing static SQL. They are
> completely inappropriate for dynamic SQL.
About half of my SQL statements are usually static. But, I've already
abstracted them out of the business logic. So, why do I need a
phrasebook? In fact, I think I'm past a phrasebook in that the
business logic doesn't even know that the data is coming from a RDBMS.
All it cares is that the $foo object can get the needed data.
Rob
_______________________________________________
sw-design mailing list
[email protected]
http://metaperl.com/cgi-bin/mailman/listinfo/sw-design
