Morg Database Schema
Not logged in

This page describes the different tables that Morg uses in its items database and the relationships between them.

morg

schema_version (integer) timestamp (text) hostname (text) username (text) magic_phrase (text)

This table is used to identify a Morg database. The Morg application will not use a preexisting SQLite file passed to it unless that database meets all of the following critera:

The schema version is the database version, not the Morg application's version. This is simply an integer we will bump up every time we make substantial changes to the data model (e.g., going from a single host to a database synced across multiple hosts).

The timestamp is simply the creation time for a new database recorded in the format yyyy-mm-dd HH:MM:SS.

The magic phrase is the SHA-1 ID computed by concatenating the other columns along with a hard-coded string.

Although the above test is easily subverted, it does, nonetheless, provide a reasonable sanity check that Morg is, in fact, using a valid Morg database and not futzing around with something else.

Items

item

id

This table is simply a list of all the items in the database. Each item has a corresponding table named item_NNN, where NNN is the item ID.

item_NNN

property_id value_id

For each item, we record a collection of properties and their corresponding values. The property_id refers to the row of the property table. The value_id refers to the row of the property_NNN table.

An item is allowed to have more than one instance of the same property. Thus, for example, an item may have multiple names, subitems, parent items, etc.

Properties

property

id name (text) type (text) range (text)

This table records the name, type, and range of each property that Morg can associate with an item.

Property names are arbitrary strings assigned by users. Property types, however, are restricted to one of the following (case-insensitive) strings:

The range of a property is optional. If given, Morg will interpret the range string as follows:

property_NNN

id item_id value

Similar to items, for each property, we will have a table property_NNN, where NNN is the ID of the property defined in the property table.

In a property_NNN table, we record the ID of the item to which that particular instance of the property belongs along with its value. The value column's type is the same as the one recorded in the property table's type column for that property.

Predefined Properties

Morg allows users to define whatever properties they would like. However, in a new database, for the sake of convenience and to be able to provide some basic functionality out-of-the-box, Morg sets up the following predefined properties:

id name type range
name text
type text task, shop
subitem_id integer
parent_id integer
day integer 1 .. 7
date integer 1 .. 31
week integer 1 .. 5
month integer 1 .. 12
year integer
start_hour integer 0 .. 23
start_minute integer 0 .. 59
end_hour integer 0 .. 23
end_minute integer 0 .. 59
priority integer
status text todo, wip, done
store text
price real
quantity integer
notes text

Users are not restricted to associating items with the above properties. They may augment the above properties with their own. They may also delete these defaults and define their own properties from scratch if they so desire.