canonical / sqlair

Friendly type mapping for SQL databases
Apache License 2.0
17 stars 9 forks source link

Add support for `INSERT ... VALUES ...` input statements #36

Closed Aflynn50 closed 1 year ago

Aflynn50 commented 1 year ago

This PR extends the syntax of the sqlair DSL to support bulk INSERT SQL DML statements. This will allow the user to write statments such as INSERT INTO person (name, id) VALUES ($Person.*), or INSERT INTO person (*) VALUES ($Person.*). This will allow for a more concise syntax when inserting multiple fields of structs.

We take advantage of the go/sql libraries support of named parameters to ensure the order of the inputs can be kept consistent in the complete stage. We name the parameters with their type and tag name. When we extract the parameter values from the structs at the complete stage we can use this information to match each value to to the corresponding input position.

We only parse the column and value information around the VALUES keyword, the INSERT INTO can be ignored. Specifically, this transforms the query as follows:

(*) VALUES ($P.*)                            => (col1, col2, col3) VALUES (@Pcol1, @Pcol2, @Pcol3)
(col1, col2) VALUES ($P.*)                           => (col1, col2) VALUES (@Pcol1, @Pcol2)
(col1) VALUES ($P.col1)                                      => (col1) VALUES (@Pcol1)
(mycol1, mycol2, mycol3) VALUES  ($P.col1, $A.col1, $M.col1) => (mycol1, mycol2, mycol3) VALUES (@Pcol1, @Acol1, @Mcol1)

Note that the final two are of the same form but the last one uses multiple types and renames the columns.

The parsing resembles that of output expressions. Where they use the keyword AS here we use the keyword VALUES.