| 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, /Lock request time out period exceeded\./ => DatabaseLockTimeout, }.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: table = table.value if table.is_a?(Sequel::SQL::Identifier)
169: i = Sequel[:i]
170: ds = metadata_dataset.from(Sequel.lit('[sys].[tables]').as(:t)).
171: join(Sequel.lit('[sys].[indexes]').as(:i), :object_id=>:object_id).
172: join(Sequel.lit('[sys].[index_columns]').as(:ic), :object_id=>:object_id, :index_id=>:index_id).
173: join(Sequel.lit('[sys].[columns]').as(:c), :object_id=>:object_id, :column_id=>:column_id).
174: select(i[:name], i[:is_unique], Sequel[:c][:name].as(:column)).
175: where{{t[:name]=>im.call(table)}}.
176: where(i[:is_primary_key]=>0, i[:is_disabled]=>0).
177: order(i[:name], Sequel[:ic][:index_column_id])
178:
179: if supports_partial_indexes?
180: ds = ds.where(i[:has_filter]=>0)
181: end
182:
183: ds.each do |r|
184: index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)}
185: index[:columns] << m.call(r[:column])
186: end
187: indexes
188: 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 192
192: def server_version(server=nil)
193: return @server_version if @server_version
194: if @opts[:server_version]
195: return @server_version = Integer(@opts[:server_version])
196: end
197: @server_version = synchronize(server) do |conn|
198: (conn.server_version rescue nil) if conn.respond_to?(:server_version)
199: end
200: unless @server_version
201: m = /^(\d+)\.(\d+)\.(\d+)/.match(fetch("SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)").single_value.to_s)
202: @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i
203: end
204: @server_version
205: end