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

#index_name, #unqualify_schema_name, #unqualify_table_name_if_default_schema

Instance Method Details

#column_definitions(table_name) ⇒ Object 



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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
 
# File 'lib/activerecord-sqlserver-adapter-schemas.rb', line 109

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

#columns(table_name, name = nil) ⇒ Object 



51
52
53
54
55
56
57
58
 
# File 'lib/activerecord-sqlserver-adapter-schemas.rb', line 51

def columns(table_name, name = nil)
  return [] if table_name.blank?
  cache_key = unqualify_table_name_if_default_schema(table_name)
  @sqlserver_columns_cache[cache_key] ||= column_definitions(table_name).collect do |ci|
    sqlserver_options = ci.except(:name,:default_value,:type,:null).merge(:database_year=>database_year)
    ::ActiveRecord::ConnectionAdapters::SQLServerColumn.new ci[:name], ci[:default_value], ci[:type], ci[:null], sqlserver_options
  end
end

#indexes(table_name, name = nil) ⇒ Object

overridden to support schemas. sp_helpindex does not support linked servers



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

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



61
62
63
64
65
 
# File 'lib/activerecord-sqlserver-adapter-schemas.rb', line 61

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

#table_exists?(table_name) ⇒ Boolean

Returns:

  • (Boolean) 


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

def table_exists?(table_name)
  table_name = unqualify_table_name_if_default_schema(table_name)
  super(table_name) || tables.include?(table_name) || views.include?(table_name)
end

#table_name_or_views_table_name(table_name) ⇒ Object 



20
21
22
23
 
# File 'lib/activerecord-sqlserver-adapter-schemas.rb', line 20

def table_name_or_views_table_name(table_name)
  unquoted_table_name = unqualify_table_name_if_default_schema(table_name)
  views.include?(unquoted_table_name) ? view_table_name(unquoted_table_name) : unquoted_table_name
end

#tables(name = nil) ⇒ Object

This method is overridden to support schema names



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

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



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

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 



90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
 
# File 'lib/activerecord-sqlserver-adapter-schemas.rb', line 90

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



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

def views(name = nil)
  # return schema.view unless the schema is the default schema, in which case just return view
  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