JSON <==> XML

[G.RichardBellamy]

Bidirectional conversion of JSON <==> XML seems off.

Given the following insert changeSet in JSON from the source:

2. {
3.     "databaseChangeLog": [
4.         {
5.             "changeSet": {
6.                 "id": "datatypetest-2",
7.                 "author": "nvoxland",
8.                 "validCheckSums": ["ANY"],
9.                 "changes": [
10.                     {
11.                         "insert": {
12.                             "tableName": "dataTypeTest",
13.                             "columns": [
14.                                 {
15.                                     "column": {
16.                                         "name": "id",
17.                                         "valueNumeric": 1
18.                                     }
19.                                 },
20.                                 {
21.                                     "column": {
22.                                         "name": "dateCol",
23.                                         "valueDate": "2007-08-09"
24.                                     }
25.                                 },
26.                                 {
27.                                     "column": {
28.                                         "name": "timeCol",
29.                                         "valueDate": "13:14:15"
30.                                     }
31.                                 },
32.                                 {
33.                                     "column": {
34.                                         "name": "dateTimeCol",
35.                                         "valueDate": "2007-08-09T13:14:15"
36.                                     }
37.                                 },
38.                                 {
39.                                     "column": {
40.                                         "name": "bigintcol",
41.                                         "valueNumeric": 2
42.                                     }
43.                                 }
44.                             ]
45.                         }
46.                     },
47.                     {
48.                         "insert": {
49.                             "tableName": "dataTypeTest",
50.                             "columns": [
51.                                 {
52.                                     "column": {
53.                                         "name": "id",
54.                                         "valueNumeric": 2
55.                                     }
56.                                 },
57.                                 {
58.                                     "column": {
59.                                         "name": "dateCol",
60.                                         "valueDate": "current_datetime"
61.                                     }
62.                                 },
63.                                 {
64.                                     "column": {
65.                                         "name": "timeCol",
66.                                         "valueComputed": "current_datetime"
67.                                     }
68.                                 },
69.                                 {
70.                                     "column": {
71.                                         "name": "dateTimeCol",
72.                                         "valueDate": "current_datetime"
73.                                     }
74.                                 },
75.                                 {
76.                                     "column": {
77.                                         "name": "bigintcol",
78.                                         "valueNumeric": 2
79.                                     }
80.                                 }
81.                             ]
82.                         }
83.                     }
84.                 ]
85.             }
86.         }
87.     ]
88. }

When converted to XML using a the JQuery json2xml converter (and others), turns into the following XML

2. <?xml version="1.0" encoding="utf-8"?>
4.  
5.     ANY
6.     datatypetest-2
7.     nvoxland
8.    
9.      
10.         dataTypeTest
11.        
12.          
13.             1
14.             id
15.          
16.        
17.        
18.          
19.             dateCol
20.             2007-08-09
21.          
22.        
23.        
24.          
25.             timeCol
26.             13:14:15
27.          
28.        
29.        
30.          
31.             dateTimeCol
32.             2007-08-09T13:14:15
33.          
34.        
35.        
36.          
37.             2
38.             bigintcol
39.          
40.        
41.      
42.    
43.    
44.      
45.         dataTypeTest
46.        
47.          
48.             2
49.             id
50.          
51.        
52.        
53.          
54.             dateCol
55.             current_datetime
56.          
57.        
58.        
59.          
60.             timeCol
61.             current_datetime
62.          
63.        
64.        
65.          
66.             dateTimeCol
67.             current_datetime
68.          
69.        
70.        
71.          
72.             2
73.             bigintcol
74.          
75.        
76.      
77.    
78.  

And the same insert changeSet in XML from the source:

2. <?xml version="1.0" encoding="UTF-8"?>
4.  
5.     ANY
6.    
7.      
8.      
9.      
10.      
11.      
12.    
13.    
14.      
15.      
16.      
17.      
18.      
19.    
20.  

When converted using the JQuery xml2json, produces the following JSON:

2. {
3.   "databaseChangeLog": {
4.     "changeSet": {
5.       "author": "nvoxland",
6.       "id": "datatypetest-2",
7.       "validCheckSum": "ANY",
8.       "insert": [
9.         {
10.           "tableName": "dataTypeTest",
11.           "column": [
12.             {
13.               "name": "id",
14.               "valueNumeric": "1"
15.             },
16.             {
17.               "name": "dateCol",
18.               "valueDate": "2007-08-09"
19.             },
20.             {
21.               "name": "timeCol",
22.               "valueDate": "13:14:15"
23.             },
24.             {
25.               "name": "dateTimeCol",
26.               "valueDate": "2007-08-09T13:14:15"
27.             },
28.             {
29.               "name": "bigintcol",
30.               "valueNumeric": "2"
31.             }
32.           ]
33.         },
34.         {
35.           "dbms": "!derby",
36.           "tableName": "dataTypeTest",
37.           "column": [
38.             {
39.               "name": "id",
40.               "valueNumeric": "2"
41.             },
42.             {
43.               "name": "dateCol",
44.               "valueDate": "current_datetime"
45.             },
46.             {
47.               "name": "timeCol",
48.               "valueComputed": "current_datetime"
49.             },
50.             {
51.               "name": "dateTimeCol",
52.               "valueDate": "current_datetime"
53.             },
54.             {
55.               "name": "bigintcol",
56.               "valueNumeric": "2"
57.             }
58.           ]
59.         }
60.       ]
61.     }
62.   }
63. }

I’m confused by this apparent discrepancy. It seems to prevent automated conversions from one format to another, which seems like a natural option given the relative compatibility of JSON, XML and YAML. I recognize that there are well-known problems with these conversions, but those don’t seem to be what is at play here.

I’d love to understand the thinking on this - it looks to me that you implemented YAML and got JSON, rather than deriving from your original XML structure.

[nvoxland]

The idea is that the XML format isn’t the canonical format, it is just one implementation. There is an underlying object structure which JSON/YAML/XML/SQL etc. are converted to and those objects are the real format. Each changelog format can be as different as they want as long as they can construct a valid object structure. 

I tried to keep the JSON/YAML and XML structures similar to make documentation easier but at the same time tried to have the json/yaml format make sense as json/yaml. If anything, the json/yaml formats are more in line with the object model than the XML is because there are places where the XML format should be different but we jump through hoops to maintain backward compatibility.

If you are looking for JSON <==> XML conversion, that is possible using the liquibase classes. The XMLChangeLogParser/JSONChangeLogParser classes can consume XML/JSON classes to create a DatabaseChangeLogObject which can then be passed to XMLChangeLogSerializer and JSONChangeLogSerializer to re-output it, even in a different format.

Nathan

[nvoxland]

I agree that the JSON/YAML format could be a bit less verbose. That’s what happens when you still try to be fairly similar to XML  I may create a tighter DSL style format at some point as well. 

Using the changelog parser/serializer classes, you should be able to shift formats down the road if you ever need. Or you can mix and match when using the tags as well.

Do you have an example of the lack of multiline support in JSON that you are seeing? 

Nathan

[Guest]

Thanks for the thorough explanation. What you say makes complete sense to me - you started with XML, so it’s going to be the more mature and encumbered of the formats.

I am not a big fan of XML in general, but in this instance it has won out as my format of choice for some very specific reasons:

1. Schema validation - when creating the database changelogs, many IDE's will provide code assistance and validation per the XSD's. IIRC neither YAML or JSON have a schema specification.
2. While the YAML markup is better suited as a DSL, I use IntelliJ, and it doesn't support the Language Injection feature for YAML - which I find incredibly valuable when dealing embedded SQL (createView, createProcedure, etc).
3. The JSON format is just too verbose for me - requiring the repeated "column" tag when the JSON itself handle arrays of values is troublesome.
4. Most damning for JSON is its lack of multiline support.
5. The Formatted SQL doesn't have the auto-rollback functionality.

This all came to the fore because at this point I’ve cycled through changelogs in every one of the four formats, I started with JSON, found its rough edges and moved to YAML, and got bit by invalid syntax and realized the superior format, for now, is XML - however I can see a need to move back to YAML at some point in the future, and having the ability to translate back and forth would really help me do that.

[un1384957735940r2id]

Okay…that was me above.

[un1384957735940r2id]

It does give me comfort knowing that I could translate using the Object Model and the parser/serializer classes.

I am currently mixing and matching my formats using the tag.

Sorry for the imprecise language - I’m referring to the JSON spec which does not allow embedded control characters, and therefore requires that multi-line strings have escaped newlines.

This plays havoc with readability.

[nvoxland]

Good point, that is a problem with JSON in general that I hadn’t really thought of.  For multiline sql you can use the sqlFile tag even in JSON, but it is a pain.

Nathan