#1 mORMot Framework » SQLite respect » 2022-05-10 08:31:29

esmondb
Replies: 1

Does anyone have an opinion on when not to use SQLite instead something like of Postgres? I guess it's when there's a large amount of users or data. My question was triggered by this article:

https://fly.io/blog/all-in-on-sqlite-litestream

#2 Re: mORMot Framework » Loading & Creating from a TRestHttpClient Definition file ? » 2021-11-23 21:33:44

Re: 'Any good link\articles\Books' the 1970's book by Niklaus Wirth 'Algorithms+Data Structures=Programs' is still worth a read.

#3 mORMot Framework » Blog » 2021-01-20 18:39:27

esmondb
Replies: 2

btw the blog page seems to be down.

#4 Re: mORMot Framework » mORMot 2 proposal: rename RawUTF8 type to Utf8 ? » 2021-01-01 23:56:28

I’m not sure that what Utf-
8 is called matters that
much. Happy New Year!

#7 mORMot Framework » Vatican » 2020-03-02 07:39:33

esmondb
Replies: 1

@ab are you involved in the release of archives from the Vatican today?

#8 Re: mORMot Framework » basilique project from ekon » 2020-01-01 23:11:36

Would it make sense to have a ‘todo’ list? It could encourage contributions.

#9 Re: mORMot Framework » Store PDF as Blobs in SQlite - performance issues? » 2019-12-10 07:09:32

If you use numbers to name the files create a set of folders numbered 1-9 and file the files depending on the first digit of the file name. Or something like that.

#10 mORMot Framework » Scared mormot! » 2019-10-16 03:35:16

esmondb
Replies: 3

Poor mormot - who won a wildlife prize just now

#11 Re: mORMot Framework » CBOR data format » 2019-08-05 20:59:17

Thanks for the answers.
JSON does seem more KISS smile

#12 mORMot Framework » CBOR data format » 2019-08-04 07:56:20

esmondb
Replies: 3

Could it make sense for mORMot to support CBOR in the future?
It seems a more compact data format than JSON for sending data. But I've only skimmed the RFC.
see cbor.io

#13 Re: mORMot Framework » [Solved] Inconsistency in returned json and cache problems » 2019-05-15 18:25:03

I had this problem ages ago with a JavaScript client and just looked for both (ID || RowID). But that doesn’t probably help here.

#14 Re: mORMot Framework » auxiliary Rtree columns » 2018-11-04 15:43:53

I've had another look at this and found a better way to remove the '_'.

@ab please could you have a look at this pull request

Thanks

#15 mORMot Framework » TSQLRestServer.OnAuthenticationUserRetrieve Problem » 2018-10-03 10:25:16

esmondb
Replies: 0

I'm using a custom TSQLAuthUser record which adds an email address field and then uses TSQLRestServer.OnAuthenticationUserRetrieve to allow a user to use their username or email to login with.

This works except TSQLRestServerAuthenticationDefault.CheckPassword always uses the username to calculate the salt. I think it needs changing from:

function TSQLRestServerAuthenticationDefault.CheckPassword(Ctxt: TSQLRestServerURIContext;
  User: TSQLAuthUser; const aClientNonce, aPassWord: RawUTF8): boolean;
var aSalt: RawUTF8;
begin
  aSalt := aClientNonce+User.LogonName+User.PasswordHashHexa;
  result := IsHex(aPassword,SizeOf(THash256)) and
    (IdemPropNameU(aPassWord,SHA256(fServer.Model.Root+CurrentServerNonce(false)+aSalt)) or
     // if current nonce failed, tries with previous 5 minutes' nonce
     IdemPropNameU(aPassWord,SHA256(fServer.Model.Root+CurrentServerNonce(true)+aSalt)));
end;

to:

function TSQLRestServerAuthenticationDefault.CheckPassword(Ctxt: TSQLRestServerURIContext;
  User: TSQLAuthUser; const aClientNonce, aUserName, aPassWord: RawUTF8): boolean;
var aSalt: RawUTF8;
begin
  aSalt := aClientNonce+aUserName+User.PasswordHashHexa;
  result := IsHex(aPassword,SizeOf(THash256)) and
    (IdemPropNameU(aPassWord,SHA256(fServer.Model.Root+CurrentServerNonce(false)+aSalt)) or
     // if current nonce failed, tries with previous 5 minutes' nonce
     IdemPropNameU(aPassWord,SHA256(fServer.Model.Root+CurrentServerNonce(true)+aSalt)));
end;

i.e. passing the acutual username used

#16 Re: mORMot Framework » FTS5 records » 2018-09-14 19:08:51

Incidentally TSQLRecordFTS5Unicode61 saves a few % in DB size compared to TSQLRecordFTS4 with the simple tokenizer.

