Merge pull request #19978 from kamipo/collation_option_support_for_postgresql

PostgreSQL: `:collation` support for string and text columns

8 files changed, 52 insertions, 32 deletions

diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_creation.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_creation.rb
index 573189d25e..18d943f452 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/schema_creation.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_creation.rb
@@ -70,6 +70,7 @@ module ActiveRecord
             column_options[:after] = o.after
             column_options[:auto_increment] = o.auto_increment
             column_options[:primary_key] = o.primary_key
+            column_options[:collation] = o.collation
             column_options
           end
 
diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
index 1b6681f34d..0ccf0c498b 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
@@ -15,7 +15,7 @@ module ActiveRecord
     # are typically created by methods in TableDefinition, and added to the
     # +columns+ attribute of said TableDefinition object, in order to be used
     # for generating a number of table creation or table changing SQL statements.
-    class ColumnDefinition < Struct.new(:name, :type, :limit, :precision, :scale, :default, :null, :first, :after, :auto_increment, :primary_key, :sql_type) #:nodoc:
+    class ColumnDefinition < Struct.new(:name, :type, :limit, :precision, :scale, :default, :null, :first, :after, :auto_increment, :primary_key, :collation, :sql_type) #:nodoc:
 
       def primary_key?
         primary_key || type.to_sym == :primary_key
@@ -437,6 +437,7 @@ module ActiveRecord
         column.after       = options[:after]
         column.auto_increment = options[:auto_increment]
         column.primary_key = type == :primary_key || options[:primary_key]
+        column.collation   = options[:collation]
         column
       end
 
diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_dumper.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_dumper.rb
index 999cb0ec5a..deb014ad46 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/schema_dumper.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_dumper.rb
@@ -35,12 +35,16 @@ module ActiveRecord
         default = schema_default(column) if column.has_default?
         spec[:default]   = default unless default.nil?
 
+        if collation = schema_collation(column)
+          spec[:collation] = collation
+        end
+
         spec
       end
 
       # Lists the valid migration options
       def migration_keys
-        [:name, :limit, :precision, :scale, :default, :null]
+        [:name, :limit, :precision, :scale, :default, :null, :collation]
       end
 
       private
@@ -56,6 +60,10 @@ module ActiveRecord
           type.type_cast_for_schema(default)
         end
       end
+
+      def schema_collation(column)
+        column.collation.inspect if column.collation
+      end
     end
   end
 end
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
index ae42e8ef8d..0705c22a8c 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
@@ -387,8 +387,8 @@ module ActiveRecord
         end
       end
 
-      def new_column(name, default, sql_type_metadata = nil, null = true)
-        Column.new(name, default, sql_type_metadata, null)
+      def new_column(name, default, sql_type_metadata = nil, null = true, default_function = nil, collation = nil)
+        Column.new(name, default, sql_type_metadata, null, default_function, collation)
       end
 
       def lookup_cast_type(sql_type) # :nodoc:
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
index 5e5a012f85..c37e0da76b 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
@@ -14,7 +14,7 @@ module ActiveRecord
       end
 
       class ColumnDefinition < ActiveRecord::ConnectionAdapters::ColumnDefinition
-        attr_accessor :charset, :collation
+        attr_accessor :charset
       end
 
       class TableDefinition < ActiveRecord::ConnectionAdapters::TableDefinition
@@ -28,7 +28,6 @@ module ActiveRecord
             column.auto_increment = true
           end
           column.charset = options[:charset]
-          column.collation = options[:collation]
           column
         end
 
@@ -75,7 +74,6 @@ module ActiveRecord
         def column_options(o)
           column_options = super
           column_options[:charset] = o.charset
-          column_options[:collation] = o.collation
           column_options
         end
 
@@ -128,20 +126,20 @@ module ActiveRecord
         spec = super
         spec.delete(:precision) if /time/ === column.sql_type && column.precision == 0
         spec.delete(:limit)     if :boolean === column.type
