I am trying to come up with a way to update the database (in my case firebird ddl statement) at the server end when no connections are active without someone having to manually do it (and kick people off). So I was thinking of using the api/databases command to return connections (json) as I see that connections are dropped after a time of inactivity even if client app is still running. So after a DB update has been scheduled I could monitor the connections until there is no active ones (json is empty ) then perform the update.
Question 1: As client apps could still be open, is there a graceful way to return a message to client upon any further http call to the database that 'An update is in progress' rather than stopping the sparkle server and client getting an http timeout until the update is complete?
Question 2: Can I get the same connection information internally at the server end (service) without having to do a client http call within my service back to itself (localhost) to avoid any potential security firewall issues?
There is no such built-in mechanism designed with that purpose in mind, but maybe you can use the OnBeforeStatement event and raise an exception there if an update is in progress. You will have to check if that fits your needs.
When you say "internally at the server" you mean from the same exe as RemoteDB is running, or from the same server computer?
Regardless, I'd recommend performing an HTTP call, using localhost as the target I really doubt the firewall would block such connections.
I'm unable to do a localhost call as the server is started with https and accessed through domain name, I would have to do an https call. I don't know the domain within the remote DB service (I'm using wildcard https://+ for BaseURL) unless I get the client to manually enter it as a configuration value. Its all getting a bit tricky.
Is there no way I can access the TRemoteDatabase for each Tdatamodule - TRemoteDBServer I have created? to access the Id, ClientId etc. And also somehow call Module-RemoveDatabase(const DatabaseId: string)?
I'm not sure I understood your question here. But in any case, RemoteDB server doesn't keep a record of data modules or components open in the client. The information he has available is provided via the admin API: Creating RemoteDB Server | TMS RemoteDB documentation
Alternatively you can keep your server running as HTTP and put an edge proxy in front of it (Traefik, Nginx) or even use ready-to-use wrappers like Cloudflare or AWS Cloudfront. Then your server is HTTPS from the outside, but it's HTTP from the internal network.
Thanks, On the https part, If I initiate my database structure update from the client I can pass the domain name through my dummy SQL query that I use to start and end the update (captured within OnBeforeStatement) that also gives me the clientid of the client executing the update so I don't disconnect that one. So performing my Database structure update from the client is ok.
The second phase is for me to have the update scheduled and run at the server end RemoteDB service application. This is where I wanted to get connection info internally within the service application.
I experimented with adding a property in the RemoteDB.Server.Module to expose the FDatabases
property (RemDatabases: TDictionary<string, TRemoteDatabase> read FDatabases;) then duplicate the DoDatabases function in my own code but no http just return the JSON from the function instead. This worked but I don't really want to change the code in the RemoteDB.Server.Module.
Thought I would use a different approach, for the scheduled server side update, I don't need to keep any connections so I can block any connections in the OnBeforeStatement and then close all database connections through the FireDac components, make a new connection just for the Database Update then unblock client requests when update is complete.