Skip to content

fix(rql2sql): properly handle date and datetime operations with SQLite

Simon Chabot requested to merge topic/default/rql2sql-date-sqlite into branch/default

Different DB backends are used by CW, mainly postgresql and sqlite. According to RQL's documentation:

Any N WHERE N is Note, N written_on D, D day> (today -10), N written_by P, P name 'jphc' or P name 'ocy'

should return all the notes less than 10 days old written by jphc or ocy.

but, as described in #109 (closed), it only works with postgresql, which is not mentioned is the documentation.

TODAY is working well in sqlite, but not the operation with TODAY. TODAY is translated to 'CURRENT_DATE' in sqlite, and SELECT CURRENT_DATE - 3 return the current year minus three, and not the current date minus 3 days as expected according to the documentation.

A lot of tests in CW are performed with SQLite (because it's fast to set up!), so… let's make it work !

This patch suggests to adapt the 'visit_mathexpression' method, to write the expected SQL when were are dealing with:

  • sqlite
  • a date(time) on the left (left-hand-side, lhs)
  • a integer (treated as an amount of days) on the right (right-hand-side, rhs)

The tests have been updated to check that the conversion rql to sql is the expected one, according to the backend.

close #109 (closed)

Merge request reports