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