Class: ActiveRecord::ConnectionAdapters::SQLServerAdapter

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

Overview

This adapter will ONLY work on Windows systems, since it relies on Win32OLE, which, to my knowledge, is only available on Window.

It 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 need to create the ADO directory). Once you’ve installed that file, you are ready to go.

Options:

  • :host – Defaults to localhost

  • :username – Defaults to sa

  • :password – Defaults to nothing

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

Instance Method Summary collapse

Methods inherited from AbstractAdapter

#add_column, #create_table, #drop_table, #initialize, #initialize_schema_information, #remove_column, #reset_runtime, #structure_dump, #transaction

Constructor Details

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

Instance Method Details

#adapter_name ⇒ Object 



294
295
296
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 294

def adapter_name()
  'SqlServer'
end

#add_limit!(sql, limit) ⇒ Object 



256
257
258
259
260
261
262
263
264
265
266
267
268
269
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 256

def add_limit!(sql, limit)
  if sql =~ /LIMIT/i
    limit = sql.slice!(/LIMIT.*/).gsub(/LIMIT.(.*)$/, '\1')
  end
  if !limit.nil?
    limit_amount = limit.to_s.include?("OFFSET") ? get_offset_amount(limit) : Array.new([limit])
    order_by = sql.include?("ORDER BY") ? get_order_by(sql.sub(/.*ORDER\sBY./, "")) : nil
    if limit_amount.size == 2
      sql.gsub!(/SELECT/i, "SELECT * FROM ( SELECT TOP #{limit_amount[0]} * FROM ( SELECT TOP #{limit_amount[1]}")<<" ) AS tmp1 ORDER BY #{order_by[1]} ) AS tmp2 ORDER BY #{order_by[0]}"
    else
      sql.gsub!(/SELECT/i, "SELECT TOP #{limit_amount[0]}")
    end
  end
end

#begin_db_transaction ⇒ Object 



206
207
208
209
210
211
212
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 206

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

#columns(table_name, name = nil) ⇒ Object 



138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 138

def columns(table_name, name = nil)
  sql = "SELECT \nCOLUMN_NAME as ColName,\nCOLUMN_DEFAULT as DefaultValue,\nDATA_TYPE as ColType,\nCOL_LENGTH('\#{table_name}', COLUMN_NAME) as Length,\nCOLUMNPROPERTY(OBJECT_ID('\#{table_name}'), COLUMN_NAME, 'IsIdentity') as IsIdentity,\nNUMERIC_SCALE as Scale\nFROM INFORMATION_SCHEMA.columns\nWHERE TABLE_NAME = '\#{table_name}'\n"

  result = nil
  # Uncomment if you want to have the Columns select statment logged.
  # Personnally, I think it adds unneccessary SQL statement bloat to the log. 
  # If you do uncomment, make sure to comment the "result" line that follows
  log(sql, name, @connection) { |conn| result = conn.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 



214
215
216
217
218
219
220
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 214

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

#create_database(name) ⇒ Object 



280
281
282
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 280

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

#drop_database(name) ⇒ Object 



276
277
278
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 276

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

#execute(sql, name = nil) ⇒ Object 



284
285
286
287
288
289
290
291
292
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 284

def execute(sql, name = nil)
  if sql =~ /^INSERT/i
    insert(sql, name)
  else
    log(sql, name, @connection) do |conn|
      conn.execute(sql)
    end
  end
end

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



163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 163

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
          # Coulnd't turn on IDENTITY_INSERT
        end
      end
    end

    log(sql, name, @connection) do |conn|
      conn.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
        # Couldn't turn off IDENTITY_INSERT
      end
    end
  end
end

#quote(value, column = nil) ⇒ Object 



230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 230

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 (column && column.type == :boolean ? "'t'" : "1")
    when FalseClass            then (column && column.type == :boolean ? "'f'" : "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 



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

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

#quote_string(s) ⇒ Object 



248
249
250
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 248

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

#recreate_database(name) ⇒ Object 



271
272
273
274
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 271

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

#rollback_db_transaction ⇒ Object 



222
223
224
225
226
227
228
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 222

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

#select_all(sql, name = nil) ⇒ Object 



127
128
129
130
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 127

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

#select_one(sql, name = nil) ⇒ Object 



132
133
134
135
136
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 132

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 



199
200
201
202
 
# File 'lib/active_record/connection_adapters/sqlserver_adapter.rb', line 199

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