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 403 def _insert_values_sql(sql, values) 404 super 405 406 if values = @opts[:multi_insert_values] 407 expression_list_append(sql, values.map{|r| Array(r)}) 408 end 409 end
Whether the given argument is an array of integers or NULL values, recursively.
# File lib/sequel/extensions/pg_auto_parameterize.rb 412 def _integer_array?(v) 413 Array === v && v.all?{|x| nil == x || Integer === x} 414 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 418 def _integer_array_auto_param(v) 419 buf = String.new 420 buf << '{' 421 comma = false 422 v.each do |x| 423 if comma 424 buf << "," 425 else 426 comma = true 427 end 428 429 buf << (x ? x.to_s : 'NULL') 430 end 431 buf << '}' 432 end
Whether the given query string currently supports automatic parameterization.
# File lib/sequel/extensions/pg_auto_parameterize.rb 390 def auto_param?(sql) 391 sql.is_a?(QueryString) && sql.auto_param? 392 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 Symbol 352 false 353 when String 354 case v 355 when LiteralString 356 false 357 when Sequel::SQL::Blob 358 "::bytea" 359 else 360 "" 361 end 362 when Integer 363 ((v > 2147483647 || v < -2147483648) ? "::int8" : "::int4") 364 when Float 365 # PostgreSQL treats literal floats as numeric, not double precision 366 # But older versions of PostgreSQL don't handle Infinity/NaN in numeric 367 v.finite? ? "::numeric" : "::double precision" 368 when BigDecimal 369 "::numeric" 370 when Sequel::SQLTime 371 "::time" 372 when Time 373 "::#{@db.cast_type_literal(Time)}" 374 when DateTime 375 "::#{@db.cast_type_literal(DateTime)}" 376 when Date 377 "::date" 378 else 379 v.respond_to?(:sequel_auto_param_type) ? v.sequel_auto_param_type(self) : auto_param_type_fallback(v) 380 end 381 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 385 def auto_param_type_fallback(v) 386 super if defined?(super) 387 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 398 def default_import_slice 399 @opts[:no_auto_parameterize] ? super : 40 400 end
A mutable string used as the prefix when explaining a query.
# File lib/sequel/extensions/pg_auto_parameterize.rb 469 def explain_sql_string_origin(opts) 470 @opts[:no_auto_parameterize] ? super : (QueryString.new << super) 471 end
Skip auto parameterization in LIMIT and OFFSET clauses
# File lib/sequel/extensions/pg_auto_parameterize.rb 435 def select_limit_sql(sql) 436 if auto_param?(sql) && (@opts[:limit] || @opts[:offset]) 437 sql.skip_auto_param{super} 438 else 439 super 440 end 441 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 445 def select_order_sql(sql) 446 if auto_param?(sql) && (order = @opts[:order]) && order.any?{|o| Integer === o || (SQL::OrderedExpression === o && Integer === o.expression)} 447 sql.skip_auto_param{super} 448 else 449 super 450 end 451 end
Skip auto parameterization in CTE CYCLE clause
# File lib/sequel/extensions/pg_auto_parameterize.rb 454 def select_with_sql_cte_search_cycle(sql,cte) 455 if auto_param?(sql) && cte[:cycle] 456 sql.skip_auto_param{super} 457 else 458 super 459 end 460 end
Unless auto parameterization is disabled, use a string that can store the parameterized arguments.
# File lib/sequel/extensions/pg_auto_parameterize.rb 464 def sql_string_origin 465 @opts[:no_auto_parameterize] ? super : QueryString.new 466 end
Use auto parameterization for datasets with static SQL using placeholders.
# File lib/sequel/extensions/pg_auto_parameterize.rb 495 def static_sql(sql) 496 if @opts[:append_sql] || @opts[:no_auto_parameterize] || String === sql 497 super 498 else 499 query_string = QueryString.new 500 literal_append(query_string, sql) 501 query_string 502 end 503 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 486 def subselect_sql_append_sql(sql, ds) 487 if args = ds.opts[:subselect_sql_args] 488 ds.send(*args) 489 else 490 super 491 end 492 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 476 def subselect_sql_dataset(sql, ds) 477 if ws_ds = ds.opts[:with_sql_dataset] 478 super(sql, ws_ds).clone(:subselect_sql_args=>ds.opts[:with_sql_args]) 479 else 480 super 481 end 482 end