Symfony Resources Central - database

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.

Using DBMS functions with sfDoctrine

Romain — Tue, 30 Oct 2007 22:11:00 +0100

I recently had a peek on symfony forum, and seen someone asking "How can I make a SELECT count(*) FROM .... with doctrine?". An answer to such a question should be pretty obvious as it's of everyday use, but the question seems to have come to life many times as well on IRC than on the forum/mailing list. Here is my two-cents how-to.

Using builtin DQL aggregate functions

Basically, you can add aggregate functions (functions that operates on a group of records instead of one record, also called GROUP BY functions) to your DQL query the same way you'd do in SQL, provided the fact you're using one of the builtin DQL aggregate functions (COUNT, MAX, MIN, AVG, SUM).

$result = Doctrine_Query::create()

     ->select('COUNT(t.id) cnt')

     ->from('Table t')

     ->execute()

     ->getFirst();



echo $result['cnt'];

Using DBMS specific aggregate functions

If the function you need is in the list, no need to go further. But some functions are DBMS specific, for example MySQL provides a GROUP_CONCAT() function. It would be nonsense for DQL to provide that, as it would not be translatable in some of the other DBMS SQL, but it would also be nonsense to restrict functionalities to the smallest common functionnalities set of every DBMS that Doctrine supports.

The way to go is to change the portablility level of doctrine, to match your needs. As the name implies, you're loosing in portability between the different DBMS, but you're gaining specific functionnalities of yours.

The following code, running in PORTABILITY_ALL mode, will throw a Doctrine_Query_Exception:

Doctrine_Manager::getInstance()->setAttribute(Doctrine::ATTR_PORTABILITY, Doctrine::PORTABILITY_ALL);

    

$result = Doctrine_Query::create()

     ->select('GROUP_CONCAT(t.value) concatedstring')

     ->from('Table t')

     ->execute()

     ->getFirst();

Now remove the Doctrine::PORTABILITY_EXPR bit to this attribute, and you will get the correct result (with mySQL):

Doctrine_Manager::getInstance()->setAttribute(Doctrine::ATTR_PORTABILITY, Doctrine::PORTABILITY_ALL ^ Doctrine::PORTABILITY_EXPR);

    

$result = Doctrine_Query::create()

     ->select('GROUP_CONCAT(t.value) concatedstring')

     ->from('Table t')

     ->execute()

     ->getFirst();



echo $result['concatedstring'];

Extending to other functions

Ok aggregate functions are usefull, but other functions can be very handy too, string functions for example. Doctrine manual tells us that builtin DQL string functions are CONCAT, SUBSTRING, TRIM, LOWER, UPPER, LOCATE and LENGTH.

Doctrine_Manager::getInstance()->setAttribute(Doctrine::ATTR_PORTABILITY, Doctrine::PORTABILITY_ALL);

    

$results = Doctrine_Query::create()

     ->select('LENGTH(t.value) val_len, t.value')

     ->from('Test t')

     ->execute();



foreach ($results as $result)

{

  echo $result['value'] . ' => ' .$result['val_len'] ."\n";

}

This code will display all records' value fields, followed by their character length computed by the DBMS, in PORTABILITY_ALL mode.

Now let's say you want to use BIT_LENGTH MySQL function...

Doctrine_Manager::getInstance()->setAttribute(Doctrine::ATTR_PORTABILITY, Doctrine::PORTABILITY_ALL ^ Doctrine::PORTABILITY_EXPR);

    

$results = Doctrine_Query::create()

     ->select('BIT_LENGTH(t.value) val_len, t.value')

     ->from('Test t')

     ->execute();



foreach ($results as $result)

{

  echo $result['value'] . ' => ' .$result['val_len'] ."\n";

}

This is the way to go again.

No need to say, this extends to every type of functions available in the different DBMS.