-- Deploy guix-build-coordinator:initial_derivation_tables to pg

BEGIN;

CREATE TABLE derivations (
       name varchar PRIMARY KEY,
       system varchar NOT NULL
);

CREATE TABLE derivation_outputs (
       id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
       derivation_name varchar NOT NULL REFERENCES derivations (name),
       name varchar NOT NULL,
       output varchar NOT NULL
);

CREATE TABLE derivation_inputs (
       derivation_name varchar REFERENCES derivations (name),
       derivation_output_id integer REFERENCES derivation_outputs (id),
       PRIMARY KEY (derivation_name, derivation_output_id)
);

COMMIT;
