Symfony Resources Central - doctrine

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>

Symfony 1.1 is out, and the winner is... 1.2!

Romain Dorgueil — Tue, 01 Jul 2008 23:52:00 +0200

The longly awaited 1.1 version is finally out after long months of development. After doing this, Fabien started the 1.2 branch, and you can take a look at that magnificient revision which for sure opens a new era of symfony developments.

A lot of nice new features are greatly awaited in the next version.

First of all, the admin generator will be completely rewritten, to make a good use of the new form framework. That will for sure open incredible new possibilities and remove the permanent need for hacks to customize your own admin interface (or frontend interface, if you do use admin generation in frontend too). At least, the first one amazed more than one person, and we can be pretty sure that new version will kick asses.

Some people were complaining about Ruby on Rails having a great advantage over symfony, by their deployment tool Capistrano, while symfony only allow to rsync to one server. Hopefully this won't be true for long anymore. For information, Capistrano allows to create real deployment scripts, like "Disable frontend app on this server, make backups of site and database, synchronize, remotely run tests, clear the cache and enable the frontend app". That will easify a lot our projects delivery procedures.

Amongst some other details, the last major point Symfony 1.2 will see is some further decoupling of the technical choices symfony 1.0 gave us, like Propel or Prototype. Prototype and the helpers will still be bundled with symfony, but as a plugin, like Propel is since 1.1. This is very important IMHO, because symfony should never force any technical choice to the teams. But many people complained that helpers was one of the easy and magic things that attract newcomers to symfony. The plugin solution is keeping everyone happy. In the same spirit, symfony 1.2 will bundle the sfDoctrine plugin thanks to Jonathan Wage work to stabilise and manage branches/features.

And for the short term, the unpublished chapters of the form framework online book are still to come too, along with a symfony 1.1 "First project" tutorial.

Long life to symfony :-)

HashBin now available in open-source flavor

Romain Dorgueil — Mon, 17 Mar 2008 23:58:00 +0100

Our first violin missed his plane yesterday, so Kwatuor is still not available in the upcoming unusable buggy pre-alpha (that miss all the functionalities anyway).

But while we're waiting for him to be available, I released HashBin in open-source, so anybody can dive into the code, and help me making it evolve. It still needs many attention, but hey, time is not the most available resource I have, and that's one of the two major reasons to give it to the community. Another one is that there is not so much open source symfony applications, and even less open source doctrine applications. After the doctrine 1.0 feature-freeze announcement, this could be a step to have simple sample applications (I hear little sarcastic laughs in the background...) people could dive in to learn this amazing ORM.

Well stop talking, here is the code.

TRAC web interface: http://trac.dakrazy.net/
SVN DAV repository: http://svn.dakrazy.net/hashbin/trunk/

SVN access is read-only for anyone, if you ever want to contribute, I'll be glad to grant you a commit access either on trunk or branch (still have to make up my mind, but at beginning that's not very important). Just ask me on IRC (hartym@freenode).

What amazing feature will you invent today?

I'd like to hear a Kwatuor play nice symfonic music

Romain Dorgueil — Tue, 26 Feb 2008 23:58:00 +0100

Today's post is a bit special.

This can be took as a question.

Or a call to developpers that have specific blogging needs.

Since quite a moment I used dotclear for blogging. That suit my needs, in some way, but maybe I'm more adaptating my needs to dotclear's capabilities. Maybe you're using wordpress. or any other. And I guess that must be the same for you, thinking for example of integrating one of those all-in-one blogging/cms platforms cleanly in another website, for example, is being more than utopian...

By clean I mean integrating it without having to duplicate the template. Neither having to use all functionalities if you only need a simple article list somewhere... And without being limited if you need to display the headers of those on another website...

I'd better not speak of blog networks communicating, or taking content from RSS/XML/anything feed in a flexible way.

So today I'd like to announce the birth of Kwatuor. Kwatuor is a blog platform project using symfony and doctrine, that will in the near future use doctrine migrations to get content from an existing dotclear/wordpress project, or any other source platform someone has enough need with he'd take the time to write migration classes for.

Templating system will be different. I'm still hesitating between a generator approach (generated partials from a dynamic skeleton, that all can be customizable) and a real proper template system (but still any part would be customizable). Only major difference with blogging platform would be the total forbidding of DRY-breaks a dotclear-like templating system is doing everyday. And this extends to integrated blogs, in wider projects.

In fact I started developing it for my own needs, but I think this is typically the kind of project everybody would need someday. Maybe you hate being jailed in your obscure, yet very good, but specific and... even more obscure blog platform.

So I'm wondering now, if people (you :p) would be interested in this project. If so, I'll make a public SVN/trac next week, so concerned users will be able to give feedback with code in their hands. Would this be usefull to you? Would you contribute to this open-source project? Do you have good (or bad :p) ideas about concepts to take in consideration from the beginning?

Related links

Doctrine Migrations

Hashbin v3 just went to public beta

Romain Dorgueil — Mon, 24 Dec 2007 06:57:00 +0100

I'm proud to annouce that HashBin v3 is out, using the latest improvements to dkGeshi (old sfGeshi, soon public) and the brand new dkAntispamPlugin, which can give a text a note about its probability of being spam, or junk. If everything goes well with hashbin, and after some required (i guess) tuning to the plugin, it will go opensource to let you take advantage of it.

For thoose who never used it, HashBin is a free PasteBin service, a collaborative debugging tool allowing developpers to share source code snippets. Hashbin is powered by the Symfony Framework and PHP Doctrine ORM.

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.