Module Sequel::MSSQL::DatabaseMethods
In: lib/sequel/adapters/shared/mssql.rb

Methods

Included Modules

Sequel::Database::SplitAlterTable

Constants

FOREIGN_KEY_ACTION_MAP = {0 => :no_action, 1 => :cascade, 2 => :set_null, 3 => :set_default}.freeze
DATABASE_ERROR_REGEXPS = { /Violation of UNIQUE KEY constraint|(Violation of PRIMARY KEY constraint.+)?Cannot insert duplicate key/ => UniqueConstraintViolation, /conflicted with the (FOREIGN KEY.*|REFERENCE) constraint/ => ForeignKeyConstraintViolation, /conflicted with the CHECK constraint/ => CheckConstraintViolation, /column does not allow nulls/ => NotNullConstraintViolation, /was deadlocked on lock resources with another process and has been chosen as the deadlock victim/ => SerializationFailure, }.freeze

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

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']
    })

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 54
 54:       def call_mssql_sproc(name, opts=OPTS)
 55:         args = opts[:args] || []
 56:         names = ['@RC AS RESULT', '@@ROWCOUNT AS NUMROWS']
 57:         declarations = ['@RC int']
 58:         values = []
 59: 
 60:         if args.is_a?(Hash)
 61:           named_args = true
 62:           args = args.to_a
 63:           method = :each
 64:         else
 65:           method = :each_with_index
 66:         end
 67: 
 68:         args.public_send(method) do |v, i|
 69:           if named_args
 70:             k = v
 71:             v, type, select = i
 72:             raise Error, "must provide output parameter name when using output parameters with named arguments" if v == :output && !select
 73:           else
 74:             v, type, select = v
 75:           end
 76: 
 77:           if v == :output
 78:             type ||= "nvarchar(max)"
 79:             if named_args
 80:               varname = select
 81:             else
 82:               varname = "var#{i}"
 83:               select ||= varname
 84:             end
 85:             names << "@#{varname} AS #{quote_identifier(select)}"
 86:             declarations << "@#{varname} #{type}"
 87:             value = "@#{varname} OUTPUT"
 88:           else
 89:             value = literal(v)
 90:           end
 91: 
 92:           if named_args
 93:             value = "@#{k}=#{value}"
 94:           end
 95: 
 96:           values << value
 97:         end
 98: 
 99:         sql = "DECLARE #{declarations.join(', ')}; EXECUTE @RC = #{name} #{values.join(', ')}; SELECT #{names.join(', ')}"
100: 
101:         ds = dataset.with_sql(sql)
102:         ds = ds.server(opts[:server]) if opts[:server]
103:         ds.first
104:       end

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 106
106:       def database_type
107:         :mssql
108:       end

Return foreign key information using the system views, including :name, :on_delete, and :on_update entries in the hashes.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 117
117:       def foreign_key_list(table, opts=OPTS)
118:         m = output_identifier_meth
119:         im = input_identifier_meth
120:         schema, table = schema_and_table(table)
121:         current_schema = m.call(get(Sequel.function('schema_name')))
122:         fk_action_map = FOREIGN_KEY_ACTION_MAP
123:         fk = Sequel[:fk]
124:         fkc = Sequel[:fkc]
125:         ds = metadata_dataset.from(Sequel.lit('[sys].[foreign_keys]').as(:fk)).
126:           join(Sequel.lit('[sys].[foreign_key_columns]').as(:fkc), :constraint_object_id => :object_id).
127:           join(Sequel.lit('[sys].[all_columns]').as(:pc), :object_id => fkc[:parent_object_id],     :column_id => fkc[:parent_column_id]).
128:           join(Sequel.lit('[sys].[all_columns]').as(:rc), :object_id => fkc[:referenced_object_id], :column_id => fkc[:referenced_column_id]).
129:           where{{object_schema_name(fk[:parent_object_id]) => im.call(schema || current_schema)}}.
130:           where{{object_name(fk[:parent_object_id]) => im.call(table)}}.
131:           select{[fk[:name], 
132:                   fk[:delete_referential_action], 
133:                   fk[:update_referential_action], 
134:                   pc[:name].as(:column), 
135:                   rc[:name].as(:referenced_column), 
136:                   object_schema_name(fk[:referenced_object_id]).as(:schema), 
137:                   object_name(fk[:referenced_object_id]).as(:table)]}.
138:           order(fk[:name], fkc[:constraint_column_id])
139:         h = {}
140:         ds.each do |row|
141:           if r = h[row[:name]]
142:             r[:columns] << m.call(row[:column])
143:             r[:key] << m.call(row[:referenced_column])
144:           else
145:             referenced_schema = m.call(row[:schema])
146:             referenced_table = m.call(row[:table])
147:             h[row[:name]] = { :name      => m.call(row[:name]), 
148:                               :table     => (referenced_schema == current_schema) ? referenced_table : Sequel.qualify(referenced_schema, referenced_table),
149:                               :columns   => [m.call(row[:column])], 
150:                               :key       => [m.call(row[:referenced_column])], 
151:                               :on_update => fk_action_map[row[:update_referential_action]], 
152:                               :on_delete => fk_action_map[row[:delete_referential_action]] }
153:           end
154:         end
155:         h.values
156:       end

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 158
158:       def freeze
159:         server_version
160:         super
161:       end

