Support for vFabric SQLFire database

Has anyone attempted to use Liquibase with VMware’s new vFabric SQLFire database?  It is based off of Apache Derby, and thus running liquibase against it makes liquibase use its built in Derby code.  I have attempted to use version 1.9.4 and version 2.0.5 with no success. 

1.9.4 had errors with the CHECKPOINT_DATABASE stored procedure in the Derby implementation being named differently than the one in SQLFire.

I am completely willing to research and attempt to code an extension for SQLFire, but I am not entirely sure where to start (since it appears that the Derby implementation is half working)

Here is output from 2.0.5

…/liquibase --driver=com.vmware.sqlfire.jdbc.ClientDriver --classpath=/Users/acooper/Downloads/vFabric_SQLFire_102/lib/sqlfireclient.jar --changeLogFile=mdm.changelog.xml --url=“jdbc:sqlfire://localhost:1527” --username=**** --password=**** --logLevel=DEBUG update
Liquibase Home is not set.
Liquibase Home: /Users/acooper/Downloads/liquibase-2.0.5-bin
DEBUG 5/18/12 8:30 AM:liquibase: Unable to load/access Apache Derby driver class to check version
DEBUG 5/18/12 8:30 AM:liquibase: Unable to load/access Apache Derby driver class to check version
DEBUG 5/18/12 8:30 AM:liquibase: Connected to scott@jdbc:sqlfire://localhost:1528/
DEBUG 5/18/12 8:30 AM:liquibase: Executing QUERY database command: SELECT LOCKED FROM DATABASECHANGELOGLOCK WHERE ID=1
DEBUG 5/18/12 8:30 AM:liquibase: Lock Database
DEBUG 5/18/12 8:30 AM:liquibase: Executing UPDATE database command: UPDATE DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = ‘fd00:6587:52d7:843:ca2a:14ff:fe45:b6f8 (fd00:6587:52d7:843:ca2a:14ff:fe45:b6f8)’, LOCKGRANTED = TIMESTAMP(‘2012-05-18 08:30:07.105000’) WHERE ID = 1 AND LOCKED = 0
INFO 5/18/12 8:30 AM:liquibase: Successfully acquired change log lock
INFO 5/18/12 8:30 AM:liquibase: mdm.changelog.xml is using schema version 1.9 rather than version 2.0
INFO 5/18/12 8:30 AM:liquibase: 1.0/mdm.createdb.xml is using schema version 1.9 rather than version 2.0
INFO 5/18/12 8:30 AM:liquibase: 1.0/mdm.indexes.xml is using schema version 1.9 rather than version 2.0
INFO 5/18/12 8:30 AM:liquibase: 1.0/mdm.modifydb.xml is using schema version 1.9 rather than version 2.0
INFO 5/18/12 8:30 AM:liquibase: 1.0/populate.initial.xml is using schema version 1.9 rather than version 2.0
INFO 5/18/12 8:30 AM:liquibase: 1.1/mdm.modifydb.xml is using schema version 1.9 rather than version 2.0
INFO 5/18/12 8:30 AM:liquibase: 1.2/mdm.modifydb.xml is using schema version 1.9 rather than version 2.0
INFO 5/18/12 8:30 AM:liquibase: 1.3/mdm.modifydb.xml is using schema version 1.9 rather than version 2.0
INFO 5/18/12 8:30 AM:liquibase: 1.4/mdm.modifydb.xml is using schema version 1.9 rather than version 2.0
INFO 5/18/12 8:30 AM:liquibase: 1.5/mdm.modifydb.xml is using schema version 1.9 rather than version 2.0
INFO 5/18/12 8:30 AM:liquibase: 1.6/mdm.modifydb.xml is using schema version 1.9 rather than version 2.0
INFO 5/18/12 8:30 AM:liquibase: 1.7/mdm.modifydb.xml is using schema version 1.9 rather than version 2.0
INFO 5/18/12 8:30 AM:liquibase: 1.8/mdm.modifydb.xml is using schema version 1.9 rather than version 2.0
INFO 5/18/12 8:30 AM:liquibase: 2.0/mdm.modifydb.xml is using schema version 1.9 rather than version 2.0
INFO 5/18/12 8:30 AM:liquibase: 2.1/mdm.modifydb.xml is using schema version 1.9 rather than version 2.0
INFO 5/18/12 8:30 AM:liquibase: 2.2/mdm.modifydb.xml is using schema version 1.9 rather than version 2.0
DEBUG 5/18/12 8:30 AM:liquibase: Executing QUERY database command: SELECT MD5SUM FROM DATABASECHANGELOG WHERE MD5SUM IS NOT NULL
INFO 5/18/12 8:30 AM:liquibase: Reading from DATABASECHANGELOG
DEBUG 5/18/12 8:30 AM:liquibase: Executing QUERY database command: SELECT FILENAME,AUTHOR,ID,MD5SUM,DATEEXECUTED,ORDEREXECUTED,TAG,EXECTYPE FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
INFO 5/18/12 8:30 AM:liquibase: Reading from DATABASECHANGELOG
DEBUG 5/18/12 8:30 AM:liquibase: Executing QUERY database command: SELECT FILENAME,AUTHOR,ID,MD5SUM,DATEEXECUTED,ORDEREXECUTED,TAG,EXECTYPE FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
DEBUG 5/18/12 8:30 AM:liquibase: Release Database Lock
DEBUG 5/18/12 8:30 AM:liquibase: Executing UPDATE database command: UPDATE DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1
INFO 5/18/12 8:30 AM:liquibase: Successfully released change log lock
Liquibase Update Failed: Unknown Reason
SEVERE 5/18/12 8:30 AM:liquibase: Unknown Reason
java.lang.NullPointerException
    at liquibase.database.core.DerbyDatabase.close(DerbyDatabase.java:108)
    at liquibase.integration.commandline.Main.doMigration(Main.java:888)
    at liquibase.integration.commandline.Main.main(Main.java:134)

Have you made any additional progress? I don’t know of anyone looking at vFire support. Like you said, the best way to add support would be through an extension. 


https://liquibase.jira.com/wiki/display/CONTRIB/Database gives some info on creating a new Database implementation. You should be able to subclass liquibase.database.core.DerbyDatabase or copy the existing Derby code over as a starting point, depending on how different they are.


Nathan

I have been working with the latest version of liquibase (3.0.8) and sqlFire driver (1.1.1) I had to make one small change null pointer check to the DerbyDatabase close() method and everything works fine.


I have a question as to how to proceed. I as I have been looking at the extension pages on the website.


Does it make sense to create SqlFireDatabase extension? 

Or should I continue using Derby Support and submit a pull request?


The problem is that the metadata call SqlFireDatabase extension will it play nice with core Derby Database implementation? Ie How would I ensure the extension is picked up before the core Derby Database?

I see your pull request, but I think it would work better to have a separate sqlfile extension vs trying to build it into Derby. I’ve generally found that databases that try to act like a different one tend to have incompatibilities still that are easier to manage in an extension that trying to push it into the real database’s implementation.


I created https://github.com/liquibase/liquibase-sqlfire and will move your pull request logic into that repository.


Nathan