module Sequel::MSSQL::DatabaseMethods

Constants

AUTO_INCREMENT
DATABASE_ERROR_REGEXPS
DECIMAL_TYPE_RE

The types to check for 0 scale to transform :decimal types to :integer.

FOREIGN_KEY_ACTION_MAP
MSSQL_DEFAULT_RE
SERVER_VERSION_RE
SERVER_VERSION_SQL
SQL_BEGIN
SQL_COMMIT
SQL_ROLLBACK
SQL_ROLLBACK_TO_SAVEPOINT
SQL_SAVEPOINT

Attributes

mssql_unicode_strings[R]

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, line 71
def call_mssql_sproc(name, opts=OPTS)
  args = opts[:args] || []
  names = ['@RC AS RESULT', '@@ROWCOUNT AS NUMROWS']
  declarations = ['@RC int']
  values = []

  if args.is_a?(Hash)
    named_args = true
    args = args.to_a
    method = :each
  else
    method = :each_with_index
  end

  args.send(method) do |v, i|
    if named_args
      k = v
      v, type, select = i
      raise Error, "must provide output parameter name when using output parameters with named arguments" if v == :output && !select
    else
      v, type, select = v
    end

    if v == :output
      type ||= "nvarchar(max)"
      if named_args
        varname = select
      else
        varname = "var#{i}"
        select ||= varname
      end
      names << "@#{varname} AS #{quote_identifier(select)}"
      declarations << "@#{varname} #{type}"
      value = "@#{varname} OUTPUT"
    else
      value = literal(v)
    end

    if named_args
      value = "@#{k}=#{value}"
    end

    values << value
  end

  sql = "DECLARE #{declarations.join(', ')}; EXECUTE @RC = #{name} #{values.join(', ')}; SELECT #{names.join(', ')}"

  ds = dataset.with_sql(sql)
  ds = ds.server(opts[:server]) if opts[:server]
  ds.first
end
database_type() click to toggle source

Microsoft SQL Server uses the :mssql type.

# File lib/sequel/adapters/shared/mssql.rb, line 124
def database_type
  :mssql
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, line 135
def foreign_key_list(table, opts=OPTS)
  m = output_identifier_meth
  im = input_identifier_meth
  schema, table = schema_and_table(table)
  current_schema = m.call(get(Sequel.function('schema_name')))
  fk_action_map = FOREIGN_KEY_ACTION_MAP
  fk = Sequel[:fk]
  fkc = Sequel[:fkc]
  ds = metadata_dataset.from(Sequel.lit('[sys].[foreign_keys]').as(:fk)).
    join(Sequel.lit('[sys].[foreign_key_columns]').as(:fkc), :constraint_object_id => :object_id).
    join(Sequel.lit('[sys].[all_columns]').as(:pc), :object_id => fkc[:parent_object_id],     :column_id => fkc[:parent_column_id]).
    join(Sequel.lit('[sys].[all_columns]').as(:rc), :object_id => fkc[:referenced_object_id], :column_id => fkc[:referenced_column_id]).
    where{{object_schema_name(fk[:parent_object_id]) => im.call(schema || current_schema)}}.
    where{{object_name(fk[:parent_object_id]) => im.call(table)}}.
    select{[fk[:name], 
            fk[:delete_referential_action], 
            fk[:update_referential_action], 
            pc[:name].as(:column), 
            rc[:name].as(:referenced_column), 
            object_schema_name(fk[:referenced_object_id]).as(:schema), 
            object_name(fk[:referenced_object_id]).as(:table)]}.
    order(fk[:name], fkc[:constraint_column_id])
  h = {}
  ds.each do |row|
    if r = h[row[:name]]
      r[:columns] << m.call(row[:column])
      r[:key] << m.call(row[:referenced_column])
    else
      referenced_schema = m.call(row[:schema])
      referenced_table = m.call(row[:table])
      h[row[:name]] = { :name      => m.call(row[:name]), 
                        :table     => (referenced_schema == current_schema) ? referenced_table : Sequel.qualify(referenced_schema, referenced_table),
                        :columns   => [m.call(row[:column])], 
                        :key       => [m.call(row[:referenced_column])], 
                        :on_update => fk_action_map[row[:update_referential_action]], 
                        :on_delete => fk_action_map[row[:delete_referential_action]] }
    end
  end
  h.values
