SQLite FTS3/4 & Calibre 2.0+

[DaltonST]

For Full Text Search, FTS3/4 are from hundreds to thousands of times faster than plain vanilla SQLite using "LIKE". According to http://cvs.hwaci.com/fts3.html#section_2 , although FTS3/4 are included with the SQLite core source code, they are not enabled by default.

Question: by any chance will the SQLite in Calibre 2.0 or later ever have FTS3/4 enabled?

A new plugin I am working on scans all of the text fields (author, title, series, comments, sorts, publisher) across all of the books in a library looking for particular substrings to relate the books to each other. I am using LIKE and other approaches, but couldn't help but dream about FTS3/4......

[kovidgoyal]

No, sqlite in calibre is used simply as a replacement for open() not as a db. If you want to do searches, use the calibre search API which is much faster than FTS (since it work on an in-memory copy of the data, and has query caching), not to mention far more capable.

[DaltonST]

Kovid,

A new plugin I am developing uses SQLite extensively because native Calibre does not support cross-library searches, cross-library metadata acquisition, cross-book and/or cross-author searches plus corresponding metadata analysis and update (nor should it).

The new plugin is really as much an add-on to Calibre as it is a plug-in, because its Calibre metadata.db has multiple new custom_columns tables, new views, new custom tables for behind the scenes processing that the user cannot and need not see, plus new indexes.

Its software runs as a Calibre Plugin, but its metadata.db is not meant for a "normal" Calibre library. The plugin menu will do absolutely nothing unless the current library is the special library that is associated with the special metadata.db file described above. It is definitely not a "generic library" plugin. It is targeted to Calibre power-users who have large libraries.

One of my 5 (so far) plugin jobs in Calibre fails because the native SQLite function "instr" does not exist in Calibre 2.0's version of sqlite3.dll. The native function "instr" was added to SQLite almost 2 years ago, on 2012-12-12 (3.7.15) according to http://www.sqlite.org/changes.html, so Calibre 2.0's version must be an older release than 3.7.15. It has blank "properties", so it is hard to say what version it is.

The latest version is 2014-08-15 (3.8.6), but I have been satisfied so far with only 3.7.16 for Windows 64 from http://synopse.info/files/SQLite3-64.7z , which I have been using as a drop-in replacement for the sqlite3.dll distributed with Calibre. The "instr" function works as advertised. Example: CREATE VIEW IF NOT EXISTS _instr_title_author_author_title AS SELECT book, booktitle, authorname FROM _books_work WHERE ( instr(booktitle,authorname) > 0 OR instr(authorname,booktitle) > 0 )

For the Windows 32bit as well as OSX and Linux, http://www.sqlite.org/download.html has the appropriate sqlite3.dll for 2014-08-15 (3.8.6).

The file size of sqlite3.dll that comes with Win 64 Calibre 2.0 is 523kb. The drop-in replacement I am using that is more recent is 547kb; not much difference in size, but a lot of difference is functionality and bug-fixes.

Qt4 was just upgraded to Qt5; could the sqlite3.dll be upgraded too? Please? Pretty Please?

http://www.sqlite.org/download.html

[kovidgoyal]

The sqlite3.dll that ships with calibre is the one that comes with python 2.7.8. I am not going to replace it, because replacing DLLs willy nilly is a recipe for disaster. Nor should you. Especially on windows where C runtime libraries are all mutually incompatible and depend on compiler version.

There's nothing preventing you from using calibre's DB API across multiple libraries, that's how the calibre copy to library function works.

[kovidgoyal]

Oh and in case you were not aware of this, calibre itself uses apsw as the python wrapper for sqlite which uses a sqlite version >= 3.8 IIRC.

[DaltonST]

Originally Posted by kovidgoyal

Oh and in case you were not aware of this, calibre itself uses apsw as the python wrapper for sqlite which uses a sqlite version >= 3.8 IIRC.

Python 2.7.8 ships with SQLite 3.6.21, just like all of the Python 2.7.x series. 3.6.21 was released in the fall of 2008.

The sqlite3.dll in "C:\Program Files\Calibre2\DLLs\sqlite3.dll" is version 3.6.21 released in 2008 because Calibre is on a Python 2.7.x release.

The policy of Python.org is upgrade its sqlite3.dll to the latest version only upon reaching a new major release of Python. So, if there were to be a Python 2.8 (which there will never be because 2.7.x is a dead-ender on life-support in maintenance mode), 2.8 would have shipped with at least SQLite version 3.8.6 which was released on 2014-08-15. But, there will never be a 2.8 (unfortunately).

http://sqlite.org/ says on its homepage in the upper right corner:

• Version 3.8.6 of SQLite is recommended for all new development. Upgrading from all other versions of SQLite is recommended.

So, SQLite.org itself recommends keeping the sqlite3.dll up-to-date. There is nothing willy-nilly about http://sqlite.org/ .

Unfortunately, APSW does not force an APSW user to upgrade their sqlite3.dll to the latest release of SQlite. If it did, "C:\Program Files\Calibre2\DLLs\sqlite3.dll" would be 2014's 3.8.6 instead of 2008's 3.6.21.

The choice to remain on a dead-end standard distribution is not something to be viewed lightly. As Charles Darwin might say, "evolve or become extinct". I want to see Calibre evolve. If I did not, I would not develop plugins to keep it as vibrant as it has been in the past.

http://sqlite.org/download.html

[kovidgoyal]

Le bubbling sigh. Python 2.7 is not a dead end, it is far more vibrant than Python 3.

And apsw includes its own copy of sqlite3, which as I said before is sqlite 3.8. Why dont you try actually listening to what I say for a change.