#17 Re: mORMot Framework » FTS5 records » 2018-09-14 15:14:02

in TSQLRecord.GetSQLCreate in mORMot.pas changing

tokenizer := 'simple';

to

if Props.Kind = rFTS5 then
  tokenizer := 'unicode61'
else
  tokenizer := 'simple';

should fix it.
Also in the comments it mentions 'unicode64' a few times - I assume that's a typo.

#18 mORMot Framework » FTS5 records » 2018-09-14 12:20:45

esmondb
Replies: 2

TSQLRecordFTS5 is causing an error in createMissingTables. The simple tokenizer seems to have been removed from FTS

see:
https://sqlite.org/fts5.html#tokenizers

#19 Re: mORMot Framework » sqlite3fts.obj » 2018-09-04 12:10:20

Free Text Search is now included by default

#20 Re: mORMot Framework » auxiliary Rtree columns » 2018-08-15 08:46:13

Finally got round to looking at this again. These changes to mORMot.pas seem to fix it:
line 33916:

       if Props.SimpleFields[i].Name[1] = '_' then    
        result := result+copy(Props.SimpleFields[i].Name,2,MaxInt)+','
      else
        result := result+Props.SimpleFields[i].Name+','; // valid simple fields

  at line 33947 the following check needs removing

    if (Props.RTreeCoordBoundaryFields<2) or
       (Props.RTreeCoordBoundaryFields>RTREE_MAX_DIMENSION*2) or
       (Props.RTreeCoordBoundaryFields and 2<>0) then
      raise EModelException.CreateUTF8('% has % fields: RTREE expects 2,4,6..% boundary columns',
        [Props.Table,Props.RTreeCoordBoundaryFields,RTREE_MAX_DIMENSION*2]);

line 51273/4

if F.Name[1] = '_' then begin
          fSQLTableSimpleFieldsNoRowID := fSQLTableSimpleFieldsNoRowID+copy(F.Name,2,MaxInt)+',';
          fSQLTableRetrieveAllFields := fSQLTableRetrieveAllFields+','+copy(F.Name,2,MaxInt);
        end else begin
          fSQLTableSimpleFieldsNoRowID := fSQLTableSimpleFieldsNoRowID+F.Name+',';
          fSQLTableRetrieveAllFields := fSQLTableRetrieveAllFields+','+F.Name;
        end;

line 51485

if Fields.List[W.Fields[i]].Name[1] = '_' then
      W.ColNames[n] := copy(Fields.List[W.Fields[i]].Name, 2, MaxInt)
    else
      W.ColNames[n] := Fields.List[W.Fields[i]].Name;

the comma in 32526 isn't needed

  result := FormatUTF8('%+% %',[result,copy(Name,2,maxInt),Props.Props.SQLFieldTypeToSQL(i)]) else

Please could you review this?

#21 Re: mORMot Framework » auxiliary Rtree columns » 2018-07-24 21:18:51

I think the '_' gets changed to a '+' by mORMot which sqlite swallows returning just the field name. I'll look more.

Thanks for https://synopse.info/fossil/info/c96db70ed9 but it still adds an extra comma at the end ('%+% %,') which sqlite seems to ignore.

Thanks for your work!

#22 Re: mORMot Framework » auxiliary Rtree columns » 2018-07-24 17:53:32

Another problem... RTREE_MAX_DIMENSION in mORMot.pas becomes invalid if auxiliary columns are used hmm

#23 Re: mORMot Framework » auxiliary Rtree columns » 2018-07-24 09:49:08

I've found another problem auxiliary columns. Because the underscore gets removed CreateMissingTables() always thinks the model has changed. This causes an sqlite error saying virtual tables can't be edited. Any suggestions on a solution?
Thanks

#24 mORMot Framework » auxiliary Rtree columns » 2018-07-24 07:17:44

esmondb
Replies: 6

I get errors creating rtree tables with auxillary columns. Changing line 32526 of mormot.pas from:

  if Name[1]='_' then // auxiliary columns for SQlite3 >= 3.24.0
    result := FormatUTF8('%+% %,',[result,@Name[2],Props.Props.SQLFieldTypeToSQL(i)]) else

to:

  if Name[1]='_' then // auxiliary columns for SQlite3 >= 3.24.0
    result := FormatUTF8('%+% %',[result,copy(Name,2,MaxInt),Props.Props.SQLFieldTypeToSQL(i)]) else

seems to fix it.

#26 Re: mORMot Framework » CreateMissingTables question » 2018-07-06 07:27:27

if you add logging to your project:

TSynLog.Family.Level := LOG_VERBOSE;

The logs will show the SQL created by CreateMissingTables.

#27 Re: mORMot Framework » slight problem with mORMotUI.pas » 2018-05-23 12:00:42

I've tried putting mORMotUI.pas both at the start and end of the uses clause and it still gives the same error (using delphi 2007).

