I think you might be misunderstanding the main use case for Liquibase.
Liquibase is version control for your DDL that–in addition–treats DDL changes as changes to apply to a database in a sequential manner. A handy side effect is that usually these changesets are portable across database vendors.
For your use case, you might look into Squirrel SQL, which includes a plugin to do exactly what you want with one push of a button.
Best,
Laird
I’m still not entirely sure what you mean by not portable.
We have a large system that we build up with Liquibase changesets on H2, PostgreSQL and Informix. The same changesets populate and maintain all three databases with nary a line of native SQL.
Recall that you don’t bring up a new database by exporting another one and sucking it in wholesale. Instead, you replay your changesets from ground zero.
Perhaps you could elaborate more–leaving exporting-to-SQL out for a moment, but addressing other issues–on how Liquibase doesn’t enable a sequence of changesets to be replayed on different databases?
Best,
Laird
Hi,
I’m new to liquibase. I’ve been exploring using it to export schemas from an oracle database which can later be imported into another database, for example, postgresql.
The changesets produced by liquibase include SQL in the flavour of the original database, presumably for simplicity of implementation. Wouldn’t it be (more?) useful to have a generic database independent format for this kind of database migration?
To try and half answer my own question. If it is as useful as I seem to think why can I not see at least an extension that tries to do this? Presumably because it isn’t as useful as I think. Why would this be?
So perhaps I am misunderstanding how to use liquibase? I am currently using the command line interface rather than anttasks as I am primarily a C++ developer and inclined more towards make and its variatns than ant. Is the ant interface much richer than the command line one?
Regards,
Bruce.
I’m finding it hard to understand what users and developers of liquibase mean by migration if they don’t mean changing database providers or versions. It sounds more like what I would term an upgrade.
The embedding of native SQL would seem to negate most of the advantages of using XML to my mind.
The one remaining one might be that XLST can be used to help transform one flavour to another.
Another related question. Is there a shared repository of XLST or similar to convert between flavours of database?
I’ve found the following useful resources describing some of the differences and guiding on how to write portable SQL
http://devpit.org/wiki/Portable_SQL
http://troels.arvin.dk/db/rdbms/
http://phplens.com/lens/adodb/tips_portable_sql.htm
http://www.codeproject.com/KB/database/macroscope.aspx
http://www.linuxnetworks.de/doc/index.php/Portable_SQL
How about a backend that attempts to write a portable subset of SQL-92? How a standard can be considered a standard if after almost 10 years its still not supported by most of the major vendors is beyond me.
Hi Laird, thanks for replying.
We do have a need for version controlling our DDL. Our present system involves exactly that. SQL placed in a version control system. Liquibase offers only a small improvement to this sie of things.
Funny you should mention Squirrel SQL because I just installed a moment ago and discovered the option.
One thing it doesn’t seem to have is a command line interface so that I can automate it without having to get into the code.
To be honest what I would really like is a suite that comprises the functionality of liquibase, dbunit & squirrelSQL in one.
My primary use case is automated system testing. DBUnit is unsuitable because it tests java applications only, though I must confess I have not investigated it thoroughly so far.
My experience with liquibase is that the changesets are not portable across database vendors because they include native SQL. So I think my questions still stand.
Perhaps the problem is the choice of originating database. Oracle may have more non standard data types in it. These appear in the XML. For instance I have:
To get this to work under mysql I have to replace VARCHAR2 with VARCHAR and get rid of “BYTE”.
I think Hibernate knows how to map types from any database with a little nudging as this is how the DBCopy plugin of SquirrelSQL works (except that it doesn’t always work).
So the problem is that the individual changesets themselves aren’t fully database independent.
Now I understand what you’re saying. Yes, if you need to pin a column in your database to a non-standard datatype, you’re going to be in trouble when you ask a tool–any tool–to infer from that datatype what the standard alternative should be on a different database.
Recall that Liquibase does a certain amount of data type conversion for you already; one of the typedef, but gives you a certain amount of flexibility when you know you want to use a certain non-standard datatype on a certain database for a certain column, and a different one on another.
Best,
Laird
This article suggests another way:
http://java.dzone.com/articles/liquibase-hibernate
After crateing a change log for your database
(something I can't do with the data due to the out of heap space bug)
I should be able run diff change log with hibernate apparently.
For example.
./liquibase \
--username=xxxx --password=xxxx \
--url=jdbc:oracle:thin:@xxxxxx \
--classpath=$CLASSPATH \
--driver=oracle.jdbc.OracleDriver \
--changeLogFile=myDatabaseEXport.xml \
--logLevel=info \
diffChangelog \
--referenceUrl=hibernate:/home/brucea/liquibase/comtel2_hibernate.xml
Except that liquibase out of the box does not like "hibernate databases".
The article is vague as well as out of date (it uses logLevel = FINE and --baseUrl rather than
--referenceUrl). Perhaps my referenceUrl syntax is just out of date as well?
It is not clear to me whether hibernate as a database type is intended to be supported by
liquibase but I think it would be one way of enabling migration between vendors without having
to specify the transformations manually.
Is this a sensible thing to attempt? or a sensible feature request?
Actually I’ve slightly misunderstood how hibernate is intended to be applied here.
I think the idea is that the hibernate configuration file which here is the reference url specifies not only how to talk to the database which the URL connection string already gives us but also how to map the databases types to java types
which is sort of what your doing with your property tags in the changeset.
Liquibase must already do this internally so that would just be fine tuning.
I think perhaps I want some a new commands / options.
Perhaps a migrate / dbcopy command
and perhaps an option to specify the hibernate mapping for a given database connection.
Right. I have now RTFMed a bit and see that I was missing the hibernate plugin which was moved out of the core for v2.0. Having installed that I now get a crpytic error message trying to use hibernate:
SEVERE 4/21/11 6:51 PM:liquibase: org.hibernate.cfg.Configuration
java.lang.NoClassDefFoundError: org/hibernate/cfg/Configuration
at java.lang.Class.getDeclaredConstructors0(Native Method)
at java.lang.Class.privateGetDeclaredConstructors(Class.java:2389)
at java.lang.Class.getConstructor0(Class.java:2699)
at java.lang.Class.getConstructor(Class.java:1657)
at liquibase.servicelocator.ServiceLocator.findClassesImpl(ServiceLocator.java:177)
at liquibase.servicelocator.ServiceLocator.findClasses(ServiceLocator.java:145)
at liquibase.database.DatabaseFactory.(DatabaseFactory.java:17)
at liquibase.database.DatabaseFactory.getInstance(DatabaseFactory.java:32)
at liquibase.integration.commandline.CommandLineUtils.createDatabaseObject(CommandLineUtils.java:53)
at liquibase.integration.commandline.Main.doMigration(Main.java:609)
at liquibase.integration.commandline.Main.main(Main.java:116)
Caused by: java.lang.ClassNotFoundException: org.hibernate.cfg.Configuration
at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:252)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320)
To me this does not read as implying my hibernate config is rubbish but that is an option. So here it is:
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
“http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd”>
com.mysql.jdbc.Driver
jdbc:mysql://localhost/xxxxxx
xxxxxx
xxxxxx
10
true
org.hibernate.dialect.MySQLDialect
update
Sorry to butt in, but I just tried liquibase for the first time and googled onto this thread while searching for an answer.
I got as far as downloading the liquibase-hibernate.jar from http://liquibase.jira.com/wiki/display/CONTRIB/Hibernate+Integration and copying it to my liquibase install dir, which got around the “Driver class was not specified and could not be determined from the url (hibernate:path/to/my/hibernate.cfg.xml)
” error.
The NoClassDefFound error for cfg/Configuration appears to be because the --classpath argument given to the liquibase script isn’t actually used on the java command line. The liquibase script builds a CP variable from:
${LIQUIBASE_HOME}/liquibase*.jar
${LIQUIBASE_HOME}/lib/*.jar
So passing --classpath=path/to/hibernate3.jar does not add any hibernate classes onto your classpath.
This can be fixed by e.g.:
ln -s /path/to/hibernate3.jar /path/to/liquibase/lib
This creates a soft link in the lib dir which liquibase then adds to the classpath when running the java VM, which can then be found by the ServiceLocator.
I then got a NoClassDefFoundError for org/dom4j/DocumentException, which I also fixed with a soft link:
ln -s /path/to/dom4j.jar /path/to/liquibase/lib
Same for slf4j (the api and the java util logging facade):
ln -s /java/slf4j-1.6.1/slf4j-api-1.6.1.jar /java/liquibase/lib
ln -s /java/slf4j-1.6.1/slf4j-jdk14-1.6.1.jar /java/liquibase/lib
Same to find javax/persistence/EntityListeners
ln -s /java/hibernate/hibernate-distribution-3.6.0.Final/lib/jpa/hibernate-jpa-2.0-api-1.0.0.Final.jar /java/liquibase/lib
I now get a lot of good looking output, followed by:
(having run liquibase with --changeLogFile=changelog.log)
INFO 4/23/11 11:43 AM:liquibase: changelog.log does not exist, creating
Liquibase Update Failed: Unknown Reason
SEVERE 4/23/11 11:43 AM:liquibase: Unknown Reason
java.lang.NullPointerException
at liquibase.diff.DiffResult.printChangeLog(DiffResult.java:507)
at liquibase.diff.DiffResult.printChangeLog(DiffResult.java:421)
at liquibase.diff.DiffResult.printChangeLog(DiffResult.java:406)
at liquibase.integration.commandline.CommandLineUtils.doDiffToChangeLog(CommandLineUtils.java:136)
at liquibase.integration.commandline.Main.doMigration(Main.java:621)
at liquibase.integration.commandline.Main.main(Main.java:116)
I’m now fighting with fetching liquibase from svn and digging into its code to figure out what’s going on here.
Could the liquibase team please update http://www.liquibase.org/manual/hibernate with some slightly more useful instructions, please?
Nick
That NullPointerException in printChangeLog is because I specified changelog.log as my output logfile:
–changeLogFile=changelog.log
Turns out liquibase only knows how to output to .xml and .txt files. It literally inspects the extension of your requested logfile and looks that up (“xml” or “txt”) in an internal hash to get the output serialiser. (see ChangeLogSerializerFactory.java:49
return serializers.get(fileNameOrExtension);
If that returns null then liquibase later falls over with the NullPointerException. Simply specify a changelog file ending in .xml or .txt to fix it.
Liquibase team: any chance of a test being put in that getSerializer method to throw a meaningful exception if the requested extension is not supported?
Nick
That’s quite helpful information thanks which should move me along when I get back to work. So please continue to butt in.
Here’s my quick-and-dirty way of getting database independent changesets into the database. I use hibernates dialects to translate from field types that match java.sql.Types constants into database specific ones before I apply the changeset.
I know this is an old thread, but I’m exactly in the same situation trying to build database agnostic migration files (for MySQL and Oracle) in Grails database-migration-plugin (is a Liquibase wrapper).
Diffs are build on MySQL. The migrations must also work on Oracle (and maybe other Db types, later).
My current approach is to define database specific properties in the main changelog file and rework the diff files (automatically by string replacements) like so:
and:
It would be great if Liquibase itself would optionally produce database agnostic migration files.
