Files

Class/Module Index [+]

Quicksearch

Sequel::MSSQL::DatabaseMethods

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 argumetns, 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 64
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 117
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 128
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
  ds = metadata_dataset.from(:sys__foreign_keys___fk).
    join(:sys__foreign_key_columns___fkc, :constraint_object_id => :object_id).
    join(:sys__all_columns___pc, :object_id => :fkc__parent_object_id,     :column_id => :fkc__parent_column_id).
    join(:sys__all_columns___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___column, 
            :rc__name___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
global_index_namespace?() click to toggle source

Microsoft SQL Server namespaces indexes per table.

# File lib/sequel/adapters/shared/mssql.rb, line 122
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 168
def indexes(table, opts=OPTS)
  m = output_identifier_meth
  im = input_identifier_meth
  indexes = {}
  ds = metadata_dataset.from(:sys__tables___t).
   join(:sys__indexes___i, :object_id=>:object_id).
   join(:sys__index_columns___ic, :object_id=>:object_id, :index_id=>:index_id).
   join(:sys__columns___c, :object_id=>:object_id, :column_id=>:column_id).
   select(:i__name, :i__is_unique, :c__name___column).
   where{{t__name=>im.call(table)}}.
   where(:i__is_primary_key=>0, :i__is_disabled=>0).
   order(:i__name, :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 28
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 194
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 210
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 215
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 220
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 225
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 231
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 237
def views(opts=OPTS)
  information_schema_tables('VIEW', opts)
end

[Validate]

Generated with the Darkfish Rdoc Generator 2.