Class: ActiveRecord::ConnectionAdapters::SQLServerAdapter

Inherits:
AbstractAdapter
  • Object
show all
Defined in:
lib/active_record/connection_adapters/sqlserver_adapter.rb

Overview

In ADO mode, this adapter will ONLY work on Windows systems, since it relies on Win32OLE, which, to my knowledge, is only available on Windows.

This mode also relies on the ADO support in the DBI module. If you are using the one-click installer of Ruby, then you already have DBI installed, but the ADO module is NOT installed. You will need to get the latest source distribution of Ruby-DBI from ruby-dbi.sourceforge.net/ unzip it, and copy the file src/lib/dbd_ado/ADO.rb to X:/Ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb (you will more than likely need to create the ADO directory). Once you’ve installed that file, you are ready to go.

In ODBC mode, the adapter requires the ODBC support in the DBI module which requires the Ruby ODBC module. Ruby ODBC 0.996 was used in development and testing, and it is available at www.ch-werner.de/rubyodbc/

Options:

  • :mode – ADO or ODBC. Defaults to ADO.

  • :username – Defaults to sa.

  • :password – Defaults to empty string.

  • :windows_auth – Defaults to “User ID=#username;Password=#password”

ADO specific options:

  • :host – Defaults to localhost.

  • :database – The name of the database. No default, must be provided.

  • :windows_auth – Use windows authentication instead of username/password.

ODBC specific options:

  • :dsn – Defaults to nothing.

ADO code tested on Windows 2000 and higher systems, running ruby 1.8.2 (2004-07-29) [i386-mswin32], and SQL Server 2000 SP3.

ODBC code tested on a Fedora Core 4 system, running FreeTDS 0.63, unixODBC 2.2.11, Ruby ODBC 0.996, Ruby DBI 0.0.23 and Ruby 1.8.2.

Linux strongmad 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:55:56 EDT 2005 i686 i686 i386 GNU/Linux

Instance Method Summary collapse

Constructor Details

#initialize(connection, logger, connection_options = nil) ⇒ SQLServerAdapter 



276
277
278
279
280
281
282
283
284
285
286
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 276

def initialize(connection, logger, connection_options=nil)
  super(connection, logger)
  @connection_options = connection_options
  if database_version =~ /(2000|2005) - (\d+)\./  
    @database_version_year = $1.to_i
    @database_version_major = $2.to_i
  else
    raise "Currently, only 2000 and 2005 are supported versions"
  end

end

Instance Method Details

#active? ⇒ Boolean

Returns true if the connection is active.



347
348
349
350
351
352
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 347

def active?
  @connection.execute("SELECT 1").finish
  true
rescue DBI::DatabaseError, DBI::InterfaceError
  false
end

#adapter_name ⇒ Object 



310
311
312
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 310

def adapter_name
  'SQLServer'
end

#add_column(table_name, column_name, type, options = {}) ⇒ Object 



730
731
732
733
734
735
736
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 730

def add_column(table_name, column_name, type, options = {})
  add_column_sql = "ALTER TABLE #{table_name} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
  add_column_options!(add_column_sql, options)
  # TODO: Add support to mimic date columns, using constraints to mark them as such in the database
  # add_column_sql << " CONSTRAINT ck__#{table_name}__#{column_name}__date_only CHECK ( CONVERT(CHAR(12), #{quote_column_name(column_name)}, 14)='00:00:00:000' )" if type == :date
  execute(add_column_sql)
end

#add_limit_offset!(sql, options) ⇒ Object 



553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 553

