module Sequel::MSSQL::DatabaseMethods

Constants

DATABASE_ERROR_REGEXPS
FOREIGN_KEY_ACTION_MAP

Attributes

mssql_unicode_strings[RW]

Whether to use N'' to quote strings, which allows unicode characters inside the strings. True by default for compatibility, can be set to false for a possible performance increase. This sets the default for all datasets created from this Database object.

Public Instance Methods

call_mssql_sproc(name, opts=OPTS) click to toggle source

Execute the given stored procedure with the given name.

Options:

:args

Arguments to stored procedure. For named arguments, this should be a hash keyed by argument named. For unnamed arguments, this should be an array. Output parameters to the function are specified using :output. You can also name output parameters and provide a type by using an array containing :output, the type name, and the parameter name.

:server

The server/shard on which to execute the procedure.

This method returns a single hash with the following keys:

:result

The result code of the stored procedure

:numrows

The number of rows affected by the stored procedure

output params

Values for any output paramters, using the name given for the output parameter

Examples:

DB.call_mssql_sproc(:SequelTest, {args: ['input arg', :output]})
DB.call_mssql_sproc(:SequelTest, {args: ['input arg', [:output, 'int', 'varname']]})

named params:
DB.call_mssql_sproc(:SequelTest, args: {
  'input_arg1_name' => 'input arg1 value',
  'input_arg2_name' => 'input arg2 value',
  'output_arg_name' => [:output, 'int', 'varname']
})
    # File lib/sequel/adapters/shared/mssql.rb
 54 def call_mssql_sproc(name, opts=OPTS)
 55   args = opts[:args] || []
 56   names = ['@RC AS RESULT', '@@ROWCOUNT AS NUMROWS']
 57   declarations = ['@RC int']
 58   values = []
 59 
 60   if args.is_a?(Hash)
 61     named_args = true
 62     args = args.to_a
 63     method = :each
 64   else
 65     method = :each_with_index
 66   end
 67 
 68   args.public_send(method) do |v, i|
 69     if named_args
 70       k = v
 71       v, type, select = i
 72       raise Error, "must provide output parameter name when using output parameters with named arguments" if v == :output && !select
 73     else
 74       v, type, select = v
 75     end
 76 
 77     if v == :output
 78       type ||= "nvarchar(max)"
 79       if named_args
 80         varname = select
 81       else
 82         varname = "var#{i}"
 83         select ||= varname
 84       end
 85       names << "@#{varname} AS #{quote_identifier(select)}"
 86       declarations << "@#{varname} #{type}"
 87       value = "@#{varname} OUTPUT"
 88     else
 89       value = literal(v)
 90     end
 91 
 92     if named_args
 93       value = "@#{k}=#{value}"
 94     end
 95 
 96     values << value
 97   end
 98 
 99   sql = "DECLARE #{declarations.join(', ')}; EXECUTE @RC = #{name} #{values.join(', ')}; SELECT #{names.join(', ')}"
100 
101   ds = dataset.with_sql(sql)
102   ds = ds.server(opts[:server]) if opts[:server]
103   ds.first
104 end
database_type() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
106 def database_type
107   :mssql
108 end
foreign_key_list(table, opts=OPTS) click to toggle source

Return foreign key information using the system views, including :name, :on_delete, and :on_update entries in the hashes.

    # File lib/sequel/adapters/shared/mssql.rb
