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
23 # :cond_out array of SQL fragments for each filter expression
24 # :param_out array of values for parameter substitution in cond_out
25 def record_filters filters, model_class
29 ar_table_name = model_class.table_name
30 filters.each do |filter|
31 attrs_in, operator, operand = filter
32 if attrs_in == 'any' && operator != '@@'
33 attrs = model_class.searchable_columns(operator)
34 elsif attrs_in.is_a? Array
39 if !filter.is_a? Array
40 raise ArgumentError.new("Invalid element in filters array: #{filter.inspect} is not an array")
41 elsif !operator.is_a? String
42 raise ArgumentError.new("Invalid operator '#{operator}' (#{operator.class}) in filter")
47 if attrs_in == 'any' && (operator.casecmp('ilike').zero? || operator.casecmp('like').zero?) && (operand.is_a? String) && operand.match('^[%].*[%]$')
48 # Trigram index search
49 cond_out << model_class.full_text_trgm + " #{operator} ?"
51 # Skip the generic per-column operator loop below
58 raise ArgumentError.new("Full text search on individual columns is not supported")
60 if operand.is_a? Array
61 raise ArgumentError.new("Full text search not supported for array operands")
64 # Skip the generic per-column operator loop below
66 # Use to_tsquery since plainto_tsquery does not support prefix
67 # search. And, split operand and join the words with ' & '
68 cond_out << model_class.full_text_tsvector+" @@ to_tsquery(?)"
69 param_out << operand.split.join(' & ')
72 subproperty = attr.split(".", 2)
74 col = model_class.columns.select { |c| c.name == subproperty[0] }.first
76 if subproperty.length == 2
77 if col.nil? or col.type != :jsonb
78 raise ArgumentError.new("Invalid attribute '#{subproperty[0]}' for subproperty filter")
81 if subproperty[1][0] == "<" and subproperty[1][-1] == ">"
82 subproperty[1] = subproperty[1][1..-2]
86 case operator.downcase
88 not_in = if operator.downcase == "!=" then "NOT " else "" end
89 cond_out << "#{not_in}(#{ar_table_name}.#{subproperty[0]} @> ?::jsonb)"
90 param_out << SafeJSON.dump({subproperty[1] => operand})
92 if operand.is_a? Array
94 cond_out << "#{ar_table_name}.#{subproperty[0]} @> ?::jsonb"
95 param_out << SafeJSON.dump({subproperty[1] => opr})
98 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
99 "for '#{operator}' operator in filters")
101 when '<', '<=', '>', '>='
102 cond_out << "#{ar_table_name}.#{subproperty[0]}->? #{operator} ?::jsonb"
103 param_out << subproperty[1]
104 param_out << SafeJSON.dump(operand)
106 cond_out << "#{ar_table_name}.#{subproperty[0]}->>? #{operator} ?"
107 param_out << subproperty[1]
110 if operand.is_a? Array
111 cond_out << "#{ar_table_name}.#{subproperty[0]}->>? NOT IN (?) OR #{ar_table_name}.#{subproperty[0]}->>? IS NULL"
112 param_out << subproperty[1]
114 param_out << subproperty[1]
116 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
117 "for '#{operator}' operator in filters")
121 cond_out << "jsonb_exists(#{ar_table_name}.#{subproperty[0]}, ?)"
122 elsif operand == false
123 cond_out << "(NOT jsonb_exists(#{ar_table_name}.#{subproperty[0]}, ?)) OR #{ar_table_name}.#{subproperty[0]} is NULL"
125 raise ArgumentError.new("Invalid operand '#{operand}' for '#{operator}' must be true or false")
127 param_out << subproperty[1]
129 raise ArgumentError.new("Invalid operator for subproperty search '#{operator}'")
131 elsif operator.downcase == "exists"
132 if col.type != :jsonb
133 raise ArgumentError.new("Invalid attribute '#{subproperty[0]}' for operator '#{operator}' in filter")
136 cond_out << "jsonb_exists(#{ar_table_name}.#{subproperty[0]}, ?)"
139 if !model_class.searchable_columns(operator).index subproperty[0]
140 raise ArgumentError.new("Invalid attribute '#{subproperty[0]}' in filter")
143 case operator.downcase
144 when '=', '<', '<=', '>', '>=', '!=', 'like', 'ilike'
145 attr_type = model_class.attribute_column(attr).type
146 operator = '<>' if operator == '!='
147 if operand.is_a? String
148 if attr_type == :boolean
149 if not ['=', '<>'].include?(operator)
150 raise ArgumentError.new("Invalid operator '#{operator}' for " \
151 "boolean attribute '#{attr}'")
153 case operand.downcase
154 when '1', 't', 'true', 'y', 'yes'
156 when '0', 'f', 'false', 'n', 'no'
159 raise ArgumentError("Invalid operand '#{operand}' for " \
160 "boolean attribute '#{attr}'")
164 # explicitly allow NULL
165 cond_out << "#{ar_table_name}.#{attr} #{operator} ? OR #{ar_table_name}.#{attr} IS NULL"
167 cond_out << "#{ar_table_name}.#{attr} #{operator} ?"
169 if (# any operator that operates on value rather than
171 operator.match(/[<=>]/) and (attr_type == :datetime))
172 operand = Time.parse operand
175 elsif operand.nil? and operator == '='
176 cond_out << "#{ar_table_name}.#{attr} is null"
177 elsif operand.nil? and operator == '<>'
178 cond_out << "#{ar_table_name}.#{attr} is not null"
179 elsif (attr_type == :boolean) and ['=', '<>'].include?(operator) and
180 [true, false].include?(operand)
181 cond_out << "#{ar_table_name}.#{attr} #{operator} ?"
183 elsif (attr_type == :integer)
184 cond_out << "#{ar_table_name}.#{attr} #{operator} ?"
187 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
188 "for '#{operator}' operator in filters")
191 if operand.is_a? Array
192 cond_out << "#{ar_table_name}.#{attr} #{operator} (?)"
194 if operator == 'not in' and not operand.include?(nil)
195 # explicitly allow NULL
196 cond_out[-1] = "(#{cond_out[-1]} OR #{ar_table_name}.#{attr} IS NULL)"
199 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
200 "for '#{operator}' operator in filters")
203 operand = [operand] unless operand.is_a? Array
206 cl = ArvadosModel::kind_class op
209 if model_class.uuid_prefix == cl.uuid_prefix
215 # Use a substring query to support remote uuids
216 cond << "substring(#{ar_table_name}.#{attr}, 7, 5) = ?"
217 param_out << cl.uuid_prefix
223 cond_out << cond.join(' OR ')
225 raise ArgumentError.new("Invalid operator '#{operator}'")
229 conds_out << cond_out.join(' OR ') if cond_out.any?
232 {:cond_out => conds_out, :param_out => param_out}