def add_limit_offset!(sql, options)
  if options[:offset]
    raise ArgumentError, "offset should have a limit" unless options[:limit]
    unless options[:offset].kind_of?Integer
      if options[:offset] =~ /^\d+$/
        options[:offset] = options[:offset].to_i
      else
        raise ArgumentError, "offset should be an integer"
      end
    end
  end

  if options[:limit] && !(options[:limit].kind_of?Integer)
    # is it just a string which should be an integer?
    if options[:limit] =~ /^\d+$/
      options[:limit] = options[:limit].to_i
    else
      raise ArgumentError, "limit should be an integer"
    end
  end

  if options[:limit] and options[:offset]
    total_rows = @connection.select_all("SELECT count(*) as TotalRows from (#{sql.gsub(/\bSELECT(\s+DISTINCT)?\b/i, "SELECT#{$1} TOP 1000000000")}) tally")[0][:TotalRows].to_i
    if (options[:limit] + options[:offset]) >= total_rows
      options[:limit] = (total_rows - options[:offset] >= 0) ? (total_rows - options[:offset]) : 0
    end

    # Wrap the SQL query in a bunch of outer SQL queries that emulate proper LIMIT,OFFSET support.
    sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i, "SELECT * FROM (SELECT TOP #{options[:limit]} * FROM (SELECT#{$1} TOP #{options[:limit] + options[:offset]}")
    sql << ") AS tmp1"

    if options[:order]
      order = options[:order].split(',').map do |field|
        order_by_column, order_direction = field.split(" ")
        order_by_column = quote_column_name(order_by_column)

        # Investigate the SQL query to figure out if the order_by_column has been renamed.
        if sql =~ /#{Regexp.escape(order_by_column)} AS (t\d_r\d\d?)/
          # Fx "[foo].[bar] AS t4_r2" was found in the SQL. Use the column alias (ie 't4_r2') for the subsequent orderings
          order_by_column = $1
        elsif order_by_column =~ /\w+\.\[?(\w+)\]?/
          order_by_column = $1
        else
          # It doesn't appear that the column name has been renamed as part of the query. Use just the column
          # name rather than the full identifier for the outer queries.
          order_by_column = order_by_column.split('.').last
        end

        # Put the column name and eventual direction back together
        [order_by_column, order_direction].join(' ').strip
      end.join(', ')

      sql << " ORDER BY #{change_order_direction(order)}) AS tmp2 ORDER BY #{order}"
    else
      sql << ") AS tmp2"
    end
  elsif sql !~ /^\s*SELECT (@@|COUNT\()/i
    sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i) do
      "SELECT#{$1} TOP #{options[:limit]}"
    end unless options[:limit].nil? || options[:limit] < 1
  end
end

#add_lock!(sql, options) ⇒ Object

Appends a locking clause to an SQL statement. This method modifies the sql parameter.

# SELECT * FROM suppliers FOR UPDATE
add_lock! 'SELECT * FROM suppliers', :lock => true
add_lock! 'SELECT * FROM suppliers', :lock => ' WITH(HOLDLOCK, ROWLOCK)'

blog.sqlauthority.com/2007/04/27/sql-server-2005-locking-hints-and-examples/



646
647
648
649
650
651
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 646

def add_lock!(sql, options)
  case lock = options[:lock]
  when true then sql << "WITH(HOLDLOCK, ROWLOCK) "
  when String then sql << "#{lock} "
  end
end

#add_order_by_for_association_limiting!(sql, options) ⇒ Object

add_limit_offset!(sql, options)



616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 616

def add_order_by_for_association_limiting!(sql, options)
  return sql if options[:order].blank?

  # Strip any ASC or DESC from the orders for the select list
  # Build fields and order arrays
  # e.g.: options[:order] = 'table.[id], table2.[col2] desc'
  # fields = ['min(table.[id]) AS id', 'min(table2.[col2]) AS col2']
  # order = ['id', 'col2 desc']
  fields = []
  order = []
  options[:order].split(/\s*,\s*/).each do |str|
    # regex matches 'table_name.[column_name] asc' or 'column_name' ('table_name.', 'asc', '[', and ']' are optional)
    # $1 = 'table_name.[column_name]'
    # $2 = 'column_name'
    # $3 = ' asc'
    str =~ /((?:\w+\.)?\[?(\w+)\]?)(\s+asc|\s+desc)?/i
    fields << "MIN(#{$1}) AS #{$2}"
    order << "#{$2}#{$3}"
  end

  sql.gsub!(/(.+?) FROM/, "\\1, #{fields.join(',')} FROM")
  sql << " ORDER BY #{order.join(',')}"
