Data Replication (SQLite and Firebird). Is it possible?

Roberto Schneiders · 2013-01-24 12:46:30

This week I'm participating in the planning of a new project that has some pretty interesting requirements.

I thought, will be the first project using mORMot.

I'll try to explain what the software will do for you to understand my problem.

It is a software for gas stations (He actually already exists, we are reformulating some things). We are refactoring the communication with CUB (hardware hub). The CUB is responsible for concentrating the supply of information sent by the fuel pumps. There will be a software (server) that will communicate with the CUB, ordering and record this information in a local database (SQLite). There may be several stations clients requesting this information (intend to use mORMot ORM for this).

We need to implement a solution in case the server has an issue (eg A meteor falls on him )

The CUB information are not persisted by him, ie, once requested by the server, are excluded from hardware memory. In this case, if the server machine has problems we have lost the data, and that sucks.

We want a local database because the main database (firebird) may be outside the local network (eg Internet) and connect to it would mean a huge detriment to performance.

One of our ideas was to create a mechanism for data persistence. The information stored in the local database (SQLite) are persisted (in the background) for a main base (firebird / postgresql). Of course there is no 100% guarantee, but it's something.

Is there a mechanism that allows the mORMot do this?

Do you have any other ideas?

ab · 2013-01-24 13:05:21

What is the amount of data to be stored, at each level? How fast is it expected to be retrieved/stored? Is it just a record of history values?

You can use low-level SynDB features to handle inter-database replication, with very high speed.
See for instance how function RowsToSQLite3() is implemented in SynDBSQLite3.pas: you can easily "push" some database content (e.g. SELECT * FROM data WHERE DATE>LastReplicationDate) from one database to another.
I've just renamed this function into the generic TSQLDBConnection.NewTableFromRows() method. It will allow to dump a SQL statement result into a new table of any database - see http://synopse.info/fossil/info/71278eef20

Or even "attach" one database to another, at ORM level, and a regular "INSERT INTO replication SELECT * FROM data WHERE DATE>lastreplicationdate" with both tables as virtual.

Replication will probably be integrated within Even-Driven scope.
http://blog.synopse.info/post/2012/09/0 … laboration

This mechanism, integrated to the mORMot core, will:
- Publish dedicated event interfaces (i.e. "classic" event-driven process);
- Allow notification of interface based services;
- Allow notification of method-based services;
- Allow notification of CRUD/ORM operations.

All client-server notification process of the events will be safely queued on the server, with some "quality levels" - i.e. some events may safely be skipped, but others may be persisted in an ACID way to ensure they are not lost.

In your case, I suspect the latest kind of events (CRUD/ORM ops) will allow easy data replication.
A dedicated "auto-push" feature could be made available, to directly synchronize one database level to another.
It will allow event-based storage, if needed (allow easy and fast rollback to any previous state, just by replaying events).

We will use optimized algorithm for storage and process, in order to assure both scalability and reactiveness of the solution.
It should behave better than trigger-based solutions, since it will be integrated at the framework core level.
All needed low-level stuffs (like fake interface generation, client-server process, JSON serialization, SQLite3 or in-memory storage) are already there.

Roberto Schneiders · 2013-01-24 15:39:47

What is the amount of data to be stored, at each level?

The current demand is around 3000 supplies per day . Each supply is a record in the table.
Thinking in an extremely high demand, would to put something like 5 records per second.

But this is only the records that are returned by CUB. These records suffer manipulations in client applications.

The client applications need to maintain a list of supplies. This list is updated every 1 second. I believe that we will use cache here.

How fast is it expected to be retrieved/stored?

I think it should not take more than 200ms to return/update a record.

Is it just a record of history values?

I do not know if I got it right.. It is only one record per supply. But this record has a status.

Junior/RO · 2013-01-24 23:19:31

AB, can I use the TSQLDBConnection.NewTableFromRows() method to send SQL from client to server? From a local SQLite3 database to a Oracle server?

How will mORMot handle the syntax?

Last edited by Junior/RO (2013-01-24 23:20:08)

ab · 2013-01-24 23:33:52

It will convert the data using its internal fields knolwledge.

And yes, you can push data from any supported database engines.

mORMot Open Source

#1 2013-01-24 12:46:30

Data Replication (SQLite and Firebird). Is it possible?

#2 2013-01-24 13:05:21

Re: Data Replication (SQLite and Firebird). Is it possible?

#3 2013-01-24 15:39:47

Re: Data Replication (SQLite and Firebird). Is it possible?

#4 2013-01-24 23:19:31

Re: Data Replication (SQLite and Firebird). Is it possible?

#5 2013-01-24 23:33:52

Re: Data Replication (SQLite and Firebird). Is it possible?

Board footer