Hi David,
All good points; I'll try to address them one by one.
On Dec 31, 2003, at 1:51 AM, David Manura wrote:
It seems, as least from the SYNOPSIS, that the module itself could deduce the "type," "requires," and "provides" statements by analyzing the corresponding SQL rather than have the programmer write these.
Yes, for simple SQL that's true (although I hate to parse SQL); this SQL is a bit harder though (not because of the parsing, but because of the ambiguity of what the required and provided fields should be named):
name: people_by_decades
provides:
- fullname
requires:
- year_digit
sql: >
SELECT CONCAT(first, IFNULL(CONCAT(" ", initial), ""), " ", last)
FROM person
WHERE age % 10 == ?You might argue that I could have written the SQL as "SELECT CONCAT(...) AS fullname)" and "WHERE age % 10 = $year_digit", and done away with the provides/requires bit (thus requiring me to carefully parse the SQL). For now, I like the "one-stop shopping" that the metadata gives me (and it allows me to provide default values, etc; see below).
As an aside, with this SQL example, the SQL catalog becomes less of a recapitulation of the underlying schema (as my documentation examples are), and more of an actual API to the data. And DBIx::Declare itself begins to look more like a specialized Data::Flow than a revamped DBIx::Class.
As is, how locking across multiple SQL statements [will be handled remains uncertain].
Good question; I think that an individual "request" should be atomic (unless otherwise specified) even if processing the request requires firing off multiple statements, requiring an isolated transaction; but I'll admit I haven't had the chance to totally think this through yet. Comments and specific use cases would be appreciated.
[How] sorting (SORT BY), and ranges requests (LIMIT) will be handled remains uncertain. One might apply different sorts on the same base SQL statement if, for example, an HTML table had buttons to choose the column and order to sort by.
Yep; in the TODO section, I list the need for an SQL::Phrasebook-like way to interpolate values into the SQL; maybe something like this:
name: cities_by_state
requires:
- state_id
- field: cities_sort_by
default: name
sql: >
SELECT city_id, name
FROM city
WHERE state_id = ?
SORT BY $cities_sort_byAnd do so similarly for other non-placeholder bindable bits ...
Then there is the question of how to disambiguate two instances of the same required or provided "data type" in the same context (e.g. "city" as in city born in v.s. "city" as in city currently residing).
Yes, I don't explicitly talk about this much in my first pass documentation, but the idea is that requested data fields may be "tablename-qualified". But perhaps you meant this case:
name: birthcity_by_person
requires: person_id
provides: city.name
sql: >
SELECT city.name
FROM city
INNER JOIN address USING (city_id)
WHERE address.birth = 1
AND address.person_id = ?name: currentcity_by_person
requires: person_id
provides: city.name
sql: >
SELECT city.name
FROM city
INNER JOIN address USING (city_id)
WHERE address.current = 1
AND address.person_id = ?Again, the way I'd solve this is to make the SQL declaration more precise: the required/provides fields need not exactly mimic the underlying SQL. The two "provides" lines above turn into "provides: birthcity" and "provides: currentcity". And again, the SQL declaration behaves more like an API than a strict relational mapping.
Remember also that DBIx::Declare will provide "direct" SQL selection by name, similar to the other Phrasebook modules, so you can mix and match your behavior.
Still, I believe something declarative like this would be useful. SQL itself is intended to be declarative but only at the level of the individual statement--not the program as a whole--unless, of course, the program is one big SQL statement.
SQL isn't just intended to be declarative, it is declarative (procedural variants like TransactSQL and PL-SQL aside).
What might be called for is something similar to a knowledgebase (e.g. Cyc, www.cyc.com) implemented on top of a relational database and allowing you to make inferences on both classes and objects given the known entity relationships amoung them. So, I query the knowledgebase with "If I know such and such, tell me such and such."
While I appreciate the power of KB's, I have the immediate, "real-world" incentive that my data currently is in a RDB, and I just want to get it out for reporting, without having the middleware "glue" code know about either the report templates, or the database schema. My ultimate goal is to have entirely template-driven dynamic content (but the templates *don't* have executable code in them a la PHP, EmbPerl, ColdFusion, etc; the templates *only* have tagged parameters a la HTML::Template and others), but I don't (yet) require that templates be able to demand data not immediately obtainable from supplied SQL (which is what a KB would provide).
<digression>
I think one of the strengths of ColdFusion and similar technologies is that it's very easy to start making dynamic content without knowing any programming language per se, only a little SQL:
<html> <cfquery name="Person"> SELECT name FROM person WHERE person_id = #Session.person_id# </cfquery> <cfoutput> <p>Hello <b>#Person.name#</b> </cfoutput> </html>
All I want to do with DBIx::Declare is provide a mechanism to separate the SQL into a separate file than the templates, so that the template designer only has to write:
<html> <p>Hello <b>#person.name#</b> </html>
And the (now reusable) SQL as:
name: person_name_by_id provides: person.name requires: person_id sql: > SELECT name FROM person WHERE person_id = ?
And have some DBIx::Declare-aware template-processing engine handle page requests automagically such that adding new templates and/or SQL code doesn't involve writing or maintaining a single line of Perl code.
</digression>
Thanks for your helpful comments,
-Aaron
