15531: Test federation migrate script WIP

[arvados.git] / services / api / lib / record_filters.rb

diff --git a/services/api/lib/record_filters.rb b/services/api/lib/record_filters.rb
index ce9fe6b13af20258987e3e2e4ca04b24aaefbebc..c8f024291c2c677c086445b294658a3c221211c0 100644 (file)
--- a/services/api/lib/record_filters.rb
+++ b/services/api/lib/record_filters.rb
@@ -44,6 +44,14 @@ module RecordFilters
 
       cond_out = []
 
+      if attrs_in == 'any' && (operator.casecmp('ilike').zero? || operator.casecmp('like').zero?) && (operand.is_a? String) && operand.match('^[%].*[%]$')
+        # Trigram index search
+        cond_out << model_class.full_text_trgm + " #{operator} ?"
+        param_out << operand
+        # Skip the generic per-column operator loop below
+        attrs = []
+      end
+
       if operator == '@@'
         # Full-text search
         if attrs_in != 'any'
@@ -63,12 +71,18 @@ module RecordFilters
       attrs.each do |attr|
         subproperty = attr.split(".", 2)
 
-        if !model_class.searchable_columns(operator).index subproperty[0]
-          raise ArgumentError.new("Invalid attribute '#{subproperty[0]}' in filter")
-        end
+        col = model_class.columns.select { |c| c.name == subproperty[0] }.first
 
         if subproperty.length == 2
-        # jsonb search
+          if col.nil? or col.type != :jsonb
+            raise ArgumentError.new("Invalid attribute '#{subproperty[0]}' for subproperty filter")
+          end
+
+          if subproperty[1][0] == "<" and subproperty[1][-1] == ">"
+            subproperty[1] = subproperty[1][1..-2]
+          end
+
+          # jsonb search
           case operator.downcase
           when '=', '!='
             not_in = if operator.downcase == "!=" then "NOT " else "" end
@@ -84,32 +98,48 @@ module RecordFilters
               raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
                                       "for '#{operator}' operator in filters")
             end
-          # when '<', '<=', '>', '>='
-          #   cond_out << "#{ar_table_name}.#{subproperty[0]}->? #{operator} ?::jsonb"
-          #   param_out << subproperty[1]
-          #   param_out << SafeJSON.dump(operand)
+          when '<', '<=', '>', '>='
+            cond_out << "#{ar_table_name}.#{subproperty[0]}->? #{operator} ?::jsonb"
+            param_out << subproperty[1]
+            param_out << SafeJSON.dump(operand)
           when 'like', 'ilike'
             cond_out << "#{ar_table_name}.#{subproperty[0]}->>? #{operator} ?"
+            param_out << subproperty[1]
             param_out << operand
           when 'not in'
             if operand.is_a? Array
-              cond_out << "#{ar_table_name}.#{subproperty[0]}->>? NOT IN (?)"
+              cond_out << "#{ar_table_name}.#{subproperty[0]}->>? NOT IN (?) OR #{ar_table_name}.#{subproperty[0]}->>? IS NULL"
               param_out << subproperty[1]
               param_out << operand
+              param_out << subproperty[1]
             else
               raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
                                       "for '#{operator}' operator in filters")
             end
-          when '?'
-          if operand
-            raise ArgumentError.new("Invalid operand for subproperty existence filter, should be empty or null")
-          end
-          cond_out << "jsonb_exists(#{ar_table_name}.#{subproperty[0]}, ?)"
-          param_out << subproperty[1]
+          when 'exists'
+            if operand == true
+              cond_out << "jsonb_exists(#{ar_table_name}.#{subproperty[0]}, ?)"
+            elsif operand == false
+              cond_out << "(NOT jsonb_exists(#{ar_table_name}.#{subproperty[0]}, ?)) OR #{ar_table_name}.#{subproperty[0]} is NULL"
+            else
+              raise ArgumentError.new("Invalid operand '#{operand}' for '#{operator}' must be true or false")
+            end
+            param_out << subproperty[1]
           else
             raise ArgumentError.new("Invalid operator for subproperty search '#{operator}'")
           end
+        elsif operator.downcase == "exists"
+          if col.type != :jsonb
+            raise ArgumentError.new("Invalid attribute '#{subproperty[0]}' for operator '#{operator}' in filter")
+          end
+
+          cond_out << "jsonb_exists(#{ar_table_name}.#{subproperty[0]}, ?)"
+          param_out << operand
         else
+          if !model_class.searchable_columns(operator).index subproperty[0]
+            raise ArgumentError.new("Invalid attribute '#{subproperty[0]}' in filter")
+          end
+
           case operator.downcase
           when '=', '<', '<=', '>', '>=', '!=', 'like', 'ilike'
             attr_type = model_class.attribute_column(attr).type
@@ -150,6 +180,9 @@ module RecordFilters
                  [true, false].include?(operand)
               cond_out << "#{ar_table_name}.#{attr} #{operator} ?"
               param_out << operand
+            elsif (attr_type == :integer)
+              cond_out << "#{ar_table_name}.#{attr} #{operator} ?"
+              param_out << operand
             else
               raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
                                       "for '#{operator}' operator in filters")
@@ -172,8 +205,17 @@ module RecordFilters
             operand.each do |op|
               cl = ArvadosModel::kind_class op
               if cl
-                cond << "#{ar_table_name}.#{attr} like ?"
-                param_out << cl.uuid_like_pattern
+                if attr == 'uuid'
+                  if model_class.uuid_prefix == cl.uuid_prefix
+                    cond << "1=1"
+                  else
+                    cond << "1=0"
+                  end
+                else
+                  # Use a substring query to support remote uuids
+                  cond << "substring(#{ar_table_name}.#{attr}, 7, 5) = ?"
+                  param_out << cl.uuid_prefix
+                end
               else
                 cond << "1=0"
               end