You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@sis.apache.org by de...@apache.org on 2019/02/02 18:14:23 UTC
[sis] branch geoapi-4.0 updated (aa7e1ec -> e10a42b)
This is an automated email from the ASF dual-hosted git repository.
desruisseaux pushed a change to branch geoapi-4.0
in repository https://gitbox.apache.org/repos/asf/sis.git.
from aa7e1ec Fixed various problem in axis order, especially when computing a localization grid.
new 4db1dea Update database driver dependencies.
new e10a42b Fix a cast error when using EPSG database on PostgreSQL. The error message was: operator does not exist: "EPSG"."Table Name" = character varying
The 2 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
Summary of changes:
.../sis/internal/metadata/sql/SQLUtilities.java | 33 +++++
.../referencing/factory/sql/EPSGDataAccess.java | 146 +++++++++++----------
.../sis/referencing/factory/sql/EPSGInstaller.java | 2 +-
.../sis/referencing/factory/sql/SQLTranslator.java | 124 ++++++++++++-----
.../sis/referencing/factory/sql/TableInfo.java | 36 ++++-
.../sis/referencing/factory/TestFactorySource.java | 23 +++-
.../referencing/factory/sql/EPSGFactoryTest.java | 11 +-
.../referencing/factory/sql/TableInfoTest.java} | 20 ++-
.../sis/test/suite/ReferencingTestSuite.java | 1 +
ide-project/NetBeans/nbproject/project.properties | 4 +-
pom.xml | 2 +-
.../apache/sis/internal/sql/feature/Analyzer.java | 18 +--
12 files changed, 270 insertions(+), 150 deletions(-)
copy core/{sis-metadata/src/test/java/org/apache/sis/internal/metadata/sql/TypeMapperTest.java => sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/TableInfoTest.java} (66%)
[sis] 01/02: Update database driver dependencies.
Posted by de...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
desruisseaux pushed a commit to branch geoapi-4.0
in repository https://gitbox.apache.org/repos/asf/sis.git
commit 4db1dea1b78cce12b771a65471c9d8703e478278
Author: Martin Desruisseaux <ma...@geomatys.com>
AuthorDate: Fri Feb 1 15:15:32 2019 +0100
Update database driver dependencies.
---
ide-project/NetBeans/nbproject/project.properties | 4 ++--
pom.xml | 2 +-
2 files changed, 3 insertions(+), 3 deletions(-)
diff --git a/ide-project/NetBeans/nbproject/project.properties b/ide-project/NetBeans/nbproject/project.properties
index 3e80b81..bfc691f 100644
--- a/ide-project/NetBeans/nbproject/project.properties
+++ b/ide-project/NetBeans/nbproject/project.properties
@@ -126,8 +126,8 @@ junit.version = 4.12
hamcrest.version = 1.3
jaxb-ns-mapper = 2.2.4
derby.version = 10.14.2.0
-hsqldb.version = 2.4.0
-postgresql.version = 42.2.2
+hsqldb.version = 2.4.1
+postgresql.version = 42.2.5
icons.version = 3.0.1
#
diff --git a/pom.xml b/pom.xml
index 51daaeb..68304ae 100644
--- a/pom.xml
+++ b/pom.xml
@@ -469,7 +469,7 @@
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
- <version>42.2.4</version>
+ <version>42.2.5</version>
<scope>test</scope>
</dependency>
[sis] 02/02: Fix a cast error when using EPSG database on
PostgreSQL. The error message was: operator does not exist: "EPSG"."Table
Name" = character varying
Posted by de...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
desruisseaux pushed a commit to branch geoapi-4.0
in repository https://gitbox.apache.org/repos/asf/sis.git
commit e10a42bc686edae0d8733ee7c1715626c87a25e0
Author: Martin Desruisseaux <ma...@geomatys.com>
AuthorDate: Sat Feb 2 15:19:03 2019 +0100
Fix a cast error when using EPSG database on PostgreSQL.
The error message was: operator does not exist: "EPSG"."Table Name" = character varying
---
.../sis/internal/metadata/sql/SQLUtilities.java | 33 +++++
.../referencing/factory/sql/EPSGDataAccess.java | 146 +++++++++++----------
.../sis/referencing/factory/sql/EPSGInstaller.java | 2 +-
.../sis/referencing/factory/sql/SQLTranslator.java | 124 ++++++++++++-----
.../sis/referencing/factory/sql/TableInfo.java | 36 ++++-
.../sis/referencing/factory/TestFactorySource.java | 23 +++-
.../referencing/factory/sql/EPSGFactoryTest.java | 11 +-
.../sis/referencing/factory/sql/TableInfoTest.java | 42 ++++++
.../sis/test/suite/ReferencingTestSuite.java | 1 +
.../apache/sis/internal/sql/feature/Analyzer.java | 18 +--
10 files changed, 300 insertions(+), 136 deletions(-)
diff --git a/core/sis-metadata/src/main/java/org/apache/sis/internal/metadata/sql/SQLUtilities.java b/core/sis-metadata/src/main/java/org/apache/sis/internal/metadata/sql/SQLUtilities.java
index 552d08a..68c43f5 100644
--- a/core/sis-metadata/src/main/java/org/apache/sis/internal/metadata/sql/SQLUtilities.java
+++ b/core/sis-metadata/src/main/java/org/apache/sis/internal/metadata/sql/SQLUtilities.java
@@ -96,6 +96,39 @@ public final class SQLUtilities extends Static {
}
/**
+ * Returns the given pattern with {@code '_'} and {@code '%'} characters escaped by the database-specific
+ * escape characters. This method should be invoked for escaping the values of all {@link DatabaseMetaData}
+ * method arguments with a name ending by {@code "Pattern"}. Note that not all arguments are pattern; please
+ * checks carefully {@link DatabaseMetaData} javadoc for each method.
+ *
+ * <div class="note"><b>Example:</b> if a method expects an argument named {@code tableNamePattern},
+ * then that argument value should be escaped. But if the argument name is only {@code tableName},
+ * then the value should not be escaped.</div>
+ *
+ * @param pattern the pattern to escape, or {@code null} if none.
+ * @param escape value of {@link DatabaseMetaData#getSearchStringEscape()}.
+ * @return escaped strings, or the same instance than {@code pattern} if there is no character to escape.
+ */
+ public static String escape(final String pattern, final String escape) {
+ if (pattern != null) {
+ StringBuilder buffer = null;
+ for (int i = pattern.length(); --i >= 0;) {
+ final char c = pattern.charAt(i);
+ if (c == '_' || c == '%') {
+ if (buffer == null) {
+ buffer = new StringBuilder(pattern);
+ }
+ buffer.insert(i, escape);
+ }
+ }
+ if (buffer != null) {
+ return buffer.toString();
+ }
+ }
+ return pattern;
+ }
+
+ /**
* Returns a SQL LIKE pattern for the given identifier. The identifier is optionally returned in all lower cases
* for allowing case-insensitive searches. Punctuations are replaced by any sequence of characters ({@code '%'})
* and non-ASCII letters or digits are replaced by any single character ({@code '_'}). This method avoid to put
diff --git a/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/EPSGDataAccess.java b/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/EPSGDataAccess.java
index 8a4fa56..3fdec31 100644
--- a/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/EPSGDataAccess.java
+++ b/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/EPSGDataAccess.java
@@ -698,6 +698,7 @@ codes: for (int i=0; i<codes.length; i++) {
do {
PreparedStatement stmt;
if (alias) {
+ // Note: "OBJECT_TABLE_NAME=?" is handled in a special way by SQLTranslator.
stmt = prepareStatement("AliasKey", "SELECT OBJECT_CODE, ALIAS FROM [Alias] WHERE OBJECT_TABLE_NAME=? AND ALIAS LIKE ?");
stmt.setString(1, table);
stmt.setString(2, pattern);
@@ -791,9 +792,10 @@ codes: for (int i=0; i<codes.length; i++) {
*/
private ResultSet executeQuery(final String table, final String sql, final int... codes) throws SQLException {
assert Thread.holdsLock(this);
+ assert CharSequences.count(sql, '?') == codes.length;
PreparedStatement stmt = prepareStatement(table, sql);
// Partial check that the statement is for the right SQL query.
- assert stmt.getParameterMetaData().getParameterCount() == CharSequences.count(sql, '?');
+ assert stmt.getParameterMetaData().getParameterCount() == codes.length;
for (int i=0; i<codes.length; i++) {
stmt.setInt(i+1, codes[i]);
}
@@ -801,6 +803,27 @@ codes: for (int i=0; i<codes.length; i++) {
}
/**
+ * Executes a query of the form {@code "SELECT … FROM Alias WHERE OBJECT_TABLE_NAME=? AND OBJECT_CODE=?"}.
+ * The first argument shall be the name of a database table.
+ *
+ * @param key a key uniquely identifying the caller.
+ * @param sql the SQL statement to use for creating the {@link PreparedStatement} object.
+ * @param table the table to set in the first parameter.
+ * @param code the object code to set in the second parameter.
+ * @return the result of the query.
+ * @throws SQLException if an error occurred while querying the database.
+ */
+ private ResultSet executeMetadataQuery(final String key, final String sql, final String table, final int code) throws SQLException {
+ assert Thread.holdsLock(this);
+ assert CharSequences.count(sql, '?') == 2;
+ PreparedStatement stmt = prepareStatement(key, sql);
+ assert stmt.getParameterMetaData().getParameterCount() == 2;
+ stmt.setString(1, table);
+ stmt.setInt(2, code);
+ return stmt.executeQuery();
+ }
+
+ /**
* Returns the cached statement or create a new one for the given table.
* The {@code table} argument shall be a key uniquely identifying the caller.
* The {@code sql} argument is used for preparing a new statement if no cached instance exists.
@@ -1009,28 +1032,6 @@ codes: for (int i=0; i<codes.length; i++) {
}
/**
- * Returns {@code true} if the given table {@code name} matches the {@code expected} name.
- * The given {@code name} may be prefixed by {@code "epsg_"} and may contain abbreviations of the full name.
- * For example {@code "epsg_coordoperation"} is considered as a match for {@code "Coordinate_Operation"}.
- *
- * <p>The table name should be one of the values enumerated in the {@code epsg_table_name} type of the
- * {@code EPSG_Prepare.sql} file.</p>
- *
- * @param expected the expected table name (e.g. {@code "Coordinate_Operation"}).
- * @param name the actual table name.
- * @return whether the given {@code name} is considered to match the expected name.
- */
- static boolean tableMatches(final String expected, String name) {
- if (name == null) {
- return false;
- }
- if (name.startsWith(SQLTranslator.TABLE_PREFIX)) {
- name = name.substring(SQLTranslator.TABLE_PREFIX.length());
- }
- return CharSequences.isAcronymForWords(name, expected);
- }
-
- /**
* Logs a warning saying that the given code is deprecated and returns the code of the proposed replacement.
*
* @param table the table of the deprecated code.
@@ -1040,16 +1041,15 @@ codes: for (int i=0; i<codes.length; i++) {
private String getSupersession(final String table, final Integer code, final Locale locale) throws SQLException {
String reason = null;
Object replacedBy = null;
- try (ResultSet result = executeQuery("Deprecation",
- "SELECT OBJECT_TABLE_NAME, DEPRECATION_REASON, REPLACED_BY" +
- " FROM [Deprecation] WHERE OBJECT_CODE = ?", code))
+ try (ResultSet result = executeMetadataQuery("Deprecation",
+ "SELECT DEPRECATION_REASON, REPLACED_BY FROM [Deprecation]" +
+ " WHERE OBJECT_TABLE_NAME=? AND OBJECT_CODE=?", table, code))
+ // Note: "OBJECT_TABLE_NAME=?" is handled in a special way by SQLTranslator.
{
while (result.next()) {
- if (tableMatches(table, result.getString(1))) {
- reason = getOptionalString (result, 2);
- replacedBy = getOptionalInteger(result, 3);
- break;
- }
+ reason = getOptionalString (result, 1);
+ replacedBy = getOptionalInteger(result, 2);
+ if (replacedBy != null) break; // Prefer the first record providing a replacement.
}
}
if (replacedBy == null) {
@@ -1064,7 +1064,7 @@ codes: for (int i=0; i<codes.length; i++) {
*/
String method = "create";
for (final TableInfo info : TableInfo.EPSG) {
- if (tableMatches(info.table, table)) {
+ if (TableInfo.tableMatches(info.table, table)) {
method += info.type.getSimpleName();
break;
}
@@ -1106,31 +1106,30 @@ codes: for (int i=0; i<codes.length; i++) {
* convenient for implementing accent-insensitive searches.
*/
final List<GenericName> aliases = new ArrayList<>();
- try (ResultSet result = executeQuery("Alias",
- "SELECT OBJECT_TABLE_NAME, NAMING_SYSTEM_NAME, ALIAS" +
+ try (ResultSet result = executeMetadataQuery("Alias",
+ "SELECT NAMING_SYSTEM_NAME, ALIAS" +
" FROM [Alias] INNER JOIN [Naming System]" +
" ON [Alias].NAMING_SYSTEM_CODE =" +
" [Naming System].NAMING_SYSTEM_CODE" +
- " WHERE OBJECT_CODE = ?", code))
+ " WHERE OBJECT_TABLE_NAME=? AND OBJECT_CODE=?", table, code))
+ // Note: "OBJECT_TABLE_NAME=?" is handled in a special way by SQLTranslator.
{
while (result.next()) {
- if (tableMatches(table, result.getString(1))) {
- final String naming = getOptionalString(result, 2);
- final String alias = getString(code, result, 3);
- NameSpace ns = null;
- if (naming != null) {
- ns = namingSystems.get(naming);
- if (ns == null) {
- ns = owner.nameFactory.createNameSpace(owner.nameFactory.createLocalName(null, naming), null);
- namingSystems.put(naming, ns);
- }
- }
- if (CharSequences.toASCII(alias).toString().equals(name)) {
- name = alias;
- } else {
- aliases.add(owner.nameFactory.createLocalName(ns, alias));
+ final String naming = getOptionalString(result, 1);
+ final String alias = getString(code, result, 2);
+ NameSpace ns = null;
+ if (naming != null) {
+ ns = namingSystems.get(naming);
+ if (ns == null) {
+ ns = owner.nameFactory.createNameSpace(owner.nameFactory.createLocalName(null, naming), null);
+ namingSystems.put(naming, ns);
}
}
+ if (CharSequences.toASCII(alias).toString().equals(name)) {
+ name = alias;
+ } else {
+ aliases.add(owner.nameFactory.createLocalName(ns, alias));
+ }
}
}
/*
@@ -3232,6 +3231,7 @@ next: while (r.next()) {
if (namePatterns != null) {
buffer.setLength(0);
buffer.append("SELECT OBJECT_CODE FROM [Alias] WHERE OBJECT_TABLE_NAME='Datum'");
+ // PostgreSQL does not require explicit cast when the value is a literal instead than "?".
String separator = " AND (";
for (final String pattern : namePatterns) {
appendFilterByName(buffer.append(separator), "ALIAS", pattern);
@@ -3310,7 +3310,7 @@ next: while (r.next()) {
buffer.append(table.codeColumn); // Only for making order determinist.
/*
* At this point the SQL query is complete. Run it, preserving order.
- * The sort the result by taking in account the supersession table.
+ * Then sort the result by taking in account the supersession table.
*/
final Set<String> result = new LinkedHashSet<>(); // We need to preserve order in this set.
try (ResultSet r = stmt.executeQuery(translator.apply(buffer.toString()))) {
@@ -3321,7 +3321,7 @@ next: while (r.next()) {
result.remove(null); // Should not have null element, but let be safe.
if (result.size() > 1) {
final Object[] id = result.toArray();
- if (sort(table.codeColumn, id)) {
+ if (sort(table.tableName(), id)) {
result.clear();
for (final Object c : id) {
result.add((String) c);
@@ -3478,27 +3478,31 @@ next: while (r.next()) {
do {
boolean changed = false;
for (int i=0; i<codes.length; i++) {
- final String code = codes[i].toString();
- try (ResultSet result = executeQuery("Supersession", null, null,
- "SELECT OBJECT_TABLE_NAME, SUPERSEDED_BY" +
- " FROM [Supersession]" +
- " WHERE OBJECT_CODE = ?" +
- " ORDER BY SUPERSESSION_YEAR DESC", code))
+ final int code;
+ try {
+ code = Integer.parseInt(codes[i].toString());
+ } catch (NumberFormatException e) {
+ unexpectedException("sort", e);
+ continue;
+ }
+ // Note: "OBJECT_TABLE_NAME=?" is handled in a special way by SQLTranslator.
+ try (ResultSet result = executeMetadataQuery("Supersession",
+ "SELECT SUPERSEDED_BY FROM [Supersession]" +
+ " WHERE OBJECT_TABLE_NAME=? AND OBJECT_CODE=?" +
+ " ORDER BY SUPERSESSION_YEAR DESC", table, code))
{
while (result.next()) {
- if (tableMatches(table, result.getString(1))) {
- final String replacement = result.getString(2);
- if (replacement != null) {
- for (int j=i+1; j<codes.length; j++) {
- final Object candidate = codes[j];
- if (replacement.equals(candidate.toString())) {
- /*
- * Found a code to move in front of the superceded one.
- */
- System.arraycopy(codes, i, codes, i+1, j-i);
- codes[i++] = candidate;
- changed = true;
- }
+ final String replacement = result.getString(1);
+ if (replacement != null) {
+ for (int j=i+1; j<codes.length; j++) {
+ final Object candidate = codes[j];
+ if (replacement.equals(candidate.toString())) {
+ /*
+ * Found a code to move in front of the superceded one.
+ */
+ System.arraycopy(codes, i, codes, i+1, j-i);
+ codes[i++] = candidate;
+ changed = true;
}
}
}
diff --git a/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/EPSGInstaller.java b/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/EPSGInstaller.java
index a226514..520fe75 100644
--- a/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/EPSGInstaller.java
+++ b/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/EPSGInstaller.java
@@ -162,7 +162,7 @@ final class EPSGInstaller extends ScriptRunner {
addReplacement(SQLTranslator.TABLE_PREFIX + "datum_kind", TableInfo.ENUM_REPLACEMENT);
addReplacement(SQLTranslator.TABLE_PREFIX + "crs_kind", TableInfo.ENUM_REPLACEMENT);
addReplacement(SQLTranslator.TABLE_PREFIX + "cs_kind", TableInfo.ENUM_REPLACEMENT);
- addReplacement(SQLTranslator.TABLE_PREFIX + "table_name", "VARCHAR(80)");
+ addReplacement(SQLTranslator.TABLE_PREFIX + "table_name", TableInfo.ENUM_REPLACEMENT);
}
}
diff --git a/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/SQLTranslator.java b/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/SQLTranslator.java
index dcf96ee..642df50 100644
--- a/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/SQLTranslator.java
+++ b/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/SQLTranslator.java
@@ -29,6 +29,7 @@ import org.apache.sis.util.ArgumentChecks;
import org.apache.sis.util.resources.Errors;
import org.apache.sis.internal.util.Constants;
import org.apache.sis.internal.metadata.sql.Reflection;
+import org.apache.sis.internal.metadata.sql.SQLUtilities;
/**
@@ -100,7 +101,7 @@ import org.apache.sis.internal.metadata.sql.Reflection;
* @author Martin Desruisseaux (IRD)
* @author Didier Richard (IGN)
* @author John Grange
- * @version 0.8
+ * @version 1.0
* @since 0.7
* @module
*/
@@ -131,13 +132,20 @@ public class SQLTranslator implements Function<String,String> {
/**
* The columns that may be of {@code BOOLEAN} type instead of {@code SMALLINT}.
*/
- private static final String[] BOOLEAN_FIELDS = {
+ private static final String[] BOOLEAN_COLUMNS = {
"SHOW_CRS",
"SHOW_OPERATION",
"DEPRECATED"
};
/**
+ * The column where {@code VARCHAR} value may need to be casted to an enumeration.
+ * With PostgreSQL, only columns in the {@code WHERE} part of the SQL statement needs
+ * an explicit cast; the columns in the {@code SELECT} part are implicitly casted.
+ */
+ private static final String ENUMERATION_COLUMN = "OBJECT_TABLE_NAME"; // In "Alias" table.
+
+ /**
* The name of the catalog that contains the EPSG tables, or {@code null} or an empty string.
* <ul>
* <li>The {@code ""} value retrieves the EPSG schema without a catalog.</li>
@@ -177,7 +185,8 @@ public class SQLTranslator implements Function<String,String> {
/**
* Mapping from words used in the MS-Access database to words used in the ANSI versions of EPSG databases.
* A word may be a table or a column name, or a part of it. A table name may consist in many words separated
- * by spaces.
+ * by spaces. This map does not list all tables used in EPSG schema, but only the ones that can not be mapped
+ * by more generic code (e.g. by replacing spaces by '_').
*
* <p>The keys are the names in the MS-Access database, and the values are the names in the SQL scripts.
* By convention, all column names in keys are in upper-case while table names are in mixed-case characters.</p>
@@ -200,6 +209,13 @@ public class SQLTranslator implements Function<String,String> {
private final String quote;
/**
+ * Non-null if the {@value #ENUMERATION_COLUMN} column in {@code "Alias"} table uses enumeration instead
+ * than character varying. In such case, this field contains the enumeration type. If {@code null}, then
+ * then column type is {@code VARCHAR} and the cast can be omitted.
+ */
+ private String tableNameEnum;
+
+ /**
* {@code true} if the database uses the {@code BOOLEAN} type instead than {@code SMALLINT}
* for the {@code show_crs}, {@code show_operation} and all {@code deprecated} fields.
*
@@ -254,12 +270,14 @@ public class SQLTranslator implements Function<String,String> {
*/
final void setup(final DatabaseMetaData md) throws SQLException {
final boolean toUpperCase = md.storesUpperCaseIdentifiers();
+ final String escape = md.getSearchStringEscape();
+ String schemaPattern = SQLUtilities.escape(schema, escape);
for (int i = SENTINEL.length; --i >= 0;) {
String table = SENTINEL[i];
if (toUpperCase && i != MIXED_CASE) {
table = table.toUpperCase(Locale.US);
}
- try (ResultSet result = md.getTables(catalog, schema, table, null)) {
+ try (ResultSet result = md.getTables(catalog, schemaPattern, table, null)) {
if (result.next()) {
isTableFound = true;
isPrefixed = table.startsWith(TABLE_PREFIX);
@@ -269,6 +287,7 @@ public class SQLTranslator implements Function<String,String> {
schema = result.getString(Reflection.TABLE_SCHEM);
} while (!Constants.EPSG.equalsIgnoreCase(schema) && result.next());
if (schema == null) schema = "";
+ schemaPattern = SQLUtilities.escape(schema, escape);
break;
}
}
@@ -285,7 +304,7 @@ public class SQLTranslator implements Function<String,String> {
* This column has been renamed "coord_axis_order" in DLL scripts.
* We need to check which name our current database uses.
*/
- try (ResultSet result = md.getColumns(catalog, schema, "Coordinate Axis", "ORDER")) {
+ try (ResultSet result = md.getColumns(catalog, schemaPattern, "Coordinate Axis", "ORDER")) {
translateColumns = !result.next();
}
} else {
@@ -299,11 +318,14 @@ public class SQLTranslator implements Function<String,String> {
* Detect if the database uses boolean types where applicable.
* We arbitrarily use the Datum table as a representative value.
*/
- String deprecated = "DEPRECATED";
+ String deprecated = "DEPRECATED";
+ String objectTable = ENUMERATION_COLUMN;
if (md.storesLowerCaseIdentifiers()) {
- deprecated = deprecated.toLowerCase(Locale.US);
+ deprecated = deprecated.toLowerCase(Locale.US);
+ objectTable = objectTable.toLowerCase(Locale.US);
}
- try (ResultSet result = md.getColumns(catalog, schema, null, deprecated)) {
+ final String tablePattern = isPrefixed ? SQLUtilities.escape(TABLE_PREFIX, escape) + '%' : null;
+ try (ResultSet result = md.getColumns(catalog, schemaPattern, tablePattern, deprecated)) {
while (result.next()) {
if (CharSequences.endsWith(result.getString(Reflection.TABLE_NAME), "Datum", true)) {
final int type = result.getInt(Reflection.DATA_TYPE);
@@ -312,7 +334,22 @@ public class SQLTranslator implements Function<String,String> {
}
}
}
- };
+ /*
+ * Detect if the table use enumeration (on PostgreSQL database) instead of VARCHAR.
+ * Enumerations appear in various tables, but is used in WHERE clause in the Alias table.
+ */
+ try (ResultSet result = md.getColumns(catalog, schemaPattern, tablePattern, objectTable)) {
+ while (result.next()) {
+ if (CharSequences.endsWith(result.getString(Reflection.TABLE_NAME), "Alias", true)) {
+ final String type = result.getString(Reflection.TYPE_NAME);
+ if (!CharSequences.startsWith(type, "VARCHAR", true)) {
+ tableNameEnum = type;
+ }
+ break;
+ }
+ }
+ }
+ }
/**
* Returns the catalog that contains the EPSG schema. This is the catalog specified at construction time
@@ -362,10 +399,10 @@ public class SQLTranslator implements Function<String,String> {
}
/**
- * If the given string is empty, returns {@code null} instead.
+ * If {@code true} if the given string is null or empty.
*/
- private static String nonEmpty(final String s) {
- return (s != null && !s.isEmpty()) ? s : null;
+ private static boolean isEmpty(final String s) {
+ return (s == null) || s.isEmpty();
}
/**
@@ -377,9 +414,7 @@ public class SQLTranslator implements Function<String,String> {
*/
@Override
public String apply(final String sql) {
- final String catalog = nonEmpty(this.catalog);
- final String schema = nonEmpty(this.schema);
- if (quote.isEmpty() && accessToAnsi.isEmpty() && schema == null && catalog == null) {
+ if (quote.isEmpty() && accessToAnsi.isEmpty() && isEmpty(schema) && isEmpty(catalog)) {
return sql;
}
final StringBuilder ansi = new StringBuilder(sql.length() + 16);
@@ -404,25 +439,7 @@ public class SQLTranslator implements Function<String,String> {
if (CharSequences.isUpperCase(name)) {
ansi.append(accessToAnsi.getOrDefault(name, name));
} else {
- if (catalog != null) {
- ansi.append(quote).append(catalog).append(quote).append('.');
- }
- if (schema != null) {
- ansi.append(quote).append(schema).append(quote).append('.');
- }
- if (quoteTableNames) {
- ansi.append(quote);
- }
- if (isPrefixed) {
- ansi.append(TABLE_PREFIX);
- }
- if (quoteTableNames) {
- ansi.append(accessToAnsi.getOrDefault(name, name)).append(quote);
- } else {
- for (final String word : name.split("\\s")) {
- ansi.append(accessToAnsi.getOrDefault(word, word));
- }
- }
+ appendIdentifier(ansi, name);
}
}
ansi.append(sql, end, sql.length());
@@ -433,7 +450,7 @@ public class SQLTranslator implements Function<String,String> {
int w = ansi.indexOf("WHERE");
if (w >= 0) {
w += 5;
- for (final String field : BOOLEAN_FIELDS) {
+ for (final String field : BOOLEAN_COLUMNS) {
int p = ansi.indexOf(field, w);
if (p >= 0) {
p += field.length();
@@ -449,10 +466,47 @@ public class SQLTranslator implements Function<String,String> {
}
}
}
+ /*
+ * If the database uses enumeration, we need an explicit cast with PostgreSQL.
+ * The enumeration type is typically "EPSG"."Table Name".
+ */
+ if (tableNameEnum != null) {
+ int w = ansi.lastIndexOf(ENUMERATION_COLUMN + "=?");
+ if (w >= 0) {
+ w += ENUMERATION_COLUMN.length() + 1;
+ ansi.replace(w, w+1, "CAST(? AS " + tableNameEnum + ')');
+ }
+ }
return ansi.toString();
}
/**
+ * Appends the given identifier in the given buffer, between quotes and prefixed with the schema name.
+ * This is used mostly for appending table names, but can also be used for appending enumeration types.
+ */
+ private void appendIdentifier(final StringBuilder buffer, final String identifier) {
+ if (!isEmpty(catalog)) {
+ buffer.append(quote).append(catalog).append(quote).append('.');
+ }
+ if (!isEmpty(schema)) {
+ buffer.append(quote).append(schema).append(quote).append('.');
+ }
+ if (quoteTableNames) {
+ buffer.append(quote);
+ }
+ if (isPrefixed) {
+ buffer.append(TABLE_PREFIX);
+ }
+ if (quoteTableNames) {
+ buffer.append(accessToAnsi.getOrDefault(identifier, identifier)).append(quote);
+ } else {
+ for (final String word : identifier.split("\\s")) {
+ buffer.append(accessToAnsi.getOrDefault(word, word));
+ }
+ }
+ }
+
+ /**
* Replaces the text at the given position in the buffer if it is equals to the {@code expected} text.
*/
private static boolean replaceIfEquals(final StringBuilder ansi, final int pos,
diff --git a/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/TableInfo.java b/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/TableInfo.java
index 15334b9..53b991f 100644
--- a/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/TableInfo.java
+++ b/core/sis-referencing/src/main/java/org/apache/sis/referencing/factory/sql/TableInfo.java
@@ -23,6 +23,7 @@ import org.opengis.referencing.datum.*;
import org.opengis.referencing.operation.*;
import org.opengis.parameter.ParameterDescriptor;
import org.apache.sis.internal.metadata.WKTKeywords;
+import org.apache.sis.util.CharSequences;
/**
@@ -30,7 +31,7 @@ import org.apache.sis.internal.metadata.WKTKeywords;
* it will be translated into ANSI SQL later by {@link SQLTranslator#apply(String)} if needed.
*
* @author Martin Desruisseaux (IRD, Geomatys)
- * @version 0.8
+ * @version 1.0
* @since 0.7
* @module
*/
@@ -162,6 +163,8 @@ final class TableInfo {
/**
* The table name for SQL queries. May contains a {@code "JOIN"} clause.
+ *
+ * @see #tableName()
*/
final String table;
@@ -185,7 +188,7 @@ final class TableInfo {
/**
* The SQL type to use as a replacement for enumerated values on databases that do not support enumerations.
*/
- static final String ENUM_REPLACEMENT = "VARCHAR(24)";
+ static final String ENUM_REPLACEMENT = "VARCHAR(80)";
/**
* Sub-interfaces of {@link #type} to handle, or {@code null} if none.
@@ -221,6 +224,35 @@ final class TableInfo {
}
/**
+ * Returns the table name without brackets.
+ */
+ final String tableName() {
+ return table.substring(1, table.length() - 1);
+ }
+
+ /**
+ * Returns {@code true} if the given table {@code name} matches the {@code expected} name.
+ * The given {@code name} may be prefixed by {@code "epsg_"} and may contain abbreviations of the full name.
+ * For example {@code "epsg_coordoperation"} is considered as a match for {@code "Coordinate_Operation"}.
+ *
+ * <p>The table name should be one of the values enumerated in the {@code epsg_table_name} type of the
+ * {@code EPSG_Prepare.sql} file.</p>
+ *
+ * @param expected the expected table name (e.g. {@code "Coordinate_Operation"}).
+ * @param name the actual table name.
+ * @return whether the given {@code name} is considered to match the expected name.
+ */
+ static boolean tableMatches(final String expected, String name) {
+ if (name == null) {
+ return false;
+ }
+ if (name.startsWith(SQLTranslator.TABLE_PREFIX)) {
+ name = name.substring(SQLTranslator.TABLE_PREFIX.length());
+ }
+ return CharSequences.isAcronymForWords(name, expected);
+ }
+
+ /**
* Appends a {@code WHERE} clause together with a condition for searching the most specific subtype,
* if such condition can be added. The clause appended by this method looks like the following example
* (details may vary because of enumeration values):
diff --git a/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/TestFactorySource.java b/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/TestFactorySource.java
index f5c0922..4451a2f 100644
--- a/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/TestFactorySource.java
+++ b/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/TestFactorySource.java
@@ -18,11 +18,13 @@ package org.apache.sis.referencing.factory;
import java.util.Map;
import java.util.HashMap;
+import org.postgresql.ds.PGSimpleDataSource;
import org.opengis.util.FactoryException;
import org.opengis.referencing.crs.CRSAuthorityFactory;
import org.apache.sis.util.logging.Logging;
import org.apache.sis.internal.system.Loggers;
import org.apache.sis.internal.util.Constants;
+import org.apache.sis.internal.metadata.sql.Initializer;
import org.apache.sis.referencing.CRS;
import org.apache.sis.referencing.factory.sql.EPSGFactory;
@@ -54,12 +56,31 @@ import static org.opengis.test.Assert.*;
* }
*
* @author Martin Desruisseaux (Geomatys)
- * @version 0.8
+ * @version 1.0
* @since 0.8
* @module
*/
public final strictfp class TestFactorySource {
/**
+ * Whether to use PostgreSQL instead than Derby for the tests. This field should be {@code false};
+ * the {@code true} value is used only for temporarily debugging of PostgreSQL-specific features.
+ * It is developer responsibility to setup a {@code "SpatialMetadata"} database on the local host.
+ * This method differs from {@link org.apache.sis.test.sql.TestDatabase} by querying a permanent
+ * database instead than a temporary database to be deleted after the tests.
+ *
+ * @see org.apache.sis.test.sql.TestDatabase#createOnPostgreSQL(String, boolean)
+ */
+ private static final boolean TEST_ON_POSTGRESQL = false;
+ static {
+ if (TEST_ON_POSTGRESQL) {
+ final PGSimpleDataSource ds = new PGSimpleDataSource();
+ ds.setServerName("localhost");
+ ds.setDatabaseName(Initializer.DATABASE);
+ Initializer.setDefault(() -> ds);
+ }
+ }
+
+ /**
* The factory instance to use for the tests, or {@code null} if not available.
* This field is set by {@link #createFactory()} and cleared by {@link #close()}.
* Test classes using this field shall declare their own {@code createFactory()}
diff --git a/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/EPSGFactoryTest.java b/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/EPSGFactoryTest.java
index 16041de..87465ab 100644
--- a/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/EPSGFactoryTest.java
+++ b/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/EPSGFactoryTest.java
@@ -79,7 +79,7 @@ import static org.apache.sis.test.ReferencingAssert.*;
*
* @author Martin Desruisseaux (IRD, Geomatys)
* @author Vadim Semenov
- * @version 0.8
+ * @version 1.0
* @since 0.7
* @module
*/
@@ -125,15 +125,6 @@ public final strictfp class EPSGFactoryTest extends TestCase {
}
/**
- * Tests {@link EPSGDataAccess#tableMatches(String, String)}.
- */
- @Test
- public void testTableMatches() {
- assertTrue(EPSGDataAccess.tableMatches("Coordinate_Operation", "epsg_coordoperation"));
- assertTrue(EPSGDataAccess.tableMatches("[Coordinate Reference System]", "epsg_coordinatereferencesystem"));
- }
-
- /**
* Tests the "WGS 84" geographic CRS (EPSG:4326).
*
* @throws FactoryException if an error occurred while querying the factory.
diff --git a/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/TableInfoTest.java b/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/TableInfoTest.java
new file mode 100644
index 0000000..455f691
--- /dev/null
+++ b/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/TableInfoTest.java
@@ -0,0 +1,42 @@
+/*
+ * 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.sis.referencing.factory.sql;
+
+import org.apache.sis.test.TestCase;
+import org.junit.Test;
+
+import static org.junit.Assert.*;
+
+
+/**
+ * Tests {@link TableInfo}.
+ *
+ * @author Martin Desruisseaux (Geomatys)
+ * @version 1.0
+ * @since 1.0
+ * @module
+ */
+public final strictfp class TableInfoTest extends TestCase {
+ /**
+ * Tests {@link TableInfo#tableMatches(String, String)}.
+ */
+ @Test
+ public void testTableMatches() {
+ assertTrue(TableInfo.tableMatches("Coordinate_Operation", "epsg_coordoperation"));
+ assertTrue(TableInfo.tableMatches("[Coordinate Reference System]", "epsg_coordinatereferencesystem"));
+ }
+}
diff --git a/core/sis-referencing/src/test/java/org/apache/sis/test/suite/ReferencingTestSuite.java b/core/sis-referencing/src/test/java/org/apache/sis/test/suite/ReferencingTestSuite.java
index c50883b..8cd71a7 100644
--- a/core/sis-referencing/src/test/java/org/apache/sis/test/suite/ReferencingTestSuite.java
+++ b/core/sis-referencing/src/test/java/org/apache/sis/test/suite/ReferencingTestSuite.java
@@ -219,6 +219,7 @@ import org.junit.BeforeClass;
org.apache.sis.referencing.factory.ConcurrentAuthorityFactoryTest.class,
org.apache.sis.referencing.factory.IdentifiedObjectFinderTest.class,
org.apache.sis.referencing.factory.MultiAuthoritiesFactoryTest.class,
+ org.apache.sis.referencing.factory.sql.TableInfoTest.class,
org.apache.sis.referencing.factory.sql.EPSGFactoryTest.class,
org.apache.sis.referencing.factory.sql.EPSGInstallerTest.class,
org.apache.sis.referencing.factory.sql.epsg.DataScriptFormatterTest.class,
diff --git a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Analyzer.java b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Analyzer.java
index 14de8f9..a24d969 100644
--- a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Analyzer.java
+++ b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Analyzer.java
@@ -35,6 +35,7 @@ import org.opengis.util.NameSpace;
import org.opengis.util.NameFactory;
import org.opengis.util.GenericName;
import org.apache.sis.internal.metadata.sql.Dialect;
+import org.apache.sis.internal.metadata.sql.SQLUtilities;
import org.apache.sis.internal.system.DefaultFactories;
import org.apache.sis.storage.sql.SQLStore;
import org.apache.sis.storage.DataStore;
@@ -191,22 +192,7 @@ final class Analyzer {
* then the value should not be escaped.</div>
*/
final String escape(final String pattern) {
- if (pattern != null) {
- StringBuilder buffer = null;
- for (int i = pattern.length(); --i >= 0;) {
- final char c = pattern.charAt(i);
- if (c == '_' || c == '%') {
- if (buffer == null) {
- buffer = new StringBuilder(pattern);
- }
- buffer.insert(i, escape);
- }
- }
- if (buffer != null) {
- return buffer.toString();
- }
- }
- return pattern;
+ return SQLUtilities.escape(pattern, escape);
}
/**