117 def foreign_key_list(table, opts=OPTS)
118   m = output_identifier_meth
119   im = input_identifier_meth
120   schema, table = schema_and_table(table)
121   current_schema = m.call(get(Sequel.function('schema_name')))
122   fk_action_map = FOREIGN_KEY_ACTION_MAP
123   fk = Sequel[:fk]
124   fkc = Sequel[:fkc]
125   ds = metadata_dataset.from(Sequel.lit('[sys].[foreign_keys]').as(:fk)).
126     join(Sequel.lit('[sys].[foreign_key_columns]').as(:fkc), :constraint_object_id => :object_id).
127     join(Sequel.lit('[sys].[all_columns]').as(:pc), :object_id => fkc[:parent_object_id],     :column_id => fkc[:parent_column_id]).
128     join(Sequel.lit('[sys].[all_columns]').as(:rc), :object_id => fkc[:referenced_object_id], :column_id => fkc[:referenced_column_id]).
129     where{{object_schema_name(fk[:parent_object_id]) => im.call(schema || current_schema)}}.
130     where{{object_name(fk[:parent_object_id]) => im.call(table)}}.
131     select{[fk[:name], 
132             fk[:delete_referential_action], 
133             fk[:update_referential_action], 
134             pc[:name].as(:column), 
135             rc[:name].as(:referenced_column), 
136             object_schema_name(fk[:referenced_object_id]).as(:schema), 
137             object_name(fk[:referenced_object_id]).as(:table)]}.
138     order(fk[:name], fkc[:constraint_column_id])
139   h = {}
140   ds.each do |row|
141     if r = h[row[:name]]
142       r[:columns] << m.call(row[:column])
143       r[:key] << m.call(row[:referenced_column])
144     else
145       referenced_schema = m.call(row[:schema])
146       referenced_table = m.call(row[:table])
147       h[row[:name]] = { :name      => m.call(row[:name]), 
148                         :table     => (referenced_schema == current_schema) ? referenced_table : Sequel.qualify(referenced_schema, referenced_table),
149                         :columns   => [m.call(row[:column])], 
150                         :key       => [m.call(row[:referenced_column])], 
151                         :on_update => fk_action_map[row[:update_referential_action]], 
152                         :on_delete => fk_action_map[row[:delete_referential_action]] }
153     end
154   end
155   h.values
156 end
freeze() click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mssql.rb
158 def freeze
159   server_version
160   super
161 end
global_index_namespace?() click to toggle source

Microsoft SQL Server namespaces indexes per table.

    # File lib/sequel/adapters/shared/mssql.rb
111 def global_index_namespace?
112   false
113 end
indexes(table, opts=OPTS) click to toggle source

Use the system tables to get index information

    # File lib/sequel/adapters/shared/mssql.rb
164 def indexes(table, opts=OPTS)
165   m = output_identifier_meth
166   im = input_identifier_meth
167   indexes = {}
168   table = table.value if table.is_a?(Sequel::SQL::Identifier)
169   i = Sequel[:i]
170   ds = metadata_dataset.from(Sequel.lit('[sys].[tables]').as(:t)).
171    join(Sequel.lit('[sys].[indexes]').as(:i), :object_id=>:object_id).
172    join(Sequel.lit('[sys].[index_columns]').as(:ic), :object_id=>:object_id, :index_id=>:index_id).
173    join(Sequel.lit('[sys].[columns]').as(:c), :object_id=>:object_id, :column_id=>:column_id).
174    select(i[:name], i[:is_unique], Sequel[:c][:name].as(:column)).
175    where{{t[:name]=>im.call(table)}}.
176    where(i[:is_primary_key]=>0, i[:is_disabled]=>0).
177    order(i[:name], Sequel[:ic][:index_column_id])
178 
179   if supports_partial_indexes?
180     ds = ds.where(i[:has_filter]=>0)
181   end
182 
183   ds.each do |r|
184     index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)}
185     index[:columns] << m.call(r[:column])
186   end
187   indexes
188 end
server_version(server=nil) click to toggle source

The version of the MSSQL server, as an integer (e.g. 10001600 for SQL Server 2008 Express).

    # File lib/sequel/adapters/shared/mssql.rb
192 def server_version(server=nil)
193   return @server_version if @server_version
194   if @opts[:server_version]
195     return @server_version = Integer(@opts[:server_version])
196   end
197   @server_version = synchronize(server) do |conn|
198     (conn.server_version rescue nil) if conn.respond_to?(:server_version)
199   end
200   unless @server_version
201     m = /^(\d+)\.(\d+)\.(\d+)/.match(fetch("SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)").single_value.to_s)
202     @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i
203   end
204   @server_version
205 end
supports_partial_indexes?() click to toggle source

MSSQL 2008+ supports partial indexes.

    # File lib/sequel/adapters/shared/mssql.rb
208 def supports_partial_indexes?
209   dataset.send(:is_2008_or_later?)
210 end
supports_savepoints?() click to toggle source

MSSQL supports savepoints, though it doesn't support releasing them

    # File lib/sequel/adapters/shared/mssql.rb
213 def supports_savepoints?
214   true
215 end
supports_transaction_isolation_levels?() click to toggle source

MSSQL supports transaction isolation levels

    # File lib/sequel/adapters/shared/mssql.rb
218 def supports_transaction_isolation_levels?
219   true
220 end
supports_transactional_ddl?() click to toggle source

MSSQL supports transaction DDL statements.

    # File lib/sequel/adapters/shared/mssql.rb
223 def supports_transactional_ddl?
224   true
225 end
tables(opts=OPTS) click to toggle source

Microsoft SQL Server supports using the INFORMATION_SCHEMA to get information on tables.

    # File lib/sequel/adapters/shared/mssql.rb
