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
POSTGRES_DURATION_UNITS = 
DURATION_UNITS.zip([:years, :months, :days, :hours, :mins, :secs].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

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.



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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
 
# File 'lib/sequel/extensions/date_arithmetic.rb', line 93

def date_add_sql_append(sql, da)
  if defined?(super)
    return super
  end

  h = da.interval
  expr = da.expr
  cast_type = da.cast_type || Time

  cast = case db_type = db.database_type
  when :postgres
    casted = Sequel.cast(expr, cast_type)

    if db.server_version >= 90400
      placeholder = []
      vals = []
      each_valid_interval_unit(h, POSTGRES_DURATION_UNITS) do |value, sql_unit|
        placeholder << "#{', ' unless placeholder.empty?}#{sql_unit} := "
        vals << value
      end
      interval = Sequel.function(:make_interval, Sequel.lit(placeholder, *vals)) unless vals.empty?
    else
      parts = String.new
      each_valid_interval_unit(h, DEF_DURATION_UNITS) do |value, sql_unit|
        parts << "#{value} #{sql_unit} "
      end
      interval = Sequel.cast(parts, :interval) unless parts.empty?
    end

    if interval
      return complex_expression_sql_append(sql, :+, [casted, interval])
    else
      return literal_append(sql, casted)
    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
    if db_type == :hsqldb
      # HSQLDB requires 2.2.9+ for the DATE_ADD function
      expr = Sequel.cast(expr, cast_type)
    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 :h2
      H2_DURATION_UNITS
    when :access
      ACCESS_DURATION_UNITS
    else
      MSSQL_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, cast_type)
    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, cast_type)
  end

  literal_append(sql, expr)
end