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 da1cc1c2df747d3db371611bbf9fc29bb41f3774
Merge: 28032e87b4 996e1106a9
Author: Martin Desruisseaux <martin.desruisse...@geomatys.com>
AuthorDate: Tue Feb 28 20:22:37 2023 +0100
Merge remote-tracking branch 'origin/fix/sql-temporal' into geoapi-4.0.
The pull request has been modified for applying the mapping specified by
JDBC 4.2.
The specialized classes are kept as fallbacks when JDBC 4.2 is not well
supported.
.../apache/sis/internal/metadata/sql/Dialect.java | 31 ++-
.../java/org/apache/sis/test/sql/TestDatabase.java | 24 ++-
.../apache/sis/internal/sql/feature/Database.java | 31 ++-
.../sis/internal/sql/feature/ValueGetter.java | 139 +++++++++---
.../sis/internal/sql/feature/package-info.java | 2 +-
.../apache/sis/internal/sql/postgis/Postgres.java | 9 +-
.../sis/internal/sql/postgis/package-info.java | 2 +-
.../sql/feature/TemporalValueGetterTest.java | 238 +++++++++++++++++++++
.../org/apache/sis/storage/sql/SQLStoreTest.java | 104 +++------
.../apache/sis/storage/sql/TestOnAllDatabases.java | 99 +++++++++
.../org/apache/sis/test/suite/SQLTestSuite.java | 1 +
11 files changed, 538 insertions(+), 142 deletions(-)
diff --cc
core/sis-metadata/src/main/java/org/apache/sis/internal/metadata/sql/Dialect.java
index e529787701,e529787701..cefb01a032
---
a/core/sis-metadata/src/main/java/org/apache/sis/internal/metadata/sql/Dialect.java
+++
b/core/sis-metadata/src/main/java/org/apache/sis/internal/metadata/sql/Dialect.java
@@@ -27,7 -27,7 +27,7 @@@ import org.apache.sis.util.CharSequence
*
* @author Martin Desruisseaux (Geomatys)
* @author Johann Sorel (Geomatys)
-- * @version 1.1
++ * @version 1.4
* @since 0.7
*/
public enum Dialect {
@@@ -36,37 -36,37 +36,40 @@@
*
* @see DatabaseMetaData#supportsANSI92EntryLevelSQL()
*/
-- ANSI(null, false, true),
++ ANSI(null, false, true, true),
/**
* The database uses Derby syntax. This is ANSI, with some constraints
that PostgreSQL does not have
* (for example column with {@code UNIQUE} constraint must explicitly be
specified as {@code NOT NULL}).
++ * Furthermore, conversions to {@link java.time} objects are not
supported.
++ *
++ * <a
href="https://issues.apache.org/jira/browse/DERBY-6445";>DERBY-6445</a>
*/
-- DERBY("derby", false, true),
++ DERBY("derby", false, true, false),
/**
* The database uses HSQL syntax. This is ANSI, but does not allow {@code
INSERT} statements inserting many lines.
* It also have a {@code SHUTDOWN} command which is specific to HSQLDB.
*/
-- HSQL("hsqldb", false, true),
++ HSQL("hsqldb", false, true, true),
/**
* The database uses PostgreSQL syntax. This is ANSI, but provided an a
separated
* enumeration value because it allows a few additional commands like
{@code VACUUM}.
*/
-- POSTGRESQL("postgresql", true, true),
++ POSTGRESQL("postgresql", true, true, true),
/**
* The database uses Oracle syntax. This is ANSI, but without {@code
"AS"} keyword.
*/
-- ORACLE("oracle", false, true),
++ ORACLE("oracle", false, true, true),
/**
* The database uses SQLite syntax. This is ANSI, but with several
limitations.
*
* @see <a href="https://www.sqlite.org/omitted.html";>SQL Features That
SQLite Does Not Implement</a>
*/
-- SQLITE("sqlite", false, false);
++ SQLITE("sqlite", false, false, false);
/**
* The protocol in JDBC URL, or {@code null} if unknown.
@@@ -95,16 -95,16 +98,28 @@@
*/
public final boolean supportsAlterTableWithAddConstraint;
++ /**
++ * Whether the JDBC driver supports conversions from objects to {@code
java.time} API.
++ * The JDBC 4.2 specification provides a mapping from {@link
java.sql.Types} to temporal objects.
++ * The specification suggests that {@link
java.sql.ResultSet#getObject(int, Class)} should accept
++ * those temporal types in the {@link Class} argument, but not all
drivers support that.
++ *
++ * @see <a
href="https://jcp.org/aboutJava/communityprocess/maintenance/jsr221/JDBC4.2MR-January2014.pdf";>JDBC
Maintenance Release 4.2</a>
++ */
++ public final boolean supportsJavaTime;
++
/**
* Creates a new enumeration value for a SQL dialect for the given
protocol.
*/
private Dialect(final String protocol,
final boolean supportsTableInheritance,
-- final boolean supportsAlterTableWithAddConstraint)
++ final boolean supportsAlterTableWithAddConstraint,
++ final boolean supportsJavaTime)
{
this.protocol = protocol;
this.supportsTableInheritance = supportsTableInheritance;
this.supportsAlterTableWithAddConstraint =
supportsAlterTableWithAddConstraint;
++ this.supportsJavaTime = supportsJavaTime;
}
/**
diff --cc
core/sis-metadata/src/test/java/org/apache/sis/test/sql/TestDatabase.java
index 57092f54ad,57092f54ad..a76ca252f6
--- a/core/sis-metadata/src/test/java/org/apache/sis/test/sql/TestDatabase.java
+++ b/core/sis-metadata/src/test/java/org/apache/sis/test/sql/TestDatabase.java
@@@ -26,6 -26,6 +26,7 @@@ import java.sql.SQLDataException
import org.postgresql.PGProperty;
import org.postgresql.ds.PGSimpleDataSource;
import org.apache.derby.jdbc.EmbeddedDataSource;
++import org.apache.sis.internal.metadata.sql.Dialect;
import org.apache.sis.internal.metadata.sql.LocalDataSource;
import org.apache.sis.internal.metadata.sql.ScriptRunner;
import org.apache.sis.test.TestCase;
@@@ -62,7 -62,7 +63,7 @@@ import static org.junit.Assume.assumeTr
*
* @author Martin Desruisseaux (Geomatys)
* @author Alexis Manin (Geomatys)
-- * @version 1.2
++ * @version 1.4
* @since 0.7
*/
public class TestDatabase implements AutoCloseable {
@@@ -88,11 -88,11 +89,18 @@@
*/
public final DataSource source;
++ /**
++ * The SQL flavor used by the database, or {@code ANSI} if unspecified.
++ * May be used for identifying the database software.
++ */
++ public final Dialect dialect;
++
/**
* Creates a new test database for the given data source.
*/
-- private TestDatabase(final DataSource source) {
-- this.source = source;
++ private TestDatabase(final DataSource source, final Dialect dialect) {
++ this.source = source;
++ this.dialect = dialect;
}
/**
@@@ -107,13 -107,13 +115,13 @@@
*/
public static TestDatabase create(final String name) throws SQLException {
if (TEST_DATABASE != null) {
-- return new TestDatabase(TEST_DATABASE);
++ return new TestDatabase(TEST_DATABASE, Dialect.ANSI);
}
final EmbeddedDataSource ds = new EmbeddedDataSource();
ds.setDatabaseName("memory:" + name);
ds.setDataSourceName("Apache SIS test database");
ds.setCreateDatabase("create");
-- return new TestDatabase(ds) {
++ return new TestDatabase(ds, Dialect.DERBY) {
@Override public void close() throws SQLException {
final EmbeddedDataSource ds = (EmbeddedDataSource) source;
ds.setCreateDatabase("no");
@@@ -157,7 -157,7 +165,7 @@@
ds = simple;
pool = null;
}
-- return new TestDatabase(ds) {
++ return new TestDatabase(ds, Dialect.HSQL) {
@Override public void close() throws SQLException {
try (Connection c = ds.getConnection(); Statement s =
c.createStatement()) {
s.execute("SHUTDOWN");
@@@ -186,7 -186,7 +194,7 @@@
final String url = "jdbc:h2:mem:" + name + ";DB_CLOSE_DELAY=-1";
final org.h2.jdbcx.JdbcDataSource ds = new
org.h2.jdbcx.JdbcDataSource();
ds.setURL(url);
-- return new TestDatabase(ds) {
++ return new TestDatabase(ds, Dialect.ANSI) {
@Override public void close() throws SQLException {
try (Connection c = ds.getConnection(); Statement s =
c.createStatement()) {
s.execute("SHUTDOWN");
@@@ -250,7 -250,7 +258,7 @@@
assumeFalse("This test needs a PostgreSQL database named \"" +
NAME + "\".", "3D000".equals(state));
throw e;
}
-- return new TestDatabase(ds) {
++ return new TestDatabase(ds, Dialect.POSTGRESQL) {
@Override public void close() throws SQLException {
final PGSimpleDataSource ds = (PGSimpleDataSource) source;
try (Connection c = ds.getConnection()) {
diff --cc
storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Database.java
index 176c573e54,a643941ef3..2201d587e2
---
a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Database.java
+++
b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Database.java
@@@ -96,7 -96,7 +96,7 @@@ import org.apache.sis.util.Debug
* @author Johann Sorel (Geomatys)
* @author Martin Desruisseaux (Geomatys)
* @author Alexis Manin (Geomatys)
-- * @version 1.2
++ * @version 1.4
* @since 1.1
*/
public class Database<G> extends Syntax {
@@@ -175,6 -175,6 +175,14 @@@
*/
final boolean supportsCatalogs, supportsSchemas;
++ /**
++ * Whether the JDBC driver supports conversions from objects to {@code
java.time} API.
++ * The JDBC 4.2 specification provides a mapping from {@link
java.sql.Types} to temporal objects.
++ * The specification suggests that {@link
java.sql.ResultSet#getObject(int, Class)} should accept
++ * those temporal types in the {@link Class} argument, but not all
drivers support that.
++ */
++ private final boolean supportsJavaTime;
++
/**
* The converter from filters/expressions to the {@code WHERE} part of
SQL statement.
* This is initialized when first needed, then kept unmodified for the
database lifetime.
@@@ -214,11 -214,11 +222,12 @@@
*
* @param source provider of (pooled) connections to the database.
* @param metadata metadata about the database.
++ * @param dialect additional information not provided by {@code
metadata}.
* @param geomLibrary the factory to use for creating geometric objects.
* @param listeners where to send warnings.
* @throws SQLException if an error occurred while reading database
metadata.
*/
-- protected Database(final DataSource source, final DatabaseMetaData
metadata,
++ protected Database(final DataSource source, final DatabaseMetaData
metadata, final Dialect dialect,
final Geometries<G> geomLibrary, final StoreListeners
listeners)
throws SQLException
{
@@@ -246,6 -246,6 +255,7 @@@
this.tablesByNames = new FeatureNaming<>();
supportsCatalogs = metadata.supportsCatalogsInDataManipulation();
supportsSchemas = metadata.supportsSchemasInDataManipulation();
++ supportsJavaTime = dialect.supportsJavaTime;
}
/**
@@@ -270,11 -270,11 +280,12 @@@
{
final DatabaseMetaData metadata = connection.getMetaData();
final Geometries<?> g = Geometries.implementation(geomLibrary);
++ final Dialect dialect = Dialect.guess(metadata);
final Database<?> db;
-- switch (Dialect.guess(metadata)) {
-- case POSTGRESQL: db = new Postgres<>(source, connection,
metadata, g, listeners); break;
++ switch (dialect) {
++ case POSTGRESQL: db = new Postgres<>(source, connection,
metadata, dialect, g, listeners); break;
default: {
-- db = new Database<>(source, metadata, g, listeners);
++ db = new Database<>(source, metadata, dialect, g, listeners);
break;
}
}
@@@ -565,11 -565,11 +576,11 @@@
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR: return
ValueGetter.AsString.INSTANCE;
-- case Types.DATE: return
ValueGetter.AsDate.INSTANCE;
-- case Types.TIME: return
ValueGetter.AsLocalTime.INSTANCE;
- case Types.TIMESTAMP: return
ValueGetter.AsInstant.INSTANCE;
- case Types.TIMESTAMP: return
ValueGetter.AsLocalDateTime.INSTANCE;
-- case Types.TIME_WITH_TIMEZONE: return
ValueGetter.AsOffsetTime.INSTANCE;
- case Types.TIMESTAMP_WITH_TIMEZONE: return
ValueGetter.AsOffsetDateTime.INSTANCE;
- case Types.TIMESTAMP_WITH_TIMEZONE: return
ValueGetter.AsInstant.INSTANCE;
++ case Types.DATE: return supportsJavaTime ?
ValueGetter.LOCAL_DATE : ValueGetter.AsLocalDate.INSTANCE;
++ case Types.TIME: return supportsJavaTime ?
ValueGetter.LOCAL_TIME : ValueGetter.AsLocalTime.INSTANCE;
++ case Types.TIMESTAMP: return supportsJavaTime ?
ValueGetter.LOCAL_DATE_TIME : ValueGetter.AsLocalDateTime.INSTANCE;
++ case Types.TIME_WITH_TIMEZONE: return supportsJavaTime ?
ValueGetter.OFFSET_TIME : ValueGetter.AsOffsetTime.INSTANCE;
++ case Types.TIMESTAMP_WITH_TIMEZONE: return supportsJavaTime ?
ValueGetter.OFFSET_DATE_TIME : ValueGetter.AsOffsetDateTime.INSTANCE;
case Types.BLOB: return
ValueGetter.AsBytes.INSTANCE;
case Types.OTHER:
case Types.JAVA_OBJECT: return getDefaultMapping();
diff --cc
storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/ValueGetter.java
index d50d2a0552,f7e03a28a8..1676b22bf3
---
a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/ValueGetter.java
+++
b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/ValueGetter.java
@@@ -16,7 -16,8 +16,6 @@@
*/
package org.apache.sis.internal.sql.feature;
- import java.util.Calendar;
-import java.time.LocalDate;
-import java.time.LocalDateTime;
import java.util.Collection;
import java.sql.Array;
import java.sql.ResultSet;
@@@ -24,8 -25,8 +23,9 @@@ import java.sql.SQLException
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
--import java.time.Instant;
import java.time.LocalTime;
++import java.time.LocalDate;
++import java.time.LocalDateTime;
import java.time.OffsetDateTime;
import java.time.OffsetTime;
import java.time.ZoneOffset;
@@@ -33,6 -34,6 +33,7 @@@ import java.math.BigDecimal
import org.apache.sis.math.Vector;
import org.apache.sis.util.Numbers;
import org.apache.sis.util.ArgumentChecks;
++import org.apache.sis.internal.util.StandardDateFormat;
import org.apache.sis.internal.util.UnmodifiableArrayList;
@@@ -52,10 -53,10 +53,45 @@@
*
* @author Alexis Manin (Geomatys)
* @author Martin Desruisseaux (Geomatys)
-- * @version 1.2
++ * @version 1.4
* @since 1.1
*/
--public abstract class ValueGetter<T> {
++public class ValueGetter<T> {
++ /**
++ * A getter of {@link LocalDate} values from the current row of a {@link
ResultSet}.
++ * According JDBC 4.2 specification, {@link java.sql.Types#DATE} shall be
mapped to
++ * this class.
++ */
++ static final ValueGetter<LocalDate> LOCAL_DATE = new
ValueGetter<>(LocalDate.class);
++
++ /**
++ * A getter of {@link LocalTime} values from the current row of a {@link
ResultSet}.
++ * According JDBC 4.2 specification, {@link java.sql.Types#TIME} without
timezone
++ * shall be mapped to this class.
++ */
++ static final ValueGetter<LocalTime> LOCAL_TIME = new
ValueGetter<>(LocalTime.class);
++
++ /**
++ * A getter of {@link LocalDateTime} values from the current row of a
{@link ResultSet}.
++ * According JDBC 4.2 specification, {@link java.sql.Types#TIMESTAMP}
without timezone
++ * shall be mapped to this class.
++ */
++ static final ValueGetter<LocalDateTime> LOCAL_DATE_TIME = new
ValueGetter<>(LocalDateTime.class);
++
++ /**
++ * A getter of {@link OffsetTime} values from the current row of a {@link
ResultSet}.
++ * According JDBC 4.2 specification, {@link
java.sql.Types#TIME_WITH_TIMEZONE} shall
++ * be mapped to this class.
++ */
++ static final ValueGetter<OffsetTime> OFFSET_TIME = new
ValueGetter<>(OffsetTime.class);
++
++ /**
++ * A getter of {@link OffsetDateTime} values from the current row of a
{@link ResultSet}.
++ * According JDBC 4.2 specification, {@link
java.sql.Types#TIMESTAMP_WITH_TIMEZONE}
++ * shall be mapped to this class.
++ */
++ static final ValueGetter<OffsetDateTime> OFFSET_DATE_TIME = new
ValueGetter<>(OffsetDateTime.class);
++
/**
* The type of Java objects fetched from the column.
* The value shall not be a primitive type; the wrapper class shall be
used instead.
@@@ -78,10 -79,10 +114,14 @@@
* The given result set must have its cursor position on the line to read.
* This method does not modify the cursor position.
*
-- * <div class="note"><b>Note:</b>
++ * <h4>Usage note</h4>
* The {@code stmts} is the same reference for all features created by a
new {@link FeatureIterator} instance,
* including its dependencies. But the {@code source} will vary depending
on whether we are iterating over the
-- * main feature or one of its dependencies.</div>
++ * main feature or one of its dependencies.
++ *
++ * <h4>Default implementation</h4>
++ * The default implementation delegates to {@link
ResultSet#getObject(int, Class)}.
++ * This is okay if the database is known to support conversions to {@link
#valueType}.
*
* @param stmts prepared statements for fetching CRS from SRID,
or {@code null} if none.
* @param source the result set from which to get the value.
@@@ -89,7 -90,7 +129,9 @@@
* @return value in the given column. May be {@code null}.
* @throws Exception if an error occurred. May be an SQL error, a WKB
parsing error, <i>etc.</i>
*/
-- public abstract T getValue(InfoStatements stmts, ResultSet source, int
columnIndex) throws Exception;
++ public T getValue(InfoStatements stmts, ResultSet source, int
columnIndex) throws Exception {
++ return source.getObject(columnIndex, valueType);
++ }
/**
* A getter of {@link Object} values from the current row of a {@link
ResultSet}.
@@@ -282,19 -283,19 +324,22 @@@
}
/**
-- * A getter of {@link Date} values from the current row of a {@link
ResultSet}.
- * This getter delegates to {@link ResultSet#getDate(int)} and returns
that value with no change.
- * This getter delegates to {@link ResultSet#getDate(int)} then convert
that value to {@link LocalDate java time API}.
++ * A getter of {@link LocalDate} values from the current row of a {@link
ResultSet}.
++ * This getter delegates to {@link ResultSet#getDate(int)} then converts
that value
++ * by a call to {@link Date#toLocalDate()}.
+ *
- * @todo Delegate to {@link ResultSet#getDate(int, Calendar)} instead.
++ * <p>This is fallback used when {@link ResultSet#getObject(int, Class)}
does not support the
++ * conversion from {@link java.sql.Types#DATE} to Java time API as
specified by JDBC 4.2.</p>
*/
- static final class AsDate extends ValueGetter<Date> {
- static final class AsDate extends ValueGetter<LocalDate> {
++ static final class AsLocalDate extends ValueGetter<LocalDate> {
/** The unique instance of this accessor. */
-- public static final AsDate INSTANCE = new AsDate();
- private AsDate() {super(Date.class);}
- private AsDate() {super(LocalDate.class);}
++ public static final AsLocalDate INSTANCE = new AsLocalDate();
++ private AsLocalDate() {super(LocalDate.class);}
/** Fetches the value from the specified column in the given result
set. */
- @Override public Date getValue(InfoStatements stmts, ResultSet
source, int columnIndex) throws SQLException {
- return source.getDate(columnIndex);
+ @Override public LocalDate getValue(InfoStatements stmts, ResultSet
source, int columnIndex) throws SQLException {
- final Date rawValue = source.getDate(columnIndex);
- if (rawValue == null) return null;
- else return rawValue.toLocalDate();
++ final Date date = source.getDate(columnIndex);
++ return (date != null) ? date.toLocalDate() : null;
}
}
@@@ -303,7 -304,10 +348,8 @@@
* This getter delegates to {@link ResultSet#getTime(int)}, then converts
the object
* by a call to {@link Time#toLocalTime()}.
*
- * @todo Delegate to {@link ResultSet#getTime(int, Calendar)} instead.
- * @todo java.sql.Time does not provide sub-second precision in its local
time conversion.
- * However, some databases support it. A better conversion function would
be:
- * {@code LocalTime.ofNanoOfDay(Math.multiplyExact(sqlTime.getTime(),
1_000_000L));}
- * But it is less "standard". We should test across multiple database
engines to verify the related impact.
++ * <p>This is fallback used when {@link ResultSet#getObject(int, Class)}
does not support the
++ * conversion from {@link java.sql.Types#TIME} to Java time API as
specified by JDBC 4.2.</p>
*/
static final class AsLocalTime extends ValueGetter<LocalTime> {
/** The unique instance of this accessor. */
@@@ -313,26 -317,24 +359,33 @@@
/** Fetches the value from the specified column in the given result
set. */
@Override public LocalTime getValue(InfoStatements stmts, ResultSet
source, int columnIndex) throws SQLException {
final Time time = source.getTime(columnIndex);
-- return (time != null) ? time.toLocalTime() : null;
++ if (time == null) return null;
++ /*
++ * `Time.toLocalTime()` does not use sub-second precision.
++ * However, some databases provide millisecond precision.
++ */
++ final int milli = (int) (time.getTime() %
StandardDateFormat.MILLIS_PER_SECOND);
++ return time.toLocalTime().withNano(milli *
StandardDateFormat.NANOS_PER_MILLISECOND);
}
}
/**
-- * A getter of {@link Instant} values from the current row of a {@link
ResultSet}.
-- * This getter delegates to {@link ResultSet#getTimestamp(int)}, then
converts the
- * object by a call to {@link Timestamp#toInstant()}.
- * object by a call to {@link Timestamp#toLocalDateTime()}.
++ * A getter of {@link LocalDateTime} values from the current row of a
{@link ResultSet}.
++ * This getter delegates to {@link ResultSet#getTimestamp(int)}, then
converts the object
++ * by a call to {@link Timestamp#toLocalDateTime()}.
+ *
- * @todo Delegate to {@link ResultSet#getTimestamp(int, Calendar)}
instead.
++ * <p>This is fallback used when {@link ResultSet#getObject(int, Class)}
does not support the
++ * conversion from {@link java.sql.Types#TIMESTAMP} to Java time API as
specified by JDBC 4.2.</p>
*/
- static final class AsInstant extends ValueGetter<Instant> {
+ static final class AsLocalDateTime extends ValueGetter<LocalDateTime> {
/** The unique instance of this accessor. */
- public static final AsInstant INSTANCE = new AsInstant();
- private AsInstant() {super(Instant.class);}
+ public static final AsLocalDateTime INSTANCE = new AsLocalDateTime();
+ private AsLocalDateTime() {super(LocalDateTime.class);}
/** Fetches the value from the specified column in the given result
set. */
- @Override public Instant getValue(InfoStatements stmts, ResultSet
source, int columnIndex) throws SQLException {
+ @Override public LocalDateTime getValue(InfoStatements stmts,
ResultSet source, int columnIndex) throws SQLException {
final Timestamp time = source.getTimestamp(columnIndex);
- return (time != null) ? time.toInstant() : null;
+ return (time != null) ? time.toLocalDateTime() : null;
}
}
@@@ -341,28 -343,40 +394,44 @@@
* This getter delegates to {@link ResultSet#getTimestamp(int)}, converts
the object by a
* call to {@link Timestamp#toInstant()} then apply the time zone offset.
*
- * @todo Delegate to {@link ResultSet#getTimestamp(int, Calendar)}
instead.
- * Note: This converter is only meant for "Timestamp with time zone" data
types, that describe a fixed/absolute point in time.
- * Semantically, an SQL "timestamp with time zone" should better match
{@link OffsetDateTime}.
- * However, none of the tested JDBC drivers (PostgreSQL and HSQLDB)
return the zone offset given at insertion.
- * They both return the timestamp UTC, meaning that in any cases, the
original zone offset information in lost.
- * Therefore, it is more straightforward to return an UTC Timestamp, i.e
{@link Instant} anyway.
++ * <p>This is fallback used when {@link ResultSet#getObject(int, Class)}
does not support conversion
++ * from {@link java.sql.Types#TIMESTAMP_WITH_TIMEZONE} to Java time API
as specified by JDBC 4.2.</p>
+ *
- * WARNING: timeStamps returned by HSQLDB looks flawed. To bypass the
problem, we add a special case to check if the
- * driver directly returns a java.time data type. If not, then we
fallback to {@link Timestamp SQL Timestamps}.
++ * <h4>Implementation note</h4>
++ * PostgreSQL always return the time in the local time zone, while HSQLDB
and H2 return the time as
++ * inserted in the database but ignoring the timezone offset. The latter
implies that we don't know
++ * how convert a HSQLDB and H2 to local or UTC timezone. Current
implementation assumes PostgreSQL
++ * behavior, which is the only one that we can map to {@link
OffsetDateTime}.
++ * Specifying a {@link java.util.Calendar} seems to have no effect.
*/
- static final class AsInstant extends ValueGetter<Instant> {
+ static final class AsOffsetDateTime extends ValueGetter<OffsetDateTime> {
/** The unique instance of this accessor. */
- public static final AsInstant INSTANCE = new AsInstant();
- private AsInstant() {super(Instant.class);}
+ public static final AsOffsetDateTime INSTANCE = new
AsOffsetDateTime();
+ private AsOffsetDateTime() {super(OffsetDateTime.class);}
/** Fetches the value from the specified column in the given result
set. */
- @Override public Instant getValue(InfoStatements stmts, ResultSet
source, int columnIndex) throws SQLException {
- final Object rawValue = source.getObject(columnIndex);
- if (rawValue == null) return null;
- else if (rawValue instanceof Instant) return (Instant) rawValue;
- else if (rawValue instanceof OffsetDateTime) return
(((OffsetDateTime) rawValue).toInstant());
- else return source.getTimestamp(columnIndex).toInstant();
+ @Override public OffsetDateTime getValue(InfoStatements stmts,
ResultSet source, int columnIndex) throws SQLException {
+ final Timestamp time = source.getTimestamp(columnIndex);
+ if (time == null) return null;
- final int offsetMinute = time.getTimezoneOffset();
++ final int offsetMinute = -time.getTimezoneOffset();
+ return
time.toInstant().atOffset(ZoneOffset.ofHoursMinutes(offsetMinute / 60,
offsetMinute % 60));
}
}
/**
-- * A getter of {@link OffsetDateTime} values from the current row of a
{@link ResultSet}.
-- * This getter delegates to {@link ResultSet#getTime(int)}, converts the
object by a call
-- * to {@link Time#toLocalTime()} then apply the time zone offset.
++ * A getter of {@link OffsetTime} values from the current row of a {@link
ResultSet}.
++ * This getter delegates to {@link ResultSet#getTime(int)}, converts the
object by a
++ * call to {@link Time#toLocalTime()} then apply the time zone offset.
+ *
- * @todo As for {@link AsLocalTime}, sub-second precision is lost here.
++ * <p>This is fallback used when {@link ResultSet#getObject(int, Class)}
does not support conversion
++ * from {@link java.sql.Types#TIME_WITH_TIMEZONE} to Java time API as
specified by JDBC 4.2.</p>
*
- * @todo Delegate to {@link ResultSet#getTime(int, Calendar)} instead.
- * Note: Timezone used on value insertion is lost here, we always return
an UTC time.
- * The main reason is that multiple database (Postgres, HSQLDB) engines
convert times to UTC on save,
- * so it is impossible to retrieve the information later.
++ * <h4>Implementation note</h4>
++ * PostgreSQL always return the time in the local time zone, while HSQLDB
and H2 return the time as
++ * inserted in the database but ignoring the timezone offset. The latter
implies that we don't know
++ * how convert a HSQLDB and H2 to local or UTC timezone. Current
implementation assumes PostgreSQL
++ * behavior, which is the only one that we can map to {@link OffsetTime}.
++ * Specifying a {@link java.util.Calendar} seems to have no effect.
*/
static final class AsOffsetTime extends ValueGetter<OffsetTime> {
/** The unique instance of this accessor. */
@@@ -371,10 -385,15 +440,12 @@@
/** Fetches the value from the specified column in the given result
set. */
@Override public OffsetTime getValue(InfoStatements stmts, ResultSet
source, int columnIndex) throws SQLException {
- final Object rawValue = source.getObject(columnIndex);
- if (rawValue == null) return null;
- // Workaround/shortcut for HSQLDB and H2
- else if (rawValue instanceof OffsetTime) return (OffsetTime)
rawValue;
- else {
- final Time time = source.getTime(columnIndex);
- final long timeNanoseconds =
Math.multiplyExact(time.getTime(), 1_000_000L);
- return
LocalTime.ofNanoOfDay(timeNanoseconds).atOffset(ZoneOffset.UTC);
- }
+ final Time time = source.getTime(columnIndex);
+ if (time == null) return null;
- final int offsetMinute = time.getTimezoneOffset();
- return
time.toLocalTime().atOffset(ZoneOffset.ofHoursMinutes(offsetMinute / 60,
offsetMinute % 60));
++ final int offsetMinute = -time.getTimezoneOffset();
++ final int milli = (int) (time.getTime() %
StandardDateFormat.MILLIS_PER_SECOND);
++ return time.toLocalTime().withNano(milli *
StandardDateFormat.NANOS_PER_MILLISECOND)
++ .atOffset(ZoneOffset.ofHoursMinutes(offsetMinute / 60,
offsetMinute % 60));
}
}
diff --cc
storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/package-info.java
index 3dfb9f6293,3dfb9f6293..f9bed5e7b0
---
a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/package-info.java
+++
b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/package-info.java
@@@ -31,7 -31,7 +31,7 @@@
* @author Johann Sorel (Geomatys)
* @author Martin Desruisseaux (Geomatys)
* @author Alexis Manin (Geomatys)
-- * @version 1.2
++ * @version 1.4
* @since 1.0
*/
package org.apache.sis.internal.sql.feature;
diff --cc
storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/postgis/Postgres.java
index a15b380991,a15b380991..386b4d2256
---
a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/postgis/Postgres.java
+++
b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/postgis/Postgres.java
@@@ -37,6 -37,6 +37,7 @@@ import org.apache.sis.internal.sql.feat
import org.apache.sis.internal.sql.feature.ValueGetter;
import org.apache.sis.internal.sql.feature.Resources;
import org.apache.sis.internal.sql.feature.SelectionClauseWriter;
++import org.apache.sis.internal.metadata.sql.Dialect;
import org.apache.sis.storage.event.StoreListeners;
import org.apache.sis.util.Version;
@@@ -48,7 -48,7 +49,7 @@@
*
* @author Alexis Manin (Geomatys)
* @author Martin Desruisseaux (Geomatys)
-- * @version 1.2
++ * @version 1.4
* @since 1.1
*/
public final class Postgres<G> extends Database<G> {
@@@ -67,14 -67,14 +68,16 @@@
* @param source provider of (pooled) connections to the database.
* @param connection the connection to the database. Should be
considered as read-only.
* @param metadata metadata about the database for which a session
is created.
++ * @param dialect additional information not provided by {@code
metadata}.
* @param geomLibrary the factory to use for creating geometric objects.
* @param listeners where to send warnings.
* @throws SQLException if an error occurred while reading database
metadata.
*/
public Postgres(final DataSource source, final Connection connection,
final DatabaseMetaData metadata,
-- final Geometries<G> geomLibrary, final StoreListeners
listeners) throws SQLException
++ final Dialect dialect, final Geometries<G> geomLibrary,
final StoreListeners listeners)
++ throws SQLException
{
-- super(source, metadata, geomLibrary, listeners);
++ super(source, metadata, dialect, geomLibrary, listeners);
Version version = null;
try (Statement st = connection.createStatement();
ResultSet result = st.executeQuery("SELECT
public.PostGIS_version();"))
diff --cc
storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/postgis/package-info.java
index 263c959238,263c959238..27cbaa85b3
---
a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/postgis/package-info.java
+++
b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/postgis/package-info.java
@@@ -24,7 -24,7 +24,7 @@@
* @author Alexis Manin (Geomatys)
* @author Martin Desruisseaux (Geomatys)
* @author Johann Sorel (Geomatys)
-- * @version 1.2
++ * @version 1.4
* @since 1.1
*/
package org.apache.sis.internal.sql.postgis;
diff --cc
storage/sis-sqlstore/src/test/java/org/apache/sis/internal/sql/feature/TemporalValueGetterTest.java
index 0000000000,ff6f65eff2..d90873b74c
mode 000000,100644..100644
---
a/storage/sis-sqlstore/src/test/java/org/apache/sis/internal/sql/feature/TemporalValueGetterTest.java
+++
b/storage/sis-sqlstore/src/test/java/org/apache/sis/internal/sql/feature/TemporalValueGetterTest.java
@@@ -1,0 -1,277 +1,238 @@@
++/*
++ * 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.internal.sql.feature;
+
+ import java.sql.Connection;
+ import java.sql.ResultSet;
-import java.sql.SQLException;
+ import java.sql.Statement;
-import java.time.Instant;
+ import java.time.LocalDate;
+ import java.time.LocalDateTime;
+ import java.time.LocalTime;
++import java.time.OffsetDateTime;
+ import java.time.OffsetTime;
-import java.util.ArrayList;
-import java.util.HashMap;
-import java.util.List;
-import java.util.Map;
-import java.util.Objects;
++import java.time.ZoneOffset;
+ import java.util.function.BiPredicate;
-import java.util.stream.Collectors;
-import org.apache.sis.test.TestCase;
++import org.apache.sis.internal.metadata.sql.Dialect;
++import org.apache.sis.storage.sql.TestOnAllDatabases;
+ import org.apache.sis.test.sql.TestDatabase;
-import org.junit.AfterClass;
-import org.junit.Assert;
-import org.junit.Assume;
-import org.junit.AssumptionViolatedException;
-import org.junit.BeforeClass;
-import org.junit.Test;
+
-import static java.time.ZoneOffset.UTC;
-import static java.time.ZoneOffset.ofHours;
-import static org.junit.Assert.assertEquals;
-import static org.junit.Assert.assertFalse;
-import static org.junit.Assert.assertNull;
++import static org.junit.Assert.*;
++
+
+ /**
- * Check access and conversion to database datetime values.
++ * Tests accesses and conversions to database date and time values.
++ *
++ * @author Alexis Manin (Geomatys)
++ * @author Martin Desruisseaux (Geomatys)
++ * @version 1.4
++ * @since 1.4
+ */
-public class TemporalValueGetterTest extends TestCase {
++public final class TemporalValueGetterTest extends TestOnAllDatabases {
++ /**
++ * Whether the JDBC driver supports conversions from objects to {@code
java.time} API.
++ */
++ private boolean supportsJavaTime;
+
- private static final String DB_TITLE = "Test Temporal values";
- private static final String TEST_SCHEMA = "SIS_TEST_TEMPORAL";
- private static final String DEFAULT_DRIVER = "DEFAULT";
- public static final String PG_DRIVER = "POSTGRESQL";
- private static Map<String, TestDatabase> DATABASES;
++ /**
++ * Whether {@code ResultSet.getTime(int)} uses an unknown time zone.
++ * In such case, the fallbacks are unreliable and their tests should
++ * be disabled.
++ */
++ private boolean unreliableFallback;
+
- @BeforeClass
- public static void initTestDatabases() throws Exception {
- if (DATABASES != null) throw new IllegalStateException("Test database
should be null before initialization");
- DATABASES = new HashMap<>();
- DATABASES.put(DEFAULT_DRIVER, TestDatabase.create(DB_TITLE));
- DATABASES.put("HSQLDB", TestDatabase.createOnHSQLDB(DB_TITLE, true));
- DATABASES.put("H2", TestDatabase.createOnH2(DB_TITLE));
- try {
- final TestDatabase db =
TestDatabase.createOnPostgreSQL(TEST_SCHEMA, true);
- DATABASES.put(PG_DRIVER, db);
- } catch (AssumptionViolatedException e) {
- // Ok, environment does not contain a PostGreSQL test database
- out.println("[FINE] PostgreSQL database deactivated due to
assumption requirement not met: "+e.getMessage());
- }
++ /**
++ * Creates a new test.
++ */
++ public TemporalValueGetterTest() {
+ }
+
- @AfterClass
- public static void disposeTestDatabases() throws Exception {
- if (DATABASES != null) {
- List<Exception> closeErrors = DATABASES.values().stream()
- .map(db -> {
- try {
- db.close();
- return null;
- } catch (Exception e) {
- return e;
- }
- })
- .filter(Objects::nonNull)
- .collect(Collectors.toList());
- if (!closeErrors.isEmpty()) {
- final Exception base = closeErrors.get(0);
- for (int i = 1 ; i < closeErrors.size() ; i++) {
- base.addSuppressed(closeErrors.get(i));
++ /**
++ * Runs all tests on a single database software.
++ *
++ * @throws Exception if the execution of an SQL query or the conversion
of a value failed.
++ */
++ @Override
++ protected void test(final TestDatabase database, final boolean noschema)
throws Exception {
++ supportsJavaTime = database.dialect.supportsJavaTime;
++ try (Connection connection = database.source.getConnection()) {
++ if (!noschema) {
++ connection.setSchema(SCHEMA);
++ }
++ try (Statement stmt = connection.createStatement()) {
++ sqlDateToLocalDate(stmt);
++ sqlTimeToLocalTime(stmt);
++ sqlTimestampToLocalDateTime(stmt);
++ if (database.dialect != Dialect.DERBY) { // Derby does not
support TIMESTAMP WITH TIME ZONE.
++ unreliableFallback = (database.dialect !=
Dialect.POSTGRESQL);
++ sqlTimeWithTimeZoneToOffsetTime(stmt);
++ sqlTimestampWithTimeZoneToOffsetDateTime(stmt);
+ }
- throw base;
+ }
+ }
+ }
+
- @Test
- public void sqlDateToLocalDate() {
- testOnEachDatabase((driver, db) -> {
- execute(driver, db,
- "CREATE TABLE TEST_DATE (\"value\" DATE)",
- "INSERT INTO TEST_DATE (\"value\") VALUES ('2022-05-01'),
('2022-05-31'), (null)"
- );
-
- ValueGetter<LocalDate> getter = ValueGetter.AsDate.INSTANCE;
- execute(driver, db, "SELECT \"value\" FROM TEST_DATE", r -> {
- r.next();
- assertEquals(LocalDate.of(2022, 5, 1), getter.getValue(null,
r, 1));
- r.next();
- assertEquals(LocalDate.of(2022, 5, 31), getter.getValue(null,
r, 1));
- r.next();
- // Ensure no NPE occurs, i.e the converter is resilient to
null inputs
- assertNull(getter.getValue(null, r, 1));
- assertFalse("Defense against test change: only 3 values
should be present in the test table", r.next());
- });
- });
- }
-
- @Test
- public void sqlTimestampToLocalDateTime() {
- testOnEachDatabase((driver, db) -> {
- execute(driver, db,
- "CREATE TABLE TEST_TIMESTAMP_WITHOUT_TIMEZONE (\"value\"
TIMESTAMP)",
- "INSERT INTO TEST_TIMESTAMP_WITHOUT_TIMEZONE (\"value\")
VALUES ('2022-05-01 01:01:01'), ('2022-05-31 23:59:59.999'), (null)"
- );
-
- ValueGetter<LocalDateTime> getter =
ValueGetter.AsLocalDateTime.INSTANCE;
- execute(driver, db, "SELECT \"value\" FROM
TEST_TIMESTAMP_WITHOUT_TIMEZONE", r -> {
- r.next();
- assertEquals(LocalDateTime.of(2022, 5, 1, 1, 1, 1),
getter.getValue(null, r, 1));
- r.next();
- assertEquals(LocalDateTime.of(2022, 5, 31, 23, 59, 59,
999_000_000), getter.getValue(null, r, 1));
- r.next();
- // Ensure no NPE occurs, i.e the converter is resilient to
null inputs
- assertNull(getter.getValue(null, r, 1));
- assertFalse("Defense against test change: only 3 values
should be present in the test table", r.next());
- });
- });
- }
-
- @Test
- public void sqlTimestampWithTimeZoneToInstant() {
- testOnEachDatabase((driver, db) -> {
- Assume.assumeFalse("Derby does not support TIMESTAMP WITH TIME
ZONE data type", DEFAULT_DRIVER.equals(driver));
- execute(driver, db,
- "CREATE TABLE TEST_TIMESTAMP_WITH_TIMEZONE (\"value\"
TIMESTAMP WITH TIME ZONE)",
- "INSERT INTO TEST_TIMESTAMP_WITH_TIMEZONE (\"value\")
VALUES ('2022-05-01 01:01:01+01:00'), ('2022-05-31 23:59:59.999+02:00'), (null)"
- );
-
- ValueGetter<Instant> getter = ValueGetter.AsInstant.INSTANCE;
- execute(driver, db, "SELECT \"value\" FROM
TEST_TIMESTAMP_WITH_TIMEZONE", r -> {
- Instant[] expectedValues = {
- LocalDateTime.of(2022, 5, 1, 1, 1,
1).atOffset(ofHours(1)).toInstant(),
- LocalDateTime.of(2022, 5, 31, 23, 59, 59,
999_000_000).atOffset(ofHours(2)).toInstant(),
- null
- };
- assertContentEquals(expectedValues, r, 1, getter, "Timestamp
with time zone conversion");
- });
- });
- }
-
+ /**
- * Note: It does not test sub-second precision, although PostGreSQL,
HSQLDB and H2 support it.
- * The conversion from {@link java.sql.Timestamp} makes it difficult to
get back the information.
- * See {@link ValueGetter.AsLocalTime} todo section for details.
++ * Returns the value getter than can be used for the test.
++ * The list of available value getters depends on the database driver.
++ *
++ * @param <V> type of values to get.
++ * @param main the preferred value getter.
++ * @param fallback the fallback to use when the main getter is not
available.
++ * @return the value getters to use for the tests.
+ */
- @Test
- public void sqlTimeToLocalTime() {
- testOnEachDatabase(((driver, db) -> {
- execute(driver, db,
- "CREATE TABLE TEST_TIME (\"value\" TIME)",
- "INSERT INTO TEST_TIME (\"value\") VALUES ('08:01:01'),
('09:30:30'), (null)");
- ValueGetter<LocalTime> getter = ValueGetter.AsLocalTime.INSTANCE;
- execute(driver, db, "SELECT \"value\" FROM TEST_TIME", r -> {
- LocalTime[] expectedValues = {
- LocalTime.of(8, 1, 1),
- LocalTime.of(9, 30, 30),
- null
- };
- assertContentEquals(expectedValues, r, 1, getter, "Time
conversion");
- });
- }));
- }
-
- @Test
- public void sqlTimeWithTimeZoneToOffsetTime() {
- testOnEachDatabase(((driver, db) -> {
- Assume.assumeFalse("Derby does not support TIMESTAMP WITH TIME
ZONE data type", DEFAULT_DRIVER.equals(driver));
- execute(driver, db,
- "CREATE TABLE TEST_TIME_WITH_TIMEZONE (\"value\" TIME
WITH TIME ZONE)",
- "INSERT INTO TEST_TIME_WITH_TIMEZONE (\"value\") VALUES
('08:01:01+00:00'), ('09:30:30-01:00'), (null)");
- ValueGetter<OffsetTime> getter =
ValueGetter.AsOffsetTime.INSTANCE;
- execute(driver, db, "SELECT \"value\" FROM
TEST_TIME_WITH_TIMEZONE", r -> {
- OffsetTime[] expectedValues = {
- LocalTime.of(8, 1, 1).atOffset(UTC),
- LocalTime.of(10, 30, 30).atOffset(UTC),
- null
- };
-
- // Databases don't properly preserve insert time zone, so we
can only check if values represent the same
- // instant from the start of day.
- assertContentEquals(expectedValues, r, 1, getter,
OffsetTime::isEqual, "Time conversion");
- });
- }));
- }
-
- private <V> void assertContentEquals(V[] expected, ResultSet actual, int
columnIndex, ValueGetter<V> valueConverter, String title) throws Exception {
- assertContentEquals(expected, actual, columnIndex, valueConverter,
Objects::equals, title);
++ @SuppressWarnings({"unchecked","rawtypes"}) // Generic array creation.
++ private <V> ValueGetter<V>[] getters(final ValueGetter<V> main, final
ValueGetter<V> fallback) {
++ if (supportsJavaTime) {
++ if (unreliableFallback) {
++ return new ValueGetter[] {main};
++ } else {
++ return new ValueGetter[] {main, fallback};
++ }
++ } else {
++ return new ValueGetter[] {fallback};
++ }
+ }
+
+ /**
- * Equivalent of {@link Assert#assertArrayEquals(String, Object[],
Object[])}, except "actual values" are retrieved
- * by parsing an {@link ResultSet SQL query result}.
- *
- * @param expected Values that should be returned by the query+converter
combo.
- * @param actual Result of an SQL query to validate.
- * @param columnIndex Column to read values from in given result set.
- * @param valueConverter How to map each SQL result value to JAVA API.
Must not be null.
- * @param comparisonFunction How to compare an expected element with.
Must not be null.
- * @param title An error message title for assertion failures.
- * @param <V> Type of values to compare.
++ * Tests conversion from SQL {@code DATE} to {@link LocalDate}.
+ */
- private <V> void assertContentEquals(V[] expected, ResultSet actual, int
columnIndex, ValueGetter<V> valueConverter, BiPredicate<V, V>
comparisonFunction, String title) throws Exception {
- final List<V> values = new ArrayList<>(expected.length);
- while (values.size() < expected.length && actual.next()) {
- values.add(valueConverter.getValue(null, actual, columnIndex));
- }
-
- assertEquals(title + ": Not enough values in query result",
expected.length, values.size());
- assertFalse(title + ": Too many values in query result",
actual.next());
-
- for (int i = 0 ; i < expected.length ; i++) {
- final V expectedValue = expected[i];
- final V actualValue = values.get(i);
-
- if (expectedValue == actualValue) continue;
-
- if (expectedValue == null || actualValue == null ||
!comparisonFunction.test(expectedValue, actualValue)) {
- throw new AssertionError(String.format("%s: error at index %d
-> Expected: %s, but was: %s", title, i, expectedValue, actualValue));
++ private void sqlDateToLocalDate(final Statement stmt) throws Exception {
++ assertFalse(stmt.execute("CREATE TABLE TEST_DATE (\"value\" DATE)"));
++ assertFalse(stmt.execute("INSERT INTO TEST_DATE (\"value\") VALUES
('2022-05-01'), ('2022-05-31'), (null)"));
++ LocalDate[] expected = {
++ LocalDate.of(2022, 5, 1),
++ LocalDate.of(2022, 5, 31),
++ null // Ensure no NPE occurs, i.e the
converter is resilient to null inputs.
++ };
++ for (ValueGetter<LocalDate> getter : getters(ValueGetter.LOCAL_DATE,
ValueGetter.AsLocalDate.INSTANCE)) {
++ try (ResultSet r = stmt.executeQuery("SELECT \"value\" FROM
TEST_DATE")) {
++ assertContentEquals(expected, r, getter, null, "Date
conversion");
+ }
+ }
+ }
+
- private void testOnEachDatabase(DatabaseTester tester) {
- DATABASES.forEach((driver, db) -> {
- try {
- tester.test(driver, db);
- } catch (AssumptionViolatedException e) {
- // OK, user has deactivated test upon some conditions
- out.println("[FINE] Error ignored because of failed
assumption. Driver="+driver+", reason="+e.getMessage());
- } catch (AssertionError e) {
- throw new AssertionError("Test failed for driver: "+driver,
e);
- } catch (Exception e) {
- throw new RuntimeException("Error while testing driver:
"+driver, e);
++ /**
++ * Tests conversion from SQL {@code TIMESTAMP} to {@link LocalDateTime}.
++ */
++ private void sqlTimestampToLocalDateTime(final Statement stmt) throws
Exception {
++ assertFalse(stmt.execute("CREATE TABLE
TEST_TIMESTAMP_WITHOUT_TIMEZONE (\"value\" TIMESTAMP)"));
++ assertFalse(stmt.execute("INSERT INTO TEST_TIMESTAMP_WITHOUT_TIMEZONE
(\"value\") "
++ + "VALUES ('2022-05-01 01:01:01'), ('2022-05-31
23:59:59.999'), (null)"));
++ LocalDateTime[] expected = {
++ LocalDateTime.of(2022, 5, 1, 1, 1, 1),
++ LocalDateTime.of(2022, 5, 31, 23, 59, 59, 999_000_000),
++ null // Ensure no NPE occurs, i.e the
converter is resilient to null inputs.
++ };
++ for (ValueGetter<LocalDateTime> getter :
getters(ValueGetter.LOCAL_DATE_TIME, ValueGetter.AsLocalDateTime.INSTANCE)) {
++ try (ResultSet r = stmt.executeQuery("SELECT \"value\" FROM
TEST_TIMESTAMP_WITHOUT_TIMEZONE")) {
++ assertContentEquals(expected, r, getter, null, "Date and time
conversion");
+ }
- });
++ }
+ }
+
- @FunctionalInterface
- private interface DatabaseTester {
- void test(String driver, TestDatabase db) throws Exception;
++ /**
++ * Tests conversion from SQL {@code TIMESTAMP WITH TIME ZONE} to {@link
OffsetDateTime}.
++ */
++ private void sqlTimestampWithTimeZoneToOffsetDateTime(final Statement
stmt) throws Exception {
++ assertFalse(stmt.execute("CREATE TABLE TEST_TIMESTAMP_WITH_TIMEZONE
(\"value\" TIMESTAMP WITH TIME ZONE)"));
++ assertFalse(stmt.execute("INSERT INTO TEST_TIMESTAMP_WITH_TIMEZONE
(\"value\") "
++ + "VALUES ('2022-05-01 01:01:01+01:00'), ('2022-05-31
23:59:59.999+02:00'), (null)"));
++ OffsetDateTime[] expected = {
++ LocalDateTime.of(2022, 5, 1, 1, 1,
1).atOffset(ZoneOffset.ofHours(1)),
++ LocalDateTime.of(2022, 5, 31, 23, 59, 59,
999_000_000).atOffset(ZoneOffset.ofHours(2)),
++ null
++ };
++ for (ValueGetter<OffsetDateTime> getter :
getters(ValueGetter.OFFSET_DATE_TIME, ValueGetter.AsOffsetDateTime.INSTANCE)) {
++ try (ResultSet r = stmt.executeQuery("SELECT \"value\" FROM
TEST_TIMESTAMP_WITH_TIMEZONE")) {
++ /*
++ * Some databases do not properly preserve insert time zone,
so we can
++ * only check if values represent the same instant from the
start of day.
++ */
++ assertContentEquals(expected, r, getter,
OffsetDateTime::isEqual,
++ "Timestamp with time zone conversion");
++ }
++ }
+ }
+
- @FunctionalInterface
- private interface QueryAction {
- void accept(ResultSet queryResult) throws Exception;
++ /**
++ * Tests conversion from SQL {@code TIME} to {@link LocalTime}.
++ */
++ private void sqlTimeToLocalTime(final Statement stmt) throws Exception {
++ assertFalse(stmt.execute("CREATE TABLE TEST_TIME (\"value\" TIME)"));
++ assertFalse(stmt.execute("INSERT INTO TEST_TIME (\"value\") VALUES
('08:01:01'), ('09:30:30'), (null)"));
++ LocalTime[] expected = {
++ LocalTime.of(8, 1, 1),
++ LocalTime.of(9, 30, 30),
++ null
++ };
++ for (ValueGetter<LocalTime> getter : getters(ValueGetter.LOCAL_TIME,
ValueGetter.AsLocalTime.INSTANCE)) {
++ try (ResultSet r = stmt.executeQuery("SELECT \"value\" FROM
TEST_TIME")) {
++ assertContentEquals(expected, r, getter, null, "Time
conversion");
++ }
++ }
+ }
+
- private static void execute(String driver, TestDatabase db, String
firstQuery, String... moreQueries) throws SQLException {
- try (Connection c = db.source.getConnection()) {
- if (PG_DRIVER.equals(driver)) c.setSchema(TEST_SCHEMA);
- try (Statement s = c.createStatement()) {
- s.execute(firstQuery);
- for (String q : moreQueries) s.execute(q);
++ /**
++ * Tests conversion from SQL {@code TIME} to {@link LocalTime}.
++ */
++ private void sqlTimeWithTimeZoneToOffsetTime(final Statement stmt) throws
Exception {
++ assertFalse(stmt.execute("CREATE TABLE TEST_TIME_WITH_TIMEZONE
(\"value\" TIME WITH TIME ZONE)"));
++ assertFalse(stmt.execute("INSERT INTO TEST_TIME_WITH_TIMEZONE
(\"value\") "
++ + "VALUES ('08:01:01+00:00'), ('09:30:30-01:00'), (null)"));
++ OffsetTime[] expected = {
++ LocalTime.of(8, 1, 1).atOffset(ZoneOffset.UTC),
++ LocalTime.of(10, 30, 30).atOffset(ZoneOffset.UTC),
++ null
++ };
++ for (ValueGetter<OffsetTime> getter :
getters(ValueGetter.OFFSET_TIME, ValueGetter.AsOffsetTime.INSTANCE)) {
++ try (ResultSet r = stmt.executeQuery("SELECT \"value\" FROM
TEST_TIME_WITH_TIMEZONE")) {
++ /*
++ * Some databases do not properly preserve insert time zone,
so we can
++ * only check if values represent the same instant from the
start of day.
++ */
++ assertContentEquals(expected, r, getter, OffsetTime::isEqual,
"Time conversion");
+ }
+ }
+ }
+
- private static void execute(String driver, TestDatabase db, String
sqlQuery, QueryAction action) throws Exception {
- try (Connection c = db.source.getConnection()) {
- if (PG_DRIVER.equals(driver)) c.setSchema(TEST_SCHEMA);
- try (Statement s = c.createStatement();
- ResultSet r = s.executeQuery(sqlQuery)
- ) {
- action.accept(r);
++ /**
++ * Compares the new rows of the given result set against the expected
values.
++ *
++ * @param <V> type of values to compare.
++ * @param expected values that should be returned by the query after
conversion by the getter.
++ * @param actual result of an SQL query to validate.
++ * @param getter how to map each SQL result value to Java objects.
Must not be null.
++ * @param comparator how to compare values, or {@code null} for default
comparison.
++ * @param title an error message for assertion failures.
++ */
++ private static <V> void assertContentEquals(final V[] expected, final
ResultSet actual, final ValueGetter<V> getter,
++ final BiPredicate<V,V>
comparator, final String title) throws Exception
++ {
++ for (final V expectedValue : expected) {
++ assertTrue("Too few result rows", actual.next());
++ final V actualValue = getter.getValue(null, actual, 1);
++ if (comparator != null && expectedValue != null && actualValue !=
null) {
++ if (comparator.test(expectedValue, actualValue)) {
++ continue;
++ }
++ // Otherwise `assertEquals(…)` will format an error message.
+ }
++ assertEquals(title, expectedValue, actualValue);
+ }
++ assertFalse("Too many result rows", actual.next());
+ }
+ }
diff --cc
storage/sis-sqlstore/src/test/java/org/apache/sis/storage/sql/SQLStoreTest.java
index e23cdb0fa0,e23cdb0fa0..927979c27f
---
a/storage/sis-sqlstore/src/test/java/org/apache/sis/storage/sql/SQLStoreTest.java
+++
b/storage/sis-sqlstore/src/test/java/org/apache/sis/storage/sql/SQLStoreTest.java
@@@ -33,8 -33,8 +33,6 @@@ import org.apache.sis.internal.sql.feat
import org.apache.sis.internal.sql.feature.TableReference;
import org.apache.sis.test.sql.TestDatabase;
import org.apache.sis.test.TestUtilities;
--import org.apache.sis.test.TestCase;
--import org.junit.Test;
import static org.apache.sis.test.Assert.*;
@@@ -53,15 -53,15 +51,10 @@@ import org.opengis.filter.SortOrder
*
* @author Martin Desruisseaux (Geomatys)
* @author Alexis Manin (Geomatys)
-- * @version 1.2
++ * @version 1.4
* @since 1.1
*/
--public final class SQLStoreTest extends TestCase {
-- /**
-- * The schema where will be stored the features to test.
-- */
-- public static final String SCHEMA = "features";
--
++public final class SQLStoreTest extends TestOnAllDatabases {
/**
* Data used in the {@code Features.sql} test file.
*/
@@@ -113,87 -113,87 +106,44 @@@
FF = DefaultFilterFactory.forFeatures();
}
-- /**
-- * Tests on Derby.
-- *
-- * @throws Exception if an error occurred while testing the database.
-- */
-- @Test
-- public void testOnDerby() throws Exception {
-- test(TestDatabase.create("SQLStore"), true);
-- }
--
-- /**
-- * Tests on HSQLDB.
-- *
-- * @throws Exception if an error occurred while testing the database.
-- */
-- @Test
-- public void testOnHSQLDB() throws Exception {
-- test(TestDatabase.createOnHSQLDB("SQLStore", true), true);
-- }
--
-- /**
-- * Tests on H2.
-- *
-- * @throws Exception if an error occurred while testing the database.
-- */
-- @Test
-- public void testOnH2() throws Exception {
-- test(TestDatabase.createOnH2("SQLStore"), true);
-- }
--
-- /**
-- * Tests on PostgreSQL.
-- *
-- * @throws Exception if an error occurred while testing the database.
-- */
-- @Test
-- public void testOnPostgreSQL() throws Exception {
-- test(TestDatabase.createOnPostgreSQL(SCHEMA, true), false);
-- }
--
/**
* Runs all tests on a single database software. A temporary schema is
created at the beginning of this method
* and deleted after all tests finished. The schema is created and
populated by the {@code Features.sql} script.
*
-- * @param inMemory whether the test database is in memory. If {@code
true}, then the schema will be created
++ * @param noschema whether the test database is in memory. If {@code
true}, then the schema will be created
* and will be the only schema to exist (ignoring
system schema); i.e. we assume that there
* is no ambiguity if we do not specify the schema in
{@link SQLStore} constructor.
*/
-- private void test(final TestDatabase database, final boolean inMemory)
-- throws Exception
-- {
++ @Override
++ protected void test(final TestDatabase database, final boolean noschema)
throws Exception {
final String[] scripts = {
"CREATE SCHEMA " + SCHEMA + ';',
"file:Features.sql"
};
-- if (!inMemory) {
++ if (!noschema) {
scripts[0] = null; // Omit the "CREATE SCHEMA" statement if
the schema already exists.
}
-- try (TestDatabase tmp = database) { // TODO: omit
`tmp` with JDK16.
-- database.executeSQL(SQLStoreTest.class, scripts);
-- final StorageConnector connector = new
StorageConnector(database.source);
-- final ResourceDefinition table = ResourceDefinition.table(null,
inMemory ? null : SCHEMA, "Cities");
-- testTableQuery(connector, table);
-- /*
-- * Verify using SQL statements instead of tables.
-- */
-- verifyFetchCityTableAsQuery(connector);
-- verifyNestedSQLQuery(connector);
-- verifyLimitOffsetAndColumnSelectionFromQuery(connector);
-- verifyDistinctQuery(connector);
-- /*
-- * Test on the table again, but with cyclic associations enabled.
-- */
-- connector.setOption(SchemaModifier.OPTION, new SchemaModifier() {
-- @Override public boolean
isCyclicAssociationAllowed(TableReference dependency) {
-- return true;
-- }
-- });
-- isCyclicAssociationAllowed = true;
-- testTableQuery(connector, table);
-- }
++ database.executeSQL(SQLStoreTest.class, scripts);
++ final StorageConnector connector = new
StorageConnector(database.source);
++ final ResourceDefinition table = ResourceDefinition.table(null,
noschema ? null : SCHEMA, "Cities");
++ testTableQuery(connector, table);
++ /*
++ * Verify using SQL statements instead of tables.
++ */
++ verifyFetchCityTableAsQuery(connector);
++ verifyNestedSQLQuery(connector);
++ verifyLimitOffsetAndColumnSelectionFromQuery(connector);
++ verifyDistinctQuery(connector);
++ /*
++ * Test on the table again, but with cyclic associations enabled.
++ */
++ connector.setOption(SchemaModifier.OPTION, new SchemaModifier() {
++ @Override public boolean
isCyclicAssociationAllowed(TableReference dependency) {
++ return true;
++ }
++ });
++ isCyclicAssociationAllowed = true;
++ testTableQuery(connector, table);
}
/**
diff --cc
storage/sis-sqlstore/src/test/java/org/apache/sis/storage/sql/TestOnAllDatabases.java
index 0000000000,0000000000..883b8a2366
new file mode 100644
--- /dev/null
+++
b/storage/sis-sqlstore/src/test/java/org/apache/sis/storage/sql/TestOnAllDatabases.java
@@@ -1,0 -1,0 +1,99 @@@
++/*
++ * 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;
++
++import org.apache.sis.test.TestCase;
++import org.apache.sis.test.sql.TestDatabase;
++import org.junit.Test;
++
++
++/**
++ * Base class of tests to be executed on all supported databases.
++ *
++ * @author Martin Desruisseaux (Geomatys)
++ * @version 1.4
++ * @since 1.4
++ */
++public abstract class TestOnAllDatabases extends TestCase {
++ /**
++ * The schema where will be stored the features to test.
++ */
++ public static final String SCHEMA = "features";
++
++ /**
++ * Creates a new test case.
++ */
++ protected TestOnAllDatabases() {
++ }
++
++ /**
++ * Tests on Derby.
++ *
++ * @throws Exception if an error occurred while testing the database.
++ */
++ @Test
++ public void testOnDerby() throws Exception {
++ try (TestDatabase database = TestDatabase.create("SQLStore")) {
++ test(database, true);
++ }
++ }
++
++ /**
++ * Tests on HSQLDB.
++ *
++ * @throws Exception if an error occurred while testing the database.
++ */
++ @Test
++ public void testOnHSQLDB() throws Exception {
++ try (TestDatabase database = TestDatabase.createOnHSQLDB("SQLStore",
true)) {
++ test(database, true);
++ }
++ }
++
++ /**
++ * Tests on H2.
++ *
++ * @throws Exception if an error occurred while testing the database.
++ */
++ @Test
++ public void testOnH2() throws Exception {
++ try (TestDatabase database = TestDatabase.createOnH2("SQLStore")) {
++ test(database, true);
++ }
++ }
++
++ /**
++ * Tests on PostgreSQL.
++ *
++ * @throws Exception if an error occurred while testing the database.
++ */
++ @Test
++ public void testOnPostgreSQL() throws Exception {
++ try (TestDatabase database = TestDatabase.createOnPostgreSQL(SCHEMA,
true)) {
++ test(database, false);
++ }
++ }
++
++ /**
++ * Runs all tests on a single database software.
++ *
++ * @param database factory for creating a test database.
++ * @param noschema whether the test database is created without schema.
++ * @throws Exception if an error occurred while executing the test.
++ */
++ protected abstract void test(final TestDatabase database, final boolean
noschema) throws Exception;
++}
