[jira] [Updated] (CAY-1927) Use deferred constraint checking on the databases that support it

["Andrus Adamchik (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CAY-1927?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Andrus Adamchik updated CAY-1927:
---------------------------------

    Description: 
We can solve the issue of operation sorting on commit (e.g. see CAY-1410) by using deferred constraints checking database feature. Not all databases support it. But there are few that do:

Oracle: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10003.htm#SQLRF55300
PostgreSQL: http://www.postgresql.org/docs/9.1/static/sql-set-constraints.html
MySQL: (no support)
Derby: (no support)

The way I see the implementation is this:

1. Define a stack property in org.apache.cayenne.configuration.Constants that turns deferred constraint checking on and off:

 String SERVER_DEFERRED_CONSTRAINTS_PROPERTY = "cayenne.server.deferred_constraints";

Default will be "false".

2. The property may be checked by DbAdapter when executing BatchAction. If the property is "true" and concrete DbAdapter supports deferred constraint checking, it will generate and execute SQL to instruct the DB to use it. 

(3.) Ideally we should also save ourselves some CPU cycles by not invoking EntitySorter inside DataDomain on commit. EntitySorter main purpose is to insert rows in such order that FK constraints are immediately satisfied (and deficiencies in the sorting alg. are the biggest motivation for this feature). However this might be tricky for two reasons: 
* EntitySorter is invoked in a higher framework layer before the queries are split by DataNode. So it will have to prematurely check which DataNode is involved for each entity in commit, to see if deferred constraints are supported.
* Aside from FK constraint checking it provides the ordering that ensure auto-incremented PK values are available to related FKs. 
So we may have to keep the sorter around until we figure out how to handle the stuff above.


  was:
We can solve the issue of operation sorting on commit (e.g. see CAY-1410) by using deferred constraints checking database feature. Not all databases support it. But there are few that do:

Oracle: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10003.htm#SQLRF55300
PostgreSQL: http://www.postgresql.org/docs/9.1/static/sql-set-constraints.html
MySQL: (no support)
Derby: (no support)

The way I see the implementation is this:

1. Define a stack property in org.apache.cayenne.configuration.Constants that turns deferred constraint checking on and off:

 String SERVER_DEFERRED_CONSTRAINTS_PROPERTY = "cayenne.server.deferred_constraints";

Default will be "false".

2. The property may be checked by DbAdapter when executing BatchAction. If the property is "true" and concrete DbAdapter supports deferred constraint checking, it will generate and execute SQL to instruct the DB to use it. 

(3.) Ideally we should also save ourselves some CPU cycles by not invoking EntitySorter inside DataDomain on commit. EntitySorter main purpose is to insert rows in such order that FK constraints are immediately satisfied (and deficiencies in the sorting alg. are the biggest motivation for this feature). However this might be tricky for two reasons: 
* EntitySorter is invoked in a higher framework layer *before* the queries are split by DataNode.
* Aside from FK constraint checking it provides the ordering that ensure auto-incremented PK values are available to related FKs. 
So we may have to keep the sorter around until we figure out how to handle the stuff above.



> Use deferred constraint checking on the databases that support it
> -----------------------------------------------------------------
>
>                 Key: CAY-1927
>                 URL: https://issues.apache.org/jira/browse/CAY-1927
>             Project: Cayenne
>          Issue Type: Task
>            Reporter: Andrus Adamchik
>            Assignee: Andrus Adamchik
>             Fix For: 3.2.M2
>
>
> We can solve the issue of operation sorting on commit (e.g. see CAY-1410) by using deferred constraints checking database feature. Not all databases support it. But there are few that do:
> Oracle: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10003.htm#SQLRF55300
> PostgreSQL: http://www.postgresql.org/docs/9.1/static/sql-set-constraints.html
> MySQL: (no support)
> Derby: (no support)
> The way I see the implementation is this:
> 1. Define a stack property in org.apache.cayenne.configuration.Constants that turns deferred constraint checking on and off:
>  String SERVER_DEFERRED_CONSTRAINTS_PROPERTY = "cayenne.server.deferred_constraints";
> Default will be "false".
> 2. The property may be checked by DbAdapter when executing BatchAction. If the property is "true" and concrete DbAdapter supports deferred constraint checking, it will generate and execute SQL to instruct the DB to use it. 
> (3.) Ideally we should also save ourselves some CPU cycles by not invoking EntitySorter inside DataDomain on commit. EntitySorter main purpose is to insert rows in such order that FK constraints are immediately satisfied (and deficiencies in the sorting alg. are the biggest motivation for this feature). However this might be tricky for two reasons: 
> * EntitySorter is invoked in a higher framework layer before the queries are split by DataNode. So it will have to prematurely check which DataNode is involved for each entity in commit, to see if deferred constraints are supported.
> * Aside from FK constraint checking it provides the ordering that ensure auto-incremented PK values are available to related FKs. 
> So we may have to keep the sorter around until we figure out how to handle the stuff above.



--
This message was sent by Atlassian JIRA
(v6.2#6252)