Chapter 8 - Inside The Model Layer

Much of the discussion so far has been devoted to building pages, and processing requests and responses. But the business logic of a web application relies mostly on its data model. Symfony's default model component is based on an object/relational mapping layer known as the Propel project (http://propel.phpdb.org/). In a symfony application, you access data stored in a database and modify it through objects; you never address the database explicitly. This maintains a high level of abstraction and portability.

This chapter explains how to create an object data model, and the way to access and modify the data in Propel. It also demonstrates the integration of Propel in Symfony.

Why Use an ORM and an Abstraction Layer?

Databases are relational. PHP 5 and symfony are object-oriented. In order to most effectively access the database in an object-oriented context, an interface translating the object logic to the relational logic is required. As explained in Chapter 1, this interface is called an object-relational mapping (ORM), and it is made up of objects that give access to data and keep business rules within themselves.

The main benefit of an ORM is reusability, allowing the methods of a data object to be called from various parts of the application, even from different applications. The ORM layer also encapsulates the data logic--for instance, the calculation of a forum user rating based on how many contributions were made and how popular these contributions are. When a page needs to display such a user rating, it simply calls a method of the data model, without worrying about the details of the calculation. If the calculation changes afterwards, you will just need to modify the rating method in the model, leaving the rest of the application unchanged.

Using objects instead of records, and classes instead of tables, has another benefit: They allow you to add new accessors to your objects that don't necessarily match a column in a table. For instance, if you have a table called client with two fields named first_name and last_name, you might like to be able to require just a Name. In an object-oriented world, it is as easy as adding a new accessor method to the Client class, as in Listing 8-1. From the application point of view, there is no difference between the FirstName, LastName, and Name attributes of the Client class. Only the class itself can determine which attributes correspond to a database column.

Listing 8-1 - Accessors Mask the Actual Table Structure in a Model Class

public function getName()
{
  return $this->getFirstName().' '.$this->getLastName();
}

All the repeated data-access functions and the business logic of the data itself can be kept in such objects. Suppose you have a ShoppingCart class in which you keep Items (which are objects). To get the full amount of the shopping cart for the checkout, write a custom method to encapsulate the actual calculation, as shown in Listing 8-2.

Listing 8-2 - Accessors Mask the Data Logic

public function getTotal()
{
  $total = 0;
  foreach ($this->getItems() as $item)
  {
    $total += $item->getPrice() * $item->getQuantity();
  }
 
  return $total;
}

There is another important point to consider when building data-access procedures: Database vendors use different SQL syntax variants. Switching to another database management system (DBMS) forces you to rewrite part of the SQL queries that were designed for the previous one. If you build your queries using a database-independent syntax, and leave the actual SQL translation to a third-party component, you can switch database systems without pain. This is the goal of the database abstraction layer. It forces you to use a specific syntax for queries, and does the dirty job of conforming to the DBMS particulars and optimizing the SQL code.

The main benefit of an abstraction layer is portability, because it makes switching to another database possible, even in the middle of a project. Suppose that you need to write a quick prototype for an application, but the client hasn't decided yet which database system would best suit his needs. You can start building your application with SQLite, for instance, and switch to MySQL, PostgreSQL, or Oracle when the client is ready to decide. Just change one line in a configuration file, and it works.

Symfony uses Propel as the ORM, and Propel uses Creole for database abstraction. These two third-party components, both developed by the Propel team, are seamlessly integrated into symfony, and you can consider them as part of the framework. Their syntax and conventions, described in this chapter, were adapted so that they differ from the symfony ones as little as possible.

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.

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.

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.

Model Classes

The schema is used to build the model classes of the ORM layer. To save execution time, these classes are generated with a command-line task called propel-build-model.

> symfony propel-build-model

Typing this command will launch the analysis of the schema and the generation of base data model classes in the lib/model/om/ directory of your project:

• BaseArticle.php
• BaseArticlePeer.php
• BaseComment.php
• BaseCommentPeer.php

In addition, the actual data model classes will be created in lib/model/:

• Article.php
• ArticlePeer.php
• Comment.php
• CommentPeer.php

You defined only two tables, and you end up with eight files. There is nothing wrong, but it deserves some explanation.

Base and Custom Classes

Why keep two versions of the data object model in two different directories?

You will probably need to add custom methods and properties to the model objects (think about the getName() method in Listing 8-1). But as your project develops, you will also add tables or columns. Whenever you change the schema.yml file, you need to regenerate the object model classes by making a new call to propel-build-model. If your custom methods were written in the classes actually generated, they would be erased after each generation.

