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 3a6a62fb82161f7516804a62e98c361ff821f231
Author: Martin Desruisseaux <martin.desruisse...@geomatys.com>
AuthorDate: Fri Aug 16 11:25:26 2024 +0200
Use "LIKE" instead of strict equality for the authority part when searching
for "authority:code" in the "SPATIAL_REF_SYS" table.
---
.../sis/metadata/sql/privy/SQLUtilities.java | 22 +++++++--
.../referencing/factory/sql/EPSGDataAccess.java | 14 +-----
.../apache/sis/storage/sql/feature/Database.java | 6 ---
.../sis/storage/sql/feature/InfoStatements.java | 53 ++++++++++++----------
.../storage/sql/feature/InfoStatementsTest.java | 13 +++---
.../sis/storage/sql/postgis/PostgresTest.java | 1 +
6 files changed, 57 insertions(+), 52 deletions(-)
diff --git
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/SQLUtilities.java
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/SQLUtilities.java
index 474aa90b25..c3196ad286 100644
---
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/SQLUtilities.java
+++
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/SQLUtilities.java
@@ -125,24 +125,38 @@ public final class SQLUtilities extends Static {
}
/**
- * Returns a SQL LIKE pattern for the given identifier. The identifier is
optionally returned in all lower cases
+ * Returns a string like the given string but with accented letters
replaced by ASCII letters
+ * and all characters that are not letter or digit replaced by the
wildcard % character.
+ *
+ * @param text the text to get as a SQL LIKE pattern.
+ * @param toLower whether to convert characters to lower case.
+ * @return the "LIKE" pattern for the given text.
+ */
+ public static String toLikePattern(final String text, final boolean
toLower) {
+ final var buffer = new StringBuilder(text.length());
+ toLikePattern(text, 0, text.length(), false, toLower, buffer);
+ return buffer.toString();
+ }
+
+ /**
+ * Returns a SQL 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-ASCII letters or digits are replaced by any single character
({@code '_'}). This method avoid to put
* a {@code '%'} symbol as the first character since it prevents some
databases to use their index.
*
- * @param identifier the identifier to get as a SQL LIKE pattern.
+ * @param text the text to get as a SQL LIKE pattern.
* @param i index of the first character to use in the given
{@code identifier}.
* @param end index after the last character to use in the given
{@code identifier}.
* @param allowSuffix whether to append a final {@code '%'} wildcard at
the end of the pattern.
* @param toLower whether to convert characters to lower case.
* @param buffer buffer where to append the SQL LIKE pattern.
*/
- public static void toLikePattern(final String identifier, int i, final int
end,
+ public static void toLikePattern(final String text, int i, final int end,
final boolean allowSuffix, final boolean toLower, final
StringBuilder buffer)
{
final int bs = buffer.length();
while (i < end) {
- final int c = identifier.codePointAt(i);
+ final int c = text.codePointAt(i);
if (Character.isLetterOrDigit(c)) {
if (c < 128) { // Use only ASCII
characters in the search.
buffer.appendCodePoint(toLower ? Character.toLowerCase(c)
: c);
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 21cf4bec22..cdf84ef70a 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
@@ -694,7 +694,7 @@ codes: for (int i=0; i<codes.length; i++) {
* We search first in the primary table. If no name is not
found there, then we
* will search in the aliases table as a fallback.
*/
- final String pattern = toLikePattern(code);
+ final String pattern = SQLUtilities.toLikePattern(code, false);
Integer resolved = null;
boolean alias = false;
do {
@@ -1205,16 +1205,6 @@ codes: for (int i=0; i<codes.length; i++) {
return properties;
}
- /**
- * Returns a string like the given string but with accented letters
replaced by ASCII letters
- * and all characters that are not letter or digit replaced by the
wildcard % character.
- */
- private static String toLikePattern(final String name) {
- final StringBuilder buffer = new StringBuilder(name.length());
- SQLUtilities.toLikePattern(name, 0, name.length(), false, false,
buffer);
- return buffer.toString();
- }
-
/**
* Returns an arbitrary object from a code. The default implementation
delegates to more specific methods,
* for example {@link #createCoordinateReferenceSystem(String)}, {@link
#createDatum(String)}, <i>etc.</i>
@@ -1266,7 +1256,7 @@ codes: for (int i=0; i<codes.length; i++) {
if (isPrimaryKey) {
stmt.setInt(1, pk);
} else {
- stmt.setString(1, toLikePattern(code));
+ stmt.setString(1, SQLUtilities.toLikePattern(code,
false));
}
Integer present = null;
try (ResultSet result = stmt.executeQuery()) {
diff --git
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Database.java
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Database.java
index cf485bceb2..66010d1a6b 100644
---
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Database.java
+++
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Database.java
@@ -158,12 +158,6 @@ public class Database<G> extends Syntax {
*/
final EnumSet<CRSEncoding> crsEncodings;
- /**
- * Whether to allow the addition of new <abbr>CRS</abbr> definitions in
the {@code SPATIAL_REF_SYS} table.
- * The default value is {@code false}, which is preferable for read-only
databases.
- */
- public volatile boolean allowAddCRS;
-
/**
* {@code true} if this database contains at least one geometry column.
* This field is initialized by {@link #analyze analyze(…)} and shall not
be modified after that point.
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 917ed56fc9..19adc0d1f7 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
@@ -41,8 +41,10 @@ import org.apache.sis.storage.DataStoreContentException;
import org.apache.sis.storage.DataStoreReferencingException;
import org.apache.sis.referencing.CRS;
import org.apache.sis.referencing.IdentifiedObjects;
+import org.apache.sis.referencing.factory.IdentifiedObjectFinder;
import org.apache.sis.referencing.privy.DefinitionVerifier;
import org.apache.sis.referencing.privy.ReferencingUtilities;
+import org.apache.sis.metadata.sql.privy.SQLUtilities;
import org.apache.sis.metadata.sql.privy.SQLBuilder;
import org.apache.sis.geometry.wrapper.GeometryType;
import org.apache.sis.system.Modules;
@@ -52,7 +54,6 @@ import org.apache.sis.util.privy.Constants;
import org.apache.sis.io.wkt.Convention;
import org.apache.sis.io.wkt.WKTFormat;
import org.apache.sis.io.wkt.Warnings;
-import org.apache.sis.referencing.factory.IdentifiedObjectFinder;
// Specific to the geoapi-3.1 and geoapi-4.0 branches:
import org.opengis.metadata.Identifier;
@@ -312,22 +313,24 @@ public class InfoStatements implements Localized,
AutoCloseable {
*/
private PreparedStatement prepareSearchCRS(final boolean byAuthorityCode)
throws SQLException {
final SpatialSchema schema = database.getSpatialSchema().orElseThrow();
- final SQLBuilder sql = new
SQLBuilder(database).append(SQLBuilder.SELECT);
+ final String search, get;
if (byAuthorityCode) {
- sql.append(schema.crsIdentifierColumn);
+ search = schema.crsAuthorityCodeColumn;
+ get = schema.crsIdentifierColumn;
} else {
- sql.append(schema.crsAuthorityNameColumn).append(", ")
- .append(schema.crsAuthorityCodeColumn);
+ search = schema.crsIdentifierColumn;
+ get = schema.crsAuthorityCodeColumn;
}
+ final var sql = new SQLBuilder(database).append(SQLBuilder.SELECT)
+ .append(schema.crsAuthorityNameColumn).append(",
").append(get);
+
for (CRSEncoding encoding : database.crsEncodings) {
sql.append(", ").append(schema.crsDefinitionColumn.get(encoding));
}
appendFrom(sql, schema.crsTable);
+ sql.append(search).append("=?");
if (byAuthorityCode) {
- sql.append(schema.crsAuthorityNameColumn).append("=? AND ")
- .append(schema.crsAuthorityCodeColumn).append("=?");
- } else {
- sql.append(schema.crsIdentifierColumn).append("=?");
+ sql.append(" AND
LOWER(").append(schema.crsAuthorityNameColumn).append(") LIKE ?");
}
return connection.prepareStatement(sql.toString());
}
@@ -468,7 +471,7 @@ public class InfoStatements implements Localized,
AutoCloseable {
* Finds a SRID code from the spatial reference systems table for the
given CRS.
* If the database does not support concurrent transactions, then the
caller is
* responsible for holding a lock. It may be a read lock or write lock
depending
- * on the {@link Database#allowAddCRS} value.
+ * on the {@link Connection#isReadOnly()} value.
*
* @param crs the CRS for which to find a SRID, or {@code null}.
* @return SRID for the given CRS, or 0 if the given CRS was null.
@@ -525,24 +528,26 @@ public class InfoStatements implements Localized,
AutoCloseable {
if (sridFromCRS == null) {
sridFromCRS = prepareSearchCRS(true);
}
- sridFromCRS.setString(1, authority);
- sridFromCRS.setInt(2, codeValue);
+ sridFromCRS.setInt(1, codeValue);
+ sridFromCRS.setString(2, SQLUtilities.toLikePattern(authority,
true));
try (ResultSet result = sridFromCRS.executeQuery()) {
while (result.next()) {
- final int srid = result.getInt(1);
- if (sridFounInUse.add(srid)) try {
- final Object parsed = parseDefinition(result, 2);
- if (Utilities.equalsApproximately(parsed, crs)) {
- synchronized (database.cacheOfSRID) {
- database.cacheOfSRID.put(crs, srid);
+ 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)) {
+ synchronized (database.cacheOfSRID) {
+ database.cacheOfSRID.put(crs, srid);
+ }
+ return srid;
}
- return srid;
+ } catch (ParseException e) {
+ if (error == null) error = e;
+ else error.addSuppressed(e);
}
- } catch (ParseException e) {
- if (error == null) error = e;
- else error.addSuppressed(e);
+ done.put(key, Boolean.FALSE); // Declare
this "authority:code" pair as not available.
}
- done.put(key, Boolean.FALSE); // Declare this
"authority:code" pair as not available.
}
}
}
@@ -563,7 +568,7 @@ public class InfoStatements implements Localized,
AutoCloseable {
* If the caller allowed the creation of new rows in that table,
creates it now.
* It is caller's responsibility to hold a write lock if needed.
*/
- if (database.allowAddCRS) {
+ if (!connection.isReadOnly()) {
String fallback = null;
int fallbackCode = 0;
for (final Map.Entry<SimpleImmutableEntry<String,Object>, Boolean>
entry : done.entrySet()) {
diff --git
a/endorsed/src/org.apache.sis.storage.sql/test/org/apache/sis/storage/sql/feature/InfoStatementsTest.java
b/endorsed/src/org.apache.sis.storage.sql/test/org/apache/sis/storage/sql/feature/InfoStatementsTest.java
index da2e2822fe..b93ac7b67a 100644
---
a/endorsed/src/org.apache.sis.storage.sql/test/org/apache/sis/storage/sql/feature/InfoStatementsTest.java
+++
b/endorsed/src/org.apache.sis.storage.sql/test/org/apache/sis/storage/sql/feature/InfoStatementsTest.java
@@ -114,15 +114,16 @@ public final class InfoStatementsTest extends TestCase {
*/
@Test
public void testFindSRID() throws Exception {
- try (InfoStatements info = new InfoStatements(database, connection)) {
- database.allowAddCRS = false;
+ final Connection c = connection;
+ try (InfoStatements info = new InfoStatements(database, c)) {
+ c.setReadOnly(true);
final CoordinateReferenceSystem crs = HardCodedCRS.WGS84;
var e = assertThrows(DataStoreReferencingException.class, () ->
info.findSRID(crs));
assertMessageContains(e, crs.getName().getCode());
// Now do the actual insertion.
- database.allowAddCRS = true; assertEquals(4326,
info.findSRID(crs));
- database.allowAddCRS = false; assertEquals(4326,
info.findSRID(crs));
+ c.setReadOnly(false); assertEquals(4326, info.findSRID(crs));
+ c.setReadOnly(true); assertEquals(4326, info.findSRID(crs));
// CRS with the same code (intentional clash with EPSG:4326).
final CoordinateReferenceSystem clash = new DefaultGeographicCRS(
@@ -130,8 +131,8 @@ public final class InfoStatementsTest extends TestCase {
CoordinateReferenceSystem.IDENTIFIERS_KEY, new
ImmutableIdentifier(null, "FOO", "4326")),
HardCodedDatum.SPHERE, null, HardCodedCS.GEODETIC_2D);
- database.allowAddCRS = true; assertEquals(1,
info.findSRID(clash));
- database.allowAddCRS = false; assertEquals(1,
info.findSRID(clash));
+ c.setReadOnly(false); assertEquals(1, info.findSRID(clash));
+ c.setReadOnly(true); assertEquals(1, info.findSRID(clash));
}
}
diff --git
a/endorsed/src/org.apache.sis.storage.sql/test/org/apache/sis/storage/sql/postgis/PostgresTest.java
b/endorsed/src/org.apache.sis.storage.sql/test/org/apache/sis/storage/sql/postgis/PostgresTest.java
index 75437a403f..c9f3d458a2 100644
---
a/endorsed/src/org.apache.sis.storage.sql/test/org/apache/sis/storage/sql/postgis/PostgresTest.java
+++
b/endorsed/src/org.apache.sis.storage.sql/test/org/apache/sis/storage/sql/postgis/PostgresTest.java
@@ -122,6 +122,7 @@ public final class PostgresTest extends TestCase {
try (Connection connection = database.source.getConnection();
ExtendedInfo info = new ExtendedInfo(pg, connection))
{
+ connection.setReadOnly(true); // For avoiding accidental
changes to "SPATIAL_REF_SYS" table.
testInfoStatements(info);
testGeometryGetter(info, connection);
testRasterReader(TestRaster.USHORT, info, connection);
