ArJdbc::MsSQL

    # File lib/arjdbc/mssql/adapter.rb, line 29
29:     def self.column_selector
30:       [/sqlserver|tds|Microsoft SQL/, lambda {|cfg,col| col.extend(::ArJdbc::MsSQL::Column)}]
31:     end

    # File lib/arjdbc/mssql/adapter.rb, line 9
 9:     def self.extended(mod)
10:       unless @lob_callback_added
11:         ActiveRecord::Base.class_eval do
12:           def after_save_with_mssql_lob
13:             self.class.columns.select { |c| c.sql_type =~ /image/ }.each do |c|
14:               value = self[c.name]
15:               value = value.to_yaml if unserializable_attribute?(c.name, c)
16:               next if value.nil?  || (value == '')
17: 
18:               connection.write_large_object(c.type == :binary, c.name, self.class.table_name, self.class.primary_key, quote_value(id), value)
19:             end
20:           end
21:         end
22: 
23:         ActiveRecord::Base.after_save :after_save_with_mssql_lob
24:         @lob_callback_added = true
25:       end
26:       mod.add_version_specific_add_limit_offset
27:     end

    # File lib/arjdbc/mssql/adapter.rb, line 33
33:     def self.jdbc_connection_class
34:       ::ActiveRecord::ConnectionAdapters::MssqlJdbcConnection
35:     end

     # File lib/arjdbc/mssql/adapter.rb, line 361
