Register Guidelines E-Books Search Today's Posts Mark Forums Read

Go Back   MobileRead Forums > E-Book Software > Calibre > Development

Notices

Reply
 
Thread Tools Search this Thread
Old 08-19-2014, 09:37 PM   #1
DaltonST
Deviser
DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!
 
DaltonST's Avatar
 
Posts: 1,219
Karma: 100494
Join Date: Aug 2013
Location: Texas
Device: 8" Win10 Tablet w/Calibre64
SQLite FTS3/4 & Calibre 2.0+

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......
DaltonST is offline   Reply With Quote
Old 08-20-2014, 12:07 AM   #2
kovidgoyal
creator of calibre
kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.
 
kovidgoyal's Avatar
 
Posts: 33,395
Karma: 10205094
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
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.
kovidgoyal is online now   Reply With Quote
Advert
Old 08-26-2014, 03:15 PM   #3
DaltonST
Deviser
DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!
 
DaltonST's Avatar
 
Posts: 1,219
Karma: 100494
Join Date: Aug 2013
Location: Texas
Device: 8" Win10 Tablet w/Calibre64
Qt4 Upgraded to Qt5; Can sqlite3.dll be upgraded too?

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
DaltonST is offline   Reply With Quote
Old 08-26-2014, 10:56 PM   #4
kovidgoyal
creator of calibre
kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.
 
kovidgoyal's Avatar
 
Posts: 33,395
Karma: 10205094
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
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 is online now   Reply With Quote
Old 08-28-2014, 04:49 AM   #5
kovidgoyal
creator of calibre
kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.
 
kovidgoyal's Avatar
 
Posts: 33,395
Karma: 10205094
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
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.
kovidgoyal is online now   Reply With Quote
Advert
Old 08-28-2014, 10:43 AM   #6
DaltonST
Deviser
DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!
 
DaltonST's Avatar
 
Posts: 1,219
Karma: 100494
Join Date: Aug 2013
Location: Texas
Device: 8" Win10 Tablet w/Calibre64
Quote:
Originally Posted by kovidgoyal View Post
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
DaltonST is offline   Reply With Quote
Old 08-28-2014, 11:02 AM   #7
kovidgoyal
creator of calibre
kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.
 
kovidgoyal's Avatar
 
Posts: 33,395
Karma: 10205094
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
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.
kovidgoyal is online now   Reply With Quote
Reply

Tags
full text scan, sqlite

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sqlite on Kobo Lucas Malor Kobo Developer's Corner 6 03-26-2014 08:06 AM
SQLite commands via telnet ectoplasm Kobo Developer's Corner 2 10-05-2013 10:01 PM
SQLite Meeky Kindle Developer's Corner 2 01-01-2013 11:02 AM
Kindle SQLite DB akira.spb Kindle Developer's Corner 2 10-18-2010 10:20 PM


All times are GMT -4. The time now is 06:57 AM.


MobileRead.com is a privately owned, operated and funded community.