end
freeze() click to toggle source
Calls superclass method
# File lib/sequel/adapters/shared/mssql.rb, line 176
def freeze
  server_version
  super
end
global_index_namespace?() click to toggle source

Microsoft SQL Server namespaces indexes per table.

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

Use the system tables to get index information

# File lib/sequel/adapters/shared/mssql.rb, line 182
def indexes(table, opts=OPTS)
  m = output_identifier_meth
  im = input_identifier_meth
  indexes = {}
  i = Sequel[:i]
  ds = metadata_dataset.from(Sequel.lit('[sys].[tables]').as(:t)).
   join(Sequel.lit('[sys].[indexes]').as(:i), :object_id=>:object_id).
   join(Sequel.lit('[sys].[index_columns]').as(:ic), :object_id=>:object_id, :index_id=>:index_id).
   join(Sequel.lit('[sys].[columns]').as(:c), :object_id=>:object_id, :column_id=>:column_id).
   select(i[:name], i[:is_unique], Sequel[:c][:name].as(:column)).
   where{{t[:name]=>im.call(table)}}.
   where(i[:is_primary_key]=>0, i[:is_disabled]=>0).
   order(i[:name], Sequel[:ic][:index_column_id])

  if supports_partial_indexes?
    ds = ds.where(i[:has_filter]=>0)
  end

  ds.each do |r|
    index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)}
    index[:columns] << m.call(r[:column])
  end
  indexes
end
mssql_unicode_strings=(v) click to toggle source
# File lib/sequel/adapters/shared/mssql.rb, line 35
def mssql_unicode_strings=(v)
  @mssql_unicode_strings = v
  reset_default_dataset
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, line 209
def server_version(server=nil)
  return @server_version if @server_version
  if @opts[:server_version]
    return @server_version = Integer(@opts[:server_version])
  end
  @server_version = synchronize(server) do |conn|
    (conn.server_version rescue nil) if conn.respond_to?(:server_version)
  end
  unless @server_version
    m = SERVER_VERSION_RE.match(fetch(SERVER_VERSION_SQL).single_value.to_s)
    @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i
  end
  @server_version
end
supports_partial_indexes?() click to toggle source

MSSQL 2008+ supports partial indexes.

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

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

# File lib/sequel/adapters/shared/mssql.rb, line 230
def supports_savepoints?
  true
end
supports_transaction_isolation_levels?() click to toggle source

MSSQL supports transaction isolation levels

# File lib/sequel/adapters/shared/mssql.rb, line 235
def supports_transaction_isolation_levels?
  true
end
supports_transactional_ddl?() click to toggle source

MSSQL supports transaction DDL statements.

# File lib/sequel/adapters/shared/mssql.rb, line 240
def supports_transactional_ddl?
  true
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, line 246
def tables(opts=OPTS)
  information_schema_tables('BASE TABLE', opts)
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, line 252
def views(opts=OPTS)
  information_schema_tables('VIEW', opts)
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, line 410
def _metadata_dataset
  super.with_quote_identifiers(true)
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, line 260
def add_drop_default_constraint_sql(sqls, table, column)
  if constraint = default_constraint_name(table, column)
    sqls << "ALTER TABLE #{quote_schema_table(table)} DROP CONSTRAINT #{constraint}"
  end
end
alter_table_sql(table, op) click to toggle source

MSSQL specific syntax for altering tables.

Calls superclass method
# File lib/sequel/adapters/shared/mssql.rb, line 272
def alter_table_sql(table, op)
  case op[:op]
  when :add_column
    "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}"
  when :drop_column
    sqls = []
    add_drop_default_constraint_sql(sqls, table, op[:name])
    sqls << super
  when :rename_column
    "sp_rename #{literal("#{quote_schema_table(table)}.#{quote_identifier(op[:name])}")}, #{literal(op[:new_name].to_s)}, 'COLUMN'"
  when :set_column_type
    sqls = []
    if sch = schema(table)
      if cs = sch.each{|k, v| break v if k == op[:name]; nil}
        cs = cs.dup
        add_drop_default_constraint_sql(sqls, table, op[:name])
        cs[:default] = cs[:ruby_default]
        op = cs.merge!(op)
        default = op.delete(:default)
      end
    end
    sqls << "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{column_definition_sql(op)}"
    sqls << alter_table_sql(table, op.merge(:op=>:set_column_default, :default=>default)) if default
    sqls
  when :set_column_null
    sch = schema(table).find{|k,v| k.to_s == op[:name].to_s}.last
    type = sch[:db_type]
    if [:string, :decimal].include?(sch[:type]) && !["text", "ntext"].include?(type) && (size = (sch[:max_chars] || sch[:column_size]))
      size = "MAX" if size == -1
      type += "(#{size}#{", #{sch[:scale]}" if sch[:scale] && sch[:scale].to_i > 0})"
    end
    "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} #{type_literal(:type=>type)} #{'NOT ' unless op[:null]}NULL"
  when :set_column_default
    "ALTER TABLE #{quote_schema_table(table)} ADD CONSTRAINT #{quote_identifier("sequel_#{table}_#{op[:name]}_def")} DEFAULT #{literal(op[:default])} FOR #{quote_identifier(op[:name])}"
  else
    super(table, op)
  end
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, line 267
def auto_increment_sql
  AUTO_INCREMENT