361:     def _execute(sql, name = nil)
362:       # Match the start of the sql to determine appropriate behaviour.  Be aware of
363:       # multi-line sql which might begin with 'create stored_proc' and contain 'insert into ...' lines.
364:       # Possible improvements include ignoring comment blocks prior to the first statement.
365:       if sql.lstrip =~ /\Ainsert/
366:         if query_requires_identity_insert?(sql)
367:           table_name = get_table_name(sql)
368:           with_identity_insert_enabled(table_name) do
369:             id = @connection.execute_insert(sql)
370:           end
371:         else
372:           @connection.execute_insert(sql)
373:         end
374:       elsif sql.lstrip =~ /\A(create|exec)/
375:         @connection.execute_update(sql)
376:       elsif sql.lstrip =~ /\A\(?\s*(select|show)/
377:         repair_special_columns(sql)
378:         @connection.execute_query(sql)
379:       else
380:         @connection.execute_update(sql)
381:       end
382:     end

     # File lib/arjdbc/mssql/adapter.rb, line 275
275:     def add_column(table_name, column_name, type, options = {})
276:       clear_cached_table(table_name)
277:       add_column_sql = "ALTER TABLE #{table_name} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
278:       add_column_options!(add_column_sql, options)
279:       # TODO: Add support to mimic date columns, using constraints to mark them as such in the database
280:       # 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
281:       execute(add_column_sql)
282:     end

     # File lib/arjdbc/mssql/adapter.rb, line 391
391:     def add_lock!(sql, options)
392:       sql
393:     end

    # File lib/arjdbc/mssql/adapter.rb, line 47
47:     def add_version_specific_add_limit_offset
48:       if sqlserver_version == "2000"
49:         extend LimitHelpers::SqlServer2000AddLimitOffset
50:       else
51:         extend LimitHelpers::SqlServerAddLimitOffset
52:       end
53:     end

    # File lib/arjdbc/mssql/adapter.rb, line 12
12:           def after_save_with_mssql_lob
13:             self.class.columns.select { |c| c.sql_type =~ /image/ }.each do |c|
14:               value = self[c.name]
15:               value = value.to_yaml if unserializable_attribute?(c.name, c)
16:               next if value.nil?  || (value == '')
17: 
18:               connection.write_large_object(c.type == :binary, c.name, self.class.table_name, self.class.primary_key, quote_value(id), value)
19:             end
20:           end

    # File lib/arjdbc/mssql/adapter.rb, line 37
37:     def arel2_visitors
38:       require 'arel/visitors/mssql'
39:       visitor_class = sqlserver_version == "2000" ? ::Arel::Visitors::SQLServer2000 : ::Arel::Visitors::SQLServer
40:       { 'mssql' => visitor_class, 'jdbcmssql' => visitor_class}
41:     end

     # File lib/arjdbc/mssql/adapter.rb, line 239
239:     def change_order_direction(order)
240:       order.split(",").collect do |fragment|
241:         case fragment
242:         when  /\bDESC\b/     then fragment.gsub(/\bDESC\b/, "ASC")
243:         when  /\bASC\b/      then fragment.gsub(/\bASC\b/, "DESC")
244:         else                  String.new(fragment).split(',').join(' DESC,') + ' DESC'
245:         end
246:       end.join(",")
247:     end

     # File lib/arjdbc/mssql/adapter.rb, line 467
467:     def clear_cached_table(name)
468:       (@table_columns ||= {}).delete(name.to_s)
469:     end

     # File lib/arjdbc/mssql/adapter.rb, line 340
340:     def columns(table_name, name = nil)
341:       # It's possible for table_name to be an empty string, or nil, if something attempts to issue SQL
342:       # which doesn't involve a table.  IE. "SELECT 1" or "SELECT * from someFunction()".
343:       return [] if table_name.blank?
344:       table_name = table_name.to_s if table_name.is_a?(Symbol)
345: 
346:       # Remove []'s from around the table name, valid in a select statement, but not when matching metadata.
347:       table_name = table_name.gsub(/[\[\]]/, '')
348: 
349:       return [] if table_name =~ /^information_schema\./
350:       @table_columns = {} unless @table_columns
351:       unless @table_columns[table_name]
352:         @table_columns[table_name] = super
353:         @table_columns[table_name].each do |col|
354:           col.identity = true if col.sql_type =~ /identity/
355:           col.is_special = true if col.sql_type =~ /text|ntext|image|xml/
356:         end
357:       end
358:       @table_columns[table_name]
359:     end

     # File lib/arjdbc/mssql/adapter.rb, line 263
263:     def create_database(name)
264:       execute "CREATE DATABASE #{name}"
265:       execute "USE #{name}"
266:     end

     # File lib/arjdbc/mssql/adapter.rb, line 452
452:     def determine_order_clause(sql)
453:       return $1 if sql =~ /ORDER BY (.*)$/
454:       table_name = get_table_name(sql)
455:       "#{table_name}.#{determine_primary_key(table_name)}"
456:     end

     # File lib/arjdbc/mssql/adapter.rb, line 458
458:     def determine_primary_key(table_name)
459:       primary_key = columns(table_name).detect { |column| column.primary || column.identity }
460:       return primary_key.name if primary_key
461:       # Look for an id column.  Return it, without changing case, to cover dbs with a case-sensitive collation.
462:       columns(table_name).each { |column| return column.name if column.name =~ /^id$/ }
463:       # Give up and provide something which is going to crash almost certainly
464:       columns(table_name)[0].name
465:     end

     # File lib/arjdbc/mssql/adapter.rb, line 258
258:     def drop_database(name)
259:       execute "USE master"
260:       execute "DROP DATABASE #{name}"
261:     end

     # File lib/arjdbc/mssql/adapter.rb, line 435
435:     def get_special_columns(table_name)
436:       special = []
437:       columns(table_name).each do |col|
438:         special << col.name if col.is_special
439:       end
440:       special
441:     end

     # File lib/arjdbc/mssql/adapter.rb, line 411
411:     def identity_column(table_name)
412:       columns(table_name).each do |col|
413:         return col.name if col.identity
414:       end
415:       return nil
416:     end

     # File lib/arjdbc/mssql/adapter.rb, line 418
418:     def query_requires_identity_insert?(sql)
419:       table_name = get_table_name(sql)
420:       id_column = identity_column(table_name)
421:       if sql.strip =~ /insert into [^ ]+ ?\((.+?)\)/
422:         insert_columns = $1.split(/, */).map(&method(:unquote_column_name))
423:         return table_name if insert_columns.include?(id_column)
424:       end
425:     end

     # File lib/arjdbc/mssql/adapter.rb, line 191
191:     def quote(value, column = nil)
192:       return value.quoted_id if value.respond_to?(:quoted_id)
193: 
194:       case value
195:       # SQL Server 2000 doesn't let you insert an integer into a NVARCHAR
196:       # column, so we include Integer here.
197:       when String, ActiveSupport::Multibyte::Chars, Integer
198:         value = value.to_s
199:         if column && column.type == :binary
200:           "'#{quote_string(ArJdbc::MsSQL::Column.string_to_binary(value))}'" # ' (for ruby-mode)
201:         elsif column && [:integer, :float].include?(column.type)
202:           value = column.type == :integer ? value.to_i : value.to_f
203:           value.to_s
204:         elsif !column.respond_to?(:is_utf8?) || column.is_utf8?
205:           "N'#{quote_string(value)}'" # ' (for ruby-mode)
206:         else
207:           super
208:         end
209:       when TrueClass             then '1'
210:       when FalseClass            then '0'
211:       else                       super
212:       end
213:     end

     # File lib/arjdbc/mssql/adapter.rb, line 223
223:     def quote_column_name(name)
224:       "[#{name}]"
225:     end

     # File lib/arjdbc/mssql/adapter.rb, line 215
215:     def quote_string(string)
216:       string.gsub(/\'/, "''")
217:     end

     # File lib/arjdbc/mssql/adapter.rb, line 219
219:     def quote_table_name(name)
220:       name
221:     end

     # File lib/arjdbc/mssql/adapter.rb, line 231
231:     def quoted_false
232:       quote false
233:     end

     # File lib/arjdbc/mssql/adapter.rb, line 227
227:     def quoted_true
228:       quote true
229:     end

     # File lib/arjdbc/mssql/adapter.rb, line 253
253:     def recreate_database(name)
254:       drop_database(name)
255:       create_database(name)
256:     end

     # File lib/arjdbc/mssql/adapter.rb, line 327
327:     def remove_check_constraints(table_name, column_name)
328:       clear_cached_table(table_name)
329:       # TODO remove all constraints in single method
330:       constraints = select "SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '#{table_name}' and COLUMN_NAME = '#{column_name}'"
331:       constraints.each do |constraint|
332:         execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["CONSTRAINT_NAME"]}"
333:       end
334:     end

     # File lib/arjdbc/mssql/adapter.rb, line 312
312:     def remove_column(table_name, column_name)
313:       clear_cached_table(table_name)
314:       remove_check_constraints(table_name, column_name)
315:       remove_default_constraint(table_name, column_name)
316:       execute "ALTER TABLE #{table_name} DROP COLUMN [#{column_name}]"
317:     end

     # File lib/arjdbc/mssql/adapter.rb, line 319
319:     def remove_default_constraint(table_name, column_name)
320:       clear_cached_table(table_name)
321:       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"
322:       defaults.each {|constraint|
323:         execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["name"]}"
324:       }
325:     end

     # File lib/arjdbc/mssql/adapter.rb, line 336
