Virtual/generated column support for MySQL 5.7.5+ and MariaDB 5.2.0+

MySQL generated columns: https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html
MariaDB virtual columns: https://mariadb.com/kb/en/mariadb/virtual-computed-columns/

Declare virtual columns with `t.virtual name, type: …, as: "expression"`.
Pass `stored: true` to persist the generated value (false by default).

Example:

    create_table :generated_columns do |t|
      t.string  :name
      t.virtual :upper_name, type: :string,  as: "UPPER(name)"
      t.virtual :name_length, type: :integer, as: "LENGTH(name)", stored: true
      t.index :name_length  # May be indexed, too!
    end

Closes #22589

9 files changed, 76 insertions, 14 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 807df2184a..81dec97bf7 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/schema_creation.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_creation.rb
@@ -106,6 +106,7 @@ module ActiveRecord
             column_options[:primary_key] = o.primary_key
             column_options[:collation] = o.collation
             column_options[:comment] = o.comment
+            column_options[:as] = o.as
             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 b518ef760b..ecc6caa8f2 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
@@ -9,7 +9,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.
-    ColumnDefinition = Struct.new(:name, :type, :limit, :precision, :scale, :default, :null, :first, :after, :auto_increment, :primary_key, :collation, :sql_type, :comment) do #:nodoc:
+    ColumnDefinition = Struct.new(:name, :type, :limit, :precision, :scale, :default, :null, :first, :after, :auto_increment, :primary_key, :collation, :sql_type, :comment, :as) do # :nodoc:
       def primary_key?
         primary_key || type.to_sym == :primary_key
       end
@@ -173,6 +173,7 @@ module ActiveRecord
         :text,
         :time,
         :timestamp,
+        :virtual,
       ].each do |column_type|
         module_eval <<-CODE, __FILE__, __LINE__ + 1
           def #{column_type}(*args, **options)
@@ -374,6 +375,7 @@ module ActiveRecord
         column.primary_key = type == :primary_key || options[:primary_key]
         column.collation   = options[:collation]
         column.comment     = options[:comment]
+        column.as          = options[:as]
         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 b912d24626..d6c912a69e 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/schema_dumper.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_dumper.rb
@@ -7,7 +7,7 @@ module ActiveRecord
     # Adapter level by over-writing this code inside the database specific adapters
     module ColumnDumper
       def column_spec(column)
-        [schema_type(column), prepare_column_options(column)]
+        [schema_type_with_virtual(column), prepare_column_options(column)]
       end
 
       def column_spec_for_primary_key(column)
@@ -59,6 +59,14 @@ module ActiveRecord
           schema_type(column) == :bigint
         end
 
+        def schema_type_with_virtual(column)
+          if supports_virtual_columns? && column.virtual?
+            :virtual
+          else
+            schema_type(column)
+          end
+        end
+
         def schema_type(column)
           if column.bigint?
             :bigint
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
index 348396de72..fd11cab5c0 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
@@ -346,6 +346,11 @@ module ActiveRecord
         true
       end
 
+      # Does this adapter support virtual columns?
+      def supports_virtual_columns?
+        false
+      end
+
       # This is meant to be implemented by the adapters that support extensions
       def disable_extension(name)
       end
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 aedf4581f5..89b4ec9473 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
@@ -141,6 +141,14 @@ module ActiveRecord
         end
       end
 
+      def supports_virtual_columns?
+        if mariadb?
+          version >= "5.2.0"
+        else
+          version >= "5.7.5"
+        end
+      end
+
       def supports_advisory_locks?
         true
       end
diff --git a/activerecord/lib/active_record/connection_adapters/mysql/column.rb b/activerecord/lib/active_record/connection_adapters/mysql/column.rb
index 1499c1681f..c9ad47c035 100644
--- a/activerecord/lib/active_record/connection_adapters/mysql/column.rb
+++ b/activerecord/lib/active_record/connection_adapters/mysql/column.rb
@@ -15,6 +15,10 @@ module ActiveRecord
         def auto_increment?
           extra == "auto_increment"
         end
+
+        def virtual?
+          /\b(?:VIRTUAL|STORED|PERSISTENT)\b/.match?(extra)
+        end
       end
     end
   end
diff --git a/activerecord/lib/active_record/connection_adapters/mysql/schema_creation.rb b/activerecord/lib/active_record/connection_adapters/mysql/schema_creation.rb
index d808b50332..39c2acbca9 100644
--- a/activerecord/lib/active_record/connection_adapters/mysql/schema_creation.rb
+++ b/activerecord/lib/active_record/connection_adapters/mysql/schema_creation.rb
@@ -2,8 +2,8 @@ module ActiveRecord
   module ConnectionAdapters
     module MySQL
       class SchemaCreation < AbstractAdapter::SchemaCreation
