[sqlite] Why FTS3 has the limitations it does

The severe limitations on FTS3 seemed odd to me, but I figured I could live with them. Then I starting finding that various queries were giving strange "out of context" errors with the MATCH operator, even though I was following all the documented rules. As a result I started looking deeply into

Re: [sqlite] regular expression search

If you need more advanced matching (I.E. full regex, beyond what GLOB can do) you could implement a custom function. A regex search is always going to have to resort to a full table scan anyway, so it won't hurt performance any. John -Original Message- From:

Re: [sqlite] Exception writing to database from multiple processes

I can empathize with this problem, having just worked through this recently. The bottom line is if you need concurrency, you're going to have to structure your code appropriately. Here are some things I found helpful: 1. ENCAPSULATE! You'll want to encapsulate your handling of queries so that

Re: [sqlite] Creating a spatial index for a large number of points- sorry for the text formating

I doubt SQLite is the right tool for this job, for a number of reasons. First, if the data is as simple as you say, you are probably better off writing your logic as straight C, rather than SQL. SQLite is VERY fast, but there is still an incredible amount of overhead in executing a query, in

Re: [sqlite] Why FTS3 has the limitations it does

users-boun...@sqlite.org] On Behalf Of P Kishor Sent: Friday, October 16, 2009 4:23 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Why FTS3 has the limitations it does On Fri, Oct 16, 2009 at 3:12 PM, Scott Hess <sh...@google.com> wrote: > On Wed, Oct 14, 2009 at 11:3

Re: [sqlite] Infinity

SQLite stores the data however you give it. I'm not aware of any documentation requiring that 9e999 be considered infinity, nor any requiring that the command line treat invalid numbers as null. Most likely, treating NaN as null is simply a way for the command line to behave reasonably in an

Re: [sqlite] Why FTS3 has the limitations it does

alf Of Scott Hess Sent: Monday, October 19, 2009 12:51 PM To: General Discussion of SQLite Database Cc: punk...@eidesis.org Subject: Re: [sqlite] Why FTS3 has the limitations it does On Sat, Oct 17, 2009 at 1:25 PM, John Crenshaw <johncrens...@priacta.com> wrote: > Agreed, HUGE thanks for FT

Re: [sqlite] Infinity

ile ( sqlite3_step(select_tl) == SQLITE_ROW){ printf("%e",sqlite3_column_double(select_tl,0)); printf("\n"); } if (rc != SQLITE_DONE) exit(rc); sqlite3_finalize(select_tl); sqlite3_close(db); printf("exit normally\n"); } On Sun, Oct 18, 2009 at 5:5

Re: [sqlite] Creating a spatial index for a large number of points-sorry for the text formating

t, etc.), I need to find something else to build my app in top of it. The other candidates I considered were HD5, and ... well I heard firebird can be used in serverless mode. Any other ideas are welcome :) -IC - Original Message From: John Crenshaw <johncrens...@priacta.com> To: Gene

Re: [sqlite] Infinity

Sorry, I think I gave you slightly buggy instructions. I just realized that max() should be the max true value capable of being stored, which should be less than the infinity value. std::numeric_limits provides another function named infinity() for getting positive infinity. I believe this value

Re: [sqlite] index for a group by

> Nothing in this process can be sped up. Actually, that isn't entirely true. While it always requires a full data scan, Some things can make the indexing part of the process faster. Since indexing is done basically using a comparative sort, anything that would speed up the sort, will speed up

Re: [sqlite] PRAGMA scope

database = the physical file itself database connection = a specific reference to the database, obtained using sqlite3_open page_size has to be set before the database is created (though I'm NOT entirely sure how you would execute the pragma before creating the database, since executing the

Re: [sqlite] PRAGMA scope

, 2009 2:37 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] PRAGMA scope On Tue, Oct 20, 2009 at 1:33 PM, John Crenshaw <johncrens...@priacta.com> wrote: > database = the physical file itself > database connection = a specific reference to the database, obta

Re: [sqlite] commit time

Sounds like a great candidate for shared cache with PRAGMA read_uncommitted = true. If other threads may also need a write lock on that table, you should handle SQLITE_LOCKED by incrementing a waiter count and calling sqlite3_unlock_notify. The thread doing the inserting can check to see if

Re: [sqlite] index for a group by

ss several transactions. Pavel On Tue, Oct 20, 2009 at 2:15 PM, John Crenshaw <johncrens...@priacta.com> wrote: >> Nothing in this process can be sped up. > > Actually, that isn't entirely true. While it always requires a full data > scan, Some things can make the indexing part

