diff options
Diffstat (limited to 'sqitch/sqlite/deploy/use_numeric_primary_keys.sql')
-rw-r--r-- | sqitch/sqlite/deploy/use_numeric_primary_keys.sql | 219 |
1 files changed, 219 insertions, 0 deletions
diff --git a/sqitch/sqlite/deploy/use_numeric_primary_keys.sql b/sqitch/sqlite/deploy/use_numeric_primary_keys.sql new file mode 100644 index 0000000..7d5fda7 --- /dev/null +++ b/sqitch/sqlite/deploy/use_numeric_primary_keys.sql @@ -0,0 +1,219 @@ +-- 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; |