#1 2014-06-06 17:25:15

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Couldn't see changes by other process, because of caching?

Dear Arnaud,

In my app that uses mORMot (with sqlite3 as the dB), in the following situation it doesn't "see" changes by other processes:

1 - Start the app and does some Retrieve() operations against a table;
2 - In another process (called SqliteSPY, a sqlite3 manager program), delete all records in that table;
3 - Do some Retrieve() operations same as step #1 above, retrieve() still returns the records that were already deleted by SqliteSpy, unless I restart the app.

I have tried both  TSQLRestClientURINamedPipe.ServerCacheFlush and TSQLRestClientURINamedPipe.Cache.Flush() after step #2 and before step #3, but they didn't help.

Any hints? Thanks in advance.


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#2 2014-06-06 17:32:24

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

The SQlite3 database file is expected to be accessed ONLY by the mORMot server.
It is mandatory, for better performance, and with default caching status enabled.

Once the mORMot server is closed, you can safely access the SQlite3 database file with any other tool or program.
You can disable the cache for all tables, if needed.

But please ensure you read the SAD pdf 1.18 about caching: there are several levels of cache.
One at ORM level (TSQLRestServer), one at SQlite3 level (TSQLDatabase).

Offline

#3 2014-06-07 02:27:15

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: Couldn't see changes by other process, because of caching?

@ab, Thanks for the explanation, I was just doing some "housekeeping" tasks while the program is under development.


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#4 2014-08-12 08:13:29

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

An other question about a update "problem":

I've changed a TSQLRec (over mORMot-server).
If I call root/konten/ID changes are visible, but if I do root/?sql=select+*+from+konten, I see the old values
(konten is the name of a table).
What's the background of that?

Offline

#5 2014-08-12 08:31:43

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

How did you update the content? Via CRUD operations, or at SQL level?
What is your DB storage used?

You could step in TSQLRestServerURIContext.ExecuteORMGet and see what is happening with sql=... URI.
For a regular SQlite3 database, you execute TSQLRestServerDB.MainEngineList() which will use the Sqlite3 level cache.
So it sounds like if the SQLite3 level cache was not flushed.

Normally, TSQLRestServerDB.MainEngineUpdate() should have called DB.Lock() in TSQLRestServerDB.EngineExecute(), and the IsSelect() function should have identified the update...
Is it not the case?

Offline

#6 2014-08-12 08:43:56

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

I've done update via CRUD with Delphi client.

The RestServer has external DB via ODBC (firebird).

As client I try Web-client with jquery, but for the first tests I've made simple http-quest with Browser.

From stepping into TSQLRestServerURIContext.ExecuteORMGet I can see, that

root/SampleRec calls Static.EngineRetrieve

and ?sql=select calls Server.MainEngineList because Server.InternalAdaptSQL(Server.Model.GetTableIndexFromSQLSelect(SQL,false),SQL) is nil.

Now I've found (but not understand) my problem:

The problem of getting non actual data with the sql was from a select like: 'select rowid,* from table' instead of 'select RowID,* from table'.

Both will result in a call of Server.MainEngineList, but second statement gives me always(!) the old data (not the updated with CRUD).
First statement gives me actual data after a while (I think after cache has write back to external db).

I'm confused. I want simply acces the actual data of the mORMot server about a URI.

Now I know: no matter how long you will wait, if the URI hasn't changed, you get old data. Do you change the URI (from rowid,* to RowID or something you never had before, than you get actual data.
There seems to be a URI-caching...

Last edited by danielkuettner (2014-08-12 10:23:21)

Offline

#7 2014-08-12 10:30:52

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

My problem is

result := DB.LockJSON(SQL,ReturnedRowCount); // lock and try from cache

in TSQLRestServerDB.MainEngineList

It's good for speed if you can use a cache, but the server should know, if a table/Record has changed and than not use the cache.

Can I do something or do I somthing wrong?

Offline

#8 2014-08-12 10:35:30

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

Is perhaps possible to update the cache after a CRUD operation?

in TSQLRestServerURIContext.ExecuteORMWrite I've insert this line: Server.FlushInternalDBCache;
Than after CRUD it works like expected.
But I'm not sure about this method.

Last edited by danielkuettner (2014-08-12 12:47:59)

Offline

#9 2014-08-12 22:09:07

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

I have created a ticket and will fix it ASAP.
See http://synopse.info/fossil/info/b109c22750f28

Offline

#10 2014-08-13 07:41:29

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

Should be fixed now, in a generic way, I suspect.
See http://synopse.info/fossil/info/33e09442190d

Thanks for your feedback!

Offline

#11 2014-08-13 09:17:13

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

It's a pleasure! I will test it next...

By the way, do you have heard already from EntityDAC? It sounds like mORMot but I haven't looked at.

Offline

#12 2014-08-13 09:33:41

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

So now I've tested the version:

After first CRUD it works (ok, the cache was empty),
after second CRUD not.

Sorry for the negativ feedback.

Daniel

Offline

#13 2014-08-13 13:16:27

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

Have you code to reproduce the issue?

What is the update sequence?

Offline

#14 2014-08-13 15:57:56

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

Sorry, I've downloaded nightly.zip at morning but the changes wasn't included there (I havn't checked whoile .pas, sorry).
Now I've tested with new patched mORMot.pas, mORMotDB.pas and  mORMotMongoDB.pas and it works well.

Daniel

Offline

#15 2014-08-13 16:04:56

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

Can I ask you something?

After CRUD in ExecuteORMWrite cache will not deleted, because self is nil.

In the GET after, now DB.LockJSON return ''. Where have you clear the cache?

Ok, you make it in TSQLRestStorageExternal.EngineUpdate.

I've read the 1.18 pdf every day since last two weeks, but I understand nothing. Where can I get a info how these classes work together (in the special caser of external tables)?

Why are my sqlite3-table (the temporary) are so small (1 KB)? In firebird I've a real big one over 500 MB. Is it possible to use only Mem-Tables without sqlite3 in case of virtual tables? What can I tune? I would like to cache the whole
firebird-db with mormot in memory without sqlite.

I can't find answers in the docu on these questions.

Last edited by danielkuettner (2014-08-13 16:19:58)

Offline

#16 2014-08-13 18:31:54

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

In the SAD 1.18 pdf you have a whole chapter about external tables.

In particular, there are several diagrams about how the CRUD operations may be directly sent to the SQL server, or use SQlite3 virtual tables as kernel.

For instance, if all your tables are external, the data is in the external DB, and the SQLite3 table is just a type definition for the virtual tables - so no data within.
You can indeed use safely a SQLite in-memory table for your main virtual SQLite3 tables, if all tables are external - use here SQLITE_MEMORY_DATABASE_NAME as SQLite3 database file name.

But you can't use SQlite3 as "cache" directly, if you mean a 1:1 copy of the FireBird data.
There is no data in the main SQlite3 tables in your case, which are just virtual tables, pointing to the external FireBird tables.

But you would benefit of the mORMot cacheS (I put a "S" here since there are several caches).
The ORM cache will always work, but it take place BEFORE the SQLite3 kernel, at CRUD level.
If you want to use the SQLite3 cache, you could force StaticVirtualTableDirect := false and the main SQLite3 engine will be used for all statements, so you may benefit of the SQlite3 level cache. But this cache will be flushed at every update, so it may be worth it only if you have a lot of read of the same data, and seldom write to the DB...

Offline

#17 2014-08-13 19:10:46

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

Thanks for answering.
I will read the chapter once again, but I thought there are things like the 1:1 copy. I will need such a feature. I will have two mORMot-servers wich have to be synchronous.
My idea was, a Firebird in one place, a mORMot-server with a sqlite3-db (1:1) and client for it.

Offline

#18 2014-08-14 06:20:11

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

For a simple implementation, I see the following requirements:
- Database should be modified only via CRUD/REST operations (not any direct UPDATE / INSERT / DELETE SQL statements);
- Database should be modified only via a primary mORMot server (this is a one-way synchronization);
- Main database may have a little (customizable) delay before sending the synch to the replicated DB.

Like a typical master/slave replication pattern.
BTW, it would be one way to implement the "Backup" feature request - see http://synopse.info/fossil/tktview?name=31eaadc5a5

Does it meet your requirements?
I suspect I could implement it easily, via InternalUpdateEvent() and a BATCH optimized process for sending the replication.
If it sounds fine to you, could you please create a Feature Request ticket, without forgetting to link to this discussion?
Thanks!

Offline

#19 2014-08-14 13:39:52

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

>>For a simple implementation, I see the following requirements:
>>- Database should be modified only via CRUD/REST operations (not any direct UPDATE / INSERT / DELETE SQL statements);

clear

>>- Database should be modified only via a primary mORMot server (this is a one-way synchronization);

clear

>>- Main database may have a little (customizable) delay before sending the synch to the replicated DB.

you mean mORMot server has a delay to write changes to DB? That's no problem at all.

>>Like a typical master/slave replication pattern.
>>BTW, it would be one way to implement the "Backup" feature request - see http://synopse.info/fossil/tktview?name=31eaadc5a5

I'dont know if this feature goes in my direction. They want a backup of what? A backup of the empty sqlite-DB (in case of external DB's)? If I've a external DB, like Firebird, than the backup feature is in Firebird or mssql. The only question is, in wich delay writes mORMot the changes into the external DB? Only such data could be lost.

