# Class SQLTranslator

Object
SQLTranslator

All Implemented Interfaces:
  `Function<String,String>`, `UnaryOperator<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 `PreparedStatement` 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 CoordinateReferenceSystem` (without quote, so the case will be database-dependent)
- `SELECT * FROM epsg_CoordinateReferenceSystem` (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_coordinatereferencesystem` | `Coordinate 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 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 mixed-case convention to the convention used by the target database.
  `String`
  `getCatalog()`
  Returns the catalog that contains the EPSG schema.
  `String`
  `getSchema()`
  Returns the schema that contains the EPSG tables.
  `final String`
  `toActualTableName(String name)`
  Converts a mixed-case table name to the convention used in the database.

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