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
H2_DURATION_UNITS
MSSQL_DURATION_UNITS
MYSQL_DURATION_UNITS
POSTGRES_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
 93 def date_add_sql_append(sql, da)
 94   if defined?(super)
 95     return super
 96   end
 97 
 98   h = da.interval
 99   expr = da.expr
100   cast_type = da.cast_type || Time
101 
102   cast = case db_type = db.database_type
103   when :postgres
104     casted = Sequel.cast(expr, cast_type)
105 
106     if db.server_version >= 90400
107       placeholder = []
108       vals = []
109       each_valid_interval_unit(h, POSTGRES_DURATION_UNITS) do |value, sql_unit|
110         placeholder << "#{', ' unless placeholder.empty?}#{sql_unit} := "
111         vals << value
112       end
113       interval = Sequel.function(:make_interval, Sequel.lit(placeholder, *vals)) unless vals.empty?
114     else
115       parts = String.new
116       each_valid_interval_unit(h, DEF_DURATION_UNITS) do |value, sql_unit|
117         parts << "#{value} #{sql_unit} "
118       end
119       interval = Sequel.cast(parts, :interval) unless parts.empty?
120     end
121 
122     if interval
123       return complex_expression_sql_append(sql, :+, [casted, interval])
124     else
125       return literal_append(sql, casted)
126     end
127   when :sqlite
128     args = [expr]
129     each_valid_interval_unit(h, DEF_DURATION_UNITS) do |value, sql_unit|
130       args << "#{value} #{sql_unit}"
131     end
132     return function_sql_append(sql, Sequel.function(:datetime, *args))
133   when :mysql, :hsqldb
134     if db_type == :hsqldb
135       # HSQLDB requires 2.2.9+ for the DATE_ADD function
136       expr = Sequel.cast(expr, cast_type)
137     end
138     each_valid_interval_unit(h, MYSQL_DURATION_UNITS) do |value, sql_unit|
139       expr = Sequel.function(:DATE_ADD, expr, Sequel.lit(["INTERVAL ", " "], value, sql_unit))
140     end
141   when :mssql, :h2, :access, :sqlanywhere
142     units = case db_type
143     when :h2
144       H2_DURATION_UNITS
145     when :access
146       ACCESS_DURATION_UNITS
147     else
148       MSSQL_DURATION_UNITS
149     end
150     each_valid_interval_unit(h, units) do |value, sql_unit|
151       expr = Sequel.function(:DATEADD, sql_unit, value, expr)
152     end
153   when :derby
154     if expr.is_a?(Date) && !expr.is_a?(DateTime)
155       # Work around for https://issues.apache.org/jira/browse/DERBY-896
156       expr = Sequel.cast_string(expr) + ' 00:00:00'
157     end
158     each_valid_interval_unit(h, DERBY_DURATION_UNITS) do |value, sql_unit|
159       expr = Sequel.lit(["{fn timestampadd(#{sql_unit}, ", ", timestamp(", "))}"], value, expr)
160     end
161   when :oracle
162     each_valid_interval_unit(h, MYSQL_DURATION_UNITS) do |value, sql_unit|
163       expr = Sequel.+(expr, Sequel.lit(["INTERVAL ", " "], value.to_s, sql_unit))
164     end
165   when :db2
166     expr = Sequel.cast(expr, cast_type)
167     each_valid_interval_unit(h, DB2_DURATION_UNITS) do |value, sql_unit|
168       expr = Sequel.+(expr, Sequel.lit(["", " "], value, sql_unit))
169     end
170     false
171   else
172     raise Error, "date arithmetic is not implemented on #{db.database_type}"
173   end
174 
175   if cast
176     expr = Sequel.cast(expr, cast_type)
177   end
178 
179   literal_append(sql, expr)
180 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
188 def each_valid_interval_unit(interval, units)
189   cast = true
190   units.each do |unit, sql_unit|
191     if (value = interval[unit]) && value != 0
192       cast = false
193       yield value, sql_unit
194     end
195   end
196   cast
197 end