+        spec
+      end
+
+      def schema_collation(column)
         if column.collation && table_name = column.instance_variable_get(:@table_name)
           @collation_cache ||= {}
           @collation_cache[table_name] ||= select_one("SHOW TABLE STATUS LIKE '#{table_name}'")["Collation"]
-          spec[:collation] = column.collation.inspect if column.collation != @collation_cache[table_name]
+          column.collation.inspect if column.collation != @collation_cache[table_name]
         end
-        spec
-      end
-
-      def migration_keys
-        super + [:collation]
       end
+      private :schema_collation
 
       class Column < ConnectionAdapters::Column # :nodoc:
-        delegate :strict, :collation, :extra, to: :sql_type_metadata, allow_nil: true
+        delegate :strict, :extra, to: :sql_type_metadata, allow_nil: true
 
         def initialize(*)
           super
@@ -195,12 +193,11 @@ module ActiveRecord
       end
 
       class MysqlTypeMetadata < DelegateClass(SqlTypeMetadata) # :nodoc:
-        attr_reader :collation, :extra, :strict
+        attr_reader :extra, :strict
 
-        def initialize(type_metadata, collation: "", extra: "", strict: false)
+        def initialize(type_metadata, extra: "", strict: false)
           super(type_metadata)
           @type_metadata = type_metadata
-          @collation = collation
           @extra = extra
           @strict = strict
         end
@@ -218,7 +215,7 @@ module ActiveRecord
         protected
 
         def attributes_for_hash
-          [self.class, @type_metadata, collation, extra, strict]
+          [self.class, @type_metadata, extra, strict]
         end
       end
 
@@ -342,8 +339,8 @@ module ActiveRecord
         raise NotImplementedError
       end
 
-      def new_column(field, default, sql_type_metadata = nil, null = true) # :nodoc:
-        Column.new(field, default, sql_type_metadata, null)
+      def new_column(field, default, sql_type_metadata = nil, null = true, default_function = nil, collation = nil) # :nodoc:
+        Column.new(field, default, sql_type_metadata, null, default_function, collation)
       end
 
       # Must return the MySQL error number from the exception, if the exception has an
@@ -566,8 +563,8 @@ module ActiveRecord
           each_hash(result).map do |field|
             field_name = set_field_encoding(field[:Field])
             sql_type = field[:Type]
-            type_metadata = fetch_type_metadata(sql_type, field[:Collation], field[:Extra])
-            new_column(field_name, field[:Default], type_metadata, field[:Null] == "YES")
+            type_metadata = fetch_type_metadata(sql_type, field[:Extra])
+            new_column(field_name, field[:Default], type_metadata, field[:Null] == "YES", nil, field[:Collation])
           end
         end
       end
@@ -826,8 +823,8 @@ module ActiveRecord
         end
       end
 
-      def fetch_type_metadata(sql_type, collation = "", extra = "")
-        MysqlTypeMetadata.new(super(sql_type), collation: collation, extra: extra, strict: strict_mode?)
+      def fetch_type_metadata(sql_type, extra = "")
+        MysqlTypeMetadata.new(super(sql_type), extra: extra, strict: strict_mode?)
       end
 
       # MySQL is too stupid to create a temporary table for use subquery, so we have
diff --git a/activerecord/lib/active_record/connection_adapters/column.rb b/activerecord/lib/active_record/connection_adapters/column.rb
index f4dda5154e..4b95b0681d 100644
--- a/activerecord/lib/active_record/connection_adapters/column.rb
+++ b/activerecord/lib/active_record/connection_adapters/column.rb
@@ -12,7 +12,7 @@ module ActiveRecord
         ISO_DATETIME = /\A(\d{4})-(\d\d)-(\d\d) (\d\d):(\d\d):(\d\d)(\.\d+)?\z/
       end
 
