Class: BarkestCore::MsSqlFunction

Inherits:
Object
  • Object
show all
Includes:
ActiveModel::Model, ActiveModel::Validations, BooleanParser, DateParser, NumberParser
Defined in:
app/models/barkest_core/ms_sql_function.rb

Overview

This class provides a model-like interface to SQL Server User Defined Functions.

It’s understandable that in terms of separation of concerns, logic has no place in the database. In the SQL Server world, UDFs cannot make changes to data, they can only present it. With that in mind, I consider UDFs to be parameterized queries, that are often times orders of magnitude faster than trying to construct a query via ActiveRecord.

Although “models” inheriting from this class are not ActiveRecord models, this class does include ActiveModel::Model and ActiveModel::Validations to allow you to construct your UDF models with similar attributes to ActiveRecord models. For instance, you can ensure that returned values meet certain requirements using the validations. This allows you to further remove logic from the database and still gain the benefit of running a parameterized query.

Constant Summary collapse

InvalidConnection = 
Class.new(StandardError)

Constants included from NumberParser

NumberParser::NUMBER_REGEX

Constants included from DateParser

DateParser::DATE_FORMAT, DateParser::NULLABLE_DATE_FORMAT

Class Method Summary collapse

Methods included from BooleanParser

included, parse_for_boolean_column, parse_for_boolean_filter

Methods included from NumberParser

included, parse_for_float_column, parse_for_float_filter, parse_for_int_column, parse_for_int_filter

Methods included from DateParser

included, parse_for_date_column, parse_for_date_filter, parse_for_time_column, parse_for_time_filter

Class Method Details

.columns ⇒ Object

Gets the column information for the UDF.



111
112
113
 
# File 'app/models/barkest_core/ms_sql_function.rb', line 111

def self.columns
  @column_info
end

.connection ⇒ Object

Gets a connection from the connection handler.

The connection must be to a SQL Server since this class has no idea how to work with UDFs in any other language at this time.

Raises:



53
54
55
56
57
 
# File 'app/models/barkest_core/ms_sql_function.rb', line 53

def self.connection
  conn = connection_handler.connection
  raise InvalidConnection unless conn.is_a?(ActiveRecord::ConnectionAdapters::SQLServerAdapter)
  conn
end

.function_name ⇒ Object

Gets the UDF name for this class.

It is important that you do not set this on the MsSqlFunction class itself.



64
65
66
67
 
# File 'app/models/barkest_core/ms_sql_function.rb', line 64

def self.function_name
  return '(none)' if self == MsSqlFunction
  @udf ||= ''
end

.function_name=(value) ⇒ Object

Sets the UDF name for this class.

It is important that you do not set this on the MsSqlFunction class itself.

Raises:

  • (StandardError) 


74
75
76
77
78
 
# File 'app/models/barkest_core/ms_sql_function.rb', line 74

def self.function_name=(value)
  raise StandardError.new("Function name for #{self} cannot be set.") if self == MsSqlFunction
  raise StandardError.new("Function name for #{self} cannot be set more than once.") unless function_name.blank?
  @udf = process_udf(value)
end

.parameters ⇒ Object

Returns parameter information for the UDF.

The returned hash contains the most important attributes for most applications including :type, :data_type, and :default.



85
86
87
 
# File 'app/models/barkest_core/ms_sql_function.rb', line 85

def self.parameters
  @param_info.inject({}) { |memo,(k,v)| memo[k] = { type: v[:type], data_type: v[:data_type], default: v[:default] }; memo }
end

.parameters=(values) ⇒ Object

Sets the default values for parameters.

The values should be a hash using the parameter name as the key and the default as the value. The easiest way to ensure it works is to set the defaults in the hash returned from #parameters.



95
96
97
98
99
100
101
102
103
104
105
106
107
 
# File 'app/models/barkest_core/ms_sql_function.rb', line 95

def self.parameters=(values)
  if values && values.is_a?(Hash)
    values.each do |k,v|
      if @param_info[k]
        if v.is_a?(Hash)
          @param_info[k][:default] = v[:default]
        else
          @param_info[k][:default] = v
        end
      end
    end
  end
end

.select(params = {}) ⇒ Object

Selects the data from the UDF using the provided parameters.

MyFunction.select(user: 'john', day_of_week: 3)

Returns an array containing the rows returned.



122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
 
# File 'app/models/barkest_core/ms_sql_function.rb', line 122

def self.select(params = {})

  args = []

  params = {} unless params.is_a?(Hash)

  @param_info.each do |k,v|
    args[v[:ordinal]] = [ v[:data_type], self.send(v[:format], params[k] || v[:default]) ]
  end

  where = ''
  idx = args.count
  params.each do |k,v|
    unless @param_info.include? k
      where += ' AND ' unless where.blank?
      where += "([#{k}]"
      if v.is_a? Array
        # IN clause
        where += ' IN (' + v.map{ |value| quote_param(value)[0] }.join(', ') + ')'
      elsif v.is_a? Hash
        if v.include? :between
          v = v[:between]
          raise ArgumentError.new("between clause for #{k} requires an array argument") unless v.is_a? Array
          where += " BETWEEN @#{idx} AND @#{idx + 1}"
          value,type = quote_param(v[0])
          args[idx] = [ type, value ]
          value,type = quote_param(v[1])
          args[idx + 1] = [ type, value ]
          idx += 2
        elsif v.include? :like
          where += " LIKE @#{idx}"
          value,type  = quote_param(v[:like].to_s)
          args[idx] = [ type, value ]
          idx += 1
        else
          operator = nil
          value = nil
          { not: '<>', lt: '<', lte: '<=', gt: '>', gte: '>=', eq: '=' }.each do |key,op|
            if v.include? key
              operator = op
              value = v[key]
              break
            end
          end
          raise ArgumentError.new("unknown clause for #{k}") unless operator
          where += " #{operator} @#{idx}"
          value,type = quote_param(value)
          args[idx] = [ type, value ]
          idx += 1
        end
      else
        where += " = @#{idx}"
        value,type = quote_param(v)
        args[idx] = [ type, value ]
        idx += 1
      end
      where += ')'
    end
  end

  sql = "SELECT * FROM #{@udf}(#{@udf_args})"
  sql += " WHERE #{where}" unless where.blank?

  ret = []

  execute(sql, args) do |row|
    ret << self.new(row)
  end

  ret
end

.use_connection(connected_object) ⇒ Object

Sets the connection handler to use for this function.

The default behavior is to piggyback on the ActiveRecord::Base connections. To override this behavior, provide a class that responds to the :connection method.

class MyFunction < MsSqlFunction
  use_connection SomeMsSqlTable
end

Raises:

  • (ArgumentError) 


37
38
39
40
41
42
43
44
45
46
 
# File 'app/models/barkest_core/ms_sql_function.rb', line 37

def self.use_connection(connected_object)
  @conn_handler =
      if connected_object.is_a?(Class)
        connected_object
      else
        const_get(connected_object || 'ActiveRecord::Base')
      end
  raise ArgumentError.new('Connected object must be a class or class name.') unless @conn_handler
  raise ArgumentError.new('Connected object must respond to :connection') unless @conn_handler.respond_to?(:connection)
end