Re: sqlite error with roundup-demo

The results of the command are inline.  Looks like the sqlite3 in my case
is compiled with FTS3 (not 5) support. Not sure if the roundup installer
could take advantage of FTS3 as well as FTS5?

On Thu, Nov 9, 2023 at 2:26 PM John P. Rouillard <[email protected]> wrote:

> 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
>

I don't get an error, but it looks like the last command didn't produce the
listing of modules.

[tmikk@drc-roundup-prd-web ~]$ sqlite3 file2.sqlite3
SQLite version 3.44.0 2023-11-01 11:23:50
Enter ".help" for usage hints.
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;
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).
>

I wasn't quite sure how to get the (roundup)$ prompt.


>
> 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.
>
> I see ('ENABLE_FTS3',) in the output.

>>> import sqlite3
>>> con = sqlite3.connect(':memory:')
>>> cur = con.cursor()
>>> cur.execute('pragma compile_options;')
<sqlite3.Cursor object at 0x7f02cb415e30>
>>> available_pragmas = cur.fetchall()
>>> con.close()
>>> print(available_pragmas)
[('DISABLE_DIRSYNC',), ('ENABLE_COLUMN_METADATA',), ('ENABLE_FTS3',),
('ENABLE_RTREE',), ('ENABLE_UNLOCK_NOTIFY',), ('SECURE_DELETE',),
('TEMP_STORE=1',), ('THREADSAFE=1',)]
>>>


> (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.
>


-- 
Tonu Mikk
Developer | Disability Resource Center | disability.umn.edu
University of Minnesota | umn.edu
[email protected]
Pronouns: He/Him

_______________________________________________
Roundup-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/roundup-users