Re: [sqlite] Problem about write data into the DB

It is likely that the file can't be opened for a very predictable reason. For example, perhaps the specified path doesn't exist. (A common variation of this would be a hard coded string with single backslashes, most languages require you to escape backslashes in strings.) Perhaps the file is read

Re: [sqlite] commit time

nsaction opened by a connection in read-uncommitted mode can neither block nor be blocked by any other connection." this is precisely what i need. thanks very much. From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On Behalf Of John

Re: [sqlite] commit time

where_." where is this described? From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw [johncrens...@priacta.com] Sent: Tuesday, October 20, 2009 7:18 PM To: General Discussion of SQLite Database Subject: Re:

Re: [sqlite] Slow SELECTs in application

Someone correct me if I'm wrong, but I don't think that UNIQUE (id_norm,word,wform) is going to have the desired result. Won't that create a single tricolumn unique index? I suspect this table needs a separate index for each. Just put the UNIQUE keyword (with no arguments) after the type on each

Re: [sqlite] Slow SELECTs in application

It isn't just speed. That is probably the cause of the insert error. A PRIMARY KEY column is implied UNIQUE and NOT NULL but the insert doesn't specify a value for id. Since it isn't aliased to rowid (and therefore doesn't autoincrement) it would raise an error. John -Original Message-

Re: [sqlite] commit time

: Re: [sqlite] commit time if thread 1 opens a read cursor in read uncommitted mode it can block a write lock? i thought the read happens w/o a lock? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw Sent

Re: [sqlite] Inner Join Performance Issue

Try to EXPLAIN the query and verify that the index is actually used. There are a lot of reasons why this query would probably NOT be using the index. John -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Sent: Wednesday,

Re: [sqlite] commit time

t; From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org > ] On Behalf Of John Crenshaw > Sent: Wednesday, October 21, 2009 12:03 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] commit time > > Good, a single write thread

Re: [sqlite] commit time

] On Behalf Of Dan Kennedy Sent: Thursday, October 22, 2009 1:27 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] commit time On Oct 22, 2009, at 11:37 AM, John Crenshaw wrote: > An open cursor will block. I've watched it. It was a major problem, > and > I spent

Re: [sqlite] Inner Join Performance Issue

achricht- > Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] Im Auftrag von John Crenshaw > Gesendet: Donnerstag, 22. Oktober 2009 05:53 > An: General Discussion of SQLite Database > Betreff: Re: [sqlite] Inner Join Performance Issue > > Try to

Re: [sqlite] Grammar of "X is Y"

ROFL -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Griggs, Donald Sent: Tuesday, October 27, 2009 12:31 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Grammar of "X is Y" Importance: Low Depends on what

Re: [sqlite] Slow SELECTs in application

Yeah, you don't just need an index on all columns. You need the right indexes with the right combination of columns. Also, use prepared statements. If you don't use prepared statements SQLite will have to recompile those queries at every execution, which can take some time. John -Original

Re: [sqlite] Idea for improving page cache

Supposing that the reduced cache misses are worth it, I think it would be better to simply allocate the nodes from a pool. Allocating from a pool maximizes locality and prevents the overhead involved in each allocation. Since the nodes have static size, pool allocation is easy. This doesn't

Re: [sqlite] Idea for improving page cache

"advantage" kind of depends. ULL is more specialized. You gain some benefit, but also lose some as well. For example, consider what is involved in doing a sorted insert into an ULL. On the other hand, you can get all of the same locality benefit with a pool allocation scheme. You don't reduce

Re: [sqlite] Grammar of "X is Y"

> There's nothing wrong with that. Not unless style counts for something. X is Y looks far too much like X as Y for my taste. I'd rather do a little extra typing to have clear logic than to have clearly unclear code like that. My first thought when I saw this was "doesn't he mean AS?" If I saw

Re: [sqlite] Idea for improving page cache

27, 2009 at 04:28:11PM -0400, John Crenshaw wrote: > "advantage" kind of depends. ULL is more specialized. You gain some > benefit, but also lose some as well. For example, consider what is > involved in doing a sorted insert into an ULL. On the other hand, you > can get

Re: [sqlite] Grammar of "X is Y"

9 8:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Grammar of "X is Y" John Crenshaw wrote: >> There's nothing wrong with that. > > Not unless style counts for something. X is Y looks far too much like X > as Y for my taste. I'd rather do a litt

