Thanks for the comments, Dag and Rick.
See my replies below, which address comments from both of you.
(Dag)> SQL uses the keyword "DROP", is there a reason you prefer "delete" over drop in the URL?
No, I wouldn't mind changing it to "drop" if that is more in line with the SQL spec. Is there something like "drop database" in the SQL spec?
Note that I don't have any plans at this time to implement a way to delete databases using SQL.
(Dag)> ... [authentication and authorization] ... But it would be slightly asymmetrical. What do you think?
(Rick)> I wonder if we need to check whether authentication is on?
I think we need two different paths here. Assuming SQL authorization will be used to control who can delete a database in the future, what about this?
if (authenticationOn && sqlAuthorizationOn)
checkPrivileges(); // May also want to see if the user is the DBO here, depending on implementation details.
Since we don't have the required functionality to use system privileges through GRANT/REVOKE yet, we can just check for DBO unconditionally as Rick suggests.
Note that this will allow only the DBO to delete the database. Another option is to allow everyone to delete a database if no authentication/authorization is enabled, but this is of course very liberal.
(Rick)> ... [authentication] ... In such a situation, I think it's enough to require that there be no open connections to the database.
This doesn't happen on shutdown, does it? Why should it happen on drop/delete, in which case the data goes away anyway?
The only reason I can think of, is to allow the existing connections to finish their queries.
As an experiment, I implemented a simple mechanism to block database access. The mechanism was activated by the delete request, but could also be made available through the JDBC connection URL (would make it possible for the DBO to block access for maintenance work). My test, which ran 3 - 20 concurrent threads accessing the database to be deleted and then executing a simple query, gave much better results when the block mechanism was used. There are levels of consideration:
- wait for ongoing connection attempts to finish (with the block mechanism, new connections won't be allowed)
- wait for a given time to allow [some] queries to complete (issue with long running queries)
- wait for all connected clients to disconnect (with timeout?)
Especially ongoing connection attempts result in lots of different error messages. I've seen the shutdown exception (which is fine, I think), NPEs, conglomerate not found exceptions, and others. It is also a question of when one should signal / perform shutdown, as this will cause the connections to be dropped at some point.
(Dag)> I think enabling dropping of on-disk databases is interesting. What would the cons be?
I'm thinking mostly about the security aspect. Are there other issues?
A possible add-on feature here would be a property disabling the JDBC delete mechanism for production systems, but this could be problematic if deleting some databases in the system should be allowed. Maybe SQL authorization could be used instead, if the drop/delete privilege can't be granted again if it has been revoked?
(Rick)> I think the feature should be designed so that it can be extended to the on-disk back end. However, I would treat on-disk back ends as a separate JIRA - ...
I agree. This will require some extra code right away, because the functionality is already used internally (to re-create the service root). I know about several ways deletion can be disabled for a specific storage factory, but they all have drawbacks... Maybe there is a better way I haven't found yet, but I'll get back to the details later.
(Rick)> ... [system privileges] ... That privilege includes the specification of where you are allowed to create databases.
Even though "where" isn't quite as well-defined in the in-memory back end as in the on-disk/directory back end, I think such a privilege may be enough. However, the Java security manager, or a custom one, would have to be faulted in since the in-memory back end doesn't access the virtual files through java.io.File.
It is not clear to me if such a privilege would allow us to differentiate between back ends, or rather subsubprotocols.
Would the system privileges also allow us to say who are allowed to create databases?
I now have the following known tasks on my list:
- add in-memory specific code required for drop
- add generic code required for drop (includes URL handling)
- add database access block mechanism
- disable drop/delete for on-disk back end (the rest of the back ends are read only)
- add drop tests (for in-memory back end primarily)
- add mechanism to control / limit database creation (we can already use the Java security manager for on-disk databases, but not for in-memory databases)
(- improve handling of unsuccessful boots in the in-memory storage factory (resource management issue))
Further comments are welcome.