Module: ArJdbc::MSSQL::LimitHelpers::SqlServer2000ReplaceLimitOffset

Included in:
Arel::Visitors::SQLServer2000
Defined in:
lib/arjdbc/mssql/limit_helpers.rb

Class Method Summary collapse

Class Method Details

.get_primary_key(order, table_name) ⇒ Object

table_name might be quoted



148
149
150
151
152
153
154
155
156
 
# File 'lib/arjdbc/mssql/limit_helpers.rb', line 148

def get_primary_key(order, table_name) # table_name might be quoted
  if order =~ /(\w*id\w*)/i
    $1
  else
    unquoted_name = Utils.unquote_table_name(table_name)
    model = descendants.find { |m| m.table_name == table_name || m.table_name == unquoted_name }
    model ? model.primary_key : 'id'
  end
end

.replace_limit_offset!(sql, limit, offset, order) ⇒ Object 



80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
# File 'lib/arjdbc/mssql/limit_helpers.rb', line 80

def replace_limit_offset!(sql, limit, offset, order)
  if limit
    offset ||= 0
    start_row = offset + 1
    end_row = offset + limit.to_i

    if match = FIND_SELECT.match(sql)
      select, distinct, rest_of_query = match[1], match[2], match[3]
    end
    #need the table name for avoiding amiguity
    table_name  = Utils.get_table_name(sql, true)
    primary_key = get_primary_key(order, table_name)

    #I am not sure this will cover all bases.  but all the tests pass
    if order[/ORDER/].nil?
      new_order = "ORDER BY #{order}, [#{table_name}].[#{primary_key}]" if order.index("#{table_name}.#{primary_key}").nil?
    else
      new_order ||= order
    end

    if (start_row == 1) && (end_row ==1)
      new_sql = "#{select} TOP 1 #{rest_of_query} #{new_order}"
      sql.replace(new_sql)
    else
      # We are in deep trouble here. SQL Server does not have any kind of OFFSET build in.
      # Only remaining solution is adding a where condition to be sure that the ID is not in SELECT TOP OFFSET FROM SAME_QUERY.
      # To do so we need to extract each part of the query to insert our additional condition in the right place.
      query_without_select = rest_of_query[/FROM/i=~ rest_of_query.. -1]
      additional_condition = "#{table_name}.#{primary_key} NOT IN (#{select} TOP #{offset} #{table_name}.#{primary_key} #{query_without_select} #{new_order})"

      # Extract the different parts of the query
      having, group_by, where, from, selection = split_sql(rest_of_query, /having/i, /group by/i, /where/i, /from/i)

      # Update the where part to add our additional condition
      if where.blank?
        where = "WHERE #{additional_condition}"
      else
        where = "#{where} AND #{additional_condition}"
      end

      # Replace the query to be our new customized query
      sql.replace("#{select} TOP #{limit} #{selection} #{from} #{where} #{group_by} #{having} #{new_order}")
    end
  end
  sql
end

.split_sql(rest_of_query, *regexs) ⇒ Object

Split the rest_of_query into chunks based on regexs (applied from end of string to the beginning) The result is an array of regexs.size+1 elements (the last one being the remaining once everything was chopped away)



129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
 
# File 'lib/arjdbc/mssql/limit_helpers.rb', line 129

def split_sql(rest_of_query, *regexs)
  results = Array.new

  regexs.each do |regex|
    if position = (regex =~ rest_of_query)
      # Extract the matched string and chop the rest_of_query
      matched       = rest_of_query[position..-1]
      rest_of_query = rest_of_query[0...position]
    else
      matched = nil
    end

    results << matched
  end
  results << rest_of_query

  results
end