Class: ActiveRecord::ConnectionAdapters::SQLServerAdapter

Inherits:
Object
  • Object
show all
Includes:
Schemas::ConnectionAdapters::SqlserverAdapter
Defined in:
lib/activerecord-sqlserver-adapter-schemas.rb

Instance Attribute Summary

Attributes included from Schemas::ConnectionAdapters::SqlserverAdapter

#default_schema

Instance Method Summary collapse

Methods included from Schemas::ConnectionAdapters::SqlserverAdapter

#columns_with_default_schema_check, included, #index_name, #table_exists_with_default_schema_check?, #table_name_or_views_table_name_with_default_schema_check, #unqualify_schema_name, #unqualify_table_name_if_default_schema

Instance Method Details

#column_definitions(table_name) ⇒ Object 



91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
 
# File 'lib/activerecord-sqlserver-adapter-schemas.rb', line 91

def column_definitions(table_name)
  db_name = unqualify_db_name(table_name)
  db_name << '.' if db_name
  schema_name = unqualify_schema_name(table_name)
  table_name = unqualify_table_name(table_name)
  sql = %{
    SELECT
    columns.TABLE_NAME as table_name,
    columns.COLUMN_NAME as name,
    columns.DATA_TYPE as type,
    columns.COLUMN_DEFAULT as default_value,
    columns.NUMERIC_SCALE as numeric_scale,
    columns.NUMERIC_PRECISION as numeric_precision,
    CASE
      WHEN columns.DATA_TYPE IN ('nchar','nvarchar') THEN columns.CHARACTER_MAXIMUM_LENGTH
      ELSE COL_LENGTH(columns.TABLE_SCHEMA+'.'+columns.TABLE_NAME, columns.COLUMN_NAME)
    END as length,
    CASE
      WHEN columns.IS_NULLABLE = 'YES' THEN 1
      ELSE NULL
    end as is_nullable,
    CASE
      WHEN COLUMNPROPERTY(OBJECT_ID(columns.TABLE_SCHEMA+'.'+columns.TABLE_NAME), columns.COLUMN_NAME, 'IsIdentity') = 0 THEN NULL
      ELSE 1
    END as is_identity
    FROM #{db_name}INFORMATION_SCHEMA.COLUMNS columns
    WHERE columns.TABLE_NAME = '#{table_name}'
    #{"AND columns.TABLE_SCHEMA = '#{schema_name}'" if schema_name}
    ORDER BY columns.ordinal_position
  }.gsub(/[ \t\r\n]+/,' ')
  results = info_schema_query { select(sql,nil,true) }
  results.collect do |ci|
    ci.symbolize_keys!
    ci[:type] = case ci[:type]
                when /^bit|image|text|ntext|datetime$/
                  ci[:type]
                when /^numeric|decimal$/i
                  "#{ci[:type]}(#{ci[:numeric_precision]},#{ci[:numeric_scale]})"
                when /^char|nchar|varchar|nvarchar|varbinary|bigint|int|smallint$/
                  ci[:length].to_i == -1 ? "#{ci[:type]}(max)" : "#{ci[:type]}(#{ci[:length]})"
                else
                  ci[:type]
                end
    if ci[:default_value].nil? && views.include?(table_name)
      real_table_name = table_name_or_views_table_name(table_name)
      real_column_name = views_real_column_name(table_name,ci[:name])
      col_default_sql = "SELECT c.COLUMN_DEFAULT FROM #{db_name}INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = '#{real_table_name}' AND c.COLUMN_NAME = '#{real_column_name}'"
      ci[:default_value] = info_schema_query { select_value(col_default_sql) }
    end
    ci[:default_value] = case ci[:default_value]
                         when nil, '(null)', '(NULL)'
                           nil
                         else
                           match_data = ci[:default_value].match(/\A\(+N?'?(.*?)'?\)+\Z/m)
                           match_data ? match_data[1] : nil
                         end
    ci[:null] = ci[:is_nullable].to_i == 1 ; ci.delete(:is_nullable)
    ci
  end
