XML-DB Mapping Project Is Done

Exception:

1. the address tags verification.
2. the address parsing and elements verification.
   
3. incorporate results to dashboard.

I will visit these left-overs once the higher up approves the time for testing the other originators.

Back To SoCal

Arriving at ONT at 10AM, show up at the office around 11:30AM.

While finishing the NGS Policy XML - database verification, I uncorked nasty application behavior. Consider:

<party>
  <name>Acme</name>
  <address>Address1</address>
  <address>Address2</address>
</party>

Normally this XML is transformed into:

Party table
PartyID	Name
1	Acme

Address table
AddressID	PartyID	Address
1	1	Address1
2	1	Address2

But nooooooo, the wise developer did this instead:

Party table
PartyID	Name	Addr1	Addr2
1	Acme	Address1	Address2

Now I have to think about catching the wrench before it hits the wheels.

Anyway, mum bought a big painting on Sunday. Since it didn't fit into Lisa's car, I was volunteered to transport the items.

I managed to get some rest around 10PM.

Long day - it is.

Why Convert XML Data To Hash Structure Using XML::SAX?

Instead of using XML::Simple? The structure, generated by XML::Simple, is not consistent since it recognizes an empty tag as empty hash structure, instead of null string.

1. Consider this XML data:
   

   <Order>
     <Number>01234</Number>
     <Status />
   </Order>

2. Using XML::Simple produces:

   $VAR1 = {
     'Order' => {
       'Status' => {},
       'Number' => '01234'
     }
   };

3. Utilizing XML::SAX, we could insert a null string instead of an empty hash structure:

   $VAR1 = {
     'Order' => {
       'Status' => '',
       'Number' => '01234'
     }
   };

Remember: it is simpler to assume that all end of hash structure branch is represented by ref($hash) eq "".

How To Slow Cook A Piglet

This is how the pieces are glued together:

1. Consider the original XML data:
   

   <Order>
     <Number>01234</Number>
     <Status>OPEN</Status>
   </Order>

2. The XML data is translated into Perl data structure, utilizing XML::SAX(why?):

   $VAR1 = {
     'Order' => {
       'Status' => 'OPEN',
       'Number' => '01234'
     }
   };

   The unordered hash index will not mangle the report anymore since:


3. Along the way (of parsing XML data using XML::SAX), a report template is created with references at the right places.
   
   

   	Status	Notes
   <Order>
   <Number>01234</Number>	${$REPORT[0]}{'RESULT'}	${$REPORT[0]}{'NOTES'}
   <Status>OPEN</Status>	${$REPORT[1]}{'RESULT'}	${$REPORT[1]}{'NOTES'}
   </Order>



4. To the bridge the result of comparison and the report template, another data structure is created (will be called: bridge) and it has an exact hash structure as the XML data, but it is holding references to the report template.

   $BRIDGE1 = {
     'Order' => {
       'Status' => '$REPORT[1]',
       'Number' => '$REPORT[0]'
     }
   };

5. Consider the check data for the XML data above:
   

   <Order>
     <Number><SQL>'SELECT Status FROM Order WHERE ORDER_ID = <value>'</SQL></Number>
     <Status><SQL>'SELECT OrdNum FROM Order WHERE ORDER_ID = <value>'</SQL></Status>
   </Order>

   Readability is the main reason the check/validation is written in XML format.


6. Converted to Perl data structure using XML::Simple

   $CHECK1 = {
     'Order' => {
       'Status' => { SQL => 'SELECT Status FROM Order WHERE ORDER_ID = <value>'},
       'Number' => { SQL => 'SELECT OrdNum FROM Order WHERE ORDER_ID = <value>'},
     }
   };

7. Iterating through the XML hash structure, while maintaining same path of traversal with the bridge and check hash structure:
   

   $XML{'Order'} 
     -> $BRIDGE1{'Order'} 
       -> $CHECK1{'Order'}
   
   $XML{'Order'}{'Status'} 
     -> $BRIDGE1{'Order'}{'Status'} 
       -> $CHECK1{'Order'}{'Status'}

