skitch schema

[pyramation]

-- Create the schema
CREATE SCHEMA IF NOT EXISTS skitch;

-- Table: skitch.projects
CREATE TABLE skitch.projects (
  project TEXT PRIMARY KEY,
  uri TEXT UNIQUE,
  created_at TIMESTAMPTZ,
  creator_name TEXT,
  creator_email TEXT
);

-- Table: skitch.releases
CREATE TABLE skitch.releases (
  version FLOAT PRIMARY KEY,
  installed_at TIMESTAMPTZ,
  installer_name TEXT,
  installer_email TEXT
);

-- Table: skitch.changes
CREATE TABLE skitch.changes (
  change_id TEXT PRIMARY KEY,
  script_hash TEXT UNIQUE,
  change TEXT,
  project TEXT REFERENCES skitch.projects(project),
  note TEXT,
  committed_at TIMESTAMPTZ,
  committer_name TEXT,
  committer_email TEXT,
  planned_at TIMESTAMPTZ,
  planner_name TEXT,
  planner_email TEXT
);

-- Table: skitch.events
CREATE TABLE skitch.events (
  event TEXT,
  change_id TEXT,
  change TEXT,
  project TEXT REFERENCES skitch.projects(project),
  note TEXT,
  requires TEXT[],
  conflicts TEXT[],
  tags TEXT[],
  committed_at TIMESTAMPTZ,
  committer_name TEXT,
  committer_email TEXT,
  planned_at TIMESTAMPTZ,
  planner_name TEXT,
  planner_email TEXT
);

-- Table: skitch.tags
CREATE TABLE skitch.tags (
  tag_id TEXT PRIMARY KEY,
  tag TEXT UNIQUE,
  project TEXT REFERENCES skitch.projects(project),
  change_id TEXT REFERENCES skitch.changes(change_id),
  note TEXT,
  committed_at TIMESTAMPTZ,
  committer_name TEXT,
  committer_email TEXT,
  planned_at TIMESTAMPTZ,
  planner_name TEXT,
  planner_email TEXT
);

-- Table: skitch.dependencies
CREATE TABLE skitch.dependencies (
  change_id TEXT REFERENCES skitch.changes(change_id),
  type TEXT,
  dependency TEXT,
  dependency_id TEXT REFERENCES skitch.changes(change_id)
);