You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@openjpa.apache.org by jr...@apache.org on 2010/06/24 23:06:47 UTC
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...
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>