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

[Source]

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

Microsoft SQL Server uses the :mssql type.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 117
117:       def database_type
118:         :mssql
119:       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 128
128:       def foreign_key_list(table, opts=OPTS)
129:         m = output_identifier_meth
130:         im = input_identifier_meth
131:         schema, table = schema_and_table(table)
132:         current_schema = m.call(get(Sequel.function('schema_name')))
133:         fk_action_map = FOREIGN_KEY_ACTION_MAP
134:         ds = metadata_dataset.from(:sys__foreign_keys___fk).
135:           join(:sys__foreign_key_columns___fkc, :constraint_object_id => :object_id).
136:           join(:sys__all_columns___pc, :object_id => :fkc__parent_object_id,     :column_id => :fkc__parent_column_id).
137:           join(:sys__all_columns___rc, :object_id => :fkc__referenced_object_id, :column_id => :fkc__referenced_column_id).
138:           where{{object_schema_name(:fk__parent_object_id) => im.call(schema || current_schema)}}.
139:           where{{object_name(:fk__parent_object_id) => im.call(table)}}.
140:           select{[:fk__name, 
141:                   :fk__delete_referential_action, 
142:                   :fk__update_referential_action, 
143:                   :pc__name___column, 
144:                   :rc__name___referenced_column, 
145:                   object_schema_name(:fk__referenced_object_id).as(:schema), 
146:                   object_name(:fk__referenced_object_id).as(:table)]}.
147:           order(:fk__name, :fkc__constraint_column_id)
148:         h = {}
149:         ds.each do |row|
150:           if r = h[row[:name]]
151:             r[:columns] << m.call(row[:column])
152:             r[:key] << m.call(row[:referenced_column])
153:           else
154:             referenced_schema = m.call(row[:schema])
155:             referenced_table = m.call(row[:table])
156:             h[row[:name]] = { :name      => m.call(row[:name]), 
157:                               :table     => (referenced_schema == current_schema) ? referenced_table : Sequel.qualify(referenced_schema, referenced_table),
158:                               :columns   => [m.call(row[:column])], 
159:                               :key       => [m.call(row[:referenced_column])], 
160:                               :on_update => fk_action_map[row[:update_referential_action]], 
161:                               :on_delete => fk_action_map[row[:delete_referential_action]] }
162:           end
163:         end
164:         h.values
165:       end

Microsoft SQL Server namespaces indexes per table.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 122
122:       def global_index_namespace?
123:         false
124:       end

Use the system tables to get index information

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 168
168:       def indexes(table, opts=OPTS)
169:         m = output_identifier_meth
170:         im = input_identifier_meth
171:         indexes = {}
172:         ds = metadata_dataset.from(:sys__tables___t).
173:          join(:sys__indexes___i, :object_id=>:object_id).
174:          join(:sys__index_columns___ic, :object_id=>:object_id, :index_id=>:index_id).
175:          join(:sys__columns___c, :object_id=>:object_id, :column_id=>:column_id).
176:          select(:i__name, :i__is_unique, :c__name___column).
177:          where{{t__name=>im.call(table)}}.
178:          where(:i__is_primary_key=>0, :i__is_disabled=>0).
179:          order(:i__name, :ic__index_column_id)
180: 
181:         if supports_partial_indexes?
182:           ds = ds.where(:i__has_filter=>0)
183:         end
184: 
185:         ds.each do |r|
186:           index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)}
187:           index[:columns] << m.call(r[:column])
188:         end
189:         indexes
190:       end

[Source]

    # File lib/sequel/adapters/shared/mssql.rb, line 28
28:       def mssql_unicode_strings=(v)
29:         @mssql_unicode_strings = v
30:         reset_default_dataset
31:       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 194
194:       def server_version(server=nil)
195:         return @server_version if @server_version
196:         if @opts[:server_version]
197:           return @server_version = Integer(@opts[:server_version])
198:         end
199:         @server_version = synchronize(server) do |conn|
200:           (conn.server_version rescue nil) if conn.respond_to?(:server_version)
201:         end
202:         unless @server_version
203:           m = SERVER_VERSION_RE.match(fetch(SERVER_VERSION_SQL).single_value.to_s)
204:           @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i
205:         end
206:         @server_version
207:       end

MSSQL 2008+ supports partial indexes.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 210
210:       def supports_partial_indexes?
211:         dataset.send(:is_2008_or_later?)
212:       end

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

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 215
215:       def supports_savepoints?
216:         true
217:       end

MSSQL supports transaction isolation levels

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 220
220:       def supports_transaction_isolation_levels?
221:         true
222:       end

MSSQL supports transaction DDL statements.

[Source]

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

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

[Source]

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

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

[Source]

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

[Validate]