X-Git-Url: https://git.arvados.org/arvados.git/blobdiff_plain/0561bd0c3c07257fd58ded6c7cfa5feeae97af57..0e1f8c8b47ddecbb3b6db360c8fda2fa612590ee:/services/api/db/structure.sql diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql index b38cba8109..0128dbf161 100644 --- a/services/api/db/structure.sql +++ b/services/api/db/structure.sql @@ -32,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: -- @@ -376,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: - -- @@ -661,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: - -- @@ -701,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), @@ -723,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: -- @@ -786,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: -- @@ -868,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: -- @@ -904,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: -- @@ -987,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: - -- @@ -1210,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: - -- @@ -1543,13 +1630,6 @@ CREATE INDEX containers_search_index ON containers USING btree (uuid, owner_uuid 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))); --- --- 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); - - -- -- Name: groups_search_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -1676,6 +1756,13 @@ CREATE INDEX index_collections_on_is_trashed ON collections USING btree (is_tras 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: -- @@ -1725,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: -- @@ -1767,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: -- @@ -1774,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: -- @@ -1781,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: -- @@ -1788,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: -- @@ -1886,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: -- @@ -2005,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: -- @@ -2145,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: -- @@ -2173,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: -- @@ -2187,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: -- @@ -2271,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: -- @@ -2299,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: -- @@ -2313,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: -- @@ -2390,6 +2575,20 @@ 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: -- @@ -2439,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: -- @@ -2789,3 +2995,9 @@ 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'); +