This document describes the syntax for XML database definition.

Notes

All names of the tables/columns/indexes/constraints etc. must match the expression '/[a-z0-9_]+/', i.e. consist only of lowercase letters, numbers and the underscore character "_".

Tags

<database>

Root element for any db-schema definition.

attributes

• classNamespace: string — namespace (prefix) for generated class names, defaults to "Wikidot_Om"

<table>

Maps to a table element.

attributes

General

• name: string, required — unique name of a table

Related to class generation

• classNamespace: string — namespace (prefix) for generated class names, defaults the namespace for the embedding <database> tag
• className: string — name of the generated class. The final class name will be classNamespace+className, e.g. Wikidot_Om_Site. If omitted it will be autogenerated from the table name which will work in most cases
• baseClassName: string — name of the "base" class. The final class name will be classNamespace+baseClassName. If not given, it defaults to "Base_"+className
• peerClassName: string — name of the "peer" class. The final class name will be classNamespace+peerClassName. If not given, it defaults to "Peer_"+className
• basePeerClassName: string — name of the "base peer" class. The final class name will be classNamespace+basePeerClassName. If not given, it defaults to "Base_Peer_"+className

Inheritance

• inherits: string — name of another table in the database that this table would inherit structure from.
• inheritsPrimaryKey = true|false — copy definition of the primary key from the parent table if true; defaults to false
• inheritsForeignKeys = true|false — copy definition of foreign keys from the parent table if true; defaults to false
• inheritsIndexes = true|false — copy definition of indexes from the parent table if true; defaults to false
• inheritsTriggers = true|false — copy definition of triggers from the parent table if true then; defaults to false

placement

within <database>

example

<table name="person">
    <!-- structure definition follow -->
</table>
<table name="content_value_user_id" className="Content_Value_UserId" 
    inherits="content_value" 
    inheritsPrimaryKey="true"
    inheritsForeignKeys="true"
    inheritsIndexes="true">
 
    <!-- structure definition follow -->
</table>

<table>/<column>

Maps to a table column.

attributes

• name: string, required — unique name of a table
• type: string, required — any SQL-compatible type
• default: string — default value for the column
• primaryKey = true | false — if a column shoud be a primary_key for the table; there can be only one primary key for a table; defaults to false
• notNull = true | false — adds a NOT NULL constraint to the column; defaults to false

placement

within <table>

example

<column name="screen_name" type="varchar(256)"/>
<column name="post_count" type="int" default="0" notNull="true"/>
<column name="user_id" type="serial" primaryKey="true"/>

<primaryKey>

Defines a primary key for a table. A primary key can be also defined by adding a primaryKey="true" attribute to a column.

attributes

• column: string, required — name of the column to become a primary key; multiple columns can be given separated by a coma, e.g. "key, template_id"
• name: string — not required, will be autogenerated if not given (tableName+'_pkey')

placement

within <table>

example

<table name="foo">
    <column name="user_id" type="serial"/>
    <primaryKey column="user_id"/>
</table>

<unique>

Unique column constraint.

attributes

• column: string, required — names of the columns to be unique in the table; multiple columns should be coma-separated. can also be an expression
• name: string — if not given, name will be generated as tableName+''+columnName(+''+columnName)+'__unique'; in case of expression a name for the constraint should be given; names are unique within the database

placement

within <table>

example

<unique column="key, template_id, content_it"/>
<unique name="unique_username_constraint" column="lower(screen_name)"/>

<check>

Check constraint for the table.

attributes

• name: string, required — name of the condition, must be unique within a table.
• condition: expression — any expression that evaluates to a boolean value

placement

within <table>

example

<table name="table1">
    <column name="column1" type="int" default="3"/>
    <column name="column2" type="varchar(64)"/>
    <column name="column3" type="text"/>
 
    <check name="positive1" condition="column1 > 0"/>
</table>

<index>

Creates an index on a column or expression.

attributes

• column: string, required — name of column(s) to index, or an expression
• name: string — not required if indexing over (multiple) columns, required if indexing over an expression.
• method = btree | hash | gin | gist — method used to create an index; defaults to btree
• unique = true | false — should the index force unique values; defaults to false

placement

within <table>

example