>>I suspect I could implement it easily, via InternalUpdateEvent() and a BATCH optimized process for sending the replication.

This sounds generaly fine, but please excuse me, I don't know the implementation of your suggestion (I've to deeper understand all).

Before I create a Feature Request, let me short explain my consideration (do you understand my german english at all?):

There are two points for me:

1. the possibility of a copy of the external db in sqlite (the db for the mORMot server). You can make this copy with one method call e.g.
I think this could be against your REST-approach, but we will see. Also a very good feature were if this "local copy" could be only in memory (or json e.g).
You could speed up the external db with such an approach (look at your speed-test results, ODBC-Firebird is slow, but think if I could have the whole db in my InMemory-DB in mORMot!

2. a buffer of CRUD-Operations should be exists (e.g in ExecuteORMWrite were a good place of that). This buffer would be processed by mORMot server over poor REST-Requests (to what ever, for me a other mORMot-Server).

Importend is the fact, that the synchronisation between the two mORMot goes over Internet and could be broken every time. The second mORMot-sever (the slave) would serv the clients in the locally net regardless without problems.

If that is in your intend, please let me know and I will create the Feature Request.

BTW I could realize my plan also with the actual mORMot version, I think. But perhaps you have ideas, that I will have after years.



Thanks!

Last edited by danielkuettner (2014-08-14 13:50:13)

Offline

#20 2014-08-14 14:42:47

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

My proposal was to include a 1:1 replication in 1 master to 1 (or several) slave databases.
For instance, 1 SQLite3 master and 1 FireBird slave database.
The Firebird Slave may be used for reporting, and backup purposes.
There would be one master, then one or several slaves:
Replication.png

Are there several mORMot sub-servers serving the clients?
It is a bit confused to me...
Why not a drawing for what architecture you are expecting? Then put the picture here?

Offline

#21 2014-08-14 15:31:52

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

Ok, I will explain with a picture ( we are not so far from each other):

Replication

The clients only know their slave (1 slave for n clients).
From the master knows only the slaves (n slaves).

The master has e.g. Firebird-DB. The slaves have sqlite or much better only JSON or MongoDB.

Last edited by danielkuettner (2014-08-14 15:38:34)

Offline

#22 2014-08-14 16:50:12

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

In this case, how are the writes performed?
In an usual Master/Slave replication scheme, the slaves are read only and you can write only on the master.

Your expectations are more like a master replication, i.e. a synchronization of all masters.
There sounds like if there is no "slave" in your configuration.

Such an architecture is very difficult to work with, since there are potential conflicts arising.
For instance, if two clients modify the same record at once, which version is to be kept?
sad

Offline

#23 2014-08-14 17:12:01

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

It's not complicated. The slaves are the clients of the master. They apply their updates just as the clients to their slaves.

I think the term of "master" and "slave" is misleading. The slaves are slaves only in the point of getting updates from the master. These updates come from applies of other slaves. But I don't see any special features there.

Every slaves applies his updates to the master, like any client of mORMot.

The special thing is only getting the first copy of the slave from the master. This feature doesn't exist yet.

Last edited by danielkuettner (2014-08-14 17:18:00)

Offline

#24 2014-08-14 19:45:23

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

So every write command will go up to the master, and all slaves will be synchronized from the master.
This additional round trip will decrease the write speed, but increase read speed...

An initial read from the master may be slow...
But a sqlite3 in memory db would make wonders at reading...

Why do you want to use Firebird as master db?

Offline

#25 2014-08-14 20:31:49

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

Yes, now you understand me.

The first initial read from master is slow, but you will need it very seldom, because your slave is always synchronous with master. This is not so easy, because every slave have to get the latest changes from master.
The master has a table with the tablename, the id and a timestamp. If the slave connects to master he call a method with his last timestamp as a parameter and the master gives a json with all last changes. The slaves import these json and is synchronous now.


The writes from client to slave are fast, and the writes from slave to master are delayed and the client knows nothing about it.

Firebird is my old db that I've used in my old application (client/server). Other users have other db's for saving their data and so every company has it's old db.
With mORMot the old db plays a background role, but she is always there.

Last edited by danielkuettner (2014-08-14 20:42:04)

Offline

#26 2014-08-15 08:26:51

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

Such usage of a by-habit-reference-database sounds weird to me, but I could understand it.
smile

Are you sure it is not premature optimization?
An unique main server is enough to serve thousands of clients.
But your idea is perhaps to allow an "offline" mode, if the Internet link is broken.
I understand it in Peru or such countries (where Internet quality is very poor), but it is not an issue in western or asiatic countries, where Internet mandatory for any business (so you pay for several providers, or could even switch to 3G/4G link).

Each table should therefore contain TModTime and TCreateTime published fields, and an index on the TModTime.
It would allow to track changes at startup, without the need to maintain a separate table with table/id/timestamp.

IMHO to implement this pattern, we may first have to implement Event-Driven, and a reliable and simple publish/subscribe pattern.
See http://synopse.info/fossil/tktview?name=aa230e5299
But for communication between servers, a simple dedicated HTTP server may do the trick.
Just implementing some synchronization interface services may be enough for a first server-side implementation.
In the future, we may benefit of a solution for any client (including mobile clients), to support an "offline" mode.

If I understand well, in your design the master and the slaves are linked over Internet.
The master is for instance at the corporation head (main office), and slaves are running in the branch offices.
Seems to be a very usual architecture.

Some questions:
1. But how do you manage any break of the Internet link? In the simplest implementation, you may be able to read the data stored in the local slave sever, but you won't be able to push writes to the master and other slaves. The ability to update locally in offline mode, then push the modifications when the link is up again, is feasible but much more difficult to implement (a lot of conflicts may arise).
2. What if we add a WHERE clause to the slaves? For instance, some tables may need to be fully replicated locally (e.g. the customer list), but some other may benefit to be restricted to some local specifications (e.g. the orders which were performed locally). And for the potential offline mode of clients, it would definitively make sense to have only the data of client's interest cached locally.
3. How should we resolve the synchronization conflicts? E.g. if two slaves do update the same record? The easiest is to pipeline all upcoming modifications in the main server, then let the latest received be the latest written... but such an optimistic solution, even if it is not difficult to implement and safe, may not be good enough for real data.

One of my concerns is that we need to setup a good unit test pattern before implementing all this as an "official" mORMot feature.
There may be a lot of problems to handle (e.g. unsynchronized work)...
In short, if everything is OK, such a design is safe and somewhat easy to implement. But in the real life, offline mode is something difficult to work with...

All this sounds like a dedicated feature request.
Worth a ticket...
See http://synopse.info/fossil/tktview/3453f314d97d

Offline

#27 2014-08-16 13:55:59

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

AB wrote:

Some questions:
1. But how do you manage any break of the Internet link? In the simplest implementation, you may be able to read the data stored in the local slave sever, but you won't be able to push writes to the master and other slaves. The ability to update locally in offline mode, then push the modifications when the link is up again, is feasible but much more difficult to implement (a lot of conflicts may arise).

Exactly the way I would do it. Every modification in the slaves is to write to master (1:1). Thats all. The last will win, what else? If there are problems and the modification couldn't save on the master, then it remains in the change queue of slave and client can delete it or try later (like mail-queue e.g.). 

AB wrote:

2. What if we add a WHERE clause to the slaves? For instance, some tables may need to be fully replicated locally (e.g. the customer list), but some other may benefit to be restricted to some local specifications (e.g. the orders which were performed locally). And for the potential offline mode of clients, it would definitively make sense to have only the data of client's interest cached locally.

Thats a very good idea and will need such a feature in practice. There will be not a stupid 1:1 copy of the data but a sql-select e.g.

AB wrote:

3. How should we resolve the synchronization conflicts? E.g. if two slaves do update the same record? The easiest is to pipeline all upcoming modifications in the main server, then let the latest received be the latest written... but such an optimistic solution, even if it is not difficult to implement and safe, may not be good enough for real data.

We need a queue on master with all modifications. We could have a standard behavior (e.g. first in first out) but the developer can work up the queue with his special code (the developer needs only access to this queue).

I think this would be an "easy" way.

BTW: I'm in Germany not in Peru. Why I need offline mode? It's only a synonym. I'm thinking of a master-slave connection over internet and a slave-client connection in local network. This speed up the REST-requests from (e.g. tablets) clients to slaves (the server of the clients) because of the higher network speed.
But the master could also crash and then the slaves are really "offline". In sum this architecture are much more stable, but also more complex.

Last edited by danielkuettner (2014-08-16 14:22:30)

Offline

#28 2014-08-16 16:21:08

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

I would like to make a suggestion for simplification.

1. Each slave could get a copy of the master db. This could be a only a part of the SQL-Model (some tables, not all e.g.).
2. Every modification of the slave is to send to the master in a queue. This queue will work off by master in the sql-db.

Thats all.

If  a slave want to updates made by other slaves, he has to get a new copy of the master-db (Point 1.).

Point 1 is slow, but easy to implement and not error-prone. If the client are decoupled from master it's no problem. The slaves get the copy in the background.

Last edited by danielkuettner (2014-08-16 16:32:07)

Offline

#29 2014-08-17 07:44:53

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

Thanks Daniel for the feedback.

We need to find some way of implementing ORM redirection as much generic as it could.
Today, we have the TSQLRestServerRemoteDB class, which is a REST server using a TSQLRestClient for all its ORM process.
But it works for all tables, without any redirection argument.

We may take a look at http://synopse.info/forum/viewtopic.php?id=1946 and http://synopse.info/forum/viewtopic.php … 899#p11899 which sounds like another case of ORM redirection...

We may have to develop something more "tunable", with a redirection using proper routing, e.g. per table, or per ID range, or with a "scope" field.
With the potential of having not only a local cache, but a true SQLIte3 engine to answer the read requests, according to the corresponding routing.

This would benefit to be part of the official mORMot repository.

Offline

#30 2014-08-17 08:14:08

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

Ok I've read the other posts and I have the following abstract model in mind:

At this time mORMot work like this:

Client<-->Restserver<-->DB

Now we want do that:

Client<-->Restserver<-->Restserver<-->DB

The Restserver have later 3 connection-models:

a) locale DB
b) external Tables
c) external Restserver

