Package adql.translator

## Class JDBCTranslator

- java.lang.Object
- - adql.translator.JDBCTranslator

- All Implemented Interfaces:
    `ADQLTranslator`
  Direct Known Subclasses:
    `MySQLTranslator`, `PostgreSQLTranslator`, `SQLServerTranslator`

      public abstract class JDBCTranslator
      extends java.lang.Object
      implements ADQLTranslator

  Implementation of `ADQLTranslator` which translates ADQL queries in SQL queries.

  It is already able to translate all SQL standard features, but lets abstract the translation of all geometrical functions. So, this translator must be extended as `PostgreSQLTranslator`, `PgSphereTranslator`, `MySQLTranslator` and `SQLServerTranslator` are doing.

  Note: Its default implementation of the SQL syntax has been inspired by the PostgreSQL one. However, it should work also with other DBMS, although some translations might be needed (as it is has been done for PostgreSQL about the mathematical functions).

  ### SQL with or without case sensitivity?

  In ADQL and in SQL, it is possible to tell the parser to respect the exact case or not of an identifier (schema, table or column name) by surrounding it with double quotes. However ADQL identifiers and SQL ones may be different. In that way, the case sensitivity specified in ADQL on the different identifiers can not be kept in SQL. That's why this translator lets specify a general rule on which types of SQL identifier must be double quoted. This can be done by implementing the abstract function `isCaseSensitive(IdentifierField)`. The functions translating column and table names will call this function in order to surround the identifiers by double quotes or not. So, be careful if you want to override the functions translating columns and tables!

  ### Translation of "SELECT TOP"

  The default behavior of this translator is to translate the ADQL "TOP" into the SQL "LIMIT" at the end of the query. This is ok for some DBMS, but not all. So, if your DBMS does not know the "LIMIT" keyword, you should override the function translating the whole query: `translate(ADQLQuery)`. Here is its current implementation:

              StringBuffer sql = new StringBuffer(translate(query.getSelect()));
              sql.append("\nFROM ").append(translate(query.getFrom()));
              if (!query.getWhere().isEmpty())
                      sql.append('\n').append(translate(query.getWhere()));
              if (!query.getGroupBy().isEmpty())
                      sql.append('\n').append(translate(query.getGroupBy()));
              if (!query.getHaving().isEmpty())
                      sql.append('\n').append(translate(query.getHaving()));
              if (!query.getOrderBy().isEmpty())
                      sql.append('\n').append(translate(query.getOrderBy()));
              if (query.getSelect().hasLimit())
                      sql.append("\nLimit ").append(query.getSelect().getLimit());
              return sql.toString();

  ### Translation of ADQL functions

  All ADQL functions are by default not translated. Consequently, the SQL translation is actually the ADQL expression. Generally the ADQL expression is generic enough. However some mathematical functions may need to be translated differently. For instance `PostgreSQLTranslator` is translating differently: LOG, LOG10, RAND and TRUNC.

  Note: Geometrical regions and types have not been managed here. They stay abstract because it is obviously impossible to have a generic translation and conversion ; it totally depends from the database system.

  ### Translation of "FROM" with JOINs

  The FROM clause is translated into SQL as written in ADQL. There is no differences except the identifiers that are replaced. The tables' aliases and their case sensitivity are kept like in ADQL.

  Since:
    1.4
  Version:
    1.5 (03/2019)
  Author:
    Grégory Mantelet (ARI;CDS)
  See Also:
    `PostgreSQLTranslator`, `PgSphereTranslator`, `MySQLTranslator`, `SQLServerTranslator`

