Don't use cascade from User -> Role or from Role -> User. Given that a user can existing with 0 or more roles, and a role can exist with 0 or more users... cascade definitely not what you want there. You might believe that "cascade" is the right choice because you want to "cascade" the deletion from User to the association table. But note that the delete rule is on the object relationship: from user -> role (and vice versa). If you had modeled the join table as an object entity, /then/ you would make the delete rule (from user to association table) cascade, and the delete rule form association table -> role "nullify". But since you're working with a "flattened" relationship, you just specify Nullify, and cayenne will do the right thing. To prove this, I changed the delete rule from user -> role to Nullify, and from role -> user to Nullify, then added the following lines to the "testAddAndRemoveUser" test:
lU = (List<User>) userService.getAll();
System.out.println("***** Checking # users *****");
assertEquals(3, lU.size());
System.out.println("***** Checking # roles *****");
assertEquals(2, roleService.getAll().size());
Without the change, you get 0 users and 0 roles. Because:
deleteUser -> deleting the user, who is associated with the default role; b/c of cascade rule, default role is deleted. Default role is associated with all users; because of cascade rule, all users are deleted. Because of cascade rule, any additional roles (admin) associated with users are /also/ deleted. So the net result is: you have no users or roles left in your db at the end of your test. :)
With the change, you get 3 users and 2 roles.
Cheers,
Robert
On Sep 29, 2010, at 9/291:07 PM , gilbertoca wrote:
>
> Some progress ... rs rs rs
> Clicking just one time on ObjRelationshipInspector[1] and confirming the new
> db relationship path my test works but not correctly. In DELETE RULES I've
> put CASCADE.
> So, just remembering:
>
> User <--> Role
>
> tables:
> SHIRO_USER --> SHIRO_USER_ROLE<---SHIRO_ROLE
> SHIRO_ROLE-->SHIRO_ROLE_PERMISSION
>
> The Cayenne config files:
> http://code.google.com/p/construtor/source/browse/#svn/trunk/click-orm-integration/orm/src/test/resources
> The test case:
> http://code.google.com/p/construtor/source/browse/trunk/click-orm-integration/orm/src/test/java/com/google/construtor/extras/security/shiro/cayenne/UserCayenneTest.java
>
> When doing userService.delete(user.getId());, cayenne removes everything(in
> this order):
> SHIRO_USER --> SHIRO_USER_ROLE<---SHIRO_ROLE-->SHIRO_ROLE_PERMISSION
>
> But should be this way:
> SHIRO_USER --> SHIRO_USER_ROLE
>
>
>
>> ===========testAddAndRemoveUser======
>> 2010-09-29 14:32:41,547 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> will run 1 query.
>> 2010-09-29 14:32:41,547 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> transaction started.
>> 2010-09-29 14:32:41,548 INFO [org.apache.cayenne.access.QueryLogger] -
>> SELECT t0.ID, t0.DESCRIPTION, t0.NAME FROM SHIRO_ROLE t0 WHERE t0.NAME = ?
>> [bind: 1->NAME:'USER_ROLE']
>> 2010-09-29 14:32:41,550 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> returned 1 row. - took 1 ms.
>> 2010-09-29 14:32:41,550 INFO [org.apache.cayenne.access.QueryLogger] - +++
>> transaction committed.
>> ***** getting role {<ObjectId:Role, ID=-2>; committed; [id=>-2; users=>?;
>> description=>Default role for all Users; name=>USER_ROLE; permissions=>?]}
>> for a new user. *****
>> 2010-09-29 14:32:41,615 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> will run 2 queries.
>> 2010-09-29 14:32:41,616 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> transaction started.
>> 2010-09-29 14:32:41,837 INFO [org.apache.cayenne.access.QueryLogger] -
>> SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE TABLE_NAME = 'SHIRO_USER'
>> 2010-09-29 14:32:41,844 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> returned 1 row. - took 7 ms.
>> 2010-09-29 14:32:41,845 INFO [org.apache.cayenne.access.QueryLogger] -
>> UPDATE AUTO_PK_SUPPORT SET NEXT_ID = NEXT_ID + 20 WHERE TABLE_NAME =
>> 'SHIRO_USER'
>> 2010-09-29 14:32:41,847 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,848 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> will run 2 queries.
>> 2010-09-29 14:32:41,849 INFO [org.apache.cayenne.access.QueryLogger] -
>> INSERT INTO SHIRO_USER (EMAIL, ID, PASSWORD, USERNAME, VERSION) VALUES (?,
>> ?, ?, ?, ?)
>> 2010-09-29 14:32:41,850 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->EMAIL:'testuse..ppfuse.org', 2->ID:1200,
>> 3->PASSWORD:'testpass', 4->USERNAME:'Test', 5->VERSION:NULL]
>> 2010-09-29 14:32:41,851 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,851 INFO [org.apache.cayenne.access.QueryLogger] -
>> INSERT INTO SHIRO_USER_ROLE (ROLE_ID, USER_ID) VALUES (?, ?)
>> 2010-09-29 14:32:41,851 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->ROLE_ID:-2, 2->USER_ID:1200]
>> 2010-09-29 14:32:41,852 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,855 INFO [org.apache.cayenne.access.QueryLogger] - +++
>> transaction committed.
>> 2010-09-29 14:32:41,855 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> will run 1 query.
>> 2010-09-29 14:32:41,855 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> transaction started.
>> 2010-09-29 14:32:41,856 INFO [org.apache.cayenne.access.QueryLogger] -
>> SELECT t0.ID, t0.USERNAME, t0.EMAIL, t0.PASSWORD, t0.VERSION FROM
>> SHIRO_USER t0
>> 2010-09-29 14:32:41,857 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> returned 4 rows. - took 1 ms.
>> 2010-09-29 14:32:41,857 INFO [org.apache.cayenne.access.QueryLogger] - +++
>> transaction committed.
>> ***** Does the object was update or shoul I get it by find method? *****
>> ***** should use cache? {<ObjectId:User, ID=1200>; committed; [id=>1200;
>> username=>Test; email=>testuse..ppfuse.org; roles=>(..);
>> password=>testpass; version=>null]}*****
>> 2010-09-29 14:32:41,861 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> will run 1 query.
>> 2010-09-29 14:32:41,862 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> transaction started.
>> 2010-09-29 14:32:41,871 INFO [org.apache.cayenne.access.QueryLogger] -
>> SELECT t0.ROLE_ID, t0.PERMISSION FROM SHIRO_ROLE_PERMISSION t0 WHERE
>> t0.ROLE_ID = ? [bind: 1->ROLE_ID:-2] - prepared in 9 ms.
>> 2010-09-29 14:32:41,872 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> returned 1 row. - took 10 ms.
>> 2010-09-29 14:32:41,873 INFO [org.apache.cayenne.access.QueryLogger] - +++
>> transaction committed.
>> 2010-09-29 14:32:41,876 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> will run 1 query.
>> 2010-09-29 14:32:41,876 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> transaction started.
>> 2010-09-29 14:32:41,883 INFO [org.apache.cayenne.access.QueryLogger] -
>> SELECT DISTINCT t0.ID, t0.USERNAME, t0.EMAIL, t0.PASSWORD, t0.VERSION FROM
>> SHIRO_USER t0 JOIN SHIRO_USER_ROLE t1 ON (t0.ID = t1.USER_ID) WHERE
>> t1.ROLE_ID = ? [bind: 1->ROLE_ID:-2] - prepared in 6 ms.
>> 2010-09-29 14:32:41,884 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> returned 4 rows. - took 7 ms.
>> 2010-09-29 14:32:41,890 INFO [org.apache.cayenne.access.QueryLogger] - +++
>> transaction committed.
>> 2010-09-29 14:32:41,891 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> will run 1 query.
>> 2010-09-29 14:32:41,891 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> transaction started.
>> 2010-09-29 14:32:41,891 INFO [org.apache.cayenne.access.QueryLogger] -
>> SELECT DISTINCT t0.ID, t0.DESCRIPTION, t0.NAME FROM SHIRO_ROLE t0 JOIN
>> SHIRO_USER_ROLE t1 ON (t0.ID = t1.ROLE_ID) WHERE t1.USER_ID = ? [bind:
>> 1->USER_ID:-3]
>> 2010-09-29 14:32:41,892 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> returned 1 row. - took 1 ms.
>> 2010-09-29 14:32:41,892 INFO [org.apache.cayenne.access.QueryLogger] - +++
>> transaction committed.
>> 2010-09-29 14:32:41,893 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> will run 1 query.
>> 2010-09-29 14:32:41,893 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> transaction started.
>> 2010-09-29 14:32:41,893 INFO [org.apache.cayenne.access.QueryLogger] -
>> SELECT DISTINCT t0.ID, t0.DESCRIPTION, t0.NAME FROM SHIRO_ROLE t0 JOIN
>> SHIRO_USER_ROLE t1 ON (t0.ID = t1.ROLE_ID) WHERE t1.USER_ID = ? [bind:
>> 1->USER_ID:-2]
>> 2010-09-29 14:32:41,894 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> returned 1 row. - took 1 ms.
>> 2010-09-29 14:32:41,894 INFO [org.apache.cayenne.access.QueryLogger] - +++
>> transaction committed.
>> 2010-09-29 14:32:41,895 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> will run 1 query.
>> 2010-09-29 14:32:41,895 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> transaction started.
>> 2010-09-29 14:32:41,895 INFO [org.apache.cayenne.access.QueryLogger] -
>> SELECT t0.ROLE_ID, t0.PERMISSION FROM SHIRO_ROLE_PERMISSION t0 WHERE
>> t0.ROLE_ID = ? [bind: 1->ROLE_ID:-1]
>> 2010-09-29 14:32:41,896 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> returned 2 rows. - took 1 ms.
>> 2010-09-29 14:32:41,896 INFO [org.apache.cayenne.access.QueryLogger] - +++
>> transaction committed.
>> 2010-09-29 14:32:41,897 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> will run 1 query.
>> 2010-09-29 14:32:41,897 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> transaction started.
>> 2010-09-29 14:32:41,898 INFO [org.apache.cayenne.access.QueryLogger] -
>> SELECT DISTINCT t0.ID, t0.USERNAME, t0.EMAIL, t0.PASSWORD, t0.VERSION FROM
>> SHIRO_USER t0 JOIN SHIRO_USER_ROLE t1 ON (t0.ID = t1.USER_ID) WHERE
>> t1.ROLE_ID = ? [bind: 1->ROLE_ID:-1]
>> 2010-09-29 14:32:41,898 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> returned 1 row. - took 1 ms.
>> 2010-09-29 14:32:41,898 INFO [org.apache.cayenne.access.QueryLogger] - +++
>> transaction committed.
>> 2010-09-29 14:32:41,901 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> will run 4 queries.
>> 2010-09-29 14:32:41,902 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> transaction started.
>> 2010-09-29 14:32:41,902 INFO [org.apache.cayenne.access.QueryLogger] -
>> DELETE FROM SHIRO_USER_ROLE WHERE ROLE_ID = ? AND USER_ID = ?
>> 2010-09-29 14:32:41,903 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->ROLE_ID:-2, 2->USER_ID:-1]
>> 2010-09-29 14:32:41,903 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,903 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->ROLE_ID:-1, 2->USER_ID:-1]
>> 2010-09-29 14:32:41,904 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,904 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->ROLE_ID:-2, 2->USER_ID:-3]
>> 2010-09-29 14:32:41,904 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,904 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->ROLE_ID:-2, 2->USER_ID:-2]
>> 2010-09-29 14:32:41,905 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,905 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->ROLE_ID:-2, 2->USER_ID:1200]
>> 2010-09-29 14:32:41,905 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,905 INFO [org.apache.cayenne.access.QueryLogger] -
>> DELETE FROM SHIRO_ROLE_PERMISSION WHERE PERMISSION = ? AND ROLE_ID = ?
>> 2010-09-29 14:32:41,906 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->PERMISSION:'secure', 2->ROLE_ID:-1]
>> 2010-09-29 14:32:41,906 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,906 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->PERMISSION:'none', 2->ROLE_ID:-2]
>> 2010-09-29 14:32:41,907 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,907 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->PERMISSION:'admin', 2->ROLE_ID:-1]
>> 2010-09-29 14:32:41,907 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,908 INFO [org.apache.cayenne.access.QueryLogger] -
>> DELETE FROM SHIRO_USER WHERE ID = ?
>> 2010-09-29 14:32:41,908 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->ID:1200]
>> 2010-09-29 14:32:41,909 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,909 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->ID:-1]
>> 2010-09-29 14:32:41,909 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,910 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->ID:-3]
>> 2010-09-29 14:32:41,910 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,910 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->ID:-2]
>> 2010-09-29 14:32:41,911 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,911 INFO [org.apache.cayenne.access.QueryLogger] -
>> DELETE FROM SHIRO_ROLE WHERE ID = ?
>> 2010-09-29 14:32:41,911 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->ID:-1]
>> 2010-09-29 14:32:41,912 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,912 INFO [org.apache.cayenne.access.QueryLogger] -
>> [bind: 1->ID:-2]
>> 2010-09-29 14:32:41,913 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> updated 1 row.
>> 2010-09-29 14:32:41,914 INFO [org.apache.cayenne.access.QueryLogger] - +++
>> transaction committed.
>> ***** what does happen if the entity doesn't exist? *****
>> 2010-09-29 14:32:41,915 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> will run 1 query.
>> 2010-09-29 14:32:41,915 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> transaction started.
>> 2010-09-29 14:32:41,916 INFO [org.apache.cayenne.access.QueryLogger] -
>> SELECT t0.ID, t0.USERNAME, t0.EMAIL, t0.PASSWORD, t0.VERSION FROM
>> SHIRO_USER t0 WHERE t0.ID = ? [bind: 1->ID:1200]
>> 2010-09-29 14:32:41,916 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> returned 0 rows. - took 1 ms.
>> 2010-09-29 14:32:41,917 INFO [org.apache.cayenne.access.QueryLogger] - +++
>> transaction committed.
>> ===========testAddUserRole======
>> 2010-09-29 14:32:41,918 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> will run 1 query.
>> 2010-09-29 14:32:41,919 INFO [org.apache.cayenne.access.QueryLogger] - ---
>> transaction started.
>> 2010-09-29 14:32:41,919 INFO [org.apache.cayenne.access.QueryLogger] -
>> SELECT t0.ID, t0.USERNAME, t0.EMAIL, t0.PASSWORD, t0.VERSION FROM
>> SHIRO_USER t0 WHERE t0.ID = ? [bind: 1->ID:-2]
>> 2010-09-29 14:32:41,920 INFO [org.apache.cayenne.access.QueryLogger] - ===
>> returned 0 rows. - took 1 ms.
>> 2010-09-29 14:32:41,920 INFO [org.apache.cayenne.access.QueryLogger] - +++
>> transaction committed.
>> Tests run: 7, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 2.584 sec
>> <<< FAILURE!
>>
>> Results :
>>
>> Tests in error:
>>
>> testAddUserRole(com.google.constructor.extras.security.shiro.cayenne.UserCayenneTest)
>>
>>
>
> Is there anything wrong with my configuration?
> You can try it just checking out the project[2] (Apache License 2.0):
> svn checkout
> http://construtor.googlecode.com/svn/trunk/click-orm-integration coi
> cd coi/orm
> mvn sql:execute ---> will create a H2 database in the data folder
> mvn clean test -Dtest=UserCayenneTest
>
> It is a multi-module maven project:
>
> # orm/ =======> JPA and Cayenne implementation of IService
> =======> ShiroJPARealm a little implementation of
> Shiro framework (used on rolesecurity module example)
> =======> ShiroCayenneRealm a little implementation
> of Shiro framework (doesn't have module example, just test for now)
> # rolesecurity/ =======> rolesecurity module example showing how to setup
> Apache Shiro with click and demonstrate the ShiroJPARealm use.
>
> Hope someone has time to take a look on it.
>
>
> Regards,
>
> Gilberto
>
> PS: Why does cayenne not do a rollback when occurs an exception? See the the
> comment test method in the UserCayenneTest class.
>
> [1]http://dl.dropbox.com/u/9093640/ObjRelationshipInspector2.png
> [2]http://code.google.com/p/construtor/
>
> --
> View this message in context: http://cayenne.195.n3.nabble.com/many-to-many-relation-tp3491p1603892.html
> Sent from the Cayenne - User mailing list archive at Nabble.com.
This archive was generated by hypermail 2.0.0 : Wed Sep 29 2010 - 20:17:45 UTC
