Database Schema

Vienna 2.x/3.x Database Format

This document explains the Vienna database and how it is managed by the application. The information here can be useful if you wish to read the database contents externally.

The policy for support for third party applications that work directly against the Vienna 2.x/3.x database is that read-only access is fully supported while read-write access is acceptable as long as you really know what you are doing.

Schema Overview

Vienna uses SQLite 3 as the database engine. The actual version of the database engine can vary with each release.

The database uses several tables. The following sections document the tables and the semantics of each field. The schema version number in which each field first appeared is also documented.

Info Table

The Info table contains the database version information. When Vienna starts, it looks for this table and if it is absent, it tries to create all the other tables.

The version field is used to determine if Vienna needs to perform an upgrade on the database. If the version is equal to or above the minimum schema supported but less than the current schema, Vienna will prompt the user whether to upgrade the database. If the user affirms the upgrade then it will automatically perform the necessary adjustments while preserving the existing data and then bump up the version field to the current schema version.

Name	Version	Description
version	12	The schema version number of this database. When the database schema is changed, this number is incremented. For Vienna 2.0, the schema version number is 12 and the minimum supported schema version is also 12.
last_opened	12	The date when the database was last opened for writing. This is only used for informational purposes so the format is not specified or fixed.
first_folder	14	The ID of the folder at the root of the folder list when manual sorting is applied.
folder_sort	15	Specifies how the folders are sorted. There are two possible values: MA_FolderSort_Manual (0) MA_FolderSort_ByName (1) Prior to rev 14, this information was stored in the Vienna preferences file.

Folders Table

The Folders table lists all folders in the database. Each folder may be one of four types: a smart folder, an RSS feed, a group folder or the one and only Trash folder. The Folders table stores the common elements for each of these tables and can be thought as the 'base' table. Smart folders and RSS folders use separate tables to store additional information.

Name	Version	Description
folder_id	12	The ID of this folder. This is an auto-increment field.
parent_id	12	The ID of the folder that contains this folder. Root level folders have no parent and thus this value is -1.
foldername	12	The name of this folder. The folder name should be unique across ALL folders. Vienna automatically handles duplicates by appending a numeric specifier to the second and subsequent occurrence of the duplicate name. Applications that write directly to the folders table need to do the same. Strange things can happen if there’s more than one folder with the same name.
unread_count	12	The count of unread messages in this folder. This field is computed by Vienna based on the unread count of each message. For performance reasons we cache this count here but it is worth noting that there can be circumstances where the unread count on the folder can get out of sync with the actual number of unread messages. When this happens, Vienna will automatically fix up the unread count on the folder.
last_update	12	The date when the most recent article was added to the folder. This is generally only relevant for feed folders. Smart folders and group folders do not use this field.
type	12	The type of the folder. This is the field that determines what the folder contains. Permissible values are: 2 =Smart folder. The folder contents are computed dynamically. The smart folder criteria is stored in the separate smart_folders table. 3= Group folder. This folder is merely a container for other folders. 4= Feed folder. This folder contains RSS/Atom subscriptions. The actual feed details are stored in the separate rss_folders table. 7= Feed folder fetched via Google Reader. This folder contains RSS/Atom subscriptions. The actual feed details are stored in the separate rss_folders table. 5=Trash folder. This is a virtual folder that acts as a pseudo-smart folder which displays all articles that have the deleted_flag field set to 1.
flags	12	This is a generic flag field used by Vienna to store extra information associated with the folder. Currently only the following values are recognised: 1 = This folder needs an image. On the next refresh, Vienna will attempt to retrieve the folder image. 2 = This folder needs credentials. On the next refresh, Vienna will prompt for credentials before it accesses the feed. (Note that the flags above are only valid if the folder is a feed folder. Arguably the settings should be in the rss_folders table but they’re here for now).
next_sibling	14	The ID of the next folder at this level in the folder tree when manual sorting is applied.
first_child	14	The ID of the first child folder within this folder.

Note: The folder_id is referenced by messages in the Messages table. If you delete a folder, you should do something with the messages that reference it. Vienna will typically just delete all messages. Failure to delete messages will leave orphan messages in the database which can be cleaned up later by a separate process.

Messages Table

The Messages table is where all articles are stored.

Smart_folders Table

The Smart_folders table stores the criteria for every smart folder.

Rss_folders Table

The Rss_folders table extends the folders table with additional information that describes an RSS/Atom feed.

Rss_guids Table

The Rss_guids table maintains a list of all message GUIDs for messages retrieved in each folder. Unlike the messages table, entries are not deleted from this table when messages are deleted. Thus Vienna is able to determine whether an article was previously retrieved.

Messages_folder_idx Index

Vienna creates an index on the Messages table using the folder_id column.

Messages_message_idx Index

Vienna creates an index on the Messages table using the message_id column.

Rss_guids_idx Index

Vienna creates an index on the Rss_Guids table using the folder_id column.