How to add reserved keywords in liquibase OracleDatabase?-CodePudding

Trying to make my spring boot JPA application compliant with Oracle DB, already running with MySQL and H2. The data generated by liquibase unfortunately uses some of Oracle's reserved keywords as table or column names.

The good news is that hibernate and liquibase implementations can detect these keywords and "quote" them when querying database (using objectQuotingStrategy="QUOTE_ONLY_RESERVED_KEYWORDS" for liquibase, and spring.jpa.properties.hibernate.auto_quote_keyword: true for hibernate). The bad news is hibernate and liquibase do not share the same list of reserved keywords for Oracle.

For example, value is not recognized as a reserved keyword by liquibase, but is by hibernate (which uses ANSI SQL:2003 keywords). One of my liquibase changeSets creates a table with a lower case value column, so Liquibase creates the table with an unquoted lowercase value column, and Oracle DB turns it automatically in an upper case VALUE column. Now when hibernate tries to fetch that column, it recognizes value and quotes it (`SELECT "value" from ...), which makes it case-sensitive, so the column is not found (ORA-00904).

I thought I found a workaround for it by extending SpringLiquibase and adding my custom keywords, as described here : https://liquibase.jira.com/browse/CORE-3324. The problem is that this does not seem to work with OracleDatabase implementation, which overwrites SpringLiquibase's set of reserved keywords (and of course, the isReservedWord() method uses OracleDatabase's set).

For now, I'll use the QUOTE_ALL_OBJECTS quoting strategy for liquibase and hibernate.globally_quoted_identifiers.

But, just out of curiosity, I wanted to know if the set of reserved keywords used by liquibase for Oracle could be appended.

spring boot version: 2.3.9.RELEASE.
hibernate-core version (spring boot dependency): 5.4.28
liquibase-core version (spring boot dependency): 3.8.9

CodePudding user response：

Hmm in case of Oracle you have keywords and reserved words.

reserved words can not be used as identifiers
keywords can be used as identifiers but it is not recommened.

You can get list of them directly from database:

select KEYWORD, RESERVED from v$reserved_words;
...
1864 rows selected

What about using uppercase names everywhere in the source code?

It looks like Liqubase depends on some JDBC driver functionally - which does not work.

OracleDatabase.java:

public void setConnection(DatabaseConnection conn) {
    //noinspection HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral,
    // HardCodedStringLiteral
    reservedWords.addAll(Arrays.asList("GROUP", "USER", "SESSION", "PASSWORD", "RESOURCE", "START", "SIZE", "UID", "DESC", "ORDER")); //more reserved words not returned by driver

    Connection sqlConn = null;
    if (!(conn instanceof OfflineConnection)) {
        try {
            /*
             * Don't try to call getWrappedConnection if the conn instance is
             * is not a JdbcConnection. This happens for OfflineConnection.
             * see https://liquibase.jira.com/browse/CORE-2192
             */
            if (conn instanceof JdbcConnection) {
                sqlConn = ((JdbcConnection) conn).getWrappedConnection();
            }
        } catch (Exception e) {
            throw new UnexpectedLiquibaseException(e);
        }

        if (sqlConn != null) {
            tryProxySession(conn.getURL(), sqlConn);

            try {
                //noinspection HardCodedStringLiteral
                reservedWords.addAll(Arrays.asList(sqlConn.getMetaData().getSQLKeywords().toUpperCase().split(",\\s*")));
            } catch (SQLException e) {
                //noinspection HardCodedStringLiteral
                Scope.getCurrentScope().getLog(getClass()).info("Could get sql keywords on OracleDatabase: "   e.getMessage());
                //can not get keywords. Continue on
            }

If Liquibase calls sqlConn.getMetaData().getSQLKeywords() and this does not return proper output, then your chances are limited. It might be a bug in JDBC drivers, or your application does not have SELECT_CATALOG_ROLE privilege and does not see v$reserved_words view (if JDBC queries this internally).