Expose SQLITE_FCNTL_*_GET_HANDLE on Unix

SQLCipher
1

Here’s something I’d like to do but don’t think is possible using the current SQLite/SQLCipher API.

I’d like (1) to be able to request the opening of a new database file, with the attempt to fail if there’s already a file there. I’d also like (2) if I request the opening of a database file that’s allowed to already exist, but with permission to create a new file if needed, I’d like to be given feedback about whether the file was in fact newly created.

The need to do these things is somewhat more pressing with SQLCipher than with SQLite, since I may want to prompt for and handle a passphrase differently, depending on whether the database was newly created.

Ideally, there would be another variant of sqlite3_open() , which took an open file descriptor or handle as an argument, rather than a pathname.

But failing that, I can use my OS’s filesystem API to open a file, and then use SQLite/SQLCipher to to attempt to open the same file, and then verify that the files are the same. Then one could release/close the filesystem-opened file. (One has to do this before SQLite/SQLCipher acquires any file locks on it.)

(If you instead just used the filesystem API to check whether any file existed at the desired path, and if not then told sqlite3 to open a file there, allowing creation, that wouldn’t be atomic. You couldn’t be sure that a file didn’t appear between the time you checked the path and saw nothing there, and the time when sqlite3’s open interacted with the filesystem.)

So how to verify that a file I opened using my OS filesystem API is the same file that a sqlite3 connection is using?

On Windows, one can extract the underlying file handle using sqlite3_file_control(..., SQLITE_FCNTL_WIN32_GET_HANDLE, ...) . On Unix, I hoped to be able to extract the underlying file descriptor in the same way. But that doesn’t yet seem to be possible.

If the following patches are applied to src/os_unix.c:

@@ -3959,6 +3959,10 @@ static int unixFileControl(sqlite3_file *id, int op, void *pArg){
       *(int*)pArg = pFile->eFileLock;
       return SQLITE_OK;
     }
+    case SQLITE_FCNTL_WIN32_GET_HANDLE: {
+      *(int*)pArg = pFile->h;
+      return SQLITE_OK;
+    }
     case SQLITE_FCNTL_LAST_ERRNO: {
       *(int*)pArg = pFile->lastErrno;
       return SQLITE_OK;

If the patches above are applied, then one can do this. (Then one can do a stat on the file descriptor and verify that its device and inode correspond to those of the file opened via the filesystem API, and afterwards close/release the filesystem-opened file.)

Of course, if that were added, it might be friendly to create another macro name for the opcode, such as:

 #define SQLITE_FCNTL_WIN32_GET_HANDLE       29
+#define SQLITE_FCNTL_UNIX_GET_FD       29

I’ve asked on the SQLite forum about adding this functionality, back in May, but got no response there. Since the usefulness of this patch is somewhat greater on SQLCipher than on SQLite (as I explained above), I thought it would be worth raising the possibility here.

2

Hello @dubiousjim - Thanks for using SQLCipher and for providing a detailed explanation of your situation. I can definitely see your point about the different behavior for windows right now, but that kind of change would really be something that should be considered upstream for SQLite. I took a look at your forum post and it might be worth bumping the thread by replying to it again. That might put it back on the development teams radar.

That said, reading over your needs, I think this would be an excellent candidate for using VFS. The VFS API is very flexible, and would allow you to create an implementation that could track the underlying file handles, add custom open behavior, or create a back-channel for reporting information to the application.

What do you think about that as an option?

3

Hi @sjlombardo, OK, thanks, I’ll look into making a custom VFS. I’ve also bumped the original request on the SQLite Forums.