Add support for specifying comments for tables, columns, and indexes.

Comments are specified in migrations, stored in database itself (in its schema),
and dumped into db/schema.rb file.

This allows to generate good documentation and explain columns and tables' purpose
to everyone from new developers to database administrators.

For PostgreSQL and MySQL only. SQLite does not support comments at the moment.

See docs for PostgreSQL: http://www.postgresql.org/docs/current/static/sql-comment.html
See docs for MySQL: http://dev.mysql.com/doc/refman/5.7/en/create-table.html

1 files changed, 43 insertions, 8 deletions

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 39e8492688..36331204f7 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
@@ -1,3 +1,5 @@
+require 'active_support/core_ext/string/strip'
+
 module ActiveRecord
   module ConnectionAdapters
     module PostgreSQL
@@ -172,7 +174,8 @@ module ActiveRecord
           table = Utils.extract_schema_qualified_name(table_name.to_s)
 
           result = query(<<-SQL, 'SCHEMA')
-            SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid
+            SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid,
+                            pg_catalog.obj_description(i.oid, 'pg_class') AS comment
             FROM pg_class t
             INNER JOIN pg_index d ON t.oid = d.indrelid
             INNER JOIN pg_class i ON d.indexrelid = i.oid
@@ -190,6 +193,7 @@ module ActiveRecord
             indkey = row[2].split(" ").map(&:to_i)
             inddef = row[3]
             oid = row[4]
+            comment = row[5]
 
             columns = Hash[query(<<-SQL, "SCHEMA")]
             SELECT a.attnum, a.attname
@@ -207,7 +211,7 @@ module ActiveRecord
               where = inddef.scan(/WHERE (.+)$/).flatten[0]
               using = inddef.scan(/USING (.+?) /).flatten[0].to_sym
 
-              IndexDefinition.new(table_name, index_name, unique, column_names, [], orders, where, nil, using)
+              IndexDefinition.new(table_name, index_name, unique, column_names, [], orders, where, nil, using, comment)
             end
           end.compact
         end
@@ -215,18 +219,33 @@ module ActiveRecord
         # Returns the list of all column definitions for a table.
         def columns(table_name) # :nodoc:
           table_name = table_name.to_s
-          column_definitions(table_name).map do |column_name, type, default, notnull, oid, fmod, collation|
+          column_definitions(table_name).map do |column_name, type, default, notnull, oid, fmod, collation, comment|
             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, table_name, default_function, collation)
+            new_column(column_name, default_value, type_metadata, !notnull, table_name, default_function, collation, comment)
           end
         end
 
-        def new_column(name, default, sql_type_metadata, null, table_name, default_function = nil, collation = nil) # :nodoc:
-          PostgreSQLColumn.new(name, default, sql_type_metadata, null, table_name, default_function, collation)
+        def new_column(name, default, sql_type_metadata, null, table_name, default_function = nil, collation = nil, comment = nil) # :nodoc:
+          PostgreSQLColumn.new(name, default, sql_type_metadata, null, table_name, default_function, collation, comment)
+        end
+
+        # Returns a comment stored in database for given table
+        def table_comment(table_name) # :nodoc:
+          name = Utils.extract_schema_qualified_name(table_name.to_s)
+          return nil unless name.identifier
+
+          select_value(<<-SQL.strip_heredoc, 'SCHEMA')
+            SELECT pg_catalog.obj_description(c.oid, 'pg_class')
+            FROM pg_catalog.pg_class c
+              LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
+            WHERE c.relname = '#{name.identifier}'
+              AND c.relkind IN ('r') -- (r)elation/table
+              AND n.nspname = #{name.schema ? "'#{name.schema}'" : 'ANY (current_schemas(false))'}
+          SQL
         end
 
         # Returns the current database name.
@@ -445,6 +464,7 @@ module ActiveRecord
         def add_column(table_name, column_name, type, options = {}) #:nodoc:
           clear_cache!
           super
+          change_column_comment(table_name, column_name, options[:comment]) if options.key?(:comment)
         end
 
         def change_column(table_name, column_name, type, options = {}) #:nodoc:
@@ -466,6 +486,7 @@ module ActiveRecord
 
           change_column_default(table_name, column_name, options[:default]) if options_include_default?(options)
           change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null)
+          change_column_comment(table_name, column_name, options[:comment]) if options.key?(:comment)
         end
 
         # Changes the default value of a table column.
@@ -494,6 +515,18 @@ module ActiveRecord
           execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL")
         end
 
+        # Adds comment for given table column or drops it if +comment+ is a +nil+
+        def change_column_comment(table_name, column_name, comment) # :nodoc:
+          clear_cache!
+          execute "COMMENT ON COLUMN #{quote_table_name(table_name)}.#{quote_column_name(column_name)} IS #{quote(comment)}"
+        end
+
+        # Adds comment for given table or drops it if +comment+ is a +nil+
+        def change_table_comment(table_name, comment) # :nodoc:
+          clear_cache!
+          execute "COMMENT ON TABLE #{quote_table_name(table_name)} IS #{quote(comment)}"
+        end
+
         # Renames a column in a table.
         def rename_column(table_name, column_name, new_column_name) #:nodoc:
           clear_cache!
@@ -502,8 +535,10 @@ module ActiveRecord
         end
 
         def add_index(table_name, column_name, options = {}) #:nodoc:
-          index_name, index_type, index_columns, index_options, index_algorithm, index_using = add_index_options(table_name, column_name, options)
-          execute "CREATE #{index_type} INDEX #{index_algorithm} #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} #{index_using} (#{index_columns})#{index_options}"
+          index_name, index_type, index_columns, index_options, index_algorithm, index_using, comment = add_index_options(table_name, column_name, options)
+          result = execute "CREATE #{index_type} INDEX #{index_algorithm} #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} #{index_using} (#{index_columns})#{index_options}"
+          execute "COMMENT ON INDEX #{quote_column_name(index_name)} IS #{quote(comment)}" if comment
+          result # Result of execute is used in tests in activerecord/test/cases/adapters/postgresql/active_schema_test.rb
         end
 
         def remove_index(table_name, options = {}) #:nodoc: