Thursday, 17 July 2008

Human Readable Compact Extensible Data Interchange Format

When working with Lotus Domino databases I needed a simple and compact way to pull data from one database into another.

I wanted something that was human-readable, extensible, simple, order-agnostic and simple to process. After some experimentation I settled on an old idea to use delimited fields. But, I wanted to be able to add and remove fields and have them in any order. To do this I added a field name.

The format is

Compact = Field* ~

Field = ~ fieldname : data

The only constraints were:

  1. The field name could not start with a "~"
  2. The field name could not contain a ":"
  3. The data could not contain a "~"
Since ":" is rarely used as a field name and "~" is rarely used in data, this was a small price to pay. If you need "~" in the data then there are many ways to do this. One way we used was to append XML-like data to the end of the Compact and then extract the data in a XML-like way.

To extract a data element we used code like this:
@left( @right( compact ; "~" + fieldname + ":" ); "~" )
This gets the string right of the "~fieldname:" and then returns data left of the "~".

Similar code can be used to extract the XML data:

@left( @right( XMLData ; "<" + fieldname + ">" ); "</" + fieldname + ">" )
(It is XML-like so you need to handle escape sequences yourself.)

Here are some examples. You can see we even carried code and XML-like data as data.

~ID:TS~SubID:VP~Icon:44~SubIcon:~SLANo:~Z1RT:2~Z2RT:2~Z1CT:8~Z2CT:8~Z1ET:7.2~Z2ET:7.2~Pri:Standard~Desc:Telephone Service:VoIP Service (Ericsson)~SubForm:VP~LocSubForm:Location~FailureCategories:~PeriodDef:~ReactionDef:~PBVer:4.3;0~NItems:~<242</ConPrice><InstPrice>0</InstPrice><MACPrice>@If(movecomplexity="0";56;movecomplexity="1";111;-1)</MACPrice><FRDSCauses>TS-VP</FRDSCauses><ResponseTime>0</ResponseTime><RepairTime>0</RepairTime><EscalateTime>0</EscalateTime><PBProductCode></PBProductCode><PBPrice>360</PBPrice><ExcludeDays></ExcludeDays><BusinessHours>[08:00]:[17:00]</BusinessHours><BillingAudit>Options(OP)~VM(OP)</BillingAudit><Applications></Applications><LineSpeeds></LineSpeeds><Interfaces></Interfaces><CDIDType></CDIDType><MIMSNoDef>PL15TE01</MIMSNoDef><AssetCategory></AssetCategory>

~RCCode:AM~RCTitle:Asset Management~MCode:~MCustCode:000001~MIDs:~Discount:20~DiscountConnect:10~

~LocA:PTSA13~Location:George St 47, Level 12N Conference Rm~Lat:30.123456~Long:151.123456~SiteZone:Metro~