X-Git-Url: https://git.arvados.org/arvados.git/blobdiff_plain/a31587cba5c2c38e0fc4f91981baf8bf2605664c..7407f41105f8000bb3908d41a31daaf3a30d9440:/services/api/db/structure.sql diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql index 5b579bd39e..83987d0518 100644 --- a/services/api/db/structure.sql +++ b/services/api/db/structure.sql @@ -7,6 +7,7 @@ SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodies = false; +SET xmloption = content; SET client_min_messages = warning; -- @@ -23,6 +24,230 @@ CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; +-- +-- Name: pg_trgm; Type: EXTENSION; Schema: -; Owner: - +-- + +CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; + + +-- +-- Name: EXTENSION pg_trgm; Type: COMMENT; Schema: -; Owner: - +-- + +-- COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams'; + + +-- +-- Name: compute_permission_subgraph(character varying, character varying, integer, character varying); Type: FUNCTION; Schema: public; Owner: - +-- + +CREATE FUNCTION public.compute_permission_subgraph(perm_origin_uuid character varying, starting_uuid character varying, starting_perm integer, perm_edge_id character varying) RETURNS TABLE(user_uuid character varying, target_uuid character varying, val integer, traverse_owned boolean) + LANGUAGE sql STABLE + AS $$ + +/* The purpose of this function is to compute the permissions for a + subgraph of the database, starting from a given edge. The newly + computed permissions are used to add and remove rows from the main + permissions table. + + perm_origin_uuid: The object that 'gets' the permission. + + starting_uuid: The starting object the permission applies to. + + starting_perm: The permission that perm_origin_uuid 'has' on + starting_uuid One of 1, 2, 3 for can_read, + can_write, can_manage respectively, or 0 to revoke + permissions. + + perm_edge_id: Identifies the permission edge that is being updated. + Changes of ownership, this is starting_uuid. + For links, this is the uuid of the link object. + This is used to override the edge value in the database + with starting_perm. This is necessary when revoking + permissions because the update happens before edge is + actually removed. +*/ +with + /* Starting from starting_uuid, determine the set of objects that + could be affected by this permission change. + + Note: We don't traverse users unless it is an "identity" + 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-_______________')) + + union + (select traverse_graph.origin_uuid, + edges.head_uuid, + least( +case (edges.edge_id = perm_edge_id) + when true then starting_perm + else edges.val + end +, + traverse_graph.val), + should_traverse_owned(edges.head_uuid, edges.val), + false + from permission_graph_edges as edges, traverse_graph + where traverse_graph.target_uuid = edges.tail_uuid + and (edges.tail_uuid like '_____-j7d0g-_______________' or + traverse_graph.starting_set))) + select traverse_graph.origin_uuid, target_uuid, max(val) as val, bool_or(traverse_owned) as traverse_owned from traverse_graph + group by (traverse_graph.origin_uuid, target_uuid) +), + + /* Find other inbound edges that grant permissions to 'targets' in + perm_from_start, and compute permissions that originate from + those. + + This is necessary for two reasons: + + 1) Other users may have access to a subset of the objects + through other permission links than the one we started from. + If we don't recompute them, their permission will get dropped. + + 2) There may be more than one path through which a user gets + permission to an object. For example, a user owns a project + and also shares it can_read with a group the user belongs + to. adding the can_read link must not overwrite the existing + 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-_______________' + from permission_graph_edges as edges + where edges.edge_id != perm_edge_id and + edges.tail_uuid not in (select target_uuid from perm_from_start where target_uuid like '_____-j7d0g-_______________') and + edges.head_uuid in (select target_uuid from perm_from_start) + + union + (select traverse_graph.origin_uuid, + edges.head_uuid, + least( +case (edges.edge_id = perm_edge_id) + when true then starting_perm + else edges.val + end +, + traverse_graph.val), + should_traverse_owned(edges.head_uuid, edges.val), + false + from permission_graph_edges as edges, traverse_graph + where traverse_graph.target_uuid = edges.tail_uuid + and (edges.tail_uuid like '_____-j7d0g-_______________' or + traverse_graph.starting_set))) + select traverse_graph.origin_uuid, target_uuid, max(val) as val, bool_or(traverse_owned) as traverse_owned from traverse_graph + group by (traverse_graph.origin_uuid, target_uuid) +), + + /* Combine the permissions computed in the first two phases. */ + all_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as ( + select * from perm_from_start + union all + select * from additional_perms + ) + + /* The actual query that produces rows to be added or removed + from the materialized_permissions table. This is the clever + bit. + + Key insights: + + * For every group, the materialized_permissions lists all users + that can access to that group. + + * The all_perms subquery has computed permissions on on a set of + objects for all inbound "origins", which are users or groups. + + * Permissions through groups are transitive. + + We can infer: + + 1) The materialized_permissions table declares that user X has permission N on group Y + 2) The all_perms result has determined group Y has permission M on object Z + 3) Therefore, user X has permission min(N, M) on object Z + + This allows us to efficiently determine the set of users that + have permissions on the subset of objects, without having to + follow the chain of permission back up to find those users. + + In addition, because users always have permission on themselves, this + query also makes sure those permission rows are always + returned. + */ + select v.user_uuid, v.target_uuid, max(v.perm_level), bool_or(v.traverse_owned) from + (select m.user_uuid, + u.target_uuid, + least(u.val, m.perm_level) as perm_level, + u.traverse_owned + from all_perms as u, materialized_permissions as m + where u.perm_origin_uuid = m.target_uuid AND m.traverse_owned + AND (m.user_uuid = m.target_uuid or m.target_uuid not like '_____-tpzed-_______________') + union all + select target_uuid as user_uuid, target_uuid, 3, true + from all_perms + where all_perms.target_uuid like '_____-tpzed-_______________') as v + group by v.user_uuid, v.target_uuid +$$; + + +-- +-- Name: project_subtree_with_trash_at(character varying, timestamp without time zone); Type: FUNCTION; Schema: public; Owner: - +-- + +CREATE FUNCTION public.project_subtree_with_trash_at(starting_uuid character varying, starting_trash_at timestamp without time zone) RETURNS TABLE(target_uuid character varying, trash_at timestamp without time zone) + LANGUAGE sql STABLE + AS $$ +/* Starting from a project, recursively traverse all the projects + underneath it and return a set of project uuids and trash_at times + (may be null). The initial trash_at can be a timestamp or null. + The trash_at time propagates downward to groups it owns, i.e. when a + group is trashed, everything underneath it in the ownership + hierarchy is also considered trashed. However, this is fact is + recorded in the trashed_groups table, not by updating trash_at field + in the groups table. +*/ +WITH RECURSIVE + project_subtree(uuid, trash_at) as ( + values (starting_uuid, starting_trash_at) + union + select groups.uuid, LEAST(project_subtree.trash_at, groups.trash_at) + from groups join project_subtree on (groups.owner_uuid = project_subtree.uuid) + ) + select uuid, trash_at from project_subtree; +$$; + + +-- +-- Name: should_traverse_owned(character varying, integer); Type: FUNCTION; Schema: public; Owner: - +-- + +CREATE FUNCTION public.should_traverse_owned(starting_uuid character varying, starting_perm integer) RETURNS boolean + LANGUAGE sql IMMUTABLE + AS $$ +/* Helper function. Determines if permission on an object implies + transitive permission to things the object owns. This is always + true for groups, but only true for users when the permission level + is can_manage. +*/ +select starting_uuid like '_____-j7d0g-_______________' or + (starting_uuid like '_____-tpzed-_______________' and starting_perm >= 3); +$$; + + SET default_tablespace = ''; SET default_with_oids = false; @@ -105,6 +330,18 @@ CREATE SEQUENCE public.api_clients_id_seq ALTER SEQUENCE public.api_clients_id_seq OWNED BY public.api_clients.id; +-- +-- Name: ar_internal_metadata; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE public.ar_internal_metadata ( + key character varying NOT NULL, + value character varying, + created_at timestamp without time zone NOT NULL, + updated_at timestamp without time zone NOT NULL +); + + -- -- Name: authorized_keys; Type: TABLE; Schema: public; Owner: - -- @@ -175,7 +412,9 @@ CREATE TABLE public.collections ( storage_classes_confirmed_at timestamp without time zone, current_version_uuid character varying, version integer DEFAULT 1 NOT NULL, - preserve_version boolean DEFAULT false + preserve_version boolean DEFAULT false, + file_count integer DEFAULT 0 NOT NULL, + file_size_total bigint DEFAULT 0 NOT NULL ); @@ -198,73 +437,6 @@ CREATE SEQUENCE public.collections_id_seq ALTER SEQUENCE public.collections_id_seq OWNED BY public.collections.id; --- --- Name: commit_ancestors; Type: TABLE; Schema: public; Owner: - --- - -CREATE TABLE public.commit_ancestors ( - id integer NOT NULL, - repository_name character varying(255), - descendant character varying(255) NOT NULL, - ancestor character varying(255) NOT NULL, - "is" boolean DEFAULT false NOT NULL, - created_at timestamp without time zone NOT NULL, - updated_at timestamp without time zone NOT NULL -); - - --- --- Name: commit_ancestors_id_seq; Type: SEQUENCE; Schema: public; Owner: - --- - -CREATE SEQUENCE public.commit_ancestors_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - - --- --- Name: commit_ancestors_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - --- - -ALTER SEQUENCE public.commit_ancestors_id_seq OWNED BY public.commit_ancestors.id; - - --- --- Name: commits; Type: TABLE; Schema: public; Owner: - --- - -CREATE TABLE public.commits ( - id integer NOT NULL, - repository_name character varying(255), - sha1 character varying(255), - message character varying(255), - created_at timestamp without time zone NOT NULL, - updated_at timestamp without time zone NOT NULL -); - - --- --- Name: commits_id_seq; Type: SEQUENCE; Schema: public; Owner: - --- - -CREATE SEQUENCE public.commits_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - - --- --- Name: commits_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - --- - -ALTER SEQUENCE public.commits_id_seq OWNED BY public.commits.id; - - -- -- Name: container_requests; Type: TABLE; Schema: public; Owner: - -- @@ -302,7 +474,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 ); @@ -358,7 +531,11 @@ CREATE TABLE public.containers ( scheduling_parameters text, secret_mounts jsonb DEFAULT '{}'::jsonb, secret_mounts_md5 character varying DEFAULT '99914b932bd37a50b983c5e7c90ae93b'::character varying, - runtime_status jsonb DEFAULT '{}'::jsonb + runtime_status jsonb DEFAULT '{}'::jsonb, + runtime_user_uuid text, + runtime_auth_scopes jsonb, + runtime_token text, + lock_count integer DEFAULT 0 NOT NULL ); @@ -752,93 +929,17 @@ ALTER SEQUENCE public.logs_id_seq OWNED BY public.logs.id; -- --- Name: users; Type: TABLE; Schema: public; Owner: - +-- Name: materialized_permissions; Type: TABLE; Schema: public; Owner: - -- -CREATE TABLE public.users ( - id integer NOT NULL, - uuid character varying(255), - owner_uuid character varying(255) NOT NULL, - created_at timestamp without time zone NOT NULL, - modified_by_client_uuid character varying(255), - modified_by_user_uuid character varying(255), - modified_at timestamp without time zone, - email character varying(255), - first_name character varying(255), - last_name character varying(255), - identity_url character varying(255), - is_admin boolean, - prefs text, - updated_at timestamp without time zone NOT NULL, - default_owner_uuid character varying(255), - is_active boolean DEFAULT false, - username character varying(255), - redirect_to_user_uuid character varying +CREATE TABLE public.materialized_permissions ( + user_uuid character varying, + target_uuid character varying, + perm_level integer, + traverse_owned boolean ); --- --- Name: materialized_permission_view; Type: MATERIALIZED VIEW; Schema: public; Owner: - --- - -CREATE MATERIALIZED VIEW public.materialized_permission_view AS - WITH RECURSIVE perm_value(name, val) AS ( - VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3) - ), perm_edges(tail_uuid, head_uuid, val, follow, trashed) AS ( - SELECT links.tail_uuid, - links.head_uuid, - pv.val, - ((pv.val = 3) OR (groups.uuid IS NOT NULL)) AS follow, - (0)::smallint AS trashed, - (0)::smallint AS followtrash - FROM ((public.links - LEFT JOIN perm_value pv ON ((pv.name = (links.name)::text))) - LEFT JOIN public.groups ON (((pv.val < 3) AND ((groups.uuid)::text = (links.head_uuid)::text)))) - WHERE ((links.link_class)::text = 'permission'::text) - UNION ALL - SELECT groups.owner_uuid, - groups.uuid, - 3, - true AS bool, - CASE - WHEN ((groups.trash_at IS NOT NULL) AND (groups.trash_at < clock_timestamp())) THEN 1 - ELSE 0 - END AS "case", - 1 - FROM public.groups - ), perm(val, follow, user_uuid, target_uuid, trashed) AS ( - SELECT (3)::smallint AS val, - true AS follow, - (users.uuid)::character varying(32) AS user_uuid, - (users.uuid)::character varying(32) AS target_uuid, - (0)::smallint AS trashed - FROM public.users - UNION - SELECT (LEAST((perm_1.val)::integer, edges.val))::smallint AS val, - edges.follow, - perm_1.user_uuid, - (edges.head_uuid)::character varying(32) AS target_uuid, - ((GREATEST((perm_1.trashed)::integer, edges.trashed) * edges.followtrash))::smallint AS trashed - FROM (perm perm_1 - JOIN perm_edges edges ON ((perm_1.follow AND ((edges.tail_uuid)::text = (perm_1.target_uuid)::text)))) - ) - SELECT perm.user_uuid, - perm.target_uuid, - max(perm.val) AS perm_level, - CASE perm.follow - WHEN true THEN perm.target_uuid - ELSE NULL::character varying - END AS target_owner_uuid, - max(perm.trashed) AS trashed - FROM perm - GROUP BY perm.user_uuid, perm.target_uuid, - CASE perm.follow - WHEN true THEN perm.target_uuid - ELSE NULL::character varying - END - WITH NO DATA; - - -- -- Name: nodes; Type: TABLE; Schema: public; Owner: - -- @@ -884,31 +985,66 @@ ALTER SEQUENCE public.nodes_id_seq OWNED BY public.nodes.id; -- --- Name: permission_refresh_lock; Type: TABLE; Schema: public; Owner: - +-- Name: users; Type: TABLE; Schema: public; Owner: - -- -CREATE TABLE public.permission_refresh_lock ( - id integer NOT NULL +CREATE TABLE public.users ( + id integer NOT NULL, + uuid character varying(255), + owner_uuid character varying(255) NOT NULL, + created_at timestamp without time zone NOT NULL, + modified_by_client_uuid character varying(255), + modified_by_user_uuid character varying(255), + modified_at timestamp without time zone, + email character varying(255), + first_name character varying(255), + last_name character varying(255), + identity_url character varying(255), + is_admin boolean, + prefs text, + updated_at timestamp without time zone NOT NULL, + default_owner_uuid character varying(255), + is_active boolean DEFAULT false, + username character varying(255), + redirect_to_user_uuid character varying ); -- --- Name: permission_refresh_lock_id_seq; Type: SEQUENCE; Schema: public; Owner: - --- - -CREATE SEQUENCE public.permission_refresh_lock_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - - --- --- Name: permission_refresh_lock_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - --- - -ALTER SEQUENCE public.permission_refresh_lock_id_seq OWNED BY public.permission_refresh_lock.id; +-- Name: permission_graph_edges; Type: VIEW; Schema: public; Owner: - +-- + +CREATE VIEW public.permission_graph_edges AS + SELECT groups.owner_uuid AS tail_uuid, + groups.uuid AS head_uuid, + 3 AS val, + groups.uuid AS edge_id + FROM public.groups +UNION ALL + SELECT users.owner_uuid AS tail_uuid, + users.uuid AS head_uuid, + 3 AS val, + users.uuid AS edge_id + FROM public.users +UNION ALL + SELECT users.uuid AS tail_uuid, + users.uuid AS head_uuid, + 3 AS val, + ''::character varying AS edge_id + FROM public.users +UNION ALL + SELECT links.tail_uuid, + links.head_uuid, + CASE + WHEN ((links.name)::text = 'can_read'::text) THEN 1 + WHEN ((links.name)::text = 'can_login'::text) THEN 1 + WHEN ((links.name)::text = 'can_write'::text) THEN 2 + WHEN ((links.name)::text = 'can_manage'::text) THEN 3 + ELSE 0 + END AS val, + links.uuid AS edge_id + FROM public.links + WHERE ((links.link_class)::text = 'permission'::text); -- @@ -1112,6 +1248,16 @@ CREATE SEQUENCE public.traits_id_seq ALTER SEQUENCE public.traits_id_seq OWNED BY public.traits.id; +-- +-- Name: trashed_groups; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE public.trashed_groups ( + group_uuid character varying, + trash_at timestamp without time zone +); + + -- -- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- @@ -1233,20 +1379,6 @@ ALTER TABLE ONLY public.authorized_keys ALTER COLUMN id SET DEFAULT nextval('pub ALTER TABLE ONLY public.collections ALTER COLUMN id SET DEFAULT nextval('public.collections_id_seq'::regclass); --- --- Name: commit_ancestors id; Type: DEFAULT; Schema: public; Owner: - --- - -ALTER TABLE ONLY public.commit_ancestors ALTER COLUMN id SET DEFAULT nextval('public.commit_ancestors_id_seq'::regclass); - - --- --- Name: commits id; Type: DEFAULT; Schema: public; Owner: - --- - -ALTER TABLE ONLY public.commits ALTER COLUMN id SET DEFAULT nextval('public.commits_id_seq'::regclass); - - -- -- Name: container_requests id; Type: DEFAULT; Schema: public; Owner: - -- @@ -1324,13 +1456,6 @@ ALTER TABLE ONLY public.logs ALTER COLUMN id SET DEFAULT nextval('public.logs_id ALTER TABLE ONLY public.nodes ALTER COLUMN id SET DEFAULT nextval('public.nodes_id_seq'::regclass); --- --- Name: permission_refresh_lock id; Type: DEFAULT; Schema: public; Owner: - --- - -ALTER TABLE ONLY public.permission_refresh_lock ALTER COLUMN id SET DEFAULT nextval('public.permission_refresh_lock_id_seq'::regclass); - - -- -- Name: pipeline_instances id; Type: DEFAULT; Schema: public; Owner: - -- @@ -1403,6 +1528,14 @@ ALTER TABLE ONLY public.api_clients ADD CONSTRAINT api_clients_pkey PRIMARY KEY (id); +-- +-- Name: ar_internal_metadata ar_internal_metadata_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.ar_internal_metadata + ADD CONSTRAINT ar_internal_metadata_pkey PRIMARY KEY (key); + + -- -- Name: authorized_keys authorized_keys_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- @@ -1419,22 +1552,6 @@ ALTER TABLE ONLY public.collections ADD CONSTRAINT collections_pkey PRIMARY KEY (id); --- --- Name: commit_ancestors commit_ancestors_pkey; Type: CONSTRAINT; Schema: public; Owner: - --- - -ALTER TABLE ONLY public.commit_ancestors - ADD CONSTRAINT commit_ancestors_pkey PRIMARY KEY (id); - - --- --- Name: commits commits_pkey; Type: CONSTRAINT; Schema: public; Owner: - --- - -ALTER TABLE ONLY public.commits - ADD CONSTRAINT commits_pkey PRIMARY KEY (id); - - -- -- Name: container_requests container_requests_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- @@ -1523,14 +1640,6 @@ ALTER TABLE ONLY public.nodes ADD CONSTRAINT nodes_pkey PRIMARY KEY (id); --- --- Name: permission_refresh_lock permission_refresh_lock_pkey; Type: CONSTRAINT; Schema: public; Owner: - --- - -ALTER TABLE ONLY public.permission_refresh_lock - ADD CONSTRAINT permission_refresh_lock_pkey PRIMARY KEY (id); - - -- -- Name: pipeline_instances pipeline_instances_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- @@ -1627,7 +1736,7 @@ 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))); -- @@ -1637,11 +1746,18 @@ CREATE INDEX collections_full_text_search_idx ON public.collections USING gin (t 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: collections_trgm_text_search_idx; Type: INDEX; Schema: public; Owner: - +-- + +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, (((((((((((((((((((((((((((((((((((((((((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))); -- @@ -1658,6 +1774,13 @@ CREATE INDEX container_requests_index_on_properties ON public.container_requests CREATE INDEX container_requests_search_index ON public.container_requests USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, name, state, requesting_container_uuid, container_uuid, container_image, cwd, output_path, output_uuid, log_uuid, output_name); +-- +-- Name: container_requests_trgm_text_search_idx; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX container_requests_trgm_text_search_idx ON public.container_requests USING gin (((((((((((((((((((((((((((((((((((((((((((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)) public.gin_trgm_ops); + + -- -- Name: containers_search_index; Type: INDEX; Schema: public; Owner: - -- @@ -1676,7 +1799,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))); -- @@ -1686,6 +1809,13 @@ CREATE INDEX groups_full_text_search_idx ON public.groups USING gin (to_tsvector CREATE INDEX groups_search_index ON public.groups USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, name, group_class); +-- +-- Name: groups_trgm_text_search_idx; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX groups_trgm_text_search_idx ON public.groups USING gin (((((((((((((((((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))) public.gin_trgm_ops); + + -- -- Name: humans_search_index; Type: INDEX; Schema: public; Owner: - -- @@ -1854,20 +1984,6 @@ CREATE INDEX index_collections_on_trash_at ON public.collections USING btree (tr CREATE UNIQUE INDEX index_collections_on_uuid ON public.collections USING btree (uuid); --- --- Name: index_commit_ancestors_on_descendant_and_ancestor; Type: INDEX; Schema: public; Owner: - --- - -CREATE UNIQUE INDEX index_commit_ancestors_on_descendant_and_ancestor ON public.commit_ancestors USING btree (descendant, ancestor); - - --- --- Name: index_commits_on_repository_name_and_sha1; Type: INDEX; Schema: public; Owner: - --- - -CREATE UNIQUE INDEX index_commits_on_repository_name_and_sha1 ON public.commits USING btree (repository_name, sha1); - - -- -- Name: index_container_requests_on_container_uuid; Type: INDEX; Schema: public; Owner: - -- @@ -2274,6 +2390,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: - -- @@ -2547,6 +2677,13 @@ CREATE INDEX index_traits_on_owner_uuid ON public.traits USING btree (owner_uuid CREATE UNIQUE INDEX index_traits_on_uuid ON public.traits USING btree (uuid); +-- +-- Name: index_trashed_groups_on_group_uuid; Type: INDEX; Schema: public; Owner: - +-- + +CREATE UNIQUE INDEX index_trashed_groups_on_group_uuid ON public.trashed_groups USING btree (group_uuid); + + -- -- Name: index_users_on_created_at; Type: INDEX; Schema: public; Owner: - -- @@ -2554,6 +2691,13 @@ CREATE UNIQUE INDEX index_traits_on_uuid ON public.traits USING btree (uuid); CREATE INDEX index_users_on_created_at ON public.users USING btree (created_at); +-- +-- Name: index_users_on_identity_url; Type: INDEX; Schema: public; Owner: - +-- + +CREATE UNIQUE INDEX index_users_on_identity_url ON public.users USING btree (identity_url); + + -- -- Name: index_users_on_modified_at; Type: INDEX; Schema: public; Owner: - -- @@ -2649,7 +2793,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))); -- @@ -2659,6 +2803,13 @@ CREATE INDEX jobs_full_text_search_idx ON public.jobs USING gin (to_tsvector('en CREATE INDEX jobs_search_index ON public.jobs USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, submit_id, script, script_version, cancelled_by_client_uuid, cancelled_by_user_uuid, output, is_locked_by_uuid, log, repository, supplied_script_version, docker_image_locator, state, arvados_sdk_version); +-- +-- Name: jobs_trgm_text_search_idx; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX jobs_trgm_text_search_idx ON public.jobs USING gin (((((((((((((((((((((((((((((((((((((((((((((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))) public.gin_trgm_ops); + + -- -- Name: keep_disks_search_index; Type: INDEX; Schema: public; Owner: - -- @@ -2723,24 +2874,24 @@ CREATE INDEX nodes_search_index ON public.nodes USING btree (uuid, owner_uuid, m -- --- Name: permission_target_trashed; Type: INDEX; Schema: public; Owner: - +-- Name: permission_target; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX permission_target_trashed ON public.materialized_permission_view USING btree (trashed, target_uuid); +CREATE INDEX permission_target ON public.materialized_permissions USING btree (target_uuid); -- --- Name: permission_target_user_trashed_level; Type: INDEX; Schema: public; Owner: - +-- Name: permission_user_target; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX permission_target_user_trashed_level ON public.materialized_permission_view USING btree (user_uuid, trashed, perm_level); +CREATE UNIQUE INDEX permission_user_target ON public.materialized_permissions USING btree (user_uuid, target_uuid); -- -- 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))); -- @@ -2750,6 +2901,13 @@ CREATE INDEX pipeline_instances_full_text_search_idx ON public.pipeline_instance CREATE INDEX pipeline_instances_search_index ON public.pipeline_instances USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, pipeline_template_uuid, name, state); +-- +-- Name: pipeline_instances_trgm_text_search_idx; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX pipeline_instances_trgm_text_search_idx ON public.pipeline_instances USING gin (((((((((((((((((((((((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)) public.gin_trgm_ops); + + -- -- Name: pipeline_template_owner_uuid_name_unique; Type: INDEX; Schema: public; Owner: - -- @@ -2761,7 +2919,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))); -- @@ -2771,6 +2929,13 @@ CREATE INDEX pipeline_templates_full_text_search_idx ON public.pipeline_template CREATE INDEX pipeline_templates_search_index ON public.pipeline_templates USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, name); +-- +-- Name: pipeline_templates_trgm_text_search_idx; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX pipeline_templates_trgm_text_search_idx ON public.pipeline_templates USING gin (((((((((((((((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)) public.gin_trgm_ops); + + -- -- Name: repositories_search_index; Type: INDEX; Schema: public; Owner: - -- @@ -2817,7 +2982,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))); -- @@ -2828,365 +2993,210 @@ CREATE INDEX workflows_search_idx ON public.workflows USING btree (uuid, owner_u -- --- PostgreSQL database dump complete +-- Name: workflows_trgm_text_search_idx; Type: INDEX; Schema: public; Owner: - -- -SET search_path TO "$user", public; - -INSERT INTO schema_migrations (version) VALUES ('20121016005009'); - -INSERT INTO schema_migrations (version) VALUES ('20130105203021'); - -INSERT INTO schema_migrations (version) VALUES ('20130105224358'); - -INSERT INTO schema_migrations (version) VALUES ('20130105224618'); - -INSERT INTO schema_migrations (version) VALUES ('20130107181109'); - -INSERT INTO schema_migrations (version) VALUES ('20130107212832'); - -INSERT INTO schema_migrations (version) VALUES ('20130109175700'); - -INSERT INTO schema_migrations (version) VALUES ('20130109220548'); - -INSERT INTO schema_migrations (version) VALUES ('20130113214204'); - -INSERT INTO schema_migrations (version) VALUES ('20130116024233'); - -INSERT INTO schema_migrations (version) VALUES ('20130116215213'); - -INSERT INTO schema_migrations (version) VALUES ('20130118002239'); - -INSERT INTO schema_migrations (version) VALUES ('20130122020042'); - -INSERT INTO schema_migrations (version) VALUES ('20130122201442'); - -INSERT INTO schema_migrations (version) VALUES ('20130122221616'); - -INSERT INTO schema_migrations (version) VALUES ('20130123174514'); - -INSERT INTO schema_migrations (version) VALUES ('20130123180224'); - -INSERT INTO schema_migrations (version) VALUES ('20130123180228'); - -INSERT INTO schema_migrations (version) VALUES ('20130125220425'); - -INSERT INTO schema_migrations (version) VALUES ('20130128202518'); - -INSERT INTO schema_migrations (version) VALUES ('20130128231343'); - -INSERT INTO schema_migrations (version) VALUES ('20130130205749'); - -INSERT INTO schema_migrations (version) VALUES ('20130203104818'); - -INSERT INTO schema_migrations (version) VALUES ('20130203104824'); - -INSERT INTO schema_migrations (version) VALUES ('20130203115329'); - -INSERT INTO schema_migrations (version) VALUES ('20130207195855'); - -INSERT INTO schema_migrations (version) VALUES ('20130218181504'); - -INSERT INTO schema_migrations (version) VALUES ('20130226170000'); - -INSERT INTO schema_migrations (version) VALUES ('20130313175417'); - -INSERT INTO schema_migrations (version) VALUES ('20130315155820'); - -INSERT INTO schema_migrations (version) VALUES ('20130315183626'); - -INSERT INTO schema_migrations (version) VALUES ('20130315213205'); - -INSERT INTO schema_migrations (version) VALUES ('20130318002138'); - -INSERT INTO schema_migrations (version) VALUES ('20130319165853'); - -INSERT INTO schema_migrations (version) VALUES ('20130319180730'); - -INSERT INTO schema_migrations (version) VALUES ('20130319194637'); - -INSERT INTO schema_migrations (version) VALUES ('20130319201431'); - -INSERT INTO schema_migrations (version) VALUES ('20130319235957'); - -INSERT INTO schema_migrations (version) VALUES ('20130320000107'); - -INSERT INTO schema_migrations (version) VALUES ('20130326173804'); - -INSERT INTO schema_migrations (version) VALUES ('20130326182917'); - -INSERT INTO schema_migrations (version) VALUES ('20130415020241'); - -INSERT INTO schema_migrations (version) VALUES ('20130425024459'); - -INSERT INTO schema_migrations (version) VALUES ('20130425214427'); - -INSERT INTO schema_migrations (version) VALUES ('20130523060112'); - -INSERT INTO schema_migrations (version) VALUES ('20130523060213'); - -INSERT INTO schema_migrations (version) VALUES ('20130524042319'); - -INSERT INTO schema_migrations (version) VALUES ('20130528134100'); - -INSERT INTO schema_migrations (version) VALUES ('20130606183519'); - -INSERT INTO schema_migrations (version) VALUES ('20130608053730'); - -INSERT INTO schema_migrations (version) VALUES ('20130610202538'); - -INSERT INTO schema_migrations (version) VALUES ('20130611163736'); - -INSERT INTO schema_migrations (version) VALUES ('20130612042554'); - -INSERT INTO schema_migrations (version) VALUES ('20130617150007'); - -INSERT INTO schema_migrations (version) VALUES ('20130626002829'); - -INSERT INTO schema_migrations (version) VALUES ('20130626022810'); - -INSERT INTO schema_migrations (version) VALUES ('20130627154537'); - -INSERT INTO schema_migrations (version) VALUES ('20130627184333'); - -INSERT INTO schema_migrations (version) VALUES ('20130708163414'); - -INSERT INTO schema_migrations (version) VALUES ('20130708182912'); - -INSERT INTO schema_migrations (version) VALUES ('20130708185153'); - -INSERT INTO schema_migrations (version) VALUES ('20130724153034'); - -INSERT INTO schema_migrations (version) VALUES ('20131007180607'); - -INSERT INTO schema_migrations (version) VALUES ('20140117231056'); - -INSERT INTO schema_migrations (version) VALUES ('20140124222114'); - -INSERT INTO schema_migrations (version) VALUES ('20140129184311'); - -INSERT INTO schema_migrations (version) VALUES ('20140317135600'); - -INSERT INTO schema_migrations (version) VALUES ('20140319160547'); - -INSERT INTO schema_migrations (version) VALUES ('20140321191343'); - -INSERT INTO schema_migrations (version) VALUES ('20140324024606'); - -INSERT INTO schema_migrations (version) VALUES ('20140325175653'); - -INSERT INTO schema_migrations (version) VALUES ('20140402001908'); - -INSERT INTO schema_migrations (version) VALUES ('20140407184311'); - -INSERT INTO schema_migrations (version) VALUES ('20140421140924'); - -INSERT INTO schema_migrations (version) VALUES ('20140421151939'); - -INSERT INTO schema_migrations (version) VALUES ('20140421151940'); +CREATE INDEX workflows_trgm_text_search_idx ON public.workflows USING gin (((((((((((((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))) public.gin_trgm_ops); -INSERT INTO schema_migrations (version) VALUES ('20140422011506'); -INSERT INTO schema_migrations (version) VALUES ('20140423132913'); - -INSERT INTO schema_migrations (version) VALUES ('20140423133559'); - -INSERT INTO schema_migrations (version) VALUES ('20140501165548'); - -INSERT INTO schema_migrations (version) VALUES ('20140519205916'); - -INSERT INTO schema_migrations (version) VALUES ('20140527152921'); - -INSERT INTO schema_migrations (version) VALUES ('20140530200539'); - -INSERT INTO schema_migrations (version) VALUES ('20140601022548'); - -INSERT INTO schema_migrations (version) VALUES ('20140602143352'); - -INSERT INTO schema_migrations (version) VALUES ('20140607150616'); - -INSERT INTO schema_migrations (version) VALUES ('20140611173003'); - -INSERT INTO schema_migrations (version) VALUES ('20140627210837'); - -INSERT INTO schema_migrations (version) VALUES ('20140709172343'); - -INSERT INTO schema_migrations (version) VALUES ('20140714184006'); - -INSERT INTO schema_migrations (version) VALUES ('20140811184643'); - -INSERT INTO schema_migrations (version) VALUES ('20140817035914'); - -INSERT INTO schema_migrations (version) VALUES ('20140818125735'); - -INSERT INTO schema_migrations (version) VALUES ('20140826180337'); - -INSERT INTO schema_migrations (version) VALUES ('20140828141043'); - -INSERT INTO schema_migrations (version) VALUES ('20140909183946'); - -INSERT INTO schema_migrations (version) VALUES ('20140911221252'); - -INSERT INTO schema_migrations (version) VALUES ('20140918141529'); - -INSERT INTO schema_migrations (version) VALUES ('20140918153541'); - -INSERT INTO schema_migrations (version) VALUES ('20140918153705'); - -INSERT INTO schema_migrations (version) VALUES ('20140924091559'); - -INSERT INTO schema_migrations (version) VALUES ('20141111133038'); - -INSERT INTO schema_migrations (version) VALUES ('20141208164553'); - -INSERT INTO schema_migrations (version) VALUES ('20141208174553'); - -INSERT INTO schema_migrations (version) VALUES ('20141208174653'); - -INSERT INTO schema_migrations (version) VALUES ('20141208185217'); - -INSERT INTO schema_migrations (version) VALUES ('20150122175935'); - -INSERT INTO schema_migrations (version) VALUES ('20150123142953'); - -INSERT INTO schema_migrations (version) VALUES ('20150203180223'); - -INSERT INTO schema_migrations (version) VALUES ('20150206210804'); - -INSERT INTO schema_migrations (version) VALUES ('20150206230342'); - -INSERT INTO schema_migrations (version) VALUES ('20150216193428'); - -INSERT INTO schema_migrations (version) VALUES ('20150303210106'); - -INSERT INTO schema_migrations (version) VALUES ('20150312151136'); - -INSERT INTO schema_migrations (version) VALUES ('20150317132720'); - -INSERT INTO schema_migrations (version) VALUES ('20150324152204'); - -INSERT INTO schema_migrations (version) VALUES ('20150423145759'); - -INSERT INTO schema_migrations (version) VALUES ('20150512193020'); - -INSERT INTO schema_migrations (version) VALUES ('20150526180251'); - -INSERT INTO schema_migrations (version) VALUES ('20151202151426'); - -INSERT INTO schema_migrations (version) VALUES ('20151215134304'); - -INSERT INTO schema_migrations (version) VALUES ('20151229214707'); - -INSERT INTO schema_migrations (version) VALUES ('20160208210629'); - -INSERT INTO schema_migrations (version) VALUES ('20160209155729'); - -INSERT INTO schema_migrations (version) VALUES ('20160324144017'); - -INSERT INTO schema_migrations (version) VALUES ('20160506175108'); - -INSERT INTO schema_migrations (version) VALUES ('20160509143250'); - -INSERT INTO schema_migrations (version) VALUES ('20160808151559'); - -INSERT INTO schema_migrations (version) VALUES ('20160819195557'); - -INSERT INTO schema_migrations (version) VALUES ('20160819195725'); - -INSERT INTO schema_migrations (version) VALUES ('20160901210110'); - -INSERT INTO schema_migrations (version) VALUES ('20160909181442'); - -INSERT INTO schema_migrations (version) VALUES ('20160926194129'); - -INSERT INTO schema_migrations (version) VALUES ('20161019171346'); - -INSERT INTO schema_migrations (version) VALUES ('20161111143147'); - -INSERT INTO schema_migrations (version) VALUES ('20161115171221'); - -INSERT INTO schema_migrations (version) VALUES ('20161115174218'); - -INSERT INTO schema_migrations (version) VALUES ('20161213172944'); - -INSERT INTO schema_migrations (version) VALUES ('20161222153434'); - -INSERT INTO schema_migrations (version) VALUES ('20161223090712'); - -INSERT INTO schema_migrations (version) VALUES ('20170102153111'); - -INSERT INTO schema_migrations (version) VALUES ('20170105160301'); - -INSERT INTO schema_migrations (version) VALUES ('20170105160302'); - -INSERT INTO schema_migrations (version) VALUES ('20170216170823'); - -INSERT INTO schema_migrations (version) VALUES ('20170301225558'); - -INSERT INTO schema_migrations (version) VALUES ('20170319063406'); - -INSERT INTO schema_migrations (version) VALUES ('20170328215436'); - -INSERT INTO schema_migrations (version) VALUES ('20170330012505'); - -INSERT INTO schema_migrations (version) VALUES ('20170419173031'); - -INSERT INTO schema_migrations (version) VALUES ('20170419173712'); - -INSERT INTO schema_migrations (version) VALUES ('20170419175801'); - -INSERT INTO schema_migrations (version) VALUES ('20170628185847'); - -INSERT INTO schema_migrations (version) VALUES ('20170704160233'); - -INSERT INTO schema_migrations (version) VALUES ('20170706141334'); - -INSERT INTO schema_migrations (version) VALUES ('20170824202826'); - -INSERT INTO schema_migrations (version) VALUES ('20170906224040'); - -INSERT INTO schema_migrations (version) VALUES ('20171027183824'); - -INSERT INTO schema_migrations (version) VALUES ('20171208203841'); - -INSERT INTO schema_migrations (version) VALUES ('20171212153352'); - -INSERT INTO schema_migrations (version) VALUES ('20180216203422'); - -INSERT INTO schema_migrations (version) VALUES ('20180228220311'); - -INSERT INTO schema_migrations (version) VALUES ('20180313180114'); - -INSERT INTO schema_migrations (version) VALUES ('20180501182859'); - -INSERT INTO schema_migrations (version) VALUES ('20180514135529'); - -INSERT INTO schema_migrations (version) VALUES ('20180607175050'); - -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'); +-- +-- PostgreSQL database dump complete +-- -INSERT INTO schema_migrations (version) VALUES ('20180917205609'); +SET search_path TO "$user", public; -INSERT INTO schema_migrations (version) VALUES ('20180919001158'); +INSERT INTO "schema_migrations" (version) VALUES +('20121016005009'), +('20130105203021'), +('20130105224358'), +('20130105224618'), +('20130107181109'), +('20130107212832'), +('20130109175700'), +('20130109220548'), +('20130113214204'), +('20130116024233'), +('20130116215213'), +('20130118002239'), +('20130122020042'), +('20130122201442'), +('20130122221616'), +('20130123174514'), +('20130123180224'), +('20130123180228'), +('20130125220425'), +('20130128202518'), +('20130128231343'), +('20130130205749'), +('20130203104818'), +('20130203104824'), +('20130203115329'), +('20130207195855'), +('20130218181504'), +('20130226170000'), +('20130313175417'), +('20130315155820'), +('20130315183626'), +('20130315213205'), +('20130318002138'), +('20130319165853'), +('20130319180730'), +('20130319194637'), +('20130319201431'), +('20130319235957'), +('20130320000107'), +('20130326173804'), +('20130326182917'), +('20130415020241'), +('20130425024459'), +('20130425214427'), +('20130523060112'), +('20130523060213'), +('20130524042319'), +('20130528134100'), +('20130606183519'), +('20130608053730'), +('20130610202538'), +('20130611163736'), +('20130612042554'), +('20130617150007'), +('20130626002829'), +('20130626022810'), +('20130627154537'), +('20130627184333'), +('20130708163414'), +('20130708182912'), +('20130708185153'), +('20130724153034'), +('20131007180607'), +('20140117231056'), +('20140124222114'), +('20140129184311'), +('20140317135600'), +('20140319160547'), +('20140321191343'), +('20140324024606'), +('20140325175653'), +('20140402001908'), +('20140407184311'), +('20140421140924'), +('20140421151939'), +('20140421151940'), +('20140422011506'), +('20140423132913'), +('20140423133559'), +('20140501165548'), +('20140519205916'), +('20140527152921'), +('20140530200539'), +('20140601022548'), +('20140602143352'), +('20140607150616'), +('20140611173003'), +('20140627210837'), +('20140709172343'), +('20140714184006'), +('20140811184643'), +('20140817035914'), +('20140818125735'), +('20140826180337'), +('20140828141043'), +('20140909183946'), +('20140911221252'), +('20140918141529'), +('20140918153541'), +('20140918153705'), +('20140924091559'), +('20141111133038'), +('20141208164553'), +('20141208174553'), +('20141208174653'), +('20141208185217'), +('20150122175935'), +('20150123142953'), +('20150203180223'), +('20150206210804'), +('20150206230342'), +('20150216193428'), +('20150303210106'), +('20150312151136'), +('20150317132720'), +('20150324152204'), +('20150423145759'), +('20150512193020'), +('20150526180251'), +('20151202151426'), +('20151215134304'), +('20151229214707'), +('20160208210629'), +('20160209155729'), +('20160324144017'), +('20160506175108'), +('20160509143250'), +('20160808151559'), +('20160819195557'), +('20160819195725'), +('20160901210110'), +('20160909181442'), +('20160926194129'), +('20161019171346'), +('20161111143147'), +('20161115171221'), +('20161115174218'), +('20161213172944'), +('20161222153434'), +('20161223090712'), +('20170102153111'), +('20170105160301'), +('20170105160302'), +('20170216170823'), +('20170301225558'), +('20170319063406'), +('20170328215436'), +('20170330012505'), +('20170419173031'), +('20170419173712'), +('20170419175801'), +('20170628185847'), +('20170704160233'), +('20170706141334'), +('20170824202826'), +('20170906224040'), +('20171027183824'), +('20171208203841'), +('20171212153352'), +('20180216203422'), +('20180228220311'), +('20180313180114'), +('20180501182859'), +('20180514135529'), +('20180607175050'), +('20180608123145'), +('20180806133039'), +('20180820130357'), +('20180820132617'), +('20180820135808'), +('20180824152014'), +('20180824155207'), +('20180904110712'), +('20180913175443'), +('20180915155335'), +('20180917200000'), +('20180917205609'), +('20180919001158'), +('20181001175023'), +('20181004131141'), +('20181005192222'), +('20181011184200'), +('20181213183234'), +('20190214214814'), +('20190322174136'), +('20190422144631'), +('20190523180148'), +('20190808145904'), +('20190809135453'), +('20190905151603'), +('20200501150153'), +('20200602141328'); -INSERT INTO schema_migrations (version) VALUES ('20181001175023'); -INSERT INTO schema_migrations (version) VALUES ('20181004131141');