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:25 UTC
[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
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);
}
/**