-- Deploy guix-build-coordinator:create_outputs to sqlite

PRAGMA foreign_keys = OFF;

BEGIN;

CREATE TABLE outputs (
       id INTEGER PRIMARY KEY NOT NULL,
       output TEXT NOT NULL
);

INSERT INTO outputs (output) SELECT DISTINCT output FROM derivation_outputs;

CREATE INDEX outputs_output ON outputs (output);




CREATE TABLE derivation_outputs_new (
       id INTEGER PRIMARY KEY ASC,
       derivation_id INTEGER NOT NULL REFERENCES derivations (id),
       name TEXT NOT NULL,
       output_id INTEGER NOT NULL REFERENCES outputs (id)
);

INSERT INTO derivation_outputs_new
  SELECT derivation_outputs.id, derivation_id, name, outputs.id
  FROM derivation_outputs
  INNER JOIN outputs
    ON derivation_outputs.output = outputs.output;

DROP TABLE derivation_outputs;
ALTER TABLE derivation_outputs_new RENAME TO derivation_outputs;

CREATE INDEX derivation_outputs_output_id_idx
  ON derivation_outputs (output_id);
CREATE UNIQUE INDEX derivation_outputs_unique_idx
  ON derivation_outputs (derivation_id, name);


CREATE TABLE unbuilt_outputs_new (
  output_id INTEGER PRIMARY KEY REFERENCES outputs (id)
);

INSERT INTO unbuilt_outputs_new
  SELECT outputs.id
  FROM unbuilt_outputs
  INNER JOIN outputs
    ON unbuilt_outputs.output = outputs.output;

DROP TABLE unbuilt_outputs;
ALTER TABLE unbuilt_outputs_new RENAME TO unbuilt_outputs;

PRAGMA foreign_key_check;

COMMIT;

PRAGMA foreign_keys = ON;