8.7. Extended Schema Syntax

A schema.yml file can be simple, as shown in Listing 8-3. But relational models are often complex. That's why the schema has an extensive syntax able to handle almost every case.

8.7.1. Attributes

Connections and tables can have specific attributes, as shown in Listing 8-24. They are set under an _attributes key.

Listing 8-24 - Attributes for Connections and Tables

propel:
  _attributes:   { noXsd: false, defaultIdMethod: none, package: lib.model }
  blog_article:
    _attributes: { phpName: Article }

You may want your schema to be validated before code generation takes place. To do that, deactivate the noXSD attribute for the connection. The connection also supports the defaultIdMethod attribute. If none is provided, then the database's native method of generating IDs will be used--for example, autoincrement for MySQL, or sequences for PostgreSQL. The other possible value is none.

The package attribute is like a namespace; it determines the path where the generated classes are stored. It defaults to lib/model/, but you can change it to organize your model in subpackages. For instance, if you don't want to mix the core business classes and the classes defining a database-stored statistics engine in the same directory, then define two schemas with lib.model.business and lib.model.stats packages.

You already saw the phpName table attribute, used to set the name of the generated class mapping the table.

Tables that contain localized content (that is, several versions of the content, in a related table, for internationalization) also take two additional attributes (see Chapter 13 for details), as shown in Listing 8-25.

Listing 8-25 - Attributes for i18n Tables

propel:
  blog_article:
    _attributes: { isI18N: true, i18nTable: db_group_i18n }

# In config/business-schema.yml
propel:
  blog_article:
    _attributes: { phpName: Article }
    id:
    title: varchar(50)

# In config/stats-schema.yml
propel:
  stats_hit:
    _attributes: { phpName: Hit }
    id:
    resource: varchar(100)
    created_at:

# In config/business-schema.yml
propel:
  blog_article:
    _attributes: { phpName: Article, package: lib.model.business }
    id:
    title: varchar(50)

# In config/stats-schema.yml
propel_bis:
  stats_hit:
    _attributes: { phpName: Hit, package: lib.model.stat }
    id:
    resource: varchar(100)
    created_at:

8.7.2. Column Details

The basic syntax gives you two choices: let symfony deduce the column characteristics from its name (by giving an empty value) or define the type with one of the type keywords. Listing 8-26 demonstrates these choices.

Listing 8-26 - Basic Column Attributes

propel:
  blog_article:
    id:                 # Let symfony do the work
    title: varchar(50)  # Specify the type yourself

But you can define much more for a column. If you do, you will need to define column settings as an associative array, as shown in Listing 8-27.

Listing 8-27 - Complex Column Attributes

propel:
  blog_article:
    id:       { type: integer, required: true, primaryKey: true, autoIncrement: true }
    name:     { type: varchar(50), default: foobar, index: true }
    group_id: { type: integer, foreignTable: db_group, foreignReference: id, onDelete: cascade }

The column parameters are as follows:

• type: Column type. The choices are boolean, tinyint, smallint, integer, bigint, double, float, real, decimal, char, varchar(size), longvarchar, date, time, timestamp, bu_date, bu_timestamp, blob, and clob.
• required: Boolean. Set it to true if you want the column to be required.
• default: Default value.
• primaryKey: Boolean. Set it to true for primary keys.
• autoIncrement: Boolean. Set it to true for columns of type integer that need to take an auto-incremented value.
• sequence: Sequence name for databases using sequences for autoIncrement columns (for example, PostgreSQL and Oracle).
• index: Boolean. Set it to true if you want a simple index or to unique if you want a unique index to be created on the column.
• foreignTable: A table name, used to create a foreign key to another table.
• foreignReference: The name of the related column if a foreign key is defined via foreignTable.
• onDelete: Determines the action to trigger when a record in a related table is deleted. When set to setnull, the foreign key column is set to null. When set to cascade, the record is deleted. If the database engine doesn't support the set behavior, the ORM emulates it. This is relevant only for columns bearing a foreignTable and a foreignReference.
• isCulture: Boolean. Set it to true for culture columns in localized content tables (see Chapter 13).

8.7.3. Foreign Keys

As an alternative to the foreignTable and foreignReference column attributes, you can add foreign keys under the _foreignKeys: key in a table. The schema in Listing 8-28 will create a foreign key on the user_id column, matching the id column in the blog_user table.

Listing 8-28 - Foreign Key Alternative Syntax

propel:
  blog_article:
    id:
    title:   varchar(50)
    user_id: { type: integer }
    _foreignKeys:
      -
        foreignTable: blog_user
        onDelete:     cascade
        references:
          - { local: user_id, foreign: id }

The alternative syntax is useful for multiple-reference foreign keys and to give foreign keys a name, as shown in Listing 8-29.

Listing 8-29 - Foreign Key Alternative Syntax Applied to Multiple Reference Foreign Key

