It is common to need to generate SQL queries dynamically. This matters when there is a dynamic number of arguments. There are four common use patterns:
-- IN lists
SELECT name FROM players WHERE id IN (:id1, :id2, ...);
-- Multiple INSERT values
INSERT INTO player_ips (name, ip, time) VALUES
('SOFe', '127.0.0.1', '2021-12-11T14:37:15'),
('SOFe', '::', '2021-12-11T14:37:16');
-- Multiple UPDATE cases
UPDATE player_money
SET money = CASE
WHEN name = 'SOFe' THEN 10
WHEN name = 'PotterHarry' THEN 8
END CASE
WHERE name IN ('SOFe', 'PotterHarry');
-- Multiple JOIN tables
SELECT id FROM labels t0
INNER JOIN labels t1 USING (id)
INNER JOIN labels t2 USING (id)
WHERE
t0.name = 'name' AND t0.value = 'SOFe'
AND t1.name = 'type'
AND t2.name = 'currency' AND t2.value = 'dollar';
Current workarounds
Currently libasynql provides two hacks.
The first hack is the list:T type, which accepts arguments as a list of scalar values, and dynamically generates (?, ?, ?, ...) depending on the number of arguments in the list. This only solves the first case, but does not help with the 3 other cases, whilst complicating the internal query formatter.
The second hack is executeXxxRaw, which accepts a raw query string and argument list. However, it suffers from several problems:
The methods are not documented at all. It is unclear how the argument placeholders should look like.
Argument placeholders are actually dialect-dependent, which defeats the point of libasynql.
@matcracker how did you even manage to make it work with BedcoreProtect?
Proposed solution
Inspired by the Rust decl-macro syntax, I would like to introduce the + ("foreach") PSF command for repetitions. The formal BNF definition is as follows:
[1..3] is equivalent to array_slice($list, 1, 3), which only repeats for $list[1] and $list[2]. Note that the right bound is exclusive.
Negative bounds are also allowed. See array_slice for the precise description.
Left-bounded ranges
[1..] is equivalent to array_slice($list, 1), which simply skips the first item.
Negative bounds are also allowed.
Right-bounded ranges
[..-1] is equivalent to [0..-1].
Unbounded ranges
[..] includes the full list. This is actually useless, because it is equivalent to not writing it. This syntax is reserved for possible syntax enhancements in the future.
##label## and $$label:argument$$
##label## is a special symbol that gets replaced with the iteration order, starting from 0, regardless of range specifiers. It is replaced blindly regardless of context, so it can be used inside other identifiers, like the t## above. For unlabelled loops, use three hashes ###.
$$label:argument$$ is similar to ##label##, but it takes the range index of a specific argument. It is also replaced blindly regardless of context. Only $$argument$$ is required if the loop is unlabelled, in which case the innermost loop of:argumentis used for the index (this should cause an error if the innermost loop of:argument` is labelled).
Nesting
Foreach commands can be nested, for different parameters or for the same parameter. This means lists of lists are allowed.
optional:
I also propose the optional: type modifier, which is similar to list:, but transforms a nullable value to a list through fn($x) => $x === null ? [] : [$x].
The problem
It is common to need to generate SQL queries dynamically. This matters when there is a dynamic number of arguments. There are four common use patterns:
Current workarounds
Currently libasynql provides two hacks.
The first hack is the
list:T
type, which accepts arguments as a list of scalar values, and dynamically generates(?, ?, ?, ...)
depending on the number of arguments in the list. This only solves the first case, but does not help with the 3 other cases, whilst complicating the internal query formatter.The second hack is
executeXxxRaw
, which accepts a raw query string and argument list. However, it suffers from several problems:Proposed solution
Inspired by the Rust decl-macro syntax, I would like to introduce the
+
("foreach") PSF command for repetitions. The formal BNF definition is as follows:Examples for each of the four use cases above:
Zipping parameters
In the
multiple_update_cases
example,+( :name :money
zips the listsname
andmoney
together, equivalent toRanges
Four types of ranges can be specified.
[1..3]
is equivalent toarray_slice($list, 1, 3)
, which only repeats for$list[1]
and$list[2]
. Note that the right bound is exclusive.array_slice
for the precise description.[1..]
is equivalent toarray_slice($list, 1)
, which simply skips the first item.[..-1]
is equivalent to[0..-1]
.[..]
includes the full list. This is actually useless, because it is equivalent to not writing it. This syntax is reserved for possible syntax enhancements in the future.##label##
and$$label:argument$$
##label##
is a special symbol that gets replaced with the iteration order, starting from 0, regardless of range specifiers. It is replaced blindly regardless of context, so it can be used inside other identifiers, like thet##
above. For unlabelled loops, use three hashes###
.$$label:argument$$
is similar to##label##
, but it takes the range index of a specific argument. It is also replaced blindly regardless of context. Only$$argument$$ is required if the loop is unlabelled, in which case the innermost loop of
:argumentis used for the index (this should cause an error if the innermost loop of
:argument` is labelled).Nesting
Foreach commands can be nested, for different parameters or for the same parameter. This means lists of lists are allowed.
optional:
I also propose the
optional:
type modifier, which is similar tolist:
, but transforms a nullable value to a list throughfn($x) => $x === null ? [] : [$x]
.