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")
49 if attrs_in == 'any' && (operator.casecmp('ilike').zero? || operator.casecmp('like').zero?) && (operand.is_a? String) && operand.match('^[%].*[%]$')
50 # Trigram index search
51 cond_out << model_class.full_text_trgm + " #{operator} ?"
53 # Skip the generic per-column operator loop below
60 raise ArgumentError.new("Full text search on individual columns is not supported")
62 if operand.is_a? Array
63 raise ArgumentError.new("Full text search not supported for array operands")
66 # Skip the generic per-column operator loop below
68 # Use to_tsquery since plainto_tsquery does not support prefix
69 # search. And, split operand and join the words with ' & '
70 cond_out << model_class.full_text_tsvector+" @@ to_tsquery(?)"
71 param_out << operand.split.join(' & ')
74 subproperty = attr.split(".", 2)
76 if subproperty.length == 2 && subproperty[0] == 'container' && model_table_name == "container_requests"
77 # attr is "tablename.colname" -- e.g., ["container.state", "=", "Complete"]
78 joins = ["JOIN containers ON container_requests.container_uuid = containers.uuid"]
79 attr_model_class = Container
80 attr_table_name = "containers"
81 subproperty = subproperty[1].split(".", 2)
83 attr_model_class = model_class
84 attr_table_name = model_table_name
88 proppath = subproperty[1]
89 col = attr_model_class.columns.select { |c| c.name == attr }.first
92 if col.nil? or col.type != :jsonb
93 raise ArgumentError.new("Invalid attribute '#{attr}' for subproperty filter")
96 if proppath[0] == "<" and proppath[-1] == ">"
97 proppath = proppath[1..-2]
101 case operator.downcase
103 not_in = if operator.downcase == "!=" then "NOT " else "" end
104 cond_out << "#{not_in}(#{attr_table_name}.#{attr} @> ?::jsonb)"
105 param_out << SafeJSON.dump({proppath => operand})
107 if operand.is_a? Array
108 operand.each do |opr|
109 cond_out << "#{attr_table_name}.#{attr} @> ?::jsonb"
110 param_out << SafeJSON.dump({proppath => opr})
113 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
114 "for '#{operator}' operator in filters")
116 when '<', '<=', '>', '>='
117 cond_out << "#{attr_table_name}.#{attr}->? #{operator} ?::jsonb"
118 param_out << proppath
119 param_out << SafeJSON.dump(operand)
121 cond_out << "#{attr_table_name}.#{attr}->>? #{operator} ?"
122 param_out << proppath
125 if operand.is_a? Array
126 cond_out << "#{attr_table_name}.#{attr}->>? NOT IN (?) OR #{attr_table_name}.#{attr}->>? IS NULL"
127 param_out << proppath
129 param_out << proppath
131 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
132 "for '#{operator}' operator in filters")
136 cond_out << "jsonb_exists(#{attr_table_name}.#{attr}, ?)"
137 elsif operand == false
138 cond_out << "(NOT jsonb_exists(#{attr_table_name}.#{attr}, ?)) OR #{attr_table_name}.#{attr} is NULL"
140 raise ArgumentError.new("Invalid operand '#{operand}' for '#{operator}' must be true or false")
142 param_out << proppath
144 cond_out << "#{attr_table_name}.#{attr} @> ?::jsonb OR #{attr_table_name}.#{attr} @> ?::jsonb"
145 param_out << SafeJSON.dump({proppath => operand})
146 param_out << SafeJSON.dump({proppath => [operand]})
148 raise ArgumentError.new("Invalid operator for subproperty search '#{operator}'")
150 elsif operator.downcase == "exists"
151 if col.type != :jsonb
152 raise ArgumentError.new("Invalid attribute '#{attr}' for operator '#{operator}' in filter")
155 cond_out << "jsonb_exists(#{attr_table_name}.#{attr}, ?)"
158 if !attr_model_class.searchable_columns(operator).index attr
159 raise ArgumentError.new("Invalid attribute '#{attr}' in filter")
162 case operator.downcase
163 when '=', '<', '<=', '>', '>=', '!=', 'like', 'ilike'
164 attr_type = attr_model_class.attribute_column(attr).type
165 operator = '<>' if operator == '!='
166 if operand.is_a? String
167 if attr_type == :boolean
168 if not ['=', '<>'].include?(operator)
169 raise ArgumentError.new("Invalid operator '#{operator}' for " \
170 "boolean attribute '#{attr}'")
172 case operand.downcase
173 when '1', 't', 'true', 'y', 'yes'
175 when '0', 'f', 'false', 'n', 'no'
178 raise ArgumentError("Invalid operand '#{operand}' for " \
179 "boolean attribute '#{attr}'")
183 # explicitly allow NULL
184 cond_out << "#{attr_table_name}.#{attr} #{operator} ? OR #{attr_table_name}.#{attr} IS NULL"
186 cond_out << "#{attr_table_name}.#{attr} #{operator} ?"
188 if (# any operator that operates on value rather than
190 operator.match(/[<=>]/) and (attr_type == :datetime))
191 operand = Time.parse operand
194 elsif operand.nil? and operator == '='
195 cond_out << "#{attr_table_name}.#{attr} is null"
196 elsif operand.nil? and operator == '<>'
197 cond_out << "#{attr_table_name}.#{attr} is not null"
198 elsif (attr_type == :boolean) and ['=', '<>'].include?(operator) and
199 [true, false].include?(operand)
200 cond_out << "#{attr_table_name}.#{attr} #{operator} ?"
202 elsif (attr_type == :integer)
203 cond_out << "#{attr_table_name}.#{attr} #{operator} ?"
206 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
207 "for '#{operator}' operator in filters")
210 if operand.is_a? Array
211 cond_out << "#{attr_table_name}.#{attr} #{operator} (?)"
213 if operator == 'not in' and not operand.include?(nil)
214 # explicitly allow NULL
215 cond_out[-1] = "(#{cond_out[-1]} OR #{attr_table_name}.#{attr} IS NULL)"
218 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
219 "for '#{operator}' operator in filters")
222 operand = [operand] unless operand.is_a? Array
225 cl = ArvadosModel::kind_class op
228 if attr_model_class.uuid_prefix == cl.uuid_prefix
234 # Use a substring query to support remote uuids
235 cond << "substring(#{attr_table_name}.#{attr}, 7, 5) = ?"
236 param_out << cl.uuid_prefix
242 cond_out << cond.join(' OR ')
244 raise ArgumentError.new("Invalid operator '#{operator}'")
248 conds_out << cond_out.join(' OR ') if cond_out.any?
251 {:cond_out => conds_out, :param_out => param_out, :joins => joins}