8. Find the end of XML hash structure branch to extract the value.
   

   if (ref($XML{'Order'}{'Status'}) eq "") {
     $XMLValue = $XML{'Order'}{'Status'};
   }

9. Get the SQL syntax from check hash and get the content from database.
   

   if (exists $CHECK1{'Order'}{'Status'}{'SQL'}) {
     $SQL = $CHECK1{'Order'}{'Status'}{'SQL'};
   }
   else {
     # bitch later: no SQL defined.
   }
   $DBVALUE = getValue($SQL);

10. Compare the content from XML hash structure and database and write the result out to $REPORT[$index], which provided by bridge hash.
    

    if ($XMLValue eq $DBVALUE) {
      eval "\${".$BRIDGE1{'Order'}{'Status'}."}{'RESULT'} = 'PASSED';";
      eval "\${".$BRIDGE1{'Order'}{'Status'}."}{'NOTES'}  = '$NOTE';";
    }
    else {
      eval "\${".$BRIDGE1{'Order'}{'Status'}."}{'RESULT'} = 'FAILED';";
      eval "\${".$BRIDGE1{'Order'}{'Status'}."}{'NOTES'}  = '$NOTE';";
    }

11. After traversing all of the branches of XML hash structure, open the report template and evaluate template, producing:
    
    

    	Status	Notes
    <Order>
    <Number>01234</Number>	PASSED
    <Status>OPEN</Status>	PASSED
    </Order>

    
    

Need To Glue All of The Pieces Together

These items are created:

1. Data - contentRef.conf (PDS)
2. Report.Template.html (HTML)
3. Bridge - reportRef.conf (PDS)
4. Report.html (HTML)
   

Need to glue them all together to generate report, may take several days to work through the exceptions.

Convert XML to Perl Hash Using XML::SAX

Finally, the XML is converted to Perl data structure through XML::SAX.

The content of contentRef.conf is equal to XML::Simple.

Diagram

Data(XML)                                  
|
|XML::SAX                           ---
|                                   |s|
|-> Data - contentRef.conf (PDS) -->|c|
|-> Report.Template.html (HTML) --->|r|
|-> Bridge - reportRef.conf (PDS) ->|i|-> Report.html (HTML)
                                    |p|
|-> Validation (PDS) -------------->|t|
|                                   ---
|XML::Simple                         |
|                                    |
Validation - CHECK.XML (XML)      Database

XML::SAX for Comparison Report

Toying with XML::SAX again since the comparison report has to reflect the original XML.

[Insert sample here]

Currently I am writing a script to mimic XML::Tidy.

See The Concept Takes Flight!

Yes, the concept works.

But it flops badly when the comparison report is generated. The regenerated XML is different from the original one, since standard Perl hash doesn't have the index ordered by insertion.

Thinking...

Comparing Data Between XML And Database

There is a need to instantly verify the content in XML with the content that's being pushed the application to the database.

The project started this Tuesday.

I started with converting the structure of the XML data into Perl hash using XML::Simple. Also, the data structure could serve as the validation data.

Consider this XML data:

<Order>
  <Number>01234</Number>
  <Status>OPEN</Status>
</Order>

Converting the XML to Perl hash using XML::Simple (with KeepRoot => 1) yields:

$VAR1 = {
  'Order' => {
    'Status' => 'OPEN',
    'Number' => '01234'
  }
};

The hash structure could be manipulated as validation data:

$VAR1 = {
  'Order' => {
    'Status' => 'SELECT Status FROM Order WHERE ORDER_ID = <value>',
    'Number' => 'SELECT OrdNum FROM Order WHERE ORDER_ID = <value>'
  }
};

Iterating through the data structure to extract the data and the SQL statement to extract content from database, it is feasible to validate the entire XML structure provided a validation data with similar structure using an algorithm.