-- Copyright 2018 IBM Corporation

-- Licensed under the Apache License, Version 2.0. See LICENSE file.

DROP SCHEMA IF EXISTS binprint CASCADE;

CREATE SCHEMA binprint;

-- domains are a lightweight form of custom type while a "TYPE" is a composite

DROP DOMAIN IF EXISTS digest64, digest128, digest160, digest256, digest384, digest512 CASCADE;

CREATE DOMAIN digest64 as bytea CHECK (length(VALUE) = 8);

CREATE DOMAIN digest128 as bytea CHECK (length(VALUE) = 16);

CREATE DOMAIN digest160 as bytea CHECK (length(VALUE) = 20);

CREATE DOMAIN digest256 as bytea CHECK (length(VALUE) = 32);

CREATE DOMAIN digest384 as bytea CHECK (length(VALUE) = 48);

CREATE DOMAIN digest512 as bytea CHECK (length(VALUE) = 64);

-- drop table if exists binprint.fingerprint;

CREATE TABLE binprint.fingerprint (

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

gitsha digest160,

sha1 digest160,

md5 digest128,

sha256 digest256,

sha384 digest384,

sha512 digest512,

hwy64 digest64,

hwy128 digest128,

hwy256 digest256,

size INT

);

-- drop table if exists binprint.metadata;

CREATE TABLE binprint.metadata (

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

fingerprint_id integer NOT NULL REFERENCES binprint.fingerprint (id),

text_data text,

json_data jsonb

);

-- drop table if exists binprint.file;

CREATE TABLE binprint.file (

-- "serial primary key" in pg10+ is now "INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY"

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

path text NOT NULL,

fingerprint_id integer NOT NULL REFERENCES binprint.fingerprint (id)

);

-- drop table if exists binprint.archive;

CREATE TABLE binprint.archive (

-- duplicate of the column inherited from binprint.file but necessary

-- to allow us to define relations to archives

-- id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

LIKE binprint.file INCLUDING ALL

) ;

-- id serial PRIMARY KEY,

-- file_id integer NOT NULL REFERENCES binprint.file (id)

-- drop table if exists binprint.archive_entry;

CREATE TABLE binprint.archive_entry (

-- id serial PRIMARY KEY,

archive_id integer NOT NULL REFERENCES binprint.archive (id),

-- LIKE binprint.file INCLUDING ALL

file_id integer NOT NULL REFERENCES binprint.file (id)

) ;

-- function wrapping a simple join, exposed as a computed field/connection with postgraphile

create function binprint.archive_files(a binprint.archive)

returns setof binprint.file as $$

select f.*

from binprint.file f

inner join binprint.archive_entry ae

ON (f.id = ae.file_id)

where ae.archive_id = a.id;

$$ language sql stable;

-- function wrapping a simple join, exposed as a computed field/connection with postgraphile

create function binprint.file_archives(f binprint.file)

returns setof binprint.archive as $$

select a.*

from binprint.archive a

inner join binprint.archive_entry ae

ON (a.id = ae.archive_id)

where ae.file_id = f.id;

$$ language sql stable;

-- drop table if exists binprint.git_repo;

CREATE TABLE binprint.git_repo (

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

branch text,

tag text,

url text,

git_commit digest160

);

-- drop table if exists binprint.git_entry;

CREATE TABLE binprint.git_entry (

git_repo_id integer NOT NULL REFERENCES binprint.git_repo (id),

-- entry_id integer NOT NULL REFERENCES binprint.file (id)

LIKE binprint.file INCLUDING ALL

) ;