mORMot Open Source

Wayne

Member

Registered: 2011-02-22

Posts: 4

Save Objects to JSON

Hello everyone,
  First, I'd like to say how great the SQLite3 framework is. So far I've found it easy enough to learn, and incredibly powerful. However, I could use some direction on where to go next.

  My data structure is very simple at the moment.

TSQLProject: TSQLRecord
TSQLProjectResource: TSQLRecord
TSQLProjectResources: TSQLRecordMany

I have successfully added TSQLProjectResource objects to TSQLProject objects using the Many-to-Many pattern. I was able to easily save this to a SQLite3 database using the HTTP REST server implementation.

My program will not only save these objects to SQLite via HTTP REST, but my intention is to also save them to a local file in JSON format. Is there an easy way to save all of these objects to a single file using JSON and then load them again?

I was thinking something along the lines of creating a new TSQLRestServer and calling something like TSQLRestServer.SaveToJSON to save the entire database. Does such a function exist? If not, what are some alternatives?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: Save Objects to JSON

You can save a Table content to JSON creating a TSQLTable then using GetJSONValues() method.

Another possibility is to use the fast in-memory engine storage instead of SQLite3.
It will rely on TSQLRestServerStaticInMemory classes, one per table, which can load its content from a JSON file and save it via the UpdateFile method.
You can either create separate TSQLRestServerStaticInMemory instances, or create a Client/Server architecture, using a regular TSQLRestServer then calling StaticDataCreate() for all the TSQLRecord classes.
Note that the TSQLRestServerStaticInMemory is very fast, but less powerful than SQLite3. In particular, you have all the ORM or Client/Server features of the framework, but you won't have an SQL engine to work with.

Wayne

Member

Registered: 2011-02-22

Posts: 4

Re: Save Objects to JSON

You can save a Table content to JSON creating a TSQLTable then using GetJSONValues() method.

Another possibility is to use the fast in-memory engine storage instead of SQLite3.
It will rely on TSQLRestServerStaticInMemory classes, one per table, which can load its content from a JSON file and save it via the UpdateFile method.
You can either create separate TSQLRestServerStaticInMemory instances, or create a Client/Server architecture, using a regular TSQLRestServer then calling StaticDataCreate() for all the TSQLRecord classes.
Note that the TSQLRestServerStaticInMemory is very fast, but less powerful than SQLite3. In particular, you have all the ORM or Client/Server features of the framework, but you won't have an SQL engine to work with.

To be clear, I'm not trying to save JSON *instead of* SQLite3, but, *in addition to* SQLite3. Sort of an alternate storage mechanism. My goal is to keep a large SQLite3 database containing all objects, but then be able to save a given "Project" and its associated "Resources" and many-to-many table in a human-readable format to a separate file. This data may be altered while disconnected from the main database, and its data would need to be updated again at a later point.

With that limitation in mind, I believe you were certainly pointing me in the right direction, thanks a lot, Arnaud! It looks like TSQLRestServerDB.DB.ExecuteJSON does the saving part properly. I am able to specify a query and receive the results back as JSON.

However, I'm not sure how to go about loading the saved JSON data back into SQLite3.

If I have a string like the following:

[{"ID":1,Artist="test",Name="first"},
{"ID":2,Artist="test",Name="second"}]

What is the best way to update an existing SQLite3 database with the data? If it makes a difference, I'm actually using a GUID field as my own primary index, and not the ID field. I was experimenting with using TSQLRestServerDB.StaticDataCreate, but couldn't get it to work right. It would appear to run, no exceptions generated, but the database didn't contain any data. Is there something I need to run after calling StaticDataCreate to actually save the data to the SQLite3 database?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: Save Objects to JSON

StaticDataCreate will create the data in memory, not in the SQLite3 database, by design.

For getting some JSON content, you should better use the TSQLRestServerDB.EngineList() method, which is of more higher-level, and will use automated prepared SQL statements - i.e. :(parameters:) - and internal request caching.

Then, from JSON content, you can use a TSQLTableJSON, then use FillPrepare/FillRow methods then write the TSQLRecord content back to the DB.

But another faster possibility is to create directly the SQL from the JSON.
See how the  TSQLRestServerDB.EngineUpdate method is implemented:

function TSQLRestServerDB.EngineUpdate(Table: TSQLRecordClass; ID: integer;
  const SentData: RawUTF8): boolean;
begin
  if (self=nil) or (Table=nil) or (ID<=0) then
    result := false else begin
    // this SQL statement use :(inlined params): for all values
    result := EngineExecuteFmt('UPDATE % SET % WHERE RowID=:(%):;',
      [Table.RecordProps.SQLTableName,GetJSONObjectAsSQL(SentData,true,true),ID]);
    if Assigned(OnUpdateEvent) then
       OnUpdateEvent(self,seUpdate,Table,ID);
  end;
end;

All the magic is done in the  GetJSONObjectAsSQL() function:

/// decode JSON fields object into an UTF-8 encoded SQL-ready statement
// - this function decodes in the P^ buffer memory itself (no memory allocation
// or copy), for faster process - so take care that it is an unique string
// - P contains the next object start or nil on unexpected end of input
// - if Fields is void, expects expanded "COL1"="VAL1" pairs in P^, stopping at '}' or ']'
// - otherwize, Fields[] contains the column names and expects "VAL1","VAL2".. in P^
// - returns 'COL1="VAL1", COL2=VAL2' if UPDATE is true (UPDATE SET format)
// - returns '(COL1, COL2) VALUES ("VAL1", VAL2)' otherwize (INSERT format)
// - escape SQL strings, according to the official SQLite3 documentation
// (i.e. ' inside a string is stored as '')
function GetJSONObjectAsSQL(var P: PUTF8Char; const Fields: TRawUTF8DynArray;
  Update, InlinedParams: boolean): RawUTF8; overload;

Wayne

Member

Registered: 2011-02-22

Posts: 4

Re: Save Objects to JSON

I think for my purposes GetJSONObjectAsSQL will be the most direct path.

I'll get a chance to test later tonight. Thanks for the help, and thanks for an awesome framework!