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 wh
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: sqlite-users-boun...@sqlite.or
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 yo
Yes, sqlite CAN deadlock. It is a weakness of the locking model combined
with a weakness in the transaction model. The big problem is that all
write locks are elevatable. The possibility for deadlock can be
eliminated if only one write lock at a time is elevatable (all others
must be known to not e
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 any
mailto:sqlite-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 wrote:
> On Wed, Oct 14, 2009 at 11:35 PM, Joh
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 otherw
-boun...@sqlite.org] On Behalf 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
wrote:
> Agreed, HUGE thanks for FT
elect_tl,NULL);
if (rc != SQLITE_OK)
exit(rc);
while ( 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("exi
hink journal, think page management, 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 Cren
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 wi
> 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 the
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 pragma
, 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 wrote:
> database = the physical file itself
> database connection = a specific reference to the database, obtained
> using sqlite3_open
>
> page
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 anybod
an one split
creating of index across several transactions.
Pavel
On Tue, Oct 20, 2009 at 2:15 PM, John Crenshaw
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 par
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 o
ion 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_."
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:
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 co
Sqlite doesn't delete data from the database the way you expect. It marks space
as deleted and overwrites it later as needed. The "other records" are probably
just deleted blocks.
Unless the VACUUM is deleting the 1 record, it hasn't reduced the file size too
much.
If the database is ACTUALLY
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-
Fr
Discussion of SQLite Database
Subject: Re: [sqlite] commit time
very good. i don't anticipate multiple writers so this should be pretty
simple.
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw
Sent: Wednesday, Octob
f SQLite Database
Subject: 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 Beh
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, O
Message-
> 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 wri
e.org] 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
>
ks
Ralf
> -Ursprüngliche Nachricht-
> 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 P
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 y
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 Me
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 save
"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 th
> 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 som
Subject: Re: [sqlite] Idea for improving page cache
On Tue, Oct 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
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
lly worth it to me.
John
-Original 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
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 need
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: Wed
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 'LOCKE
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: sqlite-users-boun...
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
--
From: sqlite-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
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
n of SQLite Database
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-re
2 and conn3 will get SQLITE_BUSY, yes? if 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-bo
urse. They should have been mentioned in 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: [sqlit
>>> *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 /
>86400.0
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 wide
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
> 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 schem
> 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 enhance
> 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" str
> > 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 n
says nothing.
John
-Original Message-
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
> 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
> enc
> 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
> 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
___
sqlite-us
> > 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 U
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 mis
> 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
SQLite
> 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 belei
> 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, o
> 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 MESSAGE-
> > Hash: SHA1
> 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 under
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
Sen
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 f
>>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 co
67 matches
Mail list logo