A page for tracking progress on the SQLiteAdapter per CAY-875..

• Driver used:

http://www.zentus.com/sqlitejdbc/ (version 0.37)

• Quirks and limitations:
  • (minor) too many open connections to the same file exhaust some internal limit, so care should be taken to keep the pool sizes low
  • Had to use Oracle8 strategy for the SQLTemplate, otherwise updating templates would get stuck in an infinite loop (clearly a driver bug)
  • Types not supported: BigDecimal. Cayenne workaround is to read BigDecimals as Strings. Still one problem remains - matching against BigDecimals. Likely a driver problem as command line produces correct results:
    

    SELECT t0.ARTIST_ID, COUNT(t0.PAINTING_ID), SUM(t0.ESTIMATED_PRICE) FROM PAINTING t0 GROUP BY t0.ARTIST_ID HAVING SUM(t0.ESTIMATED_PRICE) = ? [bind: 1000]

    .
  • NPE on NULL float and double types. This is clearly a driver bug, and we had to code a workaround.
  • Date handling is funky. The driver doesn't do the right thing: http://www.zentus.com/sqlitejdbc/usage.html
  • Reverse engineering does not produce the correct column types, as SQLite types system is very loose.
  • "delete from X" produces zero result count, while "delete from X where ...." produces correct count.