#28 mORMot Framework » slight problem with mORMotUI.pas » 2018-05-23 11:09:36

esmondb
Replies: 2

If I'm using mORMotUI with a form I can't set any VCL controls' align properties to alRight at run-time. Delphi gives the error:

[DCC Error] Unit1.pas(281): E2010 Incompatible types: 'TAlign' and 'TSQLTableToGridAlign'

As a workaround I've used TAlign(4) instead of alRight. Is there a better way?

#29 Re: mORMot Framework » Beginner needs guidance - Lazarus HTTPS REST Server » 2018-05-03 16:44:29

Isn't DasBoot better? I expect mORMot can do it but it may take more reading of the docs...

#31 Re: mORMot Framework » bug with JSON parsing » 2018-01-30 10:54:47

Maybe defining a record then an array would help

#32 mORMot Framework » TLS and recaptcha » 2018-01-08 10:11:11

esmondb
Replies: 1

Thank you for adding TLS support. I've tried google's recaptcha and gmail and it seems to work fine. Only thing I've noticed is that when sending emails only port 465 works and not 587.

In case it's useful to anyone below is some code that processes a simple html feedback form with a recaptcha:

Type
  Trecaptcha = packed record
    success: boolean;
    challenge_ts: RawUTF8;
    hostname: RawUTF8;
  end;

procedure TMyServer.sendFeedback(cTxt: TSQLRestServerURIContext);
var
  feedbackEmail, feedbackMessage, gRecaptchaResponse: RawUTF8;
  recaptchaSocket: THttpClientSocket;
  P: PUTF8Char;
  response: Trecaptcha;
  Server: TSMTPConnection;
begin
  P := PUTF8Char(cTxt.Call.InBody);
  if UrlDecodeNeedParameters(P, 'EMAIL,MESSAGE,G-RECAPTCHA-RESPONSE') then begin
    while P<>nil do begin
      UrlDecodeValue(P,'EMAIL=', feedbackEmail);
      UrlDecodeValue(P,'MESSAGE=', feedbackMessage);
      UrlDecodeValue(P,'G-RECAPTCHA-RESPONSE=', gRecaptchaResponse, @P);
    end;
    if (feedbackEmail = '') or (feedbackMessage = '') or (gRecaptchaResponse = '') then
      Ctxt.Error('Missing Parameter')
    else begin
      recaptchaSocket:= THttpClientSocket.Open(
           'www.google.com','443',cslTCP, 10000, true);
      try
        recaptchaSocket.Post('recaptcha/api/siteverify',
          'secret=XXXXXX&response='+
          gRecaptchaResponse + '&remoteip=' + Ctxt.RemoteIP, 'application/x-www-form-urlencoded');
        RecordLoadJSON(response, pointer(recaptchaSocket.Content), TypeInfo(Trecaptcha));
        if response.success then begin
          if SendEmail('smtp.gmail.com', 'me@me.net',
            'me@me.net',
            'Feedback Form From: ' + feedbackEmail, feedbackMessage, '',
            'me@me.net','xxx', '465', '', true)
          then
            Ctxt.Success
          else
            Ctxt.Error('Sorry, message sending failure');
        end else
          Ctxt.Error('recaptcha error');
      finally
        recaptchaSocket.Free;
      end;
    end;
  end else
    Ctxt.Error('Missing Parameter');
end;

const
  __recaptcha = 'success: boolean; challenge_ts: RawUTF8; hostname: RawUTF8;';

Initialization
  TTextWriter.RegisterCustomJSONSerializerFromText(TypeInfo(Trecaptcha),__recaptcha);

#34 mORMot Framework » function TPrecisionTimer.Time: RawUTF8; » 2017-12-20 20:19:28

esmondb
Replies: 2

I'm using TPrecisionTimer to time a long running procedure. Would it be possible to change the Time function to return hours and minutes as well?

Changing MicroSecToString in SynCommons.pas seems to work:

function MicroSecToString(Micro: QWord): RawUTF8;
  function TwoDigitToString(value: cardinal): RawUTF8;
  var L: integer;
  begin
    UInt32ToUtf8(value,result);
    L := length(result);
    if L=1 then
      result := '0.0'+result else // '3' -> '0.03'
    if L=2 then
      result := '0.'+result else // '35' -> '0.35'
      insert('.',result,L-1); // '103' -> '1.03'
  end;
  var seconds: Cardinal;
