Output Database Structure

When run, a database is created with all the details of the site and the data. This page describes the structure of that database.

Sometimes the id of a DataTig type or field is used in a database table or column name. In these cases, it is followed by 3 underscores (___). This clearly separates the 2 parts and avoids potential name clashes.

Table type

This lists all the different types defined.

It has the following columns:

  • id

  • directory

  • json_schema

  • list_fields

  • pretty_json_indent

  • default_format

  • markdown_body_is_field

Table type_field

This lists all the fields defined for each type.

It has the following columns:

  • type_id

  • id

  • key

  • type

  • title

  • description

  • sort

  • extra_config

Tables record_<type_id>

For each type, a different table is created. This is because each type table will have different columns depending on which fields it has, and to allow for easier querying.

It has the following columns:

  • id

  • data A JSON string of the dictionary with all data for the record. This helps you use data that is not in a defined field.

  • git_filename

  • format

Each field will have one of more columns for it. Generally fields have one column of the best type, called field_<field_id>.

Fields of type datetime or date will have several columns:

  • field_<field_id> String, in ISO format.

  • field_<field_id>___timestamp Integer, the timestamp of this value.

Tables record_<type_id>___field_<field_id>, for record fields with multiple values

Fields that can have multiple values have extra tables. This is to allow for easier querying, and in some cases because tables will have different columns depending on which fields are defined.

For fields of type list-strings, the table is called record_<type_id>___field_<field_id>. It has the following columns:

  • record_id

  • sort Integer.

  • value

For fields of type list-dictionaries, the table is called record_<type_id>___field_<field_id>. It has the following columns:

  • record_id

  • sort Integer.

  • data A JSON string of the dictionary with all data for this item in the list. This helps you use data that is not in a defined field.

  • extra columns for the fields in the dictionary.

Tables record_error_<type>

For each type, a different table is created. This is to allow for easier querying.

It has the following columns:

  • record_id

  • message

  • data_path

  • schema_path

  • generator

Table error

This lists any errors encountered when processing the site that can’t be linked directly to a record.

It has the following columns:

  • filename

  • message

Table site_config

This lists any values for the site configuration that aren’t expressed in other tables (for example, type or type_field).

It has the following columns:

  • key

  • value

Table calendar

This table always exists, so you can easily query it

It has the following columns:

  • id

  • timezone

Table calendar_event

This table only exists if any calendars are actually defined. This avoids cluttering up the database with too many tables that would never be used and confusing people.

It has the following columns:

  • calendar_id

  • id

  • summary

  • start_iso

  • start_timestamp

  • end_iso

  • end_timestamp

  • record_<type_id>___id