module Sequel::MSSQL::DatabaseMethods
Constants
- DATABASE_ERROR_REGEXPS
- FOREIGN_KEY_ACTION_MAP
Attributes
Whether to use LIKE without COLLATE Latin1_General_CS_AS. Skipping the COLLATE can significantly increase performance in some cases.
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 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