This is an automated email from the ASF dual-hosted git repository.
desruisseaux pushed a commit to branch geoapi-4.0
in repository https://gitbox.apache.org/repos/asf/sis.git
The following commit(s) were added to refs/heads/geoapi-4.0 by this push:
new 84deb1e236 `SQLBuilder.appendValue(Object)` should format temporal
objects as SQL dates.
84deb1e236 is described below
commit 84deb1e2364893293829b3323ff59928245c3e0e
Author: Martin Desruisseaux <[email protected]>
AuthorDate: Wed Mar 12 14:20:16 2025 +0100
`SQLBuilder.appendValue(Object)` should format temporal objects as SQL
dates.
---
.../apache/sis/metadata/sql/privy/SQLBuilder.java | 43 +++++++++-
.../org/apache/sis/metadata/sql/privy/Syntax.java | 14 +++-
.../org/apache/sis/temporal/LenientDateFormat.java | 6 +-
.../sis/metadata/sql/privy/SQLBuilderTest.java | 97 ++++++++++++++++++++++
.../sis/metadata/sql/privy/SQLUtilitiesTest.java | 2 +-
5 files changed, 154 insertions(+), 8 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 505fbf68ff..cae7decee6 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
@@ -18,6 +18,11 @@ package org.apache.sis.metadata.sql.privy;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
+import java.time.LocalDate;
+import java.time.LocalTime;
+import java.time.ZoneOffset;
+import java.time.temporal.TemporalAccessor;
+import java.time.temporal.TemporalQueries;
import org.apache.sis.util.CharSequences;
@@ -57,7 +62,7 @@ public class SQLBuilder extends Syntax {
/**
* Creates a new {@code SQLBuilder} initialized from the given database
metadata.
*
- * @param metadata the database metadata.
+ * @param metadata the database metadata, or {@code null} if
unavailable.
* @param quoteSchema whether the schema name should be written between
quotes.
* @throws SQLException if an error occurred while fetching the database
metadata.
*/
@@ -241,6 +246,20 @@ public class SQLBuilder extends Syntax {
* Appends a value in a {@code SELECT} or {@code INSERT} statement.
* If the given value is a character string, then it is written between
quotes.
*
+ * <h4>Date and time</h4>
+ * The standard SQL date format for inserting or setting dates is {@code
'YYYY-MM-DD'}.
+ * This format is accepted by various SQL databases, including PostgreSQL
and MySQL.
+ * The time format is {@code 'HH:MM:SS'}, optionally followed by a time
zone offset
+ * in the {@code '+HH:MM} format. If the temporal object provides both a
date and a time,
+ * these components are separated by a space instead of the ISO 8601
{@code 'T'} character.
+ * Example of a date/time with time zone: {@code '2025-03-12
14:30:00+01:00'}.
+ *
+ * <h4>When to use</h4>
+ * {@link java.sql.PreparedStatement} should be used instead of this
method,
+ * for letting the <abbr>JDBC</abbr> driver performs appropriate
conversion.
+ * This method is sometime useful for building a {@code WHERE} clause,
+ * when the number and type of conditions are not fixed in advance.
+ *
* @param value the value to append, or {@code null}.
* @return this builder, for method call chaining.
*/
@@ -249,6 +268,28 @@ public class SQLBuilder extends Syntax {
buffer.append(value);
} else if (value instanceof Boolean) {
buffer.append((Boolean) value ? "TRUE" : "FALSE");
+ } else if (value instanceof TemporalAccessor) {
+ final var t = (TemporalAccessor) value;
+ final LocalDate date = t.query(TemporalQueries.localDate());
+ final LocalTime time = t.query(TemporalQueries.localTime());
+ if (time == null && date == null) {
+ return appendValue(value.toString());
+ }
+ buffer.append('\'');
+ if (date != null) {
+ buffer.append(date); // `toString()` defined as
"uuuu-MM-dd" ('u' is year).
+ if (time != null) {
+ buffer.append(' ');
+ }
+ }
+ if (time != null) {
+ buffer.append(time); // `toString()` defined as
"HH:mm[:ss]" optionally with fractions.
+ final ZoneOffset zone = t.query(TemporalQueries.offset());
+ if (zone != null) {
+ buffer.append(zone); // `toString()` defined as "Z" or
"±hh:mm" optionally with seconds.
+ }
+ }
+ buffer.append('\'');
} else {
return appendValue((value != null) ? value.toString() : (String)
null);
}
diff --git
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/Syntax.java
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/Syntax.java
index ef21416737..d4ab856b54 100644
---
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/Syntax.java
+++
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/Syntax.java
@@ -56,14 +56,20 @@ public class Syntax {
/**
* Creates a new {@code Syntax} initialized from the given database
metadata.
*
- * @param metadata the database metadata.
+ * @param metadata the database metadata, or {@code null} if
unavailable.
* @param quoteSchema whether the schema name should be written between
quotes.
* @throws SQLException if an error occurred while fetching the database
metadata.
*/
public Syntax(final DatabaseMetaData metadata, final boolean quoteSchema)
throws SQLException {
- dialect = Dialect.guess(metadata);
- quote = metadata.getIdentifierQuoteString();
- escape = metadata.getSearchStringEscape();
+ if (metadata != null) {
+ dialect = Dialect.guess(metadata);
+ quote = metadata.getIdentifierQuoteString();
+ escape = metadata.getSearchStringEscape();
+ } else {
+ dialect = Dialect.ANSI;
+ quote = "\"";
+ escape = "\\";
+ }
this.quoteSchema = quoteSchema;
}
diff --git
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/temporal/LenientDateFormat.java
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/temporal/LenientDateFormat.java
index 5a63f41d66..337b5762c0 100644
---
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/temporal/LenientDateFormat.java
+++
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/temporal/LenientDateFormat.java
@@ -73,13 +73,15 @@ public final class LenientDateFormat extends DateFormat {
/**
* The thread-safe instance to use for reading and formatting dates.
* Only the year is mandatory, all other fields are optional at parsing
time.
- * However, all fields are written, including milliseconds at formatting
time.
+ * However, all fields are written, including milliseconds at formatting
time,
+ * unless that field is not available at all (which is not he same as
available
+ * with value zero).
*
* @see #parseInstantUTC(CharSequence, int, int)
*/
public static final DateTimeFormatter FORMAT = new
DateTimeFormatterBuilder()
.parseLenient() // For allowing fields with one
digit instead of two.
- .parseCaseInsensitive() .appendValue(ChronoField.YEAR,
4, 5, SignStyle.NORMAL) // Proleptic year (use negative number if needed).
+ .parseCaseInsensitive() .appendValue(ChronoField.YEAR,
4, 10, SignStyle.NORMAL) // Proleptic year (use negative number if needed).
.optionalStart().appendLiteral('-').appendValue(ChronoField.MONTH_OF_YEAR, 2)
.optionalStart().appendLiteral('-').appendValue(ChronoField.DAY_OF_MONTH, 2)
.optionalStart().appendLiteral('T').appendValue(ChronoField.HOUR_OF_DAY, 2)
diff --git
a/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/privy/SQLBuilderTest.java
b/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/privy/SQLBuilderTest.java
new file mode 100644
index 0000000000..59b2e68668
--- /dev/null
+++
b/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/privy/SQLBuilderTest.java
@@ -0,0 +1,97 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.sis.metadata.sql.privy;
+
+import java.sql.SQLException;
+import java.time.LocalDate;
+import java.time.LocalDateTime;
+import java.time.LocalTime;
+import java.time.OffsetDateTime;
+import java.time.Year;
+import java.time.ZoneId;
+import java.time.ZoneOffset;
+import java.time.ZonedDateTime;
+
+// Test dependencies
+import org.junit.jupiter.api.Test;
+import static org.junit.jupiter.api.Assertions.*;
+import org.apache.sis.test.TestCase;
+
+
+/**
+ * Tests the {@link SQLBuilder} class.
+ *
+ * @author Martin Desruisseaux (Geomatys)
+ */
+public final class SQLBuilderTest extends TestCase {
+ /**
+ * The builder to use for the tests.
+ */
+ private final SQLBuilder builder;
+
+ /**
+ * Creates a new test case.
+ *
+ * @throws SQLException should never happen for this test.
+ */
+ public SQLBuilderTest() throws SQLException {
+ builder = new SQLBuilder(null, false);
+ }
+
+ /**
+ * Asserts that the builder content is equal to the expected value, then
clears the buffer.
+ *
+ * @param expected the expected content.
+ */
+ private void compareAndClear(final String expected) {
+ assertEquals(expected, builder.toString());
+ assertSame(builder, builder.clear());
+ }
+
+ /**
+ * Tests the formatting of values of different types.
+ */
+ @Test
+ public void testAppendValue() {
+ assertSame(builder, builder.appendValue(46));
+ compareAndClear("46");
+
+ assertSame(builder, builder.appendValue("46"));
+ compareAndClear("'46'");
+
+ assertSame(builder, builder.appendValue(Year.of(2024)));
+ compareAndClear("'2024'");
+
+ assertSame(builder, builder.appendValue(LocalDate.of(2024, 10, 2)));
+ compareAndClear("'2024-10-02'");
+
+ assertSame(builder, builder.appendValue(LocalTime.of(9, 5)));
+ compareAndClear("'09:05'");
+
+ assertSame(builder, builder.appendValue(LocalTime.of(18, 32, 7)));
+ compareAndClear("'18:32:07'");
+
+ assertSame(builder, builder.appendValue(LocalDateTime.of(2024, 10, 2,
18, 32, 7)));
+ compareAndClear("'2024-10-02 18:32:07'");
+
+ assertSame(builder, builder.appendValue(OffsetDateTime.of(2024, 10, 2,
18, 32, 7, 0, ZoneOffset.ofHours(4))));
+ compareAndClear("'2024-10-02 18:32:07+04:00'");
+
+ assertSame(builder, builder.appendValue(ZonedDateTime.of(2024, 10, 2,
18, 32, 7, 0, ZoneId.of("CET"))));
+ compareAndClear("'2024-10-02 18:32:07+02:00'");
+ }
+}
diff --git
a/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/privy/SQLUtilitiesTest.java
b/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/privy/SQLUtilitiesTest.java
index ee7f93fe3e..192e3e787f 100644
---
a/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/privy/SQLUtilitiesTest.java
+++
b/endorsed/src/org.apache.sis.metadata/test/org/apache/sis/metadata/sql/privy/SQLUtilitiesTest.java
@@ -39,7 +39,7 @@ public final class SQLUtilitiesTest extends TestCase {
*/
@Test
public void testToLikePattern() {
- final StringBuilder buffer = new StringBuilder(30);
+ final var buffer = new StringBuilder(30);
assertEquals("WGS84", toLikePattern(buffer,
"WGS84"));
assertEquals("WGS%84", toLikePattern(buffer, "WGS
84"));
assertEquals("A%text%with%random%symbols%", toLikePattern(buffer, "A
text !* with_random:/symbols;+"));
