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")
50 raise ArgumentError.new("Full text search on individual columns is not supported")
52 if operand.is_a? Array
53 raise ArgumentError.new("Full text search not supported for array operands")
56 # Skip the generic per-column operator loop below
58 # Use to_tsquery since plainto_tsquery does not support prefix
59 # search. And, split operand and join the words with ' & '
60 cond_out << model_class.full_text_tsvector+" @@ to_tsquery(?)"
61 param_out << operand.split.join(' & ')
64 subproperty = attr.split(".", 2)
66 col = model_class.columns.select { |c| c.name == subproperty[0] }.first
68 if subproperty.length == 2
69 if col.nil? or col.type != :jsonb
70 raise ArgumentError.new("Invalid attribute '#{subproperty[0]}' for subproperty filter")
73 if subproperty[1][0] == "<" and subproperty[1][-1] == ">"
74 subproperty[1] = subproperty[1][1..-2]
78 case operator.downcase
80 not_in = if operator.downcase == "!=" then "NOT " else "" end
81 cond_out << "#{not_in}(#{ar_table_name}.#{subproperty[0]} @> ?::jsonb)"
82 param_out << SafeJSON.dump({subproperty[1] => operand})
84 if operand.is_a? Array
86 cond_out << "#{ar_table_name}.#{subproperty[0]} @> ?::jsonb"
87 param_out << SafeJSON.dump({subproperty[1] => opr})
90 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
91 "for '#{operator}' operator in filters")
93 when '<', '<=', '>', '>='
94 cond_out << "#{ar_table_name}.#{subproperty[0]}->? #{operator} ?::jsonb"
95 param_out << subproperty[1]
96 param_out << SafeJSON.dump(operand)
98 cond_out << "#{ar_table_name}.#{subproperty[0]}->>? #{operator} ?"
99 param_out << subproperty[1]
102 if operand.is_a? Array
103 cond_out << "#{ar_table_name}.#{subproperty[0]}->>? NOT IN (?) OR #{ar_table_name}.#{subproperty[0]}->>? IS NULL"
104 param_out << subproperty[1]
106 param_out << subproperty[1]
108 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
109 "for '#{operator}' operator in filters")
113 cond_out << "jsonb_exists(#{ar_table_name}.#{subproperty[0]}, ?)"
114 elsif operand == false
115 cond_out << "(NOT jsonb_exists(#{ar_table_name}.#{subproperty[0]}, ?)) OR #{ar_table_name}.#{subproperty[0]} is NULL"
117 raise ArgumentError.new("Invalid operand '#{operand}' for '#{operator}' must be true or false")
119 param_out << subproperty[1]
121 raise ArgumentError.new("Invalid operator for subproperty search '#{operator}'")
123 elsif operator.downcase == "exists"
124 if col.type != :jsonb
125 raise ArgumentError.new("Invalid attribute '#{subproperty[0]}' for operator '#{operator}' in filter")
128 cond_out << "jsonb_exists(#{ar_table_name}.#{subproperty[0]}, ?)"
131 if !model_class.searchable_columns(operator).index subproperty[0]
132 raise ArgumentError.new("Invalid attribute '#{subproperty[0]}' in filter")
135 case operator.downcase
136 when '=', '<', '<=', '>', '>=', '!=', 'like', 'ilike'
137 attr_type = model_class.attribute_column(attr).type
138 operator = '<>' if operator == '!='
139 if operand.is_a? String
140 if attr_type == :boolean
141 if not ['=', '<>'].include?(operator)
142 raise ArgumentError.new("Invalid operator '#{operator}' for " \
143 "boolean attribute '#{attr}'")
145 case operand.downcase
146 when '1', 't', 'true', 'y', 'yes'
148 when '0', 'f', 'false', 'n', 'no'
151 raise ArgumentError("Invalid operand '#{operand}' for " \
152 "boolean attribute '#{attr}'")
156 # explicitly allow NULL
157 cond_out << "#{ar_table_name}.#{attr} #{operator} ? OR #{ar_table_name}.#{attr} IS NULL"
159 cond_out << "#{ar_table_name}.#{attr} #{operator} ?"
161 if (# any operator that operates on value rather than
163 operator.match(/[<=>]/) and (attr_type == :datetime))
164 operand = Time.parse operand
167 elsif operand.nil? and operator == '='
168 cond_out << "#{ar_table_name}.#{attr} is null"
169 elsif operand.nil? and operator == '<>'
170 cond_out << "#{ar_table_name}.#{attr} is not null"
171 elsif (attr_type == :boolean) and ['=', '<>'].include?(operator) and
172 [true, false].include?(operand)
173 cond_out << "#{ar_table_name}.#{attr} #{operator} ?"
175 elsif (attr_type == :integer)
176 cond_out << "#{ar_table_name}.#{attr} #{operator} ?"
179 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
180 "for '#{operator}' operator in filters")
183 if operand.is_a? Array
184 cond_out << "#{ar_table_name}.#{attr} #{operator} (?)"
186 if operator == 'not in' and not operand.include?(nil)
187 # explicitly allow NULL
188 cond_out[-1] = "(#{cond_out[-1]} OR #{ar_table_name}.#{attr} IS NULL)"
191 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
192 "for '#{operator}' operator in filters")
195 operand = [operand] unless operand.is_a? Array
198 cl = ArvadosModel::kind_class op
201 if model_class.uuid_prefix == cl.uuid_prefix
207 # Use a substring query to support remote uuids
208 cond << "substring(#{ar_table_name}.#{attr}, 7, 5) = ?"
209 param_out << cl.uuid_prefix
215 cond_out << cond.join(' OR ')
217 raise ArgumentError.new("Invalid operator '#{operator}'")
221 conds_out << cond_out.join(' OR ') if cond_out.any?
224 {:cond_out => conds_out, :param_out => param_out}