X-Git-Url: https://git.arvados.org/arvados.git/blobdiff_plain/a079a9f1627188e3ef0bb73e004defa2129d25f2..4b563772c0dcbca24c75c0fe5aafab1009b0e3c7:/services/api/db/structure.sql diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql index 4a874fbdd9..27511145e9 100644 --- a/services/api/db/structure.sql +++ b/services/api/db/structure.sql @@ -2,20 +2,11 @@ -- -- SPDX-License-Identifier: AGPL-3.0 --- --- PostgreSQL database dump --- - --- Dumped from database version 9.6.4 --- Dumped by pg_dump version 9.6.4 - SET statement_timeout = 0; -SET idle_in_transaction_session_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; -SET row_security = off; -- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: - @@ -175,11 +166,14 @@ CREATE TABLE collections ( manifest_text text, name character varying(255), description character varying(524288), - properties text, + properties jsonb, delete_at timestamp without time zone, file_names character varying(8192), trash_at timestamp without time zone, - is_trashed boolean DEFAULT false NOT NULL + is_trashed boolean DEFAULT false NOT NULL, + storage_classes_desired jsonb DEFAULT '["default"]'::jsonb, + storage_classes_confirmed jsonb DEFAULT '[]'::jsonb, + storage_classes_confirmed_at timestamp without time zone ); @@ -305,7 +299,8 @@ CREATE TABLE container_requests ( output_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 + output_ttl integer DEFAULT 0 NOT NULL, + secret_mounts jsonb DEFAULT '{}'::jsonb ); @@ -353,12 +348,14 @@ CREATE TABLE containers ( output character varying(255), container_image character varying(255), progress double precision, - priority integer, + priority bigint, updated_at timestamp without time zone NOT NULL, exit_code integer, auth_uuid character varying(255), locked_by_uuid character varying(255), - scheduling_parameters text + scheduling_parameters text, + secret_mounts jsonb DEFAULT '{}'::jsonb, + secret_mounts_md5 character varying DEFAULT '99914b932bd37a50b983c5e7c90ae93b'::character varying ); @@ -709,24 +706,12 @@ 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: - -- CREATE TABLE logs ( - id integer DEFAULT nextval('logs_id_seq'::regclass) NOT NULL, + id integer NOT NULL, uuid character varying(255), owner_uuid character varying(255), modified_by_client_uuid character varying(255), @@ -743,6 +728,25 @@ 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: users; Type: TABLE; Schema: public; Owner: - -- @@ -780,7 +784,8 @@ CREATE MATERIALIZED VIEW materialized_permission_view AS links.head_uuid, pv.val, ((pv.val = 3) OR (groups.uuid IS NOT NULL)) AS follow, - (0)::smallint AS trashed + (0)::smallint AS trashed, + (0)::smallint AS followtrash 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)))) @@ -793,25 +798,24 @@ CREATE MATERIALIZED VIEW materialized_permission_view AS CASE WHEN ((groups.trash_at IS NOT NULL) AND (groups.trash_at < clock_timestamp())) THEN 1 ELSE 0 - END AS "case" + END AS "case", + 1 FROM groups - ), perm(val, follow, user_uuid, target_uuid, trashed, startnode) AS ( + ), perm(val, follow, user_uuid, target_uuid, trashed) AS ( SELECT (3)::smallint AS val, - false AS follow, + true 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 + (0)::smallint AS trashed 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 + ((GREATEST((perm_1.trashed)::integer, edges.trashed) * edges.followtrash))::smallint AS trashed 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)))) + 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, @@ -874,6 +878,34 @@ CREATE SEQUENCE nodes_id_seq ALTER SEQUENCE nodes_id_seq OWNED BY nodes.id; +-- +-- Name: permission_refresh_lock; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE permission_refresh_lock ( + id integer NOT NULL +); + + +-- +-- Name: permission_refresh_lock_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE 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 permission_refresh_lock_id_seq OWNED BY permission_refresh_lock.id; + + -- -- Name: pipeline_instances; Type: TABLE; Schema: public; Owner: - -- @@ -956,30 +988,6 @@ 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: - -- @@ -1016,18 +1024,6 @@ CREATE SEQUENCE repositories_id_seq ALTER SEQUENCE repositories_id_seq OWNED BY repositories.id; --- --- Name: rp_cache; Type: MATERIALIZED VIEW; Schema: public; Owner: - --- - -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: - -- @@ -1309,6 +1305,13 @@ 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: logs id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY logs ALTER COLUMN id SET DEFAULT nextval('logs_id_seq'::regclass); + + -- -- Name: nodes id; Type: DEFAULT; Schema: public; Owner: - -- @@ -1316,6 +1319,13 @@ ALTER TABLE ONLY links ALTER COLUMN id SET DEFAULT nextval('links_id_seq'::regcl ALTER TABLE ONLY nodes ALTER COLUMN id SET DEFAULT nextval('nodes_id_seq'::regclass); +-- +-- Name: permission_refresh_lock id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY permission_refresh_lock ALTER COLUMN id SET DEFAULT nextval('permission_refresh_lock_id_seq'::regclass); + + -- -- Name: pipeline_instances id; Type: DEFAULT; Schema: public; Owner: - -- @@ -1508,6 +1518,14 @@ ALTER TABLE ONLY nodes ADD CONSTRAINT nodes_pkey PRIMARY KEY (id); +-- +-- Name: permission_refresh_lock permission_refresh_lock_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY permission_refresh_lock + ADD CONSTRAINT permission_refresh_lock_pkey PRIMARY KEY (id); + + -- -- Name: pipeline_instances pipeline_instances_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- @@ -1593,11 +1611,18 @@ 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_index_on_properties; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX collection_index_on_properties ON collections USING gin (properties); + + -- -- Name: collections_full_text_search_idx; Type: INDEX; Schema: public; Owner: - -- -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))); +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)) || ' '::text) || (COALESCE(file_names, ''::character varying))::text))); -- @@ -1817,6 +1842,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_container_uuid; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_container_requests_on_container_uuid ON container_requests USING btree (container_uuid); + + -- -- Name: index_container_requests_on_modified_at_uuid; Type: INDEX; Schema: public; Owner: - -- @@ -1845,6 +1877,13 @@ CREATE INDEX index_container_requests_on_requesting_container_uuid ON container_ CREATE UNIQUE INDEX index_container_requests_on_uuid ON container_requests USING btree (uuid); +-- +-- Name: index_containers_on_modified_at_uuid; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_containers_on_modified_at_uuid ON containers USING btree (modified_at DESC, uuid); + + -- -- Name: index_containers_on_owner_uuid; Type: INDEX; Schema: public; Owner: - -- @@ -1852,6 +1891,13 @@ CREATE UNIQUE INDEX index_container_requests_on_uuid ON container_requests USING CREATE INDEX index_containers_on_owner_uuid ON containers USING btree (owner_uuid); +-- +-- Name: index_containers_on_secret_mounts_md5; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_containers_on_secret_mounts_md5 ON containers USING btree (secret_mounts_md5); + + -- -- Name: index_containers_on_uuid; Type: INDEX; Schema: public; Owner: - -- @@ -2202,6 +2248,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_modified_at_uuid; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_logs_on_modified_at_uuid ON logs USING btree (modified_at DESC, uuid); + + -- -- Name: index_logs_on_object_owner_uuid; Type: INDEX; Schema: public; Owner: - -- @@ -2643,13 +2696,6 @@ 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: - --- - -CREATE INDEX test_1 ON collections USING btree (id) WHERE (delete_at IS NULL); - - -- -- Name: traits_search_index; Type: INDEX; Schema: public; Owner: - -- @@ -3002,7 +3048,23 @@ 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'); +