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