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 559ad8b78c2a70396588433d1ec5fe6f6aceeeca Author: Martin Desruisseaux <[email protected]> AuthorDate: Sat Jun 28 15:39:04 2025 +0200 Replace the MS-Access SQL dialect by ANSI SQL in EPSG factory. For example, use "Datum" instead of [Datum] in hard-coded queries (before this commit, [Datum] was replaced by "Datum" at runtime). --- .../sis/metadata/sql/privy/ScriptRunner.java | 12 +- .../referencing/factory/sql/EPSGCodeFinder.java | 10 +- .../referencing/factory/sql/EPSGDataAccess.java | 176 +++++----- .../sis/referencing/factory/sql/EPSGFactory.java | 14 +- .../sis/referencing/factory/sql/EPSGInstaller.java | 18 +- .../sis/referencing/factory/sql/SQLTranslator.java | 377 +++++++++++---------- .../sis/referencing/factory/sql/TableInfo.java | 69 ++-- .../sis/referencing/factory/sql/package-info.java | 4 +- .../referencing/internal/SignReversalComment.java | 4 +- .../operation/provider/AbstractProvider.java | 8 +- .../referencing/factory/sql/EPSGInstallerTest.java | 5 +- .../sis/referencing/factory/sql/TableInfoTest.java | 7 +- .../org/apache/sis/util/CharSequencesTest.java | 55 +-- 13 files changed, 408 insertions(+), 351 deletions(-) diff --git a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/ScriptRunner.java b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/ScriptRunner.java index 4ca39c62ff..4883581f48 100644 --- a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/ScriptRunner.java +++ b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/ScriptRunner.java @@ -384,10 +384,14 @@ public class ScriptRunner implements AutoCloseable { * The replacement is performed only for occurrences outside identifiers or texts. * * <h4>Example</h4> - * This is used for mapping the table names in the EPSG scripts to table names as they were in the MS-Access - * flavor of EPSG database. It may also contains the mapping between SQL keywords used in the SQL scripts to - * SQL keywords understood by the database. For example if a database does not support the {@code "TEXT"} - * data type, it may be replaced by {@code "LONG VARCHAR"}. + * This is used for mapping the <abbr>EPSG</abbr> table names from the mixed-cases convention used in + * the {@code org.apache.sis.referencing.epsg} module to the lower-cases convention which is actually + * used if the user installed the <abbr>EPSG</abbr> database manually (there is more differences than + * only the case). + * + * <p>Another example is the replacement of <abbr>SQL</abbr> keywords used in the scripts by keywords + * understood by the database. For example, if a database does not support the {@code "TEXT"} data type, + * this method can be used for replacing {@code "TEXT"} by {@code "LONG VARCHAR"}.</p> * * <h4>Limitation</h3> * The {@code inScript} word to replace must be a single word with no space. 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 45a6228236..a925a30e9b 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 @@ -220,7 +220,7 @@ final class EPSGCodeFinder extends IdentifiedObjectFinder { */ @Override public final String toString() { - final StringBuilder buffer = new StringBuilder(50); + final var buffer = new StringBuilder(50); appendToWhere(buffer, false); return buffer.toString(); } @@ -382,7 +382,7 @@ crs: if (isInstance(CoordinateReferenceSystem.class, object)) { * but does not execute it now. Note that this block overwrites the `buffer` content, * so that buffer shall not contain valuable information yet. */ - final StringBuilder buffer = new StringBuilder(350); // Temporary buffer for building SQL query. + final var buffer = new StringBuilder(350); // Temporary buffer for building SQL query. final Set<String> namePatterns; final String aliasSQL; if (ArraysExt.containsIdentity(filters, Condition.NAME)) { @@ -392,7 +392,9 @@ crs: if (isInstance(CoordinateReferenceSystem.class, object)) { namePatterns.add(toDatumPattern(id.tip().toString(), buffer)); } buffer.setLength(0); - buffer.append("SELECT OBJECT_CODE FROM [Alias] WHERE OBJECT_TABLE_NAME='").append(table.unquoted()).append("' AND "); + buffer.append("SELECT OBJECT_CODE FROM \"Alias\" WHERE OBJECT_TABLE_NAME='") + .append(dao.translator.toActualTableName(table.unquoted())) + .append("' AND "); // PostgreSQL does not require explicit cast when the value is a literal instead of "?". appendFilterByName(namePatterns, "ALIAS", buffer); aliasSQL = dao.translator.apply(buffer.toString()); @@ -457,7 +459,7 @@ crs: if (isInstance(CoordinateReferenceSystem.class, object)) { * At this point the SQL query is complete. Run it, preserving order. * Then sort the result by taking in account the supersession table. */ - final Set<String> result = new LinkedHashSet<>(); // We need to preserve order in this set. + final var result = new LinkedHashSet<String>(); // We need to preserve order in this set. try (ResultSet r = stmt.executeQuery(dao.translator.apply(buffer.toString()))) { while (r.next()) { result.add(r.getString(1)); 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 f02f2a29e7..3f3f06c54c 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 @@ -19,7 +19,6 @@ package org.apache.sis.referencing.factory.sql; import java.util.Arrays; import java.util.Set; import java.util.Map; -import java.util.List; import java.util.HashMap; import java.util.LinkedHashSet; import java.util.LinkedHashMap; @@ -43,6 +42,7 @@ import java.sql.Statement; import java.sql.SQLException; import java.net.URI; import java.net.URISyntaxException; +import java.time.LocalDate; import java.time.temporal.Temporal; import javax.measure.Unit; import javax.measure.quantity.Angle; @@ -149,11 +149,6 @@ import org.opengis.referencing.ObjectDomain; * in the common case where only a few EPSG codes are used by an application. * {@code EPSGDataAccess.createFoo(String)} methods do not cache by themselves and query the database on every invocation. * - * <h2>SQL dialects</h2> - * Because the primary distribution format for the EPSG dataset is MS-Access, this class uses SQL statements formatted - * for the MS-Access dialect. For usage with other database software products like PostgreSQL or Derby, - * a {@link SQLTranslator} instance is provided to the constructor. - * * @author Yann Cézard (IRD) * @author Martin Desruisseaux (IRD, Geomatys) * @author Rueben Schulz (UBC) @@ -199,7 +194,7 @@ public class EPSGDataAccess extends GeodeticAuthorityFactory implements CRSAutho @Workaround(library = "EPSG:6401-6420", version = "8.9") // Deprecated in 2002 but still present in 2016. private static final Map<Integer,Integer> DEPRECATED_CS = deprecatedCS(); static Map<Integer,Integer> deprecatedCS() { - final Map<Integer,Integer> m = new HashMap<>(24); + final var m = new HashMap<Integer,Integer>(24); // Ellipsoidal 2D CS. Axes: latitude, longitude. Orientations: north, east. UoM: degree Integer replacement = 6422; @@ -332,8 +327,10 @@ public class EPSGDataAccess extends GeodeticAuthorityFactory implements CRSAutho protected final Connection connection; /** - * The translator from the SQL statements using MS-Access dialect - * to SQL statements using the dialect of the actual database. + * The translator from the <abbr>SQL</abbr> statements hard-coded in this class to + * <abbr>SQL</abbr> statements compatible with the actual <abbr>EPSG</abbr> database. + * This translator may also change the schema and table names used in the queries + * in the actual database uses different names. */ protected final SQLTranslator translator; @@ -349,17 +346,22 @@ public class EPSGDataAccess extends GeodeticAuthorityFactory implements CRSAutho * * @param owner the {@code EPSGFactory} which is creating this Data Access Object (DAO). * @param connection the connection to the underlying EPSG database. - * @param translator the translator from the SQL statements using MS-Access dialect - * to SQL statements using the dialect of the actual database. + * @param translator translator from the <abbr>SQL</abbr> statements hard-coded in this class to + * <abbr>SQL</abbr> statements compatible with the actual <abbr>EPSG</abbr> database. + * @throws SQLException if an error occurred with the database connection. * * @see EPSGFactory#newDataAccess(Connection, SQLTranslator) */ - protected EPSGDataAccess(final EPSGFactory owner, final Connection connection, final SQLTranslator translator) { + protected EPSGDataAccess(final EPSGFactory owner, final Connection connection, final SQLTranslator translator) + throws SQLException + { this.owner = owner; this.connection = Objects.requireNonNull(connection); this.translator = Objects.requireNonNull(translator); this.namespace = owner.nameFactory.createNameSpace( owner.nameFactory.createLocalName(null, Constants.IOGP), null); + connection.setCatalog(translator.getCatalog()); + connection.setSchema (translator.getSchema()); } /** @@ -409,7 +411,7 @@ public class EPSGDataAccess extends GeodeticAuthorityFactory implements CRSAutho /* * We do not cache this citation because the caching service is already provided by ConcurrentAuthorityFactory. */ - final DefaultCitation c = new DefaultCitation("EPSG Geodetic Parameter Dataset"); + final var c = new DefaultCitation("EPSG Geodetic Parameter Dataset"); c.setIdentifiers(Set.of(new ImmutableIdentifier(null, null, Constants.EPSG))); try { /* @@ -417,7 +419,7 @@ public class EPSGDataAccess extends GeodeticAuthorityFactory implements CRSAutho * instead of UTC because the date is for information purpose only, and the local timezone is * more likely to be shown nicely (without artificial hours) to the user. */ - final String query = translator.apply("SELECT VERSION_NUMBER, VERSION_DATE FROM [Version History]" + + final String query = translator.apply("SELECT VERSION_NUMBER, VERSION_DATE FROM \"Version History\"" + " ORDER BY VERSION_DATE DESC, VERSION_HISTORY_CODE DESC"); String version = null; try (Statement stmt = connection.createStatement(); @@ -428,7 +430,7 @@ public class EPSGDataAccess extends GeodeticAuthorityFactory implements CRSAutho final Date date = result.getDate(2); // Local timezone. if (version != null && date != null) { // Paranoiac check. c.setEdition(new SimpleInternationalString(version)); - c.setEditionDate(date); + c.setEditionDate(LocalDate.ofEpochDay(date.getTime() / Constants.MILLISECONDS_PER_DAY)); break; } } @@ -462,7 +464,7 @@ addURIs: for (int i=0; ; i++) { } default: break addURIs; // Finished adding all URIs. } - final DefaultOnlineResource r = new DefaultOnlineResource(); + final var r = new DefaultOnlineResource(); try { r.setLinkage(new URI(url)); } catch (URISyntaxException exception) { @@ -685,6 +687,7 @@ addURIs: for (int i=0; ; i++) { private int[] toPrimaryKeys(final String table, final String codeColumn, final String nameColumn, final String... codes) throws SQLException, FactoryException { + String actualTable = null; final int[] primaryKeys = new int[codes.length]; codes: for (int i=0; i<codes.length; i++) { final String code = codes[i]; @@ -694,15 +697,19 @@ codes: for (int i=0; i<codes.length; i++) { * We search first in the primary table. If no name is not found there, then we * will search in the aliases table as a fallback. */ + if (actualTable == null) { + actualTable = translator.toActualTableName(table); + } final String pattern = SQLUtilities.toLikePattern(code, false); Integer resolved = null; boolean alias = false; do { PreparedStatement stmt; if (alias) { - // Note: "OBJECT_TABLE_NAME=?" is handled in a special way by SQLTranslator. - stmt = prepareStatement("AliasKey", "SELECT OBJECT_CODE, ALIAS FROM [Alias] WHERE OBJECT_TABLE_NAME=? AND ALIAS LIKE ?"); - stmt.setString(1, table); + stmt = prepareStatement("AliasKey", + "SELECT OBJECT_CODE, ALIAS FROM \"Alias\"" + + " WHERE OBJECT_TABLE_NAME=? AND ALIAS LIKE ?"); + stmt.setString(1, actualTable); stmt.setString(2, pattern); } else { /* @@ -723,7 +730,7 @@ codes: for (int i=0; i<codes.length; i++) { if (stmt == null) { stmt = connection.prepareStatement(translator.apply( "SELECT " + codeColumn + ", " + nameColumn + - " FROM [" + table + "] WHERE " + nameColumn + " LIKE ?")); + " FROM \"" + actualTable + "\" WHERE " + nameColumn + " LIKE ?")); statements.put(KEY, stmt); lastTableForName = table; } @@ -775,7 +782,7 @@ codes: for (int i=0; i<codes.length; i++) { final String sql, final String... codes) throws SQLException, FactoryException { assert Thread.holdsLock(this); - assert sql.contains('[' + table + ']') : table; + assert sql.contains('"' + table + '"') : table; assert (codeColumn == null) || sql.contains(codeColumn) || table.equals("Area") : codeColumn; assert (nameColumn == null) || sql.contains(nameColumn) || table.equals("Area") : nameColumn; return executeQuery(table, sql, toPrimaryKeys(table, codeColumn, nameColumn, codes)); @@ -1044,9 +1051,9 @@ codes: for (int i=0; i<codes.length; i++) { String reason = null; Object replacedBy = null; try (ResultSet result = executeMetadataQuery("Deprecation", - "SELECT DEPRECATION_REASON, REPLACED_BY FROM [Deprecation]" + - " WHERE OBJECT_TABLE_NAME=? AND OBJECT_CODE=?", table, code)) - // Note: "OBJECT_TABLE_NAME=?" is handled in a special way by SQLTranslator. + "SELECT DEPRECATION_REASON, REPLACED_BY FROM \"Deprecation\"" + + " WHERE OBJECT_TABLE_NAME=? AND OBJECT_CODE=?", + translator.toActualTableName(table), code)) { while (result.next()) { reason = getOptionalString (result, 1); @@ -1060,13 +1067,13 @@ codes: for (int i=0; i<codes.length; i++) { replacedBy = replacedBy.toString(); } /* - * Try to infer the method name from the table name. For example if the deprecated code was found in - * the [Coordinate Reference System] table, then we declare createCoordinateReferenceSystem(String) + * Try to infer the method name from the table name. For example, if the deprecated code was found in + * the "Coordinate Reference System" table, then we declare `createCoordinateReferenceSystem(String)` * as the source of the log message. */ String method = "create"; for (final TableInfo info : TableInfo.EPSG) { - if (TableInfo.tableMatches(info.table, table)) { + if (info.tableMatches(table)) { method += info.type.getSimpleName(); break; } @@ -1110,14 +1117,14 @@ codes: for (int i=0; i<codes.length; i++) { * - We do not perform this replacement directly in our EPSG database because ASCII letters are more * convenient for implementing accent-insensitive searches. */ - final List<GenericName> aliases = new ArrayList<>(); + final var aliases = new ArrayList<GenericName>(); try (ResultSet result = executeMetadataQuery("Alias", "SELECT NAMING_SYSTEM_NAME, ALIAS" + - " FROM [Alias] INNER JOIN [Naming System]" + - " ON [Alias].NAMING_SYSTEM_CODE =" + - " [Naming System].NAMING_SYSTEM_CODE" + - " WHERE OBJECT_TABLE_NAME=? AND OBJECT_CODE=?", table, code)) - // Note: "OBJECT_TABLE_NAME=?" is handled in a special way by SQLTranslator. + " FROM \"Alias\" INNER JOIN \"Naming System\"" + + " ON \"Alias\".NAMING_SYSTEM_CODE =" + + " \"Naming System\".NAMING_SYSTEM_CODE" + + " WHERE OBJECT_TABLE_NAME=? AND OBJECT_CODE=?", + translator.toActualTableName(table), code)) { while (result.next()) { final String naming = getOptionalString(result, 1); @@ -1153,7 +1160,7 @@ codes: for (int i=0; i<codes.length; i++) { properties.put(NamedIdentifier.VERSION_KEY, version); properties.put(NamedIdentifier.AUTHORITY_KEY, authority); properties.put(AbstractIdentifiedObject.LOCALE_KEY, locale); - final NamedIdentifier id = new NamedIdentifier(properties); + final var id = new NamedIdentifier(properties); properties.clear(); properties.put(IdentifiedObject.NAME_KEY, id); } @@ -1348,7 +1355,7 @@ codes: for (int i=0; i<codes.length; i++) { " PROJECTION_CONV_CODE," + // [11] For ProjectedCRS " CMPD_HORIZCRS_CODE," + // [12] For CompoundCRS only " CMPD_VERTCRS_CODE" + // [13] For CompoundCRS only - " FROM [Coordinate Reference System]" + + " FROM \"Coordinate Reference System\"" + " WHERE COORD_REF_SYS_CODE = ?", code)) { while (result.next()) { @@ -1632,7 +1639,7 @@ codes: for (int i=0; i<codes.length; i++) { " DEPRECATED," + " ELLIPSOID_CODE," + // Only for geodetic type " PRIME_MERIDIAN_CODE" + // Only for geodetic type - " FROM [Datum]" + + " FROM \"Datum\"" + " WHERE DATUM_CODE = ?", code)) { while (result.next()) { @@ -1773,23 +1780,23 @@ codes: for (int i=0; i<codes.length; i++) { if (code == BursaWolfInfo.TARGET_DATUM) { return null; } - final List<BursaWolfInfo> bwInfos = new ArrayList<>(); + final var bwInfos = new ArrayList<BursaWolfInfo>(); try (ResultSet result = executeQuery("BursaWolfParametersSet", "SELECT COORD_OP_CODE," + " COORD_OP_METHOD_CODE," + " TARGET_CRS_CODE," + " AREA_OF_USE_CODE"+ - " FROM [Coordinate_Operation]" + + " FROM \"Coordinate_Operation\"" + " WHERE DEPRECATED=0" + // Do not put spaces around "=" - SQLTranslator searches for this exact match. " AND TARGET_CRS_CODE = " + BursaWolfInfo.TARGET_CRS + " AND COORD_OP_METHOD_CODE >= " + BursaWolfInfo.MIN_METHOD_CODE + " AND COORD_OP_METHOD_CODE <= " + BursaWolfInfo.MAX_METHOD_CODE + " AND SOURCE_CRS_CODE IN " + - "(SELECT COORD_REF_SYS_CODE FROM [Coordinate Reference System] WHERE DATUM_CODE = ?)" + + "(SELECT COORD_REF_SYS_CODE FROM \"Coordinate Reference System\" WHERE DATUM_CODE = ?)" + " ORDER BY TARGET_CRS_CODE, COORD_OP_ACCURACY, COORD_OP_CODE DESC", code)) { while (result.next()) { - final BursaWolfInfo info = new BursaWolfInfo( + final var info = new BursaWolfInfo( getInteger(code, result, 1), // Operation getInteger(code, result, 2), // Method getInteger(code, result, 3), // Target datum @@ -1818,7 +1825,7 @@ codes: for (int i=0; i<codes.length; i++) { /* * Now, iterate over the results and fetch the parameter values for each BursaWolfParameters object. */ - final BursaWolfParameters[] parameters = new BursaWolfParameters[size]; + final var parameters = new BursaWolfParameters[size]; final Locale locale = getLocale(); int count = 0; for (int i=0; i<size; i++) { @@ -1840,12 +1847,12 @@ codes: for (int i=0; i<codes.length; i++) { if (!equalsIgnoreMetadata(meridian, datum.getPrimeMeridian())) { continue; } - final BursaWolfParameters bwp = new BursaWolfParameters(datum, info.getDomainOfValidity(owner)); + final var bwp = new BursaWolfParameters(datum, info.getDomainOfValidity(owner)); try (ResultSet result = executeQuery("BursaWolfParameters", "SELECT PARAMETER_CODE," + " PARAMETER_VALUE," + " UOM_CODE" + - " FROM [Coordinate_Operation Parameter Value]" + + " FROM \"Coordinate_Operation Parameter Value\"" + " WHERE COORD_OP_CODE = ?" + " AND COORD_OP_METHOD_CODE = ?", info.operation, info.method)) { @@ -1905,7 +1912,7 @@ codes: for (int i=0; i<codes.length; i++) { " UOM_CODE," + " REMARKS," + " DEPRECATED" + - " FROM [Ellipsoid]" + + " FROM \"Ellipsoid\"" + " WHERE ELLIPSOID_CODE = ?", code)) { while (result.next()) { @@ -1995,7 +2002,7 @@ codes: for (int i=0; i<codes.length; i++) { " UOM_CODE," + " REMARKS," + " DEPRECATED" + - " FROM [Prime Meridian]" + + " FROM \"Prime Meridian\"" + " WHERE PRIME_MERIDIAN_CODE = ?", code)) { while (result.next()) { @@ -2053,7 +2060,7 @@ codes: for (int i=0; i<codes.length; i++) { " AREA_NORTH_BOUND_LAT," + " AREA_WEST_BOUND_LON," + " AREA_EAST_BOUND_LON" + - " FROM [Area]" + + " FROM \"Area\"" + " WHERE AREA_CODE = ?", code)) { while (result.next()) { @@ -2079,7 +2086,7 @@ codes: for (int i=0; i<codes.length; i++) { bbox = new DefaultGeographicBoundingBox(xmin, xmax, ymin, ymax); } if (description != null || bbox != null) { - DefaultExtent extent = new DefaultExtent(description, bbox, null, null); + var extent = new DefaultExtent(description, bbox, null, null); extent.transitionTo(DefaultExtent.State.FINAL); returnValue = ensureSingleton(extent, returnValue, code); } @@ -2134,7 +2141,7 @@ codes: for (int i=0; i<codes.length; i++) { " DIMENSION," + " REMARKS," + " DEPRECATED" + - " FROM [Coordinate System]" + + " FROM \"Coordinate System\"" + " WHERE COORD_SYS_CODE = ?", code)) { while (result.next()) { @@ -2241,9 +2248,6 @@ codes: for (int i=0; i<codes.length; i++) { /** * Returns the coordinate system axis from an EPSG code for a {@link CoordinateSystem}. * - * <p><strong>WARNING:</strong> The EPSG database uses "{@code ORDER}" as a column name. - * This is tolerated by Access, but MySQL does not accept that name.</p> - * * @param cs the EPSG code for the coordinate system. * @param dimension of the coordinate system, which is also the size of the returned array. * @return an array of coordinate system axis. @@ -2254,12 +2258,12 @@ codes: for (int i=0; i<codes.length; i++) { throws SQLException, FactoryException { int i = 0; - final CoordinateSystemAxis[] axes = new CoordinateSystemAxis[dimension]; + final var axes = new CoordinateSystemAxis[dimension]; try (ResultSet result = executeQuery("AxisOrder", "SELECT COORD_AXIS_CODE" + - " FROM [Coordinate Axis]" + + " FROM \"Coordinate Axis\"" + " WHERE COORD_SYS_CODE = ?" + - " ORDER BY [ORDER]", cs)) + " ORDER BY COORD_AXIS_ORDER", cs)) { while (result.next()) { final String axis = getString(cs, result, 1); @@ -2314,7 +2318,7 @@ codes: for (int i=0; i<codes.length; i++) { " COORD_AXIS_ORIENTATION," + " COORD_AXIS_ABBREVIATION," + " UOM_CODE" + - " FROM [Coordinate Axis]" + + " FROM \"Coordinate Axis\"" + " WHERE COORD_AXIS_CODE = ?", code)) { while (result.next()) { @@ -2354,7 +2358,7 @@ codes: for (int i=0; i<codes.length; i++) { if (returnValue == null) { try (ResultSet result = executeQuery("Coordinate Axis Name", "SELECT COORD_AXIS_NAME, DESCRIPTION, REMARKS" + - " FROM [Coordinate Axis Name]" + + " FROM \"Coordinate Axis Name\"" + " WHERE COORD_AXIS_NAME_CODE = ?", code)) { while (result.next()) { @@ -2413,7 +2417,7 @@ codes: for (int i=0; i<codes.length; i++) { " FACTOR_C," + " TARGET_UOM_CODE," + " UNIT_OF_MEAS_NAME" + - " FROM [Unit of Measure]" + + " FROM \"Unit of Measure\"" + " WHERE UOM_CODE = ?", code)) { while (result.next()) { @@ -2428,8 +2432,10 @@ codes: for (int i=0; i<codes.length; i++) { */ final boolean pb = (b != 1); if (pb || c != 1) { - throw new FactoryDataException(error().getString(Errors.Keys.InconsistentAttribute_2, - pb ? "FACTOR_B" : "FACTOR_C", pb ? b : c)); + throw new FactoryDataException(error().getString( + Errors.Keys.InconsistentAttribute_2, + pb ? "FACTOR_B" : "FACTOR_C", + pb ? b : c)); } } Unit<?> unit = Units.valueOfEPSG(source); // Check in our list of hard-coded unit codes. @@ -2488,7 +2494,7 @@ codes: for (int i=0; i<codes.length; i++) { " PARAMETER_NAME," + " DESCRIPTION," + " DEPRECATED" + - " FROM [Coordinate_Operation Parameter]" + + " FROM \"Coordinate_Operation Parameter\"" + " WHERE PARAMETER_CODE = ?", code)) { while (result.next()) { @@ -2511,7 +2517,7 @@ codes: for (int i=0; i<codes.length; i++) { */ type = Double.class; try (ResultSet r = executeQuery("ParameterType", - "SELECT PARAM_VALUE_FILE_REF FROM [Coordinate_Operation Parameter Value]" + + "SELECT PARAM_VALUE_FILE_REF FROM \"Coordinate_Operation Parameter Value\"" + " WHERE (PARAMETER_CODE = ?) AND PARAM_VALUE_FILE_REF IS NOT NULL", epsg)) { while (r.next()) { @@ -2532,7 +2538,7 @@ codes: for (int i=0; i<codes.length; i++) { */ units = new LinkedHashSet<>(); try (ResultSet r = executeQuery("ParameterUnit", - "SELECT UOM_CODE FROM [Coordinate_Operation Parameter Value]" + + "SELECT UOM_CODE FROM \"Coordinate_Operation Parameter Value\"" + " WHERE (PARAMETER_CODE = ?)" + " GROUP BY UOM_CODE" + " ORDER BY COUNT(UOM_CODE) DESC", epsg)) @@ -2563,7 +2569,8 @@ next: while (r.next()) { */ InternationalString isReversible = null; try (ResultSet r = executeQuery("ParameterSign", - "SELECT DISTINCT PARAM_SIGN_REVERSAL FROM [Coordinate_Operation Parameter Usage]" + + "SELECT DISTINCT PARAM_SIGN_REVERSAL" + + " FROM \"Coordinate_Operation Parameter Usage\"" + " WHERE (PARAMETER_CODE = ?)", epsg)) { if (r.next()) { @@ -2593,8 +2600,7 @@ next: while (r.next()) { final Map<String,Object> properties = createProperties("Coordinate_Operation Parameter", name, epsg, isReversible, deprecated); properties.put(Identifier.DESCRIPTION_KEY, description); - final ParameterDescriptor<?> descriptor = new DefaultParameterDescriptor<>(properties, - 1, 1, type, valueDomain, null, null); + final var descriptor = new DefaultParameterDescriptor<>(properties, 1, 1, type, valueDomain, null, null); returnValue = ensureSingleton(descriptor, returnValue, code); } } catch (SQLException exception) { @@ -2614,10 +2620,10 @@ next: while (r.next()) { * @throws SQLException if a SQL statement failed. */ private ParameterDescriptor<?>[] createParameterDescriptors(final Integer method) throws FactoryException, SQLException { - final List<ParameterDescriptor<?>> descriptors = new ArrayList<>(); + final var descriptors = new ArrayList<ParameterDescriptor<?>>(); try (ResultSet result = executeQuery("Coordinate_Operation Parameter Usage", "SELECT PARAMETER_CODE" + - " FROM [Coordinate_Operation Parameter Usage]" + + " FROM \"Coordinate_Operation Parameter Usage\"" + " WHERE COORD_OP_METHOD_CODE = ?" + " ORDER BY SORT_ORDER", method)) { @@ -2644,10 +2650,10 @@ next: while (r.next()) { " CV.PARAMETER_VALUE," + " CV.PARAM_VALUE_FILE_REF," + " CV.UOM_CODE" + - " FROM ([Coordinate_Operation Parameter Value] AS CV" + - " INNER JOIN [Coordinate_Operation Parameter] AS CP" + + " FROM (\"Coordinate_Operation Parameter Value\" AS CV" + + " INNER JOIN \"Coordinate_Operation Parameter\" AS CP" + " ON CV.PARAMETER_CODE = CP.PARAMETER_CODE)" + - " INNER JOIN [Coordinate_Operation Parameter Usage] AS CU" + + " INNER JOIN \"Coordinate_Operation Parameter Usage\" AS CU" + " ON (CP.PARAMETER_CODE = CU.PARAMETER_CODE)" + " AND (CV.COORD_OP_METHOD_CODE = CU.COORD_OP_METHOD_CODE)" + " WHERE CV.COORD_OP_METHOD_CODE = ?" + @@ -2745,7 +2751,7 @@ next: while (r.next()) { " COORD_OP_METHOD_NAME," + " REMARKS," + " DEPRECATED" + - " FROM [Coordinate_Operation Method]" + + " FROM \"Coordinate_Operation Method\"" + " WHERE COORD_OP_METHOD_CODE = ?", code)) { while (result.next()) { @@ -2759,8 +2765,8 @@ next: while (r.next()) { /* * Note: we do not store the formula at this time, because the text is very verbose and rarely used. */ - final OperationMethod method = new DefaultOperationMethod(properties, - new DefaultParameterDescriptorGroup(properties, 1, 1, descriptors)); + final var params = new DefaultParameterDescriptorGroup(properties, 1, 1, descriptors); + final var method = new DefaultOperationMethod(properties, params); returnValue = ensureSingleton(method, returnValue, code); } } catch (SQLException exception) { @@ -2814,7 +2820,7 @@ next: while (r.next()) { " COORD_OP_SCOPE," + " REMARKS," + " DEPRECATED" + - " FROM [Coordinate_Operation]" + + " FROM \"Coordinate_Operation\"" + " WHERE COORD_OP_CODE = ?", code)) { while (result.next()) { @@ -2892,7 +2898,7 @@ next: while (r.next()) { opProperties.put(CoordinateOperation.OPERATION_VERSION_KEY, version); if (!Double.isNaN(accuracy)) { opProperties.put(CoordinateOperation.COORDINATE_OPERATION_ACCURACY_KEY, - PositionalAccuracyConstant.create(accuracy)); + PositionalAccuracyConstant.create(accuracy)); } /* * Creates the operation. Conversions should be the only operations allowed to have @@ -2912,10 +2918,10 @@ next: while (r.next()) { */ result.close(); opProperties = new HashMap<>(opProperties); // Because this class uses a shared map. - final List<String> codes = new ArrayList<>(); + final var codes = new ArrayList<String>(); try (ResultSet cr = executeQuery("Coordinate_Operation Path", "SELECT SINGLE_OPERATION_CODE" + - " FROM [Coordinate_Operation Path]" + + " FROM \"Coordinate_Operation Path\"" + " WHERE (CONCAT_OPERATION_CODE = ?)" + " ORDER BY OP_PATH_STEP", epsg)) { @@ -2923,7 +2929,7 @@ next: while (r.next()) { codes.add(getString(code, cr, 1)); } } - final CoordinateOperation[] operations = new CoordinateOperation[codes.size()]; + final var operations = new CoordinateOperation[codes.size()]; ensureNoCycle(CoordinateOperation.class, epsg); try { for (int i=0; i<operations.length; i++) { @@ -3020,7 +3026,7 @@ next: while (r.next()) { ArgumentChecks.ensureNonNull("sourceCRS", sourceCRS); ArgumentChecks.ensureNonNull("targetCRS", targetCRS); final String label = sourceCRS + " ⇨ " + targetCRS; - final CoordinateOperationSet set = new CoordinateOperationSet(owner); + final var set = new CoordinateOperationSet(owner); try { final int[] pair = toPrimaryKeys(null, null, null, sourceCRS, targetCRS); boolean searchTransformations = false; @@ -3034,8 +3040,8 @@ next: while (r.next()) { if (searchTransformations) { key = "TransformationFromCRS"; sql = "SELECT COORD_OP_CODE" + - " FROM [Coordinate_Operation] AS CO" + - " JOIN [Area] ON AREA_OF_USE_CODE = AREA_CODE" + + " FROM \"Coordinate_Operation\" AS CO" + + " JOIN \"Area\" ON AREA_OF_USE_CODE = AREA_CODE" + " WHERE CO.DEPRECATED=0" + // Do not put spaces around "=" - SQLTranslator searches for this exact match. " AND SOURCE_CRS_CODE = ?" + " AND TARGET_CRS_CODE = ?" + @@ -3046,7 +3052,7 @@ next: while (r.next()) { } else { key = "ConversionFromCRS"; sql = "SELECT PROJECTION_CONV_CODE" + - " FROM [Coordinate Reference System]" + + " FROM \"Coordinate Reference System\"" + " WHERE SOURCE_GEOGCRS_CODE = ?" + " AND COORD_REF_SYS_CODE = ?"; } @@ -3123,11 +3129,11 @@ next: while (r.next()) { unexpectedException("sort", e); continue; } - // Note: "OBJECT_TABLE_NAME=?" is handled in a special way by SQLTranslator. try (ResultSet result = executeMetadataQuery("Supersession", - "SELECT SUPERSEDED_BY FROM [Supersession]" + + "SELECT SUPERSEDED_BY FROM \"Supersession\"" + " WHERE OBJECT_TABLE_NAME=? AND OBJECT_CODE=?" + - " ORDER BY SUPERSESSION_YEAR DESC", table, code)) + " ORDER BY SUPERSESSION_YEAR DESC", + translator.toActualTableName(table), code)) { while (result.next()) { final String replacement = result.getString(1); diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGFactory.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGFactory.java index 9fdc0cf6d4..fd88b10996 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGFactory.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGFactory.java @@ -161,8 +161,12 @@ public class EPSGFactory extends ConcurrentAuthorityFactory<EPSGDataAccess> impl private final InstallationScriptProvider scriptProvider; /** - * The translator from the SQL statements using MS-Access dialect to SQL statements using the dialect - * of the actual database. If null, will be created when first needed. + * The translator from the <abbr>SQL</abbr> statements hard-coded in {@link EPSGDataAccess} + * to <abbr>SQL</abbr> statements compatible with the actual <abbr>EPSG</abbr> database. + * This translator may also change the schema and table names used in the queries in the + * actual database uses different names. + * + * <p>If {@code null}, a default translator will be created when first needed.</p> */ private volatile SQLTranslator translator; @@ -507,10 +511,10 @@ public class EPSGFactory extends ConcurrentAuthorityFactory<EPSGDataAccess> impl * by {@code new MyDataAccessSubclass(…)}. * * @param connection a connection to the EPSG database. - * @param translator the translator from the SQL statements using MS-Access dialect to SQL statements - * using the dialect of the actual database. + * @param translator translator from the <abbr>SQL</abbr> statements hard-coded in {@link EPSGDataAccess} + * to <abbr>SQL</abbr> statements compatible with the actual <abbr>EPSG</abbr> database. * @return Data Access Object (DAO) to use in {@code createFoo(String)} methods. - * @throws SQLException if a problem with the database has been detected. + * @throws SQLException if an error occurred with the database connection. * * @see EPSGDataAccess#EPSGDataAccess(EPSGFactory, Connection, SQLTranslator) */ diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGInstaller.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGInstaller.java index b577ab1676..7f9e451649 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGInstaller.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGInstaller.java @@ -91,8 +91,9 @@ final class EPSGInstaller extends ScriptRunner { } /** - * Creates immediately a schema of the given name in the database and remember that the - * {@code "epsg_"} prefix in table names will need to be replaced by path to that schema. + * Creates immediately a schema of the given name in the database. + * The {@code "epsg_"} prefix in table names or the {@code "epsg"} + * schema name will be replaced by the given schema name. * * <p>This method should be invoked only once. It does nothing if the database does not supports schema.</p> * @@ -103,17 +104,22 @@ final class EPSGInstaller extends ScriptRunner { public void setSchema(final String schema) throws SQLException, IOException { if (isSchemaSupported) { /* - * Creates the schema on the database. We do that before to setup the 'toSchema' map, while the map still null. + * Creates the schema on the database. * Note that we do not quote the schema name, which is a somewhat arbitrary choice. */ - execute(new StringBuilder("CREATE SCHEMA ").append(identifierQuote).append(schema).append(identifierQuote)); + final var sb = new StringBuilder(40); + execute(sb.append("CREATE SCHEMA ").append(identifierQuote).append(schema).append(identifierQuote)); if (isGrantOnSchemaSupported) { - execute(new StringBuilder("GRANT USAGE ON SCHEMA ") + sb.setLength(0); + execute(sb.append("GRANT USAGE ON SCHEMA ") .append(identifierQuote).append(schema).append(identifierQuote).append(" TO ").append(PUBLIC)); } /* - * Mapping from the table names used in the SQL scripts to the original names used in the MS-Access database. + * Mapping from the table names used in the SQL scripts to the names used in `EPSGDataAccess`. + * Those names are the ones that were used in the original EPSG dataset in MS-Access database. * We use those original names because they are easier to read than the names in SQL scripts. + * + * TODO: move to org.apache.sis.referencing.factory.sql.epsg.DataScriptFormatter. */ addReplacement(SQLTranslator.TABLE_PREFIX + "alias", "Alias"); addReplacement(SQLTranslator.TABLE_PREFIX + "area", "Area"); 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 d4f4953eb8..27f95c2ec2 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 @@ -17,61 +17,56 @@ package org.apache.sis.referencing.factory.sql; import java.util.Map; -import java.util.HashMap; import java.util.Locale; import java.util.function.Function; import java.sql.DatabaseMetaData; +import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import org.apache.sis.util.CharSequences; +import org.apache.sis.util.OptionalCandidate; +import org.apache.sis.util.StringBuilders; import org.apache.sis.util.resources.Errors; import org.apache.sis.util.privy.Constants; -import static org.apache.sis.util.privy.Strings.isNullOrEmpty; import org.apache.sis.metadata.sql.privy.Reflection; import org.apache.sis.metadata.sql.privy.SQLUtilities; import org.apache.sis.referencing.internal.Resources; /** - * Converts the SQL statements from MS-Access dialect to standard SQL. The {@link #apply(String)} method - * is invoked when a new {@link java.sql.PreparedStatement} is about to be created from a SQL string. - * Since the <a href="https://epsg.org/";>EPSG dataset</a> is available primarily in MS-Access format, - * the original SQL statements are formatted using a dialect specific to that particular database software. - * If the actual EPSG dataset to query is hosted on another database product, then the SQL query needs to be - * adapted to the target database dialect before to be executed. - * - * <h2>Example</h2> - * SQL statements for an EPSG dataset hosted on the <i>PostgreSQL</i> database need to have their brackets - * ({@code '['} and {@code ']'}) replaced by the quote character ({@code '"'}) before to be sent to the database - * driver. Furthermore, table names may be different. So the following MS-Access query: + * Adapter of <abbr>SQL</abbr> statements for variations in syntax and table names. + * The {@link #apply(String)} method is invoked when a new {@link PreparedStatement} + * is about to be created from a <abbr>SQL</abbr> string. + * This class allows {@link EPSGFactory} to accept some variants of table names. + * For example, the following <abbr>SQL</abbr> query: * * <ul> - * <li>{@code SELECT * FROM [Coordinate Reference System]}</li> + * <li>{@code SELECT * FROM "Coordinate Reference System"}</li> * </ul> * - * needs to be converted to one of the following possibilities for a PostgreSQL database - * (the reason for those multiple choices will be discussed later): + * may be converted to one of the following possibilities: * * <ul> - * <li>{@code SELECT * FROM "Coordinate Reference System"}</li> - * <li>{@code SELECT * FROM epsg_coordinatereferencesystem} (in the default schema)</li> - * <li>{@code SELECT * FROM epsg.coordinatereferencesystem} (in the {@code "epsg"} schema)</li> - * <li>{@code SELECT * FROM epsg."Coordinate Reference System"}</li> + * <li>{@code SELECT * FROM "Coordinate Reference System"} (no change)</li> + * <li>{@code SELECT * FROM coordinatereferencesystem}</li> + * <li>{@code SELECT * FROM epsg_coordinatereferencesystem}</li> * </ul> * - * <h2>ANSI SQL</h2> - * In addition to the file in MS-Access format, EPSG also provides the dataset as SQL files for PostgreSQL, - * MySQL and Oracle databases. Those SQL files are used as both <i>Data Description Language</i> (DDL) - * and <i>Data Manipulation Language</i> (DML). - * But the table names and some column names in those scripts differ from the ones used in the MS-Access database. - * The following table summarizes the name changes: + * The mixed-case variant is used in the dataset distributed by <abbr>EPSG</abbr> in the MS-Access format, + * while the lower-case variant is used in the <abbr>SQL</abbr> scripts distributed by <abbr>EPSG</abbr>. + * By default, this class auto-detects which database schema contains the <abbr>EPSG</abbr> tables + * and whether the database uses the lower-case or mixed-case variant of table names. + * It is legal to use the mixed-case variant in a PostgreSQL database (for example) + * even if <abbr>EPSG</abbr> distributes the PosthreSQL scripts in lower-case. + * The following table gives the mapping between the two variants accepted by {@link EPSGFactory}: * * <table class="sis"> * <caption>Table and column names</caption> - * <tr><th>Element</th><th>Name in MS-Access database</th> <th>Name in SQL scripts</th></tr> + * <tr><th>Element</th><th>Name in MS-Access database</th> <th>Name in <abbr>SQL</abbr> scripts</th></tr> * <tr><td>Table</td> <td>{@code Alias}</td> <td>{@code epsg_alias}</td></tr> * <tr><td>Table</td> <td>{@code Area}</td> <td>{@code epsg_area}</td></tr> + * <tr><td>Table</td> <td>{@code Change}</td> <td>{@code epsg_change}</td></tr> * <tr><td>Table</td> <td>{@code Coordinate Axis}</td> <td>{@code epsg_coordinateaxis}</td></tr> * <tr><td>Table</td> <td>{@code Coordinate Axis Name}</td> <td>{@code epsg_coordinateaxisname}</td></tr> * <tr><td>Table</td> <td>{@code Coordinate_Operation}</td> <td>{@code epsg_coordoperation}</td></tr> @@ -83,18 +78,16 @@ import org.apache.sis.referencing.internal.Resources; * <tr><td>Table</td> <td>{@code Coordinate Reference System}</td> <td>{@code epsg_coordinatereferencesystem}</td></tr> * <tr><td>Table</td> <td>{@code Coordinate System}</td> <td>{@code epsg_coordinatesystem}</td></tr> * <tr><td>Table</td> <td>{@code Datum}</td> <td>{@code epsg_datum}</td></tr> + * <tr><td>Table</td> <td>{@code Deprecation}</td> <td>{@code epsg_deprecation}</td></tr> * <tr><td>Table</td> <td>{@code Ellipsoid}</td> <td>{@code epsg_ellipsoid}</td></tr> * <tr><td>Table</td> <td>{@code Naming System}</td> <td>{@code epsg_namingsystem}</td></tr> * <tr><td>Table</td> <td>{@code Prime Meridian}</td> <td>{@code epsg_primemeridian}</td></tr> * <tr><td>Table</td> <td>{@code Supersession}</td> <td>{@code epsg_supersession}</td></tr> * <tr><td>Table</td> <td>{@code Unit of Measure}</td> <td>{@code epsg_unitofmeasure}</td></tr> + * <tr><td>Table</td> <td>{@code Version History}</td> <td>{@code epsg_versionhistory}</td></tr> * <tr><td>Column</td> <td>{@code ORDER}</td> <td>{@code coord_axis_order}</td></tr> * </table> * - * By default this class auto-detects the schema that contains the EPSG tables and whether the table names are - * the ones used by EPSG in the MS-Access version or the PostgreSQL, MySQL or Oracle version of the database. - * Consequently, it is legal to use the MS-Access table names, which are more readable, in a PostgreSQL database. - * * <h2>Thread safety</h2> * All {@code SQLTranslator} instances given to the {@link EPSGFactory} constructor * <strong>shall</strong> be immutable and thread-safe. @@ -103,32 +96,34 @@ import org.apache.sis.referencing.internal.Resources; * @author Martin Desruisseaux (IRD) * @author Didier Richard (IGN) * @author John Grange - * @version 1.0 + * @version 1.5 * @since 0.7 */ public class SQLTranslator implements Function<String,String> { /** - * Table names used as "sentinel value" for detecting the presence of an EPSG database. - * This array lists different possible names for the same table. The first entry must be - * the MS-Access name. Other names may be in any order. They will be tried in reverse order. + * The prefix in table names. The SQL scripts are provided by EPSG with this prefix in front of all table names. + * SIS rather uses a modified version of those SQL scripts which creates the tables in an "EPSG" database schema. + * But we still need to check for existence of this prefix in case someone used the original SQL scripts. + * + * @see #usePrefixedTableNames */ - private static final String[] SENTINEL = { - "Coordinate Reference System", - "coordinatereferencesystem", - "epsg_coordinatereferencesystem" - }; + static final String TABLE_PREFIX = "epsg_"; /** - * Index of the {@link #SENTINEL} element which is in mixed case. No other element should be in mixed case. + * Table names used as "sentinel value" for detecting the presence of an <abbr>EPSG</abbr> database. + * This array lists different names that may be used for the same table. The names will be tested in + * declaration order. */ - private static final int MIXED_CASE = 0; + private static final String[] SENTINEL = { + TABLE_PREFIX + "coordinatereferencesystem", + "Coordinate Reference System", // The index of this entry must be declared in `MIXED_CASE`. + "coordinatereferencesystem" + }; /** - * The prefix in table names. The SQL scripts are provided by EPSG with this prefix in front of all table names. - * SIS rather uses a modified version of those SQL scripts which creates the tables in an "EPSG" database schema. - * But we still need to check for existence of this prefix in case someone used the original SQL scripts. + * Index of the {@link #SENTINEL} element which is in mixed case. No other element should be in mixed case. */ - static final String TABLE_PREFIX = "epsg_"; + private static final int MIXED_CASE = 1; /** * The columns that may be of {@code BOOLEAN} type instead of {@code SMALLINT}. @@ -140,22 +135,19 @@ public class SQLTranslator implements Function<String,String> { }; /** - * The column where {@code VARCHAR} value may need to be casted to an enumeration. - * With PostgreSQL, only columns in the {@code WHERE} part of the SQL statement needs - * an explicit cast; the columns in the {@code SELECT} part are implicitly casted. + * 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 + * needs an explicit cast, as the columns in the {@code SELECT} part are implicitly cast. */ private static final String ENUMERATION_COLUMN = "OBJECT_TABLE_NAME"; // In "Alias" table. /** * The name of the catalog that contains the EPSG tables, or {@code null} or an empty string. * <ul> - * <li>The {@code ""} value retrieves the EPSG schema without a catalog.</li> + * <li>The {@code ""} value retrieves the <abbr>EPSG</abbr> schema without a catalog.</li> * <li>The {@code null} value means that the catalog name should not be used to narrow the search.</li> * </ul> * - * <p><b>Consider this field as final.</b> This field is non-final only for construction convenience, - * or for updating after the {@link EPSGInstaller} class created the database.</p> - * * @see #getCatalog() */ private String catalog; @@ -164,50 +156,61 @@ public class SQLTranslator implements Function<String,String> { * The name of the schema that contains the EPSG tables, or {@code null} or an empty string. * <ul> * <li>The {@code ""} value retrieves the EPSG tables without a schema. - * In such case, table names are prefixed by {@value #TABLE_PREFIX}.</li> + * In such case, table names are typically prefixed by {@value #TABLE_PREFIX}.</li> * <li>The {@code null} value means that the schema name should not be used to narrow the search. - * In such case, {@code SQLTranslator} will tries to automatically detect the schema.</li> + * In such case, {@code SQLTranslator} will try to automatically detect the schema.</li> * </ul> * - * <b>Consider this field as final.</b> This field is non-final only for construction convenience, - * or for updating after the {@link EPSGInstaller} class created the database. + * Note that the <abbr>SQL</abbr> scripts distributed by <abbr>EPSG</abbr> do not use schema. + * Instead, <abbr>EPSG</abbr> puts an {@value #TABLE_PREFIX} prefix in front of every table names. + * The {@link #usePrefixedTableNames} flag tells whether it is the case for the current database. + * The two approaches (schema or prefix) are redundant, but it is okay to use both of them anyway. * * @see #getSchema() + * @see #usePrefixedTableNames */ private String schema; /** - * Whether the table names are prefixed by {@value #TABLE_PREFIX}. When installed by Apache SIS, - * the table names are not prefixed if the tables are stored in a schema. However, the dataset may - * have been installed manually by users following different rules. + * Whether the table names are prefixed by {@value #TABLE_PREFIX}. The <abbr>EPSG</abbr> geodetic dataset + * in the {@code org.apache.sis.referencing.epsg} module replaces that prefix by the {@code "epsg"} schema. + * However, if the dataset has been installed manually by the user, then the table name prefix is present. + * + * @see #TABLE_PREFIX + * @see #schema + */ + private boolean usePrefixedTableNames; + + /** + * {@code true} if this class uses mixed-case convention for table names. + * In such case, the table names will need to be quoted using the {@link #quote} character. */ - private boolean isPrefixed; + private boolean useMixedCaseTableNames; /** - * Mapping from words used in the MS-Access database to words used in the ANSI versions of EPSG databases. - * A word may be a table or a column name, or a part of it. A table name may consist of many words separated - * by spaces. This map does not list all tables used in EPSG schema, but only the ones that cannot be mapped + * Mapping from mixed-case "word" used by {@link EPSGDataAccess} to the words actually used by the database. + * A word may be a table name or a part of it. A table name may consist of many words separated by spaces. + * This map does not list all tables used in <abbr>EPSG</abbr> schema, but only the ones that cannot be mapped * by more generic code (e.g. by replacing spaces by '_'). * - * <p>The keys are the names in the MS-Access database, and the values are the names in the SQL scripts. - * By convention, all column names in keys are in upper-case while table names are in mixed-case characters.</p> + * <p>The keys are names that are hard-coded in the <abbr>SQL</abbr> statements of {@link EPSGDataAccess} class, + * and values are the names used in the <abbr>EPSG</abbr> database on which {@link EPSGDataAccess} is connected. + * By convention, all column names are in upper-case while table names are in mixed-case characters.</p> */ - private final Map<String,String> accessToAnsi; + private Map<String,String> tableRenaming; /** - * {@code true} if this class needs to quote table names. This quoting should be done only if the database - * uses the MS-Access table names, even if we are targeting a PostgreSQL, MySQL or Oracle database. - * - * <p><b>Consider this field as final.</b> This field is non-final only for construction convenience, - * or for updating after the {@link EPSGInstaller} class created the database.</p> + * 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. */ - private boolean quoteTableNames; + private Map<String,String> columnRenaming; /** * The characters used for quoting identifiers, or a whitespace if none. * This information is provided by {@link DatabaseMetaData#getIdentifierQuoteString()}. */ - private final String quote; + private final String identifierQuote; /** * Non-null if the {@value #ENUMERATION_COLUMN} column in {@code "Alias"} table uses enumeration instead @@ -233,9 +236,15 @@ public class SQLTranslator implements Function<String,String> { private boolean isTableFound; /** - * Creates a new SQL translator for the database described by the given metadata. + * Whether the <abbr>SQL</abbr> queries hard-coded in {@link EPSGDataAccess} can be used verbatim + * with the actual database. This is {@code true} if this translator applies no change at all. + */ + private boolean sameQueries; + + /** + * Creates a new <abbr>SQL</abbr> translator for the database described by the given metadata. * This constructor detects automatically the dialect: the characters to use for quoting identifiers, - * and whether the table names are the ones used in the MS-Access database or in the SQL scripts. + * and whether the <abbr>EPSG</abbr> table names uses the mixed-case or lower-case convention. * * <p>If the given catalog or schema name is non-null, then the {@linkplain DatabaseMetaData#getTables * search for EPSG tables} will be restricted to the catalog or schema of that name. @@ -248,8 +257,7 @@ public class SQLTranslator implements Function<String,String> { * @throws SQLException if an error occurred while querying the database metadata. */ public SQLTranslator(final DatabaseMetaData md, final String catalog, final String schema) throws SQLException { - quote = md.getIdentifierQuoteString().trim(); - accessToAnsi = new HashMap<>(4); + identifierQuote = md.getIdentifierQuoteString().trim(); this.catalog = catalog; this.schema = schema; setup(md); @@ -259,29 +267,30 @@ public class SQLTranslator implements Function<String,String> { * Sets the value of all non-final fields. This method performs two steps: * * <ol class="verbose"> - * <li>Finds the schema that seems to contain the EPSG tables. If there is more than one schema containing the + * <li>Find the schema that seems to contain the EPSG tables. If there is more than one schema containing the * tables, gives precedence to the schema named "EPSG" if one is found. If there is no schema named "EPSG", - * takes an arbitrary schema. It may be the empty string if the tables are not contained in a schema.</li> + * take an arbitrary schema. It may be the empty string if the tables are not contained in a schema.</li> * - * <li>Fills the {@link #accessToAnsi} map. That map translates the table and column names used in the SQL - * 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 SQL scripts. Both of them are distributed by EPSG.</li> + * <li>Fill the {@link #tableRenaming} and {@link #columnRenaming} 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. Both of them are distributed by <abbr>EPSG</abbr>.</li> * </ol> */ final void setup(final DatabaseMetaData md) throws SQLException { final boolean toUpperCase = md.storesUpperCaseIdentifiers(); final String escape = md.getSearchStringEscape(); String schemaPattern = SQLUtilities.escape(schema, escape); - for (int i = SENTINEL.length; --i >= 0;) { + for (int i=0; i<SENTINEL.length; i++) { String table = SENTINEL[i]; if (toUpperCase && i != MIXED_CASE) { table = table.toUpperCase(Locale.US); } try (ResultSet result = md.getTables(catalog, schemaPattern, table, null)) { if (result.next()) { - isTableFound = true; - isPrefixed = table.startsWith(TABLE_PREFIX); - quoteTableNames = (i == MIXED_CASE); + isTableFound = true; + usePrefixedTableNames = table.startsWith(TABLE_PREFIX); + useMixedCaseTableNames = (i == MIXED_CASE); do { catalog = result.getString(Reflection.TABLE_CAT); schema = result.getString(Reflection.TABLE_SCHEM); @@ -292,27 +301,28 @@ public class SQLTranslator implements Function<String,String> { } } } + tableRenaming = Map.of(); + columnRenaming = Map.of(); + if (!isTableFound) { + return; + } /* - * At this point the catalog and schema have been found or confirmed, or are still null - * if we did not found them. Now fill the 'accessToAnsi' map. + * At this point, the catalog and schema have been found or have been confirmed, + * or are still null if we did not found the EPSG table used as a sentinel value. */ - boolean translateColumns = true; - accessToAnsi.clear(); - if (quoteTableNames) { - /* - * MS-Access database uses a column named "ORDER" in the "Coordinate Axis" table. - * This column has been renamed "coord_axis_order" in DLL scripts. - * We need to check which name our current database uses. - */ - try (ResultSet result = md.getColumns(catalog, schemaPattern, "Coordinate Axis", "ORDER")) { - translateColumns = !result.next(); - } - } else { - accessToAnsi.put("Coordinate_Operation", "coordoperation"); - accessToAnsi.put("Parameter", "param"); + if (!useMixedCaseTableNames) { + tableRenaming = Map.of("Coordinate_Operation", "coordoperation", + "Parameter", "param"); } - if (translateColumns) { - accessToAnsi.put("ORDER", "coord_axis_order"); + /* + * MS-Access database uses a column named "ORDER" in the "Coordinate Axis" table. + * This column has been renamed "coord_axis_order" in DLL scripts. + * We need to check which name our current database uses. + */ + try (ResultSet result = md.getColumns(catalog, schemaPattern, toActualTableName("Coordinate Axis"), "ORDER")) { + if (result.next()) { + columnRenaming = Map.of("COORD_AXIS_ORDER", "ORDER"); + } } /* * Detect if the database uses boolean types where applicable. @@ -324,7 +334,7 @@ public class SQLTranslator implements Function<String,String> { deprecated = deprecated.toLowerCase(Locale.US); objectTable = objectTable.toLowerCase(Locale.US); } - final String tablePattern = isPrefixed ? SQLUtilities.escape(TABLE_PREFIX, escape) + '%' : null; + final String tablePattern = usePrefixedTableNames ? SQLUtilities.escape(TABLE_PREFIX, escape) + '%' : null; try (ResultSet result = md.getColumns(catalog, schemaPattern, tablePattern, deprecated)) { while (result.next()) { if (CharSequences.endsWith(result.getString(Reflection.TABLE_NAME), "Datum", true)) { @@ -335,8 +345,8 @@ public class SQLTranslator implements Function<String,String> { } } /* - * Detect if the table use enumeration (on PostgreSQL database) instead of VARCHAR. - * Enumerations appear in various tables, but is used in WHERE clause in the Alias table. + * Detect if the tables use enumeration (on PostgreSQL database) instead of VARCHAR. + * Enumerations appear in various tables, including in a WHERE clause for the Alias table. */ try (ResultSet result = md.getColumns(catalog, schemaPattern, tablePattern, objectTable)) { while (result.next()) { @@ -349,6 +359,11 @@ public class SQLTranslator implements Function<String,String> { } } } + sameQueries = (tableNameEnum == null) + && tableRenaming.isEmpty() + && columnRenaming.isEmpty() + && "\"".equals(identifierQuote) + && !useBoolean; } /** @@ -359,6 +374,7 @@ public class SQLTranslator implements Function<String,String> { * * @return the catalog that contains the EPSG schema, or {@code null}. */ + @OptionalCandidate public String getCatalog() { return catalog; } @@ -371,6 +387,7 @@ public class SQLTranslator implements Function<String,String> { * * @return the schema that contains the EPSG tables, or {@code null}. */ + @OptionalCandidate public String getSchema() { return schema; } @@ -409,60 +426,94 @@ public class SQLTranslator implements Function<String,String> { } /** - * Adapts the given SQL statement from the original MS-Access dialect to the dialect of the target database. - * Table and column names may also be replaced. + * Converts a mixed-case table name to the convention used in the database. + * The names of the tables for the two conventions are listed in a table in the Javadoc of this class. + * The rreturned string does not include the identifier quotes. + * + * @param name the mixed-case table name. + * @return the name converted to the convention used by the database. + * @since 1.5 + */ + public final String toActualTableName(String name) { + if (useMixedCaseTableNames) { + return name; + } + final var buffer = new StringBuilder(name.length() + 5); + toActualTableName(name, buffer); + return buffer.toString().toLowerCase(Locale.US); + } + + /** + * Appends the table name in the given buffer, converted to the convention used in the database. + * Identifier quotes are added when mixed-case is used. Otherwise, the identifier may not be in + * the right lower/upper case. But when there is no identifier quotes, the database should convert + * to* whatever convention it uses. + */ + private void toActualTableName(final String name, final StringBuilder buffer) { + if (useMixedCaseTableNames) { + buffer.append(identifierQuote).append(name).append(identifierQuote); + } else { + if (usePrefixedTableNames) { + buffer.append(TABLE_PREFIX); + } + for (final String word : name.split("\\s")) { + buffer.append(tableRenaming.getOrDefault(word, word)); + } + // Ignore lower/upper case. + } + } + + /** + * Adapts the given <abbr>SQL</abbr> statement from the mixed-case convention to the convention used by + * the target database. The mixed-case convention is used by {@link EPSGDataAccess} hard-coded queries. + * This method can replace the schema and table names, and sometime some <abbr>SQL</abbr> keywords, + * for complying with the expectation of the target database. * - * @param sql the statement in MS-Access dialect. - * @return the SQL statement adapted to the dialect of the target database. + * @param sql a <abbr>SQL</abbr> statement with table names in mixed-case convention. + * @return the given statement adapted to the expectation of the target database. */ @Override public String apply(final String sql) { - if (quote.isEmpty() && accessToAnsi.isEmpty() && isNullOrEmpty(schema) && isNullOrEmpty(catalog)) { - return sql; + if (sameQueries) { + return sql; // Shortcut when there is nothing to change. } - final StringBuilder ansi = new StringBuilder(sql.length() + 16); - int start, end = 0; - while ((start = sql.indexOf('[', end)) >= 0) { - /* - * Append every characters since the end of the last processed table/column name, - * or since the beginning of the SQL statement if we are in the first iteration. - * Then find the end of the new table/column name to process in this iteration. - */ - ansi.append(sql, end, start); - if ((end = sql.indexOf(']', ++start)) < 0) { - throw new IllegalArgumentException(Errors.format( - Errors.Keys.MissingCharacterInElement_2, sql.substring(start), ']')); - } - /* - * The name can be a table name or a column name, but only table names will be quoted. - * EPSG seems to write all column names in upper-case (MS-Access) or lower-case (ANSI), - * so we will let the database driver selects the case of its choice for column names. - */ - final String name = sql.substring(start, end++); - if (CharSequences.isUpperCase(name)) { - ansi.append(accessToAnsi.getOrDefault(name, name)); - } else { - appendIdentifier(ansi, name); + final var buffer = new StringBuilder(sql.length() + 16); + int end = 0; + if (!(useMixedCaseTableNames && "\"".equals(identifierQuote))) { + int start; + while ((start = sql.indexOf('"', end)) >= 0) { + /* + * Append every characters since the end of the last processed table/column name, + * or since the beginning of the SQL statement if we are in the first iteration. + * Then find the end of the new table/column name to process in this iteration. + */ + buffer.append(sql, end, start); + if ((end = sql.indexOf('"', ++start)) < 0) { + throw new IllegalArgumentException(Errors.format( + Errors.Keys.MissingCharacterInElement_2, sql.substring(start), '"')); + } + toActualTableName(sql.substring(start, end++), buffer); } } - ansi.append(sql, end, sql.length()); + 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 = ansi.indexOf("WHERE"); + int w = buffer.indexOf("WHERE"); if (w >= 0) { w += 5; for (final String field : BOOLEAN_COLUMNS) { - int p = ansi.indexOf(field, w); + int p = buffer.indexOf(field, w); if (p >= 0) { p += field.length(); - if (!replaceIfEquals(ansi, p, "=0", "=FALSE") && - !replaceIfEquals(ansi, p, "<>0", "=TRUE")) + if (!replaceIfEquals(buffer, p, "=0", "=FALSE") && + !replaceIfEquals(buffer, p, "<>0", "=TRUE")) { // Remove "ABS" in "ABS(DEPRECATED)" or "ABS(CO.DEPRECATED)". - if ((p = ansi.lastIndexOf("(", p)) > w) { - replaceIfEquals(ansi, p-3, "ABS", ""); + if ((p = buffer.lastIndexOf("(", p)) > w) { + replaceIfEquals(buffer, p-3, "ABS", ""); } } } @@ -474,49 +525,23 @@ public class SQLTranslator implements Function<String,String> { * The enumeration type is typically "EPSG"."Table Name". */ if (tableNameEnum != null) { - int w = ansi.lastIndexOf(ENUMERATION_COLUMN + "=?"); + int w = buffer.lastIndexOf(ENUMERATION_COLUMN + "=?"); if (w >= 0) { w += ENUMERATION_COLUMN.length() + 1; - ansi.replace(w, w+1, "CAST(? AS " + tableNameEnum + ')'); - } - } - return ansi.toString(); - } - - /** - * Appends the given identifier in the given buffer, between quotes and prefixed with the schema name. - * This is used mostly for appending table names, but can also be used for appending enumeration types. - */ - private void appendIdentifier(final StringBuilder buffer, final String identifier) { - if (!isNullOrEmpty(catalog)) { - buffer.append(quote).append(catalog).append(quote).append('.'); - } - if (!isNullOrEmpty(schema)) { - buffer.append(quote).append(schema).append(quote).append('.'); - } - if (quoteTableNames) { - buffer.append(quote); - } - if (isPrefixed) { - buffer.append(TABLE_PREFIX); - } - if (quoteTableNames) { - buffer.append(accessToAnsi.getOrDefault(identifier, identifier)).append(quote); - } else { - for (final String word : identifier.split("\\s")) { - buffer.append(accessToAnsi.getOrDefault(word, word)); + buffer.replace(w, w+1, "CAST(? AS " + tableNameEnum + ')'); } } + 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 ansi, final int pos, + private static boolean replaceIfEquals(final StringBuilder buffer, final int pos, final String expected, final String replacement) { - if (CharSequences.regionMatches(ansi, pos, expected)) { - ansi.replace(pos, pos + expected.length(), replacement); + if (CharSequences.regionMatches(buffer, pos, expected)) { + buffer.replace(pos, pos + expected.length(), replacement); return true; } return false; diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/TableInfo.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/TableInfo.java index 1223b01b5f..f632440ebb 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/TableInfo.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/TableInfo.java @@ -31,29 +31,29 @@ import org.apache.sis.referencing.crs.DefaultGeocentricCRS; /** - * Information about a specific table. The MS-Access dialect of SQL is assumed; - * it will be translated into ANSI SQL later by {@link SQLTranslator#apply(String)} if needed. + * Information about a specific table. This class uses the mixed-case variant of the <abbr>EPSG</abbr> table names. + * If needed, those names will be converted by {@link SQLTranslator#apply(String)} to the actual table names. * * @author Martin Desruisseaux (IRD, Geomatys) */ final class TableInfo { /** - * The {@link EPSG} item used for coordinate reference systems. + * The item used for coordinate reference systems. */ static final TableInfo CRS; /** - * The {@link EPSG} item used for datums. + * The item used for datums. */ static final TableInfo DATUM; /** - * The {@link EPSG} item used for ellipsoids. + * The item used for ellipsoids. */ static final TableInfo ELLIPSOID; /** - * List of tables and columns to test for codes values. + * List of tables and columns to test for existence of codes values. * Those tables are used by the {@link EPSGDataAccess#createObject(String)} method * in order to detect which of the following methods should be invoked for a given code: * @@ -73,7 +73,7 @@ final class TableInfo { */ static final TableInfo[] EPSG = { CRS = new TableInfo(CoordinateReferenceSystem.class, - "[Coordinate Reference System]", + "\"Coordinate Reference System\"", "COORD_REF_SYS_CODE", "COORD_REF_SYS_NAME", "COORD_REF_SYS_KIND", @@ -92,7 +92,7 @@ final class TableInfo { */ new TableInfo(CoordinateSystem.class, - "[Coordinate System]", + "\"Coordinate System\"", "COORD_SYS_CODE", "COORD_SYS_NAME", "COORD_SYS_TYPE", @@ -105,14 +105,14 @@ final class TableInfo { null), new TableInfo(CoordinateSystemAxis.class, - "[Coordinate Axis] AS CA INNER JOIN [Coordinate Axis Name] AS CAN" + - " ON CA.COORD_AXIS_NAME_CODE=CAN.COORD_AXIS_NAME_CODE", + "\"Coordinate Axis\" AS CA INNER JOIN \"Coordinate Axis Name\" AS CAN " + + "ON CA.COORD_AXIS_NAME_CODE=CAN.COORD_AXIS_NAME_CODE", "COORD_AXIS_CODE", "COORD_AXIS_NAME", null, null, null, null), DATUM = new TableInfo(Datum.class, - "[Datum]", + "\"Datum\"", "DATUM_CODE", "DATUM_NAME", "DATUM_TYPE", @@ -123,19 +123,19 @@ final class TableInfo { null), ELLIPSOID = new TableInfo(Ellipsoid.class, - "[Ellipsoid]", + "\"Ellipsoid\"", "ELLIPSOID_CODE", "ELLIPSOID_NAME", null, null, null, null), new TableInfo(PrimeMeridian.class, - "[Prime Meridian]", + "\"Prime Meridian\"", "PRIME_MERIDIAN_CODE", "PRIME_MERIDIAN_NAME", null, null, null, null), new TableInfo(CoordinateOperation.class, - "[Coordinate_Operation]", + "\"Coordinate_Operation\"", "COORD_OP_CODE", "COORD_OP_NAME", "COORD_OP_TYPE", @@ -144,31 +144,32 @@ final class TableInfo { "SHOW_OPERATION"), new TableInfo(OperationMethod.class, - "[Coordinate_Operation Method]", + "\"Coordinate_Operation Method\"", "COORD_OP_METHOD_CODE", "COORD_OP_METHOD_NAME", null, null, null, null), new TableInfo(ParameterDescriptor.class, - "[Coordinate_Operation Parameter]", + "\"Coordinate_Operation Parameter\"", "PARAMETER_CODE", "PARAMETER_NAME", null, null, null, null), new TableInfo(Unit.class, - "[Unit of Measure]", + "\"Unit of Measure\"", "UOM_CODE", "UNIT_OF_MEAS_NAME", null, null, null, null), }; /** - * The class of object to be created. + * The class of object to be created (usually a GeoAPI interface). */ final Class<?> type; /** - * The table name for SQL queries. May contains a {@code "JOIN"} clause. + * The table name for SQL queries, including schema name and quotes for mixed-case names. + * May contain a {@code JOIN} clause. * * @see #unquoted() */ @@ -186,8 +187,8 @@ final class TableInfo { /** * Column type for the type (usually with the {@code "_TYPE"} suffix), or {@code null}. - * {@link EPSGDataAccess} and {@link AuthorityCodes} assumes that values in this column - * have the maximal length described in the {@value #ENUM_REPLACEMENT} statement. + * {@link EPSGDataAccess} and {@link AuthorityCodes} assume that values in this column + * are not longer than the maximal length specified in {@value #ENUM_REPLACEMENT}. */ private final String typeColumn; @@ -213,6 +214,15 @@ final class TableInfo { /** * Stores information about a specific table. + * + * @param type the class of object to be created (usually a GeoAPI interface). + * @param table the table name for SQL queries, including quotes for mixed-case names. + * @param codeColumn column name for the code (usually with the {@code "_CODE"} suffix). + * @param nameColumn column name for the name (usually with the {@code "_NAME"} suffix), or {@code null}. + * @param typeColumn column type for the type (usually with the {@code "_TYPE"} suffix), or {@code null}. + * @param subTypes sub-interfaces of {@link #type} to handle, or {@code null} if none. + * @param typeNames names of {@code subTypes} in the database, or {@code null} if none. + * @param showColumn the column that specify if the object should be shown, or {@code null} if none. */ private TableInfo(final Class<?> type, final String table, final String codeColumn, final String nameColumn, @@ -230,32 +240,31 @@ final class TableInfo { } /** - * Returns the table name without brackets. + * Returns the table name without schema name and without quotes. */ final String unquoted() { - return table.substring(1, table.length() - 1); + return table.substring(table.indexOf('"') + 1, table.lastIndexOf('"')); } /** - * Returns {@code true} if the given table {@code name} matches the {@code expected} name. + * Returns {@code true} if the given table {@code name} matches the name expected by {@code this}. * The given {@code name} may be prefixed by {@code "epsg_"} and may contain abbreviations of the full name. * For example, {@code "epsg_coordoperation"} is considered as a match for {@code "Coordinate_Operation"}. * - * <p>The table name should be one of the values enumerated in the {@code epsg_table_name} type of the + * <p>The table name should be one of the values enumerated in the {@code epsg_table_name} types of the * {@code EPSG_Prepare.sql} file.</p> * - * @param expected the expected table name (e.g. {@code "Coordinate_Operation"}). - * @param name the actual table name. + * @param name the table name to test. * @return whether the given {@code name} is considered to match the expected name. */ - static boolean tableMatches(final String expected, String name) { + final boolean tableMatches(String name) { if (name == null) { return false; } if (name.startsWith(SQLTranslator.TABLE_PREFIX)) { name = name.substring(SQLTranslator.TABLE_PREFIX.length()); } - return CharSequences.isAcronymForWords(name, expected); + return CharSequences.isAcronymForWords(name, unquoted()); } /** @@ -288,7 +297,7 @@ final class TableInfo { * WHERE COORD_REF_SYS_KIND LIKE 'geographic%' AND * } * - * In any case, the caller shall add at least one condition after this method call. + * The caller shall add at least one condition after this method call. * * @param userType the type specified by the user. * @param buffer where to append the {@code WHERE} clause. diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/package-info.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/package-info.java index 5bf31094cb..bde70bffc3 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/package-info.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/package-info.java @@ -24,8 +24,8 @@ * * * <h2>Connection to the database</h2> - * By default Apache SIS uses Apache Derby, - * but the database can also be PostgreSQL or MS-Access. + * Apache SIS uses the Apache Derby database by default. However, connection to the <abbr>EPSG</abbr> + * dataset should work with any <abbr>SQL</abbr> compliant databases (tested on PostgreSQL and HSQL). * The database connection is obtained by the first of the following data sources which is found: * * <ol> diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/internal/SignReversalComment.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/internal/SignReversalComment.java index 68d88099d6..d69cbba856 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/internal/SignReversalComment.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/internal/SignReversalComment.java @@ -24,8 +24,8 @@ import org.apache.sis.util.AbstractInternationalString; /** * Comments telling whether a parameter value uses the same sign or the opposite sign for the inverse operation. - * Those comments are used for encoding the {@code PARAM_SIGN_REVERSAL} boolean value in the - * {@code [Coordinate_Operation Parameter Usage]} table of the EPSG dataset. + * Those comments are used for encoding the {@code PARAM_SIGN_REVERSAL} Boolean value in the + * {@code "Coordinate_Operation Parameter Usage"} table of the <abbr>EPSG</abbr> dataset. * * <p>This approach may change in any future SIS version.</p> * diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/operation/provider/AbstractProvider.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/operation/provider/AbstractProvider.java index 1d1dfa0c5b..5605224192 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/operation/provider/AbstractProvider.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/operation/provider/AbstractProvider.java @@ -327,12 +327,12 @@ public abstract class AbstractProvider extends DefaultOperationMethod implements * This is different than the EPSG dataset in two ways: * * <ul class="verbose"> - * <li>EPSG provides an equivalent information in the {@code PARAM_SIGN_REVERSAL} column of the - * {@code [Coordinate_Operation Parameter Usage]} table, but on a parameter-by-parameter basis + * <li><abbr>EPSG</abbr> provides an equivalent information in the {@code PARAM_SIGN_REVERSAL} column + * of the {@code "Coordinate_Operation Parameter Usage"} table, but on a parameter-by-parameter basis * instead of for the whole operation (which is probably better).</li> * - * <li>EPSG provides another information in the {@code REVERSE_OP} column of the - * {@code [Coordinate_Operation Method]} table, but this is not equivalent to this method because it + * <li><abbr>EPSG</abbr> provides another information in the {@code REVERSE_OP} column of the + * {@code "Coordinate_Operation Method"} table, but this is not equivalent to this method because it * does not differentiate the map projection methods from <em>inverse</em> map projection methods.</li> * </ul> * diff --git a/endorsed/src/org.apache.sis.referencing/test/org/apache/sis/referencing/factory/sql/EPSGInstallerTest.java b/endorsed/src/org.apache.sis.referencing/test/org/apache/sis/referencing/factory/sql/EPSGInstallerTest.java index 9579346e50..6926b7096b 100644 --- a/endorsed/src/org.apache.sis.referencing/test/org/apache/sis/referencing/factory/sql/EPSGInstallerTest.java +++ b/endorsed/src/org.apache.sis.referencing/test/org/apache/sis/referencing/factory/sql/EPSGInstallerTest.java @@ -16,7 +16,6 @@ */ package org.apache.sis.referencing.factory.sql; -import java.util.Map; import java.util.Set; import java.util.HashMap; import java.util.HashSet; @@ -87,7 +86,7 @@ public final class EPSGInstallerTest extends TestCaseWithLogs { "UPDATE epsg_datum\n" + "SET datum_name = replace(datum_name, CHAR(182), CHAR(10))")); - // Modified statement with MS-Access table name in a schema. + // Modified statement with mixed-case table name in a schema. assertTrue(Pattern.matches(EPSGInstaller.REPLACE_STATEMENT, "UPDATE epsg.\"Alias\"\n" + "SET object_table_name = replace(object_table_name, CHR(182), CHR(10))")); @@ -199,7 +198,7 @@ public final class EPSGInstallerTest extends TestCaseWithLogs { private void createAndTest(final DataSource ds, final InstallationScriptProvider scriptProvider) throws SQLException, FactoryException { - final Map<String,Object> properties = new HashMap<>(); + final var properties = new HashMap<String,Object>(); assertNull(properties.put("dataSource", ds)); assertNull(properties.put("scriptProvider", scriptProvider)); assertEquals(0, countCRSTables(ds), "Should not contain EPSG tables before we created them."); diff --git a/endorsed/src/org.apache.sis.referencing/test/org/apache/sis/referencing/factory/sql/TableInfoTest.java b/endorsed/src/org.apache.sis.referencing/test/org/apache/sis/referencing/factory/sql/TableInfoTest.java index 2094ed22fa..259ec10a91 100644 --- a/endorsed/src/org.apache.sis.referencing/test/org/apache/sis/referencing/factory/sql/TableInfoTest.java +++ b/endorsed/src/org.apache.sis.referencing/test/org/apache/sis/referencing/factory/sql/TableInfoTest.java @@ -35,11 +35,12 @@ public final class TableInfoTest extends TestCase { } /** - * Tests {@link TableInfo#tableMatches(String, String)}. + * Tests {@link TableInfo#tableMatches(String)}. */ @Test public void testTableMatches() { - assertTrue(TableInfo.tableMatches("Coordinate_Operation", "epsg_coordoperation")); - assertTrue(TableInfo.tableMatches("[Coordinate Reference System]", "epsg_coordinatereferencesystem")); + assertTrue(TableInfo.DATUM .tableMatches("epsg_datum")); + assertTrue(TableInfo.ELLIPSOID.tableMatches("epsg_ellipsoid")); + assertTrue(TableInfo.CRS .tableMatches("epsg_coordinatereferencesystem")); } } diff --git a/endorsed/src/org.apache.sis.util/test/org/apache/sis/util/CharSequencesTest.java b/endorsed/src/org.apache.sis.util/test/org/apache/sis/util/CharSequencesTest.java index b019636934..baf59f7d9c 100644 --- a/endorsed/src/org.apache.sis.util/test/org/apache/sis/util/CharSequencesTest.java +++ b/endorsed/src/org.apache.sis.util/test/org/apache/sis/util/CharSequencesTest.java @@ -343,36 +343,37 @@ public final class CharSequencesTest extends TestCase { assertFalse(isAcronymForWords("GC", "Open Geospatial Consortium")); assertFalse(isAcronymForWords("ENE", "NORTH_EAST")); /* - * Following are mapping of EPSG table names from MS-Access to ANSI SQL. - * All those items must be recognized as acroynms - this is requred by EPSGDataAccess. + * Following are mapping of table names between two variants of EPSG dataset. + * All those items shoud be recognized as acroynms, as this mapping is used + * by `EPSGDataAccess` for logging purposes. */ - assertTrue(isAcronymForWords("alias", "[Alias]")); - assertTrue(isAcronymForWords("area", "[Area]")); - assertTrue(isAcronymForWords("coordinateaxis", "[Coordinate Axis]")); - assertTrue(isAcronymForWords("coordinateaxisname", "[Coordinate Axis Name]")); - assertTrue(isAcronymForWords("coordoperation", "[Coordinate_Operation]")); - assertTrue(isAcronymForWords("coordoperationmethod", "[Coordinate_Operation Method]")); - assertTrue(isAcronymForWords("coordoperationparam", "[Coordinate_Operation Parameter]")); - assertTrue(isAcronymForWords("coordoperationparamusage", "[Coordinate_Operation Parameter Usage]")); - assertTrue(isAcronymForWords("coordoperationparamvalue", "[Coordinate_Operation Parameter Value]")); - assertTrue(isAcronymForWords("coordoperationpath", "[Coordinate_Operation Path]")); - assertTrue(isAcronymForWords("coordinatereferencesystem", "[Coordinate Reference System]")); - assertTrue(isAcronymForWords("coordinatesystem", "[Coordinate System]")); - assertTrue(isAcronymForWords("datum", "[Datum]")); - assertTrue(isAcronymForWords("ellipsoid", "[Ellipsoid]")); - assertTrue(isAcronymForWords("namingsystem", "[Naming System]")); - assertTrue(isAcronymForWords("primemeridian", "[Prime Meridian]")); - assertTrue(isAcronymForWords("supersession", "[Supersession]")); - assertTrue(isAcronymForWords("unitofmeasure", "[Unit of Measure]")); - assertTrue(isAcronymForWords("versionhistory", "[Version History]")); - assertTrue(isAcronymForWords("change", "[Change]")); - assertTrue(isAcronymForWords("deprecation", "[Deprecation]")); - assertFalse(isAcronymForWords(null, "[Deprecation]")); + assertTrue(isAcronymForWords("alias", "Alias")); + assertTrue(isAcronymForWords("area", "Area")); + assertTrue(isAcronymForWords("change", "Change")); + assertTrue(isAcronymForWords("coordinateaxis", "Coordinate Axis")); + assertTrue(isAcronymForWords("coordinateaxisname", "Coordinate Axis Name")); + assertTrue(isAcronymForWords("coordoperation", "Coordinate_Operation")); + assertTrue(isAcronymForWords("coordoperationmethod", "Coordinate_Operation Method")); + assertTrue(isAcronymForWords("coordoperationparam", "Coordinate_Operation Parameter")); + assertTrue(isAcronymForWords("coordoperationparamusage", "Coordinate_Operation Parameter Usage")); + assertTrue(isAcronymForWords("coordoperationparamvalue", "Coordinate_Operation Parameter Value")); + assertTrue(isAcronymForWords("coordoperationpath", "Coordinate_Operation Path")); + assertTrue(isAcronymForWords("coordinatereferencesystem", "Coordinate Reference System")); + assertTrue(isAcronymForWords("coordinatesystem", "Coordinate System")); + assertTrue(isAcronymForWords("datum", "Datum")); + assertTrue(isAcronymForWords("deprecation", "Deprecation")); + assertTrue(isAcronymForWords("ellipsoid", "Ellipsoid")); + assertTrue(isAcronymForWords("namingsystem", "Naming System")); + assertTrue(isAcronymForWords("primemeridian", "Prime Meridian")); + assertTrue(isAcronymForWords("supersession", "Supersession")); + assertTrue(isAcronymForWords("unitofmeasure", "Unit of Measure")); + assertTrue(isAcronymForWords("versionhistory", "Version History")); + assertFalse(isAcronymForWords(null, "Deprecation")); /* - * It is important the following is not recognized as an acronym, - * otherwise it leads to a confusion in EPSGDataAccess. + * It is important that the following is not recognized as an acronym, + * otherwise it leads to a confusion in `EPSGDataAccess`. */ - assertFalse(isAcronymForWords("coordoperation", "[Coordinate_Operation Method]")); + assertFalse(isAcronymForWords("coordoperation", "Coordinate_Operation Method")); } /**