end

#begin_db_transaction ⇒ Object 



482
483
484
485
486
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 482

def begin_db_transaction
  @connection["AutoCommit"] = false
rescue Exception => e
  @connection["AutoCommit"] = true
end

#change_column(table_name, column_name, type, options = {}) ⇒ Object

:nodoc:



746
747
748
749
750
751
752
753
754
755
756
757
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 746

def change_column(table_name, column_name, type, options = {}) #:nodoc:
  sql = "ALTER TABLE #{table_name} ALTER COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
  sql << " NOT NULL" if options[:null] == false
  sql_commands = [sql]
  if options_include_default?(options)
    remove_default_constraint(table_name, column_name)
    sql_commands << "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote(options[:default], options[:column])} FOR #{quote_column_name(column_name)}"
  end
  sql_commands.each {|c|
    execute(c)
  }
end

#change_column_default(table_name, column_name, default) ⇒ Object 



759
760
761
762
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 759

def change_column_default(table_name, column_name, default)
  remove_default_constraint(table_name, column_name)
  execute "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote(default, column_name)} FOR #{quote_column_name(column_name)}"
end

#columns(table_name, name = nil) ⇒ Object 



388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 388

def columns(table_name, name = nil)
  return [] if table_name.blank?
  table_names = table_name.to_s.split('.')
  table_name = table_names[-1]
  table_name = table_name.gsub(/[\[\]]/, '')
  db_name = "#{table_names[0]}." if table_names.length==3
  sql = %{
    SELECT
    columns.COLUMN_NAME as name,
    columns.DATA_TYPE as type,
    CASE
      WHEN columns.COLUMN_DEFAULT = '(null)' OR columns.COLUMN_DEFAULT = '(NULL)' THEN NULL
      ELSE columns.COLUMN_DEFAULT
    END default_value,
    columns.NUMERIC_SCALE as numeric_scale,
    columns.NUMERIC_PRECISION as numeric_precision,
    COL_LENGTH(columns.TABLE_NAME, columns.COLUMN_NAME) as length,
    CASE
      WHEN constraint_column_usage.constraint_name IS NULL THEN NULL
      ELSE 1
    END is_primary_key,
    CASE
      WHEN columns.IS_NULLABLE = 'YES' THEN 1
      ELSE NULL
    end is_nullable,
    CASE
      WHEN COLUMNPROPERTY(OBJECT_ID(columns.TABLE_NAME), columns.COLUMN_NAME, 'IsIdentity') = 0 THEN NULL
      ELSE 1
    END is_identity
    FROM #{db_name}INFORMATION_SCHEMA.COLUMNS columns
    LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS primary_key_constraints ON (
      primary_key_constraints.table_name = columns.table_name
      AND primary_key_constraints.constraint_type = 'PRIMARY KEY'
    )
    LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE constraint_column_usage ON (
      constraint_column_usage.table_name = primary_key_constraints.table_name
      AND constraint_column_usage.column_name = columns.column_name
    )
    WHERE columns.TABLE_NAME = '#{table_name}'
    ORDER BY columns.ordinal_position
  }.gsub(/[ \t\r\n]+/,' ')
  result = select(sql, name, true)
  result.collect do |column_info|
    # Remove brackets and outer quotes (if quoted) of default value returned by db, i.e:
    #   "(1)" => "1", "('1')" => "1", "((-1))" => "-1", "('(-1)')" => "(-1)"
    #   Unicode strings will be prefixed with an N. Remove that too.
    column_info.symbolize_keys!
    column_info[:default_value] = column_info[:default_value].match(/\A\(+N?'?(.*?)'?\)+\Z/)[1] if column_info[:default_value]
    SQLServerColumn.new(column_info)
  end
end

