There's three different kind of actions supported by
drop. All actions take an
optional first argument which can either be a connection key, a
connection spec or a schema. Actions are directly executed against
the given or default connection.
create action can be use to insert schemas, tables or
(create (table :users (integer :id :primary-key) (varchar :name 100)))
alter action only purpose is the modify a table
(alter (table :users (check :name (> (length :name) 1))))
drop action can be use to remove schemas, tables or
(drop (table :users))
There's a complete set of schema elements which are an abstract representation of SQL objects and their constituent. They've been designed to be easily composed so that you can write you're own helpers to create schemas of arbitrary complexity.
The first one is the
Schema element, which can be used to
create or drop schemas. It can also be used as the first argument of
an action, in which case the schema elements given will produce
qualified identifiers once compiled.
[schema-name options? & elements]
It can take an optional map of options which for now only
:db-spec key. You can specify a number of schema
elements that will be created if that schema is passed to the create
Table element is a macro that compose its given
elements. It can take columns, constraints or indexes in any
([name & elements]) Macro Constructs an abstract table definition containing the given elements. Takes an arbitrary number of table elements.
All table elements are functions taking a table element as first argument. You can write your own helpers to construct custom table macro like in the following example.
(defn surrogate-key [table] (integer table :id :auto-inc :primary-key)) (defn datetime-tracked [table] (-> table (timestamp :updated_on) (timestamp :created_on (default (now))))) (defmacro tbl [name & elements] `(-> (table ~name (surrogate-key) (datetime-tracked)) ~@elements))
Indexes may be created outside a table definition. They need at least a table name and a sequence of column names.
(index :users [:name])
You can also provide an index name and some options. For now
:unique option is recognized.
(index :users :users_unique_name [:name] :unique)
Take note that at this point, you must provide an index name
when you're calling
index with options.
Table elements are meant to be used inside the
macro. There are three type of table elements:
Columns are contructed using the
column function or the
typed column function that we'll talked about later. This function
take the table it will be attached to, a column name, an optional
data-type definition and a set of options.
(column *table* :name (data-type :varchar 100) :unique :not-null)
Here's a list of available options:
:uniquewhich construct an unique constraint on that column
:primary-keywhich make the current column the primary key
[:refer tname & options]which add a foreign key constraint to the specified table. The options are the same as the
foreign-keyfunction with the expection that you can specify only one parent column.
:not-nullprevents this column from being null
:auto-inc(for integers types) which makes it auto-populated with incremented integers
[:encoding enc](for character types) determines which encoding to use if supported by the database. Also see the natianal character types.
[:collate type](for character types) determines how equality is handled
:time-zone(for time types) determines if the type includes a time-zone
There's three types of constraint:
CheckConstraint. Each can be
construction using specific helpers with the exception of
UniqueConstraint which have two.
(unique *table* [:name])
(primary-key *table* [:id])
Foreign key constraint can be defined using the
[table name? columns parent-table parent-columns? match? & triggered-actions]
parent-columns aren't specified, the
columns will be used.
match optional argument can be one of
:simple, but note that this isn't supported by
You can specify
triggered-actions with pairs of keyword,
the first of the pairs must be one of
:on-update, while the second one can be one of
actions keywords are directly translated to SQL keywords, so you
can specify custom ones if the database you're using provide more.
You can optionally specify the constraint name just after the table for unique and foreign-key contraints.
Indexes can also be defined inside a table definition in the same way as a constraint. See the `Index` schema element documentation above.
Columns can be defined using the data-type name as a function. This enable more compact table definition.
Simple Typed Column
Simple typed columns don't take any arguments.
double-precision typed column is aliased as
Numeric columns take an optional precision and scale arguments.
[table column-name & [precision scale & options]]
Optional Precision Column
Optional precision columns take an optional precision argument.
[table column-name & [precision & options]]
Optional Length Column
Optional length columns take an optional length argument.
[table column-name & [length & options]]
nclob typed columns are aliased as
Length Bounded Column
Length bounded columns must have a length argument.
[table column-name length & options]