Query for items matching a regular expression?

[robogeek]

For a table defined by sqlite3orm, I have a field renderPath which is a string (TEXT) containing a pathname. I want to perform a SQL query like the following. The regular expression command (regexp) exists in the command-line SQLITE3 on my laptop as shown here.

sqlite> select vpath, renderPath from DOCUMENTS where renderPath regexp '/index.html$';
hier-broke/dir1/dir2/index.html.md|hier-broke/dir1/dir2/index.html
hier/dir1/dir2/index.html.md|hier/dir1/dir2/index.html
hier/dir1/index.html.md|hier/dir1/index.html
hier/imgdir/index.html.md|hier/imgdir/index.html
hier/index.html.md|hier/index.html
subdir/index.html.md|subdir/index.html

The following demonstrates how to install the sqlean package, which includes a regexp function, in the SQLITE3 instance created by sqlite3orm.

The request I have for you is

1. Add a function to SqlDatabase ex[psomg the loadExtension method and/or exposing the underlying Database object
2. Better document how to use SQL strings - I've gone over the tests in the spec directory, and they don't help much in this regard

---

There is an extension package - https://github.com/nalgeon/sqlean - that is installable with an npm package https://www.npmjs.com/package/sqlite3-sqlean - which contains a regular expression extension that functions in the same way.

To install the extension within sqlite3orm, I did the following:

import { Database } from 'sqlite3';
import sqleanLibs from 'sqlite3-sqlean';
// ...

/**
 * Subclass the SqlDatabase so we can expose
 * the underlying SQLITE3 Database object and
 * some useful methods on that class.
 */
export class SqlDatabaseChild extends SqlDatabase {
    _db() { return this.db; }

    loadExtension(filename: string, callback?: (err?: Error | null) => void): Database {
        return this.db.loadExtension(filename, callback);
    }
}
// ...
export const sqdb = new SqlDatabaseChild();
// await sqdb.open(':memory:');

await sqdb.open('test.db');
sqdb.loadExtension(sqleanLibs.reLibPath);

Then in an application I could do this:

const res = await documentsDAO.sqldb.all(`
    SelECT
        vpath, renderPath
    FROM DOCUMENTS
    WHERE renderPath regexp '/index.html$'
`);

console.log(res);

This generates the result I needed - the ability to make a database query using a regular expression.

But, my actual goal is using this as one query possibility in a more complex query that is a Filter or Where thingy for use with the selectAll function.

For example the README for the package led me to believe this would work:

await documentsDAO.selectAll({
   // clauses matching other fields
  renderPath: {
        sql: ` renderPath regexp '${options.renderpathmatch}' `
  }
  // clauses matching other fields
});

So... coding this

const res2 = await documentsDAO.selectAll({
    renderPath: {
        sql: '  renderPath regexp \'/index.html$\'  '
    }
});
console.log(res2);

Results in the following error message:

/home/david/Projects/akasharender/akasharender/node_modules/sqlite3orm/src/lib/query/QueryModel.js:95
            return Promise.reject(new Error(`select '${this.table.name}' failed: ${e.message}`));
                                  ^

Error: select 'DOCUMENTS' failed: unknown comparison operation: 'sql'
    at QueryModel.selectAll (/home/david/Projects/akasharender/akasharender/node_modules/sqlite3orm/src/lib/query/QueryModel.js:95:35)
    at async file:///home/david/Projects/akasharender/akasharender/test/setup-assets.mjs:125:14

But recoding to this:

const res2 = await documentsDAO.selectAll({
        sql: '  renderPath regexp \'/index.html$\'  '
});
console.log(res2);

Results in a successful execution.

FWIW the generated SQL is:

SELECT
  T.`vpath`,
  T.`mime`,
  T.`mounted`,
  T.`mountPoint`,
  T.`pathInMounted`,
  T.`fspath`,
  T.`renderPath`,
  T.`rendersToHTML`,
  T.`dirname`,
  T.`mtimeMs`,
  T.`docMetadata`,
  T.`metadata`,
  T.`info`
FROM "DOCUMENTS" T
  WHERE   renderPath regexp '/index.html$'  

[gms1]

So... coding this

const res2 = await documentsDAO.selectAll({
    renderPath: {
        sql: '  renderPath regexp \'/index.html$\'  '
    }
});
console.log(res2);

Results in the following error message:

actually, this should give you a compile error, if you would use Typescript (with proper settings); something like this:

Object literal may only specify known properties, and 'sql' does not exist in type 'PropertyComparisons<string | undefined> | Promise<string | (string & Date)>'

and this would give you the hint, hat 'sql' is not meant to be used for property comparison. Supporting this would just complicate the typing without much benefit

But recoding to this:

const res2 = await documentsDAO.selectAll({
        sql: '  renderPath regexp \'/index.html$\'  '
});
console.log(res2);

Results in a successful execution.

yes this looks good and you should be able to combine this with other expressions using the logical operators;

so e.g:

myDao.selectAll({
  and: [{
    sql: '  renderPath regexp \'/index.html$\'  '
  },
  {
    vpath: {isLike: '%hello%'}
  }]
});