The Base classes kept in the lib/model/om/ directory are the ones directly generated from the schema. You should never modify them, since every new build of the model will completely erase these files.

On the other hand, the custom object classes, kept in the lib/model/ directory, actually inherit from the Base ones. When the propel-build-model task is called on an existing model, these classes are not modified. So this is where you can add custom methods.

Listing 8-4 presents an example of a custom model class as created by the first call to the propel-build-model task.

Listing 8-4 - Sample Model Class File, in lib/model/Article.php

class Article extends BaseArticle
{
}

It inherits all the methods of the BaseArticle class, but a modification in the schema will not affect it.

The mechanism of custom classes extending base classes allows you to start coding, even without knowing the final relational model of your database. The related file structure makes the model both customizable and evolutionary.

Object and Peer Classes

Article and Comment are object classes that represent a record in the database. They give access to the columns of a record and to related records. This means that you will be able to know the title of an article by calling a method of an Article object, as in the example shown in Listing 8-5.

Listing 8-5 - Getters for Record Columns Are Available in the Object Class

$article = new Article();
...
$title = $article->getTitle();

ArticlePeer and CommentPeer are peer classes; that is, classes that contain static methods to operate on the tables. They provide a way to retrieve records from the tables. Their methods usually return an object or a collection of objects of the related object class, as shown in Listing 8-6.

Listing 8-6 - Static Methods to Retrieve Records Are Available in the Peer Class

$articles = ArticlePeer::retrieveByPks(array(123, 124, 125));
// $articles is an array of objects of class Article

So combining object and peer classes in a base and a custom version results in four classes generated per table described in the schema. In fact, there is a fifth class created in the lib/model/map/ directory, which contains metadata information about the table that is needed for the runtime environment. But as you will probably never change this class, you can forget about it.

Accessing Data

In symfony, your data is accessed through objects. If you are used to the relational model and using SQL to retrieve and alter your data, the object model methods will likely look complicated. But once you've tasted the power of object orientation for data access, you will probably like it a lot.

But first, let's make sure we share the same vocabulary. Relational and object data model use similar concepts, but they each have their own nomenclature:

Retrieving the Column Value

When symfony builds the model, it creates one base object class for each of the tables defined in the schema.yml. Each of these classes comes with default constructors, accessors, and mutators based on the column definitions: The new, getXXX(), and setXXX() methods help to create objects and give access to the object properties, as shown in Listing 8-7.

Listing 8-7 - Generated Object Class Methods

$article = new Article();
$article->setTitle('My first article');
$article->setContent('This is my very first article.\n Hope you enjoy it!');
 
$title   = $article->getTitle();
$content = $article->getContent();

To set several fields at one time, you can use the fromArray() method, also generated for each object class, as shown in Listing 8-8.

Listing 8-8 - The fromArray() Method Is a Multiple Setter

$article->fromArray(array(
  'title'   => 'My first article',
  'content' => 'This is my very first article.\n Hope you enjoy it!',
));

Retrieving Related Records

The article_id column in the blog_comment table implicitly defines a foreign key to the blog_article table. Each comment is related to one article, and one article can have many comments. The generated classes contain five methods translating this relationship in an object-oriented way, as follows:

• $comment->getArticle(): To get the related Article object
• $comment->getArticleId(): To get the ID of the related Article object
• $comment->setArticle($article): To define the related Article object
• $comment->setArticleId($id): To define the related Article object from an ID
• $article->getComments(): To get the related Comment objects

The getArticleId() and setArticleId() methods show that you can consider the article_id column as a regular column and set the relationships by hand, but they are not very interesting. The benefit of the object-oriented approach is much more apparent in the three other methods. Listing 8-9 shows how to use the generated setters.

Listing 8-9 - Foreign Keys Are Translated into a Special Setter

$comment = new Comment();
$comment->setAuthor('Steve');
$comment->setContent('Gee, dude, you rock: best article ever!');
 
// Attach this comment to the previous $article object
$comment->setArticle($article);
 
// Alternative syntax
// Only makes sense if the object is already saved in the database
$comment->setArticleId($article->getId());

Listing 8-10 shows how to use the generated getters. It also demonstrates how to chain method calls on model objects.

Listing 8-10 - Foreign Keys Are Translated into Special Getters

// Many to one relationship
echo $comment->getArticle()->getTitle();
 => My first article
echo $comment->getArticle()->getContent();
 => This is my very first article.
    Hope you enjoy it!
 
// One to many relationship
$comments = $article->getComments();

The getArticle() method returns an object of class Article, which benefits from the getTitle() accessor. This is much better than doing the join yourself, which may take a few lines of code (starting from the $comment->getArticleId() call).

