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

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