Caution: You are browsing the legacy 1.x part of this website.
This version of symfony is not maintained anymore. If some of your projects still use this version, consider upgrading.
This work is licensed under the GFDL license.

Master Symfony fundamentals

Be trained by SensioLabs experts (2 to 6 day sessions -- French or English).
training.sensiolabs.com

Discover the SensioLabs Support

Access to the SensioLabs Competency Center for an exclusive and tailor-made support on Symfony
sensiolabs.com
Blackfire Profiler Fire up your PHP Apps Performance

PHP Project Quality Done Right

Chapter 4 - Schema Files

In the previous chapters you've seen some various syntaxes for specifying your schema information in YAML files placed in config/doctrine. This chapter explains the syntaxes and how to specify all your schema meta data in YAML format.

Data Types

Doctrine offers several column data types. When you specify the portable Doctrine type it is automatically converted to the appropriate type of the DBMS you are using. Below is a list of the available column types that can be used as well as the type it is translated to when using the MySQL and pgSQL DBMS engines.

note

Doctrine data types are standardized and made portable across all DBMS. For the types that the DBMS do not support natively, Doctrine has the ability to convert the data on the way in to the and on the way out of the database. For example the Doctrine array and object types are serialized() on the way in and unserialized() on the way out.

Type MySQL Type pgSQL Type
integer integer int/serial
integer(1) tinyint smallint/serial
integer(2) smallint smallint/serial
integer(3) mediumint int/serial
integer(4) int int/serial
integer(5) bigint bigint/bigserial
float double float
double double float
decimal decimal numeric
char char char
varchar varchar varchar
string varchar varchar
array text text
object text text
blob longblob bytea
blob(255) tinyblob bytea
blob(65532) blob bytea
blob(16777215) mediuumblob bytea
clob longtext text
clob(255) tinytext text
clob(65532) text text
clob(16777215) mediumtext text
timestamp datetime timestamp without timezone
time time time without timezone
date date date
gzip text text
boolean tinyint(1) boolean
bit bit varbit
varbit n/a varbit
inet n/a inet
enum -see below- -see below-
  • Char(length) is used for string if "fixed" parameter is true. Length defaults to 255 if not provided.
  • Any value up to and including the number shown in brackets will produce the specified column type
  • Any integer with size greater than 4 will produce "bigint"
  • In postgres, serial is used if "autoincrement" is set
  • In Doctrine >= 1.1, timezone is not specified

sidebar

The Doctrine enum type can either be emulated or you can use the native enum type if your DBMS supports it. It is off by default so you will need to enable an attribute to use native enums.

Before we enable the attribute Doctrine will generate SQL like the following and simply emulate the enum type and will make sure the value you specify is one of the valid specified values.

CREATE TABLE user (id BIGINT AUTO_INCREMENT, username VARCHAR(255), password VARCHAR(255), user_type VARCHAR(255), PRIMARY KEY(id)) ENGINE = INNODB;

Now lets specify the use_native_enum attribute on our connection so that Doctrine knows to generate the native enum sql for your DBMS.

all:
  doctrine:
    class: sfDoctrineDatabase
    param:
      dsn: 'mysql:host=localhost;dbname=symfony12doctrine'
      username: user
      attributes:
        use_native_enum: true

Now that we have enabled the attribute Doctrine generates the following SQL under MySQL:

CREATE TABLE user (id BIGINT AUTO_INCREMENT, username VARCHAR(255), password VARCHAR(255), user_type ENUM('Normal', 'Administrator'), PRIMARY KEY(id)) ENGINE = INNODB;

Below is a sample yaml schema file that implements each of the different column types.

User:
  columns:
    id:
      type: integer(4)
      primary: true
      autoincrement: true
    username: string(255)
    password: string(255)
    latitude: float
    longitude: float
    hourly_rate:
      type: decimal
      scale: 2
    groups_array: array
    session_object: object
    description: clob
    profile_image_binary_data: blob
    created_at: timestamp
    time_last_available: time
    date_last_available: date
    roles:
      type: enum
      values: [administrator, moderator, normal]
      default: normal
    html_header: gzip

Generates the following SQL with MySQL:

CREATE TABLE user (id INT AUTO_INCREMENT, username VARCHAR(255), password VARCHAR(255), latitude DOUBLE, longitude DOUBLE, hourly_rate DECIMAL(18,2), groups_array TEXT, session_object TEXT, description LONGTEXT, profile_image_binary_data LONGBLOB, created_at DATETIME, time_last_available TIME, date_last_available DATE, roles ENUM('administrator', 'moderator', 'normal') DEFAULT 'normal', html_header TEXT, PRIMARY KEY(id)) ENGINE = INNODB;

