ActiveRecord, deliberately truncate string to db column width
Rails 4 has ActiveRecord and its MySQL adapter configured, so if you try to store an attribute in an AR model in a MySQL db where the attribute string length is too long for the MySQL column constraints, you will get an exception.
Fine! This is much better than Rails3, where it silently truncated the line.
However, sometimes I have an attribute that I clearly want to just truncate to the maximum size allowed by the db, no exception. I'm having trouble finding the best / supported way to do this with AR.
-
Ideally this should happen as soon as the attribute is set, but I would do it on save. (This is not really a "checkout" since I never want to raise, just cropping, but maybe a checkout system is the best way to do this?)
-
Ideally, it automatically calculates the db column width through AR db introspection, so if the db column width changed (on subsequent migration) the truncation limit would change accordingly. But if this is not possible, I will take a strictly limited reduction limit.
-
Ideally this would be a generic AR code that would work with any db, but if there is no good way to do it, I would take the code that only worked for MySQL
source to share
You can trim your data before inserting into db with before_save
or before_validation
See Active Callbacks - Ruby on Rails Guides and ActiveRecord :: Callbacks
You can get the information in the table with MODEL.columns
and MODEL.columns_hash
. See ActiveRecord :: ModelSchema :: ClassMethods
For example (not tested):
class User < ActiveRecord::Base
before_save :truncate_col
......
def truncate_col
col_size = User.columns_hash['your_column'].limit
self.your_column = self.your_column.truncate(col_size)
end
end
source to share
I'm sure you can accomplish this with a combination of ActiveRecord and ConnectionsAdapters callbacks. ActiveRecord contains several callbacks that you can override to execute specific logic at different points during the save stream. Since an exception is thrown on save, I would recommend adding your logic to the method before_save
. Using the column ConnectionAdapter, you should be able to define the limit of the column you want to insert, although the logic will most likely be different for row versus ints, etc. At the top of your head, you probably want to implement something like:
class User < ActiveRecord::Base
def before_save
limit = User.columns_hash['attribute'].limit
self.attribute = self.attribute[0..limit-1] if self.attribute.length > limit
end
end
The example above is for string, but this solution should work for all connection adapters, assuming they support the limit attribute. Hope this helps.
source to share
Here's my own auto-reply that truncates on a set of attributes (path to save). Curious if anyone has any feedback. It seems to work!
# An ActiveRecord extension that will let you automatically truncate
# certain attributes to the maximum length allowed by the DB.
#
# require 'truncate_to_db_limit'
# class Something < ActiveRecord::Base
# extend TruncateToDbLimit
# truncate_to_db_limit :short_attr, :short_attr2
# #...
#
# Truncation is done whenever the attribute is set, NOT waiting
# until db save.
#
# For a varchar(4), if you do:
# model.short_attr = "123456789"
# model.short_attr # => '1234'
#
#
# We define an override to the `attribute_name=` method, which ActiveRecord, I think,
# promises to call just about all the time when setting the attribute. We call super
# after truncating the value.
module TruncateToDbLimit
def truncate_to_db_limit(*attribute_names)
attribute_names.each do |attribute_name|
ar_attr = columns_hash[attribute_name.to_s]
unless ar_attr
raise ArgumentError.new("truncate_to_db_limit #{attribute_name}: No such attribute")
end
limit = ar_attr.limit
unless limit && limit.to_i != 0
raise ArgumentError.new("truncate_to_db_limit #{attribute_name}: Limit not known")
end
define_method "#{attribute_name}=" do |val|
normalized = val.slice(0, limit)
super(normalized)
end
end
end
end
source to share
I would like to highlight a few points:
If the datatype your_column
is text
, in Rails 4 User.columns_hash['your_column'].limit
will return nil
. Returns a number in case of int
or varchar
.
The text data type in MySQL has a storage limit of 64KB. The truncation value is not sufficient if the content has non-ascii characters, such as ç
which require more than 1 byte to store.
I ran into this problem quite recently, here is the fix:
before_save :truncate_your_column_to_fit_into_max_storage_size
def truncate_your_column_to_fit_into_max_storage_size
return if your_column.blank?
max_field_size_in_bytes = 65_535
self.your_column = your_column[0, max_field_size_in_bytes]
while your_column.bytesize > max_field_size_in_bytes
self.your_column = your_column[0..-2]
end
end
source to share