• MetadataUploader

Metadata Uploader

Introduction

This page describe how an SQL based Metadata Uploader can be implemented.

The idea is to replace the current Metadata Uploaded with a script and SQL based implementation that runs in the background. The current implementation can only process 100 records. It runs as a HTTP exchange, uses lots of resources and is subject to memory overflows and timeouts.

The new version can be called from the event daemon or run form the command line. It would have virtually no limit on the size of the input file.

Implementation

1. Temporary SQL Table for Holding Input File

Basic Architecture

1. Implement a set of PHP classes that implement:
   1. Creation of an input table
   2. Generation and execution of SQL statements that process the input data and make inserts and updates in the main database
   3. Handling of begin/end transaction
      1. Must also save the input table results to a temp file, in the event of a roll-back
2. Implement a command line driver PHP script that can be used to run the Metadata Uploader from a terminal
3. Implement an event daemon wrapper class so that the Metadata Uploaded can be called from the event daemon. Requires:

   1. Wrapper class that extends the EventHandlerBase class

   2. A front-end transaction that uploads an input file and stores it in a temp file on the server
   3. Create a new event 'metadata uploader' event type
   4. A front-end reporting transaction that displays the results (errors and warnings) of the upload

Input Table

The first step in an upload procedure is to identify the columns in the input file (which is expected to be a tab separated text file). A temporary database table, called the Input Table, is then created with a column for each input field. In addition to the input columns, the following columns are created:

• inLineNumber int - The line number of the input file corresponding to this record

• inErrorLevelId int - Contains a value indicating whether an error, warning or other issue has occured

• inMessage char(255) - Contains a descriptive message for errors and warnings that can be returned to the user

• inId1 int - Space for a ID value, such as a drawingId

• inId2 int - Space for a secondary ID value, such as a revisionId

Note that depending on the type of Metada Upload being performed, the inId1 and inId2 fields might be replaced with fields like drawingId, revisionId, commentId, etc.

Setting IDs and Creating Target Records

Once the input table has been loaded, then the uploader makes a join with the table to be loaded to determin which records are new and which must be created. For an upload of Drawings and Revisions, the following SQL approximates the procedure:

•     -- Get drawingIds
  
      update InputTable, Drawings
        set
          InputTable.drawingId = Drawings.drawingId
        where
          InputTable.drawingCode = Drawings.drawingCode;
  
      -- Get revisionIds
  
      update InputTable, Revisions
        set
          InputTable.drawingId = Revisions.drawingId
          InputTable.revisionCode = Revisions.revisionCode
        where
          InputTable.drawingCode = Drawings.drawingCode;
  

After these statements are executed, then Input Table records with null drawingIds or revisionIds must be created.

•     insert Drawings ( drawingId, drawingCode, ... )
        select (
            counterValue,
            drawingCode,
            ..
          from
            InputTable
          where
            drawingId is null;

Setting Fields in the Target Tables

The following cases for setting fields:

1. The field value can be copied directly into the target table. Examples include text fields, chainage, elevation, etc.
2. The field value is code which must be converted into a ID value via a lookup in a reference data (or other similar) table
3. The field require more complex special processing

A data structure, similar in concept to the one in /app/configSettings-MetaData.php can be defined that lists all the target fields that a upload can set, and includes the information to identify the type of field setting required.

The script cfg/linthal/site/data/loadComment.sql provides examples of how metadata is loaded into the Comments table.

Issues and Open Points

To be completed