[HomanXH]

Can anyone tell me what is wrong? I have turned on "ON DELETE CASCADE
ON UPDATE CASCADE" in the table. Here is the error message:

Quote

There was 1 error:

1) ProjectTest::testUserRoleAssignment
CDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: I
ntegrity constraint violation: 1452 Cannot add or update a child row: a foreign
key constraint fails (`trackstar_test`.`tbl_project_user_role`, CONSTRAINT `FK_r
ole_name` FOREIGN KEY (`role`) REFERENCES `authitem` (`name`) ON DELETE CASCADE
ON UPDATE CASCADE)

C:\inetpub\wwwroot\medappt\yii\framework\db\CDbCommand.php:338
C:\inetpub\wwwroot\medappt\trackstar\protected\models\Project.php:125
C:\inetpub\wwwroot\medappt\trackstar\protected\tests\unit\ProjectTest.php:85

FAILURES!
Tests: 6, Assertions: 13, Errors: 1.

C:\inetpub\wwwroot\medappt\trackstar\protected\tests>

and mysql ddl:

Quote

CREATE TABLE `tbl_project_user_role` (
`project_id` INT(11) NOT NULL,
`user_id` INT(11) NOT NULL,
`role` VARCHAR(64) NOT NULL,
PRIMARY KEY (`project_id`, `user_id`, `role`),
INDEX `FK_user_id` (`user_id`),
INDEX `FK_role_name` (`role`),
CONSTRAINT `FK_role_name` FOREIGN KEY (`role`) REFERENCES `authitem` (`name`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `FK_project_id` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `FK_user_id` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

project.php:

	/**
	 * creates an association between the project, the user and the user's role within the project
	 */
	public function associateUserToRole($role, $userId)
	{
		$sql = "INSERT INTO tbl_project_user_role (project_id, user_id, role) VALUES (:projectId, :userId, :role)";
		$command = Yii::app()->db->createCommand($sql);
		$command->bindValue(":projectId", $this->id, PDO::PARAM_INT);
		$command->bindValue(":userId", $userId, PDO::PARAM_INT);
		$command->bindValue(":role", $role, PDO::PARAM_STR);
		return $command->execute();
	}
	
	/**
	 * removes an association between the project, the user and the user's role within the project
	 */
	public function removeUserFromRole($role, $userId)
	{
		$sql = "DELETE FROM tbl_project_user_role WHERE project_id=:projectId AND user_id=:userId AND role=:role";
		$command = Yii::app()->db->createCommand($sql);
		$command->bindValue(":projectId", $this->id, PDO::PARAM_INT);
		$command->bindValue(":userId", $userId, PDO::PARAM_INT);
		$command->bindValue(":role", $role, PDO::PARAM_STR);
		return $command->execute(); 
	}

projecttest.php:

	public function testUserRoleAssignment()
	{
		$project = $this->projects('project1');
		$user = $this->users('user1');  
		$this->assertEquals(1,$project->associateUserToRole('owner', $user->id)); 
		$this->assertEquals(1,$project->removeUserFromRole('owner', $user->id));
	}

[jefftulsa]

Can you verify you have a row in the table authitem corresponding to name = 'owner' in your TEST database? in mysql, using the test database, what do you get if you issue:

select * from authitem where name = 'owner';

[HomanXH]

jefftulsa, on 20 June 2011 - 11:34 PM, said:

Can you verify you have a row in the table authitem corresponding to name = 'owner' in your TEST database? in mysql, using the test database, what do you get if you issue:

select * from authitem where name = 'owner';

Thanks! I was doing the order incorrectly of the book. Before I executed the rbac command, I forget to put those three Auth* tables. After I put the tables into the database, and I didn't execute rbac again. My bad, the error trigered by empty values of authitem.

[dhimes]

HomanXH, on 21 June 2011 - 02:01 AM, said:

Thanks! I was doing the order incorrectly of the book. Before I executed the rbac command, I forget to put those three Auth* tables. After I put the tables into the database, and I didn't execute rbac again. My bad, the error trigered by empty values of authitem.

Using rbac after creating the tables isn't in the book as far as I can see (I have Kindle edition, if that makes a difference). Also, executing rbac doesn't populate the table. It seems there is something missing in the book.

Hopefully I'll find it in this forum. If so, I'll update this post. I leave this here because this is the page google brings me to for this error- so presumably others will land here too.

EDIT: for anybody else on Kindle, this is location 3250 (and thereabouts). Everybody else talks about page numbers.

EDIT#2: I put in the CASCADE stuff as the OP did, but no joy. I ran and reran the rbac stuff and nothing was added to the db. Finally I simply entered 'owner' for the name in the first entry of AuthItem (and nothing else) and the test ran. I would love it if someone could clarify- are there steps missing in the book? What are the db tables supposed to look like after running the rbac? TIA.

[dhimes]

I really wish I knew what those auth databases were supposed to be initialized to. I'm now getting

CException:  Unknown authorization item "member"

when trying to run testUserAccessBasedOnProjectRole in ProjectTests.php

Interesting thing is this exception is being thrown by CPHPAuthManager.php, but I'm using the databases with my tests. So I don't even really understand how this error is happening.

It's happening in the line

$auth->assign('member',$row1['user_id'],$bizRule); 

One thing to keep in mind is that these are hours and hours wasted on debugging the unit tests, not even on debugging the app.

[Joemaxwell]

jefftulsa, on 20 June 2011 - 11:34 PM, said:

Can you verify you have a row in the table authitem corresponding to name = 'owner' in your TEST database? in mysql, using the test database, what do you get if you issue:

select * from authitem where name = 'owner';

Error Code: 1146. Table 'trackstar_test.authitem' doesn't exist
This is on my trackstar_test db