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.

ADO specific options:

  • :host – Defaults to localhost.

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

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

Methods inherited from AbstractAdapter

#add_column, #add_index, #add_limit!, #change_column, #change_column_default, #create_table, #drop_table, #initialize, #initialize_schema_information, #remove_column, #remove_index, #rename_column, #reset_runtime, #structure_dump, #supports_migrations?, #transaction, #type_to_sql

Constructor Details

This class inherits a constructor from ActiveRecord::ConnectionAdapters::AbstractAdapter

Instance Method Details

#adapter_name ⇒ Object 



181
182
183
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 181

def adapter_name
  'SQLServer'
end

#add_limit_offset!(sql, options) ⇒ Object 



300
301
302
303
304
305
306
307
308
309
310
311
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 300

def add_limit_offset!(sql, options)
  if options.has_key?(:limit) and options.has_key?(:offset) and !options[:limit].nil? and !options[:offset].nil?
    options[:order] ||= "id ASC"
    total_rows = @connection.select_all("SELECT count(*) as TotalRows from #{get_table_name(sql)}")[0][:TotalRows].to_i
    if (options[:limit] + options[:offset]) > total_rows
      options[:limit] = (total_rows - options[:offset] > 0) ? (total_rows - options[:offset]) : 1
    end
    sql.gsub!(/SELECT/i, "SELECT * FROM ( SELECT TOP #{options[:limit]} * FROM ( SELECT TOP #{options[:limit] + options[:offset]}")<<" ) AS tmp1 ORDER BY #{change_order_direction(options[:order])} ) AS tmp2 ORDER BY #{options[:order]}"
  else
    sql.gsub!(/SELECT/i, "SELECT TOP #{options[:limit]}") unless options[:limit].nil?
  end
end

#begin_db_transaction ⇒ Object 



256
257
258
259
260
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 256

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

#columns(table_name, name = nil) ⇒ Object 



195
196
197
198
199
200
201
202
203
204
205
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 195

def columns(table_name, name = nil)
  sql = "SELECT COLUMN_NAME as ColName, COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType, COL_LENGTH('#{table_name}', COLUMN_NAME) as Length, COLUMNPROPERTY(OBJECT_ID('#{table_name}'), COLUMN_NAME, 'IsIdentity') as IsIdentity, NUMERIC_SCALE as Scale FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '#{table_name}'"
  # Comment out if you want to have the Columns select statment logged.
  # Personnally, I think it adds unneccessary 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 { |field| columns << ColumnWithIdentity.new(field[:ColName], field[:DefaultValue].to_s.gsub!(/[()\']/,"") =~ /null/ ? nil : field[:DefaultValue], "#{field[:ColType]}(#{field[:Length]})", field[:IsIdentity] == 1 ? true : false, field[:Scale]) }
  columns
end

#commit_db_transaction ⇒ Object 



262
263
264
265
266
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 262

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

#create_database(name) ⇒ Object 



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

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

#drop_database(name) ⇒ Object 



318
319
320
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 318

def drop_database(name)
  execute "DROP DATABASE #{name}"
end

#execute(sql, name = nil) ⇒ Object 



238
239
240
241
242
243
244
245
246
247
248
249
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 238

def execute(sql, name = nil)
  if sql =~ /^INSERT/i
    insert(sql, name)
  elsif sql =~ /^UPDATE|DELETE/i
    log(sql, name) do
      @connection.execute(sql)
      retVal = select_one("SELECT @@ROWCOUNT AS AffectedRows")["AffectedRows"]
    end
  else
    log(sql, name) { @connection.execute(sql) }
  end
end

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



207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 207

def insert(sql, name = nil, pk = nil, id_value = nil)
  begin
    table_name = get_table_name(sql)
    col = get_identity_column(table_name)
    ii_enabled = false

    if col != nil
      if query_contains_identity_column(sql, col)
        begin
          execute enable_identity_insert(table_name, true)
          ii_enabled = true
        rescue Exception => e
          raise ActiveRecordError, "IDENTITY_INSERT could not be turned ON"
        end
      end
    end
    log(sql, name) do
      @connection.execute(sql)
      select_one("SELECT @@IDENTITY AS Ident")["Ident"]
    end
  ensure
    if ii_enabled
      begin
        execute enable_identity_insert(table_name, false)
      rescue Exception => e
        raise ActiveRecordError, "IDENTITY_INSERT could not be turned OFF"
      end
    end
  end
end

#native_database_types ⇒ Object 



165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 165

def native_database_types
  {
    :primary_key => "int NOT NULL IDENTITY(1, 1) PRIMARY KEY",
    :string      => { :name => "varchar(255)" },
    :text        => { :name => "text(16)" },
    :integer     => { :name => "int(4)", :limit => 11 },
    :float       => { :name => "float(8)" },
    :datetime    => { :name => "datetime(8)" },
    :timestamp   => { :name => "datetime(8)" },
    :time        => { :name => "datetime(8)" },
    :date        => { :name => "datetime(8)" },
    :binary      => { :name => "image(16)" },
    :boolean     => { :name => "bit(1)" }
  }
end

#quote(value, column = nil) ⇒ Object 



274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 274

def quote(value, column = nil)
  case value
    when String                
      if column && column.type == :binary
        "'#{quote_string(column.string_to_binary(value))}'"
      else
        "'#{quote_string(value)}'"
      end
    when NilClass              then "NULL"
    when TrueClass             then '1'
    when FalseClass            then '0'
    when Float, Fixnum, Bignum then value.to_s
    when Date                  then "'#{value.to_s}'" 
    when Time, DateTime        then "'#{value.strftime("%Y-%m-%d %H:%M:%S")}'"
    else                            "'#{quote_string(value.to_yaml)}'"
  end
end

#quote_column_name(name) ⇒ Object 



296
297
298
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 296

def quote_column_name(name)
  "[#{name}]"
end

#quote_string(string) ⇒ Object 



292
293
294
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 292

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

#recreate_database(name) ⇒ Object 



313
314
315
316
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 313

def recreate_database(name)
  drop_database(name)
  create_database(name)
end

#rollback_db_transaction ⇒ Object 



268
269
270
271
272
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 268

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

#select_all(sql, name = nil) ⇒ Object 



185
186
187
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 185

def select_all(sql, name = nil)
  select(sql, name)
end

#select_one(sql, name = nil) ⇒ Object 



189
190
191
192
193
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 189

def select_one(sql, name = nil)
  add_limit!(sql, nil)
  result = select(sql, name)
  result.nil? ? nil : result.first
end

#update(sql, name = nil) ⇒ Object Also known as: delete 



251
252
253
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 251

def update(sql, name = nil)
  execute(sql, name)
end