Module | Sequel::MSSQL::DatabaseMethods |
In: |
lib/sequel/adapters/shared/mssql.rb
|
FOREIGN_KEY_ACTION_MAP | = | {0 => :no_action, 1 => :cascade, 2 => :set_null, 3 => :set_default}.freeze |
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 |
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 Database object. |
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'] })
# File lib/sequel/adapters/shared/mssql.rb, line 54 54: def call_mssql_sproc(name, opts=OPTS) 55: args = opts[:args] || [] 56: names = ['@RC AS RESULT', '@@ROWCOUNT AS NUMROWS'] 57: declarations = ['@RC int'] 58: values = [] 59: 60: if args.is_a?(Hash) 61: named_args = true 62: args = args.to_a 63: method = :each 64: else 65: method = :each_with_index 66: end 67: 68: args.public_send(method) do |v, i| 69: if named_args 70: k = v 71: v, type, select = i 72: raise Error, "must provide output parameter name when using output parameters with named arguments" if v == :output && !select 73: else 74: v, type, select = v 75: end 76: 77: if v == :output 78: type ||= "nvarchar(max)" 79: if named_args 80: varname = select 81: else 82: varname = "var#{i}" 83: select ||= varname 84: end 85: names << "@#{varname} AS #{quote_identifier(select)}" 86: declarations << "@#{varname} #{type}" 87: value = "@#{varname} OUTPUT" 88: else 89: value = literal(v) 90: end 91: 92: if named_args 93: value = "@#{k}=#{value}" 94: end 95: 96: values << value 97: end 98: 99: sql = "DECLARE #{declarations.join(', ')}; EXECUTE @RC = #{name} #{values.join(', ')}; SELECT #{names.join(', ')}" 100: 101: ds = dataset.with_sql(sql) 102: ds = ds.server(opts[:server]) if opts[:server] 103: ds.first 104: 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, line 117 117: def foreign_key_list(table, opts=OPTS) 118: m = output_identifier_meth 119: im = input_identifier_meth 120: schema, table = schema_and_table(table) 121: current_schema = m.call(get(Sequel.function('schema_name'))) 122: fk_action_map = FOREIGN_KEY_ACTION_MAP 123: fk = Sequel[:fk] 124: fkc = Sequel[:fkc] 125: ds = metadata_dataset.from(Sequel.lit('[sys].[foreign_keys]').as(:fk)). 126: join(Sequel.lit('[sys].[foreign_key_columns]').as(:fkc), :constraint_object_id => :object_id). 127: join(Sequel.lit('[sys].[all_columns]').as(:pc), :object_id => fkc[:parent_object_id], :column_id => fkc[:parent_column_id]). 128: join(Sequel.lit('[sys].[all_columns]').as(:rc), :object_id => fkc[:referenced_object_id], :column_id => fkc[:referenced_column_id]). 129: where{{object_schema_name(fk[:parent_object_id]) => im.call(schema || current_schema)}}. 130: where{{object_name(fk[:parent_object_id]) => im.call(table)}}. 131: select{[fk[:name], 132: fk[:delete_referential_action], 133: fk[:update_referential_action], 134: pc[:name].as(:column), 135: rc[:name].as(:referenced_column), 136: object_schema_name(fk[:referenced_object_id]).as(:schema), 137: object_name(fk[:referenced_object_id]).as(:table)]}. 138: order(fk[:name], fkc[:constraint_column_id]) 139: h = {} 140: ds.each do |row| 141: if r = h[row[:name]] 142: r[:columns] << m.call(row[:column]) 143: r[:key] << m.call(row[:referenced_column]) 144: else 145: referenced_schema = m.call(row[:schema]) 146: referenced_table = m.call(row[:table]) 147: h[row[:name]] = { :name => m.call(row[:name]), 148: :table => (referenced_schema == current_schema) ? referenced_table : Sequel.qualify(referenced_schema, referenced_table), 149: :columns => [m.call(row[:column])], 150: :key => [m.call(row[:referenced_column])], 151: :on_update => fk_action_map[row[:update_referential_action]], 152: :on_delete => fk_action_map[row[:delete_referential_action]] } 153: end 154: end 155: h.values 156: end
# File lib/sequel/adapters/shared/mssql.rb, line 158 158: def freeze 159: server_version 160: super 161: end
Use the system tables to get index information
# File lib/sequel/adapters/shared/mssql.rb, line 164 164: def indexes(table, opts=OPTS) 165: m = output_identifier_meth 166: im = input_identifier_meth 167: indexes = {} 168: i = Sequel[:i] 169: ds = metadata_dataset.from(Sequel.lit('[sys].[tables]').as(:t)). 170: join(Sequel.lit('[sys].[indexes]').as(:i), :object_id=>:object_id). 171: join(Sequel.lit('[sys].[index_columns]').as(:ic), :object_id=>:object_id, :index_id=>:index_id). 172: join(Sequel.lit('[sys].[columns]').as(:c), :object_id=>:object_id, :column_id=>:column_id). 173: select(i[:name], i[:is_unique], Sequel[:c][:name].as(:column)). 174: where{{t[:name]=>im.call(table)}}. 175: where(i[:is_primary_key]=>0, i[:is_disabled]=>0). 176: order(i[:name], Sequel[:ic][:index_column_id]) 177: 178: if supports_partial_indexes? 179: ds = ds.where(i[:has_filter]=>0) 180: end 181: 182: ds.each do |r| 183: index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)} 184: index[:columns] << m.call(r[:column]) 185: end 186: indexes 187: 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, line 191 191: def server_version(server=nil) 192: return @server_version if @server_version 193: if @opts[:server_version] 194: return @server_version = Integer(@opts[:server_version]) 195: end 196: @server_version = synchronize(server) do |conn| 197: (conn.server_version rescue nil) if conn.respond_to?(:server_version) 198: end 199: unless @server_version 200: m = /^(\d+)\.(\d+)\.(\d+)/.match(fetch("SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)").single_value.to_s) 201: @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i 202: end 203: @server_version 204: end