Telemetry data download and storage (FTP and web services)

[[)roi(]]

                 ┌──────────────┐                        
                 │ FTP incoming │                        
                 │    folder    │                        
                 └──────────────┘                        
                         │                               
                         │                               
                         ▼                               
                 ┌──────────────┐      ┌─────────────────┐
                 │ Scan folder, │      │  File Metadata  │
                 │ compare with │◀───▶│  incl. import   │
                 │   Metadata   │      │state: e.g. done │
                 └──────────────┘      └─────────────────┘
                         │                               
                         │                               
        ┌─if new .xml────┴─────if .open┐                 
        │                              │                 
        ▼                             ▼                 
┌──────────────┐           ┌──────────────────────┐      
│  Import new  │           │   Import to Active   │      
│   .xml to    │           │  (incl. Diffing or   │      
│   Archive    │           │ simply drop, create) │      
└──────────────┘           └──────────────────────┘

RE PHP, import should be fairly simple based on what you've shared. For example:

$xml = simplexml_load_file("filename.xml");
foreach (records->record as $record) {
  printf("<p>assets: %s <br>assetname: %s</p>",
        $record->assetid,
        $record->assetname
    );
}

 

[gkm]

Have you maybe considered using some kind of queue service (RabbitMQ, SQS etc), instead of copying files around?

 

[[)roi(]]

Have you maybe considered using some kind of queue service (RabbitMQ, SQS etc), instead of copying files around?

As opposed to? the use of FTP?
Is this what you're roughly referring to?


If yes, then that really going to depend on the source server, hardly useful if it's solid state, plus pushing onto the queue at the source end implies an ability to introduce changes on the source server. @imranpanji would have to confirm if that's possible / agreeable.

Plus how do you envisage dealing with the .open file i.e. no fixed trigger point, aside from the hourly completion / state change You could push a copy onto the queue at a timed interval, but then we probably need to question the merits of this over FTP?

 

[Hamish McPanji]

As opposed to? the use of FTP?
Is this what you're roughly referring to?
View attachment 402412

If yes, then that really going to depend on the source server, hardly useful if it's solid state, plus pushing onto the queue at the source end implies an ability to introduce changes on the source server. @imranpanji would have to confirm if that's possible / agreeable.

Plus how do you envisage dealing with the .open file i.e. no fixed trigger point, aside from the hourly completion / state change You could push a copy onto the queue at a timed interval, but then we probably need to question the merits of this over FTP?

Only basic FTP access to the source server. I can't even delete files there, they disappear after 30 days

 

[Hamish McPanji]

This is what i have so far.
If any of you feckers laugh at my spagetti code, am going to clap you some choice ones.
This is my first real squizz at PHP , so deal with it. I even had to google how to add comments
And it works, i tested it

<?php


$Companyname="transform";  //Will be changed to a function input parameter in future
$XSLTSTART = file_get_contents('./xsltstart.txt'); //beginning of the xslt transformation

// echo $XSLTSTART;

//loop through all the xml files in the $Companyname folder
foreach (glob("./" . $Companyname . "/*.xml") as $filename) {

//  echo "$filename size " . filesize($filename) . "\n";
//	echo basename($filename). "\n";

// Concat the XSLT contruct to complete the XSLT transform document.
$XSLTTRANS = $XSLTSTART . "<Company>" . $Companyname . "</Company>\n<file>" . basename($filename) . "</file>\n</xsl:template>\n</xsl:stylesheet>";
// echo $XSLTTRANS;	


//Create a DOM object and load up the XSSLT Transform
$XSLTDOM = new DOMDocument();
$XSLTDOM->loadXML($XSLTTRANS);
//echo $XSLTDOM->saveXML();

// Open the XML file using DOM 
$XMLPRETRANSFORM = new DOMDocument();
$XMLPRETRANSFORM->load($filename);
//echo $XMLPRETRANSFORM->saveXML();

// Prepare XSLT Processor
$XSLTFINALPROC = new XSLTProcessor;
$XSLTFINALPROC->importStyleSheet($XSLTDOM); // attach the xsl rules

echo $XSLTFINALPROC->transformToXML($XMLPRETRANSFORM);

//ToDo :Add Code for database importation
//ToDo :Error Handling 


//In process : Move completed file to done folder or rename
$donefile = $filename . ".done";

if (!copy($filename, $donefile)) {
    echo "failed to copy $filename...\n";
} else {
    unlink($filename);
}
	
}
?>

I just have to do the SQL update thing

The : $XSLTFINALPROC->transformToXML($XMLPRETRANSFORM); gives me the output i find acceptable to import into the DB

Any suggestions? Especially on the error handling side and loading the data into the DB
I also rename the files that have been processed at the end, and it does all xml files in the folder

 

