Schema definition

The schema definition XML outline:

<?xml version="1.0"?>
<schema package="org.example" class="DatabaseSchema">
        <tables/>
        <views/>
</schema>

Schema configuration

The schema element has the following attributes:

package (required): the package of the generated schema definition class.
class (required): the class name (excluding the package name) of the generated class.
revision (optional): the schema revision number.
table-prefix (optional): the table prefix. All table names in the underlying SQL database will be prefixed with this string. Particularly useful if applications share a database. This value may be overridden by specifying a runtime parameter.

Table definitions

The tables section contains any number of table definitions. Each table definition corresponds to a SQL table. A simple table definition looks like this:

<table name="person">
        <column name="id" type="auto" null="false"/>
        <column name="name" type="string" null="false"/>
        <column name="birthdate" type="date" null="false"/>
        <column name="length" type="int" null="false"/>
        <key type="primary">
                <column ref="id"/>
        </key>
</table>

Columns

A column definition has the following attributes.

name (required): the name of the column.
type (required): the column type; may be one of:
- auto
- boolean
- int
- long
- float
- double
- string
- binary
- password
- date
size (optional): the column size, whose interpretation depends on the column type.
charset (optional): the character encoding (only valid for columns of type string).
case-sensitive (optional): case sensitivity of the column (only valid for columns of type string). Defaults to true.
null (optional): indicates whether or not the column value may be null. Defaults to true.

A column reference has just a single ref attribute, whose value is the name of a previously defined column.

Keys

Column keys declare which columns are indexed.

As we have seen before, the person table has a key for the id column:

<key type="primary">
        <column ref="id"/>
</key>

This is a primary key, which means that the primary identification of a person is done by means of the id column. The effect is that the id column will be indexed (for fast lookup), and that no two persons can have the same id.

Say we want to index also the name column. Since two persons can have the same name, we use the index key type:

<key type="index">
        <column ref="name"/>
</key>

If we wanted to enforce the constraint that no two persons can have the same name (it can be confusing, no?), we would have used the unique key type.

<key type="unique">
        <column ref="name"/>
</key>

In some cases a key may span multiple columns. For example, if we want to enforce that no two persons born on the same day can have the same name:

<key type="unique">
        <column ref="name"/>
        <column ref="birthdate"/>
</key>

References

Say we have the person table as declared before, and we want to extend the database with a friendship table, which contains friendship relations between persons.

<table name="friendship">
        <column name="person_id" type="long" null="false"/>
        <column name="friend_id" type="long" null="false"/>
        <key type="primary">
                <column ref="person_id"/>
                <column ref="person_id"/>
        </key>
        <reference table="person" name="to_person">
                <column ref="person_id" foreign="id"/>
        </reference>
        <reference table="person" name="to_friend">
                <column ref="friend_id" foreign="id"/>
        </reference>
</table>

The table has two columns:

person_id, which refers to the id of the person who has a friend, and
friend_id, which refers to the id of the person who is the friend.

Both of the columns refer to the id column of a row in the person table. (And probably to a different row in the person table, except if a person is his/her own friend.) That means there are two references, one to the person and one to his/her friend.

Views

The views element may contain any number of views. The following is an example of a view of the person table.

<view name="person">
        <table ref="person">
                <column ref="id"/>
                <column ref="name"/>
                <column ref="birthdate"/>
                <column ref="length"/>
        </table>
</view>

The view refers to the table and each of its columns. A column reference may have the following attributes.

ref (required): the name of the column.
getter (optional): the name of the getter method of a Java bean which returns the value of this column.
default (optional): the default value of this column.

Joins

<view name="friends">
        <table ref="friendship">
                <column ref="person_id"/>
                <table ref="person" key="to_friend">
                        <column ref="id"/>
                        <column ref="name"/>
                        <column ref="birthdate"/>
                        <column ref="length"/>
                </table>
        </table>
</view>

Inverse joins

<view name="friends">
        <table ref="person">
                <column ref="id"/>
                <column ref="name"/>
                <column ref="birthdate"/>
                <column ref="length"/>
                <table ref="friendship" back-key="to_friend">
                        <column ref="person_id"/>
                </table>
        </table>
</view>

Multi-column joins

<view name="friends">
        <table ref="person">
                <column ref="id"/>
                <column ref="name"/>
                <column ref="birthdate"/>
                <column ref="length"/>
                <table ref="friendship">
                        <back-key>to_friend</back-key>
                        <column ref="person_id"/>
                </table>
        </table>
</view>

Join types

The join type is selected by the join attribute, as follows:

<view name="friends">
        <table ref="friendship">
                <column ref="person_id"/>
                <table ref="person" join="inner" key="to_friend">
                        <column ref="id"/>
                        <column ref="name"/>
                        <column ref="birthdate"/>
                        <column ref="length"/>
                </table>
        </table>
</view>

The following join types are supported:

inner (default)
left
right
full

Keys

Each view has a key. A view's key is the set of columns whose values identify its etries. By default, the primary key of the primary table of the view (if any) is the view's key, but this behavior can be overridden. The key is determined as follows (in order of priority).

If the view element has a use-key attribute, use the primary table's unique key whose name matches the use-key attribute value. For example, the following table has an id column (which is the primary key column) and a name column, which is unique, as enforced by the key named name_key.
```
<table name="person">
        <column name="id" type="auto" null="false"/>
        <column name="name" type="string" null="false"/>
        <key type="primary">
                <column ref="id"/>
        </key>
        <key name="name_key" type="unique">
                <column ref="name"/>
        </key>
</table>
```
We create a simple view for this column, as follows.
```
<view name="person">
        <table ref="person" use-key="name_key">
                <column ref="id"/>
                <column ref="name"/>
        </table>
</view>
```
Normally, the view's elements would be identified by their id value, because that is the primary key column. However, the use-key attribute overrides this behavior by using name_key instead of the primary key.
If any of the view's columns have a key-part attribute, the set of columns with this attribute is used as the view's key. The value of the key-part attributes is ignored. For example, the same behavior as above can be accomplished with the following view.
```
<view name="person">
        <table ref="person">
                <column ref="id"/>
                <column ref="name" key-part="yes"/>
        </table>
</view>
```
The key-part attribute can be used in conjunction with use-key, in which case the key is union of the sets of columns of both key selection mechanisms.
If no custom key is specified, but the primary table has a primary key, this primary key is used as the view's key.
In the event that none of the key selection mechanisms can be applied, the empty set is used the view's key.

User-defined classes

<view name="person" class="org.example.Person">
        <table ref="person">
                <column ref="id"/>
                <column ref="name"/>
                <column ref="birthdate"/>
                <column ref="length"/>
        </table>
</view>

TableTools schema generation plugin

Project Documentation