Re: sqlite error with roundup-demo

Hi Tonu:

Can you replicate this command sequence?

  % sqlite3 file.sqlite3
  sqlite> CREATE VIRTUAL TABLE email USING fts5(sender, title, body);
  sqlite> .schema
  CREATE VIRTUAL TABLE email USING fts5(sender, title, body)
  /* email(sender,title,body) */;
  CREATE TABLE IF NOT EXISTS 'email_data'(id INTEGER PRIMARY KEY, block BLOB);
  CREATE TABLE IF NOT EXISTS 'email_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
  CREATE TABLE IF NOT EXISTS 'email_content'(id INTEGER PRIMARY KEY, c0, c1, c2);
  CREATE TABLE IF NOT EXISTS 'email_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
  CREATE TABLE IF NOT EXISTS 'email_config'(k PRIMARY KEY, v) WITHOUT ROWID;

If you get an error on the first create virtual table, then it looks
like your sqlite3 was built with FTS5 disabled.

Confirm this by running:

  sqlite> pragma compile_options

ENABLE_FTS5 should show up in the list.

If that works, what does:

    (roundup)$ python -c 'import sqlite3; print(sqlite3.version_info, sqlite3.sqlite_version_info)'
    (2, 6, 0) (3, 22, 0)

print? I expect the second tuple to be (3, 44, 0).

Try running:

====
import sqlite3

con = sqlite3.connect(':memory:')
cur = con.cursor()
cur.execute('pragma compile_options;')
available_pragmas = cur.fetchall()
con.close()

print(available_pragmas)

====

('ENABLE_FTS5',) should be in the output.

(from https://stackoverflow.com/questions/60244988/how-can-i-use-the-fts5-extension-with-the-sqlite3-python-module-with-python-3-7)

Your original error makes me think that RedHat built sqlite without the
fts5 module, or the sqlite3 Python library is calling the wrong
library (without fts5 support).

It looks like you have SQLite 3.44.0 at the command line. Which should
have FTS5.

This is an old report that redhat did not enable fts3 support when
building their sqlite libraries. I wonder if that's your case as well.

  https://unix.stackexchange.com/questions/45834/enable-sqlite-fts-enhanced-query-syntax-in-python-in-rhel6?rq=1

Just to chop out the rest of the original email in case fts5 does seem
to be supported, the summary is:

  python 3.6.12

  roundup 2.3.0 installed via pip install roundup in your virtual environment
   located at  /home/tmikk/pydev/py36-venv

>> What is the #! line at the top of roundup-demo?
>
>I am not sure how to know that?

Use 'which roundup-demo' to find the path to the file and cat the
file.  The file is just python code.

>> Is /swadm/roundup/trackers a local or NFS filesystem?
>>
>
>Yes

Use df /swadm/roundup/. and df /home/tmikk to see if it's a local or
network filesystem. (local will shows something like /dev/... network
will show a hostname followed by a :. For example my network home
directory is:

  % df -kh ~/.
  Filesystem                   1K-blocks      Used Available Use% Mounted on
  de14:/vol/user/home/rouilj        590G      180G      381G  33% /home/rouilj

while /tmp is local and looks like:

  % df /tmp
  Filesystem                   1K-blocks      Used Available Use% Mounted on
  /dev/mapper/xubuntu--vg-root 305013784 238600540  50846396  83% /

)

>> This is what I get:
>>
>>    $ roundup-admin -V
>>    Roundup 2.3.0 ready for input.
>>
>> (type exit to exit the admin interface)

I assume you get the same output when running roundup-admin from
within your virtual-env?

If it turns out that fts5 is not supported by python3 or the
underlying sqlite library I am kind of out of ideas. In theory you
could edit the installed back_sqlite.py and comment out the calls to
self._add_fts5_table(). As long as you didn't try to use native-fts as
the indexer config option, I think it would work. But YMMV.

Given that RedHat 7 only has 1 year left in it's main support cycle,
you would probably be better served by upgrading to something newer.

IIRC you already run a Roundup instance right? Which version? I assume
2.1.0 or earlier since 2.2.0 added native-fts for sqlite?

Let me know what you find.

--
				-- rouilj
John Rouillard
===========================================================================
My employers don't acknowledge my existence much less my opinions.