Reading an In-Memory SQLite Database

I’m currently working on a utility to improve upon the basic Error Lookup utility found in Microsoft Visual Studio. I am trying to improve it by collating all the error codes and messages found in the Microsoft Platform SDK into a database, which can be accessed and queried in a similar way to the existing utility, but with a extra bells and whistles (think free-text search, partial code matching, etc.). To make distribution of the utility easier, I decided to try embedding my error database into the executable file, which was straightforward. Using the database however required a bit more work, which I am presenting here in the hope that others will find it useful.

Embedding a SQLite Database in a Windows EXE or DLL

The data I am working with is in a SQLite database, which is a database consisting of a single file. To embed this file in my Visual Studio project I did the following.

  1. Add a resource file to your project (only if you don’t have one already).
  2. Right-click on the resource file and choose ‘Add Resource’.
  3. Select ‘Import…’ and choose your database file.
  4. Enter a resource type of ’10’.

Using an Embedded SQLite Database

Now you have your data embedded in your application, first you need to obtain a pointer to the data bytes and next you need to get SQLite to read the in-memory data. Although this looks simple, it took a few nights to understand how the SQLite storage model worked in order to get it working in a truly read-only manner.

This is how I do it in my code:

void open_mem_db()
{
    char errcode = 0;

    void* pBuffer = NULL;
    HRSRC hr = ::FindResource(NULL, MAKEINTRESOURCE(IDR_ERRORCODEDB), RT_RCDATA);

    if (hr)
    {
        HGLOBAL hg = ::LoadResource(NULL, hr);

        if (hg)
        {
            DWORD dwSize = ::SizeofResource(NULL, hr);
            pBuffer = ::LockResource(hg);

            set_mem_db(pBuffer, dwSize);

            int nInitResult = readonlymemvfs_init();
            assert(nInitResult == SQLITE_OK);
            errcode = sqlite3_open_v2( "_", &s_database,
                SQLITE_OPEN_READONLY, READONLY_MEM_VFS_NAME );
        }
    }
}

Remember to change IDR_ERRORCODEDB to your resource identifier. This makes use of some extra code to allow SQLite to read data straight from memory, which can be downloaded from http://www.tolon.co.uk/download/readonlymemvfs-0.1.zip or from github. Use of the code is entirely at your own risk and no warranty or guarantee is provided.

I am planning to develop the code further to tidy it up and support encryption of the embedded SQLite database, so if you have any comments or any improvements to the code I will gladly accept them and try to incorporate them into the code.

4 thoughts on “Reading an In-Memory SQLite Database”

  1. Hi Tigzy!

    I’m glad you found this helpful. May I ask what you are using it for?

    I haven’t had the time to add encryption (yet), but I was only thinking of using a simple symmetric-key block cipher, just to make stealing my data inconvenient rather than impossible.

    Despite the fact I’ve not done it yet, this should be quite easy to achieve. The data you add to the resource should already be encrypted and the spmemfileRead function should decrypt the blocks requested, keeping in mind the alignment between the requested data offset and the block size of the chosen cipher. Easy! 🙂

  2. @tolon
    Actually I want to use it for one of my apps (RogueKiller). I don’t want my database to be available (at least easily) so I don’t want to drop it anywhere on the drive and it’s better to read it directly from resources (and it’s quicker).

    Yeah the DB should be encrypted before added into resources, and then decrypted at runtime (by little blocks). Thanks for the tip, indeed it looks easy to achieve. I’ll try to modify it.

  3. That’s done, and quite easy indeed 🙂

    //—-

    int spmemfileRead( sqlite3_file * file, void * buffer, int len, sqlite3_int64 offset )
    {
    spmemfile_t * memfile = (spmemfile_t*)file;

    if (memfile->buffer == s_pData)
    {
    if( ( offset + len ) > s_nDataLen ) {
    return SQLITE_IOERR_SHORT_READ;
    }

    memcpy( buffer, s_pData + offset, len );

    //— Decrypt buffer
    if (useEncryption)
    {
    int cypherIndex = offset % encSize;
    EncryptBufferWithKey((BYTE*)buffer, (BYTE*)buffer, (size_t)len, (BYTE*)encBuff, (size_t)encSize, cypherIndex);
    }

    return SQLITE_OK;
    }
    else
    {
    return SQLITE_IOERR_SHORT_READ;
    }
    }

Leave a Reply