<index column="template_id"/>
<index name="unique_screen_name" column="lower(screen_name)" unique="true"/>
<index column="unix_name, content_id"/>

<foreignKey>

Defines a foreign key that references another table.

attributes

• column: string, required — local column or coma-separated columns
• referencedTable: string, required — name of the referenced table
• referencedColumn: string, required — name of the referenced column or coma-separated list of columns
• onUpdate = no action | restrict | cascade | set null | set default — action taken when a referenced row in the master table is updated; defaults to "no action"
• onDelete = no action | restrict | cascade | set null | set default — action taken when a referenced row in the master table is deleted; defaults to "no action"
• deferrable = true | false — is it possible to defer checking the reference until a commit of a transaction; defaults to false
• initiallyDeferred = true | false — if checking is initially deferred; defaults to false

placement

within <table>

example

<foreignKey 
    column="site_id" 
    referencedTable="site" 
    referencedColumn="site_id"
    onDelete="cascade"
    onUpdate="cascade"
/>

<trigger>

Creates a trigger for the table.

attributes

• name: string, required — name of the trigger
• timing = after | before, required
• event = insert | update | delete, required
• for = row | statement — defaults to "statement"
• function: string, required — name of the function to execute (with arguments, if any)
• deferrable = true | false — is it possible to defer checking the reference until a commit of a transaction; defaults to false; forces the trigger to be a "constraint trigger"
• initiallyDeferred = true | false — if checking is initially deferred; defaults to false; forces the trigger to be a "constraint trigger"

placement

within <table>

example

<trigger name="check_change" event="update" timing="after" 
        function="template_property_change_trigger_php()" for="row"/>

<view>

Defines a view.

attributes

• name: string, required — name of the view

placement

within <database>

example

<view name="page">
    <column name="content_id" type="integer"/>
    <column name="unix_name" type="text"/>
    <column name="site_id" type="integer"/>
    <column name="category_id" type="integer"/>
    <column name="title" type="text"/>
    <column name="document" type="xml"/>
    <definition>
        SELECT 
            c.content_id AS content_id,
            c.unix_name AS unix_name,
            g.site_id AS site_id,
            c.category_id AS category_id,
            get_content_property_text(content_id, 'title') AS title,
            get_content_property_xml(content_id, 'document') AS document
        FROM
            content c
            JOIN content_category g USING (category_id)
        WHERE g.viewable = TRUE
    </definition>
</view>

<view>/<column>

A column inside a <view> element. Columns are defined here only to inform the code generator to generate proper get/set methods in the classes. Column names should match names defined in the supporting SELECT query.

attributes

• name: string, required — name of the column.

<view>/<definition>

Definition of the view. A SELECT SQL query. See above.

<function>

Defines a function.

attributes

• name: string, required — name of the function
• language: string, name of any defined language, e.g. plpgsql, plperl, plsql…
• volatility = volatile | stable | immutable, defaults to "volatile"

subnodes

• returns: SQL type, required, e.g. text, integer, trigger
• body: contains definition of the function

placement

within <database>

example

<function name="template_property_change_trigger"
    language="plpgsql">
    <returns>trigger</returns>
    <body>
        <![CDATA[
BEGIN
    IF NEW.template_id != OLD.template_id THEN
        RAISE EXCEPTION 'Cannot change template_id of the property.';
    END IF;
    IF NEW.type != OLD.type THEN
        RAISE EXCEPTION 'Cannot change type of the property.';
    END IF;
END;
        ]]>
    </body>
</function>
 
<function name="template_property_change_trigger_php"
    language="plphp">
    <returns>trigger</returns>
    <body>
        <![CDATA[
if($_TD['new']['template_id'] != $_TD['old']['template_id']){
    pg_raise('error', 'Cannot change template_id of the property.');
}
if($_TD['new']['type'] != $_TD['old']['type']){
    pg_raise('error', 'Cannot change type of the property.');
}
        ]]>
    </body>
</function>

<description>

Adds a description to any defined object. This is later added to the database as a COMMENT.

placement

within any tag

example

<table name="person">
    <description>
        Wikidot user.
    </description>
 
    <column name="person_id" type="serial" primaryKey="true">
        <description>
            Id of the user and its primary key.
        </description>
    </column>
</table>