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

Because Sequel datasets only support a single result set per query, and retrieving the result code and number of rows requires a query, this does not support stored procedures which also return result sets. To handle such stored procedures, you should drop down to the connection/driver level by using Sequel::Database#synchronize to get access to the underlying connection object.

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
 60 def call_mssql_sproc(name, opts=OPTS)
 61   args = opts[:args] || []
 62   names = ['@RC AS RESULT', '@@ROWCOUNT AS NUMROWS']
 63   declarations = ['@RC int']
 64   values = []
 65 
 66   if args.is_a?(Hash)
 67     named_args = true
 68     args = args.to_a
 69     method = :each
 70   else
 71     method = :each_with_index
 72   end
 73 
 74   args.public_send(method) do |v, i|
 75     if named_args
 76       k = v
 77       v, type, select = i
 78       raise Error, "must provide output parameter name when using output parameters with named arguments" if v == :output && !select
 79     else
 80       v, type, select = v
 81     end
 82 
 83     if v == :output
 84       type ||= "nvarchar(max)"
 85       if named_args
 86         varname = select
 87       else
 88         varname = "var#{i}"
 89         select ||= varname
 90       end
 91       names << "@#{varname} AS #{quote_identifier(select)}"
 92       declarations << "@#{varname} #{type}"
 93       value = "@#{varname} OUTPUT"
 94     else
 95       value = literal(v)
 96     end
 97 
 98     if named_args
 99       value = "@#{k}=#{value}"
100     end
101 
102     values << value
103   end
104 
105   sql = "DECLARE #{declarations.join(', ')}; EXECUTE @RC = #{name} #{values.join(', ')}; SELECT #{names.join(', ')}"
106 
107   ds = dataset.with_sql(sql)
108   ds = ds.server(opts[:server]) if opts[:server]
109   ds.first
110 end
database_type() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
112 def database_type
113   :mssql
114 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
123 def foreign_key_list(table, opts=OPTS)
124   m = output_identifier_meth
125   im = input_identifier_meth
126   schema, table = schema_and_table(table)
127   current_schema = m.call(get(Sequel.function('schema_name')))
128   fk_action_map = FOREIGN_KEY_ACTION_MAP
129   fk = Sequel[:fk]
130   fkc = Sequel[:fkc]
131   ds = metadata_dataset.from(Sequel.lit('[sys].[foreign_keys]').as(:fk)).
132     join(Sequel.lit('[sys].[foreign_key_columns]').as(:fkc), :constraint_object_id => :object_id).
133     join(Sequel.lit('[sys].[all_columns]').as(:pc), :object_id => fkc[:parent_object_id],     :column_id => fkc[:parent_column_id]).
134     join(Sequel.lit('[sys].[all_columns]').as(:rc), :object_id => fkc[:referenced_object_id], :column_id => fkc[:referenced_column_id]).
135     where{{object_schema_name(fk[:parent_object_id]) => im.call(schema || current_schema)}}.
136     where{{object_name(fk[:parent_object_id]) => im.call(table)}}.
137     select{[fk[:name], 
138             fk[:delete_referential_action], 
139             fk[:update_referential_action], 
140             pc[:name].as(:column), 
141             rc[:name].as(:referenced_column), 
142             object_schema_name(fk[:referenced_object_id]).as(:schema), 
143             object_name(fk[:referenced_object_id]).as(:table)]}.
144     order(fk[:name], fkc[:constraint_column_id])
145   h = {}
146   ds.each do |row|
147     if r = h[row[:name]]
148       r[:columns] << m.call(row[:column])
149       r[:key] << m.call(row[:referenced_column])
150     else
151       referenced_schema = m.call(row[:schema])
152       referenced_table = m.call(row[:table])
153       h[row[:name]] = { :name      => m.call(row[:name]), 
154                         :table     => (referenced_schema == current_schema) ? referenced_table : Sequel.qualify(referenced_schema, referenced_table),
155                         :columns   => [m.call(row[:column])], 
156                         :key       => [m.call(row[:referenced_column])], 
157                         :on_update => fk_action_map[row[:update_referential_action]], 
158                         :on_delete => fk_action_map[row[:delete_referential_action]] }
159     end
160   end
161   h.values
162 end
freeze() click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mssql.rb
164 def freeze
165   server_version
166   super
167 end
global_index_namespace?() click to toggle source

Microsoft SQL Server namespaces indexes per table.

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

Use the system tables to get index information

    # File lib/sequel/adapters/shared/mssql.rb
170 def indexes(table, opts=OPTS)
171   m = output_identifier_meth
172   im = input_identifier_meth
173   indexes = {}
174   table = table.value if table.is_a?(Sequel::SQL::Identifier)
175   i = Sequel[:i]
176   ds = metadata_dataset.from(Sequel.lit('[sys].[tables]').as(:t)).
177    join(Sequel.lit('[sys].[indexes]').as(:i), :object_id=>:object_id).
178    join(Sequel.lit('[sys].[index_columns]').as(:ic), :object_id=>:object_id, :index_id=>:index_id).
179    join(Sequel.lit('[sys].[columns]').as(:c), :object_id=>:object_id, :column_id=>:column_id).
180    select(i[:name], i[:is_unique], Sequel[:c][:name].as(:column)).
181    where{{t[:name]=>im.call(table)}}.
182    where(i[:is_primary_key]=>0, i[:is_disabled]=>0).
183    order(i[:name], Sequel[:ic][:index_column_id])
184 
185   if supports_partial_indexes?
186     ds = ds.where(i[:has_filter]=>0)
187   end
188 
189   ds.each do |r|
190     index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)}
191     index[:columns] << m.call(r[:column])
192   end
193   indexes
194 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
198 def server_version(server=nil)
199   return @server_version if @server_version
200   if @opts[:server_version]
201     return @server_version = Integer(@opts[:server_version])
202   end
203   @server_version = synchronize(server) do |conn|
204     (conn.server_version rescue nil) if conn.respond_to?(:server_version)
205   end
206   unless @server_version
207     m = /^(\d+)\.(\d+)\.(\d+)/.match(fetch("SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)").single_value.to_s)
208     @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i
209   end
210   @server_version
211 end
supports_partial_indexes?() click to toggle source

MSSQL 2008+ supports partial indexes.

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

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

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

MSSQL supports transaction isolation levels

    # File lib/sequel/adapters/shared/mssql.rb
224 def supports_transaction_isolation_levels?
225   true
226 end
supports_transactional_ddl?() click to toggle source

MSSQL supports transaction DDL statements.

    # File lib/sequel/adapters/shared/mssql.rb
229 def supports_transactional_ddl?
230   true
231 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
235 def tables(opts=OPTS)
236   information_schema_tables('BASE TABLE', opts)
237 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
241 def views(opts=OPTS)
242   information_schema_tables('VIEW', opts)
243 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
395 def _metadata_dataset
396   super.with_quote_identifiers(true)
397 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
249 def add_drop_default_constraint_sql(sqls, table, column)
250   if constraint = default_constraint_name(table, column)
251     sqls << "ALTER TABLE #{quote_schema_table(table)} DROP CONSTRAINT #{constraint}"
252   end
253 end
alter_table_sql(table, op) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mssql.rb
260 def alter_table_sql(table, op)
261   case op[:op]
262   when :add_column
263     "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}"
264   when :drop_column
265     sqls = []
266     add_drop_default_constraint_sql(sqls, table, op[:name])
267     sqls << super
268   when :rename_column
269     "sp_rename #{literal("#{quote_schema_table(table)}.#{quote_identifier(op[:name])}")}, #{literal(metadata_dataset.with_quote_identifiers(false).quote_identifier(op[:new_name]))}, 'COLUMN'"
270   when :set_column_type
271     sqls = []
272     if sch = schema(table)
273       if cs = sch.each{|k, v| break v if k == op[:name]; nil}
274         cs = cs.dup
275         add_drop_default_constraint_sql(sqls, table, op[:name])
276         cs[:default] = cs[:ruby_default]
277         op = cs.merge!(op)
278         default = op.delete(:default)
279       end
280     end
281     sqls << "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{column_definition_sql(op)}"
282     sqls << alter_table_sql(table, op.merge(:op=>:set_column_default, :default=>default, :skip_drop_default=>true)) if default
283     sqls
284   when :set_column_null
285     sch = schema(table).find{|k,v| k.to_s == op[:name].to_s}.last
286     type = sch[:db_type]
287     if [:string, :decimal].include?(sch[:type]) && !["text", "ntext"].include?(type) && (size = (sch[:max_chars] || sch[:column_size]))
288       size = "MAX" if size == -1
289       type += "(#{size}#{", #{sch[:scale]}" if sch[:scale] && sch[:scale].to_i > 0})"
290     end
291     "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} #{type_literal(:type=>type)} #{'NOT ' unless op[:null]}NULL"
292   when :set_column_default
293     sqls = []
294     add_drop_default_constraint_sql(sqls, table, op[:name]) unless op[:skip_drop_default]
295     sqls << "ALTER TABLE #{quote_schema_table(table)} ADD CONSTRAINT #{quote_identifier("sequel_#{table}_#{op[:name]}_def")} DEFAULT #{literal(op[:default])} FOR #{quote_identifier(op[:name])}"
296   else
297     super(table, op)
298   end
299 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
256 def auto_increment_sql
257   'IDENTITY(1,1)'
258 end
begin_savepoint_sql(depth) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
301 def begin_savepoint_sql(depth)
302   "SAVE TRANSACTION autopoint_#{depth}"
303 end
begin_transaction_sql() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
305 def begin_transaction_sql
306   "BEGIN TRANSACTION"
307 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
310 def can_add_primary_key_constraint_on_nullable_columns?
311   false
312 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
315 def column_schema_normalize_default(default, type)
316   if m = /\A(?:\(N?('.*')\)|\(\((-?\d+(?:\.\d+)?)\)\))\z/.match(default)
317     default = m[1] || m[2]
318   end
319   super(default, type)
320 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
323 def commit_transaction(conn, opts=OPTS)
324   log_connection_execute(conn, commit_transaction_sql) unless savepoint_level(conn) > 1
325 end
commit_transaction_sql() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
327 def commit_transaction_sql
328   "COMMIT TRANSACTION"
329 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
342 def create_table_as(name, ds, options)
343   raise(Error, "must provide dataset instance as value of create_table :as option on MSSQL") unless ds.is_a?(Sequel::Dataset)
344   run(ds.into(name).sql)
345 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
334 def create_table_prefix_sql(name, options)
335   "CREATE TABLE #{quote_schema_table(options[:temp] ? "##{name}" : name)}"
336 end
database_error_regexps() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
355 def database_error_regexps
356   DATABASE_ERROR_REGEXPS
357 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
362 def default_constraint_name(table, column_name)
363   if server_version >= 9000000
364     table_name = schema_and_table(table).compact.join('.')
365     self[Sequel[:sys][:default_constraints]].
366       where{{:parent_object_id => Sequel::SQL::Function.new(:object_id, table_name), col_name(:parent_object_id, :parent_column_id) => column_name.to_s}}.
367       get(:name)
368   end
369 end
drop_index_sql(table, op) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
371 def drop_index_sql(table, op)
372   "DROP INDEX #{quote_identifier(op[:name] || default_index_name(table, op[:columns]))} ON #{quote_schema_table(table)}"
373 end
index_definition_sql(table_name, index) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
375 def index_definition_sql(table_name, index)
376   index_name = index[:name] || default_index_name(table_name, index[:columns])
377   raise Error, "Partial indexes are not supported for this database" if index[:where] && !supports_partial_indexes?
378   if index[:type] == :full_text
379     "CREATE FULLTEXT INDEX ON #{quote_schema_table(table_name)} #{literal(index[:columns])} KEY INDEX #{literal(index[:key_index])}"
380   else
381     "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]}"
382   end
383 end
information_schema_tables(type, opts) click to toggle source

Backbone of the tables and views support.

    # File lib/sequel/adapters/shared/mssql.rb