#commit_db_transaction ⇒ Object 



488
489
490
491
492
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 488

def commit_db_transaction
  @connection.commit
ensure
  @connection["AutoCommit"] = true
end

#create_database(name) ⇒ Object

truncate



701
702
703
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 701

def create_database(name)
  execute "CREATE DATABASE #{name}"
end

#current_database ⇒ Object 



705
706
707
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 705

def current_database
  @connection.select_one("SELECT DB_NAME()")[0]
end

#database_version ⇒ Object 



314
315
316
317
318
319
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 314

def database_version
  # returns string such as:
  # "Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) \n\tMay  3 2005 23:18:38 \n\tCopyright (c) 1988-2003 Microsoft Corporation\n\tEnterprise Edition on Windows NT 5.2 (Build 3790: )\n"
  # "Microsoft SQL Server 2005 - 9.00.3215.00 (Intel X86) \n\tDec  8 2007 18:51:32 \n\tCopyright (c) 1988-2005 Microsoft Corporation\n\tStandard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)\n"
  return select_value("SELECT @@version")
end

#disconnect! ⇒ Object

Disconnects from the database



365
366
367
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 365

def disconnect!
  @connection.disconnect rescue nil
end

#drop_database(name) ⇒ Object 



675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 675

def drop_database(name)
  retry_count = 0
  max_retries = 1
  begin
    execute "DROP DATABASE #{name}"
  rescue ActiveRecord::StatementInvalid => err
    # Remove existing connections and rollback any transactions if we received the message
    #  'Cannot drop the database 'test' because it is currently in use'
    if err.message =~ /because it is currently in use/
      raise if retry_count >= max_retries
      retry_count += 1
      remove_database_connections_and_rollback(name)
      retry
    else
      raise
    end
  end
end

#empty_insert_statement(table_name) ⇒ Object 



440
441
442
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 440

def empty_insert_statement(table_name)
  "INSERT INTO #{table_name} DEFAULT VALUES"
end

#execute(sql, name = nil) ⇒ Object 



464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 464

def execute(sql, name = nil)
  if sql =~ /^\s*INSERT/i && (table_name = query_requires_identity_insert?(sql))
    log(sql, name) do
      with_identity_insert_enabled(table_name) do
        @connection.execute(sql) do |handle|
          yield(handle) if block_given?
        end
      end
    end
  else
    log(sql, name) do
      @connection.execute(sql) do |handle|
        yield(handle) if block_given?
      end
    end
  end
end

#indexes(table_name, name = nil) ⇒ Object 



719
720
721
722
723
724
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 719

def indexes(table_name, name = nil)
  ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = false
  __indexes(table_name, name)
ensure
  ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = true
end

#insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) ⇒ Object 



444
445
446
447
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 444

def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
  set_utf8_values!(sql)
  super || select_value("SELECT SCOPE_IDENTITY() AS Ident")
end

#native_database_types ⇒ Object 



288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 288

def native_database_types
  # support for varchar(max) and varbinary(max) for text and binary cols if our version is 9 (2005)
  txt = @database_version_major >= 9 ? "varchar(max)"   : "text"
  
  # TODO:  Need to verify image column works correctly with 2000 if string_to_binary stores a hex string
  bin = @database_version_major >= 9 ? "varbinary(max)" : "image"
  {
    :primary_key => "int NOT NULL IDENTITY(1, 1) PRIMARY KEY",
    :string      => { :name => "varchar", :limit => 255  },
    :text        => { :name =>  txt },
    :integer     => { :name => "int" },
    :float       => { :name => "float", :limit => 8 },
    :decimal     => { :name => "decimal" },
    :datetime    => { :name => "datetime" },
    :timestamp   => { :name => "datetime" },
    :time        => { :name => "datetime" },
    :date        => { :name => "datetime" },
    :binary      => { :name =>  bin },
    :boolean     => { :name => "bit"}
  }
end

#quote(value, column = nil) ⇒ Object 



500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 500

