Class: ActiveRecord::ConnectionAdapters::MsSqlClientAdapter

Inherits:
AbstractAdapter
  • Object
show all
Includes:
MsSqlClient
Defined in:
lib/mssqlclient_adapter.rb

Instance Method Summary collapse

Constructor Details

#initialize(logger, connection_options = nil) ⇒ MsSqlClientAdapter

Returns a new instance of MsSqlClientAdapter.



20
21
22
23
24
25
26
 
# File 'lib/mssqlclient_adapter.rb', line 20

def initialize(logger, connection_options = nil)
  @connection_options, @logger = connection_options, logger
				@connection = self
  @runtime = @last_verification = 0
				@active = true
				@connection_options[:schema] ||= 'dbo'				
end

Instance Method Details

#active? ⇒ Boolean

Returns true if the connection is active.

Returns:

  • (Boolean) 


69
70
71
 
# File 'lib/mssqlclient_adapter.rb', line 69

def active?
  @active
end

#adapter_name ⇒ Object 



54
55
56
 
# File 'lib/mssqlclient_adapter.rb', line 54

def adapter_name
  'MsSqlClient'
end

#add_limit_offset!(sql, options) ⇒ Object 



180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
 
# File 'lib/mssqlclient_adapter.rb', line 180

def add_limit_offset!(sql, options)
				# STDOUT << "\n\nadd_limit_offset options = #{options.inspect}\n\n"
				# STDOUT << "add_limit_offset sql = #{sql}\n\n"

  if options[:limit] and options[:offset]
					sub_query = sql.gsub /\bSELECT\b(\s*DISTINCT)?/i do
