Class SQLTranslator

Object
SQLTranslator
All Implemented Interfaces:
Function<String,String>, Unary­Operator<String>
public class SQLTranslator extends Object implements UnaryOperator<String>
Translator of SQL statements for variations in schema, table and column names. The apply(String) method is invoked when a new Prepared­Statement is about to be created from a SQL string. That method can modify the string before execution. For example, the following SQL query:
  • SELECT * FROM "Coordinate Reference System"
can be translated to one of the following possibilities:
  • SELECT * FROM "Coordinate Reference System" (no change)
  • SELECT * FROM Coordinate­Reference­System (without quote, so the case will be database-dependent)
  • SELECT * FROM epsg_Coordinate­Reference­System (same as above with "epsg_" prefix added)
Some differences are the lower or camel case, the spaces between words and the "epsg_" prefix. Those differences exist because the EPSG database is distributed in two family of formats: as an MS-Access file and as SQL scripts, and those two families use different table names. In addition to format-dependent differences, there is also changes in the database schema between some versions of the EPSG dataset.

Table naming convention

For readability reasons, Apache SIS generally uses the naming convention found in the MS-Access database, except for the cases shown in the "Name in Apache SIS" column of the table below. The SQL statements given to the apply(String) method use the latter. The following table gives the mapping between the naming conventions:
Mapping of table names
Name in SQL scripts Name in MS-Access database Name in Apache SIS
epsg_alias Alias ″
epsg_area Area ″
epsg_change Change ″
epsg_conventionalrs ConventionalRS Conventional RS
epsg_coordinateaxis Coordinate Axis ″
epsg_coordinateaxisname Coordinate Axis Name ″
epsg_coordoperation Coordinate_Operation ″
epsg_coordoperationmethod Coordinate_Operation Method ″
epsg_coordoperationparam Coordinate_Operation Parameter ″
epsg_coordoperationparamusage Coordinate_Operation Parameter Usage″
epsg_coordoperationparamvalue Coordinate_Operation Parameter Value″
epsg_coordoperationpath Coordinate_Operation Path ″
epsg_coordinatereferencesystemCoordinate Reference System ″
epsg_coordinatesystem Coordinate System ″
epsg_datum Datum ″
epsg_datumensemble DatumEnsemble Datum Ensemble
epsg_datumensemblemember DatumEnsembleMember Datum Ensemble Member
epsg_datumrealizationmethod DatumRealizationMethod Datum Realization Method
epsg_definingoperation DefiningOperation Defining Operation
epsg_deprecation Deprecation ″
epsg_ellipsoid Ellipsoid ″
epsg_extent Extent ″
epsg_namingsystem Naming System ″
epsg_primemeridian Prime Meridian ″
epsg_scope Scope ″
epsg_supersession Supersession ″
epsg_unitofmeasure Unit of Measure ″
epsg_versionhistory Version History ″
epsg_usage Usage ″
Columns have the same name in all formats, with only one exception: for avoiding confusion with the SQL keyword of the same name, the ORDER column in MS-Access has been renamed coord_axis_order in SQL scripts. Apache SIS uses the latter.

Apache SIS automatically detects which name convention is used, regardless the database engine. For example, it is legal to use the mixed-case variant in a PostgreSQL database even if EPSG distributes the PostgreSQL scripts in lower-case. The "epsg_" prefix is redundant with database schema and can be omitted. SQLTranslator automatically detects which database schema contains the EPSG tables.

Versions of EPSG schema

Apache SIS assumes an EPSG database schema version 10 or latter. If EPSGFactory is connected to the EPSG version 9 database, then this SQLTranslator class will modify the SQL statements on-the-fly.

Thread safety

All SQLTranslator instances given to the EPSGFactory constructor shall be immutable and thread-safe.
Since:
0.7

  • Constructor Details

    • SQLTranslator

      public SQLTranslator(DatabaseMetaData md, String catalog, String schema) throws SQLException
      Creates a new SQL 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 EPSG table names uses the mixed-case or lower-case convention.

      If the given catalog or schema name is non-null, then the search for EPSG tables will be restricted to the catalog or schema of that name. An empty string ("") means to search for tables without catalog or schema. A null value means that the catalog or schema should not be used to narrow the search.

      Parameters:
      md - information about the database.
      catalog - the catalog where to look for EPSG schema, or null if any.
      schema - the schema where to look for EPSG tables, or null if any.
      Throws:
      SQLException - if an error occurred while querying the database metadata.

  • Method Details

    • getCatalog

      @OptionalCandidate public String getCatalog()
      Returns the catalog that contains the EPSG schema. This is the catalog specified at construction time if it was non-null, or the catalog discovered by the constructor otherwise. Note that this method may still return null if the EPSG tables were not found or if the database does not supports catalogs.
      Returns:
      the catalog that contains the EPSG schema, or null.

    • getSchema

      @OptionalCandidate public String getSchema()
      Returns the schema that contains the EPSG tables. This is the schema specified at construction time if it was non-null, or the schema discovered by the constructor otherwise. Note that this method may still return null if the EPSG tables were not found or if the database does not supports schemas.
      Returns:
      the schema that contains the EPSG tables, or null.

    • toActualTableName

      public final String toActualTableName(String name)
      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 returned string does not include the identifier quotes.
      Parameters:
      name - the mixed-case table name, without quotes.
      Returns:
      the name converted to the convention used by the database.
      Since:
      1.5

    • apply

      public String apply(String sql)
      Adapts the given SQL statement from the mixed-case convention to the convention used by the target database. The mixed-case convention is used by EPSGData­Access hard-coded queries. This method can replace the schema and table names, and sometime some SQL keywords, for complying with the expectation of the target database.
      Specified by:
      apply in interface Function<String,String>
      Parameters:
      sql - a SQL statement with table names in mixed-case convention.
      Returns:
      the given statement adapted to the expectation of the target database.