X-Git-Url: https://git.arvados.org/arvados.git/blobdiff_plain/2c0b0226983d13084e77fe059efb85d5ce2c33f5..98d6c8c5743e0fd6be85af3b9f30286a358bd1d4:/services/api/db/structure.sql diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql index d6b74df4ae..357e95c564 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 ); @@ -787,7 +781,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)))) @@ -800,7 +795,8 @@ 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) AS ( SELECT (3)::smallint AS val, @@ -814,7 +810,7 @@ CREATE MATERIALIZED VIEW materialized_permission_view AS 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 + ((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)))) ) @@ -1612,11 +1608,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))); -- @@ -1836,6 +1839,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: - -- @@ -1864,6 +1874,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: - -- @@ -3021,7 +3038,19 @@ 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'); +