db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject svn commit: r633560 [2/2] - in /db/derby/code/trunk/java: engine/org/apache/derby/ engine/org/apache/derby/catalog/ engine/org/apache/derby/catalog/types/ engine/org/apache/derby/iapi/services/io/ engine/org/apache/derby/iapi/sql/dictionary/ engine/org...
Date Tue, 04 Mar 2008 17:39:00 GMT
Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/sort/UniqueWithDuplicateNullsMergeSort.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/sort/UniqueWithDuplicateNullsMergeSort.java?rev=633560&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/sort/UniqueWithDuplicateNullsMergeSort.java
(added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/sort/UniqueWithDuplicateNullsMergeSort.java
Tue Mar  4 09:38:48 2008
@@ -0,0 +1,77 @@
+/*
+ 
+   Derby - Class org.apache.derby.impl.store.access.sort.UniqueWithDuplicateNullsMergeSort
+ 
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to you under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+ 
+      http://www.apache.org/licenses/LICENSE-2.0
+ 
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+ 
+ */
+
+package org.apache.derby.impl.store.access.sort;
+
+import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.types.DataValueDescriptor;
+
+/**
+ * This class extends and customizes MergeSort to support almost unique index.
+ * It overrides compare method to consider keypart - 1 parts of the keys while
+ * comparing (only for non null keys).
+ */
+final class UniqueWithDuplicateNullsMergeSort extends MergeSort {
+    
+    /**
+     * Compares two sets of keys. If all the parts of the keys are not null
+     * keys.length - 1 part is compared other wise all the parts are compared.
+     * This methods assumes that last part is location.
+     * @param r1 keys 
+     * @param r2 keys
+     * @return 0 for duplicates non zero for distinct keys 
+     */
+    protected int compare(DataValueDescriptor[] r1, DataValueDescriptor[] r2)
+    throws StandardException {
+        // Get the number of columns we have to compare.
+        int colsToCompare = columnOrdering.length;
+        int r;
+
+        // Compare the columns specified in the column
+        // ordering array.
+        boolean nonull = true;
+        for (int i = 0; i < colsToCompare; i++) {
+            if (i == colsToCompare - 1 && nonull)
+                return 0;
+            // Get columns to compare.
+            int colid = columnOrderingMap[i];
+            boolean nullsLow = columnOrderingNullsLowMap[i];
+            
+            // If the columns don't compare equal, we're done.
+            // Return the sense of the comparison.
+            if ((r = r1[colid].compare(r2[colid], nullsLow))
+            != 0) {
+                if (this.columnOrderingAscendingMap[i])
+                    return r;
+                else
+                    return -r;
+            } else {
+                if (r1[colid].isNull())
+                    nonull = false;
+            }
+        }
+        
+        // We made it through all the columns, and they must have
+        // all compared equal.  So return that the rows compare equal.
+        return 0;
+    }
+    
+}

Propchange: db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/sort/UniqueWithDuplicateNullsMergeSort.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/engine/org/apache/derby/modules.properties
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/modules.properties?rev=633560&r1=633559&r2=633560&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/modules.properties (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/modules.properties Tue Mar  4 09:38:48
2008
@@ -294,6 +294,9 @@
 derby.module.access.sort=org.apache.derby.impl.store.access.sort.ExternalSortFactory
 cloudscape.config.access.sort=all
 
+derby.module.access.uniquewithduplicatenullssort=org.apache.derby.impl.store.access.sort.UniqueWithDuplicateNullsExternalSortFactory
+cloudscape.config.access.uniquewithduplicatenullssort=all
+
 derby.module.access=org.apache.derby.impl.store.access.RllRAMAccessManager
 cloudscape.config.access=all
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out?rev=633560&r1=633559&r2=633560&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
Tue Mar  4 09:38:48 2008
@@ -544,7 +544,7 @@
 alter table t3 add column c3 int;
 0 rows inserted/updated/deleted
 ij> alter table t3 add unique(c3);
-ERROR 42831: 'C3' cannot be a column of a primary key or unique key because it can contain
null values.
+0 rows inserted/updated/deleted
 ij> -- add unique constraint to 0 and 1 row tables and verify 
 alter table t0_2 add column c2 int not null unique default 0;
 0 rows inserted/updated/deleted
@@ -564,7 +564,7 @@
 1 row inserted/updated/deleted
 ij> -- add unique constraint to table with > 1 row
 alter table t3 add unique(c1);
-ERROR 42831: 'C1' cannot be a column of a primary key or unique key because it can contain
null values.
+0 rows inserted/updated/deleted
 ij> -- verify prepared alter table dependent on underlying table
 prepare p1 as 'alter table xxx add check(c2 = 1)';
 ERROR 42Y55: 'ALTER TABLE' cannot be performed on 'XXX' because it does not exist.

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out?rev=633560&r1=633559&r2=633560&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
Tue Mar  4 09:38:48 2008
@@ -113,20 +113,6 @@
 0 rows inserted/updated/deleted
 ij> drop table customer;
 0 rows inserted/updated/deleted
-ij> -- Unique key constraint, DB2 requires NOT null on the columns.
-create table customer (id int unique, name char(100));
-ERROR 42831: 'ID' cannot be a column of a primary key or unique key because it can contain
null values.
-ij> create table customer (id  int NOT NULL, id2 int, constraint custCon unique(id, id2));
-ERROR 42831: 'ID2' cannot be a column of a primary key or unique key because it can contain
null values.
-ij> -- check they actually work!
-create table customer (id int NOT NULL primary key, name char(100));
-0 rows inserted/updated/deleted
-ij> drop table customer;
-0 rows inserted/updated/deleted
-ij> create table customer (id  int NOT NULL, id2 int NOT NULL, name char(100), primary
key (id, id2));
-0 rows inserted/updated/deleted
-ij> drop table customer;
-0 rows inserted/updated/deleted
 ij> -- drop schema requires restrict
 create schema fred;
 0 rows inserted/updated/deleted
@@ -1720,7 +1706,7 @@
 ij> alter table t1 add constraint pk1 primary key (c1);
 ERROR 42831: 'C1' cannot be a column of a primary key or unique key because it can contain
null values.
 ij> alter table t1 add constraint uc1 unique (c2);
-ERROR 42831: 'C2' cannot be a column of a primary key or unique key because it can contain
null values.
+0 rows inserted/updated/deleted
 ij> -- positive tests
 create table t2 (c1 int not null, c2 char(10) not null);
 0 rows inserted/updated/deleted

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out?rev=633560&r1=633559&r2=633560&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
Tue Mar  4 09:38:48 2008
@@ -4775,8 +4775,6 @@
 0 rows inserted/updated/deleted
 ij> CREATE INDEX "APP"."NEW_INDEX2" ON "APP"."COLLS" ("ID");
 0 rows inserted/updated/deleted
-ij> ALTER TABLE "APP"."COLLS" ADD CONSTRAINT "NEW_KEY2" UNIQUE ("ID", "COLLID");
-ERROR 42831: 'ID' cannot be a column of a primary key or unique key because it can contain
null values.
 ij> insert into colls values ('123', 2);
 1 row inserted/updated/deleted
 ij> insert into colls values ('124', -5);

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullableUniqueConstraintTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullableUniqueConstraintTest.java?rev=633560&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullableUniqueConstraintTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullableUniqueConstraintTest.java
Tue Mar  4 09:38:48 2008
@@ -0,0 +1,470 @@
+/**
+ *  Derby - Class org.apache.derbyTesting.functionTests.tests.lang.NullableUniqueConstraintTest
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Savepoint;
+import java.sql.Statement;
+import java.sql.Types;
+import java.util.Enumeration;
+
+import junit.framework.Test;
+import junit.framework.TestFailure;
+import junit.framework.TestResult;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Test unique constraint
+ */
+public class NullableUniqueConstraintTest extends BaseJDBCTestCase {
+    
+    /**
+     * Basic constructor.
+     */
+    public NullableUniqueConstraintTest(String name) {
+        super(name);
+    }
+    
+    /**
+     * Returns the implemented tests.
+     *
+     * @return An instance of <code>Test</code> with the implemented tests to
+     *         run.
+     */
+    public static Test suite() {
+        TestSuite suite = new TestSuite("NullableUniqueConstraintTest");
+        suite.addTest(TestConfiguration.defaultSuite(
+                            NullableUniqueConstraintTest.class));
+        return suite;
+    }
+    
+    /**
+     * Create table for test cases to use.
+     */
+    protected void setUp() throws Exception {
+        Connection con = getConnection();
+        Statement stmt = con.createStatement();
+        stmt.executeUpdate("create table constraintest (val1 varchar (20), " +
+                "val2 varchar (20), val3 varchar (20), val4 varchar (20))");
+    }
+    
+    protected void tearDown() throws Exception {
+        Connection con = getConnection();
+        con.commit ();
+        Statement stmt = con.createStatement();
+        stmt.executeUpdate("drop table constraintest");
+        stmt.close ();
+        con.commit ();
+        super.tearDown();
+    }
+    /**
+     * Basic test of Unique Constraint using single part key.
+     * @throws SQLException
+     */
+    public void testSingleKeyPartUniqueConstraint() throws SQLException {
+        Connection con = getConnection();
+        Statement stmt = con.createStatement();
+        //create unique constraint without not null
+        stmt.executeUpdate("alter table constraintest add constraint " +
+                "u_con unique (val1)");
+        PreparedStatement ps  = con.prepareStatement("insert into " +
+                "constraintest (val1, val2) values (?, ?)");
+        ps.setString(1, "test");
+        ps.setString(2, "should pass");
+        ps.execute();
+        try {
+            ps.setString(1, "test");
+            ps.setString(2, "should fail");
+            ps.execute();
+            fail("duplicate key inserted expected '23505'");
+        }
+        catch (SQLException e) {
+            assertSQLState("inserting duplicate", "23505", e);
+        }
+        ps.setNull(1, Types.VARCHAR);
+        ps.setString(2, "should pass");
+        ps.execute();
+        ps.setNull(1, Types.VARCHAR);
+        ps.setString(2, "should pass");
+        ps.execute();
+        //check if there are two record with val1=null
+        ResultSet rs = stmt.executeQuery("select count (*) from " +
+                "constraintest where val1 is null");
+        rs.next();
+        assertEquals("expected 2 rows", 2, rs.getInt(1));
+        //try creating constraint with existing value
+        stmt.execute("alter table constraintest drop constraint u_con");
+        stmt.execute("delete from constraintest where val1 is null");
+        con.commit ();
+        ps.setString(1, "test");
+        ps.setString(2, "removeit");
+        ps.execute();
+        //constraint dropped successfully
+        //create constraint - must fail
+        try {
+            stmt.executeUpdate("alter table constraintest add constraint " +
+                "u_con unique (val1)");
+            fail ("create unique constraint with duplicate key in " +
+                    "table should fail");
+        }
+        catch (SQLException e) {
+            assertSQLState("creating unique constraint when duplicate" +
+                    " keys are present  duplicate", "23505", e);
+        }
+        //remove duplicate record
+        stmt.execute ("delete from constraintest where val2 = 'removeit'");
+        //should be fine now
+        stmt.executeUpdate("alter table constraintest add constraint " +
+                "u_con unique (val1)");
+        con.commit();
+        stmt.close ();
+        ps.close();
+    }
+    
+    /**
+     * Basic test of Unique Constraint using multipart part key.
+     * @throws SQLException
+     */
+    public void testMultipartKeyUniqueConstraint() throws SQLException {
+        Connection con = getConnection();
+        Statement stmt = con.createStatement();
+        //create unique constraint without not null
+        stmt.executeUpdate("alter table constraintest add constraint " +
+                "u_con unique (val1, val2, val3)");
+        PreparedStatement ps  = con.prepareStatement("insert into " +
+                "constraintest (val1, val2, val3, val4) values (?, ?, ?, ?)");
+        ps.setString(1, "part1");
+        ps.setString(2, "part2");
+        ps.setString(3, "part3");
+        ps.setString(4, "should pass");
+        ps.execute();
+        try {
+            ps.setString(1, "part1");
+            ps.setString(2, "part2");
+            ps.setString(3, "part3");
+            ps.setString(4, "should fail");
+            ps.execute();
+            fail("duplicate key inserted expected '23505'");
+        }
+        catch (SQLException e) {
+            assertSQLState("inserting duplicate", "23505", e);
+        }
+        ps.setNull(1, Types.VARCHAR);
+        ps.setString(2, "part2");
+        ps.setString(3, "part3");
+        ps.setString(4, "should pass");
+        ps.execute();
+        ps.setNull(1, Types.VARCHAR);
+        ps.setString(2, "part2");
+        ps.setString(3, "part3");
+        ps.setString(4, "should pass");
+        ps.execute();
+        ps.setString(1, "part1");
+        ps.setNull(2, Types.VARCHAR);
+        ps.setString(3, "part3");
+        ps.setString(4, "should pass");
+        ps.execute();
+        //check if there are two record with val1=null
+        ResultSet rs = stmt.executeQuery("select count (*) from " +
+                "constraintest where val1 is null");
+        rs.next();
+        assertEquals("expected 2 rows", 2, rs.getInt(1));
+        //try creating constraint with existing value
+        stmt.execute("alter table constraintest drop constraint u_con");
+        con.commit ();
+        ps.setString(1, "part1");
+        ps.setString(2, "part2");
+        ps.setString(3, "part3");
+        ps.setString(4, "removeit");
+        ps.execute();
+        //constraint dropped successfully
+        //create constraint - must fail
+        try {
+            stmt.executeUpdate("alter table constraintest add constraint " +
+                "u_con unique (val1, val2, val3)");
+            fail ("create unique constraint with duplicate key in " +
+                    "table should fail");
+        }
+        catch (SQLException e) {
+            assertSQLState("creating unique constraint when duplicate" +
+                    " keys are present  duplicate", "23505", e);
+        }
+        //remove duplicate record
+        stmt.execute ("delete from constraintest where val4 = 'removeit'");
+        //should be fine now
+        stmt.executeUpdate("alter table constraintest add constraint " +
+                "u_con unique (val1, val2, val3)");
+        con.commit();
+        stmt.close ();
+        ps.close();
+    }
+    
+    /**
+     * Inserts a duplicate key of a deleted key within same transaction.
+     * @throws java.sql.SQLException
+     */
+    public void testWithDeletedKey() throws SQLException {
+        Connection con = getConnection();
+        Statement stmt = con.createStatement();
+        //create unique constraint without not null
+        stmt.executeUpdate("alter table constraintest add constraint " +
+                "u_con unique (val1, val2, val3)");
+        PreparedStatement ps  = con.prepareStatement("insert into " +
+                "constraintest (val1, val2, val3, val4) values (?, ?, ?, ?)");
+        ps.setString(1, "part1");
+        ps.setString(2, "part2");
+        ps.setString(3, "part3");
+        ps.setString(4, "should pass");
+        ps.execute();
+        //delete a record within transaction and try inserting same record
+        con.setAutoCommit(false);
+        stmt.executeUpdate("delete from constraintest where " +
+                "val1 = 'part1' and val2 = 'part2' and val3 = 'part3'");
+        //insert same record
+        ps.setString(1, "part1");
+        ps.setString(2, "part2");
+        ps.setString(3, "part3");
+        ps.setString(4, "should pass");
+        ps.execute();
+        stmt.close();
+        ps.close();
+        con.commit();
+    }
+    
+    public void testDistinctQuery() throws SQLException {
+        Connection con = getConnection();
+        Statement stmt = con.createStatement();
+        stmt.executeUpdate("alter table constraintest add constraint " +
+                "u_con unique (val1)");
+        PreparedStatement ps  = con.prepareStatement("insert into " +
+                "constraintest (val1) values (?)");
+        //insert 5 null keys
+        for (int i = 0; i < 5; i++) {
+            ps.setNull(1, Types.VARCHAR);
+            ps.executeUpdate();
+        }
+        
+        //insert 5 null keys
+        for (int i = 0; i < 5; i++) {
+            ps.setString(1, String.valueOf(i));
+            ps.executeUpdate();
+        }
+        ResultSet rs = stmt.executeQuery("select count (*) from constraintest");
+        rs.next();
+        assertEquals(10, rs.getInt(1));
+        rs.close ();
+
+        rs = stmt.executeQuery("select count (distinct (val1)) from " +
+                "constraintest");
+        rs.next();
+        assertEquals(5, rs.getInt(1));
+        rs.close ();
+    }
+    /**
+     * Test null ordering of the key in order by query.
+     * @throws java.sql.SQLException
+     */
+    public void testNullOrdering() throws SQLException {
+        Connection con = getConnection();
+        Statement stmt = con.createStatement();
+        stmt.executeUpdate("alter table constraintest add constraint " +
+                "u_con unique (val1)");
+        PreparedStatement ps  = con.prepareStatement("insert into " +
+                "constraintest (val1) values (?)");
+        //insert 5 null keys
+        for (int i = 0; i < 5; i++) {
+            ps.setNull(1, Types.VARCHAR);
+            ps.executeUpdate();
+        }
+        
+        //insert 5 non null keys
+        for (int i = 0; i < 5; i++) {
+            ps.setString(1, String.valueOf(i));
+            ps.executeUpdate();
+        }
+        
+        ResultSet rs = stmt.executeQuery("select val1 from constraintest " +
+                            "order by val1 nulls last");
+        //first 5 should be non null
+        for (int i = 0; i < 5; i++) {
+            rs.next();
+            assertEquals (String.valueOf(i), rs.getString(1));
+        }
+        
+        //next 5 should be null
+        for (int i = 0; i < 5; i++) {
+            rs.next();
+            assertEquals (null, rs.getString(1));
+        }
+        rs.close ();
+        rs = stmt.executeQuery("select val1 from constraintest " +
+                            "order by val1 nulls first");
+        //first 5 should be null
+        for (int i = 0; i < 5; i++) {
+            rs.next();
+            assertEquals (null, rs.getString(1));
+        }
+        
+        //next 5 should be null
+        for (int i = 0; i < 5; i++) {
+            rs.next();
+            assertEquals (String.valueOf(i), rs.getString(1));
+        }
+        rs.close ();
+    }
+    
+    /**
+     * Tries to forces internal routibe to travel across
+     * pages to check for duplicates. It first inserts large 
+     * number of records assuming they occupy multiple pages 
+     * in index and then tries to insert duplicates of each 
+     * of them. Rrecords at the page boundry will require 
+     * duplucate checking routine to check more than one page 
+     * to look for locate. If that routine is not working properly 
+     * duplucate will be inserted in tree.
+     * @throws java.sql.SQLException
+     */
+    public void testComparisonAcrossPages() throws SQLException {
+        Connection con = getConnection();
+        Statement stmt = con.createStatement();
+        //create unique constraint without not null
+        stmt.executeUpdate("alter table constraintest add constraint " +
+                "u_con unique (val1)");
+        PreparedStatement ps  = con.prepareStatement("insert into " +
+                "constraintest (val1, val2) values (?, ?)");
+        for (int i = 0; i < 500; i++) {
+            ps.setString(1, "" + i);
+            ps.setString (2, "" + i);
+            ps.execute();
+        }
+        
+        for (int i = 0; i < 500; i++) {
+            ps.setString(1, "" + i);
+            ps.setString (2, "" + i);
+            try {
+                ps.execute();
+                fail("duplicate key inserted expected '23505'");
+            }
+            catch (SQLException e) {
+                assertSQLState("inserting duplicate", "23505", e);  
+            }
+        }
+        //mark all records except for first, as deleted and try 
+        //inserting duplicate. This will force comparison 
+        //logic to scan all the records to find another rcord for 
+        //comparison.
+        con.setAutoCommit(false);
+        assertEquals (499, stmt.executeUpdate (
+                "delete from constraintest where val1 != '0'"));
+        Savepoint deleted = con.setSavepoint("deleted");
+        ps.setString(1, "0");
+        ps.setString (2, "test");
+        try {
+            ps.execute();
+            fail ("managed to insert a duplicate");
+        }
+        catch (SQLException e) {
+            assertSQLState("inserting duplicate", "23505",  e);
+        }
+        //rollback to check point and try to insert a record 
+        //at the middle
+        con.rollback(deleted);
+        ps.setString(1, "250");
+        ps.setString(2, "test");
+        ps.execute ();
+        //rollback to check point and try 
+        //inserting at end
+        con.rollback(deleted);
+        ps.setString(1, "499");
+        ps.setString (2, "test");
+        ps.execute ();
+
+        ResultSet rs = stmt.executeQuery("select count (*) from constraintest");
+        rs.next ();
+        assertEquals(2, rs.getInt(1));
+        
+        con.rollback ();
+        ps.close();
+        stmt.close();
+        ps.close();
+    }
+    
+    /**
+     * Checks is insert for updates uses deffered inserts or not. 
+     * It inserts two part keys in the form of
+     * part1 part2
+     * 1        1
+     * 1        2
+     * 1        3
+     * 2        1
+     * 2        2
+     * 2        3
+     * 3        1
+     * 3        2
+     * 3        3
+     * 
+     * and then tries to update all the records so that the values 
+     * part1 and part2 are interchanged. Internally updates are 
+     * treated as delete and insert and unless inserts are deffered 
+     * till all deletes are over, there will be unique constraint 
+     * violation.
+     * @throws java.sql.SQLException
+     */
+    public void testDefferedInsert() throws SQLException {
+        Connection con = getConnection();
+        Statement stmt = con.createStatement();
+        //create unique constraint without not null
+        stmt.executeUpdate("alter table constraintest add constraint " +
+                "u_con unique (val1, val2)");
+        PreparedStatement ps  = con.prepareStatement("insert into " +
+                "constraintest (val1, val2) values (?, ?)");
+        for (int i = 0; i < 5; i++) {
+            for (int j = 0; j < 5; j++) {
+                ps.setString(1, String.valueOf(i));
+                ps.setString(2, String.valueOf(j));
+                ps.executeUpdate();
+            }
+        }
+        //interchange the values of val1 and val2
+        //this will fail unless its handled by deffered inserts
+        assertEquals("updating 25 records", 25, 
+                stmt.executeUpdate("update constraintest set " +
+                "val1 = val2, val2 = val1"));
+    }
+    public static void main(String [] args) {
+        TestResult tr = new TestResult();
+        Test t = suite();
+        t.run(tr);
+        System.out.println(tr.errorCount());
+        Enumeration e = tr.failures();
+        while (e.hasMoreElements()) {
+            ((TestFailure)e.nextElement ()).thrownException().printStackTrace();
+        }
+        System.out.println(tr.failureCount());
+    }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullableUniqueConstraintTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PrimaryKeyTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PrimaryKeyTest.java?rev=633560&r1=633559&r2=633560&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PrimaryKeyTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PrimaryKeyTest.java
Tue Mar  4 09:38:48 2008
@@ -144,8 +144,6 @@
 		assertUpdateCount(s , 1 , "insert into pos1(c1) values(1)");
 		assertStatementError("23505" , s , "insert into pos1(c1) values(1)");
 		assertStatementError("23502" , s , "insert into pos1(c1) values(null)");
-		//-- verify that a unique key can not contain nulls
-		assertStatementError("42831" , s , "create table pos1 (c1 int unique, c2 int)");
 		assertUpdateCount(s , 0 , "drop table pos1");
 	}
 	/**

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=633560&r1=633559&r2=633560&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Tue Mar  4 09:38:48 2008
@@ -124,6 +124,7 @@
         suite.addTest(CommentTest.suite());
         suite.addTest(NestedWhereSubqueryTest.suite());
         suite.addTest(ConglomerateSharingTest.suite());
+        suite.addTest(NullableUniqueConstraintTest.suite());
         suite.addTest(OLAPTest.suite());
 
         // Add the XML tests, which exist as a separate suite

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql?rev=633560&r1=633559&r2=633560&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
Tue Mar  4 09:38:48 2008
@@ -85,20 +85,6 @@
 create table customer (id  int NOT NULL, id2 int, name char(100), primary key (id, id2));
 drop table customer;
 
--- Unique key constraint, DB2 requires NOT null on the columns.
-
-create table customer (id int unique, name char(100));
-
-create table customer (id  int NOT NULL, id2 int, constraint custCon unique(id, id2));
-
--- check they actually work!
-create table customer (id int NOT NULL primary key, name char(100));
-drop table customer;
-
-create table customer (id  int NOT NULL, id2 int NOT NULL, name char(100), primary key (id,
id2));
-drop table customer;
-
-
 -- drop schema requires restrict
 create schema fred;
 drop schema fred;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql?rev=633560&r1=633559&r2=633560&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql
Tue Mar  4 09:38:48 2008
@@ -284,7 +284,6 @@
 CREATE TABLE "APP"."COLLS" ("ID" VARCHAR(128), "COLLID" SMALLINT NOT NULL);
 CREATE INDEX "APP"."NEW_INDEX3" ON "APP"."COLLS" ("COLLID");
 CREATE INDEX "APP"."NEW_INDEX2" ON "APP"."COLLS" ("ID");
-ALTER TABLE "APP"."COLLS" ADD CONSTRAINT "NEW_KEY2" UNIQUE ("ID", "COLLID");
 insert into colls values ('123', 2);
 insert into colls values ('124', -5);
 insert into colls values ('24', 1);



Mime
View raw message