X-Git-Url: https://git.arvados.org/arvados.git/blobdiff_plain/9c3cc0f61751720cfdea62717934746d1aa32b72..0e1f8c8b47ddecbb3b6db360c8fda2fa612590ee:/services/api/db/structure.sql diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql index e715cd60c4..0128dbf161 100644 --- a/services/api/db/structure.sql +++ b/services/api/db/structure.sql @@ -1,3 +1,7 @@ +-- Copyright (C) The Arvados Authors. All rights reserved. +-- +-- SPDX-License-Identifier: AGPL-3.0 + -- -- PostgreSQL database dump -- @@ -28,6 +32,74 @@ SET default_tablespace = ''; SET default_with_oids = false; +-- +-- Name: groups; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE groups ( + id integer NOT NULL, + uuid character varying(255), + owner_uuid character varying(255), + 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, + name character varying(255) NOT NULL, + description character varying(524288), + updated_at timestamp without time zone NOT NULL, + group_class character varying(255), + trash_at timestamp without time zone, + is_trashed boolean DEFAULT false NOT NULL, + delete_at timestamp without time zone +); + + +-- +-- Name: links; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE links ( + id integer NOT NULL, + uuid character varying(255), + owner_uuid character varying(255), + 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, + tail_uuid character varying(255), + link_class character varying(255), + name character varying(255), + head_uuid character varying(255), + properties text, + updated_at timestamp without time zone NOT NULL +); + + +-- +-- Name: users; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE 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) +); + + -- -- Name: api_client_authorizations; Type: TABLE; Schema: public; Owner: -; Tablespace: -- @@ -44,9 +116,7 @@ CREATE TABLE api_client_authorizations ( created_at timestamp without time zone NOT NULL, updated_at timestamp without time zone NOT NULL, default_owner_uuid character varying(255), - scopes text DEFAULT '--- -- all -'::text NOT NULL, + scopes text DEFAULT '["all"]'::text, uuid character varying(255) NOT NULL ); @@ -169,8 +239,10 @@ CREATE TABLE collections ( name character varying(255), description character varying(524288), properties text, - expires_at timestamp without time zone, - file_names character varying(8192) + delete_at timestamp without time zone, + file_names character varying(8192), + trash_at timestamp without time zone, + is_trashed boolean DEFAULT false NOT NULL ); @@ -294,7 +366,9 @@ CREATE TABLE container_requests ( use_existing boolean DEFAULT true, scheduling_parameters text, output_uuid character varying(255), - log_uuid character varying(255) + log_uuid character varying(255), + output_name character varying(255) DEFAULT NULL::character varying, + output_ttl integer DEFAULT 0 NOT NULL ); @@ -370,25 +444,6 @@ CREATE SEQUENCE containers_id_seq ALTER SEQUENCE containers_id_seq OWNED BY containers.id; --- --- Name: groups; Type: TABLE; Schema: public; Owner: -; Tablespace: --- - -CREATE TABLE groups ( - id integer NOT NULL, - uuid character varying(255), - owner_uuid character varying(255), - 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, - name character varying(255) NOT NULL, - description character varying(524288), - updated_at timestamp without time zone NOT NULL, - group_class character varying(255) -); - - -- -- Name: groups_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- @@ -655,27 +710,6 @@ CREATE SEQUENCE keep_services_id_seq ALTER SEQUENCE keep_services_id_seq OWNED BY keep_services.id; --- --- Name: links; Type: TABLE; Schema: public; Owner: -; Tablespace: --- - -CREATE TABLE links ( - id integer NOT NULL, - uuid character varying(255), - owner_uuid character varying(255), - 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, - tail_uuid character varying(255), - link_class character varying(255), - name character varying(255), - head_uuid character varying(255), - properties text, - updated_at timestamp without time zone NOT NULL -); - - -- -- Name: links_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- @@ -695,12 +729,24 @@ CREATE SEQUENCE links_id_seq ALTER SEQUENCE links_id_seq OWNED BY links.id; +-- +-- Name: logs_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE logs_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + -- -- Name: logs; Type: TABLE; Schema: public; Owner: -; Tablespace: -- CREATE TABLE logs ( - id integer NOT NULL, + id integer DEFAULT nextval('logs_id_seq'::regclass) NOT NULL, uuid character varying(255), owner_uuid character varying(255), modified_by_client_uuid character varying(255), @@ -717,25 +763,6 @@ CREATE TABLE logs ( ); --- --- Name: logs_id_seq; Type: SEQUENCE; Schema: public; Owner: - --- - -CREATE SEQUENCE logs_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - - --- --- Name: logs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - --- - -ALTER SEQUENCE logs_id_seq OWNED BY logs.id; - - -- -- Name: nodes; Type: TABLE; Schema: public; Owner: -; Tablespace: -- @@ -780,6 +807,68 @@ CREATE SEQUENCE nodes_id_seq ALTER SEQUENCE nodes_id_seq OWNED BY nodes.id; +-- +-- Name: permission_view; Type: MATERIALIZED VIEW; Schema: public; Owner: -; Tablespace: +-- + +CREATE MATERIALIZED VIEW 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 + FROM ((links + LEFT JOIN perm_value pv ON ((pv.name = (links.name)::text))) + LEFT JOIN 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" + FROM groups + ), perm(val, follow, user_uuid, target_uuid, trashed, startnode) AS ( + SELECT (3)::smallint AS val, + false AS follow, + (users.uuid)::character varying(32) AS user_uuid, + (users.uuid)::character varying(32) AS target_uuid, + (0)::smallint AS trashed, + true AS startnode + FROM 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))::smallint AS trashed, + false AS startnode + FROM (perm perm_1 + JOIN perm_edges edges ON (((perm_1.startnode OR 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: pipeline_instances; Type: TABLE; Schema: public; Owner: -; Tablespace: -- @@ -862,6 +951,30 @@ CREATE SEQUENCE pipeline_templates_id_seq ALTER SEQUENCE pipeline_templates_id_seq OWNED BY pipeline_templates.id; +-- +-- Name: read_permissions; Type: VIEW; Schema: public; Owner: - +-- + +CREATE VIEW read_permissions AS + WITH RECURSIVE read_permissions(follow, user_uuid, readable_uuid) AS ( + SELECT true AS bool, + users.uuid, + users.uuid + FROM users + UNION + SELECT (((links.name)::text = 'can_manage'::text) OR ((links.head_uuid)::text ~~ 'su92l-j7d0g-%'::text)) AS follow, + rp.user_uuid, + links.head_uuid + FROM read_permissions rp, + links + WHERE (rp.follow AND ((links.tail_uuid)::text = (rp.readable_uuid)::text)) + ) + SELECT read_permissions.follow, + read_permissions.user_uuid, + read_permissions.readable_uuid + FROM read_permissions; + + -- -- Name: repositories; Type: TABLE; Schema: public; Owner: -; Tablespace: -- @@ -898,6 +1011,18 @@ CREATE SEQUENCE repositories_id_seq ALTER SEQUENCE repositories_id_seq OWNED BY repositories.id; +-- +-- Name: rp_cache; Type: MATERIALIZED VIEW; Schema: public; Owner: -; Tablespace: +-- + +CREATE MATERIALIZED VIEW rp_cache AS + SELECT read_permissions.follow, + read_permissions.user_uuid, + read_permissions.readable_uuid + FROM read_permissions + WITH NO DATA; + + -- -- Name: schema_migrations; Type: TABLE; Schema: public; Owner: -; Tablespace: -- @@ -981,31 +1106,6 @@ CREATE SEQUENCE traits_id_seq ALTER SEQUENCE traits_id_seq OWNED BY traits.id; --- --- Name: users; Type: TABLE; Schema: public; Owner: -; Tablespace: --- - -CREATE TABLE 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) -); - - -- -- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- @@ -1204,13 +1304,6 @@ ALTER TABLE ONLY keep_services ALTER COLUMN id SET DEFAULT nextval('keep_service ALTER TABLE ONLY links ALTER COLUMN id SET DEFAULT nextval('links_id_seq'::regclass); --- --- Name: id; Type: DEFAULT; Schema: public; Owner: - --- - -ALTER TABLE ONLY logs ALTER COLUMN id SET DEFAULT nextval('logs_id_seq'::regclass); - - -- -- Name: id; Type: DEFAULT; Schema: public; Owner: - -- @@ -1495,18 +1588,11 @@ CREATE INDEX api_clients_search_index ON api_clients USING btree (uuid, owner_uu CREATE INDEX authorized_keys_search_index ON authorized_keys USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, name, key_type, authorized_user_uuid); --- --- Name: collection_owner_uuid_name_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: --- - -CREATE UNIQUE INDEX collection_owner_uuid_name_unique ON collections USING btree (owner_uuid, name) WHERE (expires_at IS NULL); - - -- -- Name: collections_full_text_search_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX collections_full_text_search_idx ON collections USING gin (to_tsvector('english'::regconfig, (((((((((((((((((' '::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(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) || (COALESCE(file_names, ''::character varying))::text))); +CREATE INDEX collections_full_text_search_idx ON 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) || (COALESCE(file_names, ''::character varying))::text))); -- @@ -1520,14 +1606,14 @@ CREATE INDEX collections_search_index ON collections USING btree (owner_uuid, mo -- Name: container_requests_full_text_search_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX container_requests_full_text_search_idx ON container_requests USING gin (to_tsvector('english'::regconfig, (((((((((((((((((((((((((((((((((((' '::text || (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) || (COALESCE(state, ''::character varying))::text) || ' '::text) || (COALESCE(requesting_container_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(container_uuid, ''::character varying))::text) || ' '::text) || COALESCE(mounts, ''::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)))); +CREATE INDEX container_requests_full_text_search_idx ON 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) || (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))); -- -- Name: container_requests_search_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX container_requests_search_index ON 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); +CREATE INDEX container_requests_search_index ON 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); -- @@ -1541,14 +1627,7 @@ CREATE INDEX containers_search_index ON containers USING btree (uuid, owner_uuid -- Name: groups_full_text_search_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX groups_full_text_search_idx ON groups USING gin (to_tsvector('english'::regconfig, (((((((((((((' '::text || (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))); - - --- --- Name: groups_owner_uuid_name_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: --- - -CREATE UNIQUE INDEX groups_owner_uuid_name_unique ON groups USING btree (owner_uuid, name); +CREATE INDEX groups_full_text_search_idx ON 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))); -- @@ -1656,6 +1735,20 @@ CREATE UNIQUE INDEX index_authorized_keys_on_uuid ON authorized_keys USING btree CREATE INDEX index_collections_on_created_at ON collections USING btree (created_at); +-- +-- Name: index_collections_on_delete_at; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_collections_on_delete_at ON collections USING btree (delete_at); + + +-- +-- Name: index_collections_on_is_trashed; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_collections_on_is_trashed ON collections USING btree (is_trashed); + + -- -- Name: index_collections_on_modified_at; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -1663,6 +1756,13 @@ CREATE INDEX index_collections_on_created_at ON collections USING btree (created CREATE INDEX index_collections_on_modified_at ON collections USING btree (modified_at); +-- +-- Name: index_collections_on_modified_at_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_collections_on_modified_at_uuid ON collections USING btree (modified_at DESC, uuid); + + -- -- Name: index_collections_on_owner_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -1670,6 +1770,27 @@ CREATE INDEX index_collections_on_modified_at ON collections USING btree (modifi CREATE INDEX index_collections_on_owner_uuid ON collections USING btree (owner_uuid); +-- +-- Name: index_collections_on_owner_uuid_and_name; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE UNIQUE INDEX index_collections_on_owner_uuid_and_name ON collections USING btree (owner_uuid, name) WHERE (is_trashed = false); + + +-- +-- Name: index_collections_on_portable_data_hash; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_collections_on_portable_data_hash ON collections USING btree (portable_data_hash); + + +-- +-- Name: index_collections_on_trash_at; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_collections_on_trash_at ON collections USING btree (trash_at); + + -- -- Name: index_collections_on_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -1691,6 +1812,13 @@ CREATE UNIQUE INDEX index_commit_ancestors_on_descendant_and_ancestor ON commit_ CREATE UNIQUE INDEX index_commits_on_repository_name_and_sha1 ON commits USING btree (repository_name, sha1); +-- +-- Name: index_container_requests_on_modified_at_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_container_requests_on_modified_at_uuid ON container_requests USING btree (modified_at DESC, uuid); + + -- -- Name: index_container_requests_on_owner_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -1698,6 +1826,13 @@ CREATE UNIQUE INDEX index_commits_on_repository_name_and_sha1 ON commits USING b CREATE INDEX index_container_requests_on_owner_uuid ON container_requests USING btree (owner_uuid); +-- +-- Name: index_container_requests_on_requesting_container_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_container_requests_on_requesting_container_uuid ON container_requests USING btree (requesting_container_uuid); + + -- -- Name: index_container_requests_on_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -1726,6 +1861,13 @@ CREATE UNIQUE INDEX index_containers_on_uuid ON containers USING btree (uuid); CREATE INDEX index_groups_on_created_at ON groups USING btree (created_at); +-- +-- Name: index_groups_on_delete_at; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_groups_on_delete_at ON groups USING btree (delete_at); + + -- -- Name: index_groups_on_group_class; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -1733,6 +1875,13 @@ CREATE INDEX index_groups_on_created_at ON groups USING btree (created_at); CREATE INDEX index_groups_on_group_class ON groups USING btree (group_class); +-- +-- Name: index_groups_on_is_trashed; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_groups_on_is_trashed ON groups USING btree (is_trashed); + + -- -- Name: index_groups_on_modified_at; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -1740,6 +1889,13 @@ CREATE INDEX index_groups_on_group_class ON groups USING btree (group_class); CREATE INDEX index_groups_on_modified_at ON groups USING btree (modified_at); +-- +-- Name: index_groups_on_modified_at_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_groups_on_modified_at_uuid ON groups USING btree (modified_at DESC, uuid); + + -- -- Name: index_groups_on_owner_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -1747,6 +1903,20 @@ CREATE INDEX index_groups_on_modified_at ON groups USING btree (modified_at); CREATE INDEX index_groups_on_owner_uuid ON groups USING btree (owner_uuid); +-- +-- Name: index_groups_on_owner_uuid_and_name; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE UNIQUE INDEX index_groups_on_owner_uuid_and_name ON groups USING btree (owner_uuid, name) WHERE (is_trashed = false); + + +-- +-- Name: index_groups_on_trash_at; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_groups_on_trash_at ON groups USING btree (trash_at); + + -- -- Name: index_groups_on_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -1775,6 +1945,13 @@ CREATE UNIQUE INDEX index_humans_on_uuid ON humans USING btree (uuid); CREATE INDEX index_job_tasks_on_created_at ON job_tasks USING btree (created_at); +-- +-- Name: index_job_tasks_on_created_by_job_task_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_job_tasks_on_created_by_job_task_uuid ON job_tasks USING btree (created_by_job_task_uuid); + + -- -- Name: index_job_tasks_on_job_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -1838,6 +2015,13 @@ CREATE INDEX index_jobs_on_finished_at ON jobs USING btree (finished_at); CREATE INDEX index_jobs_on_modified_at ON jobs USING btree (modified_at); +-- +-- Name: index_jobs_on_modified_at_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_jobs_on_modified_at_uuid ON jobs USING btree (modified_at DESC, uuid); + + -- -- Name: index_jobs_on_output; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -1957,6 +2141,13 @@ CREATE INDEX index_links_on_head_uuid ON links USING btree (head_uuid); CREATE INDEX index_links_on_modified_at ON links USING btree (modified_at); +-- +-- Name: index_links_on_modified_at_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_links_on_modified_at_uuid ON links USING btree (modified_at DESC, uuid); + + -- -- Name: index_links_on_owner_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -2006,6 +2197,13 @@ CREATE INDEX index_logs_on_event_type ON logs USING btree (event_type); CREATE INDEX index_logs_on_modified_at ON logs USING btree (modified_at); +-- +-- Name: index_logs_on_object_owner_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_logs_on_object_owner_uuid ON logs USING btree (object_owner_uuid); + + -- -- Name: index_logs_on_object_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -2090,6 +2288,13 @@ CREATE INDEX index_pipeline_instances_on_created_at ON pipeline_instances USING CREATE INDEX index_pipeline_instances_on_modified_at ON pipeline_instances USING btree (modified_at); +-- +-- Name: index_pipeline_instances_on_modified_at_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_pipeline_instances_on_modified_at_uuid ON pipeline_instances USING btree (modified_at DESC, uuid); + + -- -- Name: index_pipeline_instances_on_owner_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -2118,6 +2323,13 @@ CREATE INDEX index_pipeline_templates_on_created_at ON pipeline_templates USING CREATE INDEX index_pipeline_templates_on_modified_at ON pipeline_templates USING btree (modified_at); +-- +-- Name: index_pipeline_templates_on_modified_at_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_pipeline_templates_on_modified_at_uuid ON pipeline_templates USING btree (modified_at DESC, uuid); + + -- -- Name: index_pipeline_templates_on_owner_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -2132,6 +2344,13 @@ CREATE INDEX index_pipeline_templates_on_owner_uuid ON pipeline_templates USING CREATE UNIQUE INDEX index_pipeline_templates_on_uuid ON pipeline_templates USING btree (uuid); +-- +-- Name: index_repositories_on_modified_at_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_repositories_on_modified_at_uuid ON repositories USING btree (modified_at DESC, uuid); + + -- -- Name: index_repositories_on_name; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -2216,6 +2435,13 @@ CREATE INDEX index_users_on_created_at ON users USING btree (created_at); CREATE INDEX index_users_on_modified_at ON users USING btree (modified_at); +-- +-- Name: index_users_on_modified_at_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_users_on_modified_at_uuid ON users USING btree (modified_at DESC, uuid); + + -- -- Name: index_users_on_owner_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -2244,6 +2470,13 @@ CREATE UNIQUE INDEX index_users_on_uuid ON users USING btree (uuid); CREATE INDEX index_virtual_machines_on_hostname ON virtual_machines USING btree (hostname); +-- +-- Name: index_virtual_machines_on_modified_at_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_virtual_machines_on_modified_at_uuid ON virtual_machines USING btree (modified_at DESC, uuid); + + -- -- Name: index_virtual_machines_on_owner_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -2258,6 +2491,13 @@ CREATE INDEX index_virtual_machines_on_owner_uuid ON virtual_machines USING btre CREATE UNIQUE INDEX index_virtual_machines_on_uuid ON virtual_machines USING btree (uuid); +-- +-- Name: index_workflows_on_modified_at_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_workflows_on_modified_at_uuid ON workflows USING btree (modified_at DESC, uuid); + + -- -- Name: index_workflows_on_owner_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -2283,7 +2523,7 @@ CREATE INDEX job_tasks_search_index ON job_tasks USING btree (uuid, owner_uuid, -- Name: jobs_full_text_search_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX jobs_full_text_search_idx ON jobs USING gin (to_tsvector('english'::regconfig, (((((((((((((((((((((((((((((((((((((((((' '::text || (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))); +CREATE INDEX jobs_full_text_search_idx ON 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)))); -- @@ -2335,11 +2575,25 @@ CREATE INDEX logs_search_index ON logs USING btree (uuid, owner_uuid, modified_b CREATE INDEX nodes_search_index ON nodes USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, hostname, domain, ip_address, job_uuid); +-- +-- Name: permission_target_trashed; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX permission_target_trashed ON permission_view USING btree (trashed, target_uuid); + + +-- +-- Name: permission_target_user_trashed_level; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX permission_target_user_trashed_level ON permission_view USING btree (user_uuid, trashed, perm_level); + + -- -- Name: pipeline_instances_full_text_search_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX pipeline_instances_full_text_search_idx ON pipeline_instances USING gin (to_tsvector('english'::regconfig, (((((((((((((((((((((' '::text || (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 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))); -- @@ -2360,7 +2614,7 @@ CREATE UNIQUE INDEX pipeline_template_owner_uuid_name_unique ON pipeline_templat -- Name: pipeline_templates_full_text_search_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX pipeline_templates_full_text_search_idx ON pipeline_templates USING gin (to_tsvector('english'::regconfig, (((((((((((((' '::text || (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 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))); -- @@ -2384,6 +2638,13 @@ CREATE INDEX repositories_search_index ON repositories USING btree (uuid, owner_ CREATE INDEX specimens_search_index ON specimens USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, material); +-- +-- Name: test_1; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX test_1 ON collections USING btree (id) WHERE (delete_at IS NULL); + + -- -- Name: traits_search_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -2416,7 +2677,7 @@ CREATE INDEX virtual_machines_search_index ON virtual_machines USING btree (uuid -- Name: workflows_full_text_search_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX workflows_full_text_search_idx ON workflows USING gin (to_tsvector('english'::regconfig, (((((((((((((' '::text || (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(definition, ''::text)))); +CREATE INDEX workflows_full_text_search_idx ON 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)))); -- @@ -2704,4 +2965,39 @@ INSERT INTO schema_migrations (version) VALUES ('20161111143147'); INSERT INTO schema_migrations (version) VALUES ('20161115171221'); -INSERT INTO schema_migrations (version) VALUES ('20161115174218'); \ No newline at end of file +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 ('20170824202826'); + +INSERT INTO schema_migrations (version) VALUES ('20170906224040'); +