[Hamish McPanji]

                 ┌──────────────┐                        
                 │ FTP incoming │                        
                 │    folder    │                        
                 └──────────────┘                        
                         │                               
                         │                               
                         ▼                               
                 ┌──────────────┐      ┌─────────────────┐
                 │ Scan folder, │      │  File Metadata  │
                 │ compare with │◀───▶│  incl. import   │
                 │   Metadata   │      │state: e.g. done │
                 └──────────────┘      └─────────────────┘
                         │                               
                         │                               
        ┌─if new .xml────┴─────if .open┐                 
        │                              │                 
        ▼                             ▼                 
┌──────────────┐           ┌──────────────────────┐      
│  Import new  │           │   Import to Active   │      
│   .xml to    │           │  (incl. Diffing or   │      
│   Archive    │           │ simply drop, create) │      
└──────────────┘           └──────────────────────┘

RE PHP, import should be fairly simple based on what you've shared. For example:

$xml = simplexml_load_file("filename.xml");
foreach (records->record as $record) {
  printf("<p>assets: %s <br>assetname: %s</p>",
        $record->assetid,
        $record->assetname
    );
}

This actually looks decent.
What do you mean by "Scan folder, compare with metadata"

In the MySQL, should i have a seperate table for the file names already imported? Or is there somewhere else i can put that information?
I have done it differently, by adding a seperate column for each record. Yes, i know it's not normalised and it's inefficient.

Schit, now i feel like an idiot. No worries, will fix later (famous last words).
Half my problem is i am using code and sql after more than 10 years. So i am half focussed on learning and the only half on getting the solution right.

 

[Hamish McPanji]

So my database structure as follows:

First i will import all data into TelemetryImport



at some future stage i will extract the data from TelemetryImport data into the other tables which are normalised
Do you think that i have missed anything?

 

[[)roi(]]

This actually looks decent.
What do you mean by "Scan folder, compare with metadata"

In the MySQL, should i have a seperate table for the file names already imported? Or is there somewhere else i can put that information?
I have done it differently, by adding a seperate column for each record. Yes, i know it's not normalised and it's inefficient.

Schit, now i feel like an idiot. No worries, will fix later (famous last words).
Half my problem is i am using code and sql after more than 10 years. So i am half focussed on learning and the only half on getting the solution right.

The diagram is meant to roughly depict 3 tables; 1 for archived records, 1 for active records (.open) and 1 for the file metadata: this table contains information about the files that your import routine has processed (incl. status); the basic idea is that if a file in the folder is flagged as processed in the metadata table, you can skip over it i.e. only process the new files. The principle is that mirroring folders, prevents you from moving already imported files to a processed folder.

In your diagram, your Importfiles table is the metadata table I was alluding to.

 

[Hamish McPanji]

I have hit a bit of a roadblock. My phpmyadmin is rejecting credentials for some reason, so i have to do sql command line.
Looking for alternate web manage mysql server apps

Any suggestions?

 

[Thor]

I have hit a bit of a roadblock. My phpmyadmin is rejecting credentials for some reason, so i have to do sql command line.
Looking for alternate web manage mysql server apps

Any suggestions?

Why is it doing that?

Are you on local line wamp then it is root and no password.

Otherwise, send me your teamviewer details. (I love phpmyadmin I can help you)

 

[Hamish McPanji]

Why is it doing that?

Are you on local line wamp then it is root and no password.

Otherwise, send me your teamviewer details. (I love phpmyadmin I can help you)

No, this is my Linux server on the internet.
I managed to sort it out, had to change a few things to get wirking again.

Have drawn out my sequencing of how events should occur based on feedback from this thread.

Just recreating my db accordingly.

 

[Hamish McPanji]

Am really getting knocked for diving headstrong into PHP without studying the syntax at all.
Am struggling with this at the moment

I have an array from the XML columns which i retrieved via an xslt transform. It retrieve for me the unique node values in the file (translates to columns for me).

I am using the below code to retrieve the Columns that exist in the Database.
I want it to go into and array

The idea is to compare the 2 arrays for differences, and add the columns to the table that dont exist into the database as required. Over time, all the columns will already exist, so it's only the initial period where this will have some results

$sqlColumns = "SELECT * FROM COLUMNS WHERE  TABLE_SCHEMA='TelemetryArchive' and$
//echo $sqlColumns;

$resultColumns = mysqli_query($conn, $sqlColumns);
//echo mysqli_num_rows($resultColumns);

$row=mysqli_fetch_array($resultColumns,MYSQLI_NUM);
printf ("%s (%s)\n",$row[0],$row[1]);
print_r (array_values($row));

My problem is as follows:

I don't actually know what the type and content of this array is , and so am unable to do standard array comparison
Is there a way of dumping the values into a standard array so that i can compare?

 

[Hamish McPanji]

I hit a major roadblock. A lot of the XML does not pass validation. Different errors with about 5% of the files. Unfortunately that for the purpose I wanted to use it for....stuff like trip history, it's essentially over unless I manually parse through the file as text. Have contacted the developers about it.

Will now look to the live file instead now, where I essentially only have to display the last valid records only.