The $comments variable in Listing 8-10 contains an array of objects of class Comment. You can display the first one with $comments[0] or iterate through the collection with foreach ($comments as $comment).

Saving and Deleting Data

By calling the new constructor, you created a new object, but not an actual record in the blog_article table. Modifying the object has no effect on the database either. In order to save the data into the database, you need to call the save() method of the object.

$article->save();

The ORM is smart enough to detect relationships between objects, so saving the $article object also saves the related $comment object. It also knows if the saved object has an existing counterpart in the database, so the call to save() is sometimes translated in SQL by an INSERT, and sometimes by an UPDATE. The primary key is automatically set by the save() method, so after saving, you can retrieve the new primary key with $article->getId().

If you read comments to your articles, you might change your mind about the interest of publishing on the Internet. And if you don't appreciate the irony of article reviewers, you can easily delete the comments with the delete() method, as shown in Listing 8-11.

Listing 8-11 - Delete Records from the Database with the delete()Method on the Related Object

foreach ($article->getComments() as $comment)
{
  $comment->delete();
}

Retrieving Records by Primary Key

If you know the primary key of a particular record, use the retrieveByPk() class method of the peer class to get the related object.

$article = ArticlePeer::retrieveByPk(7);

The schema.yml file defines the id field as the primary key of the blog_article table, so this statement will actually return the article that has id 7. As you used the primary key, you know that only one record will be returned; the $article variable contains an object of class Article.

In some cases, a primary key may consist of more than one column. In those cases, the retrieveByPK() method accepts multiple parameters, one for each primary key column.

You can also select multiple objects based on their primary keys, by calling the generated retrieveByPKs() method, which expects an array of primary keys as a parameter.

Retrieving Records with Criteria

When you want to retrieve more than one record, you need to call the doSelect() method of the peer class corresponding to the objects you want to retrieve. For instance, to retrieve objects of class Article, call ArticlePeer::doSelect().

The first parameter of the doSelect() method is an object of class Criteria, which is a simple query definition class defined without SQL for the sake of database abstraction.

An empty Criteria returns all the objects of the class. For instance, the code shown in Listing 8-12 retrieves all the articles.

Listing 8-12 - Retrieving Records by Criteria with doSelect()--Empty Criteria

$c = new Criteria();
$articles = ArticlePeer::doSelect($c);
 
// Will result in the following SQL query
SELECT blog_article.ID, blog_article.TITLE, blog_article.CONTENT,
       blog_article.CREATED_AT
FROM   blog_article;

For a more complex object selection, you need an equivalent of the WHERE, ORDER BY, GROUP BY, and other SQL statements. The Criteria object has methods and parameters for all these conditions. For example, to get all comments written by Steve, ordered by date, build a Criteria as shown in Listing 8-13.

Listing 8-13 - Retrieving Records by Criteria with doSelect()--Criteria with Conditions

$c = new Criteria();
$c->add(CommentPeer::AUTHOR, 'Steve');
$c->addAscendingOrderByColumn(CommentPeer::CREATED_AT);
$comments = CommentPeer::doSelect($c);
 
// Will result in the following SQL query
SELECT blog_comment.ARTICLE_ID, blog_comment.AUTHOR, blog_comment.CONTENT,
       blog_comment.CREATED_AT
FROM   blog_comment
WHERE  blog_comment.author = 'Steve'
ORDER BY blog_comment.CREATED_AT ASC;

The class constants passed as parameters to the add() methods refer to the property names. They are named after the capitalized version of the column names. For instance, to address the content column of the blog_article table, use the ArticlePeer::CONTENT class constant.

Table 8-1 compares the SQL syntax with the Criteria object syntax.

Table 8-1 - SQL and Criteria Object Syntax

Listing 8-14 shows another example of Criteria with multiple conditions. It retrieves all the comments by Steve on articles containing the word "enjoy," ordered by date.

Listing 8-14 - Another Example of Retrieving Records by Criteria with doSelect()--Criteria with Conditions

$c = new Criteria();
$c->add(CommentPeer::AUTHOR, 'Steve');
$c->addJoin(CommentPeer::ARTICLE_ID, ArticlePeer::ID);
$c->add(ArticlePeer::CONTENT, '%enjoy%', Criteria::LIKE);
$c->addAscendingOrderByColumn(CommentPeer::CREATED_AT);
$comments = CommentPeer::doSelect($c);
 
// Will result in the following SQL query
SELECT blog_comment.ID, blog_comment.ARTICLE_ID, blog_comment.AUTHOR,
       blog_comment.CONTENT, blog_comment.CREATED_AT
