Module: Sequel::SQL::DateAdd::DatasetMethods

Defined in:
lib/sequel/extensions/date_arithmetic.rb

Overview

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

Constant Summary collapse

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

Instance Method Summary collapse

Instance Method Details

#date_add_sql_append(sql, da) ⇒ Object

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



72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
 
# 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))
  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