Module org.apache.sis.referencing
Package org.apache.sis.referencing.factory.sql

Class SQLTranslator

Object
SQLTranslator
All Implemented Interfaces:
Function<String,String>
public class SQLTranslator extends Object implements Function<String,String>
Converts the SQL statements from MS-Access dialect to standard SQL. The apply(String) method is invoked when a new Prepared­Statement is about to be created from a SQL string. Since the EPSG dataset 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.

Example

SQL statements for an EPSG dataset hosted on the PostgreSQL database need to have their brackets ('[' and ']') replaced by the quote character ('"') before to be sent to the database driver. Furthermore, table names may be different. So the following MS-Access query:
  • SELECT * FROM [Coordinate Reference System]
needs to be converted to one of the following possibilities for a PostgreSQL database (the reason for those multiple choices will be discussed later):
  • SELECT * FROM "Coordinate Reference System"
  • SELECT * FROM epsg_coordinatereferencesystem (in the default schema)
  • SELECT * FROM epsg​.coordinatereferencesystem (in the "epsg" schema)
  • SELECT * FROM epsg."Coordinate Reference System"

ANSI SQL

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 Data Description Language (DDL) and Data Manipulation Language (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:
Table and column names
ElementName in MS-Access database Name in SQL scripts
Table Alias epsg_alias
Table Area epsg_area
Table Coordinate Axis epsg_coordinateaxis
Table Coordinate Axis Name epsg_coordinateaxisname
Table Coordinate_Operation epsg_coordoperation
Table Coordinate_Operation Method epsg_coordoperationmethod
Table Coordinate_Operation Parameter epsg_coordoperationparam
Table Coordinate_Operation Parameter Usage epsg_coordoperationparamusage
Table Coordinate_Operation Parameter Value epsg_coordoperationparamvalue
Table Coordinate_Operation Path epsg_coordoperationpath
Table Coordinate Reference System epsg_coordinatereferencesystem
Table Coordinate System epsg_coordinatesystem
Table Datum epsg_datum
Table Ellipsoid epsg_ellipsoid
Table Naming System epsg_namingsystem
Table Prime Meridian epsg_primemeridian
Table Supersession epsg_supersession
Table Unit of Measure epsg_unitofmeasure
Column ORDER coord_axis_order
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.

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 table names are the ones used in the MS-Access database or in the SQL scripts.

      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

      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

      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.

    • apply

      public String apply(String sql)
      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.
      Specified by:
      apply in interface Function<String,String>
      Parameters:
      sql - the statement in MS-Access dialect.
      Returns:
      the SQL statement adapted to the dialect of the target database.