FROM   blog_comment, blog_article
WHERE  blog_comment.AUTHOR = 'Steve'
       AND blog_article.CONTENT LIKE '%enjoy%'
       AND blog_comment.ARTICLE_ID = blog_article.ID
ORDER BY blog_comment.CREATED_AT ASC

Just as SQL is a simple language that allows you to build very complex queries, the Criteria object can handle conditions with any level of complexity. But since many developers think first in SQL before translating a condition into object-oriented logic, the Criteria object may be difficult to comprehend at first. The best way to understand it is to learn from examples and sample applications. The symfony project website, for instance, is full of Criteria building examples that will enlighten you in many ways.

In addition to the doSelect() method, every peer class has a doCount() method, which simply counts the number of records satisfying the criteria passed as a parameter and returns the count as an integer. As there is no object to return, the hydrating process doesn't occur in this case, and the doCount() method is faster than doSelect().

The peer classes also provide doDelete(), doInsert(), and doUpdate() methods, which all expect a Criteria as a parameter. These methods allow you to issue DELETE, INSERT, and UPDATE queries to your database. Check the generated peer classes in your model for more details on these Propel methods.

Finally, if you just want the first object returned, replace doSelect() with a doSelectOne() call. This may be the case when you know that a Criteria will return only one result, and the advantage is that this method returns an object rather than an array of objects.

Using Raw SQL Queries

Sometimes, you don't want to retrieve objects, but want to get only synthetic results calculated by the database. For instance, to get the latest creation date of all articles, it doesn't make sense to retrieve all the articles and to loop on the array. You will prefer to ask the database to return only the result, because it will skip the object hydrating process.

On the other hand, you don't want to call the PHP commands for database management directly, because then you would lose the benefit of database abstraction. This means that you need to bypass the ORM (Propel) but not the database abstraction (Creole).

Querying the database with Creole requires that you do the following:

1. Get a database connection.
2. Build a query string.
3. Create a statement out of it.
4. Iterate on the result set that results from the statement execution.

If this looks like gibberish to you, the code in Listing 8-15 will probably be more explicit.

Listing 8-15 - Custom SQL Query with Creole

$connection = Propel::getConnection();
$query = 'SELECT MAX(?) AS max FROM ?';
$statement = $connection->prepareStatement($query);
$statement->setString(1, ArticlePeer::CREATED_AT);
$statement->setString(2, ArticlePeer::TABLE_NAME);
$resultset = $statement->executeQuery();
$resultset->next();
$max = $resultset->getInt('max');

Just like Propel selections, Creole queries are tricky when you first start using them. Once again, examples from existing applications and tutorials will show you the right way.

Using Special Date Columns

Usually, when a table has a column called created_at, it is used to store a timestamp of the date when the record was created. The same applies to updated_at columns, which are to be updated each time the record itself is updated, to the value of the current time.

The good news is that symfony will recognize the names of these columns and handle their updates for you. You don't need to manually set the created_at and updated_at columns; they will automatically be updated, as shown in Listing 8-16. The same applies for columns named created_on and updated_on.

Listing 8-16 - created_at and updated_at Columns Are Dealt with Automatically

$comment = new Comment();
$comment->setAuthor('Steve');
$comment->save();
 
// Show the creation date
echo $comment->getCreatedAt();
  => [date of the database INSERT operation]

Additionally, the getters for date columns accept a date format as an argument:

echo $comment->getCreatedAt('Y-m-d');

public function executeShowPopularArticlesForTag()
{
  $tag = TagPeer::retrieveByName($this->getRequestParameter('tag'));
  $this->forward404Unless($tag);
  $this->articles = $tag->getPopularArticles(10);
}

Database Connections

The data model is independent from the database used, but you will definitely use a database. The minimum information required by symfony to send requests to the project database is the name, the access codes, and the type of database. These connection settings should be entered in the databases.yml file located in the config/ directory. Listing 8-17 shows an example of such a file.

Listing 8-17 - Sample Database Connection Settings, in myproject/config/databases.yml

prod:
  propel:
    param:
      hostspec:           mydataserver
      username:           myusername
      password:           xxxxxxxxxx
 
all:
  propel:
    class:                sfPropelDatabase
    param:
      phptype:            mysql     # Database vendor
      hostspec:           localhost
      database:           blog
      username:           login
      password:           passwd
      port:               80
      encoding:           utf8      # Default charset for table creation
      persistent:         true      # Use persistent connections

