source.dussan.org Git - sonarqube.git/blob

   1 module ::ArJdbc
   2   module MsSQL
   3     module LimitHelpers
   4       module_function
   5       def get_table_name(sql)
   6         if sql =~ /^\s*insert\s+into\s+([^\(\s,]+)\s*|^\s*update\s+([^\(\s,]+)\s*/i
   7           $1
   8         elsif sql =~ /\bfrom\s+([^\(\s,]+)\s*/i
   9           $1
  10         else
  11           nil
  12         end
  13       end
  14
  15       module SqlServer2000ReplaceLimitOffset
  16         module_function
  17         def replace_limit_offset!(sql, limit, offset, order)
  18           if limit
  19             offset ||= 0
  20             start_row = offset + 1
  21             end_row = offset + limit.to_i
  22             find_select = /\b(SELECT(?:\s+DISTINCT)?)\b(.*)/im
  23             whole, select, rest_of_query = find_select.match(sql).to_a
  24             if (start_row == 1) && (end_row ==1)
  25               new_sql = "#{select} TOP 1 #{rest_of_query}"
  26               sql.replace(new_sql)
  27             else
  28               #UGLY
  29               #KLUDGY?
  30               #removing out stuff before the FROM...
  31               rest = rest_of_query[/FROM/i=~ rest_of_query.. -1]
  32               #need the table name for avoiding amiguity
  33               table_name = LimitHelpers.get_table_name(sql)
  34               primary_key = order[/(\w*id\w*)/i]
  35               #I am not sure this will cover all bases.  but all the tests pass
  36               new_order = "ORDER BY #{order}, #{table_name}.#{primary_key}" if order.index("#{table_name}.#{primary_key}").nil?
  37               new_order ||= order
  38
  39               if (rest_of_query.match(/WHERE/).nil?)
  40                 new_sql = "#{select} TOP #{limit} #{rest_of_query} WHERE #{table_name}.#{primary_key} NOT IN (#{select} TOP #{offset} #{table_name}.#{primary_key} #{rest} #{new_order}) #{order} "
  41               else
  42                 new_sql = "#{select} TOP #{limit} #{rest_of_query} AND #{table_name}.#{primary_key} NOT IN (#{select} TOP #{offset} #{table_name}.#{primary_key} #{rest} #{new_order}) #{order} "
  43               end
  44
  45               sql.replace(new_sql)
  46             end
  47           end
  48           sql
  49         end
  50       end
  51
  52       module SqlServer2000AddLimitOffset
  53         def add_limit_offset!(sql, options)
  54           if options[:limit]
  55             order = "ORDER BY #{options[:order] || determine_order_clause(sql)}"
  56             sql.sub!(/ ORDER BY.*$/i, '')
  57             SqlServerReplaceLimitOffset.replace_limit_offset!(sql, options[:limit], options[:offset], order)
  58           end
  59         end
  60       end
  61
  62       module SqlServerReplaceLimitOffset
  63         module_function
  64         def replace_limit_offset!(sql, limit, offset, order)
  65           if limit
  66             offset ||= 0
  67             start_row = offset + 1
  68             end_row = offset + limit.to_i
  69             find_select = /\b(SELECT(?:\s+DISTINCT)?)\b(.*)/im
  70             whole, select, rest_of_query = find_select.match(sql).to_a
  71             rest_of_query.strip!
  72             if rest_of_query[0] == "1"
  73               rest_of_query[0] = "*"
  74             end
  75             if rest_of_query[0] == "*"
  76               from_table = LimitHelpers.get_table_name(rest_of_query)
  77               rest_of_query = from_table + '.' + rest_of_query
  78             end
  79             new_sql = "#{select} t.* FROM (SELECT ROW_NUMBER() OVER(#{order}) AS _row_num, #{rest_of_query}"
  80             new_sql << ") AS t WHERE t._row_num BETWEEN #{start_row.to_s} AND #{end_row.to_s}"
  81             sql.replace(new_sql)
  82           end
  83           sql
  84         end
  85       end
  86
  87       module SqlServerAddLimitOffset
  88         def add_limit_offset!(sql, options)
  89           if options[:limit]
  90             order = "ORDER BY #{options[:order] || determine_order_clause(sql)}"
  91             sql.sub!(/ ORDER BY.*$/i, '')
  92             SqlServerReplaceLimitOffset.replace_limit_offset!(sql, options[:limit], options[:offset], order)
  93           end
  94         end
  95       end
  96     end
  97   end
  98 end