Data Manipulation Language (DML)

Delightql supports SQL’s tree mutation operations through four destructively sigilized pipe targets:

• update!(T(*))(*) – modify existing rows
• insert!(T(*))(*) – add new rows
• delete!(T(*))(*) – remove rows
• keep!(T(*))(*) – keep rows, deleting others

The T(*) is the mutation target – a functor expression identifying which relation to mutate.

The !! Marker

For update!, delete!, and keep!, the source relation is also the mutation target – the rows being sourced are the rows being mutated. Mark the source with !! to make this explicit:

hr.employee!!(*)                   // !! = "these rows will be mutated"
  , Department = "Executive"
  |> delete!(hr.employee(*))(*)

The !! marker is required when the source is the mutation target. The compiler verifies that the !!-marked relation matches the terminal target.

For insert!, the source rows are read-only input – even when the source table happens to be the same as the target. Do not use !! on insert sources:

employees(*)                       // no !! -- these rows are read-only
  , department = "Engineering"
  |> (id + 10 as id, name, department, age, salary)
  |> insert!(employees(*))(*)

Update

To update a table, pipe a matching schema into update! pseudo-predicate. The name of the table to be updated must be the higher-order parameter – this is the mutation target. Note that this implies that the DML pseudo-predicate accepts two higher-order parameters: the contents of the query before the pipe and the table targeted. The mutation target must be the source of the data as well.

hr.employee!!(*)
  , Department = "Executive"
  |> $$("-------" as ssn)
  |> update!(hr.employee(*))(*)

UPDATE hr.employee
SET ssn = '-------'
WHERE Department = 'Executive';

Delete

To delete from a table, use predication to select the rows that should be removed. The mutation target must also be the source table and the schemas must match.

hr.employee!!(*)
  , Department = "Executive"
  |> delete!(hr.employee(*))(*)

DELETE FROM hr.employee
WHERE Department = 'Executive';

Without filters, all rows are deleted:

hr.employee!!(*) |> delete!(hr.employee(*))(*)

DELETE FROM hr.employee;

For convenience, you can use inverted predication and declare which tuples you wish to keep.This is a simple enough rewrite and will be compiled into a delete.

hr.employee!!(*)
  , Department = "Engineering"
  |> keep!(hr.employee(*))(*)

Insert

Use the insert! pseudo-predicate to insert rows. The relation entering the insert! pipe must contain a subset of the schema of the mutation target. Any extra or erroneously named columns are an error.

_(LastName, FirstName, age @ "eklund", "daniel", 20)
  |> insert!(employee(*))(*)

INSERT INTO hr.employee (LastName, FirstName, age)
VALUES ('eklund', 'daniel', 20);

You may union tables and prediacte their tuples to provide input tuples:

hr.employee(*)
  |;| new_hires(*)
  |;| transfers(, effective_date = today:())
  |> insert!(employee(*))(*)

candidates(*),
  score > 90 |> (name, Department, start_date)
  |> insert!(hr.employee(*))(*)

INSERT INTO hr.employee (name, Department, start_date)
SELECT name, Department, start_date
FROM candidates
WHERE score > 90;