And now we have several ways to make the "middle" Restserver (with a local DB and so on).

Offline

#31 2014-08-17 12:26:50

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

Indeed.

At first, a per-table routing, able to mix your a/b/c kind of connections in the same server, may sound enough?
It would be not much difficult to implement, since all CRUD operations are in fact indexed per table.
There is already a/b routing available.
We need to implement "c" in addition to a/b - but sounds not difficult to do it.

We may later on temperate this per-table routing via an automated pseudo-table mechanism, allowing on-the-fly creation of a table in the model, depending on the client.
(sounds like something similar to what Celso and Moctes do expect...)
I need to think about it, since there is no obvious way to implement it yet. Our TSQLModel relies on the "one class = one table" rule... and if you need another table, you just create a new class, sub-classing the previous. We need to let it be done at runtime (perhaps not during server run, but maybe at server startup).

Offline

#32 2014-08-17 12:42:30

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

AB wrote:

At first, a per-table routing, able to mix your a/b/c kind of connections in the same server, may sound enough?

Yes think it would be sufficient. All other discussed features will come on top later.

Offline

#33 2014-08-17 13:03:18

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

I don't know, would it be a solution, if TSQLRecord would had a field like:

TTableType = (ttExternal, ttExternalCopy, ttLocal)?

Offline

