Doctrine Tips and Gotchas

Some basic Doctrine tips and links for additional help.

Data indexing is performed internally from code base since it doesn't rely on database indexing unless the transaction or query happens directly on the database level. Symfony's Lucene plugin indexing purely relies on an external binary file generated from passing in the fields into an index generator function. In order to optimize Lucene's query searches, the fields must first be indexed through Lucene's indexing function is happening through frontend coding.

Doctrine does a similar practice with table relations as noted above in the data indexing gotcha. Doctrine query does not allow any joinings of tables if the tables are not defined in the base classes. Thus, leading to unsupporting of subqueries, view accesses, and temporary table creations, and limitation on self referencing fields. These are basic tools used in data management within relational database. These table relation restrictions immensely impact query performances, which in another term, slows down execution time. As an alternative, Doctrine raw dql will be applied in replace of doctrine query where needed. Doctrine raw dql will allow more flexibility for more functional queries. Doctrine is a better choice of data managing than doctrine query under certain conditions. However, there are still limitations with Doctrine raw sql. For instance, union joins of multiple select queries still are not supported.

Other issues, which are mentioned in the Mayon Information Architecture page, are non-supporting of composite foreign keys and surrogate keys practices.

Doctrine will automatically add timestamped created_at and updated_at fields for us when a table model is properly configured in schema.yml. This should save us some time on table creation (especially in YML) and table interaction from the frontend.

You can also disable updated_at if you only want created_at, or vice versa.

  The Timestampable behavior will automatically add a created_at and updated_at column and automatically set the values when a 
  record is inserted and updated.
  Since it is common to want to know the date a post is made lets expand our BlogPost model and add the Timestampable behavior 
  to automatically set these dates for us.

Timestampable behavior can be set globally.

Doctrine documentation: http://www.doctrine-project.org/documentation/manual/1_2/en/behaviors:core-behaviors:timestampable

To open a connection with the database, you just need to instantiate the sfDatabaseManager class, which takes a ProjectConfiguration object as a parameter. When you instantiate sfDatabaseManager, it will read the provided configuration, and pass it on to Doctrine_Manager, which will retain the connection instance for Doctrine requests. Here is how you instantiate the class:

$databaseManager = new sfDatabaseManager($configuration);

And here is the call stack for what happens when you do:

sfDatabaseManager->__construct()
sfDatabaseManager->initialize()
  sfDatabaseManager->loadConfiguration()
    config_databases.yml.php (include)
      sfDoctrineDatabase->__construct() (extends sfDatabase)
        Doctrine_Manager->openConnection()

Versionable is a Doctrine behaviour that tracks record history. It automatically adds a version field (an integer data type) to the table. Each record created starts with version 1 and will be incremented as the record gets updated. By default the field name is labelled version. However, you can label whatever the field name you want it to be in the table. This behavior is particularly helpful when the auditLog attribute is set to true. By turning on auditLog, a version table is automatically generated to capture all record transactions in the original table. The version table is an exact replica of the original table, except the version field is set as part of the primary field. Whenever a new record is created in the original table, it is also created in the version table. As record gets updated in the original table, the updated record is saved as a new record in the version table. The version table is like a log table. With this version table, it allows the user to revert any records on the original table back to any revision the user so chooses.

This behaviour, however, does not work well with Timestampable behavior when Timestampable is set globally in the YML file. In order to use both behaviors, within the YML file they must both be specified directly in the block of the tables that are version. This behavior does not work well nesting in I18N behavior either. This is reported as a bug in doctrine.

Correct way of applying both Timestampable and Versionable to a table.

BlogPost:
  actAs:
    Timestampable: ~
    Versionable:
      versionColumn: version
      className: %CLASS%Version
      auditLog: true
  columns:
    title: string(255)
    body: clob

A major downfall of this behaviour is the delete action. Whenever a record is deleted from the original table, all the history of that record in the version table is also deleted. This defeats the purpose of an audit method. Adding the SoftDelete behaviour and nesting SoftDelete within Versionable does not resolve deleting the history record in the version table.

Doctrine Versionable Behaviour: http://www.doctrine-project.org/projects/orm/1.2/docs/manual/behaviors%3Acore-behaviors%3Aversionable/en

Similar to Timestampable behaviour, it automatically adds a Timestamped deleted_at field to the table. This field does not get populated until the record is deleted. By default, this fields holds a null value until someone deletes the record. In other words, deleting a record does not mean removing the record from table. It only means the deleted_at field is set with the date and time of when the record was asked to be deleted. This behaviour may still require additional code to hide deleted records from showing and prevent updating any deleted records.

Doctrine SoftDelete Behavior: http://www.doctrine-project.org/projects/orm/1.2/docs/manual/behaviors%3Acore-behaviors%3Asoftdelete/en