JSON File Generation

Change log:

Description

The purpose of this document is to provide a description of the JSON file export feature of the File Processor framework. 

1 - JSON Generation

To be able to export data from Rator database tables into a JSON file, some generic utilities were created in Rator File Processor framework.

This page describes the design of the framework and also some examples to use it in other parts of the system.

2 - Overview

The generation of an JSON-file is done according to a database configuration where it is possible to configure all the tags that must be printed, their structure and how data can be retrieved to fill their content.

Once the configuration is ready the JSON-producer just needs the data that will be passed as a map.

<File>
  <Name>M_OI_BIL.M_SP_CORR.GGSN101_20101125235414_14068.dat.SP_IN.038476.dat</Name>
  <BillingSourceId>201012031002438274</BillingSourceId>
  <Record>
    <BillingRecordId>201011262043130885</BillingRecordId>
    <RatingKey></RatingKey>
    <PriceElements>
      <PriceElement>
        <ChargeItemId>201005121347550887</ChargeItemId>
        <Description>SMS til 31240912</Description>
        <IdlId>201012011045262305</IdlId>
      </PriceElement>
    </PriceElements>
    <Code>HI3GNET</Code>
    <Duration>0</Duration>
  </Record>
</File>

In this example the map can contain 2 objects: “HEADER” will contain the billing source object used to fill the <Name> and the <BillingSourceId> tags, then in the map we will also find an object containing the list of records with key “RECORDS” for instance. The keys and the methods to retrieve the data from the map and from the objects inside the map will be specified in the configuration.

2.1 - Encoding

The encoding used to generate the file is UTF-8..

3 - Design

The persistent objects introduced in this framework and which are part of the configuration are JSONTagDef (table JSON_TAG_DEF) and DocumentDef (table DOCUMENT_DEF) and ValueFormatter (table VALUE_FORMATTER).

The valueFormatter can also be used outside the JSON-generation.

3.1 - JSONTagDef

A record in the JSON_TAG_DEF defines a single tag (key/value pair) of the resulting JSON file, and it has the following attributes:

3.1.1 - Columns

3.2 - DocumentDef

An DocumentDef models the definition for a document type for example JSON. Here a filler class can be defined that will contain the logic to fill the map with the data, the methods to be called and also the reload method.

3.2.1 - Columns

3.3 - DocumentFiller

An abstract class DocumentFiller has been defined with an abstract method populate that will generate the dataBeans map according to a context map.

The concrete subclasses of DocumentFiller class should contain the specific logic for the generation of the document.

Following Concrete subclasses of DocumentFiller can be found in the FileProcessor Integration project: see JSONDocumentFiller 

3.3.1 - JSONDocumentFiller 

The JSONDocumentFiller class contains the logic for generating the JSON-files from data obtained by joining some database tables by executing the SQL-script configured via the corresponding DOCUMENT_DEF record.  If the desired JSON-file format cannot be achieved with JSONDocumentFiller sub class, A new subclass of  DocumentFiller must be created as per the requirements.

Instructions to set up file processor configuration for JSON-file generation with JSONDocumentFiller :

• Create JSON_TAG_DEF entries for the required JSON file format: A record in the JSON_TAG_DEF defines a single element (key/value pair) of the resulting JSON file. It is possible to define parent tag for the JSON_TAG_DEF entry, and thus it is possible to define the full hierarchy of the JSON elements. Please choose proper FORMATTER_ID (ID of the VALUE_FORMATTER table record, used to format the value before writing it to the JSON file) for each JSON_TAG_DEF entry based on the value type.
• Create a DOCUMENT_DEF record with FILLER_CLASS = 'com.CDRator.billing.document.json.JSONDocumentFiller', ROOT_TAG_ID = '<Id of the JSON Root Tag entry from JSON_TAG_DEF entries created for this JSON file format>' 
• The SQL_LOAD_RECORDS column of the above created DOCUMENT_DEF record must contain the SQL query to obtain the records that should be exported to the file. This SQL string can contain the label <BILLING_SOURCE_ID> which will be replaced with appropriate value at run-time.
  If this SQL query string contains the label <BILLING_SOURCE_ID>, it will be replaced with the ID of the current source record currently being processed to create the JSON file. Note: The billing source table name is configured in the column BILLING_SOURCE_TABLE_NAME of the table CDR_FILE_DEF. <BILLING_SOURCE_ID> will be replaced with the ID of the current row being processed from the table configured in the column CDR_FILE_DEF.BILLING_SOURCE_TABLE_NAME.
