Re: [sqlite] Full text search FTS3 of files

2010-10-17 Thread Dami Laurent (PJ)
>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

2010-10-17 Thread Max Vlasov
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

2010-10-17 Thread marbex


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

2010-10-17 Thread P Kishor
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

2010-10-17 Thread pipilu
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

2010-10-17 Thread Black, Michael (IS)
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

2010-10-17 Thread Simon Slavin

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

2010-10-17 Thread Germán Herrera
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

2010-10-17 Thread Dickie.wild

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

2010-10-17 Thread Jos Groot Lipman
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