Module: ArJdbc::MSSQL::LimitHelpers::SqlServer2000ReplaceLimitOffset
- Included in:
- Arel::Visitors::SQLServer2000
- Defined in:
- lib/arjdbc/mssql/limit_helpers.rb
Class Method Summary collapse
-
.get_primary_key(order, table_name) ⇒ Object
table_name might be quoted.
- .replace_limit_offset!(sql, limit, offset, order) ⇒ Object
-
.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).
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 |