[jira] Resolved: (DDLUTILS-214) Primary Key Column order lost (a problem if there are multiple Primary Keys)

["Thomas Dudziak (JIRA)" <ji...@apache.org>]

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

Thomas Dudziak resolved DDLUTILS-214.
-------------------------------------

    Resolution: Fixed

> Primary Key Column order lost (a problem if there are multiple Primary Keys)
> ----------------------------------------------------------------------------
>
>                 Key: DDLUTILS-214
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-214
>             Project: DdlUtils
>          Issue Type: Bug
>          Components: Core (No specific database)
>    Affects Versions: 1.1
>         Environment: Any
>            Reporter: Rijk van Haaften
>            Assignee: Thomas Dudziak
>            Priority: Critical
>             Fix For: 1.1
>
>
> Symptom:
> 14558 [btpool0-5] DEBUG org.apache.ddlutils.platform.postgresql.PostgreSqlPlatform  - About to execute SQL DELETE FROM "a" WHERE "p" = ? AND "q" = ?
> The delete sometimes works, sometimes I get this:
> 14569 [btpool0-5] WARN  org.apache.ddlutils.platform.postgresql.PostgreSqlPlatform  - Attempted to delete a single row "a": q = 6, p = 3 in table "a" but changed 0 row(s).
> Symptom analysis:
> The query
> DELETE FROM "a" WHERE "p" = ? AND "q" = ?
> is filled in order (6,3), without regarding the field names (q = 6, p = 3) resulting in
> DELETE FROM "a" WHERE "p" = 6 AND "q" = 3
> Code analysis
> 1. SqlBuilder.getDeleteSql
> SqlBuilder.getDeleteSql uses the Map pkValues iterator to generate the prepared statement.
> 2. PlatformImplBase.toColumknValues
> This has a major problem: Map iterator order is not defined. Java documentation: "Some map implementations, like the TreeMap class, make specific guarantees as to their order; others, like the HashMap class, do not." In this case, the Map used is created by PlatformImplBase.toColumknValues:
>     protected HashMap toColumnValues(SqlDynaProperty[] properties, DynaBean bean) {
>         HashMap result = new HashMap();
>         ...
>     }
> so iterator order is undefined.
> 3. PlatformImplBase.delete (for example)
> a)
>             String sql = createDeleteSql(model, dynaClass, primaryKeys, null);
> createDeleteSql uses getDeleteSql (1) and due to (2), the order the columns in the generated String sql is undefined. So, this could generate either
>  DELETE FROM "a" WHERE "p" = ? AND "q" = ?
>  DELETE FROM "a" WHERE "q" = ? AND "p" = ?
> which one cannot be determined
> b) setObject uses a deterministic array order sqlIndex to fill the ? - marks
>             SqlDynaProperty[] primaryKeys = dynaClass.getPrimaryKeyProperties();
>             for (int idx = 0; idx < primaryKeys.length; idx++)
>             {
>                 setObject(statement, idx + 1, dynaBean, primaryKeys[idx]);
>             }
> so there is a chance of 1/(number of PK columns) this will run correctly.
> Solution:
> use LinkedHashMap or TreeMap: they retain put-order, which is based on the SqlDynaProperty[] so by definition has the same order as the setObject loop:
>     protected HashMap toColumnValues(SqlDynaProperty[] properties, DynaBean bean)
>     {
>         HashMap result = new LinkedHashMap();
>         ...
>     }

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.