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, line 257 def cast_sql_append(sql, expr, type) if auto_param?(sql) && auto_param_type(expr) sql << 'CAST(' sql.add_arg(expr) sql << ' AS ' << db.cast_type_literal(type).to_s << ')' else super end 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, line 272 def complex_expression_sql_append(sql, op, args) case op when :IN, :"NOT IN" l, r = args if auto_param?(sql) && !l.is_a?(Array) && _integer_array?(r) && r.size > 1 if op == :IN op = :"=" func = :ANY else op = :!= func = :ALL end args = [l, Sequel.function(func, Sequel.cast(_integer_array_auto_param(r), 'int8[]'))] end end super 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, line 303 def literal_append(sql, v) if auto_param?(sql) && (type = auto_param_type(v)) sql.add_arg(v) << type else super end end
Parameterize insertion of multiple values
# File lib/sequel/extensions/pg_auto_parameterize.rb, line 292 def multi_insert_sql(columns, values) if @opts[:no_auto_parameterize] super else [clone(:multi_insert_values=>values.map{|r| Array(r)}).insert_sql(columns, LiteralString.new('VALUES '))] end end
Return a clone of the dataset that will not do automatic parameterization.
# File lib/sequel/extensions/pg_auto_parameterize.rb, line 248 def no_auto_parameterize cached_dataset(:_no_auto_parameterize_ds) do @opts[:no_auto_parameterize] ? self : clone(:no_auto_parameterize=>true) end end
The class to use for placeholder literalizers.
# File lib/sequel/extensions/pg_auto_parameterize.rb, line 312 def placeholder_literalizer_class if @opts[:no_auto_parameterize] super else PlaceholderLiteralizer end end
Disable automatic parameterization when using a cursor.
# File lib/sequel/extensions/pg_auto_parameterize.rb, line 321 def use_cursor(*) super.no_auto_parameterize 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, line 327 def with_sql(*a) ds = super if Symbol === a[0] ds = ds.clone(:with_sql_dataset=>self, :with_sql_args=>a.freeze) end ds end
Protected Instance Methods
Disable automatic parameterization for prepared statements, since they will use manual parameterization.
# File lib/sequel/extensions/pg_auto_parameterize.rb, line 339 def to_prepared_statement(*a) @opts[:no_auto_parameterize] ? super : no_auto_parameterize.to_prepared_statement(*a) end
Private Instance Methods
Handle parameterization of #multi_insert_sql
# File lib/sequel/extensions/pg_auto_parameterize.rb, line 400 def _insert_values_sql(sql, values) super if values = @opts[:multi_insert_values] expression_list_append(sql, values.map{|r| Array(r)}) end end
Whether the given argument is an array of integers or NULL values, recursively.
# File lib/sequel/extensions/pg_auto_parameterize.rb, line 409 def _integer_array?(v) Array === v && v.all?{|x| nil == x || Integer === x} 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, line 415 def _integer_array_auto_param(v) buf = String.new buf << '{' comma = false v.each do |x| if comma buf << "," else comma = true end buf << (x ? x.to_s : 'NULL') end buf << '}' end
Whether the given query string currently supports automatic parameterization.
# File lib/sequel/extensions/pg_auto_parameterize.rb, line 387 def auto_param?(sql) sql.is_a?(QueryString) && sql.auto_param? 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, line 348 def auto_param_type(v) case v when String case v when LiteralString false when Sequel::SQL::Blob "::bytea" else "" end when Integer ((v > 2147483647 || v < -2147483648) ? "::int8" : "::int4") when Float # PostgreSQL treats literal floats as numeric, not double precision # But older versions of PostgreSQL don't handle Infinity/NaN in numeric v.finite? ? "::numeric" : "::double precision" when BigDecimal "::numeric" when Sequel::SQLTime "::time" when Time "::#{@db.cast_type_literal(Time)}" when DateTime "::#{@db.cast_type_literal(DateTime)}" when Date "::date" else v.respond_to?(:sequel_auto_param_type) ? v.sequel_auto_param_type(self) : auto_param_type_fallback(v) end 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, line 382 def auto_param_type_fallback(v) super if defined?(super) 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, line 395 def default_import_slice 40 end
Skip auto parameterization in LIMIT and OFFSET clauses
# File lib/sequel/extensions/pg_auto_parameterize.rb, line 432 def select_limit_sql(sql) if auto_param?(sql) && (@opts[:limit] || @opts[:offset]) sql.skip_auto_param{super} else super end 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, line 442 def select_order_sql(sql) if auto_param?(sql) && (order = @opts[:order]) && order.any?{|o| Integer === o || (SQL::OrderedExpression === o && Integer === o.expression)} sql.skip_auto_param{super} else super end end
Skip auto parameterization in CTE CYCLE clause
# File lib/sequel/extensions/pg_auto_parameterize.rb, line 451 def select_with_sql_cte_search_cycle(sql,cte) if auto_param?(sql) && cte[:cycle] sql.skip_auto_param{super} else super end end
Unless auto parameterization is disabled, use a string that can store the parameterized arguments.
# File lib/sequel/extensions/pg_auto_parameterize.rb, line 461 def sql_string_origin @opts[:no_auto_parameterize] ? super : QueryString.new end
Use auto parameterization for datasets with static SQL using placeholders.
# File lib/sequel/extensions/pg_auto_parameterize.rb, line 487 def static_sql(sql) if @opts[:append_sql] || @opts[:no_auto_parameterize] || String === sql super else query_string = QueryString.new literal_append(query_string, sql) query_string end 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, line 478 def subselect_sql_append_sql(sql, ds) if args = ds.opts[:subselect_sql_args] ds.send(*args) else super end 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, line 468 def subselect_sql_dataset(sql, ds) if ws_ds = ds.opts[:with_sql_dataset] super(sql, ws_ds).clone(:subselect_sql_args=>ds.opts[:with_sql_args]) else super end end