- - ### Constructor Summary

    Constructors
    Constructor | Description
    --- | ---
    `JDBCTranslator()` |

  - ### Method Summary

    All Methods Instance Methods Abstract Methods Concrete Methods
    Modifier and Type | Method | Description
    --- | --- | ---
    `java.lang.StringBuffer` | `appendIdentifier​(java.lang.StringBuffer str, java.lang.String id, boolean caseSensitive)` | Appends the given identifier to the given StringBuffer.
    `java.lang.StringBuffer` | `appendIdentifier​(java.lang.StringBuffer str, java.lang.String id, IdentifierField field)` | Appends the given identifier in the given StringBuffer.
    `abstract DBType` | `convertTypeFromDB​(int dbmsType, java.lang.String rawDbmsTypeName, java.lang.String dbmsTypeName, java.lang.String[] typeParams)` | Convert any type provided by a JDBC driver into a type understandable by the ADQL/TAP library.
    `abstract java.lang.String` | `convertTypeToDB​(DBType type)` | Convert any type provided by the ADQL/TAP library into a type understandable by a JDBC driver.
    `java.lang.String` | `getColumnName​(DBColumn column)` | Get the DB name of the given column
    `protected java.lang.String` | `getDefaultADQLFunction​(ADQLFunction fct)` | Gets the default SQL output for the given ADQL function.
    `protected java.lang.String` | `getDefaultADQLList​(ADQLList<? extends ADQLObject> list)` | Gets the default SQL output for a list of ADQL objects.
    `protected java.lang.String` | `getDefaultADQLList​(ADQLList<? extends ADQLObject> list, boolean withNamePrefix)` | Gets the default SQL output for a list of ADQL objects.
    `protected java.lang.String` | `getDefaultColumnReference​(ColumnReference ref)` | Gets the default SQL output for a column reference.
    `java.lang.String` | `getQualifiedSchemaName​(DBTable table)` | Get the qualified DB name of the schema containing the given table.
    `java.lang.String` | `getQualifiedTableName​(DBTable table)` | Get the qualified DB name of the given table.
    `java.lang.String` | `getTableName​(DBTable table, boolean withSchema)` | Get the DB name of the given table.
    `abstract boolean` | `isCaseSensitive​(IdentifierField field)` | Tell whether the specified identifier MUST be translated so that being interpreted case sensitively or not.
    `java.lang.String` | `translate​(ADQLList<? extends ADQLObject> list)` |
    `java.lang.String` | `translate​(ADQLObject obj)` |
    `java.lang.String` | `translate​(ADQLOrder order)` |
    `java.lang.String` | `translate​(ADQLQuery query)` |
    `java.lang.String` | `translate​(ClauseConstraints clause)` |
    `java.lang.String` | `translate​(ClauseSelect clause)` |
    `java.lang.String` | `translate​(ColumnReference ref)` |
    `java.lang.String` | `translate​(ADQLConstraint cons)` |
    `java.lang.String` | `translate​(Between comp)` |
    `java.lang.String` | `translate​(Comparison comp)` |
    `java.lang.String` | `translate​(Exists exists)` |
    `java.lang.String` | `translate​(In in)` |
    `java.lang.String` | `translate​(IsNull isNull)` |
    `java.lang.String` | `translate​(NotConstraint notCons)` |
    `java.lang.String` | `translate​(ADQLJoin join)` |
    `java.lang.String` | `translate​(ADQLTable table)` |
    `java.lang.String` | `translate​(FromContent content)` |
    `java.lang.String` | `translate​(ADQLColumn column)` |
    `java.lang.String` | `translate​(ADQLOperand op)` |
    `java.lang.String` | `translate​(Concatenation concat)` |
    `java.lang.String` | `translate​(ADQLFunction fct)` |
    `java.lang.String` | `translate​(GeometryFunction fct)` |
    `java.lang.String` | `translate​(GeometryFunction.GeometryValue<? extends GeometryFunction> geomValue)` |
    `java.lang.String` | `translate​(MathFunction fct)` |
    `java.lang.String` | `translate​(SQLFunction fct)` |
    `java.lang.String` | `translate​(UserDefinedFunction fct)` |
    `java.lang.String` | `translate​(NegativeOperand negOp)` |
    `java.lang.String` | `translate​(NumericConstant numConst)` |
    `java.lang.String` | `translate​(Operation op)` |
    `java.lang.String` | `translate​(StringConstant strConst)` |
    `java.lang.String` | `translate​(WrappedOperand op)` |
    `java.lang.String` | `translate​(SelectAllColumns item)` |
    `java.lang.String` | `translate​(SelectItem item)` |
    `abstract STCS.Region` | `translateGeometryFromDB​(java.lang.Object jdbcColValue)` | Parse the given JDBC column value as a geometry object and convert it into a `STCS.Region`.
    `abstract java.lang.Object` | `translateGeometryToDB​(STCS.Region region)` | Convert the given STC region into a DB column value.

    - ### Methods inherited from class java.lang.Object

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

    - ### Methods inherited from interface adql.translator.ADQLTranslator

      `translate, translate, translate, translate, translate, translate, translate, translate, translate, translate, translate, translate`

