Support for binary data in 2.0

General Discussion
1

Hello,

I’m new to this forum and a new user to LiquiBase. Currently I’m using LiquiBase 1.9 mostly to insert master and/or test data
though I’m the only developer in the project.

My question is: Is there any support for inserting binary data? Since data values are specified as XML attribute of the ‘column’
tag its use seems to be rather limited for this case. Are there any plans to support specifying data as tag value, something like:
…
This way the (binary) data could be base64 encoded or the like.

And if I use the ‘loadData’ tag to use a CSV file, what encoding does LiquiBase expect?

A last question: Since most example in the Wiki seem to use release 1.6l, is there any special documentation for release 2.0 yet?
Or is the Wiki up to date?

regards,

Robin.

2

The wiki is up to date.  I don’t always update the schema reference if there hasn’t been any major/nessisary changes. 

The trouble with binary data is that we want to be able to support generation of SQL to apply later, and there is no way to load that data in from a standard SQL script.  It would be nice to add support, and would be a great option for an extension (http://liquibase.org/extensions), but it is not on the roadmap at this point.

Nathan

3

Thanks for your answer!
The ability to generate standard SQL is one thing, but beeing able to hande binary data at all (maybe not for all databases)
is sure an import point. Maybe I can provide an extension for this at some point in time…
Actually I stumbled over LiquiBase in search of a standard SQL way to insert binary data! :slight_smile: (which doesn’t exist it seems…)

But don’t you think it’s possible to use the LoadData tag for this purpose?
Unfortunately the documention for this tag is somewhat short, I didn’t even achieve to load anything!

Using the LiquiBase 1.9 ant task, MySQL 5.1, I tried the following:

 
      new profile
     
        …
     
 

‘profile.csv’ contained no binary data, it’s contents was:
2,0,true,1981-01-12,Hamburg,DE,2009-07-14 14:31:51,NULL,test1@test.de,NULL,true,NULL,NULL,1,Test1,test,42356,NULL,1,NULL,NULL,NULL

First I tried it to the changelog, then I used the file path relative to the CP root, but only with an absolute file path
it succeeded. With any relative path I always got:
java.lang.RuntimeException: profile.csv could not be found
at liquibase.change.LoadDataChange.getMD5Sum(LoadDataChange.java:192)
at liquibase.ChangeSet.getMd5sum(ChangeSet.java:111)
at liquibase.ChangeSet.toString(ChangeSet.java:341)
        …

But then, with an absolute path the command passed successfully but no data where inserted into the DB!

So how to specify the file attribute? (relative to what?)
And are the column definitions mandatory for loadData? The command passed with and without them but in
neither case any data were inserted! (and no error, no warning reported)

And then: if this would succeed, do you think I can import binary data with this?
Using a CSV like:  1,myname,xxxxxxxxxx
where xxxxxx would be binary data encoded in UTF-8 or Base64?

thanks in advance,

Robin.

4

Sorry, forget about the file attribute, looking into the code I found that it is resolved as a (ant) classpath resource.
I forgot to include the classes directory in my Ant classpath…

But still, nothing gets imported into my database, no warning no error!

5

Ok, how is someone supposed to actually use this stuff? I’m somewhat confused, after trial and error I figured out that the
CSV file needs a first line containing the names of any column, is this right?
E.g. like this:

id,blocked_id,blocker_id
1,1,4
2,1,8

In my first attempt I had only one row with the data and Liquibase used that as column names I guess, therefor no insert…
Is the ANY description of the CSV format? At the moment I’m trying to insert a BOOLEAN, in what format should I specifiy
a boolean value? (true or TRUE didn’t work, 1 neither…)

Or is LiquiBase’s main intend to be used for schema migration?

6

The CSV format is actually fairly flexible.  It does need to be documented more, though.

You are right that the first row is the names of the columns.  It assumes it is string fields by default, but you can change the type liquibase tries to load the data as by specifying the type attribute in the loadData’s column tag.  If you specify a type as BOOLEAN it will know what to do with ‘true’ and ‘false’ values.

For example

   
   
   

Nathan

7

The main purpose of liquibase is for database migration which is primarily schema migration, but also includes data manipulation as your project progresses.  That is why we do have the , and tags.

Nathan

8

It is possible to write an extension that will load the binary data.  The key is to use a prepared statement, execute it inside your generator, and return null.  Take a look at this thread for an example:

http://forum.liquibase.org/#topic/49382000000031017

I was able to get clob loading working using this trick in an extension.

I’d think this is something a lot of people are going to need to do, and it really ought to be added to the core.  I know that generating the SQL won’t work, but that’s an acceptable trade off for us. 

9

Yes, it is probably worth adding into the core project. If you have a good extension, add it to the extension portal for now (liquibase.org/extensions) and we can look at including in a future liquibase version.


Nathan

10

12 years have passed, and Liquibase still cannot import binary data. Are there any plans to support this yet? Or does anybody have any workaround to solve this problem?