def quote(value, column = nil)
  return value.quoted_id if value.respond_to?(:quoted_id)

  case value
    when TrueClass             then '1'
    when FalseClass            then '0'

    when String, ActiveSupport::Multibyte::Chars
      value = value.to_s

      # for binary columns, don't quote the result of the string to binary
      return column.class.string_to_binary(value) if column && column.type == :binary && column.class.respond_to?(:string_to_binary)
      super
    else
      if value.acts_like?(:time)
        "'#{value.strftime("%Y%m%d %H:%M:%S")}'"
      elsif value.acts_like?(:date)
        "'#{value.strftime("%Y%m%d")}'"
      else
        super
      end
  end
end

#quote_column_name(identifier) ⇒ Object

Quotes the given column identifier.

Examples

quote_column_name('foo') # => '[foo]'
quote_column_name(:foo) # => '[foo]'
quote_column_name('foo.bar') # => '[foo].[bar]'



547
548
549
550
551
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 547

def quote_column_name(identifier)
  identifier.to_s.split('.').collect do |name|
    "[#{name}]"          
  end.join(".")
end

#quote_string(string) ⇒ Object 



524
525
526
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 524

def quote_string(string)
  string.gsub(/\'/, "''")
end

#quote_table_name(name) ⇒ Object 



528
529
530
531
532
533
534
535
536
537
538
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 528

def quote_table_name(name)
  name_split_on_dots = name.to_s.split('.')

  if name_split_on_dots.length == 3
    # name is on the form "foo.bar.baz"
    "[#{name_split_on_dots[0]}].[#{name_split_on_dots[1]}].[#{name_split_on_dots[2]}]"
  else
    super(name)
  end

end

#reconnect! ⇒ Object

Reconnects to the database, returns false if no connection could be made.



355
356
357
358
359
360
361
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 355

def reconnect!
  disconnect!
  @connection = DBI.connect(*@connection_options)
rescue DBI::DatabaseError => e
  @logger.warn "#{adapter_name} reconnection failed: #{e.message}" if @logger
  false
end

#recreate_database(name) ⇒ Object 



653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 653

def recreate_database(name)      
  # Switch to another database or we'll receive a "Database in use" error message.
  existing_database = current_database.to_s
  if name.to_s == existing_database
    # The master database should be available on all SQL Server instances, use that
    execute 'USE master' 
  end

  # Recreate the database
  drop_database(name)
  create_database(name)

  # Switch back to the database if we switched away from it above
  execute "USE #{existing_database}" if name.to_s == existing_database 
end

#remove_check_constraints(table_name, column_name) ⇒ Object 



779
780
781
782
783
784
785
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 779

def remove_check_constraints(table_name, column_name)
  # TODO remove all constraints in single method
  constraints = select "SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '#{table_name}' and COLUMN_NAME = '#{column_name}'"
  constraints.each do |constraint|
    execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["CONSTRAINT_NAME"]}"
  end
end

#remove_column(table_name, column_name) ⇒ Object 



764
765
766
767
768
769
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 764

def remove_column(table_name, column_name)
  remove_check_constraints(table_name, column_name)
  remove_default_constraint(table_name, column_name)
  remove_indexes(table_name, column_name)
  execute "ALTER TABLE [#{table_name}] DROP COLUMN #{quote_column_name(column_name)}"
end

#remove_database_connections_and_rollback(name) ⇒ Object 



669
670
671
672
673
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 669

def remove_database_connections_and_rollback(name)
  # This should disconnect all other users and rollback any transactions for SQL 2000 and 2005
  # http://sqlserver2000.databases.aspfaq.com/how-do-i-drop-a-sql-server-database.html
  execute "ALTER DATABASE #{name} SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
end

#remove_default_constraint(table_name, column_name) ⇒ Object 



771
772
773
774
775
776
777
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 771

def remove_default_constraint(table_name, column_name)
  constraints = select "SELECT def.name FROM sysobjects def, syscolumns col, sysobjects tab WHERE col.cdefault = def.id AND col.name = '#{column_name}' AND tab.name = '#{table_name}' AND col.id = tab.id"

  constraints.each do |constraint|
    execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["name"]}"
  end
end

#remove_index(table_name, options = {}) ⇒ Object 



793
794
795
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 793

def remove_index(table_name, options = {})
  execute "DROP INDEX #{table_name}.#{quote_column_name(index_name(table_name, options))}"
end

#remove_indexes(table_name, column_name) ⇒ Object 



787
788
789
790
791
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 787

def remove_indexes(table_name, column_name)
  __indexes(table_name).select {|idx| idx.columns.include? column_name }.each do |idx|
    remove_index(table_name, {:name => idx.name})
  end
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object 



738
739
740
741
742
743
744
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 738

def rename_column(table_name, column_name, new_column_name)
  if columns(table_name).find{|c| c.name.to_s == column_name.to_s}
    execute "EXEC sp_rename '#{table_name}.#{column_name}', '#{new_column_name}'"
  else
    raise ActiveRecordError, "No such column: #{table_name}.#{column_name}"
  end
end

#rename_table(name, new_name) ⇒ Object 



726
727
728
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 726

def rename_table(name, new_name)
  execute "EXEC sp_rename '#{name}', '#{new_name}'"
end

#rollback_db_transaction ⇒ Object 



494
495
496
497
498
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 494

def rollback_db_transaction
  @connection.rollback
ensure
  @connection["AutoCommit"] = true
end

#select_rows(sql, name = nil) ⇒ Object 



369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 369

def select_rows(sql, name = nil)
  rows = []
  repair_special_columns(sql)
  log(sql, name) do
    @connection.select_all(sql) do |row|
      record = []
      row.each do |col|
        if col.is_a? DBI::Timestamp
          record << col.to_time
        else
          record << col
        end
      end
      rows << record
    end
  end
  rows
end

#supports_migrations? ⇒ Boolean

:nodoc:



321
322
323
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 321

def supports_migrations? #:nodoc:
  true
end

#tables(name = nil) ⇒ Object 



709
710
711
712
713
714
715
716
717
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 709

def tables(name = nil)
  execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'", name) do |sth|
    result = sth.inject([]) do |tables, field|
      table_name = field[0]
      tables << table_name unless table_name == 'dtproperties'
      tables
    end
  end
end

#truncate(table_name) ⇒ Object

Clear the given table and reset the table’s id to 1 Argument:

table_name

(String) Name of the table to be cleared and reset



697
698
699
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 697

def truncate(table_name)
  execute("TRUNCATE TABLE #{table_name}; DBCC CHECKIDENT ('#{table_name}', RESEED, 1)")
end

#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object

:nodoc:



325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 325

def type_to_sql(type, limit = nil, precision = nil, scale = nil) #:nodoc:
  # Remove limit for data types which do not require it
  # Valid:   ALTER TABLE sessions ALTER COLUMN [data] varchar(max)
  # Invalid: ALTER TABLE sessions ALTER COLUMN [data] varchar(max)(16777215)
  limit = nil if %w{text varchar(max) nvarchar(max) ntext varbinary(max) image}.include?(native_database_types[type.to_sym][:name])

  return super unless type.to_s == 'integer'

  if limit.nil?
    'integer'
  elsif limit > 4
    'bigint'
  elsif limit < 3
    'smallint'
  else
    'integer'
  end
end

#update_sql(sql, name = nil) ⇒ Object 



449
450
451
452
453
454
455
456
457
458
459
460
461
462
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 449

def update_sql(sql, name = nil)
  set_utf8_values!(sql)
  auto_commiting = @connection["AutoCommit"]
  begin
    begin_db_transaction if auto_commiting
    execute(sql, name)
    affected_rows = select_value("SELECT @@ROWCOUNT AS AffectedRows")
    commit_db_transaction if auto_commiting
    affected_rows
  rescue
    rollback_db_transaction if auto_commiting
    raise
  end
end