#34 2014-08-17 13:14:41

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

IMHO it should not be part of TSQLRecord, but as part of TSQLRestServer itself, and its TSQLModel.

This is how TSQLRestStorage classes are used for TObjectList or external tables (i.e. to implement a/b).

My though was just to add, just like for TObjectList in-memory storage, two levels of indirection:
- a TSQLRestServer.RemoteDataCreate() method;
- a TSQLVirtualTableRemote class,  for TModel.VirtualTableRegister() method.

After a call to TSQLRestServer.RemoteDataCreate(aClass: TSQLRecordClass; aRemoteRest: TSQLRest) method, any ORM operation to the specific class would be redirected to aRemoteRest.

After a call to TModel.VirtualTableRegister(TSQLRecordMyClass,TSQLVirtualTableRemote),  you may even be able to JOIN some SQL requests over those tables, via the main SQlite3 process of virtual tables.

Offline

#35 2014-08-18 13:36:00

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

I've added TSQLRestStorageRemote class and TSQLRestServer.RemoteDataCreate() method.
See http://synopse.info/fossil/info/796e966c81

Regression tests were also added: the TSQLRecordHistory will be stored in such a redirected table.
We could not find any noticeable performance penalty by using another TSQLRestServerDB instance.

Only direct ORM methods are available yet for per-table redirection: still missing a TSQLVirtualTableRemote class, able to JOIN some SQL requests between those tables.

