Rails - Apr 2005 - [Tip] Introspection to determine if a column may be null

I still want to get this into Rails for all connection adaptors, but 
for now I''ve hacked my own addition on for just PostgreSQL.

The following code extends every Column object returned by 
MyModel.columns to support a new #required? method, which indicates if 
the column may or may not be null.

The following is a huge hack - suggestions on a cleaner way to add this 
functionality (without changing the postgresql adaptor itself) are very 
much welcomed. As our implementations for any other adaptors.


# From my own lib/rails_extensions.rb file, included in 
config/environment.rb
class ActiveRecord::ConnectionAdapters::Column
   def required?
     @required
   end
end

class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
   alias_method :_real_columns, :columns
   def columns( table_name, name=nil )
     cols = _real_columns( table_name, name )
     cols.each{ |col|
       sql = "select is_nullable from information_schema.columns where 
table_name=''#{table_name}'' and
column_name=''#{col.name}''"
       col_is_required = nil
       log(sql, nil, @connection) { |connection| col_is_required = 
connection.query(sql)[0][0].downcase != ''yes'' }
       col.instance_variable_set( :@required, col_is_required )
     }
   end
end

--
(-, /\ \/ / /\/

This among other autovalidation features is already in the works, see
tickets #1112 and #608. Still neither implementation infers column
non-nullness on Oracle, SqlServer, SqlLite or db2 adapters. If you
wish to help, adding support for these adapters would be welcomed.

Cheers,
Zsombor

On 4/23/05, Gavin Kistner <gavin-XtLdkLkwz3ZWk0Htik3J/w@public.gmane.org>
wrote:
> I still want to get this into Rails for all connection adaptors, but
> for now I''ve hacked my own addition on for just PostgreSQL.
> 
> The following code extends every Column object returned by
> MyModel.columns to support a new #required? method, which indicates if
> the column may or may not be null.
> 
> The following is a huge hack - suggestions on a cleaner way to add this
> functionality (without changing the postgresql adaptor itself) are very
> much welcomed. As our implementations for any other adaptors.
> 
> # From my own lib/rails_extensions.rb file, included in
> config/environment.rb
> class ActiveRecord::ConnectionAdapters::Column
>    def required?
>      @required
>    end
> end
> 
> class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
>    alias_method :_real_columns, :columns
>    def columns( table_name, name=nil )
>      cols = _real_columns( table_name, name )
>      cols.each{ |col|
>        sql = "select is_nullable from information_schema.columns where
> table_name=''#{table_name}'' and
column_name=''#{col.name}''"
>        col_is_required = nil
>        log(sql, nil, @connection) { |connection| col_is_required >
connection.query(sql)[0][0].downcase != ''yes'' }
>        col.instance_variable_set( :@required, col_is_required )
>      }
>    end
> end
> 
> --
> (-, /\ \/ / /\/
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
http://deezsombor.blogspot.com

On Apr 24, 2005, at 6:26 AM, Dee Zsombor wrote:
> This among other autovalidation features is already in the works, see
> tickets #1112 and #608. Still neither implementation infers column
> non-nullness on Oracle, SqlServer, SqlLite or db2 adapters. If you
> wish to help, adding support for these adapters would be welcomed.
I''ll see what I can do to help. However, this isn''t about 
autovalidation - this is about introspection.

I''m writing my own scaffold-type functions. When generating the form 
for adding/editing a record, I want to produce HTML which flags (in 
text, CSS and JS) non-null fields as ''required''. So, I need to
be able
to look at the definition for a table/column and determine properties 
on it that exist in the DB schema, but which ActiveRecord does not 
expose.

It''s additionally cool if rails automatically determines that saving a 
record without a required field will go wrong, but I need to know about 
the schema separate from any particular row instance.

In addition to the required/non-null feature that I''ve just added for 
pgsql, I''m shortly going to be needing to add some way to deduce 
foreign-key relationships for a particular column.


By the way, the code I posted to start this thread was very separate 
from the existing implementation of the postgresql adaptor--it 
"piggybacked" onto the work of the other as a completely separate code
branch--but it produced one sql select per column every time 
Model#columns was asked for. Terrible!

So I decided that it was better (for me) to simple override/rewrite 
some of the existing functions in the adaptor. The new code (which 
produces no additional SQL calls) follows:

class ActiveRecord::ConnectionAdapters::Column
   def required?
     @required
   end
end

module ActiveRecord
   module ConnectionAdapters
     class PostgreSQLAdapter
       # Updated from the original to draw out the is_nullable 
information
       def table_structure(table_name)
         database_name = @connection.db
         schema_name, table_name = split_table_schema(table_name)

         # Grab a list of all the default values for the columns.
         sql =  "SELECT column_name, column_default, 
character_maximum_length, data_type, is_nullable=''NO'' as
required"
         sql << "  FROM information_schema.columns "
         sql << " WHERE table_catalog =
''#{database_name}'' "
         sql << "   AND table_schema =
''#{schema_name}'' "
         sql << "   AND table_name =
''#{table_name}'';"

         column_defaults = nil
         log(sql, nil, @connection) { |connection| column_defaults = 
connection.query(sql) }
         column_defaults.collect do |row|
             field   = row[0]
             type    = type_as_string(row[3], row[2])
             default = default_value(row[1])
             length  = row[2]
             reqd    = row[4] == ''t''

             [field, type, default, length, reqd]
         end
       end

       # Updated from the original to save the ''required''
information
       def columns(table_name, name = nil)
         table_structure(table_name).inject([]) do |columns, field|
           col = Column.new(field[0], field[2], field[1])
           col.instance_variable_set( :@required, field[4] )
           columns << col
           columns
         end
       end
     end
   end
end