1 # Copyright (C) The Arvados Authors. All rights reserved.
3 # SPDX-License-Identifier: AGPL-3.0
5 # Mixin module providing a method to convert filters into a list of SQL
6 # fragments suitable to be fed to ActiveRecord #where.
18 # +filters+ array of conditions, each being [column, operator, operand]
19 # +model_class+ subclass of ActiveRecord being filtered
22 # Hash with the following keys:
23 # :cond_out array of SQL fragments for each filter expression
24 # :param_out array of values for parameter substitution in cond_out
25 # :joins array of joins: either [] or ["JOIN containers ON ..."]
26 def record_filters filters, model_class
31 model_table_name = model_class.table_name
32 filters.each do |filter|
33 attrs_in, operator, operand = filter
35 raise ArgumentError.new("Full text search operator is no longer supported")
38 attrs = model_class.searchable_columns(operator)
39 elsif attrs_in.is_a? Array
44 if !filter.is_a? Array
45 raise ArgumentError.new("Invalid element in filters array: #{filter.inspect} is not an array")
46 elsif !operator.is_a? String
47 raise ArgumentError.new("Invalid operator '#{operator}' (#{operator.class}) in filter")
52 if attrs_in == 'any' && (operator.casecmp('ilike').zero? || operator.casecmp('like').zero?) && (operand.is_a? String) && operand.match('^[%].*[%]$')
53 # Trigram index search
54 cond_out << model_class.full_text_trgm + " #{operator} ?"
56 # Skip the generic per-column operator loop below
61 subproperty = attr.split(".", 2)
63 if subproperty.length == 2 && subproperty[0] == 'container' && model_table_name == "container_requests"
64 # attr is "tablename.colname" -- e.g., ["container.state", "=", "Complete"]
65 joins = ["JOIN containers ON container_requests.container_uuid = containers.uuid"]
66 attr_model_class = Container
67 attr_table_name = "containers"
68 subproperty = subproperty[1].split(".", 2)
70 attr_model_class = model_class
71 attr_table_name = model_table_name
75 proppath = subproperty[1]
76 col = attr_model_class.columns.select { |c| c.name == attr }.first
79 if col.nil? or col.type != :jsonb
80 raise ArgumentError.new("Invalid attribute '#{attr}' for subproperty filter")
83 if proppath[0] == "<" and proppath[-1] == ">"
84 proppath = proppath[1..-2]
88 case operator.downcase
90 not_in = if operator.downcase == "!=" then "NOT " else "" end
91 cond_out << "#{not_in}(#{attr_table_name}.#{attr} @> ?::jsonb)"
92 param_out << SafeJSON.dump({proppath => operand})
94 if operand.is_a? Array
96 cond_out << "#{attr_table_name}.#{attr} @> ?::jsonb"
97 param_out << SafeJSON.dump({proppath => opr})
100 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
101 "for '#{operator}' operator in filters")
103 when '<', '<=', '>', '>='
104 cond_out << "#{attr_table_name}.#{attr}->? #{operator} ?::jsonb"
105 param_out << proppath
106 param_out << SafeJSON.dump(operand)
108 cond_out << "#{attr_table_name}.#{attr}->>? #{operator} ?"
109 param_out << proppath
112 if operand.is_a? Array
113 cond_out << "#{attr_table_name}.#{attr}->>? NOT IN (?) OR #{attr_table_name}.#{attr}->>? IS NULL"
114 param_out << proppath
116 param_out << proppath
118 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
119 "for '#{operator}' operator in filters")
123 cond_out << "jsonb_exists(#{attr_table_name}.#{attr}, ?)"
124 elsif operand == false
125 cond_out << "(NOT jsonb_exists(#{attr_table_name}.#{attr}, ?)) OR #{attr_table_name}.#{attr} is NULL"
127 raise ArgumentError.new("Invalid operand '#{operand}' for '#{operator}' must be true or false")
129 param_out << proppath
131 cond_out << "#{attr_table_name}.#{attr} @> ?::jsonb OR #{attr_table_name}.#{attr} @> ?::jsonb"
132 param_out << SafeJSON.dump({proppath => operand})
133 param_out << SafeJSON.dump({proppath => [operand]})
135 raise ArgumentError.new("Invalid operator for subproperty search '#{operator}'")
137 elsif operator.downcase == "exists"
138 if col.type != :jsonb
139 raise ArgumentError.new("Invalid attribute '#{attr}' for operator '#{operator}' in filter")
142 cond_out << "jsonb_exists(#{attr_table_name}.#{attr}, ?)"
145 if !attr_model_class.searchable_columns(operator).index attr
146 raise ArgumentError.new("Invalid attribute '#{attr}' in filter")
149 case operator.downcase
150 when '=', '<', '<=', '>', '>=', '!=', 'like', 'ilike'
151 attr_type = attr_model_class.attribute_column(attr).type
152 operator = '<>' if operator == '!='
153 if operand.is_a? String
154 if attr_type == :boolean
155 if not ['=', '<>'].include?(operator)
156 raise ArgumentError.new("Invalid operator '#{operator}' for " \
157 "boolean attribute '#{attr}'")
159 case operand.downcase
160 when '1', 't', 'true', 'y', 'yes'
162 when '0', 'f', 'false', 'n', 'no'
165 raise ArgumentError("Invalid operand '#{operand}' for " \
166 "boolean attribute '#{attr}'")
170 # explicitly allow NULL
171 cond_out << "#{attr_table_name}.#{attr} #{operator} ? OR #{attr_table_name}.#{attr} IS NULL"
173 cond_out << "#{attr_table_name}.#{attr} #{operator} ?"
175 if (# any operator that operates on value rather than
177 operator.match(/[<=>]/) and (attr_type == :datetime))
178 operand = Time.parse operand
181 elsif operand.nil? and operator == '='
182 cond_out << "#{attr_table_name}.#{attr} is null"
183 elsif operand.nil? and operator == '<>'
184 cond_out << "#{attr_table_name}.#{attr} is not null"
185 elsif (attr_type == :boolean) and ['=', '<>'].include?(operator) and
186 [true, false].include?(operand)
187 cond_out << "#{attr_table_name}.#{attr} #{operator} ?"
189 elsif (attr_type == :integer)
190 cond_out << "#{attr_table_name}.#{attr} #{operator} ?"
193 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
194 "for '#{operator}' operator in filters")
197 if operand.is_a? Array
198 cond_out << "#{attr_table_name}.#{attr} #{operator} (?)"
200 if operator == 'not in' and not operand.include?(nil)
201 # explicitly allow NULL
202 cond_out[-1] = "(#{cond_out[-1]} OR #{attr_table_name}.#{attr} IS NULL)"
205 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
206 "for '#{operator}' operator in filters")
209 operand = [operand] unless operand.is_a? Array
212 cl = ArvadosModel::kind_class op
215 if attr_model_class.uuid_prefix == cl.uuid_prefix
221 # Use a substring query to support remote uuids
222 cond << "substring(#{attr_table_name}.#{attr}, 7, 5) = ?"
223 param_out << cl.uuid_prefix
229 cond_out << cond.join(' OR ')
231 raise ArgumentError.new("Invalid operator '#{operator}'")
235 conds_out << cond_out.join(' OR ') if cond_out.any?
238 {:cond_out => conds_out, :param_out => param_out, :joins => joins}