• The SQL_RELOAD_RECORDS column of the above created DOCUMENT_DEF record must contain the SQL query to load the next batch of records with ID greater than the last loaded maximum ID, which are to be exported into the JSON file. This SQL query can contain the tags <BILLING_SOURCE_ID> and <MAX_LOADED_ID> labels which will be replaced with appropriate values at run-time
• Create a CDR_FILE_DEF record with CODE ='JSON' , BILLING_SOURCE_TABLE_NAME = 'VOUCHER_EXPORT_SOURCE', BILLING_SOURCE_CLASS= 'com.CDRator.billing.product.vouchers.export.data.VoucherExportSource', EXPORT_TYPE = '<The Export_Type for the Customer this JSON file is generated for>', DOCUMENT_DEF_ID = '<ID of the DOCUMENT_DEF record created in above step>', FILE_ANME-SUFFIX = 'json'. See the sample configuration supplied for more details on the values used for different columns of CDR_FILE_DEF. Please note that the CDR_PARSER_ID, RECORD_SEPARATOR_ID are not used for JSON file export and hence they can be empty.
• The same post processor method name, post processor hookpoint key, filename format and filename generator method can be used for Flat file generation, XML file and JSON file generation, as described in Voucher Export File Processor (GENERATOR) engine.
• Create a CDR_RECORD_DEF record with CODE ='DETAIL' , RECORD_TABLE_NAME = 'VOUCHER_EXPORT_RECORD', RECORD_CLASS = 'com.CDRator.billing.product.vouchers.export.data.VoucherExportRecord', CDR_FILE_DEF_ID = '<ID of the CDR_FILE_DEF created in above step>'. The JSON file processing engine will only need a CDR_RECORD_DEF entry for DETAIL records. So HEADER, TRAILER record definitions are not needed.
• Create a CDR_FILE_PROCESSOR entry with CODE='GENERATOR',  CDR_FILE_DEF_ID = '<ID of the CDR_FILE_DEF created in earlier step>', FILE_TRANSMITTER_ID= '<Id of the corresponding FILE_TRANSMITTER entry>' and BRAND_ID = '<Corresponding brandId>' 

Important Note: It is mandatory to configure both DOCUMENT_DEF.SQL_LOAD_RECORDS and  DOCUMENT_DEF.SQL_RELOAD_RECORDS columns with appropriate SQL queries, because the File processor JSON filler class is configured to load only a maximum 10000 records in one select. If there are more records in that Voucher Batch, the reload_method  column of the relevant JSON_TAG_DEF entry must be set to 'reloadRecords'. In that case, after printing the first set of 10000 records, the JSONDocumentFiller tries to load next batch of records with IDs greater than the ID of the last retrieved record in the previous step. 

For voucher export, value of DOCUMENT_DEF.SQL_LOAD_RECORDS column should be: 

Select vr.* from VOUCHER_EXPORT_RECORD vr where vr.VOUCHER_EXPORT_SOURCE_ID = <BILLING_SOURCE_ID> order by vr.id

And value of DOCUMENT_DEF.SQL_RELOAD_RECORDS column should be: 

Select vr.* from VOUCHER_EXPORT_RECORD vr where vr.VOUCHER_EXPORT_SOURCE_ID = <BILLING_SOURCE_ID> and vr.id > <MAX_LOADED_ID> order by vr.id

If there are 20000 records, then the JSONDocumentFiller uses the SQL queries configured in the DOCUMENT_DEF record as explained below: 

• The query from SQL_LOAD_RECORDS column is used to load the first batch of records (10000).
• If there are more records to be added to the same JSON file and the reload_method on one of the JSON_TAG_DEF entries is set to 'reloadRecords', the SQL from SQL_RELOAD_RECORDS column is used to load the next batches, with 10000 max rows selected in each subsequent batch, until all pending records have been added to the file.

3.4 - ValueFormatter

The value formatter is used in order to be able to configure the formatting of field values. Here a formatter class and a pattern can be specified that will be used to generate a string.

Following Value Formatter types are available:

• com.CDRator.billing.utils.formatter.DateFormatter
• com.CDRator.billing.utils.formatter.NumberFormatter

3.4.1 - Columns

4 - Example of Generation

After having configured a new XML-definition and the tags structure it is possible to generate an XML with the following steps:

1. retrieve the configured xmlDocumentDef

• xmlDocDef = (XmlDocumentDef)getBroker().getInstance(XmlDocumentDef.class,"key = " + <doc_key>);

2. create a context hash map containing the information needed by the filler to populate the dataBeans map.

3. create a new output stream used to write the XML-file generated

4. call xmlDocDef.produce(context, outputStream);

Then the xmlDocDef will generate the dataBeans map calling the filler associated with the file and will invoke the write method on the root tag of the xml.

Then the generation will be propagated to all the sub tags, and the data will be filled according to the fields and methods set in the xml_tag_def table.