Symfony Resources Central - propel

Foreign key's onDelete for dummies

Romain Dorgueil — Thu, 10 Jul 2008 23:55:00 +0200

Any modern relational database engine supports the onDelete and onUpdate attributes on foreign keys. Propel and Doctrine of course allows to define it in the schema file (yaml or xml) and I believe it is important to explicitly set it.

Restrict mode

«Better do nothing than something stupid.»

The default behaviour is ON DELETE RESTRICT. This is the most secure setting, so it is normal to have it as default.

If we try to DELETE a record referenced by the current object, the database engine just raise an SQL error. If it was not an error to DELETE this, you will have to start by removing the linked object, or setting the foreign key value to something different.

Cascade mode

«No need to keep obsolete things.»

But our database engines are able to do better. For example, if you have an user's settings table and you delete an user, you may not wan't to keep his settings. You just set ON DELETE CASCADE on user's setting foreign key column referencing the user table, and if a user is ever deleted, all his associated properties will be destroyed.

Along with ON DELETE CASCADE, we usually add a NOT NULL constraint (or required: true), as the existence of current object is directly related to the existence of linked object. This may be wrong from time to time, but it is usually the case.

Set null mode

«We don't rely on this. Just forget it.»

The last behavior is the ON DELETE SET NULL option. It does exactly what it says. If linked object is removed, we set our foreign key value to null.

This the behavior you may want to use for articles having a link to their author for example. If the author is removed from database, we don't want to delete his articles, so we just loose the author information (well ok, this is a simplification, but it basically shows the idea).

Why I think we should explicitly use it

The default database engine behavior is «I don't know how to handle it». Setting it will add power to your database model, while taking profit of your RDBMS features. Not setting it is like saying «I don't know which way I want my database to behave in case we delete a linked object», and that's like saying «I don't know how I want my model to react». Thinking a little to understand which way you want it to work will make you certain you're understanding the behavior you need in your project.

How to set it in your favorite ORM:

schema.yml with Doctrine

Article:
  columns:
    title:   string(255)
    body:    string(4096)
    user_id: integer(4)
  relations:
    User:
      onDelete: CASCADE

PHP model with Doctrine

public function setUp()

{

  $this->hasOne('User', array('local'    => 'user_id',

                              'foreign'  => 'id',

                              'onDelete' => 'CASCADE'));

}

schema.yml with Propel

    data_id:  { type: integer, primaryKey: true, foreignTable: data, foreignReference: id, onDelete: cascade, onUpdate: cascade }

schema.xml with Propel

<foreign-key foreignTable="data" onDelete = "setnull">

    <reference local="data_id" foreign="id" />

  </foreign-key>

Complex relations population in propel

Romain Dorgueil — Thu, 31 Jan 2008 23:47:00 +0100

Since quite a bit, I've been faced with an annoying problem on every projects I use propel on. Propel builders only generates some specific cases selection methods, which consists of pretty ugly copy paste of the same code to populate the objects, and if your needs are not satisfied by the finite little number of propel handled cases, you'll have to either use pure SQL, or write a custom doSelect method. That seems okay at first sight, but it is not. In fact, you're about copypasting the propel generated method, and that's a rude violation of D.R.Y. principle.

I found no solutions during the two last years, but maybe things will change soon with the new sfPropelImpersonatorPlugin. This plugin is aiming at doing arbitrary object population based on informations provided by propel's introspection methods (DatabaseMap/TableMap/ColumnMap) to link populated objects.

The plugin is currently in very early stage, but is working pretty well for my needs, and I'm looking forward to know what others are thinking about it.

Symfony 1.0 is out!

Romain Dorgueil — Mon, 19 Feb 2007 08:13:00 +0100

Despite the DIGG side effects of which symfony project server suffered because of heavy traffic brought by the well known social bookmarking site's homepage anouncing symfony's first "stable" release, the long awaited 1.0 version is here!

For thoose who don't know it, Symfony is a MVC (Model-View-Component) PHP5 framework aiming to Rapid Application Development and good codinig practices like the DRY (Don't Repeat Yourself) principle. Their main contributors, french developpers from Sensio Labs Fabien Potencier and François Zaninotto have written a very good documentation book about it, that you can either buy at amazon (for thoose who like holding a real book), or download/read freely on the symfony project website as a PDF file.

Supported by a large community, you'll find support about symfony in diverse flavour, from the symfony forum to different languages mailing lists, going thru #symfony and #symfony-fr (for french developpers) on Freenode IRC network.

As the official release note is saying: At last, the long-awaited 1.0 stable version of symfony is just released. For all those who waited for the "stable" status to dive into symfony, the time has come.

Some reference: