File Processor Framework

Document Logs
Change log:

1 - Purpose of Document

This document presents the design of the file processor framework. This framework can be used to load and export any type of ASCII-based file. The design has been based on loading and exporting of CDR (Call Detail Records) ASCII files. As such must references have been made to CDR files. However, the same concept has been used for loading/exporting other non-CDR ASCII based files

2 - Design Overview

A CDR File is a file containing CALL DETAIL RECORDS in a pre-defined format and order. This document aims at modelling a CDR File in a generic way. This could also be extended to any normal ASCII-based file. As such this document could also be used to model any ASCII-based file.

2.1 Design

A CDR file is made up of one or more logical CDR Records separated by a pre-defined separator (delimiter). The default separator is the systems line separator which in most cases is a carriage return line feed.

A CDR file has three types of records.

1. Header Record
2. Body Record
3. Trailer Record.

A CDR record is made up of one or more CDR fields, separated by a pre-defined separator.
A CDR record may be identified by one or more CDR_RECORD_IDENTIFIERs. The default record (NO CDR_RECORD_IDENTIFIER) is a detail record.
A CDR Field contains values in a pre-defined format, which will henceforth be referred to as a CDRFieldFormatDef (CDR_FIELD_FORMAT_DEF).

2.2 CDR File Model

A CDR File is made up of one or more physical lines (lines in a normal file). A CDR File is also made up of one or more logical lines. A physical line may contain one or more logical lines. A logical line represents a CDR record. Hence, it is possible for a logical line to span one or more physical lines. A delimiter or separator determines the separation of a physical line into logical lines.

In a typical scenario, one physical line will equal one logical line.

To model the description presented in section 2.1 the following tables are defined:

1. CDR_FILE_DEF (class CdrFileDef)
2. CDR_RECORD_DEF (class CdrRecordDef)
3. CDR_FIELD_DEF (class CdrFieldDef)
4. CDR_FIELD_FORMAT_DEF (class CdrFieldFormaDef)
5. CDR_RECORD_FIELD_DEF (class CdrRecordFieldDef)
6. CDR_RECORD_IDENTIFIER (class CdrRecordIndentfier)
7. CDR_PARSER (class CdrParser)
8. ITEM_SEPARATOR (class ItemSeparator)

All the above mentioned classes are persistent. In addition to the persistent classes, the following non-persistent helper classes are present:

1. CdrFile
2. CdrRecord
3. CdrField

Figure 2.1 (below) represents the above tables and their respective classes:

A file definition (CDR_FILE_DEF) defines the source table where the source information of files to be loaded are stored. The file definition defines a BILLING_SOURCE_CLASS, a filename format, and a pre- and post-processor method. These methods - if present - must be located in the defined BILLING_SOURCE_CLASS. If the filename format is present, then only filenames that match this format will be loaded, otherwise all files defined in the incoming folder will be loaded. The location of the incoming, processed and error folders are defined in a file transmitter (FILE_TRANSMITTER) linked to a file processor (CDR_FILE_PROCESSOR). A file definition also defines a parser (CDR_PARSER) for parsing the file into one or more records.

Linked to a file definition are one or more record definitions (CDR_RECORD_DEF : CDR_RECORD_DEF.CDR_FILE_DEF.ID = CDR_FILE_DEF.ID). There are three types of record definitions; HEADER, DETAIL and TRAILER (CDR_RECORD_DEF.CODE in ('HEADER','DETAIL','TRAILER')). For loading MMD-CDRS, all the three record definition types are used.

Each record definition consists of one or more field definitions (CDR_FIELD_DEF) which define the fields that make up a record and the source table of the values these fields will assume in the export file. A record definition contains a field separator (ITEM_SEPARATOR : CDR_RECORD_DEF.FIELD_SEPARATOR_ID = ITEM_SEPARATOR.ID) that defines a delimiter for separating fields within a record.
A record definition also defines a parser (CDR_PARSER) for parsing the record into one or more fields using the defined field separator.

In order to identify a record type (HEADER,DETAIL,TRAILER), a record identifier (CDR_RECORD_IDENTIFIER) is defined. A record identifier defines a record definition (CDR_RECORD_DEF_ID), a field position (CDR_FIELD_POSITION) and a default field value (DEFAULT_FIELD_VALUE). For example, an MMD-GSM header record is identified by the string 'HD' in position 1 of the record. When a record is loaded and if the record has the value 'HD' at position 1, then the record is considered a header record.

A detail MMD-CDR record has no record type identifier. When an MMD-CDR is loaded, a record is first checked for an identifier. If an identifier can be located, then the record definition identified by the record identifier is used, otherwise the record definition (CDR_RECORD_DEF) without an identifier is used. This means that if a file definition (CDR_FILE_DEF) has N types of record types, then there must be at least N-1 record type definitions, otherwise loading will fail.

A field definition has a field format definition (CDR_FIELD_FORMAT_DEF: CDR_FIELD_DEF.CDR_FIELD_FORMAT_DEF_ID = CDR_FIELD_FORMAT_DEF.ID) that defines the format to be assumed by the field in the export file. A field definition also defines any padding or fillers that must be added to the field before it is written to a file.

2.3 File Processing Model

A CDR File (CDR_FILE_DEF) is processed by a CDR File Processor (CDR_FILE_PROCESSOR), i.e. the file is loaded and parsed or generated and made ready for export. This processing is modelled by the following classes.

1. CdrFileProcessor (Table CDR_FILE_PROCESSOR)
2. CdrFileLoader (Table CDR_FILE_PROCESSOR)
3. CdrFileGenerator (Table CDR_FILE_PROCESSOR)
4. FileTransmitter

The CdrFileLoader and CdrFileGenerator are sub-classes of the CdrFileProcessor. The FileTransmitter will implement a Transmitter interface.

The above design is shown in figure 2.3.1 below:

Figure 2.3.1 File Processor UML.

3 Model Description

3.1 Loading Files

To configure the loading of a file, the following must be defined or configured.

3.1.1 CDR File Definition (CDR_FILE_DEF)

A CdrFileDef defines a CDR file and is defined by a unique ID and KEY. A CDR File Definition must define a CdrParser (CDR_PARSER_ID pointing to a record in the CDR_PARSER table) to be used for parsing the records in the CDR File, and a record separator (RECORD_SEPARATOR_ID pointing to a record in the ITEM_SEPARATOR table). In a normal scenario, it is assumed that the record separator will be a carriage return line feed. In addition to the above mentioned attributes some more attributes are present as shown in figure 1.1. A CdrFiledef consists of one or more CdrRecordDefs. The column present in the CDR_FILE_DEF table have been presented in figure 2.1. Some important columns worth describing include:

3.1.2 CDR Record Definition (CDR_RECORD_DEF)

A CDR Record Definition (CDR_RECORD_DEF) defines a record in a CDR file and has the following columns in the CDR_RECORD_DEF table.

3.1.3 CDR Record Identifier (CDR_RECORD_IDENTIFIER)

A CdrRecordIdentifier consists of:

• a CDR_RECORD_DEF_ID (CDR Record DEF)
• a field position (CDR_FIELD_POSITION), and
• a default value (DEFAULT_FIELD_VALUE).

A Cdr Record Definition can have zero or more CDR_RECORD_IDENTIFIERs. The CDR Record Identifier is used for identifying a CDR RECORD. For example, a Header Cdr Record might be identified by its first field being ‘HD’. In this example, a header CDR_RECORD_DEF will have an entry in the CDR_RECORD_IDENTIFIER table with CDR_FIELD_POSITION equalling 1 and the default field value equalling ‘HD’. In this situation, the RECORD_IDENTIFIER_TYPE attribute in the CDR_RECORD_DEF table will be ‘ALL’.
In another example, a CDR detail record might be identified by at least one of the following values; ‘20’,’21’,’22’, in its first field. In this scenario the CDR_RECORD_DEF for the detail record will have three entries in the CDR_RECORD_IDENTIFIER table each with a field position equal to 1 and default field value ‘20’,’21’,’22’, respectively.

3.1.4 CDR Field Definition (CDR_FIELD_DEF)

A Cdr Field Defintion (class : CdrFieldDef, Table: CDR_FIELD_DEF), defines a field in a record (CDR_RECORD_DEF). Its attributes are shown in figure 2.1 and has the following columns.

3.1.5 CDR Record Field Definition (CDR_RECORD_FIELD_DEF)

A CDR record field definition (Class:CdrRecordFieldDef, Table: CDR_RECORD_FIELD_DEF) defines the position (FIELD_POSITION) of a CdrFieldDef (hence a CdrField) in a CdrRecordDef (hence a CdrRecord). The CdrRecordFieldDef implements the ISequence interface. This interface ensures that no two CdrFieldDefs in a CdrRecordFieldDef have the same position. If a CdrFiedDef is added to a CdrRecordDef with the same position as an already existing CdrFieldDef, the new CdrFielddef will take the place of the old CdrFieldDef and the old CdrFielddef will be pushed on to a position behind.

Each time a new CdrFieldDef is added to a CdrRecordDef, an entry is made in the CDR_RECORD_FIELD_DEF table. The method addCdrFieldDef(CdrFieldDef, int) takes the CdrFieldDef to be added and the position the field occupies. This method returns the next available position to which a new CdrFieldDef could be added. A CdrRecordDef consists of one or more CdrRecordFieldDefs.

The following columns are found in the CDR_RECORD_FIELD_DEF table:

3.1.6 CDR Field Format Definition (CDR_FIELD_FORMAT_DEF)

A CDR field format definition (Class:CdrFieldFormatDef, Table: CDR_FIELD_FORMAT_DEF) defines the format of a field definition (CDR_FIELD_DEF). It has the following columns:

3.1.7 Item Separator (ITEM_SEPARATOR)

An Item Separator (Class:ItemSeparator, Table: ITEM_SEPARATOR) defines how an item (CDR_RECORD (in a file) or CDR_FIELD (in a record)) is separated. An item separator has a SEPARATOR attribute that defines what separates a record/field from another. The default separator for a CDR_RECORD is a carriage return line feed while there is no default separator for a CDR_FIELD. This has to be defined.

An Item separator is used by a CDR Parser to parse a CDR record and a CDR Field. Special types of Item separator exists. These include the Index Item Separator and the Null Item separator. As a NULL cannot be defined in the database, NULL item separator (Null delimiter) has been defined. When a NULL item separator is used, the system will set the correct Java definition of a null string as the separator. When records or fields are separated by indexes, the Index Item Separator (Index delimiter) is used. In order to use an Index Item separator the start index, end index and the max. length of the first record/field must be defined. Please note that the start index, end index and max. length on an Index Item separator are used only to identify the first record/field. The locations of the subsequent fields must be defined in the CDR_FIELD_DEF.

The Item Separator table has the following columns:

3.1.8 CDR Parser

A CDR Parser (Class:CdrParser, Table: CDR_PARSER) parses a CDR File into CDR records and a CDR record into CDR Fields. A CDR file definition knows the CDR parser it needs to parse a CDR file into CDR records while a CDR record knows the CDR parser it needs to parse a CDR record into fields. By default, a CDR Parser will use the carriage return line feed to parse a CDR file into CDR records. However, to parse a CDR record into CDR Fields a field separator (ITEM_SEPERATOR) is needed. This is set by the appropriate CDR Record Definition at run time.

3.1.9 File Transmitter

The File transmitter (Class:FileTransmitter, Table: FILE_TRANSMITTER) defines where a CDR File is located before processing, where it is put after successful or unsuccessful processing, for loading. It also knows where an export file is to be created and where it should be put after successful or unsuccessful file generation. The File transmitter handles the writing to a file during file generation (export).

3.1.10 CDR File Processor

The CDR File Processor (Class:CdrFileProcessor, Table: CDR_FILE_PROCESSOR) handles both loading and generation (export) of CDR files. A CDR File processor knows the CDR File Definition and File Transmitter to use when handling a CDR File. The CDR FILE PROCESSOR has as sub classes CdrFileLoader (CODE = 'LOADER') and CdrFileGenerator ( CODE = 'GENERATOR'). As the names imply, the CdrFileLoader handles loading of files while the CdrFileGenerator handles generating of files. The CdrFileGenerator does not handle exporting of a file to any third party. This will be handled by a different process.

The CDR_FILE_PROCESSOR table has the following columns

3.1.11 Invoking Hookpoint in the Pre or Post Process

