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

Returns a new instance of SQLServerAdapter.



294
295
296
297
298
299
300
301
302
303
304
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 294

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.

Returns:

  • (Boolean) 


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

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

#adapter_name ⇒ Object 



328
329
330
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 328

def adapter_name
  'SQLServer'
end

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



753
754
755
756
757
758
759
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 753

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 



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
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 571

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/



664
665
666
667
668
669
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 664

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)



634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 634

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 



500
501
502
503
504
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 500

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

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

:nodoc:



769
770
771
772
773
774
775
776
777
778
779
780
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 769

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 



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

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 



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
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 406

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

  # COL_LENGTH returns values that do not reflect how much data can be stored in certain data types.
  # COL_LENGTH returns -1 for varchar(max), nvarchar(max), and varbinary(max)
  # COL_LENGTH returns 16 for ntext, text, image types
  # My sessions.data column was varchar(max) and resulted in the following error:
  # Your session data is larger than the data column in which it is to be stored. You must increase the size of your data column if you intend to store large data.
  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,
    CASE
      WHEN columns.DATA_TYPE IN ('nvarchar') AND COL_LENGTH(columns.TABLE_NAME, columns.COLUMN_NAME) = -1 THEN 1073741823
      WHEN columns.DATA_TYPE IN ('varchar', 'varbinary') AND COL_LENGTH(columns.TABLE_NAME, columns.COLUMN_NAME) = -1 THEN 2147483647
      WHEN columns.DATA_TYPE IN ('ntext') AND COL_LENGTH(columns.TABLE_NAME, columns.COLUMN_NAME) = 16 THEN 1073741823
      WHEN columns.DATA_TYPE IN ('text', 'image') AND COL_LENGTH(columns.TABLE_NAME, columns.COLUMN_NAME) = 16 THEN 2147483647
      ELSE COL_LENGTH(columns.TABLE_NAME, columns.COLUMN_NAME) 
    END as length,
    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
    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 



506
507
508
509
510
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 506

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

#create_database(name) ⇒ Object

truncate



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

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

#current_database ⇒ Object 



723
724
725
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 723

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

#database_version ⇒ Object 



332
333
334
335
336
337
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 332

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



383
384
385
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 383

def disconnect!
  @connection.disconnect rescue nil
end

#drop_database(name) ⇒ Object 



693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 693

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 



458
459
460
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 458

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

#execute(sql, name = nil) ⇒ Object 



482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 482

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 



742
743
744
745
746
747
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 742

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 



462
463
464
465
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 462

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 



306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 306

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

#pk_and_sequence_for(table_name) ⇒ Object

Returns a table’s primary key and belonging sequence (not applicable to SQL server).



821
822
823
824
825
826
827
828
829
830
831
832
833
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 821

def pk_and_sequence_for(table_name)
  @connection["AutoCommit"] = false
  keys = []
  execute("EXEC sp_helpindex '#{table_name}'") do |handle|
    if handle.column_info.any?
      pk_index = handle.detect {|index| index[1] =~ /primary key/ }
      keys << pk_index[2] if pk_index
    end
  end
  keys.length == 1 ? [keys.first, nil] : nil
ensure
  @connection["AutoCommit"] = true
end

#quote(value, column = nil) ⇒ Object 



518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 518

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]'



565
566
567
568
569
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 565

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

#quote_string(string) ⇒ Object 



542
543
544
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 542

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

#quote_table_name(name) ⇒ Object 



546
547
548
549
550
551
552
553
554
555
556
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 546

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.



373
374
375
376
377
378
379
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 373

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 



671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 671

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 



802
803
804
805
806
807
808
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 802

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 



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

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 



687
688
689
690
691
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 687

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 



794
795
796
797
798
799
800
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 794

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 



816
817
818
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 816

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 



810
811
812
813
814
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 810

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 



761
762
763
764
765
766
767
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 761

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 



749
750
751
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 749

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

#rollback_db_transaction ⇒ Object 



512
513
514
515
516
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 512

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

#select_rows(sql, name = nil) ⇒ Object 



387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 387

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:

Returns:

  • (Boolean) 


339
340
341
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 339

def supports_migrations? #:nodoc:
  true
end

#table_exists?(table_name) ⇒ Boolean

Returns:

  • (Boolean) 


737
738
739
740
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 737

def table_exists?(table_name)
  #If the table is external, see if it has columns
  super(table_name) || (columns(table_name).size>0)
end

#tables(name = nil) ⇒ Object 



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

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



715
716
717
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 715

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:



343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 343

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 



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

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