-        delegate :add_sql_comment!, to: :@conn
-        private :add_sql_comment!
+        delegate :add_sql_comment!, :mariadb?, to: :@conn
+        private :add_sql_comment!, :mariadb?
 
         private
 
@@ -32,6 +32,7 @@ module ActiveRecord
           def column_options(o)
             column_options = super
             column_options[:charset] = o.charset
+            column_options[:stored] = o.stored
             column_options
           end
 
@@ -44,6 +45,13 @@ module ActiveRecord
               sql << " COLLATE #{collation}"
             end
 
+            if as = options[:as]
+              sql << " AS (#{as})"
+              if options[:stored]
+                sql << (mariadb? ? " PERSISTENT" : " STORED")
+              end
+            end
+
             add_sql_comment!(super, options[:comment])
           end
 
diff --git a/activerecord/lib/active_record/connection_adapters/mysql/schema_definitions.rb b/activerecord/lib/active_record/connection_adapters/mysql/schema_definitions.rb
index f1ba0cb708..76ebd0bf6c 100644
--- a/activerecord/lib/active_record/connection_adapters/mysql/schema_definitions.rb
+++ b/activerecord/lib/active_record/connection_adapters/mysql/schema_definitions.rb
@@ -3,8 +3,7 @@ module ActiveRecord
     module MySQL
       module ColumnMethods
         def primary_key(name, type = :primary_key, **options)
-          options[:auto_increment] = true if [:primary_key, :integer, :bigint].include?(type) && !options.key?(:default)
-          options[:limit] = 8 if [:primary_key].include?(type)
+          options[:auto_increment] = true if [:integer, :bigint].include?(type) && !options.key?(:default)
           super
         end
 
@@ -58,24 +57,29 @@ module ActiveRecord
       end
 
       class ColumnDefinition < ActiveRecord::ConnectionAdapters::ColumnDefinition
-        attr_accessor :charset, :unsigned
+        attr_accessor :charset, :unsigned, :stored
       end
 
       class TableDefinition < ActiveRecord::ConnectionAdapters::TableDefinition
         include ColumnMethods
 
         def new_column_definition(name, type, options) # :nodoc:
-          column = super
-          case column.type
+          case type
+          when :virtual
+            type = options[:type]
           when :primary_key
-            column.type = :integer
-            column.auto_increment = true
+            type = :integer
+            options[:limit] ||= 8
+            options[:auto_increment] = true
+            options[:primary_key] = true
           when /\Aunsigned_(?<type>.+)\z/
-            column.type = $~[:type].to_sym
-            column.unsigned = true
+            type = $~[:type].to_sym
+            options[:unsigned] = true
           end
-          column.unsigned ||= options[:unsigned]
+          column = super
+          column.unsigned = options[:unsigned]
           column.charset = options[:charset]
+          column.stored = options[:stored]
           column
         end
 
diff --git a/activerecord/lib/active_record/connection_adapters/mysql/schema_dumper.rb b/activerecord/lib/active_record/connection_adapters/mysql/schema_dumper.rb
index d44c35714f..7a277b8cfd 100644
--- a/activerecord/lib/active_record/connection_adapters/mysql/schema_dumper.rb
+++ b/activerecord/lib/active_record/connection_adapters/mysql/schema_dumper.rb
@@ -14,6 +14,13 @@ module ActiveRecord
         def prepare_column_options(column)
           spec = super
           spec[:unsigned] = "true" if column.unsigned?
+
+          if supports_virtual_columns? && column.virtual?
+            spec[:as] = extract_expression_for_virtual_column(column)
+            spec[:stored] = "true" if /\b(?:STORED|PERSISTENT)\b/.match?(column.extra)
+            spec = { type: schema_type(column).inspect }.merge!(spec)
+          end
+
           spec
         end
 
@@ -46,6 +53,21 @@ module ActiveRecord
               column.collation.inspect if column.collation != @table_collation_cache[table_name]
             end
           end
+
+          def extract_expression_for_virtual_column(column)
+            if mariadb?
+              create_table_info = create_table_info(column.table_name)
+              if %r/#{quote_column_name(column.name)} #{Regexp.quote(column.sql_type)} AS \((?<expression>.+?)\) #{column.extra}/m =~ create_table_info
+                $~[:expression].inspect
+              end
+            else
+              sql = "SELECT generation_expression FROM information_schema.columns" \
+                    " WHERE table_schema = #{quote(@config[:database])}" \
+                    "   AND table_name = #{quote(column.table_name)}" \
+                    "   AND column_name = #{quote(column.name)}"
+              select_value(sql, "SCHEMA").inspect
+            end
+          end
       end
     end
   end