Re: Reusing SQL data-related logic, replacing weird modules and improving Class::DBI

> Suppose I was writing a system that would allow me to write
> portable queries.

Again, I'd urge you to build on one of the existing efforts in this
area.

> So would it be possible to add such functionality to
> DBIx::SQLEngine -- the ability to analyze the parts of an existing
> query and identify each one and potentially modify it?

Yes -- in fact, it's already doing some of the basics of this. For
example, the DBIx::SQLEngine interface already supports portability
adaptors for joins, limits, unions, and other non-standard features.

Each query is built up from data structures into a SQL string and
parameters by a Driver object with automatic subclassing based on DBD
type. Applications can assemble, review, and modify a query as a hash
of parameters, and then send it off to the driver to get turned into a
locally ideosyncratic SQL string.

(Of course, applications can define their own SQL in some other way and
pass it through without any modification; the translation process
typically only applies when you've asked the driver to generate queries
from parameters.)

I'd be entirely open to a DBIx::SQLEngine patch that automatically
rewrote "CONCAT(col1, col2)" to "col1 || col2" when buidling queries
for Postgres. (I think this could be done in the sql_select and
sql_where methods of the DBIx::SQLEngine::Driver::Pg class.)

Generalizing from that, it should be reasonably straightforward to
build a table of various common functions with indications of whether
they're supported and what syntax is required on various platforms.

Ultimately, some functions may not be supported on some drivers --
you'll never be able to make DBD::CSV act like Postgres and Oracle --
but you can provide a capability interface that reports whether or not
a given feature is available, and provide a helpful error message if an
application tries to use a feature not supported on a given platform.

Patches welcome...

On the other hand, if you find DBIx::SQLEngine's flexible options
procesing insufficiently rigorous, try taking a look at Rosetta and
SQL::Routine. Like your proposed syntax, they're focused on building
and maintaining trees representing the structure of various queries...
I think the result is a little too unwieldy for my purposes, but you
might find it useful.

-Simon

.

Relevant Pages

  • Re: front end and back end
    ... There's no benefit to be gained by putting the queries in the back-end. ... communicate with SQL string and result set to reduce network traffic? ... Does it mean that query should store on the backend, ...
    (microsoft.public.access.formscoding)
  • Re: jTDS driver
    ... > But I have been suggested another driver that is supposely faster. ... if you have serious problems with your queries (missing indexes, ... processing *on* the server. ...
    (comp.lang.java.programmer)
  • Re: Can drivers lie?
    ... Queries to the driver: ... so it appears I am running OpenGL 1.3. ... A card might be 99% OpenGL 2.0 compliant and support ...
    (comp.graphics.api.opengl)
  • RE: getParameterMetaData bug?
    ... I'm getting stmt.getParameterMetaDataexceptions with simple queries like ... Query: SELECT program FROM ... Is your driver supposed to work with these simpler queries? ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: joining two queries to product one document
    ... Post the details of the Tables that involve in the 2 source Queries, ... Strings of these 2 Queries and the SQL String of the final / combine Query. ... >>>> insert the various reports as subreports into one big ...
    (microsoft.public.access.queries)
Loading