SQLite + FTS (full text search)

[Mark Summerfield]

Hi,

On my Debian stable 64-bit system, SQLite3 has FTS (full text search) enabled (although at version 3 rather than the recommended version 4):

Python 3.2.3 (default, Feb 20 2013, 14:44:27) [GCC 4.7.2] on linux2
Type "copyright", "credits" or "license()" for more information.print(row)
....
('ENABLE_FTS3',)
....

But on Windows when I use the official Python 3.3 32-bit binary from www.python.org this is not enabled.

My guess is that on Debian, the packagers install a full SQLite 3 and the Python package uses that. But on Windows I think the Python packagers bundle their own SQLite (quite rightly since it might not already be installed).

I'd like the Windows binary to include SQLite 3 with FTS4 support, but I don't know how much work that involves or if it would make the Python .msi file too big?

Anyway, I guess if anyone else is interested in this they could perhaps reply to indicate this?

If you're curious about the feature, it is documented here:
http://www.sqlite.org/fts3.html

 

[Joseph L. Casale]

But on Windows when I use the official Python 3.3 32-bit binary from

www.python.org this is not enabled.

For an unobtrusive way [1] to gain this, see apsw. For what it's worth, I prefer
this package over the built in module.


Python 3.3.3 (v3.3.3:c3896275c0f6, Nov 18 2013, 21:19:30) [MSC v.1600 64 bit (AMD64)]
....
IPython 2.0.0-dev -- An enhanced Interactive Python.
....

In [1]: import apsw

In [2]: c = apsw.Connection(':memory:')

In [3]: for x in c.cursor().execute('pragma compile_options'): print(x)
('ENABLE_FTS3',)
('ENABLE_FTS3_PARENTHESIS',)
('ENABLE_FTS4',)
('ENABLE_RTREE',)
('THREADSAFE=1',)

hth,
jlc

[1] See the sqlite mailing list for a way to replace the dll, while I have done it I
also have tested it thoroughly.

 

[Asaf Las]

Hi,
On my Debian stable 64-bit system, SQLite3 has FTS (full text search)
enabled (although at version 3 rather than the recommended version 4):

Python 3.2.3 (default, Feb 20 2013, 14:44:27) [GCC 4.7.2] on linux2
Type "copyright", "credits" or "license()" for more information. print(row)
...
('ENABLE_FTS3',)
...
But on Windows when I use the official Python 3.3 32-bit binary
from www.python.org this is not enabled.

My guess is that on Debian, the packagers install a full SQLite 3
and the Python package uses that. But on Windows I think the Python
packagers bundle their own SQLite (quite rightly since it might not
already be installed).

I'd like the Windows binary to include SQLite 3 with FTS4 support,
but I don't know how much work that involves or if it would make
the Python .msi file too big?

Anyway, I guess if anyone else is interested in this they
could perhaps reply to indicate this?
If you're curious about the feature, it is documented here:

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

It is compile time option.
http://www.sqlite.org/compile.html#enable_fts3
you have to build it with this option enabled.

 

[Mark Lawrence]

Hi,
On my Debian stable 64-bit system, SQLite3 has FTS (full text search)
enabled (although at version 3 rather than the recommended version 4):

Python 3.2.3 (default, Feb 20 2013, 14:44:27) [GCC 4.7.2] on linux2
Type "copyright", "credits" or "license()" for more information.

import sqlite3
con = sqlite3.connect(":memory:")
cur = con.execute("pragma compile_options")
for row in cur:

print(row)
...
('ENABLE_FTS3',)
...
But on Windows when I use the official Python 3.3 32-bit binary
from www.python.org this is not enabled.

My guess is that on Debian, the packagers install a full SQLite 3
and the Python package uses that. But on Windows I think the Python
packagers bundle their own SQLite (quite rightly since it might not
already be installed).

I'd like the Windows binary to include SQLite 3 with FTS4 support,
but I don't know how much work that involves or if it would make
the Python .msi file too big?

Anyway, I guess if anyone else is interested in this they
could perhaps reply to indicate this?
If you're curious about the feature, it is documented here:

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

It is compile time option.
http://www.sqlite.org/compile.html#enable_fts3
you have to build it with this option enabled.

As an option can be represented in a single bit then presumably the
Windows msi file only needs an extra bit to allow for this, or have I
missed something? While I'm at it what is this "compile time" thingy,
being on Windows I'm not used to seeing such terminology?

 

[Asaf Las]

On 23/01/2014 13:24, Asaf Las wrote:
As an option can be represented in a single bit then presumably the
Windows msi file only needs an extra bit to allow for this, or have I
missed something? While I'm at it what is this "compile time" thingy,
being on Windows I'm not used to seeing such terminology?
Mark Lawrence

Oh i am poor in terminology
from what i have seen in sqlite page i guess this option should
included into list of defines for win ide or into compiler command
line option or can be put directly onto header file as
#define SQLITE_ENABLE_FTS3
to enable FTS

am i wrong?

 

[Antoine Pitrou]

Hi,

My guess is that on Debian, the packagers install a full SQLite 3 and the

Python package uses that. But on

Windows I think the Python packagers bundle their own SQLite (quite

rightly since it might not already be installed).

I'd like the Windows binary to include SQLite 3 with FTS4 support, but I don't know how much work that
involves or if it would make the Python .msi file too big?

You can create a feature request on http://bugs.python.org

Regards

Antoine.

 

[Mark Summerfield]

Hi,






Python package uses that. But on


rightly since it might not already be installed).



don't know how much work that


You can create a feature request on http://bugs.python.org

Regards

Antoine.

Good point. I've now done that:
http://bugs.python.org/issue20366

 

[Rustom Mody]

As an option can be represented in a single bit then presumably the
Windows msi file only needs an extra bit to allow for this, or have I
missed something? While I'm at it what is this "compile time" thingy,
being on Windows I'm not used to seeing such terminology?

On intel processors, in the cr0 register there is a bit called the PE
(protection enable) bit.
Turn it off and you have 1M memory
Turn it on and you will have GBs and more.

A wee little bit more than a single bit dont you think?

 

[Terry Reedy]

Hi,
On my Debian stable 64-bit system, SQLite3 has FTS (full text search)
enabled (although at version 3 rather than the recommended version 4):

Python 3.2.3 (default, Feb 20 2013, 14:44:27) [GCC 4.7.2] on linux2
Type "copyright", "credits" or "license()" for more information.

import sqlite3
con = sqlite3.connect(":memory:")
cur = con.execute("pragma compile_options")
for row in cur:

print(row)
...
('ENABLE_FTS3',)
...
But on Windows when I use the official Python 3.3 32-bit binary
from www.python.org this is not enabled.

On 64 bit 3.4.0b2, the output is ('THREADSAFE=1',)

It is compile time option.
http://www.sqlite.org/compile.html#enable_fts3
you have to build it with this option enabled.

If one clones the hg.python.org/cpython repository and runs
Tools/buildbots/external.bat with minimal svn installed, it copies
sqlite3 source into sqlite-3.8.1 with files
shell.c
sqlite3.c # 5 mb
sqlite3.h
sqlite3ext.h

If that is everything needed for FTS and if pcbuild/ _sqlite3 and
sqlite3 project files were altered to change the compile option and
anything else needed ... then you would have FTS on Windows.