# File lib/sequel/adapters/shared/mssql.rb 419 def primary_key_constraint_sql_fragment(opts) 420 add_clustered_sql_fragment(super, opts) 421 end
module Sequel::MSSQL::DatabaseMethods
Constants
- DATABASE_ERROR_REGEXPS
- FOREIGN_KEY_ACTION_MAP
Attributes
Whether to use LIKE without COLLATE Latin1_General_CS_AS. Skipping the COLLATE can significantly increase performance in some cases.
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
Because Sequel
datasets only support a single result set per query, and retrieving the result code and number of rows requires a query, this does not support stored procedures which also return result sets. To handle such stored procedures, you should drop down to the connection/driver level by using Sequel::Database#synchronize
to get access to the underlying connection object.
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 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.public_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
# File lib/sequel/adapters/shared/mssql.rb 116 def database_type 117 :mssql 118 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 127 def foreign_key_list(table, opts=OPTS) 128 m = output_identifier_meth 129 im = input_identifier_meth 130 schema, table = schema_and_table(table) 131 current_schema = m.call(get(Sequel.function('schema_name'))) 132 fk_action_map = FOREIGN_KEY_ACTION_MAP 133 fk = Sequel[:fk] 134 fkc = Sequel[:fkc] 135 ds = metadata_dataset.from(Sequel.lit('[sys].[foreign_keys]').as(:fk)). 136 join(Sequel.lit('[sys].[foreign_key_columns]').as(:fkc), :constraint_object_id => :object_id). 137 join(Sequel.lit('[sys].[all_columns]').as(:pc), :object_id => fkc[:parent_object_id], :column_id => fkc[:parent_column_id]). 138 join(Sequel.lit('[sys].[all_columns]').as(:rc), :object_id => fkc[:referenced_object_id], :column_id => fkc[:referenced_column_id]). 139 where{{object_schema_name(fk[:parent_object_id]) => im.call(schema || current_schema)}}. 140 where{{object_name(fk[:parent_object_id]) => im.call(table)}}. 141 select{[fk[:name], 142 fk[:delete_referential_action], 143 fk[:update_referential_action], 144 pc[:name].as(:column), 145 rc[:name].as(:referenced_column), 146 object_schema_name(fk[:referenced_object_id]).as(:schema), 147 object_name(fk[:referenced_object_id]).as(:table)]}. 148 order(fk[:name], fkc[:constraint_column_id]) 149 h = {} 150 ds.each do |row| 151 if r = h[row[:name]] 152 r[:columns] << m.call(row[:column]) 153 r[:key] << m.call(row[:referenced_column]) 154 else 155 referenced_schema = m.call(row[:schema]) 156 referenced_table = m.call(row[:table]) 157 h[row[:name]] = { :name => m.call(row[:name]), 158 :table => (referenced_schema == current_schema) ? referenced_table : Sequel.qualify(referenced_schema, referenced_table), 159 :columns => [m.call(row[:column])], 160 :key => [m.call(row[:referenced_column])], 161 :on_update => fk_action_map[row[:update_referential_action]], 162 :on_delete => fk_action_map[row[:delete_referential_action]] } 163 end 164 end 165 h.values 166 end
# File lib/sequel/adapters/shared/mssql.rb 168 def freeze 169 server_version 170 super 171 end
Microsoft SQL
Server namespaces indexes per table.
# File lib/sequel/adapters/shared/mssql.rb 121 def global_index_namespace? 122 false 123 end
Use the system tables to get index information
# File lib/sequel/adapters/shared/mssql.rb 174 def indexes(table, opts=OPTS) 175 m = output_identifier_meth 176 im = input_identifier_meth 177 indexes = {} 178 table = table.value if table.is_a?(Sequel::SQL::Identifier) 179 i = Sequel[:i] 180 ds = metadata_dataset.from(Sequel.lit('[sys].[tables]').as(:t)). 181 join(Sequel.lit('[sys].[indexes]').as(:i), :object_id=>:object_id). 182 join(Sequel.lit('[sys].[index_columns]').as(:ic), :object_id=>:object_id, :index_id=>:index_id). 183 join(Sequel.lit('[sys].[columns]').as(:c), :object_id=>:object_id, :column_id=>:column_id). 184 select(i[:name], i[:is_unique], Sequel[:c][:name].as(:column)). 185 where{{t[:name]=>im.call(table)}}. 186 where(i[:is_primary_key]=>0, i[:is_disabled]=>0). 187 order(i[:name], Sequel[:ic][:index_column_id]) 188 189 if supports_partial_indexes? 190 ds = ds.where(i[:has_filter]=>0) 191 end 192 193 ds.each do |r| 194 index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)} 195 index[:columns] << m.call(r[:column]) 196 end 197 indexes 198 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 202 def server_version(server=nil) 203 return @server_version if @server_version 204 if @opts[:server_version] 205 return @server_version = Integer(@opts[:server_version]) 206 end 207 @server_version = synchronize(server) do |conn| 208 (conn.server_version rescue nil) if conn.respond_to?(:server_version) 209 end 210 unless @server_version 211 m = /^(\d+)\.(\d+)\.(\d+)/.match(fetch("SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)").single_value.to_s) 212 @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i 213 end 214 @server_version 215 end
MSSQL
2008+ supports partial indexes.
# File lib/sequel/adapters/shared/mssql.rb 218 def supports_partial_indexes? 219 dataset.send(:is_2008_or_later?) 220 end
MSSQL
supports savepoints, though it doesn't support releasing them
# File lib/sequel/adapters/shared/mssql.rb 223 def supports_savepoints? 224 true 225 end
MSSQL
supports transaction isolation levels
# File lib/sequel/adapters/shared/mssql.rb 228 def supports_transaction_isolation_levels? 229 true 230 end
MSSQL
supports transaction DDL statements.
# File lib/sequel/adapters/shared/mssql.rb 233 def supports_transactional_ddl? 234 true 235 end
Microsoft SQL
Server supports using the INFORMATION_SCHEMA to get information on tables.
# File lib/sequel/adapters/shared/mssql.rb 239 def tables(opts=OPTS) 240 information_schema_tables('BASE TABLE', opts) 241 end
Microsoft SQL
Server supports using the INFORMATION_SCHEMA to get information on views.
# File lib/sequel/adapters/shared/mssql.rb 245 def views(opts=OPTS) 246 information_schema_tables('VIEW', opts) 247 end
Private Instance Methods
Always quote identifiers in the metadata_dataset, so schema parsing works.
# File lib/sequel/adapters/shared/mssql.rb 414 def _metadata_dataset 415 super.with_quote_identifiers(true) 416 end
Add CLUSTERED or NONCLUSTERED as needed
# File lib/sequel/adapters/shared/mssql.rb 252 def add_clustered_sql_fragment(sql, opts) 253 clustered = opts[:clustered] 254 unless clustered.nil? 255 sql += " #{'NON' unless clustered}CLUSTERED" 256 end 257 258 sql 259 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 263 def add_drop_default_constraint_sql(sqls, table, column) 264 if constraint = default_constraint_name(table, column) 265 sqls << "ALTER TABLE #{quote_schema_table(table)} DROP CONSTRAINT #{constraint}" 266 end 267 end
# File lib/sequel/adapters/shared/mssql.rb 274 def alter_table_sql(table, op) 275 case op[:op] 276 when :add_column 277 "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}" 278 when :drop_column 279 sqls = [] 280 add_drop_default_constraint_sql(sqls, table, op[:name]) 281 sqls << super 282 when :rename_column 283 "sp_rename #{literal("#{quote_schema_table(table)}.#{quote_identifier(op[:name])}")}, #{literal(metadata_dataset.with_quote_identifiers(false).quote_identifier(op[:new_name]))}, 'COLUMN'" 284 when :set_column_type 285 sqls = [] 286 if sch = schema(table) 287 if cs = sch.each{|k, v| break v if k == op[:name]; nil} 288 cs = cs.dup 289 add_drop_default_constraint_sql(sqls, table, op[:name]) 290 cs[:default] = cs[:ruby_default] 291 op = cs.merge!(op) 292 default = op.delete(:default) 293 end 294 end 295 sqls << "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{column_definition_sql(op)}" 296 sqls << alter_table_sql(table, op.merge(:op=>:set_column_default, :default=>default, :skip_drop_default=>true)) if default 297 sqls 298 when :set_column_null 299 sch = schema(table).find{|k,v| k.to_s == op[:name].to_s}.last 300 type = sch[:db_type] 301 if [:string, :decimal, :blob].include?(sch[:type]) && !["text", "ntext"].include?(type) && (size = (sch[:max_chars] || sch[:column_size])) 302 size = "MAX" if size == -1 303 type += "(#{size}#{", #{sch[:scale]}" if sch[:scale] && sch[:scale].to_i > 0})" 304 end 305 "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} #{type_literal(:type=>type)} #{'NOT ' unless op[:null]}NULL" 306 when :set_column_default 307 sqls = [] 308 add_drop_default_constraint_sql(sqls, table, op[:name]) unless op[:skip_drop_default] 309 sqls << "ALTER TABLE #{quote_schema_table(table)} ADD CONSTRAINT #{quote_identifier("sequel_#{table}_#{op[:name]}_def")} DEFAULT #{literal(op[:default])} FOR #{quote_identifier(op[:name])}" 310 else 311 super(table, op) 312 end 313 end
MSSQL
uses the IDENTITY(1,1) column for autoincrementing columns.
# File lib/sequel/adapters/shared/mssql.rb 270 def auto_increment_sql 271 'IDENTITY(1,1)' 272 end
# File lib/sequel/adapters/shared/mssql.rb 315 def begin_savepoint_sql(depth) 316 "SAVE TRANSACTION autopoint_#{depth}" 317 end
# File lib/sequel/adapters/shared/mssql.rb 319 def begin_transaction_sql 320 "BEGIN TRANSACTION" 321 end
MSSQL
does not allow adding primary key constraints to NULLable columns.
# File lib/sequel/adapters/shared/mssql.rb 324 def can_add_primary_key_constraint_on_nullable_columns? 325 false 326 end
Handle MSSQL
specific default format.
# File lib/sequel/adapters/shared/mssql.rb 334 def column_schema_normalize_default(default, type) 335 if m = /\A(?:\(N?('.*')\)|\(\((-?\d+(?:\.\d+)?)\)\))\z/.match(default) 336 default = m[1] || m[2] 337 end 338 super(default, type) 339 end
MSSQL
tinyint types are unsigned.
# File lib/sequel/adapters/shared/mssql.rb 329 def column_schema_tinyint_type_is_unsigned? 330 true 331 end
Commit the active transaction on the connection, does not release savepoints.
# File lib/sequel/adapters/shared/mssql.rb 342 def commit_transaction(conn, opts=OPTS) 343 log_connection_execute(conn, commit_transaction_sql) unless savepoint_level(conn) > 1 344 end
# File lib/sequel/adapters/shared/mssql.rb 346 def commit_transaction_sql 347 "COMMIT TRANSACTION" 348 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 361 def create_table_as(name, ds, options) 362 raise(Error, "must provide dataset instance as value of create_table :as option on MSSQL") unless ds.is_a?(Sequel::Dataset) 363 run(ds.into(name).sql) 364 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 353 def create_table_prefix_sql(name, options) 354 "CREATE TABLE #{quote_schema_table(options[:temp] ? "##{name}" : name)}" 355 end
# File lib/sequel/adapters/shared/mssql.rb 374 def database_error_regexps 375 DATABASE_ERROR_REGEXPS 376 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 381 def default_constraint_name(table, column_name) 382 if server_version >= 9000000 383 table_name = schema_and_table(table).compact.join('.') 384 self[Sequel[:sys][:default_constraints]]. 385 where{{:parent_object_id => Sequel::SQL::Function.new(:object_id, table_name), col_name(:parent_object_id, :parent_column_id) => column_name.to_s}}. 386 get(:name) 387 end 388 end
# File lib/sequel/adapters/shared/mssql.rb 390 def drop_index_sql(table, op) 391 "DROP INDEX #{quote_identifier(op[:name] || default_index_name(table, op[:columns]))} ON #{quote_schema_table(table)}" 392 end
# File lib/sequel/adapters/shared/mssql.rb 394 def index_definition_sql(table_name, index) 395 index_name = index[:name] || default_index_name(table_name, index[:columns]) 396 raise Error, "Partial indexes are not supported for this database" if index[:where] && !supports_partial_indexes? 397 if index[:type] == :full_text 398 "CREATE FULLTEXT INDEX ON #{quote_schema_table(table_name)} #{literal(index[:columns])} KEY INDEX #{literal(index[:key_index])}" 399 else 400 "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]}" 401 end 402 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/mssql.rb 405 def information_schema_tables(type, opts) 406 m = output_identifier_meth 407 metadata_dataset.from(Sequel[:information_schema][:tables].as(:t)). 408 select(:table_name). 409 where(:table_type=>type, :table_schema=>(opts[:schema]||'dbo').to_s). 410 map{|x| m.call(x[:table_name])} 411 end
Handle clustered and nonclustered primary keys
Use sp_rename to rename the table
# File lib/sequel/adapters/shared/mssql.rb 424 def rename_table_sql(name, new_name) 425 "sp_rename #{literal(quote_schema_table(name))}, #{quote_identifier(schema_and_table(new_name).pop)}" 426 end
# File lib/sequel/adapters/shared/mssql.rb 428 def rollback_savepoint_sql(depth) 429 "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION autopoint_#{depth}" 430 end
# File lib/sequel/adapters/shared/mssql.rb 432 def rollback_transaction_sql 433 "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION" 434 end
# File lib/sequel/adapters/shared/mssql.rb 436 def schema_column_type(db_type) 437 case db_type 438 when /\A(?:bit)\z/io 439 :boolean 440 when /\A(?:(?:small)?money)\z/io 441 :decimal 442 when /\A(timestamp|rowversion)\z/io 443 :blob 444 else 445 super 446 end 447 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 452 def schema_parse_table(table_name, opts) 453 m = output_identifier_meth(opts[:dataset]) 454 m2 = input_identifier_meth(opts[:dataset]) 455 tn = m2.call(table_name.to_s) 456 info_sch_sch = opts[:information_schema_schema] 457 inf_sch_qual = lambda{|s| info_sch_sch ? Sequel.qualify(info_sch_sch, s) : Sequel[s]} 458 table_id = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:objects])).where(:name => tn).select_map(:object_id).first 459 460 identity_cols = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:columns])). 461 where(:object_id=>table_id, :is_identity=>true). 462 select_map(:name) 463 464 pk_index_id = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:sysindexes])). 465 where(:id=>table_id, :indid=>1..254){{(status & 2048)=>2048}}. 466 get(:indid) 467 pk_cols = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:sysindexkeys]).as(:sik)). 468 join(inf_sch_qual.call(Sequel[:sys][:syscolumns]).as(:sc), :id=>:id, :colid=>:colid). 469 where{{sik[:id]=>table_id, sik[:indid]=>pk_index_id}}. 470 select_order_map{sc[:name]} 471 472 ds = metadata_dataset.from(inf_sch_qual.call(Sequel[:information_schema][:tables]).as(:t)). 473 join(inf_sch_qual.call(Sequel[:information_schema][:columns]).as(:c), :table_catalog=>:table_catalog, 474 :table_schema => :table_schema, :table_name => :table_name). 475 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)]}. 476 where{{c[:table_name]=>tn}} 477 478 if schema = opts[:schema] 479 ds = ds.where{{c[:table_schema]=>schema}} 480 end 481 482 ds.map do |row| 483 if row[:primary_key] = pk_cols.include?(row[:column]) 484 row[:auto_increment] = identity_cols.include?(row[:column]) 485 end 486 row[:allow_null] = row[:allow_null] == 'YES' ? true : false 487 row[:default] = nil if blank_object?(row[:default]) 488 row[:type] = if row[:db_type] =~ /number|numeric|decimal/i && row[:scale] == 0 489 :integer 490 else 491 schema_column_type(row[:db_type]) 492 end 493 row[:max_length] = row[:max_chars] if row[:type] == :string && row[:max_chars] >= 0 494 [m.call(row.delete(:column)), row] 495 end 496 end
Set the mssql_unicode_strings
settings from the given options.
# File lib/sequel/adapters/shared/mssql.rb 499 def set_mssql_unicode_strings 500 @mssql_unicode_strings = typecast_value_boolean(@opts.fetch(:mssql_unicode_strings, true)) 501 end
MSSQL
has both datetime and timestamp classes, most people are going to want datetime
# File lib/sequel/adapters/shared/mssql.rb 505 def type_literal_generic_datetime(column) 506 :datetime 507 end
MSSQL
uses varbinary(max) type for blobs
# File lib/sequel/adapters/shared/mssql.rb 515 def type_literal_generic_file(column) 516 :'varbinary(max)' 517 end
MSSQL
doesn't have a true boolean class, so it uses bit
# File lib/sequel/adapters/shared/mssql.rb 510 def type_literal_generic_trueclass(column) 511 :bit 512 end
Handle clustered and nonclustered unique constraints
# File lib/sequel/adapters/shared/mssql.rb 520 def unique_constraint_sql_fragment(opts) 521 add_clustered_sql_fragment(super, opts) 522 end
MSSQL
supports views with check option, but not local.
# File lib/sequel/adapters/shared/mssql.rb 525 def view_with_check_option_support 526 true 527 end