386 def information_schema_tables(type, opts)
387   m = output_identifier_meth
388   metadata_dataset.from(Sequel[:information_schema][:tables].as(:t)).
389     select(:table_name).
390     where(:table_type=>type, :table_schema=>(opts[:schema]||'dbo').to_s).
391     map{|x| m.call(x[:table_name])}
392 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
400 def rename_table_sql(name, new_name)
401   "sp_rename #{literal(quote_schema_table(name))}, #{quote_identifier(schema_and_table(new_name).pop)}"
402 end
rollback_savepoint_sql(depth) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
404 def rollback_savepoint_sql(depth)
405   "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION autopoint_#{depth}"
406 end
rollback_transaction_sql() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
408 def rollback_transaction_sql
409   "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION"
410 end
schema_column_type(db_type) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mssql.rb
412 def schema_column_type(db_type)
413   case db_type
414   when /\A(?:bit)\z/io
415     :boolean
416   when /\A(?:(?:small)?money)\z/io
417     :decimal
418   when /\A(timestamp|rowversion)\z/io
419     :blob
420   else
421     super
422   end
423 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
428 def schema_parse_table(table_name, opts)
429   m = output_identifier_meth(opts[:dataset])
430   m2 = input_identifier_meth(opts[:dataset])
431   tn = m2.call(table_name.to_s)
432   info_sch_sch = opts[:information_schema_schema]
433   inf_sch_qual = lambda{|s| info_sch_sch ? Sequel.qualify(info_sch_sch, s) : Sequel[s]}
434   table_id = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:objects])).where(:name => tn).select_map(:object_id).first
435 
436   identity_cols = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:columns])).
437     where(:object_id=>table_id, :is_identity=>true).
438     select_map(:name)
439 
440   pk_index_id = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:sysindexes])).
441     where(:id=>table_id, :indid=>1..254){{(status & 2048)=>2048}}.
442     get(:indid)
443   pk_cols = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:sysindexkeys]).as(:sik)).
444     join(inf_sch_qual.call(Sequel[:sys][:syscolumns]).as(:sc), :id=>:id, :colid=>:colid).
445     where{{sik[:id]=>table_id, sik[:indid]=>pk_index_id}}.
446     select_order_map{sc[:name]}
447 
448   ds = metadata_dataset.from(inf_sch_qual.call(Sequel[:information_schema][:tables]).as(:t)).
449    join(inf_sch_qual.call(Sequel[:information_schema][:columns]).as(:c), :table_catalog=>:table_catalog,
450         :table_schema => :table_schema, :table_name => :table_name).
451    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)]}.
452    where{{c[:table_name]=>tn}}
453 
454   if schema = opts[:schema]
455     ds = ds.where{{c[:table_schema]=>schema}}
456   end
457 
458   ds.map do |row|
459     if row[:primary_key] = pk_cols.include?(row[:column])
460       row[:auto_increment] = identity_cols.include?(row[:column])
461     end
462     row[:allow_null] = row[:allow_null] == 'YES' ? true : false
463     row[:default] = nil if blank_object?(row[:default])
464     row[:type] = if row[:db_type] =~ /number|numeric|decimal/i && row[:scale] == 0
465       :integer
466     else
467       schema_column_type(row[:db_type])
468     end
469     row[:max_length] = row[:max_chars] if row[:type] == :string && row[:max_chars] >= 0
470     [m.call(row.delete(:column)), row]
471   end
472 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
475 def set_mssql_unicode_strings
476   @mssql_unicode_strings = typecast_value_boolean(@opts.fetch(:mssql_unicode_strings, true))
477 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
481 def type_literal_generic_datetime(column)
482   :datetime
483 end
type_literal_generic_file(column) click to toggle source

MSSQL uses varbinary(max) type for blobs

    # File lib/sequel/adapters/shared/mssql.rb
491 def type_literal_generic_file(column)
492   :'varbinary(max)'
493 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
486 def type_literal_generic_trueclass(column)
487   :bit
488 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
496 def view_with_check_option_support
497   true
498 end