-
Notifications
You must be signed in to change notification settings - Fork 227
Database Schema
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.
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.
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 3.1.0, the schema version number is 18 and the minimum supported schema version is also 18. |
| 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. |
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. 6= Search Results folder |
| flags | 12 | This is a generic flag field used by Vienna to store extra information associated with the folder. Currently only the following bitwise values are persistent (stored in database): 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. 8 = This folder is not subscribed to (it is not fetched by the 'Refresh All Subscriptions' command). 32 = Articles for this folder are displayed using their web pages (Note that the first three flags above are only relevant 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.
The Messages table is where all articles are stored.
| Name | Version | Description |
|---|---|---|
| message_id | 12 | The ID of the message. This is actually a GUID string that references a specific article and should be considered unique. |
| folder_id | 12 | The ID of this folder where this message is stored. |
| parent_id | 12 | The ID of the message to which this is a comment. If this message starts a new thread then the value of this field is -1. (Note: Vienna 2.0 doesn't use this field). |
| read_flag | 12 | A Boolean that is YES if the message is read, NO if unread. |
| marked_flag | 12 | A Boolean that is YES if the message is marked (flagged), NO otherwise. |
| deleted_flag | 12 | A Boolean that is YES if the message is deleted, NO otherwise. All messages in the Trash folder are simply all those messages in this table which have the deleted_flag set to YES. |
| title | 12 | The message title. This is the first line of a comment for services where comments have no explicit subject line. |
| sender | 12 | The name of the person who posted this message. |
| link | 12 | The link associated with this article. Typically the URL of the original article on the web. |
| createddate | 13 | The date when the article was first published. Formerly (until Vienna 3.9.2), the system date when the article was first added in the database. Expressed as the number of seconds offset since 1st January 1970, 00:00 GMT. |
| date | 12 | The date when the article was last updated by the publisher. Formerly (until Vienna 3.9.2), the most recent date attached to the message (either publication date or update date). Expressed as the number of seconds offset since 1st January 1970, 00:00 GMT. |
| text | 12 | The message text. This is pretty much a blob of text that may or may not have HTML within it. |
| revised_flag | 16 | A Boolean that is YES if the message was revised, NO otherwise. Revised articles are those that changed in the feed after they were first retrieved. |
| enclosuredownloaded_flag | 17 | Not used, always set to 0. |
| hasenclosure_flag | 17 | A Boolean that is YES if the message has an enclosure associated with it, NO otherwise. |
| enclosure | 17 | A string that contains the URL of any enclosure associated with the message, otherwise this is empty. |
The Smart_folders table stores the criteria for every smart folder.
| Name | Version | Description |
|---|---|---|
| folder_id | 12 | The ID of the folder to which this smart folder criteria is associated. The folder must have type=2. |
| search_string | 12 | This is the XML text that defines the smart folder criteria. The format is documented below. |
A smart folder criteria is stored as an XML string that adheres to the following schema:
<criteriagroup condition=[all|any]>
<criteria field="fieldname">
<operator>[operatorvalue]</operator>
<value>[criteriavalue]</value>
</criteria>
</criteriagroup>
There may be multiple <criteria> blocks within a criteriagroup. If the condition is all , every criteria block has to match otherwise at least one criteria block has to match for the criteria to succeed.
The [operatorvalue] is an integer that represents the operator applied to the field and value. It must be one of the following:
Value to Operation map
- Is
- Is Not
- Is Less Than
- Is Greater Than
- Is Less Than or Equal
- Is Greater Than or Equal
- Contains
- Not Contains
- Is Before
- Is After
- Is On or Before
- Is On or After
Note that 'Is Before' and 'Is Less Than' are semantically equivalent but are given different values for clarity. Also 'Is' and 'Is Equal' are synonymous as are 'Is Not' and 'Is Not Equal'.
If the field is a date then the value field is a date represented as the number of seconds since 1st January 1970, 00:00 GMT. This representation simplifies portability and arithmetic. This is also the representation of dates of articles in the message table.
Vienna automatically converts each criteriagroup to the corresponding SQL statements when it queries for all articles in a smart folder.
The Rss_folders table extends the folders table with additional information that describes an RSS/Atom feed.
| Name | Version | Description |
|---|---|---|
| folder_id | 12 | The ID of the folder to which this smart folder criteria is associated. The folder must have type=4 or type=7. |
| feed_url | 12 | The URL of the RSS/Atom feed itself. |
| username | 12 | If this feed requires authentication then this stores the user name portion of the credentials. The password is NOT stored in this table but instead is obtained from the OS keychain using the feed URL and username as references. |
| last_update_string | 12 | This is the date of the last update extracted directly from the "Last-Modified" field of HTTP header of the feed data. It is stored as a string as the host server expects the "If-Modified-Since" date string to be in the same format as its "Last-Modified" string. |
| description | 12 | The feed's description. Vienna doesn't presently display this but it is here for future reference. |
| home_page | 12 | The URL of the feed's home page. This is distinct from the URL of the feed itself. |
| bloglines_id | 12 | Not used. Always set to 0. |
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.
| Name | Version | Description |
|---|---|---|
| folder_id | 18 | The ID of the folder. Refer to the folders table for the details. |
| message_id | 18 | The GUID, as a string, of a message in the folder. |
Vienna creates an index on the Messages table using the folder_id column.
Vienna creates an index on the Messages table using the message_id column.
Vienna creates an index on the Rss_Guids table using the folder_id column.
Vienna uses a singleton Database object to manage access to the database. The following code obtains a reference to the database object:
Database * db = [Database sharedManager];Database operations are performed on an FMDatabaseQueue which is accessed through:
FMDatabaseQueue *queue = [[Database sharedManager] databaseQueue]For information on the FMDatabaseQueue, check the FMDatabaseQueue Class Reference
The database management code is in both database.h and database.m in the Vienna source code.
The process of obtaining the reference may cause the database object to be initialised and a message panel to be displayed if any errors are encountered. Thus Vienna intentionally obtains a reference when it initialises the UI as so to control the point at which the database initialisation itself occurs.
The process of initialising the database object is as follows:
- Vienna obtains the name and path of the database file from the preferences via the DefaultDatabase setting. This setting can be used to locate the database in a folder other than the default.
- Vienna checks that the database folder itself is present and creates it otherwise. If it cannot create the folder, it reports an error and sets the database reference to NIL.
- Vienna calls
FMDatabaseQueueto open the database. If this fails, it reports an error and exits. - It then queries the FMDatabase for the userVersion number (which itself is stored inside an SQLite
PRAMGA user_version. (currently Vienna also queries the Info table for the legacy version field and compares the two values). If this returns no results then it assumes the database is empty. - It then checks whether the version is equal to or greater than the minimum supported version. If it is less, it reports via an alert panel that it does not recognise the database format.
- Otherwise if the version is less than the current supported version, it prompts the user whether they want to upgrade the database or create a new database in the current format.
- Whether the user elects to upgrade or create a new database, it saves a copy of the old database in the same folder as the current one but with the ".bak" extension appended to the database file name.
- If the user elects to create a new database, it simply deletes the current one after the backup.
- If creating a new database, it tries to create the Info table. If this fails due to a
SQLITE_LOCKEDerror then it assumes the user is trying to create the database on a remote network folder. This isn't supported by the version of SQLite that ships with Vienna 2.0 so Vienna reports an error panel and prompts the user to find an alternative location for the database. Once it has the new location, it tries to create the database there and repeats the process of creating the Info table. If this new location still reports SQLITE_LOCKED, it goes through the same error panel motions again. - Assuming that the Info table is successfully created, it creates all the other tables and the index, and then it stamps the database version into the Info table.
- As part of creating the schema, it also creates the initial smart folders: "Marked Articles", "Unread Articles" and "Today's Articles". It also creates the Trash folder.
- If Vienna needs to upgrade the database, it uses the current database schema version to determine what tables need to be modified.
- Finally, it updates the
last_openedfield in the Info table. If this fails then it assumes the database is read-only. It sets an internal flag that the rest of Vienna uses to constrain what the user can do in the current session (i.e. no actions that would write to the database are allowed).
By the time the database has been fully initialised, Vienna can now start querying against the database object.