Re: [sqlite] Grammar of "X is Y"

Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Darren Duncan Sent: Wednesday, October 28, 2009 12:09 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Grammar of "X is Y" John Crenshaw wrote: > Yeah, I tend to ag

Re: [sqlite] Late data typing. Am I missing something?

SQLite's data typing means it can support any and all field types supported in any other SQL database. That's a big deal. For the most part, the proper method for accessing any given data is going to be simple and universal. Homegrown routines will only happen if people have specific homegrown

Re: [sqlite] Late data typing. Am I missing something?

Yeah, the code is fortunately all there, so once you know what you're looking for it is easy to copy out, but it should have been exposed in the API. John -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of O'Neill, Owen Sent:

Re: [sqlite] shared cache mode and 'LOCKED'

It appears to be up to date. John -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of O'Neill, Owen Sent: Wednesday, October 28, 2009 1:45 PM To: General Discussion of SQLite Database Subject: [sqlite] shared cache mode and

Re: [sqlite] SELECT * vs SELECT columns ?

You could use EXPLAIN to see if there is a different query plan, but I'd bet there isn't. * will generally be slower, just because you usually won't need EVERY column. If you can specify only certain columns, that will save you some time. John -Original Message- From:

Re: [sqlite] shared cache mode and 'LOCKED'

I don't know. Elsewhere it says you really shouldn't use the same connection in multiple threads. I use a different connection in each thread. With the shared cache, this results in very little overhead, so I'm unsure why you would need to do this the "not recommended" way. The contention between

Re: [sqlite] shared cache mode and 'LOCKED'

-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw Sent: Wednesday, October 28, 2009 12:38 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] shared cache mode and 'LOCKED' I don't know. Elsewhere it says you really shouldn't use the same

Re: [sqlite] How to input a double num?

Bad plan. Use prepared statements and bind. Otherwise you're going to create SQL injection vulnerabilities. Prepared statements are faster and easier to read anyway. John -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of liubin

Re: [sqlite] Late data typing. Am I missing something?

Subject: Re: [sqlite] Late data typing. Am I missing something? John Crenshaw wrote: > SQLite has plenty of date editing routines. Dates are stored in a double > as a Julian date. Well, that's one way of doing it. I store them as strings because I wanted a human-readable format. The do

Re: [sqlite] shared cache mode and 'LOCKED'

f conn2 writes to tab1 then conn1 will get SQLITE_BUSY but conn3 will get SQLITE_LOCKED (if trying to write to tab1; will succeed if trying to write to tab2). correct? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw

Re: [sqlite] Late data typing. Am I missing something?

the documentation. John -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Bishop Sent: Thursday, October 29, 2009 3:15 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Late data typing. Am I missing someth

Re: [sqlite] Late data typing. Am I missing something?

>>> *if* you know that the number *is* a date. >>> >> >> If the column has a type of timestamp, it should be safe to always >> assume that it IS a date. >sqlite> CREATE TABLE t1 (StartTime TIMESTAMP, Duration REAL); >sqlite> CREATE TABLE t2 AS SELECT StartTime, StartTime + Duration /

Re: [sqlite] Some clarification needed about Unicode

1. My understanding is that it will convert between UTF-8 and UTF-16 when you use these functions. I haven't tested this though, so you might try a simple test app just to make sure. 2. UTF-8 is NOT the same as ASCII for values greater than 127. Similarly, UTF-16 is NOT the same as UCS-2 (the

Re: [sqlite] Some clarification needed about Unicode

Yes, they are obscure, but that is the whole point of supporting Unicode. Because users want to enter obscure characters into your application ;) My main point is that you can't take the UTF-16 string and safely supply it to APIs which want UCS-2 encoded text, such as Win32 APIs (including things

Re: [sqlite] SELECT * vs SELECT columns ?

> If the schema changes, a listing of every column can be > invalidated, but the asterisk cannot. This is only partly true. At some point, the code is going to need to grab the individual fields, and that is the point where the asterisk fails to serve you well. If new fields are added to the

Re: [sqlite] Some clarification needed about Unicode

> there must exist zillions [working] wrappers to VC++. You would think. In fact, there are only a few, and most are not very good. I used the wrapper at Code Project as a base, then added handling for SQLITE_LOCKED, a date class, better blob handling, transaction support, and other useful

Re: [sqlite] Some clarification needed about Unicode