end
begin_savepoint_sql(depth) click to toggle source

SQL to start a new savepoint

# File lib/sequel/adapters/shared/mssql.rb, line 312
def begin_savepoint_sql(depth)
  SQL_SAVEPOINT % depth
end
begin_transaction_sql() click to toggle source

SQL to BEGIN a transaction.

# File lib/sequel/adapters/shared/mssql.rb, line 317
def begin_transaction_sql
  SQL_BEGIN
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, line 322
def can_add_primary_key_constraint_on_nullable_columns?
  false
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, line 327
def column_schema_normalize_default(default, type)
  if m = MSSQL_DEFAULT_RE.match(default)
    default = m[1] || m[2]
  end
  super(default, type)
end
commit_transaction(conn, opts=OPTS) click to toggle source

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

# File lib/sequel/adapters/shared/mssql.rb, line 336
def commit_transaction(conn, opts=OPTS)
  log_connection_execute(conn, commit_transaction_sql) unless savepoint_level(conn) > 1
end
commit_transaction_sql() click to toggle source

SQL to COMMIT a transaction.

# File lib/sequel/adapters/shared/mssql.rb, line 341
def commit_transaction_sql
  SQL_COMMIT
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, line 356
def create_table_as(name, ds, options)
  raise(Error, "must provide dataset instance as value of create_table :as option on MSSQL") unless ds.is_a?(Sequel::Dataset)
  run(ds.into(name).sql)
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, line 348
def create_table_prefix_sql(name, options)
  "CREATE TABLE #{quote_schema_table(options[:temp] ? "##{name}" : name)}"
end
database_error_regexps() click to toggle source
# File lib/sequel/adapters/shared/mssql.rb, line 368
def database_error_regexps
  DATABASE_ERROR_REGEXPS
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, line 375
def default_constraint_name(table, column_name)
  if server_version >= 9000000
    table_name = schema_and_table(table).compact.join('.')
    self[Sequel[:sys][:default_constraints]].
      where{{:parent_object_id => Sequel::SQL::Function.new(:object_id, table_name), col_name(:parent_object_id, :parent_column_id) => column_name.to_s}}.
      get(:name)
  end
end
drop_index_sql(table, op) click to toggle source

The SQL to drop an index for the table.

# File lib/sequel/adapters/shared/mssql.rb, line 385
def drop_index_sql(table, op)
  "DROP INDEX #{quote_identifier(op[:name] || default_index_name(table, op[:columns]))} ON #{quote_schema_table(table)}"
end
index_definition_sql(table_name, index) click to toggle source

support for clustered index type

# File lib/sequel/adapters/shared/mssql.rb, line 390
def index_definition_sql(table_name, index)
  index_name = index[:name] || default_index_name(table_name, index[:columns])
  raise Error, "Partial indexes are not supported for this database" if index[:where] && !supports_partial_indexes?
  if index[:type] == :full_text
    "CREATE FULLTEXT INDEX ON #{quote_schema_table(table_name)} #{literal(index[:columns])} KEY INDEX #{literal(index[:key_index])}"
  else
    "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]}"
  end
end
information_schema_tables(type, opts) click to toggle source

Backbone of the tables and views support.

# File lib/sequel/adapters/shared/mssql.rb, line 401
def information_schema_tables(type, opts)
  m = output_identifier_meth
  metadata_dataset.from(Sequel[:information_schema][:tables].as(:t)).
    select(:table_name).
    where(:table_type=>type, :table_schema=>(opts[:schema]||'dbo').to_s).
    map{|x| m.call(x[:table_name])}
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, line 415
def rename_table_sql(name, new_name)
  "sp_rename #{literal(quote_schema_table(name))}, #{quote_identifier(schema_and_table(new_name).pop)}"
