[jira] [Created] (OPENJPA-2627) Create an option to disable column type checking during schema validation.

["Heath Thomann (JIRA)" <ji...@apache.org>]

Heath Thomann created OPENJPA-2627:
--------------------------------------

             Summary: Create an option to disable column type checking during schema validation.
                 Key: OPENJPA-2627
                 URL: https://issues.apache.org/jira/browse/OPENJPA-2627
             Project: OpenJPA
          Issue Type: Improvement
          Components: usability
    Affects Versions: 2.2.3, 2.4.1
            Reporter: Heath Thomann
            Assignee: Heath Thomann
            Priority: Trivial


I have a customer scenario (as I'll describe in a moment) that requires them to set the following property:

<property name="openjpa.jdbc.SchemaFactory" value="native(ForeignKeys=true)"/>


When I have the customer set this property, OpenJPA throws this exception:

 org.apache.openjpa.persistence.ArgumentException:
"com.xxx.yyy.Parent.phone" declares a column that is not
compatible with the expected type "varchar".  Column details:
Full Name: Parent.phone
Type: varbinary


In other words, the customer's column for 'phone' is defined as varbinary in their SQL Server database.  This type seems to be specific to SQL Server.  The JPA entity defines 'phone' as a String.  Therefore, from OpenJPA's point of view, this is a mismatch.  The customer only sees this when the above property is set, without this property OpenJPA doesn't do the schema validation as such this goes unchecked and all works fine.  While OpenJPA views this as a mismatch and doesn't allow them to go on, the JDBC driver and database allows the String to be stored into an SQL Server varbinary.

The scenario under which they are required to set the SchemaFactory is the typical SQL miss ordering that can occur when OpenJPA doesn't know about a FK constraint.  That is, they have an FK constraint in the database between, as an example, a Child to Parent.  OpenJPA doesn't know about this FK constraint.  Given this, when the customer persists a new Parent and Child, sometimes OpenJPA inserts the Child first, which yield an FK Constraint Violation exception.  This is nothing new and OpenJPA, and the SchemaFactory property was created to handle this scenario.  It allows OpenJPA to view the database table schema, and in so doing OpenJPA can detect (learn about) the FK constraint which will allow it to properly order SQL statements.

As you can see, we are in a bind here: the customer can not change their column type, and OpenJPA will not go on given this column mismatch.  I have seen other cases where the use of SchemaFactory causes exceptions from column type checking that otherwise go unchecked and work fine when the property is not set.  Given this, we need to allow a customer a way (property) to disable the column type checking.  Note that I realize another option for this particular scenario is to use @ForeignKey.  However, this requires a customer to change their code and is OpenJPA specific.  Therefore the customer is not willing to use this.

Thanks,

Heath



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)