Options

Often you need to set options on your table for controlling things like charset, collation and table type in mysql. These can be controlled easily with options.

User:
  options:
    type: MyISAM
    collate: utf8_unicode_ci
    charset: utf8
  columns:
    username: string(255)
    password: string(255)

Generates the following SQL with MySQL:

CREATE TABLE user (id BIGINT AUTO_INCREMENT, username VARCHAR(255), password VARCHAR(255), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = MyISAM;

Indexes

You can optimize your database by defining indexes on columns which are used in conditions on your queries. Below is an example of indexing the username column of a user table since it is common to do lookups on the table by the users username.

User:
  columns:
    username: string(255)
    password: string(255)
  indexes:
    username_index:
      fields: [username]
      type: unique

Generates the following SQL with MySQL:

CREATE TABLE user (id BIGINT AUTO_INCREMENT, username VARCHAR(255), password VARCHAR(255), UNIQUE INDEX username_indext_idx (username), PRIMARY KEY(id)) ENGINE = INNODB;

You can also optionally specify unique directly on the column when dealing with single column unique indexes.

User:
  columns:
    username:
      type: string(255)
      unique: true
    password: string(255)

Generates the following SQL with MySQL:

CREATE TABLE user (id BIGINT AUTO_INCREMENT, username VARCHAR(255) UNIQUE, password VARCHAR(255), PRIMARY KEY(id)) ENGINE = INNODB;

note

Indexes are automatically created on relationship foreign keys when the relationships are defined. The next section explains how to define relationships between foreign keys on your tables.

Relationships

Doctrine offers the ability to map the relationships which exist in your database to the ORM so that it can be the most help when working with your data.

One to One

Here is a simple example of how to define a one-to-one relation between a User and Profile model.

Profile:
  columns:
    user_id: integer
    name: string(255)
    email_address:
      type: string(255)
      email: true
  relations:
    User:
      local: user_id
      foreign: id
      type: one
      foreignType: one

Generates the following SQL with MySQL:

CREATE TABLE profile (id BIGINT AUTO_INCREMENT, user_id BIGINT, name VARCHAR(255), email_address VARCHAR(255), INDEX user_id_idx (user_id), PRIMARY KEY(id)) ENGINE = INNODB;
ALTER TABLE profile ADD FOREIGN KEY (user_id) REFERENCES user(id);

One to Many

Here is a simple example of how to define a one-to-many relation between a User and Phonenumber model.

Phonenumber:
  columns:
    user_id: integer
    phonenumber: string(255)
  relations:
    User:
      foreignAlias: Phonenumbers
      local: user_id
      foreign: id
      type: one
      foreignType: many

Generates the following SQL with MySQL:

CREATE TABLE phonenumber (id BIGINT AUTO_INCREMENT, user_id BIGINT, phonenumber VARCHAR(255), INDEX user_id_idx (user_id), PRIMARY KEY(id)) ENGINE = INNODB;
ALTER TABLE phonenumber ADD FOREIGN KEY (user_id) REFERENCES user(id);

Many to Many

Here is a simple example of how to define a many-to-many relation between a BlogPost and Tag model.

BlogPost:
  columns:
    user_id: integer
    title: string(255)
    body: clob
  relations:
    User:
      local: user_id
      foreign: id
      type: one
      foreignType: one
      foreignAlias: BlogPosts
    Tags:
      class: Tag
      foreignAlias: BlogPosts
      refClass: BlogPostTag
      local: blog_post_id
      foreign: tag_id
 
Tag:
  columns:
    name: string(255)
 
BlogPostTag:
  columns:
    blog_post_id:
      type: integer
      primary: true
    tag_id:
      type: integer
      primary: true
  relations:
    BlogPost:
      local: blog_post_id
      foreign: id
      foreignAlias: BlogPostTags
    Tag:
      local: tag_id
      foreign: id
      foreignAlias: BlogPostTags

Generates the following SQL with MySQL:

CREATE TABLE blog_post (id BIGINT AUTO_INCREMENT, user_id BIGINT, title VARCHAR(255), body LONGTEXT, INDEX user_id_idx (user_id), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE blog_post_tag (blog_post_id BIGINT, tag_id BIGINT, PRIMARY KEY(blog_post_id, tag_id)) ENGINE = INNODB;
CREATE TABLE tag (id BIGINT AUTO_INCREMENT, name VARCHAR(255), PRIMARY KEY(id)) ENGINE = INNODB;
ALTER TABLE blog_post ADD FOREIGN KEY (user_id) REFERENCES user(id);
ALTER TABLE blog_post_tag ADD FOREIGN KEY (tag_id) REFERENCES tag(id);
ALTER TABLE blog_post_tag ADD FOREIGN KEY (blog_post_id) REFERENCES blog_post(id);

Cascading Operations

When saving objects in Doctrine it is cascaded to associated objects by default. Deleting is slightly different. Doctrine has the ability to do both application and database level cascading deletes.

Database Level

Doctrine also has the ability to export cascading operations to the database level. Below is an example of how to setup a model with some cascading options.

User:
  columns:
    username: string(255)
    password: string(255)
 
Phonenumber:
  columns:
    user_id: integer
    phonenumber: string(255)
  relations:
    User:
      foreignAlias: Phonenumbers
      local: user_id
      foreign: id
      type: one
      foreignType: many
      onDelete: CASCADE

Generates the following SQL with MySQL:

CREATE TABLE phonenumber (id BIGINT AUTO_INCREMENT, user_id BIGINT, phonenumber VARCHAR(255), INDEX user_id_idx (user_id), PRIMARY KEY(id)) ENGINE = INNODB;
ALTER TABLE phonenumber ADD FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE;

note

Database level cascading is specified on the side where the foreign key lives.

Application Level

Unlike the save() operations the delete() cascading needs to be turned on explicitly. Here is an example:

note

Application level cascading save() and delete() does not apply when doing DQL update and delete statements, only when calling save() and delete() on your objects.

User:
  columns:
    username: string(255)
    password: string(255)
  relations:
    Phonenumbers:
      class: Phonenumber
      local: id
      foreign: id
      type: many
      foreignType: one
      cascade: [delete]

Phonenumber:
  columns:
    user_id: integer
    phonenumber: string(255)
  relations:
    User:
      foreignAlias: Phonenumbers
      local: user_id
      foreign: id
      type: one
      foreignType: many

note

Application level cascading deletes differ from database level in that they are defined on the side where the relationship you wish to cascade on is defined. This is different than database level cascades where you always specify it on the side where the foreign key lives.

Behaviors

One great feature of Doctrine is the ability to have plug n' play behavior. These behaviors can be easily included in your model definitions and you inherit functionality automatically.

Core Behaviors

Here is a list of behavior bundled with Doctrine core. You can use any of the behaviors in your models without writing any code.

Name Description
Geographical Adds latitude and longitude to your model and offers functionality for calculating miles/kilometers between records.
I18n Adds internationalization capabilities to your models.
NestedSet Turn your models in to a traversable tree.
Searchable Index all the data in your models and make it searchable.
Sluggable Add a slug field to your models and have it automatically create a slug based on your configuration.
SoftDelete Never really delete a record. Will simply set a deleted flag instead and filter all deleted records from select queries.
Timestampable Add a created_at and updated_at column to your models have Doctrine set them when inserting and updating records.
Versionable Turn your models in to an audit log and record all changes. Offers the ability to revert back to previous versions easily.

You can easily enable a behavior by using the actAs functionality. Below is an example of how to use the Sluggable behavior.

BlogPost:
  actAs:
    Sluggable:
      fields: [title]
      unique: true
  columns:
    user_id: integer
    title: string(255)
    body: clob

The above example will automatically add a slug column to the model and will set the value of the slug column based on the value of the title column and make sure the value is unique. If a slug already exists in the database with the same value then 1, 2, 3, etc. is appended to the end.

Generates the following SQL with MySQL:

CREATE TABLE blog_post (id BIGINT AUTO_INCREMENT, user_id BIGINT, title VARCHAR(255), body LONGTEXT, slug VARCHAR(255), UNIQUE INDEX sluggable_idx (slug), INDEX user_id_idx (user_id), PRIMARY KEY(id)) ENGINE = INNODB;

note

You can also write your own behaviors. Check out the source code of the existing behaviors to get a peek at how they work. They can be found in SF_ROOT/plugins/sfDoctrinePlugin/lib/doctrine/Doctrine/Template. And you can read more about Doctrine behaviors in the manual.

Nesting Behaviors

Doctrine offers the ability to easily nest behaviors. For example you may want to have a Sluggable behavior on your auto-generated model with the I18n behavior.

Gallery:
  actAs:
    I18n:
      fields: [title, description]
      actAs:
        Sluggable:
          fields:  [title]
  columns:
    title: string(255)
    description: clob

Now the GalleryTranslation model which is automatically generated will have a slug column which is automatically set for you based on the translated title column. You can mix your behaviors together but remember some behaviors will not always play together as they are developed standalone and are not aware of each other.

Inheritance

Another great feature of Doctrine is the ability to use native PHP OOP inheritance with your models. It supports three different inheritance strategies which can be used independently or mixed together. Below are some examples of the different inheritance strategies.

Inheritance Types

Name Description
Concrete Each child class has a separate table has all the columns of its parents
Simple Each child class shares the same table and columns as its parents
Column Aggregation All columns must be defined in the parent and each child class is determined by a type column

Below are some examples of the three different inheritance strategies supported by Doctrine.

Concrete Inheritance

Concrete inheritance creates separate tables for child classes. However in concrete inheritance each class generates a table which contains all columns, including inherited columns.

TextItem:
  columns:
    topic: string(100)

Comment:
  inheritance:
    extends: TextItem
    type: concrete
  columns:
    content: string(300)

Generates the following SQL with MySQL:

CREATE TABLE text_item (id BIGINT AUTO_INCREMENT, topic VARCHAR(100), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE comment (id BIGINT AUTO_INCREMENT, topic VARCHAR(100), content TEXT, PRIMARY KEY(id)) ENGINE = INNODB;

Simple Inheritance

Simple inheritance is the simplest inheritance. In simple inheritance all the child classes share the same columns as the parent.

Entity:
  columns:
    name: string(30)
    username: string(20)
    password: string(16)
    created: integer(11)

User:
  inheritance:
    extends: Entity
    type: simple

Group:
  inheritance:
    extends: Entity
    type: simple

Generates the following SQL with MySQL:

CREATE TABLE entity (id BIGINT AUTO_INCREMENT, name VARCHAR(30), username VARCHAR(20), password VARCHAR(16), created BIGINT, PRIMARY KEY(id)) ENGINE = INNODB;

Column Aggregation Inheritance

In the following example we have one database table called entity. Users and groups are both entities and they share the same database table.

The entity table has a column called type automatically added which tells whether an entity is a group or a user.

Entity:
  columns:
    name: string(30)
    username: string(20)
    password: string(16)
    created: integer(11)

User:
  inheritance:
    extends: Entity
    type: column_aggregation

Group:
  inheritance:
    extends: Entity
    type: column_aggregation

Generates the following SQL with MySQL:

CREATE TABLE entity (id BIGINT AUTO_INCREMENT, name VARCHAR(30), username VARCHAR(20), password VARCHAR(16), created BIGINT, type VARCHAR(255), PRIMARY KEY(id)) ENGINE = INNODB;

Global Schema Information

Doctrine schemas allow you to specify certain parameters that will apply to all of the models defined in the schema file. Below you can find an example on what global parameters you can set for schema files.

List of global parameters:

Name Description
connection Name of the connection to bind the models to
attributes Array of attributes to apply to the models
actAs Array of actAs behaviors and options to enable on the models
options Array of table options to apply to the models
inheritance Inheritance options to apply to the models

Here is a sample schema file which implements some global schema information:

connection: conn_name1
actAs: [Timestampable]
options:
  type: INNODB
 
User:
  columns:
    id:
      type: integer(4)
      primary: true
      autoincrement: true
    contact_id:
      type: integer(4)
    username:
      type: string(255)
    password:
      type: string(255)
  relations:
    Contact:
      foreignType: one
 
Contact:
  columns:
    id:
      type: integer(4)
      primary: true
      autoincrement: true
    name:
      type: string(255)

Generates the following SQL with MySQL:

CREATE TABLE contact (id INT AUTO_INCREMENT, name VARCHAR(255), created_at DATETIME, updated_at DATETIME, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE user (id INT AUTO_INCREMENT, contact_id INT, username VARCHAR(255), password VARCHAR(255), created_at DATETIME, updated_at DATETIME, INDEX contact_id_idx (contact_id), PRIMARY KEY(id)) ENGINE = INNODB;
ALTER TABLE user ADD FOREIGN KEY (contact_id) REFERENCES contact(id);

All of the settings at the top will be applied to every model which is defined in that yaml file.

Plugin Schemas

With symfony plugins, using Doctrine schemas are no different than using them in your main config/doctrine folder. The plugin should also have the same config/doctrine directory containing YAML files. It is not necessary to specify any package parameter like you have to with Propel. The plugin is smart enough to know it is a part of a plugin because of its location.

The models, forms, filters, etc. are all generated in to sub-folders for the plugin to make organization and maintenance of your models easier. For example in sfDoctrineGuardPlugin sfGuardUser is generated as follows.

lib/
  model/
   doctrine/
     sfDoctrineGuardPlugin/
       sfGuardUser.class.php
       sfGuardUserTable.class.php
       base
         BasesfGuardUser.class.php
  form/
    doctrine/
      BaseFormDoctrine.class.php
      sfDoctrineGuardPlugin/
        sfGuardUserForm.class.php
        base
          BasesfGuardUserForm.class.php
plugins/
  sfDoctrineGuardPlugin/
    lib/
      model/
        doctrine/
          PluginsfGuardUser.class.php
          PluginsfGuardUserTable.class.php
      form/
        doctrine/
          PluginsfGuardUserForm.class.php

The hierarchy of the generated classes are as follows.

Name Extends Description
sfGuardUser PluginsfGuardUser Top level model class for all your custom project functionality.
PluginsfGuardUser BasesfGuardUser Plugin level model class for functionality bundled with the plugin.
BasesfGuardUser sfDoctrineRecord Generated base model class containing schema meta data.
sfGuardUserTable PluginsfGuardUserTable Top level table class for custom project functionality.
PluginsfGuardUserTable Doctrine_Table Plugin level table class for functionality bundled with the plugin.
sfGuardUserForm PluginsfGuardUserForm Top level form class for all your custom project functionality.
PluginsfGuardUserForm BasesfGuardUserForm Plugin level form class for functionality bundled with the plugin.
BasesfGuardUserForm BaseFormDoctrine Generated base form class containing form widgets and validators.
BaseFormDoctrine sfFormDoctrine Generated base form class which all generated forms extend.

Element Definitions

Below is a list with all the allowed element names and a brief definition for each one.

Root Elements

Name Description
abstract Whether or not to make the generated class abstract. Defaults to false. When a class is abstract it is not exported to the database.
className Name of the class to generate
tableName Name of the table in your DBMS to use.
connection Name of the Doctrine_Connection instance to bind the model to.
columns Column definitions.
relations Relationship definitions.
indexes Index definitions.
attributes Attribute definitions.
actAs ActAs definitions.
options Option definitions.
inheritance Array for inheritance definition
listeners Array defining listeners to attach
checks Checks to run at application level as well as exporting to your DBMS

Columns

Name Description
name Name of the column.
fixed Whether or not the column is fixed.
primary Whether or not the column is a part of the primary key.
autoincrement Whether or not the column is an autoincrement column.
type Doctrine data type of the column
length Length of the column
default Default value of the column
scale Scale of the column. Used for the decimal type.
values List of values for the enum type.
comment Comment for the column.
sequence Sequence definition for column.
zerofill Whether or not to make the column fill empty characters with zeros
extra Array of extra information to store with the column definition
unsigned Unsigned modifiers for some field definitions, although not all DBMS's support this modifier for integer field types.

Relations

Name Description
class Name of class to use for relationship.
alias Alias to use to identify relationship.
type The relationship type. Value can be either one or many and it defaults to one.
refClass Middle reference class to use for many to many relationships.
local The local field name used in the relationship.
foreign the foreign field name used in the relationship.
foreignAlias The alias of the opposite end of the relationship. Only allowed when autoComplete is set to true.
foreignType The type of the opposite end of the relationship. Only allowed when autoComplete is set to true.
autoComplete Whether or not to add the relationship to the opposite end making it bi-directional. Defaults to true.
cascade Application level cascading options.
onDelete Database level cascading delete value.
onUpdate Database level cascading update value.
equal Whether or not the relationship is a equal nested many to many.
owningSide -
refClassRelationAlias -

Inheritance

Name Description
type Type of inheritance to use. Allowed values are concrete, column_aggregation, and simple.
extends Name of the class to extend.
keyField Name of the field to use as the key for column_aggregation inheritance.
keyValue Value to fill the keyField with for column_aggregation inheritance.

Indexes

Name Description
name Name of the index to create.
fields Array of fields to use in the index.
unique Whether or not the index is unique.

tip

More can be read about schema files in the Doctrine Manual here.