mORMot Open Source

chapa

Member

Registered: 2012-04-30

Posts: 117

TSQLVirtualTable

Hi ab,

I would like to implement TSQLVirtualTable, TSQLRestServerStatic and TSQLVirtualTableCursorIndex descendants. Just like TSQLVirtualTableJSON do for example.
So far everything is going well.

I am overriding TSQLVirtualTable's:

function Delete(aRowID: Int64): boolean; override;

and at some point would like to do:

Static.Owner.fCache.NotifyDeletion(Static.StoredClass, aRowID); //pasted from TSQLVirtualTableJSON.Delete()

fCache can be accessed from public TSQLRest.Cache property, but protected TSQLRestCache's method NotifyDeletion and whole protected Notify* family methods are not accessible.

Solution may be to implement my descendants in SQLite3Commons unit, so protected RestCache methods will be accessible. But I prefer to not mess with framework code and define my classes in other units.
I would like to recommend moving the needed methods from protected to public section of the TSQLRestCache class, so everyone will be able to control the Rest Cache implementing custom Virtual Table.

Thanks.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,658

Website

Re: TSQLVirtualTable

You are right.

Most of the cache is implemented at REST level, so in most cases, you do not need to deal of caching in your virtual tables (for instance, SQLite3DB does not set the cache at all).
But if you want to be able to have cache working at SQL/DB level, and not only at CRUD/REST level, you will have to call Notify*() methods.

See http://synopse.info/fossil/info/5325a462e1

For my curiosity, which kind of virtual table are you implementing?
Could it be shared within the main mORMot project?

chapa

Member

Registered: 2012-04-30

Posts: 117

Re: TSQLVirtualTable

Surely it will be shared, I would love to, just give me a few weeks to tune up my existing code within mORMot framework.
I am driven from my use cases, but I am sure they can suit most of developer needs.

There are two use cases I need to cover.
First is handling big amount of data, second is decoupling user interface from the data, even of very intensive insert/update/delete operation over the data. User interface may not react to every single data change. But finally, they will be in sync.

1.
Lets imagine we have a big amount of business data. "Big amount" is abstract for every user. Lets say we have a table, containing a few millions of records.
We expose this table to our customers. It can be read only to them, but not to the editors of the data. But every reader will need to see the changes made by the "editors" on demand.

First problem is, how to show millions of records to every reader?
Google approach is to show nothing at front page, than put a search, will see paginated match of query.
But in custom application, human mind will feel more comfortable if it see a virtual table (list) containing all the records, than filter the records by its criteria, than filter again, result may contain also a millions of records, but he will keep filtering till find what is searching for. And most important, the user will see all the result in one table, being able to scroll and preview all the result data, just scrolling the table, not paginating. This will not help much finding exact match, but it is an advantage from human philosophical point of view.
TVirtualTree is good example how we can show initially big amount of rows (records) and initialize the nodes on demand. Nodes initialized by request from user interface.
This data is most common to persist.

2.
Viewing "real-time" statistic data, which is changed very intensive. Very few rows, let say 50, fitting on one screen. Statistic information which may change hundreds of time per seconds. So much that it is not suitable for user-interface to react to every change.
This data is most common to not persist.

When showing data in virtual table view, we will need to couple the data "row" id and table row index. Just like you do with IDToIndex in TSQLRestServerStaticInMemory.
This is where I define three Virtual "Proxy" Tables. I use proxy word in context of proxy to memory, proxy to db, and proxy to cached db, just like TSQLRestServer do using TSQLRestCache.

1. Proxy to memory:
Little count of rows, which will not need to persists, can be updated very intensive.
2.Proxy to db:
Big amount of on-disk persistent data, which will be not so fast, update more rarely but will be safe.
3.
Proxy to cached db, mix of 1 and 2:
Big amount of on-disk persistent data, little set of which will be updated very intensive. Will persist, but in case of failure last updates may not have a chance to persist.

All three cases are implemented using VirtualTables.
You may say all of this is currently implemented in mORMot framework.

What I will add over current implementation is:
1. All virtual tables will expose virtual methods for functions like GetFieldVarData(), SetFieldVarData() or CopyObject(), so if one like to gain extra speed will implement them knowing what kind of TSQLRecord descendant is dealing with.
2. Will let client to subscribe to listen/notify channel over tables/records of interest. Currently and unfortunately not transaction aware. Will track all changes, consolidate them, stream to the client on request.

In a simple example it may look like this:
1. Client request to listen to TSQLBigData table of records.
2. Client receive thread safe records count.
3. Client request TSQLBigData records from index 0 to 20, for the needs of user-interface.
4. As client scroll, it will need other TSQLBigData records, located at view index position. Returned rows will be according current client view state. Ex. If client request row idx 15 for view, have not still received changes from the server, but record 10 is already deleted and logged as delete event, the server will know that it should return record idx 14 because when the client process deletion of record 10, the requested row 15 will be at position 14.
5. Client will be notified for table count change, inserts, deletes, updates, only for the data, which is already requested in view. It will be up to the client to define the UI refresh interval.
6. Client may change (inset/delete/update) the set. Every other listening client, which have focus on changing record will be notified for the change.

It will be transparent to client how big the data is, how often it is updated, does it persists, etc.
Client will be able to show both big data sources and high intensive ones.

All low-level functionality already done and in production. I can not write here about all implementation details, aspects and use-cases. This post is already too long. But I would like to see them in mORMot framework, hoping they will help other users too.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,658

Website

Re: TSQLVirtualTable

Very nice!

But IMHO this is already how virtual table works: you have to override some methods for all CRUD operations.
And in-memory cache is better implemented at REST level, IMHO.

Paging is a need, but the underneath DB would need to feature it.
Most DBs are doing "false paging", i.e. the data is retrieved on the server side, but sent by little peaces.
I have found it to be a bit slow, unless paging order exactly match index orders. For most sophisticated queries, this is not the case.
I'm not sure how you will be able to implement it better than a DB backend.

Sounds to me like a re-implementation of RDBMS classic "views".
It should be better implemented at the DB side.

But virtual tables in SQLite3 are an amazing feature.
Worth studying and investigating, I think.