Yii Framework Forum: Agile Yii Book - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Agile Yii Book Problem with tbl_project_user_role Fixtures Data

#1 User is offline   yiier 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 18-September 10

Posted 18 September 2010 - 02:55 PM

Hi,

I am going through Agile Web Application Development with Yii 1.1 and PHP5. In Chapter 8, the reader is asked to set up fixture data for the table `tbl_project_user_role`. However, doing so seems to cause a FK constraint issue with the ProjectTest::testDelete() method.

Has anyone encountered this problem? Perhaps I am doing something wrong? The quick fix is to remove the foreign key constraint for `tbl_project_user_role`.project_id to the `tbl_project`.id field.

Thanks,
Yiier
0

#2 User is offline   jefftulsa 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 168
  • Joined: 06-October 08
  • Location:Austin, TX

Posted 18 September 2010 - 09:37 PM

Hello Yiier,

Are you using MySQL as you are following along with the examples? If so, have you ensured that the foreign key is defined with ON DELETE CASCADE?:

ALTER TABLE `tbl_project_user_role` ADD CONSTRAINT `FK_user_id` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

0

#3 User is offline   yiier 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 18-September 10

Posted 18 September 2010 - 10:05 PM

Hello jefftulsa,

Thank you for the quick reply.

Yes, I am using MySQL. The book does not appear to specify any cascading deletes for any of the FK constraints applied to the `tbl_project_user_role` table. The problem was with the project_id constraint, not the user_id constraint. Did you mean to reference this field instead?

I did try your suggestion (but applied to the project_id field instead) and it worked on the test db with fixtures. I re-ran the unit test and it works now. However, and if I am wrong please forgive me, if we set up the relationship this way on the actual live DB, wouldn't this delete any project (or user in your case) when the role is deleted? Seems like this would not be intended. Furthermore, it would seem that the test DB's schema should precisely match that of the live DB?

Thank you for your input,
Yiier



View Postjefftulsa, on 18 September 2010 - 09:37 PM, said:

Hello Yiier,

Are you using MySQL as you are following along with the examples? If so, have you ensured that the foreign key is defined with ON DELETE CASCADE?:

ALTER TABLE `tbl_project_user_role` ADD CONSTRAINT `FK_user_id` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;


0

#4 User is offline   jefftulsa 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 168
  • Joined: 06-October 08
  • Location:Austin, TX

Posted 19 September 2010 - 10:04 PM

Hello Yiier,

Yes, I did mean to reference the project_id rather than the user_id FK relationship in the previous post:

ALTER TABLE `tbl_project_user_role` ADD CONSTRAINT `FK_project_id` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;


With this in place, if you delete a row in the "parent" table, i.e. tbl_project, and there are any associated rows (i.e. via the FK project_id) in the tbl_project_user_role table, these will be deleted as well. However, there is no FK defined on tbl_project back to tbl_project_user_role, so simply deleting a row in this table will not delete rows in your tbl_project table.

Of course, you can also take care of this programmatically if you prefer and not define it this way in MySQL. Then, of course, you would need to alter how you delete projects by first manually deleting from any other table that have FKs tied back to the tbl_project.id field.

You are correct that the foreign key is not specified this way in the DDL statement for creating the table. The book is attempting to say database independent, and the low level DDL statements for tables are intended to be general (i.e. not MySQL specific).

If you download the actual code for the application, there are MySQL specific SQL (DDL) statements for all objects in the database and these are defined this way there.

You can get the code chapter_by_chapter from the packt site:

https://www.packtpub...evelopment/book

or grab it from github:

git@github.com:jefftulsa/TrackStar.git
1

#5 User is offline   yiier 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 18-September 10

Posted 19 September 2010 - 10:38 PM

Hi jefftulsa,

Your thorough explanation makes perfect sense, and now I have a better understanding of how the cascading deletes in MySQL work. Much appreciated!

Sincerely,
Yiier

View Postjefftulsa, on 19 September 2010 - 10:04 PM, said:

Hello Yiier,

Yes, I did mean to reference the project_id rather than the user_id FK relationship in the previous post:

ALTER TABLE `tbl_project_user_role` ADD CONSTRAINT `FK_project_id` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;


With this in place, if you delete a row in the "parent" table, i.e. tbl_project, and there are any associated rows (i.e. via the FK project_id) in the tbl_project_user_role table, these will be deleted as well. However, there is no FK defined on tbl_project back to tbl_project_user_role, so simply deleting a row in this table will not delete rows in your tbl_project table.

Of course, you can also take care of this programmatically if you prefer and not define it this way in MySQL. Then, of course, you would need to alter how you delete projects by first manually deleting from any other table that have FKs tied back to the tbl_project.id field.

You are correct that the foreign key is not specified this way in the DDL statement for creating the table. The book is attempting to say database independent, and the low level DDL statements for tables are intended to be general (i.e. not MySQL specific).

If you download the actual code for the application, there are MySQL specific SQL (DDL) statements for all objects in the database and these are defined this way there.

You can get the code chapter_by_chapter from the packt site:

https://www.packtpub...evelopment/book

or grab it from github:

git@github.com:jefftulsa/TrackStar.git

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users