More complicated Sequel selections

Question

There is a simple database in Sequel:

DB = Sequel.sqlite

DB.create_table :items do
    primary_key :id
    DateTime :date
    String :name
end

items = DB[:items]
items.insert(:name => 'abc', :date => DateTime.now)
items.insert(:name => 'ghi', :date => DateTime.now)
items.insert(:name => 'def', :date => DateTime.now)

The question: is it a good idea to store 'strange' objects in database, like DateTime?

puts items.first(:name => 'ghi')[:date].year

Outputs '2010' so, well - it works. But still i'm quite curious about it. If it's nothing bad, what about filtering? Things like that:

puts items.first(:date.year => 2010)[:name]

... won't work. Is it possible to do it in other way? How?

Answer 1

I think you're asking 2 different questions here:

1) It's absolutely normal to store rich objects like dates in relational databases (most if not all support dates and the like). Sequel's DateTime attribute type is acknowledging this fact and provides an abstraction over all its supported back-ends.

2) Filtering is less obvious. Different back-ends (read database implementations) will provide very different ways of decomposing and hence selecting parts of these objects. An ORM like Sequel has to draw some line of abstraction which is (hopefully) commonly applicable to all the supported back-ends. In some cases (and DateTime may well be one of them) the more sophisticated filtering will not be available via the ORM's syntactic sugar. You may, in such cases, need to drop down to the per-database SQL facility to achieve what you want, or at the very least, be aware of the underlying semantics when forming your syntactic sugar solution

Answer 2

bjg is correct. For your specific situation, instead of:

puts items.first(:date.year => 2010)[:name]

You could try:

puts items.first(:date.extract(:year) => 2010)[:name]

Which should work if your database supports the SQL standard extract function (not all databases do). If your database does not, you'll have to call your whatever similar function exists for your database.