io7m | single-page | multi-page | Laurel User Manual 0.99.1-SNAPSHOT
5.5. Metadata
Previous
5. Theory Of Operation
Up
5.7. Validation
Next

# 5.6. File Model

- 5.6.1. Overview
- 5.6.2. Format
  - 5.6.2.1. Overview
  - 5.6.2.2. Schema
- 5.6.3. Undo

## 5.6.1. Overview

1
The applications stores the dataset in a structure known as the file model.

## 5.6.2. Format

## 5.6.2.1. Overview

1
The file model's underlying representation is an SQLite database. The database contains all of the images, captions, categories, metadata, and the undo history.

## 5.6.2.2. Schema

1
The database uses the following schema:

### 5.6.2.2.2. Schema

    CREATE TABLE schema_version (
      version_lock            INTEGER NOT NULL DEFAULT 1,
      version_application_id  TEXT    NOT NULL,
      version_number          INTEGER NOT NULL,

      CONSTRAINT check_lock_primary
        PRIMARY KEY (version_lock),

      CONSTRAINT check_lock_locked
        CHECK (version_lock = 1)
    )
    -- [jooq ignore start]
    STRICT
    -- [jooq ignore stop];
    CREATE TABLE metadata (
      meta_name  TEXT NOT NULL,
      meta_value TEXT NOT NULL

    -- [jooq ignore start]
      ,
      CONSTRAINT metadata_primary_key
        PRIMARY KEY (meta_name)
    -- [jooq ignore stop]
    )
    -- [jooq ignore start]
    STRICT
    -- [jooq ignore stop];
    CREATE TABLE image_blobs (
      image_blob_id      INTEGER PRIMARY KEY NOT NULL,
      image_blob_data    BLOB                NOT NULL,
      image_blob_sha256  TEXT                NOT NULL,
      image_blob_type    TEXT                NOT NULL
    )
    -- [jooq ignore start]
    STRICT
    -- [jooq ignore stop];
    CREATE TABLE images (
      image_id      INTEGER PRIMARY KEY NOT NULL,
      image_name    TEXT                NOT NULL,
      image_file    TEXT,
      image_source  TEXT,
      image_blob    INTEGER             NOT NULL,

    -- [jooq ignore start]
      CONSTRAINT images_name_unique
        UNIQUE (image_name),
    -- [jooq ignore stop]

      CONSTRAINT images_blob_exists
        FOREIGN KEY (image_blob) REFERENCES image_blobs (image_blob_id)
    )
    -- [jooq ignore start]
    STRICT
    -- [jooq ignore stop];
    CREATE TABLE captions (
      caption_id    INTEGER PRIMARY KEY NOT NULL,
      caption_text  TEXT                NOT NULL

    -- [jooq ignore start]
      ,
      CONSTRAINT captions_text_unique
        UNIQUE (caption_text)
    -- [jooq ignore stop]
    )
    -- [jooq ignore start]
    STRICT
    -- [jooq ignore stop];
    CREATE TABLE global_captions (
      global_caption_id     INTEGER PRIMARY KEY NOT NULL,
      global_caption_text   TEXT                NOT NULL,
      global_caption_order  INTEGER             NOT NULL

    -- [jooq ignore start]
      ,
      CONSTRAINT global_captions_text_unique
        UNIQUE (global_caption_text)
    -- [jooq ignore stop]
    )
    -- [jooq ignore start]
    STRICT
    -- [jooq ignore stop];
    CREATE TABLE categories (
      category_id        INTEGER PRIMARY KEY NOT NULL,
      category_text      TEXT                NOT NULL,
      category_required  INTEGER             NOT NULL

    -- [jooq ignore start]
      ,
      CONSTRAINT categories_text_unique
        UNIQUE (category_text)
    -- [jooq ignore stop]
    )
    -- [jooq ignore start]
    STRICT
    -- [jooq ignore stop];
    CREATE TABLE caption_categories (
      caption_caption_id       INTEGER NOT NULL,
      caption_category_id  INTEGER NOT NULL,

      CONSTRAINT caption_categories_caption_exists
        FOREIGN KEY (caption_caption_id)
          REFERENCES captions (caption_id)
            ON DELETE CASCADE,

      CONSTRAINT caption_categories_category_exists
        FOREIGN KEY (caption_category_id)
          REFERENCES categories (category_id)
            ON DELETE CASCADE,

      CONSTRAINT caption_categories_primary_key
        PRIMARY KEY (caption_caption_id, caption_category_id)
    )
    -- [jooq ignore start]
    STRICT
    -- [jooq ignore stop];
    CREATE TABLE image_captions (
      image_caption_image    INTEGER NOT NULL,
      image_caption_caption  INTEGER NOT NULL,

      CONSTRAINT image_captions_image_exists
        FOREIGN KEY (image_caption_image)
          REFERENCES images (image_id)
            ON DELETE CASCADE,

      CONSTRAINT image_captions_caption_exists
        FOREIGN KEY (image_caption_caption)
          REFERENCES captions (caption_id)
            ON DELETE CASCADE,

      CONSTRAINT image_captions_primary_key
        PRIMARY KEY (image_caption_image, image_caption_caption)
    )
    -- [jooq ignore start]
    STRICT
    -- [jooq ignore stop];
    CREATE VIEW image_captions_counts AS
      SELECT
        image_captions.image_caption_caption         AS count_caption_id,
        count (image_captions.image_caption_caption) AS count_caption_count
      FROM
        image_captions
      GROUP BY image_captions.image_caption_caption;
    CREATE TABLE undo (
      undo_id           INTEGER PRIMARY KEY NOT NULL,
      undo_data         BLOB                NOT NULL,
      undo_description  TEXT                NOT NULL,
      undo_time         INTEGER             NOT NULL
    )
    -- [jooq ignore start]
    STRICT
    -- [jooq ignore stop];
    CREATE TABLE redo (
      redo_id           INTEGER PRIMARY KEY NOT NULL,
      redo_data         BLOB                NOT NULL,
      redo_description  TEXT                NOT NULL,
      redo_time         INTEGER             NOT NULL
    )
    -- [jooq ignore start]
    STRICT
    -- [jooq ignore stop];

3
The schema_version table's single row MUST contain com.io7m.laurel in the version_application_id column.
4
Limitations in SQLite mean that it is, unfortunately, impractical to enforce invariants such as category and caption formats at the database level.

## 5.6.3. Undo

1
When an undoable command is successfully executed on the file model, the parameters of the original command, and the data that was modified, is stored in the undo table. When a command is undone, that same data is moved to the redo table.
2
The data and parameters are serialized to Java Properties format, but the precise names and types of the keys is currently unspecified. This means that, although applications other than Laurel can open and manipulate datasets, they will currently need to do some mild reverse engineering to manipulate the history.

## Footnotes

1 | Tables are required to be STRICT. Flexible typing is a bug and not a feature, regardless of how many times the SQLite documentation extols the virtues of being able to accidentally insert malformed data into database tables.

5.5. Metadata
Previous
5. Theory Of Operation
Up
5.7. Validation
Next
io7m | single-page | multi-page | Laurel User Manual 0.99.1-SNAPSHOT