module Sequel::SQL::DateAdd::DatasetMethods

  1. lib/sequel/extensions/date_arithmetic.rb

These methods are added to datasets using the date_arithmetic extension, for the purposes of correctly literalizing DateAdd expressions for the appropriate database type.

Methods

Public Instance

  1. date_add_sql_append

Constants

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  
DEF_DURATION_UNITS = DURATION_UNITS.zip(DURATION_UNITS.map{|s| s.to_s.freeze}).freeze  
DERBY_DURATION_UNITS = DURATION_UNITS.zip(DURATION_UNITS.map{|s| Sequel.lit("SQL_TSI_#{s.to_s.upcase[0...-1]}").freeze}).freeze  
DURATION_UNITS = [:years, :months, :days, :hours, :minutes, :seconds].freeze  
H2_DURATION_UNITS = DURATION_UNITS.zip(DURATION_UNITS.map{|s| s.to_s[0...-1].freeze}).freeze  
MSSQL_DURATION_UNITS = DURATION_UNITS.zip(DURATION_UNITS.map{|s| Sequel.lit(s.to_s[0...-1]).freeze}).freeze  
MYSQL_DURATION_UNITS = DURATION_UNITS.zip(DURATION_UNITS.map{|s| Sequel.lit(s.to_s.upcase[0...-1]).freeze}).freeze  
POSTGRES_DURATION_UNITS = DURATION_UNITS.zip([:years, :months, :days, :hours, :mins, :secs].map{|s| s.to_s.freeze}).freeze  

Public Instance methods

date_add_sql_append(sql, da)

Append the SQL fragment for the DateAdd expression to the SQL query.

[show source]
    # 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