336:     def remove_index(table_name, options = {})
337:       execute "DROP INDEX #{table_name}.#{index_name(table_name, options)}"
338:     end

     # File lib/arjdbc/mssql/adapter.rb, line 284
284:     def rename_column(table, column, new_column_name)
285:       clear_cached_table(table)
286:       execute "EXEC sp_rename '#{table}.#{column}', '#{new_column_name}'"
287:     end

     # File lib/arjdbc/mssql/adapter.rb, line 268
268:     def rename_table(name, new_name)
269:       clear_cached_table(name)
270:       execute "EXEC sp_rename '#{name}', '#{new_name}'"
271:     end

     # File lib/arjdbc/mssql/adapter.rb, line 443
443:     def repair_special_columns(sql)
444:       special_cols = get_special_columns(get_table_name(sql))
445:       for col in special_cols.to_a
446:         sql.gsub!(Regexp.new(" #{col.to_s} = "), " #{col.to_s} LIKE ")
447:         sql.gsub!(/ORDER BY #{col.to_s}/, '')
448:       end
449:       sql
450:     end

     # File lib/arjdbc/mssql/adapter.rb, line 384
384:     def select(sql, name = nil)
385:       log(sql, name) do
386:         @connection.execute_query(sql)
387:       end
388:     end

     # File lib/arjdbc/mssql/adapter.rb, line 405
405:     def set_identity_insert(table_name, enable = true)
406:       execute "SET IDENTITY_INSERT #{table_name} #{enable ? 'ON' : 'OFF'}"
407:     rescue Exception => e
408:       raise ActiveRecord::ActiveRecordError, "IDENTITY_INSERT could not be turned #{enable ? 'ON' : 'OFF'} for table #{table_name}"
409:     end

    # File lib/arjdbc/mssql/adapter.rb, line 43
43:     def sqlserver_version
44:       @sqlserver_version ||= select_value("select @@version")[/Microsoft SQL Server\s+(\d{4})/, 1]
45:     end

     # File lib/arjdbc/mssql/adapter.rb, line 249
249:     def supports_ddl_transactions?
250:       true
251:     end

     # File lib/arjdbc/mssql/adapter.rb, line 427
427:     def unquote_column_name(name)
428:       if name =~ /^\[.*\]$/
429:         name[1..2]
430:       else
431:         name
432:       end
433:     end

     # File lib/arjdbc/mssql/adapter.rb, line 398
398:     def with_identity_insert_enabled(table_name, &block)
399:       set_identity_insert(table_name, true)
400:       yield
401:     ensure
402:       set_identity_insert(table_name, false)
403:     end