lbitonti / liquibase-hana Goto Github PK
View Code? Open in Web Editor NEWLiquibase Hana extension
License: Apache License 2.0
Liquibase Hana extension
License: Apache License 2.0
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
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
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).
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.
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.
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.
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).
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
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()));
Hello,
i get the following error.
Can you please help me?
ljs_output_9ed34e1_2017-07-18.txt
Thanks and Kind Regards,
Christoph
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.