aboutsummaryrefslogtreecommitdiff
path: root/sqitch/deploy
diff options
context:
space:
mode:
authorChristopher Baines <mail@cbaines.net>2019-04-14 10:06:13 +0100
committerChristopher Baines <mail@cbaines.net>2019-04-14 10:06:13 +0100
commitd69f77d59eefc1ca577d6df0cfe6d8cd46742b82 (patch)
tree86dd2c86d984dcbb49741e4852407af66587d2a8 /sqitch/deploy
parent70faad04371ff441793da4e6213ae0855723b019 (diff)
downloaddata-service-d69f77d59eefc1ca577d6df0cfe6d8cd46742b82.tar
data-service-d69f77d59eefc1ca577d6df0cfe6d8cd46742b82.tar.gz
Add some initial Sqitch migrations
These are based on the state of the current manually managed database.
Diffstat (limited to 'sqitch/deploy')
-rw-r--r--sqitch/deploy/appschema.sql7
-rw-r--r--sqitch/deploy/buildstatus_enum.sql18
-rw-r--r--sqitch/deploy/initial_import.sql358
3 files changed, 383 insertions, 0 deletions
diff --git a/sqitch/deploy/appschema.sql b/sqitch/deploy/appschema.sql
new file mode 100644
index 0000000..9b5dc13
--- /dev/null
+++ b/sqitch/deploy/appschema.sql
@@ -0,0 +1,7 @@
+-- Deploy guix-data-service:appschema to pg
+
+BEGIN;
+
+CREATE SCHEMA guix_data_service;
+
+COMMIT;
diff --git a/sqitch/deploy/buildstatus_enum.sql b/sqitch/deploy/buildstatus_enum.sql
new file mode 100644
index 0000000..f471966
--- /dev/null
+++ b/sqitch/deploy/buildstatus_enum.sql
@@ -0,0 +1,18 @@
+-- Deploy guix-data-service:buildstatus_enum to pg
+-- requires: appschema
+
+BEGIN;
+
+SET client_min_messages = 'warning';
+
+CREATE TYPE guix_data_service.buildstatus AS ENUM (
+ 'scheduled',
+ 'started',
+ 'succeeded',
+ 'failed',
+ 'failed-dependency',
+ 'failed-other',
+ 'canceled'
+);
+
+COMMIT;
diff --git a/sqitch/deploy/initial_import.sql b/sqitch/deploy/initial_import.sql
new file mode 100644
index 0000000..238922d
--- /dev/null
+++ b/sqitch/deploy/initial_import.sql
@@ -0,0 +1,358 @@
+-- Deploy guix-data-service:initial_import to pg
+
+BEGIN;
+
+
+CREATE TABLE guix_data_service.build_servers (
+ id integer NOT NULL,
+ url character varying NOT NULL,
+ lookup_all_derivations boolean NOT NULL
+);
+
+ALTER TABLE guix_data_service.build_servers ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
+ SEQUENCE NAME guix_data_service.build_servers_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
+
+
+CREATE TABLE guix_data_service.build_status (
+ id integer NOT NULL,
+ status_fetched_at timestamp without time zone DEFAULT clock_timestamp() NOT NULL,
+ internal_build_id integer NOT NULL,
+ starttime timestamp without time zone,
+ stoptime timestamp without time zone,
+ status guix_data_service.buildstatus NOT NULL
+);
+
+ALTER TABLE guix_data_service.build_status ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
+ SEQUENCE NAME guix_data_service.build_status_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
+
+
+CREATE TABLE guix_data_service.builds (
+ internal_id integer NOT NULL,
+ id integer NOT NULL,
+ build_server_id integer NOT NULL,
+ derivation_id integer NOT NULL,
+ "timestamp" timestamp without time zone NOT NULL
+);
+
+ALTER TABLE guix_data_service.builds ALTER COLUMN internal_id ADD GENERATED ALWAYS AS IDENTITY (
+ SEQUENCE NAME guix_data_service.builds_internal_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
+
+
+CREATE TABLE guix_data_service.derivation_inputs (
+ derivation_id integer NOT NULL,
+ derivation_output_id integer NOT NULL
+);
+
+
+CREATE TABLE guix_data_service.derivation_output_details (
+ id integer NOT NULL,
+ path character varying NOT NULL,
+ hash_algorithm character varying,
+ hash character varying,
+ recursive boolean NOT NULL
+);
+
+ALTER TABLE guix_data_service.derivation_output_details ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
+ SEQUENCE NAME guix_data_service.derivation_output_details_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
+
+
+CREATE TABLE guix_data_service.derivation_outputs (
+ derivation_id integer NOT NULL,
+ name character varying NOT NULL,
+ derivation_output_details_id integer NOT NULL,
+ id integer NOT NULL
+);
+
+ALTER TABLE guix_data_service.derivation_outputs ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
+ SEQUENCE NAME guix_data_service.derivation_outputs_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
+
+
+CREATE TABLE guix_data_service.derivation_source_files (
+ id integer NOT NULL,
+ store_path character varying NOT NULL
+);
+
+ALTER TABLE guix_data_service.derivation_source_files ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
+ SEQUENCE NAME guix_data_service.derivation_source_files_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
+
+CREATE TABLE guix_data_service.derivation_sources (
+ derivation_id integer NOT NULL,
+ derivation_source_file_id integer NOT NULL
+);
+
+
+CREATE TABLE guix_data_service.derivations (
+ id integer NOT NULL,
+ file_name character varying NOT NULL,
+ builder character varying NOT NULL,
+ args character varying[] DEFAULT ARRAY[''::text] NOT NULL,
+ env_vars character varying[] DEFAULT ARRAY[]::character varying[] NOT NULL,
+ system character varying DEFAULT ''::character varying NOT NULL
+);
+
+ALTER TABLE guix_data_service.derivations ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
+ SEQUENCE NAME guix_data_service.derivations_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
+
+
+CREATE TABLE guix_data_service.guix_revision_package_derivations (
+ revision_id integer NOT NULL,
+ package_derivation_id integer NOT NULL
+);
+
+
+CREATE TABLE guix_data_service.guix_revisions (
+ id integer NOT NULL,
+ url character varying NOT NULL,
+ commit character varying NOT NULL,
+ store_path character varying NOT NULL
+);
+
+ALTER TABLE guix_data_service.guix_revisions ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
+ SEQUENCE NAME guix_data_service.guix_revisions_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
+
+
+CREATE TABLE guix_data_service.load_new_guix_revision_jobs (
+ id integer NOT NULL,
+ url character varying NOT NULL,
+ commit character varying NOT NULL,
+ source character varying NOT NULL
+);
+
+ALTER TABLE guix_data_service.load_new_guix_revision_jobs ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
+ SEQUENCE NAME guix_data_service.load_new_guix_revision_jobs_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
+
+
+CREATE TABLE guix_data_service.package_derivations (
+ id integer NOT NULL,
+ package_id integer NOT NULL,
+ derivation_id integer NOT NULL,
+ system character varying NOT NULL,
+ target character varying NOT NULL
+);
+
+ALTER TABLE guix_data_service.package_derivations ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
+ SEQUENCE NAME guix_data_service.package_derivations_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
+
+
+CREATE TABLE guix_data_service.package_metadata (
+ id integer NOT NULL,
+ sha1_hash character(40) NOT NULL,
+ synopsis character varying,
+ description character varying,
+ home_page character varying
+);
+
+ALTER TABLE guix_data_service.package_metadata ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
+ SEQUENCE NAME guix_data_service.package_metadata_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
+
+
+CREATE TABLE guix_data_service.packages (
+ id integer NOT NULL,
+ name character varying NOT NULL,
+ version character varying NOT NULL,
+ package_metadata_id integer NOT NULL
+);
+
+ALTER TABLE guix_data_service.packages ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
+ SEQUENCE NAME guix_data_service.packages_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
+
+
+ALTER TABLE ONLY guix_data_service.build_servers
+ ADD CONSTRAINT build_servers_id_key UNIQUE (id);
+
+ALTER TABLE ONLY guix_data_service.build_servers
+ ADD CONSTRAINT build_servers_pkey PRIMARY KEY (url);
+
+ALTER TABLE ONLY guix_data_service.build_status
+ ADD CONSTRAINT build_status_id_key UNIQUE (id);
+
+ALTER TABLE ONLY guix_data_service.builds
+ ADD CONSTRAINT builds_internal_id_key UNIQUE (internal_id);
+
+ALTER TABLE ONLY guix_data_service.builds
+ ADD CONSTRAINT builds_pkey PRIMARY KEY (id, build_server_id);
+
+ALTER TABLE ONLY guix_data_service.derivation_inputs
+ ADD CONSTRAINT derivation_inputs_pkey PRIMARY KEY (derivation_id, derivation_output_id);
+
+ALTER TABLE ONLY guix_data_service.derivation_output_details
+ ADD CONSTRAINT derivation_output_details_id_unique UNIQUE (id);
+
+ALTER TABLE ONLY guix_data_service.derivation_output_details
+ ADD CONSTRAINT derivation_output_details_pkey PRIMARY KEY (path);
+
+ALTER TABLE ONLY guix_data_service.derivation_outputs
+ ADD CONSTRAINT derivation_outputs_pkey PRIMARY KEY (derivation_id, name);
+
+ALTER TABLE ONLY guix_data_service.derivation_outputs
+ ADD CONSTRAINT derivation_outputs_unique_id UNIQUE (id);
+
+ALTER TABLE ONLY guix_data_service.derivation_source_files
+ ADD CONSTRAINT derivation_source_files_id_unique UNIQUE (id);
+
+ALTER TABLE ONLY guix_data_service.derivation_source_files
+ ADD CONSTRAINT derivation_source_files_pkey PRIMARY KEY (store_path);
+
+ALTER TABLE ONLY guix_data_service.derivation_sources
+ ADD CONSTRAINT derivation_sources_pkey PRIMARY KEY (derivation_id, derivation_source_file_id);
+
+ALTER TABLE ONLY guix_data_service.derivations
+ ADD CONSTRAINT derivations_pkey PRIMARY KEY (file_name);
+
+ALTER TABLE ONLY guix_data_service.derivations
+ ADD CONSTRAINT file_name_unique UNIQUE (file_name);
+
+ALTER TABLE ONLY guix_data_service.guix_revision_package_derivations
+ ADD CONSTRAINT guix_revision_package_derivations_pkey PRIMARY KEY (revision_id, package_derivation_id);
+
+ALTER TABLE ONLY guix_data_service.guix_revisions
+ ADD CONSTRAINT guix_revisions_pkey PRIMARY KEY (id);
+
+ALTER TABLE ONLY guix_data_service.derivations
+ ADD CONSTRAINT id_unique UNIQUE (id);
+
+ALTER TABLE ONLY guix_data_service.package_derivations
+ ADD CONSTRAINT package_derivations_id_key UNIQUE (id);
+
+ALTER TABLE ONLY guix_data_service.package_derivations
+ ADD CONSTRAINT package_derivations_pkey PRIMARY KEY (package_id, derivation_id, system, target);
+
+ALTER TABLE ONLY guix_data_service.package_metadata
+ ADD CONSTRAINT package_metadata_pkey PRIMARY KEY (id);
+
+ALTER TABLE ONLY guix_data_service.packages
+ ADD CONSTRAINT packages_id_key UNIQUE (id);
+
+ALTER TABLE ONLY guix_data_service.packages
+ ADD CONSTRAINT packages_pkey PRIMARY KEY (name, version, package_metadata_id);
+
+ALTER TABLE ONLY guix_data_service.package_metadata
+ ADD CONSTRAINT synopsis_description_home_page UNIQUE (synopsis, description, home_page);
+
+ALTER TABLE ONLY guix_data_service.package_metadata
+ ADD CONSTRAINT unique_sha1_hash UNIQUE (sha1_hash);
+
+
+CREATE INDEX build_status_internal_build_id_and_status_fetched_at_desc ON guix_data_service.build_status USING btree (internal_build_id, status_fetched_at DESC);
+
+CREATE INDEX builds_derivation_id ON guix_data_service.builds USING hash (derivation_id);
+
+CREATE INDEX derivation_inputs_derivation_output_id_idx ON guix_data_service.derivation_inputs USING btree (derivation_output_id);
+
+CREATE INDEX guix_revisions_commit_idx ON guix_data_service.guix_revisions USING hash (commit);
+
+CREATE INDEX package_derivations_derivation_id ON guix_data_service.package_derivations USING hash (derivation_id);
+
+
+ALTER TABLE ONLY guix_data_service.build_status
+ ADD CONSTRAINT build_status_internal_build_id_fkey FOREIGN KEY (internal_build_id) REFERENCES guix_data_service.builds(internal_id);
+
+ALTER TABLE ONLY guix_data_service.builds
+ ADD CONSTRAINT builds_build_server_id_fkey FOREIGN KEY (build_server_id) REFERENCES guix_data_service.build_servers(id);
+
+ALTER TABLE ONLY guix_data_service.builds
+ ADD CONSTRAINT builds_derivation_id_fkey FOREIGN KEY (derivation_id) REFERENCES guix_data_service.derivations(id);
+
+ALTER TABLE ONLY guix_data_service.derivation_inputs
+ ADD CONSTRAINT derivation_id_fk FOREIGN KEY (derivation_id) REFERENCES guix_data_service.derivations(id);
+
+ALTER TABLE ONLY guix_data_service.derivation_inputs
+ ADD CONSTRAINT derivation_output_id_fk FOREIGN KEY (derivation_output_id) REFERENCES guix_data_service.derivation_outputs(id);
+
+ALTER TABLE ONLY guix_data_service.derivation_outputs
+ ADD CONSTRAINT derivation_outputs_derivation_id_fk FOREIGN KEY (derivation_id) REFERENCES guix_data_service.derivations(id);
+
+ALTER TABLE ONLY guix_data_service.derivation_outputs
+ ADD CONSTRAINT derivation_outputs_derivation_output_details_id_fk FOREIGN KEY (derivation_output_details_id) REFERENCES guix_data_service.derivation_output_details(id);
+
+ALTER TABLE ONLY guix_data_service.derivation_sources
+ ADD CONSTRAINT derivation_sources_derivation_source_file_id_fk FOREIGN KEY (derivation_source_file_id) REFERENCES guix_data_service.derivation_source_files(id);
+
+ALTER TABLE ONLY guix_data_service.derivation_sources
+ ADD CONSTRAINT dervaition_sources_derivation_id_fk FOREIGN KEY (derivation_id) REFERENCES guix_data_service.derivations(id);
+
+ALTER TABLE ONLY guix_data_service.guix_revision_package_derivations
+ ADD CONSTRAINT guix_revision_package_derivations_package_derivation_id_fkey FOREIGN KEY (package_derivation_id) REFERENCES guix_data_service.package_derivations(id);
+
+ALTER TABLE ONLY guix_data_service.guix_revision_package_derivations
+ ADD CONSTRAINT guix_revision_package_derivations_revision_id_fkey FOREIGN KEY (revision_id) REFERENCES guix_data_service.guix_revisions(id);
+
+ALTER TABLE ONLY guix_data_service.packages
+ ADD CONSTRAINT package_metadata_id FOREIGN KEY (package_metadata_id) REFERENCES guix_data_service.package_metadata(id);
+
+
+GRANT USAGE ON SCHEMA guix_data_service TO guix_data_service;
+
+COMMIT;