3. Creating the database schema

Doctrine uses the YAML files to describe and manage the database contents. The schema consists of two parts.

  1. The database structure.
  2. The initial data (so-called fixtures) that are uploaded together with the structure.

Our photo gallery is too simple to have any fixtures, so we will stop on the structure which is going to have just two tables: for photos and comments. Let's create the /doctrine/schema/schema.yml file with the following content:

---
options:
    type: InnoDB
    collate: utf8_general_ci
    charset: utf8

These options concern the tables and depend on the database engine. As we write this article for MySQL, we have chosen the InnoDB table type and Unicode charsets for the text field values.

As we can see, the YAML syntax is very simple, and moreover – it is converted directly to PHP arrays, contrary to XML. The disadvantage is performance. The existing parsers are not too fast, especially if they are written in pure PHP, but fortunately Doctrine uses YAML only from time to time, usually during the development process, when we want to apply some changes to the database structure. Going back to YAML format itself, you must pay attention to the indentation. There must not be used the tabulation, but a constant number of spaces. In this article I decided to use four spaces for one indentation level.

Let's add the photo table to our model:

Photo:
    tableName: photos
    columns:
        id:
            type: integer(4)
            primary: true
            notnull: true
            autoincrement: true
        title:
            type: string(50)
            notnull: true
        filename:
            type: string(50)
            notnull: true
        date:
            type: integer(4)
            notnull: true
    listeners: [PhotoListener]
    relations:
        Comments:
            class: Comment
            local: id
            foreign: photo_id
            onUpdate: CASCADE
            onDelete: CASCADE
            type: many
            foreignType: one
            foreignAlias: Photo

The first identifier is the name of our model which we will use while accessing to it via Doctrine. In our case, this is Photo. We may also specify the name of the table in the database because it does not have to match the model name. The columns section defines the list of model columns. If we want to specify just the data type, Doctrine provides us a shortened form:

field1: type
field2: type
field3: type(length)

Doctrine uses its own type system and converts them to the appropriate database types depending on the chosen engine. We need only two base types: integer and string, in both cases we must specify their length in bytes. The library must also know about the relationships we want to establish between the tables and here we have the relations section. Each relationship must be identified with its own unique name to access it in DQL queries. Then, we provide the attributes:

  1. class – the name of the model that we create the relationship to.
  2. local – the field in the current table used in the relationship.
  3. foregin – the field in the foreign table used in the relationship.
  4. onUpdate, onDelete – actions that must be performed on the foreign table rows, if we update or delete the matching row in the current table. The CASCADE value is one of the standard options. It orders to update or remove the foreign rows, too.
  5. type – relationship type in the direction from Photo to Comment (many comments)
  6. foreignType – relationship type in the direction from Comment to Photo (one photo for a comment)
  7. foreignAlias – the alias used to access the Photo model from the comments' point of view.

There is also one more section, listeners - we will get back to it later.

The second model will be used to store comments:

Comment:
    tableName: comments
    columns:
        id:
            type: integer(4)
            primary: true
            notnull: true
            autoincrement: true
        author:
            type: string(50)
            notnull: true
        date:
            type: integer(4)
            notnull: true
        content:
            type: string(4000)
            notnull: true
        photo_id:
            type: integer(4)
            notnull: true
    listeners: [CommentListener]
    indexes:
        Photo_Index:
            fields: [photo_id]

The structure is similar to the previous one, but we can notice here another section: indexes which defines table indexes, as the name says. Similarly to relationships, each index must be given a unique name and a list of the columns. The list can be defined with square brackets, and the names are separated with commas.