module Sequel::SQL::DateAdd::DatasetMethods

  1. lib/sequel/extensions/date_arithmetic.rb
Parent: DateAdd

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

Methods

Public Instance

  1. date_add_sql_append

Constants

ACCESS_DURATION_UNITS = DURATION_UNITS.zip(%w'yyyy m d h n s'.map{|s| s.freeze}).freeze  
DB2_DURATION_UNITS = DURATION_UNITS.zip(DURATION_UNITS.map{|s| Sequel.lit(s.to_s).freeze}).freeze  
DEF_DURATION_UNITS = DURATION_UNITS.zip(DURATION_UNITS.map{|s| s.to_s.freeze}).freeze  
DERBY_DURATION_UNITS = DURATION_UNITS.zip(DURATION_UNITS.map{|s| Sequel.lit("SQL_TSI_#{s.to_s.upcase[0...-1]}").freeze}).freeze  
DURATION_UNITS = [:years, :months, :days, :hours, :minutes, :seconds].freeze  
H2_DURATION_UNITS = DURATION_UNITS.zip(DURATION_UNITS.map{|s| s.to_s[0...-1].freeze}).freeze  
MSSQL_DURATION_UNITS = DURATION_UNITS.zip(DURATION_UNITS.map{|s| Sequel.lit(s.to_s[0...-1]).freeze}).freeze  
MYSQL_DURATION_UNITS = DURATION_UNITS.zip(DURATION_UNITS.map{|s| Sequel.lit(s.to_s.upcase[0...-1]).freeze}).freeze  

Public Instance methods

date_add_sql_append (sql, da)

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

[show source] 
# File lib/sequel/extensions/date_arithmetic.rb, line 66
def date_add_sql_append(sql, da)
  h = da.interval
  expr = da.expr
  cast = case db_type = db.database_type
  when :postgres
    interval = ""
    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, :datetime, args)
  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