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
405 def _metadata_dataset
406   super.with_quote_identifiers(true)
407 end
add_clustered_sql_fragment(sql, opts) click to toggle source

Add CLUSTERED or NONCLUSTERED as needed

    # File lib/sequel/adapters/shared/mssql.rb
248 def add_clustered_sql_fragment(sql, opts)
249   clustered = opts[:clustered]
250   unless clustered.nil?
251     sql += " #{'NON' unless clustered}CLUSTERED"
252   end
253 
254   sql
255 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
259 def add_drop_default_constraint_sql(sqls, table, column)
260   if constraint = default_constraint_name(table, column)
261     sqls << "ALTER TABLE #{quote_schema_table(table)} DROP CONSTRAINT #{constraint}"
262   end
263 end
alter_table_sql(table, op) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mssql.rb
270 def alter_table_sql(table, op)
271   case op[:op]
272   when :add_column
273     "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}"
274   when :drop_column
275     sqls = []
276     add_drop_default_constraint_sql(sqls, table, op[:name])
277     sqls << super
278   when :rename_column
279     "sp_rename #{literal("#{quote_schema_table(table)}.#{quote_identifier(op[:name])}")}, #{literal(metadata_dataset.with_quote_identifiers(false).quote_identifier(op[:new_name]))}, 'COLUMN'"
280   when :set_column_type
281     sqls = []
282     if sch = schema(table)
283       if cs = sch.each{|k, v| break v if k == op[:name]; nil}
284         cs = cs.dup
285         add_drop_default_constraint_sql(sqls, table, op[:name])
286         cs[:default] = cs[:ruby_default]
287         op = cs.merge!(op)
288         default = op.delete(:default)
289       end
290     end
291     sqls << "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{column_definition_sql(op)}"
292     sqls << alter_table_sql(table, op.merge(:op=>:set_column_default, :default=>default, :skip_drop_default=>true)) if default
293     sqls
294   when :set_column_null
295     sch = schema(table).find{|k,v| k.to_s == op[:name].to_s}.last
296     type = sch[:db_type]
297     if [:string, :decimal, :blob].include?(sch[:type]) && !["text", "ntext"].include?(type) && (size = (sch[:max_chars] || sch[:column_size]))
298       size = "MAX" if size == -1
299       type += "(#{size}#{", #{sch[:scale]}" if sch[:scale] && sch[:scale].to_i > 0})"
300     end
301     "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} #{type_literal(:type=>type)} #{'NOT ' unless op[:null]}NULL"
302   when :set_column_default
303     sqls = []
304     add_drop_default_constraint_sql(sqls, table, op[:name]) unless op[:skip_drop_default]
305     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])}"
306   else
307     super(table, op)
308   end
309 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
266 def auto_increment_sql
267   'IDENTITY(1,1)'
268 end
begin_savepoint_sql(depth) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
311 def begin_savepoint_sql(depth)
312   "SAVE TRANSACTION autopoint_#{depth}"
313 end
begin_transaction_sql() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
315 def begin_transaction_sql
316   "BEGIN TRANSACTION"
317 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
320 def can_add_primary_key_constraint_on_nullable_columns?
321   false
322 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
325 def column_schema_normalize_default(default, type)
326   if m = /\A(?:\(N?('.*')\)|\(\((-?\d+(?:\.\d+)?)\)\))\z/.match(default)
327     default = m[1] || m[2]
328   end
329   super(default, type)
330 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
333 def commit_transaction(conn, opts=OPTS)
334   log_connection_execute(conn, commit_transaction_sql) unless savepoint_level(conn) > 1
335 end
commit_transaction_sql() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
337 def commit_transaction_sql
338   "COMMIT TRANSACTION"
339 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
352 def create_table_as(name, ds, options)
353   raise(Error, "must provide dataset instance as value of create_table :as option on MSSQL") unless ds.is_a?(Sequel::Dataset)
354   run(ds.into(name).sql)
355 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
344 def create_table_prefix_sql(name, options)
345   "CREATE TABLE #{quote_schema_table(options[:temp] ? "##{name}" : name)}"
346 end
database_error_regexps() click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
365 def database_error_regexps
366   DATABASE_ERROR_REGEXPS
367 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
372 def default_constraint_name(table, column_name)
373   if server_version >= 9000000
374     table_name = schema_and_table(table).compact.join('.')
375     self[Sequel[:sys][:default_constraints]].
376       where{{:parent_object_id => Sequel::SQL::Function.new(:object_id, table_name), col_name(:parent_object_id, :parent_column_id) => column_name.to_s}}.
377       get(:name)
378   end
379 end
drop_index_sql(table, op) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
381 def drop_index_sql(table, op)
382   "DROP INDEX #{quote_identifier(op[:name] || default_index_name(table, op[:columns]))} ON #{quote_schema_table(table)}"
383 end
index_definition_sql(table_name, index) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
385 def index_definition_sql(table_name, index)
386   index_name = index[:name] || default_index_name(table_name, index[:columns])
387   raise Error, "Partial indexes are not supported for this database" if index[:where] && !supports_partial_indexes?
388   if index[:type] == :full_text
389     "CREATE FULLTEXT INDEX ON #{quote_schema_table(table_name)} #{literal(index[:columns])} KEY INDEX #{literal(index[:key_index])}"
390   else
391     "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]}"
392   end
393 end
information_schema_tables(type, opts) click to toggle source

Backbone of the tables and views support.

    # File lib/sequel/adapters/shared/mssql.rb
396 def information_schema_tables(type, opts)
397   m = output_identifier_meth
398   metadata_dataset.from(Sequel[:information_schema][:tables].as(:t)).
399     select(:table_name).
400     where(:table_type=>type, :table_schema=>(opts[:schema]||'dbo').to_s).
401     map{|x| m.call(x[:table_name])}
402 end
primary_key_constraint_sql_fragment(opts) click to toggle source

Handle clustered and nonclustered primary keys

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

MSSQL uses varbinary(max) type for blobs

    # File lib/sequel/adapters/shared/mssql.rb
506 def type_literal_generic_file(column)
507   :'varbinary(max)'
508 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
501 def type_literal_generic_trueclass(column)
502   :bit
503 end
unique_constraint_sql_fragment(opts) click to toggle source

Handle clustered and nonclustered unique constraints

Calls superclass method
    # File lib/sequel/adapters/shared/mssql.rb
511 def unique_constraint_sql_fragment(opts)
512   add_clustered_sql_fragment(super, opts)
513 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
516 def view_with_check_option_support
517   true
518 end