openjpa-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jrba...@apache.org
Subject svn commit: r957715 - in /openjpa/trunk: openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/schema/ openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/ openjpa-jdbc/src/main/resources/org/apache/openjpa/jdbc/sql/ openjpa-persistence-jdbc/src/test/j...
Date Thu, 24 Jun 2010 21:06:47 GMT
Author: jrbauer
Date: Thu Jun 24 21:06:47 2010
New Revision: 957715

URL: http://svn.apache.org/viewvc?rev=957715&view=rev
Log:
OPENJPA-1689 Provide better determination of PostgreSQL db owned sequences

Added:
    openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/sequence/SequencedEntity.java
  (with props)
    openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/sequence/TestDropAddSequence.java
  (with props)
Modified:
    openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/schema/SchemaGenerator.java
    openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java
    openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/PostgresDictionary.java
    openjpa/trunk/openjpa-jdbc/src/main/resources/org/apache/openjpa/jdbc/sql/localizer.properties
    openjpa/trunk/openjpa-persistence-jdbc/src/test/resources/META-INF/persistence.xml

Modified: openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/schema/SchemaGenerator.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/schema/SchemaGenerator.java?rev=957715&r1=957714&r2=957715&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/schema/SchemaGenerator.java
(original)
+++ openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/schema/SchemaGenerator.java
Thu Jun 24 21:06:47 2010
@@ -974,7 +974,7 @@ public class SchemaGenerator {
                     || seqUpper.startsWith("JDO_"))) // legacy
                 continue;
             if (_dict.isSystemSequence(sequenceName, sequenceSchema,
-                schemaName != null))
+                schemaName != null, conn))
                 continue;
             if (!isAllowedTable(sequenceSchema, null))
                 continue;

Modified: openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java?rev=957715&r1=957714&r2=957715&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java
(original)
+++ openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java
Thu Jun 24 21:06:47 2010
@@ -3866,6 +3866,23 @@ public class DBDictionary
     }
 
     /**
+     * This method is used to filter system sequences from database metadata.
+     * Return true if the given sequence represents a system sequence that
+     * should not appear in the schema definition. Returns true if system
+     * schema by default.
+     *
+     * @param name the table name
+     * @param schema the table schema; may be null
+     * @param targetSchema if true, then the given schema was listed by
+     * the user as one of his schemas
+     * @param conn connection to the database
+     */
+    public boolean isSystemSequence(DBIdentifier name, DBIdentifier schema,
+        boolean targetSchema, Connection conn) {
+        return isSystemSequence(name, schema, targetSchema);
+    }
+
+    /**
      * Reflect on the schema to find tables matching the given name pattern.
      * @deprecated
      */

Modified: openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/PostgresDictionary.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/PostgresDictionary.java?rev=957715&r1=957714&r2=957715&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/PostgresDictionary.java
(original)
+++ openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/PostgresDictionary.java
Thu Jun 24 21:06:47 2010
@@ -36,9 +36,13 @@ import java.text.ParseException;
 import java.text.SimpleDateFormat;
 import java.util.Arrays;
 import java.util.Date;
+import java.util.HashMap;
 import java.util.Map;
+import java.util.StringTokenizer;
+import java.util.Map.Entry;
 
 import org.apache.openjpa.jdbc.identifier.DBIdentifier;
+import org.apache.openjpa.jdbc.identifier.Normalizer;
 import org.apache.openjpa.jdbc.kernel.JDBCFetchConfiguration;
 import org.apache.openjpa.jdbc.kernel.JDBCStore;
 import org.apache.openjpa.jdbc.kernel.exps.FilterValue;
@@ -48,6 +52,7 @@ import org.apache.openjpa.jdbc.schema.Ta
 import org.apache.openjpa.kernel.Filters;
 import org.apache.openjpa.lib.jdbc.DelegatingConnection;
 import org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement;
+import org.apache.openjpa.lib.jdbc.ReportingSQLException;
 import org.apache.openjpa.lib.util.ConcreteClassGenerator;
 import org.apache.openjpa.lib.util.J2DoPrivHelper;
 import org.apache.openjpa.lib.util.Localizer;
@@ -124,6 +129,15 @@ public class PostgresDictionary
      * method.
      */
     public boolean supportsSetFetchSize = true;
