Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #64598 > unrolled thread

SQLite + FTS (full text search)

Back to article view | Back to comp.lang.python

Contents

  SQLite + FTS (full text search) Mark Summerfield <list@qtrac.plus.com> - 2014-01-23 04:20 -0800
    Re: SQLite + FTS (full text search) Asaf Las <roegltd@gmail.com> - 2014-01-23 05:24 -0800
      Re: SQLite + FTS (full text search) Mark Lawrence <breamoreboy@yahoo.co.uk> - 2014-01-23 13:39 +0000
        Re: SQLite + FTS (full text search) Asaf Las <roegltd@gmail.com> - 2014-01-23 05:49 -0800
        Re: SQLite + FTS (full text search) Rustom Mody <rustompmody@gmail.com> - 2014-01-23 07:23 -0800
      Re: SQLite + FTS (full text search) Chris Angelico <rosuav@gmail.com> - 2014-01-24 01:13 +1100
      Re: SQLite + FTS (full text search) Terry Reedy <tjreedy@udel.edu> - 2014-01-23 19:59 -0500
    RE: SQLite + FTS (full text search) "Joseph L. Casale" <jcasale@activenetwerx.com> - 2014-01-23 13:23 +0000
    Re: SQLite + FTS (full text search) Antoine Pitrou <solipsis@pitrou.net> - 2014-01-23 14:09 +0000
      Re: SQLite + FTS (full text search) Mark Summerfield <list@qtrac.plus.com> - 2014-01-23 06:39 -0800

#64598 — SQLite + FTS (full text search)

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

[toc] | [next] | [standalone]

#64603

On Thursday, January 23, 2014 2:20:31 PM UTC+2, Mark Summerfield wrote:
> 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.

[toc] | [prev] | [next] | [standalone]

#64606

On 23/01/2014 13:24, Asaf Las wrote:
> On Thursday, January 23, 2014 2:20:31 PM UTC+2, Mark Summerfield wrote:
>> 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?

-- 
My fellow Pythonistas, ask not what our language can do for you, ask 
what you can do for our language.

Mark Lawrence

[toc] | [prev] | [next] | [standalone]

#64607

On Thursday, January 23, 2014 3:39:08 PM UTC+2, Mark Lawrence wrote:
> 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? 

[toc] | [prev] | [next] | [standalone]

#64616

On Thursday, January 23, 2014 7:09:08 PM UTC+5:30, Mark Lawrence wrote:
> On 23/01/2014 13:24, Asaf Las wrote:
> > 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?

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? :D

[toc] | [prev] | [next] | [standalone]

#64609

On Fri, Jan 24, 2014 at 12:39 AM, Mark Lawrence <breamoreboy@yahoo.co.uk> wrote:
>>
>> 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?

The implication of a compile-time switch is that it completely changes
the code that gets compiled. For instance, Python 3.4 can be compiled
with/out debugging support (which will slow down execution but enable
certain features), with/out IPv6, and so on. Python 3.2 could also be
compiled "narrow" or "wide" with regard to Unicode handling (internal
representation UTF-16 or UTF-32). Each such change could make a huge
difference to the size of the .msi file, but more importantly, a huge
change to functionality. In the case of something like this, I'd guess
that the compile-time switch would control the presence or absence of
the code; at an absolute minimum, that would mean that disabling it
makes for a smaller binary, but since it's an option I'd guess that
there's more to it (maybe the presence of the feature has a
performance penalty even if you don't use it).

On Windows, where you're accustomed to downloading a ready-made
binary, all compile-time choices were made for you at the time the
binary was built. That's what compile time means - when Python (or
SQLite) was turned from C source code into i386 or amd64 opcodes and
packaged up into an exe and an msi. (In fact, one of the compile-time
choices is the architecture - whether you build a 32-bit or 64-bit
binary, whether you aim it at an Intel Itanium chip, etc, etc, etc.
Generally, code runs only if the architecture is correct, though there
are a few compatibility cases (i386 running on amd64, for instance).)
The only real difference between Windows and Linux here is that it's
customary for Linux systems to have C compilers readily available,
where Windows generally forces you to think more about getting one. I
can simply type "sudo apt-get build-dep python" on my Debian system
and it'll go fetch a C compiler and all the necessary bits and bobs
for building Python from source. (In fact, I did exactly that
recently, as part of setting up a buildbot.) On Windows you would have
to download the right compiler (probably some version of MS Visual
Studio Express, if you want to replicate the python.org binaries),
manually fetch any libraries you need, etc. It's more of a social
difference than a technical one, but it does mean that your average
Linux user is more likely to understand "Type ./configure
--with-some-cool-feature to enable SomeCoolFeature" than your average
Windows user is.

ChrisA

[toc] | [prev] | [next] | [standalone]

#64646

On 1/23/2014 8:24 AM, Asaf Las wrote:
> On Thursday, January 23, 2014 2:20:31 PM UTC+2, Mark Summerfield wrote:
>> 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',)

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

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.
-- 
Terry Jan Reedy

[toc] | [prev] | [next] | [standalone]

#64604

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

[toc] | [prev] | [next] | [standalone]

#64608

Hi,

Mark Summerfield <list <at> qtrac.plus.com> writes:
> 
> 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.

[toc] | [prev] | [next] | [standalone]

#64612

On Thursday, 23 January 2014 14:09:19 UTC, Antoine Pitrou  wrote:
> Hi,
> 
> 
> 
> Mark Summerfield <list <at> qtrac.plus.com> writes:
> 
> > 
> 
> > 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.

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web