path: root/sqitch/sqlite/deploy/use_numeric_primary_keys.sql

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

BEGIN;

ALTER TABLE derivations RENAME TO old_derivations;

CREATE TABLE derivations (
       id INTEGER PRIMARY KEY ASC,
       name TEXT NOT NULL,
       system TEXT NOT NULL,
       fixed_output BOOLEAN
       CHECK (fixed_output IN (0,1))
);

INSERT INTO derivations (name, system, fixed_output)
  SELECT name, system, fixed_output FROM old_derivations;

DROP TABLE old_derivations;

CREATE UNIQUE INDEX derivations_name_idx ON derivations (name);

-- derivation_outputs

ALTER TABLE derivation_outputs RENAME TO old_derivation_outputs;

CREATE TABLE derivation_outputs (
       id INTEGER PRIMARY KEY ASC,
       derivation_id INTEGER NOT NULL REFERENCES derivations (id),
       name TEXT NOT NULL,
       output TEXT NOT NULL
);

INSERT INTO derivation_outputs (id, derivation_id, name, output)
  SELECT old_derivation_outputs.id,
         derivations.id,
         old_derivation_outputs.name,
         old_derivation_outputs.output
  FROM old_derivation_outputs
  INNER JOIN derivations
    ON derivations.name = old_derivation_outputs.derivation_name;

DROP TABLE old_derivation_outputs;

CREATE INDEX derivation_outputs_output_idx
  ON derivation_outputs (output);
CREATE INDEX derivation_outputs_unique_idx
  ON derivation_outputs (derivation_id, name);

-- derivation_inputs

ALTER TABLE derivation_inputs RENAME TO old_derivation_inputs;

CREATE TABLE derivation_inputs (
       derivation_id INTEGER REFERENCES derivations (id),
       derivation_output_id INTEGER REFERENCES derivation_outputs (id),
       PRIMARY KEY (derivation_id, derivation_output_id)
);

INSERT INTO derivation_inputs (derivation_id, derivation_output_id)
  SELECT derivations.id, derivation_output_id
  FROM old_derivation_inputs
  INNER JOIN derivations
    ON derivations.name = old_derivation_inputs.derivation_name;

DROP TABLE old_derivation_inputs;

CREATE INDEX derivation_inputs_derivation_output_id
  ON derivation_inputs (derivation_output_id);

-- builds

ALTER TABLE builds RENAME TO old_builds;

CREATE TABLE builds (
       id INTEGER PRIMARY KEY ASC,
       uuid TEXT NOT NULL,
       derivation_id NUMBER NOT NULL REFERENCES derivations (id),
       priority INTEGER NOT NULL,
       processed BOOLEAN NOT NULL DEFAULT 0,
       created_at TEXT,
       end_time TEXT,
       canceled BOOLEAN NOT NULL DEFAULT 0,
       deferred_until TEXT
);

INSERT INTO builds (uuid, derivation_id, priority, processed, created_at, end_time, canceled, deferred_until)
  SELECT uuid, derivations.id, priority, processed, created_at, end_time, canceled, deferred_until
  FROM old_builds
  INNER JOIN derivations ON derivations.name = old_builds.derivation_name;

DROP TABLE old_builds;

CREATE UNIQUE INDEX builds_uuid ON builds (uuid);
CREATE INDEX builds_derivation_id_idx ON builds (derivation_id);
CREATE INDEX builds_unprocessed ON builds (processed) WHERE processed = 0;

-- allocated_builds

ALTER TABLE allocated_builds RENAME TO old_allocated_builds;

CREATE TABLE allocated_builds (
       build_id PRIMARY KEY NOT NULL REFERENCES builds (id),
       agent_id NOT NULL REFERENCES agents (id)
);

INSERT INTO allocated_builds (build_id, agent_id)
  SELECT builds.id, agent_id
  FROM old_allocated_builds
  INNER JOIN builds
    ON builds.uuid = old_allocated_builds.build_id;

DROP TABLE old_allocated_builds;

-- build_allocation_plan

DROP TABLE build_allocation_plan;

CREATE TABLE build_allocation_plan (
       build_id NOT NULL REFERENCES builds (id),
       agent_id NOT NULL REFERENCES agents (id),
       ordering INTEGER NOT NULL,
       PRIMARY KEY (agent_id, build_id)
);

-- build_results

ALTER TABLE build_results RENAME TO old_build_results;

CREATE TABLE build_results (
       build_id INTEGER PRIMARY KEY ASC REFERENCES builds (id),
       agent_id TEXT NOT NULL REFERENCES agents (id),
       result TEXT NOT NULL,
       failure_reason
);

INSERT INTO build_results (build_id, agent_id, result, failure_reason)
  SELECT builds.id, agent_id, result, failure_reason
  FROM old_build_results
  INNER JOIN builds ON builds.uuid = old_build_results.build_id;

DROP TABLE old_build_results;

CREATE INDEX build_results_result_idx ON build_results (result);

-- setup_failures

ALTER TABLE setup_failures RENAME TO old_setup_failures;

CREATE TABLE setup_failures (
       id INTEGER PRIMARY KEY ASC,
       build_id INTEGER NOT NULL REFERENCES builds (id),
       agent_id TEXT NOT NULL REFERENCES agents (id),
       failure_reason TEXT NOT NULL
);

INSERT INTO setup_failures (id, build_id, agent_id, failure_reason)
  SELECT old_setup_failures.id, builds.id, old_setup_failures.agent_id, old_setup_failures.failure_reason
  FROM old_setup_failures
  INNER JOIN builds ON builds.uuid = old_setup_failures.build_id;

DROP TABLE old_setup_failures;

-- output_metadata

ALTER TABLE output_metadata RENAME TO old_output_metadata;

CREATE TABLE output_metadata (
       build_id INTEGER NOT NULL REFERENCES builds (id),
       derivation_output_id INTEGER NOT NULL REFERENCES derivation_outputs (id),
       hash TEXT NOT NULL,
       size INTEGER NOT NULL,
       store_references TEXT NOT NULL
);

INSERT INTO output_metadata
  (build_id, derivation_output_id, hash, size, store_references)
  SELECT builds.id, old_output_metadata.derivation_output_id, old_output_metadata.hash, old_output_metadata.size, old_output_metadata.store_references
  FROM old_output_metadata
  INNER JOIN builds ON builds.uuid = old_output_metadata.build_id;

DROP TABLE old_output_metadata;

-- build_tags

ALTER TABLE build_tags RENAME TO old_build_tags;

CREATE TABLE build_tags (
       build_id INTEGER NOT NULL REFERENCES builds (id),
       tag_id INTEGER NOT NULL REFERENCES tags (id)
);

INSERT INTO build_tags (build_id, tag_id)
  SELECT builds.id, tag_id
  FROM old_build_tags
  INNER JOIN builds ON builds.uuid = old_build_tags.build_id;

DROP TABLE old_build_tags;

CREATE INDEX build_tags_build_id_idx ON build_tags (build_id);

-- build_starts

ALTER TABLE build_starts RENAME TO old_build_starts;

CREATE TABLE build_starts (
       id INTEGER PRIMARY KEY ASC,
       build_id INTEGER NOT NULL REFERENCES builds (id),
       agent_id TEXT NOT NULL REFERENCES agents (id),
       start_time TEXT NOT NULL
);

INSERT INTO build_starts (id, build_id, agent_id, start_time)
  SELECT old_build_starts.id, builds.id, old_build_starts.agent_id, old_build_starts.start_time
  FROM old_build_starts
  INNER JOIN builds ON builds.uuid = old_build_starts.build_id;

DROP TABLE old_build_starts;

COMMIT;