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

Methods

Included Modules

Sequel::Database::SplitAlterTable

Constants

AUTO_INCREMENT = 'IDENTITY(1,1)'.freeze
SERVER_VERSION_RE = /^(\d+)\.(\d+)\.(\d+)/.freeze
SERVER_VERSION_SQL = "SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)".freeze
SQL_BEGIN = "BEGIN TRANSACTION".freeze
SQL_COMMIT = "COMMIT TRANSACTION".freeze
SQL_ROLLBACK = "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION".freeze
SQL_ROLLBACK_TO_SAVEPOINT = 'IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION autopoint_%d'.freeze
SQL_SAVEPOINT = 'SAVE TRANSACTION autopoint_%d'.freeze
MSSQL_DEFAULT_RE = /\A(?:\(N?('.*')\)|\(\((-?\d+(?:\.\d+)?)\)\))\z/
FOREIGN_KEY_ACTION_MAP = {0 => :no_action, 1 => :cascade, 2 => :set_null, 3 => :set_default}.freeze
DECIMAL_TYPE_RE = /number|numeric|decimal/io   The types to check for 0 scale to transform :decimal types to :integer.
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  [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

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 74
 74:       def call_mssql_sproc(name, opts=OPTS)
 75:         args = opts[:args] || []
 76:         names = ['@RC AS RESULT', '@@ROWCOUNT AS NUMROWS']
 77:         declarations = ['@RC int']
 78:         values = []
 79: 
 80:         if args.is_a?(Hash)
 81:           named_args = true
 82:           args = args.to_a
 83:           method = :each
 84:         else
 85:           method = :each_with_index
 86:         end
 87: 
 88:         args.send(method) do |v, i|
 89:           if named_args
 90:             k = v
 91:             v, type, select = i
 92:             raise Error, "must provide output parameter name when using output parameters with named arguments" if v == :output && !select
 93:           else
 94:             v, type, select = v
 95:           end
 96: 
 97:           if v == :output
 98:             type ||= "nvarchar(max)"
 99:             if named_args
100:               varname = select
101:             else
102:               varname = "var#{i}"
103:               select ||= varname
104:             end
105:             names << "@#{varname} AS #{quote_identifier(select)}"
106:             declarations << "@#{varname} #{type}"
107:             value = "@#{varname} OUTPUT"
108:           else
109:             value = literal(v)
110:           end
111: 
112:           if named_args
113:             value = "@#{k}=#{value}"
114:           end
115: 
116:           values << value
117:         end
118: 
119:         sql = "DECLARE #{declarations.join(', ')}; EXECUTE @RC = #{name} #{values.join(', ')}; SELECT #{names.join(', ')}"
120: 
121:         ds = dataset.with_sql(sql)
122:         ds = ds.server(opts[:server]) if opts[:server]
123:         ds.first
124:       end

Microsoft SQL Server uses the :mssql type.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 127
127:       def database_type
128:         :mssql
129:       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 138
138:       def foreign_key_list(table, opts=OPTS)
139:         m = output_identifier_meth
140:         im = input_identifier_meth
141:         schema, table = schema_and_table(table)
142:         current_schema = m.call(get(Sequel.function('schema_name')))
143:         fk_action_map = FOREIGN_KEY_ACTION_MAP
144:         ds = metadata_dataset.from(Sequel.lit('[sys].[foreign_keys]').as(:fk)).
145:           join(Sequel.lit('[sys].[foreign_key_columns]').as(:fkc), :constraint_object_id => :object_id).
146:           join(Sequel.lit('[sys].[all_columns]').as(:pc), :object_id => :fkc__parent_object_id,     :column_id => :fkc__parent_column_id).
147:           join(Sequel.lit('[sys].[all_columns]').as(:rc), :object_id => :fkc__referenced_object_id, :column_id => :fkc__referenced_column_id).
148:           where{{object_schema_name(:fk__parent_object_id) => im.call(schema || current_schema)}}.
149:           where{{object_name(:fk__parent_object_id) => im.call(table)}}.
150:           select{[:fk__name, 
151:                   :fk__delete_referential_action, 
152:                   :fk__update_referential_action, 
153:                   :pc__name___column, 
154:                   :rc__name___referenced_column, 
155:                   object_schema_name(:fk__referenced_object_id).as(:schema), 
156:                   object_name(:fk__referenced_object_id).as(:table)]}.
157:           order(:fk__name, :fkc__constraint_column_id)
158:         h = {}
159:         ds.each do |row|
160:           if r = h[row[:name]]
161:             r[:columns] << m.call(row[:column])
162:             r[:key] << m.call(row[:referenced_column])
163:           else
164:             referenced_schema = m.call(row[:schema])
165:             referenced_table = m.call(row[:table])
166:             h[row[:name]] = { :name      => m.call(row[:name]), 
167:                               :table     => (referenced_schema == current_schema) ? referenced_table : Sequel.qualify(referenced_schema, referenced_table),
168:                               :columns   => [m.call(row[:column])], 
169:                               :key       => [m.call(row[:referenced_column])], 
170:                               :on_update => fk_action_map[row[:update_referential_action]], 
171:                               :on_delete => fk_action_map[row[:delete_referential_action]] }
172:           end
173:         end
174:         h.values
175:       end

Microsoft SQL Server namespaces indexes per table.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 132
132:       def global_index_namespace?
133:         false
134:       end

Use the system tables to get index information

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 178
178:       def indexes(table, opts=OPTS)
179:         m = output_identifier_meth
180:         im = input_identifier_meth
181:         indexes = {}
182:         ds = metadata_dataset.from(Sequel.lit('[sys].[tables]').as(:t)).
183:          join(Sequel.lit('[sys].[indexes]').as(:i), :object_id=>:object_id).
184:          join(Sequel.lit('[sys].[index_columns]').as(:ic), :object_id=>:object_id, :index_id=>:index_id).
185:          join(Sequel.lit('[sys].[columns]').as(:c), :object_id=>:object_id, :column_id=>:column_id).
186:          select(:i__name, :i__is_unique, :c__name___column).
187:          where{{t__name=>im.call(table)}}.
188:          where(:i__is_primary_key=>0, :i__is_disabled=>0).
189:          order(:i__name, :ic__index_column_id)
190: 
191:         if supports_partial_indexes?
192:           ds = ds.where(:i__has_filter=>0)
193:         end
194: 
195:         ds.each do |r|
196:           index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)}
197:           index[:columns] << m.call(r[:column])
198:         end
199:         indexes
200:       end

[Source]

    # File lib/sequel/adapters/shared/mssql.rb, line 38
38:       def mssql_unicode_strings=(v)
39:         @mssql_unicode_strings = v
40:         reset_default_dataset
41:       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 204
204:       def server_version(server=nil)
205:         return @server_version if @server_version
206:         if @opts[:server_version]
207:           return @server_version = Integer(@opts[:server_version])
208:         end
209:         @server_version = synchronize(server) do |conn|
210:           (conn.server_version rescue nil) if conn.respond_to?(:server_version)
211:         end
212:         unless @server_version
213:           m = SERVER_VERSION_RE.match(fetch(SERVER_VERSION_SQL).single_value.to_s)
214:           @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i
215:         end
216:         @server_version
217:       end

MSSQL 2008+ supports partial indexes.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 220
220:       def supports_partial_indexes?
221:         dataset.send(:is_2008_or_later?)
222:       end

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

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 225
225:       def supports_savepoints?
226:         true
227:       end

MSSQL supports transaction isolation levels

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 230
230:       def supports_transaction_isolation_levels?
231:         true
232:       end

MSSQL supports transaction DDL statements.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 235
235:       def supports_transactional_ddl?
236:         true
237:       end

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

[Source]

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

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

[Source]

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

[Validate]