In my database abstraction library I am extending SQL prepared statements syntax to hint a parser with expected literal type. I take it as a very essential improvement, my reasoning you can read here. If you want to question the idea itself, please let me know in the comments - I'd like to start another question instead of mixing it here.
The advice I want is on the syntax. I am quite ignorant of all the languages and dialects beside php and mysql, and may be there already a general purpose solution I am unaware of. If not - then I want to know your personal opinion or suggestion.
At the moment, only positional placeholders supported. Yet I was asked many times to add support for the named ones. However, I cannot decide which syntax to choose for that.
At the moment I mark a conventional question mark with single letter, i.e. ?s
- string, ?i
- int, ?n
- name (identifier) and so forth.
Yet for the named placeholder (:name
style) the only sane option to me is
i:name
or, if I decide to add more verbose modifiers, even
int:name
so, the query would look like
SELECT * FROM ident:table WHERE id IN (int:id)
The question is: do you see any drawbacks for such a syntax? Do you have a suggestion for any other style? Do you think it's readable at all?
Another question is on repeating things. At the moment I have two placeholders for the arrays - ?a
for the comma separated string list and ?u
for the ?n=?s
pairs. It bugs me a long time already, that such a syntax is superfluous, yet insufficient. What if I need a set of comma-separated identifiers? Or even a complex statement like whole VALUES clause? With some sort of repetition operator I can make SQL query building dramatically better. Here are examples of what I am thinking of:
SELECT * FROM table WHERE id IN([?i])
SELECT [?n] FROM table
INSERT INTO table VALUES [(?i,?s,?s,?s)]
UPDATE table SET [?n=?s]
As you can see, I use square brackets to designate a repetition. However, I am still not sure if I should use them. First objection is that they will collide with SQL server identifier syntax. Next is rather philosophical one - it is another syntax element and I want to introduce as less changes to the syntax as possible.
Also, this syntax will require much more complicated parsing logic, which I am trying to avoid too. So - another option is just to extend the set of types, either with distinct placeholders like ?k
for the list of numbers, ?o
for the list of identifiers, of with adding a single=letter modifier to the existing type like ?as
(or ?astr
or astr:name
) is an array of strings, ?an
and the like.
There is another option - to detect repetitions automatically. If we have a scalar - format it as a single value. If it's array coming - format as a comma-separated list. However, many people find this approach unacceptable, as it can lead to unpredictable results and erroneous (however still safe) query. I am still in doubts if I may choose this way.