lbitonti/liquibase-hana

Liquibase Hana extension

liquibase-hana's Issues

Generated SQL statement for GetViewDefinitionStatement does not consider Schema

Table VIEWS of SYS schema holds View Definitions. We can have the same view in multiple schemas.

Liquibase Core expects exaclty one result entry from the View Exists sql statement.

As this code does not consider the Schema, an error is raised, in case the same view really exists in multiple schemas, and is therefore returned as multiple instances as part of hte result set.

Proposal: Add Schema to the select:
Current Code:
new UnparsedSql("SELECT DEFINITION FROM VIEWS WHERE upper(VIEW_NAME)='" + statement.getViewName().toUpperCase() + "'")

Proposal:
new UnparsedSql("SELECT DEFINITION FROM VIEWS WHERE upper(SCHEMA_NAME) = '" + statement.getSchemaName().toUpperCase() + "' AND upper(VIEW_NAME) = '" + statement.getViewName().toUpperCase() + "'")

Definition of VIEWS Table in HANA
https://help.sap.com/doc/4fe29514fd584807ac9f2a04f6754767/2.0.02/en-US/2102bf28751910149d0a817a65699956.html

Code:
https://github.com/lbitonti/liquibase-hana/blob/master/src/main/java/liquibase/sqlgenerator/ext/GetViewDefinitionGeneratorHanaDB.java

diff not displaying differences

Hi,
While comparing two schemas in my HANA db using the generateChangeLog command line statement, I get the no differences. When I set the log level to debug, I get the following information

liquibase: Did not find catalog 'DEFAULT' to snapshot liquibase: Did not find schema 'SomeSchema' to snapshot
similar message is displayed for the other schema

