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
