How to create a temporary table, and discard it afterwards?


#1

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.

  1. Script handler parses incoming JSON packet, creates temporary table from it.
  2. Use SQL API calls to perform queries on virtually joined persistent tables and the temporary one
  3. Return query results in response’s JSON packet.
  4. 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.


#2

Followup.
Assuming Roles access is set-up properly, which I do not want to dive into…

  1. Solved. Create table with schema API call from script, than, next call is used to populate it. Table dropped in finally{} scope afterwards.
  2. Solved. Fell back to calling stored procedures, with internal PREPARE, EXECUTE, and DEALLOCATE PREPARE, parametrized by table names. Calls made from script, using API as well.

As a result - just one GET call to scripted PHP service, passing comparison data packet, returned are ‘updated’ and ‘new’ packets, stuffed into response content.
Bonus: I’m two days wiser.


#3

Thanks for the follow up @Vsevolod_Gromov! We’re glad to hear you are the wiser from it :slight_smile: