gnarfle · October 27, 2011 17:22 · Oct 27, 2011 · Oct 27, 2011 · Oct 27, 2011 · Oct 27, 2011
diff --git a/sqlserver_extensions.rb b/sqlserver_extensions.rb
@@ -4,7 +4,6 @@ module Sqlserver
       module SchemaStatements
 
         def identity_column(table_name)
-          Rails.logger.debug 'Overridden #identity_column has been called ' + columns(table_name).detect(&:is_identity?).inspect
           columns(table_name).detect(&:is_identity?)
         end
 

diff --git a/gistfile1.txt → sqlserver_extensions.rb b/gistfile1.txt → sqlserver_extensions.rb
diff --git a/gistfile1.txt b/gistfile1.txt
@@ -1,3 +1,19 @@
+module ActiveRecord
+  module ConnectionAdapters
+    module Sqlserver
+      module SchemaStatements
+
+        def identity_column(table_name)
+          Rails.logger.debug 'Overridden #identity_column has been called ' + columns(table_name).detect(&:is_identity?).inspect
+          columns(table_name).detect(&:is_identity?)
+        end
+
+        def column_definitions(table_name)
+          db_name = unqualify_db_name(table_name)
+          db_name_with_period = "#{db_name}." if db_name
+          table_schema = unqualify_table_schema(table_name)
+          table_name = unqualify_table_name(table_name)
+          sql = %{
             SELECT DISTINCT 
             #{lowercase_schema_reflection_sql('columns.TABLE_NAME')} AS table_name,
             #{lowercase_schema_reflection_sql('columns.COLUMN_NAME')} AS name,
@@ -15,7 +31,6 @@
               ELSE NULL
             END AS [is_nullable],
             CASE 
-              WHEN CCU.COLUMN_NAME IS NOT NULL AND TC.CONSTRAINT_TYPE = N'PRIMARY KEY' THEN 1
               WHEN COLUMNPROPERTY(OBJECT_ID(columns.TABLE_SCHEMA+'.'+columns.TABLE_NAME), columns.COLUMN_NAME, 'IsIdentity') = 1 THEN 1
               ELSE NULL
             END AS [is_identity]
@@ -24,4 +39,48 @@
             LEFT OUTER JOIN #{db_name_with_period}INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME AND CCU.COLUMN_NAME = columns.COLUMN_NAME
             WHERE columns.TABLE_NAME = @0
               AND columns.TABLE_SCHEMA = #{table_schema.blank? ? "schema_name()" : "@1"}
-            ORDER BY columns.ordinal_position
+            ORDER BY columns.ordinal_position
+
+          }.gsub(/[ \t\r\n]+/,' ')
+          binds = [['table_name', table_name]]
+          binds << ['table_schema',table_schema] unless table_schema.blank?
+          results = info_schema_query { do_exec_query(sql, 'InfoSchema::ColumnDefinitions', binds) }
+          results.collect do |ci|
+            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 /^float|real$/i
+                           "#{ci[:type]}(#{ci[:numeric_precision]})"
+                         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_with_period}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
+                                 when /\A\((\w+\(\))\)\Z/
+                                   ci[:default_function] = $1
+                                   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[:is_identity] = ci[:is_identity].to_i == 1
+            ci
+          end
+        end        
+      end
+    end
+  end
+end
diff --git a/gistfile1.txt b/gistfile1.txt
@@ -15,6 +15,7 @@
               ELSE NULL
             END AS [is_nullable],
             CASE 
+              WHEN CCU.COLUMN_NAME IS NOT NULL AND TC.CONSTRAINT_TYPE = N'PRIMARY KEY' THEN 1
               WHEN COLUMNPROPERTY(OBJECT_ID(columns.TABLE_SCHEMA+'.'+columns.TABLE_NAME), columns.COLUMN_NAME, 'IsIdentity') = 1 THEN 1
               ELSE NULL
             END AS [is_identity]

diff --git a/gistfile1.txt b/gistfile1.txt
@@ -0,0 +1,26 @@
+            SELECT DISTINCT 
+            #{lowercase_schema_reflection_sql('columns.TABLE_NAME')} AS table_name,
+            #{lowercase_schema_reflection_sql('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,
+            columns.ordinal_position,
+            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') = 1 THEN 1
+              ELSE NULL
+            END AS [is_identity]
+            FROM #{db_name_with_period}INFORMATION_SCHEMA.COLUMNS columns
+            LEFT OUTER JOIN #{db_name_with_period}INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ON TC.TABLE_NAME = columns.TABLE_NAME AND TC.CONSTRAINT_TYPE = N'PRIMARY KEY' 
+            LEFT OUTER JOIN #{db_name_with_period}INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME AND CCU.COLUMN_NAME = columns.COLUMN_NAME
+            WHERE columns.TABLE_NAME = @0
+              AND columns.TABLE_SCHEMA = #{table_schema.blank? ? "schema_name()" : "@1"}
+            ORDER BY columns.ordinal_position
No results found