_foreignKeys:
  my_foreign_key:
    foreignTable:  db_user
    onDelete:      cascade
    references:
      - { local: user_id, foreign: id }
      - { local: post_id, foreign: id }

8.7.4. Indexes

As an alternative to the index column attribute, you can add indexes under the _indexes: key in a table. If you want to define unique indexes, you must use the _uniques: header instead. For columns that require a size, because they are text columns, the size of the index is specified the same way as the length of the column using parentheses. Listing 8-30 shows the alternative syntax for indexes.

Listing 8-30 - Indexes and Unique Indexes Alternative Syntax

propel:
  blog_article:
    id:
    title:            varchar(50)
    created_at:
    _indexes:
      my_index:       [title(10), user_id]
    _uniques:
      my_other_index: [created_at]

The alternative syntax is useful only for indexes built on more than one column.

8.7.5. Empty Columns

When meeting a column with no value, symfony will do some magic and add a value of its own. See Listing 8-31 for the details added to empty columns.

Listing 8-31 - Column Details Deduced from the Column Name

# Empty columns named id are considered primary keys
id:         { type: integer, required: true, primaryKey: true, autoIncrement: true }

# Empty columns named XXX_id are considered foreign keys
foobar_id:  { type: integer, foreignTable: db_foobar, foreignReference: id }

# Empty columns named created_at, updated at, created_on and updated_on
# are considered dates and automatically take the timestamp type
created_at: { type: timestamp }
updated_at: { type: timestamp }

For foreign keys, symfony will look for a table having the same phpName as the beginning of the column name, and if one is found, it will take this table name as the foreignTable.

8.7.6. I18n Tables

Symfony supports content internationalization in related tables. This means that when you have content subject to internationalization, it is stored in two separate tables: one with the invariable columns and another with the internationalized columns.

In a schema.yml file, all that is implied when you name a table foobar_i18n. For instance, the schema shown in Listing 8-32 will be automatically completed with columns and table attributes to make the internationalized content mechanism work. Internally, symfony will understand it as if it were written like Listing 8-33. Chapter 13 will tell you more about i18n.

Listing 8-32 - Implied i18n Mechanism

propel:
  db_group:
    id:
    created_at:

  db_group_i18n:
    name:        varchar(50)

Listing 8-33 - Explicit i18n Mechanism

propel:
  db_group:
    _attributes: { isI18N: true, i18nTable: db_group_i18n }
    id:
    created_at:

  db_group_i18n:
    id:       { type: integer, required: true, primaryKey: true,foreignTable: db_group, foreignReference: id, onDelete: cascade }
    culture:  { isCulture: true, type: varchar(7), required: true,primaryKey: true }
    name:     varchar(50)

8.7.7. Beyond the schema.yml: The schema.xml

As a matter of fact, the schema.yml format is internal to symfony. When you call a propel- command, symfony actually translates this file into a generated-schema.xml file, which is the type of file expected by Propel to actually perform tasks on the model.

The schema.xml file contains the same information as its YAML equivalent. For example, Listing 8-3 is converted to the XML file shown in Listing 8-34.

Listing 8-34 - Sample schema.xml, Corresponding to Listing 8-3

<?xml version="1.0" encoding="UTF-8"?>
 <database name="propel" defaultIdMethod="native" noXsd="true" package="lib.model">
    <table name="blog_article" phpName="Article">
      <column name="id" type="integer" required="true" primaryKey="true"autoIncrement="true" />
      <column name="title" type="varchar" size="255" />
      <column name="content" type="longvarchar" />
      <column name="created_at" type="timestamp" />
    </table>
    <table name="blog_comment" phpName="Comment">
      <column name="id" type="integer" required="true" primaryKey="true"autoIncrement="true" />
      <column name="article_id" type="integer" />
      <foreign-key foreignTable="blog_article">
        <reference local="article_id" foreign="id"/>
      </foreign-key>
      <column name="author" type="varchar" size="255" />
      <column name="content" type="longvarchar" />
      <column name="created_at" type="timestamp" />
    </table>
 </database>

The description of the schema.xml format can be found in the documentation and the "Getting Started" sections of the Propel project website (propel.phpdb.org/docs/user_guide/chapters/appendices/AppendixB-SchemaReference.html).

The YAML format was designed to keep the schemas simple to read and write, but the trade-off is that the most complex schemas can't be described with a schema.yml file. On the other hand, the XML format allows for full schema description, whatever its complexity, and includes database vendor-specific settings, table inheritance, and so on.

Symfony actually understands schemas written in XML format. So if your schema is too complex for the YAML syntax, if you have an existing XML schema, or if you are already familiar with the Propel XML syntax, you don't have to switch to the symfony YAML syntax. Place your schema.xml in the project config/ directory, build the model, and there you go.