From 1aa3f43606ba5e31633c063851d25b8fab4b93e2 Mon Sep 17 00:00:00 2001 From: radhika Date: Thu, 16 Feb 2017 12:27:51 -0500 Subject: [PATCH] 11103: Omit mounts from container_requests and definition from workflows for full text search index. --- services/api/app/models/container_request.rb | 4 +++ services/api/app/models/workflow.rb | 4 +++ ..._workflow_def_in_full_text_search_index.rb | 28 +++++++++++++++++++ services/api/db/structure.sql | 8 ++++-- 4 files changed, 41 insertions(+), 3 deletions(-) create mode 100644 services/api/db/migrate/20170216170823_no_cr_mounts_and_workflow_def_in_full_text_search_index.rb diff --git a/services/api/app/models/container_request.rb b/services/api/app/models/container_request.rb index 6122c34661..398dadae3f 100644 --- a/services/api/app/models/container_request.rb +++ b/services/api/app/models/container_request.rb @@ -133,6 +133,10 @@ class ContainerRequest < ArvadosModel update_attributes!(state: Final, output_uuid: out_coll, log_uuid: log_coll) end + def self.full_text_searchable_columns + super - ["mounts"] + end + protected def fill_field_defaults diff --git a/services/api/app/models/workflow.rb b/services/api/app/models/workflow.rb index f786914927..54fcf9b9ab 100644 --- a/services/api/app/models/workflow.rb +++ b/services/api/app/models/workflow.rb @@ -39,4 +39,8 @@ class Workflow < ArvadosModel end end end + + def self.full_text_searchable_columns + super - ["definition"] + end end diff --git a/services/api/db/migrate/20170216170823_no_cr_mounts_and_workflow_def_in_full_text_search_index.rb b/services/api/db/migrate/20170216170823_no_cr_mounts_and_workflow_def_in_full_text_search_index.rb new file mode 100644 index 0000000000..3d4a6062b4 --- /dev/null +++ b/services/api/db/migrate/20170216170823_no_cr_mounts_and_workflow_def_in_full_text_search_index.rb @@ -0,0 +1,28 @@ +class NoCrMountsAndWorkflowDefInFullTextSearchIndex < ActiveRecord::Migration + def fts_indexes + { + "container_requests" => "container_requests_full_text_search_idx", + "workflows" => "workflows_full_text_search_idx", + } + end + + def up + # remove existing fts index and recreate for container_requests and workflows + fts_indexes.each do |t, i| + t.classify.constantize.reset_column_information + ActiveRecord::Base.connection.indexes(t).each do |idx| + if idx.name == i + remove_index t.to_sym, :name => i + break + end + end + execute "CREATE INDEX #{i} ON #{t} USING gin(#{t.classify.constantize.full_text_tsvector});" + end + end + + def down + fts_indexes.each do |t, i| + remove_index t.to_sym, :name => i + end + end +end diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql index 9ff935e2b2..4e3cd36344 100644 --- a/services/api/db/structure.sql +++ b/services/api/db/structure.sql @@ -1516,7 +1516,7 @@ 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, (((((((((((((((((((((((((((((((((((((((((((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)) || ' '::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 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))); -- @@ -2440,7 +2440,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, (((((((((((((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)))); -- @@ -2740,4 +2740,6 @@ INSERT INTO schema_migrations (version) VALUES ('20170102153111'); INSERT INTO schema_migrations (version) VALUES ('20170105160301'); -INSERT INTO schema_migrations (version) VALUES ('20170105160302'); \ No newline at end of file +INSERT INTO schema_migrations (version) VALUES ('20170105160302'); + +INSERT INTO schema_migrations (version) VALUES ('20170216170823'); \ No newline at end of file -- 2.30.2