DB Evolution Scratchpad

Document cloning (May 22, 2019)

ALTER TABLE document ADD COLUMN cloned_from TEXT ;

Folder Sharing

ALTER TABLE folder ADD COLUMN public_visibility TEXT NOT NULL DEFAULT 'PRIVATE';
ALTER TABLE folder ADD COLUMN public_access_level TEXT;
ALTER TABLE sharing_policy ALTER COLUMN folder_id TYPE UUID USING null;

-- DELETE FROM sharing_policy WHERE folder_id IS NOT NULL;
ALTER TABLE sharing_policy ADD CONSTRAINT sharing_policy_folder_id_fkey FOREIGN KEY (folder_id) REFERENCES folder(id);
ALTER TABLE sharing_policy ADD CONSTRAINT sharing_policy_document_id_fkey FOREIGN KEY (document_id) REFERENCES document(id);

Introduction of folders and readmes

-- Drop previous (unused) folder tables and re-create
DROP TABLE folder_association;
DROP TABLE folder CASCADE;

CREATE TABLE folder (
  id UUID PRIMARY KEY,
  owner TEXT NOT NULL REFERENCES "user"(username),
  title TEXT NOT NULL,
  -- if parent is empty then it's a root folder
  parent UUID REFERENCES folder(id),
  readme TEXT
);

CREATE TABLE folder_association (
  folder_id UUID NOT NULL REFERENCES folder(id),
  document_id TEXT NOT NULL REFERENCES document(id) ON DELETE CASCADE,
  PRIMARY KEY (folder_id, document_id)
);

-- Add readme column to user table
ALTER TABLE "user" ADD COLUMN readme TEXT;

Revised task API

DROP TABLE task;

CREATE TABLE task (
  id UUID PRIMARY KEY,
  task_type TEXT NOT NULL,
  class_name TEXT NOT NULL,
  -- one ore more tasks belong to one job
  job_id UUID NOT NULL,
  -- some tasks run on specific documents and/or fileparts
  document_id TEXT,
  filepart_id UUID,
  spawned_by TEXT,
  spawned_at TIMESTAMP WITH TIME ZONE NOT NULL,
  stopped_at TIMESTAMP WITH TIME ZONE,
  -- all-purpose text field for holding results, exception message, etc.
  stopped_with TEXT,
  status TEXT NOT NULL DEFAULT 'PENDING',
  progress INTEGER NOT NULL
);
CREATE INDEX idx_task_job_id ON task(job_id);