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 you need to add a line with the table column description. in case, if I have 250 levels like this ex: file system. how to do this. any idea? You can use a shell script to do so. I leave it as exercise to you ;) Thanks in advance. HTH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 highlighter does not behave correctly with the 3.7.9 amalgamation file because it greys out not only the sections of code that are not compiled at all (because of if(n)def's) but also other sections that DO compile. Well, I think that this is a bug of the VS 2005 syntax highlighter for such a large source file, because the file compiles ok, but working with such a file in VS 2005 is frustrating. Did anyone see (did anyone find a solution for) this problem? I have not seen this for older versions of the amalgamation file. Best regards Alex ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 csv file which you can import directly. Perhaps you need to add a line with the table column description. or at the SQLite3 shell do sqliteseparator / before doing a .import no? Cheers, Mohit. 27/12/2011 | 10:10 PM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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, how? Change '/' to ','. This way you get a csv file which you can import directly. Perhaps you need to add a line with the table column description. or at the SQLite3 shell do sqliteseparator / before doing a .import no? Yes, didn't remember. Cheers, Mohit. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
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. Matching 3 million tablea rows to 49,000 tableb rows takes 90 seconds, I think. create tablea (id int, pos int); create tableb (pos int, ?? int); /* not sure what is represented by index position of range */ create table ids as select ?? from tableb b join tablea a on b.pos = a.pos; On Tue, Dec 27, 2011 at 9:39 AM, Bo Peng ben@gmail.com wrote: 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 position of about 8 million objects, and table B saves about 40 thousand ranges. I need to find out all ids in tableA that falls into one of the ranges in tableB, and insert the results into table ids. I am using a query INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND end; with indexes on tableA.pos and tableB.start, tableB.end (combined), this query takes hours to execute. Is there anyway to optimize this query? My understanding is that, if the query takes each pos and compare it to all ranges, it will be slow. If it takes each range and get all pos fall into the range, the query will be much faster. I have tried to 'EXPLAIN' the query but I do not understand the output because it looks different from what is described in http://www.sqlite.org/eqp.html. I will appreciate it if someone can tell me what sqlite is doing for this query. explain select id from tableA, tableB where pos between start and end; 0|Trace|0|0|0||00| 1|Goto|0|26|0||00| 2|OpenRead|1|2|0|2|00| 3|OpenRead|0|1446|0|2|00| 4|OpenRead|2|133259|0|keyinfo(1,BINARY)|00| 5|Rewind|1|22|0||00| 6|Column|1|0|1||00| 7|IsNull|1|21|0||00| 8|Affinity|1|1|0|d|00| 9|SeekGe|2|21|1|1|00| 10|Column|1|1|1||00| 11|IsNull|1|21|0||00| 12|Affinity|1|1|0|d|00| 13|IdxGE|2|21|1|1|01| 14|Column|2|0|2||00| 15|IsNull|2|20|0||00| 16|IdxRowid|2|2|0||00| 17|Seek|0|2|0||00| 18|Column|0|0|3||00| 19|ResultRow|3|1|0||00| 20|Next|2|13|0||00| 21|Next|1|6|0||01| 22|Close|1|0|0||00| 23|Close|0|0|0||00| 24|Close|2|0|0||00| 25|Halt|0|0|0||00| 26|Transaction|0|0|0||00| 27|VerifyCookie|0|6|0||00| 28|TableLock|0|2|0|tableB|00| 29|TableLock|0|1446|0|tableA|00| 30|Goto|0|2|0||00| Many thanks in advance, Bo ___ 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] 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 experience I find that most syntax highlighters fail JJD on occasion, especially with large files. Trust the compiler not the JJD editor. JJD 2011/12/27 Alexandr Němec a.ne...@atlas.cz 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 highlighter does not behave correctly with the 3.7.9 amalgamation file because it greys out not only the sections of code that are not compiled at all (because of if(n)def's) but also other sections that DO compile. Well, I think that this is a bug of the VS 2005 syntax highlighter for such a large source file, because the file compiles ok, but working with such a file in VS 2005 is frustrating. Did anyone see (did anyone find a solution for) this problem? I have not seen this for older versions of the amalgamation file. Best regards Alex ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 TABLE ids (id INT); First, thanks a lot for posting that, which saves us all a huge amount of guessing. tableA saves position of about 8 million objects, and table B saves about 40 thousand ranges. I need to find out all ids in tableA that falls into one of the ranges in tableB, and insert the results into table ids. I am using a query So you don't care how many ranges in tableB an object falls into, you just want it to appear once ? Or your data is structures so that ranges don't overlap ? Either way, what you really want for ids is something more like CREATE TABLE ids (id INTEGER PRIMARY KEY, ON CONFLICT IGNORE); To understand this better, read http://www.sqlite.org/lang_createtable.html#rowid 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; INSERT INTO ids SELECT tableA.id FROM tableB JOIN tableA ON pos BETWEEN start AND end; Which one is faster depends on some aspects about your data and it's easier for you to test it than for me to guess. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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; INSERT INTO ids SELECT tableA.id FROM tableB JOIN tableA ON pos BETWEEN start AND end; Which one is faster depends on some aspects about your data and it's easier for you to test it than for me to guess. If these two don't behave identically to each other and don't behave identically to the original query then there's bug in SQLite. With inner join it shouldn't matter for optimizer which form your query is written in. For OP: please issue EXPLAIN QUERY PLAN instead of EXPLAIN on your query. It will give more understandable information on how SQLite processes your query. Pavel On Tue, Dec 27, 2011 at 11:39 AM, Simon Slavin slav...@bigfraud.org wrote: 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 TABLE ids (id INT); First, thanks a lot for posting that, which saves us all a huge amount of guessing. tableA saves position of about 8 million objects, and table B saves about 40 thousand ranges. I need to find out all ids in tableA that falls into one of the ranges in tableB, and insert the results into table ids. I am using a query So you don't care how many ranges in tableB an object falls into, you just want it to appear once ? Or your data is structures so that ranges don't overlap ? Either way, what you really want for ids is something more like CREATE TABLE ids (id INTEGER PRIMARY KEY, ON CONFLICT IGNORE); To understand this better, read http://www.sqlite.org/lang_createtable.html#rowid 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; INSERT INTO ids SELECT tableA.id FROM tableB JOIN tableA ON pos BETWEEN start AND end; Which one is faster depends on some aspects about your data and it's easier for you to test it than for me to guess. Simon. ___ 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] 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); tableA saves position of about 8 million objects, and table B saves about 40 thousand ranges. I need to find out all ids in tableA that falls into one of the ranges in tableB, and insert the results into table ids. If you need to do this with any regularity, you should look at R-Tree module: http://www.sqlite.org/rtree.html I am using a query INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND end; with indexes on tableA.pos and tableB.start, tableB.end (combined), this query takes hours to execute. An index on tableB(start, end) can only be used to satisfy one condition (pos = start). After that, it's a linear scan. Is there anyway to optimize this query? My understanding is that, if the query takes each pos and compare it to all ranges, it will be slow. If it takes each range and get all pos fall into the range, the query will be much faster. Try changing the order of tables in the FROM clause: SELECT id FROM tableB, tableA WHERE pos BETWEEN start AND end; All other things being equal (and here SQLite has no reason to believe they are not), SQLite tends to scan on the left hand side of the join, and search on the right hand side. Alternatively, drop an index on TableB(start, end). It doesn't help much anyway, and without it, things are no longer equal and SQLite should choose the plan you want. Note that, unless you know that ranges don't overlap, you may be getting duplicate IDs. You may want to change your query to SELECT DISTINCT id FROM tableB, tableA WHERE pos BETWEEN start AND end; I have tried to 'EXPLAIN' the query but I do not understand the output Use EXPLAIN QUERY PLAN instead. This produces a human-readable summary of the plan. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 a.ne...@atlas.cz 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 highlighter does not behave correctly with the 3.7.9 amalgamation file because it greys out not only the sections of code that are not compiled at all (because of if(n)def's) but also other sections that DO compile. Well, I think that this is a bug of the VS 2005 syntax highlighter for such a large source file, because the file compiles ok, but working with such a file in VS 2005 is frustrating. Did anyone see (did anyone find a solution for) this problem? I have not seen this for older versions of the amalgamation file. Best regards Alex ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- *Jim Dodgen* * * ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 position of about 8 million objects, and table B saves about 40 thousand ranges. I need to find out all ids in tableA that falls into one of the ranges in tableB, and insert the results into table ids. I am using a query INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND end; with indexes on tableA.pos and tableB.start, tableB.end (combined), this query takes hours to execute. Is there anyway to optimize this query? My understanding is that, if the query takes each pos and compare it to all ranges, it will be slow. If it takes each range and get all pos fall into the range, the query will be much faster. I have tried to 'EXPLAIN' the query but I do not understand the output because it looks different from what is described in http://www.sqlite.org/eqp.html. I will appreciate it if someone can tell me what sqlite is doing for this query. explain select id from tableA, tableB where pos between start and end; 0|Trace|0|0|0||00| 1|Goto|0|26|0||00| 2|OpenRead|1|2|0|2|00| 3|OpenRead|0|1446|0|2|00| 4|OpenRead|2|133259|0|keyinfo(1,BINARY)|00| 5|Rewind|1|22|0||00| 6|Column|1|0|1||00| 7|IsNull|1|21|0||00| 8|Affinity|1|1|0|d|00| 9|SeekGe|2|21|1|1|00| 10|Column|1|1|1||00| 11|IsNull|1|21|0||00| 12|Affinity|1|1|0|d|00| 13|IdxGE|2|21|1|1|01| 14|Column|2|0|2||00| 15|IsNull|2|20|0||00| 16|IdxRowid|2|2|0||00| 17|Seek|0|2|0||00| 18|Column|0|0|3||00| 19|ResultRow|3|1|0||00| 20|Next|2|13|0||00| 21|Next|1|6|0||01| 22|Close|1|0|0||00| 23|Close|0|0|0||00| 24|Close|2|0|0||00| 25|Halt|0|0|0||00| 26|Transaction|0|0|0||00| 27|VerifyCookie|0|6|0||00| 28|TableLock|0|2|0|tableB|00| 29|TableLock|0|1446|0|tableA|00| 30|Goto|0|2|0||00| Many thanks in advance, Bo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] search
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
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 into virfts4 values('/countr1/state1/village1/', 1800, 1000, 'D');
select * from virfts4 where residence match '/*'; -- dint work
how to get counties names from this db by using query?
select * from virfts4 where residence match '/c*'; -- it's worked and very
fast.
Thanks in advance.
On Tue, Dec 27, 2011 at 2:01 PM, Kit kit.sa...@gmail.com wrote:
2011/12/27 Durga D durga.d...@gmail.com:
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.
Send your SQL query and table structure.
Maybe you used LIKE instead of MATCH.
--
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
Re: [sqlite] search
2011/12/27 Durga D durga.d...@gmail.com: 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. Send your SQL query and table structure. Maybe you used LIKE instead of MATCH. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 BETWEEN start AND end; then try DELETE FROM ids; INSERT INTO ids SELECT tableA.id FROM tableB JOIN tableA ON pos BETWEEN start AND end; Which one is faster depends on some aspects about your data and it's easier for you to test it than for me to guess. If these two don't behave identically to each other and don't behave identically to the original query then there's bug in SQLite. With inner join it shouldn't matter for optimizer which form your query is written in. After an 'ANALYZE', perhaps. But without it my two forms give different 'EXPLAIN QUERY PLAN' results to one-another, though one of them is the same as the OP's version. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 start AND end; Which one is faster depends on some aspects about your data and it's easier for you to test it than for me to guess. If these two don't behave identically to each other and don't behave identically to the original query then there's bug in SQLite. With inner join it shouldn't matter for optimizer which form your query is written in. Thank everyone for your quick replies. I trimmed down my tables to have 10,000 and 1000 rows respectively, and re-create a test database test1_fresh.DB WITHOUT analyze it. bpeng@bp8:annoDB % cp test1_fresh.db test1.db bpeng@bp8:annoDB % time sqlite3 test1.db 'insert into ids select tableA.id from tableA join tableB on pos between start and end;' real0m28.026s user0m27.994s sys 0m0.016s bpeng@bp8:annoDB % sqlite3 test1.db 'select count(*) from ids;' 32486 bpeng@bp8:annoDB % cp test1_fresh.db test1.db bpeng@bp8:annoDB % time sqlite3 test1.db 'insert into ids select tableA.id from tableB join tableA on pos between start and end;' real0m0.085s user0m0.061s sys 0m0.010s bpeng@bp8:annoDB % sqlite3 test1.db 'select count(*) from ids;' 32486 To my surprise, the order of join has a significant impact on the performance of query. The explain command shows why: bpeng@bp8:annoDB % sqlite3 test1.db 'explain query plan insert into ids select tableA.id from tableA join tableB on pos between start and end;' 0|0|0|SCAN TABLE tableA (~100 rows) 0|1|1|SEARCH TABLE tableB USING COVERING INDEX tableB_idx (start?) (~125000 rows) bpeng@bp8:annoDB % sqlite3 test1.db 'explain query plan insert into ids select tableA.id from tableB join tableA on pos between start and end;' 0|0|0|SCAN TABLE tableB (~100 rows) 0|1|1|SEARCH TABLE tableA USING INDEX tableA_idx (pos? AND pos?) (~3 rows) A simple 'analyze' command seems to be able to fix the problem: bpeng@bp8:annoDB % cp test1_fresh.db test1.db bpeng@bp8:annoDB % sqlite3 test1.db 'analyze' bpeng@bp8:annoDB % time sqlite3 test1.db 'insert into ids select tableA.id from tableA join tableB on pos between start and end;' real0m0.086s user0m0.062s sys 0m0.011s Anyway, 'from tableA join tableB' and 'from tableA, tableB' seem to have the same performance: bpeng@bp8:annoDB % cp test1_fresh.db test1.db bpeng@bp8:annoDB % sqlite3 test1.db 'analyze' bpeng@bp8:annoDB % time sqlite3 test1.db 'insert into ids select tableA.id from tableA, tableB where pos between start and end;' real0m0.086s user0m0.062s sys 0m0.010s bpeng@bp8:annoDB % cp test1_fresh.db test1.db bpeng@bp8:annoDB % time sqlite3 test1.db 'insert into ids select tableA.id from tableA, tableB where pos between start and end;' real0m27.972s user0m27.918s sys 0m0.035s Going back to my original problem: my ranges do overlap and I only need distinct IDs. The query I am using is a subquery in my application and I use 'DISTINCT' to select distinct IDs. From the 'explain' output: bpeng@bp8:annoDB % sqlite3 test1.db 'explain query plan insert into ids select tableA.id from tableA join tableB on pos between start and end;' 0|0|1|SCAN TABLE tableB (~1000 rows) 0|1|0|SEARCH TABLE tableA USING INDEX tableA_idx (pos? AND pos?) (~3000 rows) bpeng@bp8:annoDB % sqlite3 test1.db 'explain query plan insert into ids select distinct tableA.id from tableA join tableB on pos between start and end;' 0|0|1|SCAN TABLE tableB (~1000 rows) 0|1|0|SEARCH TABLE tableA USING INDEX tableA_idx (pos? AND pos?) (~3000 rows) 0|0|0|USE TEMP B-TREE FOR DISTINCT 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? Bo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [shell] utf-8 bug
Im 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 for instructions
Enter SQL statements terminated with a ;
sqlite select length('füchsen');
7
sqlite .quit
C:\chcp 65001
Active code page: 65001
C:\Documents and Settings\s360740.DIFL1ITE0017341\My Documents\Visual Studio
2005\Projects\sqlite\Debugsqlite
SQLite version 3.7.9 2011-11-01 00:52:41
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite select length('füchsen');
aborts
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 Jens Frederich [jfreder...@gmail.com]
Sent: Tuesday, December 27, 2011 1:59 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] [shell] utf-8 bug
Hi all,
I believe there is a bug in shell tool on Windows. I've tried to store
utf-8 encoded literals in my test db. Do the following to reproduce the
issue:
1. Open a Windows Console (cmd.exe)
2. chcp 65001 # change cmd.exe code page from 437(OEM) to utf-8
3. Run the shell
sqlite3.exe test.db
create table test(id integer primary key, value text);
insert into test (id, value) values (1, 'füchsen'); # literial with
multi byte characters
The shell unexpectantly closed on the last command.
What is the preferred way to save utf-8 encoded literals with the windows
shell tool?
Jens
___
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] 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 figure out whether a given range is in fact the first one a given ID falls into? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 the IDs it has already retrieved, how do you expect SQLite to figure out whether a given range is in fact the first one a given ID falls into? Yeah. You do need to store the results. If you use INSERT OR IGNORE (or some equivalent way of keeping only the first found result) you can kind of hack it by relying on how SQLite uses indexes, but it is a hack and might fail in some future version of SQLite. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimizing a query with range comparison.
On Tue, Dec 27, 2011 at 10:47 AM, Igor Tandetnik itandet...@mvps.org 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 using the sqlite module from Python and a good news is that, at least on Mac, RTREE is enabled by default (python 2.7, Mac/BuildScript/build-installer.py has -DSQLITE_ENABLE_RTREE). I will report back my if I can use this module to optimize my query. Thanks, Bo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 \xBC\xC3. If you look at the database
via hexdump or xxd you can see that the ü-character is encoded with the hex
sequence 0x81. That's the OEM encoding and not UTF-8. I use my database on
different operating systems. Thus, it is very important that all characters
are correctly UTF-8 encoded.
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.
Jens
On Tue, Dec 27, 2011 at 6:29 PM, Black, Michael (IS) michael.bla...@ngc.com
wrote:
Im 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 for instructions
Enter SQL statements terminated with a ;
sqlite select length('füchsen');
7
sqlite .quit
C:\chcp 65001
Active code page: 65001
C:\Documents and Settings\s360740.DIFL1ITE0017341\My Documents\Visual
Studio 2005\Projects\sqlite\Debugsqlite
SQLite version 3.7.9 2011-11-01 00:52:41
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite select length('füchsen');
aborts
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 Jens Frederich [jfreder...@gmail.com]
Sent: Tuesday, December 27, 2011 1:59 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] [shell] utf-8 bug
Hi all,
I believe there is a bug in shell tool on Windows. I've tried to store
utf-8 encoded literals in my test db. Do the following to reproduce the
issue:
1. Open a Windows Console (cmd.exe)
2. chcp 65001 # change cmd.exe code page from 437(OEM) to utf-8
3. Run the shell
sqlite3.exe test.db
create table test(id integer primary key, value text);
insert into test (id, value) values (1, 'füchsen'); # literial with
multi byte characters
The shell unexpectantly closed on the last command.
What is the preferred way to save utf-8 encoded literals with the windows
shell tool?
Jens
___
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
Re: [sqlite] [shell] utf-8 bug
On Tue, Dec 27, 2011 at 12:44 PM, Jens Frederich jfreder...@gmail.com 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 encodings (other than UTF8-UTF-16). Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [shell] utf-8 bug
On 12/27/11, Jens Frederich jfreder...@gmail.com 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: https://www.google.com/search?q=codepage+65001+Windows+WriteFile+bug -- -- -- -- --ô¿ô-- K e V i N ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [shell] utf-8 bug
Thank you! What a mess... On Tue, Dec 27, 2011 at 9:00 PM, Kevin Benson kevin.m.ben...@gmail.comwrote: On 12/27/11, Jens Frederich jfreder...@gmail.com 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: https://www.google.com/search?q=codepage+65001+Windows+WriteFile+bug -- -- -- -- --ô¿ô-- K e V i N ___ 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] 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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 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
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 probably more than enough for your example. The query plan looks correct to my simple eye... SQLite version 3.7.9 2011-11-01 00:52:41 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE TABLE tableA (id INT, pos INT); sqlite CREATE TABLE tableB (start INT, end INT); sqlite sqlite CREATE INDEX tableA_idx on tableA (pos asc); sqlite CREATE INDEX tableB_idx on tableB (start asc, end asc); sqlite sqlite CREATE TABLE ids (id INT); sqlite sqlite explain query plan INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND end; 0|0|0|SCAN TABLE tableA (~100 rows) 0|1|1|SEARCH TABLE tableB USING COVERING INDEX tableB_idx (start?) (~125000 rows) 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 Bo Peng [ben@gmail.com] Sent: Tuesday, December 27, 2011 9:39 AM To: General Discussion of SQLite Database Subject: EXT :[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 position of about 8 million objects, and table B saves about 40 thousand ranges. I need to find out all ids in tableA that falls into one of the ranges in tableB, and insert the results into table ids. I am using a query INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND end; with indexes on tableA.pos and tableB.start, tableB.end (combined), this query takes hours to execute. Is there anyway to optimize this query? My understanding is that, if the query takes each pos and compare it to all ranges, it will be slow. If it takes each range and get all pos fall into the range, the query will be much faster. I have tried to 'EXPLAIN' the query but I do not understand the output because it looks different from what is described in http://www.sqlite.org/eqp.html. I will appreciate it if someone can tell me what sqlite is doing for this query. explain select id from tableA, tableB where pos between start and end; 0|Trace|0|0|0||00| 1|Goto|0|26|0||00| 2|OpenRead|1|2|0|2|00| 3|OpenRead|0|1446|0|2|00| 4|OpenRead|2|133259|0|keyinfo(1,BINARY)|00| 5|Rewind|1|22|0||00| 6|Column|1|0|1||00| 7|IsNull|1|21|0||00| 8|Affinity|1|1|0|d|00| 9|SeekGe|2|21|1|1|00| 10|Column|1|1|1||00| 11|IsNull|1|21|0||00| 12|Affinity|1|1|0|d|00| 13|IdxGE|2|21|1|1|01| 14|Column|2|0|2||00| 15|IsNull|2|20|0||00| 16|IdxRowid|2|2|0||00| 17|Seek|0|2|0||00| 18|Column|0|0|3||00| 19|ResultRow|3|1|0||00| 20|Next|2|13|0||00| 21|Next|1|6|0||01| 22|Close|1|0|0||00| 23|Close|0|0|0||00| 24|Close|2|0|0||00| 25|Halt|0|0|0||00| 26|Transaction|0|0|0||00| 27|VerifyCookie|0|6|0||00| 28|TableLock|0|2|0|tableB|00| 29|TableLock|0|1446|0|tableA|00| 30|Goto|0|2|0||00| Many thanks in advance, Bo ___ 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] 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 Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Alexandr Němec [a.ne...@atlas.cz] Sent: Tuesday, December 27, 2011 8:55 AM To: sqlite-users@sqlite.org Subject: EXT :[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 highlighter does not behave correctly with the 3.7.9 amalgamation file because it greys out not only the sections of code that are not compiled at all (because of if(n)def's) but also other sections that DO compile. Well, I think that this is a bug of the VS 2005 syntax highlighter for such a large source file, because the file compiles ok, but working with such a file in VS 2005 is frustrating. Did anyone see (did anyone find a solution for) this problem? I have not seen this for older versions of the amalgamation file. Best regards Alex ___ 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] 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 Mistachkin sql...@mistachkin.com
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
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] (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] 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 something wrong. Note: there is no spelling mistakes. all sql stmts compiled. but dint get result. result should be greater than 0. it has 4 records. Thanks in advance. On Tue, Dec 27, 2011 at 7:40 PM, Mohit Sindhwani m...@onghu.com 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, how? Change '/' to ','. This way you get a csv file which you can import directly. Perhaps you need to add a line with the table column description. or at the SQLite3 shell do sqliteseparator / before doing a .import no? Cheers, Mohit. 27/12/2011 | 10:10 PM. __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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] 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, 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
