mORMot Open Source

Well this was kind of urgent and I know everybody here are pretty busy people, I passed the task to the existing TCP (non mormot) server and stored the user info on a legacy database, but STILL it would be nice if someone can enligthen me on how can one work with the SQLite database from SERVER code and in particular in this use case, I did some tests from client code and is pretty easy but not in this case, I couldn't use a TSQLRestclient in sessionCreated event because, well the TSQLRestclient needs to authenticate and that would fire sessionCreated again and that give us an ugly loop of events.

Regards

@AOG

I am, please go on

@AB

Agreed, I'm ready to give it a shot if you need some feedback

@hnb

Great! I like that too, compatibilty is dead serious, this is all very exciting I'll be watching the progress

Thank you Arnaud , I think we can go Windows first and following your advice I'll begin to research on the linux side of things later

Feliz navidad para todos !!   Y que el año próximo traiga lo mejor.

I copied libpq.dll and libintl.dll into my app folder and all is working fine, are you sure these files are in a path discoverable by your application?

Hi Michal, did you noticed any performance differences with FB 3.0 vs FB 2.5 ?

I don't want to bother you with this because I know mORMot is muilti-db and you must limit yourself to the lowest common denominator and I am ATM more interested on PostgreSQL support and maybe other people don't care about it, but I have to say it, have you seen the soon to be released 9.4 version and his JSON/JSONB support? 

http://opensourceconnections.com/blog/2 … xtensions/
http://blogs.enterprisedb.com/2014/09/2 … r-reality/

I don't know what level of support for this feature will be on Zeos ( hello EgonHugeist ), FireDAC or UNIDAC (I still don't explore this on my own) but it could be worth to take a look at it and perhaps in the future we can have some of these niceties on our little mORMot ?

Googling around you can find some posts like this:

https://lonewolfer.wordpress.com/2011/10/11/side-track/

On the comments there is also a reference to another and so on:

http://weblambdazero.blogspot.mx/2011/0 … actor.html

It's been a long time since then but maybe is worth that you take a look at those.

Hi Thomas,

Make sure you download  mORMotNightlyBuild.zip after uncompressing you will find the folder "SQLite3\Samples" which is what you are asking for, also make sure to download the latest SAD Synopse mORMot Framework SAD 1.18.pdf .

Enjoy it!

Edit: Also don't forget to download the latest SQLite3 .obj files from sqlite3obj.7z

Hi Arnaud,

I found sometime to dig deeper on the failed tests, the TestSQL3 program is failing at first at p line 8576 (SynSefTests.pas) :

n := 0;
        while RInt.FillOne do begin
          if RInt.fID<100 then // some real entries for backup testing
            aExternalClient.Add(RInt,true,true);
          RExt.Data := RInt.Data;
          RExt.FirstName := RInt.FirstName;
          RExt.LastName := RInt.LastName;
          RExt.YearOfBirth := RInt.YearOfBirth;
          RExt.YearOfDeath := RInt.YearOfDeath;
          {$ifndef NOVARIANTS}
          RExt.Value := ValuesToVariantDynArray(['text',RInt.YearOfDeath]);
          {$endif}
          RExt.fLastChange := 0;
          RExt.CreatedAt := 0;
          if RInt.fID>100 then begin
            if aExternalClient.BatchCount=0 then
              aExternalClient.BatchStart(TSQLRecordPeopleExt);
            aExternalClient.BatchAdd(RExt,true);
          end else begin
            aID := aExternalClient.Add(RExt,true);
            Check(aID<>0);
            Check(RExt.LastChange>=Start);  //  <--------- HERE!

Values

aID = 1
  RExt.LastChange = 135197975833
  Start = 135197975833

aID = 2
  RExt.LastChange = 135197955540
  Start = 135197975833

The test fails when aID > 1 don't know if this behaviour rings a bell for you.

What do you think?

I understand because I been there ;-), unfortunately I only have 2007,  XE and XE3 and the tests previously ran fine,  right now I don't have the time but maybe in a few weeks I can download a Delphi XE7 trial version and see what will happen there

Thanks for taking a look at this

UPDATE: Nightly build from today,  same tests failed:

Synopse framework used: 1.18.290
SQlite3 engine used: 3.8.6
Generated with: Delphi XE3 compiler

I don't have a single problem with my mORMot production code, I'm just reporting TestSQL3 Behavior :-)

UPDATE: Downloaded latest version:

 2.8. External database:
  - TQuery: 2,003 assertions passed  6.38ms
  - External records: 2 assertions passed  512us
  - Auto adapt SQL: 448 assertions passed  19.22ms
  - Crypted database: 253,275 assertions passed  155.50ms
