Class ActiveRecord::ConnectionAdapters::SQLServerAdapter < ActiveRecord::ConnectionAdapters::AbstractAdapter

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 189
def initialize(connection, logger, connection_options=nil)
  super(connection, logger)
  @connection_options = connection_options
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 234
def active?
  @connection.execute("SELECT 1").finish
  true
rescue DBI::DatabaseError, DBI::InterfaceError
  false
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 211
def adapter_name
  'SQLServer'
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 448
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

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 364
def add_limit_offset!(sql, options)
  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
    sql.sub!(%r^\s*SELECT(\s+DISTINCT)?/, "SELECT * FROM (SELECT TOP #{options[:limit]} * FROM (SELECT#{$1} TOP #{options[:limit] + options[:offset]} ")
    sql << ") AS tmp1"
    if options[:order]
      options[:order] = options[:order].split(',').map do |field|
        parts = field.split(" ")
        tc = parts[0]
        if sql =~ %r\.\[/ and tc =~ %r\./ # if column quoting used in query
          tc.gsub!(%r\./, '\.\[')
          tc << '\]'
        end
        if sql =~ %r#{tc} AS (t\d_r\d\d?)/
          parts[0] = $1
        elsif parts[0] =~ %r\w+\.(\w+)/
          parts[0] = $1
        end
        parts.join(' ')
      end.join(', ')
      sql << " ORDER BY #{change_order_direction(options[:order])}) AS tmp2 ORDER BY #{options[:order]}"
    else
      sql << " ) AS tmp2"
    end
  elsif sql !~ %r^\s*SELECT (@@|COUNT\()/
    sql.sub!(%r^\s*SELECT(\s+DISTINCT)?/) do
      "SELECT#{$1} TOP #{options[:limit]}"
    end unless options[:limit].nil?
  end
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 326
def begin_db_transaction
  @connection["AutoCommit"] = false
rescue Exception => e
  @connection["AutoCommit"] = true
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 256
def columns(table_name, name = nil)
  return [] if table_name.blank?
  table_name = table_name.to_s if table_name.is_a?(Symbol)
  table_name = table_name.split('.')[-1] unless table_name.nil?
  table_name = table_name.gsub(%r[\[\]]/, '')
  sql = %Q{
    SELECT 
      cols.COLUMN_NAME as ColName,  
      cols.COLUMN_DEFAULT as DefaultValue,
      cols.NUMERIC_SCALE as numeric_scale,
      cols.NUMERIC_PRECISION as numeric_precision, 
      cols.DATA_TYPE as ColType, 
      cols.IS_NULLABLE As IsNullable,  
      COL_LENGTH(cols.TABLE_NAME, cols.COLUMN_NAME) as Length,  
      COLUMNPROPERTY(OBJECT_ID(cols.TABLE_NAME), cols.COLUMN_NAME, 'IsIdentity') as IsIdentity,  
      cols.NUMERIC_SCALE as Scale 
    FROM INFORMATION_SCHEMA.COLUMNS cols 
    WHERE cols.TABLE_NAME = '#{table_name}'   
  }
  # Comment out if you want to have the Columns select statment logged.
  # Personally, I think it adds unnecessary bloat to the log. 
  # If you do comment it out, make sure to un-comment the "result" line that follows
  result = log(sql, name) { @connection.select_all(sql) }
  #result = @connection.select_all(sql)
  columns = []
  result.each do |field|
    default = field[:DefaultValue].to_s.gsub!(%r[()\']/,"") =~ %rnull/ ? nil : field[:DefaultValue]
    if field[:ColType] =~ %rnumeric|decimal/
      type = "#{field[:ColType]}(#{field[:numeric_precision]},#{field[:numeric_scale]})"
    else
      type = "#{field[:ColType]}(#{field[:Length]})"
    end
    is_identity = field[:IsIdentity] == 1
    is_nullable = field[:IsNullable] == 'YES'
    columns << SQLServerColumn.new(field[:ColName], default, type, is_identity, is_nullable)
  end
  columns
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 332
def commit_db_transaction
  @connection.commit
ensure
  @connection["AutoCommit"] = true
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 407
def create_database(name)
  execute "CREATE DATABASE #{name}"
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 411
def current_database
  @connection.select_one("select DB_NAME()")[0]
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 252
def disconnect!
  @connection.disconnect rescue nil
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 403
def drop_database(name)
  execute "DROP DATABASE #{name}"
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 308
def execute(sql, name = nil)
  if sql =~ %r^\s*INSERT/ && (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

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 425
def indexes(table_name, name = nil)
  ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = false
  indexes = []        
  execute("EXEC sp_helpindex '#{table_name}'", name) do |sth|
    sth.each do |index| 
      unique = index[1] =~ %runique/
      primary = index[1] =~ %rprimary key/
      if !primary
        indexes << IndexDefinition.new(table_name, index[0], unique, index[2].split(", "))
      end
    end
  end
  indexes
  ensure
    ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = true
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 295
def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
  execute(sql, name)
  id_value || select_one("SELECT @@IDENTITY AS Ident")["Ident"]
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 194
def native_database_types
  {
    :primary_key => "int NOT NULL IDENTITY(1, 1) PRIMARY KEY",
    :string      => { :name => "varchar", :limit => 255  },
    :text        => { :name => "text" },
    :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 => "image"},
    :boolean     => { :name => "bit"}
  }
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 344
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 Time, DateTime        then "'#{value.strftime("%Y%m%d %H:%M:%S")}'"
    when Date                  then "'#{value.strftime("%Y%m%d")}'"
    else                       super
  end
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 360
def quote_column_name(name)
  "[#{name}]"
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 356
def quote_string(string)
  string.gsub(%r\'/, "''")
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 242
def reconnect!
  disconnect!
  @connection = DBI.connect(*@connection_options)
rescue DBI::DatabaseError => e
  @logger.warn "#{adapter_name} reconnection failed: #{e.message}" if @logger
  false
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 398
def recreate_database(name)
  drop_database(name)
  create_database(name)
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 485
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

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 471
def remove_column(table_name, column_name)
  remove_check_constraints(table_name, column_name)
  remove_default_constraint(table_name, column_name)
  execute "ALTER TABLE [#{table_name}] DROP COLUMN [#{column_name}]"
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 477
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

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 493
def remove_index(table_name, options = {})
  execute "DROP INDEX #{table_name}.#{quote_column_name(index_name(table_name, options))}"
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 456
def rename_column(table, column, new_column_name)
  execute "EXEC sp_rename '#{table}.#{column}', '#{new_column_name}'"
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 442
def rename_table(name, new_name)
  execute "EXEC sp_rename '#{name}', '#{new_name}'"
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 338
def rollback_db_transaction
  @connection.rollback
ensure
  @connection["AutoCommit"] = true
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 415
def tables(name = nil)
  execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'", name) do |sth|
    sth.inject([]) do |tables, field|
      table_name = field[0]
      tables << table_name unless table_name == 'dtproperties'
      tables
    end
  end
end

# File rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 300
def update(sql, name = nil)
  execute(sql, name) do |handle|
    handle.rows
  end || select_one("SELECT @@ROWCOUNT AS AffectedRows")["AffectedRows"]        
end