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
