Module | Sequel::MSSQL::DatabaseMethods |
In: |
lib/sequel/adapters/shared/mssql.rb
|
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 |
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. |
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 74 74: def call_mssql_sproc(name, opts=OPTS) 75: args = opts[:args] || [] 76: names = ['@RC AS RESULT', '@@ROWCOUNT AS NUMROWS'] 77: declarations = ['@RC int'] 78: values = [] 79: 80: if args.is_a?(Hash) 81: named_args = true 82: args = args.to_a 83: method = :each 84: else 85: method = :each_with_index 86: end 87: 88: args.send(method) do |v, i| 89: if named_args 90: k = v 91: v, type, select = i 92: raise Error, "must provide output parameter name when using output parameters with named arguments" if v == :output && !select 93: else 94: v, type, select = v 95: end 96: 97: if v == :output 98: type ||= "nvarchar(max)" 99: if named_args 100: varname = select 101: else 102: varname = "var#{i}" 103: select ||= varname 104: end 105: names << "@#{varname} AS #{quote_identifier(select)}" 106: declarations << "@#{varname} #{type}" 107: value = "@#{varname} OUTPUT" 108: else 109: value = literal(v) 110: end 111: 112: if named_args 113: value = "@#{k}=#{value}" 114: end 115: 116: values << value 117: end 118: 119: sql = "DECLARE #{declarations.join(', ')}; EXECUTE @RC = #{name} #{values.join(', ')}; SELECT #{names.join(', ')}" 120: 121: ds = dataset.with_sql(sql) 122: ds = ds.server(opts[:server]) if opts[:server] 123: ds.first 124: 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 138 138: def foreign_key_list(table, opts=OPTS) 139: m = output_identifier_meth 140: im = input_identifier_meth 141: schema, table = schema_and_table(table) 142: current_schema = m.call(get(Sequel.function('schema_name'))) 143: fk_action_map = FOREIGN_KEY_ACTION_MAP 144: ds = metadata_dataset.from(Sequel.lit('[sys].[foreign_keys]').as(:fk)). 145: join(Sequel.lit('[sys].[foreign_key_columns]').as(:fkc), :constraint_object_id => :object_id). 146: join(Sequel.lit('[sys].[all_columns]').as(:pc), :object_id => :fkc__parent_object_id, :column_id => :fkc__parent_column_id). 147: join(Sequel.lit('[sys].[all_columns]').as(:rc), :object_id => :fkc__referenced_object_id, :column_id => :fkc__referenced_column_id). 148: where{{object_schema_name(:fk__parent_object_id) => im.call(schema || current_schema)}}. 149: where{{object_name(:fk__parent_object_id) => im.call(table)}}. 150: select{[:fk__name, 151: :fk__delete_referential_action, 152: :fk__update_referential_action, 153: :pc__name___column, 154: :rc__name___referenced_column, 155: object_schema_name(:fk__referenced_object_id).as(:schema), 156: object_name(:fk__referenced_object_id).as(:table)]}. 157: order(:fk__name, :fkc__constraint_column_id) 158: h = {} 159: ds.each do |row| 160: if r = h[row[:name]] 161: r[:columns] << m.call(row[:column]) 162: r[:key] << m.call(row[:referenced_column]) 163: else 164: referenced_schema = m.call(row[:schema]) 165: referenced_table = m.call(row[:table]) 166: h[row[:name]] = { :name => m.call(row[:name]), 167: :table => (referenced_schema == current_schema) ? referenced_table : Sequel.qualify(referenced_schema, referenced_table), 168: :columns => [m.call(row[:column])], 169: :key => [m.call(row[:referenced_column])], 170: :on_update => fk_action_map[row[:update_referential_action]], 171: :on_delete => fk_action_map[row[:delete_referential_action]] } 172: end 173: end 174: h.values 175: end
Use the system tables to get index information
# File lib/sequel/adapters/shared/mssql.rb, line 178 178: def indexes(table, opts=OPTS) 179: m = output_identifier_meth 180: im = input_identifier_meth 181: indexes = {} 182: ds = metadata_dataset.from(Sequel.lit('[sys].[tables]').as(:t)). 183: join(Sequel.lit('[sys].[indexes]').as(:i), :object_id=>:object_id). 184: join(Sequel.lit('[sys].[index_columns]').as(:ic), :object_id=>:object_id, :index_id=>:index_id). 185: join(Sequel.lit('[sys].[columns]').as(:c), :object_id=>:object_id, :column_id=>:column_id). 186: select(:i__name, :i__is_unique, :c__name___column). 187: where{{t__name=>im.call(table)}}. 188: where(:i__is_primary_key=>0, :i__is_disabled=>0). 189: order(:i__name, :ic__index_column_id) 190: 191: if supports_partial_indexes? 192: ds = ds.where(:i__has_filter=>0) 193: end 194: 195: ds.each do |r| 196: index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)} 197: index[:columns] << m.call(r[:column]) 198: end 199: indexes 200: end
# File lib/sequel/adapters/shared/mssql.rb, line 38 38: def mssql_unicode_strings=(v) 39: @mssql_unicode_strings = v 40: reset_default_dataset 41: 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 204 204: def server_version(server=nil) 205: return @server_version if @server_version 206: if @opts[:server_version] 207: return @server_version = Integer(@opts[:server_version]) 208: end 209: @server_version = synchronize(server) do |conn| 210: (conn.server_version rescue nil) if conn.respond_to?(:server_version) 211: end 212: unless @server_version 213: m = SERVER_VERSION_RE.match(fetch(SERVER_VERSION_SQL).single_value.to_s) 214: @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i 215: end 216: @server_version 217: end