end

#indexes(table_name, name = nil) ⇒ Object

overridden to support schemas. sp_helpindex does not support linked servers



50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
 
# File 'lib/activerecord-sqlserver-adapter-schemas.rb', line 50

def indexes(table_name, name = nil)
  db_name = unqualify_db_name(table_name)
  db_name << '.' if db_name
  schema_name = unqualify_schema_name(table_name) << '.'
  table_name = unqualify_table_name(table_name)

  select("EXEC sp_helpindex '#{quote_table_name("#{db_name}#{schema_name}#{table_name}")}'",name).inject([]) do |indexes,index|
    if index['index_description'] =~ /primary key/
      indexes
    else
      name    = index['index_name']
      unique  = index['index_description'] =~ /unique/
      columns = index['index_keys'].split(',').map do |column|
        column.strip!
        column.gsub! '(-)', '' if column.ends_with?('(-)')
        column
      end
      indexes << ::ActiveRecord::ConnectionAdapters::IndexDefinition.new(table_name, name, unique, columns)
    end
  end
end

#quote_column_name(column_name) ⇒ Object

This method is overridden to support references such as database..table



43
44
45
46
47
 
# File 'lib/activerecord-sqlserver-adapter-schemas.rb', line 43

def quote_column_name(column_name)
  column_name.to_s.split('..').collect do |part|
    part.split('.').map{ |name| name =~ /^\[.*\]$/ ? name : "[#{name}]" }.join('.')
  end.join('..')
end

#tables(name = nil) ⇒ Object

This method is overridden to support schema names



22
23
24
25
26
27
28
29
 
# File 'lib/activerecord-sqlserver-adapter-schemas.rb', line 22

def tables(name = nil)
  # return schema.table unless the schema is the default schema, in which case just return table
  info_schema_query do
    select_values("SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME <> 'dtproperties'").collect do |table|
      default_schema && table.index("#{default_schema}.") == 0 ? table[default_schema.length + 1..table.length] : table
    end
  end
end

#unqualify_db_name(table_name) ⇒ Object

This method is overridden to support linked servers



16
17
18
19
 
# File 'lib/activerecord-sqlserver-adapter-schemas.rb', line 16

def unqualify_db_name(table_name)
  table_names = table_name.to_s.split('.')
  table_names.length >= 3 ? table_names[0...table_names.length - 2].join('.').tr('[]','') : nil
end

#view_information(table_name) ⇒ Object 



72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
 
# File 'lib/activerecord-sqlserver-adapter-schemas.rb', line 72

def view_information(table_name)
  db_name = unqualify_db_name(table_name)
  schema_name = unqualify_schema_name(table_name)
  table_name = unqualify_table_name(table_name)
  
  @@sqlserver_view_information_cache ||= {}
  @@sqlserver_view_information_cache[table_name.downcase] ||= begin
    sql = "SELECT * FROM #{"#{db_name}." if db_name}INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = '#{table_name}'"
    sql << " and TABLE_SCHEMA = '#{schema_name}'" if schema_name
    view_info = info_schema_query { select_one(sql) }
    if view_info
      if view_info['VIEW_DEFINITION'].blank? || view_info['VIEW_DEFINITION'].length == 4000
        view_info['VIEW_DEFINITION'] = info_schema_query { select_values("EXEC sp_helptext #{table_name}").join }
      end
    end
    view_info
  end
end

#views(name = nil) ⇒ Object

This method is overridden to support schema names



32
33
34
35
36
37
38
39
40
 
# File 'lib/activerecord-sqlserver-adapter-schemas.rb', line 32

def views(name = nil)
  # return schema.view unless the schema is the default schema, in which case just return view
  @sqlserver_views_cache ||= 
    info_schema_query do
       select_values("SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME NOT IN ('sysconstraints','syssegments')").collect do |view|
         default_schema && view.index("#{default_schema}.") == 0 ? view[default_schema.length + 1..view.length] : view
       end
    end
end