Using SQLite Databases in Any Folder in WinRT

• Download SQLiteWinRTVFS.zip - 199.3 KB

Introduction

Application developers don't have many options when it comes to database access in "Windows Store Apps" (or as I prefer, WinRT). Fortunately, Microsoft officially supports SQLite, which has been ably ported to WinRT and fits most mobile developers' needs. Unfortunately, SQLite for WinRT is limited as to where database files may be located - they must be in the app's local data store and nowhere else. With this code, we change all that.

Background

If you're brave enough to delve into the SQLite source code, (http://sqlite.org/download.html), you'll see that the file I/O APIs for WinRT rely on CreateFile2. In Windows desktop applications, CreateFile2 and its associated read/write APIs are very versatile and fast, and a great choice for a lightweight in-process database engine. In WinRT, however, CreateFile2 is limited to opening or creating files in the app's local storage folder. Even if your app has access to a location via a file picker or rights declared in the manifest, it doesn't matter. Because SQLite uses CreateFile2, and not StorageFile and StorageFolder, you simply cannot read or write database files anywhere other than to or from the app's sandbox. Forget about accessing databases on a network, thumbdrive, etc., without copying them first in toto.

We could just re-write SQLite to work with StorageFile and StorageFolder directly, but boy would that be a huge headache. Fortunately, the writers of SQLite were pretty clever. At a time well before cross-platform was cool, they used an ingenius file I/O abstraction system called VFS - virtual file system. Every time SQLite is ported to a new platform, a new VFS is created specific to that platform. A VFS is basically a highly simplified version of a COM interface. That's right - it's little more than a struct (two actually) full of function pointers  - pointers to functions which you can write yourself in C or C++ (or potentially even C#) and provide to SQLite at the beginning of your app's lifecycle. Which means, in a nutshell, we can write our own VFS for WinRT that uses StorageFile and StorageFolder to access our databases, rather than rely on CreateFile2 with its limitations. With this VFS, if your app has access to a folder - any folder - it can read and write databases there without having to copy them to ApplicationData.LocalFolder first. And, it's 100% Windows Store policy compliant.

Coding a VFS

Writing a VFS is not all that easy, because there are a lot of functions that need to be declared (even if not fully implemented). A great starting point is available from SQLite's website, though, which will serve as the framework for our VFS. Right now it's called test_demovfs.c. Let's re-name it to WinRTVFS.cpp because we're going to be using C++/CX for this.

Defining the "File"

One of the first things we need to define for our VFS is what will a "file" be. We do this by defining a struct that more or less "derives" from the sqlite3_file base struct. (This is C-based OOP my friends! Doesn't it make you nostalgic?) You'll see in the demo VFS, the basic "file" type is defined as:

typedef struct DemoFile DemoFile;
struct DemoFile {
  sqlite3_file base;              /* Base class. Must be first. */
  int fd;                         /* File descriptor */

  char *aBuffer;                  /* Pointer to malloc'd buffer */
  int nBuffer;                    /* Valid bytes of data in zBuffer */
  sqlite3_int64 iBufferOfst;      /* Offset in file of zBuffer[0] */
};

Well this won't work at all for WinRT now will it? WinRT doesn't use "file descriptors" (nor handles) but rather StorageFile objects which are ref classes. Now, you *could* actually include a ref handle to a StorageFile in this struct if you wanted to - because it's unmanaged code, C++/CX actually lets you mix ref handles and traditional pointers unlike C++/CLI which is managed. So you could do something like this:

typedef struct
{
   sqlite3_file base;              /* Base class. Must be first. */
   StorageFile^    storageFile;    /* ref handle to StorageFile object representing database */
} WinRTFile;

This isn't a good idea either though, because a file object in an SQLite VFS actually represents an opened file. A StorageFile on the other hand is really little more than a glorified path. You don't actually gain access to the file until you call OpenAsync or a similar method on StorageFile, and get the IRandomAccessStream. But SQLite calls read and write functions A LOT, and having to open the file and get a stream each time will kill performance.

Instead, the closest thing we have to a file handle in WinRT is in fact the IRandomAccessStream we get when we open the file, so that in fact is what we'll use to represent the file:

typedef struct
{
   sqlite3_file base;              /* Base class. Must be first. */
   IRandomAccessStream^ stream;
} WinRTFile;

The stream member is our own, but what is this base member? It's actually just a table of function pointers which we will need to populate. As mentioned, you can think of this strucutre as analogous to a COM interface, or a derived class (in which case, the function pointers would be pure virtual until you populate them). 

It'll be up to us to initialize and populate this structure every time SQLite wants to open a database file. We'll see how in a minute.

Declaring the Function Implementations

There are actually two "interfaces" we are going to need to implement. The first is the VFS object itself; the second is for the WinRTFile object. Here's the full list (renamed to WinRTxxx):

// These are functions that implement the VFS "interface"
int WinRTOpen(sqlite3_vfs *pVfs, const char *zName, sqlite3_file *pFile, int flags, int *pOutFlags);
int WinRTDelete(sqlite3_vfs *pVfs, const char *zPath, int dirSync);
int WinRTAccess(sqlite3_vfs *pVfs, const char *zPath, int flags, int *pResOut);
int WinRTFullPathname(sqlite3_vfs *pVfs, const char *zPath, int nPathOut, char *zPathOut);
void *WinRTDlOpen(sqlite3_vfs *pVfs, const char *zPath);
void WinRTDlError(sqlite3_vfs *pVfs, int nByte, char *zErrMsg);
void(*WinRTDlSym(sqlite3_vfs *pVfs, void *pH, const char *z))(void);
void WinRTDlClose(sqlite3_vfs *pVfs, void *pHandle);
int WinRTRandomness(sqlite3_vfs *pVfs, int nByte, char *zByte);
int WinRTSleep(sqlite3_vfs *pVfs, int nMicro);
int WinRTCurrentTime(sqlite3_vfs *pVfs, double *pTime);

// These are the required sqlite3_io_methods to implement the sqlite3_file "interface"
int WinRTClose(sqlite3_file *pFile);
int WinRTRead(sqlite3_file *pFile, void *zBuf, int iAmt, sqlite_int64 iOfst);
int WinRTWrite(sqlite3_file *pFile, const void *zBuf, int iAmt, sqlite_int64 iOfst);
int WinRTTruncate(sqlite3_file *pFile, sqlite_int64 size);
int WinRTSync(sqlite3_file *pFile, int flags);
int WinRTFileSize(sqlite3_file *pFile, sqlite_int64 *pSize);
int WinRTLock(sqlite3_file *pFile, int eLock);
int WinRTUnlock(sqlite3_file *pFile, int eLock);
int WinRTCheckReservedLock(sqlite3_file *pFile, int *pResOut);
int WinRTFileControl(sqlite3_file *pFile, int op, void *pArg);
int WinRTSectorSize(sqlite3_file *pFile);
int WinRTDeviceCharacteristics(sqlite3_file *pFile);

Creating the VFS for the Consumer

Before we jump to the implementation, let's look at how we actually set up the objects we ultimately will need to give to our consumer so that they can utilize our VFS. Just like COM frameworks, a VFS needs some global function that creates and populates the object and returns the "interface" that the consumer can use. So we need to write a function that creates an sqlite3_vfs pointer to pass to the sqlite3_vfs_register function to register this file system with SQLite so that it can be used to read from and write to databases.

Since this is WinRT, chances are we might want to interop with C# at some point, so the best practice here is to encapsulate this code in a static function of a ref class so that this VFS consumed by other languages as a Windows Runtime component. 

namespace SQLiteWinRTExtension
{
    public ref class WinRTVFS sealed
    {
    public:
        static bool Initialize (bool makeDefaultVFS)
        {
            sqlite3_vfs* pVFS = new sqlite3_vfs
            {
                1,                            /* iVersion */
                sizeof(WinRTFile),             /* szOsFile */
                MAXPATHNAME,                  /* mxPathname */
                0,                            /* pNext */
                "WinRTVFS",                   /* zName */
                (void*)0,                      /* pAppData */
                WinRTOpen,                     /* xOpen */
                WinRTDelete,                   /* xDelete */
                WinRTAccess,                   /* xAccess */
                WinRTFullPathname,             /* xFullPathname */
                WinRTDlOpen,                   /* xDlOpen */
                WinRTDlError,                  /* xDlError */
                WinRTDlSym,                    /* xDlSym */
                WinRTDlClose,                  /* xDlClose */
                WinRTRandomness,               /* xRandomness */
                WinRTSleep,                    /* xSleep */
                WinRTCurrentTime,              /* xCurrentTime */
            };
            return (::sqlite3_vfs_register(pVFS, makeDefaultVFS) == SQLITE_OK);            
        }
    };
}

The only necessary function here is the static Initialize, which takes one parameter - a flag that specifies whether this should be the default file system for this SQLite instance. Usually you should make it the default. This function should be called once on application startup and need never be called again.

The sqlite3_vfs structure is populated at creation, mostly with the 11 functions we declared earlier. The other members define the version, the size of the WinRTFile handle we defined earlier, the max path we can support (512 for WinRT should be sufficient) a pointer to the next registered VFS if we'd defined more than 1 (no need to), and finally the name of our custom VFS - which you'll need to pass to the sqlite3_open_v2 function if you don't make the WinRT VFS the default. Also, since this function is only going to get called once during the application lifecycle, there's really no need to delete the sqlite3_vfs object. (The demovfs example uses a static local variable; find that terribly risky in Windows Runtime. since we have no idea what's really going on under the hood.).

Implementing VFS-Level Functions

Now we turn to the real meat of implementing a VFS - the required functions. We certainly won't go through all of them - nor is it necessary to implement all of them - but the most important ones are obviously the Open and Close functions, so we'll cover those here first.

Opening a Database File

Here's the detaled parameter list of our WinRTOpen function:

int WinRTOpen(
    sqlite3_vfs *pVfs,              /* VFS */
    const char *zName,              /* File to open, or 0 for a temp file */
    sqlite3_file *pFile,            /* Pointer to WinRTFile struct to populate */
    int flags,                      /* Input SQLITE_OPEN_XXX flags */
    int *pOutFlags                  /* Output SQLITE_OPEN_XXX flags (or NULL) */
    )
{

This need more or less needs to open the file at the path given by SQLite, and then populate the sqlite3_file structure that SQLite provides via pFile with the other VFS functions can use to access the database file. Of course, this will actually be a pointer to a WinRTFile structure, though SQLite will be totally oblivious to this. SQLite has already pre-allocated enough memory to hold our entire WinRTFile structure - that's why we had to tell it the sizeof that structure when we created the VFS above. Therefore the first thing we should do is cast pFile to a WinRTFile*:

WinRTFile *p = (WinRTFile*)pFile; /* Populate this structure */

The next thing to do is populate the WinRTFile object with the function table it needs to really be an "interface." The function table is an sqlite3_io_methods structure, which is part of the sqlite3_file "base class" of WinRTFile; in fact it's sqlite3_file's only member. We populate the function table like so:

p->base.pMethods = new sqlite3_io_methods
{
    1,                            /* iVersion */
    WinRTClose,                    /* xClose */
    WinRTRead,                     /* xRead */
    WinRTWrite,                    /* xWrite */
    WinRTTruncate,                 /* xTruncate */
    WinRTSync,                     /* xSync */
    WinRTFileSize,                 /* xFileSize */
    WinRTLock,                     /* xLock */
    WinRTUnlock,                   /* xUnlock */
    WinRTCheckReservedLock,        /* xCheckReservedLock */
    WinRTFileControl,              /* xFileControl */
    WinRTSectorSize,               /* xSectorSize */
    WinRTDeviceCharacteristics     /* xDeviceCharacteristics */
};

Next is where things get a little awkward in WinRT. SQLite I/O is completely synchronous; WinRT I/O is completely asynchronous. There simply are no *legal* synchronous I/O calls we can make that would allow us the kind of access to the file system that we want and allow app store certification. (Again, CreateFile2 restricts us to ApplicationData.LocalFolder while the other CreateFilexxx functions are not allowed in WinRT. But we also can't use well behaved asynchronous programming here because SQLite is expecting a result as soon as the function returns.

Until someone makes a natively asynchronous SQLite, the only solution is to use task::get() or task::wait(). It's unfortunate, but we have to block the thread while the I/O calls are occurring. The upshot is that SQLite functions utilizing our VFS must be called from worker threads. This is a small price to pay, IMHO, since good libraries like SQLite.NET PCL already let you do this. But if you're interacting with SQLite directly in C++ or C#, don't forget this. The Runtime will be extremely angry at you if you try to do what we're about to do from the UI thread!

The other thing we have to do is get a StorageFile corresponding to the zName given to us by SQLite (well, actually, we will give it to SQLite3 via sqlite3_open or sqlite3_open_v2 - we will actually pass the full path of the database file to these functions).  But we have to be very careful here; StorageFile::GetFileFromPathAsync won't work because that will only let you open a specific file you've already been given access to. That's fine for the main DB, but SQLite also wants to create a temporary journal file in the same folder as the database for safe writing. So we have to get a StorageFolder first, and then get a StorageFile from the StorageFolder. And, again, everything needs to be in a location we've either declared in the manifest (e.g., Pictures) or gotten from a folder picker, recent access list, etc.

To do this properly we've made a helper function called GetStorageFileFromPath which takes a null-terminated ANSI C string as a path and returns a StorageFile object. This function is very useful outside of this example, so you might want to keep it in your back pocket. Just remember never to call it from the UI thread!

StorageFile^ GetStorageFileFromPath(const char* zPath)
{
    int pathLength = ::strlen(zPath);
    int i;

    for (i = pathLength; i >= 0; i--)
    {
        if (zPath[i-1] == '\\')
            break;
    }

    wchar_t* lpwstrPath = new wchar_t[i];
    int folderPathCount = ::MultiByteToWideChar(CP_ACP, MB_PRECOMPOSED, zPath, i, lpwstrPath, i);
    String^ strFolderPath = ref new String(lpwstrPath, folderPathCount);
    delete lpwstrPath;

    wchar_t* lpwstrFilename = new wchar_t[pathLength - i];
    int fileNameCount = ::MultiByteToWideChar(CP_ACP, MB_PRECOMPOSED, zPath + i, pathLength - i, lpwstrFilename, pathLength - i);
    String^ strFilePath = ref new String(lpwstrFilename, fileNameCount);
    delete lpwstrFilename;

    try
    {
        StorageFolder^ folder =
            create_task(
                StorageFolder::GetFolderFromPathAsync(strFolderPath)
            ).get();
        if (folder == nullptr)
            return nullptr;

        StorageFile^ file =
            create_task(
                folder->CreateFileAsync(
                    strFilePath,
                    CreationCollisionOption::OpenIfExists
            )).get();
        if (file == nullptr)
            return nullptr;

        return file;
    }
    catch (Platform::AccessDeniedException^)
    {
        return nullptr;
    }
}

Turning back to WinRTOpen, we can finally get the IRandomAccessStream:

    IRandomAccessStream^ stream = nullptr;    
    try
    {
        StorageFile^ file = ::GetStorageFileFromPath(zName);
        if (file == nullptr) return SQLITE_IOERR_ACCESS;
                            
        stream = create_task(
            file->OpenAsync(
                flags & SQLITE_OPEN_READONLY ? FileAccessMode::Read : FileAccessMode::ReadWrite
                )).get();
    }
    catch (AccessDeniedException^ ex)
    {
        return SQLITE_IOERR_ACCESS;
    }

This should be pretty self explanatory. Usually, SQLite database connections are opened as SQLITE_OPEN_READWRITE, but we can allow for the rare read-only case and potentially let other applications access the database while we are in it.

One thing I need to emphasize here though is NEVER allow more than one simultaneous connection to the same file. The normal SQLite WinRT implementation clearly contemplates shared access to files, making multiple open attempts, waiting for other threads to finish, locking sections of a file for writing, etc. - all things that make sense for a large shared database but not in the sandboxed WinRT environment. I strongly advise against such an approach, having had very nasty and untrackable errors (e.g., System.ExecutionEngineException!) emerge every time I've tried to allow something like that. If the file is inaccessible, let the WinRTOpen call fail gracefully and handle the error in your main code. If you really want to try again, delay the thread and try again from the main code, but not from the VFS.

The last thing to do is set the return flags (indicated by pFlags), and provide the new IRandomAccessStream to the WinRTFile object, and return:

    if (pOutFlags)
        *pOutFlags = flags;

    p->stream = stream;
    return SQLITE_OK;
}

Some Other VFS-Level Functions

Let's briefly look at some of the other VFS-level functions that need to be written, even if not fully implemented.

WinRTDelete

You'll want to write a real delete function, because that's how the temporary journal file gets removed for journaled writes. Here's ours:

/*
** Delete the file identified by argument zPath. If the dirSync parameter
** is non-zero, then ensure the file-system modification to delete the
** file has been synced to disk before returning.
*/
int WinRTDelete(sqlite3_vfs *pVfs, const char *zPath, int dirSync)
{
    try
    {
        StorageFile^ file = ::GetStorageFileFromPath(zPath);
        auto deleteFileTask = create_task(
            file->DeleteAsync()
            );
        deleteFileTask.wait();
        return SQLITE_OK;
    }
    catch (AccessDeniedException^ ex)
    {
        return SQLITE_IOERR_ACCESS;
    }
}

You might think we can get away with not waiting for deleteFileTask if dirSync is false.. But doing so, I found, created situations where SQLite tried to open a file while the delete operation on the same file was pending. Therefore, we always need to await the deleteFileTask and ignore dirSync.

WinRTAccess

SQLite will call the WinRTAccess function to determine if a file exists and what access rights the caller has. Since we're checking for access issues in our other functions, this just always says we have access. But a more robust implementation could attempt to obtain access to the file directly and respond accordinglly.

/*
** Query the file-system to see if the named file exists, is readable or
** is both readable and writable. Currently a no-op (always says yes); caller is responsible
** for making sure the file is available.
*/
int WinRTAccess(
    sqlite3_vfs *pVfs,
    const char *zPath,
    int flags,
    int *pResOut
    )
{
    *pResOut = 0;
    return SQLITE_OK;
}

WinRTFullPathname

This function is for translating the path provided to the sqlite3_open_xxx functions to file system paths. This is where,  if you were ambitious, you could implement parsing of WinRT URIs (e.g., ms-appx:///). Whatever you return to zPathOut is what will get passed to zPath in WinRTOpen, though, so there's really little need to implement this function as anything other than a straight copy, which is what we do.

/*
** Argument zPath points to a null-terminated string containing a file path.
** If zPath is an absolute path, then it is copied as is into the output
** buffer. Otherwise, if it is a relative path, then the equivalent full
** path is written to the output buffer.
**
** For WinRT, all paths need to be absolute; but this would be where we might
** parse URIs if we really wanted to.
*/
int WinRTFullPathname(
    sqlite3_vfs *pVfs,              /* VFS */
    const char *zPath,              /* Input path (possibly a relative path) */
    int nPathOut,                   /* Size of output buffer in bytes */
    char *zPathOut                  /* Pointer to output buffer */
    )
{
    ::memcpy(zPathOut, zPath, strlen(zPath) + 1);
    return SQLITE_OK;
}

WinRTSleep

Sometimes the SQLite core itself will try to put itself to sleep while waiting for I/O operations or for other reasons which elude anyone who's not extremely familiar with that code. So it's a good idea to implement a real sleep function here.

Naturally, WinRT won't let you use Sleep or SleepEx, so we need to use a task awaiter. For this we've borrowed the complete_after function example provided by Microsoft:

/*
** Sleep for at least nMicro microseconds. Return the (approximate) number
** of microseconds slept for.
*/
int WinRTSleep(sqlite3_vfs *pVfs, int nMicro)
{
    ::complete_after(nMicro / 1000).wait();
    return nMicro;
}

// Creates a task that completes after the specified delay.
task<void> complete_after(unsigned int timeout)
{
    // A task completion event that is set when a timer fires.
    task_completion_event<void> tce;

    // Create a non-repeating timer.
    auto fire_once = new timer<int>(timeout, 0, nullptr, false);
    // Create a call object that sets the completion event after the timer fires.
    auto callback = new call<int>([tce](int)
    {
        tce.set();
    });

    // Connect the timer to the callback and start the timer.
    fire_once->link_target(callback);
    fire_once->start();

    // Create a task that completes after the completion event is set.
    task<void> event_set(tce);

    // Create a continuation task that cleans up resources and 
    // and return that continuation task. 
    return event_set.then([callback, fire_once]()
    {
        delete callback;
        delete fire_once;
    });
}

Unimplemented Functions

The rest of the VFS-level functions in WinRTVFS don't currently do anything and are not necessary. The only interesting one is WinRTRandomness. It's not clear to me when and if this function is ever called by SQLite. But if you do find a use for it, it could be useful to use WinRT's built-in cryptographic randomness functions rather than the very poor rand() function from the C standard library.

Implementing File-Level Functions

The VFS-level functions obviously operate without an sqlite_file handle; the remainder of the functions we need to write operate on the sqlite_file created by our WinRTOpen function.

Reading and Writing

A file system wouldn't be very useful without reading and writing. The WinRTRead and WinRTWrite functions are pretty straightforward if you're familiar with disc access in WinRT C++. The trickiest part is translating between the C pointers SQLite uses and the IBuffer's WinRT uses. This requires some ugly COM interface casting that is more or less copied from MSDN examples. No sense in reinventing the wheel!

The only thing that is not obvious in the read function is that the returned buffer must be filled with zeroes if the operation does not result in a full read, and return the SQLITE_IOERR_SHORT_READ "error" (which really doesn't result in an error at all). Also, with WinRT file access you have the option of seeking and using the IRandomAccessStream, or obtaining IInputStream and IOutputStream interfaces already pointing to the desired positions. We use the latter here, but I think it's just a matter of preference.

/*
** Read data from a file.
*/
int WinRTRead(
    sqlite3_file *pFile,
    void *zBuf,
    int iAmt,
    sqlite_int64 iOfst
    )
{
    WinRTFile *p = (WinRTFile*)pFile;

    if (p->stream == nullptr)
        throw ref new Exception(
            E_HANDLE, 
            "WinRTVFS Exception: SQLite database file already closed"
            );

    IInputStream^ inputStream = p->stream->GetInputStreamAt(
        iOfst
        ); 
    Buffer^ readBuffer = ref new Buffer(iAmt);
    IBuffer^ finalBuffer = nullptr;
    
    try
    {
        auto readTask = create_task(
            inputStream->ReadAsync(
                readBuffer, 
                iAmt, 
                InputStreamOptions::ReadAhead)
            );
        // always use the returned buffer, not the original buffer!
        finalBuffer = readTask.get();        
    }
    catch (AccessDeniedException^ ex)
    {
        delete readBuffer;
        return SQLITE_IOERR_ACCESS;
    }

    ComPtr<IBufferByteAccess> bufferByteAccess;
    reinterpret_cast<IInspectable*>(finalBuffer)->QueryInterface(
        IID_PPV_ARGS(&bufferByteAccess)
        );
    BYTE* pData = nullptr;
    if (FAILED(
        bufferByteAccess->Buffer(&pData)
        )) 
        return SQLITE_IOERR;

    ::memcpy(zBuf, pData, finalBuffer->Length);

    delete readBuffer;

    if (finalBuffer->Length < iAmt)
    {
        // must zero out remainder of return buffer if short read
        ::memset(
            (BYTE*)zBuf + finalBuffer->Length, 
            0, 
            iAmt - finalBuffer->Length
            );
        return SQLITE_IOERR_SHORT_READ;
    }
    else
    {
        return SQLITE_OK;
    }
}

/*
** Write data to the file
*/
int WinRTWrite(
    sqlite3_file *pFile,
    const void *zBuf,
    int iAmt,
    sqlite_int64 iOfst
    )
{
    WinRTFile *p = (WinRTFile*)pFile;    

    if (p->stream == nullptr)
        throw ref new Exception(
            E_HANDLE, 
            "WinRTVFS Exception: SQLite database file already closed"
            );

    IOutputStream^ outputStream = p->stream->GetOutputStreamAt(
        iOfst
        );
    Buffer^ writeBuffer = ref new Buffer(iAmt);
    ComPtr<IBufferByteAccess> bufferByteAccess;
    reinterpret_cast<IInspectable*>(writeBuffer)->QueryInterface(
        IID_PPV_ARGS(&bufferByteAccess)
        );
    BYTE* pData = nullptr;
    if (FAILED(
        bufferByteAccess->Buffer(&pData)
        )) 
        return SQLITE_IOERR;

    ::memcpy(pData, zBuf, iAmt);
    writeBuffer->Length = iAmt;

    int result = SQLITE_OK;
    try
    {
        auto writeTask = create_task(
            outputStream->WriteAsync(writeBuffer)
            );
        writeTask.wait();
    }
    catch (AccessDeniedException^ ex)
    {
        result = SQLITE_IOERR_ACCESS;
    }

    delete outputStream;
    delete writeBuffer;

    return result;
}

Syncing, Closing, File Size, and Truncating

The WinRTSync function just needs to flush the IRandomAccessStream. Because we will want to make sure this is done on close also, we've written a helper function, WinRTFlush:

int WinRTFlush (WinRTFile *p)
{
    int retries = 0;
    bool success = false;
    if (p->stream == nullptr)
        throw ref new Exception(E_HANDLE, "WinRTVFS Exception : SQLite database file already closed");
    while (!success && retries++ < 10)
    {
        try
        {
            create_task(
                p->stream->FlushAsync()
                ).wait();
            success = true;
        }
        catch (Exception^ ex)
        {
            ::WinRTSleep(nullptr, 1000000);
        }
    }
    if (!success)
        return SQLITE_IOERR_ACCESS;

    return SQLITE_OK;
}

We actually do multiple tries here, only because it's conceivable something could be legitimately preventing us from flushing the stream and we don't want to lose changes. The WinRTSync function simply calls this helper.

The WinRTClose function likewise flushes the stream, deletes it, and cleans up anything else we allocated for the WinRTFile:

/*
** Close a file.
*/
int WinRTClose(sqlite3_file *pFile)
{
    WinRTFile *p = (WinRTFile*)pFile;
    int result = WinRTFlush (p);
    if (result != SQLITE_OK)
        return result;
    delete p->stream;
    delete p->base.pMethods;
    p->stream = nullptr;
    p->base.pMethods = nullptr;
    return SQLITE_OK;
}

We need to implement WinRTFileSize. This is easy:

/*
** Write the size of the file in bytes to *pSize.
*/
int WinRTFileSize(sqlite3_file *pFile, sqlite_int64 *pSize)
{
    WinRTFile *p = (WinRTFile*)pFile;
    if (p->stream == nullptr)
        throw ref new Exception(
        E_HANDLE,
        "WinRTVFS Exception: SQLite database file already closed"
        );
    *pSize = p->stream->Size;
    return SQLITE_OK;
}

WinRTTruncate is also an important function when databases entries are deleted. Without it, database sizes will never shrink on disc - not good.

/*
** Truncate a file. 
*/
int WinRTTruncate(sqlite3_file *pFile, sqlite_int64 size)
{
    WinRTFile *p = (WinRTFile*)pFile;
    if ( p->stream == nullptr )
        throw ref new Exception(
            E_HANDLE, 
            "WinRTVFS Exception: SQLite database file already closed"
            );
    p->stream->Size = size;
    return SQLITE_OK;
}

Unimplemented Functions

The rest of the file-level functions don't need to be implemented substantively - returning SQLITE_OK (or SQLITE_NOTFOUND in the case of WinRTFileControl) will do. Some, like WinRTLock, are simply impossible without some extremely sophisticated coding, and unnecessary in the WinRT file system anyway.

Using the VFS

That was a lot of work, but it's all smooth sailing from here, especially if you use the WinRTVFS Windows Runtime Extension in the example project. Just add a reference to it - along with a reference to SQLite for Windows Runtime - in your main project. 

At application startup, call SQLiteWinRTExtension::WinRTVFS::Initialize and specify whether you want WinRTVFS to be the default file system. That's it for initialization.

All that's left is to obtain a database file and open the connection. Just remember, the key to making this work is  having access to the StorageFolder where the database is located. So it's not enough merely to pick the database file from a file picker, unless you're ONLY going to read from the database.

In the sample C# app, the first thing we do is browse for a folder that should contain existing databases. That folder needs to be added to our Windows.Storage.AccessCache.StorageApplicationPermissions.FutureAccessList (it can always be removed later, but there's no harm in keeping it).

Once the folder is chosen, we populate a GridView with the list of paths and filenames. Next, the user picks a database to open, at which point another GridView is populated with the tables in the database. We're using the SQLite-WinRT library for this because it already wraps all SQLite calls in Tasks so that they're WinRT async friendly - thus we needn't worry about the fact that our VFS functions are blocking the thread. If you use something else like SQLite.NET PCL, you need to make sure to use the async functions as well.

Room For Improvement

It would be nice to remove the restriction that you need full access to the database's containing folder in order to write to it. A way to improve this would be to have the WinRTOpen function create journal files in the ApplicationData local storage folder regardless where the main database is located. This would really just require checking for the SQLITE_OPEN_MAINJOURNAL flag in the flags parameter of WinRTOpen, and creating / opening the file in the app's local storage folder instead of the path actually provided. 

Besides that, having control over the VFS allows for some interesting possibilities. For example, if you know that your databases are going to be small, you can cache them in memory the first time they're opened, such that the WinRTRead functions could just copy the bytes from memory - which would be much faster than using the disk constantly. If you wanted to implement an encryption system, you could use all of WinRT's Cryptographic APIs to do so as well. 

Finally, if you really hate C++, it would be eminently possible to define a ref interface that the consuming C# application could implement and provide to the WinRTVFS class on intiialization. You'd still need to define those barebones VFS functions, but all they'd do is call your interface members with the appropriate marshalling. For now, I'll leave that to others to implement. 

About the License Choice

Being a commercial developer, usually I frown upon GPL or LGPL because I don't want strings attached when I find good open sourced projects. But here, since SQLite is public domain, I think it's only fair that if you find ways of improviing upon this VFS, you share them with the rest of the SQLite community. So, LGPL v3 it is. The project is available on GitHub too.

History

This is version 1!