Aggregate function

ingoberg · 2010-12-16 02:07:01

Thanks a lot for a great framework. Could you please demonstrate how to implement a custom aggregate function (e.g. combining strings, calculating stats etc).

ab · 2010-12-16 07:36:26

You've some C source code here:
http://snippets.dzone.com/posts/show/3717

But some dedicated C api seems to be missing in current version of SQLite3.pas: I'll add them soon.

ab · 2010-12-16 09:22:35

OK. I've added the aggregate function features to the SQLite3 unit.

I've added, as a sample, a CONCAT() function:

CREATE TABLE test (animals VARCHAR, interjection VARCHAR);
insert into test (animals, interjection) values ('lions', 'oh my');
insert into test (animals, interjection) values ('tigers', 'oh my'); 
insert into test (animals, interjection) values ('bears', 'oh my');

select concat(animals, ' and ') from test
 -> "lions and tigers and bears"

for example, the associated unitary test is the following (extracted from TTestSQLite3Engine.DirectAccess):

  Demo.Execute('SELECT Concat(FirstName," and ") FROM People WHERE LastName="Einstein"',s);
  Check(Hash32(s)=$68A74D8E,'Albert1 and Albert1 and Albert2 and Albert3 and ...');

Here is the implementation of this CONCAT() function:

// supplies a CONCAT() function to process fast string concatenation
type
  PConcatRec = ^TConcatRec;
  TConcatRec = record
    result: PUTF8Char;
    resultlen: PtrInt;
  end;

procedure InternalConcatStep(Context: TSQLite3FunctionContext;
  argc: integer; var argv: TSQLite3ValueArray); {$ifdef USEC}cdecl;{$endif}
var sep, txt: PUTF8Char;
    seplen, txtlen: PtrInt;
begin
  if argc=2 then
    with PConcatRec(sqlite3_aggregate_context(Context,sizeof(TConcatRec)))^ do begin
      // +1 below for adding a final #0
      txt := sqlite3_value_text(argv[0]);
      txtlen := SynCommons.strlen(txt);
      if result=nil then
        GetMem(result,txtlen+1)
      else begin
        sep := sqlite3_value_text(argv[1]);
        seplen := SynCommons.strlen(sep);
        ReallocMem(result,resultlen+txtlen+seplen+1);
        Move(sep^,result[resultlen],seplen);
        inc(resultlen,seplen);
      end;
      Move(txt^,result[resultlen],txtlen+1);
      inc(resultlen,txtlen);
    end else
    sqlite3_result_error(Context, 'Concat(): wrong number of arguments');
end;

procedure InternalConcatFinal(Context: TSQLite3FunctionContext); {$ifdef USEC}cdecl;{$endif}
begin
  with PConcatRec(sqlite3_aggregate_context(Context,sizeof(TConcatRec)))^ do
    // sqlite3InternalFree will call Freemem(ConcatRec^.result)
    sqlite3_result_text(Context,result,resultlen+1,sqlite3InternalFree);
end;

function TSQLDataBase.DBOpen: integer;
(....)
  // CONCAT() function to process fast string concatenation
  sqlite3_create_function_v2(DB,'CONCAT',2,SQLITE_UTF8,nil,nil,
    InternalConcatStep,InternalConcatFinal,nil);
end;

It could be now simple to implement fast custom statistics against the database.

ingoberg · 2010-12-17 03:40:16

Thanks! Would you mind implementing the following function:

sqlite3_value_numeric_type

This function allows me to determine if the value is numeric or not.

ingoberg · 2010-12-17 03:51:45

For some reason I'm not able to create my own aggregate function when I implement it outside the SQLite3 unit. I get an error complaining about some of the arguments that I'm passing in the sqlite3_create_function_v2 (even if they are identical to the samples that you provide in the SQLite unit.). However, when I bring my function into the SQLite3 unit, everything is ok. Do you know why?

ab · 2010-12-17 08:31:03

Perhaps you didn't declare it as cdecl (the USEC compiler define is private to SQLite3.pas).

I've added the sqlite3_value_numeric_type() function to the source code repository.

ingoberg · 2010-12-18 02:28:48

You were right ->>>> (Perhaps you didn't declare it as cdecl (the USEC compiler define is private to SQLite3.pas).)

ab · 2010-12-18 09:18:02

mORMot Open Source

#1 2010-12-16 02:07:01

Aggregate function

#2 2010-12-16 07:36:26

Re: Aggregate function

#3 2010-12-16 09:22:35

Re: Aggregate function

#4 2010-12-17 03:40:16

Re: Aggregate function

#5 2010-12-17 03:51:45

Re: Aggregate function

#6 2010-12-17 08:31:03

Re: Aggregate function

#7 2010-12-18 02:28:48

Re: Aggregate function

#8 2010-12-18 09:18:02

Re: Aggregate function

Board footer