Setup Backend (Database schema definition)

From Tine 2.0 - Wiki

With the tine20 setup backend classes it is possible to create and manipulate database tables based on database-agnostic xml schema definitions and php migration scripts.

Each tine20 application has a file called setup.xml which describes the application tables that should be created when the application gets installed. Furthermore one can create php update scripts (migrations) to change the table layout of existing installations.

Internally the setup backend classes utilize the Zend Db database abstraction layer and currently work with Zend_Db_Adapter_Pdo_Mysql (Pdo Mysql) and Zend_Db_Adapter_Oracle (oci8).

The supported datatypes were inspired by eZ Components Database Schema (http://www.ezcomponents.org/docs/api/latest/introduction_DatabaseSchema.html) and are listed and described below.

Example setup.xml

    <?xml version="1.0" encoding="utf-8"?>
    <application>
        <name>ExampleApplication</name>
        <!-- gettext('ExampleApplication') -->   
        <version>0.1</version>
        <order>60</order>
        <status>enabled</status>
        <tables>
            <table>
                <name>example_table</name>
                <version>1</version>
                <declaration>
                    <field>
                        <name>id</name>
                        <type>integer</type>
                        <autoincrement>true</autoincrement>
                    </field>
                    <field>
                        <name>name</name>
                        <type>text</type>
                        <length>40</length>
                        <notnull>true</notnull>
                    </field>
                    <field>
                        <name>description</name>
                        <type>clob</type>
                    </field>
                    <field>
                        <name>created_by</name>
                        <type>integer</type>
                    </field>
                    <field>
                        <name>creation_time</name>
                        <type>datetime</type>
                    </field> 
                    <field>
                        <name>is_deleted</name>
                        <type>boolean</type>
                        <default>false</default>
                    </field>
                    <index>
                        <name>id</name>
                        <primary>true</primary>
                        <field>
                            <name>id</name>
                        </field>
                    </index>
                </declaration>
            </table>
        </tables>
    </application>

Currently supported datatypes

  • integer
  • decimal
  • float
  • boolean
  • date
  • datetime
  • text
  • clob
  • blob
  • enum

Field Tag Syntax: Defining a table column

Each table column is described by a <field> tag which supports a number of supported child tags:

  • name: string column name
  • type: string column type (see supported datat types section)
  • default: default value that is inserted to db in no value is specified
  • notnull: field can be empty?
  • primary: field is the primary key/is part of tthe primary key

Everey field tag requires at least a <name> and a <type>. The other tags are optional.

Depending on the specified field type there are additional optional tags:

  • integer
    • length: integer value for the length
    • unsigned: true | false
    • autoincrement: true | false
  • decimal
    • length: integer value for the precision (the total digits count)
    • scale: integer value for the scale (number of digits after the decimal point)
  • text
    • length: depending on the length the actual datatype can vary (e.g. varchar, text, longtext, clop etc.)
  • enum
    • value: each value tag represents one value that is allowed to be inserted into the enum column
  • float
  • boolean
  • date
  • datetime
  • clob
  • blob


Differences to eZ Database Schema

Datatypes supported by tine20 Setup Backend that are not supported by eZ Database Schema:

   * enum
   * datetime

Datatypes supported by eZ Database Schema that are not supported by tine20 Setup Backend:

   * timestamp