8.2. Symfony's Database Schema

In order to create the data object model that symfony will use, you need to translate whatever relational model your database has to an object data model. The ORM needs a description of the relational model to do the mapping, and this is called a schema. In a schema, you define the tables, their relations, and the characteristics of their columns.

Symfony's syntax for schemas uses the YAML format. The schema.yml files must be located in the myproject/config/ directory.

8.2.1. Schema Example

How do you translate a database structure into a schema? An example is the best way to understand it. Imagine that you have a blog database with two tables: blog_article and blog_comment, with the structure shown in Figure 8-1.

Figure 8.1 A blog database table structure

The related schema.yml file should look like Listing 8-3.

Listing 8-3 - Sample schema.yml

propel:
  blog_article:
    _attributes: { phpName: Article }
    id:
    title:       varchar(255)
    content:     longvarchar
    created_at:
  blog_comment:
    _attributes: { phpName: Comment }
    id:
    article_id:
    author:      varchar(255)
    content:     longvarchar
    created_at:

Notice that the name of the database itself (blog) doesn't appear in the schema.yml file. Instead, the database is described under a connection name (propel in this example). This is because the actual connection settings can depend on the environment in which your application runs. For instance, when you run your application in the development environment, you will access a development database (maybe blog_dev), but with the same schema as the production database. The connection settings will be specified in the databases.yml file, described in the "Database Connections" section later in this chapter. The schema doesn't contain any detailed connection to settings, only a connection name, to maintain database abstraction.

8.2.2. Basic Schema Syntax

In a schema.yml file, the first key represents a connection name. It can contain several tables, each having a set of columns. According to the YAML syntax, the keys end with a colon, and the structure is shown through indentation (one or more spaces, but no tabulations).

A table can have special attributes, including the phpName (the name of the class that will be generated). If you don't mention a phpName for a table, symfony creates it based on the camelCase version of the table name.

A table contains columns. The column value can be defined in three different ways:

• If you define nothing, symfony will guess the best attributes according to the column name and a few conventions that will be described in the "Empty Columns" section later in this chapter. For instance, the id column in Listing 8-3 doesn't need to be defined. Symfony will make it an auto-incremented integer, primary key of the table. The article_id in the blog_comment table will be understood as a foreign key to the blog_article table (columns ending with _id are considered to be foreign keys, and the related table is automatically determined according to the first part of the column name). Columns called created_at are automatically set to the timestamp type. For all these columns, you don't need to specify any type. This is one of the reasons why schema.yml is so easy to write.
• If you define only one attribute, it is the column type. Symfony understands the usual column types: boolean, integer, float, date, varchar(size), longvarchar (converted, for instance, to text in MySQL), and so on. For text content over 256 characters, you need to use the longvarchar type, which has no size (but cannot exceed 65KB in MySQL). Note that the date and timestamp types have the usual limitations of Unix dates and cannot be set to a date prior to 1970-01-01. As you may need to set older dates (for instance, for dates of birth), a format of dates "before Unix" can be used with bu_date and bu_timestamp.
• If you need to define other column attributes (like default value, required, and so on), you should write the column attributes as a set of key: value. This extended schema syntax is described later in the chapter.

Columns can also have a phpName attribute, which is the capitalized version of the name (Id, Title, Content, and so on) and doesn't need overriding in most cases.

Tables can also contain explicit foreign keys and indexes, as well as a few database-specific structure definitions. Refer to the "Extended Schema Syntax" section later in this chapter to learn more.