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 argumetns, 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 64 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.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
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 128 128: def foreign_key_list(table, opts=OPTS) 129: m = output_identifier_meth 130: im = input_identifier_meth 131: schema, table = schema_and_table(table) 132: current_schema = m.call(get(Sequel.function('schema_name'))) 133: fk_action_map = FOREIGN_KEY_ACTION_MAP 134: ds = metadata_dataset.from(:sys__foreign_keys___fk). 135: join(:sys__foreign_key_columns___fkc, :constraint_object_id => :object_id). 136: join(:sys__all_columns___pc, :object_id => :fkc__parent_object_id, :column_id => :fkc__parent_column_id). 137: join(:sys__all_columns___rc, :object_id => :fkc__referenced_object_id, :column_id => :fkc__referenced_column_id). 138: where{{object_schema_name(:fk__parent_object_id) => im.call(schema || current_schema)}}. 139: where{{object_name(:fk__parent_object_id) => im.call(table)}}. 140: select{[:fk__name, 141: :fk__delete_referential_action, 142: :fk__update_referential_action, 143: :pc__name___column, 144: :rc__name___referenced_column, 145: object_schema_name(:fk__referenced_object_id).as(:schema), 146: object_name(:fk__referenced_object_id).as(:table)]}. 147: order(:fk__name, :fkc__constraint_column_id) 148: h = {} 149: ds.each do |row| 150: if r = h[row[:name]] 151: r[:columns] << m.call(row[:column]) 152: r[:key] << m.call(row[:referenced_column]) 153: else 154: referenced_schema = m.call(row[:schema]) 155: referenced_table = m.call(row[:table]) 156: h[row[:name]] = { :name => m.call(row[:name]), 157: :table => (referenced_schema == current_schema) ? referenced_table : Sequel.qualify(referenced_schema, referenced_table), 158: :columns => [m.call(row[:column])], 159: :key => [m.call(row[:referenced_column])], 160: :on_update => fk_action_map[row[:update_referential_action]], 161: :on_delete => fk_action_map[row[:delete_referential_action]] } 162: end 163: end 164: h.values 165: end
Use the system tables to get index information
# File lib/sequel/adapters/shared/mssql.rb, line 168 168: def indexes(table, opts=OPTS) 169: m = output_identifier_meth 170: im = input_identifier_meth 171: indexes = {} 172: ds = metadata_dataset.from(:sys__tables___t). 173: join(:sys__indexes___i, :object_id=>:object_id). 174: join(:sys__index_columns___ic, :object_id=>:object_id, :index_id=>:index_id). 175: join(:sys__columns___c, :object_id=>:object_id, :column_id=>:column_id). 176: select(:i__name, :i__is_unique, :c__name___column). 177: where{{t__name=>im.call(table)}}. 178: where(:i__is_primary_key=>0, :i__is_disabled=>0). 179: order(:i__name, :ic__index_column_id) 180: 181: if supports_partial_indexes? 182: ds = ds.where(:i__has_filter=>0) 183: end 184: 185: ds.each do |r| 186: index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)} 187: index[:columns] << m.call(r[:column]) 188: end 189: indexes 190: end
# 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).
# File lib/sequel/adapters/shared/mssql.rb, line 194 194: def server_version(server=nil) 195: return @server_version if @server_version 196: if @opts[:server_version] 197: return @server_version = Integer(@opts[:server_version]) 198: end 199: @server_version = synchronize(server) do |conn| 200: (conn.server_version rescue nil) if conn.respond_to?(:server_version) 201: end 202: unless @server_version 203: m = SERVER_VERSION_RE.match(fetch(SERVER_VERSION_SQL).single_value.to_s) 204: @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i 205: end 206: @server_version 207: end