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}, ?)"
144 elsif expr = /^ *\( *(\w+) *(<=?|>=?|=) *(\w+) *\) *$/.match(attr)
145 if operator != '=' || ![true,"true"].index(operand)
146 raise ArgumentError.new("Invalid expression filter '#{attr}': subsequent elements must be [\"=\", true]")
149 attr1, attr2 = expr[1], expr[3]
150 allowed = attr_model_class.searchable_columns(operator)
151 [attr1, attr2].each do |tok|
152 if !allowed.index(tok)
153 raise ArgumentError.new("Invalid attribute in expression: '#{tok}'")
155 col = attr_model_class.columns.select { |c| c.name == tok }.first
156 if col.type != :integer
157 raise ArgumentError.new("Non-numeric attribute in expression: '#{tok}'")
160 cond_out << "#{attr1} #{operator} #{attr2}"
162 if !attr_model_class.searchable_columns(operator).index attr
163 raise ArgumentError.new("Invalid attribute '#{attr}' in filter")
166 case operator.downcase
167 when '=', '<', '<=', '>', '>=', '!=', 'like', 'ilike'
168 attr_type = attr_model_class.attribute_column(attr).type
169 operator = '<>' if operator == '!='
170 if operand.is_a? String
171 if attr_type == :boolean
172 if not ['=', '<>'].include?(operator)
173 raise ArgumentError.new("Invalid operator '#{operator}' for " \
174 "boolean attribute '#{attr}'")
176 case operand.downcase
177 when '1', 't', 'true', 'y', 'yes'
179 when '0', 'f', 'false', 'n', 'no'
182 raise ArgumentError("Invalid operand '#{operand}' for " \
183 "boolean attribute '#{attr}'")
187 # explicitly allow NULL
188 cond_out << "#{attr_table_name}.#{attr} #{operator} ? OR #{attr_table_name}.#{attr} IS NULL"
190 cond_out << "#{attr_table_name}.#{attr} #{operator} ?"
192 if (# any operator that operates on value rather than
194 operator.match(/[<=>]/) and (attr_type == :datetime))
195 operand = Time.parse operand
198 elsif operand.nil? and operator == '='
199 cond_out << "#{attr_table_name}.#{attr} is null"
200 elsif operand.nil? and operator == '<>'
201 cond_out << "#{attr_table_name}.#{attr} is not null"
202 elsif (attr_type == :boolean) and ['=', '<>'].include?(operator) and
203 [true, false].include?(operand)
204 cond_out << "#{attr_table_name}.#{attr} #{operator} ?"
206 elsif (attr_type == :integer)
207 cond_out << "#{attr_table_name}.#{attr} #{operator} ?"
210 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
211 "for '#{operator}' operator in filters")
214 if operand.is_a? Array
215 cond_out << "#{attr_table_name}.#{attr} #{operator} (?)"
217 if operator == 'not in' and not operand.include?(nil)
218 # explicitly allow NULL
219 cond_out[-1] = "(#{cond_out[-1]} OR #{attr_table_name}.#{attr} IS NULL)"
222 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
223 "for '#{operator}' operator in filters")
226 operand = [operand] unless operand.is_a? Array
229 cl = ArvadosModel::kind_class op
232 if attr_model_class.uuid_prefix == cl.uuid_prefix
238 # Use a substring query to support remote uuids
239 cond << "substring(#{attr_table_name}.#{attr}, 7, 5) = ?"
240 param_out << cl.uuid_prefix
246 cond_out << cond.join(' OR ')
248 raise ArgumentError.new("Invalid operator '#{operator}'")
252 conds_out << cond_out.join(' OR ') if cond_out.any?
255 {:cond_out => conds_out, :param_out => param_out, :joins => joins}