end
rollback_savepoint_sql(depth) click to toggle source

SQL to rollback to a savepoint

# File lib/sequel/adapters/shared/mssql.rb, line 420
def rollback_savepoint_sql(depth)
  SQL_ROLLBACK_TO_SAVEPOINT % depth
end
rollback_transaction_sql() click to toggle source

SQL to ROLLBACK a transaction.

# File lib/sequel/adapters/shared/mssql.rb, line 425
def rollback_transaction_sql
  SQL_ROLLBACK
end
schema_column_type(db_type) click to toggle source

The closest MSSQL equivalent of a boolean datatype is the bit type.

Calls superclass method
# File lib/sequel/adapters/shared/mssql.rb, line 430
def schema_column_type(db_type)
  case db_type
  when /\A(?:bit)\z/io
    :boolean
  when /\A(?:(?:small)?money)\z/io
    :decimal
  when /\A(timestamp|rowversion)\z/io
    :blob
  else
    super
  end
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, line 446
def schema_parse_table(table_name, opts)
  m = output_identifier_meth(opts[:dataset])
  m2 = input_identifier_meth(opts[:dataset])
  tn = m2.call(table_name.to_s)
  table_id = get(Sequel.function(:object_id, tn))
  info_sch_sch = opts[:information_schema_schema]
  inf_sch_qual = lambda{|s| info_sch_sch ? Sequel.qualify(info_sch_sch, s) : Sequel[s]}
  sys_qual = lambda{|s| info_sch_sch ? Sequel.qualify(info_sch_sch, Sequel.qualify(Sequel.lit(''), s)) : Sequel[s]}

  identity_cols = metadata_dataset.from(Sequel.lit('[sys].[columns]')).
    where(:object_id=>table_id, :is_identity=>true).
    select_map(:name)

  pk_index_id = metadata_dataset.from(sys_qual.call(Sequel.lit('sysindexes'))).
    where(:id=>table_id, :indid=>1..254){{(status & 2048)=>2048}}.
    get(:indid)
  pk_cols = metadata_dataset.from(sys_qual.call(Sequel.lit('sysindexkeys')).as(:sik)).
    join(sys_qual.call(Sequel.lit('syscolumns')).as(:sc), :id=>:id, :colid=>:colid).
    where{{sik[:id]=>table_id, sik[:indid]=>pk_index_id}}.
    select_order_map{sc[:name]}

  ds = metadata_dataset.from(inf_sch_qual.call(Sequel[:information_schema][:tables]).as(:t)).
   join(inf_sch_qual.call(Sequel[:information_schema][:columns]).as(:c), :table_catalog=>:table_catalog,
        :table_schema => :table_schema, :table_name => :table_name).
   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)]}.
   where{{c[:table_name]=>tn}}

  if schema = opts[:schema]
    ds = ds.where{{c[:table_schema]=>schema}}
  end

  ds.map do |row|
    if row[:primary_key] = pk_cols.include?(row[:column])
      row[:auto_increment] = identity_cols.include?(row[:column])
    end
    row[:allow_null] = row[:allow_null] == 'YES' ? true : false
    row[:default] = nil if blank_object?(row[:default])
    row[:type] = if row[:db_type] =~ DECIMAL_TYPE_RE && row[:scale] == 0
      :integer
    else
      schema_column_type(row[:db_type])
    end
    row[:max_length] = row[:max_chars] if row[:type] == :string && row[:max_chars] >= 0
    [m.call(row.delete(:column)), row]
  end
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, line 494
def set_mssql_unicode_strings
  @mssql_unicode_strings = typecast_value_boolean(@opts.fetch(:mssql_unicode_strings, true))
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, line 500
def type_literal_generic_datetime(column)
  :datetime
end
type_literal_generic_file(column) click to toggle source

MSSQL uses varbinary(max) type for blobs

# File lib/sequel/adapters/shared/mssql.rb, line 516
def type_literal_generic_file(column)
  :'varbinary(max)'
end
type_literal_generic_time(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, line 506
def type_literal_generic_time(column)
  column[:only_time] ? :time : :datetime
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, line 511
def type_literal_generic_trueclass(column)
  :bit
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, line 521
def view_with_check_option_support
  true
end