Issue Details (XML | Word | Printable)

Key: NUCRDBMS-258
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Andy Jefferson
Reporter: Yang ZHONG
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

Support use of CHECK constraint when Boolean field mapped as INT/TINYINT/SMALLINT

Created: 11/Sep/09 01:20 AM   Updated: 26/Apr/10 03:39 PM   Resolved: 06/Apr/10 06:50 PM
Component/s: Schema
Affects Version/s: 1.1.3, 1.1.4, 1.1.5
Fix Version/s: 2.1.0.m2

Environment: Java 5, Linux

Datastore: IBM DB2
Severity: Production


 Description  « Hide
Check Constraint is generated for Boolean mapping of char(1):
    field CHAR(1) CHECK (field IN ('Y','N') OR field IS NULL)

Recommend similar Check Constraint other Boolean mappings such as SmallInt.

Test Case was attached @
    HTTP://WWW.DataNucleus.org/servlet/jira/secure/attachment/10960/204.zip

Only for the only purpose of Proof of Concept, changing org.datanucleus.store.rdbms.mapping.SmallIntRDBMSMapping#initialize() from

            // Valid Values
            if (getJavaTypeMapping() instanceof SingleFieldMapping)
            {
                ...
            }

to

            // Valid Values
            JavaTypeMapping javaTypeMapping = getJavaTypeMapping();
            if (javaTypeMapping instanceof SingleFieldMapping)
            {
                ...
            }
            if (Boolean.class == javaTypeMapping.getJavaType()) // Boolean is final and "==" is faster than isAssignableFrom
            {// NumericRDBMSMapping
                Object identifier = column.getIdentifier();
                StringBuilder constraints = new StringBuilder("CHECK (")
                    .append(identifier)
                    .append(" IN (1,0)");
                if (column.isNullable())
                {
                    constraints.append(" OR ")
                        .append(identifier)
                        .append(" IS NULL");
                }
                constraints.append(')');
                column.setConstraints(constraints.toString());
                column.checkDecimal();
            }

will generate

    field SMALLINT CHECK (field IN (1,0) OR field IS NULL)

Thanks.

Yang ZHONG added a comment - 15/Oct/09 04:42 PM
Same fix is also applicable to 1.1.6.

Andy Jefferson made changes - 10/Dec/09 11:13 AM
Field Original Value New Value
Summary There should have been Check Constraint for Boolean mapping other than char(1) DB2 : There should have been Check Constraint for Boolean mapping other than char(1)
Andy Jefferson made changes - 06/Apr/10 06:49 PM
Summary DB2 : There should have been Check Constraint for Boolean mapping other than char(1) Support use of CHECK constraint when Boolean field mapped as INT/TINYINT/SMALLINT
Assignee Andy Jefferson [ andy ]
Fix Version/s 2.1.0.m2 [ 10912 ]
Priority Major [ 3 ] Minor [ 4 ]
Andy Jefferson added a comment - 06/Apr/10 06:50 PM
SVN trunk handles this for those JDBC types (tested for MySQL)

Andy Jefferson made changes - 06/Apr/10 06:50 PM
Status Open [ 1 ] Resolved [ 5 ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 26/Apr/10 03:39 PM
Status Resolved [ 5 ] Closed [ 6 ]