Hello folks,
I’m currently trying to get our existing liquibase changesets working with the head of the liquibase trunk. We have some existing changesets that insert the string “NULL” as a value (i.e. not setting the column to , but inserting the string “NULL”). We also have other changesets that insert an empty string as a value. Prior to revision 1678 (http://liquibase.jira.com/source/changelog/CORE/?name=otaranenko&cs=1678), trying to insert an empty string would result in a value, which is not what we wanted; post-1678, we can insert an empty string, but now inserting the string “NULL” results in a value, which is also not what we want. How do I now insert the string “NULL” as a value?
Thanks,
Dean.
I’ll look at the issue and try to fix it
In the integration tests all such examples correctly inserts null values.
Dean, could you please supply your change set.
Is it not in the loadData tag?
Hi, thanks for quick response. I have a changeset which looks something like:
In this case, I’d like the column ‘myString’ to contain the string “null”, but instead I get a null value for that column.
Originally posted by: dean
Hi, thanks for quick response. I have a changeset which looks something like:
In this case, I’d like the column ‘myString’ to contain the string “null”, but instead I get a null value for that column.
Ah-h-h… I gotcha, what you mean. I thought quite the contrary. “NULL” in such situation must be treated as value. To support your case I suggest add additional attribute at the changeSet level, say nullAsStringValue=“true”. If no has objection I could add this.
Or may be someone has another idea?
Cheers, Oleg
What was the behaviour with value=“null” at v1.x? Probably best to keep this previous behaviour, whatever it was. The “nullAsStringValue” attribute would be fine with me.
Originally posted by: dean
What was the behaviour with value="null" at v1.x? Probably best to keep this previous behaviour, whatever it was. The "nullAsStringValue" attribute would be fine with me.
I'm not sure, if that behavior was specially stated but not occasionally. If so, we have a chance to correct how it is right.
I think about another format of xml. May be
sounds better? In this case we can handle in one insert statement and "null" values.
I saw change 1678 come through that changed the 1.9 behavior of empty=null, which is probably good. With 1.9 we didn’t have a great answer to empty string vs. null vs “null”, and whatever change we make will break existing changesets.
I’m thinking the best option is to have a more special value like \null\ that we use to actually mean null. So you can say value="" and get an empty string, value=“null” and get the string “null” and value="\null" to be a null value. That way we don’t need an addtional attribute, which will also help with other formats like .csv data imports that can use \null\ as a value.
For 1.9->2.0 conversion, it may be worth adding a changeLog file-wide attribute of “emptyStringMeansNull” that people with a 1.9 changelog can use to keep the old behavior. You would need to add a switch to what was changed in 1678. You should be able to get a handle on the databaseChangeLog object to check the state of the flag.
Nathan
at start value="\null" looks good, but what will you do, if someone wanna insert “\null” as string? Enforce he to write “\null\”? And this will occurs with any string, you can imagine. No warranty about an exception.
At my taste, method with attribute is the only way to untie that knot.
As for import in .csv i suggest the same manner: bare null is and “null” is a string value. Double quotes is already reserves character in the parser. We could make a decision using such manner.
I like better than \null\ for a special value. It’s more SQL-like, less code-like.
I think to handle inserting “” as a string, we would just need to allow escaping like watching for “<null>” It’s enough of an edge case (is that a string anyone would ever really want to insert into a database?), I think we can handle the escaping at a later time, though.
Nathan
I’m convincing the syntax for any action should be as simple as possible.
It will facilitate using any program tool in the future and for any. Syntactic-sugar?
Therefore an attribute base approach to avoid remember the exact escaping for null values.
It will avoid hard-coded null value escaping in the codebase too.
case #1
case #2
both #1 and #2 produce
insert into TestTable(myNull, myString) values(null,‘null’);
#1 attribute escapeNull (default=“true”) allows fine-grained handling of the values. For example null, ‘null’ and ‘\null’ values in the same insert/update clause
#2 attribute nullSubstitue (default=“null”) allows avoid verbose for regular data
#3
#4
testNull.csv::
myNull,myString
null,\null
#3 => insert into TestTable(myNull, myString) values(null,’\null’);
#4 => insert into TestTable(myNull, myString) values(‘null’,null);
Note for csv import there is no possible row-level handling of the edge. It should be split into different csv files.
I agree it should be simple, that’s why I’m thinking it should just be a single value that we handle as null in whatever form the changeset is in: xml, csv, or anything else without needing to worry about escapeNull or nullSubstitue parameters. If we have null values in different formats, I think that would make it more difficult for future tools to handle.
would have problems with the angle brackets because of XML, however. Maybe {null} is better?
(note: I’ll be on vacation from the 21st to the 29th and may not be able to respond for a while)
Nathan
Hello Nathan, Oleg,
Did you reach a resolution on this issue? We’re still suffering from this problem with the version of liquibase we’re running with (revision 1719 of trunk). Thanks,
Dean.
no, we did not >:(
for me personally changing null to {null} follows to problem with currently supported liquibase projects. That’s why I’ve suggested the approach with nullSubstitute solution. nullSubstitude will allow avoid a lot of additional work to keep current checksums. In my case the current csv files will not be changed, only adding an attribute in changeset tag.
May be add nullSubstitude attribute at the <databaseChangeLog …> level? By default <databaseChangeLog nullSubstitude="{null}" or what ever you want…
Nathan?
That is a good point about needing to support the existing 1.9 style to not break checksums. With 2.0 we are already breaking checksums, but there are many people that have already been running 2.0 and it would be good to not cause them more problems than we need to.
Given that, I think it would be best to introduce a new attribute to control which value means NULL. For backwards compatibility and for least surprising, I think we should keep the default value as “NULL”. However, you can set the nullSubstitute attribute at the changeSet or the changeLog level and override the value that liquibase looks for. I think “nullPlaceholder” would be a better attribute value, though. Any other suggestions?
So the normal behavior would be to have and it would have a value of NULL. You can set nullPlaceholder to anything you want ("{null} ", “REALLY_A_NULL”, etc) on the changeSet tag or the databaseChangeLog tag to allow you to set the value as the string null.
Nathan
Thank you Nathan, nullPlaceholder sounds better.
Would it be case sensitive? I hope yes. In that case syntax will be more intuitive. I.e.
allows to insert null as a string, what dean wants
insert into TestTable(myNull, myString) values(null,'null');
I’ll have to see what the convention was in 1.9 so that continues to work. I think case insensitive would actually be better, I find people have a hard time remembering what case to use, and I’m not sure why using NULL as a placeholder would be more or less intuitive than null as a placeholder.
You’d have to use something completely different than null if you want to insert “null” as a string:
Nathan
case (in)sensitive is no problem to me 8-). Let see in 1.9 for compatible
I undid the change the modified the 1.9 behavior. I also created an issue for nullPlaceholder (http://liquibase.jira.com/browse/CORE-722) but will not tackle it until post-2.0
Nathan
How to insert the empty string now? emptyPlaceholder=“empty”?
This is a bug in 1.9 and as I said before, we need to fix it.
If you take care about 1.9 => 2.0 compability, checksums are breaking anyway. Along with checksum correction, it is possible to write the xslt script to convert the 1.9-style changelog files to new standard with “” as empty string, “null” for null value (as a default for nullPlaceholder).
And also I think it is better introduce the nullPlaceholder right now, BEFORE 2.0 release to avoid additional changes of the changeset’s files later.
