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 258 def cast_sql_append(sql, expr, type) 259 if auto_param?(sql) && auto_param_type(expr) 260 sql << 'CAST(' 261 sql.add_arg(expr) 262 sql << ' AS ' << db.cast_type_literal(type).to_s << ')' 263 else 264 super 265 end 266 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 273 def complex_expression_sql_append(sql, op, args) 274 case op 275 when :IN, :"NOT IN" 276 l, r = args 277 if auto_param?(sql) && !l.is_a?(Array) && _integer_array?(r) && r.size > 1 278 if op == :IN 279 op = :"=" 280 func = :ANY 281 else 282 op = :!= 283 func = :ALL 284 end 285 args = [l, Sequel.function(func, Sequel.cast(_integer_array_auto_param(r), 'int8[]'))] 286 end 287 end 288 289 super 290 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 304 def literal_append(sql, v) 305 if auto_param?(sql) && (type = auto_param_type(v)) 306 sql.add_arg(v) << type 307 else 308 super 309 end 310 end
Parameterize insertion of multiple values
# File lib/sequel/extensions/pg_auto_parameterize.rb 293 def multi_insert_sql(columns, values) 294 if @opts[:no_auto_parameterize] 295 super 296 else 297 [clone(:multi_insert_values=>values.map{|r| Array(r)}).insert_sql(columns, LiteralString.new('VALUES '))] 298 end 299 end
Return a clone of the dataset that will not do automatic parameterization.
# File lib/sequel/extensions/pg_auto_parameterize.rb 249 def no_auto_parameterize 250 cached_dataset(:_no_auto_parameterize_ds) do 251 @opts[:no_auto_parameterize] ? self : clone(:no_auto_parameterize=>true) 252 end 253 end
The class to use for placeholder literalizers.
# File lib/sequel/extensions/pg_auto_parameterize.rb 313 def placeholder_literalizer_class 314 if @opts[:no_auto_parameterize] 315 super 316 else 317 PlaceholderLiteralizer 318 end 319 end
Disable automatic parameterization when using a cursor.
# File lib/sequel/extensions/pg_auto_parameterize.rb 322 def use_cursor(*) 323 super.no_auto_parameterize 324 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 328 def with_sql(*a) 329 ds = super 330 if Symbol === a[0] 331 ds = ds.clone(:with_sql_dataset=>self, :with_sql_args=>a.freeze) 332 end 333 ds 334 end
Protected Instance Methods
Disable automatic parameterization for prepared statements, since they will use manual parameterization.
# File lib/sequel/extensions/pg_auto_parameterize.rb 340 def to_prepared_statement(*a) 341 @opts[:no_auto_parameterize] ? super : no_auto_parameterize.to_prepared_statement(*a) 342 end
Private Instance Methods
Handle parameterization of multi_insert_sql
# File lib/sequel/extensions/pg_auto_parameterize.rb 401 def _insert_values_sql(sql, values) 402 super 403 404 if values = @opts[:multi_insert_values] 405 expression_list_append(sql, values.map{|r| Array(r)}) 406 end 407 end
Whether the given argument is an array of integers or NULL values, recursively.
# File lib/sequel/extensions/pg_auto_parameterize.rb 410 def _integer_array?(v) 411 Array === v && v.all?{|x| nil == x || Integer === x} 412 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 416 def _integer_array_auto_param(v) 417 buf = String.new 418 buf << '{' 419 comma = false 420 v.each do |x| 421 if comma 422 buf << "," 423 else 424 comma = true 425 end 426 427 buf << (x ? x.to_s : 'NULL') 428 end 429 buf << '}' 430 end
Whether the given query string currently supports automatic parameterization.
# File lib/sequel/extensions/pg_auto_parameterize.rb 388 def auto_param?(sql) 389 sql.is_a?(QueryString) && sql.auto_param? 390 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 349 def auto_param_type(v) 350 case v 351 when String 352 case v 353 when LiteralString 354 false 355 when Sequel::SQL::Blob 356 "::bytea" 357 else 358 "" 359 end 360 when Integer 361 ((v > 2147483647 || v < -2147483648) ? "::int8" : "::int4") 362 when Float 363 # PostgreSQL treats literal floats as numeric, not double precision 364 # But older versions of PostgreSQL don't handle Infinity/NaN in numeric 365 v.finite? ? "::numeric" : "::double precision" 366 when BigDecimal 367 "::numeric" 368 when Sequel::SQLTime 369 "::time" 370 when Time 371 "::#{@db.cast_type_literal(Time)}" 372 when DateTime 373 "::#{@db.cast_type_literal(DateTime)}" 374 when Date 375 "::date" 376 else 377 v.respond_to?(:sequel_auto_param_type) ? v.sequel_auto_param_type(self) : auto_param_type_fallback(v) 378 end 379 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 383 def auto_param_type_fallback(v) 384 super if defined?(super) 385 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 396 def default_import_slice 397 40 398 end
Skip auto parameterization in LIMIT and OFFSET clauses
# File lib/sequel/extensions/pg_auto_parameterize.rb 433 def select_limit_sql(sql) 434 if auto_param?(sql) && (@opts[:limit] || @opts[:offset]) 435 sql.skip_auto_param{super} 436 else 437 super 438 end 439 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 443 def select_order_sql(sql) 444 if auto_param?(sql) && (order = @opts[:order]) && order.any?{|o| Integer === o || (SQL::OrderedExpression === o && Integer === o.expression)} 445 sql.skip_auto_param{super} 446 else 447 super 448 end 449 end
Skip auto parameterization in CTE CYCLE clause
# File lib/sequel/extensions/pg_auto_parameterize.rb 452 def select_with_sql_cte_search_cycle(sql,cte) 453 if auto_param?(sql) && cte[:cycle] 454 sql.skip_auto_param{super} 455 else 456 super 457 end 458 end
Unless auto parameterization is disabled, use a string that can store the parameterized arguments.
# File lib/sequel/extensions/pg_auto_parameterize.rb 462 def sql_string_origin 463 @opts[:no_auto_parameterize] ? super : QueryString.new 464 end
Use auto parameterization for datasets with static SQL
using placeholders.
# File lib/sequel/extensions/pg_auto_parameterize.rb 488 def static_sql(sql) 489 if @opts[:append_sql] || @opts[:no_auto_parameterize] || String === sql 490 super 491 else 492 query_string = QueryString.new 493 literal_append(query_string, sql) 494 query_string 495 end 496 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 479 def subselect_sql_append_sql(sql, ds) 480 if args = ds.opts[:subselect_sql_args] 481 ds.send(*args) 482 else 483 super 484 end 485 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 469 def subselect_sql_dataset(sql, ds) 470 if ws_ds = ds.opts[:with_sql_dataset] 471 super(sql, ws_ds).clone(:subselect_sql_args=>ds.opts[:with_sql_args]) 472 else 473 super 474 end 475 end