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
34 if attrs_in == 'any' && operator != '@@'
35 attrs = model_class.searchable_columns(operator)
36 elsif attrs_in.is_a? Array
41 if !filter.is_a? Array
42 raise ArgumentError.new("Invalid element in filters array: #{filter.inspect} is not an array")
43 elsif !operator.is_a? String
44 raise ArgumentError.new("Invalid operator '#{operator}' (#{operator.class}) in filter")
47 operator = operator.downcase
50 if attrs_in == 'any' && (operator == 'ilike' || operator == 'like') && (operand.is_a? String) && operand.match('^[%].*[%]$')
51 # Trigram index search
52 cond_out << model_class.full_text_trgm + " #{operator} ?"
54 # Skip the generic per-column operator loop below
61 raise ArgumentError.new("Full text search on individual columns is not supported")
63 if operand.is_a? Array
64 raise ArgumentError.new("Full text search not supported for array operands")
67 # Skip the generic per-column operator loop below
69 # Use to_tsquery since plainto_tsquery does not support prefix
70 # search. And, split operand and join the words with ' & '
71 cond_out << model_class.full_text_tsvector+" @@ to_tsquery(?)"
72 param_out << operand.split.join(' & ')
75 subproperty = attr.split(".", 2)
77 if subproperty.length == 2 && subproperty[0] == 'container' && model_table_name == "container_requests"
78 # attr is "tablename.colname" -- e.g., ["container.state", "=", "Complete"]
79 joins = ["JOIN containers ON container_requests.container_uuid = containers.uuid"]
80 attr_model_class = Container
81 attr_table_name = "containers"
82 subproperty = subproperty[1].split(".", 2)
84 attr_model_class = model_class
85 attr_table_name = model_table_name
89 proppath = subproperty[1]
90 col = attr_model_class.columns.select { |c| c.name == attr }.first
93 if col.nil? or col.type != :jsonb
94 raise ArgumentError.new("Invalid attribute '#{attr}' for subproperty filter")
97 if proppath[0] == "<" and proppath[-1] == ">"
98 proppath = proppath[1..-2]
104 not_in = if operator == "!=" then "NOT " else "" end
105 cond_out << "#{not_in}(#{attr_table_name}.#{attr} @> ?::jsonb)"
106 param_out << SafeJSON.dump({proppath => operand})
108 if operand.is_a? Array
109 operand.each do |opr|
110 cond_out << "#{attr_table_name}.#{attr} @> ?::jsonb"
111 param_out << SafeJSON.dump({proppath => opr})
114 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
115 "for '#{operator}' operator in filters")
117 when '<', '<=', '>', '>='
118 cond_out << "#{attr_table_name}.#{attr}->? #{operator} ?::jsonb"
119 param_out << proppath
120 param_out << SafeJSON.dump(operand)
122 cond_out << "#{attr_table_name}.#{attr}->>? #{operator} ?"
123 param_out << proppath
126 if operand.is_a? Array
127 cond_out << "#{attr_table_name}.#{attr}->>? NOT IN (?) OR #{attr_table_name}.#{attr}->>? IS NULL"
128 param_out << proppath
130 param_out << proppath
132 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
133 "for '#{operator}' operator in filters")
137 cond_out << "jsonb_exists(#{attr_table_name}.#{attr}, ?)"
138 elsif operand == false
139 cond_out << "(NOT jsonb_exists(#{attr_table_name}.#{attr}, ?)) OR #{attr_table_name}.#{attr} is NULL"
141 raise ArgumentError.new("Invalid operand '#{operand}' for '#{operator}' must be true or false")
143 param_out << proppath
145 cond_out << "#{attr_table_name}.#{attr} @> ?::jsonb OR #{attr_table_name}.#{attr} @> ?::jsonb"
146 param_out << SafeJSON.dump({proppath => operand})
147 param_out << SafeJSON.dump({proppath => [operand]})
149 raise ArgumentError.new("Invalid operator for subproperty search '#{operator}'")
151 elsif operator == "exists"
152 if col.type != :jsonb
153 raise ArgumentError.new("Invalid attribute '#{attr}' for operator '#{operator}' in filter")
156 cond_out << "jsonb_exists(#{attr_table_name}.#{attr}, ?)"
159 if !attr_model_class.searchable_columns(operator).index(attr) &&
160 !(col.andand.type == :jsonb && ['contains', '=', '<>', '!='].index(operator))
161 raise ArgumentError.new("Invalid attribute '#{attr}' in filter")
165 when '=', '<', '<=', '>', '>=', '!=', 'like', 'ilike'
166 attr_type = attr_model_class.attribute_column(attr).type
167 operator = '<>' if operator == '!='
168 if operand.is_a? String
169 if attr_type == :boolean
170 if not ['=', '<>'].include?(operator)
171 raise ArgumentError.new("Invalid operator '#{operator}' for " \
172 "boolean attribute '#{attr}'")
174 case operand.downcase
175 when '1', 't', 'true', 'y', 'yes'
177 when '0', 'f', 'false', 'n', 'no'
180 raise ArgumentError("Invalid operand '#{operand}' for " \
181 "boolean attribute '#{attr}'")
185 # explicitly allow NULL
186 cond_out << "#{attr_table_name}.#{attr} #{operator} ? OR #{attr_table_name}.#{attr} IS NULL"
188 cond_out << "#{attr_table_name}.#{attr} #{operator} ?"
190 if (# any operator that operates on value rather than
192 operator.match(/[<=>]/) and (attr_type == :datetime))
193 operand = Time.parse operand
196 elsif operand.nil? and operator == '='
197 cond_out << "#{attr_table_name}.#{attr} is null"
198 elsif operand.nil? and operator == '<>'
199 cond_out << "#{attr_table_name}.#{attr} is not null"
200 elsif (attr_type == :boolean) and ['=', '<>'].include?(operator) and
201 [true, false].include?(operand)
202 cond_out << "#{attr_table_name}.#{attr} #{operator} ?"
204 elsif (attr_type == :integer)
205 cond_out << "#{attr_table_name}.#{attr} #{operator} ?"
208 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
209 "for '#{operator}' operator in filters")
212 if operand.is_a? Array
213 cond_out << "#{attr_table_name}.#{attr} #{operator} (?)"
215 if operator == 'not in' and not operand.include?(nil)
216 # explicitly allow NULL
217 cond_out[-1] = "(#{cond_out[-1]} OR #{attr_table_name}.#{attr} IS NULL)"
220 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
221 "for '#{operator}' operator in filters")
224 operand = [operand] unless operand.is_a? Array
227 cl = ArvadosModel::kind_class op
230 if attr_model_class.uuid_prefix == cl.uuid_prefix
236 # Use a substring query to support remote uuids
237 cond << "substring(#{attr_table_name}.#{attr}, 7, 5) = ?"
238 param_out << cl.uuid_prefix
244 cond_out << cond.join(' OR ')
246 if col.andand.type != :jsonb
247 raise ArgumentError.new("Invalid attribute '#{attr}' for '#{operator}' operator")
250 raise ArgumentError.new("Invalid operand '#{operand.inspect}' for '#{operator}' operator")
252 operand = [operand] unless operand.is_a? Array
255 raise ArgumentError.new("Invalid element #{operand.inspect} in operand for #{operator.inspect} operator (operand must be a string or array of strings)")
258 q = operand.map { |s| ActiveRecord::Base.connection.quote(s) }.join(',')
259 # We use jsonb_exists_all(a,b) instead of "a ?& b" because
260 # the pg gem thinks "?" is a bind var.
261 cond_out << "jsonb_exists_all(#{attr_table_name}.#{attr}, array[#{q}])"
263 raise ArgumentError.new("Invalid operator '#{operator}'")
267 conds_out << cond_out.join(' OR ') if cond_out.any?
270 {:cond_out => conds_out, :param_out => param_out, :joins => joins}