> Certainly not! > > Win32 supports full Unicode 5.1 in UTF-16 for a long time. I double checked this just to be sure, and now I'm not. Here are the facts: 1. When Microsoft describes "Unicode" and "wide characters" the description always matches UCS-2 (they continually mention that "Unicode"

Re: [sqlite] Late data typing. Am I missing something?

> > I must agree with other posters that the lack of an exposed timestamp > > type does feel like something of a gap. > > Given the rather large number of reasonable ways to represent > timestamps, I have to disagree. I'd rather have the freedom to use > whichever representation is best for my

Re: [sqlite] Some clarification needed about Unicode

--- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Thursday, October 29, 2009 5:08 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Some clarification needed about Unicode John Crenshaw <johncrens...@priacta.com> wrote:

Re: [sqlite] Some clarification needed about Unicode

> Don't worry: we're all confused with MS wording! For what I understand > having also myself tried to sort out the question, is that there is a > line drawn: before XP unicode support included was nothing else than > UCS-2 (W2K). Xp and post-XP system include Unicode 5.1 and use UTF-16 >

Re: [sqlite] Late data typing. Am I missing something?

> Um...how do I go to the page that describes the date-time information > without just 'knowing' it's there? For example, there seems to be no > path to http://www.sqlite.org/lang_datefunc.html from > http://.sqlite.org. I used Google personally. "sqlite date functions" John

Re: [sqlite] How to input a double num?

> He's probably measuring the number of atoms in a city or something. LOL. Actually my bet is that field of the project has more to do with number theory. That's the only likely way to get a number that large with such high precision. John ___

Re: [sqlite] Some clarification needed about Unicode

> > No, I mean which encoding. You can't give a UTF-16 string to an API > > that only knows how to handle UCS-2 encoded data > > Well, most of the time, you can. Only in rare cases do you need to treat > surrogate pairs in special way. One such case, relevant to this discussion, > is converting

Re: [sqlite] feature proposal - strong but dynamic typing

Been watching this discussion go back and forth, and I'd like to weigh in. I'm generally a HUGE fan of strong typing, but this doesn't do it for me. To me, strongly typed means a compiler catches my type mismatches before the app goes out the door. In this case though, no matter what you do, a

Re: [sqlite] How to input a double num?

> May I use sqlite3_bind_double() and sqlite3_prepare_v2() to solve the > problem. That won't fix it. Your number is too large to fit in any native data type. Even the plain math inside your own program won't work right, because the precision of the number is limited at the C level, not the

Re: [sqlite] Idea for improving page cache

> Just for the sake of discussion I've attached a performance > graph for various C++ data structures plus the Unrolled LL. > The tests where run on a dell vostro 1400 laptop. As you can > see the graphs show the ULL to be quite efficient for > insert/delete from the front/back of the list. I

Re: [sqlite] Some clarification needed about Unicode

> http://codesnipers.com/?q=utf-8-versus-windows-unicode > > The author asset that .NET is the only platform that offer full UTF-16 > support in the Windows API. The author is half mistaken, as was I. Michael Kaplan and Raymond Chen (big MS names many will recognize) clarified this. For Win2k,

Re: [sqlite] feature proposal - strong but dynamic typing

> I believe I understand Darren's point (whether or not I care for them > is another story). Yes, you've understood Darren for the most part, but clearly don't understand the objections. > On Fri, Oct 30, 2009 at 2:22 AM, Roger Binns wrote: > > -BEGIN PGP SIGNED

Re: [sqlite] Table within a table??

> Jay A. Kreibich wrote: >> On Tue, Nov 03, 2009 at 02:51:23AM -0800, CityDev scratched on the wall: >>> That just seems so contrary to the original idea of the relational >>> model that you shouldn't have any data whose meaning is not defined >>> by data (in the case of an array you need to

Re: [sqlite] User-defined infix functions

In your specific example you could simply define a custom "LIKE" function, and LIKE could become Unicode aware without any goofy new operators. John -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jean-Christophe Deschamps

Re: [sqlite] Local data structures vs sqlite

SQLite will have some overhead, no questions asked. If you aren't using indexes, a raw loop and raw data is going to be faster. A raw file will also always be smaller. That said, Dr. Hipp is right. If you are STORING the data, you should strongly consider using SQLite rather than proprietary file

Re: [sqlite] Local data structures vs sqlite

>>I wouldn't use SQLite for most in memory data that never needs to be >>stored on disk > >Even this depends entirely on your context. Of course if only a simple >lookup in a table is more or less all you ever need, there is little >point in SQLite. But if or when your requirements get more