[Dbix-class] Escaping placeholders, take 2

[Darren Duncan]

Thank you for this post Tim, it seemed to lay out the issues well and make a lot 
of things clear.

I'm now inclined to support your backslash-escape proposal at the DBI driver 
level in principle.

(I also agree that the doubling method is nasty.)

Or alternately I suggest variation on that proposal that brings in shades of the 
vendor escape clauses.

I suggest a variant where instead of a single backslash character indicating an 
escape, we have some multi-character thing to indicate it, ideally involving 
delimiters so it is more clear on how far the effect of the new escape feature 
is supposed to go.

For example, using \{?} rather than \? or \{:foo} rather than \:foo.

One benefit of that is if you have some SQL that contains ? or : numerous times, 
you only need to surround the whole fragment with \{} and not individually 
escape each occurrence, making for neater code.

As to dealing with say literal { or } in the SQL as I could see reasonably 
happening, the quoting mechanism could be made more generic like Perl's "q"/etc, 
so for example it would take the form \X...X where whatever character appears 
after the \ is what is matched, and it could be a multiplicity if necessary 
within reason, eg \{{{...}}} would just work.  For that matter, heredocs or the 
"quoted printable" feature you can see in email messages or such, eg you have 
\"foo"...foo or some such.

I'm speaking in principle here, I'm not proposing a specific feature set, but 
both "q" as well as Perl 6's related quoting mechanism is useful for guidance, 
and I think something involving delimiters is best.

But if some of that sounds unduly complicated, I have a better idea.

I propose that the DBI include an API for users to tell the driver what possible 
escape delimiters they are using.  For example, doable at least at a statement 
level (and optionally on a connection/etc level for defaulting if that makes sense).

The API could involve an 'attr' given when preparing a SQL statement or other 
appropriate places.

   placeholder_escape_delimiters => [ '\{','}' ]
   placeholder_escape_delimiters => [ '\{','}','\[',']' ]
   placeholder_escape_delimiters => [ '\{{{','}}}' ]
   placeholder_escape_delimiters => [ '{{{','}}}' ]

In this way, the backslash is no longer special, or necessary, though I 
anticipate it would often still be used for the mnemonics.

Rather, when the driver is parsing the SQL for placeholders, if it encounters 
any left delimiter strings, if will leave the following SQL unaltered until it 
encounters the corresponding right delimiter string, and then it looks for 
placeholders again.

(As to numbered placeholders, which are effectively a special case of named 
placeholders, not being directly composable in SQL::Abstract, I see that as 
being a problem itself.  It would be a great help to developers in principle if 
the native way for working with parameters was named rather than positionally. 
However, that is really a separate matter to deal with and I think it is a good 
idea for Tim's proposal in some form to happen regardless of dealing with this 
separate matter.)

-- Darren Duncan