cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Robert Zeigler <robert.zeig...@roxanemy.com>
Subject Re: many to many relation
Date Wed, 29 Sep 2010 20:17:11 GMT
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:'testuser@appfuse.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=>testuser@appfuse.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.


Mime
View raw message