- - ### Constructor Detail

    - #### JDBCTranslator

          public JDBCTranslator()

  - ### Method Detail

    - #### isCaseSensitive

          public abstract boolean isCaseSensitive​(IdentifierField field)

      Tell whether the specified identifier MUST be translated so that being interpreted case sensitively or not. By default, an identifier that must be translated with case sensitivity will be surrounded by double quotes. But, if this function returns FALSE, the SQL name will be written just as given in the metadata, without double quotes.

      WARNING: An `IdentifierField` object can be a SCHEMA, TABLE, COLUMN and ALIAS. However, in this translator, aliases are translated like in ADQL (so, with the same case sensitivity specification as in ADQL). So, this function will never be used to know the case sensitivity to apply to an alias. It is then useless to write a special behavior for the ALIAS value.

      Parameters:
        `field` - The identifier whose the case sensitive to apply is asked.
      Returns:
        true if the specified identifier must be translated case sensitivity, false otherwise (included if ALIAS or NULL).

    - #### getQualifiedSchemaName

          public java.lang.String getQualifiedSchemaName​(DBTable table)

      Get the qualified DB name of the schema containing the given table.

      Note: This function will, by default, add double quotes if the schema name must be case sensitive in the SQL query. This information is provided by `isCaseSensitive(IdentifierField)`.

      Parameters:
        `table` - A table of the schema whose the qualified DB name is asked.
      Returns:
        The qualified (with DB catalog name prefix if any, and with double quotes if needed) DB schema name, or an empty string if there is no schema or no DB name.

    - #### getQualifiedTableName

          public java.lang.String getQualifiedTableName​(DBTable table)

      Get the qualified DB name of the given table.

      Note: This function will, by default, add double quotes if the table name must be case sensitive in the SQL query. This information is provided by `isCaseSensitive(IdentifierField)`.

      Parameters:
        `table` - The table whose the qualified DB name is asked.
      Returns:
        The qualified (with DB catalog and schema prefix if any, and with double quotes if needed) DB table name, or an empty string if the given table is NULL or if there is no DB name.
      See Also:
        `getTableName(DBTable, boolean)`

    - #### getTableName

          public java.lang.String getTableName​(DBTable table,
                                               boolean withSchema)

      Get the DB name of the given table. The second parameter lets specify whether the table name must be prefixed by the qualified schema name or not.

      Note: This function will, by default, add double quotes if the table name must be case sensitive in the SQL query. This information is provided by `isCaseSensitive(IdentifierField)`.

      Parameters:
        `table` - The table whose the DB name is asked.
        `withSchema` - true if the qualified schema name must prefix the table name, false otherwise.
      Returns:
        The DB table name (prefixed by the qualified schema name if asked, and with double quotes if needed), or an empty string if the given table is NULL or if there is no DB name.
      Since:
        2.0

    - #### getColumnName

          public java.lang.String getColumnName​(DBColumn column)

      Get the DB name of the given column

      Note: This function will, by default, add double quotes if the column name must be case sensitive in the SQL query. This information is provided by `isCaseSensitive(IdentifierField)`.

      Caution: The given column may be NULL and in this case an empty string will be returned. But if the given column is not NULL, its DB name MUST NOT BE NULL!

      Parameters:
        `column` - The column whose the DB name is asked.
      Returns:
        The DB column name (with double quotes if needed), or an empty string if the given column is NULL.

    - #### appendIdentifier

          public final java.lang.StringBuffer appendIdentifier​(java.lang.StringBuffer str,
                                                               java.lang.String id,
                                                               IdentifierField field)

      Appends the given identifier in the given StringBuffer.

      This function just call `appendIdentifier(StringBuffer, String, boolean)` with the same 2 first parameters. The third one is the result of: ``isCaseSensitive(field)``.

      Note: In order to keep a consistent output of the `appendIdentifier(...)` functions, this function can not be overwritten ; it is just a shortcut function.

      Parameters:
        `str` - The string buffer.
        `id` - The identifier to append.
        `field` - The type of identifier (column, table, schema, catalog or alias ?).
      Returns:
        The string buffer + identifier.
      See Also:
        `appendIdentifier(StringBuffer, String, boolean)`

    - #### appendIdentifier

          public java.lang.StringBuffer appendIdentifier​(java.lang.StringBuffer str,
                                                         java.lang.String id,
                                                         boolean caseSensitive)

      Appends the given identifier to the given StringBuffer.
      Parameters:
        `str` - The string buffer.
        `id` - The identifier to append.
        `caseSensitive` - true to format the identifier so that preserving the case sensitivity, false otherwise.
      Returns:
        The string buffer + identifier.

    - #### translate

          public java.lang.String translate​(ADQLObject obj)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(ADQLQuery query)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(ADQLList<? extends ADQLObject> list)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### getDefaultADQLList

          protected final java.lang.String getDefaultADQLList​(ADQLList<? extends ADQLObject> list)
                                                       throws TranslationException

      Gets the default SQL output for a list of ADQL objects.

      Implementation note: This function just calls `getDefaultADQLList(ADQLList, boolean)` with the given list in first parameter and `true` in second one. In other words, this function always prefixes the list items by the list name.

      Parameters:
        `list` - List to format into SQL.
      Returns:
        The corresponding SQL.
      Throws:
        `TranslationException` - If there is an error during the translation.
      See Also:
        `getDefaultADQLList(ADQLList, boolean)`

    - #### getDefaultADQLList

          protected java.lang.String getDefaultADQLList​(ADQLList<? extends ADQLObject> list,
                                                        boolean withNamePrefix)
                                                 throws TranslationException

      Gets the default SQL output for a list of ADQL objects.
      Parameters:
        `list` - List to format into SQL.
        `withNamePrefix` - Prefix the list by its name or not. (e.g. 'false' for a Concatenation)
      Returns:
        The corresponding SQL.
      Throws:
        `TranslationException` - If there is an error during the translation.
      Since:
        1.4

    - #### translate

          public java.lang.String translate​(ClauseSelect clause)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(ClauseConstraints clause)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(SelectItem item)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(SelectAllColumns item)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(ColumnReference ref)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### getDefaultColumnReference

          protected java.lang.String getDefaultColumnReference​(ColumnReference ref)
                                                        throws TranslationException

      Gets the default SQL output for a column reference.
      Parameters:
        `ref` - The column reference to format into SQL.
      Returns:
        The corresponding SQL.
      Throws:
        `TranslationException` - If there is an error during the translation.

    - #### translate

          public java.lang.String translate​(ADQLOrder order)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(FromContent content)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(ADQLTable table)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(ADQLJoin join)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(ADQLOperand op)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(ADQLColumn column)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(Concatenation concat)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(NegativeOperand negOp)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(NumericConstant numConst)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(StringConstant strConst)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(WrappedOperand op)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(Operation op)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(ADQLConstraint cons)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(Comparison comp)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(Between comp)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(Exists exists)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(In in)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(IsNull isNull)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(NotConstraint notCons)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(ADQLFunction fct)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### getDefaultADQLFunction

          protected final java.lang.String getDefaultADQLFunction​(ADQLFunction fct)
                                                           throws TranslationException

      Gets the default SQL output for the given ADQL function.
      Parameters:
        `fct` - The ADQL function to format into SQL.
      Returns:
        The corresponding SQL.
      Throws:
        `TranslationException` - If there is an error during the translation.

    - #### translate

          public java.lang.String translate​(SQLFunction fct)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(MathFunction fct)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(UserDefinedFunction fct)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(GeometryFunction fct)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### translate

          public java.lang.String translate​(GeometryFunction.GeometryValue<? extends GeometryFunction> geomValue)
                                     throws TranslationException

      Specified by:
        `translate` in interface `ADQLTranslator`
      Throws:
        `TranslationException`

    - #### convertTypeFromDB

          public abstract DBType convertTypeFromDB​(int dbmsType,
                                                   java.lang.String rawDbmsTypeName,
                                                   java.lang.String dbmsTypeName,
                                                   java.lang.String[] typeParams)

      Convert any type provided by a JDBC driver into a type understandable by the ADQL/TAP library.
      Parameters:
        `dbmsType` - Type returned by a JDBC driver. Note: this value is returned by ResultSetMetadata.getColumnType(int) and correspond to a type of java.sql.Types
        `rawDbmsTypeName` - Full name of the type returned by a JDBC driver. Note: this name is returned by ResultSetMetadata.getColumnTypeName(int) ; this name may contain parameters
        `dbmsTypeName` - Name of type, without the eventual parameters. Note: this name is extracted from rawDbmsTypeName.
        `typeParams` - The eventual type parameters (e.g. char string length). Note: these parameters are extracted from rawDbmsTypeName.
      Returns:
        The corresponding ADQL/TAP type or NULL if the specified type is unknown.

    - #### convertTypeToDB

          public abstract java.lang.String convertTypeToDB​(DBType type)

      Convert any type provided by the ADQL/TAP library into a type understandable by a JDBC driver.

      Note: The returned DBMS type may contain some parameters between brackets.

      Parameters:
        `type` - The ADQL/TAP library's type to convert.
      Returns:
        The corresponding DBMS type or NULL if the specified type is unknown.

    - #### translateGeometryFromDB

          public abstract STCS.Region translateGeometryFromDB​(java.lang.Object jdbcColValue)
                                                       throws ParseException

      Parse the given JDBC column value as a geometry object and convert it into a `STCS.Region`.

      Note: Generally the returned object will be used to get its STC-S expression.

      Note: If the given column value is NULL, NULL will be returned.

      Important note: This function is called ONLY for value of columns flagged as geometries by `convertTypeFromDB(int, String, String, String[])`. So the value should always be of the expected type and format. However, if it turns out that the type is wrong and that the conversion is finally impossible, this function SHOULD throw a `ParseException`.

      Parameters:
        `jdbcColValue` - A JDBC column value (returned by ResultSet.getObject(int)).
      Returns:
        The corresponding `STCS.Region` if the given value is a geometry.
      Throws:
        `ParseException` - If the given object is not a geometrical object or can not be transformed into a `STCS.Region` object.

    - #### translateGeometryToDB

          public abstract java.lang.Object translateGeometryToDB​(STCS.Region region)
                                                          throws ParseException

      Convert the given STC region into a DB column value.

      Note: This function is used only by the UPLOAD feature, to import geometries provided as STC-S expression in a VOTable document inside a DB column.

      Note: If the given region is NULL, NULL will be returned.

      Parameters:
        `region` - The region to store in the DB.
      Returns:
        The corresponding DB column object.
      Throws:
        `ParseException` - If the given STC Region can not be converted into a DB object.