Reading an In-Memory SQLite Database

8 Comments

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.

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

  2. 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! 🙂

  3. @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.

  4. 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;
    }
    }

  5. Hi,

    Does this work if you have a existing buffer with db content and call on set_mem_db to point to this buffer? I tried it and it doesn’t seem to work?

    Thanks for your help in advance!

  6. set_mem_db(pBuffer, dwSize);
    Is not thread-safe. Why use a static buffer? what happens if two threads try to set enter set_mem_db at the same time with different buffer/size?

  7. That is true, but I’d say the biggest issue here is that the static buffer prevents the use of multiple in-memory databases even if the application code were to synchronise access to the library functions. The design was sufficient for my needs at the time, i.e. a process using a single in-memory database from a single thread, but if your needs are more complex please feel free to modify the code accordingly.

    If you come up with some good improvements, I’d be happy to review a pull request. 🙂

  8. Thanks for your reply.

    I tried to modify the code to provide an extra 2 arguments for buffer and size in spmemvfsOpen function but i couldn’t get it to compile.
    The VFS is poorly documented. I guess there is no need to lock as long as you are providing different buffer per database handle.
    My need is for multiple database handling simultaneously. So i would really appreciate some help on this matter. Other than that, your example works pretty fine.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.