!  - External via REST: 21,893 / 168,346 FAILED  1.31s
!  - External via virtual table: 21,893 / 168,346 FAILED  2.38s
!  - External via REST with change tracking: 22,808 / 178,446 FAILED  2.47s
  - JET database: 7,007 assertions passed  920.15ms
  Total failed: 66,594 / 777,873  - External database FAILED  7.27s

Synopse framework used: 1.18.281
SQlite3 engine used: 3.8.6
Generated with: Delphi XE3 compiler

Time elapsed for all tests: 72.12s
Tests performed at 25/09/2014 09:28:31 a.m.

Total assertions failed for all test suits:  66,594 / 17,532,744
! Some tests FAILED: please correct the code.

So I got the same behavior with latest sources on the TestSQL3 just FYI.

Is it just me?  Now I tested with 3 different PC's (two with XE3 and 1 with XE)

I downloaded both (mormot and sqlite) files at the same time I suppose they are the latest, can't see version number on file properties how can I check that?

I have to agree with Michael about generating ID's at the database level and if the database does not have an internal mechanism we can always fallback to the "select Max(ID)", but of course Arnaud has the last word on this; I think it will always be "safer" to let the database handle that job also I want to be very careful here, that doesn't mean I'm against AB approach it is just to let you know how I think about this if I may :-)

AFAIK you can do this

var
  context: TServiceRunningContext;
  user: Integer;
Begin
  context := CurrentServiceContext;
  user  := context.Request.SessionUser;  
End;

Regards

I'll need to investigate more, I'm creating 3 TSQLRestServerDB instances in a demo application and one postgres instance for each TSQLRestServerDB is being created also by mORMot which seems correct to me, I was thinking that each instance of TSQLRestServerDB will route the incoming requests through one of these 3  already created postgres processes, something like:

   TSQLRestServerDB Instance 1 <---> TSQLDBZEOSConnection <---> postgres process 1
   TSQLRestServerDB Instance 2 <---> TSQLDBZEOSConnection <---> postgres process 2
   TSQLRestServerDB Instance 3 <---> TSQLDBZEOSConnection <---> postgres process 3

So when I make a request on the browser and the url is routed to one of the TSQLRestServerDB instances it would be reusing the DB connection already created (kind of a DB connection pool) but what I'm seeing is a new db connection (TSQLDBZEOSConnection) is created for each page refresh on the browser (yes, using the  very same url every time).

I can see on SynDBZeos.pas that the following code gets executed:

function TSQLDBConnectionPropertiesThreadSafe.ThreadSafeConnection: TSQLDBConnection;
...
      result := NewConnection;

and Newconnection is as follows:

function TSQLDBZEOSConnectionProperties.NewConnection: TSQLDBConnection;
Begin
  result := TSQLDBZEOSConnection.Create(self);
End;

Thus what is happening is a new connection is being created each time :

TSQLDBZEOSConnection <---> postgres process 4
TSQLDBZEOSConnection <---> postgres process 5
TSQLDBZEOSConnection <---> postgres process 6
...

Maybe I'm wrong on my understanding of who the "client" is, this is what I thought was happening :

      Client  <------> TSQLHttpServer instance <---->  | TSQLRestServerDB Instance 1 <------> postgres process 1
    (browser)                                          | TSQLRestServerDB Instance 2 <------> postgres process 2
                                                       | TSQLRestServerDB Instance 3 <------> postgres process 3

I'm considering the browser the "client" of the TSQLHttpServer instance, and the former as the "client" of the TSQLRestServerDB instances which in turn are "client" of the already created postgres processes but it looks like if no connection created at first is being "reused".

I don't know if my english is good enough to make my case, I hope you can understand what I mean  :-)

I have to say this, your framework is AWESOME!

I had to tweak my classes because I realized I shouldn't be freeing Props and Model instances, also used TRawUTF8List as suggested and as you said I don't need tho free the owned objects by hand, no memory leaks so far and now I can retrieve data using URIs which includes the database + schema

I'm enjoying working with mORMot, at first it looks complicated but once you get used to it, is really easy.

This is what I have so far:

type
  TlxServerSpec = record
    ServerType: TSQLDBDefinition;
    Host,
    Port,
    DBName,
    DBSchema,
    User,
    Pass : RawUTF8;
  end;

  TlxServerList = class
  private
    FDBServers  : TStringList;
  public
    constructor Create;
    destructor  Destroy;override;

    procedure Add(aSpec: TlxServerSpec; aModel: TSQLModel);
    function GetServer(schemaName: RawUTF8): TSQLRestserverDB;
  end;

implementation

const
  SCHEMA_SEPARATOR = ':';
{ TlxServers }

