On Wed, Sep 18, 2019 at 01:10:29PM +0300, Lassi Kortela wrote:
> Also, it's easier to figure out how to replace "{cad}" in "abra{cad}abra"
> than how to replace "$cad" in "abra$cadabra". No ambiguity about what to do
> when the placeholder is in the middle of a bigger word. We just have to ban
> braces in the placeholder name itself, so '|{foo}| and '|{foo| and '|foo}|
> are not valid placeholder symbols.

Possible, but not required, I'd say.

> > So if we had
> >
> >       (sql-execute
> >        "INSERT INTO foo VALUES ({myval1}, '{myval2}'::text[])"
> >        '((myval1 . 1)))
> >
> > this would simply insert a tuple of 1 and the array with the single
> > string value 'myval2' into the table, right?  Because myval2 is not
> > supplied, it gets converted to
> >
> >     "INSERT INTO foo VALUES ($1, '{myval2}'::text[])".
> >
> > or in MySQL:
> >
> >     "INSERT INTO foo VALUES (?, '{myval2}'::text[])".
>
> Yeah. (Added the missing closing ' quotes that you probably intended.)

Thanks :)

> It does look counterintuitive that {myval2} is not expanded, but I don't see
> a way to avoid situations like this without a baroque SQL parser.

Same here.

> > I could see some problems with this if the user would incorrectly quote
> > their expressions.  In the case of
> >
> >       (sql-execute
> >        "INSERT INTO foo VALUES ('{myval1}', '{myval2}'::text[])"
> >        '((myval1 . "hi there")))
> >
> > it would get translated into the following, because we're doing "dumb"
> > replacement:
> >
> >     "INSERT INTO foo VALUES ('$1', '{myval2}'::text[])"
> >
> > or in MySQL:
> >
> >     "INSERT INTO foo VALUES ('?', '{myval2}'::text[])"
> >
> > With a positional parameter of 'hi there', which will hopefully give
> > an error message.
>
> Good point. So in this case, the DB engine would not parse any parameter
> names from the SQL query, but we would send it one parameter anyway. I.e.
> the same thing as calling a procedure with too many arguments in a
> programming language.

Yeah.  There might be engines that don't care about this, for instance
it is possible in Postgres to do

    (query "SELECT $2, $3" 1 2 3)

It won't give an error about $1 not being used, but it does give an error
about not being able to figure out the type of parameter 1.  I don't know
if it will _always_ do this, though.

> Again, how to avoid this without a baroque parser? There is probably no way.

Indeed.

> One more thing that bothers me is that in (sql-execute sql params), the sql
> string and params list can come from anywhere. This is similar to the
> classic problems with printf() in C, and there evolved a convention of
> always using a constant string for the formatting template.
>
> In Scheme, this might be a good place for a macro so we can guarantee that
> the query is a literal string, we can parse the placeholders from the string
> template at macroexpansion time, can parse the parameter names (which must
> be symbols known at macroexpansion time), and can check that the set of
> placeholders exactly matches the set of symbols.

I would definitely vote against this idea.  Consider the basic case of
wanting to dynamically build a query (for example, WHERE filters passed
in from somewhere).  In my personal experience, it is VERY rare to have
completely static strings where only the parameters are dynamic.  Maybe
only in extremely simple statements, but that's a case I don't think is
worth optimising for.

> There could be a low-level (sql-execute-raw ...) that is a procedure instead
> of a macro.

Sounds ugly to me.

Cheers,
Peter