module Sequel::MSSQL::DatabaseMethods
Constants
- DATABASE_ERROR_REGEXPS
- FOREIGN_KEY_ACTION_MAP
Attributes
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 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 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
# File lib/sequel/adapters/shared/mssql.rb 106 def database_type 107 :mssql 108 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 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 158 def freeze 159 server_version 160 super 161 end
Microsoft SQL
Server namespaces indexes per table.
# File lib/sequel/adapters/shared/mssql.rb 111 def global_index_namespace? 112 false 113 end
Use the system tables to get index information
# File lib/sequel/adapters/shared/mssql.rb 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 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
MSSQL
2008+ supports partial indexes.
# File lib/sequel/adapters/shared/mssql.rb 208 def supports_partial_indexes? 209 dataset.send(:is_2008_or_later?) 210 end
MSSQL
supports savepoints, though it doesn't support releasing them
# File lib/sequel/adapters/shared/mssql.rb 213 def supports_savepoints? 214 true 215 end
MSSQL
supports transaction isolation levels
# File lib/sequel/adapters/shared/mssql.rb 218 def supports_transaction_isolation_levels? 219 true 220 end
MSSQL
supports transaction DDL statements.
# File lib/sequel/adapters/shared/mssql.rb 223 def supports_transactional_ddl? 224 true 225 end
Microsoft SQL
Server supports using the INFORMATION_SCHEMA to get information on tables.
# File lib/sequel/adapters/shared/mssql.rb 229 def tables(opts=OPTS) 230 information_schema_tables('BASE TABLE', opts) 231 end
Microsoft SQL
Server supports using the INFORMATION_SCHEMA to get information on views.
# File lib/sequel/adapters/shared/mssql.rb 235 def views(opts=OPTS) 236 information_schema_tables('VIEW', opts) 237 end
Private Instance Methods
Always quote identifiers in the metadata_dataset, so schema parsing works.
# File lib/sequel/adapters/shared/mssql.rb 387 def _metadata_dataset 388 super.with_quote_identifiers(true) 389 end
Add dropping of the default constraint to the list of SQL
queries. This is necessary before dropping the column or changing its type.
# File lib/sequel/adapters/shared/mssql.rb 243 def add_drop_default_constraint_sql(sqls, table, column) 244 if constraint = default_constraint_name(table, column) 245 sqls << "ALTER TABLE #{quote_schema_table(table)} DROP CONSTRAINT #{constraint}" 246 end 247 end
# File lib/sequel/adapters/shared/mssql.rb 254 def alter_table_sql(table, op) 255 case op[:op] 256 when :add_column 257 "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}" 258 when :drop_column 259 sqls = [] 260 add_drop_default_constraint_sql(sqls, table, op[:name]) 261 sqls << super 262 when :rename_column 263 "sp_rename #{literal("#{quote_schema_table(table)}.#{quote_identifier(op[:name])}")}, #{literal(metadata_dataset.with_quote_identifiers(false).quote_identifier(op[:new_name]))}, 'COLUMN'" 264 when :set_column_type 265 sqls = [] 266 if sch = schema(table) 267 if cs = sch.each{|k, v| break v if k == op[:name]; nil} 268 cs = cs.dup 269 add_drop_default_constraint_sql(sqls, table, op[:name]) 270 cs[:default] = cs[:ruby_default] 271 op = cs.merge!(op) 272 default = op.delete(:default) 273 end 274 end 275 sqls << "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{column_definition_sql(op)}" 276 sqls << alter_table_sql(table, op.merge(:op=>:set_column_default, :default=>default)) if default 277 sqls 278 when :set_column_null 279 sch = schema(table).find{|k,v| k.to_s == op[:name].to_s}.last 280 type = sch[:db_type] 281 if [:string, :decimal].include?(sch[:type]) && !["text", "ntext"].include?(type) && (size = (sch[:max_chars] || sch[:column_size])) 282 size = "MAX" if size == -1 283 type += "(#{size}#{", #{sch[:scale]}" if sch[:scale] && sch[:scale].to_i > 0})" 284 end 285 "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} #{type_literal(:type=>type)} #{'NOT ' unless op[:null]}NULL" 286 when :set_column_default 287 "ALTER TABLE #{quote_schema_table(table)} ADD CONSTRAINT #{quote_identifier("sequel_#{table}_#{op[:name]}_def")} DEFAULT #{literal(op[:default])} FOR #{quote_identifier(op[:name])}" 288 else 289 super(table, op) 290 end 291 end
MSSQL
uses the IDENTITY(1,1) column for autoincrementing columns.
# File lib/sequel/adapters/shared/mssql.rb 250 def auto_increment_sql 251 'IDENTITY(1,1)' 252 end
# File lib/sequel/adapters/shared/mssql.rb 293 def begin_savepoint_sql(depth) 294 "SAVE TRANSACTION autopoint_#{depth}" 295 end
# File lib/sequel/adapters/shared/mssql.rb 297 def begin_transaction_sql 298 "BEGIN TRANSACTION" 299 end
MSSQL
does not allow adding primary key constraints to NULLable columns.
# File lib/sequel/adapters/shared/mssql.rb 302 def can_add_primary_key_constraint_on_nullable_columns? 303 false 304 end
Handle MSSQL
specific default format.
# File lib/sequel/adapters/shared/mssql.rb 307 def column_schema_normalize_default(default, type) 308 if m = /\A(?:\(N?('.*')\)|\(\((-?\d+(?:\.\d+)?)\)\))\z/.match(default) 309 default = m[1] || m[2] 310 end 311 super(default, type) 312 end
Commit the active transaction on the connection, does not release savepoints.
# File lib/sequel/adapters/shared/mssql.rb 315 def commit_transaction(conn, opts=OPTS) 316 log_connection_execute(conn, commit_transaction_sql) unless savepoint_level(conn) > 1 317 end
# File lib/sequel/adapters/shared/mssql.rb 319 def commit_transaction_sql 320 "COMMIT TRANSACTION" 321 end
MSSQL
doesn't support CREATE TABLE AS, it only supports SELECT INTO. Emulating CREATE TABLE AS using SELECT INTO is only possible if a dataset is given as the argument, it can't work with a string, so raise an Error
if a string is given.
# File lib/sequel/adapters/shared/mssql.rb 334 def create_table_as(name, ds, options) 335 raise(Error, "must provide dataset instance as value of create_table :as option on MSSQL") unless ds.is_a?(Sequel::Dataset) 336 run(ds.into(name).sql) 337 end
MSSQL
uses the name of the table to decide the difference between a regular and temporary table, with temporary table names starting with a #.
# File lib/sequel/adapters/shared/mssql.rb 326 def create_table_prefix_sql(name, options) 327 "CREATE TABLE #{quote_schema_table(options[:temp] ? "##{name}" : name)}" 328 end
# File lib/sequel/adapters/shared/mssql.rb 347 def database_error_regexps 348 DATABASE_ERROR_REGEXPS 349 end
The name of the constraint for setting the default value on the table and column. The SQL
used to select default constraints utilizes MSSQL
catalog views which were introduced in 2005. This method intentionally does not support MSSQL
2000.
# File lib/sequel/adapters/shared/mssql.rb 354 def default_constraint_name(table, column_name) 355 if server_version >= 9000000 356 table_name = schema_and_table(table).compact.join('.') 357 self[Sequel[:sys][:default_constraints]]. 358 where{{:parent_object_id => Sequel::SQL::Function.new(:object_id, table_name), col_name(:parent_object_id, :parent_column_id) => column_name.to_s}}. 359 get(:name) 360 end 361 end
# File lib/sequel/adapters/shared/mssql.rb 363 def drop_index_sql(table, op) 364 "DROP INDEX #{quote_identifier(op[:name] || default_index_name(table, op[:columns]))} ON #{quote_schema_table(table)}" 365 end
# File lib/sequel/adapters/shared/mssql.rb 367 def index_definition_sql(table_name, index) 368 index_name = index[:name] || default_index_name(table_name, index[:columns]) 369 raise Error, "Partial indexes are not supported for this database" if index[:where] && !supports_partial_indexes? 370 if index[:type] == :full_text 371 "CREATE FULLTEXT INDEX ON #{quote_schema_table(table_name)} #{literal(index[:columns])} KEY INDEX #{literal(index[:key_index])}" 372 else 373 "CREATE #{'UNIQUE ' if index[:unique]}#{'CLUSTERED ' if index[:type] == :clustered}INDEX #{quote_identifier(index_name)} ON #{quote_schema_table(table_name)} #{literal(index[:columns])}#{" INCLUDE #{literal(index[:include])}" if index[:include]}#{" WHERE #{filter_expr(index[:where])}" if index[:where]}" 374 end 375 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/mssql.rb 378 def information_schema_tables(type, opts) 379 m = output_identifier_meth 380 metadata_dataset.from(Sequel[:information_schema][:tables].as(:t)). 381 select(:table_name). 382 where(:table_type=>type, :table_schema=>(opts[:schema]||'dbo').to_s). 383 map{|x| m.call(x[:table_name])} 384 end
Use sp_rename to rename the table
# File lib/sequel/adapters/shared/mssql.rb 392 def rename_table_sql(name, new_name) 393 "sp_rename #{literal(quote_schema_table(name))}, #{quote_identifier(schema_and_table(new_name).pop)}" 394 end
# File lib/sequel/adapters/shared/mssql.rb 396 def rollback_savepoint_sql(depth) 397 "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION autopoint_#{depth}" 398 end
# File lib/sequel/adapters/shared/mssql.rb 400 def rollback_transaction_sql 401 "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION" 402 end
# File lib/sequel/adapters/shared/mssql.rb 404 def schema_column_type(db_type) 405 case db_type 406 when /\A(?:bit)\z/io 407 :boolean 408 when /\A(?:(?:small)?money)\z/io 409 :decimal 410 when /\A(timestamp|rowversion)\z/io 411 :blob 412 else 413 super 414 end 415 end
MSSQL
uses the INFORMATION_SCHEMA to hold column information, and parses primary key information from the sysindexes, sysindexkeys, and syscolumns system tables.
# File lib/sequel/adapters/shared/mssql.rb 420 def schema_parse_table(table_name, opts) 421 m = output_identifier_meth(opts[:dataset]) 422 m2 = input_identifier_meth(opts[:dataset]) 423 tn = m2.call(table_name.to_s) 424 table_id = get(Sequel.function(:object_id, tn)) 425 info_sch_sch = opts[:information_schema_schema] 426 inf_sch_qual = lambda{|s| info_sch_sch ? Sequel.qualify(info_sch_sch, s) : Sequel[s]} 427 sys_qual = lambda{|s| info_sch_sch ? Sequel.qualify(info_sch_sch, Sequel.qualify(Sequel.lit(''), s)) : Sequel[s]} 428 429 identity_cols = metadata_dataset.from(Sequel.lit('[sys].[columns]')). 430 where(:object_id=>table_id, :is_identity=>true). 431 select_map(:name) 432 433 pk_index_id = metadata_dataset.from(sys_qual.call(Sequel.lit('sysindexes'))). 434 where(:id=>table_id, :indid=>1..254){{(status & 2048)=>2048}}. 435 get(:indid) 436 pk_cols = metadata_dataset.from(sys_qual.call(Sequel.lit('sysindexkeys')).as(:sik)). 437 join(sys_qual.call(Sequel.lit('syscolumns')).as(:sc), :id=>:id, :colid=>:colid). 438 where{{sik[:id]=>table_id, sik[:indid]=>pk_index_id}}. 439 select_order_map{sc[:name]} 440 441 ds = metadata_dataset.from(inf_sch_qual.call(Sequel[:information_schema][:tables]).as(:t)). 442 join(inf_sch_qual.call(Sequel[:information_schema][:columns]).as(:c), :table_catalog=>:table_catalog, 443 :table_schema => :table_schema, :table_name => :table_name). 444 select{[column_name.as(:column), data_type.as(:db_type), character_maximum_length.as(:max_chars), column_default.as(:default), is_nullable.as(:allow_null), numeric_precision.as(:column_size), numeric_scale.as(:scale)]}. 445 where{{c[:table_name]=>tn}} 446 447 if schema = opts[:schema] 448 ds = ds.where{{c[:table_schema]=>schema}} 449 end 450 451 ds.map do |row| 452 if row[:primary_key] = pk_cols.include?(row[:column]) 453 row[:auto_increment] = identity_cols.include?(row[:column]) 454 end 455 row[:allow_null] = row[:allow_null] == 'YES' ? true : false 456 row[:default] = nil if blank_object?(row[:default]) 457 row[:type] = if row[:db_type] =~ /number|numeric|decimal/i && row[:scale] == 0 458 :integer 459 else 460 schema_column_type(row[:db_type]) 461 end 462 row[:max_length] = row[:max_chars] if row[:type] == :string && row[:max_chars] >= 0 463 [m.call(row.delete(:column)), row] 464 end 465 end
Set the mssql_unicode_strings
settings from the given options.
# File lib/sequel/adapters/shared/mssql.rb 468 def set_mssql_unicode_strings 469 @mssql_unicode_strings = typecast_value_boolean(@opts.fetch(:mssql_unicode_strings, true)) 470 end
MSSQL
has both datetime and timestamp classes, most people are going to want datetime
# File lib/sequel/adapters/shared/mssql.rb 474 def type_literal_generic_datetime(column) 475 :datetime 476 end
MSSQL
uses varbinary(max) type for blobs
# File lib/sequel/adapters/shared/mssql.rb 484 def type_literal_generic_file(column) 485 :'varbinary(max)' 486 end
MSSQL
doesn't have a true boolean class, so it uses bit
# File lib/sequel/adapters/shared/mssql.rb 479 def type_literal_generic_trueclass(column) 480 :bit 481 end
MSSQL
supports views with check option, but not local.
# File lib/sequel/adapters/shared/mssql.rb 489 def view_with_check_option_support 490 true 491 end