Newbie help needed

AntonE · 2013-02-14 12:06:16

Hello,
I have looked at mORMot a while ago and have now committed to start using it for new projects.
We start with a small program to test and get familiar with mORMot framework.
I have read SAD document twice and I get a general feel, but it still hasn't 'clicked' properly yet as I start to use it now, so I need some advice please:

I'll try to re-write a VCL/Firebird Internet traffic recording program...
I has a background thread that get IP-pairs (TSQLTraffic) with bytes traffic every minute or 2 from a router.
All traffic is recorded and software detects if new PC is on network (if new IP in 'trusted' IP range appears as FromIP or ToIP. It then creates a TSQLComputer and user can enter HostName/DeviceName if it cannot be detected. Anyways, that's not the problem.

TSQLComputer = class(TSQLRecord)
private
fHostName : RawUTF8;
fLastLanIP : RawUTF8;
published
property HostName : RawUTF8 read fHostName write fHostName;
property LastLANIP : RawUTF8 read fLastLANIP write fLastLANIP;
end;
TSQLTraffic = class(TSQLRecord)
private
fTime : TTimeLog;
fFromIP : Integer;
fToIP : Integer;
fBytes : Integer;
fPackets: Integer;
published
property Time : TTimeLog read fTime write fTime;
property FromIP : Integer read fFromIP write fFromIP;
property ToIP : Integer read fToIP write fToIP;
property Bytes : Integer read fBytes write fBytes;
property Packets : Integer read fPackets write fPackets;
end;

Eventually there will be millions of TSQLTraffic records and typically < 100 TSQLComputers.

I need to be able to do fast queries on e.g. :
- Total upload/download traffic for IP between 2 dates...
- Total traffic for IP range(s) between 2 dates...
- Total traffic per hour between 2 dates...
- Total daily traffic...
- Graphs over time for IP range or specific IPs, etc.
- Top 5 Source IPs in last hour/day/month
- Top 5 Destination IPs in last hour/day/month
etc.

I do not know how to do the master/detail relationship. Is both TSQLRecord or TSQLRecordMany ?
Help please, I'm still a bit confused. :~|

Problems:
1)link 1 TSQLTraffic record to 'possibly' 0, 1 or 2 TSQLComputer records
2)Make fast queries and stats based on FromIP and/or ToIP fields
3)'Aggregate' record subtotals into e.g. daily, monthly, like using SQL, sum(xx) and GROUP BY.

In old program I had (below), so it was easy to do, eg:

select "YEAR","MONTH","DAY","HOUR",SUM(UL+DL) FROM TRAFFIC GROUP BY "YEAR","MONTH","DAY","HOUR";

But then graphs only show in 'hour' chucks. I hope mORMot can help me make the database more fine-grained that Hour 'chunks'

CREATE TABLE "TRAFFIC"
(
"IP"    INTEGER NOT NULL,
"YEAR"    INTEGER NOT NULL,
"MONTH"    INTEGER NOT NULL,
"DAY"    INTEGER NOT NULL,
"HOUR"    INTEGER NOT NULL,
"DL"    DOUBLE PRECISION,
"UL"    DOUBLE PRECISION,
"DATETIME"    TIMESTAMP,
CONSTRAINT "PKTRAFFIC" PRIMARY KEY ("IP", "YEAR", "MONTH", "DAY", "HOUR")
);

Later I might need to add LanIP and WiFi-IP for laptops, so a computer is a sum of the traffic for 2 IPs...

ab · 2013-02-14 17:39:43

Sounds to me that a regular RDBMS database is not what you need.

You need a "Round Robin Database" for such a task.
See http://en.wikipedia.org/wiki/RRDtool

I'm not sure mORMot would make it much better than regular SQL.

The better is to change your storage and search algorithm, i.e. use a RRD layout of the data.

AntonE · 2013-02-15 08:05:52

Thank you for your reply. It makes interesting reading.
I realize my question was to broad.
I will re-post more specifics.

Thank you
AntonE
South Africa

mORMot Open Source

#1 2013-02-14 12:06:16

Newbie help needed

#2 2013-02-14 17:39:43

Re: Newbie help needed

#3 2013-02-15 08:05:52

Re: Newbie help needed

Board footer