2011/10/4 Reinier Post <r...@win.tue.nl>: > 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
How old are you're DBD::CSV / SQL::Statement versions? Of course, SELECT DISTINCT works well in DBD::CSV. /Jens > 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 >
