处理 Ruby ActiveRecord 中的列转换
在处理遗留数据库时，我遇到了 SQL Server 数据库中的一列，其中日期存储为小数。例如，2011-04-23 存储为 20110423.0。
是否有通用的 ActiveRecord 机制来处理“奇怪的”列存储约定？类似枚举的列实际上存储为整数是另一种情况，也可能使用相同的机制，但我无法完全找到我正在寻找的内容。
连载似乎让我部分实现了这一目标：
class Thing < ActiveRecord::Base
class DecimalDate
def load(date)
if date.is_a? Numeric
y,m,d = /^(\d\d\d\d)(\d\d)(\d\d)/.match(date.to_s)[1..3].map(&:to_i)
Date.civil(y,m,d)
end
end
def dump(date)
date ? date.strftime('%Y%m%d').to_i : 0
end
end
serialize :weird_date, DecimalDate
end
rails c
> Thing.first.weird_date
=> Sun, 02 Jan 2011
但是，幻觉很薄弱。该列不“知道”它是存储为小数的日期。例如比较失败：
rails c
> Thing.where('weird_date > ?', 1.week.ago)
...
ActiveRecord::StatementInvalid: ... Error converting data type varchar to numeric.:
Dealing with a legacy database, I've come across a column in a SQL Server database where the date is stored as a decimal. E.g. 2011-04-23 is stored as 20110423.0.
Is there a general ActiveRecord mechanism for dealing with "weird" column storage conventions? Enum-like columns where they're actually stored as integers is another case that might also make use of the same mechanism, but I can't quite find what I'm looking for.
It seems like serialization gets me partly there:
class Thing < ActiveRecord::Base
class DecimalDate
def load(date)
if date.is_a? Numeric
y,m,d = /^(\d\d\d\d)(\d\d)(\d\d)/.match(date.to_s)[1..3].map(&:to_i)
Date.civil(y,m,d)
end
end
def dump(date)
date ? date.strftime('%Y%m%d').to_i : 0
end
end
serialize :weird_date, DecimalDate
end
rails c
> Thing.first.weird_date
=> Sun, 02 Jan 2011
But, the illusion is thin. The column doesn't "know" that it's a date stored as a decimal. E.g. comparisons fail:
rails c
> Thing.where('weird_date > ?', 1.week.ago)
...
ActiveRecord::StatementInvalid: ... Error converting data type varchar to numeric.:
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您被迫处理遗留数据，我认为有两种管理它的可能性。
1 来自数据库
您可以通过创建表视图来“转换”数据，该视图会动态转换（日期）字段。然后，您在此视图上创建一个触发器（在插入/更新之前），将数据转换回旧格式。最后，您告诉 ActiveRecord 使用您的视图而不是表。
2 从您的应用程序 (Rails)
找到一种方法来告诉 ActiveRecord 执行相同的工作。您是否已经尝试过通过 AR 回调使用 after_initialize 和 before_save 来管理它？更多信息此处
If your are forced to deal with legacy data I see two possibilities to manage it.
1 From your database
You can "convert" your data by making a view of your table which convert your (date) fields on the fly. Then you make a trigger (before insert/update) on this view which convert your data back to your old format. Finally you tell ActiveRecord to use your view instead of your table.
2 From your application (Rails)
Find a way to tell ActiveRecord to do the same job. Have you already tried to manage it with AR callbacks with after_initialize and before_save? More informations here