It is possible to invoke a hookpoint in both the pre process and post process of file processing. This functionality is activated by adding the Hookpoint Key to either the PRE_PROCESSOR_HOOKPOINT_KEY or POST_PROCESSOR_HOOKPOINT_KEY column of the CDR_FILE_DEF. If the PRE_PROCESSOR_HOOKPOINT_KEY value is not emty the hookpoint key with the prefix "FILE_PROCESSOR_" with the brand of the CDR_FILE_PROCESSOR is attempted invoked during the specific process. The particular CdrFile object by the FileProcessor is inserted into context under the key "CDR_FILE".

3.2 Generating (Export) Files

In order to generate CDR files for export, in addition to the tables defined in 3.1 above, the following table must also be defined.

Unlike for loading where the header/trailer information of a CDR File is stored in a BILLING SOURCE table, the header/trailer information used for generating an export file is stored in an SP_FILE_HEADER table and the BILLING RECORD table must have a column named SP_FILE_ID, that points to the SP_FILE_HEADER record that will be used to generate the export file. An SP_FILE_HEADER table must have at least the following columns ID, FILENAME, BRAND_ID, STATUS and FILE_CREATED_DATE. These columns are inherently used within the model.

Sometimes, when generating a file for export, some information needed for export (usually in the header and/or trailer records) is not yet present. This information could be generated by a pre-defined stored procedure that will be called before any file is generated for export. If such a pre-defined stored procedure is needed, then it must accept one the SP_FILE_HEADER_ID as parameter since all processing must depended on the SP_FILE to be generated. The column PRE_EXPOR_PROCEDURE on the CDR_FILE_DEF table holds the value of the pre-defined stored procedure. If such a procedure exists, it will be called before the export file is generated.

To generate any other file, the header and trailer records should be stored in the BILLING_SOURCE_TABLE_NAME table defined in the CDR_FILE_DEF while the detail record definition (CDR_RECORD_DEF) pointing to this CDR_FILE_DEF should have a SOURCE_TABLE_KEY appropriate defined as described in section  3.1.2 CDR Record Definition (CDR_RECORD_DEF) and the BILLING_SOURCE_TABLE_NAME table must have a column following columns ID, FILENAME, BRAND_ID, STATUS and FILE_CREATED_DATE. All other processing is the same as described above.

It should be noted that only records in the BILLING_SOURCE_TABLE_NAME table that have status = 1 (ready for export) will be pickec up by the CDR_PROCESSING_ENGINE (CODE = 'GENERATOR')

3.3 Setup Of File Loader and Generator Engines

The CdrFileProcessing engine handles loading files and generating files for export. The CdrFileProcessignEngine takes at most three parameters.

• Parameter 1 is the engine name (“CDR_FILE_LOADER_ENGINE” (for CdrFileLoaderEngine) or “CDR_EXPOR_FILE_GENERATOR_ENGINE” (for CdrExportFileGeneratoeEngine)), or “CDR_FILE_PROCESSING_ENGINE” (for both loading and file generation)).
• Parameter 2 is the Engine Instance Number
• Parameter 3 is the type of processing (“LOADER” (for CdrFileLoaderEngine) or “GENERATOR” (for CdrExportFileGeneratoeEngine)).
  If parameter 3 is not set, then the engine will perform both loading and export file generation.

4 Parameter Setup

The following table shows the parameter setup that can be used by CdrFileprocessorEngine. The parameters are under the node "FILE_PROCESSING".

5  MONITORING

5.1 File Generator

If the File Processor engine cannot generate the file due to an exception, it will insert an entry into EXCEPTION_LOG table. The ID from EXCEPTION_LOG.EXCEPTION_STACK_TRACE_ID can be used to find the corresponding entry from EXCEPTION_STACK_TRACE, which contains the stack trace of the exception. 

select * from EXCEPTION_LOG where TABLE_NAME = 'CDR_FILE_PROCESSOR' and SOURCE_KEY = '<CDR_FILE_PROCESSOR.NAME for corresponding CDR_FILE_PROCESSOR entry>' order by id desc; 

5.1 File Loader

If the File Processor engine cannot load  the file due to an exception or due to errors while parsing the file, it will insert an entry into LOAD_ERRORS table. 

File Processor engine inserts an entry into CDR_FILE_PROCESSOR_LOG table upon successful loading of the file.