Modifications to the database can be done using the following functions;
insert-records,
delete-records and update-records. The functions commit,
rollback and the macro with-transaction are used to control transactions. Although
commit or rollback may be used in isolation it is advisable to do any updates inside a with-transaction form instead. This provides consistency across different database transaction models. For example, some database systems do not provide an explicit "start-transaction" command while others do. with-transaction allows user code to ignore database-specific transaction models.
The function
insert-records creates records in a specified table. The values can be either specified directly with the argument
values
or in the argument
av-pairs
, or they can be the result of a query specified in the
query
argument. The attributes can be specified with the argument
attributes
or in the argument
av-pairs
.
If attributes is supplied then values must be a corresponding list of values for each of the listed attribute names. For example, both:
(insert-records :into [person]
:attributes '(person_id income surname occupation)
:values '(115 11000 "Johnson" "plumber"))
(insert-records :into [person]
:av-pairs `((person_id 115)
(income 11000)
(surname "Johnson")
(occupation "plumber")))
are equivalent to the following SQL:
INSERT INTO PERSON
(PERSON_ID,INCOME,SURNAME,OCCUPATION)
VALUES (115,11000,'Johnson','plumber')
If query is provided, then neither values nor av-pairs should be. In this case the attribute names in the query expression must also exist in the insertion table. For example:
(insert-records :into [person]
:query [select [id] [firstname] [surname]
:from [manager]]
:attributes '(person_id firstname surname))
To delete or alter those records in a table which match some condition, use
delete-records or
update-records.