229 def tables(opts=OPTS)
230   information_schema_tables('BASE TABLE', opts)
231 end
views(opts=OPTS) click to toggle source

Microsoft SQL Server supports using the INFORMATION_SCHEMA to get information on views.

    # File lib/sequel/adapters/shared/mssql.rb
235 def views(opts=OPTS)
236   information_schema_tables('VIEW', opts)
237 end

Private Instance Methods

_metadata_dataset() click to toggle source

Always quote identifiers in the metadata_dataset, so schema parsing works.

Calls superclass method
    # File lib/sequel/adapters/shared/mssql.rb
387 def _metadata_dataset
388   super.with_quote_identifiers(true)
389 end
add_drop_default_constraint_sql(sqls, table, column) click to toggle source

Add dropping of the default constraint to the list of SQL queries. This is necessary before dropping the column or changing its type.

    # File lib/sequel/adapters/shared/mssql.rb
243 def add_drop_default_constraint_sql(sqls, table, column)
244   if constraint = default_constraint_name(table, column)
245     sqls << "ALTER TABLE #{quote_schema_table(table)} DROP CONSTRAINT #{constraint}"
246   end
247 end
alter_table_sql(table, op) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mssql.rb
254 def alter_table_sql(table, op)
255   case op[:op]
256   when :add_column
257     "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}"
258   when :drop_column
259     sqls = []
260     add_drop_default_constraint_sql(sqls, table, op[:name])
261     sqls << super
262   when :rename_column
263     "sp_rename #{literal("#{quote_schema_table(table)}.#{quote_identifier(op[:name])}")}, #{literal(metadata_dataset.with_quote_identifiers(false).quote_identifier(op[:new_name]))}, 'COLUMN'"
264   when :set_column_type
265     sqls = []
266     if sch = schema(table)
267       if cs = sch.each{|k, v| break v if k == op[:name]; nil}
268         cs = cs.dup
269         add_drop_default_constraint_sql(sqls, table, op[:name])
270         cs[:default] = cs[:ruby_default]
271         op = cs.merge!(op)
272         default = op.delete(:default)
273       end
274     end
275     sqls << "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{column_definition_sql(op)}"
276     sqls << alter_table_sql(table, op.merge(:op=>:set_column_default, :default=>default)) if default
277     sqls
278   when :set_column_null
279     sch = schema(table).find{|k,v| k.to_s == op[:name].to_s}.last
280     type = sch[:db_type]
281     if [:string, :decimal].include?(sch[:type]) && !["text", "ntext"].include?(type) && (size = (sch[:max_chars] || sch[:column_size]))
282       size = "MAX" if size == -1
283       type += "(#{size}#{", #{sch[:scale]}" if sch[:scale] && sch[:scale].to_i > 0})"
284     end
285     "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} #{type_literal(:type=>type)} #{'NOT ' unless op[:null]}NULL"
286   when :set_column_default
287     "ALTER TABLE #{quote_schema_table(table)} ADD CONSTRAINT #{quote_identifier("sequel_#{table}_#{op[:name]}_def")} DEFAULT #{literal(op[:default])} FOR #{quote_identifier(op[:name])}"
288   else
289     super(table, op)
290   end
291 end
auto_increment_sql() click to toggle source

MSSQL uses the IDENTITY(1,1) column for autoincrementing columns.

    # File lib/sequel/adapters/shared/mssql.rb
250 def auto_increment_sql
251   'IDENTITY(1,1)'
252 end
begin_savepoint_sql(depth) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
293 def begin_savepoint_sql(depth)
294   "SAVE TRANSACTION autopoint_#{depth}"
295 end
begin_transaction_sql() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
297 def begin_transaction_sql
298   "BEGIN TRANSACTION"
299 end
can_add_primary_key_constraint_on_nullable_columns?() click to toggle source

MSSQL does not allow adding primary key constraints to NULLable columns.

    # File lib/sequel/adapters/shared/mssql.rb
302 def can_add_primary_key_constraint_on_nullable_columns?
303   false
304 end
column_schema_normalize_default(default, type) click to toggle source

Handle MSSQL specific default format.

Calls superclass method
    # File lib/sequel/adapters/shared/mssql.rb
307 def column_schema_normalize_default(default, type)
308   if m = /\A(?:\(N?('.*')\)|\(\((-?\d+(?:\.\d+)?)\)\))\z/.match(default)
309     default = m[1] || m[2]
310   end
311   super(default, type)
312 end
commit_transaction(conn, opts=OPTS) click to toggle source

