Class SQLTranslator

java.lang.Object
org.apache.sis.referencing.factory.sql.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"
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
Version:
1.0
  • Field Summary

    Fields
    Modifier and Type
    Field
    Description
    private final Map<String,String>
    Mapping from words used in the MS-Access database to words used in the ANSI versions of EPSG databases.
    private static final String[]
    The columns that may be of BOOLEAN type instead of SMALLINT.
    private String
    The name of the catalog that contains the EPSG tables, or null or an empty string.
    private static final String
    The column where VARCHAR value may need to be casted to an enumeration.
    private boolean
    Whether the table names are prefixed by "epsg_".
    private boolean
    true if one of the SENTINEL tables exist.
    private static final int
    Index of the SENTINEL element which is in mixed case.
    private final String
    The characters used for quoting identifiers, or a whitespace if none.
    private boolean
    true if this class needs to quote table names.
    private String
    The name of the schema that contains the EPSG tables, or null or an empty string.
    private static final String[]
    Table names used as "sentinel value" for detecting the presence of an EPSG database.
    (package private) static final String
    The prefix in table names.
    private String
    Non-null if the "OBJECT_TABLE_NAME" column in "Alias" table uses enumeration instead than character varying.
    private boolean
    true if the database uses the BOOLEAN type instead of SMALLINT for the show_crs, show_operation and all deprecated fields.
  • Constructor Summary

    Constructors
    Constructor
    Description
    Creates a new SQL translator for the database described by the given metadata.
  • Method Summary

    Modifier and Type
    Method
    Description
    private void
    appendIdentifier(StringBuilder buffer, String identifier)
    Appends the given identifier in the given buffer, between quotes and prefixed with the schema name.
    Adapts the given SQL statement from the original MS-Access dialect to the dialect of the target database.
    Returns the catalog that contains the EPSG schema.
    Returns the schema that contains the EPSG tables.
    private static boolean
    If true if the given string is null or empty.
    (package private) final boolean
    Returns whether the EPSG tables have been found.
    private static boolean
    replaceIfEquals(StringBuilder ansi, int pos, String expected, String replacement)
    Replaces the text at the given position in the buffer if it is equal to the expected text.
    (package private) final void
    Sets the value of all non-final fields.
    (package private) static String
    Returns the error message for the exception to throw if the EPSG tables are not found and we cannot create them.
    (package private) final boolean
    Returns true if the database uses the BOOLEAN type instead of SMALLINT for the show_crs, show_operation and all deprecated fields.

    Methods inherited from class java.lang.Object

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

    Methods inherited from interface java.util.function.Function

    andThen, compose
  • Field Details

    • SENTINEL

      private static final String[] SENTINEL
      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.
    • MIXED_CASE

      private static final int MIXED_CASE
      Index of the SENTINEL element which is in mixed case. No other element should be in mixed case.
      See Also:
    • TABLE_PREFIX

      static final String TABLE_PREFIX
      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 Also:
    • BOOLEAN_COLUMNS

      private static final String[] BOOLEAN_COLUMNS
      The columns that may be of BOOLEAN type instead of SMALLINT.
    • ENUMERATION_COLUMN

      private static final String ENUMERATION_COLUMN
      The column where VARCHAR value may need to be casted to an enumeration. With PostgreSQL, only columns in the WHERE part of the SQL statement needs an explicit cast; the columns in the SELECT part are implicitly casted.
      See Also:
    • catalog

      private String catalog
      The name of the catalog that contains the EPSG tables, or null or an empty string.
      • The "" value retrieves the EPSG schema without a catalog.
      • The null value means that the catalog name should not be used to narrow the search.

      Consider this field as final. This field is non-final only for construction convenience, or for updating after the EPSGInstaller class created the database.

      See Also:
    • schema

      private String schema
      The name of the schema that contains the EPSG tables, or null or an empty string.
      • The "" value retrieves the EPSG tables without a schema. In such case, table names are prefixed by "epsg_".
      • The null value means that the schema name should not be used to narrow the search. In such case, SQLTranslator will tries to automatically detect the schema.
      Consider this field as final. This field is non-final only for construction convenience, or for updating after the EPSGInstaller class created the database.
      See Also:
    • isPrefixed

      private boolean isPrefixed
      Whether the table names are prefixed by "epsg_". 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.
    • accessToAnsi

      private final Map<String,String> accessToAnsi
      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 in many words separated by spaces. This map does not list all tables used in EPSG schema, but only the ones that cannot be mapped by more generic code (e.g. by replacing spaces by '_').

      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.

    • quoteTableNames

      private boolean quoteTableNames
      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.

      Consider this field as final. This field is non-final only for construction convenience, or for updating after the EPSGInstaller class created the database.

    • quote

      private final String quote
      The characters used for quoting identifiers, or a whitespace if none. This information is provided by DatabaseMetaData.getIdentifierQuoteString().
    • tableNameEnum

      private String tableNameEnum
      Non-null if the "OBJECT_TABLE_NAME" column in "Alias" table uses enumeration instead than character varying. In such case, this field contains the enumeration type. If null, then then column type is VARCHAR and the cast can be omitted.
    • useBoolean

      private boolean useBoolean
      true if the database uses the BOOLEAN type instead of SMALLINT for the show_crs, show_operation and all deprecated fields.
      See Also:
    • isTableFound

      private boolean isTableFound
      true if one of the SENTINEL tables exist. If false, then EPSGInstaller needs to be run.
      See Also:
  • 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

    • setup

      final void setup(DatabaseMetaData md) throws SQLException
      Sets the value of all non-final fields. This method performs two steps:
      1. Finds 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.
      2. Fills the 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.
      Throws:
      SQLException
    • 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.
    • isTableFound

      final boolean isTableFound()
      Returns whether the EPSG tables have been found. If false, then EPSGInstaller needs to be run.
    • tableNotFound

      static String tableNotFound(Locale locale)
      Returns the error message for the exception to throw if the EPSG tables are not found and we cannot create them.
    • useBoolean

      final boolean useBoolean()
      Returns true if the database uses the BOOLEAN type instead of SMALLINT for the show_crs, show_operation and all deprecated fields.
    • isEmpty

      private static boolean isEmpty(String s)
      If true if the given string is null or empty.
    • 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.
    • appendIdentifier

      private void appendIdentifier(StringBuilder buffer, String identifier)
      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.
    • replaceIfEquals

      private static boolean replaceIfEquals(StringBuilder ansi, int pos, String expected, String replacement)
      Replaces the text at the given position in the buffer if it is equal to the expected text.