The thing I need to do is rather simple. Retrieve updated|new records from the application server.
Updates propagate strictly from server to client.
GET on scripted endpoint have an additional parameter which holds JSON packet of record ids+updated timestamps of records from local DB.
- Script handler parses incoming JSON packet, creates temporary table from it.
- Use SQL API calls to perform queries on virtually joined persistent tables and the temporary one
- Return query results in response’s JSON packet.
- Complete script execution, dropping temporary table (or make it to drop automatically upon exit script scope). At first glance, the concept of temporary table is not supported via API, moreover, I suspect, that DF uses one DB session internally per DB connection, so implementing temporary tables would be useless in that scenario, because tmp tables would be multiplied unless session is dropped.
So it seems that right approach would be creating a table via API (see an additional question below, though), in try scope, temporary name may be generated by str_random(n) (Laravel utility function), and dropping it in finally.
It it possible to implement such a scenario using existing API? If temporary tables are not supported, maybe there are methods to create table with arbitrary scrambled name, and drop it afterwards, wrapping script in try-finally scope?
Additional question relates to defining an access.
As soon as scripted handler requires to create a temporary table on specified MySQL schema via API calls, though internal ones, I suspect that the Role, defining access to the scripted service functionality, should, as well, allow write access to schema, right?
I would rather like to reserve write schema access for the script internals only. I.e. script access Role should provide read-only endpoints, like for anonymous|unauthenticated users, while internally, it may invoke write access to specific DB service, to allow temporary table creation and deletion.
Whether it is possible or not, remains unclear.
As an ugly, CPU-ineffective, and memory-consuming alternative, which I would prefer not to use at all, would be processing each record from a table to be queried, programmatically, in a loop, instead.