Commit the active transaction on the connection, does not release savepoints.

    # File lib/sequel/adapters/shared/mssql.rb
315 def commit_transaction(conn, opts=OPTS)
316   log_connection_execute(conn, commit_transaction_sql) unless savepoint_level(conn) > 1
317 end
commit_transaction_sql() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
319 def commit_transaction_sql
320   "COMMIT TRANSACTION"
321 end
create_table_as(name, ds, options) click to toggle source

MSSQL doesn't support CREATE TABLE AS, it only supports SELECT INTO. Emulating CREATE TABLE AS using SELECT INTO is only possible if a dataset is given as the argument, it can't work with a string, so raise an Error if a string is given.

    # File lib/sequel/adapters/shared/mssql.rb
334 def create_table_as(name, ds, options)
335   raise(Error, "must provide dataset instance as value of create_table :as option on MSSQL") unless ds.is_a?(Sequel::Dataset)
336   run(ds.into(name).sql)
337 end
create_table_prefix_sql(name, options) click to toggle source

MSSQL uses the name of the table to decide the difference between a regular and temporary table, with temporary table names starting with a #.

    # File lib/sequel/adapters/shared/mssql.rb
326 def create_table_prefix_sql(name, options)
327   "CREATE TABLE #{quote_schema_table(options[:temp] ? "##{name}" : name)}"
328 end
database_error_regexps() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
347 def database_error_regexps
348   DATABASE_ERROR_REGEXPS
349 end
default_constraint_name(table, column_name) click to toggle source

The name of the constraint for setting the default value on the table and column. The SQL used to select default constraints utilizes MSSQL catalog views which were introduced in 2005. This method intentionally does not support MSSQL 2000.

    # File lib/sequel/adapters/shared/mssql.rb
354 def default_constraint_name(table, column_name)
355   if server_version >= 9000000
356     table_name = schema_and_table(table).compact.join('.')
357     self[Sequel[:sys][:default_constraints]].
358       where{{:parent_object_id => Sequel::SQL::Function.new(:object_id, table_name), col_name(:parent_object_id, :parent_column_id) => column_name.to_s}}.
359       get(:name)
360   end
361 end
drop_index_sql(table, op) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
363 def drop_index_sql(table, op)
364   "DROP INDEX #{quote_identifier(op[:name] || default_index_name(table, op[:columns]))} ON #{quote_schema_table(table)}"
365 end
index_definition_sql(table_name, index) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
367 def index_definition_sql(table_name, index)
368   index_name = index[:name] || default_index_name(table_name, index[:columns])
369   raise Error, "Partial indexes are not supported for this database" if index[:where] && !supports_partial_indexes?
370   if index[:type] == :full_text
371     "CREATE FULLTEXT INDEX ON #{quote_schema_table(table_name)} #{literal(index[:columns])} KEY INDEX #{literal(index[:key_index])}"
372   else
373     "CREATE #{'UNIQUE ' if index[:unique]}#{'CLUSTERED ' if index[:type] == :clustered}INDEX #{quote_identifier(index_name)} ON #{quote_schema_table(table_name)} #{literal(index[:columns])}#{" INCLUDE #{literal(index[:include])}" if index[:include]}#{" WHERE #{filter_expr(index[:where])}" if index[:where]}"
374   end
375 end
information_schema_tables(type, opts) click to toggle source

Backbone of the tables and views support.

    # File lib/sequel/adapters/shared/mssql.rb
378 def information_schema_tables(type, opts)
379   m = output_identifier_meth
380   metadata_dataset.from(Sequel[:information_schema][:tables].as(:t)).
381     select(:table_name).
382     where(:table_type=>type, :table_schema=>(opts[:schema]||'dbo').to_s).
383     map{|x| m.call(x[:table_name])}
384 end
rename_table_sql(name, new_name) click to toggle source

Use sp_rename to rename the table

    # File lib/sequel/adapters/shared/mssql.rb
392 def rename_table_sql(name, new_name)
393   "sp_rename #{literal(quote_schema_table(name))}, #{quote_identifier(schema_and_table(new_name).pop)}"
394 end
rollback_savepoint_sql(depth) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
396 def rollback_savepoint_sql(depth)
397   "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION autopoint_#{depth}"
398 end
rollback_transaction_sql() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
400 def rollback_transaction_sql
401   "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION"
402 end
schema_column_type(db_type) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mssql.rb
404 def schema_column_type(db_type)
405   case db_type
406   when /\A(?:bit)\z/io
407     :boolean
408   when /\A(?:(?:small)?money)\z/io
409     :decimal
410   when /\A(timestamp|rowversion)\z/io
411     :blob
412   else
413     super
414   end
415 end
schema_parse_table(table_name, opts) click to toggle source

