Re: [sqlite] search

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,

Re: [sqlite] search

Mohit, sqlite> .separator / sqlite> create virtual table virfts4 using fts4 (residence); sqlite> insert into virfts4 select * from source sqlite> select count(*) from virfts4 where residence match '/*'; -- result is 0. Please correct it. I think, i am doing

[sqlite] (no subject)

Thanks, Pratibha Kulkarni ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hi All, How to use System.data.sqlite on both 32-bit and 64-bit system.

Or is there some way to use both 32-bit and 64-bit dll in one project? I did some test, but they use same namespaces, I can't use them at same time. e.g. some code like this? if (is_64_bit){ db = new System.Data.Sqlite_64; }else{ db = new System.Data.Sqlite_32; } 2011/12/28 Joe

Re: [sqlite] 3.7.9 amalgamation file in VS2005

I duplicated your problem on C++ 2005 Express and C++ 2008 Express. C++ 2010 Express does the syntax highlighting correctly (or at least a lot better). Can you upgrade? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop

Re: [sqlite] EXT : Optimizing a query with range comparison.

Does this type of insert need a begin/commit around it? And would WAL mode make any difference? And try increasing your cache_size to at least the size of your database file. cache_size is in pages so the default of 2000 is 2MB. #pragma cache_size=20 That'll make it 200MB which is

Re: [sqlite] search

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

Re: [sqlite] Hi All, How to use System.data.sqlite on both 32-bit and 64-bit system.

> > is there a dll can be used on both system? > Not really. Different native code is required for 32-bit and 64-bit processes. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] [shell] utf-8 bug

Thank you! What a mess... On Tue, Dec 27, 2011 at 9:00 PM, Kevin Benson wrote: > On 12/27/11, Jens Frederich wrote: > > The sqlite3 command line app doesn't write the string correctly to the > > database file. It uses the terminal (cmd) encoding

Re: [sqlite] [shell] utf-8 bug

On 12/27/11, Jens Frederich wrote: > The sqlite3 command line app doesn't write the string correctly to the > database file. It uses the terminal (cmd) encoding instead the 'PRAGMA > encoding' statement. chcp 65001 ...may not be a reliable avenue:

[sqlite] Hi All, How to use System.data.sqlite on both 32-bit and 64-bit system.

Hi All, I have a project with sqlite, I use System.data.sqlite.dll to connect the sqlite database. but some customer use 32-bit system, some use 64-bit system. I know there is a 64-bit dll file, but I must build the project twice with different dll. is there a dll can be used on both system?

Re: [sqlite] [shell] utf-8 bug

On Tue, Dec 27, 2011 at 12:44 PM, Jens Frederich wrote: > The sqlite3 command line app doesn't write the string correctly to the > database file. It uses the terminal (cmd) encoding instead the 'PRAGMA > encoding' statement. None of the SQLite3 code converts between

Re: [sqlite] [shell] utf-8 bug

I'm an UTF-8 expert. Code page 65001 is CP_UTF8, look here ( http://msdn.microsoft.com/en-us/library/windows/desktop/ms683169(v=vs.85).aspx ). I know that code page 437 (alias OEM) works. But the ü-character isn't correct encoded in the database file. The UTF-8 hex byte sequence of ü is

Re: [sqlite] Optimizing a query with range comparison.

On Tue, Dec 27, 2011 at 10:47 AM, Igor Tandetnik wrote: > If you need to do this with any regularity, you should look at R-Tree > module: > > http://www.sqlite.org/rtree.html I do have a lot of range-based queries and rtree seems to be a perfect solution for my problem. I am

Re: [sqlite] Optimizing a query with range comparison.

On 27 Dec 2011, at 5:36pm, Igor Tandetnik wrote: > On 12/27/2011 12:17 PM, Bo Peng wrote: >> sqlite selects all ids before it uses B-TREE for DISTINCT. Is there a >> way to tell sqlite to return an id when it founds the first range that >> the id falls into? > > Without a temporary set to store

Re: [sqlite] Optimizing a query with range comparison.

On 12/27/2011 12:17 PM, Bo Peng wrote: sqlite selects all ids before it uses B-TREE for DISTINCT. Is there a way to tell sqlite to return an id when it founds the first range that the id falls into? Without a temporary set to store the IDs it has already retrieved, how do you expect SQLite to

Re: [sqlite] [shell] utf-8 bug

I"m not a UTF expert but codepage 437 seems to work fine for your example. codepage 65001 is not "real" UTF-8 according to several google sources. You do have to use Lucida font. C:\chcp 437 Active code page: 437 C:\sqlite test.db SQLite version 3.7.9 2011-11-01 00:52:41 Enter ".help"

Re: [sqlite] Optimizing a query with range comparison.

