Doctrine Schema

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 DBMS engine.

Type	MySQL Type
integer	integer
float	double
decimal	decimal
string	varchar
array	text
object	text
blob	longblob
clob	longtext
timestamp	datetime
time	time
date	date
enum	varchar/enum
gzip	text

If you want to use MySQL built-in enum type, you need enable the native enum support in the config file.

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

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

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

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

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

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
      foreignType: many

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

Sluggable:

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

Nesting Behaviors

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