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 a09dffd3c53a5290bab76d2c043808b0e722f738
Author: Martin Desruisseaux <martin.desruisse...@geomatys.com>
AuthorDate: Thu Aug 15 19:53:05 2024 +0200
If a CRS is not found in the "SPATIAL_REF_SYS" table, optionally add a new
record in that table.
This feature need to be enabled by setting the `Database.allowAddCRS` flag
to `true`,
which should be done only when the caller intend to write in the database.
---
.../apache/sis/metadata/sql/privy/SQLBuilder.java | 13 +
.../org/apache/sis/metadata/sql/TestDatabase.java | 8 +-
.../apache/sis/storage/sql/feature/Database.java | 23 +-
.../sis/storage/sql/feature/InfoStatements.java | 349 ++++++++++++++++++---
.../sis/storage/sql/feature/SpatialSchema.java | 30 +-
.../sis/storage/sql/postgis/ExtentEstimator.java | 2 +-
.../storage/sql/feature/InfoStatementsTest.java | 152 +++++++++
7 files changed, 518 insertions(+), 59 deletions(-)
diff --git
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/SQLBuilder.java
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/SQLBuilder.java
index e3b3dac0a7..b341fedbfe 100644
---
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/SQLBuilder.java
+++
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/SQLBuilder.java
@@ -306,6 +306,19 @@ public class SQLBuilder extends Syntax {
return this;
}
+ /**
+ * Removes the {@code WHERE} clause and everything after it.
+ *
+ * @return this builder, for method call chaining.
+ */
+ public final SQLBuilder removeWhereClause() {
+ final int index = buffer.indexOf(" WHERE ");
+ if (index >= 0) {
+ buffer.setLength(index);
+ }
+ return this;
+ }
+
/**
* Returns a SQL statement for adding a column in a table.
* The returned statement is of the form:
diff --git
a/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/TestDatabase.java
b/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/TestDatabase.java
index 6568d54fee..0da93bf15a 100644
---
a/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/TestDatabase.java
+++
b/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/TestDatabase.java
@@ -139,13 +139,13 @@ public class TestDatabase implements AutoCloseable {
if (TEST_DATABASE != null) {
return new TestDatabase(TEST_DATABASE, Dialect.ANSI);
}
- final EmbeddedDataSource ds = new EmbeddedDataSource();
+ final var ds = new EmbeddedDataSource();
ds.setDatabaseName("memory:" + name);
ds.setDataSourceName("Apache SIS test database");
ds.setCreateDatabase("create");
return new TestDatabase(ds, Dialect.DERBY) {
@Override public void close() throws SQLException {
- final EmbeddedDataSource ds = (EmbeddedDataSource) source;
+ final var ds = (EmbeddedDataSource) source;
ds.setCreateDatabase("no");
ds.setConnectionAttributes("drop=true");
try {
@@ -183,7 +183,7 @@ public class TestDatabase implements AutoCloseable {
pool.setURL(url);
ds = pool;
} else {
- final org.hsqldb.jdbc.JDBCDataSource simple = new
org.hsqldb.jdbc.JDBCDataSource();
+ final var simple = new org.hsqldb.jdbc.JDBCDataSource();
simple.setURL(url);
ds = simple;
pool = null;
@@ -216,7 +216,7 @@ public class TestDatabase implements AutoCloseable {
* The DB_CLOSE_DELAY=-1 parameter keeps the database alive until
SHUTDOWN is invoked.
*/
final String url = "jdbc:h2:mem:" + name + ";DB_CLOSE_DELAY=-1";
- final org.h2.jdbcx.JdbcDataSource ds = new
org.h2.jdbcx.JdbcDataSource();
+ final var ds = new org.h2.jdbcx.JdbcDataSource();
ds.setURL(url);
return new TestDatabase(ds, Dialect.ANSI) {
@Override public void close() throws SQLException {
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 75c9a39306..cf485bceb2 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
@@ -135,8 +135,7 @@ public class Database<G> extends Syntax {
/**
* All tables known to this {@code Database} in declaration order.
* This array contains only the tables specified at initialization time,
not the dependencies.
- * This field is initialized by {@link #analyze(SQLStore, Connection,
ResourceDefinition...)}
- * and shall not be modified after that point.
+ * This field is initialized by {@link #analyze analyze(…)} and shall not
be modified after that point.
*/
private Table[] tables;
@@ -159,6 +158,12 @@ 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.
@@ -552,14 +557,14 @@ public class Database<G> extends Syntax {
}
/**
- * Appends a call to a function defined in the spatial schema.
- * The function name will be prefixed by catalog and schema name if
applicable.
- * The function will not be quoted.
+ * Appends a call to a function or table defined in the spatial schema.
+ * The name will be prefixed by catalog and schema name if applicable.
+ * The name will not be quoted.
*
- * @param sql the SQL builder where to add the spatial function
name.
- * @param function the function to append.
+ * @param sql the SQL builder where to add the spatial function or
table name.
+ * @param name the function or table to append.
*/
- public final void appendFunctionCall(final SQLBuilder sql, final String
function) {
+ public final void appendSpatialSchema(final SQLBuilder sql, final String
name) {
final String schema = schemaOfSpatialTables;
if (schema != null && !schema.isEmpty()) {
final String catalog = catalogOfSpatialTables;
@@ -568,7 +573,7 @@ public class Database<G> extends Syntax {
}
sql.appendIdentifier(schema).append('.');
}
- sql.append(function);
+ sql.append(name);
}
/**
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 ac6e66e91d..917ed56fc9 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
@@ -19,7 +19,9 @@ package org.apache.sis.storage.sql.feature;
import java.util.Map;
import java.util.Set;
import java.util.HashSet;
+import java.util.LinkedHashMap;
import java.util.AbstractMap.SimpleImmutableEntry;
+import java.util.ArrayList;
import java.util.Iterator;
import java.util.Locale;
import java.util.logging.Level;
@@ -29,6 +31,7 @@ import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
+import java.sql.Statement;
import java.sql.SQLException;
import org.opengis.referencing.IdentifiedObject;
import org.opengis.referencing.NoSuchAuthorityCodeException;
@@ -74,6 +77,18 @@ import org.opengis.metadata.Identifier;
* @see <a href="https://www.ogc.org/standards/sfs";>OGC Simple feature access
— Part 2: SQL option</a>
*/
public class InfoStatements implements Localized, AutoCloseable {
+ /**
+ * Upper limit of the range of low SRID codes that we may use for custom
CRS.
+ * This is used only if the EPSG code cannot be used.
+ */
+ private static final int LOW_SRID_RANGE = 1000;
+
+ /**
+ * Upper limit of the high range of SRID codes that we can use for custom
CRS.
+ * This is used only if the EPSG code cannot be used.
+ */
+ private static final int HIGH_SRID_RANGE = 40000;
+
/**
* The database that created this set of cached statements. This object
includes the
* cache of CRS created from SRID codes and the listeners where to send
warnings.
@@ -105,9 +120,11 @@ public class InfoStatements implements Localized,
AutoCloseable {
private PreparedStatement sridFromCRS;
/**
- * The object to use for parsing Well-Known Text (WKT), created when first
needed.
+ * The object to use for parsing or formatting Well-Known Text (WKT),
created when first needed.
+ *
+ * @see #wktFormat()
*/
- private WKTFormat wktReader;
+ private WKTFormat wktFormat;
/**
* Creates an initially empty {@code CachedStatements} which will use
@@ -146,11 +163,7 @@ public class InfoStatements implements Localized,
AutoCloseable {
* The table name will be prefixed by catalog and schema name if
applicable.
*/
private void appendFrom(final SQLBuilder sql, final String table) {
- /*
- * Despite its name, `appendFunctionCall(…)` can also be used for
formatting
- * table names provided that we want unquoted names (which is the case
here).
- */
- database.appendFunctionCall(sql.append(" FROM "), table);
+ database.appendSpatialSchema(sql.append(" FROM "), table);
sql.append(" WHERE ");
}
@@ -299,8 +312,7 @@ 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);
- sql.append(SQLBuilder.SELECT);
+ final SQLBuilder sql = new
SQLBuilder(database).append(SQLBuilder.SELECT);
if (byAuthorityCode) {
sql.append(schema.crsIdentifierColumn);
} else {
@@ -393,7 +405,7 @@ public class InfoStatements implements Localized,
AutoCloseable {
* Following warnings may have occurred during WKT
parsing and are considered minor.
* They will be reported only if there are no more
important warnings to report.
*/
- final Warnings w = wktReader.getWarnings();
+ final Warnings w = wktFormat.getWarnings();
if (w != null) {
warning = new LogRecord(Level.WARNING,
w.toString(getLocale()));
}
@@ -412,13 +424,23 @@ public class InfoStatements implements Localized,
AutoCloseable {
final SpatialSchema schema =
database.getSpatialSchema().orElseThrow();
throw invalidSRID(Resources.Keys.UnknownSRID_2, schema.crsTable,
srid, null);
}
+ log("fetchCRS", warning);
+ return crs;
+ }
+
+ /**
+ * Logs the given warning if it is non-null.
+ *
+ * @param method name of the method logging a warning.
+ * @param warning the warning to log, or {@code null} if none.
+ */
+ private void log(final String method, final LogRecord warning) {
if (warning != null) {
warning.setLoggerName(Modules.SQL);
warning.setSourceClassName(getClass().getName());
- warning.setSourceMethodName("fetchCRS");
+ warning.setSourceMethodName(method);
database.listeners.warning(warning);
}
- return crs;
}
/**
@@ -435,8 +457,7 @@ public class InfoStatements implements Localized,
AutoCloseable {
private DataStoreContentException invalidSRID(final short message, final
Object complement, final int srid,
final NoSuchAuthorityCodeException suppressed)
{
- final DataStoreContentException e = new DataStoreContentException(
- Resources.forLocale(getLocale()).getString(message,
complement, srid));
+ final var e = new
DataStoreContentException(Resources.forLocale(getLocale()).getString(message,
complement, srid));
if (suppressed != null) {
e.addSuppressed(suppressed);
}
@@ -445,8 +466,11 @@ 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.
*
- * @param crs the CRS for which to find a SRID, or {@code null}.
+ * @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.
* @throws Exception if an SQL error, parsing error or other error
occurred.
*/
@@ -460,21 +484,26 @@ public class InfoStatements implements Localized,
AutoCloseable {
return srid;
}
}
- final Set<SimpleImmutableEntry<String,String>> done = new HashSet<>();
- Iterator<IdentifiedObject> alternatives = null;
- IdentifiedObject candidate = crs;
+ /*
+ * Search in the database. In the `done` map, keys are
"authority:code" pairs that we
+ * already tried and values tell whether we can use that pair if we
need to add new CRS.
+ */
Exception error = null;
- for (;;) {
+ boolean tryWithGivenCRS = true;
+ final var sridFounInUse = new HashSet<Integer>();
+ final var done = new
LinkedHashMap<SimpleImmutableEntry<String,Object>, Boolean>();
+ for (Iterator<IdentifiedObject> it =
Set.<IdentifiedObject>of(crs).iterator(); it.hasNext();) {
+ final IdentifiedObject candidate = it.next();
/*
* First, iterate over the identifiers declared in the CRS object.
* If we cannot find an identifier that we can map to a SRID, then
this loop may be
- * executed more times with CRS from EPSG database that are equal,
ignore axis order.
+ * executed more times with CRS from EPSG database that are equal,
ignoring axis order.
*/
for (final Identifier id : candidate.getIdentifiers()) {
final String authority = id.getCodeSpace();
if (authority == null) continue;
final String code = id.getCode();
- if (!done.add(new SimpleImmutableEntry<>(authority, code))) {
+ if (done.putIfAbsent(new SimpleImmutableEntry<>(authority,
code), Boolean.FALSE) != null) {
continue; // Skip
"authority:code" that we already tried.
}
final int codeValue;
@@ -485,6 +514,10 @@ public class InfoStatements implements Localized,
AutoCloseable {
else error.addSuppressed(e);
continue; // Ignore codes that
are not integers.
}
+ final var key = new
SimpleImmutableEntry<String,Object>(authority, codeValue);
+ if (done.putIfAbsent(key, codeValue > 0) != null) {
+ continue;
+ }
/*
* Found an "authority:code" pair that we did not tested
before.
* Get the WKT and verifies if the CRS is approximately equal.
@@ -496,10 +529,10 @@ public class InfoStatements implements Localized,
AutoCloseable {
sridFromCRS.setInt(2, codeValue);
try (ResultSet result = sridFromCRS.executeQuery()) {
while (result.next()) {
- try {
+ final int srid = result.getInt(1);
+ if (sridFounInUse.add(srid)) try {
final Object parsed = parseDefinition(result, 2);
if (Utilities.equalsApproximately(parsed, crs)) {
- final int srid = result.getInt(1);
synchronized (database.cacheOfSRID) {
database.cacheOfSRID.put(crs, srid);
}
@@ -509,6 +542,7 @@ public class InfoStatements implements Localized,
AutoCloseable {
if (error == null) error = e;
else error.addSuppressed(e);
}
+ done.put(key, Boolean.FALSE); // Declare this
"authority:code" pair as not available.
}
}
}
@@ -517,16 +551,247 @@ public class InfoStatements implements Localized,
AutoCloseable {
* It may be because the CRS has no identifier at all. Search for
* possible identifiers in the EPSG database, then try them.
*/
- if (alternatives == null) {
+ if (tryWithGivenCRS) {
+ tryWithGivenCRS = false;
final IdentifiedObjectFinder finder =
IdentifiedObjects.newFinder(Constants.EPSG);
finder.setIgnoringAxes(true);
- alternatives = finder.find(crs).iterator();
+ it = finder.find(crs).iterator();
+ }
+ }
+ /*
+ * At this point, we found no CRS definition in the current
`SPATIAL_REF_SYS` table.
+ * 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) {
+ String fallback = null;
+ int fallbackCode = 0;
+ for (final Map.Entry<SimpleImmutableEntry<String,Object>, Boolean>
entry : done.entrySet()) {
+ if (entry.getValue()) {
+ final SimpleImmutableEntry<String,Object> identifier =
entry.getKey();
+ final int code = (Integer) identifier.getValue(); // Safe
cast when `entry.getValue()` is true.
+ final String authority = identifier.getKey();
+ if (Constants.EPSG.equalsIgnoreCase(authority)) {
+ return addCRS(authority, code, crs, sridFounInUse);
+ }
+ if (fallback == null) {
+ fallback = authority;
+ fallbackCode = code;
+ }
+ }
+ }
+ if (fallback != null) {
+ return addCRS(fallback, fallbackCode, crs, sridFounInUse);
+ }
+ // In the current version, we don't encode a CRS without an
"authority:code" pair.
+ }
+ final Locale locale = getLocale();
+ throw new
DataStoreReferencingException(Resources.forLocale(locale).getString(
+ Resources.Keys.CanNotFindSRID_1,
IdentifiedObjects.getDisplayName(crs, locale)), error);
+ }
+
+ /**
+ * Adds a new entry in the {@code SPATIAL_REF_SYS} table for the given
<abbr>CRS</abbr>.
+ * The {@code authority} and {@code code} arguments are the values to
store in the {@code "AUTH_NAME"} and
+ * {@code "AUTH_SRID"} columns respectively. A common usage is to prefer
EPSG codes, but this is not mandatory.
+ *
+ * @param authority authority providing the CRS definition.
+ * @param code authority code.
+ * @param crs the coordinate reference system to encode.
+ * @param sridFounInUse SRIDs which have been found in use, for avoiding
SRID that would be certain to fail.
+ * This is only a hint. A SRID not in this set is
not a guarantee that it is available.
+ * @return SRID of the CRS added by this method.
+ * @throws DataStoreReferencingException if the given CRS cannot be
encoded in at least one supported format.
+ * @throws SQLException if an error occurred while executing the SQL
statement.
+ */
+ private int addCRS(final String authority, final int code, final
CoordinateReferenceSystem crs,
+ final Set<Integer> sridFounInUse) throws Exception
+ {
+ final SpatialSchema schema = database.getSpatialSchema().orElseThrow();
+ final var sql = new SQLBuilder(database).append(SQLBuilder.INSERT);
+ database.appendSpatialSchema(sql, schema.crsTable);
+ sql.append(" (").append(schema.crsIdentifierColumn)
+ .append(", ").append(schema.crsAuthorityNameColumn)
+ .append(", ").append(schema.crsAuthorityCodeColumn);
+ /*
+ * Append a variable number of columns for the CRS definitions in
various formats.
+ * We add columns only for the formats that we can use. At least one
of them must
+ * be filled, otherwise we cannot add the row.
+ */
+ @SuppressWarnings("LocalVariableHidesMemberVariable")
+ final var wktFormat = wktFormat();
+ final var crsEncodings = database.crsEncodings;
+ final var warnings = new Warnings[crsEncodings.size()];
+ final var definitions = new String[warnings.length + 2]; // +2
columns for name and description.
+ int numDefinitions = 0, numWarnings = 0;
+ for (final CRSEncoding encoding : crsEncodings) {
+ final String def;
+ switch (encoding) {
+ default: continue; // Skip unknown formats (none at
this time).
+ case WKT1: def = wktFormat.format(crs); break;
+ case WKT2: {
+ try {
+ wktFormat.setConvention(Convention.WKT2);
+ def = wktFormat.format(crs);
+ } finally {
+ wktFormat.setConvention(Convention.WKT1_COMMON_UNITS);
+ }
+ break;
+ }
+ }
+ Warnings warning = wktFormat.getWarnings();
+ if (warning != null) {
+ warnings[numWarnings++] = warning;
+ } else {
+ definitions[numDefinitions++] = def;
+ sql.append(",
").append(schema.crsDefinitionColumn.get(encoding));
+ }
+ }
+ /*
+ * If we have not been able to format the CRS in any encoding, throw
an exception with the first warning.
+ * We take the first one because the `CRSEncodng` enumeration is
ordered with most complete formats first,
+ * so if we fail with the first encoding the next ones should be worst.
+ */
+ if (numDefinitions == 0) {
+ throw new
DataStoreReferencingException(warnings[0].toString(getLocale()));
+ }
+ for (int i=0; i<numWarnings; i++) {
+ log("addCRS", new LogRecord(Level.WARNING,
warnings[i].toString(getLocale())));
+ }
+ /*
+ * Optional columns for CRS name and description.
+ * The following loop is executed exactly twice.
+ */
+ boolean description = false;
+ do {
+ final String column = description ? schema.crsDescriptionColumn :
schema.crsNameColumn;
+ if (column != null) {
+ String name = description ?
IdentifiedObjects.getDisplayName(crs, getLocale())
+ : IdentifiedObjects.getName(crs,
null);
+ if (name != null) {
+ definitions[numDefinitions++] = name;
+ sql.append(", ").append(column);
+ }
+ }
+ } while ((description = !description) == true);
+ /*
+ * Complete the SQL statement with the parameters that we will need to
provide.
+ * In addition to the definition, there is 3 columns for (SRID,
AUTHORITY, CODE)
+ * columns, and one more column if we also store the CRS name.
+ */
+ String separator = ") VALUES (";
+ for (int i = numDefinitions + 3; --i >= 0;) {
+ sql.append(separator).append('?');
+ separator = ", ";
+ }
+ int srid = code;
+ try (PreparedStatement stmt =
connection.prepareStatement(sql.append(')').toString())) {
+ stmt.setString(2, authority);
+ stmt.setInt(3, code);
+ for (int i=0; i<numDefinitions; i++) {
+ stmt.setString(4+i, definitions[i]);
+ }
+ /*
+ * Execute the statement first by trying to use a SRID of the same
value as the authority code.
+ * We do that because a common common practice is to use EPSG code
as SRID values. If the SRID
+ * is not available (i.e., if we get an integrity violation), we
will try another SRID value.
+ */
+ final var failures = new ArrayList<Exception>();
+ if (!sridFounInUse.contains(srid)) try {
+ stmt.setInt(1, srid);
+ stmt.executeUpdate();
+ return srid;
+ } catch (SQLException e) {
+ filterConstraintViolation(e);
+ /*
+ * SQL state category 23: integrity constraint violation.
Maybe the new CRS has been added concurrently.
+ * Or maybe the CRS for that SRID would have been suitable but
`findSRID(…)` didn't saw it, for example
+ * because the authority name has different spelling or
different lower/upper case.
+ */
+ try {
+ final CoordinateReferenceSystem candidate = fetchCRS(srid);
+ if (Utilities.equalsIgnoreMetadata(crs, candidate)) {
+ return srid;
+ }
+ } catch (Exception f) {
+ failures.add(f);
+ }
+ failures.add(e);
+ }
+ /*
+ * Search for an available SRID, then try again. The loop should
be executed only once,
+ * unless the database content changed concurrently. In the latter
case we retry until
+ * we got a free SRID.
+ */
+ try {
+ srid = 0;
+ while (srid < (srid = findFreeSRID(schema,
sql.clear().append(SQLBuilder.SELECT)))) {
+ try {
+ stmt.setInt(1, srid);
+ stmt.executeUpdate();
+ return srid;
+ } catch (SQLException e) {
+ filterConstraintViolation(e);
+ failures.add(e);
+ }
+ }
+ } catch (Exception e) {
+ failures.add(e);
+ }
+ /*
+ * If an unexpected error occurred, rethrow the last error and add
all previous errors
+ * as suppressed exceptions, in reverse order. We chose the last
exception because, in
+ * case of integrity violation, it is the record which was
supposed to be okay.
+ */
+ int i = failures.size();
+ final Exception e = failures.get(--i);
+ while (--i >= 0) e.addSuppressed(failures.get(i));
+ throw e;
+ }
+ }
+
+ /**
+ * Rethrows the given exception if the SQL state is not category 23:
integrity constraint violation.
+ * If the exception is a integrity constraint violation, do nothing.
+ *
+ * @param e the exception to filter.
+ * @throws SQLException if the given exception is not of category 23.
+ */
+ private static void filterConstraintViolation(final SQLException e) throws
SQLException {
+ final String state = e.getSQLState();
+ if (state == null || !state.equals("23505")) throw e;
+ }
+
+ /**
+ * Searches a free SRID for a new CRS definition.
+ *
+ * @param schema value of {@link Database#getSpatialSchema()}.
+ * @param sql a preexisting builder initialized with the {@code
"SELECT "} string.
+ * @return an available SRID guaranteed to be greater than zero.
+ * @throws SQLException if an error occurred while searching for a free
SRID.
+ */
+ private int findFreeSRID(final SpatialSchema schema, final SQLBuilder sql)
throws SQLException {
+
appendFrom(sql.append("MAX(").append(schema.crsIdentifierColumn).append(')'),
schema.crsTable);
+
sql.append(schema.crsIdentifierColumn).append('<').append(LOW_SRID_RANGE);
+ try (Statement stmt = connection.createStatement()) {
+ for (boolean high = false;;) { //
Loop will be executed 1 or 2 times.
+ try (ResultSet result = stmt.executeQuery(sql.toString())) {
+ if (result.next()) {
+ final int srid = result.getInt(1) + 1; //
Next value after the highest one.
+ if (high) {
+ return Math.max(srid, HIGH_SRID_RANGE);
+ } else if (srid < LOW_SRID_RANGE) {
+ return Math.max(srid, 1);
+ }
+ }
+ }
+ if (high) {
+ return 1;
+ }
+ high = true;
+ sql.removeWhereClause();
}
- if (!alternatives.hasNext()) break;
- candidate = alternatives.next();
}
- throw new DataStoreReferencingException(Resources.format(
- Resources.Keys.CanNotFindSRID_1,
IdentifiedObjects.getDisplayName(crs, null)), error);
}
/**
@@ -539,24 +804,32 @@ public class InfoStatements implements Localized,
AutoCloseable {
*/
private Object parseDefinition(final ResultSet result, int column) throws
SQLException, ParseException {
for (CRSEncoding encoding : database.crsEncodings) {
- final String wkt = result.getString(column++);
- if (wkt == null || wkt.isBlank()) {
+ final String def = result.getString(column++);
+ // Note: Geopackage stores "undefined" instead of no value.
+ if (def == null || def.isBlank() ||
def.equalsIgnoreCase("undefined")) {
continue;
}
switch (encoding) {
- default: {
- if (wktReader == null) {
- wktReader = new WKTFormat();
- wktReader.setConvention(Convention.WKT1_COMMON_UNITS);
- }
- return wktReader.parseObject(wkt);
- }
+ default: return wktFormat().parseObject(def);
// JSON encoding may be added in a future version.
}
}
return null;
}
+ /**
+ * Returns the object to use for parsing or formatting Well-Known Text of
<abbr>CRS</abbr>.
+ * The parser/formatter is created when first needed.
+ */
+ private WKTFormat wktFormat() {
+ if (wktFormat == null) {
+ wktFormat = new WKTFormat();
+ wktFormat.setIndentation(WKTFormat.SINGLE_LINE);
+ wktFormat.setConvention(Convention.WKT1_COMMON_UNITS);
+ }
+ return wktFormat;
+ }
+
/**
* Closes all prepared statements. This method does <strong>not</strong>
close the connection.
*
diff --git
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SpatialSchema.java
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SpatialSchema.java
index e405545841..cd2b2c3204 100644
---
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SpatialSchema.java
+++
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SpatialSchema.java
@@ -40,10 +40,10 @@ public enum SpatialSchema {
* except for table names, for the case (Geopackage uses lower case) and
for the addition of a
* {@code geometry_type_name} column.
*/
- GEOPACKAGE("gpkg_spatial_ref_sys", "srs_id", "organization",
"organization_coordsys_id",
+ GEOPACKAGE("gpkg_spatial_ref_sys", "srs_name", "srs_id", "organization",
"organization_coordsys_id",
Map.of(CRSEncoding.WKT1, "definition",
CRSEncoding.WKT2, "definition_12_063"),
-
+ "description",
"gpkg_geometry_columns", "table_catalog", "table_schema",
"table_name",
"column_name", "geometry_type_name",
GeometryTypeEncoding.TEXTUAL),
@@ -64,8 +64,8 @@ public enum SpatialSchema {
* In Geopackage, this table is named {@code "gpkg_spatial_ref_sys"} but
otherwise has identical content
* except for the case (Geopackage uses lower case).
*/
- SQL_MM("ST_SPATIAL_REFERENCE_SYSTEMS", "SRS_ID", "ORGANIZATION",
"ORGANIZATION_COORDSYS_ID",
- Map.of(CRSEncoding.WKT1, "DEFINITION"),
+ SQL_MM("ST_SPATIAL_REFERENCE_SYSTEMS", "SRS_NAME", "SRS_ID",
"ORGANIZATION", "ORGANIZATION_COORDSYS_ID",
+ Map.of(CRSEncoding.WKT1, "DEFINITION"), "DESCRIPTION",
"ST_GEOMETRY_COLUMNS", "TABLE_CATALOG", "TABLE_SCHEMA",
"TABLE_NAME", "COLUMN_NAME", null, null),
/**
@@ -82,7 +82,7 @@ public enum SpatialSchema {
* SRTEXT CHARACTER VARYING(2048))
* }
*/
- SIMPLE_FEATURE("SPATIAL_REF_SYS", "SRID", "AUTH_NAME", "AUTH_SRID",
Map.of(CRSEncoding.WKT1, "SRTEXT"),
+ SIMPLE_FEATURE("SPATIAL_REF_SYS", null, "SRID", "AUTH_NAME", "AUTH_SRID",
Map.of(CRSEncoding.WKT1, "SRTEXT"), null,
"GEOMETRY_COLUMNS", "F_TABLE_CATALOG", "F_TABLE_SCHEMA",
"F_TABLE_NAME", "F_GEOMETRY_COLUMN",
"GEOMETRY_TYPE", GeometryTypeEncoding.NUMERIC);
@@ -92,6 +92,12 @@ public enum SpatialSchema {
*/
final String crsTable;
+ /**
+ * Name of the column for CRS name, or {@code null} if none.
+ * Example: {@code "SRS_NAME"}.
+ */
+ final String crsNameColumn;
+
/**
* Name of the column for CRS identifiers.
* Example: {@code "SRID"}, {@code "SRS_ID"}.
@@ -118,6 +124,11 @@ public enum SpatialSchema {
*/
final Map<CRSEncoding, String> crsDefinitionColumn;
+ /**
+ * Name of the column for the CRS description, or {@code null} if none.
+ */
+ final String crsDescriptionColumn;
+
/**
* Name of the table enumerating the geometry columns.
*/
@@ -162,10 +173,12 @@ public enum SpatialSchema {
* Creates a new enumeration value.
*
* @param crsTable name of the table for Spatial Reference
System definitions.
+ * @param crsNameColumn name of the column for CRS names, or
{@code null} if none.
* @param crsIdentifierColumn name of the column for CRS identifiers.
* @param crsAuthorityNameColumn name of the column for CRS authority
names.
* @param crsAuthorityCodeColumn name of the column for CRS authority
codes.
* @param crsDefinitionColumn name of the column for CRS definitions
in <abbr>WKT</abbr> format.
+ * @param crsDescriptionColumn name of the column for the CRS
description, or {@code null} if none.
* @param geometryColumns name of the table enumerating the
geometry columns.
* @param geomCatalogColumn name of the column where the catalog of
each geometry column is stored.
* @param geomSchemaColumn name of the column where the schema of
each geometry column is stored.
@@ -174,17 +187,20 @@ public enum SpatialSchema {
* @param geomTypeColumn name of the column where the type of
each geometry column is stored, or null if none.
* @param typeEncoding how geometry types are encoded in the
{@link #geomTypeColumn}.
*/
- private SpatialSchema(String crsTable, String crsIdentifierColumn, String
crsAuthorityNameColumn,
- String crsAuthorityCodeColumn,
Map<CRSEncoding,String> crsDefinitionColumn,
+ private SpatialSchema(String crsTable, String crsNameColumn, String
crsIdentifierColumn,
+ String crsAuthorityNameColumn, String
crsAuthorityCodeColumn,
+ Map<CRSEncoding,String> crsDefinitionColumn, String
crsDescriptionColumn,
String geometryColumns, String geomCatalogColumn,
String geomSchemaColumn,
String geomTableColumn, String geomColNameColumn,
String geomTypeColumn,
GeometryTypeEncoding typeEncoding)
{
this.crsTable = crsTable;
+ this.crsNameColumn = crsNameColumn;
this.crsIdentifierColumn = crsIdentifierColumn;
this.crsAuthorityNameColumn = crsAuthorityNameColumn;
this.crsAuthorityCodeColumn = crsAuthorityCodeColumn;
this.crsDefinitionColumn = crsDefinitionColumn;
+ this.crsDescriptionColumn = crsDescriptionColumn;
this.geometryColumns = geometryColumns;
this.geomCatalogColumn = geomCatalogColumn;
this.geomSchemaColumn = geomSchemaColumn;
diff --git
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/ExtentEstimator.java
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/ExtentEstimator.java
index 97367f75a6..d9680f2620 100644
---
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/ExtentEstimator.java
+++
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/ExtentEstimator.java
@@ -120,7 +120,7 @@ final class ExtentEstimator {
private void query(final Statement statement) throws SQLException {
for (final Column column : columns) {
if (column.getGeometryType().isPresent()) {
- database.appendFunctionCall(builder.append(SQLBuilder.SELECT),
"ST_EstimatedExtent");
+
database.appendSpatialSchema(builder.append(SQLBuilder.SELECT),
"ST_EstimatedExtent");
builder.append('(');
if (table.schema != null) {
builder.appendValue(table.schema).append(", ");
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
new file mode 100644
index 0000000000..da2e2822fe
--- /dev/null
+++
b/endorsed/src/org.apache.sis.storage.sql/test/org/apache/sis/storage/sql/feature/InfoStatementsTest.java
@@ -0,0 +1,152 @@
+/*
+ * 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.storage.sql.feature;
+
+import java.util.Map;
+import java.util.List;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.lang.reflect.Field;
+import org.opengis.referencing.crs.CoordinateReferenceSystem;
+import org.apache.sis.referencing.crs.DefaultGeographicCRS;
+import org.apache.sis.metadata.sql.privy.Dialect;
+import org.apache.sis.geometry.wrapper.Geometries;
+import org.apache.sis.setup.GeometryLibrary;
+import org.apache.sis.storage.DataStoreReferencingException;
+import org.apache.sis.storage.event.StoreListeners;
+
+// Test dependencies
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.TestInstance;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.AfterAll;
+import org.junit.jupiter.api.parallel.Execution;
+import org.junit.jupiter.api.parallel.ExecutionMode;
+import static org.junit.jupiter.api.Assertions.*;
+import static org.apache.sis.test.Assertions.assertMessageContains;
+import org.apache.sis.test.TestCase;
+import org.apache.sis.storage.DataStoreMock;
+import org.apache.sis.metadata.sql.TestDatabase;
+import org.apache.sis.referencing.ImmutableIdentifier;
+import org.apache.sis.referencing.cs.HardCodedCS;
+import org.apache.sis.referencing.crs.HardCodedCRS;
+import org.apache.sis.referencing.datum.HardCodedDatum;
+
+
+/**
+ * Tests {@link InfoStatements}.
+ *
+ * @author Martin Desruisseaux (Geomatys)
+ */
+@Execution(ExecutionMode.SAME_THREAD)
+@TestInstance(TestInstance.Lifecycle.PER_CLASS)
+public final class InfoStatementsTest extends TestCase {
+ /**
+ * A in-memory database on Derby.
+ */
+ private final TestDatabase test;
+
+ /**
+ * SQL connection used for the tests.
+ */
+ private Connection connection;
+
+ /**
+ * The database to test.
+ */
+ private Database<?> database;
+
+ /**
+ * Creates a new test case.
+ *
+ * @throws SQLException if an error occurred while creating a temporary
in-memory database.
+ */
+ public InfoStatementsTest() throws SQLException {
+ test = TestDatabase.create("InfoStatements");
+ }
+
+ /**
+ * Creates some tables needed by the tests, then creates a {@link
Database} instance needed for the tests.
+ *
+ * @throws Exception if an error occurred while creating the tables.
+ */
+ @BeforeAll
+ public void initialize() throws Exception {
+ test.executeSQL(List.of("CREATE TABLE SPATIAL_REF_SYS (" +
+ "SRID INTEGER NOT NULL PRIMARY KEY, " +
+ "AUTH_NAME VARCHAR(100), " +
+ "AUTH_SRID INTEGER, " +
+ "SRTEXT VARCHAR(2000));"));
+
+ connection = test.source.getConnection();
+ database = new Database<>(test.source, connection.getMetaData(),
Dialect.DERBY,
+ Geometries.factory(GeometryLibrary.JAVA2D),
+ new StoreListeners(null, new
DataStoreMock("Unused")));
+ /*
+ * The `spatialSchema` is private, so we need to use reflection for
setting its value.
+ * Normally that field would be set by `Database.analyze(…)`, bur we
want to avoid that
+ * complexity for more isolated tests.
+ */
+ Field field = Database.class.getDeclaredField("spatialSchema");
+ field.setAccessible(true);
+ field.set(database, SpatialSchema.SIMPLE_FEATURE);
+ assertTrue(database.crsEncodings.add(CRSEncoding.WKT1));
+ }
+
+ /**
+ * Tests {@link InfoStatements#findSRID(CoordinateReferenceSystem)}.
+ *
+ * @throws Exception if a SQL, WKT or other error occurred.
+ */
+ @Test
+ public void testFindSRID() throws Exception {
+ try (InfoStatements info = new InfoStatements(database, connection)) {
+ database.allowAddCRS = false;
+ 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));
+
+ // CRS with the same code (intentional clash with EPSG:4326).
+ final CoordinateReferenceSystem clash = new DefaultGeographicCRS(
+ Map.of(CoordinateReferenceSystem.NAME_KEY, "Sphere",
+ 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));
+ }
+ }
+
+ /**
+ * Closes the temporary in-memory database.
+ *
+ * @throws SQLException if an error occurred while closing the database.
+ */
+ @AfterAll
+ public void close() throws SQLException {
+ try (test) {
+ if (connection != null) {
+ connection.close();
+ connection = null;
+ }
+ }
+ }
+}
