Is there a way to override the normal column type handling in active record so that a native database function string could be passed? For example post.date = ''CURDATE()'' post.save where CURDATE() is a native Mysql function? I''m actually not doing date handling, but trying to use the spatial extentions which rely heavily on native functions. I''d like to avoid coding to raw sql (eg connection.insert, etc..). Any ideas? -Tim
i know it doesn''t directly answer your question, but... why not just use the ''magic'' table field names created_on and updated_on? http://api.rubyonrails.com/classes/ActiveRecord/Timestamp.html On 10/5/05, Tim <tim234-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Is there a way to override the normal column type handling in active record so > that a native database function string could be passed? For example > > post.date = ''CURDATE()'' > post.save > > where CURDATE() is a native Mysql function? I''m actually not doing date > handling, but trying to use the spatial extentions which rely heavily on native > functions. > > I''d like to avoid coding to raw sql (eg connection.insert, etc..). > > Any ideas? > > -Tim > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On Wednesday 05 October 2005 09:45, Tim wrote:> Is there a way to override the normal column type handling in active > record so that a native database function string could be passed? > For example > > post.date = ''CURDATE()'' > post.save > > where CURDATE() is a native Mysql function? I''m actually not doing > date handling, but trying to use the spatial extentions which rely > heavily on native functions.If I understand this correctly, what you need is a way to tell ActiveRecord to *not* quote a column value when constructing the SQL for a statement. You can''t do this currently, but I think it wouldn''t be overly complicated to add this. I''d do it like this: For an attribute named ''date'', AR already provides an accessor date_before_typecasting. Similarly, you could make it generate or simulated (method_missing) a method date_without_quoting. Methods of the latter variety would note in a map that the specific attributes should not be quotes in SQL. Then, in the code generating the SQL, the map has to be consulted to decide whether to quote or not to quote an attribute. Michael -- Michael Schuerig They tell you that the darkness mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org Is a blessing in disguise http://www.schuerig.de/michael/ --Janis Ian, From Me To You
Thanks Mike and Chris for your suggestions! Michael Schuerig <michael@...> writes:> > If I understand this correctly, what you need is a way to tell > ActiveRecord to *not* quote a column value when constructing the SQL > for a statement. >Exactly. Obviously, my date example is silly. But there are times when you _have_ to interact with the database using native db functions. For example, the only way to insert a geometric point using the spatial extentions is with the POINT() function. Here''s my solution: The relevant code for this is in active_record/connection_adapters/abstract/quoting.rb def quote(value, column = nil) case value when String if column && column.type == :binary "''#{quote_string(column.string_to_binary(value))}''" elsif column && [:integer, :float].include?(column.type) value.to_s else "''#{quote_string(value)}''" # '' (for ruby-mode) end when NilClass then "NULL" when TrueClass then (column && column.type == :boolean ? quoted_true : "1") when FalseClass then (column && column.type = :boolean ? quoted_false : "0") when Float, Fixnum, Bignum then value.to_s when Date then "''#{value.to_s}''" when Time, DateTime then "''#{value.strftime("%Y-%m-%d %H:%M:%S")}''" else "''#{quote_string(value.to_yaml)}''" end end The problem is that when you do something like post.date = ''CURDATE()'', it sees a string, and quotes it. The hack that I came up with is to wrap the string in a new class, and then modify the ''quote'' function to look for this class and output the string unquoted. Put this code in lib: class DbFunction < String end module ActiveRecord module ConnectionAdapters # :nodoc: module Quoting # Quotes the column value to help prevent # {SQL injection attacks}[http://en.wikipedia.org/wiki/SQL_injection]. def quote(value, column = nil) case value when DbFunction then value when String if column && column.type == :binary "''#{quote_string(column.string_to_binary(value))}''" elsif column && [:integer, :float].include?(column.type) value.to_s else "''#{quote_string(value)}''" # '' (for ruby-mode) end when NilClass then "NULL" when TrueClass then (column && column.type = :boolean ? quoted_true : "1") when FalseClass then (column && column.type == :boolean ? quoted_false : "0") when Float, Fixnum, Bignum then value.to_s when Date then "''#{value.to_s}''" when Time, DateTime then "''#{value.strftime("%Y-%m-%d %H:%M:%S")}''" else "''#{quote_string(value.to_yaml)}''" end end end end end To use it: post.date = DbFunction.new "CURDATE()" Now the string passes through untouched. It is ugly, but it works.