And no differences are displayed
Product Name: EQUAL
Product Version: EQUAL
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s): NONE
Missing Column(s): NONE
Unexpected Column(s): NONE
Changed Column(s): NONE
Missing Foreign Key(s): NONE
Unexpected Foreign Key(s): NONE
Changed Foreign Key(s): NONE
Missing Index(s): NONE
Unexpected Index(s): NONE
Changed Index(s): NONE
Missing Primary Key(s): NONE
Unexpected Primary Key(s): NONE
Changed Primary Key(s): NONE
Missing Schema(s): NONE
Unexpected Schema(s): NONE
Changed Schema(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Stored Procedure(s): NONE
Unexpected Stored Procedure(s): NONE
Changed Stored Procedure(s): NONE
Missing Table(s): NONE
Unexpected Table(s): NONE
Changed Table(s): NONE
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
Missing View(s): NONE
Unexpected View(s): NONE
Changed View(s): NONE
Liquibase 'diff' Successful

This is despite both the schemas are starkly different. After analyzing the differences, I want to generate the changelog to be created. As no differences are found, changelog is not generated.

The command used is

liquibase --driver=com.sap.db.jdbc.Driver --classpath=ngdbc.jar --classpath=liquibase-hana.jar --url=jdbc:sap://localhost:30015/ --username=MyUserName --password=MyPassword --logLevel=debug ---defaultSchemaName=FirstSchema diff --referenceUrl=jdbc:sap://localhost:30115/ --referenceUsername=OtherUserName --referencePassword=OtherPassWord --referenceDefaultSchemaName=RefSchemaName

Thanks,
Abdullah

DB-Identifiers are always quoted and are thus case sensitive

We use Liquibase to create tables used with Hibernate and plain SQL statements.

Currently we use lower case or mixed case identifiers for table/column/sequence names.

This works with OracleDatabase where the DB converts all unquoted identifiers to uppercase names, both on creation with Liquibase and later on access via Hibernate or plain SQL.

It also works with MSSQLDatabase where the (always) quoted identifiers are matched case insensitively in queries.

In HanaDBDatabase you always add quotes to the identifiers (Liquibase 3.x: QUOTE_ALL_OBJECTS; Liquibase 2.x: escapeDatabaseObject) which causes tables/columns/sequences to be created in the unchanged writing (not uppercased by the HANA DB).
When using the same identifiers within Hibernate or plain SQL statements without quotes, they are converted to uppercase and do not match.

Our workaround was to implement HanaDBDatabase#escapeDatabaseObject (we are still using Liquibase 2.x) the same way as for OracleDatabase, causing quotes to be only added for non-word characters or reserved words (which we don't use).
Thus it works the same way as with Oracle Databases (auto-uppercase both by Liquibase and on use in Hibernate and SQL statements).

Question What is the relationship of this vs the "official" extension?

I wonder what the relationship of this repository is with the "official" Liquibase extension (https://github.com/liquibase/liquibase-hanadb). As far as I understand Liquibase took an earlier version of this code and has refactored it meanwhile for newer releases of Liquibase. So the pull request #3 would not apply cleanly there.

Is there a plan to contribute the much needed improvements from here to the Liquibase repository again?

Kind regards,
Christian.

DB-Connection is set to autocommit mode which breaks manual commit

We use Liquibase together with Hibernate transaction handling in an application. Hibernate expects DB connections to be not in autocommit mode.

liquibase.database.ext.HanaDBDatabase#supportsDDLInTransaction returns false
which causes getAutoCommitMode() of the base class to return true, used in setConnection(DatabaseConnection) where connection.setAutoCommit is set on the DatabaseConnection - true in case of the HanaDB.
When the same DB-connection is used by Hibernate and explicitly committed at
the end of a transaction, the Hana JDBC driver throws an exception:
com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: Connection is currently in auto commit mode.

Our workaround was to override HanaDBDatabase#getAutoCommitMode() to return false.

Possilby supportsDDLInTransaction might return true (like for Oracle and MS-SQL).
You refer to http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/fdf9cb75191014b85aaa9dec841291/content.htm

However there is also a SET TRANSACTION AUTOCOMMIT DDL statement which claims that rollbacks of DDL statements are supported.
https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.00/en-US/d538d11053bd4f3f847ec5ce817a3d4c.html

Not sure, which one is true, and where the SET TRANSACTION AUTOCOMMIT DDL ON had to be placed.
Also not sure, which side effects the return false by HanaDBDatabase#getAutoCommitMode() might cause.

Rename of DATABASECHANGELOG doesn't work

If you run

liquibase .... --databaseChangeLogTableName=com.sap.test::DatabaseChangeLog

Two database change log tables are created:

• com.sap.test::DatabaseChangeLog
• DATABASECHANGELOG

The migration failes because com.sap.test::DatabaseChangeLog remains empty.

Error on default schema

When running this extension against Liquibase 3.2.2 I get the following error message:

INFO 9/25/14 10:58 AM: liquibase: Error getting default schema
liquibase.exception.DatabaseException: Error executing SQL call current_schema: SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "current_schema": line 1 col 6 (at pos 6)

---

This could be fixed by either overriding getConnectionSchemaName or getDefaultSchemaName (which I used to just return "SYSTEM" for our system)

You could try doing something like:

    @Override
    protected String getConnectionSchemaName() {
        if (getConnection() == null || getConnection() instanceof OfflineConnection) {
            return null;
        }
        try {
            return ExecutorService.getInstance().getExecutor(this).queryForObject(new RawSqlStatement("select current_schema from dummy"), String.class);
        } catch (Exception e) {
            LogFactory.getLogger().info("Error getting default schema", e);
        }
        return null;
    }

but this requires modifying the Liquibase dependency from 3.0 to a newer version which ends up breaking some other parts of the code (as the DataTypeFactory.getInstance().fromDescription method changed to require a second parameter, the database).

Support for VARBINARY data type

Hi Luigi, Hi Matthew,

can I get write access to the repository so that I can add the VARBINARY data type support? I have wwritten a liquibase.datatype.ext.VarbinaryTypeHanaDB and changed liquibase.datatype.ext.BlobTypeHanaDB to ignore VARBINARYs

Support of replaceIfExists for CreateView

We use CreateView with replaceIfExists="true" to change the definition of an existent view.
Also by directly creating a CreateViewChange which does not exclude Hana support in its supportsReplaceIfExistsOption (because it does not know about it).

For MS-SQL the CreateViewGenerator creates some SQL that checks for existence of the view with a top level IF NOT EXISTS which I have not found for Hana.
http://stackoverflow.com/questions/163246/sql-server-equivalent-to-oracles-create-or-replace-view

Hana supports an ALTER VIEW in addition to CREATE VIEW and DROP VIEW, however the ALTER VIEW requires an existent view, whereas replaceIfExists should both work when the view exists (replacing it) or not (creating it).

The simplest way for replaceIfExists I have found, was to do a DROP VIEW in case of CREATE VIEW (like done for MS-SQL in Liquibase 2.x); this will log an error if the view did not exist, but still create it afterwards.

Changes in CreateViewGeneratorHanaDB.java:

• validate: Comment out
  if (createViewStatement.isReplaceIfExists()) { validationErrors.checkDisallowedField("replaceIfExists", ...); }

• generateSql:
  String viewName = database.escapeViewName(statement.getSchemaName(), statement.getViewName());
  if (statement.isReplaceIfExists()) {
  sql.add(new UnparsedSql("DROP VIEW " + viewName));
  }
  sql.add(new UnparsedSql("CREATE VIEW " + viewName +
  " AS " + statement.getSelectQuery()));

Error Liquibase hanadb

Hello,

i get the following error.
Can you please help me?

ljs_output_9ed34e1_2017-07-18.txt

Thanks and Kind Regards,
Christoph

Incorect syntax near onDelete, onUpdate

Hi!
When I try apply changeSet - addForeignKeyConstraint I get error

SAP DBTech JDBC: [257] (at 140): sql syntax error: incorrect syntax near "NO": line 1 col 140 (at pos 140)
Its lines:

onDelete="NO ACTION"
onUpdate="NO ACTION"

I also have tryed :

onDelete='NO ACTION'
onUpdate='NO ACTION'

but its work with single word parameter like "CASCADE"

However I set nothing

onDelete=""
onUpdate=""

It returns me error

Value '' is not facet-valid with respect to enumeration '[CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION]'. It must be a value from the enumeration.

So, parameter "NO ACTION" exists in options.

How can I connect HANADB with liquibase?

Hi everyone,

I use this JDBC url to connect to HANADB but it returns to failure.And I don't know how can I select my schema.

Could you please me help me out?Or if you can give me some examples.