Class/Module Index [+]

Quicksearch

TSqlMethods

Common methods for handling TSQL databases.

Public Instance Methods

add_limit_offset!(sql, options) click to toggle source
# File lib/jdbc_adapter/tsql_helper.rb, line 27
def add_limit_offset!(sql, options)
  if options[:limit] and options[:offset]
    total_rows = select_all("SELECT count(*) as TotalRows from (#{sql.gsub(/\bSELECT(\s+DISTINCT)?\b/i, "SELECT\\1 TOP 1000000000")}) tally")[0]["TotalRows"].to_i
    if (options[:limit] + options[:offset]) >= total_rows
      options[:limit] = (total_rows - options[:offset] >= 0) ? (total_rows - options[:offset]) : 0
    end
    sql.sub!(/^\s*SELECT(\s+DISTINCT)?/, "SELECT * FROM (SELECT TOP #{options[:limit]} * FROM (SELECT\\1 TOP #{options[:limit] + options[:offset]} ")
    sql << ") AS tmp1"
    if options[:order]
      options[:order] = options[:order].split(',').map do |field|
        parts = field.split(" ")
        tc = parts[0]
        if sql =~ /\.\[/ and tc =~ /\./ # if column quoting used in query
          tc.gsub!(/\./, '\.\[')
          tc << '\]'
        end
        if sql =~ /#{tc} AS (t\d_r\d\d?)/
            parts[0] = $1
        elsif parts[0] =~ /\w+\.(\w+)/
          parts[0] = $1
        end
        parts.join(' ')
      end.join(', ')
      sql << " ORDER BY #{change_order_direction(options[:order])}) AS tmp2 ORDER BY #{options[:order]}"
    else
      sql << " ) AS tmp2"
    end
  elsif sql !~ /^\s*SELECT (@@|COUNT\()/
    sql.sub!(/^\s*SELECT(\s+DISTINCT)?/) do
      "SELECT#{$1} TOP #{options[:limit]}"
    end unless options[:limit].nil?
  end
end

[Validate]

Generated with the Darkfish Rdoc Generator 2.