Data comparison

Question

My setting: I took a code from the Internet that parses the Openvpn log and plays it in the database to play it in a table on an html page. The exit is more or less like this: https://www.devco.net/code/openvpnStatusParser/openvpnstatus-sample.html This parser scans a log file that has all clients connected.. It will run every 5 minutes.

Through the page, a customer key.

What I thought: parser analyzes the log, plays in a table in the bank with an active status column (because this log contains only active clients). Create another table that will contain the same data, however if this key is locked, put the status to locked. And compare the two tables, if it is locked in one and unlocked in another, the final result will be blocked. Then another script will run and lock the key definitely (block on the firewall).

But I do not know how to do this. I do not know if it is better to compare in txt, better make the comparison in the database... And if it’s one of two options, I don’t know how to make that comparison.

Answer 1

There are some ways to solve this problem:

Two tables, balance line

Two tables. Gives SELECT in both tables, simultaneously ordering the records by the same fields. Compare the key of the two "current" records (which are at the top). If key equal, you do the above business test. If different and "left" record has smaller key, it only exists in the left table, idem to right table, and does treatments based on this information. If equal key advances to the next line/record in both lists, if different key advances the smaller key record.

Filter the data (by date?) to not load the whole table.

Two tables, SELECT in key

The algorithm you mentioned seems very possible to implement directly. SELECT of active records in a table, followed by SELECT by key in the other table. It must be the same key, primary or at least with index, otherwise the processing will be slow.

A table

Instead of creating two tables and staying make a thousand queries, creates one, containing:

• Customer key;
• Specific fields that only exist in the first table;
• Specific fields that only exist in the second table.

And feeds this table with the data from the two sources, taking care of each import only touch on "your" specific field. So just one SELECT in a table only to know the active/blocked difference.

Which to choose

The first case is more appropriate to text files (which does not have as much structure) or large masses (historical tables).

The second case only works for tables, well structured (with indexes), historical or not. If they are historical, take care to always catch the "last record".

The third case is to take the "merge" of the data in memory and put in a table. It makes it easier because it separates the problem into three very clear parts: importing data from source 1, importing data from source 2, picking up the difference. Laborious, not so fast, but possibly the only one that works for huge database (that do not fit in memory).