SQLite Forum

Timeline
Login

5 forum posts by user dubiousjim

2020-12-24
17:51 Reply: SQLITE_FCNTL_WIN32_GET_HANDLE (artifact: 47f322ad0d user: dubiousjim)

Hi just bumping this, in case it fell under the developers' radar.

2020-05-26
18:02 Reply: readonly access to WAL database (artifact: bf882b18d7 user: dubiousjim)

Thanks. I was just using the shell for testing. And I was only using the SQLITE_FCNTL_PERSIST_WAL mode in order to make it possible to open the database -readonly even when no r/w processes were using it. (Didn't want to rely on the r/o process having write access to the directory containing the database.)

What I've learned from all this is:

  • Though it's possible in recent versions of sqlite to open -wal databases r/o, it still is awkward in a couple of ways, and opening rollback-journal databases r/o is easier.

  • If you do want to open a -wal database r/o, it can't be in locking_mode = EXCLUSIVE. That's ok with rollback-journal databases though.

  • If you do open a rollback-journal database in locking_mode = EXCLUSIVE, while lacking write permissions to the database file or the directory that contains it, you are able to successfully use the database. From my experiments, it looks like the lock is in those cases simply not acquired.

14:01 Reply: readonly access to WAL database (artifact: b287de44c8 user: dubiousjim)

Looks like the problem is that I was defaulting to locking_mode = EXCLUSIVE. If I open the database -readonly but then do pragma locking_mode=normal;, it works as expected.

(But then a -shm file is created, so I expect this wouldn't work if that file didn't already exist and the sqlite process didn't have permissions to create it.)

12:39 Post: readonly access to WAL database (artifact: 5a4383833f user: dubiousjim)

Hi I'm finding myself unable to open a WAL database readonly. I've read the WAL documentation and this thread.

I observe this problem with the sqlite3 shell as well as with my C code, so let's set the latter aside. The only thing relevant about the C code is that it first creates the database, sets journal_mode = WAL, and calls sqlite3_file_control(...SQLITE_FCNTL_PERSIST_WAL, &1). In my testing, the -wal file is always at 0 bytes.

I can then open the database fine in r/w mode using the sqlite3 shell. (Though doing so then removes the -wal file when I close the database, as the SQLITE_FCNTL_PERSIST_WAL apparently needs to be set every time the file is opened. Don't know how to do that from the shell.)

If I try to open the database -readonly using the shell (whether the -wal file has been removed or not), this is what happens:

$ sqlite3 -readonly test.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .databases
Error: disk I/O error
sqlite> .schema
Error: disk I/O error
sqlite> select count(*) from sqlite_master;
Error: disk I/O error
sqlite> .q

The sqlite3 shell does have Unix r/w permissions to the database file, the -wal file, and the directory that contains them.

My environment is a Mac 10.10.5, using the macports toolchain. But I compiled the sqlite3 shell by hand.

There is no -shm file because I compiled the shell (and also the libraries my C code linked against) using -DSQLITE_DEFAULT_LOCKING_MODE=1.

Here are the shell's config options:

sqlite> pragma compile_options;
COMPILER=clang-9.0.1
DEFAULT_FOREIGN_KEYS
DEFAULT_LOCKING_MODE=1
DEFAULT_WAL_SYNCHRONOUS=1
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS5
ENABLE_GEOPOLY
ENABLE_JSON1
ENABLE_LOAD_EXTENSION
ENABLE_PREUPDATE_HOOK
ENABLE_RTREE
ENABLE_SESSION
ENABLE_STAT4
ENABLE_STMTVTAB
ENABLE_UNKNOWN_SQL_FUNCTION
HAVE_ISNAN
LIKE_DOESNT_MATCH_BLOBS
MAX_EXPR_DEPTH=0
MAX_VARIABLE_NUMBER=512
OMIT_AUTOINIT
OMIT_DECLTYPE
OMIT_DEPRECATED
OMIT_PROGRESS_CALLBACK
OMIT_SHARED_CACHE
SECURE_DELETE
SOUNDEX
TEMP_STORE=2
THREADSAFE=0
USE_ALLOCA
USE_URI

If I open the database in r/w mode, set the journal_mode = DELETE, and close it, then I can subsequently open and use the database fine in -readonly mode. But I'd like to understand what I'm doing that producing the inability to access the database with WAL and readonly together, which the documentation seems to say should work in my situation. (If the -wal file still exists, it's supposed to work, or if that file doesn't exist, but the shell has write-access to the directory and so can create it, it's also supposed to work.)

2020-05-20
11:59 Post: SQLITE_FCNTL_WIN32_GET_HANDLE (artifact: 26e53da72f user: dubiousjim)

This opcode was introduced in sqlite 3.15.1 (2016-11-04). But it's currently only implemented in src/os_win.c. It'd be useful to have a parallel implementation for unix, something like this:

@@ -42334,6 +42334,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;

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

Here's why I'd find this helpful. 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. sqlite3's current API doesn't provide for these things. I can to some extent accomplish them using my OS's filesystem API. Ideally, there would be another variant of sqlite3_open(), which took an open file descriptor or handle as an argument, rather than a pathname. I'd welcome that if it were feasible. But a more minimal approach would be to use the filesystem API to open a file, and use the sqlite3 API 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.

(If you 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. 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.)

I expect there's been prior discussion of this, but when I search this forum and the (now-closed) mailing list for the WIN32_GET_HANDLE opcode, I come up empty. I did find this thread.