In the process of application development, developers are often faced with the problem of database population. A few specific solutions exist for some database systems, but none can be used on top of the object-relational mapping. Thanks to YAML and the sfPropelData object, symfony can automatically transfer data from a text source to a database. Although writing a text file source for data may seem like more work than entering the records by hand using a CRUD interface, it will save you time in the long run. You will find this feature very useful for automatically storing and populating the test data for your application.
16.4.1. Fixture File Syntax
Symfony can read data files that follow a very simple YAML syntax, provided that they are located under the data/fixtures/ directory. Fixture files are organized by class, each class section being introduced by the class name as a header. For each class, records labeled with a unique string are defined by a set of fieldname: value pairs. Listing 16-16 shows an example of a data file for database population.
Listing 16-16 - Sample Fixture File, in data/fixtures/import_data.yml
Article: ## Insert records in the blog_article table first_post: ## First record label title: My first memories content: | For a long time I used to go to bed early. Sometimes, when I had put out my candle, my eyes would close so quickly that I had not even time to say "I'm going to sleep". second_post: ## Second record label title: Things got worse content: | Sometimes he hoped that she would die, painlessly, in some accident, she who was out of doors in the streets, crossing busy thoroughfares, from morning to night.
Symfony translates the column keys into setter methods by using a camelCase converter (setTitle(), setContent()). This means that you can define a password key even if the actual table doesn't have a password field — just define a setPassword() method in the User object, and you can populate other columns based on the password (for instance, a hashed version of the password).
The primary key column doesn't need to be defined. Since it is an auto-increment field, the database layer knows how to determine it.
The created_at columns don't need to be set either, because symfony knows that fields named that way must be set to the current system time when created.
16.4.2. Launching the Import
The propel:data-load task imports data from YAML files to a database. The connection settings come from the databases.yml file, and therefore need an application name to run. Optionally, you can specify an environment name by adding a — env option (dev by default).
> php symfony propel:data-load — env=prod frontend
This command reads all the YAML fixture files from the data/fixtures/ directory and inserts the records into the database. By default, it replaces the existing database content, but if you add an --append option, the command will not erase the current data.
> php symfony propel:data-load --append frontend
You can specify another fixture directory in the call. In this case, add a path relative to the project directory.
> php symfony propel:data-load frontend --dir[]=data/myfixtures
16.4.3. Using Linked Tables
You now know how to add records to a single table, but how do you add records with foreign keys to another table? Since the primary key is not included in the fixtures data, you need an alternative way to relate records to one another.
Let's return to the example in Chapter 8, where a blog_article table is linked to a blog_comment table, as shown in Figure 16-8.
Figure 16.8 A sample database relational model
This is where the labels given to the records become really useful. To add a Comment field to the first_post article, you simply need to append the lines shown in Listing 16-17 to the import_data.yml data file.
Listing 16-17 - Adding a Record to a Related Table, in data/fixtures/import_data.yml
Comment: first_comment: article_id: first_post author: Anonymous content: Your prose is too verbose. Write shorter sentences.
The propel:data-load task will recognize the label that you gave to an article previously in import_data.yml, and grab the primary key of the corresponding Article record to set the article_id field. You don't even see the IDs of the records; you just link them by their labels — it couldn't be simpler.
The only constraint for linked records is that the objects called in a foreign key must be defined earlier in the file; that is, as you would do if you defined them one by one. The data files are parsed from the top to the bottom, and the order in which the records are written is important.
New in symfony 1.1: This also works for many-to-many relationships, where two classes are related through a third class. For instance, an Article can have many Authors, and an Author can have many Articles. You usually use an ArticleAuthor class for that, corresponding to an article_author table with an article_id and an author_id columns. Listing 16-18 shows how to write a fixture file to define many-to-many relationships with this model. Notice the plural table name used here — this is what triggers the search for a middle class.
Listing 16-18 - Adding a Record to a Table Related by a Many-to-Many relationship, in data/fixtures/import_data.yml
Author: first_author: name: John Doe article_authors: [first_post, second_post]
One data file can contain declarations of several classes. But if you need to insert a lot of data for many different tables, your fixture file might get too long to be easily manipulated.
The propel:data-load task parses all the files it finds in the fixtures/ directory, so nothing prevents you from splitting a YAML fixture file into smaller pieces. The important thing to keep in mind is that foreign keys impose a processing order for the tables. To make sure that they are parsed in the correct order, prefix the files with an ordinal number.
100_article_import_data.yml 200_comment_import_data.yml 300_rating_import_data.yml