= Database Schema =

The schema for {{{schema.xml}}} contains a small number of elements with required and optional attributes.  The Propel generator contains a DTD (source:trunk/generator/dtd/database.dtd) that can be used to validate your {{{schema.xml}}} document.  Also, when you build your SQL and OM, the Propel generator will automatically validate your {{{schema.xml}}} file using a new, highly-detailed XSD (source:trunk/generator/resources/xsd/database.xsd).

== At-a-Glance == 

The hierarchical tree relationship for the elements is:

{{{
#!xml
<database>
   <external-schema />
   <table>
     <column>
       <inheritance />
     </column>
     <foreign-key>
       <reference />
     </foreign-key>
     <index>
       <index-column />
     </index>
     <unique>
       <unique-column />
     </unique>
     <id-method-parameter/>
   </table>
</database>
}}}

== Detailed Reference ==

This page provides an alternate rendering of the Appendix B - Schema Reference from the user's guide.  
It spells out in specific detail, just where each attribute or element belongs.

First, some conventions:

  *  Text surrounded by a '''/''' is text that you would provide and is not defined in the language. (i.e. a table name is a good example of this.)
  *  Optional items are surrounded by '''[''' and ''']''' characters.
  *  Items where you have an alternative choice have a '''|''' character between them (i.e. true|false)
  *  Alternative choices may be delimited by '''{''' and '''}''' to indicate that this is the default option, if not overridden elsewhere.
  *  '''...''' means repeat the previous item.

=== <database> element ===

Starting with the {{{<database>}}} element.  The ''attributes'' and ''elements'' available are:

{{{
  <database 
    name="/DatabaseName/" 
    defaultIdMethod="native|none" 
    [package="/ProjectName/"] 
    [baseClass="/baseClassName/"]
    [basePeer="/baseClassPeerName/"]
    [defaultPhpNamingMethod="nochange|underscore|phpname"
    [heavyIndexing="true|false"]
  >
    <external-schema>
    <table>
    ...
  </database>
}}}

The {{{package}}}, {{{baseClass}}}, {{{basePeer}}}, {{{defaultPhpNamingMethod}}}, and {{{heavyIndexing}}} attributes are generally optional.
A Database element may include an {{{<external-schema>}}} element, or multiple {{{<table>}}} elements.

=== <external-schema> element ===

The {{{<external-schema>}}} element is pretty simple. It just includes a schema file from somewhere on the file systems.  The format is:

{{{
  <external-schema filename="/a path to a file/" />
}}}

=== <table> element ===

The {{{<table>}}} element is the most complicated of the usable elements.  Its definition looks like this:

{{{
  <table 
     name = "/TableName/"
     [idMethod = "native|{none}"]
     [phpName = "/PhpObjectName/"]
     [skipSql = "true|false"]
     [abstract = "true|false"]
     [phpNamingMethod = "nochange|{underscore}|phpname"]
     [baseClass = "/baseClassName/"]
     [basePeer = "/baseClassPeerName/"]
     [description="/A text description of the table/"]
     [heavyIndexing = "true|false"]
     [readOnly = "true|false"]
  >

    <column>
    ...
    <foreign-key>
    ...
    <index>
    ...
    <unique>
    ...
    <id-method-parameter>
    ...
  </table>
}}}

According to the schema, {{{name}}} is the only required attribute.  Also, the {{{idMethod}}}, {{{phpNamingMethod}}}, {{{baseClass}}}, {{{basePeer}}}, and {{{heavyIndexing}}} attributes all default to what is specified by the {{{<database>}}} element.

=== <column> element ===

{{{
  <column
    name = "/ColumnName/"
    [phpName = "/PHPColumnName/"]
    [peerName = "/PEERNAME/"]
    [primaryKey = "true|{false}"]
    [required = "true|{false}"]
    [type = "BOOLEAN|TINYINT|SMALLINT|INTEGER|BIGINT|DOUBLE|FLOAT|REAL|DECIMAL|CHAR|{VARCHAR}|LONGVARCHAR|DATE|TIME|TIMESTAMP|BLOB|CLOB"]
    [size = "/NumericLengthOfColumn/"]
    [scale = "/DigitsAfterDecimalPlace/"]
    [default = "/AnyDefaultValueMatchingType/"]
    [autoIncrement = "true|{false}"]
    [description = "/Column Description/"]
    [phpNamingMethod = "nochange|underscore|phpname"]
    [inheritance = "single|{false}"]
    [inputValidator = "NameOfInputValidatorClass"]
   >
   [<inheritance key="/KeyName/" class="/ClassName/" [extends="/BaseClassName/"] />]
   </column>
}}}

=== <foreign-key> element ===

To link a column to another table use the following syntax:

{{{
  <foreign-key 
     foreignTable = "/TheOtherTableName/"
     [name = "/Name for this foreign key/"]
     [onDelete = "cascade|setnull|{restrict}"]
  >
    <reference local="/LocalColumnName/" foreign="/ForeignColumnName/" />
  </foreign-key>
}}}

=== <index> element ===

To create an index on one or more columns, use the following syntax:

{{{
  <index>
     <index-column name="/ColumnName/" [size="/LengthOfIndexColumn/"] />
     ...
  </index>
}}}

In some cases your RDBMS may require you to specify an index size.

=== <unique> element ===

To create a unique index on one or more columns, use the following syntax:

{{{
  <unique>
     <unique-column name="/ColumnName/" [size="/LengthOfIndexColumn/"] />
     ...
  </unique>
}}}

In some cases your RDBMS may require you to specify an index size for unique indexes.

=== <id-method-parameter> element ===

If you are using a database that uses sequences for auto-increment columns (e.g. PostgreSQL or Oracle), you can customize the name of the sequence using  the <id-method-paramter/> tag:

{{{
  <id-method-parameter value="my_custom_sequence_name"/>
}}}