Class | ActiveRecord::ConnectionAdapters::SQLServerAdapter |
In: |
vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb
|
Parent: | AbstractAdapter |
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:
ADO specific options:
ODBC specific options:
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]
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 189 189: def initialize(connection, logger, connection_options=nil) 190: super(connection, logger) 191: @connection_options = connection_options 192: end
Returns true if the connection is active.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 234 234: def active? 235: @connection.execute("SELECT 1").finish 236: true 237: rescue DBI::DatabaseError, DBI::InterfaceError 238: false 239: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 211 211: def adapter_name 212: 'SQLServer' 213: end
Adds a new column to the named table. See TableDefinition#column for details of the options you can use.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 448 448: def add_column(table_name, column_name, type, options = {}) 449: add_column_sql = "ALTER TABLE #{table_name} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" 450: add_column_options!(add_column_sql, options) 451: # TODO: Add support to mimic date columns, using constraints to mark them as such in the database 452: # 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 453: execute(add_column_sql) 454: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 364 364: def add_limit_offset!(sql, options) 365: if options[:limit] and options[:offset] 366: 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 367: if (options[:limit] + options[:offset]) >= total_rows 368: options[:limit] = (total_rows - options[:offset] >= 0) ? (total_rows - options[:offset]) : 0 369: end 370: sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i, "SELECT * FROM (SELECT TOP #{options[:limit]} * FROM (SELECT#{$1} TOP #{options[:limit] + options[:offset]} ") 371: sql << ") AS tmp1" 372: if options[:order] 373: options[:order] = options[:order].split(',').map do |field| 374: parts = field.split(" ") 375: tc = parts[0] 376: if sql =~ /\.\[/ and tc =~ /\./ # if column quoting used in query 377: tc.gsub!(/\./, '\\.\\[') 378: tc << '\\]' 379: end 380: if sql =~ /#{tc} AS (t\d_r\d\d?)/ 381: parts[0] = $1 382: elsif parts[0] =~ /\w+\.(\w+)/ 383: parts[0] = $1 384: end 385: parts.join(' ') 386: end.join(', ') 387: sql << " ORDER BY #{change_order_direction(options[:order])}) AS tmp2 ORDER BY #{options[:order]}" 388: else 389: sql << " ) AS tmp2" 390: end 391: elsif sql !~ /^\s*SELECT (@@|COUNT\()/i 392: sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i) do 393: "SELECT#{$1} TOP #{options[:limit]}" 394: end unless options[:limit].nil? 395: end 396: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 326 326: def begin_db_transaction 327: @connection["AutoCommit"] = false 328: rescue Exception => e 329: @connection["AutoCommit"] = true 330: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 256 256: def columns(table_name, name = nil) 257: return [] if table_name.blank? 258: table_name = table_name.to_s if table_name.is_a?(Symbol) 259: table_name = table_name.split('.')[-1] unless table_name.nil? 260: table_name = table_name.gsub(/[\[\]]/, '') 261: sql = %Q{ 262: SELECT 263: cols.COLUMN_NAME as ColName, 264: cols.COLUMN_DEFAULT as DefaultValue, 265: cols.NUMERIC_SCALE as numeric_scale, 266: cols.NUMERIC_PRECISION as numeric_precision, 267: cols.DATA_TYPE as ColType, 268: cols.IS_NULLABLE As IsNullable, 269: COL_LENGTH(cols.TABLE_NAME, cols.COLUMN_NAME) as Length, 270: COLUMNPROPERTY(OBJECT_ID(cols.TABLE_NAME), cols.COLUMN_NAME, 'IsIdentity') as IsIdentity, 271: cols.NUMERIC_SCALE as Scale 272: FROM INFORMATION_SCHEMA.COLUMNS cols 273: WHERE cols.TABLE_NAME = '#{table_name}' 274: } 275: # Comment out if you want to have the Columns select statment logged. 276: # Personally, I think it adds unnecessary bloat to the log. 277: # If you do comment it out, make sure to un-comment the "result" line that follows 278: result = log(sql, name) { @connection.select_all(sql) } 279: #result = @connection.select_all(sql) 280: columns = [] 281: result.each do |field| 282: default = field[:DefaultValue].to_s.gsub!(/[()\']/,"") =~ /null/ ? nil : field[:DefaultValue] 283: if field[:ColType] =~ /numeric|decimal/i 284: type = "#{field[:ColType]}(#{field[:numeric_precision]},#{field[:numeric_scale]})" 285: else 286: type = "#{field[:ColType]}(#{field[:Length]})" 287: end 288: is_identity = field[:IsIdentity] == 1 289: is_nullable = field[:IsNullable] == 'YES' 290: columns << SQLServerColumn.new(field[:ColName], default, type, is_identity, is_nullable) 291: end 292: columns 293: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 332 332: def commit_db_transaction 333: @connection.commit 334: ensure 335: @connection["AutoCommit"] = true 336: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 407 407: def create_database(name) 408: execute "CREATE DATABASE #{name}" 409: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 411 411: def current_database 412: @connection.select_one("select DB_NAME()")[0] 413: end
Disconnects from the database
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 252 252: def disconnect! 253: @connection.disconnect rescue nil 254: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 403 403: def drop_database(name) 404: execute "DROP DATABASE #{name}" 405: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 308 308: def execute(sql, name = nil) 309: if sql =~ /^\s*INSERT/i && (table_name = query_requires_identity_insert?(sql)) 310: log(sql, name) do 311: with_identity_insert_enabled(table_name) do 312: @connection.execute(sql) do |handle| 313: yield(handle) if block_given? 314: end 315: end 316: end 317: else 318: log(sql, name) do 319: @connection.execute(sql) do |handle| 320: yield(handle) if block_given? 321: end 322: end 323: end 324: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 425 425: def indexes(table_name, name = nil) 426: ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = false 427: indexes = [] 428: execute("EXEC sp_helpindex '#{table_name}'", name) do |sth| 429: sth.each do |index| 430: unique = index[1] =~ /unique/ 431: primary = index[1] =~ /primary key/ 432: if !primary 433: indexes << IndexDefinition.new(table_name, index[0], unique, index[2].split(", ")) 434: end 435: end 436: end 437: indexes 438: ensure 439: ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = true 440: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 295 295: def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) 296: execute(sql, name) 297: id_value || select_one("SELECT @@IDENTITY AS Ident")["Ident"] 298: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 194 194: def native_database_types 195: { 196: :primary_key => "int NOT NULL IDENTITY(1, 1) PRIMARY KEY", 197: :string => { :name => "varchar", :limit => 255 }, 198: :text => { :name => "text" }, 199: :integer => { :name => "int" }, 200: :float => { :name => "float", :limit => 8 }, 201: :decimal => { :name => "decimal" }, 202: :datetime => { :name => "datetime" }, 203: :timestamp => { :name => "datetime" }, 204: :time => { :name => "datetime" }, 205: :date => { :name => "datetime" }, 206: :binary => { :name => "image"}, 207: :boolean => { :name => "bit"} 208: } 209: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 344 344: def quote(value, column = nil) 345: return value.quoted_id if value.respond_to?(:quoted_id) 346: 347: case value 348: when TrueClass then '1' 349: when FalseClass then '0' 350: when Time, DateTime then "'#{value.strftime("%Y%m%d %H:%M:%S")}'" 351: when Date then "'#{value.strftime("%Y%m%d")}'" 352: else super 353: end 354: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 360 360: def quote_column_name(name) 361: "[#{name}]" 362: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 356 356: def quote_string(string) 357: string.gsub(/\'/, "''") 358: end
Reconnects to the database, returns false if no connection could be made.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 242 242: def reconnect! 243: disconnect! 244: @connection = DBI.connect(*@connection_options) 245: rescue DBI::DatabaseError => e 246: @logger.warn "#{adapter_name} reconnection failed: #{e.message}" if @logger 247: false 248: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 398 398: def recreate_database(name) 399: drop_database(name) 400: create_database(name) 401: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 485 485: def remove_check_constraints(table_name, column_name) 486: # TODO remove all constraints in single method 487: constraints = select "SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '#{table_name}' and COLUMN_NAME = '#{column_name}'" 488: constraints.each do |constraint| 489: execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["CONSTRAINT_NAME"]}" 490: end 491: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 471 471: def remove_column(table_name, column_name) 472: remove_check_constraints(table_name, column_name) 473: remove_default_constraint(table_name, column_name) 474: execute "ALTER TABLE [#{table_name}] DROP COLUMN [#{column_name}]" 475: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 477 477: def remove_default_constraint(table_name, column_name) 478: 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" 479: 480: constraints.each do |constraint| 481: execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["name"]}" 482: end 483: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 493 493: def remove_index(table_name, options = {}) 494: execute "DROP INDEX #{table_name}.#{quote_column_name(index_name(table_name, options))}" 495: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 456 456: def rename_column(table, column, new_column_name) 457: execute "EXEC sp_rename '#{table}.#{column}', '#{new_column_name}'" 458: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 442 442: def rename_table(name, new_name) 443: execute "EXEC sp_rename '#{name}', '#{new_name}'" 444: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 338 338: def rollback_db_transaction 339: @connection.rollback 340: ensure 341: @connection["AutoCommit"] = true 342: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 415 415: def tables(name = nil) 416: execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'", name) do |sth| 417: sth.inject([]) do |tables, field| 418: table_name = field[0] 419: tables << table_name unless table_name == 'dtproperties' 420: tables 421: end 422: end 423: end