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
The following commit(s) were added to refs/heads/geoapi-4.0 by this push:
new d430daadd1 Replace the `LIKE` statement by collation when searching
CRS by name. This is faster (allow to use the database index), give better
results (ignore cases and accents) and simpler (remove checks for false
positives).
d430daadd1 is described below
commit d430daadd1112729430d204ed9b294f7a7da7fb1
Author: Martin Desruisseaux <[email protected]>
AuthorDate: Wed Oct 15 18:54:34 2025 +0200
Replace the `LIKE` statement by collation when searching CRS by name.
This is faster (allow to use the database index), give better results
(ignore cases and accents) and simpler (remove checks for false positives).
---
.../org/apache/sis/metadata/sql/Installer.java | 9 +-
.../apache/sis/metadata/sql/MetadataSource.java | 2 +-
.../sql/internal/shared/LocalDataSource.java | 51 ++++---
.../metadata/sql/internal/shared/SQLUtilities.java | 109 +--------------
.../metadata/sql/internal/shared/ScriptRunner.java | 122 +++++++++++++---
.../sis/metadata/sql/internal/shared/Syntax.java | 2 +-
.../metadata/sql/internal/shared/TypeMapper.java | 56 +++-----
.../sql/internal/shared/SQLUtilitiesTest.java | 32 +----
.../sql/internal/shared/ScriptRunnerTest.java | 2 +
.../sql/internal/shared/TypeMapperTest.java | 48 -------
.../referencing/factory/sql/AuthorityCodes.java | 34 ++---
.../referencing/factory/sql/EPSGCodeFinder.java | 68 +++++----
.../referencing/factory/sql/EPSGDataAccess.java | 153 +++++++++++----------
.../sis/referencing/factory/sql/EPSGInstaller.java | 57 +++-----
.../sis/referencing/factory/sql/SQLTranslator.java | 8 +-
.../referencing/factory/sql/EPSGFactoryTest.java | 8 +-
.../sis/storage/sql/feature/InfoStatements.java | 29 ++--
.../org/apache/sis/storage/geopackage/Content.java | 2 +-
.../sis/storage/geopackage/GpkgStoreProvider.java | 2 +-
.../sis/resources/embedded/EmbeddedResources.java | 9 +-
.../sis/referencing/factory/sql/epsg/Prepare.sql | 6 +
.../sis/referencing/factory/sql/epsg/Changes.md | 46 +++++++
.../sis/referencing/factory/sql/epsg/README.md | 70 ++--------
23 files changed, 413 insertions(+), 512 deletions(-)
diff --git
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/Installer.java
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/Installer.java
index 23687c082c..b8246afce8 100644
---
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/Installer.java
+++
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/Installer.java
@@ -20,7 +20,6 @@ import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.sis.metadata.sql.internal.shared.ScriptRunner;
-import org.apache.sis.util.CharSequences;
import org.apache.sis.util.StringBuilders;
@@ -81,16 +80,16 @@ final class Installer extends ScriptRunner {
}
/**
- * Invoked for each line of the SQL installation script to execute.
+ * Invoked for each statement of the <abbr>SQL</abbr> installation script
which creates a table.
* If the database does not support enumerations, replaces enumeration
columns by {@code VARCHAR}.
*/
@Override
- protected int execute(final StringBuilder sql) throws SQLException,
IOException {
- if (!isEnumTypeSupported && CharSequences.startsWith(sql, "CREATE
TABLE", true)) {
+ protected void editTableCreation(final StringBuilder sql) {
+ super.editTableCreation(sql);
+ if (!isEnumTypeSupported) {
for (final String type : enumTypes) {
StringBuilders.replace(sql, type, "VARCHAR(25)");
}
}
- return super.execute(sql);
}
}
diff --git
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/MetadataSource.java
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/MetadataSource.java
index cac410fe31..16850c2092 100644
---
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/MetadataSource.java
+++
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/MetadataSource.java
@@ -468,7 +468,7 @@ public class MetadataSource implements AutoCloseable {
return;
}
}
- final Installer installer = new Installer(connection);
+ final var installer = new Installer(connection);
installer.run();
}
diff --git
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/LocalDataSource.java
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/LocalDataSource.java
index c9af2161c6..5e218446d6 100644
---
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/LocalDataSource.java
+++
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/LocalDataSource.java
@@ -29,6 +29,7 @@ import java.nio.file.Path;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.logging.LogRecord;
+import java.util.concurrent.Callable;
import org.apache.sis.util.ArraysExt;
import org.apache.sis.util.logging.Logging;
import org.apache.sis.system.Loggers;
@@ -56,8 +57,8 @@ public final class LocalDataSource implements DataSource,
Comparable<LocalDataSo
private static final String DERBY_HOME_KEY = "derby.system.home";
/**
- * The database product to use. Currently supported values are
- * {@link Dialect#DERBY} and {@link Dialect#HSQL}.
+ * The database product to use.
+ * Currently supported values are {@link Dialect#DERBY} and {@link
Dialect#HSQL}.
*/
private final Dialect dialect;
@@ -79,8 +80,9 @@ public final class LocalDataSource implements DataSource,
Comparable<LocalDataSo
final boolean create;
/**
- * Prepares a new data source for the given database file. This
construction is incomplete;
- * after return either {@link #initialize()} shall be invoked or this
{@code LocalDataSource} is discarded.
+ * Prepares a new data source for the given database file. This
construction is incomplete:
+ * the {@link #initialize()} method shall be invoked after construction,
+ * unless the caller decides to discard this {@code LocalDataSource}
instance.
*
* @param dialect {@link Dialect#DERBY} or {@link Dialect#HSQL}.
* @param dbFile path to the database to open on the local file system.
@@ -146,7 +148,7 @@ public final class LocalDataSource implements DataSource,
Comparable<LocalDataSo
* SIS_DATA and get the impression that their setting is
ignored.
*/
final Path path = Path.of(home);
- create = !Files.exists(path.resolve(database)) &&
Files.isDirectory(path);
+ create = Files.notExists(path.resolve(database)) &&
Files.isDirectory(path);
dbFile = database;
} else {
continue;
@@ -165,23 +167,23 @@ public final class LocalDataSource implements DataSource,
Comparable<LocalDataSo
/**
* Wraps an existing data source for adding a shutdown method to it.
- * This method is used for source of data embedded in a separated JAR file.
+ * This method is used for source of data embedded in a separated
<abbr>JAR</abbr> file.
*
- * @param ds the data source, usually given by {@link
Initializer#embedded()}.
+ * @param source the data source, usually given by {@link
Initializer#embedded()}.
* @return the data source wrapped with a shutdown method, or {@code ds}.
*/
- static DataSource wrap(final DataSource ds) {
+ static DataSource wrap(final DataSource source) {
final Dialect dialect;
- final String cn = ds.getClass().getName();
+ final String cn = source.getClass().getName();
if (cn.startsWith("org.apache.derby.")) {
dialect = Dialect.DERBY;
} else if (cn.startsWith("org.hsqldb.")) {
dialect = Dialect.HSQL;
} else {
- return ds;
+ return source;
}
- final LocalDataSource local = new LocalDataSource(dialect, null,
false);
- local.source = ds;
+ final var local = new LocalDataSource(dialect, null, false);
+ local.source = source;
return local;
}
@@ -245,29 +247,36 @@ public final class LocalDataSource implements DataSource,
Comparable<LocalDataSo
}
/**
- * Creates the database if needed. For Derby we need to explicitly allow
creation.
+ * Creates the database if needed.
+ * For Derby we need to explicitly allow creation.
* For HSQLDB the creation is enabled by default.
*/
final void createDatabase() throws Exception {
if (create) {
- final Method enabler;
+ Callable<?> finisher = null;
switch (dialect) {
+ // More cases may be added in future versions.
case DERBY: {
- enabler = source.getClass().getMethod("setCreateDatabase",
String.class);
- enabler.invoke(source, "create");
+ final Class<?> c = source.getClass();
+ final Method setter = c.getMethod("setCreateDatabase",
String.class);
+ finisher = () -> setter.invoke(source, "no"); // Any
value other than "create".
+ setter.invoke(source, "create");
+ /*
+ * Make the database uses case-insensitive and
accent-insensitive searches.
+ *
https://db.apache.org/derby/docs/10.17/devguide/tdevdvlpcaseinscoll.html
+ */
+ c.getMethod("setConnectionAttributes", String.class)
+ .invoke(source,
"territory=en_GB;collation=TERRITORY_BASED:PRIMARY");
break;
}
- // More cases may be added in future versions.
- default: enabler = null; break;
}
try (Connection c = source.getConnection()) {
for (Initializer init : Initializer.load()) {
init.createSchema(c);
}
} finally {
- switch (dialect) {
- // More cases may be added in future versions.
- case DERBY: enabler.invoke(source, "no"); break; //
Any value other than "create".
+ if (finisher != null) {
+ finisher.call();
}
}
}
diff --git
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/SQLUtilities.java
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/SQLUtilities.java
index a238fe525a..a9fe257f96 100644
---
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/SQLUtilities.java
+++
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/SQLUtilities.java
@@ -19,9 +19,6 @@ package org.apache.sis.metadata.sql.internal.shared;
import java.sql.SQLException;
import java.sql.SQLDataException;
import java.sql.DatabaseMetaData;
-import org.apache.sis.util.Characters;
-import org.apache.sis.util.CharSequences;
-import org.apache.sis.util.Workaround;
import org.apache.sis.util.internal.shared.Strings;
import org.apache.sis.util.resources.Errors;
@@ -58,12 +55,12 @@ public final class SQLUtilities {
}
/**
- * Converts the given string to a boolean value, or returns {@code null}
if the value is unrecognized.
+ * Converts the given string to a Boolean value, or returns {@code null}
if the value is unrecognized.
* This method recognizes "true", "false", "yes", "no", "t", "f", 0 and 1
(case insensitive).
* An empty string is interpreted as {@code null}.
*
- * @param text the characters to convert to a boolean value, or {@code
null}.
- * @return the given characters as a boolean value, or {@code null} if the
given text was null or empty.
+ * @param text the characters to convert to a Boolean value, or {@code
null}.
+ * @return the given characters as a Boolean value, or {@code null} if the
given text was null or empty.
* @throws SQLDataException if the given text is non-null and non-empty
but not recognized.
*
* @see Boolean#parseBoolean(String)
@@ -115,7 +112,7 @@ public final class SQLUtilities {
* @param escape value of {@link
DatabaseMetaData#getSearchStringEscape()}. May be null or empty.
* @return the given text with wildcard characters escaped.
*/
- public static String escape(final String text, final String escape) {
+ public static String escapeWildcards(final String text, final String
escape) {
if (text != null && !Strings.isNullOrEmpty(escape)) {
final char escapeChar = escape.charAt(0);
StringBuilder buffer = null;
@@ -134,102 +131,4 @@ public final class SQLUtilities {
}
return text;
}
-
- /**
- * Returns a string like the given string but with accented letters
replaced by any character ({@code '_'})
- * and all characters that are not letter or digit replaced by the
wildcard ({@code '%'}).
- *
- * @param text the text to get as a SQL LIKE pattern.
- * @param toLowerCase whether to convert characters to lower case.
- * @param escape value of {@link
DatabaseMetaData#getSearchStringEscape()}. May be null or empty.
- * @return the {@code LIKE} pattern for the given text.
- */
- public static String toLikePattern(final String text, final boolean
toLowerCase, final String escape) {
- final var buffer = new StringBuilder(text.length());
- toLikePattern(text, 0, text.length(), false, toLowerCase, escape,
buffer);
- return buffer.toString();
- }
-
- /**
- * Returns a <abbr>SQL</abbr> LIKE pattern for the given text. The text is
optionally returned in all lower cases
- * for allowing case-insensitive searches. Punctuations are replaced by
any sequence of characters ({@code '%'})
- * and non-<abbr>ASCII</abbr> Latin letters are replaced by any single
character ({@code '_'}).
- * Ideograms (Japanese, Chinese, …) and hiragana (Japanese) are kept
unchanged.
- * This method avoids to put a {@code '%'} symbol as the first character
- * because such character prevents some databases to use their index.
- *
- * @param text the text to get as a <abbr>SQL</abbr> {@code LIKE}
pattern.
- * @param textStart index of the first character to use in the given
{@code text}.
- * @param textEnd index after the last character to use in the given
{@code text}.
- * @param allowSuffix whether to append a final {@code '%'} wildcard at
the end of the pattern.
- * @param toLowerCase whether to convert characters to lower case.
- * @param escape value of {@link
DatabaseMetaData#getSearchStringEscape()}. May be null or empty.
- * @param buffer buffer where to append the <abbr>SQL</abbr> {@code
LIKE} pattern.
- */
- public static void toLikePattern(final String text, int textStart, final
int textEnd, final boolean allowSuffix,
- final boolean toLowerCase, final String
escape, final StringBuilder buffer)
- {
- final int bufferStart = buffer.length();
- while (textStart < textEnd) {
- final int c = text.codePointAt(textStart);
- if (Character.isLetterOrDigit(c)) {
- // Ignore accented letters and Greek letters (before `U+0400`)
in the search.
- if (c < 0x80 || c >= 0x400) {
- buffer.appendCodePoint(toLowerCase ?
Character.toLowerCase(c) : c);
- } else {
- appendIfNotRedundant(buffer, '_');
- }
- } else {
- final int length = buffer.length();
- if (length == bufferStart) {
- // Do not use wildcard in the first character.
- if (escape != null && (c == '%' || c == '_' ||
text.startsWith(escape, textStart))) {
- // Note: there will be bug if `escape` is a repetition
of the same character.
- // But we assume that this corner case is too rare for
being worth a check.
- buffer.append(escape);
- }
- buffer.appendCodePoint(c);
- } else if (buffer.charAt(length - 1) != '%') {
- buffer.append('%');
- }
- }
- textStart += Character.charCount(c);
- }
- if (allowSuffix) {
- appendIfNotRedundant(buffer, '%');
- }
- for (int i=bufferStart; (i = buffer.indexOf("_%", i)) >= 0;) {
- buffer.deleteCharAt(i);
- }
- }
-
- /**
- * Appends the given wildcard character to the given buffer if the buffer
does not ends with {@code '%'}.
- */
- private static void appendIfNotRedundant(final StringBuilder buffer, final
char wildcard) {
- final int length = buffer.length();
- if (length == 0 || buffer.charAt(length - 1) != '%') {
- buffer.append(wildcard);
- }
- }
-
- /**
- * Workaround for what seems to be a Derby 10.11 bug, which seems to
behave as if the LIKE pattern
- * had a trailing % wildcard. This can be verified with the following
query on the EPSG database:
- *
- * {@snippet lang="sql" :
- * SELECT COORD_REF_SYS_CODE, COORD_REF_SYS_NAME FROM EPSG."Coordinate
Reference System"
- * WHERE COORD_REF_SYS_NAME LIKE 'NTF%Paris%Lambert%zone%I'
- * }
- *
- * which returns "NTF (Paris) / Lambert zone I" as expected but also zones
II and III.
- *
- * @param expected the string to search.
- * @param actual the string found in the database.
- * @return {@code true} if the given string can be accepted.
- */
- @Workaround(library = "Derby", version = "10.11")
- public static boolean filterFalsePositive(final String expected, final
String actual) {
- return CharSequences.equalsFiltered(expected, actual,
Characters.Filter.LETTERS_AND_DIGITS, false);
- }
}
diff --git
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/ScriptRunner.java
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/ScriptRunner.java
index 0223fbe946..84aefe219b 100644
---
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/ScriptRunner.java
+++
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/ScriptRunner.java
@@ -55,6 +55,26 @@ import org.apache.sis.util.resources.Errors;
* @author Johann Sorel (Geomatys)
*/
public class ScriptRunner implements AutoCloseable {
+ /**
+ * The <abbr>SQL</abbr> {@value} keyword. In the current {@code
ScriptRunner} implementation,
+ * this keyword must be last before the comma in the declaration of a
column. Example:
+ *
+ * {@snippet lang="sql" :
+ * coord_sys_name VARCHAR(254) NOT NULL COLLATE "Ignore Accent and Case",
+ * }
+ *
+ * @see #editTableCreation(StringBuilder)
+ */
+ private static final String COLLATE = "COLLATE";
+
+ /**
+ * The <abbr>SQL</abbr> {@value} keyword. In the current {@code
ScriptRunner} implementation,
+ * this keyword must be last before the end of a {@code CREATE TABLE}
statement.
+ *
+ * @see #editTableCreation(StringBuilder)
+ */
+ private static final String INHERITS = "INHERITS";
+
/**
* The sequence for SQL comments. Leading lines starting by those
characters will be ignored.
*/
@@ -98,7 +118,7 @@ public class ScriptRunner implements AutoCloseable {
protected final String identifierQuote;
/**
- * {@code true} if the database supports enums.
+ * {@code true} if the database supports enumerations in the way used by
Apache <abbr>SIS</abbr>.
* Example:
*
* {@snippet lang="sql" :
@@ -117,6 +137,17 @@ public class ScriptRunner implements AutoCloseable {
*/
protected final boolean isEnumTypeSupported;
+ /**
+ * {@code true} if the database supports collations in the way used by
Apache <abbr>SIS</abbr>.
+ * The way to use collations vary a lot between databases, so the
"<abbr>SIS</abbr> way" is the
+ * PostgreSQL way for now. A value of {@code false} does not necessarily
means that the database
+ * does not support collations at all, but the database does not support
{@code CREATE COLLATION}
+ * statements and collations declared in column definitions.
+ *
+ * @see #statementsToSkip
+ */
+ protected final boolean isCollationSupported;
+
/**
* The maximum number of rows allowed per {@code "INSERT"} statement.
* This is 1 if the database does not support multi-rows insertion.
@@ -131,12 +162,13 @@ public class ScriptRunner implements AutoCloseable {
private final Statement statement;
/**
- * If non-null, the SQL statements to skip (typically because not
supported by the database).
+ * If non-null, the <abbr>SQL</abbr> statements to skip (typically because
not supported by the database).
* The matcher is built as an alternation of many regular expressions
separated by the pipe symbol.
* The list of statements to skip depends on which {@code is*Supported}
fields are set to {@code true}:
*
* <ul>
* <li>{@link #isEnumTypeSupported} for {@code "CREATE TYPE …"} or
{@code "CREATE CAST …"} statements.</li>
+ * <li>{@link #isCollationSupported} for {@code "CREATE COLLATION …"}
statements.</li>
* <li>{@link Dialect#supportsGrantUsageOnSchema} for {@code "GRANT
USAGE ON SCHEMA …"} statements.</li>
* <li>{@link Dialect#supportsGrantSelectOnTable} for {@code "GRANT
SELECT ON TABLE …"} statements.</li>
* <li>{@link Dialect#supportsComment} for {@code "COMMENT ON …"}
statements.</li>
@@ -208,16 +240,19 @@ public class ScriptRunner implements AutoCloseable {
statement = connection.createStatement();
switch (dialect) {
default: {
- isEnumTypeSupported = false;
+ isEnumTypeSupported = false;
+ isCollationSupported = false;
break;
}
case POSTGRESQL: {
final int version = metadata.getDatabaseMajorVersion();
- isEnumTypeSupported = (version == 8) ?
metadata.getDatabaseMinorVersion() >= 4 : version >= 8;
+ isEnumTypeSupported = (version >= 9);
+ isCollationSupported = (version >= 15); // Version when
ICU collation provider is available.
break;
}
case HSQL: {
- isEnumTypeSupported = false;
+ isEnumTypeSupported = false;
+ isCollationSupported = false;
/*
* HSQLDB stores tables in memory by default. For storing the
tables on files, we have to
* use "CREATE CACHED TABLE" statement, which is
HSQL-specific. For avoiding SQL dialect,
@@ -237,6 +272,9 @@ public class ScriptRunner implements AutoCloseable {
if (!isEnumTypeSupported) {
addStatementToSkip("CREATE\\s+(?:TYPE|CAST)\\s+.*");
}
+ if (!isCollationSupported) {
+ addStatementToSkip("CREATE\\s+COLLATION\\s+.*");
+ }
if (!dialect.supportsAllGrants()) {
addStatementToSkip("GRANT\\s+\\w+\\s+ON\\s+");
if (dialect.supportsGrantUsageOnSchema()) {
@@ -271,6 +309,7 @@ public class ScriptRunner implements AutoCloseable {
*
* <ul>
* <li>{@code "CREATE TYPE …"} or {@code "CREATE CAST …"} if {@link
#isEnumTypeSupported} is {@code false}.</li>
+ * <li>{@code "CREATE COLLATION …"} if {@link #isCollationSupported} is
{@code false}.</li>
* <li>{@code "GRANT USAGE ON SCHEMA …"} if {@link
Dialect#supportsGrantUsageOnSchema} is {@code false}.</li>
* <li>{@code "GRANT SELECT ON TABLE …"} if {@link
Dialect#supportsGrantSelectOnTable} is {@code false}.</li>
* <li>{@code "COMMENT ON …"} if {@link Dialect#supportsComment} is
{@code false}.</li>
@@ -567,7 +606,7 @@ parseLine: while (pos < length) {
* @param to index after the last character of the fragment.
* @return whether the given fragment seems outside quotes.
*/
- private static boolean isOutsideQuotes(final CharSequence sql, int from,
final int to) {
+ private static boolean isOutsideQuotes(final StringBuilder sql, int from,
final int to) {
int nq = 0, ni = 0;
while (from < to) {
switch (sql.charAt(from++)) {
@@ -588,6 +627,60 @@ parseLine: while (pos < length) {
return ((nq | ni) & 1) == 0;
}
+ /**
+ * Invoked for each {@code CREATE TABLE} statement.
+ * The default implementation removes the declarations listed below if
they are unsupported.
+ *
+ * <h4>Table inheritance</h4>
+ * Removes {@code INHERITS} declarations if they are unsupported by the
target database.
+ * This method expects <abbr>SQL</abbr> statements for a PostgreSQL
database like below.
+ * The {@code INHERITS} fragment must be last because everything after may
be ignored:
+ *
+ * {@snippet lang="sql" :
+ * CREATE TABLE metadata."Organisation" (
+ * "ID" VARCHAR(15) NOT NULL PRIMARY KEY,
+ * "name" VARCHAR(120))
+ * INHERITS (metadata."Party");
+ * }
+ *
+ * <h4>Collation</h4>
+ * Removes {@code COLLATE} declarations if they are unsupported by the
target database.
+ * This method expects <abbr>SQL</abbr> statements for a PostgreSQL
database like below.
+ * The {@code COLLATE} fragment must be last before the comma because
everything between
+ * the keyword and the comma will be ignored:
+ *
+ * {@snippet lang="sql" :
+ * CREATE TABLE "Coordinate System" (
+ * coord_sys_code INTEGER NOT NULL,
+ * coord_sys_name VARCHAR(254) NOT NULL COLLATE "Ignore Accent and
Case",
+ * CONSTRAINT pk_coordinatesystem PRIMARY KEY (coord_sys_code))
+ * }
+ *
+ * @param sql the statement from where to remove {@code COLLATE}
declarations.
+ */
+ protected void editTableCreation(final StringBuilder sql) {
+ if (!dialect.supportsTableInheritance()) {
+ final int i = sql.lastIndexOf(INHERITS);
+ if (i >= 0 && isOutsideQuotes(sql, i + INHERITS.length(),
sql.length())) {
+ sql.setLength(CharSequences.skipTrailingWhitespaces(sql, 0,
i));
+ }
+ }
+ if (!isCollationSupported) {
+ int i = sql.length();
+ while ((i = sql.lastIndexOf(COLLATE, i - 1)) > 0) {
+ final int w = i + COLLATE.length();
+ final int s = sql.indexOf(",", w);
+ if (s > w && isOutsideQuotes(sql, w, s)) {
+ if (Character.isWhitespace(sql.codePointAt(w)) &&
+ Character.isWhitespace(sql.codePointBefore(i)))
+ {
+ sql.delete(i, s);
+ }
+ }
+ }
+ }
+ }
+
/**
* Returns {@code true} if the given SQL statements is supported by the
database engine,
* or {@code false} if this statement should be ignored. The default
implementation checks
@@ -613,13 +706,12 @@ parseLine: while (pos < length) {
}
/**
- * Executes the given SQL statement.
+ * Executes the given <abbr>SQL</abbr> statement.
* This method performs the following choices:
*
* <ul>
* <li>If {@link #isSupported(CharSequence)} returns {@code false}, then
this method does nothing.</li>
- * <li>If the statement is {@code CREATE TABLE ... INHERITS ...} but the
database does not support
- * table inheritance, then this method drops the {@code INHERITS
...} part.</li>
+ * <li>If the statement starts with {@code CREATE TABLE}, invokes {@link
#editTableCreation(StringBuilder)}.</li>
* <li>If the {@code maxRowsPerInsert} argument given at construction
time was zero,
* then this method skips {@code "INSERT INTO"} statements but
executes all other.</li>
* <li>Otherwise this method executes the given statement with the
following modification:
@@ -631,7 +723,7 @@ parseLine: while (pos < length) {
* Subclasses that override this method can freely edit the {@link
StringBuilder} content before
* to invoke this method.
*
- * @param sql the SQL statement to execute.
+ * @param sql the <abbr>SQL</abbr> statement to execute.
* @return the number of rows added or modified as a result of the
statement execution.
* @throws SQLException if an error occurred while executing the SQL
statement.
* @throws IOException if an I/O operation was required and failed.
@@ -640,15 +732,11 @@ parseLine: while (pos < length) {
if (!isSupported(sql)) {
return 0;
}
- String subSQL = currentSQL =
CharSequences.trimWhitespaces(sql).toString();
- if (!dialect.supportsTableInheritance() && subSQL.startsWith("CREATE
TABLE")) {
- final int s = sql.lastIndexOf("INHERITS");
- if (s >= 0 && isOutsideQuotes(sql, s+8, sql.length())) {
// 8 is the length of "INHERITS".
- sql.setLength(CharSequences.skipTrailingWhitespaces(sql, 0,
s));
- subSQL = currentSQL = sql.toString();
- }
+ if (CharSequences.startsWith(sql, "CREATE TABLE", true)) {
+ editTableCreation(sql);
}
int count = 0;
+ String subSQL = currentSQL =
CharSequences.trimWhitespaces(sql).toString();
/*
* The scripts usually do not contain any SELECT statement. One
exception is the creation
* of geometry columns in a PostGIS database, which use "SELECT
AddGeometryColumn(…)".
diff --git
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/Syntax.java
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/Syntax.java
index 71abf67e19..5638794a08 100644
---
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/Syntax.java
+++
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/Syntax.java
@@ -166,7 +166,7 @@ public class Syntax {
* @return the given text with wildcard characters escaped.
*/
public final String escapeWildcards(final String text) {
- return SQLUtilities.escape(text, wildcardEscape);
+ return SQLUtilities.escapeWildcards(text, wildcardEscape);
}
/**
diff --git
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/TypeMapper.java
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/TypeMapper.java
index 601660b935..b9df12b181 100644
---
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/TypeMapper.java
+++
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/TypeMapper.java
@@ -18,10 +18,11 @@ package org.apache.sis.metadata.sql.internal.shared;
import java.util.Date;
import java.sql.Types;
+import java.sql.JDBCType;
/**
- * Maps a few basic Java types to JDBC types.
+ * Maps a few basic Java types to <abbr>JDBC</abbr> types.
*
* @author Martin Desruisseaux (Geomatys)
*/
@@ -34,15 +35,15 @@ final class TypeMapper {
* <p>The types declared here matches both the Derby and PostgreSQL
mapping.</p>
*/
private static final TypeMapper[] TYPES = {
- new TypeMapper(Boolean.class, Types.BOOLEAN, "BOOLEAN"),
- new TypeMapper(Date .class, Types.TIMESTAMP, "TIMESTAMP"),
- new TypeMapper(Double .class, Types.DOUBLE, "DOUBLE PRECISION"),
- new TypeMapper(Float .class, Types.REAL, "REAL"),
- new TypeMapper(Long .class, Types.BIGINT, "BIGINT"),
- new TypeMapper(Integer.class, Types.INTEGER, "INTEGER"),
- new TypeMapper(Short .class, Types.SMALLINT, "SMALLINT"),
- new TypeMapper(Byte .class, Types.TINYINT, "SMALLINT"), //
Derby does not support TINYINT.
- new TypeMapper(Number .class, Types.DECIMAL, "DECIMAL") //
Implemented by BigDecimal.
+ new TypeMapper(Boolean.class, JDBCType.BOOLEAN),
+ new TypeMapper(Date .class, JDBCType.TIMESTAMP),
+ new TypeMapper(Double .class, JDBCType.DOUBLE),
+ new TypeMapper(Float .class, JDBCType.REAL),
+ new TypeMapper(Long .class, JDBCType.BIGINT),
+ new TypeMapper(Integer.class, JDBCType.INTEGER),
+ new TypeMapper(Short .class, JDBCType.SMALLINT),
+ new TypeMapper(Byte .class, JDBCType.TINYINT),
+ new TypeMapper(Number .class, JDBCType.DECIMAL) // Implemented by
BigDecimal.
};
/**
@@ -53,20 +54,14 @@ final class TypeMapper {
/**
* A constant from the SQL {@link Types} enumeration.
*/
- private final int type;
-
- /**
- * The SQL keyword for that type.
- */
- private final String keyword;
+ private final JDBCType type;
/**
* For internal use only.
*/
- private TypeMapper(final Class<?> classe, final int type, final String
keyword) {
- this.classe = classe;
- this.type = type;
- this.keyword = keyword;
+ private TypeMapper(final Class<?> classe, final JDBCType type) {
+ this.classe = classe;
+ this.type = type;
}
/**
@@ -81,25 +76,14 @@ final class TypeMapper {
if (classe != null) {
for (final TypeMapper type : TYPES) {
if (type.classe.isAssignableFrom(classe)) {
- return type.keyword;
+ switch (type.type) {
+ case DOUBLE: return "DOUBLE PRECISION";
+ case TINYINT: return "SMALLINT"; // Derby does not
support TINYINT.
+ default: return type.type.name();
+ }
}
}
}
return null;
}
-
- /**
- * Return the Java class for the given SQL type, or {@code null} if none.
- *
- * @param type one of the {@link Types} constants.
- * @return the Java class, or {@code null} if none.
- */
- public static Class<?> toJavaType(final int type) {
- for (final TypeMapper t : TYPES) {
- if (t.type == type) {
- return t.classe;
- }
- }
- return null;
- }
}
diff --git
a/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/internal/shared/SQLUtilitiesTest.java
b/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/internal/shared/SQLUtilitiesTest.java
index 2c8a911ea9..70330a2ec3 100644
---
a/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/internal/shared/SQLUtilitiesTest.java
+++
b/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/internal/shared/SQLUtilitiesTest.java
@@ -35,35 +35,13 @@ public final class SQLUtilitiesTest extends TestCase {
}
/**
- * Tests {@link SQLUtilities#escape(String, String)}.
+ * Tests {@link SQLUtilities#escapeWildcards(String, String)}.
*/
@Test
public void testEscape() {
- assertEquals("foo", SQLUtilities.escape("foo",
"\\"));
- assertEquals("foo\\_biz\\%bar", SQLUtilities.escape("foo_biz%bar",
"\\"));
- assertEquals("foo\\\\bar", SQLUtilities.escape("foo\\bar",
"\\"));
- assertEquals("foo#!#!bar#not", SQLUtilities.escape("foo#!bar#not",
"#!"));
- }
-
- /**
- * Tests {@link SQLUtilities#toLikePattern(String, int, int, boolean,
boolean, StringBuilder)}.
- */
- @Test
- public void testToLikePattern() {
- final var buffer = new StringBuilder(30);
- assertEquals("WGS84", toLikePattern(buffer,
"WGS84"));
- assertEquals("WGS%84", toLikePattern(buffer, "WGS
84"));
- assertEquals("A%text%with%random%symbols%", toLikePattern(buffer, "A
text !* with_random:/symbols;+"));
- assertEquals("*%With%non%letter%start", toLikePattern(buffer,
"*_+%=With non-letter start"));
- assertEquals("\\%Special%case", toLikePattern(buffer,
"%Special_case"));
- }
-
- /**
- * Helper method for {@link #testToLikePattern()}.
- */
- private static String toLikePattern(final StringBuilder buffer, final
String identifier) {
- buffer.setLength(0);
- SQLUtilities.toLikePattern(identifier, 0, identifier.length(), false,
false, "\\", buffer);
- return buffer.toString();
+ assertEquals("foo", SQLUtilities.escapeWildcards("foo",
"\\"));
+ assertEquals("foo\\_biz\\%bar",
SQLUtilities.escapeWildcards("foo_biz%bar", "\\"));
+ assertEquals("foo\\\\bar",
SQLUtilities.escapeWildcards("foo\\bar", "\\"));
+ assertEquals("foo#!#!bar#not",
SQLUtilities.escapeWildcards("foo#!bar#not", "#!"));
}
}
diff --git
a/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/internal/shared/ScriptRunnerTest.java
b/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/internal/shared/ScriptRunnerTest.java
index dbbe7cf356..c54bd45a21 100644
---
a/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/internal/shared/ScriptRunnerTest.java
+++
b/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/internal/shared/ScriptRunnerTest.java
@@ -64,6 +64,7 @@ public final class ScriptRunnerTest extends TestCase {
@TestStep
public static void testSupportedFlags(final ScriptRunner sr) {
assertFalse(sr.isEnumTypeSupported);
+ assertFalse(sr.isCollationSupported);
}
/**
@@ -76,6 +77,7 @@ public final class ScriptRunnerTest extends TestCase {
public static void testRegularExpressions(final ScriptRunner sr) {
assertFalse(sr.isSupported("CREATE TYPE CI_DateTypeCode AS ENUM
('creation', 'publication')"));
assertFalse(sr.isSupported("CREATE CAST (VARCHAR AS CI_DateTypeCode)
WITH INOUT AS ASSIGNMENT"));
+ assertFalse(sr.isSupported("CREATE COLLATION \"Ignore Accent and
Case\" (provider=icu, locale='und-u-kn-ks-level1')"));
assertTrue (sr.isSupported("CREATE TABLE CI_Citation (…)"));
assertFalse(sr.isSupported("GRANT USAGE ON SCHEMA metadata TO
PUBLIC"));
assertFalse(sr.isSupported("GRANT SELECT ON TABLE \"Coordinate
Reference System\" TO PUBLIC"));
diff --git
a/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/internal/shared/TypeMapperTest.java
b/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/internal/shared/TypeMapperTest.java
deleted file mode 100644
index b3384aa091..0000000000
---
a/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/internal/shared/TypeMapperTest.java
+++ /dev/null
@@ -1,48 +0,0 @@
-/*
- * 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.metadata.sql.internal.shared;
-
-import java.sql.Types;
-
-// Test dependencies
-import org.junit.jupiter.api.Test;
-import static org.junit.jupiter.api.Assertions.*;
-import org.apache.sis.test.TestCase;
-
-
-/**
- * Tests {@link TypeMapper}.
- *
- * @author Martin Desruisseaux (Geomatys)
- */
-public final class TypeMapperTest extends TestCase {
- /**
- * Creates a new test case.
- */
- public TypeMapperTest() {
- }
-
- /**
- * Tests {@link TypeMapper#toJavaType(int)}.
- */
- @Test
- public void testToJavaType() {
- assertEquals(Integer.class, TypeMapper.toJavaType(Types.INTEGER));
- assertEquals(Boolean.class, TypeMapper.toJavaType(Types.BOOLEAN));
- assertNull ( TypeMapper.toJavaType(Types.LONGVARCHAR));
- }
-}
diff --git
a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/AuthorityCodes.java
b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/AuthorityCodes.java
index b2ca6ba0a4..66bdc86982 100644
---
a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/AuthorityCodes.java
+++
b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/AuthorityCodes.java
@@ -25,7 +25,6 @@ import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Statement;
import org.opengis.referencing.IdentifiedObject;
-import org.apache.sis.metadata.sql.internal.shared.SQLUtilities;
import org.apache.sis.util.collection.BackingStoreException;
import org.apache.sis.util.collection.IntegerList;
import org.apache.sis.util.internal.shared.AbstractMap;
@@ -162,31 +161,21 @@ final class AuthorityCodes extends
AbstractMap<String,String> implements Seriali
}
sql[ALL_CODES] = buffer.append(" ORDER BY
").append(table.codeColumn).toString();
/*
- * Build the SQL query for fetching the codes of object having a name
matching a pattern.
+ * Build the SQL query for fetching the codes of object having the
given name.
* It is of the form:
*
- * SELECT code FROM table WHERE name LIKE ? AND DEPRECATED=FALSE
ORDER BY code;
+ * SELECT code FROM table WHERE name=? AND DEPRECATED=FALSE ORDER
BY code;
*/
- if (NUM_QUERIES > CODES_FOR_NAME) {
- sql[CODES_FOR_NAME] = buffer.insert(conditionStart,
table.nameColumn + " LIKE ? AND ").toString();
- /*
- * Workaround for Derby bug. See
`SQLUtilities.filterFalsePositive(…)`.
- */
- String t = sql[CODES_FOR_NAME];
- t = t.substring(0, columnNameEnd) + ", " + table.nameColumn +
t.substring(columnNameEnd);
- sql[CODES_FOR_NAME] = t;
- }
+ sql[CODES_FOR_NAME] = buffer.insert(conditionStart, table.nameColumn +
"=? AND ").toString();
/*
* Build the SQL query for fetching the name of a single object for a
given code.
* This query will also be used for testing object existence. It is of
the form:
*
* SELECT name FROM table WHERE code = ?
*/
- if (NUM_QUERIES > NAME_FOR_CODE) {
- buffer.setLength(conditionStart);
- buffer.replace(columnNameStart, columnNameEnd, table.nameColumn);
- sql[NAME_FOR_CODE] = buffer.append(table.codeColumn).append(" =
?").toString();
- }
+ buffer.setLength(conditionStart);
+ buffer.replace(columnNameStart, columnNameEnd, table.nameColumn);
+ sql[NAME_FOR_CODE] =
buffer.append(table.codeColumn).append("=?").toString();
for (int i=0; i<NUM_QUERIES; i++) {
sql[i] = factory.translator.apply(sql[i]);
}
@@ -217,19 +206,18 @@ final class AuthorityCodes extends
AbstractMap<String,String> implements Seriali
/**
* Puts codes associated to the given name in the given collection.
*
- * @param pattern the {@code LIKE} pattern of the name to search.
- * @param name the original name. This is a temporary workaround for
a Derby bug (see {@code filterFalsePositive(…)}).
- * @param addTo the collection where to add the codes.
+ * @param name the name of the object to search.
+ * @param addTo the collection where to add the codes.
* @throws SQLException if an error occurred while querying the database.
*/
- final void findCodesFromName(final String pattern, final String name,
final Collection<Integer> addTo) throws SQLException {
+ final void findCodesFromName(final String name, final Collection<Integer>
addTo) throws SQLException {
synchronized (factory) {
final PreparedStatement statement =
prepareStatement(CODES_FOR_NAME);
- statement.setString(1, pattern);
+ statement.setString(1, name);
try (ResultSet result = statement.executeQuery()) {
while (result.next()) {
final int code = result.getInt(1);
- if (!result.wasNull() &&
SQLUtilities.filterFalsePositive(name, result.getString(2))) {
+ if (!result.wasNull()) {
addTo.add(code);
}
}
diff --git
a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGCodeFinder.java
b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGCodeFinder.java
index cde2d6b370..b4a3d800d1 100644
---
a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGCodeFinder.java
+++
b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGCodeFinder.java
@@ -55,7 +55,6 @@ import org.apache.sis.util.collection.BackingStoreException;
import org.apache.sis.pending.jdk.JDK16;
import org.apache.sis.pending.jdk.JDK19;
import org.apache.sis.metadata.internal.shared.ReferencingServices;
-import org.apache.sis.metadata.sql.internal.shared.SQLUtilities;
import org.apache.sis.metadata.iso.citation.Citations;
import org.apache.sis.referencing.IdentifiedObjects;
import org.apache.sis.referencing.internal.shared.Formulas;
@@ -390,7 +389,7 @@ crs: if (isInstance(CoordinateReferenceSystem.class,
object)) {
*
* SELECT DATUM_CODE FROM "Datum"
* WHERE ELLIPSOID_CODE IN (?,…)
- * AND (LOWER(DATUM_NAME) LIKE '?%')
+ * AND (DATUM_NAME LIKE ?)
*/
source = TableInfo.DATUM;
if (isInstance(GeodeticDatum.class, object)) {
@@ -428,7 +427,7 @@ crs: if (isInstance(CoordinateReferenceSystem.class,
object)) {
* so that buffer shall not contain valuable information yet.
*/
final var buffer = new StringBuilder(350); // Temporary buffer for
building SQL query.
- final Set<String> namePatterns;
+ final Set<String> namePatterns; // Name including
aliases.
final String aliasSQL;
if (ArraysExt.containsIdentity(filters, Condition.NAME)) {
namePatterns = new LinkedHashSet<>();
@@ -441,7 +440,7 @@ crs: if (isInstance(CoordinateReferenceSystem.class,
object)) {
.append(dao.translator.toActualTableName(source.table))
.append("' AND ");
// PostgreSQL does not require explicit cast when the value is a
literal instead of "?".
- appendFilterByName(namePatterns, "ALIAS", buffer);
+ appendLikeNames(namePatterns, "ALIAS", buffer);
aliasSQL = dao.translator.apply(buffer.toString());
buffer.setLength(0);
} else {
@@ -454,7 +453,7 @@ crs: if (isInstance(CoordinateReferenceSystem.class,
object)) {
* SELECT <codeColumn> FROM <table>
* WHERE CAST(<typeColumn> AS VARCHAR(80)) LIKE 'type%'
* AND <filter.column> IN (<filter.values>)
- * AND (LOWER(<nameColumn>) LIKE '<name>%')
+ * AND (<nameColumn> = ?)
*
* The query is assembled in the `buffer`. The first WHERE condition
specifies the desired type.
* That condition may be absent. The next conditions specify desired
values. It may be EPSG codes
@@ -476,7 +475,7 @@ crs: if (isInstance(CoordinateReferenceSystem.class,
object)) {
if (namePatterns != null) {
if (isNext) buffer.append(" AND ");
isNext = false;
- appendFilterByName(namePatterns, source.nameColumn, buffer);
+ appendLikeNames(namePatterns, source.nameColumn, buffer);
try (ResultSet result = stmt.executeQuery(aliasSQL)) {
while (result.next()) {
final int code = result.getInt(1);
@@ -535,19 +534,24 @@ crs: if (isInstance(CoordinateReferenceSystem.class,
object)) {
}
/**
- * Returns a SQL pattern for the given datum name. The name is returned in
all lower cases for allowing
- * case-insensitive searches. Punctuations are replaced by any sequence of
characters ({@code '%'}) and
- * non-ASCII letters are replaced by any single character ({@code '_'}).
The returned pattern should be
- * flexible enough for accepting all names considered equal in {@code
DefaultGeodeticDatum} comparisons.
- * In case of doubt, it is okay to return a pattern accepting more names.
+ * Returns a <abbr>SQL</abbr> {@code LIKE} pattern for the given datum
name.
+ * Characters other than letters and digits are replaced by the {@code
'%'} wildcard.
+ * Note that this rule replaces also the {@code %}, {@code _} and {@code
'} characters,
+ * thus avoiding the need to escape them.
+ *
+ * <p>This method does not try to address case-insensitive and
accent-insensitive searches.
+ * This method assumes that the column uses a database collation ignoring
cases and accents.
+ * This method is used in complement to collation, for adding more
flexibility in the punctuations.
+ * In case of doubt, it is okay to return a pattern accepting more names,
as the caller will check
+ * if we really have a match (including checks of ellipsoid and prime
meridian).</p>
*
* @param name the datum name for which to return a SQL pattern.
* @param buffer temporary buffer to use for creating the pattern.
- * @return the SQL pattern for the given name.
+ * @return the <abbr>SQL</abbr> {@code LIKE} pattern for the given datum
name.
*
* @see
org.apache.sis.referencing.datum.DefaultGeodeticDatum#isHeuristicMatchForName(String)
*/
- private String toDatumPattern(final String name, final StringBuilder
buffer) {
+ private static String toDatumPattern(final String name, final
StringBuilder buffer) {
int start = 0;
if (name.startsWith(ESRI_DATUM_PREFIX)) {
start = ESRI_DATUM_PREFIX.length();
@@ -556,7 +560,22 @@ crs: if (isInstance(CoordinateReferenceSystem.class,
object)) {
if (end < 0) end = name.length();
end = CharSequences.skipTrailingWhitespaces(name, start, end);
buffer.setLength(0);
- SQLUtilities.toLikePattern(name, start, end, true, true,
dao.translator.wildcardEscape, buffer);
+ while (start < end) {
+ final int c = name.codePointAt(start);
+ if (Character.isLetterOrDigit(c)) {
+ buffer.appendCodePoint(c);
+ } else {
+ final int length = buffer.length();
+ if (length > 0 && buffer.charAt(length - 1) != '%') {
+ buffer.append('%');
+ }
+ }
+ start += Character.charCount(c);
+ }
+ final int length = buffer.length();
+ if (length == 0 || buffer.charAt(length - 1) != '%') {
+ buffer.append('%');
+ }
return buffer.toString();
}
@@ -565,7 +584,7 @@ crs: if (isInstance(CoordinateReferenceSystem.class,
object)) {
* {@link #toDatumPattern(String, StringBuilder)}. This method append a
SQL fragment like below:
*
* {@snippet lang="sql" :
- * (LOWER(<column>) LIKE '<pattern>' OR …)
+ * (<column> LIKE '<pattern>' OR …)
* }
*
* This method assumes that {@code namePatterns} contains at least one
element.
@@ -574,11 +593,10 @@ crs: if (isInstance(CoordinateReferenceSystem.class,
object)) {
* @param column column where the search for the names.
* @param buffer buffer where to add the SQL fragment.
*/
- private static void appendFilterByName(final Set<String> namePatterns,
final String column, final StringBuilder buffer) {
+ private static void appendLikeNames(final Set<String> namePatterns, final
String column, final StringBuilder buffer) {
String separator = "(";
for (final String pattern : namePatterns) {
- buffer.append(separator).append("LOWER(").append(column)
- .append(") LIKE '").append(pattern).append('\'');
+ buffer.append(separator).append(column).append(" LIKE
'").append(pattern).append('\'');
separator = " OR ";
}
buffer.append(')');
@@ -623,12 +641,9 @@ crs: if (isInstance(CoordinateReferenceSystem.class,
object)) {
/** The object to search. */
private final IdentifiedObject object;
- /** Workaround for a Derby bug (see {@code filterFalsePositive(…)}). */
+ /** The name of the object to search. */
private String name;
- /** {@code LIKE} Pattern of the name of the object to search. */
- private String namePattern;
-
/** Information about the tables of the object to search. */
private final TableInfo source;
@@ -697,17 +712,14 @@ crs: if (isInstance(CoordinateReferenceSystem.class,
object)) {
case 0: { // Fetch codes from the name.
if (domain != Domain.EXHAUSTIVE_VALID_DATASET) {
name = getName(object);
- if (name != null) { // Should never be null,
but we are paranoiac.
- namePattern = dao.toLikePattern(name);
- dao.findCodesFromName(source,
TableInfo.toCacheKey(object), namePattern, name, addTo);
- }
+ dao.findCodesFromName(source,
TableInfo.toCacheKey(object), name, addTo);
}
break;
}
case 1: { // Fetch codes from the aliases.
if (domain != Domain.EXHAUSTIVE_VALID_DATASET) {
- if (namePattern != null) {
- dao.findCodesFromAlias(source, namePattern,
name, addTo);
+ if (name != null) {
+ dao.findCodesFromAlias(source, name, addTo);
}
}
break;
diff --git
a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGDataAccess.java
b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGDataAccess.java
index b0e62db918..b0a8fc0bc9 100644
---
a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGDataAccess.java
+++
b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGDataAccess.java
@@ -829,11 +829,10 @@ public class EPSGDataAccess extends
GeodeticAuthorityFactory implements CRSAutho
* then we will search in the aliases table as a fallback.
*/
final var result = new ArrayList<Integer>();
- final String pattern = toLikePattern(code);
- findCodesFromName(source, source.type, pattern, code, result);
+ findCodesFromName(source, source.type, code, result);
if (result.isEmpty()) {
// Search in aliases only if no match was found in primary
names.
- findCodesFromAlias(source, pattern, code, result);
+ findCodesFromAlias(source, code, result);
}
Integer resolved = null;
for (Integer value : result) {
@@ -871,58 +870,47 @@ public class EPSGDataAccess extends
GeodeticAuthorityFactory implements CRSAutho
return primaryKeys;
}
- /**
- * Returns the given object name as a pattern which can be used in a
{@code LIKE} clause.
- * This method does not change the character case for avoiding the need to
use {@code LOWER}
- * in the <abbr>SQL</abbr> statement (because it may prevent the use of
the database index).
- */
- final String toLikePattern(final String name) {
- return SQLUtilities.toLikePattern(name, false,
translator.wildcardEscape);
- }
-
/**
* Finds the authority codes for the given name.
+ * The search is case-insensitive and accent-insensitive if the database
uses a lenient collation.
*
* @param source information about the table where the code should
appear.
* @param cacheKey object class or {@link
TableInfo#toCacheKey(IdentifiedObject)} value.
- * @param pattern the name to search as a pattern that can be used with
{@code LIKE}.
- * @param name the original name. This is a temporary workaround for
a Derby bug (see {@code filterFalsePositive(…)}).
+ * @param name name of the object to search. Case and accents will
be ignored if possible.
* @param addTo the collection where to add the codes that have been
found.
* @throws SQLException if an error occurred while querying the database.
*/
- final void findCodesFromName(final TableInfo source, final Object
cacheKey, final String pattern, final String name,
+ final void findCodesFromName(final TableInfo source, final Object
cacheKey, final String name,
final Collection<Integer> addTo) throws
SQLException
{
AuthorityCodes codes = getCodeMap(cacheKey, source, false);
if (codes != null) {
- codes.findCodesFromName(pattern, name, addTo);
+ codes.findCodesFromName(name, addTo);
}
}
/**
* Finds the authority codes for the given alias.
+ * The search is case-insensitive and accent-insensitive if the database
uses a lenient collation.
*
- * @param source information about the table where the code should
appear.
- * @param pattern the name to search as a pattern that can be used with
{@code LIKE}.
- * @param name the original name. This is a temporary workaround for
a Derby bug (see {@code filterFalsePositive(…)}).
- * @param addTo the collection where to add the codes that have been
found.
+ * @param source information about the table where the code should
appear.
+ * @param name name of the object to search. Case and accents will be
ignored if possible.
+ * @param addTo the collection where to add the codes that have been
found.
* @throws SQLException if an error occurred while querying the database.
*/
- final void findCodesFromAlias(final TableInfo source, final String
pattern, final String name, final Collection<Integer> addTo)
+ final void findCodesFromAlias(final TableInfo source, final String name,
final Collection<Integer> addTo)
throws SQLException
{
final PreparedStatement stmt = prepareStatement(
"AliasKey",
"SELECT OBJECT_CODE, ALIAS"
+ " FROM \"Alias\""
- + " WHERE OBJECT_TABLE_NAME=? AND ALIAS LIKE ?");
+ + " WHERE OBJECT_TABLE_NAME=? AND ALIAS=?");
stmt.setString(1, translator.toActualTableName(source.table));
- stmt.setString(2, pattern);
+ stmt.setString(2, name);
try (ResultSet result = stmt.executeQuery()) {
while (result.next()) {
- if (SQLUtilities.filterFalsePositive(name,
result.getString(2))) {
- addTo.add(getOptionalInteger(result, 1));
- }
+ addTo.add(getOptionalInteger(result, 1));
}
}
}
@@ -1115,13 +1103,41 @@ public class EPSGDataAccess extends
GeodeticAuthorityFactory implements CRSAutho
*
* @param result the result set to fetch value from.
* @param columnIndex the column index (1-based).
- * @return the Boolean at the specified column, or {@code null}.
+ * @return the Boolean at the specified column, or {@code false}.
* @throws SQLException if an error occurred while querying the database.
*/
- private boolean getOptionalBoolean(final ResultSet result, final int
columnIndex) throws SQLException {
+ private boolean getBoolean(final ResultSet result, final int columnIndex)
throws SQLException {
return translator.useBoolean() ? result.getBoolean(columnIndex) :
(result.getInt(columnIndex) != 0);
}
+ /**
+ * Gets the value from the specified {@link ResultSet}, or {@code null} if
none.
+ * This method is invoked for columns where the <abbr>EPSG</abbr> database
uses "Yes", "No" or empty values.
+ * The Apache <abbr>SIS</abbr> scripts rather uses the {@code BOOLEAN}
type, but allowing null values for the
+ * cases where the flag is allowed to be absent (empty).
+ *
+ * <h4>Exceptions</h4>
+ * If a string is not recognized as a Boolean value, this method throws a
{@link SQLException} because a wrong
+ * {@code PARAM_SIGN_REVERSAL} value would let {@code EPSGDataAccess}
finishes its work without apparent problem
+ * but would cause failures later when Apache <abbr>SIS</abbr> tries to
infer an inverse operation. An exception
+ * thrown at a later time is more difficult to relate to the root cause
than if we throw the exception here.
+ *
+ * @param result the result set to fetch value from.
+ * @param columnIndex the column index (1-based).
+ * @return the Boolean at the specified column, or {@code null}.
+ * @throws SQLException if an error occurred while querying the database.
+ */
+ private Boolean getOptionalBoolean(final ResultSet result, final int
columnIndex) throws SQLException {
+ Boolean value;
+ if (translator.useBoolean()) {
+ value = result.getBoolean(columnIndex);
+ } else {
+ // May throw SQLException - see above comment.
+ value = SQLUtilities.parseBoolean(result.getString(columnIndex));
+ }
+ return result.wasNull() ? null : value;
+ }
+
/**
* Formats an error message for an unexpected null value.
*/
@@ -1557,11 +1573,8 @@ search: try (ResultSet result =
executeMetadataQuery("Deprecation",
final String column = isPrimaryKey ? source.codeColumn :
source.nameColumn;
query.setLength(queryStart);
query.append(source.codeColumn);
- if (!isPrimaryKey) {
- query.append(", ").append(column); // Only for
filterFalsePositive(…).
- }
query.append(" FROM ").append(source.fromClause)
- .append(" WHERE ").append(column).append(isPrimaryKey ? "
= ?" : " LIKE ?");
+ .append(" WHERE ").append(column).append("=?");
try (PreparedStatement stmt =
connection.prepareStatement(translator.apply(query.toString()))) {
/*
* Check if at least one record is found for the code or
the name.
@@ -1570,14 +1583,12 @@ search: try (ResultSet result =
executeMetadataQuery("Deprecation",
if (isPrimaryKey) {
stmt.setInt(1, key);
} else {
- stmt.setString(1, toLikePattern(code));
+ stmt.setString(1, code);
}
Integer present = null;
try (ResultSet result = stmt.executeQuery()) {
while (result.next()) {
- if (isPrimaryKey ||
SQLUtilities.filterFalsePositive(code, result.getString(2))) {
- present =
ensureSingleton(getOptionalInteger(result, 1), present, code);
- }
+ present =
ensureSingleton(getOptionalInteger(result, 1), present, code);
}
}
if (present != null) {
@@ -1712,7 +1723,7 @@ search: try (ResultSet result =
executeMetadataQuery("Deprecation",
final String area = getOptionalString (result, 3);
final String scope = getOptionalString (result, 4);
final String remarks = getOptionalString (result, 5);
- final boolean deprecated = getOptionalBoolean(result, 6);
+ final boolean deprecated = getBoolean (result, 6);
final String type = getString (code, result, 7);
/*
* Do not invoke `createProperties` now, even if we have all
required information,
@@ -2003,7 +2014,7 @@ search: try (ResultSet result =
executeMetadataQuery("Deprecation",
final String area = getOptionalString (result, 8);
final String scope = getOptionalString (result, 9);
final String remarks = getOptionalString (result, 10);
- final boolean deprecated = getOptionalBoolean (result, 11);
+ final boolean deprecated = getBoolean (result, 11);
final Integer convRSCode = getOptionalInteger (result, 15);
/*
* Do not invoke `createProperties` now, even if we have all
required information,
@@ -2209,10 +2220,10 @@ search: try (ResultSet result =
executeMetadataQuery("Deprecation",
+ " WHERE CONVENTIONAL_RS_CODE = ?", code))
{
while (result.next()) {
- final Integer epsg = getInteger (code, result, 1);
- final String name = getString (code, result, 2);
- final String remarks = getOptionalString (result, 3);
- final boolean deprecated = getOptionalBoolean (result, 4);
+ final Integer epsg = getInteger (code, result, 1);
+ final String name = getString (code, result, 2);
+ final String remarks = getOptionalString(result, 3);
+ final boolean deprecated = getBoolean (result, 4);
/*
* Map of properties should be populated only after we
extracted all
* information needed from the `ResultSet`, because it may
be closed.
@@ -2286,7 +2297,7 @@ search: try (ResultSet result =
executeMetadataQuery("Deprecation",
final double semiMinorAxis = getOptionalDouble (result,
5);
final String uom_code = getString (code, result,
6);
final String remarks = getOptionalString (result,
7);
- final boolean deprecated = getOptionalBoolean(result,
8);
+ final boolean deprecated = getBoolean (result,
8);
final Unit<Length> unit =
owner.createUnit(uom_code).asType(Length.class);
final boolean useSemiMinor =
Double.isNaN(inverseFlattening);
if (useSemiMinor && Double.isNaN(semiMinorAxis)) {
@@ -2379,7 +2390,7 @@ search: try (ResultSet result =
executeMetadataQuery("Deprecation",
final double longitude = getDouble (code, result, 3);
final String uom_code = getString (code, result, 4);
final String remarks = getOptionalString (result, 5);
- final boolean deprecated = getOptionalBoolean(result, 6);
+ final boolean deprecated = getBoolean (result, 6);
final Unit<Angle> unit =
owner.createUnit(uom_code).asType(Angle.class);
/*
* Map of properties should be populated only after we
extracted all
@@ -2471,7 +2482,7 @@ search: try (ResultSet result =
executeMetadataQuery("Deprecation",
double zmax = getOptionalDouble (result, 9);
Temporal tmin = getOptionalTemporal(result, 11,
"createExtent");
Temporal tmax = getOptionalTemporal(result, 12,
"createExtent");
- boolean deprecated = getOptionalBoolean (result, 13);
+ boolean deprecated = getBoolean (result, 13);
DefaultGeographicBoundingBox bbox = null;
if (!(Double.isNaN(ymin) && Double.isNaN(ymax) &&
Double.isNaN(xmin) && Double.isNaN(xmax))) {
/*
@@ -2580,12 +2591,12 @@ search: try (ResultSet result =
executeMetadataQuery("Deprecation",
+ " WHERE COORD_SYS_CODE = ?", code))
{
while (result.next()) {
- final Integer epsg = getInteger (code, result, 1);
- final String name = getString (code, result, 2);
- final String type = getString (code, result, 3);
- final int dimension = getInteger (code, result, 4);
- final String remarks = getOptionalString (result, 5);
- final boolean deprecated = getOptionalBoolean(result, 6);
+ final Integer epsg = getInteger (code, result, 1);
+ final String name = getString (code, result, 2);
+ final String type = getString (code, result, 3);
+ final int dimension = getInteger (code, result, 4);
+ final String remarks = getOptionalString(result, 5);
+ final boolean deprecated = getBoolean (result, 6);
final CoordinateSystemAxis[] axes = createComponents(
GeodeticAuthorityFactory::createCoordinateSystemAxis,
"AxisOrder",
@@ -2999,6 +3010,7 @@ search: try (ResultSet result =
executeMetadataQuery("Deprecation",
*
* @param parameter the <abbr>EPSG</abbr> code of the parameter
descriptor.
* @param options the options where to store the {@value
#SIGN_REVERSAL_OPTION} value.
+ * @throws SQLException if the flag uses an unrecognized string value.
*/
private void getSignReversal(final int parameter, final Map<String,
String> options) throws SQLException {
try (ResultSet result = executeQueryForCodes(
@@ -3009,15 +3021,8 @@ search: try (ResultSet result =
executeMetadataQuery("Deprecation",
{
Boolean reversibility = null;
while (result.next()) {
- Boolean value;
- if (translator.useBoolean()) {
- value = result.getBoolean(1);
- if (result.wasNull()) return;
- } else {
- // May throw SQLException - see above comment.
- value = SQLUtilities.parseBoolean(result.getString(1));
- if (value == null) return;
- }
+ Boolean value = getOptionalBoolean(result, 1);
+ if (value == null) return;
if (reversibility == null) reversibility = value;
else if (!reversibility.equals(value)) return;
}
@@ -3086,10 +3091,10 @@ search: try (ResultSet result =
executeMetadataQuery("Deprecation",
+ " WHERE PARAMETER_CODE = ?", code))
{
while (result.next()) {
- final Integer epsg = getInteger (code, result, 1);
- final String name = getString (code, result, 2);
- final String description = getOptionalString (result, 3);
- final boolean deprecated = getOptionalBoolean(result, 4);
+ final Integer epsg = getInteger (code, result, 1);
+ final String name = getString (code, result, 2);
+ final String description = getOptionalString(result, 3);
+ final boolean deprecated = getBoolean (result, 4);
getParameterUnit(epsg, options, null);
getParameterType(epsg, options);
getSignReversal (epsg, options);
@@ -3368,10 +3373,10 @@ search: try (ResultSet result =
executeMetadataQuery("Deprecation",
+ " WHERE COORD_OP_METHOD_CODE = ?", code))
{
while (result.next()) {
- final Integer epsg = getInteger (code, result, 1);
- final String name = getString (code, result, 2);
- final String remarks = getOptionalString (result, 3);
- final boolean deprecated = getOptionalBoolean(result, 4);
+ final Integer epsg = getInteger (code, result, 1);
+ final String name = getString (code, result, 2);
+ final String remarks = getOptionalString(result, 3);
+ final boolean deprecated = getBoolean (result, 4);
final ParameterDescriptor<?>[] descriptors = createComponents(
GeodeticAuthorityFactory::createParameterDescriptor,
"Coordinate_Operation Parameter Usage",
@@ -3475,12 +3480,12 @@ search: try (ResultSet result =
executeMetadataQuery("Deprecation",
} else {
methodCode = getInteger(code, result, 6);
}
- final String version = getOptionalString (result, 7);
- final double accuracy = getOptionalDouble (result, 8);
- final String area = getOptionalString (result, 9);
- final String scope = getOptionalString (result, 10);
- final String remarks = getOptionalString (result, 11);
- final boolean deprecated = getOptionalBoolean(result, 12);
+ final String version = getOptionalString(result, 7);
+ final double accuracy = getOptionalDouble(result, 8);
+ final String area = getOptionalString(result, 9);
+ final String scope = getOptionalString(result, 10);
+ final String remarks = getOptionalString(result, 11);
+ final boolean deprecated = getBoolean (result, 12);
/*
* Create the source and target CRS for the codes fetched
above. Those CRS are optional only for
* conversions (the above calls to getString(code, result, …)
verified that those CRS are defined
diff --git
a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGInstaller.java
b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGInstaller.java
index eef5cf7db1..f4f2002d64 100644
---
a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGInstaller.java
+++
b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGInstaller.java
@@ -122,7 +122,18 @@ final class EPSGInstaller extends ScriptRunner {
}
/**
- * Processes to the creation of the EPSG database using the SQL scripts
from the given provider.
+ * Processes to the creation of the <abbr>EPSG</abbr> database using the
<abbr>SQL</abbr> scripts
+ * from the given provider. If the given provider is {@code null}, then
this method searches for
+ * a <abbr>SQL</abbr> default provider on the module path, in preference
order:
+ *
+ * <ol>
+ * <li>A provider from a publicly supported dependency such as {@code
sis-epsg.jar} or {@code sis-embedded.jar}.
+ * Users have to put one of those dependencies in the module path
themselves. This action is interpreted as
+ * an acceptance of <abbr>EPSG</abbr> terms of use, so no license
agreement window will popup.</li>
+ * <li>A provider offering users to automatically download the data
through an interactive application.
+ * Those providers are defined by {@code org.apache.sis.console} and
{@code org.apache.sis.gui} modules.
+ * Users must accept <abbr>EPSG</abbr> terms of use before the
database can be installed.
+ * </ol>
*
* @param scriptProvider user-provided scripts, or {@code null} for
automatic lookup.
* @param locale the locale for information or warning messages,
if any.
@@ -134,7 +145,14 @@ final class EPSGInstaller extends ScriptRunner {
public boolean run(InstallationResources scriptProvider, final Locale
locale) throws SQLException, IOException {
long time = System.nanoTime();
if (scriptProvider == null) {
- scriptProvider = lookupProvider(locale);
+ for (final InstallationResources candidate :
InstallationResources.load()) {
+ if (candidate.getAuthorities().contains(Constants.EPSG)) {
+ scriptProvider = candidate;
+ if
(!candidate.getClass().isAnnotationPresent(Fallback.class)) {
+ break;
+ }
+ }
+ }
if (scriptProvider == null) {
return false;
}
@@ -163,41 +181,6 @@ final class EPSGInstaller extends ScriptRunner {
return true;
}
- /**
- * Searches for a SQL script provider on the module path before to
fallback on the default provider.
- * The returned provider will be, in preference order:
- *
- * <ol>
- * <li>A provider from a publicly supported dependency such as {@code
sis-epsg.jar} or {@code sis-embedded.jar}.
- * Users have to put one of those dependencies in the module path
themselves. This action is interpreted as
- * an acceptance of EPSG terms of use, so no license agreement
window will popup.</li>
- * <li>A provider offering users to automatically download the data.
Those providers are defined by
- * {@code org.apache.sis.console} and {@code org.apache.sis.gui}
modules.
- * Users must accept EPSG terms of use before the database can be
installed.
- * </ol>
- *
- * @param locale the locale for information or warning messages, if any.
- * @return the SQL preferred script provider, or {@code null} if none.
- */
- private static InstallationResources lookupProvider(final Locale locale)
throws IOException {
- InstallationResources fallback = null;
- for (final InstallationResources provider :
InstallationResources.load()) {
- if (provider.getAuthorities().contains(Constants.EPSG)) {
- if (!provider.getClass().isAnnotationPresent(Fallback.class)) {
- return provider;
- }
- fallback = provider;
- }
- }
- /*
- * If we did not found a provider ready to use such as `sis-epsg.jar`
or `sis-embedded.jar`,
- * we may fallback on a provider offering to download the data (those
fallbacks are provided
- * by `org.apache.sis.console` and `org.apache.sis.gui` modules).
Those fallbacks will ask to
- * the users if they accept the EPSG Terms of Use.
- */
- return fallback;
- }
-
/**
* Creates a message reporting the failure to create EPSG database. This
method is invoked when {@link EPSGFactory}
* caught an exception. This method completes the exception message with
the file name and line number where the
diff --git
a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/SQLTranslator.java
b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/SQLTranslator.java
index 92350ce9fd..11bf562de0 100644
---
a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/SQLTranslator.java
+++
b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/SQLTranslator.java
@@ -314,7 +314,7 @@ public class SQLTranslator implements UnaryOperator<String>
{
*/
@SuppressWarnings("fallthrough")
final void setup(final DatabaseMetaData md) throws SQLException {
- String schemaPattern = SQLUtilities.escape(schema, wildcardEscape);
+ String schemaPattern = SQLUtilities.escapeWildcards(schema,
wildcardEscape);
int tableIndex = 0;
do {
usePrefixedTableNames = false;
@@ -323,7 +323,7 @@ public class SQLTranslator implements UnaryOperator<String>
{
switch (tableIndex++) {
case 0: { // Test EPSG standard table name first.
usePrefixedTableNames = true;
- table = SQLUtilities.escape(TABLE_PREFIX, wildcardEscape);
+ table = SQLUtilities.escapeWildcards(TABLE_PREFIX,
wildcardEscape);
// Fallthrough for testing "epsg_coordoperation".
}
case 2: {
@@ -357,7 +357,7 @@ public class SQLTranslator implements UnaryOperator<String>
{
* naming convention (unquoted or mixed-case, prefixed by "epsg_" or
not).
*/
UnaryOperator<String> toNativeCase = UnaryOperator.identity();
- schemaPattern = SQLUtilities.escape(schema, wildcardEscape);
+ schemaPattern = SQLUtilities.escapeWildcards(schema, wildcardEscape);
tableRewording = new HashMap<>();
replacements = new HashMap<>();
/*
@@ -461,7 +461,7 @@ check: for (;;) {
boolean isTableFound = false;
brokenTargetCols.addAll(mayRenameColumns.values());
table = toNativeCase.apply(toActualTableName(table));
- try (ResultSet result = md.getColumns(catalog, schemaPattern,
SQLUtilities.escape(table, wildcardEscape), "%")) {
+ try (ResultSet result = md.getColumns(catalog, schemaPattern,
SQLUtilities.escapeWildcards(table, wildcardEscape), "%")) {
while (result.next()) {
isTableFound = true; // Assuming that all tables
contain at least one column.
final String column =
result.getString(Reflection.COLUMN_NAME).toUpperCase(Locale.US);
diff --git
a/endorsed/src/org.apache.sis.referencing/test/org/apache/sis/referencing/factory/sql/EPSGFactoryTest.java
b/endorsed/src/org.apache.sis.referencing/test/org/apache/sis/referencing/factory/sql/EPSGFactoryTest.java
index 99d047429c..1f60163481 100644
---
a/endorsed/src/org.apache.sis.referencing/test/org/apache/sis/referencing/factory/sql/EPSGFactoryTest.java
+++
b/endorsed/src/org.apache.sis.referencing/test/org/apache/sis/referencing/factory/sql/EPSGFactoryTest.java
@@ -305,13 +305,13 @@ public final class EPSGFactoryTest extends
TestCaseWithLogs {
assertAxisDirectionsEqual(crs.getCoordinateSystem(),
AxisDirection.EAST, AxisDirection.NORTH);
assertSame(crs, factory.createProjectedCRS("27571"));
/*
- * Gets the CRS using 'createObject'. It will require more SQL
+ * Gets the CRS using `createObject`. It will require more SQL
* statement internally in order to determines the object type.
*/
assertSame(crs, factory.createObject("27571"));
assertSame(crs, factory.createObject("NTF (Paris) / Lambert zone I"));
- assertSame(crs, factory.createProjectedCRS("NTF Paris Lambert zone
I"));
- assertSame(crs, factory.createObject("NTF Paris Lambert zone I"));
+ assertSame(crs, factory.createProjectedCRS("ntf (paris) / lambert zone
I"));
+ assertSame(crs, factory.createObject("ntf (paris) / lambert zone I"));
loggings.assertNoUnexpectedLog();
}
@@ -558,7 +558,7 @@ public final class EPSGFactoryTest extends TestCaseWithLogs
{
final EPSGFactory factory = dataEPSG.factory();
/*
* Most basic objects.
- * Note: the numbers in 'size() >= x' checks were determined from the
content of EPSG dataset version 7.9.
+ * Note: the numbers in `size() >= x` checks were determined from the
content of EPSG dataset version 7.9.
*/
try {
final Set<String> axes =
factory.getAuthorityCodes(CoordinateSystemAxis.class);
diff --git
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/InfoStatements.java
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/InfoStatements.java
index d0096a7b1f..4fd44e5021 100644
---
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/InfoStatements.java
+++
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/InfoStatements.java
@@ -46,7 +46,6 @@ import org.apache.sis.referencing.cs.AxesConvention;
import org.apache.sis.referencing.factory.IdentifiedObjectFinder;
import org.apache.sis.referencing.internal.shared.DefinitionVerifier;
import org.apache.sis.referencing.internal.shared.ReferencingUtilities;
-import org.apache.sis.metadata.sql.internal.shared.SQLUtilities;
import org.apache.sis.metadata.sql.internal.shared.SQLBuilder;
import org.apache.sis.geometry.wrapper.GeometryType;
import org.apache.sis.system.CommonExecutor;
@@ -149,7 +148,7 @@ public class InfoStatements implements Localized,
AutoCloseable {
private PreparedStatement sridForUnknownTable;
/**
- * The statement for fetching a SRID from a CRS and its set of authority
codes.
+ * The statement for fetching a <abbr>SRID</abbr> from a <abbr>CRS</abbr>
and its set of authority codes.
* Created when first needed.
*
* @see #findOrAddCRS(CoordinateReferenceSystem)
@@ -484,7 +483,7 @@ public class InfoStatements implements Localized,
AutoCloseable {
appendFrom(sql, schema.crsTable);
sql.append(search).append("=?");
if (byAuthorityCode) {
- sql.append(" AND
LOWER(").append(schema.crsAuthorityNameColumn).append(") LIKE ?");
+ sql.append(" AND
").append(schema.crsAuthorityNameColumn).append("=?");
}
return connection.prepareStatement(sql.toString());
}
@@ -822,23 +821,21 @@ public class InfoStatements implements Localized,
AutoCloseable {
sridFromCRS = prepareSearchCRS(true);
}
sridFromCRS.setInt(1, code);
- sridFromCRS.setString(2, SQLUtilities.toLikePattern(authority,
true, database.wildcardEscape));
+ sridFromCRS.setString(2, authority);
try (ResultSet result = sridFromCRS.executeQuery()) {
while (result.next()) {
- if (SQLUtilities.filterFalsePositive(authority,
result.getString(1))) {
- final int srid = result.getInt(2);
- if (sridFounInUse.add(srid)) try {
- final Object parsed = parseDefinition(result,
3);
- if (Utilities.equalsApproximately(parsed,
crs)) {
- search.srid = srid;
- return search;
- }
- } catch (ParseException e) {
- if (error == null) error = e;
- else error.addSuppressed(e);
+ final int srid = result.getInt(2);
+ if (sridFounInUse.add(srid)) try {
+ final Object parsed = parseDefinition(result, 3);
+ if (Utilities.equalsApproximately(parsed, crs)) {
+ search.srid = srid;
+ return search;
}
- done.put(search, Boolean.FALSE); // Declare
this "authority:code" pair as not available.
+ } catch (ParseException e) {
+ if (error == null) error = e;
+ else error.addSuppressed(e);
}
+ done.put(search, Boolean.FALSE); // Declare this
"authority:code" pair as not available.
}
}
}
diff --git
a/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/Content.java
b/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/Content.java
index 08ef3b8fd4..2856608183 100644
---
a/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/Content.java
+++
b/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/Content.java
@@ -254,7 +254,7 @@ public class Content extends ResourceDefinition {
final String escape =
stmt.getConnection().getMetaData().getSearchStringEscape();
while (rs.next()) {
String tableName = getString(rs, 1);
- GenericName name = factory.createLocalName(namespace,
SQLUtilities.escape(tableName, escape));
+ GenericName name = factory.createLocalName(namespace,
SQLUtilities.escapeWildcards(tableName, escape));
contents.add(new Content(dao, name, tableName, rs, listeners));
}
} catch (SQLException e) {
diff --git
a/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/GpkgStoreProvider.java
b/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/GpkgStoreProvider.java
index 7fa5f33137..1fd3503b28 100644
---
a/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/GpkgStoreProvider.java
+++
b/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/GpkgStoreProvider.java
@@ -192,7 +192,7 @@ public class GpkgStoreProvider extends DataStoreProvider {
if (source != null) {
try (Connection connection = source.getConnection()) {
final DatabaseMetaData metadata = connection.getMetaData();
- final String table = SQLUtilities.escape(Content.TABLE_NAME,
metadata.getSearchStringEscape());
+ final String table =
SQLUtilities.escapeWildcards(Content.TABLE_NAME,
metadata.getSearchStringEscape());
try (ResultSet r = metadata.getColumns(null, null, table,
"%")) {
boolean hasTable = false, hasType = false;
while (r.next()) {
diff --git
a/optional/src/org.apache.sis.referencing.database/main/org/apache/sis/resources/embedded/EmbeddedResources.java
b/optional/src/org.apache.sis.referencing.database/main/org/apache/sis/resources/embedded/EmbeddedResources.java
index 8dabce60c1..79524923b1 100644
---
a/optional/src/org.apache.sis.referencing.database/main/org/apache/sis/resources/embedded/EmbeddedResources.java
+++
b/optional/src/org.apache.sis.referencing.database/main/org/apache/sis/resources/embedded/EmbeddedResources.java
@@ -131,13 +131,18 @@ public class EmbeddedResources extends
InstallationResources {
*
* @param authority shall be {@code "Embedded"}.
* @param index shall be 0.
- * @return the embedded data source.
+ * @return the embedded data source, or {@code null} if not available.
*/
@Override
public DataSource getResource(String authority, int index) {
verifyAuthority(authority);
synchronized (Initializer.class) {
- if (dataSource == null) {
+ /*
+ * Check for `LICENSE.txt` as a way to detect whether the data are
available.
+ * It should always be the case when using a released JAR file,
but it may not
+ * be the case when using a local build.
+ */
+ if (dataSource == null &&
EmbeddedResources.class.getResourceAsStream("LICENSE.txt") != null) {
final var ds = new EmbeddedDataSource();
ds.setDataSourceName(Initializer.DATABASE);
ds.setDatabaseName("classpath:" + DIRECTORY + "/Databases/" +
Initializer.DATABASE);
diff --git
a/optional/src/org.apache.sis.referencing.epsg/main/org/apache/sis/referencing/factory/sql/epsg/Prepare.sql
b/optional/src/org.apache.sis.referencing.epsg/main/org/apache/sis/referencing/factory/sql/epsg/Prepare.sql
index 9083d21524..080febcdbb 100644
---
a/optional/src/org.apache.sis.referencing.epsg/main/org/apache/sis/referencing/factory/sql/epsg/Prepare.sql
+++
b/optional/src/org.apache.sis.referencing.epsg/main/org/apache/sis/referencing/factory/sql/epsg/Prepare.sql
@@ -54,3 +54,9 @@ CREATE CAST (VARCHAR AS "CRS Kind") WITH INOUT AS
ASSIGNMENT;
CREATE CAST (VARCHAR AS "CS Kind") WITH INOUT AS ASSIGNMENT;
CREATE CAST (VARCHAR AS "Supersession Type") WITH INOUT AS ASSIGNMENT;
CREATE CAST (VARCHAR AS "Table Name") WITH INOUT AS ASSIGNMENT;
+
+--
+-- PostgreSQL: collation using the International Components for Unicode (ICU)
library.
+--
https://www.postgresql.org/docs/current/collation.html#ICU-COLLATION-SETTINGS
+--
+CREATE COLLATION "Ignore Accent and Case" (PROVIDER = 'icu', DETERMINISTIC =
false, LOCALE = 'en_GB-u-ka-shifted-ks-level1');
diff --git
a/optional/src/org.apache.sis.referencing.epsg/test/org/apache/sis/referencing/factory/sql/epsg/Changes.md
b/optional/src/org.apache.sis.referencing.epsg/test/org/apache/sis/referencing/factory/sql/epsg/Changes.md
new file mode 100644
index 0000000000..11e941e896
--- /dev/null
+++
b/optional/src/org.apache.sis.referencing.epsg/test/org/apache/sis/referencing/factory/sql/epsg/Changes.md
@@ -0,0 +1,46 @@
+# Changes in EPSG scripts
+
+This page summarizes the changes applied by Apache SIS on the SQL scripts
published by EPSG.
+For more information about the use of EPSG geodetic dataset in Apache SIS, see
[README](./README.md).
+
+## Tables
+
+Additions:
+* Comments in a header for explaining the origin of the file.
+* In the "Coordinate Axis" table, `NOT NULL` constraint added to the
`coord_axis_code` column.
+* `COLLATE "Ignore Accent and Case"` added in definitions of columns for
object names or aliases.
+
+Removal of unused tables or columns:
+* The "Change" table and the `change_id` column in all tables.
+* The `information_source`, `data_source` and `revision_date` columns in all
tables.
+* The `crs_scope`, `coord_op_scope`, `datum_scope` and `area_of_use_code`
columns (deprecated).
+
+Change of data types:
+* Type of `realization_epoch` and `publication_date` columns changed to `DATE`
(in the "Datum" table).
+* Type of `epsg_usage` column changed from `SERIAL` to `INTEGER NOT NULL`.
+* Type of `table_name` column in all tables changed from `VARCHAR(80)` to
"Table Name".
+* Type of `coord_ref_sys_kind` column changed from `VARCHAR(24)` to "CRS Kind".
+* Type of `coord_sys_type` column changed from `VARCHAR(24)` to "CS Kind".
+* Type of `datum_type` column changed from `VARCHAR(24)` to "Datum Kind".
+* Type of `supersession_type` column changed from `VARCHAR(50)` to
"Supersession Type".
+* Type of `ellipsoid_shape`, `reverse_op`, `param_sign_reversal`, `show_crs`,
`show_operation`
+ and all `deprecated` columns changed from `SMALLINT` (or sometimes
`VARCHAR(3)`) to `BOOLEAN`.
+* Change all `FLOAT` types to `DOUBLE PRECISION` because Apache SIS reads all
numbers as `double` type.
+ This change avoids spurious digits in the conversions from `float` to
`double`.
+
+Changes that do not impact data:
+* Rename `epsg_` table names to the camel case convention used by Apache SIS.
+* Use a different column order for keeping related columns close to each other.
+* Suppress trailing `NULL` (not to be confused with `NOT NULL`) as they are
implicit.
+
+**Maintenance note:** if some values were added in any enumeration, check the
maximal
+length of the `VARCHAR` replacements in the
`EPSGInstaller.identifierReplacements` map.
+If some new columns have their type changed to the Boolean or double-precision
type,
+some hard-coded values in the `DataScriptFormatter` class may need to be
modified,
+in particular the `booleanColumns` and `doubleColumns` collections.
+
+
+## Foreigner keys
+
+* Remove the `fk_change_id` foreigner key.
+* At the end of all `ALTER TABLE` statement, append `ON UPDATE RESTRICT ON
DELETE RESTRICT`.
diff --git
a/optional/src/org.apache.sis.referencing.epsg/test/org/apache/sis/referencing/factory/sql/epsg/README.md
b/optional/src/org.apache.sis.referencing.epsg/test/org/apache/sis/referencing/factory/sql/epsg/README.md
index 4c4beff5dd..d52b844c47 100644
---
a/optional/src/org.apache.sis.referencing.epsg/test/org/apache/sis/referencing/factory/sql/epsg/README.md
+++
b/optional/src/org.apache.sis.referencing.epsg/test/org/apache/sis/referencing/factory/sql/epsg/README.md
@@ -37,8 +37,10 @@ Execute the scripts in the `public` schema of a PostgreSQL
database on the local
This page assumes that the database name is "Referencing", but any other name
can be used
if the argument given to `TableScriptUpdater` (later in this page) is adjusted
accordingly.
-If a copy of the original SQL scripts (as downloaded from EPSG) for the
previous version is still available,
-and if the following commands report no difference, then jump to "Automatic
updates after the manual checks" step.
+
+### Updates the Data Definition scripts
+
+Verify that the new SQL scripts downloaded from EPSG defines the same tables
as the previous version:
```bash
cd _<directory containing EPSG scripts of previous version>_
@@ -46,70 +48,16 @@ diff PostgreSQL_Table_Script.sql
$EPSG_SCRIPTS/PostgreSQL_Table_Script.sql
diff PostgreSQL_FKey_Script.sql $EPSG_SCRIPTS/PostgreSQL_FKey_Script.sql
```
-Otherwise, move to the directory which contains the Apache SIS scripts:
+If there are some changes, port them manually to the {@code Tables.sql} and
{@code FKeys.sql} scripts.
+The [page listing the changes](./Changes.html) gives information about the
changes to expert or to reproduce.
+Then, execute the `main` method of the
`org.apache.sis.referencing.factory.sql.epsg.*Updater` classes
+located in the test directory of the `org.apache.sis.non-free:sis-epsg` Maven
sub-project.
+Adjust version numbers as needed in the following commands:
```bash
cd <path to a local copy of
http://svn.apache.org/repos/asf/sis/data/non-free/EPSG/>
export NON_FREE_DIR=$PWD
-```
-
-Overwrite `Tables.sql` and `FKeys.sql` with the new SQL scripts.
-Do not overwrite `Data.sql` yet:
-
-```bash
-cp $EPSG_SCRIPTS/PostgreSQL_Table_Script.sql Tables.sql
-cp $EPSG_SCRIPTS/PostgreSQL_FKey_Script.sql FKeys.sql
-```
-
-### Manual checks and editions
-
-Open the `Tables.sql` file for edition:
-
-* Keep the header comments that existed in the overwritten file.
-* Remove the `"Change"` table and the `change_id` column in all tables. They
are EPSG metadata unused by Apache SIS.
-* Remove the `information_source`, `data_source` and `revision_date` columns
in all tables. They are EPSG metadata unused by Apache SIS.
-* Remove the `crs_scope`, `coord_op_scope`, `datum_scope` and
`area_of_use_code` columns, which are deprecated.
-* Keep the same column order than in the previous `Tables.sql`.
-* Rename `epsg_` table names to the camel case convention used by Apache SIS.
-* Suppress trailing `NULL` (not to be confused with `NOT NULL`) as they are
implicit.
-* In the statement creating the `coordinateaxis` table,
- add the `NOT NULL` constraint to the `coord_axis_code` column.
-* In the statement creating the `epsg_datum` table,
- change the type of the `realization_epoch` and `publication_date` columns to
`DATE`.
-* Change the type of `ellipsoid_shape`, `reverse_op`, `param_sign_reversal`
- `show_crs`, `show_operation` and all `deprecated` fields from `SMALLINT`
- (or sometimes `VARCHAR(3)`) to `BOOLEAN`.
-* Change all `FLOAT` types to `DOUBLE PRECISION` because Apache SIS reads all
numbers as `double` type.
- This change avoids spurious digits in the conversions from `float` to
`double`.
-* Change the type of `epsg_usage` column from `SERIAL` to `INTEGER NOT NULL`.
-* Change the type of every `table_name` columns from `VARCHAR(80)` to `"Table
Name"`.
-* Change the type of `coord_ref_sys_kind` column from `VARCHAR(24)` to `"CRS
Kind"`.
-* Change the type of `coord_sys_type` column from `VARCHAR(24)` to `"CS Kind"`.
-* Change the type of `datum_type` column from `VARCHAR(24)` to `"Datum Kind"`.
-* Change the type of `supersession_type` column from `VARCHAR(50)` to
`"Supersession Type"`.
-* If new enumeration values are added, check the maximal lengths of `VARCHAR`
replacements in `EPSGInstaller`.
-* Suppress trailing spaces and save.
-
-Then open the `FKeys.sql` file for edition:
-
-* Remove the `fk_change_id` foreigner key.
-* At the end of all `ALTER TABLE` statement, append `ON UPDATE RESTRICT ON
DELETE RESTRICT`.
-* Suppress trailing spaces and save.
-
-Usually, the above editions result in no change compared to the previous
scripts (ignoring white spaces),
-in which case the maintainer can just revert the changes in order to preserve
the formatting.
-However, if some changes are found in the schema, then hard-coded values in
the `DataScriptFormatter` class
-may need to be modified, in particular the `booleanColumns` and
`doubleColumns` collections.
-
-
-### Automatic updates after the manual checks
-
-Execute the `main` method of the
`org.apache.sis.referencing.factory.sql.epsg.*Updater` classes
-located in the test directory of the `org.apache.sis.non-free:sis-epsg` Maven
sub-project.
-Adjust version numbers as needed in the following commands:
-
-```bash
cd _<path to SIS project directory>_
gradle clean test jar
export
CLASSPATH=~/.m2/repository/org/apache/derby/derby/10.14.2.0/derby-10.14.2.0.jar
