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 # :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 raise ArgumentError.new("Invalid operator for subproperty search '#{operator}'")
146 elsif operator.downcase == "exists"
147 if col.type != :jsonb
148 raise ArgumentError.new("Invalid attribute '#{attr}' for operator '#{operator}' in filter")
151 cond_out << "jsonb_exists(#{attr_table_name}.#{attr}, ?)"
154 if !attr_model_class.searchable_columns(operator).index attr
155 raise ArgumentError.new("Invalid attribute '#{attr}' in filter")
158 case operator.downcase
159 when '=', '<', '<=', '>', '>=', '!=', 'like', 'ilike'
160 attr_type = attr_model_class.attribute_column(attr).type
161 operator = '<>' if operator == '!='
162 if operand.is_a? String
163 if attr_type == :boolean
164 if not ['=', '<>'].include?(operator)
165 raise ArgumentError.new("Invalid operator '#{operator}' for " \
166 "boolean attribute '#{attr}'")
168 case operand.downcase
169 when '1', 't', 'true', 'y', 'yes'
171 when '0', 'f', 'false', 'n', 'no'
174 raise ArgumentError("Invalid operand '#{operand}' for " \
175 "boolean attribute '#{attr}'")
179 # explicitly allow NULL
180 cond_out << "#{attr_table_name}.#{attr} #{operator} ? OR #{attr_table_name}.#{attr} IS NULL"
182 cond_out << "#{attr_table_name}.#{attr} #{operator} ?"
184 if (# any operator that operates on value rather than
186 operator.match(/[<=>]/) and (attr_type == :datetime))
187 operand = Time.parse operand
190 elsif operand.nil? and operator == '='
191 cond_out << "#{attr_table_name}.#{attr} is null"
192 elsif operand.nil? and operator == '<>'
193 cond_out << "#{attr_table_name}.#{attr} is not null"
194 elsif (attr_type == :boolean) and ['=', '<>'].include?(operator) and
195 [true, false].include?(operand)
196 cond_out << "#{attr_table_name}.#{attr} #{operator} ?"
198 elsif (attr_type == :integer)
199 cond_out << "#{attr_table_name}.#{attr} #{operator} ?"
202 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
203 "for '#{operator}' operator in filters")
206 if operand.is_a? Array
207 cond_out << "#{attr_table_name}.#{attr} #{operator} (?)"
209 if operator == 'not in' and not operand.include?(nil)
210 # explicitly allow NULL
211 cond_out[-1] = "(#{cond_out[-1]} OR #{attr_table_name}.#{attr} IS NULL)"
214 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
215 "for '#{operator}' operator in filters")
218 operand = [operand] unless operand.is_a? Array
221 cl = ArvadosModel::kind_class op
224 if attr_model_class.uuid_prefix == cl.uuid_prefix
230 # Use a substring query to support remote uuids
231 cond << "substring(#{attr_table_name}.#{attr}, 7, 5) = ?"
232 param_out << cl.uuid_prefix
238 cond_out << cond.join(' OR ')
240 raise ArgumentError.new("Invalid operator '#{operator}'")
244 conds_out << cond_out.join(' OR ') if cond_out.any?
247 {:cond_out => conds_out, :param_out => param_out, :joins => joins}