sfDoctrinePlugin

NOTA: O plugin foi movido para uma nova localização no SVN! Veja a seção de downloads.

Descrição

Este plugin permite que usuarios do symfony usem o Doctrine no lugar do propel. Este plugin integra completamente o doctrine no symfony. Você pode usar todas a funcionalidades no symfony com o propel substituido pelo doctrine, graças ao plugin sfDoctrine.

• criação automática da classe modelo de um arquivo schema.yml
• todas as funcionalidades do admin generator
• compatibilidade com templates escritos com o propel
• i18n (trabalha exatamente como no propel)
• altera automaticamente os campos created_at e updated_at
• query logging e timing
• carrega fixtures de yml
• lista dupla, check lists e todos os many2many admin helpers
• object helpers
• uso de conexão no databases.yml
• paginadores

Este está em versão beta. Bug reports no trac do symfony são bem-vindos. Cheque a pagina http://trac.symfony-project.com/wiki/sfDoctrineStatus para informação sobre qual versão está trabalhando bem assim como dicas de como migrar para uma nova versão do sfDoctrine/doctrine.

Installation

Prerequisites

Either symfony 1.0 or a subversion check out of symfony higher than 3525.

Doctrine requires PHP >= 5.1. The newest Doctrine segfaults on occasion with PHP < 5.2.2. It uses PDO which is bundled with PHP by default. Be sure to enable the PDO extension, as well as the database-specific DLL file, in your PHP.ini settings. See http://php.net/pdo.

Download the plugin

sfDoctrinePlugin is now a regular symfony plugin. Please do not install it via the command line. Currently only the subversion method is supported.

Download the plugin from svn by running:

> svn co http://svn.symfony-project.com/plugins/sfDoctrinePlugin/branches/0.1

and place that in the plugins directory of your project. The doctrine library will be automatically downloaded as well.

Update regularly since sfDoctrinePlugin is updated rather frequently:

> svn update

You may also [browser:plugins/sfDoctrinePlugin browse the source code] of sfDoctrine.

Setting the plugin as an svn:externals

To have the plugin updated when you do a svn update run:

> svn propedit svn:externals plugins

and type:

sfDoctrinePlugin  http://svn.symfony-project.com/plugins/sfDoctrinePlugin/branches/0.1

Setup

Copy doctrine.yml

In order to change common doctrine settings (such as turning table autocreation on and off), you'll need to edit doctrine.yml To do this, copy sfDoctrinePlugin/config/doctrine.yml to your project/config folder. This will override the plugin YML, and ensure that your settings arent overwritten when you update the plugin.

Event Listeners

A new syntax is useable to select your global event listeners for connection and records, check the sample file. It will detect which interface is implemented in your class and attach it to the matching component. The default EventListener? in the sample file immitate symfony 1.0/propel created_at/updated_at behaviour.

Database Connections

Setup your database connections in databases.yml. You will need to specify sfDoctrineDatabase as your class as in the example below.

all:
  myConnection:
    class: sfDoctrineDatabase
    param:
      dsn: mysql://user:pass@localhost/mydb

Notice that you may add a "encoding" parameter which will be used for connection with mysql. If no encoding is provided, UTF8 is assumed.

Multiple Connections

If you have declared only one doctrine connection in databases.yml then there is nothing to be done.

If you have several doctrine connections in databases.yml but want to use only one of them, you may set default_database (in config/settings.yml) to the name of that database.

If you have several connections, or if you want to load only some of you schema files, you may use a project configuration file named schemas.yml that resides in config/ in the project directory to map the connections to your various schema files. The syntax is as follows:

connection1: [schema1, schema2]
connection2: [myPlugin/schema3]

Here, schema1.yml and schema2.yml are supposed to be inside config/doctrine/ folder in the project directory. On the other hand, schema3.yml is supposed to be in the plugin directory, namely in the plugins/myPlugin/config/doctrine/ folder.

If no such config/schemas.yml configuration file is found, then all the schemas found under the various config/doctrine folders will be loaded.

