DB Evolution Scratchpad

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;