diff options
| -rw-r--r-- | sqitch/deploy/appschema.sql | 7 | ||||
| -rw-r--r-- | sqitch/deploy/buildstatus_enum.sql | 18 | ||||
| -rw-r--r-- | sqitch/deploy/initial_import.sql | 358 | ||||
| -rw-r--r-- | sqitch/revert/appschema.sql | 7 | ||||
| -rw-r--r-- | sqitch/revert/buildstatus_enum.sql | 7 | ||||
| -rw-r--r-- | sqitch/revert/initial_import.sql | 21 | ||||
| -rw-r--r-- | sqitch/sqitch.plan | 3 | ||||
| -rw-r--r-- | sqitch/verify/appschema.sql | 7 | ||||
| -rw-r--r-- | sqitch/verify/buildstatus_enum.sql | 7 | ||||
| -rw-r--r-- | sqitch/verify/initial_import.sql | 50 |
10 files changed, 485 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; diff --git a/sqitch/revert/appschema.sql b/sqitch/revert/appschema.sql new file mode 100644 index 0000000..1446dc7 --- /dev/null +++ b/sqitch/revert/appschema.sql @@ -0,0 +1,7 @@ +-- Revert guix-data-service:appschema from pg + +BEGIN; + +DROP SCHEMA guix_data_service; + +COMMIT; diff --git a/sqitch/revert/buildstatus_enum.sql b/sqitch/revert/buildstatus_enum.sql new file mode 100644 index 0000000..08b321c --- /dev/null +++ b/sqitch/revert/buildstatus_enum.sql @@ -0,0 +1,7 @@ +-- Revert guix-data-service:buildstatus_enum from pg + +BEGIN; + +DROP TYPE guix_data_service.buildstatus; + +COMMIT; diff --git a/sqitch/revert/initial_import.sql b/sqitch/revert/initial_import.sql new file mode 100644 index 0000000..2a24cb3 --- /dev/null +++ b/sqitch/revert/initial_import.sql @@ -0,0 +1,21 @@ +-- Revert guix-data-service:initial_import from pg + +BEGIN; + +DROP TABLE guix_data_service.build_servers; +DROP TABLE guix_data_service.build_status; +DROP TABLE guix_data_service.builds; +DROP TABLE guix_data_service.derivation_inputs; +DROP TABLE guix_data_service.derivation_output_details; +DROP TABLE guix_data_service.derivation_outputs; +DROP TABLE guix_data_service.derivation_source_files; +DROP TABLE guix_data_service.derivation_sources; +DROP TABLE guix_data_service.derivations; +DROP TABLE guix_data_service.guix_revision_package_derivations; +DROP TABLE guix_data_service.guix_revisions; +DROP TABLE guix_data_service.load_new_guix_revision_jobs; +DROP TABLE guix_data_service.package_derivations; +DROP TABLE guix_data_service.package_metadata; +DROP TABLE guix_data_service.packages; + +COMMIT; diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan index 4e50000..61fd9a9 100644 --- a/sqitch/sqitch.plan +++ b/sqitch/sqitch.plan @@ -2,3 +2,6 @@ %project=guix-data-service %uri=https://git.cbaines.net/guix/data-service/ +appschema 2019-04-13T11:43:59Z Christopher Baines <mail@cbaines.net> # Add schema for the Guix Data Service +buildstatus_enum [appschema] 2019-04-13T11:56:37Z Christopher Baines <mail@cbaines.net> # Creates the buildstatus enum +initial_import 2019-04-13T13:06:28Z Christopher Baines <mail@cbaines.net> # Import the manually managed database schema diff --git a/sqitch/verify/appschema.sql b/sqitch/verify/appschema.sql new file mode 100644 index 0000000..bc056f0 --- /dev/null +++ b/sqitch/verify/appschema.sql @@ -0,0 +1,7 @@ +-- Verify guix-data-service:appschema on pg + +BEGIN; + +SELECT pg_catalog.has_schema_privilege('guix_data_service', 'usage'); + +ROLLBACK; diff --git a/sqitch/verify/buildstatus_enum.sql b/sqitch/verify/buildstatus_enum.sql new file mode 100644 index 0000000..988cff5 --- /dev/null +++ b/sqitch/verify/buildstatus_enum.sql @@ -0,0 +1,7 @@ +-- Verify guix-data-service:buildstatus_enum on pg + +BEGIN; + +SELECT pg_catalog.has_type_privilege('guix_data_service.buildstatus', 'usage'); + +ROLLBACK; diff --git a/sqitch/verify/initial_import.sql b/sqitch/verify/initial_import.sql new file mode 100644 index 0000000..c1cd488 --- /dev/null +++ b/sqitch/verify/initial_import.sql @@ -0,0 +1,50 @@ +-- Verify guix-data-service:initial_import on pg + +BEGIN; + +SELECT id, url, lookup_all_derivations + FROM guix_data_service.build_servers WHERE FALSE; + +SELECT id, status_fetched_at, internal_build_id, starttime, stoptime status + FROM guix_data_service.build_status WHERE FALSE; + +SELECT internal_id, id, build_server_id, derivation_id + FROM guix_data_service.builds WHERE FALSE; + +SELECT derivation_id, derivation_output_id + FROM guix_data_service.derivation_inputs WHERE FALSE; + +SELECT id, path, hash_algorithm, hash, recursive + FROM guix_data_service.derivation_output_details WHERE FALSE; + +SELECT derivation_id, name, derivation_output_details_id, id + FROM guix_data_service.derivation_outputs WHERE FALSE; + +SELECT id, store_path + FROM guix_data_service.derivation_source_files WHERE FALSE; + +SELECT derivation_id, derivation_source_file_id + FROM guix_data_service.derivation_sources WHERE FALSE; + +SELECT id, file_name, builder, args, env_vars, system + FROM guix_data_service.derivations WHERE FALSE; + +SELECT revision_id, package_derivation_id + FROM guix_data_service.guix_revision_package_derivations WHERE FALSE; + +SELECT id, url, commit, store_path + FROM guix_data_service.guix_revisions WHERE FALSE; + +SELECT id, url, commit, source + FROM guix_data_service.load_new_guix_revision_jobs WHERE FALSE; + +SELECT id, package_id, derivation_id, system, target + FROM guix_data_service.package_derivations WHERE FALSE; + +SELECT id, sha1_hash, synopsis, description, home_page + FROM guix_data_service.package_metadata WHERE FALSE; + +SELECT id, name, version, package_metadata_id + FROM guix_data_service.packages WHERE FALSE; + +ROLLBACK; |