Batch insert

[mfp]

Support batch insert (using a tuple list) with syntax INSERT INTO foo(x, y) VALUES @foos or INSERT INTO foo VALUES @foos.

Notes

• empty tuple lists are supported
• the generated code performs textual substitution and builds the SQL in linear space/time
• the param is shown as having type Any in the XML output

The generated code looks like

  let insert_set db ~foos ~duplicate =
    let set_params stmt =
      let p = T.start_params stmt (1) in
      begin match duplicate with None -> T.set_param_null p | Some v -> T.set_param_Text p v end;
      T.finish_params p
    in
    ( match foos with [] -> T.execute db "-- empty insert" T.no_params | _ :: _ -> T.execute db ("INSERT INTO foo(id, foo) VALUES " ^ (let _sqlgg_b = Buffer.create 13 in List.iteri (fun _sqlgg_idx (id, foo) -> Buffer.add_string _sqlgg_b (if _sqlgg_idx = 0 then "(" else ", ("); Buffer.add_string _sqlgg_b (T.Types.Int.to_literal id); Buffer.add_string _sqlgg_b ", "; Buffer.add_string _sqlgg_b (T.Types.Text.to_literal foo); Buffer.add_char _sqlgg_b ')') foos; Buffer.contents _sqlgg_b) ^ "\n\
ON DUPLICATE KEY UPDATE foo = ?") set_params )

given

CREATE TABLE foo(
    id INTEGER PRIMARY KEY,
    foo TEXT NULL
);

-- @insert_set
INSERT INTO foo(id, foo) VALUES @foos
ON DUPLICATE KEY UPDATE foo = @duplicate;