Offline

#36 2014-08-18 21:05:59

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

Can you explain the difference between TSQLRestServerRemoteDB (page 272 of the Docu) and the new class TSQLRestStorageRemote?
It sounds both like the same for me.

Offline

#37 2014-08-18 21:16:53

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

TSQLRestServerRemoteDB will redirect all tables.

RemoteDataCreate will redirect one table.

Offline

#38 2014-08-19 06:00:29

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

Sorry for my question. You had written abaut TSQLRestServerRemoteDB in your Post #29.
(I'm trying to understand mORMot, but this takes a time.)

Now with RemoteDataCreate we have more possibilities, right?

I can define a HttpRestserver with external tables, like:

VirtualTableExternalRegister(Model, TSampleRecord, Props, '');
Database:= TSQLRestDbServer.Create(Model,'file.sqlite',false); //no user-auth
Database.CreateMissingTables(0);
RestServer := TSQLHttpServer.Create('8180',[Database],'+',useHttpApiRegisteringURI);

And now I can define for special tables a "RestProxy" like this:

VirtualTableExternalRegister(Model, TSampleRecord, Props, '');
Database:= TSQLRestDbServer.Create(Model,'file.sqlite',false); //no user-auth

Database.RemoteDataCreate(TProxySampleRecord, TSQLRest(RestServer));

Database.CreateMissingTables(0);
RestServer := TSQLHttpServer.Create('8180',[Database],'+',useHttpApiRegisteringURI);

Right?

Last edited by danielkuettner (2014-08-19 06:01:28)

Offline

#39 2014-08-19 06:23:24

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

No... You are forcing a wrong type transfer by writing TSQLRest(RestServer)...
Never do this syntax but use "as" operator... And it won't compile.

Offline

#40 2014-08-19 06:51:07

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

DB.RemoteDataCreate(TSQLSampleRecord, TSQLHttpClient.Create('serverdnsname-or-ip.de','8080',Model));

Better?

Offline

#41 2014-08-19 08:04:41

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

Yes!
smile

But ensure that each TSQLRest instance has its own TSQLModel, which may be diverse (this is one of the beauties of this implementation).

Offline

#42 2014-08-19 08:11:27

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

That means, you couldn't share the Model-unit between client and server.

You say beauty, and for you :D, it is.

But don't forget the mm (market and money), the nerds want it as easy as possible.

Last edited by danielkuettner (2014-08-19 08:18:44)

Offline

#43 2014-08-19 08:34:37

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

You could have the same shared model everywhere.
It would be the easiest simple case.
Especially if you use the TRecordReference kind of fields (which identifies the table by model's index).

Or you can have a customized model for each TSQLRestServer instance.
Which may be useful, e.g. for your main server / local branch networks layout.

A good pattern is IMHO to use ORM for simple requests for one table at a time (using data sharding and complex types like TDocVariant variants, records or dynamic arrays), and interface-based services, running on the main server, for more complex (joined) queries and process.

I've just enhanced the documentation about this.
http://synopse.info/fossil/info/d9e53fd620

Offline

#44 2014-08-19 09:46:07

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

I can share the Model-unit, but not the Reference of TSQLModel.

AB wrote:

A good pattern is IMHO to use ORM for simple requests for one table at a time (using data sharding and complex types like TDocVariant variants, records or dynamic arrays), and interface-based services, running on the main server, for more complex (joined) queries and process.

You mean, a have a normalized SQL-DB, make a view/join an handle this in ORM with "one" TSQLRecord instead of using joined TSQLRecords.
Thats also my method up to now.

Last edited by danielkuettner (2014-08-19 10:19:25)

Offline

#45 2014-08-19 10:53:11

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

Yes this is called "sharding".
See http://blog.synopse.info/post/2011/07/0 … chitecture
And the "aggregate" concept, as defined in DDD, in the SAD PDF and NoSQL DB - see http://blog.synopse.info/post/2014/02/2 … bases-ACID

Offline

#46 2014-08-19 11:13:16

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Couldn't see changes by other process, because of caching?

Thanks for your confirmation, it's hard to hear so many No's.
But, you write this and have implemented the possibility of "joins" with TSQLRecords. Becaus of this I will insecure and I've to ask such questions.
Please don't think I write to make me important.
I will give you a feedback from a mORMot starter and his problems. Perhaps you could use this information for your work.

Last edited by danielkuettner (2014-08-19 11:17:13)

Offline

#47 2014-08-19 14:50:32

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

Yes, sometimes the relational model is good idea.

We did not want to be tied to one model.
But on the other side, mORMot supports both RDBMS and NoSQL/Aggregates implementation patterns.
You can choice the best for you.

Offline

#48 2015-04-01 09:16:35

AOG
Member
Registered: 2014-02-24
Posts: 490

Re: Couldn't see changes by other process, because of caching?

Hello Ab,

Thanks for the ORM Master/Slave Replication feature !

As you will know, I am using the mORMot also on Android.
And this replication will (hopefully) solve a very important mobile problem/aspect: offline functionality.

Usercase:

Mobile users will, when online, be connected with a mORMot restserver. Doing CRUD.
When offline, they will have to fallback to local storage, only doing reads.

What would you advice:

1) Always query the local database, that will update all changes to the remote master when online. Local database will be updated by master when online.
2) Doing REST with the master when online and keep the local database updated. Connect locally when offline.

