Class SQLTranslator
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.
'['
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]
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"
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 |
Thread safety
AllSQLTranslator
instances given to the EPSGFactory
constructor
shall be immutable and thread-safe.- Since:
- 0.7
- Version:
- 1.0
-
Field Summary
FieldsModifier and TypeFieldDescriptionMapping 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 ofBOOLEAN
type instead ofSMALLINT
.private String
The name of the catalog that contains the EPSG tables, ornull
or an empty string.private static final String
The column whereVARCHAR
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 theSENTINEL
tables exist.private static final int
Index of theSENTINEL
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, ornull
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 theBOOLEAN
type instead ofSMALLINT
for theshow_crs
,show_operation
and alldeprecated
fields. -
Constructor Summary
ConstructorsConstructorDescriptionSQLTranslator
(DatabaseMetaData md, String catalog, String schema) Creates a new SQL translator for the database described by the given metadata. -
Method Summary
Modifier and TypeMethodDescriptionprivate 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
Iftrue
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 theexpected
text.(package private) final void
Sets the value of all non-final fields.(package private) 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.(package private) final boolean
Returnstrue
if the database uses theBOOLEAN
type instead ofSMALLINT
for theshow_crs
,show_operation
and alldeprecated
fields.
-
Field Details
-
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_CASEIndex of theSENTINEL
element which is in mixed case. No other element should be in mixed case.- See Also:
-
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
The columns that may be ofBOOLEAN
type instead ofSMALLINT
. -
ENUMERATION_COLUMN
The column whereVARCHAR
value may need to be casted to an enumeration. With PostgreSQL, only columns in theWHERE
part of the SQL statement needs an explicit cast; the columns in theSELECT
part are implicitly casted.- See Also:
-
catalog
The name of the catalog that contains the EPSG tables, ornull
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:
- The
-
schema
The name of the schema that contains the EPSG tables, ornull
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.
EPSGInstaller
class created the database.- See Also:
- The
-
isPrefixed
private boolean isPrefixedWhether 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
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 quoteTableNamestrue
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
The characters used for quoting identifiers, or a whitespace if none. This information is provided byDatabaseMetaData.getIdentifierQuoteString()
. -
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. Ifnull
, then then column type isVARCHAR
and the cast can be omitted. -
useBoolean
private boolean useBooleantrue
if the database uses theBOOLEAN
type instead ofSMALLINT
for theshow_crs
,show_operation
and alldeprecated
fields.- See Also:
-
isTableFound
private boolean isTableFound- See Also:
-
-
Constructor Details
-
SQLTranslator
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. Anull
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, ornull
if any.schema
- the schema where to look for EPSG tables, ornull
if any.- Throws:
SQLException
- if an error occurred while querying the database metadata.
-
-
Method Details
-
setup
Sets the value of all non-final fields. This method performs two steps:- 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.
- 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
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 returnnull
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
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 returnnull
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. Iffalse
, thenEPSGInstaller
needs to be run. -
tableNotFound
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()Returnstrue
if the database uses theBOOLEAN
type instead ofSMALLINT
for theshow_crs
,show_operation
and alldeprecated
fields. -
isEmpty
Iftrue
if the given string is null or empty. -
apply
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. -
appendIdentifier
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 theexpected
text.
-