Re: [sqlite] search

[Alek Paunov]

BTW, in my eyes, sqlite has the full potential to become most used DB 
engine for hierarchical data too (besides relational), once someone 
clever hacker manage to port something like Pathfinder [1,2,3] (which 
is, let say, optimizing XQuery/SQL compiler) to generate VDBE bytecode 
(like the build-in frontend for SQL)

[1] http://dev.monetdb.org/hg/MonetDB/file/f7d6c302cc9c/pathfinder
[2] http://hackage.haskell.org/package/Pathfinder
[3] 
http://www.lug-erding.de/vortrag/Purely%20Relational%20XQuery%20LUG%20Erding.pdf

On 30.12.2011 16:35, Aris Setyawan wrote:
Hi Durga,

Another alternative, you can use an xml database. It will fix your
problem easily using xquery, like this:
     doc('region')//country/title/text() ->  it will show all region you have
     doc('region')//village/title/text() ->  it will show all village you have

You also can use selection too (where condition).
http://en.wikibooks.org/wiki/XQuery/XPath_examples
http://sedna.org

SQLite with fts is my favorite, but for tree like data structure I
will use xml database.

-aris

On 12/28/11, Durga D<durga.d...@gmail.com>  wrote:
Dear Michael.Black.

         It's correct.

        I need to design database to store file paths and their info like
size. I have an idea
           item(file or folder), level0(imm. parent), level1(grand parent)
to level160(ancestor), type(file type or folder type).

      primary key: (item, level0 to level160)

      Is it correct approach? This is from server side. Need to store
millions of records.

     Need optimum relationship between folders and files uniquely.

     for ex: c:/mydocs/home/a.doc
                 c:/mydocs/office/agreement.doc

   insertion of filepaths,deltion of file paths are enough. should be able
to search by folder wise also.

    any ideas?

Thanks in advance.

On Tue, Dec 27, 2011 at 7:54 PM, Black, Michael (IS)<michael.bla...@ngc.com
wrote:

I don't know if FTS or a normal table will matter here but just normalize
the whole thing.



CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);

Your level can be CO, ST, CI, VI.  Or 1,2,3,4.  1,2,3,4 would be a touch
faster.



INSERT INTO virfts4 VALUES(1,'CO','country1');

INSERT INTO virfts4 VALUES(1,'ST','state1');

INSERT INTO virfts4 VALUES(1,'CI','city1');

INSERT INTO virfts4 VALUES(1,'VI','village1');

SELECT DISTINCT value FROM virfts4 WHERE level MATCH 'CO';



You can store as many levels as you want.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems

________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of Durga D [durga.d...@gmail.com]
Sent: Tuesday, December 27, 2011 4:27 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] search

Thank you. I agree. It's correct.

I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?

if yes, how?

in case, if I have 250 levels like this ex: file system. how to do this.
any idea?

Thanks in advance.

On Tue, Dec 27, 2011 at 3:38 PM, Kit<kit.sa...@gmail.com>  wrote:

2011/12/27 Durga D<durga.d...@gmail.com>:
select * from virfts4 where residence match '/*'; -- dint work
how to get counties names from this db by using query?

Normalize database to 1NF, e.g.
CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
arrivtime, duration, imagelocation);
INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
0730, 1500,'C');

then use select:
SELECT DISTINCT country FROM virfts4;
--
Kit
_______________________________________________
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
_______________________________________________
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

_______________________________________________
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