-      attr_reader :name, :null, :sql_type_metadata, :default, :default_function
+      attr_reader :name, :null, :sql_type_metadata, :default, :default_function, :collation
 
       delegate :precision, :scale, :limit, :type, :sql_type, to: :sql_type_metadata, allow_nil: true
 
@@ -22,12 +22,13 @@ module ActiveRecord
       # +default+ is the type-casted default value, such as +new+ in <tt>sales_stage varchar(20) default 'new'</tt>.
       # +sql_type_metadata+ is various information about the type of the column
       # +null+ determines if this column allows +NULL+ values.
-      def initialize(name, default, sql_type_metadata = nil, null = true, default_function = nil)
+      def initialize(name, default, sql_type_metadata = nil, null = true, default_function = nil, collation = nil)
         @name = name
         @sql_type_metadata = sql_type_metadata
         @null = null
         @default = default
         @default_function = default_function
+        @collation = collation
         @table_name = nil
       end
 
@@ -60,7 +61,7 @@ module ActiveRecord
       protected
 
       def attributes_for_hash
-        [self.class, name, default, sql_type_metadata, null, default_function]
+        [self.class, name, default, sql_type_metadata, null, default_function, collation]
       end
     end
 
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
index 050b815aad..662c6b4d38 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
@@ -8,6 +8,13 @@ module ActiveRecord
           o.sql_type = type_to_sql(o.type, o.limit, o.precision, o.scale, o.array)
           super
         end
+
+        def add_column_options!(sql, options)
+          if options[:collation]
+            sql << " COLLATE \"#{options[:collation]}\""
+          end
+          super
+        end
       end
 
       module SchemaStatements
@@ -159,18 +166,18 @@ module ActiveRecord
         # Returns the list of all column definitions for a table.
         def columns(table_name)
           # Limit, precision, and scale are all handled by the superclass.
-          column_definitions(table_name).map do |column_name, type, default, notnull, oid, fmod|
+          column_definitions(table_name).map do |column_name, type, default, notnull, oid, fmod, collation|
             oid = oid.to_i
             fmod = fmod.to_i
             type_metadata = fetch_type_metadata(column_name, type, oid, fmod)
             default_value = extract_value_from_default(default)
             default_function = extract_default_function(default_value, default)
-            new_column(column_name, default_value, type_metadata, !notnull, default_function)
+            new_column(column_name, default_value, type_metadata, !notnull, default_function, collation)
           end
         end
 
-        def new_column(name, default, sql_type_metadata = nil, null = true, default_function = nil) # :nodoc:
-          PostgreSQLColumn.new(name, default, sql_type_metadata, null, default_function)
+        def new_column(name, default, sql_type_metadata = nil, null = true, default_function = nil, collation = nil) # :nodoc:
+          PostgreSQLColumn.new(name, default, sql_type_metadata, null, default_function, collation)
         end
 
         # Returns the current database name.
@@ -409,6 +416,9 @@ module ActiveRecord
           quoted_column_name = quote_column_name(column_name)
           sql_type = type_to_sql(type, options[:limit], options[:precision], options[:scale], options[:array])
           sql = "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quoted_column_name} TYPE #{sql_type}"
+          if options[:collation]
+            sql << " COLLATE \"#{options[:collation]}\""
+          end
           if options[:using]
             sql << " USING #{options[:using]}"
           elsif options[:cast_as]
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
index 332ac9d88c..7e15c2ab26 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
@@ -772,7 +772,9 @@ module ActiveRecord
         def column_definitions(table_name) # :nodoc:
           exec_query(<<-end_sql, 'SCHEMA').rows
               SELECT a.attname, format_type(a.atttypid, a.atttypmod),
-                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
+                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod,
+             (SELECT c.collname FROM pg_collation c, pg_type t
+               WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation)
                 FROM pg_attribute a LEFT JOIN pg_attrdef d
                   ON a.attrelid = d.adrelid AND a.attnum = d.adnum
                WHERE a.attrelid = '#{quote_table_name(table_name)}'::regclass