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 `PreparedStatement` 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
Element | Name 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 Summary

  Constructors
  Constructor
  Description
  `SQLTranslator(DatabaseMetaData md, String catalog, String schema)`
  Creates a new SQL translator for the database described by the given metadata.

- ## Method Summary

  Modifier and Type
  Method
  Description
  `String`
  `apply(String sql)`
  Adapts the given SQL statement from the original MS-Access dialect to the dialect of the target database.
  `String`
  `getCatalog()`
  Returns the catalog that contains the EPSG schema.
  `String`
  `getSchema()`
  Returns the schema that contains the EPSG tables.

  ### Methods inherited from class Object

  `clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait`

  ### Methods inherited from interface Function

  `andThen, compose`

- ## 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.