Module | Sequel::MSSQL::DatasetMethods |
In: |
lib/sequel/adapters/shared/mssql.rb
|
BOOL_TRUE | = | '1'.freeze |
BOOL_FALSE | = | '0'.freeze |
SELECT_CLAUSE_ORDER | = | %w'with limit distinct columns from table_options join where group order having compounds'.freeze |
TIMESTAMP_FORMAT | = | "'%Y-%m-%d %H:%M:%S'".freeze |
WILDCARD | = | LiteralString.new('*').freeze |
CONSTANT_MAP | = | {:CURRENT_DATE=>'CAST(CURRENT_TIMESTAMP AS DATE)'.freeze, :CURRENT_TIME=>'CAST(CURRENT_TIMESTAMP AS TIME)'.freeze} |
When returning all rows, if an offset is used, delete the row_number column before yielding the row.
# File lib/sequel/adapters/shared/mssql.rb, line 170 170: def each(&block) 171: @opts[:offset] ? super{|r| r.delete(row_number_column); yield r} : super(&block) 172: end
MSSQL uses a UNION ALL statement to insert multiple values at once.
# File lib/sequel/adapters/shared/mssql.rb, line 180 180: def multi_insert_sql(columns, values) 181: values = values.map {|r| "SELECT #{expression_list(r)}" }.join(" UNION ALL ") 182: ["#{insert_sql_base}#{source_list(@opts[:from])} (#{identifier_list(columns)}) #{values}"] 183: end
MSSQL Requires the use of the ROW_NUMBER window function to emulate an offset. This implementation requires MSSQL 2005 or greater (offset can‘t be emulated well in MSSQL 2000).
The implementation is ugly, cloning the current dataset and modifying the clone to add a ROW_NUMBER window function (and some other things), then using the modified clone in a CTE which is selected from.
If offset is used, an order must be provided, because the use of ROW_NUMBER requires an order.
# File lib/sequel/adapters/shared/mssql.rb, line 205 205: def select_sql 206: return super unless o = @opts[:offset] 207: raise(Error, 'MSSQL requires an order be provided if using an offset') unless order = @opts[:order] 208: dsa1 = dataset_alias(1) 209: dsa2 = dataset_alias(2) 210: rn = row_number_column 211: unlimited. 212: unordered. 213: from_self(:alias=>dsa2). 214: select{[WILDCARD, ROW_NUMBER(:over, :order=>order){}.as(rn)]}. 215: from_self(:alias=>dsa1). 216: limit(@opts[:limit]). 217: where(rn > o). 218: select_sql 219: end