MSSQL uses the INFORMATION_SCHEMA to hold column information, and parses primary key information from the sysindexes, sysindexkeys, and syscolumns system tables.

    # File lib/sequel/adapters/shared/mssql.rb
420 def schema_parse_table(table_name, opts)
421   m = output_identifier_meth(opts[:dataset])
422   m2 = input_identifier_meth(opts[:dataset])
423   tn = m2.call(table_name.to_s)
424   table_id = get(Sequel.function(:object_id, tn))
425   info_sch_sch = opts[:information_schema_schema]
426   inf_sch_qual = lambda{|s| info_sch_sch ? Sequel.qualify(info_sch_sch, s) : Sequel[s]}
427   sys_qual = lambda{|s| info_sch_sch ? Sequel.qualify(info_sch_sch, Sequel.qualify(Sequel.lit(''), s)) : Sequel[s]}
428 
429   identity_cols = metadata_dataset.from(Sequel.lit('[sys].[columns]')).
430     where(:object_id=>table_id, :is_identity=>true).
431     select_map(:name)
432 
433   pk_index_id = metadata_dataset.from(sys_qual.call(Sequel.lit('sysindexes'))).
434     where(:id=>table_id, :indid=>1..254){{(status & 2048)=>2048}}.
435     get(:indid)
436   pk_cols = metadata_dataset.from(sys_qual.call(Sequel.lit('sysindexkeys')).as(:sik)).
437     join(sys_qual.call(Sequel.lit('syscolumns')).as(:sc), :id=>:id, :colid=>:colid).
438     where{{sik[:id]=>table_id, sik[:indid]=>pk_index_id}}.
439     select_order_map{sc[:name]}
440 
441   ds = metadata_dataset.from(inf_sch_qual.call(Sequel[:information_schema][:tables]).as(:t)).
442    join(inf_sch_qual.call(Sequel[:information_schema][:columns]).as(:c), :table_catalog=>:table_catalog,
443         :table_schema => :table_schema, :table_name => :table_name).
444    select{[column_name.as(:column), data_type.as(:db_type), character_maximum_length.as(:max_chars), column_default.as(:default), is_nullable.as(:allow_null), numeric_precision.as(:column_size), numeric_scale.as(:scale)]}.
445    where{{c[:table_name]=>tn}}
446 
447   if schema = opts[:schema]
448     ds = ds.where{{c[:table_schema]=>schema}}
449   end
450 
451   ds.map do |row|
452     if row[:primary_key] = pk_cols.include?(row[:column])
453       row[:auto_increment] = identity_cols.include?(row[:column])
454     end
455     row[:allow_null] = row[:allow_null] == 'YES' ? true : false
456     row[:default] = nil if blank_object?(row[:default])
457     row[:type] = if row[:db_type] =~ /number|numeric|decimal/i && row[:scale] == 0
458       :integer
459     else
460       schema_column_type(row[:db_type])
461     end
462     row[:max_length] = row[:max_chars] if row[:type] == :string && row[:max_chars] >= 0
463     [m.call(row.delete(:column)), row]
464   end
465 end
set_mssql_unicode_strings() click to toggle source

Set the mssql_unicode_strings settings from the given options.

    # File lib/sequel/adapters/shared/mssql.rb
468 def set_mssql_unicode_strings
469   @mssql_unicode_strings = typecast_value_boolean(@opts.fetch(:mssql_unicode_strings, true))
470 end
type_literal_generic_datetime(column) click to toggle source

MSSQL has both datetime and timestamp classes, most people are going to want datetime

    # File lib/sequel/adapters/shared/mssql.rb
474 def type_literal_generic_datetime(column)
475   :datetime
476 end
type_literal_generic_file(column) click to toggle source

MSSQL uses varbinary(max) type for blobs

    # File lib/sequel/adapters/shared/mssql.rb
484 def type_literal_generic_file(column)
485   :'varbinary(max)'
486 end
type_literal_generic_trueclass(column) click to toggle source

MSSQL doesn't have a true boolean class, so it uses bit

    # File lib/sequel/adapters/shared/mssql.rb
479 def type_literal_generic_trueclass(column)
480   :bit
481 end
view_with_check_option_support() click to toggle source

MSSQL supports views with check option, but not local.

    # File lib/sequel/adapters/shared/mssql.rb
489 def view_with_check_option_support
490   true
491 end