The connection settings are environment-dependent. You can define distinct settings for the prod, dev, and test environments, or any other environment in your application. This configuration can also be overridden per application, by setting different values in an application-specific file, such as in apps/myapp/config/databases.yml. For instance, you can use this approach to have different security policies for a front-end and a back-end application, and define several database users with different privileges in your database to handle this.

For each environment, you can define many connections. Each connection refers to a schema being labeled with the same name. In the example in Listing 8-17, the propel connection refers to the propel schema in Listing 8-3.

The permitted values of the phptype parameter are the ones of the database systems supported by Creole:

• mysql
• mssql
• pgsql
• sqlite
• oracle

hostspec, database, username, and password are the usual database connection settings. They can also be written in a shorter way as a data source name (DSN). Listing 8-18 is equivalent to the all: section of Listing 8-17.

Listing 8-18 - Shorthand Database Connection Settings

all:
  propel:
    class:          sfPropelDatabase
    param:
      dsn:          mysql://login:passwd@localhost/blog

If you use a SQLite database, the hostspec parameter must be set to the path of the database file. For instance, if you keep your blog database in data/blog.db, the databases.yml file will look like Listing 8-19.

Listing 8-19 - Database Connection Settings for SQLite Use a File Path As Host

all:
  propel:
    class:          sfPropelDatabase
      param:
        phptype:  sqlite
        database: %SF_DATA_DIR%/blog.db

Extending the Model

The generated model methods are great but often not sufficient. As soon as you implement your own business logic, you need to extend it, either by adding new methods or by overriding existing ones.

Adding New Methods

You can add new methods to the empty model classes generated in the lib/model/ directory. Use $this to call methods of the current object, and use self:: to call static methods of the current class. Remember that the custom classes inherit methods from the Base classes located in the lib/model/om/ directory.

For instance, for the Article object generated based on Listing 8-3, you can add a magic __toString() method so that echoing an object of class Article displays its title, as shown in Listing 8-20.

Listing 8-20 - Customizing the Model, in lib/model/Article.php

class Article extends BaseArticle
{
  public function __toString()
  {
    return $this->getTitle();  // getTitle() is inherited from BaseArticle
  }
}

You can also extend the peer classes--for instance, to add a method to retrieve all articles ordered by creation date, as shown in Listing 8-21.

Listing 8-21 - Customizing the Model, in lib/model/ArticlePeer.php

class ArticlePeer extends BaseArticlePeer
{
  public static function getAllOrderedByDate()
  {
    $c = new Criteria();
    $c->addAscendingOrderByColumn(self::CREATED_AT);
    return self::doSelect($c);
 
  }
}

The new methods are available in the same way as the generated ones, as shown in Listing 8-22.

Listing 8-22 - Using Custom Model Methods Is Like Using the Generated Methods

foreach (ArticlePeer::getAllOrderedByDate() as $article)
{
  echo $article;      // Will call the magic __toString() method
}

Overriding Existing Methods

If some of the generated methods in the Base classes don't fit your requirements, you can still override them in the custom classes. Just make sure that you use the same method signature (that is, the same number of arguments).

For instance, the $article->getComments() method returns an array of Comment objects, in no particular order. If you want to have the results ordered by creation date, with the latest comment coming first, then override the getComments() method, as shown in Listing 8-23. Be aware that the original getComments() method (found in lib/model/om/BaseArticle.php) expects a criteria value and a connection value as parameters, so your function must do the same.

Listing 8-23 - Overriding Existing Model Methods, in lib/model/Article.php

public function getComments($criteria = null, $con = null)
{
  if (is_null($criteria))
  {
    $criteria = new Criteria();
  }
  else
  {
    // Objects are passed by reference in PHP5, so to avoid modifying the original, you must clone it
    $criteria = clone $criteria;
  }
  $criteria->addDescendingOrderByColumn(CommentPeer::CREATED_AT);
 
  return parent::getComments($criteria, $con);
}

The custom method eventually calls the one of the parent Base class, and that's good practice. However, you can completely bypass it and return the result you want.

Using Model Behaviors

Some model modifications are generic and can be reused. For instance, methods to make a model object sortable and an optimistic lock to prevent conflicts between concurrent object saving are generic extensions that can be added to many classes.

Symfony packages these extensions into behaviors. Behaviors are external classes that provide additional methods to model classes. The model classes already contain hooks, and symfony knows how to extend them by way of sfMixer (see Chapter 17 for details).

To enable behaviors in your model classes, you must modify one setting in the config/propel.ini file:

propel.builder.AddBehaviors = true     // Default value is false

There is no behavior bundled by default in symfony, but they can be installed via plug-ins. Once a behavior plug-in is installed, you can assign the behavior to a class with a single line. For instance, if you install the sfPropelParanoidBehaviorPlugin in your application, you can extend an Article class with this behavior by adding the following at the end of the Article.class.php:

sfPropelBehavior::add('Article', array(
  'paranoid' => array('column' => 'deleted_at')
));

After rebuilding the model, deleted Article objects will remain in the database, invisible to the queries using the ORM, unless you temporarily disable the behavior with sfPropelParanoidBehavior::disable().

Check the list of symfony plug-ins in the wiki to find behaviors (http://trac.symfony-project.org/wiki/SymfonyPlugins#Behaviors). Each has its own documentation and installation guide.

Extended Schema Syntax

A schema.yml file can be simple, as shown in Listing 8-3. But relational models are often complex. That's why the schema has an extensive syntax able to handle almost every case.

Attributes

Connections and tables can have specific attributes, as shown in Listing 8-24. They are set under an _attributes key.

Listing 8-24 - Attributes for Connections and Tables

propel:
  _attributes:   { noXsd: false, defaultIdMethod: none, package: lib.model }
  blog_article:
    _attributes: { phpName: Article }

You may want your schema to be validated before code generation takes place. To do that, deactivate the noXSD attribute for the connection. The connection also supports the defaultIdMethod attribute. If none is provided, then the database's native method of generating IDs will be used--for example, autoincrement for MySQL, or sequences for PostgreSQL. The other possible value is none.

The package attribute is like a namespace; it determines the path where the generated classes are stored. It defaults to lib/model/, but you can change it to organize your model in subpackages. For instance, if you don't want to mix the core business classes and the classes defining a database-stored statistics engine in the same directory, then define two schemas with lib.model.business and lib.model.stats packages.

You already saw the phpName table attribute, used to set the name of the generated class mapping the table.

Tables that contain localized content (that is, several versions of the content, in a related table, for internationalization) also take two additional attributes (see Chapter 13 for details), as shown in Listing 8-25.

Listing 8-25 - Attributes for i18n Tables

propel:
  blog_article:
    _attributes: { isI18N: true, i18nTable: db_group_i18n }

// In config/business-schema.yml
propel:
  blog_article:
    _attributes: { phpName: Article }
    id:
    title: varchar(50)
 
// In config/stats-schema.yml
propel:
  stats_hit:
    _attributes: { phpName: Hit }
    id:
    resource: varchar(100)
    created_at:

// In config/business-schema.yml
propel:
  blog_article:
    _attributes: { phpName: Article, package: lib.model.business }
    id:
    title: varchar(50)
 
// In config/stats-schema.yml
propel_bis:
  stats_hit:
    _attributes: { phpName: Hit, package: lib.model.stat }
    id:
    resource: varchar(100)
    created_at:

Column Details

The basic syntax gives you two choices: let symfony deduce the column characteristics from its name (by giving an empty value) or define the type with one of the type keywords. Listing 8-26 demonstrates these choices.

Listing 8-26 - Basic Column Attributes

propel:
  blog_article:
    id:                 # Let symfony do the work
    title: varchar(50)  # Specify the type yourself

But you can define much more for a column. If you do, you will need to define column settings as an associative array, as shown in Listing 8-27.

Listing 8-27 - Complex Column Attributes

propel:
  blog_article:
    id:       { type: integer, required: true, primaryKey: true, autoIncrement: true }
    name:     { type: varchar(50), default: foobar, index: true }
    group_id: { type: integer, foreignTable: db_group, foreignReference: id, onDelete: cascade }

The column parameters are as follows:

• type: Column type. The choices are boolean, tinyint, smallint, integer, bigint, double, float, real, decimal, char, varchar(size), longvarchar, date, time, timestamp, bu_date, bu_timestamp, blob, and clob.
• required: Boolean. Set it to true if you want the column to be required.
• size: The size or length of the field for types that support it
• scale: Number of decimal places for use with decimal data type (size must also be specified)
• default: Default value.
• primaryKey: Boolean. Set it to true for primary keys.
• autoIncrement: Boolean. Set it to true for columns of type integer that need to take an auto-incremented value.
• sequence: Sequence name for databases using sequences for autoIncrement columns (for example, PostgreSQL and Oracle).
• index: Boolean. Set it to true if you want a simple index or to unique if you want a unique index to be created on the column.
• foreignTable: A table name, used to create a foreign key to another table.
• foreignReference: The name of the related column if a foreign key is defined via foreignTable.
• onDelete: Determines the action to trigger when a record in a related table is deleted. When set to setnull, the foreign key column is set to null. When set to cascade, the record is deleted. If the database engine doesn't support the set behavior, the ORM emulates it. This is relevant only for columns bearing a foreignTable and a foreignReference.
• isCulture: Boolean. Set it to true for culture columns in localized content tables (see Chapter 13).

Foreign Keys

As an alternative to the foreignTable and foreignReference column attributes, you can add foreign keys under the _foreignKeys: key in a table. The schema in Listing 8-28 will create a foreign key on the user_id column, matching the id column in the blog_user table.

Listing 8-28 - Foreign Key Alternative Syntax

propel:
  blog_article:
    id:
    title:   varchar(50)
    user_id: { type: integer }
    _foreignKeys:
      -
        foreignTable: blog_user
        onDelete:     cascade
        references:
          - { local: user_id, foreign: id }

The alternative syntax is useful for multiple-reference foreign keys and to give foreign keys a name, as shown in Listing 8-29.

Listing 8-29 - Foreign Key Alternative Syntax Applied to Multiple Reference Foreign Key

    _foreignKeys:
      my_foreign_key:
        foreignTable:  db_user
        onDelete:      cascade
        references:
          - { local: user_id, foreign: id }
          - { local: post_id, foreign: id }

Indexes

As an alternative to the index column attribute, you can add indexes under the _indexes: key in a table. If you want to define unique indexes, you must use the _uniques: header instead. For columns that require a size, because they are text columns, the size of the index is specified the same way as the length of the column using parentheses. Listing 8-30 shows the alternative syntax for indexes.

Listing 8-30 - Indexes and Unique Indexes Alternative Syntax

propel:
  blog_article:
    id:
    title:            varchar(50)
    created_at:
    _indexes:
      my_index:       [title(10), user_id]
    _uniques:
      my_other_index: [created_at]

The alternative syntax is useful only for indexes built on more than one column.

Empty Columns

When meeting a column with no value, symfony will do some magic and add a value of its own. See Listing 8-31 for the details added to empty columns.

Listing 8-31 - Column Details Deduced from the Column Name

// Empty columns named id are considered primary keys
id:         { type: integer, required: true, primaryKey: true, autoIncrement: true }

// Empty columns named XXX_id are considered foreign keys
foobar_id:  { type: integer, foreignTable: db_foobar, foreignReference: id }

// Empty columns named created_at, updated at, created_on and updated_on
// are considered dates and automatically take the timestamp type
created_at: { type: timestamp }
updated_at: { type: timestamp }

For foreign keys, symfony will look for a table having the same phpName as the beginning of the column name, and if one is found, it will take this table name as the foreignTable.

I18n Tables

Symfony supports content internationalization in related tables. This means that when you have content subject to internationalization, it is stored in two separate tables: one with the invariable columns and another with the internationalized columns.

In a schema.yml file, all that is implied when you name a table foobar_i18n. For instance, the schema shown in Listing 8-32 will be automatically completed with columns and table attributes to make the internationalized content mechanism work. Internally, symfony will understand it as if it were written like Listing 8-33. Chapter 13 will tell you more about i18n.

Listing 8-32 - Implied i18n Mechanism

propel:
  db_group:
    id:
    created_at:
 
  db_group_i18n:
    name:        varchar(50)

Listing 8-33 - Explicit i18n Mechanism

propel:
  db_group:
    _attributes: { isI18N: true, i18nTable: db_group_i18n }
    id:
    created_at:
 
  db_group_i18n:
    id:       { type: integer, required: true, primaryKey: true,foreignTable: db_group, foreignReference: id, onDelete: cascade }
    culture:  { isCulture: true, type: varchar(7), required: true,primaryKey: true }
    name:     varchar(50)

Beyond the schema.yml: The schema.xml

As a matter of fact, the schema.yml format is internal to symfony. When you call a propel- command, symfony actually translates this file into a generated-schema.xml file, which is the type of file expected by Propel to actually perform tasks on the model.

The schema.xml file contains the same information as its YAML equivalent. For example, Listing 8-3 is converted to the XML file shown in Listing 8-34.

Listing 8-34 - Sample schema.xml, Corresponding to Listing 8-3

<?xml version="1.0" encoding="UTF-8"?>
 <database name="propel" defaultIdMethod="native" noXsd="true" package="lib.model">
    <table name="blog_article" phpName="Article">
      <column name="id" type="integer" required="true" primaryKey="true"autoIncrement="true" />
      <column name="title" type="varchar" size="255" />
      <column name="content" type="longvarchar" />
      <column name="created_at" type="timestamp" />
    </table>
    <table name="blog_comment" phpName="Comment">
      <column name="id" type="integer" required="true" primaryKey="true"autoIncrement="true" />
      <column name="article_id" type="integer" />
      <foreign-key foreignTable="blog_article">
        <reference local="article_id" foreign="id"/>
      </foreign-key>
      <column name="author" type="varchar" size="255" />
      <column name="content" type="longvarchar" />
      <column name="created_at" type="timestamp" />
    </table>
 </database>

The description of the schema.xml format can be found in the documentation and the "Getting Started" sections of the Propel project website (http://propel.phpdb.org/docs/user_guide/chapters/appendices/AppendixB-SchemaReference.html).

The YAML format was designed to keep the schemas simple to read and write, but the trade-off is that the most complex schemas can't be described with a schema.yml file. On the other hand, the XML format allows for full schema description, whatever its complexity, and includes database vendor-specific settings, table inheritance, and so on.

Symfony actually understands schemas written in XML format. So if your schema is too complex for the YAML syntax, if you have an existing XML schema, or if you are already familiar with the Propel XML syntax, you don't have to switch to the symfony YAML syntax. Place your schema.xml in the project config/ directory, build the model, and there you go.

Don't Create the Model Twice

The trade-off of using an ORM is that you must define the data structure twice: once for the database, and once for the object model. Fortunately, symfony offers command-line tools to generate one based on the other, so you can avoid duplicate work.

Building a SQL Database Structure Based on an Existing Schema

If you start your application by writing the schema.yml file, symfony can generate a SQL query that creates the tables directly from the YAML data model. To use the query, go to your root project directory and type this:

> symfony propel-build-sql

A lib.model.schema.sql file will be created in myproject/data/sql/. Note that the generated SQL code will be optimized for the database system defined in the phptype parameter of the propel.ini file.

You can use the schema.sql file directly to build the tables. For instance, in MySQL, type this:

> mysqladmin -u root -p create blog
> mysql -u root -p blog < data/sql/lib.model.schema.sql

The generated SQL is also helpful to rebuild the database in another environment, or to change to another DBMS. If the connection settings are properly defined in your propel.ini, you can even use the symfony propel-insert-sql command to do this automatically.

Generating a YAML Data Model from an Existing Database

Symfony can use the Creole database access layer to generate a schema.yml file from an existing database, thanks to introspection (the capability of databases to determine the structure of the tables on which they are operating). This can be particularly useful when you do reverse-engineering, or if you prefer working on the database before working on the object model.

In order to do this, you need to make sure that the project propel.ini file points to the correct database and contains all connection settings, and then call the propel-build-schema command:

> symfony propel-build-schema

A brand-new schema.yml file built from your database structure is generated in the config/ directory. You can build your model based on this schema.

The schema-generation command is quite powerful and can add a lot of database-dependent information to your schema. As the YAML format doesn't handle this kind of vendor information, you need to generate an XML schema to take advantage of it. You can do this simply by adding an xml argument to the build-schema task:

> symfony propel-build-schema xml

Instead of generating a schema.yml file, this will create a schema.xml file fully compatible with Propel, containing all the vendor information. But be aware that generated XML schemas tend to be quite verbose and difficult to read.

 propel.database.createUrl = mysql://login:passwd@localhost
 propel.database.url       = mysql://login:passwd@localhost/blog

 // Base classes are autoloaded in symfony
 // Set this to true to use include_once statements instead
 // (Small negative impact on performance)
 propel.builder.addIncludes = false

 // Generated classes are not commented by default
 // Set this to true to add comments to Base classes
 // (Small negative impact on performance)
 propel.builder.addComments = false

 // Behaviors are not handled by default
 // Set this to true to be able to handle them
 propel.builder.AddBehaviors = false

 // Identifiers are quoted by default
 // Set to true to disable identifier quoting (e.g. for oracle)
 propel.disableIdentifierQuoting = false

Summary

Symfony uses Propel as the ORM and Creole as the database abstraction layer. It means that you must first describe the relational schema of your database in YAML before generating the object model classes. Then, at runtime, use the methods of the object and peer classes to retrieve information about a record or a set of records. You can override them and extend the model easily by adding methods to the custom classes. The connection settings are defined in a databases.yml file, which can support more than one connection. And the command line contains special tasks to avoid duplicate structure definition.

The model layer is the most complex of the symfony framework. One reason for this complexity is that data manipulation is an intricate matter. The related security issues are crucial for a website and should not be ignored. Another reason is that symfony is more suited for middle- to large-scale applications in an enterprise context. In such applications, the automations provided by the symfony model really represent a gain of time, worth the investment in learning its internals.

So don't hesitate to spend some time testing the model objects and methods to fully understand them. The solidity and scalability of your applications will be a great reward.