@see quote @private
@private
@private
@private these cannot specify a limit
@private
NOTE: these do not handle = equality as expected see {repair_special_columns} (TEXT, NTEXT, and IMAGE data types are deprecated) @private
@deprecated no longer used @see ActiveRecord::ConnectionAdapters::JdbcAdapter#arel2_visitors
# File lib/arjdbc/mssql/adapter.rb, line 77 def self.arel2_visitors(config) visitor = arel_visitor_type(config) { 'mssql' => visitor, 'jdbcmssql' => visitor, 'sqlserver' => visitor } end
@see ActiveRecord::ConnectionAdapters::Jdbc::ArelSupport
# File lib/arjdbc/mssql/adapter.rb, line 69 def self.arel_visitor_type(config) require 'arel/visitors/sql_server' ( config && config[:sqlserver_version].to_s == '2000' ) ? ::Arel::Visitors::SQLServer2000 : ::Arel::Visitors::SQLServer end
@see ActiveRecord::ConnectionAdapters::JdbcColumn#column_types
# File lib/arjdbc/mssql/column.rb, line 5 def self.column_selector [ /sqlserver|tds|Microsoft SQL/, lambda { |config, column| column.extend(Column) } ] end
@private
# File lib/arjdbc/mssql/adapter.rb, line 17 def self.extended(adapter) initialize! version = adapter.config[:sqlserver_version] ||= adapter.sqlserver_version adapter.send(:setup_limit_offset!, version) end
@private
# File lib/arjdbc/mssql/adapter.rb, line 28 def self.initialize! return if @@_initialized; @@_initialized = true require 'arjdbc/util/serialized_attributes' Util::SerializedAttributes.setup /image/, 'after_save_with_mssql_lob' end
@see ActiveRecord::ConnectionAdapters::JdbcAdapter#jdbc_connection_class
# File lib/arjdbc/mssql/adapter.rb, line 59 def self.jdbc_connection_class ::ActiveRecord::ConnectionAdapters::MSSQLJdbcConnection end
@see update_lob_values?
# File lib/arjdbc/mssql/adapter.rb, line 46 def self.update_lob_values=(update); @@update_lob_values = update; end
Updating records with LOB values (binary/text columns) in a separate statement can be disabled using :
ArJdbc::MSSQL.update_lob_values = false
@note This only applies when prepared statements are not used.
# File lib/arjdbc/mssql/adapter.rb, line 44 def self.update_lob_values?; @@update_lob_values; end
# File lib/arjdbc/mssql/adapter.rb, line 243 def adapter_name ADAPTER_NAME end
Adds a new column to the named table. @override
# File lib/arjdbc/mssql/adapter.rb, line 361 def add_column(table_name, column_name, type, options = {}) clear_cached_table(table_name) add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" add_column_options!(add_column_sql, options) # TODO: Add support to mimic date columns, using constraints to mark them as such in the database # 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 execute(add_column_sql) end
@override
# File lib/arjdbc/mssql/adapter.rb, line 403 def change_column(table_name, column_name, type, options = {}) column = columns(table_name).find { |c| c.name.to_s == column_name.to_s } indexes = EMPTY_ARRAY if options_include_default?(options) || (column && column.type != type.to_sym) remove_default_constraint(table_name, column_name) indexes = indexes(table_name).select{ |index| index.columns.include?(column_name.to_s) } remove_indexes(table_name, column_name) end if ! options[:null].nil? && options[:null] == false && ! options[:default].nil? execute "UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote_default_value(options[:default], column)} WHERE #{quote_column_name(column_name)} IS NULL" clear_cached_table(table_name) end change_column_type(table_name, column_name, type, options) change_column_default(table_name, column_name, options[:default]) if options_include_default?(options) indexes.each do |index| # add any removed indexes back index_columns = index.columns.map { |c| quote_column_name(c) }.join(', ') execute "CREATE INDEX #{quote_table_name(index.name)} ON #{quote_table_name(table_name)} (#{index_columns})" end end
# File lib/arjdbc/mssql/adapter.rb, line 434 def change_column_default(table_name, column_name, default) remove_default_constraint(table_name, column_name) unless default.nil? column = columns(table_name).find { |c| c.name.to_s == column_name.to_s } result = execute "ALTER TABLE #{quote_table_name(table_name)} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote_default_value(default, column)} FOR #{quote_column_name(column_name)}" clear_cached_table(table_name) result end end
# File lib/arjdbc/mssql/adapter.rb, line 426 def change_column_type(table_name, column_name, type, options = {}) sql = "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" sql << (options[:null] ? " NULL" : " NOT NULL") if options.has_key?(:null) result = execute(sql) clear_cached_table(table_name) result end
# File lib/arjdbc/mssql/adapter.rb, line 247 def change_order_direction(order) asc, desc = /\bASC\b/, /\bDESC\b/ order.split(",").collect do |fragment| case fragment when desc then fragment.gsub(desc, "ASC") when asc then fragment.gsub(asc, "DESC") else "#{fragment.split(',').join(' DESC,')} DESC" end end.join(",") end
# File lib/arjdbc/mssql/adapter.rb, line 305 def charset select_value "SELECT SERVERPROPERTY('SqlCharSetName')" end
# File lib/arjdbc/mssql/adapter.rb, line 550 def clear_cached_table(table_name) ( @table_columns ||= {} ).delete(table_name.to_s) end
# File lib/arjdbc/mssql/adapter.rb, line 309 def collation select_value "SELECT SERVERPROPERTY('Collation')" end
# File lib/arjdbc/mssql/adapter.rb, line 529 def columns(table_name, name = nil, default = EMPTY_ARRAY) # It's possible for table_name to be an empty string, or nil, if something # attempts to issue SQL which doesn't involve a table. # IE. "SELECT 1" or "SELECT * FROM someFunction()". return default if table_name.blank? table_name = unquote_table_name(table_name) return default if table_name =~ SKIP_COLUMNS_TABLE_NAMES_RE unless columns = ( @table_columns ||= {} )[table_name] columns = super(table_name, name) for column in columns column.identity = true if column.sql_type =~ IDENTITY_COLUMN_TYPE_RE column.special = true if column.sql_type =~ SPECIAL_COLUMN_TYPE_RE end @table_columns[table_name] = columns end columns end
# File lib/arjdbc/mssql/adapter.rb, line 382 def columns_for_distinct(columns, orders) return columns if orders.blank? # construct a clean list of column names from the ORDER BY clause, # removing any ASC/DESC modifiers order_columns = [ orders ]; order_columns.flatten! # AR 3.x vs 4.x order_columns.map! do |column| column = column.to_sql unless column.is_a?(String) # handle AREL node column.split(',').collect!{ |s| s.split.first } end.flatten! order_columns.reject!(&:blank?) order_columns = order_columns.zip(0...order_columns.size).to_a order_columns = order_columns.map{ |s, i| "#{s}" } columns = [ columns ]; columns.flatten! columns.push( *order_columns ).join(', ') # return a DISTINCT clause that's distinct on the columns we want but # includes all the required columns for the ORDER BY to work properly end
# File lib/arjdbc/mssql/adapter.rb, line 82 def configure_connection use_database # config[:database] end
# File lib/arjdbc/mssql/adapter.rb, line 345 def create_database(name, options = {}) execute "CREATE DATABASE #{quote_database_name(name)}" end
# File lib/arjdbc/mssql/adapter.rb, line 313 def current_database select_value 'SELECT DB_NAME()' end
`SELECT CURRENT_USER`
# File lib/arjdbc/mssql/adapter.rb, line 301 def current_user @current_user ||= @connection.execute_query_raw("SELECT CURRENT_USER").first[''] end
# File lib/arjdbc/mssql/adapter.rb, line 349 def database_exists?(name) select_value "SELECT name FROM sys.databases WHERE name = '#{name}'" end
Returns the default schema (to be used for table resolution) used for the {current_user}.
# File lib/arjdbc/mssql/adapter.rb, line 281 def default_schema return current_user if sqlserver_2000? @default_schema ||= @connection.execute_query_raw( "SELECT default_schema_name FROM sys.database_principals WHERE name = CURRENT_USER" ).first['default_schema_name'] end
@private @see ArJdbc::MSSQL::LimitHelpers
# File lib/arjdbc/mssql/adapter.rb, line 577 def determine_order_clause(sql) return $1 if sql =~ /ORDER BY (.*)$/ table_name = get_table_name(sql) # determine primary key for table : columns = self.columns(table_name) primary_column = columns.find { |column| column.primary || column.identity } unless primary_column # look for an id column and return it, # without changing case, to cover DBs with a case-sensitive collation : primary_column = columns.find { |column| column.name =~ /^id$/ } raise "no columns for table: #{table_name}" if columns.empty? end # NOTE: if still no PK column simply get something for ORDER BY ... "#{quote_table_name(table_name)}.#{quote_column_name((primary_column || columns.first).name)}" end
SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause. MSSQL requires the ORDER BY columns in the select list for distinct queries.
# File lib/arjdbc/mssql/adapter.rb, line 378 def distinct(columns, order_by) "DISTINCT #{columns_for_distinct(columns, order_by)}" end
# File lib/arjdbc/mssql/adapter.rb, line 339 def drop_database(name) current_db = current_database use_database('master') if current_db.to_s == name execute "DROP DATABASE #{quote_database_name(name)}" end
Support for executing a stored procedure.
# File lib/arjdbc/mssql/adapter.rb, line 593 def exec_proc(proc_name, *variables) vars = if variables.any? && variables.first.is_a?(Hash) variables.first.map { |k, v| "@#{k} = #{quote(v)}" } else variables.map { |v| quote(v) } end.join(', ') sql = "EXEC #{proc_name} #{vars}".strip log(sql, 'Execute Procedure') do result = @connection.execute_query_raw(sql) result.map! do |row| row = row.is_a?(Hash) ? row.with_indifferent_access : row yield(row) if block_given? row end result end end
@override
# File lib/arjdbc/mssql/adapter.rb, line 614 def exec_query(sql, name = 'SQL', binds = []) # NOTE: we allow to execute SQL as requested returning a results. # e.g. this allows to use SQLServer's EXEC with a result set ... if sql.respond_to?(:to_sql) sql = to_sql(sql, binds); to_sql = true end sql = repair_special_columns(sql) if prepared_statements? log(sql, name, binds) { @connection.execute_query(sql, binds) } else sql = suble_binds(sql, binds) unless to_sql # deprecated behavior log(sql, name) { @connection.execute_query(sql) } end end
@override
# File lib/arjdbc/mssql/adapter.rb, line 630 def exec_query_raw(sql, name = 'SQL', binds = [], &block) if sql.respond_to?(:to_sql) sql = to_sql(sql, binds); to_sql = true end sql = repair_special_columns(sql) if prepared_statements? log(sql, name, binds) { @connection.execute_query_raw(sql, binds, &block) } else sql = suble_binds(sql, binds) unless to_sql # deprecated behavior log(sql, name) { @connection.execute_query_raw(sql, &block) } end end
@see ActiveRecord::ConnectionAdapters::JdbcAdapter#jdbc_column_class
# File lib/arjdbc/mssql/adapter.rb, line 64 def jdbc_column_class ::ActiveRecord::ConnectionAdapters::MSSQLColumn end
# File lib/arjdbc/mssql/adapter.rb, line 94 def modify_types(types) types[:string] = { :name => "NVARCHAR", :limit => 255 } if sqlserver_2000? types[:text] = { :name => "NTEXT" } else types[:text] = { :name => "NVARCHAR(MAX)" } end types[:primary_key] = "int NOT NULL IDENTITY(1, 1) PRIMARY KEY" types[:integer][:limit] = nil types[:boolean] = { :name => "bit" } types[:binary] = { :name => "image" } types end
@override
# File lib/arjdbc/mssql/adapter.rb, line 144 def quote(value, column = nil) return value.quoted_id if value.respond_to?(:quoted_id) return value if sql_literal?(value) case value # SQL Server 2000 doesn't let you insert an integer into a NVARCHAR when String, ActiveSupport::Multibyte::Chars, Integer value = value.to_s column_type = column && column.type if column_type == :binary if update_lob_value?(value, column) BLOB_VALUE_MARKER else "'#{quote_string(column.class.string_to_binary(value))}'" # ' (for ruby-mode) end elsif column_type == :integer value.to_i.to_s elsif column_type == :float value.to_f.to_s elsif ! column.respond_to?(:is_utf8?) || column.is_utf8? "N'#{quote_string(value)}'" # ' (for ruby-mode) else super end when Date, Time if column && column.type == :time "'#{quoted_time(value)}'" else "'#{quoted_date(value)}'" end when TrueClass then '1' when FalseClass then '0' else super end end
# File lib/arjdbc/mssql/adapter.rb, line 222 def quote_column_name(name) name = name.to_s.split('.') name.map! { |n| quote_name_part(n) } # "[#{name}]" name.join('.') end
# File lib/arjdbc/mssql/adapter.rb, line 228 def quote_database_name(name) quote_name_part(name.to_s) end
Does not quote function default values for UUID columns
# File lib/arjdbc/mssql/adapter.rb, line 233 def quote_default_value(value, column) if column.type == :uuid && value =~ /\(\)/ value else quote(value) end end
# File lib/arjdbc/mssql/adapter.rb, line 218 def quote_table_name(name) quote_column_name(name) end
@override
# File lib/arjdbc/mssql/adapter.rb, line 181 def quoted_date(value) if value.respond_to?(:usec) "#{super}.#{sprintf("%03d", value.usec / 1000)}" else super end end
@deprecated no longer used @private
# File lib/arjdbc/mssql/adapter.rb, line 202 def quoted_datetime(value) quoted_date(value) end
@deprecated no longer used @private
# File lib/arjdbc/mssql/adapter.rb, line 208 def quoted_full_iso8601(value) if value.acts_like?(:time) value.is_a?(Date) ? get_time(value).to_time.xmlschema.to(18) : get_time(value).iso8601(7).to(22) else quoted_date(value) end end
@private
# File lib/arjdbc/mssql/adapter.rb, line 190 def quoted_time(value) if value.acts_like?(:time) tz_value = get_time(value) usec = value.respond_to?(:usec) ? ( value.usec / 1000 ) : 0 sprintf("%02d:%02d:%02d.%03d", tz_value.hour, tz_value.min, tz_value.sec, usec) else quoted_date(value) end end
@private
# File lib/arjdbc/mssql/adapter.rb, line 323 def recreate_database(name, options = {}) drop_database(name) create_database(name, options) end
@private
# File lib/arjdbc/mssql/adapter.rb, line 329 def recreate_database!(database = nil) current_db = current_database database ||= current_db use_database('master') if this_db = ( database.to_s == current_db ) drop_database(database) create_database(database) ensure use_database(current_db) if this_db end
@override
# File lib/arjdbc/mssql/adapter.rb, line 644 def release_savepoint(name = current_savepoint_name) if @connection.jtds_driver? @connection.release_savepoint(name) else # MS invented it's "own" way @connection.rollback_savepoint(name) end end
# File lib/arjdbc/mssql/adapter.rb, line 495 def remove_check_constraints(table_name, column_name) clear_cached_table(table_name) constraints = select_values "SELECT constraint_name" << " FROM information_schema.constraint_column_usage" << " WHERE table_name = '#{table_name}' AND column_name = '#{column_name}'" constraints.each do |constraint_name| execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint_name}" end end
@override
# File lib/arjdbc/mssql/adapter.rb, line 465 def remove_column(table_name, column_name, type = nil, options = {}) remove_columns(table_name, column_name) end
# File lib/arjdbc/mssql/adapter.rb, line 444 def remove_columns(table_name, *column_names) raise ArgumentError.new("You must specify at least one column name. Example: remove_column(:people, :first_name)") if column_names.empty? # remove_columns(:posts, :foo, :bar) old syntax : remove_columns(:posts, [:foo, :bar]) clear_cached_table(table_name) column_names = column_names.flatten return do_remove_column(table_name, column_names.first) if column_names.size == 1 column_names.each { |column_name| do_remove_column(table_name, column_name) } end
# File lib/arjdbc/mssql/adapter.rb, line 475 def remove_default_constraint(table_name, column_name) clear_cached_table(table_name) if sqlserver_2000? # NOTE: since SQLServer 2005 these are provided as sys.sysobjects etc. # but only due backwards-compatibility views and should be avoided ... defaults = select_values "SELECT d.name" << " FROM sysobjects d, syscolumns c, sysobjects t" << " WHERE c.cdefault = d.id AND c.name = '#{column_name}'" << " AND t.name = '#{table_name}' AND c.id = t.id" else defaults = select_values "SELECT d.name FROM sys.tables t" << " JOIN sys.default_constraints d ON d.parent_object_id = t.object_id" << " JOIN sys.columns c ON c.object_id = t.object_id AND c.column_id = d.parent_column_id" << " WHERE t.name = '#{table_name}' AND c.name = '#{column_name}'" end defaults.each do |def_name| execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{def_name}" end end
# File lib/arjdbc/mssql/adapter.rb, line 512 def remove_index(table_name, options = {}) execute "DROP INDEX #{quote_table_name(table_name)}.#{index_name(table_name, options)}" end
# File lib/arjdbc/mssql/adapter.rb, line 505 def remove_indexes(table_name, column_name) indexes = self.indexes(table_name) indexes.select{ |index| index.columns.include?(column_name.to_s) }.each do |index| remove_index(table_name, { :name => index.name }) end end
@override
# File lib/arjdbc/mssql/adapter.rb, line 371 def rename_column(table_name, column_name, new_column_name) clear_cached_table(table_name) execute "EXEC sp_rename '#{table_name}.#{column_name}', '#{new_column_name}', 'COLUMN'" end
@override
# File lib/arjdbc/mssql/adapter.rb, line 354 def rename_table(table_name, new_table_name) clear_cached_table(table_name) execute "EXEC sp_rename '#{table_name}', '#{new_table_name}'" end
# File lib/arjdbc/mssql/adapter.rb, line 554 def reset_column_information @table_columns = nil if defined? @table_columns end
# File lib/arjdbc/mssql/adapter.rb, line 568 def set_identity_insert(table_name, enable = true) execute "SET IDENTITY_INSERT #{table_name} #{enable ? 'ON' : 'OFF'}" rescue Exception => e raise ActiveRecord::ActiveRecordError, "IDENTITY_INSERT could not be turned" + " #{enable ? 'ON' : 'OFF'} for table #{table_name} due : #{e.inspect}" end
# File lib/arjdbc/mssql/adapter.rb, line 86 def sqlserver_version @sqlserver_version ||= begin config_version = config[:sqlserver_version] config_version ? config_version.to_s : select_value("SELECT @@version")[/(Microsoft SQL Server\s+|Microsoft SQL Azure.+\n.+)(\d{4})/, 2] end end
# File lib/arjdbc/mssql/adapter.rb, line 258 def supports_ddl_transactions? true end
# File lib/arjdbc/mssql/adapter.rb, line 262 def tables(schema = current_schema) @connection.tables(nil, schema) end
# File lib/arjdbc/mssql/adapter.rb, line 111 def type_to_sql(type, limit = nil, precision = nil, scale = nil) type_s = type.to_s # MSSQL's NVARCHAR(n | max) column supports either a number between 1 and # 4000, or the word "MAX", which corresponds to 2**30-1 UCS-2 characters. # # It does not accept NVARCHAR(1073741823) here, so we have to change it # to NVARCHAR(MAX), even though they are logically equivalent. # # MSSQL Server 2000 is skipped here because I don't know how it will behave. # # See: http://msdn.microsoft.com/en-us/library/ms186939.aspx if type_s == 'string' && limit == 1073741823 && ! sqlserver_2000? 'NVARCHAR(MAX)' elsif NO_LIMIT_TYPES.include?(type_s) super(type) elsif type_s == 'integer' || type_s == 'int' if limit.nil? || limit == 4 'int' elsif limit == 2 'smallint' elsif limit == 1 'tinyint' else 'bigint' end elsif type_s == 'uniqueidentifier' type_s else super end end
@see update_lob_values? @see ArJdbc::Util::SerializedAttributes#update_lob_columns
# File lib/arjdbc/mssql/adapter.rb, line 54 def update_lob_value?(value, column = nil) MSSQL.update_lob_values? && ! prepared_statements? # && value end
# File lib/arjdbc/mssql/adapter.rb, line 317 def use_database(database = nil) database ||= config[:database] execute "USE #{quote_database_name(database)}" unless database.blank? end
Turns IDENTITY_INSERT ON for table during execution of the block N.B. This sets the state of IDENTITY_INSERT to OFF after the block has been executed without regard to its previous state
# File lib/arjdbc/mssql/adapter.rb, line 561 def with_identity_insert_enabled(table_name) set_identity_insert(table_name, true) yield ensure set_identity_insert(table_name, false) end
Generated with the Darkfish Rdoc Generator 2.