12960: Migrate properties fields to jsonb to support subproperty filters.

Specifically: links.properties and container_requests.properties,
nodes.properties, and nodes.info.

Arvados-DCO-1.1-Signed-off-by: Peter Amstutz <pamstutz@veritasgenetics.com>

diff --git a/services/api/db/migrate/20180607175050_properties_to_jsonb.rb b/services/api/db/migrate/20180607175050_properties_to_jsonb.rb
new file mode 100644 (file)
index 0000000..988227a
--- /dev/null
+++ b/services/api/db/migrate/20180607175050_properties_to_jsonb.rb
@@ -0,0 +1,32 @@
+# Copyright (C) The Arvados Authors. All rights reserved.
+#
+# SPDX-License-Identifier: AGPL-3.0
+
+require './db/migrate/20161213172944_full_text_search_indexes'
+
+class PropertiesToJsonb < ActiveRecord::Migration
+
+  @@tables_columns = [["nodes", "properties"],
+                      ["nodes", "info"],
+                      ["container_requests", "properties"],
+                      ["links", "properties"]]
+
+  def up
+    @@tables_columns.each do |table, column|
+      # Drop the FT index before changing column type to avoid
+      # "PG::DatatypeMismatch: ERROR: COALESCE types jsonb and text
+      # cannot be matched".
+      ActiveRecord::Base.connection.execute "DROP INDEX IF EXISTS #{table}_full_text_search_idx"
+      ActiveRecord::Base.connection.execute "ALTER TABLE #{table} ALTER COLUMN #{column} TYPE jsonb USING #{column}::jsonb"
+      ActiveRecord::Base.connection.execute "CREATE INDEX #{table}_index_on_#{column} ON #{table} USING gin (#{column})"
+    end
+    FullTextSearchIndexes.new.replace_index("container_requests")
+  end
+
+  def down
+    @@tables_columns.each do |table, column|
+      ActiveRecord::Base.connection.execute "DROP INDEX IF EXISTS #{table}_index_on_#{column}"
+      ActiveRecord::Base.connection.execute "ALTER TABLE #{table} ALTER COLUMN #{column} TYPE text"
+    end
+  end
+end

diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql
index 0ab30c5e1cf0d2b7af810c0a264ae888a52ebeb5..297bf57e0f6693bce7f6ec7ba13ae20b74658ee6 100644 (file)
--- a/services/api/db/structure.sql
+++ b/services/api/db/structure.sql
@@ -277,7 +277,7 @@ CREATE TABLE container_requests (
     modified_by_user_uuid character varying(255),
     name character varying(255),
     description text,
-    properties text,
+    properties jsonb,
     state character varying(255),
     requesting_container_uuid character varying(255),
     container_uuid character varying(255),
@@ -682,7 +682,7 @@ CREATE TABLE links (
     link_class character varying(255),
     name character varying(255),
     head_uuid character varying(255),
-    properties text,
+    properties jsonb,
     updated_at timestamp without time zone NOT NULL
 );
 
@@ -853,9 +853,9 @@ CREATE TABLE nodes (
     ip_address character varying(255),
     first_ping_at timestamp without time zone,
     last_ping_at timestamp without time zone,
-    info text,
+    info jsonb,
     updated_at timestamp without time zone NOT NULL,
-    properties text,
+    properties jsonb,
     job_uuid character varying(255)
 );
 
@@ -1637,7 +1637,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: -
 --
 
-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)));
+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)) || ' '::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_index_on_properties; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX container_requests_index_on_properties ON container_requests USING gin (properties);
 
 
 --
@@ -2613,6 +2620,13 @@ CREATE INDEX keep_disks_search_index ON keep_disks USING btree (uuid, owner_uuid
 CREATE INDEX keep_services_search_index ON keep_services USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, service_host, service_type);
 
 
+--
+-- Name: links_index_on_properties; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX links_index_on_properties ON links USING gin (properties);
+
+
 --
 -- Name: links_search_index; Type: INDEX; Schema: public; Owner: -
 --
@@ -2634,6 +2648,20 @@ CREATE UNIQUE INDEX links_tail_name_unique_if_link_class_name ON links USING btr
 CREATE INDEX logs_search_index ON logs USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, object_uuid, event_type, object_owner_uuid);
 
 
+--
+-- Name: nodes_index_on_info; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX nodes_index_on_info ON nodes USING gin (info);
+
+
+--
+-- Name: nodes_index_on_properties; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX nodes_index_on_properties ON nodes USING gin (properties);
+
+
 --
 -- Name: nodes_search_index; Type: INDEX; Schema: public; Owner: -
 --
@@ -3080,3 +3108,5 @@ INSERT INTO schema_migrations (version) VALUES ('20180501182859');
 
 INSERT INTO schema_migrations (version) VALUES ('20180514135529');
 
+INSERT INTO schema_migrations (version) VALUES ('20180607175050');
+