-- PostGit: one Git repository maps to a PostgreSQL schema (e.g. public).
CREATE TYPE git_object_type AS ENUM ('commit', 'tree', 'blob', 'tag');
COMMENT ON TYPE git_object_type IS 'libgit2 object types, 1=commit, 2=tree, 3=blob, 4=(annotated) tag.';

CREATE TABLE objects (
  oid bytea NOT NULL
    CHECK (octet_length(oid) IN (20, 32))
    PRIMARY KEY,
  object_type git_object_type NOT NULL,
  object_data bytea NOT NULL
);

CREATE INDEX objects_object_type_idx ON objects (object_type);

CREATE TABLE refs (
  ref_name text NOT NULL PRIMARY KEY,
  target_oid bytea NULL
    CHECK (target_oid IS NULL OR octet_length(target_oid) IN (20, 32))
    REFERENCES objects (oid) ON DELETE RESTRICT,
  symbolic_target text NULL,
  CONSTRAINT refs_direct_or_symbolic_chk CHECK (
    (symbolic_target IS NULL AND target_oid IS NOT NULL)
    OR (symbolic_target IS NOT NULL AND target_oid IS NULL)
  )
);

CREATE VIEW blobs AS
SELECT oid, object_type, object_data
FROM objects
WHERE object_type = 'blob';

CREATE VIEW trees AS
SELECT oid, object_type, object_data
FROM objects
WHERE object_type = 'tree';

CREATE VIEW commits AS
SELECT oid, object_type, object_data
FROM objects
WHERE object_type = 'commit';

CREATE VIEW tags AS
SELECT oid, object_type, object_data
FROM objects
WHERE object_type = 'tag';