Greetings, Alfred.

Offline

#49 2015-04-01 10:07:34

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Couldn't see changes by other process, because of caching?

We could indeed have a SQLite3 storage (or even in-memory TObjectList storage) on the mobile.
Of course, using FPC for compilation - the feature is not available in the SynCrossPlatform units yet.

About use cases, see http://synopse.info/files/html/Synopse% … l#TITLE_98
In your case, the mobile app is some kind of "local office", with its own local server.
1) The problem is about the difficulty of the mobile to be a "master" replication, without being a HTTP server.
We may consider using websockets, or a set of commands from the mobile to emulate REST requests...
2) The problem is that a slave DB is read-only when offline... but may be fine in most cases, with a temporary storage system.

Offline

#50 2015-04-01 10:31:48

AOG
Member
Registered: 2014-02-24
Posts: 490

Re: Couldn't see changes by other process, because of caching?

Yes (forgot to mention), I am on FPC, so I am able to use the whole mORMot.

I will go for option 2. Readonly when offline is perfect.
Thanks.

One question remains:
Your link sends me towards : 5.8.2. TSQLRecord.ComputeFieldsBeforeWrite.
Could you elaborate your intention ?
Do you mean to use this to automagically update the local database when sending data to the remote database ?

Offline

Board footer

Powered by FluxBB