Writing a Calibre frontend for Windows8/WinRT using ‘SQLite for WinRT’

While developing a Windows Store frontend application for the Calibre ebookmanager, I’m hitting several bumps along the road. In this post I’ll explain some of the bumps and how I’ve tried to tackle them.

Basically they can be summarized as follows:

• How to access a Sqlite database in a WinRT application
• Circumvent file access limitation of SQLIte for WinRT
• Load cover files of each book
• Create incremental-loading Gridview using ISupportIncrementalLoading

Calibre is a great open source and free to use ebookmanager. It allows me to manage my ever-growing ebook-library and it supports lots of ebook-filetypes, including the ability to convert between the types.

My goal is to write a simple Windows Store application that acts as a frontend for the Calibre database. It will show my library in a visual appealing manner (aka TDPFAM, “The-design-principle-formerly-known-as-Metro”) and allow the user to rapidly query his database from anywhere in windows. At least that’s the idea. We’ll see where we end up (check here for a little video demonstrating the application I’m building).

Future follow ups on this project might be found here on the Calibre developer forum.

Early alpha of Calibre frontend, simply demonstration what can be done (check out the youtube movie). Note the layout that is based on one of the existing VS2012 WinRT project setup.

The original Calibre program

With this post I hope others get triggered to create their own Calibre frontend, because, knowing myself, I’ll get bored of the project pretty soon once I have to tackle the UI/UX stuff …which I don’t like.

Getting SQLite for Windows Runtime

The Calibre program stores all its information inside the “metadata.db” file, located in the rootfolder of the books library. his file is a simple SQLite-database, so we’ll need to get the “SQLIte for Windows RT” extension. I’ll discuss the database and folder layout later on when we’ll actually need to access it.

This post clearly explains how to install the “SQLIte for Windows Runtime” extensions that is needed to be able to access a Sqlite database.

Summarized, these are the steps to perform:

• Go to Tools => Extensions and Updates
• Search for Sqlite (online ofcourse!)
• Select and download “Sqlite for Windows Runtime”
• Add reference to both “MS Visual C++ Runtime Package” and “SqlLite for Windows Runtime”
• Change the platform target to x86, arm or x64 (Project => Project Properties)

Sqlite-net

Sqlite-net allows a more programmer-friendly way of accessing a Sqlite database, including the ability to query your db using linq. It can be found on github here. However, it is also available through NuGet. If you install sqlite-net throught NuGet, two additional files (SQLite.cs and SQLiteAsync.cs) will be added to your project.

Get read-rights in calibre books database folder and load metadata.db

File access for WinRT applications is pretty tight. Basically your application only has full access to the app’s local folder. If you need access to other locations, you will need permission from the user. Because we need access to the Calibre  database folder and all subfolders, which can be situated anywhere on the computer and/or network, we will use the FolderPicker and immediately store the chosen folder to the StorageApplicationPermissions.FutureAccessList. This allows our application to access that folder in the future without needing new permission from the user.

var folderPicker = new FolderPicker();
folderPicker.SuggestedStartLocation = PickerLocationId.Desktop;
folderPicker.FileTypeFilter.Add(".db");

StorageFolder folder = await folderPicker.PickSingleFolderAsync();
if (folder != null)
{
    // Application now has read/write access to all contents in the picked folder (including other sub-folder contents)
    StorageApplicationPermissions.FutureAccessList.Clear();
    StorageApplicationPermissions.FutureAccessList.AddOrReplace("dbfolder", folder);

    var file = await folder.GetFileAsync("metadata.db");
    if (file != null)
        await file.CopyAsync(
            Windows.Storage.ApplicationData.Current.LocalFolder,
            "metadata.db",
            NameCollisionOption.ReplaceExisting);
}

For the purpose of this demo, I clear the FutureAccessList (line 09). However, if you want your app to reuse the db-folder later on, it should be clear that clearing this list isn’t really helping to improve the usability of your app.

Calibre allows quick switching between 2 or more libraries (for example, I have one “literature” lib and one “technical” lib). The FutureAccessList of course can harbor all these library-locations, as long as the user has added the folders using the folderpicker.

Update (7/12/12) (thanks Philip Colmer for reminding me of this)
Also take note of line 14 where I copy the metadata.db file to the local app folder. This needs to be done since the current version of SQLite for WinRT can only work in a few locations. (the reason, stated here being that “SQLite uses the CreateFile2 API which is not a WinRT broker API. This means that it is restricted to certain areas of the AppContainer.”). What this means is that my application will have additional code to frequently check of the original metadata.db has changed, and if so copy this new version to the local app folder.
As a bonus, this will also circumvent any file access exceptions that might occur if both Calibre itself and my application try to access the metadata.db file.

Load book information from database

A Calibre library has a perfect straightforward layout. Basically there’s a root folder in which the metadata.db database is situated. This sqlite db contains all the books (meta)data, including the path where the actual book is situated on the file system. This path is always a subfolder of the folder where metadata.db file itself is located. Calibre creates a folder per author and in each author folder there’s one or more title-folders containing one book (but possibly several formats like pdf, epub, mobi, etc.)). Eg:

• [MyLib]
  • “metadata.db”
  • [Steve Jobs]
    • [His first book]
      • “hisfirstbook.pdf”
      • “histfirstbook.mobi”
      • “cover.jpg”
    • [Another book]
      • HowICreatedAnApple.epub
      • “cover.jpg”
  • [Bill Gates]
    • ….

The metadata.db itself is pretty straightforward. Since I couldn’t find any documentation on the database (I didn’t search very hard though) I used SQLiteSpy to discover the database layout. Everything starts from the books table.

Using SQLiteSpy to discover calibre database layout

So once we know the database layout and folder organization, it issimply a matter of using SQLite for WinRT (and sqlite-net) to dump the metadata.db file to the application as shown here:

var resultlist = new List();
var fileex = await ApplicationData.Current.LocalFolder.GetFileAsync(filename);
var folder = await StorageApplicationPermissions.FutureAccessList.GetFolderAsync("dbfolder");
if (fileex != null)
{
    using (var db = new SQLite.SQLiteConnection(
        Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, filename),
        SQLite.SQLiteOpenFlags.ReadOnly))
        {
        var allbooks = db.Table();

Load cover images

The nice thing of the FutureAccessList is that we not only can access the folder itself, but also all folders underneath it. Since each cover image of a book (if there is one) is located in the same folder as the bookfiles, we can simply access them without any extra hassle.

The path of a cover image is simply the path of the bookfile itself (which can be found in the books-table) and is always called “cover.jpg”. The books-table in the metadata.db also has a “has_cover” field,  which we can use to make our code less error-prone by preventing to open non-existing cover files.

Following code briefly shows how we could create a new Image containing the cover-image of the book.

var folder =
    await StorageApplicationPermissions.FutureAccessList.GetFolderAsync("dbfolder");
StorageFile file =
    await StorageFile.GetFileFromPathAsync(
    Path.Combine(folder.Path, book.path.Replace('/', '\\'),
    "cover.jpg"));
IRandomAccessStream stream = await file.OpenAsync(FileAccessMode.Read);
BitmapImage img = new BitmapImage();
img.SetSource(stream);
Image cover = new Image() { Source = img };

Incremental loading (experimental)

Just for fun and experimenting I’ve taken a look to how the new WinRT GridView is supporting incremental loading. Since most Calibre libraries contains several hundreds or thousands of books, it might be overkill (and a big power drain) if we’d tried to load in the book information, especially the coverfiles, all at once. If the itemsource of a GridView implements ISupportIncrementalLoading, the GridView will be able to load in new data when it’s needed.

Solid examples and additional information on how to implement this interface weren’t readily found (check out this one and this one to get started). I must admit, the implementation I now have has several glitches and I’m pretty sure that I’m not doing this right…however, it works ..and that’s what counts for now I guess.Shown here are the most important pieces of the class.

public class DalBooksIncrementSource :
    ObservableCollection, ISupportIncrementalLoading
{
    public DalBooksIncrementSource()
    {
        CalibreDal.LoadSource();
    }

public bool HasMoreItems
{
    get
    {
        return (this.Count < CalibreDal.BookSource.Count());
    }
}

    private int AlreadyLoaded = 0;
    public Windows.Foundation.IAsyncOperation LoadMoreItemsAsync(
        uint count)
    {


        CoreDispatcher dispatcher = Window.Current.Dispatcher;
        return Task.Run(
            () =>
            {

                List books = new List();
                for (int i = (int)AlreadyLoaded;
                    i < AlreadyLoaded + count && i 
                    {
                        foreach (var item in books)
                        {
                            this.Add(item);
                        }
                    });
                AlreadyLoaded += (int)count;
                return new LoadMoreItemsResult()
                    {
                        Count = (uint)books.Count
                    };

            }).AsAsyncOperation();

    }
}

Conclusion

Ok, that’s it for now. What this post mainly was , was one big dump of pieces of code I’ll be needing once I get to writing a full Calibre Windows Store Application. In the mean time I’m going to keep experimenting with this stuff.

For example, I’m now going to have a look how the whole search charm stuff works, allowing users to query their Calibre database(s) from anywhere and immediately open up the corresponding book.