I agree with Greg's counter-proposal, from which I derive my own words here.
1. I propose that there be no modification to the DBI spec related to new
escaping whether optional or not, so leave things the way they are here, SQL
continues to be interpreted the way it long has by default.
2. When users want to use operators in PostgreSQL that contain literal ? in
their names, then they enable DBD::Pg's pg_placeholder_dollaronly so that ? are
no longer treated as placeholders. Likewise, pg_placeholder_nocolons can be
enabled when they don't want literal : to indicate a placeholder either. Users
would either do this directly if they're using DBI/DBD::Pg directly, or
indirectly using their over-top framework of choice. When users aren't using
the ? operators et al, they can leave things alone which will work as normal.
3. SQL::Abstract et al, those tools external to DBI/DBDs, are the correct and
proper places to modify where users of said want to use the operators with ?
names and such. These tools already have special knowledge of individual DBMS
systems to work with them effectively, and the ? operators is just one more of
those things. The users of said tools may have to flip a configuration switch
possibly so $1 etc are used behind the scenes, if necessary, but that's okay
because the use of ? operators only happens when the users choose to make a
change to use them anyway.
In summary, now is not the time or place to be introducing backslashing doubled
or otherwise in DBI such as discussed, that's a poor solution and its better to
save such risky/etc changes for when there's a more compelling case for them.
I should also mention I feel it is perfectly reasonable for each DBMS to have
operators composed of any characters they want where doing so makes sense within
the context of the SQL/etc itself. See also that Perl itself has both ? and :
and etc as operator names, Perl 6 even more so, and I don't see anyone thinking
that's a bad idea. So I have no problem with PostgreSQL having ? in operator
names such as it did. Its not like the SQL standard reserves ? or whatever for
prepared statement parameters, that's defined to be implementation dependent I
believe (WD 9075-2:200w(E) 20.6 <prepare statement>).
-- Darren Duncan
On 2014-12-21 7:17 AM, Greg Sabino Mullane (the tenth man) wrote:
Tim Bunce wrote:
For code not using DBIx::Class the pg_placeholder_dollaronly attribute
might work, see https://metacpan.org/pod/DBD::Pg#Placeholders
Yes, this is the current canonical solution. It's a real shame
that ? was used as an operator, but that horse has left the barn.
For code using DBIx::Class the problem is more tricky. I'm pretty sure
that SQL::Abstract and thus DBIx::Class only support question mark
placeholders. That means it probably impossible to use expressions
containing a question mark operator with SQL::Abstract/DBIx::Class.
(Though I'd be delighted to be proven wrong.)
So I think the DBI spec for placeholders needs to be extended to allow a
way to 'escape' a question mark that the driver would otherwise treat as
a placeholder.
The obvious and natural approach would be to use a backslash before a
question mark. The backslash would be removed by the driver before the
statement is passed to the backend.
I'm going to play devil's advocate a bit here. There are some problems with
this approach. First, it will require that the user know if the underlying
DBD supports backslashes. Which likely means that SQL::Abstract and/or
DBIx::Class will need to know as well. (Unless they expose the DBD directly
to the user, which ruins the point a bit). Since we will thus need to patch
those other modules, so why not fix them to do the right thing? (see below
for a counter proposal).
Another problem is that we have now extended the SQL syntax for our own
purposes. While one could argue that placeholders already do so, their
current use is consistent, widespread (e.g. not just DBI), and in part
used by the underlying RDBMSs themselves (e.g. Postgres uses dollar-number
placeholders). So we will have both escaped and unescaped versions of SQL
floating around, subject to the whims of whether or not your particular
DBD supports it (and in which version). All of which seems like an awful
lot of work to "fix" SQL::Abstract. Why bother patching every DBD in
existence when we can simply patch SQL::Abstract?
Which leads to my counter-proposal: have SQL::Abstract accept dollar-number
placeholders. It can pass pg_placeholder_dollaronly down the stack as
needed. This neatly puts the onus back onto the frameworks, rather than
having the DBDs selectively remove backslashes before passing to the
RDBMS (ick). DBIx::Class and friends could even map dollar signs back to
a format supported by the underlying DBDs, if they don't support dollar
signs (that is one of their grand purposes after all - abstracting out
details and taking care of things in the background).
