3. Creating the database schema
Doctrine uses the YAML files to describe and manage the database contents. The schema consists of two parts.
- The database structure.
- 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:
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:
class– the name of the model that we create the relationship to.
local– the field in the current table used in the relationship.
foregin– the field in the foreign table used in the relationship.
onDelete– actions that must be performed on the foreign table rows, if we update or delete the matching row in the current table. The
CASCADEvalue is one of the standard options. It orders to update or remove the foreign rows, too.
type– relationship type in the direction from Photo to Comment (many comments)
foreignType– relationship type in the direction from Comment to Photo (one photo for a comment)
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.