Re: [sqlite] SQLite Database Browser for creating databases
On Thu, 2007-08-23 at 13:02 -0500, Mark Brown wrote: > Hi- > > We are currently creating databases using this open source tool. According > to the documentation, it is using version 3.3.5 of SQLite. I was wondering > if there are any problems with creating a database with the tool, but then > using the database with an application that is using SQLite 3.4.1? Would > the format of the database be compatible? Yes. It will be compatible. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FTS2 suggestion
I On 23/08/07, Russell Leighton <[EMAIL PROTECTED]> wrote: > > > Could fts3 (the next fts) have the option to override the default > 'match' function with one passed in (similar to the tokenizer)? > > The reason I ask is then the fts table could be used as smart index > when the tokenizer is > something like bigram, trigram, etc. and the 'match' function computes > a similarity metric > and returns the row if above a threshold. > > Postgres does this when you declare an index of type trigram, see: > > http://www.sai.msu.su/~megera/postgres/gist/pg_trgm/README.pg_trgm > > Since SQLite does not allow 'plug-in' indexes, the idea would be to > create an fts3 table with a key back to the main table and the string > column you want index. > Indexing becomes a join through the fts3 table. > > You would probably want to allow the user to pass args to the 'match' > function so a threshold could be set to non-default values and maybe > tweak matching options > specific to the match and tokenization. > > Thoughts? I think this idea is great... If the ft3 has this optionality i could rewrite the match function, I like the idea to give the possibility that users can training with data, and in database is where most data are store, and usually by categories, tags, or other system. My goal is to give a set of data for learn, then in new inserts assign the correct or closest category. And another feature that I want is that it could learn about its mistakes (human assisted) On Aug 23, 2007, at 4:56 PM, Scott Hess wrote: > > > On 8/20/07, Cesar D. Rodas <[EMAIL PROTECTED]> wrote: > >> As I know ( I can be wrong ) SQLite Full Text Search is only match > >> with hole > >> words right? It could not be > >> And also no FT extension to db ( as far I know) is miss spell > >> tolerant, > > > > Yes, fts is matching exactly. There is some primitive support for > > English stemming using the Porter stemmer, but, honestly, it's not > > well-exercised. > > > >> And > >> I've found this Paper that talks about *Using Superimposed Coding Of > >> N-Gram > >> Lists For Efficient Inexact Matching* > > > > http://citeseer.ist.psu.edu/cache/papers/cs/22812/http: > > zSzzSzwww.novodynamics.comzSztrenklezSzpaperszSzatc92v.pdf/ > > william92using.pdf > >> > >> I was reading and it is not so hard to implement, but it cost a extra > >> storage space, but I think the benefits are more. > >> > >> Also following this paper could be done a way to match with fragments > >> of > >> words... what do you think of it? > > > > It's an interesting paper, and I must say that anything which involves > > Bloom Filters automatically draws my attention :-). > > > > While I think spelling-suggestion might be valuable for fts in the > > longer term, I'm not very enthusiastic about this particular model. > > It seems much more useful in the standard indexing model of building > > the index, manually tweaking it, and then doing a ton of queries > > against it. fts is really fairly constrained, because many use-cases > > are more along the lines of update the index quite a bit, and query it > > only a few times. > > > > Also, I think the concepts in the paper might have very significant > > problems handling Unicode, because the bit vectors will get so very > > large. I may be wrong, sometimes the overlapping-vector approach can > > have surprising relevance depending on the frequency distribution of > > the things in the vector. It would need some experimentation to > > figure that out. > > > > Certainly something to bookmark, though. > > > > Thanks, > > scott > > > > --- > > -- > > To unsubscribe, send email to [EMAIL PROTECTED] > > --- > > -- > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > -- Cesar D. Rodas http://www.cesarodas.com/ Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED]
Re: [sqlite] FTS2 suggestion
Could fts3 (the next fts) have the option to override the default 'match' function with one passed in (similar to the tokenizer)? The reason I ask is then the fts table could be used as smart index when the tokenizer is something like bigram, trigram, etc. and the 'match' function computes a similarity metric and returns the row if above a threshold. Postgres does this when you declare an index of type trigram, see: http://www.sai.msu.su/~megera/postgres/gist/pg_trgm/README.pg_trgm Since SQLite does not allow 'plug-in' indexes, the idea would be to create an fts3 table with a key back to the main table and the string column you want index. Indexing becomes a join through the fts3 table. You would probably want to allow the user to pass args to the 'match' function so a threshold could be set to non-default values and maybe tweak matching options specific to the match and tokenization. Thoughts? On Aug 23, 2007, at 4:56 PM, Scott Hess wrote: On 8/20/07, Cesar D. Rodas <[EMAIL PROTECTED]> wrote: As I know ( I can be wrong ) SQLite Full Text Search is only match with hole words right? It could not be And also no FT extension to db ( as far I know) is miss spell tolerant, Yes, fts is matching exactly. There is some primitive support for English stemming using the Porter stemmer, but, honestly, it's not well-exercised. And I've found this Paper that talks about *Using Superimposed Coding Of N-Gram Lists For Efficient Inexact Matching* http://citeseer.ist.psu.edu/cache/papers/cs/22812/http: zSzzSzwww.novodynamics.comzSztrenklezSzpaperszSzatc92v.pdf/ william92using.pdf I was reading and it is not so hard to implement, but it cost a extra storage space, but I think the benefits are more. Also following this paper could be done a way to match with fragments of words... what do you think of it? It's an interesting paper, and I must say that anything which involves Bloom Filters automatically draws my attention :-). While I think spelling-suggestion might be valuable for fts in the longer term, I'm not very enthusiastic about this particular model. It seems much more useful in the standard indexing model of building the index, manually tweaking it, and then doing a ton of queries against it. fts is really fairly constrained, because many use-cases are more along the lines of update the index quite a bit, and query it only a few times. Also, I think the concepts in the paper might have very significant problems handling Unicode, because the bit vectors will get so very large. I may be wrong, sometimes the overlapping-vector approach can have surprising relevance depending on the frequency distribution of the things in the vector. It would need some experimentation to figure that out. Certainly something to bookmark, though. Thanks, scott --- -- To unsubscribe, send email to [EMAIL PROTECTED] --- -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Spatial searches
Besides the suggestions from Dennis below, please search the archives for emails by me on doing exactly this. I achieved fairly decent performance on a database of 7.5 million rows doing lookups on 250k rectangles. I was working on a quad-Xeon server with 4 Gb ram and Win XP, using Perl to work on SQLite. The entire task would take about 23 hours... the performance was nearly linear... slowing down slightly as more records were processed. On 8/24/07, Dennis Cote <[EMAIL PROTECTED]> wrote: > David Thieme wrote: > > Scott, > > Yes, the SELECT is very simple, but slow. I have tens of thousands of > > records and I need the data very fast (embedded realtime system). Some > > databases natively support spatial searches, using KD-trees or R-Trees or > > Quad-trees to improve the search speed. I found an article that explains > > how to implement a custom-spatial search in SQL 2007: > > "Using Table Valued Functions in SQL Server > >2005 to Implement a Spatial Data Library" > > But the solution is very specific to SQL server. I thought there might be > > other tricks that might be common for implementing a fast spatial search in > > a database that doesn't natively support this feature. > > > David, > > SQLite has no direct support for spatial searches, but you should be > able to get reasonable results for a table with thousands of records > using a couple of indexes on the latitude and longitude of the points, > assuming your range is a reasonably small part of your total search space. > > Given a schema like this: > > create table pts ( > id integer primary key, > lat real, > lng real, > data text > ); > > You can create two indexes that will speed up the searches for points > within a rectangle. > > create index lat_idx on pts(lat); > create index lng_idx on pts(lng); > > Now, to do the search you can use a query like this: > > select * from pts where id in > ( > select id from pts where lat between :min_lat and :max_lat > intersect > select id from pts where lng between :min_lng and :max_lng > ); > > If you use explain query plan you can see how this will be executed: > > sqlite> explain query plan select * from pts where id in >...> ( >...> select id from pts where lat between :min_lat and :max_lat >...> intersect >...> select id from pts where lng between :min_lng and :max_lng >...> ); > 0|0|TABLE pts USING PRIMARY KEY > 0|0|TABLE pts WITH INDEX lat_idx > 0|0|TABLE pts WITH INDEX lng_idx > > Or in all its excruciating detail using explain: > > sqlite> explain select * from pts where id in >...> ( >...> select id from pts where lat between :min_lat and :max_lat >...> intersect >...> select id from pts where lng between :min_lng and :max_lng >...> ); > addr opcode p1 p2 p3 > -- -- -- > - > 0 Goto0 78 > 1 Integer 0 0 > 2 OpenRead0 2 > 3 SetNumColumns 0 4 > 4 MemLoad 0 0 > 5 If 0 63 > 6 MemInt 1 0 > 7 OpenEphemeral 3 0 keyinfo(1,BINARY) > 8 SetNumColumns 3 1 > 9 OpenEphemeral 4 1 keyinfo(1,BINARY) > 10Integer 0 0 > 11OpenRead6 3 keyinfo(1,BINARY) > 12SetNumColumns 6 2 > 13Variable2 0 :max_lat > 14IsNull -1 29 > 15MakeRecord 1 0 e > 16MemStore2 1 > 17Variable1 0 :min_lat > 18IsNull -1 29 > 19MakeRecord 1 0 e > 20MoveGe 6 29 > 21MemLoad 2 0 > 22IdxGE 6 29 + > 23Column 6 0 > 24IsNull 1 28 > 25IdxRowid6 0 > 26MakeRecord 1 0 > 27IdxInsert 4 0 > 28Next6 21 > 29Close 6 0 > 30OpenEphemeral 5 1 keyinfo(1,BINARY) > 31Integer 0 0 > 32OpenRead7 4 keyinfo(1,BINARY) > 33SetNumColumns 7 2 > 34Variable4 0 :max_lng > 35IsNull -1 50 > 36MakeRecord 1 0 e > 37MemStore4 1 > 38Variable3 0 :min_lng > 39IsNull
Re: [sqlite] Spatial searches
David Thieme wrote:
Scott,
Yes, the SELECT is very simple, but slow. I have tens of thousands of
records and I need the data very fast (embedded realtime system). Some
databases natively support spatial searches, using KD-trees or R-Trees or
Quad-trees to improve the search speed. I found an article that explains
how to implement a custom-spatial search in SQL 2007:
"Using Table Valued Functions in SQL Server
2005 to Implement a Spatial Data Library"
But the solution is very specific to SQL server. I thought there might be
other tricks that might be common for implementing a fast spatial search in
a database that doesn't natively support this feature.
David,
SQLite has no direct support for spatial searches, but you should be
able to get reasonable results for a table with thousands of records
using a couple of indexes on the latitude and longitude of the points,
assuming your range is a reasonably small part of your total search space.
Given a schema like this:
create table pts (
id integer primary key,
lat real,
lng real,
data text
);
You can create two indexes that will speed up the searches for points
within a rectangle.
create index lat_idx on pts(lat);
create index lng_idx on pts(lng);
Now, to do the search you can use a query like this:
select * from pts where id in
(
select id from pts where lat between :min_lat and :max_lat
intersect
select id from pts where lng between :min_lng and :max_lng
);
If you use explain query plan you can see how this will be executed:
sqlite> explain query plan select * from pts where id in
...> (
...> select id from pts where lat between :min_lat and :max_lat
...> intersect
...> select id from pts where lng between :min_lng and :max_lng
...> );
0|0|TABLE pts USING PRIMARY KEY
0|0|TABLE pts WITH INDEX lat_idx
0|0|TABLE pts WITH INDEX lng_idx
Or in all its excruciating detail using explain:
sqlite> explain select * from pts where id in
...> (
...> select id from pts where lat between :min_lat and :max_lat
...> intersect
...> select id from pts where lng between :min_lng and :max_lng
...> );
addr opcode p1 p2 p3
-- -- --
-
0 Goto0 78
1 Integer 0 0
2 OpenRead0 2
3 SetNumColumns 0 4
4 MemLoad 0 0
5 If 0 63
6 MemInt 1 0
7 OpenEphemeral 3 0 keyinfo(1,BINARY)
8 SetNumColumns 3 1
9 OpenEphemeral 4 1 keyinfo(1,BINARY)
10Integer 0 0
11OpenRead6 3 keyinfo(1,BINARY)
12SetNumColumns 6 2
13Variable2 0 :max_lat
14IsNull -1 29
15MakeRecord 1 0 e
16MemStore2 1
17Variable1 0 :min_lat
18IsNull -1 29
19MakeRecord 1 0 e
20MoveGe 6 29
21MemLoad 2 0
22IdxGE 6 29 +
23Column 6 0
24IsNull 1 28
25IdxRowid6 0
26MakeRecord 1 0
27IdxInsert 4 0
28Next6 21
29Close 6 0
30OpenEphemeral 5 1 keyinfo(1,BINARY)
31Integer 0 0
32OpenRead7 4 keyinfo(1,BINARY)
33SetNumColumns 7 2
34Variable4 0 :max_lng
35IsNull -1 50
36MakeRecord 1 0 e
37MemStore4 1
38Variable3 0 :min_lng
39IsNull -1 50
40MakeRecord 1 0 e
41MoveGe 7 50
42MemLoad 4 0
43IdxGE 7 50 +
44Column 7 0
45IsNull 1 49
46IdxRowid7 0
47MakeRecord 1 0
48IdxInsert 5 0
49Next7 42
50Close 7 0
51Rewind 4 61
52RowKey 4 0
53NotFound5 60
54Column 4 0
55NotNull -1 58
56Pop 1 0
57Goto0 60
58MakeRecord
Re: [sqlite] FTS2 suggestion
It's all interesting, but categorization is hard. Not so hard to get some results, sort of hard to get quality results. Might work as a nice adjunct to fts, so that you can throw the search terms into the categorization engine and put up suggestions for re-running the search with a tighter focus. -scott On 8/23/07, Cesar D. Rodas <[EMAIL PROTECTED]> wrote: > On 23/08/07, Scott Hess <[EMAIL PROTECTED]> wrote: > > On 8/20/07, Cesar D. Rodas <[EMAIL PROTECTED]> wrote: > > > As I know ( I can be wrong ) SQLite Full Text Search is only match with > > > hole > > > words right? It could not be > > > And also no FT extension to db ( as far I know) is miss spell tolerant, > > > > Yes, fts is matching exactly. There is some primitive support for > > English stemming using the Porter stemmer, but, honestly, it's not > > well-exercised. > > > > > And > > > I've found this Paper that talks about *Using Superimposed Coding Of > > > N-Gram > > > Lists For Efficient Inexact Matching* > > > > http://citeseer.ist.psu.edu/cache/papers/cs/22812/http:zSzzSzwww.novodynamics.comzSztrenklezSzpaperszSzatc92v.pdf/william92using.pdf > > > > > > I was reading and it is not so hard to implement, but it cost a extra > > > storage space, but I think the benefits are more. > > > > > > Also following this paper could be done a way to match with fragments of > > > words... what do you think of it? > > > > It's an interesting paper, and I must say that anything which involves > > Bloom Filters automatically draws my attention :-). > > Yeah. I am doing some investigations about that, I love that too. And > I was watching that with n-grams you get a filter to stop common > words, and could be used as a stemming-like algorithm but independent > from the language. > > I was thinking to implement this > http://www.mail-archive.com/sqlite-users%40sqlite.org/msg26923.html > when I finish up some things. What do you think of it? > > > While I think spelling-suggestion might be valuable for fts in the > > longer term, I'm not very enthusiastic about this particular model. > > It seems much more useful in the standard indexing model of building > > the index, manually tweaking it, and then doing a ton of queries > > against it. fts is really fairly constrained, because many use-cases > > are more along the lines of update the index quite a bit, and query it > > only a few times. > > > > Also, I think the concepts in the paper might have very significant > > problems handling Unicode, because the bit vectors will get so very > > large. I may be wrong, sometimes the overlapping-vector approach can > > have surprising relevance depending on the frequency distribution of > > the things in the vector. It would need some experimentation to > > figure that out. > > > > Certainly something to bookmark, though. > > > > Thanks, > > scott > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > > > > -- > Cesar D. Rodas > http://www.cesarodas.com/ > Mobile Phone: 595 961 974165 > Phone: 595 21 645590 > [EMAIL PROTECTED] > [EMAIL PROTECTED] > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
On Thu, 23 Aug 2007 14:15:00 -0400, you wrote: >On Thu, 23 Aug 2007 18:58:32 +0200, Kees Nuyt wrote: > >>Hi Chris, > >>On Thu, 23 Aug 2007 12:14:51 -0400, you wrote: > >>>On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: >>> Will INSERT OR REPLACE do what you want? >>> Gerry >>> >>>Regrettably no. When an existing core record is found then it >>>is deleted before the insert. That means that all columns are >>>given new values and not just the ones to be updated. > >>That is exactly what INSERT OR REPLACE does. > >>http://www.sqlite.org/lang_insert.html >>http://www.sqlite.org/lang_conflict.html > > >Sorry for the confusion I introduced. I know the behaviour >of INSERT OR REPLACE is as-described, and that is NOT >what I want. I need to keep the non-updated columns. > >Chris Oops, I obviously misread your statement. Just a suggestion (no time to try it myself): Perhaps a BEFORE INSERT trigger on Core can help, triggered by an INSERT ... SELECT ... FROM UpdateTable? I'm not sure if it would work and how fast it would be. Good luck! -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem opening a new SQLite3 database file
Hello Dennis, Thursday, August 23, 2007, 6:05:09 PM, you wrote: DA> I cannot open a new SQLite3 database file through the command prompt. In DA> the DA> windows "run" window, I type "SQLite3 mydatabase.db3" and I get the DA> following error DA> message: DA> ** DA> "Cannot find the file 'SQLite3' (or one of its components). Make DA> sure the path and filename are correct and that all required libraries are DA> available." DA> *** DA> The only thing I can do is open the SQLite3.exe file and work directly off DA> of it, but I DA> cannot save anything. DA> I have the following files in a folder located on the C drive: DA> 1. sqlite3.exeapplication DA> 2. sqlite3_analyzer application DA> 3. sqlite3.dll DA> 4. tclsqlite3.dll DA> 5. sqlite3.def DA> 6. fts2.def DA> 7. fts2.dll DA> This happens on my two (Win XP, Win 98SE) home computers and my work DA> computer (Win 2000). Can anybody help me figure this out so I can start DA> creating my database? I'd DA> greatly appreciate it. Thanks DA> Dennis Achá DA> _ DA> A new home for Mom, no cleanup required. All starts here. DA> http://www.reallivemoms.com?ocid=TXT_TAGHM=us DA> - DA> To unsubscribe, send email to [EMAIL PROTECTED] DA> - First thing I'd do is open a real command prompt window. CD to the folder that contains the EXE's and try it from the command prompt. That error suggests Windows doesn't know where SQLite3.exe is so, you may want to try C:\Sqlite3.exe or wherever the path is. Personally, I think you need to open a command prompt and work from there and not try doing the "Run" thing till you get the command prompt version working. -- Best regards, Tegmailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem opening a new SQLite3 database file
What folder are you in at the command prompt when you type "SQLite3 mydatabase.db3"? Does it actually contain SQLite3.exe? If not, you need to specify the full path, or navigate to the folder first. Michael Hooker On 23/08/2007 23:05:09, Dennis Achá ([EMAIL PROTECTED]) wrote: I cannot open a new SQLite3 database file through the command prompt. In the windows "run" window, I type "SQLite3 mydatabase.db3" and I get the following error message: ** "Cannot find the file 'SQLite3' (or one of its components). Make sure the path and filename are correct and that all required libraries are available." *** The only thing I can do is open the SQLite3.exe file and work directly off of it, but I cannot save anything. I have the following files in a folder located on the C drive: 1. sqlite3.exeapplication 2. sqlite3_analyzer application 3. sqlite3.dll 4. tclsqlite3.dll 5. sqlite3.def 6. fts2.def 7. fts2.dll This happens on my two (Win XP, Win 98SE) home computers and my work computer (Win 2000). Can anybody help me figure this out so I can start creating my database? I'd greatly appreciate it. Thanks - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Many master journal files
I have an application that attaches 7 databases and then does MANY updates to those databases (maybe a million or two inserts in a day, spread out over maybe 10,000 separate transactions). For some reason the client is seeing many master journal files (-mj) left lying around. I'm asking for logs to see if there are any SQLite errors returned. In the mean time, what would cause those files to not get deleted? I'm quite sure the app isn't stopping while running. One possible twist: I have two database handles to the set of 7 databases. One is used only for SELECTS, while the other does INSERTS and SELECTS (made my wrapper easier to handle). They are both only used from a single thread, and each action is atomic (ie the two handles are never used in an interleaved fashion--one has finalized all outstanding statements before the other is used). This is 3.4.1 on Windows 2003. Thanks for any ideas. Doug - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Problem opening a new SQLite3 database file
I cannot open a new SQLite3 database file through the command prompt. In the windows "run" window, I type "SQLite3 mydatabase.db3" and I get the following error message: ** "Cannot find the file 'SQLite3' (or one of its components). Make sure the path and filename are correct and that all required libraries are available." *** The only thing I can do is open the SQLite3.exe file and work directly off of it, but I cannot save anything. I have the following files in a folder located on the C drive: 1. sqlite3.exeapplication 2. sqlite3_analyzer application 3. sqlite3.dll 4. tclsqlite3.dll 5. sqlite3.def 6. fts2.def 7. fts2.dll This happens on my two (Win XP, Win 98SE) home computers and my work computer (Win 2000). Can anybody help me figure this out so I can start creating my database? I'd greatly appreciate it. Thanks Dennis Achá _ A new home for Mom, no cleanup required. All starts here. http://www.reallivemoms.com?ocid=TXT_TAGHM=us - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FTS2 suggestion
On 23/08/07, Scott Hess <[EMAIL PROTECTED]> wrote: > On 8/20/07, Cesar D. Rodas <[EMAIL PROTECTED]> wrote: > > As I know ( I can be wrong ) SQLite Full Text Search is only match with hole > > words right? It could not be > > And also no FT extension to db ( as far I know) is miss spell tolerant, > > Yes, fts is matching exactly. There is some primitive support for > English stemming using the Porter stemmer, but, honestly, it's not > well-exercised. > > > And > > I've found this Paper that talks about *Using Superimposed Coding Of N-Gram > > Lists For Efficient Inexact Matching* > > http://citeseer.ist.psu.edu/cache/papers/cs/22812/http:zSzzSzwww.novodynamics.comzSztrenklezSzpaperszSzatc92v.pdf/william92using.pdf > > > > I was reading and it is not so hard to implement, but it cost a extra > > storage space, but I think the benefits are more. > > > > Also following this paper could be done a way to match with fragments of > > words... what do you think of it? > > It's an interesting paper, and I must say that anything which involves > Bloom Filters automatically draws my attention :-). Yeah. I am doing some investigations about that, I love that too. And I was watching that with n-grams you get a filter to stop common words, and could be used as a stemming-like algorithm but independent from the language. I was thinking to implement this http://www.mail-archive.com/sqlite-users%40sqlite.org/msg26923.html when I finish up some things. What do you think of it? > While I think spelling-suggestion might be valuable for fts in the > longer term, I'm not very enthusiastic about this particular model. > It seems much more useful in the standard indexing model of building > the index, manually tweaking it, and then doing a ton of queries > against it. fts is really fairly constrained, because many use-cases > are more along the lines of update the index quite a bit, and query it > only a few times. > > Also, I think the concepts in the paper might have very significant > problems handling Unicode, because the bit vectors will get so very > large. I may be wrong, sometimes the overlapping-vector approach can > have surprising relevance depending on the frequency distribution of > the things in the vector. It would need some experimentation to > figure that out. > > Certainly something to bookmark, though. > > Thanks, > scott > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Cesar D. Rodas http://www.cesarodas.com/ Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FTS2 suggestion
On 8/20/07, Cesar D. Rodas <[EMAIL PROTECTED]> wrote: > As I know ( I can be wrong ) SQLite Full Text Search is only match with hole > words right? It could not be > And also no FT extension to db ( as far I know) is miss spell tolerant, Yes, fts is matching exactly. There is some primitive support for English stemming using the Porter stemmer, but, honestly, it's not well-exercised. > And > I've found this Paper that talks about *Using Superimposed Coding Of N-Gram > Lists For Efficient Inexact Matching* http://citeseer.ist.psu.edu/cache/papers/cs/22812/http:zSzzSzwww.novodynamics.comzSztrenklezSzpaperszSzatc92v.pdf/william92using.pdf > > I was reading and it is not so hard to implement, but it cost a extra > storage space, but I think the benefits are more. > > Also following this paper could be done a way to match with fragments of > words... what do you think of it? It's an interesting paper, and I must say that anything which involves Bloom Filters automatically draws my attention :-). While I think spelling-suggestion might be valuable for fts in the longer term, I'm not very enthusiastic about this particular model. It seems much more useful in the standard indexing model of building the index, manually tweaking it, and then doing a ton of queries against it. fts is really fairly constrained, because many use-cases are more along the lines of update the index quite a bit, and query it only a few times. Also, I think the concepts in the paper might have very significant problems handling Unicode, because the bit vectors will get so very large. I may be wrong, sometimes the overlapping-vector approach can have surprising relevance depending on the frequency distribution of the things in the vector. It would need some experimentation to figure that out. Certainly something to bookmark, though. Thanks, scott - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Spatial searches
Scott, Yes, the SELECT is very simple, but slow. I have tens of thousands of records and I need the data very fast (embedded realtime system). Some databases natively support spatial searches, using KD-trees or R-Trees or Quad-trees to improve the search speed. I found an article that explains how to implement a custom-spatial search in SQL 2007: "Using Table Valued Functions in SQL Server 2005 to Implement a Spatial Data Library" But the solution is very specific to SQL server. I thought there might be other tricks that might be common for implementing a fast spatial search in a database that doesn't natively support this feature. Thanks in advance, David -Original Message- From: Scott Baker [mailto:[EMAIL PROTECTED] Sent: Thursday, August 23, 2007 10:52 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Spatial searches David Thieme wrote: > I've been looking for a WinCE embedded database that supports spatial > searches. We are already using SQLite for a very small application; we're > hoping that someone may have some tricks/hints on how to implement fast > searches on spatial data with SQLite. A typical search would be finding > items whose lat/lon falls within a given rectangle (e.g., hotel's closest to > my car). If not, can someone recommend a WinCE database engine that > supports spatial searches? Wouldn't that be something simple like... SELECT Foo WHERE Lat > 1.2 AND LAT < 1.4 AND LONG > 5.6 AND LONG < 6.0? That'll give you a rectangle of values pretty easy. In fact I've implemented that in another database. Pretty easy really. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Spatial searches
On Thu, 23 Aug 2007 10:03:00 -0700, David Thieme wrote: >I've been looking for a WinCE embedded database that supports spatial >searches. We are already using SQLite for a very small application; we're >hoping that someone may have some tricks/hints on how to implement fast >searches on spatial data with SQLite. A typical search would be finding >items whose lat/lon falls within a given rectangle (e.g., hotel's closest to >my car). If not, can someone recommend a WinCE database engine that >supports spatial searches? > >David What is wrong with: select * from SpacialData where (SpacialData.PointLatitude <= CurrentLatitude + LatOffset) and (SpacialData.PointLatitude >= CurrentLatitude - LatOffset) and (SpacialData.PointLongitude <= CurrentLongitude + LonOffset) and (SpacialData.PointLongitude >= CurrentLongitude - LonOffset); Chris - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
On Thu, 23 Aug 2007 18:58:32 +0200, Kees Nuyt wrote: >Hi Chris, >On Thu, 23 Aug 2007 12:14:51 -0400, you wrote: >>On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: >> >>>Chris Peachment wrote: I have a database with more than 200,000 records in the core table. An update table of similar record count contains a proper subset of the core table columns. I'm looking for a fast method of merging the values in the two tables such that : 1. core table columns are updated, and 2. non-existent core records are inserted from the update table. >>>Will INSERT OR REPLACE do what you want? >> >> >>>Gerry >> >> >>Regrettably no. When an existing core record is found then it >>is deleted before the insert. That means that all columns are >>given new values and not just the ones to be updated. >That is exactly what INSERT OR REPLACE does. >http://www.sqlite.org/lang_insert.html >http://www.sqlite.org/lang_conflict.html Sorry for the confusion I introduced. I know the behaviour of INSERT OR REPLACE is as-described, and that is NOT what I want. I need to keep the non-updated columns. Chris - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite Database Browser for creating databases
Hi- We are currently creating databases using this open source tool. According to the documentation, it is using version 3.3.5 of SQLite. I was wondering if there are any problems with creating a database with the tool, but then using the database with an application that is using SQLite 3.4.1? Would the format of the database be compatible? Thanks, Mark
Re: [sqlite] Spatial searches
David Thieme wrote: > I've been looking for a WinCE embedded database that supports spatial > searches. We are already using SQLite for a very small application; we're > hoping that someone may have some tricks/hints on how to implement fast > searches on spatial data with SQLite. A typical search would be finding > items whose lat/lon falls within a given rectangle (e.g., hotel's closest to > my car). If not, can someone recommend a WinCE database engine that > supports spatial searches? Wouldn't that be something simple like... SELECT Foo WHERE Lat > 1.2 AND LAT < 1.4 AND LONG > 5.6 AND LONG < 6.0? That'll give you a rectangle of values pretty easy. In fact I've implemented that in another database. Pretty easy really. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
Chris Peachment wrote: On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: Chris Peachment wrote: I have a database with more than 200,000 records in the core table. An update table of similar record count contains a proper subset of the core table columns. I'm looking for a fast method of merging the values in the two tables such that : 1. core table columns are updated, and 2. non-existent core records are inserted from the update table. Will INSERT OR REPLACE do what you want? Gerry Regrettably no. When an existing core record is found then it is deleted before the insert. That means that all columns are given new values and not just the ones to be updated. Chris You do have to specify all the columns, but you can set the unchanging columns to what they already are. I don't remember the exact syntax, but it can be done. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
Hi Chris, On Thu, 23 Aug 2007 12:14:51 -0400, you wrote: >On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: > >>Chris Peachment wrote: >>> I have a database with more than 200,000 records in the >>> core table. An update table of similar record count contains >>> a proper subset of the core table columns. >>> >>> I'm looking for a fast method of merging the values in the >>> two tables such that : >>> >>> 1. core table columns are updated, and >>> 2. non-existent core records are inserted from the update table. >>> >>Will INSERT OR REPLACE do what you want? > > >>Gerry > > >Regrettably no. When an existing core record is found then it >is deleted before the insert. That means that all columns are >given new values and not just the ones to be updated. That is exactly what INSERT OR REPLACE does. http://www.sqlite.org/lang_insert.html http://www.sqlite.org/lang_conflict.html >Chris -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Spatial searches
I've been looking for a WinCE embedded database that supports spatial searches. We are already using SQLite for a very small application; we're hoping that someone may have some tricks/hints on how to implement fast searches on spatial data with SQLite. A typical search would be finding items whose lat/lon falls within a given rectangle (e.g., hotel's closest to my car). If not, can someone recommend a WinCE database engine that supports spatial searches? David
[sqlite] Re: Update Columns in One Table Using Values From Another Table
Chris Peachment <[EMAIL PROTECTED]> wrote: I have a database with more than 200,000 records in the core table. An update table of similar record count contains a proper subset of the core table columns. I'm looking for a fast method of merging the values in the two tables such that : 1. core table columns are updated, and 2. non-existent core records are inserted from the update table. It is not necessary that records missing from the update table cause deletes in the core table. Try this (assuming fieldU exists in both tables and fieldC is only in core table, and the rows are matched up by the field named "id"): insert or replace into coreTable(fieldU, fieldC) select u.id, u.fieldU, c.fieldC from updateTable u left join coreTable c on (u.id = c.id); This requires that coreTable have a uniqueness constraint on id field, e.g. having it as a primary key. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: >Chris Peachment wrote: >> I have a database with more than 200,000 records in the >> core table. An update table of similar record count contains >> a proper subset of the core table columns. >> >> I'm looking for a fast method of merging the values in the >> two tables such that : >> >> 1. core table columns are updated, and >> 2. non-existent core records are inserted from the update table. >> >Will INSERT OR REPLACE do what you want? >Gerry Regrettably no. When an existing core record is found then it is deleted before the insert. That means that all columns are given new values and not just the ones to be updated. Chris - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
Chris Peachment wrote: I have a database with more than 200,000 records in the core table. An update table of similar record count contains a proper subset of the core table columns. I'm looking for a fast method of merging the values in the two tables such that : 1. core table columns are updated, and 2. non-existent core records are inserted from the update table. Will INSERT OR REPLACE do what you want? Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Update Columns in One Table Using Values From Another Table
I have a database with more than 200,000 records in the core table. An update table of similar record count contains a proper subset of the core table columns. I'm looking for a fast method of merging the values in the two tables such that : 1. core table columns are updated, and 2. non-existent core records are inserted from the update table. It is not necessary that records missing from the update table cause deletes in the core table. Case 1 above needs something like: update Core set B = (select UpdateTable.B from UpdateTable as U where U.A = Core.A) set C = (select UpdateTable.C from UpdateTable as U where U.A = Core.A) ... where exists (select U.A from UpdateTable as U where U.A = Core.A); The table schema include indexes on the A columns, but this operations takes more than 10 minutes (and still not finished) when using sqlite3 from the command line. This is too long for use with PHP and a web-browser since the server kills the process after 30 seconds. Can anyone help? Chris Peachment - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Build in M$ Visual Studio 250+ warnings
I use this framework to build sqlite on VS2005.SP1:
// \file import_sqlite.cpp
// \brief Import the SQLITE database.
//
#pragma warning(push, 0)
#pragma warning(disable: 4701)
#pragma runtime_checks("", off)
// enable multi-thread mode.
#define THREADSAFE 1
// optimize for performance by using large block sizes by default.
#define SQLITE_DEFAULT_PAGE_SIZE 32768
#include "../../src/sqlite/sqlite3.c"
#pragma warning(pop)
I choose to assume that sqlite is well-tested and that any warnings about
portability, 64-bit cleanliness, loss of data precision, structure packing,
conversion operations, and runtime conversions that exceed the size of the
destination argument (by truncation) are immaterial to correct operation and
are safe to ignore.
Since my build environment insists on "treat warnings as errors", I use this
framework to successful use it in my applications.
Hope that helps.
-- andy
On 8/23/07, Cory Nelson <[EMAIL PROTECTED]> wrote:
>
> On 8/23/07, Andre du Plessis <[EMAIL PROTECTED]> wrote:
> > Just wanted to check with you guys that my build is actually stable in
> > Visual Studio 2005, I get about 250+ warnings when building SQLite I can
> > come back to you with more details if this is not correct, just want to
> > make sure that's seems correct, ive been getting the occasional weird
> > SQLite error, I was just wondering if my build is not a bit buggy, I
> > have added the THREADSAFE in the defines though so I don't think it is,
> > just don't want to corrupt my db's.
> >
>
> The warnings are normal - the author thinks they are spurious.
>
> --
> Cory Nelson
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
Re: [sqlite] _ROWID_ internal column, versus explicit INTEGER PRIMARY KEY with VACUUM
"Andrew Finkenstadt" <[EMAIL PROTECTED]> wrote: > I realize that FTS1/2 has this slight flaw with the text indexes recording > the _rowid_ of a table, in the expectation that a rowid was permanent. That > would have caught me unawares, as in Oracle a ROWID is permanent... even if > the row has migrated, there's a migrate record at the place where the row > used to be. > > Does the same renumbering during VACUUM (auto or otherwise) apply to the > explicitly identified integer primary key column when it is named "id", or > named "pkey"? No. INTEGER PRIMARY KEY columns keep the same value through a vacuum. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Build in M$ Visual Studio 250+ warnings
On 8/23/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: > Just wanted to check with you guys that my build is actually stable in > Visual Studio 2005, I get about 250+ warnings when building SQLite I can > come back to you with more details if this is not correct, just want to > make sure that's seems correct, ive been getting the occasional weird > SQLite error, I was just wondering if my build is not a bit buggy, I > have added the THREADSAFE in the defines though so I don't think it is, > just don't want to corrupt my db's. > The warnings are normal - the author thinks they are spurious. -- Cory Nelson - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite Build in M$ Visual Studio 250+ warnings
Just wanted to check with you guys that my build is actually stable in Visual Studio 2005, I get about 250+ warnings when building SQLite I can come back to you with more details if this is not correct, just want to make sure that's seems correct, ive been getting the occasional weird SQLite error, I was just wondering if my build is not a bit buggy, I have added the THREADSAFE in the defines though so I don't think it is, just don't want to corrupt my db's. Thanks.
Re: [sqlite] Sparse matrix
At 1:54 PM +1000 8/23/07, T wrote: Hi Darren, It seems to me that you have a flawed design. Displaying sparse like that should be a function of your application display code, not the database I had to chuckle that when I asked "How do I use this to do that", your solution was "you shouldn't have that and you should do it with something other than this" ;-) Not really helpful, but good for a chuckle. Fortunately Simon's replies helped me out. Hopefully my followup posting gives a better example. Tom, my answer was quite appropriate, considering your example, and I stand by it. It is very common that people asking for help are demonstrating that they are heading in a bad direction and want help in dealing with the result of that; I think it is more helpful to assist with the bigger picture than to necessarily support a bad direction. Or put another way, often when people want to solve problem A, then rather than asking for help in solving problem A, perhaps with some examples of what they tried, they assume that a certain way to go is best, and only ask for help in going that way, that is problem B. At 1:53 PM +1000 8/23/07, T wrote: But with counts of repetitions, like this: Day Room Subject TeacherPeriod MondayA1 1 English 1 Ng 1 1 A2 2 Maths 1 Peters 2 2 Computing 1 3 H1 1 Sport 1 Kent 1 4 Tuesday A2 1 Maths 1 Peters 1 1 A1 3 History 1 Ng 3 2 English 1 3 History 1 4 Wednesday A2 1 Maths 1 Peters 1 1 H1 1 Sport 1 Kent 1 2 S1 2 Science 2 Who1 3 Smith 1 4 The "1" counts could even appear as null/blank, that would be fine. Now your newer example does demonstrate a more reasonable problem to solve. By the looks of it you want to display results in a prettier or easier to read grid by having blanks rather than repeated field values; perhaps you are rendering an HTML table, and the numerical count you want is to provide a "rowspan" attribute value. If the DBMS can do this, then your HTML generator would be extremely simple. Or you aren't making HTML, but have similar issues. That said, if what you want is even possible with SQL, then it would probably be rather verbose SQL, involving multiple joins and group-bys and order-bys etc, and I suspect that your total code size would be smaller if you used appropriate logic in your application to blank the result fields rather than SQL. So I still recommend you do what you want in your presentation layer rather than in the data layer. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
