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
393 def _metadata_dataset
394   super.with_quote_identifiers(true)
395 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)) 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     "ALTER TABLE #{quote_schema_table(table)} ADD CONSTRAINT #{quote_identifier("sequel_#{table}_#{op[:name]}_def")} DEFAULT #{literal(op[:default])} FOR #{quote_identifier(op[:name])}"
294   else
295     super(table, op)
296   end
297 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
299 def begin_savepoint_sql(depth)
300   "SAVE TRANSACTION autopoint_#{depth}"
301 end
begin_transaction_sql() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
303 def begin_transaction_sql
304   "BEGIN TRANSACTION"
305 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
308 def can_add_primary_key_constraint_on_nullable_columns?
309   false
310 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
313 def column_schema_normalize_default(default, type)
314   if m = /\A(?:\(N?('.*')\)|\(\((-?\d+(?:\.\d+)?)\)\))\z/.match(default)
315     default = m[1] || m[2]
316   end
317   super(default, type)
318 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
321 def commit_transaction(conn, opts=OPTS)
322   log_connection_execute(conn, commit_transaction_sql) unless savepoint_level(conn) > 1
323 end
commit_transaction_sql() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
325 def commit_transaction_sql
326   "COMMIT TRANSACTION"
327 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
340 def create_table_as(name, ds, options)
341   raise(Error, "must provide dataset instance as value of create_table :as option on MSSQL") unless ds.is_a?(Sequel::Dataset)
342   run(ds.into(name).sql)
343 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
332 def create_table_prefix_sql(name, options)
333   "CREATE TABLE #{quote_schema_table(options[:temp] ? "##{name}" : name)}"
334 end
database_error_regexps() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
353 def database_error_regexps
354   DATABASE_ERROR_REGEXPS
355 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
360 def default_constraint_name(table, column_name)
361   if server_version >= 9000000
362     table_name = schema_and_table(table).compact.join('.')
363     self[Sequel[:sys][:default_constraints]].
364       where{{:parent_object_id => Sequel::SQL::Function.new(:object_id, table_name), col_name(:parent_object_id, :parent_column_id) => column_name.to_s}}.
365       get(:name)
366   end
367 end
drop_index_sql(table, op) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
369 def drop_index_sql(table, op)
370   "DROP INDEX #{quote_identifier(op[:name] || default_index_name(table, op[:columns]))} ON #{quote_schema_table(table)}"
371 end
index_definition_sql(table_name, index) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
373 def index_definition_sql(table_name, index)
374   index_name = index[:name] || default_index_name(table_name, index[:columns])
375   raise Error, "Partial indexes are not supported for this database" if index[:where] && !supports_partial_indexes?
376   if index[:type] == :full_text
377     "CREATE FULLTEXT INDEX ON #{quote_schema_table(table_name)} #{literal(index[:columns])} KEY INDEX #{literal(index[:key_index])}"
378   else
379     "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]}"
380   end
381 end
information_schema_tables(type, opts) click to toggle source

Backbone of the tables and views support.

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

MSSQL uses varbinary(max) type for blobs

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