Was definitely disappointed in the way the php domdocument object handles errors. It cannot be error handled easily, as it shows internal errors without throwing up a normal php error. Anyone had experience with this issue?

 

[Thor]

I hit a major roadblock. A lot of the XML does not pass validation. Different errors with about 5% of the files. Unfortunately that for the purpose I wanted to use it for....stuff like trip history, it's essentially over unless I manually parse through the fila as text.

Will now look to the live file instead now, where I essentially only have to display the last valid records only.

Was definitely disappointed in the way the php domdocument object handles errors. It cannot be error handled easily, as it shows internal errors without throwing up a normal php error. Anyone had experience with this issue?

Example please

 

[Hamish McPanji]

Example please

Of the XML, or the php XML domdocument errors?

 

[Thor]

Of the XML, or the php XML domdocument errors?

All of it - How sensitive is this?

Mind mailing me the code? - If not post a sample how you are phrasing and what validation is failig

<?php

function libxml_display_error($error)
{
    $return = "<br/>\n";
    switch ($error->level) {
        case LIBXML_ERR_WARNING:
            $return .= "<b>Warning $error->code</b>: ";
            break;
        case LIBXML_ERR_ERROR:
            $return .= "<b>Error $error->code</b>: ";
            break;
        case LIBXML_ERR_FATAL:
            $return .= "<b>Fatal Error $error->code</b>: ";
            break;
    }
    $return .= trim($error->message);
    if ($error->file) {
        $return .=    " in <b>$error->file</b>";
    }
    $return .= " on line <b>$error->line</b>\n";

    return $return;
}

function libxml_display_errors() {
    $errors = libxml_get_errors();
    foreach ($errors as $error) {
        print libxml_display_error($error);
    }
    libxml_clear_errors();
}

// Enable user error handling
libxml_use_internal_errors(true);

$xml = new DOMDocument(); 
$xml->load('example.xml'); 

if (!$xml->schemaValidate('example.xsd')) {
    print '<b>DOMDocument::schemaValidate() Generated Errors!</b>';
    libxml_display_errors();
}

?>

https://secure.php.net/manual/en/ref.libxml.php

 

[Hamish McPanji]

Example please

The records are meant to start and end with <record></record> tags and in between have assetid,assetname, date , lat,lon,alt,heading,odo,address at minimum. Other nodes may he present...have coded this is they are added as required

Errors in the XML below:

Odo and address missing :

** <lon>13.1580269444444</lon>

* * <altitude>39.5</altitude>

* * <active>False</active>

* * <speed>0</speed>

* * <heading>15</heading></records>

A wtf moment for me:

* * <lat>-8.97141722222222</lat>

* **<lon>13.1659169444444</lon>

* * <alt <record>
 * <assetid>7fb755f1-66a7-485b-9973-20a4f6010fa0</assetid>

*

And another:
*

<ac></ac>

* * <cargodoor>Closed</cargodoor>

* * <panic>Off</panic>

* * <fu <record>

* * <assetid>fab98f46-259e-4fa4-b31f-d3d1d43c1c32</assetid>

* * <assetname>Skais XTRAIL</assetname>

* * <date>11/9/2016 10:53:25 AM</date>

 

[Thor]

The records are meant to start and end with <record></record> tags and in between have assetid,assetname, date , lat,lon,alt,heading,odo,address at minimum. Other nodes may he present...have coded this is they are added as required

Errors in the XML below:

Odo and address missing :

** <lon>13.1580269444444</lon>

* * <altitude>39.5</altitude>

* * <active>False</active>

* * <speed>0</speed>

* * <heading>15</heading></records>

A wtf moment for me:

* * <lat>-8.97141722222222</lat>

* **<lon>13.1659169444444</lon>

* * <alt <record>
 * <assetid>7fb755f1-66a7-485b-9973-20a4f6010fa0</assetid>

*

And another:
*

<ac></ac>

* * <cargodoor>Closed</cargodoor>

* * <panic>Off</panic>

* * <fu <record>

* * <assetid>fab98f46-259e-4fa4-b31f-d3d1d43c1c32</assetid>

* * <assetname>Skais XTRAIL</assetname>

* * <date>11/9/2016 10:53:25 AM</date>

Make sure you defined the correct types - that is normally the issue (at least in my case)

example.xml
<?xml version="1.0"?>
<example>
    <child_string>This is an example.</child_string>
    <child_integer>Error condition.</child_integer>
</example>

example.xsd
<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
    <xs:element name="example">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="child_string" type="xs:string"/>
                <xs:element name="child_integer" type="xs:integer"/>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

 

[_kabal_]

It's a tough one really, but invalid xml is invalid.

you could always try a SAX parser instead of a DOM parser.

But this could happen with any data that must follow conventions.

a JSON string missing a comma or a colon will also just explode with no warning.

 

[[)roi(]]

Is it possible to post a complete test file?, specifically one that is demonstrating the parsing problems.