On Fri, Sep 23, 2011 at 11:19:32PM -0400, Brendan Byrd wrote: > [...], the Perl community in general needs this. Developers need to just > grab some modules via CPAN, describe the relational models (if they don't > already exist), and have the flow of data *just work!* [...]
I would love to have some idea of how to write multi-DBMS SQL applications with DBI (or with anything else). The main problem I see is the differences in SQL between DB backends. They are large, and SQL::Statement just adds another dialect. For a start it would be nice to have a matrix up somewhere listing DBMSes against SQL constructs, so I can look up, for instance, against which types of DBD backends which SQL syntax will actually work (i.e. not only be accepted but also take effect). You can't do much that will work everywhere. For instance, I have a simple script that needs to count the number of distinct values in columns. This seems like a pretty elementary thing to do, but after a lot of tweaking and tinkering I've found that - SELECT COUNT(DISTINCT) ... is invalid in Jet (Access MDB) - SELECT COUNT(*) FROM (SELECT DISTINCT ...)) is invalid elsewhere - SELECT DISTINCT ... is accepted but just does SELECT in DBD::CSV The safe thing is to just do a SELECT and deduplicate in Perl, but it would be nice to do more within the backends. Once this is in place it would be nice to start to 'plug the holes' by defining a common SQL and translations into backend-specific SQL. This can be used to define a DBD::Any that can be used to talk to many different backends. (To some extent this can be approximated by a SQL processor that supports external table data, such as Access or Pg.) Of course there are many other differences to resolve, e.g. in treatment of character sets. These issues can be dealt with in a similar way. The nice thing about such a matrix is that it will already be useful on its own, even when it's unfinished. -- Reinier Post TU Eindhoven