+    
+    /**
+     * Statement used to determine whether a sequence is owned.  Owned 
+     * sequences are managed by the database and are considered system 
+     * sequences.
+     * parm 1: '<table_name.schema_name>'
+     * parm 2: '<column_name>'
+     */
+    public String isOwnedSequenceSQL = "SELECT pg_get_serial_sequence(?, ?)";
 
     public PostgresDictionary() {
         platform = "PostgreSQL";
@@ -378,12 +392,165 @@ public class PostgresDictionary
 
     public boolean isSystemSequence(DBIdentifier name, DBIdentifier schema,
         boolean targetSchema) {
+        return isSystemSequence(name, schema, targetSchema, null);
+    }
+    
+    public boolean isSystemSequence(DBIdentifier name, DBIdentifier schema,
+        boolean targetSchema, Connection conn) {
         if (super.isSystemSequence(name, schema, targetSchema))
             return true;
+        
+        if (isOwnedSequence(name, schema, conn)) {
+            return true;
+        }
+        return false;
+    }
+
+    /**
+     * Uses the native Postgres function pg_get_serial_sequence to determine whether
+     * a sequence is owned by the database.  Column types such as bigserial use a 
+     * system assigned sequence generator of the format: table_column_seq
+     * 
+     * @see http://www.postgresql.org/docs/current/static/functions-info.html
+     */
+    public boolean isOwnedSequence(DBIdentifier name, DBIdentifier schema, Connection conn)
{
+        
+        String strName = DBIdentifier.isNull(name) ? "" : name.getName();
+        // basic check for SEQ suffix.  not SEQ, not an owned sequence
+        if (strName == null || !strName.toUpperCase().endsWith("_SEQ"))
+            return false;
+
+        // If no connection, use secondary method to determine ownership
+        if (conn == null) {
+            return isOwnedSequence(strName);
+        }
+        
+        // Build permutations of table, column pairs from the provided 
+        // sequence name.  If any of them are determined owned, assume the 
+        // sequence is owned.  This is not perfect, but considerably better than
+        // considering all sequences suffixed with _seq are db owned.
+        String[][] namePairs = buildNames(strName);
+        try {
+            for (int i = 0; i < namePairs.length; i++) {
+                if (queryOwnership(conn, namePairs[i], schema)) {
+                    return true;
+                }
+            }
+        } catch (Throwable t) {
+            if (log.isWarnEnabled())
+                log.warn(_loc.get("psql-owned-seq-warning"), t);
+            return isOwnedSequence(strName);
+        }
+        return false;
+    }
+    
+    private boolean queryOwnership(Connection conn, String[] namePair,
+        DBIdentifier schema) throws Throwable {
+        PreparedStatement ps = null;
+        ResultSet rs = null;
+        try {
+            ps = prepareStatement(conn, isOwnedSequenceSQL);
+            String tblName = "";
+            if (!DBIdentifier.isEmpty(schema)) {
+                tblName = schema.getName() + getIdentifierDelimiter();  
+            }
+            tblName += namePair[0];
+            ps.setString(1, tblName);
+            String colName = toDBName(DBIdentifier.newColumn(namePair[1]));
+            ps.setString(2, colName);
+            ps.execute();
+            rs = ps.getResultSet();
+            if (rs == null || !rs.next()) {
+                return false;
+            }
+            String val = getString(rs, 1);
+            if (val == null || val.length() == 0) {
+                return false;
+            }
+            return true;
+        } catch (Throwable t) {
+            if (t instanceof ReportingSQLException) {
+                // Handle known/acceptable exceptions
+                // 42P01 - table does not exist
+                // 42703 - column does not exist within table
+                ReportingSQLException rse = (ReportingSQLException)t;
+                if ("42P01".equals(rse.getSQLState()) ||
+                    "42703".equals(rse.getSQLState())) {
+                    return false;
+                }
+            }
+            throw t;
+        }
+        finally {
+            if (rs != null) {
+                try {
+                    rs.close();
+                } catch (Throwable t) {}
+            }
+            if (ps != null) {
+                try {
+                    ps.close();
+                } catch (Throwable t) {}
+            }
+        }
+    }
+
+    /**
+     * Owned sequences are of the form <table>_<col>_seq. Table and column 
+     * names can contain underscores so permutations of these names must be 
+     * produced for ownership verification.
+     * @param strName
+     * @return
+     */
+    private String[][] buildNames(String strName) {
+        // split the sequence name into components
+        // owned sequences are of the form <table>_<col>_seq
+        String[] parts = Normalizer.splitName(strName, "_");
+        
+        if (parts == null || parts.length < 3) {
+            return null;
+        }
+        // Simple and most common case
+        if (parts.length == 3) {
+            return new String[][] { {parts[0], parts[1]} };
+        }
+        // If table or column names contain underscores, build a list
+        // of possibilities
+        String[][] names = new String[(parts.length - 2)][2];
+        for (int i = 0; i < parts.length - 2; i++) {
+            String[] namePair = new String[2];
+            StringBuilder name0 = new StringBuilder();
+            StringBuilder name1 = new StringBuilder();
+            for (int j = 0; j < parts.length - 1; j++) {
+                if (j <= i) {
+                    name0.append(parts[j]);
+                    if (j < i) {
+                        name0.append("_");
+                    }
+                } else {
+                    name1.append(parts[j]);
+                    if (j < parts.length - 2) {
+                        name1.append("_");
+                    }
+                }
+            }
+            namePair[0] = name0.toString();
+            namePair[1] = name1.toString();
+            names[i] = namePair;
+        }
+        return names;
+    }
 
+    /**
+     * Secondary logic if owned sequences cannot be determined by calling the
+     * db.  This logic assumes that any sequence prefixed with _SEQ is an
+     * owned sequence (identical to the behavior of prior versions of OpenJPA).
+     * @param strName
+     * @return
+     */
+    private boolean isOwnedSequence(String strName) {
         // filter out generated sequences used for bigserial cols, which are
         // of the form <table>_<col>_seq
-        String strName = DBIdentifier.isNull(name) ? "" : name.getName();
         int idx = (strName == null) ? -1 : strName.indexOf('_');
         return idx != -1 && idx != strName.length() - 4
             && strName.toUpperCase().endsWith("_SEQ");

Modified: openjpa/trunk/openjpa-jdbc/src/main/resources/org/apache/openjpa/jdbc/sql/localizer.properties
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/resources/org/apache/openjpa/jdbc/sql/localizer.properties?rev=957715&r1=957714&r2=957715&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/main/resources/org/apache/openjpa/jdbc/sql/localizer.properties
(original)
+++ openjpa/trunk/openjpa-jdbc/src/main/resources/org/apache/openjpa/jdbc/sql/localizer.properties
Thu Jun 24 21:06:47 2010
@@ -207,4 +207,8 @@ unknown-delim-support: Unable to determi
 can_not_get_current_schema: Unable to get current schema. SQLException message is "{0}".
 cannot-determine-identifier-base-case: Unable to determine the case to use for \
     identifiers.  The default value of "{0}" will be used.
-can-not-execute: Unable to execute {0}.
\ No newline at end of file
+can-not-execute: Unable to execute {0}.
+psql-owned-seq-warning: Unable to determine which sequences are owned by the database. \
+	OpenJPA will consider all sequences suffixed with "_seq" as database managed.  This
+	may result in improper creation or removal of sequences with this suffix. The \
+	original PostgreSQL driver exception is being logged for your reference.
\ No newline at end of file

Added: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/sequence/SequencedEntity.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/sequence/SequencedEntity.java?rev=957715&view=auto
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/sequence/SequencedEntity.java
(added)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/sequence/SequencedEntity.java
Thu Jun 24 21:06:47 2010
@@ -0,0 +1,82 @@
+/*
+ * 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.openjpa.persistence.sequence;
+
+import javax.persistence.Column;
+import javax.persistence.Entity;
+import javax.persistence.GeneratedValue;
+import javax.persistence.GenerationType;
+import javax.persistence.Id;
+import javax.persistence.SequenceGenerator;
+import javax.persistence.Table;
+
+@Entity
+@Table(name="SEQENTITY_TBL")
+@SequenceGenerator(name="SeqEntity", sequenceName="SEQENTITY_ntv_seq", allocationSize = 1)
+public class SequencedEntity {
+
+    @Id
+    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SeqEntity")
+    private int id;
+
+    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SeqEntity2")
+    @SequenceGenerator(name="SeqEntity2", sequenceName="SEQENTITY_TBL_gval1_seq", allocationSize
= 1)
+    private int gval1;
+    
+    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SeqEntity3")
+    @SequenceGenerator(name="SeqEntity3", sequenceName="SEQENTITY_TBL_g_val2_seq", allocationSize
= 1)
+    @Column(name="g_val2")
+    private int gval2;
+
+    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SeqEntity4")
+    @SequenceGenerator(name="SeqEntity4", sequenceName="SEQENTITY_gval3_seq", allocationSize
= 1)
+    private int gval3;
+
+    public void setId(int id) {
+        this.id = id;
+    }
+
+    public int getId() {
+        return id;
+    }
+
+    public void setGval2(int gval) {
+        this.gval2 = gval;
+    }
+
+    public int getGval2() {
+        return gval2;
+    }
+
+    public void setGval1(int gval1) {
+        this.gval1 = gval1;
+    }
+
+    public int getGval1() {
+        return gval1;
+    }
+
+    public void setGval3(int gval3) {
+        this.gval3 = gval3;
+    }
+
+    public int getGval3() {
+        return gval3;
+    }
+}

Propchange: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/sequence/SequencedEntity.java
------------------------------------------------------------------------------
    svn:eol-style = native

Added: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/sequence/TestDropAddSequence.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/sequence/TestDropAddSequence.java?rev=957715&view=auto
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/sequence/TestDropAddSequence.java
(added)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/sequence/TestDropAddSequence.java
Thu Jun 24 21:06:47 2010
@@ -0,0 +1,93 @@
+/*
+ * 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.openjpa.persistence.sequence;
+
+import org.apache.openjpa.jdbc.conf.JDBCConfiguration;
+import org.apache.openjpa.jdbc.sql.DBDictionary;
+import org.apache.openjpa.jdbc.sql.PostgresDictionary;
+import org.apache.openjpa.persistence.OpenJPAEntityManager;
+import org.apache.openjpa.persistence.OpenJPAEntityManagerFactorySPI;
+import org.apache.openjpa.persistence.test.SingleEMFTestCase;
+
+/**
+ * Tests the drop then add schema action for SynchronizeMappings when a
+ * native sequence suffixed with "_seq" is in use.  This test only runs when 
+ * the configured database supports native sequences.
+ */
+public class TestDropAddSequence extends SingleEMFTestCase {
+    
+    @Override
+    public void setUp() throws Exception {
+
+        // Create a basic emf to determine whether sequences are supported.
+        // If so, run the normal setup with the test PU.
+        OpenJPAEntityManagerFactorySPI tempEMF = createNamedEMF("test");
+        try {
+            if (!supportsSequences(tempEMF)) {
+                return;
+            }
+        } finally {
+            if (tempEMF != null) {
+                tempEMF.close();
+            }
+        }
+        super.setUp();
+        // Force creation of the base schema artifacts including the base
+        // sequences (add, no drop)
+        emf.createEntityManager().close();
+    }
+
+    @Override
+    protected String getPersistenceUnitName() {
+        return "TestDropAddSequence";
+    }
+
+    
+    /**
+     * Verifies a new EMF can be created when the runtime forward mapping tool 
+     * is enabled with drop then add schema action when an entity contains a
+     * named native sequence suffixed with "_seq".
+     */
+    public void testDropAddSequence() {
+        
+        if (!supportsSequences(emf)) {
+            return;
+        }
+        
+        Object[] props = new Object[] { "openjpa.jdbc.SynchronizeMappings",
+            "buildSchema(SchemaAction='drop,add')" };
+        OpenJPAEntityManagerFactorySPI oemf = createNamedEMF("TestDropAddSequence",props);
+        
+        OpenJPAEntityManager em = oemf.createEntityManager();
+        
+        em.close();
+        oemf.close();
+    }
+    
+    private boolean supportsSequences(OpenJPAEntityManagerFactorySPI oemf) {
+        if (oemf == null) {
+            return false;
+        }
+        DBDictionary dict = ((JDBCConfiguration)oemf.getConfiguration()).getDBDictionaryInstance();
+        if (dict != null) {
+            return dict.nextSequenceQuery != null;
+        }
+        return false;
+    }
+}

Propchange: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/sequence/TestDropAddSequence.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/resources/META-INF/persistence.xml
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/resources/META-INF/persistence.xml?rev=957715&r1=957714&r2=957715&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/resources/META-INF/persistence.xml (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/resources/META-INF/persistence.xml Thu
Jun 24 21:06:47 2010
@@ -365,4 +365,12 @@
         </properties>
     </persistence-unit>
 
+    <persistence-unit name="TestDropAddSequence">
+        <class>org.apache.openjpa.persistence.sequence.SequencedEntity</class>
+        <exclude-unlisted-classes>true</exclude-unlisted-classes>
+        <properties>
+            <property name="openjpa.jdbc.SynchronizeMappings"
+                value="buildSchema"/>
+        </properties>
+    </persistence-unit>
 </persistence>



Mime
View raw message