Schema yml setup

Advantages over Propel schema setup

• only the connections in databases.yml are used; there is no equivalent to the propel.ini file (thank god)
• you can have foreign keys from one schema pointing to another one; an extreme use of the schemas files would be to have one schema per class

schema.yml syntax

The syntax of the schema files is totally different from the usual schema.yml. You will put the schema doctrine file inside config/doctrine.

Here comes an example from which you should be able to figure out most of the syntax. It models a table of publications.

• inheritance
  1. Column aggregation: publications can be books or articles
  2. Different tables: SuperUser is a subclass of User and its instances are stored in a different table

NOTE: the table creation is broken when you do column aggregation inheritance. Temporary fix it to add the child columns to the parent table definition.

• many to one an article or a book has an author
• many to many books can be reviewed by several users
• relations are inheritance-aware there are no reviews for the articles
• subclasses have proper fields the isbn field exists only for the subclass "book"
• i18n users have a localised "description" field

Notice that the 'id' fields are automatically created by doctrine. Notice also that the i18n culture field and the inheritance key field are automatically created by sfDoctrine.

Types

You may use any doctrine type you like. Take a look at the Doctrine type documentation for the list of available types.

The default column type is string while the default type for foreign keys is integer. Notice also the short syntax type(size).

The enum type is obtained by giving an array in the type field: column: type: [a, b, c].

Sample Schema

User: 
  tableName: user
  i18n: {class: UserI18n, cultureField: culture}
  columns:
    firstName: {columnName: first_name, type: string(100)}
    last_name: {type: string(100)}
    email:     # with a validator 
      type: string(150)
      unique: true
      email: true
    created_at: timestamp #automatically populated
    updated_at: timestamp #automatically populated

UserI18n:
  tableName: user_i18n
  columns:
    description: {type: string, size: 100} # alternate syntax
    another_localised_field: string(100)
    other_one: string(200)

SuperUser:
  tableName: super_user
  inheritance: {extends: User}
  columns:
    password: string(50)

Publication:
  tableName: publication
  columns:
    publication_date: {type: timestamp}
    state: {type: [published, on_hold, reviewed]}
    title: {type: string(100), default: Untitled}
    author_id:
      foreignClass: User
      cascadeDelete: true

# note: column aggregation on table inheritance is currently broken.
#Book:
#  inheritance: {extends: Publication, keyField: class_key, keyValue: 1}
#
##  Alternate syntax
##  inheritance: {extends: Publication, keyFields: {class_key: 1}}
#
##  Alternate syntax (multi-column key)
##  inheritance: {extends: Publication, keyFields: {primary_key: 2, sub_key: 1}}
#
#  columns:
#    isbn:  {type: integer(10), primary: true}
    
#Article:
#  inheritance: {extends: Publication, keyField: class_key, keyValue: 2}
#  columns:
#    journalName: {type: string(50), columnName: journal_name}

BookReview:
  tableName: book_reviews
  columns:
    review: string #no length param will default to max size allowed by db
    reviewed_book_id:
      foreignClass: Publication
      localName: reviewers
      counterpart: reviewers_id # counterpart is used for a hasOne relationship
    reviewers_id:
      foreignClass: User
      localName: reviewed_books
      counterpart: reviewed_book_id # see above counterpart

Specifying indexes in schema

User: 
  tableName: user
  indexes:
    user_email_idx:
      fields: [ email ]
    user_name_idx:
      fields: [ first_name, last_name ]
  columns:
    firstName: {columnName: first_name, type: string(100)}
    last_name: {type: string(100)}
    email:     # with a validator 
      type: string(150)
      unique: true
      email: true

#  Will produce the following index:
#  $this->index('user_email_idx', array ('fields' => array (0 => 'email'),));
#  $this->index('user_name_idx', array ('fields' => array (0 => 'first_name', 1=> 'last_name'),));

Build the Model

Run the following command in a terminal:

symfony doctrine-build-model

then be sure to clear the cache to avoid all manner of errors and warnings!

symfony cc