procedure TlxServerList.Add(aSpec: TlxServerSpec; aModel: TSQLModel);
var
  props   : TSQLDBZEOSConnectionProperties;
  DBServer: TSQLRestServerDB;
  FullSchemaName,
  portStr : RawUTF8;
begin
  //Own convention, separating DB from schema with :
  FullSchemaName := aSpec.DBName + SCHEMA_SEPARATOR + aSpec.DBSchema;

  if FDBServers.IndexOf( FullSchemaName ) >= 0 then
    Exit; // a server for the schema already exists

  if aSpec.Port<>'' then
    portStr := ':'+aSpec.Port;

  Props := TSQLDBZEOSConnectionProperties.Create(
      TSQLDBZEOSConnectionProperties.URI(aSpec.ServerType, aSpec.Host+portStr),
      aSpec.DBName, aSpec.User, aSpec.Pass);
  Props.ForcedSchemaName := aSpec.DBSchema;  //  <--- Not recommended by MPV
  VirtualTableExternalRegisterAll(aModel,Props);
  try
    DBServer := TSQLRestServerDB.Create(aModel,':memory:',false);
    DBServer.CreateMissingTables(0);

    FDBServers.AddObject(aSpec.DBSchema, DBServer);
  finally
    Props.Free;
  end;
end;

constructor TlxServerList.Create;
begin
  FDBServers := TStringList.Create;
end;

destructor TlxServerList.Destroy;
var
  i: Integer;
begin
  for I := 0 to FDBServers.Count-1 do
    TSQLRestServerDB(FDBServers.Objects[i]).Free;
  FDBServers.Free;
  inherited;
end;


function TlxServerList.GetServer(schemaName: RawUTF8): TSQLRestserverDB;
begin
  Result := TSQLRestServerDB( FDBServers.Objects[FDBServers.IndexOf(schemaName)] );
end;

I can create a global instance of the previous class when starting the server like this :

var
  Model: TSQLModel;
  sSpec : TlxServerSpec;
begin
  Model := TSQLModel.Create([TSQLPais, TSQLCliente]);
  try
    FDBServerList := TlxServerList.Create;

    //Populate the list of physicaldb/schemas  from a SQLite db maybe, still not decided
    sSpec.ServerType := dPostgreSQL;
    sSpec.Host       := 'localhost';
    sSpec.Port       := '5432';
    sSpec.DBName     := 'mormot';
    sSpec.DBSchema   := 'public';
    sSpec.User       := 'postgres';
    sSpec.Pass       := 'mypass';
    FDBServerList.Add( sSpec, Model);

    sSpec.ServerType := dPostgreSQL;
    sSpec.DBName     := 'test';
    FDBServerList.Add( sSpec, Model);

  finally
    Model.Free;
  end;
end;

Before I go further I would like to hear why MPV doesn't recommend using ForcedSchemaName,  I'm planning to follow his advice and create roles for the schemas for security reasons, but I would also like to have explicitly stated on each connection what Schema to use.

What do you think?

Thanks Arnaud and MPV, I can see both approaches are at connection level, so if I have a shared TSQLRestServerDB instance on the server I wont be able to change the schema based on the client who did the request (which is my intention).

I have used mORMot on a server which act as a proxy between a delphi middleware and a Titanium Appcelerator mobile application with success, and now  I'm beginning with the ORM part and enjoying the tremendous work of AB so far, I think I can solve the issue with an array of TSQLRestServerDB and access them using Interface based services, What do you think?

It is sad to hear that but I understand, I'm researching right now hosting options, I'm undecided between Dedicated Server/VPS or some cloud computing offerings like Amazon (btw the cheaper options on Amazon AWS are for Linux)

Amazon has many attractive options out of the box, their windows pricing is just too expensive for us; I'm not convinced in general with the pricing but on the DB side (RDS) I will feel better with the high availability service, automated backups, pre-configured parameters, automatic software patching etc. we are not many people and that kind of service means less effort on our side to have our solution working.

What are others doing? 

What do you think of a combo VPS/Amazon RDS (PostgreSQL), Won't that defeat the performance gains of mormot because of the added latency to Amazon RDS?  I'm going to start with the Amazon AWS free tier and then compare with a Cheap VPS/Dedicated but if anyone wants to share their experiences I would love to hear how they are doing.

Regards,
Mocte

Nevermind, I thought this problem was related to one of mine but the URL on the GET call I was doing was wrong hence I was receiving an error.

Thanks for answering esmondb, but adding "/" causes errors like:

net::ERR_FILE_NOT_FOUND

Trying RangerX code seems like I can go farther, at least the passwordcheck is successful; I'll keep posting my findings.