Bounty update: Already got a very good answer from Mark. Adapted := into :, below. However, I'm still looking for similar schemes besides DBIx. I'm just interested in being compatible to anything.


I need advise on the syntax I've picked for "extended" placeholders in parameterized SQL statements. Because building some constructs (IN clauses) was bugging me, I decided on a few syntax shortcuts that automatically expand into ordinary ? placeholders.
I like them. But I want to package it up for distribution, and am asking myself if they are easily understandable.

Basically my new placeholders are ?? and :? (enumerated params) and :& and :, and :| and :: (for named placeholders) with following use cases:

-> db("  SELECT * FROM all WHERE id IN (??)  ", [$a, $b, $c, $d, $e])

The ?? expands into ?,?,?,?,?,... depending on the number of $args to my db() func. This one is pretty clear, and its syntax is already sort of standardized. Perls DBIx::Simple uses it too. So I'm pretty certain this is an acceptable idea.

-> db("  SELECT :? FROM any WHERE id>0   ",  ["title", "frog", "id"]);
// Note: not actually parameterized attr, needs cleanup regex

Admit it. I just liked the smiley. Basically this :? placeholder expands an associative $args into plain column names. It throws away any $args values in fact. It's actually useful for INSERTs in conjunction with ??, and sometimes for IN clauses. But here I'm already wondering if this new syntax is sensible, or not just a misnomer because it mixes : and ? characters. But somehow it seems to match the syntax scheme well.

-> db("  UPDATE some SET :, WHERE :& AND (:|)   ", $row, $keys, $or);

Here the mnemonic :, expands into a list of name=:name pairs separated by , commas. Whereas the :& is a column=:column list joined by ANDs. For parity I've added :|. The :& has other use cases out of UPDATE commands, though.
But my question is not about the usefulness, but if :, and :& appear to be rememberable?

 -> db("  SELECT * FROM all WHERE name IN (::)  ", $assoc);

After some though I also added :: to interpolate a :named,:value,:list very much like ?? expands to ?,?,?. Similar use cases, and sensible to have for uniformness.

Anyway, has anybody else implemented a scheme like that? Different placeholders? Or which would you recommend for simplicity? Update: I know that the PHP Oracle OCI interface can also bind array parameters, but doesn't use specific placeholders for it. And I'm looking for comparable placeholder syntaxes.

Comments

I think you put a lot of thought into this, but what about GROUP BY, HAVING, and ORDER BY clauses and subqueries? What about custom formatting, like when you need to group by date without including the time portion? What about functions that need additional config, like COALESCE or anything using the OVER clause (assuming supported)?

Written by OMG Ponies

@OMG Nah, didn't put much thought into that. I've concentrated on my most frequent use cases. And specific dynamic SQL still needs manual work. Especially expressions. But for example :? could be used for ORDER and GROUP BY clauses. And for a few constructs I already have {TOKEN} support (which in essence is SQL concatenation again).

Written by mario

looks quite similar to what i'm doing here code.google.com/p/mria/source/browse/trunk/yo/db.inc.php

Written by stereofrog

@stereofrog: Indeed. Allthough the backend target is different and I was more concerned with throwing arrays at SQL. The placeholder methodology is comparable.

Written by mario

Accepted Answer

You might want to avoid using := as a placeholder because it already has a usage in for example MySQL.

See for example this answer for a real world usage.

Written by Mark Byers
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki