This page describes the different tables that Morg uses in its items database and the relationships between them.
|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:
- It contains a table named morg that has exactly the above columns and exactly one row.
- The magic phrase matches the expected value.
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.
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.
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.
|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:
- Commas separate discrete allowed values, e.g., a property foo with type integer and range 1,3,5,7 can only take on those values.
- Double dots specify numeric ranges, e.g., a property foo of type real and range -1..+1 will be allowed to take on values from -1 to +1 (inclusive).
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.
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:
|day||integer||1 .. 7|
|date||integer||1 .. 31|
|week||integer||1 .. 5|
|month||integer||1 .. 12|
|start_hour||integer||0 .. 23|
|start_minute||integer||0 .. 59|
|end_hour||integer||0 .. 23|
|end_minute||integer||0 .. 59|
|status||text||todo, wip, done|
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.