Re: [sqlite] FTS4: Datatypes

[nobre]

Looks promising!
With the me triggers mentioned in
http://www.sqlite.org/src/artifact/fdc666a70d5257a64fee209f97cf89e0e6e32b51
, it can be a really time saver. Its awful having to deal with two tables
that store a single, split up entity, also the inability to create
additional indexes on the text columns (when needed i must make a shadow
copy on the real table only to have it indexed)

Any speed comparisons were made on regular+fts tables joined when searching,
vs fts4 + external content table triggered in the way described above ?


Fabian-40 wrote:
> 
> It seems I finally have some sort of a solution:
> 
> "As of SQLite version 3.7.9, FTS4 supports a new option - "content"
> -designed to extend FTS4 to support the creation of full-text indexes
> where:
> 
> +
> +* The indexed documents are not stored within the SQLite database
> +  at all (a "contentless" FTS4 table), or
> +
> +* The indexed documents are stored in a database table created and
> +  managed by the user (an "external content" FTS4 table).
> 
> 
> Using this new option I can keep all data together in a regular table,
> and use FTS purely as an index.
> 
> I wonder why this new option wasn't mentioned in the changelog of
> 3.7.9, but maybe because it's still experimental?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/FTS4%3A-Datatypes-tp32815033p32818172.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4: Datatypes

[Richard Hipp]

On Wed, Nov 9, 2011 at 8:02 PM, Fabian  wrote:

>
> "As of SQLite version 3.7.9, FTS4 supports a new option - "content"
> -designed to extend FTS4 to support the creation of full-text indexes
> where:
>
> I wonder why this new option wasn't mentioned in the changelog of
> 3.7.9, but maybe because it's still experimental?
>


Because it is experimental.  Also, it comes with no safety-belts:  you can
do a lot of damage by misusing it, and it is quite fussy and easy to
misuse.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4: Datatypes

[Fabian]

It seems I finally have some sort of a solution:

"As of SQLite version 3.7.9, FTS4 supports a new option - "content"
-designed to extend FTS4 to support the creation of full-text indexes
where:

+
+* The indexed documents are not stored within the SQLite database
+  at all (a "contentless" FTS4 table), or
+
+* The indexed documents are stored in a database table created and
+  managed by the user (an "external content" FTS4 table).


Using this new option I can keep all data together in a regular table,
and use FTS purely as an index.

I wonder why this new option wasn't mentioned in the changelog of
3.7.9, but maybe because it's still experimental?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4: Datatypes

[Petite Abeille]

On Nov 9, 2011, at 11:59 PM, Fabian wrote:

> So would it be an idea to have a simple flag (NOINDEX for example) which
> dictates that a certain column shouldn't be indexed by FTS, just stored? It
> may be a lot simpler to implement than actual datatypes, and I could work
> around the other limitations myself.

Yes, it's called a plain, old table (POT).

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS4: Datatypes

[Fabian]

Ever since I started using FTS, I'm always confronted with the problem that
I need two tables: one FTS table with the TEXT columns, and one normal
table with the INTEGER columns for numerical values. This causes all kinds
of problems (keeping the rowid's in sync, complex queries, etc.).

>From a previous post from the author of FTS, I understand that it will be
hard to implement datatypes for the virtual tables that FTS uses, so I
already ruled that out as a solution.

I could just store the numerical values as TEXT in the FTS table, but even
the most simple operation (sorting for example) will be a challenge, since
I have to format the numbers in such a way that they sort right (prepending
zeroes to make them all the same length). But suppose I take all that into
account, there still is another problem: FTS will unnecessary index all
those extra columns.

So would it be an idea to have a simple flag (NOINDEX for example) which
dictates that a certain column shouldn't be indexed by FTS, just stored? It
may be a lot simpler to implement than actual datatypes, and I could work
around the other limitations myself.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users