symfony framework forum: General plug-ins => multi column unique key with sfDoctrine

symfony forum

Members

FAQ

Home

Home » plugins » General plug-ins » multi column unique key with sfDoctrine

Show: Today's Messages :: Show Polls :: Message Navigator

multi column unique key with sfDoctrine [message #28121]

Fri, 25 May 2007 12:01

ysarazin
Messages: 13
Registered: March 2007
Location: France

Junior Member

Hello,

I'm looking to create a unique key over serveral columns in the shema.yml file.

Doctrine documentation indicate this possibility :

class User extends Doctrine_Record
{
        public function setTableDefinition()
        {
            $this->hasColumn('name', 'string', 200);
            $this->hasColumn('age', 'integer', 2);

            $this->unique(array('name', 'age'));
        }
}

I think something similar to :

User:
  tableName: user
  columns:
    name: string(200)
    age: integer(2)
  uniques:
    unique_name: [name, age]

could be working, but "uniques" is not allowed in the schema.yml :

[sfDoctrineSchemaException]                               
  Invalid key "uniques" in description of class User

Does anybody has a solution to define such a unique key ?

Report message to a moderator

Re: multi column unique key with sfDoctrine [message #28694 is a reply to message #28121 ]

Tue, 05 June 2007 08:57

pat79
Messages: 2
Registered: June 2007

Junior Member

Hi there,

i have the same issue.

the (propel-specific) doc says that you have to name your item in yml "_uniques"; attend the underscore. Also you have to ident it the same level as your columns.
like this:

Person:
  columns: 
    name: string (30)
    age: integer(3)
    _uniques:
      age_name_unique: [name, age]

In my case this is mapped into a further column in my table instead of being mapped into an unique-constraint.

I appreciate any help,

thanks. patrick

Report message to a moderator

Re: multi column unique key with sfDoctrine [message #29211 is a reply to message #28694 ]

Tue, 12 June 2007 18:37

pitiBalrog
Messages: 5
Registered: December 2006

Junior Member

I try to convert an existing propel schema wich use unique constraint but there is no trace of unique constraint in the generated yml. I think sfDoctrine plugin don't allow unique constraint on multiple columns for now.

Here is extract from the files for my Friend table:
xml propel schema

   <table name="friend" phpName="Friend">
     <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true" />
     <column name="user_id" type="integer" required="true" />
     <foreign-key foreignTable="zp_user" onDelete="cascade">
       <reference local="user_id" foreign="id" />
     </foreign-key>
     <column name="friend_id" type="integer" required="true" />
     <foreign-key foreignTable="zp_user" onDelete="cascade">
       <reference local="friend_id" foreign="id" />
     </foreign-key>
	 <unique>
		<unique-column name="user_id" />
		<unique-column name="friend_id" />
	 </unique>
   </table>

yml generated by the doctrine-import

Friend: 
  tableName: friend
  columns: 
    user_id: 
      columnName: 
      type: integer
      size: 11
      foreignClass: User
      foreignReference: id
      localName: Friends
      foreignName: User
      cascadeDelete: 1
    friend_id: 
      columnName: 
      type: integer
      size: 11
      foreignClass: User
      foreignReference: id
      localName: Friends
      foreignName: User
      cascadeDelete: 1

Report message to a moderator

Re: multi column unique key with sfDoctrine [message #31841 is a reply to message #29211 ]

Fri, 20 July 2007 09:24

sleepless
Messages: 27
Registered: September 2006

Junior Member

I am having the same problem. Has anyone figured out the walk around?

Report message to a moderator

Re: multi column unique key with sfDoctrine [message #31853 is a reply to message #31841 ]

Fri, 20 July 2007 11:01

pitiBalrog
Messages: 5
Registered: December 2006

Junior Member

No doctrine solution for me.
Now I create constraints with SQL.

Report message to a moderator

Re: multi column unique key with sfDoctrine [message #35571 is a reply to message #28121 ]

Fri, 14 September 2007 12:44

hardrock
Messages: 46
Registered: March 2006

Member

Just in case anyone else reads this:

This is in fact possible (with the current version of sfDoctrine (0.4.2), at least).

You just have to add an index over multiple columns with type unique:

MyClass:
  tableName: my_class
  columns: 
    a: integer
    b: integer
  indexes:
    a_b:
      fields: [a, b]
      type: unique

Report message to a moderator

Re: multi column unique key with sfDoctrine [message #35596 is a reply to message #28121 ]

Fri, 14 September 2007 15:18

sleepless
Messages: 27
Registered: September 2006

Junior Member

Thank for bring this up, but I discovered a bug with this code.

For some reason, the generator places the index creation code

CREATE UNIQUE INDEX a_b ON blabla(a, b);

at beginning of the generated database schema. DBMS will complain about this because it couldn't find table blabla.

However, when I tested without "type: unique", the generator generates a working database schema, in which it places the index creation code after table definition codes.

Add: Not a serious bug though, I can fix this by more the index creation code to where it suppose to be.

[Updated on: Fri, 14 September 2007 15:19]

Report message to a moderator

Re: multi column unique key with sfDoctrine [message #35629 is a reply to message #35596 ]

Fri, 14 September 2007 19:56

hardrock
Messages: 46
Registered: March 2006

Member

Interesting. For me it worked painlessly (I believe - actually I get errors when doing doctrine-insert-sql after doctrine-build-model, but as long as my tables are there correctly...)

How does the method setTableDefinition() of your generated class look like? For me, Doctrine creates the following statement:

$this->index('product_merchant', array (
  'fields' => 
  array (
    0 => 'product_id',
    1 => 'merchant_id',
  ),
  'type' => 'unique',
));

which is just the way the Doctrine Documentation suggests it.

Regards

Report message to a moderator

Re: multi column unique key with sfDoctrine [message #35640 is a reply to message #35629 ]

Sat, 15 September 2007 00:16

sleepless
Messages: 27
Registered: September 2006

Junior Member

It'd be the same:

  public function setTableDefinition()
  {
    $this->setTableName('photo_album_content');

    $this->hasColumn('album_id', 'integer', 11, array ());
    $this->hasColumn('image_id', 'integer', 11, array ());
    $this->hasColumn('created_at', 'timestamp', null, array ());
    $this->index('album_image_idx', array (
  'fields' => 
  array (
    0 => 'album_id',
    1 => 'image_id',
  ),
  'type' => 'unique',
));
  }

I am using PostgreSQL by the way.

Report message to a moderator

Previous Topic:	[sfGuard] where is RBAC functionality?
Next Topic:	sfDoctrinePlugin and sfGuardDoctrinePlugin: Syntax error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ]