mORMot Open Source

AntonE

Member

Registered: 2012-02-03

Posts: 74

FormatSQLWhere for selecting multiple inputs

Hi,
I have a data object as such:

type
  TIPAddress : Cardinal;
type
  TSQLTrafficItem=class(TSQLRecord)
  private
    FIP         : TIPAddress;
    FDate       : TDateTime;
    FUL         : Int64;
    FDL         : Int64;
  published
    property IP            : TIPAddress     read FIP        write FIP       ;
    property DateSlot   : TDateTime      read FDate      write FDate     ;
    property UL           : Int64          read FUL        write FUL       ;
    property DL           : Int64          read FDL        write FDL       ;
  end;

But in my app, each client can have many IPs, in fact some IPs might be subnets, containing multiple IPs.
So to get some aggregated data, I used to pass a dynamic array :

type TIPsPair= record
                     IP:String;
                     IP1,IP2:Cardinal;
                    end;
     TIPsPairArr = Array of TIPsPair;

where IP is the text subnet, e.g. "10.1.2.0/24" and IP1 will then hold calculated cardinal value of 10.1.2.0 and IP2 will hold the cardinal value of 10.1.2.255 to indicate the range.
So passing a client with say these IPs:
10.2.2.2/32  (10.2.2.2-10.2.2.2) call these cardinal values IPA1-IPA2
10.3.3.0/30 (10.3.3.0-10.3.3.3) call these cardinal values IPB1-IPB2
10.4.4.0/24 (10.4.4.0-10.4.4.255) call these cardinal values IPC1-IPC2
I had to built a SQL query like so:

select * from TRAFFIC where DateSlot>=:DateF and DateSlot<:DateT and 
(
((IP>=IPA1)and(IP<=IPA2))or
((IP>=IPB1)and(IP<=IPB2))or
((IP>=IPC1)and(IP<=IPC2))
)

OFC values IPA1, IPA2,etc replaced with cardinal numbers in real query.

Is there a better, more efficient way to do it in mORMot to harness cached prepared statements where I can maybe create a larger 'flat' array of the required IPs, e.g.

Var Arr : Array of Cardinal;
Arr[0]:=IPStrToCardinal(10.2.2.2);
Arr[1]:=IPStrToCardinal(10.3.3.0);
Arr[2]:=IPStrToCardinal(10.3.3.1);
Arr[3]:=IPStrToCardinal(10.3.3.2);
....

Then use something like:

aList:=Server.RetrieveList<TSQLTrafficItem>(' IP IN ? AND DATESLOT>= ? AND DATESLOT < ?',[Arr,DateF,DateT]);

Or some other advice to make this query as efficient/fast as possible?

Thanks
AntonE

igors233

Member

Registered: 2012-09-10

Posts: 241

Re: FormatSQLWhere for selecting multiple inputs

Just an idea, to store IPs (or use a custom search function) as numeric values, with it you could test IP ranges with BETWEEN.

Last edited by igors233 (2017-03-09 20:01:58)