Merge pull request #20041 from akshay-vishnoi/sqlite_collation

SQLite3: Add collation support for string and text columns

2 files changed, 66 insertions, 4 deletions

diff --git a/activerecord/lib/active_record/connection_adapters/sqlite3/schema_creation.rb b/activerecord/lib/active_record/connection_adapters/sqlite3/schema_creation.rb
new file mode 100644
index 0000000000..fe1dcbd710
--- /dev/null
+++ b/activerecord/lib/active_record/connection_adapters/sqlite3/schema_creation.rb
@@ -0,0 +1,15 @@
+module ActiveRecord
+  module ConnectionAdapters
+    module SQLite3
+      class SchemaCreation < AbstractAdapter::SchemaCreation
+        private
+          def add_column_options!(sql, options)
+            if options[:collation]
+              sql << " COLLATE \"#{options[:collation]}\""
+            end
+            super
+          end
+      end
+    end
+  end
+end
diff --git a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb
index 7faca9cb70..87129c42cf 100644
--- a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb
@@ -1,5 +1,6 @@
 require 'active_record/connection_adapters/abstract_adapter'
 require 'active_record/connection_adapters/statement_pool'
+require 'active_record/connection_adapters/sqlite3/schema_creation'
 
 gem 'sqlite3', '~> 1.3.6'
 require 'sqlite3'
@@ -84,6 +85,10 @@ module ActiveRecord
         end
       end
 
+      def schema_creation # :nodoc:
+        SQLite3::SchemaCreation.new self
+      end
+
       def initialize(connection, logger, connection_options, config)
         super(connection, logger)
 
@@ -344,9 +349,10 @@ module ActiveRecord
             field["dflt_value"] = $1.gsub('""', '"')
           end
 
+          collation = field['collation']
           sql_type = field['type']
           type_metadata = fetch_type_metadata(sql_type)
-          new_column(field['name'], field['dflt_value'], type_metadata, field['notnull'].to_i == 0)
+          new_column(field['name'], field['dflt_value'], type_metadata, field['notnull'].to_i == 0, nil, collation)
         end
       end
 
@@ -441,6 +447,7 @@ module ActiveRecord
             self.null    = options[:null] if options.include?(:null)
             self.precision = options[:precision] if options.include?(:precision)
             self.scale   = options[:scale] if options.include?(:scale)
+            self.collation = options[:collation] if options.include?(:collation)
           end
         end
       end
@@ -454,9 +461,9 @@ module ActiveRecord
       protected
 
         def table_structure(table_name)
-          structure = exec_query("PRAGMA table_info(#{quote_table_name(table_name)})", 'SCHEMA').to_hash
+          structure = exec_query("PRAGMA table_info(#{quote_table_name(table_name)})", 'SCHEMA')
           raise(ActiveRecord::StatementInvalid, "Could not find table '#{table_name}'") if structure.empty?
-          structure
+          table_structure_with_collation(table_name, structure)
         end
 
         def alter_table(table_name, options = {}) #:nodoc:
@@ -491,7 +498,7 @@ module ActiveRecord
               @definition.column(column_name, column.type,
                 :limit => column.limit, :default => column.default,
                 :precision => column.precision, :scale => column.scale,
-                :null => column.null)
+                :null => column.null, collation: column.collation)
             end
             yield @definition if block_given?
           end
@@ -553,6 +560,46 @@ module ActiveRecord
             super
           end
         end
+
+      private
+        COLLATE_REGEX = /.*\"(\w+)\".*collate\s+\"(\w+)\".*/i.freeze
+
+        def table_structure_with_collation(table_name, basic_structure)
+          collation_hash = {}
+          sql            = "SELECT sql FROM
+                              (SELECT * FROM sqlite_master UNION ALL
+                               SELECT * FROM sqlite_temp_master)
+                            WHERE type='table' and name='#{ table_name }' \;"
+
+          # Result will have following sample string
+          # CREATE TABLE "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+          #                       "password_digest" varchar COLLATE "NOCASE");
+          result = exec_query(sql, 'SCHEMA').first
+
+          if result
+            # Splitting with left parantheses and picking up last will return all
+            # columns separated with comma(,).
+            columns_string = result["sql"].split('(').last
+
+            columns_string.split(',').each do |column_string|
+              # This regex will match the column name and collation type and will save
+              # the value in $1 and $2 respectively.
+              collation_hash[$1] = $2 if (COLLATE_REGEX =~ column_string)
+            end
+
+            basic_structure.map! do |column|
+              column_name = column['name']
+
+              if collation_hash.has_key? column_name
+                column['collation'] = collation_hash[column_name]
+              end
+
+              column
+            end
+          else
+            basic_structure.to_hash
+          end
+        end
     end
   end
 end