Re: [sqlite] Full text search FTS3 of files

[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

[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

[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

[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

[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

[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

[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

[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

[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