module Sequel::SQL::DateAdd::DatasetMethods

These methods are added to datasets using the date_arithmetic extension, for the purposes of correctly literalizing DateAdd expressions for the appropriate database type.

Constants

ACCESS_DURATION_UNITS
DB2_DURATION_UNITS
DEF_DURATION_UNITS
DERBY_DURATION_UNITS
DURATION_UNITS
FDBSQL_DURATION_UNITS
H2_DURATION_UNITS
MSSQL_DURATION_UNITS
MYSQL_DURATION_UNITS

Public Instance Methods

date_add_sql_append(sql, da) click to toggle source

Append the SQL fragment for the DateAdd expression to the SQL query.

Calls superclass method
# File lib/sequel/extensions/date_arithmetic.rb, line 72
def date_add_sql_append(sql, da)
  if defined?(super)
    return super
  end
  h = da.interval
  expr = da.expr
  cast = case db_type = db.database_type
  when :postgres
    interval = String.new
    each_valid_interval_unit(h, DEF_DURATION_UNITS) do |value, sql_unit|
      interval << "#{value} #{sql_unit} "
    end
    if interval.empty?
      return literal_append(sql, Sequel.cast(expr, Time))
    else
      return complex_expression_sql_append(sql, :+, [Sequel.cast(expr, Time), Sequel.cast(interval, :interval)])
    end
  when :sqlite
    args = [expr]
    each_valid_interval_unit(h, DEF_DURATION_UNITS) do |value, sql_unit|
      args << "#{value} #{sql_unit}"
    end
    return function_sql_append(sql, Sequel.function(:datetime, *args))
  # SEQUEL5: Remove cubrid
  when :mysql, :hsqldb, :cubrid
    if db_type == :hsqldb
      # HSQLDB requires 2.2.9+ for the DATE_ADD function
      expr = Sequel.cast(expr, Time)
    end
    each_valid_interval_unit(h, MYSQL_DURATION_UNITS) do |value, sql_unit|
      expr = Sequel.function(:DATE_ADD, expr, Sequel.lit(["INTERVAL ", " "], value, sql_unit))
    end
  when :mssql, :h2, :access, :sqlanywhere
    units = case db_type
    when :mssql, :sqlanywhere
      MSSQL_DURATION_UNITS
    when :h2
      H2_DURATION_UNITS
    when :access
      ACCESS_DURATION_UNITS
    end
    each_valid_interval_unit(h, units) do |value, sql_unit|
      expr = Sequel.function(:DATEADD, sql_unit, value, expr)
    end
  when :derby
    if expr.is_a?(Date) && !expr.is_a?(DateTime)
      # Work around for https://issues.apache.org/jira/browse/DERBY-896
      expr = Sequel.cast_string(expr) + ' 00:00:00'
    end
    each_valid_interval_unit(h, DERBY_DURATION_UNITS) do |value, sql_unit|
      expr = Sequel.lit(["{fn timestampadd(#{sql_unit}, ", ", timestamp(", "))}"], value, expr)
    end
  when :oracle
    each_valid_interval_unit(h, MYSQL_DURATION_UNITS) do |value, sql_unit|
      expr = Sequel.+(expr, Sequel.lit(["INTERVAL ", " "], value.to_s, sql_unit))
    end
  when :db2
    expr = Sequel.cast(expr, Time)
    each_valid_interval_unit(h, DB2_DURATION_UNITS) do |value, sql_unit|
      expr = Sequel.+(expr, Sequel.lit(["", " "], value, sql_unit))
    end
    false
  else
    raise Error, "date arithmetic is not implemented on #{db.database_type}"
  end

  if cast
    expr = Sequel.cast(expr, Time)
  end

  literal_append(sql, expr)
end

Private Instance Methods

each_valid_interval_unit(interval, units) { |value, sql_unit| ... } click to toggle source

Yield the value in the interval for each of the units present in the interval, along with the SQL fragment representing the unit name. Returns false if any values were yielded, true otherwise

# File lib/sequel/extensions/date_arithmetic.rb, line 151
def each_valid_interval_unit(interval, units)
  cast = true
  units.each do |unit, sql_unit|
    if (value = interval[unit]) && value != 0
      cast = false
      yield value, sql_unit
    end
  end
  cast
end