...
The purpose of this document is to provide a description of the JSON file export feature of the File Processor framework.
1 - JSON Generation
It was required to export the CDR files and the records related to them in XML-format, and for this reason To be able to export data from Rator database tables into a JSON file, some generic utilities were created in Rator to generate XMLFile 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 XMLJSON-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 XMLJSON-producer just needs the data that will be passed as a map.
...
The encoding used to generate the file is UTF-8, therefore the XML-declaration of the generated XML-files will be "<?xml version="1.0" encoding="UTF-8"?>".
3 - Design
The persistent objects ..
3 - Design
The persistent objects introduced in this framework and which are part of the configuration are XmlTagDef JSONTagDef (table xmlJSON_tagTAG_defDEF) and XmlDocumentDef DocumentDef (xml_document_deftable DOCUMENT_DEF) and ValueFormatter (valuetable VALUE_formatterFORMATTER).
The valueFormatter can also be used outside the XMLJSON-generation.
3.1 -
...
JSONTagDef
A record in the xml JSON_tagTAG_def DEF defines a single tag (key/value pair) of the resulting XMLJSON file, and it has the following attributes:
3.1.1 - Columns
| TAG_NAME | Name of the tag, in the previous example will be . For example Record, Code, Name, Voucher, Id, brand etc | ||||||||||||||||||||||||||||||||||||||||||||||||
| MAP_KEY | METHOD_INPUT | In some cases when the METHOD_NAME is invoked we also need some extra information that must be passed in the method. ForKey to retrieve the correct component from the map. In the previous example this can be HEADER or RECORDS. | |||||||||||||||||||||||||||||||||||||||||||||||
| VALUE_TYPE | Can be FIELD (if the value to print is a field of the map object) or METHOD (if the content is retrieved calling a specific method in the map object). | ||||||||||||||||||||||||||||||||||||||||||||||||
| FIELD_NAME | If the value type is field then this specify the field to print in the tag content. | ||||||||||||||||||||||||||||||||||||||||||||||||
| METHOD_NAME | if the value type is method, then this specify the method to invoke in the map object to retrieve the data to fill the content for the current tag. | ||||||||||||||||||||||||||||||||||||||||||||||||
| PARENT_ID | Defines the ID of the parent tag and therefore it is used to define the structure of the XML-file. The parent ID for the root tag will be null. | ||||||||||||||||||||||||||||||||||||||||||||||||
| FLUSH_CONTENT | if ‘Y’ the content generated in the outputStream will be flushed in the XML-file when the end tag of the current tag is printed. | ||||||||||||||||||||||||||||||||||||||||||||||||
| FORMATTER_ID | ID of the value_formatter table. This can be used to format the output data (see subsection below). | ||||||||||||||||||||||||||||||||||||||||||||||||
Example: HEADER or FILEDEF or RECORD_DEF or tag name of the parent record from which a child tag value can be obtained. HEADER holds the file source record. FILEDEF holds the current instance of JSONDocumenrFiller. RECORD_DEF holds the CDR_RECORD_DEF (with CODE = DETAIL) instance set up for this JSON Document export | |||||||||||||||||||||||||||||||||||||||||||||||||
| VALUE_TYPE | Can be FIELD (if the value to print is a field of the map object) or METHOD (if the content is retrieved calling a specific method in the map object obtained via the key set in MAP_KEY). | ||||||||||||||||||||||||||||||||||||||||||||||||
| FIELD_NAME | If the value type is FIELD then this specify the field to print in the JSON tag content. | ||||||||||||||||||||||||||||||||||||||||||||||||
| METHOD_NAME | if the value type is METHOD, then this specify the method to invoke on the map object to retrieve the data to fill the content for the current tag. | ||||||||||||||||||||||||||||||||||||||||||||||||
| JSON_DATA_TYPE | The data type with which the JSON field value should be represented while writing value to file. Possible value are OBJECT, ARRAY, STRING, NUMBER, BOOLEAN. OBJECT, ARRAY types can be used only for the parent tag types (i.e JSON_TAG_DEF entries that have children). STRING, NUMBER, BOOLEAN types can be used only for the JSON_TAG_DEF entries that do not have any children. Note: JSON Key names will always be surrounded by double quotes. This JSON_DATA_TYPE configuration settings STRING, NUMBER, BOOLEAN are applicable only for values, not the Keys.
| ||||||||||||||||||||||||||||||||||||||||||||||||
| PARENT_ID | Defines the ID of the parent tag and therefore it is used to define the structure of the JSON-file. The parent ID for the root tag will be null. | ||||||||||||||||||||||||||||||||||||||||||||||||
| FLUSH_CONTENT | if ‘Y’ the content generated in the outputStream will be flushed in the JSON-file when the end tag of the current tag is printed. | ||||||||||||||||||||||||||||||||||||||||||||||||
| FORMATTER_ID | ID of the value_formatter table entry. This can be used to format the JSON field value, before applying the JSON_DATA_TYPE (see subsection below). So, first the Value formatter is applied to the field value, and then the JSON_DATA_TYPE setting is applied. | ||||||||||||||||||||||||||||||||||||||||||||||||
| METHOD_INPUT | In some cases when the METHOD_NAME is invoked we also need some extra information that must be passed in the method. For instance in the cdr generation if we have a list of invoice detail lines inside the record tag we need to retrieve only the invoice detail lines related to that billing record and this can be done by defining in this field the tag name related to the object to pass: in this case it will be “Record”. | ||||||||||||||||||||||||||||||||||||||||||||||||
| METHOD_INPUT_CLASS_NAME | This is the class name of the method_input field (“com.CDRator.billing.rating.BillingRecords” in this example). | ||||||||||||||||||||||||||||||||||||||||||||||||
| SEQ_ORDER | If a tag has more than one child, here we can specify the preferred order to print them. | ||||||||||||||||||||||||||||||||||||||||||||||||
| RELOAD_METHOD | If the tag to print is related to a list (i.e. a list of records) then it is possible to load them in different steps. In the XML- generation when the content of the list has been printed then we try to retrieve further elements using the reload method if set. |
3.2 - XmlDocumentDef
An XmlDocumentDef models the definition for one type of XML. 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 ones.
3.2.1 - Columns
| DESCRIPTION | Short description of the document definition |
| KEY | Unique key to retrieve the document definition |
| FILLER_CLASS | Class name used to generate the map for this specific type of xml. This class will also be part of the map if it is needed to call methods inside the filler to retrieve data. |
| XML_ROOT_TAG_ID | Reference of the first tag of the XML. |
| SQL_LOAD_RECORDS | Contains 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. |
| SQL_RELOAD_RECORDS | Contains 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 XML-file. This is used only if the reload_method is setup on at least one of the xml_tag_def entries. 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 |
Note: Please note that the columns sql_load_records, sql_reload_records are added to the XmlDocumentDef class overridden within the FileProcessor Integration project as they are added to support the GenericXMLFiller implementation for XML-file export by FileProcessor engine. So these are available only from the FileProcessor integration project.
3.3 - XmlFiller
...
| .billing.rating.BillingRecords” in this example). | |
| SEQ_ORDER | If a tag has more than one child, here we can specify the preferred order to print them. |
| RELOAD_METHOD | If the tag to print is related to a list (i.e. a list of records) then it is possible to load them in multiple batches. In the JSON file generation when the content of the current batch has been printed then we try to retrieve further elements using the reload method if set. |
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
| DESCRIPTION | Short description of the document definition |
| KEY | Unique key to retrieve the document definition |
| FILLER_CLASS | Class name used to generate the map for this specific type of document. This class will also be part of the map (with key "FILEDEF") if it is needed to call methods inside the filler to retrieve data. |
| ROOT_TAG_ID | Reference of the Root tag of the document. |
| SQL_LOAD_RECORDS | Contains the SQL-query to obtain the records that should be exported to the file. This SQL-query string can contain the label <BILLING_SOURCE_ID> which will be replaced with appropriate value at run-time. |
| SQL_RELOAD_RECORDS | Contains 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 XML-file. This is used only if the reload_method is setup on at least one of the xml_tag_def entries. 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 |
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.
This class contains The concrete subclasses of DocumentFiller class should contain the specific logic for the generation of the XML- document.
Following Concrete subclasses of xmlFiller DocumentFiller can be found in the FileProcessor Integration project: see GenericXMLFiller, XmlFillerBillingRecords and XmlFillerIdl. JSONDocumentFiller
3.3.1 -
...
JSONDocumentFiller
The GenericXMLfiller JSONDocumentFiller class contains the logic for generating the XMLJSON-files from data obtained by joining some database tables by executing the SQL-script configured via the corresponding XML_ DOCUMENT_DEF record. If the required XML-file needs to execute multiple SQL-queries to get the data that is to be placed in the XML-document in different levels, the GenericXMLfiller cannot be used. Adesired JSON-file format cannot be achieved with JSONDocumentFiller sub class, A new subclass of XmlFiller DocumentFiller must be created , if the GenericXMLFiller cannot produce the XML-file in the required format. as per the requirements.
Instructions to set up file processor configuration for
...
JSON-file generation with
...
JSONDocumentFiller :
- Create XMLJSON_TAG_DEF entries for the required XML-JSON file format: A record in the xmlthe JSON_tagTAG_def defines DEF defines a single tag element (key/value pair) of the resulting xmlJSON file. It is possible to define parent tag for the XMLthe JSON_TAG_DEF entryDEF entry, and thus it is possible to define the full hierarchy of the XML-elements.
Create an XML_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.dataloaderdocument.cdrdata.xmljson.GenericXMLFillerJSONDocumentFiller', XML_ ROOT_TAG_ID = '<Id of the XML-JSON Root Tag entry from XMLJSON_TAG_DEF entries created for this XML-JSON file format>'
- The SQL_LOAD_RECORDS column of the above created XML_created DOCUMENT_DEF record must contain the SQL - query to obtain the records that should be exported to the file. This 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 current source record currently being processed to create the XML-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 XML_created DOCUMENT_DEF record must contain the SQL-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 XML-file. This is used only if the reload_method is setup on at least one of the xml_tag_def entries. This SQL-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.
Make sure that the SQL-scripts setup in SQL_LOAD_RECORDS, SQL_RELOAD_RECORDS columns of the corresponding XML_DOCUMENT_DEF are valid.<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 ='XMLJSON' , BILLING_SOURCE_TABLE_NAME = <Source table name>'VOUCHER_EXPORT_SOURCE', BILLING_SOURCE_CLASS= <Java class for Billing Source>'com.CDRator.billing.product.vouchers.export.data.VoucherExportSource', EXPORT_TYPE = '<The Export_Type for the Customer this XML-file is generated for>', XML_DOCUMENT_DEF_ID = '<ID of the XML_DOCUMENT_DEF record created in above step>'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 = <DB table name for the records to be exported>'VOUCHER_EXPORT_RECORD', RECORD_CLASS = <Java class name for the Records to be exported>'com.CDRator.billing.product.vouchers.export.data.VoucherExportRecord', CDR_FILE_DEF_ID = '<ID of the CDR_FILE_DEF created in above step>'. The XML-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 an earlier step>', FILE_TRANSMITTER_ID= '<Id of the corresponding FILE_TRANSMITTER entry>' and BRAND_ID = '<Corresponding brandId>'
Important Note: It It is mandatory to configure both XML_ DOCUMENT_DEF.SQL_LOAD_RECORDS and XML_DOCUMENTand DOCUMENT_DEF.SQL_RELOAD_RECORDS columns with appropriate SQL - queries, because the File processor XML-filler classes are JSON filler class is configured to load only a maximum of 10,000 10000 records in one select. If there are more records in that database table, and we want to add them to the XML-fileVoucher Batch, the reload_method column of the relevant xmlrelevant JSON_tagTAG_def entry DEF entry must be set to 'reloadRecords'. In that case, after printing the first set of 10,000 10000 records, the GenericXMLFiller tries the JSONDocumentFiller tries to load next batch of records with an ID records with IDs greater than the ID of the last retrieved record in the previous step.
Example value for XML_For voucher export, value of DOCUMENT_DEF.SQL_LOAD_RECORDS column can should be:
...
| language | sql |
|---|
...
Select vr.*
...
from
...
VOUCHER_EXPORT_RECORD
...
vr
...
where
...
vr.VOUCHER_EXPORT_SOURCE_ID
...
=
...
<BILLING_SOURCE_ID>
...
order
...
by
...
vr.id
Example And value for XML_of DOCUMENT_DEF.SQL_RELOAD_RECORDS column can column should be:
...
| language | sql |
|---|
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 20,000 20000 records, then the GenericXMLFiller uses the JSONDocumentFiller uses the SQL - queries configured in the above parameter tree entries like this:the DOCUMENT_DEF record as explained below:
- The query from RECORDfrom SQL_RETRIEVELOAD_SQL parameter RECORDS column is used to load the first batch of records (10000).
- If there are more records to be added to the same XML-JSON file and the reload_method on one of the XMLJSON_TAG_DEF entries is set to 'reloadRecords', the SQL from RECORD_RETRIEVE_from SQL_WITH_ID_GREATERTHAN_CLAUSE parameter 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.3.2 - XmlFillerBillingRecords and XmlFillerIdl
...
- each subsequent batch, until all pending records have been added to the file.
3.4 - ValueFormatter
The value formatter has been introduced is used in order to be able to configure the formatting of objectsfield values. Here a formatter class and a pattern can be specified that will be used to generate a string.
Two of them have been committed in Core, one for dates and one for numbersFollowing Value Formatter types are available:
- com.CDRator.billing.utils.formatter.DateFormatter
- com.CDRator.billing.utils.formatter.NumberFormatter
...