# File lib/sequel/adapters/shared/mssql.rb 410 def primary_key_constraint_sql_fragment(opts) 411 add_clustered_sql_fragment(super, opts) 412 end
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
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 60 def call_mssql_sproc(name, opts=OPTS) 61 args = opts[:args] || [] 62 names = ['@RC AS RESULT', '@@ROWCOUNT AS NUMROWS'] 63 declarations = ['@RC int'] 64 values = [] 65 66 if args.is_a?(Hash) 67 named_args = true 68 args = args.to_a 69 method = :each 70 else 71 method = :each_with_index 72 end 73 74 args.public_send(method) do |v, i| 75 if named_args 76 k = v 77 v, type, select = i 78 raise Error, "must provide output parameter name when using output parameters with named arguments" if v == :output && !select 79 else 80 v, type, select = v 81 end 82 83 if v == :output 84 type ||= "nvarchar(max)" 85 if named_args 86 varname = select 87 else 88 varname = "var#{i}" 89 select ||= varname 90 end 91 names << "@#{varname} AS #{quote_identifier(select)}" 92 declarations << "@#{varname} #{type}" 93 value = "@#{varname} OUTPUT" 94 else 95 value = literal(v) 96 end 97 98 if named_args 99 value = "@#{k}=#{value}" 100 end 101 102 values << value 103 end 104 105 sql = "DECLARE #{declarations.join(', ')}; EXECUTE @RC = #{name} #{values.join(', ')}; SELECT #{names.join(', ')}" 106 107 ds = dataset.with_sql(sql) 108 ds = ds.server(opts[:server]) if opts[:server] 109 ds.first 110 end
# File lib/sequel/adapters/shared/mssql.rb 112 def database_type 113 :mssql 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 123 def foreign_key_list(table, opts=OPTS) 124 m = output_identifier_meth 125 im = input_identifier_meth 126 schema, table = schema_and_table(table) 127 current_schema = m.call(get(Sequel.function('schema_name'))) 128 fk_action_map = FOREIGN_KEY_ACTION_MAP 129 fk = Sequel[:fk] 130 fkc = Sequel[:fkc] 131 ds = metadata_dataset.from(Sequel.lit('[sys].[foreign_keys]').as(:fk)). 132 join(Sequel.lit('[sys].[foreign_key_columns]').as(:fkc), :constraint_object_id => :object_id). 133 join(Sequel.lit('[sys].[all_columns]').as(:pc), :object_id => fkc[:parent_object_id], :column_id => fkc[:parent_column_id]). 134 join(Sequel.lit('[sys].[all_columns]').as(:rc), :object_id => fkc[:referenced_object_id], :column_id => fkc[:referenced_column_id]). 135 where{{object_schema_name(fk[:parent_object_id]) => im.call(schema || current_schema)}}. 136 where{{object_name(fk[:parent_object_id]) => im.call(table)}}. 137 select{[fk[:name], 138 fk[:delete_referential_action], 139 fk[:update_referential_action], 140 pc[:name].as(:column), 141 rc[:name].as(:referenced_column), 142 object_schema_name(fk[:referenced_object_id]).as(:schema), 143 object_name(fk[:referenced_object_id]).as(:table)]}. 144 order(fk[:name], fkc[:constraint_column_id]) 145 h = {} 146 ds.each do |row| 147 if r = h[row[:name]] 148 r[:columns] << m.call(row[:column]) 149 r[:key] << m.call(row[:referenced_column]) 150 else 151 referenced_schema = m.call(row[:schema]) 152 referenced_table = m.call(row[:table]) 153 h[row[:name]] = { :name => m.call(row[:name]), 154 :table => (referenced_schema == current_schema) ? referenced_table : Sequel.qualify(referenced_schema, referenced_table), 155 :columns => [m.call(row[:column])], 156 :key => [m.call(row[:referenced_column])], 157 :on_update => fk_action_map[row[:update_referential_action]], 158 :on_delete => fk_action_map[row[:delete_referential_action]] } 159 end 160 end 161 h.values 162 end
# File lib/sequel/adapters/shared/mssql.rb 164 def freeze 165 server_version 166 super 167 end
Microsoft SQL
Server namespaces indexes per table.
# File lib/sequel/adapters/shared/mssql.rb 117 def global_index_namespace? 118 false 119 end
Use the system tables to get index information
# File lib/sequel/adapters/shared/mssql.rb 170 def indexes(table, opts=OPTS) 171 m = output_identifier_meth 172 im = input_identifier_meth 173 indexes = {} 174 table = table.value if table.is_a?(Sequel::SQL::Identifier) 175 i = Sequel[:i] 176 ds = metadata_dataset.from(Sequel.lit('[sys].[tables]').as(:t)). 177 join(Sequel.lit('[sys].[indexes]').as(:i), :object_id=>:object_id). 178 join(Sequel.lit('[sys].[index_columns]').as(:ic), :object_id=>:object_id, :index_id=>:index_id). 179 join(Sequel.lit('[sys].[columns]').as(:c), :object_id=>:object_id, :column_id=>:column_id). 180 select(i[:name], i[:is_unique], Sequel[:c][:name].as(:column)). 181 where{{t[:name]=>im.call(table)}}. 182 where(i[:is_primary_key]=>0, i[:is_disabled]=>0). 183 order(i[:name], Sequel[:ic][:index_column_id]) 184 185 if supports_partial_indexes? 186 ds = ds.where(i[:has_filter]=>0) 187 end 188 189 ds.each do |r| 190 index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)} 191 index[:columns] << m.call(r[:column]) 192 end 193 indexes 194 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 198 def server_version(server=nil) 199 return @server_version if @server_version 200 if @opts[:server_version] 201 return @server_version = Integer(@opts[:server_version]) 202 end 203 @server_version = synchronize(server) do |conn| 204 (conn.server_version rescue nil) if conn.respond_to?(:server_version) 205 end 206 unless @server_version 207 m = /^(\d+)\.(\d+)\.(\d+)/.match(fetch("SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)").single_value.to_s) 208 @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i 209 end 210 @server_version 211 end
MSSQL
2008+ supports partial indexes.
# File lib/sequel/adapters/shared/mssql.rb 214 def supports_partial_indexes? 215 dataset.send(:is_2008_or_later?) 216 end
MSSQL
supports savepoints, though it doesn't support releasing them
# File lib/sequel/adapters/shared/mssql.rb 219 def supports_savepoints? 220 true 221 end
MSSQL
supports transaction isolation levels
# File lib/sequel/adapters/shared/mssql.rb 224 def supports_transaction_isolation_levels? 225 true 226 end
MSSQL
supports transaction DDL statements.
# File lib/sequel/adapters/shared/mssql.rb 229 def supports_transactional_ddl? 230 true 231 end
Microsoft SQL
Server supports using the INFORMATION_SCHEMA to get information on tables.
# File lib/sequel/adapters/shared/mssql.rb 235 def tables(opts=OPTS) 236 information_schema_tables('BASE TABLE', opts) 237 end
Microsoft SQL
Server supports using the INFORMATION_SCHEMA to get information on views.
# File lib/sequel/adapters/shared/mssql.rb 241 def views(opts=OPTS) 242 information_schema_tables('VIEW', opts) 243 end
Private Instance Methods
Always quote identifiers in the metadata_dataset, so schema parsing works.
# File lib/sequel/adapters/shared/mssql.rb 405 def _metadata_dataset 406 super.with_quote_identifiers(true) 407 end
Add CLUSTERED or NONCLUSTERED as needed
# File lib/sequel/adapters/shared/mssql.rb 248 def add_clustered_sql_fragment(sql, opts) 249 clustered = opts[:clustered] 250 unless clustered.nil? 251 sql += " #{'NON' unless clustered}CLUSTERED" 252 end 253 254 sql 255 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 259 def add_drop_default_constraint_sql(sqls, table, column) 260 if constraint = default_constraint_name(table, column) 261 sqls << "ALTER TABLE #{quote_schema_table(table)} DROP CONSTRAINT #{constraint}" 262 end 263 end
# File lib/sequel/adapters/shared/mssql.rb 270 def alter_table_sql(table, op) 271 case op[:op] 272 when :add_column 273 "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}" 274 when :drop_column 275 sqls = [] 276 add_drop_default_constraint_sql(sqls, table, op[:name]) 277 sqls << super 278 when :rename_column 279 "sp_rename #{literal("#{quote_schema_table(table)}.#{quote_identifier(op[:name])}")}, #{literal(metadata_dataset.with_quote_identifiers(false).quote_identifier(op[:new_name]))}, 'COLUMN'" 280 when :set_column_type 281 sqls = [] 282 if sch = schema(table) 283 if cs = sch.each{|k, v| break v if k == op[:name]; nil} 284 cs = cs.dup 285 add_drop_default_constraint_sql(sqls, table, op[:name]) 286 cs[:default] = cs[:ruby_default] 287 op = cs.merge!(op) 288 default = op.delete(:default) 289 end 290 end 291 sqls << "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{column_definition_sql(op)}" 292 sqls << alter_table_sql(table, op.merge(:op=>:set_column_default, :default=>default, :skip_drop_default=>true)) if default 293 sqls 294 when :set_column_null 295 sch = schema(table).find{|k,v| k.to_s == op[:name].to_s}.last 296 type = sch[:db_type] 297 if [:string, :decimal, :blob].include?(sch[:type]) && !["text", "ntext"].include?(type) && (size = (sch[:max_chars] || sch[:column_size])) 298 size = "MAX" if size == -1 299 type += "(#{size}#{", #{sch[:scale]}" if sch[:scale] && sch[:scale].to_i > 0})" 300 end 301 "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} #{type_literal(:type=>type)} #{'NOT ' unless op[:null]}NULL" 302 when :set_column_default 303 sqls = [] 304 add_drop_default_constraint_sql(sqls, table, op[:name]) unless op[:skip_drop_default] 305 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])}" 306 else 307 super(table, op) 308 end 309 end
MSSQL
uses the IDENTITY(1,1) column for autoincrementing columns.
# File lib/sequel/adapters/shared/mssql.rb 266 def auto_increment_sql 267 'IDENTITY(1,1)' 268 end
# File lib/sequel/adapters/shared/mssql.rb 311 def begin_savepoint_sql(depth) 312 "SAVE TRANSACTION autopoint_#{depth}" 313 end
# File lib/sequel/adapters/shared/mssql.rb 315 def begin_transaction_sql 316 "BEGIN TRANSACTION" 317 end
MSSQL
does not allow adding primary key constraints to NULLable columns.
# File lib/sequel/adapters/shared/mssql.rb 320 def can_add_primary_key_constraint_on_nullable_columns? 321 false 322 end
Handle MSSQL
specific default format.
# File lib/sequel/adapters/shared/mssql.rb 325 def column_schema_normalize_default(default, type) 326 if m = /\A(?:\(N?('.*')\)|\(\((-?\d+(?:\.\d+)?)\)\))\z/.match(default) 327 default = m[1] || m[2] 328 end 329 super(default, type) 330 end
Commit the active transaction on the connection, does not release savepoints.
# File lib/sequel/adapters/shared/mssql.rb 333 def commit_transaction(conn, opts=OPTS) 334 log_connection_execute(conn, commit_transaction_sql) unless savepoint_level(conn) > 1 335 end
# File lib/sequel/adapters/shared/mssql.rb 337 def commit_transaction_sql 338 "COMMIT TRANSACTION" 339 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 352 def create_table_as(name, ds, options) 353 raise(Error, "must provide dataset instance as value of create_table :as option on MSSQL") unless ds.is_a?(Sequel::Dataset) 354 run(ds.into(name).sql) 355 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 344 def create_table_prefix_sql(name, options) 345 "CREATE TABLE #{quote_schema_table(options[:temp] ? "##{name}" : name)}" 346 end
# File lib/sequel/adapters/shared/mssql.rb 365 def database_error_regexps 366 DATABASE_ERROR_REGEXPS 367 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 372 def default_constraint_name(table, column_name) 373 if server_version >= 9000000 374 table_name = schema_and_table(table).compact.join('.') 375 self[Sequel[:sys][:default_constraints]]. 376 where{{:parent_object_id => Sequel::SQL::Function.new(:object_id, table_name), col_name(:parent_object_id, :parent_column_id) => column_name.to_s}}. 377 get(:name) 378 end 379 end
# File lib/sequel/adapters/shared/mssql.rb 381 def drop_index_sql(table, op) 382 "DROP INDEX #{quote_identifier(op[:name] || default_index_name(table, op[:columns]))} ON #{quote_schema_table(table)}" 383 end
# File lib/sequel/adapters/shared/mssql.rb 385 def index_definition_sql(table_name, index) 386 index_name = index[:name] || default_index_name(table_name, index[:columns]) 387 raise Error, "Partial indexes are not supported for this database" if index[:where] && !supports_partial_indexes? 388 if index[:type] == :full_text 389 "CREATE FULLTEXT INDEX ON #{quote_schema_table(table_name)} #{literal(index[:columns])} KEY INDEX #{literal(index[:key_index])}" 390 else 391 "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]}" 392 end 393 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/mssql.rb 396 def information_schema_tables(type, opts) 397 m = output_identifier_meth 398 metadata_dataset.from(Sequel[:information_schema][:tables].as(:t)). 399 select(:table_name). 400 where(:table_type=>type, :table_schema=>(opts[:schema]||'dbo').to_s). 401 map{|x| m.call(x[:table_name])} 402 end
Handle clustered and nonclustered primary keys
Use sp_rename to rename the table
# File lib/sequel/adapters/shared/mssql.rb 415 def rename_table_sql(name, new_name) 416 "sp_rename #{literal(quote_schema_table(name))}, #{quote_identifier(schema_and_table(new_name).pop)}" 417 end
# File lib/sequel/adapters/shared/mssql.rb 419 def rollback_savepoint_sql(depth) 420 "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION autopoint_#{depth}" 421 end
# File lib/sequel/adapters/shared/mssql.rb 423 def rollback_transaction_sql 424 "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION" 425 end
# File lib/sequel/adapters/shared/mssql.rb 427 def schema_column_type(db_type) 428 case db_type 429 when /\A(?:bit)\z/io 430 :boolean 431 when /\A(?:(?:small)?money)\z/io 432 :decimal 433 when /\A(timestamp|rowversion)\z/io 434 :blob 435 else 436 super 437 end 438 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 443 def schema_parse_table(table_name, opts) 444 m = output_identifier_meth(opts[:dataset]) 445 m2 = input_identifier_meth(opts[:dataset]) 446 tn = m2.call(table_name.to_s) 447 info_sch_sch = opts[:information_schema_schema] 448 inf_sch_qual = lambda{|s| info_sch_sch ? Sequel.qualify(info_sch_sch, s) : Sequel[s]} 449 table_id = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:objects])).where(:name => tn).select_map(:object_id).first 450 451 identity_cols = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:columns])). 452 where(:object_id=>table_id, :is_identity=>true). 453 select_map(:name) 454 455 pk_index_id = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:sysindexes])). 456 where(:id=>table_id, :indid=>1..254){{(status & 2048)=>2048}}. 457 get(:indid) 458 pk_cols = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:sysindexkeys]).as(:sik)). 459 join(inf_sch_qual.call(Sequel[:sys][:syscolumns]).as(:sc), :id=>:id, :colid=>:colid). 460 where{{sik[:id]=>table_id, sik[:indid]=>pk_index_id}}. 461 select_order_map{sc[:name]} 462 463 ds = metadata_dataset.from(inf_sch_qual.call(Sequel[:information_schema][:tables]).as(:t)). 464 join(inf_sch_qual.call(Sequel[:information_schema][:columns]).as(:c), :table_catalog=>:table_catalog, 465 :table_schema => :table_schema, :table_name => :table_name). 466 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)]}. 467 where{{c[:table_name]=>tn}} 468 469 if schema = opts[:schema] 470 ds = ds.where{{c[:table_schema]=>schema}} 471 end 472 473 ds.map do |row| 474 if row[:primary_key] = pk_cols.include?(row[:column]) 475 row[:auto_increment] = identity_cols.include?(row[:column]) 476 end 477 row[:allow_null] = row[:allow_null] == 'YES' ? true : false 478 row[:default] = nil if blank_object?(row[:default]) 479 row[:type] = if row[:db_type] =~ /number|numeric|decimal/i && row[:scale] == 0 480 :integer 481 else 482 schema_column_type(row[:db_type]) 483 end 484 row[:max_length] = row[:max_chars] if row[:type] == :string && row[:max_chars] >= 0 485 [m.call(row.delete(:column)), row] 486 end 487 end
Set the mssql_unicode_strings
settings from the given options.
# File lib/sequel/adapters/shared/mssql.rb 490 def set_mssql_unicode_strings 491 @mssql_unicode_strings = typecast_value_boolean(@opts.fetch(:mssql_unicode_strings, true)) 492 end
MSSQL
has both datetime and timestamp classes, most people are going to want datetime
# File lib/sequel/adapters/shared/mssql.rb 496 def type_literal_generic_datetime(column) 497 :datetime 498 end
MSSQL
uses varbinary(max) type for blobs
# File lib/sequel/adapters/shared/mssql.rb 506 def type_literal_generic_file(column) 507 :'varbinary(max)' 508 end
MSSQL
doesn't have a true boolean class, so it uses bit
# File lib/sequel/adapters/shared/mssql.rb 501 def type_literal_generic_trueclass(column) 502 :bit 503 end
Handle clustered and nonclustered unique constraints
# File lib/sequel/adapters/shared/mssql.rb 511 def unique_constraint_sql_fragment(opts) 512 add_clustered_sql_fragment(super, opts) 513 end
MSSQL
supports views with check option, but not local.
# File lib/sequel/adapters/shared/mssql.rb 516 def view_with_check_option_support 517 true 518 end