Using queries with ORDER BY when testing with the embedded runtime

Question

I am developing my composer application using TDD approach, so it's important that all the code can run in the embedded runtime used in tests.

My stumbling block is that I cannot make queries using ORDER BY clause to work in tests.

This is a snippet of my model:

asset AssetStatement identified by statementRevisionId {
  o String statementRevisionId
  o String statementId
  o DateTime createdAt
  o String effectiveDate regex=/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/
  o Integer revision range=[0,]
  o Boolean actual
  o AssetStatementInfo info
}

And this is the query:

query FindActualAssetStatement {
  description: "Finds actual asset statement with given (non-unique) id"
  statement:
    SELECT com.assetrisk.AssetStatement
    WHERE (actual == true AND statementId == _$id)
    ORDER BY revision DESC
    LIMIT 1
}

If I remove ORDER BY line the query runs, but when it's there I am getting the following exception:

Error: Cannot sort on field(s) "revision" when using the default index
 at validateSort (node_modules/pouchdb-find/lib/index.js:472:13)
 at node_modules/pouchdb-find/lib/index.js:1138:5
 at <anonymous>
 at process._tickCallback (internal/process/next_tick.js:188:7)

The same error happens even if I use asset's unique key field for sorting.

This seems to be a known feature of PouchDB: https://github.com/pouchdb/pouchdb/issues/6399

However, I don't seem to have access to the underlying database object in the embedded composer environment to configure indices for tests to work.

Is there a way this could be made to work?

score 0 · Accepted Answer · answered Jul 19 '18 at 09:28

Currently, we bypass this by modifying the test command to comment out the ORDER BY statements before the tests and uncomment them after:

"test": "sed -i '' -e 's, ORDER BY,// ORDER BY,g' ./queries.qry && mocha -t 0 --recursive && sed -i '' -e 's,// ORDER BY, ORDER BY,g' ./queries.qry"

It is a bit hacky, and does not solve the complex issue of PouchDB not handling indices, but it's probably better than not testing at all.

Using queries with ORDER BY when testing with the embedded runtime

1 Answers1