begin
  if Micro<=0 then
    result := '0us' else
  if Micro<1000 then
    result := SmallUInt32UTF8[Micro]+'us' else
  if Micro<1000*1000 then
    result := TwoDigitToString(Int64Rec(Micro).Lo div 10)+'ms' else
  if Micro<1000*1000*60 then
    result := TwoDigitToString(Micro div (10*1000))+'s' else
  begin
    seconds := Micro div (1000*1000);
    if seconds < 60*60 then
      result := UInt32ToUtf8(seconds div 60)+'m '+UInt32ToUtf8(seconds mod 60)+'s'
    else
      result := UInt32ToUtf8(seconds div (60*60))+'h '+
                UInt32ToUtf8((seconds mod (60*60)) div 60)+'m '+
                UInt32ToUtf8(seconds mod 60)+'s'
  end;
end;

#36 Re: mORMot Framework » Help for new user » 2017-12-08 10:27:11

rather ironically the link above hangs on my iphone5 chrome browser when I scroll down to 'Too much documentation can kill the documentation!'.
It's really convenient to have online documentation but would there be any way to divide up the html pages to make it more responsive?

#37 Re: mORMot Framework » SessionGroup and authentication » 2017-12-03 19:01:33

Sorry, I misread undefined in the comments as uninitialised.

#38 mORMot Framework » SessionGroup and authentication » 2017-12-03 10:41:53

esmondb
Replies: 2

I want to create a method based service which works for both logged in and anonymous users. Is it safe to do something like this?:

procedure TMySQLRestServer.search(cTxt: TSQLRestServerURIContext);
begin
  if cTxt.SessionGroup = 0 then
    //return results for anonymous users
  else
    //return results for logged in users
end;

I only ask as the code comments

/// the corresponding TAuthSession.User.GroupRights.ID value
  // - is undefined if Session is 0 or 1 (no authentication running)
  SessionGroup: integer;

but it seems that I can rely on SessionGroup being 0 rather than undefined if no authentication is running.

#40 Re: mORMot Framework » I need help again... » 2017-10-24 19:21:24

If you use JSON1 remember to call TSQLDataBase.CacheFlush() after edits to keep the ORM cache in sync. I got caught out by that!

#41 Re: mORMot Framework » Lazarus can no longer create SQLite3 databases using TSQLRestServerDB » 2017-10-05 11:49:46

My tuppence worth is that haven't found a javascript framework that's helpful. I prefer to code as much as possible by hand. Admittedly I do use jQuery and Bootstrap (by twitter) for a modern UI (don't much like dealing with css). There is also fuelux if you want some fancy controls like a datepicker.

#42 Re: mORMot Framework » Understanding TTextWriter » 2017-10-01 12:14:40

Thanks, I should have spotted that.

I got confused by using ObjectToJSON() in syncommons as an example. It seems to use the class constructor in an unusual way.

#43 mORMot Framework » Understanding TTextWriter » 2017-10-01 09:09:00

esmondb
Replies: 3

I'm trying to use TTextWriter with a simple test:

procedure test;
var
  tw: TTextWriter;
begin
  with tw.CreateOwnedStream do
    try

    finally
      Free;
    end;
end;

This gives an Access Violation. but if I declare the TTextWriter globally like:

var
  tw : TTextWriterClass = TTextWriter;

it works. Do TTextWriter vars need to be declared globally or am I missing something?

#46 Re: mORMot Framework » Invalid Timestamp » 2017-08-30 11:39:27

Great, thanks for the quick response!

#47 mORMot Framework » Invalid Timestamp » 2017-08-30 08:47:54

esmondb
Replies: 2

I'm having a problem with invalid timestamps when making two requests in quick succession after logging in. The first request succeeds but the second one fails.

In the logs I've got: Invalid TimeStamp: expected >=-8, got 142

The problem seems to be in line 51786 of mormot.pas when result.fLastTimeStamp is less than fTimeStampCoherencyTicks, I assume causing an overflow:

if HexDisplayToCardinal(PTimeStamp,aTimeStamp) and
     (fNoTimeStampCoherencyCheck or (result.fLastTimeStamp=0) or
      (aTimeStamp>=result.fLastTimeStamp-fTimeStampCoherencyTicks)) then begin

could using abs() could be a solution?:

  (aTimeStamp>=abs(result.fLastTimeStamp-fTimeStampCoherencyTicks))) then begin

Also in line 51803 it's failing to log the timestamp value and looks like it should be:

Ctxt.Log.Log(sllUserAuth,'Invalid TimeStamp: expected >=%, got %',
      [result.fLastTimeStamp-fTimeStampCoherencyTicks,Int64(aTimeStamp)],self);  //<<< added Int64(aTimeStamp)

#48 Re: mORMot Framework » Javascript authentication » 2017-08-23 06:56:09

I think there was a bug in the original code. in the crc32 function try changing this line:

 crc = crc ^ (-1);

to this:

 crc = crc^0xFFFFFFFF; 

#50 Re: mORMot Framework » 2nd Edition of mORMot book » 2017-02-04 21:14:46

The photo looks like a Giacometti view of our mORMot!

Board footer

Powered by FluxBB