Microsoft SQL Server namespaces indexes per table.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 111
111:       def global_index_namespace?
112:         false
113:       end

Use the system tables to get index information

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 164
164:       def indexes(table, opts=OPTS)
165:         m = output_identifier_meth
166:         im = input_identifier_meth
167:         indexes = {}
168:         i = Sequel[:i]
169:         ds = metadata_dataset.from(Sequel.lit('[sys].[tables]').as(:t)).
170:          join(Sequel.lit('[sys].[indexes]').as(:i), :object_id=>:object_id).
171:          join(Sequel.lit('[sys].[index_columns]').as(:ic), :object_id=>:object_id, :index_id=>:index_id).
172:          join(Sequel.lit('[sys].[columns]').as(:c), :object_id=>:object_id, :column_id=>:column_id).
173:          select(i[:name], i[:is_unique], Sequel[:c][:name].as(:column)).
174:          where{{t[:name]=>im.call(table)}}.
175:          where(i[:is_primary_key]=>0, i[:is_disabled]=>0).
176:          order(i[:name], Sequel[:ic][:index_column_id])
177: 
178:         if supports_partial_indexes?
179:           ds = ds.where(i[:has_filter]=>0)
180:         end
181: 
182:         ds.each do |r|
183:           index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)}
184:           index[:columns] << m.call(r[:column])
185:         end
186:         indexes
187:       end

The version of the MSSQL server, as an integer (e.g. 10001600 for SQL Server 2008 Express).

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 191
191:       def server_version(server=nil)
192:         return @server_version if @server_version
193:         if @opts[:server_version]
194:           return @server_version = Integer(@opts[:server_version])
195:         end
196:         @server_version = synchronize(server) do |conn|
197:           (conn.server_version rescue nil) if conn.respond_to?(:server_version)
198:         end
199:         unless @server_version
200:           m = /^(\d+)\.(\d+)\.(\d+)/.match(fetch("SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)").single_value.to_s)
201:           @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i
202:         end
203:         @server_version
204:       end

MSSQL 2008+ supports partial indexes.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 207
207:       def supports_partial_indexes?
208:         dataset.send(:is_2008_or_later?)
209:       end

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

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 212
212:       def supports_savepoints?
213:         true
214:       end

MSSQL supports transaction isolation levels

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 217
217:       def supports_transaction_isolation_levels?
218:         true
219:       end

MSSQL supports transaction DDL statements.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 222
222:       def supports_transactional_ddl?
223:         true
224:       end

Microsoft SQL Server supports using the INFORMATION_SCHEMA to get information on tables.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 228
228:       def tables(opts=OPTS)
229:         information_schema_tables('BASE TABLE', opts)
230:       end

Microsoft SQL Server supports using the INFORMATION_SCHEMA to get information on views.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 234
234:       def views(opts=OPTS)
235:         information_schema_tables('VIEW', opts)
236:       end

[Validate]