Skip to content
  • Simon Chabot's avatar
    fix(rql2sql): properly handle date and datetime operations with SQLite · be2138f52fb6
    Simon Chabot authored
    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, it only works with postgresql, which is *not*
    mentioned in the documentation.
    
    TODAY is working well in sqlite, but not the operations 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
    be2138f52fb6