permission (permission origin is self).
*/
perm_from_start(perm_origin_uuid, target_uuid, val, traverse_owned) as (
-
+
WITH RECURSIVE
traverse_graph(origin_uuid, target_uuid, val, traverse_owned, starting_set) as (
-
+
values (perm_origin_uuid, starting_uuid, starting_perm,
should_traverse_owned(starting_uuid, starting_perm),
(perm_origin_uuid = starting_uuid or starting_uuid not like '_____-tpzed-_______________'))
can_manage permission granted by ownership.
*/
additional_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
-
+
WITH RECURSIVE
traverse_graph(origin_uuid, target_uuid, val, traverse_owned, starting_set) as (
-
+
select edges.tail_uuid as origin_uuid, edges.head_uuid as target_uuid, edges.val,
should_traverse_owned(edges.head_uuid, edges.val),
edges.head_uuid like '_____-j7d0g-_______________'
The "inherited" priority comes from the path we followed from the root, the parent container
priority hasn't been updated in the table yet but we need to behave it like it has been.
*/
-select coalesce(max(case when container_requests.priority = 0 then 0
- when containers.uuid = inherited_from then inherited
+select coalesce(max(case when containers.uuid = inherited_from then inherited
when containers.priority is not NULL then containers.priority
else container_requests.priority * 1125899906842624::bigint - (extract(epoch from container_requests.created_at)*1000)::bigint
end), 0) from
container_requests left outer join containers on container_requests.requesting_container_uuid = containers.uuid
- where container_requests.container_uuid = for_container_uuid and container_requests.state = 'Committed' and container_requests.priority > 0;
+ where container_requests.container_uuid = for_container_uuid and
+ container_requests.state = 'Committed' and
+ container_requests.priority > 0 and
+ container_requests.owner_uuid not in (select group_uuid from trashed_groups);
$$;
$$;
+--
+-- Name: container_tree_priorities(character varying); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION public.container_tree_priorities(for_container_uuid character varying) RETURNS TABLE(pri_container_uuid character varying, upd_priority bigint)
+ LANGUAGE sql
+ AS $$
+/* Calculate the priorities of all containers starting from for_container_uuid.
+ This traverses the process tree downward and calls container_priority for each container
+ and returns a table of container uuids and their new priorities.
+*/
+with recursive tab(upd_container_uuid, upd_priority) as (
+ select for_container_uuid, container_priority(for_container_uuid, 0, '')
+union
+ select containers.uuid, container_priority(containers.uuid, child_requests.upd_priority, child_requests.upd_container_uuid)
+ from (tab join container_requests on tab.upd_container_uuid = container_requests.requesting_container_uuid) as child_requests
+ join containers on child_requests.container_uuid = containers.uuid
+ where containers.state in ('Queued', 'Locked', 'Running')
+)
+select upd_container_uuid, upd_priority from tab;
+$$;
+
+
+--
+-- Name: jsonb_exists_all_inline_op(jsonb, text[]); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION public.jsonb_exists_all_inline_op(jsonb, text[]) RETURNS boolean
+ LANGUAGE sql IMMUTABLE
+ AS $_$SELECT $1 ?& $2$_$;
+
+
+--
+-- Name: jsonb_exists_inline_op(jsonb, text); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION public.jsonb_exists_inline_op(jsonb, text) RETURNS boolean
+ LANGUAGE sql IMMUTABLE
+ AS $_$SELECT $1 ? $2$_$;
+
+
--
-- Name: project_subtree_with_is_frozen(character varying, boolean); Type: FUNCTION; Schema: public; Owner: -
--
$$;
---
--- Name: update_priorities(character varying); Type: FUNCTION; Schema: public; Owner: -
---
-
-CREATE FUNCTION public.update_priorities(for_container_uuid character varying) RETURNS TABLE(pri_container_uuid character varying, upd_priority bigint)
- LANGUAGE sql
- AS $$
-/* Calculate the priorities of all containers starting from for_container_uuid.
- This traverses the process tree downward and calls container_priority for each container
- and returns a table of container uuids and their new priorities.
-*/
-with recursive tab(upd_container_uuid, upd_priority) as (
- select for_container_uuid, container_priority(for_container_uuid, 0, '')
-union
- select containers.uuid, container_priority(containers.uuid, child_requests.upd_priority, child_requests.upd_container_uuid)
- from (tab join container_requests on tab.upd_container_uuid = container_requests.requesting_container_uuid) as child_requests
- join containers on child_requests.container_uuid = containers.uuid
- where containers.state in ('Queued', 'Locked', 'Running')
-)
-select upd_container_uuid, upd_priority from tab;
-$$;
-
-
SET default_tablespace = '';
SET default_with_oids = false;
runtime_token text,
output_storage_classes jsonb DEFAULT '["default"]'::jsonb,
output_properties jsonb DEFAULT '{}'::jsonb,
- cumulative_cost double precision DEFAULT 0.0 NOT NULL
+ cumulative_cost double precision DEFAULT 0.0 NOT NULL,
+ output_glob text DEFAULT '[]'::text
);
output_storage_classes jsonb DEFAULT '["default"]'::jsonb,
output_properties jsonb DEFAULT '{}'::jsonb,
cost double precision DEFAULT 0.0 NOT NULL,
- subrequests_cost double precision DEFAULT 0.0 NOT NULL
+ subrequests_cost double precision DEFAULT 0.0 NOT NULL,
+ output_glob text DEFAULT '[]'::text
);
CREATE INDEX collections_trgm_text_search_idx ON public.collections USING gin (((((((((((((((((((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))) public.gin_trgm_ops);
+--
+-- 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, 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) || ' '::text) || COALESCE((output_properties)::text, ''::text)) || ' '::text) || COALESCE(output_glob, ''::text)), 0, 8000)));
+
+
--
-- Name: container_requests_index_on_properties; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_collections_on_name ON public.collections USING gin (name public.gin_trgm_ops);
+--
+-- Name: index_collections_on_name_btree; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX index_collections_on_name_btree ON public.collections USING btree (name);
+
+
--
-- Name: index_collections_on_owner_uuid; Type: INDEX; Schema: public; Owner: -
--
-- 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));
+CREATE INDEX index_containers_on_reuse_columns ON public.containers USING btree (md5(command), cwd, md5(environment), output_path, md5(output_glob), container_image, md5(mounts), secret_mounts_md5, md5(runtime_constraints));
--
CREATE INDEX index_groups_on_name ON public.groups USING gin (name public.gin_trgm_ops);
+--
+-- Name: index_groups_on_name_btree; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX index_groups_on_name_btree ON public.groups USING btree (name);
+
+
--
-- Name: index_groups_on_owner_uuid; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX index_logs_on_uuid ON public.logs USING btree (uuid);
+--
+-- Name: index_materialized_permissions_target_is_not_user; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX index_materialized_permissions_target_is_not_user ON public.materialized_permissions USING btree (target_uuid, traverse_owned, ((((user_uuid)::text = (target_uuid)::text) OR ((target_uuid)::text !~~ '_____-tpzed-_______________'::text))));
+
+
--
-- Name: index_nodes_on_created_at; Type: INDEX; Schema: public; Owner: -
--
('20221219165512'),
('20221230155924'),
('20230421142716'),
-('20230503224107');
-
-
+('20230503224107'),
+('20230815160000'),
+('20230821000000'),
+('20230922000000'),
+('20231013000000'),
+('20240329173437'),
+('20240402162733');