-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
Tim Bunce wrote:
> Wherein I attempt to summarize the requirements, the background, the options,
> the risks, and the counter-proposal ...
Thanks for taking the time to write all this out in a coherent manner.
I may once again assume the role of devil's advocate herein.
> In modern application development, however, the DBI is just another
> foundation layer, low down in the stack. Above it are modules like
> SQL::Abstract, and ORMs like DBIx::Class. Companies have large and
> growing investments in these stacks, plus the modules they've built over
> them to provide abstraction and encapsulation of business logic.
Agreed.
> For these large applications the attribute workarounds are rarely helpful.
Is that a true generalization? Or is it only because these large
applications have not bothered to support the attributes?
> Working 'high up' in an application stack that's dynamically constructing
> SQL with placeholders it should be possible to express any desired behavior.
Agreed.
> Some mechanism is needed to allow an individual character sequence that
> normally represents a placeholder, to appear in the SQL without it being
> treated as a placeholder by the driver. To "escape" it's usual meaning.
But... okay I can't find any fault with this. :)
> If escaping question mark placeholders was already defined in 2008 then
> pg_placeholder_dollaronly might not have been added to DBD::Pg.
Perhaps, perhaps not. pg_placeholder_dollaronly still allows the same SQL to
be slung around, including using it inside of Postgres itself. So it's
likely both methods would have been available.
> 1. Backslash
Seems like a good solution. A little ugly, but certainly unlikely to
clash with any future SQL-isms.
> but that seems very minor as the use of backslashes in single and double
> quotes is natural and familar for Perl developers.
True. We could recommend always using double quotes - and Perl::Critic's
inevitable nagging be damned. :)
> 2. Doubling
Ugh. No thanks.
> I presume there are no database dialects where two adjacent placeholders
> would be valid, so I think it's safe.
Nope, '??' is a perfectly legal operator in Postgres. A backslash, however,
cannot be part of an operator's name. (nor can braces).
> 3. Vendor escape clauses
Interesting. Probably overkill at this point, but certainly something
we could think about supporting.
> $sql_abstract->where({ json_field => { '{verbatim ?}' => $bar } })
I find it hard to imagine people getting excited about writing
that, and I would imagine SQL::Abstract would simply let people write
{ "\\?" => $bar } and write the long-form behind their back.
> No code will break!
Doubling will. But nobody likes that option anyway.
> +Some drivers allow you to prevent the recognition of a placeholder by
> placing a
> +single backslash character (C<\>) immediately before it. The driver will
> remove
> +the backslash character and ignore the placeholder, passing it unchanged to
> the
> +backend. If the driver supports this then L</get_info>(9000) will return
> true.
Seems a good start. I don't like the phrase "prevent recognition of a
placeholder"
but it's too late at night to propose an alternate right now.
> (I'm undecided about making the get_info(9000) return value a bit-map
Ugh, no. Drivers should escape all placeholder types it supports.
>> 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).
>
> Not true.
How will a user of SQL::Abstract know if escapes are supported or not?
Isn't the job of such frameworks to release the user from such worries,
or at least present some sort of attribute the user can rely on?
>> 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).
>
> There's no such thing as "escaped and unescaped versions of SQL".
> That's a false premise. This is a really important point: the syntax
> being proposed *is invalid SQL*.
I'm not sure of the distinction you are making here. The proposed
syntax is invalid, true, and the "original" syntax may or may not be
invalid, depending on how you look at it. Portable code that won't
know in advance if the underlying dbd supports escaping will need
to have two SQLs paths - one with escaping, and one to do it some
other way.
> Then ribasushi, a principle maintainer of SQL::Abstract and DBIx::Class,
> pointed out the fatal flaw: numbered placeholders aren't directly composable.
...
> Trying to do the same with numbered placeholders (colon or dollar) would
> require parsing and rewriting of the SQL. Clearly non-trivial and slow.
> For this reason he wouldn't accept such a patch to SQL::Abstract, and I'd
> agree with that position.
Non-trivial, but not exactly difficult either, especially considering all the
other contortions said modules are already performing. As to the slow, I
find it hard to swallow that, for no matter how much string manipulation
we are doing (and Perl is quite good at such things), the cost of connecting
to the database, running the query, and parsing the result will always
overshadow such string fiddling.
> Question marks are the international standard for placeholders.
> The DBI should have specified a way to escape them from the start.
> I'm attempting to fix that now.
Taking off the devil's advocate hat now. I can agree with this
position and the backslash solution.
> To demonstrate the relative complexity, here's a proof-of-concept patch
> for DBD::Pg to implement support for escaping question mark placeholders:
>
> https://github.com/timbunce/dbdpg/commit/54358c7a7efeeaf2666c5e28c301e47624fb9615
+1, thanks for that. I certainly cannot see any harm in adding escape support
to DBD::Pg, and then letting the upstream modules start adapting to it.
- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201412302232
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAlSjcHAACgkQvJuQZxSWSsgB0wCfXdx6B52oM4r+2CrDStrjd614
jEoAoOCrx/aJ2yPIDhdVI3JaPCQuNqrJ
=sYMa
-----END PGP SIGNATURE-----
