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;
int fd;
char *aBuffer;
int nBuffer;
sqlite3_int64 iBufferOfst;
};
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;
StorageFile^ storageFile;
} 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;
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):
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);
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,
sizeof(WinRTFile),
MAXPATHNAME,
0,
"WinRTVFS",
(void*)0,
WinRTOpen,
WinRTDelete,
WinRTAccess,
WinRTFullPathname,
WinRTDlOpen,
WinRTDlError,
WinRTDlSym,
WinRTDlClose,
WinRTRandomness,
WinRTSleep,
WinRTCurrentTime,
};
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,
const char *zName,
sqlite3_file *pFile,
int flags,
int *pOutFlags
)
{
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;
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,
WinRTClose,
WinRTRead,
WinRTWrite,
WinRTTruncate,
WinRTSync,
WinRTFileSize,
WinRTLock,
WinRTUnlock,
WinRTCheckReservedLock,
WinRTFileControl,
WinRTSectorSize,
WinRTDeviceCharacteristics
};
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:
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.
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.
int WinRTFullPathname(
sqlite3_vfs *pVfs,
const char *zPath,
int nPathOut,
char *zPathOut
)
{
::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:
int WinRTSleep(sqlite3_vfs *pVfs, int nMicro)
{
::complete_after(nMicro / 1000).wait();
return nMicro;
}
task<void> complete_after(unsigned int timeout)
{
task_completion_event<void> tce;
auto fire_once = new timer<int>(timeout, 0, nullptr, false);
auto callback = new call<int>([tce](int)
{
tce.set();
});
fire_once->link_target(callback);
fire_once->start();
task<void> event_set(tce);
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.
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)
);
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)
{
::memset(
(BYTE*)zBuf + finalBuffer->Length,
0,
iAmt - finalBuffer->Length
);
return SQLITE_IOERR_SHORT_READ;
}
else
{
return SQLITE_OK;
}
}
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
:
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:
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.
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!