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 d122f9a3c6984184a89340438edc53fdd6d7a041 Author: Martin Desruisseaux <[email protected]> AuthorDate: Fri Aug 15 11:58:26 2025 +0200 Modify the SQL statements in `EPSGDataAccess` for expecting the Boolean type. It makes "search and replace" easier. --- .../referencing/factory/sql/AuthorityCodes.java | 4 +- .../referencing/factory/sql/EPSGCodeFinder.java | 16 +++-- .../referencing/factory/sql/EPSGDataAccess.java | 2 +- .../sis/referencing/factory/sql/SQLTranslator.java | 77 ++++++---------------- 4 files changed, 35 insertions(+), 64 deletions(-) diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/AuthorityCodes.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/AuthorityCodes.java index 27b01d3b7d..ab1f20c895 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/AuthorityCodes.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/AuthorityCodes.java @@ -137,11 +137,11 @@ final class AuthorityCodes extends AbstractMap<String,String> implements Seriali final Class<?> tableType = table.where(factory, type, buffer); final int conditionStart = buffer.length(); if (table.showColumn != null) { - buffer.append(table.showColumn).append("<>0 AND "); + buffer.append(table.showColumn).append("=TRUE AND "); // Do not put spaces around "<>" - SQLTranslator searches for this exact match. } // Do not put spaces around "=" - SQLTranslator searches for this exact match. - buffer.append("DEPRECATED=0 ORDER BY ").append(table.codeColumn); + buffer.append("DEPRECATED=FALSE ORDER BY ").append(table.codeColumn); sql[ALL] = factory.translator.apply(buffer.toString()); /* * Build the SQL query for fetching the name of a single object for a given code. diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGCodeFinder.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGCodeFinder.java index cab3292126..690cb585ef 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGCodeFinder.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGCodeFinder.java @@ -296,7 +296,7 @@ crs: if (isInstance(CoordinateReferenceSystem.class, object)) { if (n == 2) { filters = new Condition[2]; for (int i=0; i<=1; i++) { - if ((filters[i] = dependencies((i==0) ? "CMPD_HORIZCRS_CODE" : "CMPD_VERTCRS_CODE", + if ((filters[i] = dependencies((i == 0) ? "CMPD_HORIZCRS_CODE" : "CMPD_VERTCRS_CODE", CoordinateReferenceSystem.class, components.get(i), false)) == null) { return Set.of(); @@ -315,7 +315,7 @@ crs: if (isInstance(CoordinateReferenceSystem.class, object)) { * * SELECT COORD_REF_SYS_CODE FROM "Coordinate Reference System" * WHERE CAST(COORD_REF_SYS_KIND AS VARCHAR(80)) LIKE 'geographic%' - * AND DATUM_CODE IN (?,…) AND DEPRECATED=0 + * AND DATUM_CODE IN (?,…) AND DEPRECATED=FALSE * ORDER BY COORD_REF_SYS_CODE */ final Condition filter; @@ -449,9 +449,15 @@ crs: if (isInstance(CoordinateReferenceSystem.class, object)) { } if (isNext) buffer.append(')'); } - buffer.append(getSearchDomain() == Domain.ALL_DATASET - ? " ORDER BY ABS(DEPRECATED), " - : " AND DEPRECATED=0 ORDER BY "); // Do not put spaces around "=" - SQLTranslator searches for this exact match. + final boolean all = (getSearchDomain() == Domain.ALL_DATASET); + if (!all) { + buffer.append(" AND DEPRECATED=FALSE"); + // Do not put spaces around "=" because SQLTranslator searches for this exact match. + } + buffer.append(" ORDER BY "); + if (all) { + buffer.append(dao.translator.useBoolean() ? "DEPRECATED" : "ABS(DEPRECATED)").append(", "); + } for (final Condition filter : filters) { filter.appendToOrderBy(buffer); } diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGDataAccess.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGDataAccess.java index 49eca3b051..c5dff9eb64 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGDataAccess.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGDataAccess.java @@ -3392,7 +3392,7 @@ next: while (r.next()) { key = "TransformationFromCRS"; sql = "SELECT COORD_OP_CODE" + " FROM \"Coordinate_Operation\"" - + " WHERE DEPRECATED=0" // Do not put spaces around "=" - SQLTranslator searches for this exact match. + + " WHERE DEPRECATED=FALSE" // Do not put spaces around "=" - SQLTranslator searches for this exact match. + " AND SOURCE_CRS_CODE = ?" + " AND TARGET_CRS_CODE = ?" + " ORDER BY COORD_OP_ACCURACY ASC NULLS LAST"; diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/SQLTranslator.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/SQLTranslator.java index 784539212e..9228a36e80 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/SQLTranslator.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/SQLTranslator.java @@ -139,15 +139,6 @@ public class SQLTranslator implements UnaryOperator<String> { */ static final String TABLE_PREFIX = "epsg_"; - /** - * The columns that may be of {@code BOOLEAN} type instead of {@code SMALLINT}. - */ - private static final String[] BOOLEAN_COLUMNS = { - "SHOW_CRS", - "SHOW_OPERATION", - "DEPRECATED" - }; - /** * The column where {@code VARCHAR} value may need to be cast to an enumeration. * With PostgreSQL, only columns in the {@code WHERE} part of the <abbr>SQL</abbr> statement @@ -214,11 +205,13 @@ public class SQLTranslator implements UnaryOperator<String> { private Map<String,String> tableRewording; /** - * Mapping from column names used by {@link EPSGDataAccess} to the names actually used by the database. - * The {@code COORD_AXIS_ORDER} column may be {@code ORDER} in the MS-Access database. - * This map is rarely non-empty. + * Replacements to perform on <abbr>SQL</abbr> statements before execution. Some entries are the replacements + * of column names used by {@link EPSGDataAccess} to the names actually used in the target database. The main + * case is the {@code COORD_AXIS_ORDER} column which may be named {@code ORDER} in the dataset for MS-Access. + * Other entries are replacements of missing columns by {@code CAST(NULL AS …)} expressions and replacements + * of {@code BOOLEAN} comparator operands by {@code SMALLINT}. */ - private Map<String,String> columnRenaming; + private Map<String,String> replacements; /** * The characters used for quoting identifiers, or a whitespace if none. @@ -307,7 +300,7 @@ public class SQLTranslator implements UnaryOperator<String> { * <li>Determine whether the table names are prefixed by {@value #TABLE_PREFIX} * and whether table names are in lower-case or mixed-case.</li> * - * <li>Fill the {@link #tableRewording} and {@link #columnRenaming} maps. These maps translate table + * <li>Fill the {@link #tableRewording} and {@link #replacements} maps. These maps translate table * and column names used in the <abbr>SQL</abbr> statements into the names used by the database. * Two conventions are understood: the names used in the MS-Access database or the names used * in the <abbr>SQL</abbr> scripts, potentially with {@linkplain #TABLE_PREFIX prefix} removed.</li> @@ -360,8 +353,8 @@ public class SQLTranslator implements UnaryOperator<String> { */ UnaryOperator<String> toNativeCase = UnaryOperator.identity(); schemaPattern = SQLUtilities.escape(schema, escape); - columnRenaming = new HashMap<>(); tableRewording = new HashMap<>(); + replacements = new HashMap<>(); /* * Special cases not covered by the generic algorithm implemented in `toActualTableName(…)`. * The entries are actually not full table names, but words separated by space. For example, @@ -500,10 +493,10 @@ check: for (;;) { tableRewording.put("Extent", "Area"); } missingColumns.forEach((column, type) -> { - columnRenaming.put(column, "CAST(NULL AS " + type + ") AS " + column); + replacements.put(column, "CAST(NULL AS " + type + ") AS " + column); }); mayRenameColumns.values().removeAll(brokenTargetCols); // For renaming only when the old name has been found. - columnRenaming.putAll(mayRenameColumns); + replacements.putAll(mayRenameColumns); mayReuse = false; } if (!mayReuse) { @@ -512,10 +505,18 @@ check: for (;;) { missingColumns.clear(); } } + /* + * If the database uses the SMALLINT type instead of BOOLEAN, replace `DEPRECATED=FALSE` by `DEPRECATED=0`. + * Note: is does not cover all cases. Some more complex cases are handled in `if (useBoolean())` blocks. + */ + if (!useBoolean) { + replacements.put("=FALSE", "=0"); + replacements.put("=TRUE", "<>0"); + } + replacements = Map.copyOf(replacements); tableRewording = Map.copyOf(tableRewording); - columnRenaming = Map.copyOf(columnRenaming); sameTableNames = useMixedCaseTableNames && "\"".equals(identifierQuote) && tableRewording.isEmpty(); - sameQueries = sameTableNames && (tableNameEnum == null) && columnRenaming.isEmpty() && !useBoolean; + sameQueries = sameTableNames && useBoolean && (tableNameEnum == null) && replacements.isEmpty(); } /** @@ -669,30 +670,7 @@ check: for (;;) { } } buffer.append(sql, end, sql.length()); - columnRenaming.forEach((toSearch, replaceBy) -> StringBuilders.replace(buffer, toSearch, replaceBy)); - /* - * If the database use the BOOLEAN type instead of SMALLINT, replaces "deprecated=0' by "deprecated=false". - */ - if (useBoolean) { - int w = buffer.indexOf("WHERE"); - if (w >= 0) { - w += 5; - for (final String field : BOOLEAN_COLUMNS) { - int p = buffer.indexOf(field, w); - if (p >= 0) { - p += field.length(); - if (!replaceIfEquals(buffer, p, "=0", "=FALSE") && - !replaceIfEquals(buffer, p, "<>0", "=TRUE")) - { - // Remove "ABS" in "ABS(DEPRECATED)" or "ABS(CO.DEPRECATED)". - if ((p = buffer.lastIndexOf("(", p)) > w) { - replaceIfEquals(buffer, p-3, "ABS", ""); - } - } - } - } - } - } + replacements.forEach((toSearch, replaceBy) -> StringBuilders.replace(buffer, toSearch, replaceBy)); /* * If the database uses enumeration, we need an explicit cast with PostgreSQL. * The enumeration type is typically "EPSG"."Table Name". @@ -706,17 +684,4 @@ check: for (;;) { } return buffer.toString(); } - - /** - * Replaces the text at the given position in the buffer if it is equal to the {@code expected} text. - */ - private static boolean replaceIfEquals(final StringBuilder buffer, final int pos, - final String expected, final String replacement) - { - if (CharSequences.regionMatches(buffer, pos, expected)) { - buffer.replace(pos, pos + expected.length(), replacement); - return true; - } - return false; - } }