Table Creation

note: the below tasks arent fully functional yet. (examples needed, also.) Table auto-creation has been removed from Doctrine, so that foreign keys are now properly supported! Tables (and foreign keys and indexes) are created with:

symfony doctrine-insert-sql

If you wish to simply generate the insert sql, you can use:

symfony doctrine-build-sql

Loading Data

Here are the data loading instructions?.

Doctrine Validators

note: link to new doctrine manpage on validators needed, once this info has been added

All custom doctrine validators are supported by the sfDoctrine's yaml schema, as demonstrated by the User.email column in the sample schema. More is available in the Validators section of the Doctrine manual.

Pake Tasks

*doctrine-build-all         > doctrine build all - generate model and initialize database
*doctrine-build-all-load    > doctrine build all load - generate model, initialize database, and load data from fixtures
  doctrine-build-db          > doctrine build database - initialize database
  doctrine-build-model       > build Doctrine classes
*doctrine-build-schema      > doctrine build schema - build schema from an existing database
  doctrine-build-sql         > exports doctrine schemas to sql
  doctrine-drop-all-tables   > doctrine drop all - drop all database tables
  doctrine-drop-db           > doctrine drop database - drops database
*doctrine-dump-data         > dump data to yaml fixtures file
  doctrine-generate-crud     > Creates Doctrine CRUD Module
  doctrine-import            > converts propel schema.*ml into doctrine schema
  doctrine-init-admin        > initialize a new doctrine admin module
  doctrine-insert-sql        > insert sql for doctrine schemas in to database
*doctrine-load-data         > load data from yaml fixtures file
  doctrine-load-nested-set   > load doctrine nested set data from nested set fixtures file

Items with * to the left of them are not fully functional

doctrine-build-all           > Not sure what causes this to not work, but the individual pake tasks work when running them individually
doctrine-build-all-load > Broken for same reason as above
doctrine-build-schema > Method shell implemented, but it doesn't have any code to do the task 
doctrine-load-data         > A bug in Doctrine causes the loading of the data to not work because we need the ability to create blank records. http://phpdoctrine.net/trac/ticket/378
doctrine-dump-data      > Has issues with dumping data from complex relationships

Usage

Doctrine Query Examples

Simple Queries

<?php

// start a new query
$q = new Doctrine_Query();
// or
$q = Doctrine_Query::create()->etc...

// find some stuff
$stuff = $q->select('foo.baz')->from('myClass foo')->where('foo.bar = ?', 3)->execute();

echo $stuff[0]['baz'];

// select only some columns
$q->select('u.first_name, u.last_name')->from('User u')->execute();

