Shotwell Architecture Overview: Database

Shotwell uses SQLite to manage a database holding tables for the major data structures in the system, such as photos and events. Any major data structure that must persist should be stored in the database.

The general implementation strategy has been to provide thin wrapper classes that abstract out the mechanics of operating with the database. The reason for this is convenience and code reuse. The SQLite database could be ripped out and replaced with another in the future without the rest of the application being affected (although the need for this seems unlikely). It also means all the SQL statements are located in a single place, rather than spread throughout the code.

These thin wrapper layers do nothing more than construct database queries and return responses. There is no caching or upper-level object construction. (That is, it returns IDs rather than objects that use those IDs for their work, i.e. PhotoID versus Photo).

Note that Shotwell's design doesn't allow for more than one process to access the database. It's not that SQLite doesn't support concurrency — it does — but that if one Shotwell process alters the database the other is not notified. Shotwell caches database rows in memory for performance reasons. Therefore, if the data in the database changes out from underneath it, the cached values would be inaccurate, and when they were written, would obliterate the prior changes.

1. DatabaseTable

All database wrapper classes inherit from DatabaseTable. DatabaseTable holds a protected static class member that is the SQLite database connection object. Today there is no reason (performance or otherwise) to maintain multiple connections to the database. All subclasses use this object when issuing queries.

DatabaseTable also offers several helper functions for reporting errors and executing queries of common patterns (i.e. SELECT FROM Table WHERE id=?).

Finally, DatabaseTable has a const int defined which represents the version number of the database schema. If any table's schema is modified between releases, this value should be incremented. This allows an easy way to upgrade user's databases when schemas change.

1.1. Children of DatabaseTable

When Shotwell was first being developed, we faced a question about how the table classes would be designed. Since there's only one table in a database, there were three class design choices:

• Classes with all static methods (or a namespace for each table)
• A singleton class
• Multiple instances (which, because they hold no state, are cheap to create and destroy)

Because there was some anticipation of using threads for background work, I went with option three, as that allows for precompiled statements without worrying about synchronization issues.

This is no longer considered a win. The code currently uses option two, a singleton class.

2. PhotoID, ImportID, EventID, et al.

In SQLite (and most other databases), each row in a table has a primary key. SQLite's primary keys are 64-bit ints. The database classes work with these IDs.

However, since Vala does not support a typedef keyword, Shotwell defines structs which hold the 64-bit value for type safety. Because of problems with inheritance and structs in Vala, we can't define a base struct with various consts and helper functions (such as DatabaseID), and then define base structs for each table (PhotoID, EventID, etc.). Again, this would be for type safety and code readability.

Since these two things are desirable, each table defines a complementary ID class which provides an INVALID const and is_valid () and is invalid, and miscellaneous user data maintained as a string. user_data is currently unused.

3. PhotoTable

PhotoTable maintains information on all photos imported into the library. Most of its fields are self-explanatory. A couple of notes:

• ImportID does not refer to a primary key of another table. Each round of imports is assigned a unique value for all photos in that import batch. The ImportID is, in fact, the time taken at the start of the import. Previously this was a conveniently unique number; now it is guaranteed, and so the date and ordering of all the import rolls can be relied upon by examining their IDs.

• All transformations other than orientation (i.e. crop, color adjustment, etc.) are stored as a text KeyFile in the transformations column. This allows for new transformations to be easily added without modifying the database schema.

• md5 is a full MD5 hash of the entire photo file. thumbnailmd5 is only of its embedded preview and exifmd5 is only of the EXIF data (not including the preview, which is commonly attached to the tail of the EXIF block).

• backlinks are persistent links to container objects, i.e. Events and Tags. These are used when a Photo has been removed from one (for example, it’s been trashed or marked offline). If the Photo is returned to the main heap of photos (i.e. untrashed), these backlinks are used to restore its connections to the containers.

Because a low application start time is seen as valuable, optimization testing showed the quickest way to load the photos was to scoop up the entire PhotoTable in one transaction and then store the entire row in memory (one row per TransformablePhoto object). This is the most significant example of database caching in Shotwell.

4. EventTable

In Shotwell, an event is a grouping of photos based on the time of their exposure. A primary design note of events is that a photo can only belong to one event (or none at all). Thus, rather than each row in an EventTable maintaining it's list of photos, each photo in PhotoTable maintains an event_id. Thus, EventTable is a pretty simple table, maintaining only the event's name and it's primary photo (which is displayed as a thumbnail representing the entire event).

5. TagTable

Photos may be tagged with a descriptive name and presented in groups. The TagTable holds a list of each tag and a serialized list of PhotoIDs that are tagged. Note this is different than Events, where each row in the PhotoTable holds an EventID; this is because of the different requirements for events and tags, where a photo can only be associated with a single event, but may be associated with many tags.

6. A Special Note about Hierarchical Tags

Since version 0.11, Shotwell has supported nesting tags within other tags. These tags are called “Hierarchical Tags” and are sometimes referred to in comments in the Shotwell source code as “HTags.” For example, you can nest a child tag named “Beach” inside of two different parent tags, one named “Florida” and the other named “Spain”, thereby categorizing your different beach photos by location.

In the Shotwell database’s TagTable, hierarchical tags are distinguished from traditional flat tags by the fact that their name begins with a forward-slash character. In the Shotwell database, every fully-qualified tag path has an entry. In the example above, the tags “Florida” > “Beach” and “Spain” >; “Beach” would create 4 entries in the Shotwell TagTable: “/Florida”, “/Florida/Beach”, “/Spain” and “/Spain/Beach”. Because of this, the entire Shotwell tag hierarchy can be encoded in the names of tags alone. This is to say, in Shotwell, in-memory parent Tag objects don’t have pointers to their child Tag objects or vice-versa. Instead, each tag simply knows its name, and by convention Shotwell knows that the Tag object with the name “/Florida” must be the parent of the Tag object with the name “/Florida/Beach”.

In many cases, it’s important to see the Tag objects in the tag tree in a particular order. For example, you might want to traverse the tag tree in a breadth-first manner so that you’re guaranteed to see every parent tag before its child tag. To accomplish this, just collate the tag names into an array and sort that array lexicographically. Since lexicographic ordering dictates that “nothing comes before something,” you’re guaranteed to see “/Florida” before “/Florida/Beach”.

In summary, be aware that the names of tags in the database’s TagTable encode all of the relationships of the parent-child tag hierarchy, so you should be very circumspect about modifying the TagTable in any way. If you do find that you need to work with tag names in the database to modify the structure of the tag hierarchy, you should never do so by manipulating tag name strings directly. A variety of tag name manipulation utility functions are available in the HierarchicalTagUtilities class of the tags unit.

7. BackingPhotoTable

The BackingPhotoTable is designed to hold reference information to any number of alternative backing photos for a Photo object. Currently the only use is for the editable photo, which is generated when the user asks to edit a photo with an external program. (Shotwell's non-destructive edits are flattened to this editable backing file.)

No transformations are held in the BackingPhotoTable. All transformations are held in the PhotoTable.

8. VideoTable

The VideoTable is analogous to the PhotoTable. See PhotoTable for information on most of them. One column is different:

• is_interpretable is set to true when Shotwell has detected that GStreamer (the multimedia streaming library Shotwell uses to generate video thumbnails) supports the video's format. In general, this means Shotwell was able to successfully generate a thumbnail for the video.

9. TombstoneTable

The TombstoneTable is used in conjunction with library monitoring. If the user removes a photo from the library but does not delete the file, it's undesirable for Shotwell to notice the file at next startup and re-import it. A tombstone is literally that, a marker than the file is “dead”.