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/ => 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

Microsoft SQL Server uses the :mssql type.

[Source]

    # File lib/sequel/adapters/shared/mssql.rb, line 38
38:       def database_type
39:         :mssql
40:       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 49
49:       def foreign_key_list(table, opts=OPTS)
50:         m = output_identifier_meth
51:         im = input_identifier_meth
52:         schema, table = schema_and_table(table)
53:         current_schema = m.call(get(Sequel.function('schema_name')))
54:         fk_action_map = FOREIGN_KEY_ACTION_MAP
55:         ds = metadata_dataset.from(:sys__foreign_keys___fk).
56:           join(:sys__foreign_key_columns___fkc, :constraint_object_id => :object_id).
57:           join(:sys__all_columns___pc, :object_id => :fkc__parent_object_id,     :column_id => :fkc__parent_column_id).
58:           join(:sys__all_columns___rc, :object_id => :fkc__referenced_object_id, :column_id => :fkc__referenced_column_id).
59:           where{{object_schema_name(:fk__parent_object_id) => im.call(schema || current_schema)}}.
60:           where{{object_name(:fk__parent_object_id) => im.call(table)}}.
61:           select{[:fk__name, 
62:                   :fk__delete_referential_action, 
63:                   :fk__update_referential_action, 
64:                   :pc__name___column, 
65:                   :rc__name___referenced_column, 
66:                   object_schema_name(:fk__referenced_object_id).as(:schema), 
67:                   object_name(:fk__referenced_object_id).as(:table)]}.
68:           order(:name, :fkc__constraint_column_id)
69:         h = {}
70:         ds.each do |row|
71:           if r = h[row[:name]]
72:             r[:columns] << m.call(row[:column])
73:             r[:key] << m.call(row[:referenced_column])
74:           else
75:             referenced_schema = m.call(row[:schema])
76:             referenced_table = m.call(row[:table])
77:             h[row[:name]] = { :name      => m.call(row[:name]), 
78:                               :table     => (referenced_schema == current_schema) ? referenced_table : "#{referenced_schema}__#{referenced_table}""#{referenced_schema}__#{referenced_table}",
79:                               :columns   => [m.call(row[:column])], 
80:                               :key       => [m.call(row[:referenced_column])], 
81:                               :on_update => fk_action_map[row[:update_referential_action]], 
82:                               :on_delete => fk_action_map[row[:delete_referential_action]] }
83:           end
84:         end
85:         h.values
86:       end

Microsoft SQL Server namespaces indexes per table.

[Source]

    # File lib/sequel/adapters/shared/mssql.rb, line 43
43:       def global_index_namespace?
44:         false
45:       end

Use the system tables to get index information

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 89
 89:       def indexes(table, opts=OPTS)
 90:         m = output_identifier_meth
 91:         im = input_identifier_meth
 92:         indexes = {}
 93:         metadata_dataset.from(:sys__tables___t).
 94:          join(:sys__indexes___i, :object_id=>:object_id).
 95:          join(:sys__index_columns___ic, :object_id=>:object_id, :index_id=>:index_id).
 96:          join(:sys__columns___c, :object_id=>:object_id, :column_id=>:column_id).
 97:          select(:i__name, :i__is_unique, :c__name___column).
 98:          where{{t__name=>im.call(table)}}.
 99:          where(:i__is_primary_key=>0, :i__is_disabled=>0).
100:          order(:i__name, :ic__index_column_id).
101:          each do |r|
102:           index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)}
103:           index[:columns] << m.call(r[:column])
104:         end
105:         indexes
106:       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 110
110:       def server_version(server=nil)
111:         return @server_version if @server_version
112:         @server_version = synchronize(server) do |conn|
113:           (conn.server_version rescue nil) if conn.respond_to?(:server_version)
114:         end
115:         unless @server_version
116:           m = SERVER_VERSION_RE.match(fetch(SERVER_VERSION_SQL).single_value.to_s)
117:           @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i
118:         end
119:         @server_version
120:       end

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

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 123
123:       def supports_savepoints?
124:         true
125:       end

MSSQL supports transaction isolation levels

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 128
128:       def supports_transaction_isolation_levels?
129:         true
130:       end

MSSQL supports transaction DDL statements.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 133
133:       def supports_transactional_ddl?
134:         true
135:       end

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

[Source]

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

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

[Source]

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

[Validate]