// multiple where conditions (note array value syntax)
$stuff = $q->from('myClass foo')->where('foo.bar = ? AND foo.wee = ?', array(3, 'wee')->execute();
$stuff = $q->from('myClass foo')->where('foo.bar = ? OR foo.wee = ? AND foo.doh = ?', array(3, 'wee', 'doh')->execute();

// using getFirst() will only return 1 entry 
$stuff = $q->select('foo.baz')->from('myClass foo')->where('foo.bar = ?', 3)->execute()->getFirst();

echo $stuff['baz']; //no array offset needed because of getFirst()

// using orderBy(), default is ascending(ASC)
$q->from('User u')->where('u.dept_id = ?', $foo)->orderBy('u.last_name DESC')->execute();

// using groupBy()
$q->from('User u')->where('u.dept_id = ?', $foo)->groupBy('u.first_name')->execute();


// break up the query into multiple lines
$q->select('foo.doh')->from('myClass foo');
$q->where('foo.bar = ?', $bar);
$q->execute();

// extending the where clause in a separate line
$q->where('foo.bar = ?', $bar);
$q->addWhere('foo.doh = ?', $doh);
$q->execute();


// Create new record
$record = new MyRecord(); // MyRecord extends Doctrine_Record
$record->set('key', 'value');
$record->save(); // This will create a new entry in the table used by the MyRecord class

// using FETCH_ARRAY: retrieve collection as an associative array (column => value)
//note that additional objects get their own nested arrays
$results = $q->select('b.foo, b.bar, b.blah, a.heh')->from('Baz b')->innerJoin('b.Argh a')->execute(array(), Doctrine::FETCH_ARRAY);
print_r($results);
//results:
Array
(
  0 =>
    Array
    (
      [foo] => wee
      [bar] => argh 
      [blah] => pfeh
      [Argh] =>
        Array
        (
          [heh] => george
        )
    )
)

// sfDoctrine does support transactions
$conn = Doctrine_Manager::connection();
$conn->beginTransaction();

 // queries

$conn->commit();

Update/Delete

<?php

// update
$q->update('MyClass foo')->set('foo.bar', 33)->where('foo.baz = ?', 'doh')->execute();

// update (escaped)
$q->update('MyClass foo')->set('foo.bar', '?','bar')->where('foo.baz = ?', 'doh')->execute();

// delete
$item = $q->from('myClass foo')->where('foo.id = ?', $id)->execute();
$item->delete();

From/Joins

note that the array accessor syntax is recommended:

echo $myrecord['myfield'];

This allows seamless transition to the much-faster Fetch_Array syntax. Of course, if the relations arent already loaded (ie, you want to use lazy loading), this syntax won't work.

<?php

// doSelectJoinAuthor() and doSelectJoinEditor() (impossible to do with propel)
$q->from('Article a LEFT JOIN a.Author au LEFT JOIN a.Editor e')->execute();

// this is the recommended syntax
$stuff = $q->select('a.*, au.last_name')->from('Article a LEFT JOIN a.author au')->execute();

echo $stuff[0]['author']['last_name'];
echo $stuff[0]['title'];    //note the absence of table accessor method, has to be omitted for first table in from()


// another doSelectJoin which is impossible with propel (cannot add table aliases with this shorthand syntax)
// for aliasing:  ...from('Article a LEFT JOIN a.author au LEFT JOIN au.address ad')
$stuff = $q->from('Article.author.address')->execute(); // *one* query to get the articles, their authors and the addresses of the authors (three tables)

foreach ($stuff as $thisRow)
{
    // to get article title
    echo $thisRow['title'];
    
    // to get article author's last name
    echo $thisRow['author']['last_name'];
    
    // to get street name of article author
    echo $thisRow['author']['address']['streetName'];
}

Doctrine getTable Examples

Note that sfDoctrine::getTable() is a shortcut to: Doctrine_Manager::getInstance()->getTable().

<?php

// get all entries
$q = sfDoctrine::getTable('myClass')->findAll();

// get entry by pk
$q = sfDoctrine::getTable('myClass')->find('primary key');

// set primary key for key access
$q->setAttribute(Doctrine::ATTR_COLL_KEY, 'column_name');
// now you can do
$q->get('column_name_value')

Pager

<?php
//pager
$pager = new sfDoctrinePager('MyClass', 10);
$pager->getQuery()->where('category = ?', 'foo')->orderBy('created_at desc');
$pager->setPage(2);
$pager->init();
$pager->getResults();
$pager->getResults('array'); //use the MUCH faster fetch_array syntax!

A beginners guide on how to use the pager is available at sfDoctrinePager?.

If you are used to Propel

<?php
// some propel equivalents

// retrieveByPk()
$stuff = Doctrine_Manager::getInstance()->getTable('myClass')->find($pk);

// doSelectOne()
$stuff = Doctrine_Query::create()->from('User')->where('User.email = ?', $email)->limit(1)->execute()->getFirst();

Propel compatibility mode

<?php
class myClass extends sfDoctrineRecord
{
  $this->hasColumn('foo', integer);
}
.....
$object = Doctrine_Manager::getInstance()->getTable('myClass')->find(1);
// the following three calls will return the same value
echo $object['foo']; //recommended for fetch_array compatibility
echo $object->getFoo(); // propel compatibility mode
echo $object->get('foo');


// you can override the getters/setters with 2 methods in sfDoctrine. We have filters for the getters/setters and then the ability to completely override the getters/setters
function filterGetFoo()
function filterSetFoo()
function getFoo()
function setFoo()

Be careful when upgrading, if you have custom methods like getCategory() and you also have a column named category on the same model, then you will run in to problems, because getCategory() will override the getter for that model/column completely. You will need to rename that function in order for it to be a custom function and so it does not override the getter.

Equivalent of peer methods

The equivalent of the peer methods are either static methods in the doctrine record classes or regular methods in the Table classes:

<?php
// static style
// in a 'Post' class
static public function findPublished()
{
  $q = Doctrine_Query::create()->from('Post');
  return $q->where('Post.is_published = ?', true)->execute();
}

// Table style
// in a class 'PostTable':
public function findPublished()
{
  $q = $this->createQuery(); // the equivalent of Doctrine_Query::create()->from()
  return $q->where('Post.is_published = ?', true)->execute();
}

// in the controller (actions.class.php for example):
// static style
$posts = Post::findPublished();
// table style
$posts = Doctrine_Manager::getInstance()->getTable('Post')->findPublished();

Determining if a record exists:

Doctrine always returns a record object (albeit empty) when you query a foreign relation:

<?php
$articleAuthor = $article->get('author'); // 'author' is a foreign key
if (!$articleAuthor->exists()) // doctrine will always return a record, albeit perhaps an empty one, so make sure to use the exists() method!
  echo 'This article has no author!';

More Examples

With the schema above one may then use the following syntax in the code:

<?php
// elementary queries (no joins!)
$q = new Doctrine_Query;
$jackspublications = $q->from('Publication p')->where('p.author.firstName = ?', 'Jack')->execute();
// try this one with propel ;-)
$q = new Doctrine_Query;
$authorsOfPublicationsReviewedByJack = $q->from('User u')->where('u.authoredPublications.reviewers.first_name = ?', 'Jack')->execute();

// i18n
$author->setCulture('foo');
$author->set('description', 'Bar');
echo $author->get('description'); // "Bar"

// one to many
$author = $book->get('author'); echo $author['firstName'];

// many to one
$books = $author->get('authoredPublications'); foreach ($publications as $publication) echo $publication['publication_date'];

// many to many
$books = $user->get('authoredPublications'); echo count($publications);

// inheritance
Doctrine_Manager::getInstance()->getTable('Book')->findAll(); // you get only books, not articles
// all the articles written by John (not the books):
$articles = Doctrine_Query::create()->from('Article a')->where('a.author.id = ?', $john->get('id'))->execute();

Raw SQL examples

If you need for some reason to run raw SQL, you can always use the underlying PDO engine to do so:

From a table model class:

<?php
class ObjectTable extends Doctrine_Table
{
  public function findBySql($sql)
  {
    try 
      {
        $res = $this->getConnection()->getDbh()->query($sql);
        return $res->fetch(PDO::FETCH_ASSOC);
      }
      catch (PDOException $e)
      {
        die('PDO Exception: '.$e->getMessage());
      }
  }
}

From anywhere:

<?php

try
{
  $res = sfDoctrine::connection()->getDbh()->query($sql);
  return $res->fetch(PDO::FETCH_ASSOC);
}
catch (PDOException $e)
{
  die('PDO Exception: '.$e->getMessage());
}

Object helpers

It is perfectly all right to use helpers with sfDoctrine but you'll have to pass an array instead of a name of a getter. Here is a revisited example from the symfony documentation:

<?php
// instead of object_select_tag($article, 'getAuthorId', 'related_class=Author')
object_select_tag($article, array('get', array('author_id')), 'related_class=Author')

For the object helpers, the general syntax is:

<?php
// to represent functionName('arg1', 'arg2') you would use:
array('functionName',array('arg1', 'arg2'))

sfDoctrine Generators

Crud Generation

symfony doctrine-generate-crud <application> <module> <class>

Admin Generation

Many to many relationships

For many to many relationships you will have to replace for example admin_check_list by doctrine_admin_check_list. Besides, the throughClass parameter is not necessary. All in all it looks like:

      fields:
        objects: doctrine_admin_check_list

Foreign keys

In the doctrine admin generator, you should never use the foreign key column names. Use only the foreign names. So if you have a relation named "Author" and the foreign key is "u_id" you should use "Author" in generator.yml, and not "u_id". This holds for both the display and the edit part of the admin generator interface. Take a look at the FAQ? for more details and examples.

Propel Model Import

First move all your propel model classes outside of the autoloading scope.

Import/export from a propel schema.xml

You may try

symfony doctrine-import

to import the bulk of your database description into a doctrine format. Inheritance and i18n are not imported.

To export a doctrine model to propel you may use: {{{ symfony doctrine-export }}} Inheritance and i18n are ''not'' exported.

This command automatically generates doctrine description files and its subclasses from a connection.yml schema file. The generated files are all placed in lib/model/doctrine.

Advanced

Tuning (doctrine.yml)

First look at doctrine.yml in the config folder of the sfDoctrine plugin. The file is self-documented and you will find all default attribute values. Do not edit the file!

To change doctrine attributes, create a doctrine.yml file in one of the config folders of your symfony project. The config is overridable like other symfony configs, so you can put doctrine.yml files in any config folder. Here is an example of how to use doctrine.yml to control attributes for different environments and connections:

  test:
    # Enable validation for all connections in test environment
    attributes:
      vld: on
  
    # No validation for this connection in test environment
    trustedConnection:
      attributes:
        vld: off

Overriding set and get

Instead of overriding the accessors you will use filters. Filters have been removed from Doctrine (for speed?), but are planned to be re-implemented in sfDoctrine (status of this?). The filter prefixes are, by default, filterGet and filterSet but those names are configurable. So if you would like to change the behaviour of the set('foo') setter you would proceed as follows:

<?php
public function filterSetFoo($value)
{
  return $value.'bar'; // every time you use set('foo', $s), you actually save $s.'bar' in the database
}

// similarly for the getters
public function filterGetFoo($value)
{
  return "The value is $value";
}


// NOTE: For underscore fields like `start_date`, you must keep the underscore (temp bug with Doctrine - 6/8/07)
public function filterGetStart_Date($value)
{
  return "The value is $value";
}

Overriding save()

For example say you have the table User and Report. When a new Report is created, you want a counter field User.report_count to be incremented. In Doctrine, we can use the EventListener? function onInsert().

lib/model/doctrine/Report.class.php Note: this changed recently in Doctrine; now onInsert and onUpdate events are handled by Doctrine_Record, rather than Doctrine_EventListener, and the methods have been renamed to preInsert, postInsert, preUpdate, postUpdate. The previous example could be rewritten as:

// This class would likely be part of your model
class Report_Record extends Doctrine_Record
{
    public function postInsert($event)
    {
        $q = new Doctrine_Query();
        $q->select('u.report_count')->from('User u')->where('u.id = ?', $this->get('user_id'));
        $row = $q->execute()->getFirst();

        $row->set('report_count', ($row->get('report_count') + 1));
        $row->save();
    }
}

And then when Report_Record::save is called on a record for the first time (which triggers an insert), the postInsert event will fire and the code will be executed.

Hierarchical data

Doctrine supports managing hierarchical data (i.e. Trees) and currently fully supports the Nested Set implementation.

You can read more about how to manage trees in doctrine here

Below is an example of how to configure a tree within the schema.yml

Tree:
  tableName: tree
  options:
    treeImpl: NestedSet
  columns:
    name: {type: string, size: 1000}

Note that you can use the options namespace in the schema.yml to configure your model with any options, these are then accessed as follows:

<?php
Doctrine_Manager::getInstance()->getTable('myClass')->getOption($name);

Links

• sfDoctrineFaq?
• sfDoctrinePager?
• sfGuardDoctrinePlugin?
• sfDoctrinePluginDev?

Snippets

All snippets should be created in symfony snippets. Here are the current doctrine snippets.