Data Manipulation

Aside from information retrieval tasks, queries may be intended to change data in some way:

add new records;
update existing records (either partially or fully);
delete existing records.

Such queries are often refered to as data manipulation queries.

Insert, Update & Delete

Circumflex ORM employs Active Record design pattern. Each Record has following data manipulation methods which correspond to their SQL analogues:

INSERT_!(fields: Field[_, R]*) — executes an SQL INSERT statement for the record, that is, persists that record into database table. You can optionally specify fields which will appear in the statement; if no fields specified, then only non-empty fields will be used (they will be populated with NULLs or default values by database).
INSERT(fields: Field[_, R]*) — same as INSERT_!, but runs record validation before actual execution;
UDPATE_!(fields: Field[_, R]*) — executes an SQL UPDATE statement for the record, that is, updates all record's fields (or only specified fields, if any). The record is being looked up by it's id, so this method does not make any sense with transient records.
UPDATE(fields: Field[_, R]*) — same as UPDATE_!, but runs record validation before actual execution;
DELETE_!() — executes an SQL DELETE statement for the record, that is, removes that record from database. The record is being looked up by it's id, so this method does not make any sense with transient records.

Save

Circumflex ORM provides higher abstraction for persisting records — the save_! method. It's algorithm is trivial:

if record is persistent (id is not empty), it is updated using the UPDATE_! method;
otherwise the INSERT_! method is called, which causes database to persist the record.

There is also a handy save() method, which runs record validation and then delegates to save_!().

Note that in order to use save and save_! methods your records should support identifier generation.

Bulk Queries

Circumflex ORM provides support for the following bulk data manipulation queries:

INSERT ... SELECT — inserts the result set of specified SQLQuery into specified Relation;
UPDATE — updates certain rows in specified Relation;
DELETE — removes certain rows from specified Relation.

All data manipulation queries derive from the DMLQuery class. It defines a single method for execution, execute(), which executes corresponding statement and returns the number of affected rows.

Also note that each execution of any data manipulation query evicts all records from transaction-scoped cache.

Insert-Select

The InsertSelect query has following syntax:

// prepare query
val q = (Country AS "co").map(co => INSERT_INTO (co) SELECT ...)
// execute it
q.execute

Note that projections of specified SQLQuery must match the columns of the Relation.

Update & Delete

SQL databases support UPDATE and DELETE statements for bulk operations. Circumflex ORM provides equivalent abstractions for these operations, Update and Delete respectively.

The Update query allows you to use DSL for updating fields of multiple records at a time:

(Country AS "co").map(co =>
  UPDATE (co) SET (co.name, "United Kingdom") SET (co.code, "uk") execute)

The Delete query allows you to delete multiple records from a single relation:

(Country AS "co").map(co => DELETE (co) execute)

An optional WHERE clause specifies predicate for searched update or delete:

UPDATE (co) SET (co.name, "United Kingdom") WHERE (co.code LIKE 'uk')
DELETE (co) WHERE (co.code LIKE 'uk')

Many database vendors also allow USING clause in UPDATE and DELETE statements. Circumflex ORM does not support this feature yet.

← Querying

Circumflex ORM Documentation