Comparison of data

0

My scenario : I got a code on the internet that does the OpenVPN log parser and plays it in the database to play a table on an html page. The output looks something like this: link This parser parses a log file that has all clients logged in. It will run every 5 minutes.

On the page, a client key may be blocked.

What I thought: parser parses the log, plays a table in the database with an active status column (because that log only contains active clients). Create another table that will contain the same data, but if this key is locked, put the status to locked. And comparing the two tables, if it is locked in one and unlocked in another, the end result will be blocked. Then another script will run and lock the key permanently (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 do the comparison in the database ... And if it is one of two options, I do not know how to make that comparison.     

asked by anonymous 25.05.2016 / 17:00

1 answer

0

There are a few ways you can solve this problem:

Two tables, balance line

Two tables. It 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 negotiation test above. If different and "left" record has lower key, it only exists in the left table, idem to right table, and does the treatments based on that information. If equal key advances to the next row / record in both lists, if different key advances the minor key record.

Filter the data (by date?) so as not to load the entire table.

Two tables, SELECT on the key

The algorithm you said seems quite possible to implement directly. SELECT of active records in one table, followed by SELECT per key in another table. It has to be even key, primary or at least index, otherwise the processing will be slow.

A table

Instead of creating two tables and making 1,000 queries, create one, containing:

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

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

Which to choose

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

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

The third case is to "merge" the data into memory and into a table. Ease because it separates the problem into three clear parts: import data from source 1, import source data 2, pick up the difference. Worky, not so fast, but possibly the only one that works for huge database (which does not fit in memory).

    
25.05.2016 / 17:47