mORMot Open Source

ivanius

Member

Registered: 2016-03-26

Posts: 4

correctly use Read\Write\Delete to local SQLite

My first experience work with SQLite and DB, i use Delphi 2010 for create simple GPS where have 2 tables (TSQLLink = Class(TSQLRecord), TSQLPoint = Class(TSQLRecord)).
I read docs and forum, but cant find methods to do this:

Function DelPoint(ID: Int64): boolean;
Begin
  Result := false;
  If Assigned(MainForm.Database) Then
    Result := MainForm.Database.Delete(TSQLPoint, ID);
End;

Function AddLink(start_point_id: Integer = 0; end_point_id: Integer = 0; one_way: Integer = 0; temp_link: Integer = 0): Boolean;
Var
  Rec: TSQLLink;
Begin
  Result := False;
  If Assigned(MainForm.Database) Then
  Begin
    Rec := TSQLLink.Create;
    Try
      Rec.start_point_id := start_point_id;
      Rec.end_point_id := end_point_id;
      Rec.one_way := one_way;
      Rec.temp_link := temp_link;
      If MainForm.Database.Add(Rec, {true} false) = 0 Then //ofc here i have error of DB is Busy
        Result := False
      Else
      Begin
        MainForm.AddLinkList(MainForm.SGLink, Rec);
      End;
    Finally
      FreeAndNil(Rec);
    End;
  End;
End;
//....
   rcPoint := TSQLPoint.CreateAndFillPrepare(Database, 'X >= ? AND X <= ? AND Y >= ? AND Y <= ?', [OnMap.X - 150, OnMap.X + 150, OnMap.Y - 150, OnMap.Y + 150]);
   If rcPoint.FillOne Then
   Begin
     AddLink(Bottom, rcPoint.ID);
  end;
  FreeAndNil(rcPoint);

After thinking - i need store myPoints and myLinks in memory - do changes - update DB.
Generics\Dynarrays\TSQLTableJSON or?

i think i find - need FillClose; or use TSQLTableJSON.
Plz sry for my bad english.

Last edited by ivanius (2016-03-26 23:31:41)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,669

Website

Re: correctly use Read\Write\Delete to local SQLite

What is your SQLite3 error?
There should not be "SQlite3 busy" error during run, unless the SQLite3 database file is opened by a third-party tool during the service run.

To apply several modifications at once, use a Batch - see http://synopse.info/files/html/Synopse% … ml#TITL_28

ivanius

Member

Registered: 2016-03-26

Posts: 4

Re: correctly use Read\Write\Delete to local SQLite

Ty for answer, very good documentation - i find what i need.
But, can i think best and easy way - TSQLRestStorageInMemory.
How use this is it? i have only 2 tables.
ty again.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,669

Website

Re: correctly use Read\Write\Delete to local SQLite

Try TSQLRestServerFullMemory - see the doc and samples about it.
If you use it, only a sub-set of SQL/ORM would be available, but simple queries would work, and performance would be huge.
The SQlite3 engine won't be linked to the executable, if you use only TSQLRestServerFullMemory.

ivanius

Member

Registered: 2016-03-26

Posts: 4

Re: correctly use Read\Write\Delete to local SQLite

//my trys to create TSQLRestStorageInMemory
If Not Assigned(Model) Then
      Model := CreateSampleModel;
Database := TSQLRestClientDB.Create(Model, CreateSampleModel, filename, TSQLRestServerDB);
Database.Server.CreateMissingTables;
StDatabase :=  TSQLRestStorageInMemory.Create(TSQLPoint, Database.Server, filename);
StDatabase := TSQLRestStorageInMemory(Database.Server.StaticDataAdd(StDatabase));

Or i don`t need this - if all ok now all work - i use only Database.MultiFieldValues
And all read cycles use 4781 Mks

Last edited by ivanius (2016-03-28 19:57:29)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,669

Website

Re: correctly use Read\Write\Delete to local SQLite

CreateMissingTables will create all storage tables in the main TSQLRestServerDB.
So adding a TSQLRestStorageInMemory afterwards won't work.

What you can do is use TSQLRestServerFullMemory, then retrieve the storage class as such if needed:

  Database  := TSQLRestServerFullMemory.Create(....);
  PointDB := Database.StaticDataServer[TSQLPoint] as TSQLRestStorageInMemory;

ivanius

Member

Registered: 2016-03-26

Posts: 4

Re: correctly use Read\Write\Delete to local SQLite

ty for all, i think now all ok.

its free for use program, not for sale, but what you logo use in about screen or only name of library?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,669

Website

Re: correctly use Read\Write\Delete to local SQLite

Nice looking screenshot!

The framework name, "under MPL license terms", and a link to http://synopse.info is enough in the about box.