mORMot Open Source

Hi all,
still finding my feet in mORMot...

I need to do a type of CAS in the DB.
I'm recording traffic that is like this:

type
  TSQLTrafficItem=class(TSQLRecord)
  private
...
  published
    property Router   : Integer    read FRouter  write FRouter;
    property Year     : Word       read FYear    write FYear;
    property Month    : Word       read FMonth   write FMonth;
    property Day      : Word       read FDay     write FDay;
    property Hour     : Word       read FHour    write FHour;
    property Minute   : Word       read FMinute  write FMinute;
    property FIP      : RawUTF8    read FFIP     write FFIP;
    property TIP      : RawUTF8    read FTIP     write FTIP;
    property FMAC     : RawUTF8    read FFMAC    write FFMAC;
    property TMAC     : RawUTF8    read FTMAC    write FTMAC;
    property Bytes    : Int64      read FBytes   write FBytes;
    property Packets  : Int64      read FPackets write FPackets;
    property FUN      : RawUTF8    read FFUN     write FFUN;
    property TUN      : RawUTF8    read FTUN     write FTUN;
  end;
  DB.CreateSQLMultiIndex(TSQLTrafficItem,['Router','Year','Month','Day','Hour','Minute','FIP','TIP'],True);
  DB.CreateSQLIndex     (TSQLTrafficItem,'FMAC',False);
  DB.CreateSQLIndex     (TSQLTrafficItem,'TMAC',False);

Traffic is grouped by variable time increment, e.g. 5 or 15 minutes, so when receiving (logging) new traffic, I have to :
1)Check if such record/row exist (unique key : 'Router','Year','Month','Day','Hour','Minute','FIP','TIP')
2a)If it does, add new traffic to it (Bytes+Packets)
2b)If it does not, create new record.
So for each new item, I have to do at least 2 SQL statements.

ATM I'm doing like this:

   TI:=TSQLTrafficItem.FillPrepare(ServerForm.DB,'ROUTER = ? AND YEAR = ? AND MONTH = ? AND DAY = ? AND HOUR = ? AND MINUTE = ? AND FIP = ? AND TIP = ?',[],[RouterID,Y,M,D,H,N,IPF,IPT]);
   if TI.FillOne
      then begin
            if (TI.FMAC='')and (MACF<>'')
               then TI.FMAC:=MACF;
            if (TI.TMAC='')and (MACT<>'')
               then TI.TMAC:=MACT;
            TI.Bytes  :=TI.Bytes  +IPB;
            TI.Packets:=TI.Packets+IPP;
            ServerForm.DB.Update(TI);
           end
      else begin
            TI.Router   :=RouterID;
            TI.Year     :=Y;
            TI.Month    :=M;
            TI.Day      :=D;
            TI.Hour     :=H;
            TI.Minute   :=N;
            TI.FIP      :=IPF;
            TI.TIP      :=IPT;
            TI.FMAC     :=MACF;
            TI.TMAC     :=MACT;
            TI.Bytes    :=IPB;
            TI.Packets  :=IPP;
            TI.FUN      :=UNF;
            TI.TUN      :=UNT;
            ServerForm.DB.Add(TI,True);
           end;
   TI.Free;

But I need to get it as fast/efficient as possible. Would it be significantly faster direct to SQL?
I tried to have a look at direct SQL in documentation and other threads, but could not figure out how to get a connection.

Would something like this be possible/optimal where I get 3 statements beforehand?

  SQLF := dbConn.NewStatementPrepared('select * from TrafficItem WHERE ROUTER = ? AND YEAR = ? AND MONTH = ? AND DAY = ? AND HOUR = ? AND MINUTE = ? AND FIP = ? AND TIP = ?',false, true);
  SQLI := dbConn.NewStatementPrepared('insert into TrafficItem (ROUTER,YEAR,MONTH,DAY,HOUR,MINUTE,FIP,TIP,FMAC,TMAC,BYTES,PACKETS,FUN,TUN) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)',false, true);
  SQLU := dbConn.NewStatementPrepared('update TrafficItem (FMAC,TMAC,BYTES,PACKETS,FUN,TUN) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?) WHERE (ROUTER = ? AND YEAR = ? AND MONTH = ? AND DAY = ? AND HOUR = ? AND MINUTE = ? AND FIP = ? AND TIP = ?',false, true);

If so, how do I get/make a direct SQL connection? Can't figure where to get a DB connection from TSQLRestServerDB to call "NewStatementPrepared".

These statements run in a loop, sometimes recording >8000 new items at a time, minutes or even seconds apart. Do I start/stop a transaction before/after loop? It's safe from data integrity point of view but will it block DB during that time?
This 'table' (TSQLTrafficItem) is only updated from 1 thread in the server code itself.

Thanks
Regards
AntonE

I'm trying to build a custom format for LiveBindings to display ISO8601 (Don't know if it's needed, but good exercise)

It is basically a package that you need to compile/install into IDE, but I get error in subject when I try to compile.
Some reading suggest a rebuild with {$G+} or {$IMPORTEDDATA ON} but it does not work.

Thanks

unit ISO8601ToStrMethod;

interface

implementation

{$IMPORTEDDATA ON}
uses System.Bindings.Methods, System.SysUtils, MethodUtils,SynCommons,DateUtils;

const
  sIDIso8601ToStr = 'Iso8601ToStr';
  sIDStrToIso8601 = 'StrToIso8601';

procedure RegisterMethods;
begin
  TMethodUtils.RegisterMethod<Iso8601,string>(sIDIso8601ToStr,
    function(AValue: Iso8601): string
    begin
      Result := DateTimeToStr(AValue.ToDateTime);
    end);

  TMethodUtils.RegisterMethod<string,Iso8601>(sIDStrToIso8601,
    function(AValue: string): ISO8601
    var D : TDateTime;
        I : Iso8601;
    begin
     try
      D := StrToDateTime(AValue);
      PIso8601(@I)^.From(D);
      Result:=I;
     finally
      Result.Value:=0;
     end;
    end);
end;

procedure UnregisterMethods;
begin
  TBindingMethodsFactory.UnRegisterMethod(sIDIso8601ToStr);
  TBindingMethodsFactory.UnRegisterMethod(sIDStrToIso8601);
end;

initialization
  RegisterMethods;
finalization
  UnregisterMethods;
end.

I have a HTTP server (like Sample 4) but need to have Threads running in background that does various tasks. Some of these tasks involve heavy DB access.
I am concerned that my Threads that run on server, will cause problems.

Should I do something to avoid/detect e.g. simultaneous access by a 'server worker thread' and a Client request to same Object?
What happens if 2 threads calls SQLRestServerDB.Update at the same time...
(Or Should each thread create a client connection to server?)

Regards
AntonE

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...

Reading the documentation, this seems 90% similar to a library I wrote in BorlandPascal (DOS) with turbo-vision UI in the 80's.
I always thought we should get out of dark ages with 'flat' databases.
Although I understand most of the concepts, it is a slow process to get up to speed.
Is there a complete (client/server)demo(s) available so I can get some sample code? I saw mention of demos, but could not find a download link.

Thanks
AntonE