"SELECT #{$1} TOP 1000000000"
					end

    total_rows = select("SELECT count(*) as TotalRows from (#{sub_query}) 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!(/^\s*SELECT/i, "SELECT * FROM (SELECT TOP #{options[:limit]} * FROM (SELECT TOP #{options[:limit] + options[:offset]} ")
    sql << ") AS tmp1"

					# (SELECT TOP 0 * FROM (SELECT TOP 10  DISTINCT posts.id

					sql.sub! /\(SELECT\sTOP\s(\d+)\s+DISTINCT/ do
"(SELECT DISTINCT TOP #{$1} "
					end

    if options[:order]
      options[:order] = options[:order].split(',').map do |field|
        parts = field.split(" ")
        tc = parts[0]
        if sql =~ /\.\[/ and tc =~ /\./ # if column quoting used in query
          tc.gsub!(/\./, '\\.\\[')
          tc << '\\]'
        end
        if sql =~ /#{tc} AS (t\d_r\d\d?)/
            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 !~ /^\s*SELECT (@@|COUNT\()/i
    sql.sub!(/^\s*SELECT([\s]*distinct)?/i) do
      "SELECT#{$1} TOP #{options[:limit]}"
    end unless options[:limit].nil?
  end
end

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

:nodoc:



278
279
280
281
282
283
284
285
286
287
 
# File 'lib/mssqlclient_adapter.rb', line 278

def change_column(table_name, column_name, type, options = {}) #:nodoc:
  sql_commands = ["ALTER TABLE #{table_name} ALTER COLUMN #{column_name} #{type_to_sql(type, options[:limit])}"]
  if options[:default]
    remove_default_constraint(table_name, column_name)
    sql_commands << "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{options[:default]} FOR #{column_name}"
  end
  sql_commands.each {|c|
    execute(c)
  }
end

#columns(table_name, name = nil) ⇒ Object 



97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/mssqlclient_adapter.rb', line 97

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?
  sql = "SELECT COLUMN_NAME as ColName, COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType, IS_NULLABLE as IsNullable, 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}' AND TABLE_SCHEMA = '#{@connection_options[:schema]}'"
				# puts sql
				# 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) { _select(sql) }
  #result = @connection.select_all(sql)
  columns = []
  result.each { |field| field.symbolize_keys!; columns << ColumnWithIdentityAndOrdinal.new(field[:ColName], field[:DefaultValue].to_s.gsub!(/[()\']/,"") =~ /null/ ? nil : field[:DefaultValue], "#{field[:ColType]}(#{field[:Length]})", field[:IsIdentity] == 1 ? true : false, field[:IsNullable] == 'YES', field[:Scale], field[:Ordinal]) }
				# puts columns.inspect
  columns
end

#connection_string ⇒ Object 



28
29
30
31
32
33
34
35
36
 
# File 'lib/mssqlclient_adapter.rb', line 28

def connection_string
	if @connection_options[:trusted]
		"Server=#{@connection_options[:host]};Database=#{@connection_options[:database]};Trusted_Connection=True;"
	elsif @connection_options[:connection_string]
		@connection_options[:connection_string]
	else
		"Data Source=#{@connection_options[:host]};Initial Catalog=#{@connection_options[:database]};User Id=#{@connection_options[:username]};Password=#{@connection_options[:password]};";
	end
end

#create_database(name) ⇒ Object 



236
237
238
 
# File 'lib/mssqlclient_adapter.rb', line 236

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

#current_database ⇒ Object 



240
241
242
 
# File 'lib/mssqlclient_adapter.rb', line 240

def current_database
  @connection.select_one("select DB_NAME() as [Name]")['Name']
end

#disconnect! ⇒ Object

Disconnects from the database



80
81
82
83
 
# File 'lib/mssqlclient_adapter.rb', line 80

def disconnect!
				@active = false
				true
end

#drop_database(name) ⇒ Object 



232
233
234
 
# File 'lib/mssqlclient_adapter.rb', line 232

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

#execute(sql, name = nil) ⇒ Object 



131
132
133
134
135
136
137
138
139
 
# File 'lib/mssqlclient_adapter.rb', line 131

def execute(sql, name = nil)
  if sql =~ /^\s*INSERT/i
    insert(sql, name)
  else
					# puts "About to execute"
					# sleep 1
    log(sql, name) { _execute(sql) }
  end
end

#indexes(table_name, name = nil) ⇒ Object 



254
255
256
257
258
259
260
261
262
263
264
 
# File 'lib/mssqlclient_adapter.rb', line 254

def indexes(table_name, name = nil)
  indexes = []
  select("EXEC sp_helpindex #{table_name}", name).each do |index| 
    unique = index['index_description'] =~ /unique/
    primary = index['index_description'] =~ /primary key/
    if !primary
      indexes << IndexDefinition.new(table_name, index['index_name'], unique, index['index_keys'].split(", "))
    end
  end
  indexes
end

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



114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/mssqlclient_adapter.rb', line 114

def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
				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)
	sql = wrap_identity_insert(table_name, sql)
					end
				end

				log(sql, name) do
					new_id = _insert(sql)
					id_value || new_id
				end
end

#native_database_types ⇒ Object 



38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
# File 'lib/mssqlclient_adapter.rb', line 38

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 },
    :datetime    => { :name => "datetime" },
    :timestamp   => { :name => "datetime" },
    :time        => { :name => "datetime" },
    :date        => { :name => "datetime" },
    :binary      => { :name => "image"},
    :boolean     => { :name => "bit"}
  }
end

#quote(value, column = nil) ⇒ Object 



146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
 
# File 'lib/mssqlclient_adapter.rb', line 146

def quote(value, column = nil)
  case value
    when String
      if column && column.type == :binary && column.class.respond_to?(:string_to_binary)
        "'#{quote_string(column.class.string_to_binary(value))}'"
      else
        "N'#{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 



176
177
178
 
# File 'lib/mssqlclient_adapter.rb', line 176

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

#quote_string(string) ⇒ Object 



164
165
166
 
# File 'lib/mssqlclient_adapter.rb', line 164

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

#quoted_false ⇒ Object 



172
173
174
 
# File 'lib/mssqlclient_adapter.rb', line 172

def quoted_false
  "0"
end

#quoted_true ⇒ Object 



168
169
170
 
# File 'lib/mssqlclient_adapter.rb', line 168

def quoted_true
  "1"
end

#reconnect! ⇒ Object

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



74
75
76
 
# File 'lib/mssqlclient_adapter.rb', line 74

def reconnect!
				@active = true
end

#recreate_database(name) ⇒ Object 



227
228
229
230
 
# File 'lib/mssqlclient_adapter.rb', line 227

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

#remove_column(table_name, column_name) ⇒ Object 



270
271
272
 
# File 'lib/mssqlclient_adapter.rb', line 270

def remove_column(table_name, column_name)
  execute "ALTER TABLE #{table_name} DROP COLUMN #{column_name}"
end

#remove_default_constraint(table_name, column_name) ⇒ Object 



294
295
296
297
298
299
 
# File 'lib/mssqlclient_adapter.rb', line 294

def remove_default_constraint(table_name, column_name)
  defaults = 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"
  defaults.each {|constraint|
    execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["name"]}"
  }
end

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



301
302
303
 
# File 'lib/mssqlclient_adapter.rb', line 301

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

#rename_column(table, column, new_column_name) ⇒ Object 



274
275
276
 
# File 'lib/mssqlclient_adapter.rb', line 274

def rename_column(table, column, new_column_name)
  execute "EXEC sp_rename '#{table}.#{column}', '#{new_column_name}'"
end

#rename_table(name, new_name) ⇒ Object 



266
267
268
 
# File 'lib/mssqlclient_adapter.rb', line 266

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

#select_all(sql, name = nil) ⇒ Object 



85
86
87
 
# File 'lib/mssqlclient_adapter.rb', line 85

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

#select_one(sql, name = nil) ⇒ Object

alias_method :select_all, :select



91
92
93
94
95
 
# File 'lib/mssqlclient_adapter.rb', line 91

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

#supports_migrations? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


58
59
60
 
# File 'lib/mssqlclient_adapter.rb', line 58

def supports_migrations? #:nodoc:
  true				
end

#tables(name = nil) ⇒ Object 



244
245
246
247
248
249
250
251
252
 
# File 'lib/mssqlclient_adapter.rb', line 244

def tables(name = nil)
				results = select("SELECT table_name from information_schema.tables WHERE table_type = 'BASE TABLE'", name)				

  results.inject([]) do |tables, field|
    table_name = field['table_name']
    tables << table_name unless table_name == 'dtproperties'
    tables
  end
end

#type_to_sql(type, limit = nil) ⇒ Object

:nodoc:



305
306
307
308
309
310
311
312
 
# File 'lib/mssqlclient_adapter.rb', line 305

def type_to_sql(type, limit = nil) #:nodoc:
  native = native_database_types[type]
  # if there's no :limit in the default type definition, assume that type doesn't support limits
  limit = limit || native[:limit]
  column_type_sql = native[:name]
  column_type_sql << "(#{limit})" if limit
  column_type_sql
end

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



141
142
143
 
# File 'lib/mssqlclient_adapter.rb', line 141

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

#verify!(timeout) ⇒ Object

CONNECTION MANAGEMENT ====================================#



64
65
66
 
# File 'lib/mssqlclient_adapter.rb', line 64

def verify!(timeout)
  @last_verification = Time.now.to_i
end