>> Try using a JOIN instead.  In fact, try both ways around: >> >> DELETE FROM ids; >> INSERT INTO ids SELECT tableA.id FROM tableA JOIN tableB ON pos BETWEEN >> start AND end; >> >> then try >> >> DELETE FROM ids; >> INSERT INTO ids SELECT tableA.id FROM tableB JOIN tableA ON pos BETWEEN >>

Re: [sqlite] Optimizing a query with range comparison.

On 27 Dec 2011, at 4:46pm, Pavel Ivanov wrote: >>> INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND >>> end; >> >> Try using a JOIN instead. In fact, try both ways around: >> >> DELETE FROM ids; >> INSERT INTO ids SELECT tableA.id FROM tableA JOIN tableB ON pos

Re: [sqlite] Optimizing a query with range comparison.

On 12/27/2011 10:39 AM, Bo Peng wrote: The schema of my test tables is as follows: CREATE TABLE tableA (id INT, pos INT); CREATE TABLE tableB (start INT, end INT); CREATE INDEX tableA_idx on tableA (pos asc); CREATE INDEX tableB_idx on tableB (start asc, end asc); CREATE TABLE ids (id INT);

Re: [sqlite] Optimizing a query with range comparison.

>> INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND >> end; > > Try using a JOIN instead.  In fact, try both ways around: > > DELETE FROM ids; > INSERT INTO ids SELECT tableA.id FROM tableA JOIN tableB ON pos BETWEEN start > AND end; > > then try > > DELETE FROM ids; >

Re: [sqlite] Optimizing a query with range comparison.

On 27 Dec 2011, at 3:39pm, Bo Peng wrote: > The schema of my test tables is as follows: > > CREATE TABLE tableA (id INT, pos INT); > CREATE TABLE tableB (start INT, end INT); > > CREATE INDEX tableA_idx on tableA (pos asc); > CREATE INDEX tableB_idx on tableB (start asc, end asc); > > CREATE

Re: [sqlite] 3.7.9 amalgamation file in VS2005

VS2010 has the same problem. The file is simply too big for it. I'm not a fan of the amalgamation because of this. It's essentially impossible to trace into the SQlite code now. That said, it compiles and works fine. C Tuesday, December 27, 2011, 10:50:14 AM, you wrote: JJD> In my

Re: [sqlite] Optimizing a query with range comparison.

I do something similar, where the ranges are zip codes. However, my tableb is arranged vertically with one key (zip code) and one value (geographic zone). I would then join the two tables using the zip code, rather than trying to identify the zip code within a range of zip codes in tableb.

Re: [sqlite] 3.7.9 amalgamation file in VS2005

In my experience I find that most syntax highlighters fail on occasion, especially with large files. Trust the compiler not the editor. 2011/12/27 Alexandr Němec > > Dear all, > > I have one question that is not strictly a SQLite question (sorry), but > maybe someone

[sqlite] Optimizing a query with range comparison.

Dear Sqlite experts, The schema of my test tables is as follows: CREATE TABLE tableA (id INT, pos INT); CREATE TABLE tableB (start INT, end INT); CREATE INDEX tableA_idx on tableA (pos asc); CREATE INDEX tableB_idx on tableB (start asc, end asc); CREATE TABLE ids (id INT); tableA saves

[sqlite] 3.7.9 amalgamation file in VS2005

Dear all,   I have one question that is not strictly a SQLite question (sorry), but maybe someone encountered this problem and found the solution. I have upgraded from an older SQLite release to 3.7.9 and loaded the amalgamation file into a Visual Studio 2005 project. But the syntax code

Re: [sqlite] search

At 15:10 27/12/2011, Mohit Sindhwani wrote: On 27/12/2011 9:25 PM, Eduardo Morras wrote: At 11:27 27/12/2011, you wrote: 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,

Re: [sqlite] search

On 27/12/2011 9:25 PM, Eduardo Morras wrote: At 11:27 27/12/2011, you wrote: 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? Change '/' to ','. This way you get a

Re: [sqlite] search

At 11:27 27/12/2011, you wrote: 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? Change '/' to ','. This way you get a csv file which you can import directly. Perhaps

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,

Re: [sqlite] search

2011/12/27 Durga D : > 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,

Re: [sqlite] search

Hi kit, Thanks for your response. CREATE VIRTUAL TABLE virfts4 using fts4(residence, arrivtime, duration, imagelocation); insert into virfts4 values('/country1/state1/city1/village1', 0730, 1500, 'C'); insert into virfts4 values('/country1/state1/city1/village2', 0731, 1500, 'C'); insert

Re: [sqlite] search

2011/12/27 Durga D : > HI all, >   I have sqlite database with more than 1000 records. Here, > residencearea is the primary key. > /country/state/city/village >   I tried with fts3 and fts4 virtual tables. Not much performance. >   like query on direct table, dead slow.