Methods
Public Instance
Included modules
Constants
| 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, /Lock request time out period exceeded\./ => DatabaseLockTimeout, }.freeze | ||
| FOREIGN_KEY_ACTION_MAP | = | {0 => :no_action, 1 => :cascade, 2 => :set_null, 3 => :set_default}.freeze |
Attributes
| like_without_collate | [RW] |
Whether to use LIKE without COLLATE Latin1_General_CS_AS. Skipping the COLLATE can significantly increase performance in some cases. |
| 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 |
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 name. 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 |
Because Sequel datasets only support a single result set per query, and retrieving the result code and number of rows requires a query, this does not support stored procedures which also return result sets. To handle such stored procedures, you should drop down to the connection/driver level by using Sequel::Database#synchronize to get access to the underlying connection object.
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 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.public_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
# File lib/sequel/adapters/shared/mssql.rb 116 def database_type 117 :mssql 118 end
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 127 def foreign_key_list(table, opts=OPTS) 128 m = output_identifier_meth 129 im = input_identifier_meth 130 schema, table = schema_and_table(table) 131 current_schema = m.call(get(Sequel.function('schema_name'))) 132 fk_action_map = FOREIGN_KEY_ACTION_MAP 133 fk = Sequel[:fk] 134 fkc = Sequel[:fkc] 135 ds = metadata_dataset.from(Sequel.lit('[sys].[foreign_keys]').as(:fk)). 136 join(Sequel.lit('[sys].[foreign_key_columns]').as(:fkc), :constraint_object_id => :object_id). 137 join(Sequel.lit('[sys].[all_columns]').as(:pc), :object_id => fkc[:parent_object_id], :column_id => fkc[:parent_column_id]). 138 join(Sequel.lit('[sys].[all_columns]').as(:rc), :object_id => fkc[:referenced_object_id], :column_id => fkc[:referenced_column_id]). 139 where{{object_schema_name(fk[:parent_object_id]) => im.call(schema || current_schema)}}. 140 where{{object_name(fk[:parent_object_id]) => im.call(table)}}. 141 select{[fk[:name], 142 fk[:delete_referential_action], 143 fk[:update_referential_action], 144 pc[:name].as(:column), 145 rc[:name].as(:referenced_column), 146 object_schema_name(fk[:referenced_object_id]).as(:schema), 147 object_name(fk[:referenced_object_id]).as(:table)]}. 148 order(fk[:name], fkc[:constraint_column_id]) 149 h = {} 150 ds.each do |row| 151 if r = h[row[:name]] 152 r[:columns] << m.call(row[:column]) 153 r[:key] << m.call(row[:referenced_column]) 154 else 155 referenced_schema = m.call(row[:schema]) 156 referenced_table = m.call(row[:table]) 157 h[row[:name]] = { :name => m.call(row[:name]), 158 :table => (referenced_schema == current_schema) ? referenced_table : Sequel.qualify(referenced_schema, referenced_table), 159 :columns => [m.call(row[:column])], 160 :key => [m.call(row[:referenced_column])], 161 :on_update => fk_action_map[row[:update_referential_action]], 162 :on_delete => fk_action_map[row[:delete_referential_action]] } 163 end 164 end 165 h.values 166 end
# File lib/sequel/adapters/shared/mssql.rb 168 def freeze 169 server_version 170 super 171 end
Microsoft SQL Server namespaces indexes per table.
# File lib/sequel/adapters/shared/mssql.rb 121 def global_index_namespace? 122 false 123 end
Use the system tables to get index information
# File lib/sequel/adapters/shared/mssql.rb 174 def indexes(table, opts=OPTS) 175 m = output_identifier_meth 176 im = input_identifier_meth 177 indexes = {} 178 table = table.value if table.is_a?(Sequel::SQL::Identifier) 179 i = Sequel[:i] 180 ds = metadata_dataset.from(Sequel.lit('[sys].[tables]').as(:t)). 181 join(Sequel.lit('[sys].[indexes]').as(:i), :object_id=>:object_id). 182 join(Sequel.lit('[sys].[index_columns]').as(:ic), :object_id=>:object_id, :index_id=>:index_id). 183 join(Sequel.lit('[sys].[columns]').as(:c), :object_id=>:object_id, :column_id=>:column_id). 184 select(i[:name], i[:is_unique], Sequel[:c][:name].as(:column)). 185 where{{t[:name]=>im.call(table)}}. 186 where(i[:is_primary_key]=>0, i[:is_disabled]=>0). 187 order(i[:name], Sequel[:ic][:index_column_id]) 188 189 if supports_partial_indexes? 190 ds = ds.where(i[:has_filter]=>0) 191 end 192 193 ds.each do |r| 194 index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)} 195 index[:columns] << m.call(r[:column]) 196 end 197 indexes 198 end
The version of the MSSQL server, as an integer (e.g. 10001600 for SQL Server 2008 Express).
# File lib/sequel/adapters/shared/mssql.rb 202 def server_version(server=nil) 203 return @server_version if @server_version 204 if @opts[:server_version] 205 return @server_version = Integer(@opts[:server_version]) 206 end 207 @server_version = synchronize(server) do |conn| 208 (conn.server_version rescue nil) if conn.respond_to?(:server_version) 209 end 210 unless @server_version 211 m = /^(\d+)\.(\d+)\.(\d+)/.match(fetch("SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)").single_value.to_s) 212 @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i 213 end 214 @server_version 215 end
MSSQL 2008+ supports partial indexes.
# File lib/sequel/adapters/shared/mssql.rb 218 def supports_partial_indexes? 219 dataset.send(:is_2008_or_later?) 220 end
MSSQL supports savepoints, though it doesn’t support releasing them
# File lib/sequel/adapters/shared/mssql.rb 223 def supports_savepoints? 224 true 225 end
MSSQL supports transaction isolation levels
# File lib/sequel/adapters/shared/mssql.rb 228 def supports_transaction_isolation_levels? 229 true 230 end
MSSQL supports transaction DDL statements.
# File lib/sequel/adapters/shared/mssql.rb 233 def supports_transactional_ddl? 234 true 235 end
Microsoft SQL Server supports using the INFORMATION_SCHEMA to get information on tables.
# File lib/sequel/adapters/shared/mssql.rb 239 def tables(opts=OPTS) 240 information_schema_tables('BASE TABLE', opts) 241 end
Microsoft SQL Server supports using the INFORMATION_SCHEMA to get information on views.
# File lib/sequel/adapters/shared/mssql.rb 245 def views(opts=OPTS) 246 information_schema_tables('VIEW', opts) 247 end
Attempt to acquire an exclusive advisory lock with the given lock_id (which will be converted to a string). If successful, yield to the block, then release the advisory lock when the block exits. If unsuccessful, raise a Sequel::AdvisoryLockError.
Options:
| :wait |
Do not raise an error, instead, wait until the advisory lock can be acquired. |
# File lib/sequel/adapters/shared/mssql.rb 255 def with_advisory_lock(lock_id, opts=OPTS) 256 lock_id = lock_id.to_s 257 timeout = opts[:wait] ? -1 : 0 258 server = opts[:server] 259 260 synchronize(server) do 261 begin 262 res = call_mssql_sproc(:sp_getapplock, :server=>server, :args=>{'Resource'=>lock_id, 'LockTimeout'=>timeout, 'LockMode'=>'Exclusive', 'LockOwner'=>'Session'}) 263 264 unless locked = res[:result] >= 0 265 raise AdvisoryLockError, "unable to acquire advisory lock #{lock_id.inspect}" 266 end 267 268 yield 269 ensure 270 if locked 271 call_mssql_sproc(:sp_releaseapplock, :server=>server, :args=>{'Resource'=>lock_id, 'LockOwner'=>'Session'}) 272 end 273 end 274 end 275 end