Repeating block

[SOF3]

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:

-- 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:

foreach_command := foreach_command_start | foreach_command_end

foreach_command_start := "+" WHITESPACE* "(" WHITESPACE* (label WHITESPACE+)? argument_list
label := [A-Za-z_][A-Za-z0-9_]*
argument_list := ":" argument_identifier WHITESPACE* range_specifier? (WHITESPACE+ ":" argument_identifier WHITESPACE* range_specifier?)*
argument_identifier := [A-Za-z_][A-Za-z0-9_]*
range_specifier := WHITESPACE* "[" WHITESPACE* start_index? WHITESPACE* ".." WHITESPACE* end_index? WHITESPACE* "]"
start_index := -?[0-9]+
end_index := -?[0-9]+

foreach_command_end := "+" WHITESPACE* ")" WHITESPACE* delimiter
delimiter := [^ \t]*

Examples for each of the four use cases above:

-- #{ in_lists
-- #    :id list:string
SELECT name FROM players WHERE id IN (
-- #    +( :id
:id
-- #    +) ,
);
-- #}

-- #{ multiple_insert_values
-- #    :name string
-- #    :ip list:string
-- #    :time list:string
INSERT INTO player_ips (name, ip, time) VALUES
-- #    +( :ip :time
(:name, :ip, :time)
-- #    +) ,
;
-- #}

-- #{ multiple_update_cases
-- #    :name list:string
-- #    :money list:int
UPDATE player_money
SET money = CASE
-- #    +( :name :money
  WHEN name = :name THEN :money
-- #    +)
END CASE
WHERE name IN ('SOFe', 'PotterHarry');
-- #}
-- #{ multiple_join_tables
-- #    :name list:string
-- #    :value list:optional:string
SELECT id FROM labels t0
-- #    +( :name[1..]
  INNER JOIN labels t$$name$$ USING (id)
-- #    +)
WHERE
-- #    +( nv_pairs :name :value
  t##nv_pairs##.name = :name
-- #        +( :value
    AND t##nv_pairs##.value
-- #        +)
-- #    +) AND
  INNER JOIN labels t2 USING (id)
WHERE
  t0.name = 'name' AND t0.value = 'SOFe'
  AND t1.name = 'type' AND t1.value = 'cash'
  AND t2.name = 'currency' AND t2.value = 'dollar';
-- #}

Zipping parameters

In the multiple_update_cases example, +( :name :money zips the lists name and money together, equivalent to

if(count($nameList) !== count($moneyList)) throw new Exception;
for($i = 0; $i < count($nameList); $i++) {
    $name = $nameList[$i];
    $money = $moneyList[$i];
    write_body($name, $money);
}

Ranges

Four types of ranges can be specified.

• Bounded ranges
  • [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].