On 22/12/2014 00:39, Darren Duncan wrote:
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
I tend to agree with Greg and Darren on this for all the same reasons.
If it could be done in DBI without affecting DBDs then I might have less
of a problem with it as anything wanting this feature would only have to
check DBI. There are already too many differences between DBDs and
introducing yet another one which would make applications and interfaces
even more complex. Plus, this seems to be a postgres specific issue and
escaping of ? or : would only benefit other DBDs in the few rare cases
where those are legitmate chrs in themselves.
There is already an inconsistency in placeholders as some DBDs
need/support ':name' when binding and some only support 'name'.
DBD::ODBC already has a ignore placeholders switch for when users want
to pass SQL containing a ? or : when neither is a placeholder e.g., SQL
Server procedures often contain colons.
If it was not for some poor ODBC drivers that don't support SQLNumParams
DBD::ODBC could get away with not parsing the SQL at all which would be
my preferred solution and in fact, the version I use does not parse the
SQL at all. Adding escaping would mean more work and probably taking a
copy of the scalar for modification.
However, I think Greg and Darren's arguments are strong enough on their own.
If, it ends up where the DBI spec is changed to allow escaping can we
please introduce the DBD capabilities API at the same time. I know I
started the thread some time ago and put together an initial list then
did not move it forward, but it would make application and interface
layers so much easier if they could just say 'does this DBD do X' and
maybe 'how do I make it do X'.
Martin
--
Martin J. Evans
Wetherby, UK
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).
