Re: [sqlite] Full text search FTS3 of files
>Is it possible to use FTS3 for search without storing the actual file >contents/search terms/keywords in a row. In other words, create a FTS3 >tables with rows that only contains an ID and populate the B-Tree with >keywords for search. > Each FTS3 table t is stored internally within three regular tables : t_content, t_segments and t_segdir. The last two tables contain the fulltext index. The first table t_content stores the complete documents being indexed, and is only used when you call the offsets() or snippets() functions. So if you don't need those functions, you can cheat : a) call FTS3 to index your document as usual; b) do an update on the t_content table to remove the document text. I did play with that scenario, and gained quite a lot of disk space; however it's really a hack and maybe wouldn't work in future versions of SQLite. More on http://search.cpan.org/dist/DBD-SQLite/lib/DBD/SQLite/Cookbook.pod#Spari ng_database_disk_space ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full text search FTS3 of files
On Sun, Oct 17, 2010 at 11:54 PM, pipilu wrote: > > My question is: > Is it possible to use FTS3 for search without storing the actual file > contents/search terms/keywords in a row. In other words, create a FTS3 > tables with rows that only contains an ID and populate the B-Tree with > keywords for search. > > John, technically if you ask " without storing", the answer is no. But the way of how you could implement this depends on what you want from your search. If only keyword search (without phrases or complex queries), then it's a simple task: create two tables (keywords and index) and develop a simple parser (you don't really want the power of fts3 here) But if you want phrases, you have to provide ordering information about your words. In this case you can use fts3 for the search and the only drawback is that fts will keep the copy of your texts. But my experience showed that fts3 index was implemented very effectively. I have my own implementation of full-text search made with general sqlite tables and I compared a real data for both and even if the texts are excluded from fts3, the index will take twice as lower space for the same pool of articles. So there's a real chance that even if you implement something that doesn't store the texts, you will end up with a bigger index Max Vlasov maxerist.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL: SQLite query help pls
Black, Michael (IS) wrote:
>
> Since there aren't a whole lot of string manipulaion functions (like
> indexof or such) try this:
>
> sqlite> create table t(s varchar);
> sqlite> create table t2(s varchar);
> sqlite> insert into t values('C:\richEminem\file.txt');
> sqlite> select rtrim(s,'._
> abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')||'folder.jpg' from
> t;
> C:\richEminem\folder.jpg
> sqlite> insert into t2(s) select rtrim(s,'._
> abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')||'folder.jpg' from
> t;
> sqlite> select * from t2;
> C:\richEminem\folder.jpg
>
> You do, of course, need to put all allowable characters in the rtrim
> character set (except the backslash or forward slash).
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
I came up with this SQL some time ago when I needed to get the folder from a
full path, the beauty with it is that it always works regardless of which
characters you have in the filename. I though I'd share it.
select RTRIM(path,REPLACE(path,'\','')) from
(select 'C:\richEminem\file.txt' path)
--
View this message in context:
http://old.nabble.com/SQLite-query-help-pls-tp29983175p29985874.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] Full text search FTS3 of files
On Sun, Oct 17, 2010 at 2:54 PM, pipilu wrote: > Hi: > I am trying to build a sqlite3 database to index files. What I want to do is > to keep the files in the file system on the disk (not in the database) and > index the files with keywords such that when a search is performed, the > right file names are returned. > > My question is: > Is it possible to use FTS3 for search without storing the actual file > contents/search terms/keywords in a row. In other words, create a FTS3 > tables with rows that only contains an ID and populate the B-Tree with > keywords for search. No. Use something like e-Swish, or htdig > > Thanks a lot > John > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Full text search FTS3 of files
Hi: I am trying to build a sqlite3 database to index files. What I want to do is to keep the files in the file system on the disk (not in the database) and index the files with keywords such that when a search is performed, the right file names are returned. My question is: Is it possible to use FTS3 for search without storing the actual file contents/search terms/keywords in a row. In other words, create a FTS3 tables with rows that only contains an ID and populate the B-Tree with keywords for search. Thanks a lot John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL: SQLite query help pls
Since there aren't a whole lot of string manipulaion functions (like indexof or
such) try this:
sqlite> create table t(s varchar);
sqlite> create table t2(s varchar);
sqlite> insert into t values('C:\richEminem\file.txt');
sqlite> select rtrim(s,'._
abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')||'folder.jpg' from t;
C:\richEminem\folder.jpg
sqlite> insert into t2(s) select rtrim(s,'._
abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')||'folder.jpg' from t;
sqlite> select * from t2;
C:\richEminem\folder.jpg
You do, of course, need to put all allowable characters in the rtrim character
set (except the backslash or forward slash).
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org on behalf of Dickie.wild
Sent: Sun 10/17/2010 7:26 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] SQLite query help pls
Hi All,
I was hoping for a little help, well little would be an understatement, I
currently have a file location in a field and i would like to take all of it
up untill the last / (folder) and copy it in to another column and then
attach folder.jpg on the end. So the end result ends up being something like
c:\rich\Eminem\folder.jpg
I have never used SQLite before and help would be great
R
--
View this message in context:
http://old.nabble.com/SQLite-query-help-pls-tp29983175p29983175.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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite query help pls
On 17 Oct 2010, at 1:26pm, Dickie.wild wrote: > I was hoping for a little help, well little would be an understatement, I > currently have a file location in a field and i would like to take all of it > up untill the last / (folder) and copy it in to another column and then > attach folder.jpg on the end. So the end result ends up being something like > c:\rich\Eminem\folder.jpg SQLite has a whacky 'rtrim()' function which can trim things other than whitespace. So work out which characters you want to strip from after the last '/', presumably something like 'abcd... ABCD... 123... ._' and do something like UPDATE locations SET jpegPath TO (rtrim(filePath, 'abcd... ABCD... 123... ._') || 'folder.jpg') I haven't tried it but it might work. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite query help pls
You want to strip a complete path + name and save them as separate fields, or you already have it splitted and want to join them together ? On 10/17/2010 09:26 AM, Dickie.wild wrote: > > Hi All, > > I was hoping for a little help, well little would be an understatement, I > currently have a file location in a field and i would like to take all of it > up untill the last / (folder) and copy it in to another column and then > attach folder.jpg on the end. So the end result ends up being something like > c:\rich\Eminem\folder.jpg > > I have never used SQLite before and help would be great > > R > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite query help pls
Hi All, I was hoping for a little help, well little would be an understatement, I currently have a file location in a field and i would like to take all of it up untill the last / (folder) and copy it in to another column and then attach folder.jpg on the end. So the end result ends up being something like c:\rich\Eminem\folder.jpg I have never used SQLite before and help would be great R -- View this message in context: http://old.nabble.com/SQLite-query-help-pls-tp29983175p29983175.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
[sqlite] FTS3 simple tokenizer splits at underscore
The documentation at http://www.sqlite.org/fts3.html#tokenizer states about
the tokenizer
eligible characters are all alphanumeric characters, the "_"
character,
and all characters with UTF codepoints greater than or equal to 128
This suggests to me that an underscore is part of words like 'normal'
characters.
However, it seems words are still split at the underscore charachter:
CREATE VIRTUAL TABLE simple USING fts3(tokenize=simple);
INSERT INTO simple VALUES('This is a word_with_underscores');
INSERT INTO simple VALUES('This is a wordwithoutunderscores');
-- If the underscore is not a token separator this should yield no records.
SELECT * FROM simple WHERE simple MATCH 'with';
-- The first record is returned though
Tested in version 3.7.2
BTW: a documentation error on http://www.sqlite.org/fts3.html#tokenizer
SELECT * FROM simple WHERE simple MATCH 'Frustrated');
Several of these lines should drop the last parenthesis.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
