module Sequel::Postgres::AutoParameterize::DatasetMethods
Public Instance Methods
Do not add implicit typecasts for directly typecasted values, since the user is presumably doing so to set the type, not convert from the implicitly typecasted type.
# File lib/sequel/extensions/pg_auto_parameterize.rb 231 def cast_sql_append(sql, expr, type) 232 if auto_param?(sql) && auto_param_type(expr) 233 sql << 'CAST(' 234 sql.add_arg(expr) 235 sql << ' AS ' << db.cast_type_literal(type).to_s << ')' 236 else 237 super 238 end 239 end
Transform column IN (int, …) expressions into column = ANY($) and column NOT IN (int, …) expressions into column != ALL($) using an integer array bound variable for the ANY/ALL argument. This is the same optimization PostgreSQL performs internally, but this reduces the number of bound variables.
# File lib/sequel/extensions/pg_auto_parameterize.rb 246 def complex_expression_sql_append(sql, op, args) 247 case op 248 when :IN, :"NOT IN" 249 l, r = args 250 if auto_param?(sql) && !l.is_a?(Array) && _integer_array?(r) && r.size > 1 251 if op == :IN 252 op = :"=" 253 func = :ANY 254 else 255 op = :!= 256 func = :ALL 257 end 258 args = [l, Sequel.function(func, Sequel.cast(_integer_array_auto_param(r), 'int8[]'))] 259 end 260 end 261 262 super 263 end
For strings, numeric arguments, and date/time arguments, add them as parameters to the query instead of literalizing them into the SQL
.
# File lib/sequel/extensions/pg_auto_parameterize.rb 277 def literal_append(sql, v) 278 if auto_param?(sql) && (type = auto_param_type(v)) 279 sql.add_arg(v) << type 280 else 281 super 282 end 283 end
Parameterize insertion of multiple values
# File lib/sequel/extensions/pg_auto_parameterize.rb 266 def multi_insert_sql(columns, values) 267 if @opts[:no_auto_parameterize] 268 super 269 else 270 [clone(:multi_insert_values=>values.map{|r| Array(r)}).insert_sql(columns, LiteralString.new('VALUES '))] 271 end 272 end
Return a clone of the dataset that will not do automatic parameterization.
# File lib/sequel/extensions/pg_auto_parameterize.rb 222 def no_auto_parameterize 223 cached_dataset(:_no_auto_parameterize_ds) do 224 @opts[:no_auto_parameterize] ? self : clone(:no_auto_parameterize=>true) 225 end 226 end
Placeholder literalizers are not supported supported when using automatic parameterization.
# File lib/sequel/extensions/pg_auto_parameterize.rb 286 def supports_placeholder_literalizer? 287 @opts[:no_auto_parameterize] 288 end
Disable automatic parameterization when using a cursor.
# File lib/sequel/extensions/pg_auto_parameterize.rb 291 def use_cursor(*) 292 super.no_auto_parameterize 293 end
Store receiving dataset and args when with_sql
is used with a method name symbol, so sql can be parameterized correctly if used as a subselect.
# File lib/sequel/extensions/pg_auto_parameterize.rb 297 def with_sql(*a) 298 ds = super 299 if Symbol === a[0] 300 ds = ds.clone(:with_sql_dataset=>self, :with_sql_args=>a.freeze) 301 end 302 ds 303 end
Protected Instance Methods
Disable automatic parameterization for prepared statements, since they will use manual parameterization.
# File lib/sequel/extensions/pg_auto_parameterize.rb 309 def to_prepared_statement(*a) 310 @opts[:no_auto_parameterize] ? super : no_auto_parameterize.to_prepared_statement(*a) 311 end
Private Instance Methods
Handle parameterization of multi_insert_sql
# File lib/sequel/extensions/pg_auto_parameterize.rb 370 def _insert_values_sql(sql, values) 371 super 372 373 if values = @opts[:multi_insert_values] 374 expression_list_append(sql, values.map{|r| Array(r)}) 375 end 376 end
Whether the given argument is an array of integers or NULL values, recursively.
# File lib/sequel/extensions/pg_auto_parameterize.rb 379 def _integer_array?(v) 380 Array === v && v.all?{|x| nil == x || Integer === x} 381 end
Create the bound variable string that will be used for the IN (int, …) to = ANY($) optimization for integer arrays.
# File lib/sequel/extensions/pg_auto_parameterize.rb 385 def _integer_array_auto_param(v) 386 buf = String.new 387 buf << '{' 388 comma = false 389 v.each do |x| 390 if comma 391 buf << "," 392 else 393 comma = true 394 end 395 396 buf << (x ? x.to_s : 'NULL') 397 end 398 buf << '}' 399 end
Whether the given query string currently supports automatic parameterization.
# File lib/sequel/extensions/pg_auto_parameterize.rb 357 def auto_param?(sql) 358 sql.is_a?(QueryString) && sql.auto_param? 359 end
If auto parameterization is supported for the value, return a string for the implicit typecast to use. Return false/nil if the value should not be automatically parameterized.
# File lib/sequel/extensions/pg_auto_parameterize.rb 318 def auto_param_type(v) 319 case v 320 when String 321 case v 322 when LiteralString 323 false 324 when Sequel::SQL::Blob 325 "::bytea" 326 else 327 "" 328 end 329 when Integer 330 ((v > 2147483647 || v < -2147483648) ? "::int8" : "::int4") 331 when Float 332 # PostgreSQL treats literal floats as numeric, not double precision 333 # But older versions of PostgreSQL don't handle Infinity/NaN in numeric 334 v.finite? ? "::numeric" : "::double precision" 335 when BigDecimal 336 "::numeric" 337 when Sequel::SQLTime 338 "::time" 339 when Time 340 "::#{@db.cast_type_literal(Time)}" 341 when DateTime 342 "::#{@db.cast_type_literal(DateTime)}" 343 when Date 344 "::date" 345 else 346 v.respond_to?(:sequel_auto_param_type) ? v.sequel_auto_param_type(self) : auto_param_type_fallback(v) 347 end 348 end
Allow other extensions to support auto parameterization in ways that do not require adding the sequel_auto_param_type method.
# File lib/sequel/extensions/pg_auto_parameterize.rb 352 def auto_param_type_fallback(v) 353 super if defined?(super) 354 end
Default the import slice to 40, since PostgreSQL supports a maximum of 1600 columns per table, and it supports a maximum of 65k parameters. Technically, there can be more than one parameter per column, so this doesn't prevent going over the limit, though it does make it less likely.
# File lib/sequel/extensions/pg_auto_parameterize.rb 365 def default_import_slice 366 40 367 end
Skip auto parameterization in LIMIT and OFFSET clauses
# File lib/sequel/extensions/pg_auto_parameterize.rb 402 def select_limit_sql(sql) 403 if auto_param?(sql) && (@opts[:limit] || @opts[:offset]) 404 sql.skip_auto_param{super} 405 else 406 super 407 end 408 end
Skip auto parameterization in ORDER clause if used with integer values indicating ordering by the nth column.
# File lib/sequel/extensions/pg_auto_parameterize.rb 412 def select_order_sql(sql) 413 if auto_param?(sql) && (order = @opts[:order]) && order.any?{|o| Integer === o || (SQL::OrderedExpression === o && Integer === o.expression)} 414 sql.skip_auto_param{super} 415 else 416 super 417 end 418 end
Skip auto parameterization in CTE CYCLE clause
# File lib/sequel/extensions/pg_auto_parameterize.rb 421 def select_with_sql_cte_search_cycle(sql,cte) 422 if auto_param?(sql) && cte[:cycle] 423 sql.skip_auto_param{super} 424 else 425 super 426 end 427 end
Unless auto parameterization is disabled, use a string that can store the parameterized arguments.
# File lib/sequel/extensions/pg_auto_parameterize.rb 431 def sql_string_origin 432 @opts[:no_auto_parameterize] ? super : QueryString.new 433 end
Use auto parameterization for datasets with static SQL
using placeholders.
# File lib/sequel/extensions/pg_auto_parameterize.rb 457 def static_sql(sql) 458 if @opts[:append_sql] || @opts[:no_auto_parameterize] || String === sql 459 super 460 else 461 query_string = QueryString.new 462 literal_append(query_string, sql) 463 query_string 464 end 465 end
If subquery used with_sql
with a method name symbol, use the arguments to with_sql
to determine the sql, so that the subselect can be parameterized.
# File lib/sequel/extensions/pg_auto_parameterize.rb 448 def subselect_sql_append_sql(sql, ds) 449 if args = ds.opts[:subselect_sql_args] 450 ds.send(*args) 451 else 452 super 453 end 454 end
If subquery uses with_sql
with a method name symbol, get the dataset with_sql
was called on, and use that as the subquery, recording the arguments to with_sql
that will be used to calculate the sql.
# File lib/sequel/extensions/pg_auto_parameterize.rb 438 def subselect_sql_dataset(sql, ds) 439 if ws_ds = ds.opts[:with_sql_dataset] 440 super(sql, ws_ds).clone(:subselect_sql_args=>ds.opts[:with_sql_args]) 441 else 442 super 443 end 444 end