X-Git-Url: https://git.arvados.org/arvados.git/blobdiff_plain/572601ef7712342e599e2b2abf22268f8f378b07..4ec3bf28abdaccca697563dac1bd126ef6df9975:/services/api/db/structure.sql diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql index 7a355a5eba..4520e1bc04 100644 --- a/services/api/db/structure.sql +++ b/services/api/db/structure.sql @@ -172,7 +172,12 @@ CREATE TABLE public.collections ( is_trashed boolean DEFAULT false NOT NULL, storage_classes_desired jsonb DEFAULT '["default"]'::jsonb, storage_classes_confirmed jsonb DEFAULT '[]'::jsonb, - storage_classes_confirmed_at timestamp without time zone + storage_classes_confirmed_at timestamp without time zone, + current_version_uuid character varying, + version integer DEFAULT 1 NOT NULL, + preserve_version boolean DEFAULT false, + file_count integer DEFAULT 0 NOT NULL, + file_size_total bigint DEFAULT 0 NOT NULL ); @@ -299,7 +304,8 @@ CREATE TABLE public.container_requests ( log_uuid character varying(255), output_name character varying(255) DEFAULT NULL::character varying, output_ttl integer DEFAULT 0 NOT NULL, - secret_mounts jsonb DEFAULT '{}'::jsonb + secret_mounts jsonb DEFAULT '{}'::jsonb, + runtime_token text ); @@ -354,7 +360,12 @@ CREATE TABLE public.containers ( locked_by_uuid character varying(255), scheduling_parameters text, secret_mounts jsonb DEFAULT '{}'::jsonb, - secret_mounts_md5 character varying DEFAULT '99914b932bd37a50b983c5e7c90ae93b'::character varying + secret_mounts_md5 character varying DEFAULT '99914b932bd37a50b983c5e7c90ae93b'::character varying, + runtime_status jsonb DEFAULT '{}'::jsonb, + runtime_user_uuid text, + runtime_auth_scopes jsonb, + runtime_token text, + lock_count integer DEFAULT 0 NOT NULL ); @@ -1623,21 +1634,21 @@ CREATE INDEX collection_index_on_properties ON public.collections USING gin (pro -- Name: collections_full_text_search_idx; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX collections_full_text_search_idx ON public.collections USING gin (to_tsvector('english'::regconfig, (((((((((((((((((COALESCE(owner_uuid, ''::character varying))::text || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(portable_data_hash, ''::character varying))::text) || ' '::text) || (COALESCE(uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || (COALESCE(description, ''::character varying))::text) || ' '::text) || COALESCE((properties)::text, ''::text)) || ' '::text) || COALESCE(file_names, (''::character varying)::text)))); +CREATE INDEX collections_full_text_search_idx ON public.collections USING gin (to_tsvector('english'::regconfig, substr((((((((((((((((((COALESCE(owner_uuid, ''::character varying))::text || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(portable_data_hash, ''::character varying))::text) || ' '::text) || (COALESCE(uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || (COALESCE(description, ''::character varying))::text) || ' '::text) || COALESCE((properties)::text, ''::text)) || ' '::text) || COALESCE(file_names, ''::text)), 0, 1000000))); -- -- Name: collections_search_index; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX collections_search_index ON public.collections USING btree (owner_uuid, modified_by_client_uuid, modified_by_user_uuid, portable_data_hash, uuid, name); +CREATE INDEX collections_search_index ON public.collections USING btree (owner_uuid, modified_by_client_uuid, modified_by_user_uuid, portable_data_hash, uuid, name, current_version_uuid); -- -- Name: container_requests_full_text_search_idx; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX container_requests_full_text_search_idx ON public.container_requests USING gin (to_tsvector('english'::regconfig, (((((((((((((((((((((((((((((((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || COALESCE(description, ''::text)) || ' '::text) || COALESCE((properties)::text, ''::text)) || ' '::text) || (COALESCE(state, ''::character varying))::text) || ' '::text) || (COALESCE(requesting_container_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(container_uuid, ''::character varying))::text) || ' '::text) || COALESCE(runtime_constraints, ''::text)) || ' '::text) || (COALESCE(container_image, ''::character varying))::text) || ' '::text) || COALESCE(environment, ''::text)) || ' '::text) || (COALESCE(cwd, ''::character varying))::text) || ' '::text) || COALESCE(command, ''::text)) || ' '::text) || (COALESCE(output_path, ''::character varying))::text) || ' '::text) || COALESCE(filters, ''::text)) || ' '::text) || COALESCE(scheduling_parameters, ''::text)) || ' '::text) || (COALESCE(output_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(log_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(output_name, ''::character varying))::text))); +CREATE INDEX container_requests_full_text_search_idx ON public.container_requests USING gin (to_tsvector('english'::regconfig, substr((((((((((((((((((((((((((((((((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || COALESCE(description, ''::text)) || ' '::text) || COALESCE((properties)::text, ''::text)) || ' '::text) || (COALESCE(state, ''::character varying))::text) || ' '::text) || (COALESCE(requesting_container_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(container_uuid, ''::character varying))::text) || ' '::text) || COALESCE(runtime_constraints, ''::text)) || ' '::text) || (COALESCE(container_image, ''::character varying))::text) || ' '::text) || COALESCE(environment, ''::text)) || ' '::text) || (COALESCE(cwd, ''::character varying))::text) || ' '::text) || COALESCE(command, ''::text)) || ' '::text) || (COALESCE(output_path, ''::character varying))::text) || ' '::text) || COALESCE(filters, ''::text)) || ' '::text) || COALESCE(scheduling_parameters, ''::text)) || ' '::text) || (COALESCE(output_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(log_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(output_name, ''::character varying))::text), 0, 1000000))); -- @@ -1672,7 +1683,7 @@ CREATE INDEX group_index_on_properties ON public.groups USING gin (properties); -- Name: groups_full_text_search_idx; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX groups_full_text_search_idx ON public.groups USING gin (to_tsvector('english'::regconfig, (((((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || (COALESCE(description, ''::character varying))::text) || ' '::text) || (COALESCE(group_class, ''::character varying))::text) || ' '::text) || COALESCE((properties)::text, ''::text)))); +CREATE INDEX groups_full_text_search_idx ON public.groups USING gin (to_tsvector('english'::regconfig, substr((((((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || (COALESCE(description, ''::character varying))::text) || ' '::text) || (COALESCE(group_class, ''::character varying))::text) || ' '::text) || COALESCE((properties)::text, ''::text)), 0, 1000000))); -- @@ -1780,6 +1791,13 @@ CREATE UNIQUE INDEX index_authorized_keys_on_uuid ON public.authorized_keys USIN CREATE INDEX index_collections_on_created_at ON public.collections USING btree (created_at); +-- +-- Name: index_collections_on_current_version_uuid_and_version; Type: INDEX; Schema: public; Owner: - +-- + +CREATE UNIQUE INDEX index_collections_on_current_version_uuid_and_version ON public.collections USING btree (current_version_uuid, version); + + -- -- Name: index_collections_on_delete_at; Type: INDEX; Schema: public; Owner: - -- @@ -1819,7 +1837,7 @@ CREATE INDEX index_collections_on_owner_uuid ON public.collections USING btree ( -- Name: index_collections_on_owner_uuid_and_name; Type: INDEX; Schema: public; Owner: - -- -CREATE UNIQUE INDEX index_collections_on_owner_uuid_and_name ON public.collections USING btree (owner_uuid, name) WHERE (is_trashed = false); +CREATE UNIQUE INDEX index_collections_on_owner_uuid_and_name ON public.collections USING btree (owner_uuid, name) WHERE ((is_trashed = false) AND ((current_version_uuid)::text = (uuid)::text)); -- @@ -1934,6 +1952,20 @@ CREATE INDEX index_containers_on_owner_uuid ON public.containers USING btree (ow CREATE INDEX index_containers_on_queued_state ON public.containers USING btree (state, ((priority > 0))); +-- +-- Name: index_containers_on_reuse_columns; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_containers_on_reuse_columns ON public.containers USING btree (md5(command), cwd, md5(environment), output_path, container_image, md5(mounts), secret_mounts_md5, md5(runtime_constraints)); + + +-- +-- Name: index_containers_on_runtime_status; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_containers_on_runtime_status ON public.containers USING gin (runtime_status); + + -- -- Name: index_containers_on_secret_mounts_md5; Type: INDEX; Schema: public; Owner: - -- @@ -2249,6 +2281,20 @@ CREATE INDEX index_links_on_modified_at_uuid ON public.links USING btree (modifi CREATE INDEX index_links_on_owner_uuid ON public.links USING btree (owner_uuid); +-- +-- Name: index_links_on_substring_head_uuid; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_links_on_substring_head_uuid ON public.links USING btree ("substring"((head_uuid)::text, 7, 5)); + + +-- +-- Name: index_links_on_substring_tail_uuid; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_links_on_substring_tail_uuid ON public.links USING btree ("substring"((tail_uuid)::text, 7, 5)); + + -- -- Name: index_links_on_tail_uuid; Type: INDEX; Schema: public; Owner: - -- @@ -2624,7 +2670,7 @@ CREATE INDEX job_tasks_search_index ON public.job_tasks USING btree (uuid, owner -- Name: jobs_full_text_search_idx; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX jobs_full_text_search_idx ON public.jobs USING gin (to_tsvector('english'::regconfig, (((((((((((((((((((((((((((((((((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(submit_id, ''::character varying))::text) || ' '::text) || (COALESCE(script, ''::character varying))::text) || ' '::text) || (COALESCE(script_version, ''::character varying))::text) || ' '::text) || COALESCE(script_parameters, ''::text)) || ' '::text) || (COALESCE(cancelled_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(cancelled_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(output, ''::character varying))::text) || ' '::text) || (COALESCE(is_locked_by_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(log, ''::character varying))::text) || ' '::text) || COALESCE(tasks_summary, ''::text)) || ' '::text) || COALESCE(runtime_constraints, ''::text)) || ' '::text) || (COALESCE(repository, ''::character varying))::text) || ' '::text) || (COALESCE(supplied_script_version, ''::character varying))::text) || ' '::text) || (COALESCE(docker_image_locator, ''::character varying))::text) || ' '::text) || (COALESCE(description, ''::character varying))::text) || ' '::text) || (COALESCE(state, ''::character varying))::text) || ' '::text) || (COALESCE(arvados_sdk_version, ''::character varying))::text) || ' '::text) || COALESCE(components, ''::text)))); +CREATE INDEX jobs_full_text_search_idx ON public.jobs USING gin (to_tsvector('english'::regconfig, substr((((((((((((((((((((((((((((((((((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(submit_id, ''::character varying))::text) || ' '::text) || (COALESCE(script, ''::character varying))::text) || ' '::text) || (COALESCE(script_version, ''::character varying))::text) || ' '::text) || COALESCE(script_parameters, ''::text)) || ' '::text) || (COALESCE(cancelled_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(cancelled_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(output, ''::character varying))::text) || ' '::text) || (COALESCE(is_locked_by_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(log, ''::character varying))::text) || ' '::text) || COALESCE(tasks_summary, ''::text)) || ' '::text) || COALESCE(runtime_constraints, ''::text)) || ' '::text) || (COALESCE(repository, ''::character varying))::text) || ' '::text) || (COALESCE(supplied_script_version, ''::character varying))::text) || ' '::text) || (COALESCE(docker_image_locator, ''::character varying))::text) || ' '::text) || (COALESCE(description, ''::character varying))::text) || ' '::text) || (COALESCE(state, ''::character varying))::text) || ' '::text) || (COALESCE(arvados_sdk_version, ''::character varying))::text) || ' '::text) || COALESCE(components, ''::text)), 0, 1000000))); -- @@ -2715,7 +2761,7 @@ CREATE INDEX permission_target_user_trashed_level ON public.materialized_permiss -- Name: pipeline_instances_full_text_search_idx; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX pipeline_instances_full_text_search_idx ON public.pipeline_instances USING gin (to_tsvector('english'::regconfig, (((((((((((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(pipeline_template_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || COALESCE(components, ''::text)) || ' '::text) || COALESCE(properties, ''::text)) || ' '::text) || (COALESCE(state, ''::character varying))::text) || ' '::text) || COALESCE(components_summary, ''::text)) || ' '::text) || (COALESCE(description, ''::character varying))::text))); +CREATE INDEX pipeline_instances_full_text_search_idx ON public.pipeline_instances USING gin (to_tsvector('english'::regconfig, substr((((((((((((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(pipeline_template_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || COALESCE(components, ''::text)) || ' '::text) || COALESCE(properties, ''::text)) || ' '::text) || (COALESCE(state, ''::character varying))::text) || ' '::text) || COALESCE(components_summary, ''::text)) || ' '::text) || (COALESCE(description, ''::character varying))::text), 0, 1000000))); -- @@ -2736,7 +2782,7 @@ CREATE UNIQUE INDEX pipeline_template_owner_uuid_name_unique ON public.pipeline_ -- Name: pipeline_templates_full_text_search_idx; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX pipeline_templates_full_text_search_idx ON public.pipeline_templates USING gin (to_tsvector('english'::regconfig, (((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || COALESCE(components, ''::text)) || ' '::text) || (COALESCE(description, ''::character varying))::text))); +CREATE INDEX pipeline_templates_full_text_search_idx ON public.pipeline_templates USING gin (to_tsvector('english'::regconfig, substr((((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || COALESCE(components, ''::text)) || ' '::text) || (COALESCE(description, ''::character varying))::text), 0, 1000000))); -- @@ -2792,7 +2838,7 @@ CREATE INDEX virtual_machines_search_index ON public.virtual_machines USING btre -- Name: workflows_full_text_search_idx; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX workflows_full_text_search_idx ON public.workflows USING gin (to_tsvector('english'::regconfig, (((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || COALESCE(description, ''::text)))); +CREATE INDEX workflows_full_text_search_idx ON public.workflows USING gin (to_tsvector('english'::regconfig, substr((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || COALESCE(description, ''::text)), 0, 1000000))); -- @@ -3142,7 +3188,39 @@ INSERT INTO schema_migrations (version) VALUES ('20180608123145'); INSERT INTO schema_migrations (version) VALUES ('20180806133039'); +INSERT INTO schema_migrations (version) VALUES ('20180820130357'); + INSERT INTO schema_migrations (version) VALUES ('20180820132617'); +INSERT INTO schema_migrations (version) VALUES ('20180820135808'); + +INSERT INTO schema_migrations (version) VALUES ('20180824152014'); + INSERT INTO schema_migrations (version) VALUES ('20180824155207'); +INSERT INTO schema_migrations (version) VALUES ('20180904110712'); + +INSERT INTO schema_migrations (version) VALUES ('20180913175443'); + +INSERT INTO schema_migrations (version) VALUES ('20180915155335'); + +INSERT INTO schema_migrations (version) VALUES ('20180917200000'); + +INSERT INTO schema_migrations (version) VALUES ('20180917205609'); + +INSERT INTO schema_migrations (version) VALUES ('20180919001158'); + +INSERT INTO schema_migrations (version) VALUES ('20181001175023'); + +INSERT INTO schema_migrations (version) VALUES ('20181004131141'); + +INSERT INTO schema_migrations (version) VALUES ('20181005192222'); + +INSERT INTO schema_migrations (version) VALUES ('20181011184200'); + +INSERT INTO schema_migrations (version) VALUES ('20181213183234'); + +INSERT INTO schema_migrations (version) VALUES ('20190214214814'); + +INSERT INTO schema_migrations (version) VALUES ('20190322174136'); +