source.dussan.org Git - sonarqube.git/blob

   1 require 'jdbc_adapter/tsql_helper'
   2
   3 module ::ActiveRecord
   4   class Base
   5     # After setting large objects to empty, write data back with a helper method
   6     after_save :write_lobs
   7     def write_lobs() #:nodoc:
   8       if connection.is_a?(JdbcSpec::MsSQL)
   9         self.class.columns.select { |c| c.sql_type =~ /image/i }.each { |c|
  10           value = self[c.name]
  11           value = value.to_yaml if unserializable_attribute?(c.name, c)
  12           next if value.nil?  || (value == '')
  13
  14           connection.write_large_object(c.type == :binary, c.name, self.class.table_name, self.class.primary_key, quote_value(id), value)
  15         }
  16       end
  17     end
  18     private :write_lobs
  19   end
  20 end
  21
  22 module JdbcSpec
  23   module MsSQL
  24     include TSqlMethods
  25
  26     def self.column_selector
  27       [/sqlserver|tds/i, lambda {|cfg,col| col.extend(::JdbcSpec::MsSQL::Column)}]
  28     end
  29
  30     def self.adapter_selector
  31       [/sqlserver|tds/i, lambda {|cfg,adapt| adapt.extend(::JdbcSpec::MsSQL)}]
  32     end
  33
  34     module Column
  35       attr_accessor :identity, :is_special
  36
  37       def simplified_type(field_type)
  38         case field_type
  39           when /int|bigint|smallint|tinyint/i                        then :integer
  40           when /numeric/i                                            then (@scale.nil? || @scale == 0) ? :integer : :decimal
  41           when /float|double|decimal|money|real|smallmoney/i         then :decimal
  42           when /datetime|smalldatetime/i                             then :datetime
  43           when /timestamp/i                                          then :timestamp
  44           when /time/i                                               then :time
  45           when /text|ntext/i                                         then :text
  46           when /binary|image|varbinary/i                             then :binary
  47           when /char|nchar|nvarchar|string|varchar/i                 then :string
  48           when /bit/i                                                then :boolean
  49           when /uniqueidentifier/i                                   then :string
  50         end
  51       end
  52
  53       def type_cast(value)
  54         return nil if value.nil? || value == "(null)" || value == "(NULL)"
  55         case type
  56         when :string then unquote_string value
  57         when :integer then unquote(value).to_i rescue value ? 1 : 0
  58         when :primary_key then value == true || value == false ? value == true ? 1 : 0 : value.to_i
  59         when :decimal   then self.class.value_to_decimal(unquote(value))
  60         when :datetime  then cast_to_datetime(value)
  61         when :timestamp then cast_to_time(value)
  62         when :time      then cast_to_time(value)
  63         when :date      then cast_to_datetime(value)
  64         when :boolean   then value == true or (value =~ /^t(rue)?$/i) == 0 or unquote(value)=="1"
  65         when :binary    then unquote value
  66         else value
  67         end
  68       end
  69
  70       # JRUBY-2011: Match balanced quotes and parenthesis - 'text',('text') or (text)
  71       def unquote_string(value)
  72         value.sub(/^\((.*)\)$/,'\1').sub(/^'(.*)'$/,'\1')
  73       end
  74
  75       def unquote(value)
  76         value.to_s.sub(/\A\([\(\']?/, "").sub(/[\'\)]?\)\Z/, "")
  77       end
  78
  79       def cast_to_time(value)
  80         return value if value.is_a?(Time)
  81         time_array = ParseDate.parsedate(value)
  82         time_array[0] ||= 2000
  83         time_array[1] ||= 1
  84         time_array[2] ||= 1
  85         Time.send(ActiveRecord::Base.default_timezone, *time_array) rescue nil
  86       end
  87
  88       def cast_to_datetime(value)
  89         if value.is_a?(Time)
  90           if value.year != 0 and value.month != 0 and value.day != 0
  91             return value
  92           else
  93             return Time.mktime(2000, 1, 1, value.hour, value.min, value.sec) rescue nil
  94           end
  95         end
  96         return cast_to_time(value) if value.is_a?(Date) or value.is_a?(String) rescue nil
  97         value
  98       end
  99
 100       # These methods will only allow the adapter to insert binary data with a length of 7K or less
 101       # because of a SQL Server statement length policy.
 102       def self.string_to_binary(value)
 103         ''
 104       end
 105     end
 106
 107     def quote(value, column = nil)
 108       return value.quoted_id if value.respond_to?(:quoted_id)
 109
 110       case value
 111       when String, ActiveSupport::Multibyte::Chars
 112         value = value.to_s
 113         if column && column.type == :binary
 114           "'#{quote_string(JdbcSpec::MsSQL::Column.string_to_binary(value))}'" # ' (for ruby-mode)
 115         elsif column && [:integer, :float].include?(column.type)
 116           value = column.type == :integer ? value.to_i : value.to_f
 117           value.to_s
 118         else
 119           "'#{quote_string(value)}'" # ' (for ruby-mode)
 120         end
 121       when TrueClass             then '1'
 122       when FalseClass            then '0'
 123       when Time, DateTime        then "'#{value.strftime("%Y%m%d %H:%M:%S")}'"
 124       when Date                  then "'#{value.strftime("%Y%m%d")}'"
 125       else                       super
 126       end
 127     end
 128
 129     def quote_string(string)
 130       string.gsub(/\'/, "''")
 131     end
 132
 133     def quote_table_name(name)
 134       name
 135     end
 136
 137     def quote_column_name(name)
 138       "[#{name}]"
 139     end
 140
 141     def change_order_direction(order)
 142       order.split(",").collect {|fragment|
 143         case fragment
 144         when  /\bDESC\b/i     then fragment.gsub(/\bDESC\b/i, "ASC")
 145         when  /\bASC\b/i      then fragment.gsub(/\bASC\b/i, "DESC")
 146         else                  String.new(fragment).split(',').join(' DESC,') + ' DESC'
 147         end
 148       }.join(",")
 149     end
 150
 151     def recreate_database(name)
 152       drop_database(name)
 153       create_database(name)
 154     end
 155
 156     def drop_database(name)
 157       execute "DROP DATABASE #{name}"
 158     end
 159
 160     def create_database(name)
 161       execute "CREATE DATABASE #{name}"
 162     end
 163
 164       def rename_table(name, new_name)
 165         execute "EXEC sp_rename '#{name}', '#{new_name}'"
 166       end
 167
 168       # Adds a new column to the named table.
 169       # See TableDefinition#column for details of the options you can use.
 170       def add_column(table_name, column_name, type, options = {})
 171         add_column_sql = "ALTER TABLE #{table_name} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
 172         add_column_options!(add_column_sql, options)
 173         # TODO: Add support to mimic date columns, using constraints to mark them as such in the database
 174         # 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
 175         execute(add_column_sql)
 176       end
 177
 178       def rename_column(table, column, new_column_name)
 179         execute "EXEC sp_rename '#{table}.#{column}', '#{new_column_name}'"
 180       end
 181
 182       def change_column(table_name, column_name, type, options = {}) #:nodoc:
 183         sql_commands = ["ALTER TABLE #{table_name} ALTER COLUMN #{column_name} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"]
 184         if options_include_default?(options)
 185           remove_default_constraint(table_name, column_name)
 186           sql_commands << "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote(options[:default], options[:column])} FOR #{column_name}"
 187         end
 188         sql_commands.each {|c|
 189           execute(c)
 190         }
 191       end
 192       def change_column_default(table_name, column_name, default) #:nodoc:
 193         remove_default_constraint(table_name, column_name)
 194         execute "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote(default, column_name)} FOR #{column_name}"
 195       end
 196       def remove_column(table_name, column_name)
 197         remove_check_constraints(table_name, column_name)
 198         remove_default_constraint(table_name, column_name)
 199         execute "ALTER TABLE #{table_name} DROP COLUMN [#{column_name}]"
 200       end
 201
 202       def remove_default_constraint(table_name, column_name)
 203         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"
 204         defaults.each {|constraint|
 205           execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["name"]}"
 206         }
 207       end
 208
 209       def remove_check_constraints(table_name, column_name)
 210         # TODO remove all constraints in single method
 211         constraints = select "SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '#{table_name}' and COLUMN_NAME = '#{column_name}'"
 212         constraints.each do |constraint|
 213           execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["CONSTRAINT_NAME"]}"
 214         end
 215       end
 216
 217       def remove_index(table_name, options = {})
 218         execute "DROP INDEX #{table_name}.#{index_name(table_name, options)}"
 219       end
 220
 221
 222       def columns(table_name, name = nil)
 223         return [] if table_name =~ /^information_schema\./i
 224         cc = super
 225         cc.each do |col|
 226           col.identity = true if col.sql_type =~ /identity/i
 227           col.is_special = true if col.sql_type =~ /text|ntext|image/i
 228         end
 229         cc
 230       end
 231
 232       def _execute(sql, name = nil)
 233         if sql.lstrip =~ /^insert/i
 234           if query_requires_identity_insert?(sql)
 235             table_name = get_table_name(sql)
 236             with_identity_insert_enabled(table_name) do
 237               id = @connection.execute_insert(sql)
 238             end
 239           else
 240             @connection.execute_insert(sql)
 241           end
 242         elsif sql.lstrip =~ /^\(?\s*(select|show)/i
 243           repair_special_columns(sql)
 244           @connection.execute_query(sql)
 245         else
 246           @connection.execute_update(sql)
 247         end
 248       end
 249
 250
 251       private
 252       # Turns IDENTITY_INSERT ON for table during execution of the block
 253       # N.B. This sets the state of IDENTITY_INSERT to OFF after the
 254       # block has been executed without regard to its previous state
 255
 256       def with_identity_insert_enabled(table_name, &block)
 257         set_identity_insert(table_name, true)
 258         yield
 259       ensure
 260         set_identity_insert(table_name, false)
 261       end
 262
 263       def set_identity_insert(table_name, enable = true)
 264         execute "SET IDENTITY_INSERT #{table_name} #{enable ? 'ON' : 'OFF'}"
 265       rescue Exception => e
 266         raise ActiveRecord::ActiveRecordError, "IDENTITY_INSERT could not be turned #{enable ? 'ON' : 'OFF'} for table #{table_name}"
 267       end
 268
 269       def get_table_name(sql)
 270         if sql =~ /^\s*insert\s+into\s+([^\(\s,]+)\s*|^\s*update\s+([^\(\s,]+)\s*/i
 271           $1
 272         elsif sql =~ /from\s+([^\(\s,]+)\s*/i
 273           $1
 274         else
 275           nil
 276         end
 277       end
 278
 279       def identity_column(table_name)
 280         @table_columns = {} unless @table_columns
 281         @table_columns[table_name] = columns(table_name) if @table_columns[table_name] == nil
 282         @table_columns[table_name].each do |col|
 283           return col.name if col.identity
 284         end
 285
 286         return nil
 287       end
 288
 289       def query_requires_identity_insert?(sql)
 290         table_name = get_table_name(sql)
 291         id_column = identity_column(table_name)
 292         sql =~ /\[#{id_column}\]/ ? table_name : nil
 293       end
 294
 295       def get_special_columns(table_name)
 296         special = []
 297         @table_columns ||= {}
 298         @table_columns[table_name] ||= columns(table_name)
 299         @table_columns[table_name].each do |col|
 300           special << col.name if col.is_special
 301         end
 302         special
 303       end
 304
 305       def repair_special_columns(sql)
 306         special_cols = get_special_columns(get_table_name(sql))
 307         for col in special_cols.to_a
 308           sql.gsub!(Regexp.new(" #{col.to_s} = "), " #{col.to_s} LIKE ")
 309           sql.gsub!(/ORDER BY #{col.to_s}/i, '')
 310         end
 311         sql
 312       end
 313     end
 314   end
 315