Re: [sqlite] Unicode support

[Beau Wilkinson]

A few minutes ago I wrote that:

>I think that as a general rule, the "combining" accents should be disregared 
>during collation.
>
> etc.

I just read that "collation" page from Unicode.org and it seems to be 
completely at odds with what I suggested, e.g. in its insistence that some 
sequences of code points are "canonically equivalent."

In light of this fact, I do not see how Unicode can ever really be considered 
"collated." And it follows that it cannot be reversed. At least, this is the 
case if one follows the advice at Unicode.org.

The "collation" that Unicode.org seems to suggest is basically the invention of 
some academics. It does not seem to correspond to any human alphabet. Please, 
please correct me if I am wrong on this.

I have never been one of those to just ignore Unicode. But I am starting to see 
that it does not really work so well in the real world once one leaves the 
realm of  "ASCII-with-zeroes-on-top."


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Igor Tandetnik [itandet...@mvps.org]
Sent: Tuesday, November 17, 2009 1:01 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unicode support

Simon Slavin  wrote:
> On 17 Nov 2009, at 6:37pm, Igor Tandetnik wrote:
>
>> Simon Slavin  wrote:
>>> First split the string into characters, then reassemble them in
>>> reverse order.
>>
>> The problem is, in Unicode it's not quite clear what constitutes a
>> "character". Are we talking about codepoints, sort elements,
>> graphemes? Depending on the application, either definition might
>> make sense.
>
> I agree about the problem, but sort elements is the obvious answer in
> this case.

This would mean that the result of the hypothetical flip() function would be 
locale-dependent. E.g. in Spanish Traditional sort, a combination 'ch' sorts as 
if it were a single letter between 'c' and 'd', forming a single sort element 
(a so-called contraction). So should 'a ch b' reverse to 'b ch a' under Spanish 
Traditional sort, and to 'b hc a' otherwise? Would you pass a desired locale as 
a parameter to flip(), in order to achieve that?

Igor Tandetnik

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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unicode support

[Beau Wilkinson]

>> On 17 Nov 2009, at 5:52pm, Igor Tandetnik wrote:
>>
>>> But for your goals, it has to be sortable, right? In a proper
>>> Unicode collation, U+0041 U+0301 would behave quite differently from
>>> U+0301 U+0041. Consider "A ' E" (where ' stands for a combining
>>> acute accent). In most locales, this would sort between AE and BE.
>>> Now, if we reverse it naively, we'll end up with "E ' A", with the
>>> accent now attached to E and not A. The result would sort between EA
>>> and FA, rather than between EA and EB as you would probably want.
>>

I think that as a general rule, the "combining" accents should be disregared 
during collation.

For example: if a string contains the letter "a" plus a "combining acute 
accent," to me that seems like a hint that what we have is basically a letter 
"a," not a distinct letter with its own place in the collation sequence. This 
should be collated as an "a" that just happens to be accented, for whatever 
reason.

In Spanish, for example, a diaresis is sometimes placed over the letter "U." 
This indicates that the preceding consonant is hard. It does not make the "U" 
into a different letter, or signficantly affect the collation sequence. (At 
most, it is a tie-breaker between two otherwise identical words.)

So, I think the Spanish diaresis thus represents a legitimate use of the 
Uniciode "combining diaresis." In fact, I would submit that encoding Spanish's 
"U with diaresis" using code point U+00FC is just wrong, in the same way as 
coding letter "O" as ASCII 0x30 (zero) is wrong. We do not need to worry about 
cleaning up such a mistake in our collation code.

In German, and the Scandinavian languages, the opposite is true. Putting a 
diaresis over a letter makes a new letter, which collates differently. 
"Combining accents" code points are not appropriate in these languages and 
their use should not be supported by a collation algorithm. Rather, these 
letters should be encoded using single code points.

I think a better approach (to the design of Unicode) would have been for 
Spanish and German (for instance) to share absolutely nothing in the encoding 
standards. Each language ought to have its own little span of letters, 
immortalized into the standard in correct order-of-collation, with no sharing 
of "code points," "characters," or anything else.

Unicode screws this up, as it does with so many things, and this is a big 
reason why it's widely reviled (or, ignored) by many programmers. This is 
editorial commentary, but I do not necessarily think it is irrelevant. I get 
the feeling that something better than Unicode must be brewing somewhere.

Of course, sometimes bad standards have a life of their own, because they give 
us license to refuse to implement things and still look smart in so refusing. I 
suggest that this a very detrimental pattern, though.


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Igor Tandetnik [itandet...@mvps.org]
Sent: Tuesday, November 17, 2009 1:01 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unicode support

Simon Slavin  wrote:
> On 17 Nov 2009, at 6:37pm, Igor Tandetnik wrote:
>
>> Simon Slavin  wrote:
>>> First split the string into characters, then reassemble them in
>>> reverse order.
>>
>> The problem is, in Unicode it's not quite clear what constitutes a
>> "character". Are we talking about codepoints, sort elements,
>> graphemes? Depending on the application, either definition might
>> make sense.
>
> I agree about the problem, but sort elements is the obvious answer in
> this case.

This would mean that the result of the hypothetical flip() function would be 
locale-dependent. E.g. in Spanish Traditional sort, a combination 'ch' sorts as 
if it were a single letter between 'c' and 'd', forming a single sort element 
(a so-called contraction). So should 'a ch b' reverse to 'b ch a' under Spanish 
Traditional sort, and to 'b hc a' otherwise? Would you pass a desired locale as 
a parameter to flip(), in order to achieve that?

Igor Tandetnik

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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Verbosity of inner join queries

[Beau Wilkinson]

>My current query looks something like this:
>  SELECT tblA.someValue from tblB inner join tblA on
> tblB.fkcol = tblA.col1 where tblB.col1 = ?
>
>   Couldn't it just be:
>  SELECT fkcol.someValue from tblB where col1  = ?

I think you can, under standard SQL, write the following equivalent query:

SELECT tblA.someValue from tblB, tblA where tblB.fkcol = tblA.col1 and 
tblB.col1 = ?

I think this will be equivalent in terms of result, per the standard. I guess 
it's possible that different EXPLAIN PLANS will result from the two queries. 
But I wrote queries this way for years, and considered the INNER JOIN syntax to 
be wordy and academic-looking.

Looking at your proposed query, I guess that a SQL engine could probably infer 
all of the things it would need to. But the way the column in your second 
select list is qualifed (fkcol.someValue) is odd-looking to me, as is the 
omission of a queried table from the WHERE clause. Also, I think the inferences 
you describe rely on detailed metadata (FK constraints), which are not always 
present. These are the main obstacles I perceive to what you proposed.


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Shaun Seckman (Firaxis) [shaun.seck...@firaxis.com]
Sent: Monday, November 16, 2009 11:37 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Verbosity of inner join queries

Hey guys,

This may be more of a question about the SQL language
itself rather than SQLite.  I'm finding inner join statements to be more
verbose than they should be and I'm curious if there is any sort of
syntax sugar that can be done to shorten them.



An Example:

CREATE TABLE tblA( col1 integer, someValue integer);

CREATE TABLE tblB( col1 integer, fkcol integer, foreign
key (col2) references tblA(col1));



My current query looks something like this:

SELECT tblA.someValue from tblB inner join tblA on
tblB.fkcol = tblA.col1 where tblB.col1 = ?



Couldn't it just be:

SELECT fkcol.someValue from tblB where col1  = ?



The reasoning here is that SQL knows the schema of tblB
and knows exactly where the FK is pointing to.  If  fkcol was pointing
to an invalid row or was null, fkcol.someValue could be null.  It seems
like this would just be syntax sugar and both should  generate the same
query plan.  The other thing I like about this syntax is that it would
have to throw an error if fkcol wasn't a true FK.  In this example, the
inner join line was fairly short, but it gets pretty intense when you
have multiple inner joins.



Thoughts?



-Shaun





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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite on PocketBook

[Beau Wilkinson]

I really think this warrants further discussion. Perhaps the correct answer 
(that ARMs implement a non-standard FP type which is incompatible with Sqlite) 
is already out there, but I think the issues I raised with that answer should 
at least be addressed.

Assuming (and perhaps this is the rub...) that Sqlite is built around C++ 
"float" and "double,"  then I fail to see how any FP system that is even 
plausibly useful could give the results cited by Mr Drozd. If I put (for 
example) the value 100.0 into a "double," and then transport or store/retrieve 
the binary representation somehow, and then take those bits and once more treat 
them as a "double," then I ought to get 100 (or at least something very, very 
close). These are the sorts of things that Sqlite should, to my mind at least, 
be doing with real number data, and it ought not to matter what the underlying 
representation is.

And yet it has been put forth in this forum that such is not the case. Rather, 
the underlying representation must comply with the IEEE FP standard, or even 
basic operations will not work. And this is so certain, well-known, and 
reasonable that discussion amongst the plebians is not warranted.

How is this possible architecturally? The only explanation I can fathom is that 
Sqlite depends on the underlying representation following the IEEE standard at 
the bit level. For example, when doing sorts, maybe Sqlite is assuming the 
mantissae and exponents are in the bit ranges specified by IEEE, and that they 
are represented in the specified format (e.g. excess vs. complement notation) 
as well.

If this is indeed the case, I think this is a very misguided architecture. 
Depending on the bit-level representation is bad. It's a brittle design. Of 
course, it's easy for you all to intimidate anyone who has a problem with this 
architecture... the complainer is "not in compliance with the IEEE standard" 
and is thus clearly worthy of your speedy dismissal. Bah.

Ultimately, I think this is an easy excuse for a bad design. Types like "float" 
and "double" are intended by their designers to abstract over many FP 
implementations. They are not just convenient macros from IEEE FP, nor should 
they be.

I could go on to take issue with the IEEE standard itself. The allocation of 
bits to exponent-versus-mantissa is rigid, for example. IEEE makes no allowance 
(that I know of) for allowing a tradeoff between precision and dynamic range, 
which is a major oversight for such a widely-used standard. Until very recently 
IEEE FP included no support for 16-bit (half precision) data. IEEE was also 
designed by committee so it includes all sorts of nice-to-have pet features 
(two zeros, distinct error and condition codes, etc.) which may or may not be 
worthwhile on any given real-world system. (I tend to lean toward the "may not" 
direction).

But whether IEEE is bad or good or indifferent makes no difference- the 
standard should not, in my opinion, be built into Sqlite. Basic software 
engineering sense must still trump even the best standard.

Forgive me if I have missed something here, but this seems like what I would 
call "Standardizationism" run amok.


From: Beau Wilkinson
Sent: Wednesday, November 04, 2009 9:39 AM
To: General Discussion of SQLite Database
Cc: Alexander Drozd
Subject: RE: [sqlite] SQLite on PocketBook

>I'm guessing that your hardward does not implement IEEE 754 floating
>point correctly.  We've seen that sort of thing before, especially
>with GCC.  There are some options to GCC (which escape my memory right
>now) that can force it to use strict IEEE 754 floating point rather
>than its preferred, speedier but non-standard alternative.

What he's getting back is so far from correct, though, that I would tend to 
blame a run-of-the-mill bug rather than some point-of-detail. Non-IEEE floating 
point often sacrifices things like the distinctions between "Not-a-Number" and 
"Infinity," or the difference between positive and negative zero, and so on. 
Perhaps in some cases the rounding of the last bit is wrong. But no FP system 
should give results that are flat out wrong, especially when doing arithmetic. 
(I can see where series of operations involving exponents  might get way 
out-of-line but I don't think Sqlite is doing any of these things.)

What he is getting back looks like Double.MaxVal... is there a divide-by-zero 
somewhere? That is the sort of thing that different FP specs will legimately 
handle differently.

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of D. Richard Hipp [...@hwaci.com]
Sent: Wednesday, November 04, 2009 9:26 AM
To: General Discussion of SQLite Database
Cc: Alexander Drozd
Subject: Re: [sqlite] SQLite on PocketBook

On Nov 4, 2009, at 4:53 AM, A

Re: [sqlite] SQLite on PocketBook

[Beau Wilkinson]

> Correct, ARM's emulate hardware floating point using software.

That doesn't really say anything about compliance with standards/


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of O'Neill, Owen [oone...@averyberkel.com]
Sent: Wednesday, November 04, 2009 9:36 AM
To: General Discussion of SQLite Database
Cc: Alexander Drozd
Subject: Re: [sqlite] SQLite on PocketBook

Correct, ARM's emulate hardware floating point using software.
And it's really slow too since it forces a context switch as the
processor spots it's been given a FP operation to do and has to jump
into the fp emulation.

If you are lucky enough to have control over it & depending on the level
of accuracy you need, I'd recommend storing everything as an integer
with a separate scalar.

Owen.



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Wednesday, November 04, 2009 3:26 PM
To: General Discussion of SQLite Database
Cc: Alexander Drozd
Subject: Re: [sqlite] SQLite on PocketBook


On Nov 4, 2009, at 4:53 AM, Alexander Drozd wrote:
>
>  My name is Alexander. I am working on an open-source  spaced-
> repetition software project  (http://code.google.com/p/pbanki/). My
> software relies on SQLite library. I came across some bug-like
> problems with running SQLite on a low-memory e-ink reader device. I
> am very  sorry to bother you, but I tried to submit my  problem to
> the bugtracker at the SQLite site, and for some reason anonymous
> login failed.
>
>  The problem appears at the point of reading real values from an
> SQLite database. I created a simple database
>
> CREATE TABLE cards (
>text TEXT NOT NULL,
>value REAL NOT NULL
> );
>
> I also tried to use NUMERIC and FLOAT instead of REAL. Then I
> inserted a few values:
>
> INSERT INTO cards VALUES('second',100.1);
> INSERT INTO cards VALUES('first', 100.0);
>
> Then I execute "select * from cards order by due" query with sample
> code from http://www.sqlite.org/quickstart.html It works perfectly
> when compiled on desktop computer, but fails on target device. The
> device is PocketBook301+ (http://pocketbook.com.ua/). Unfortunately
> their site does not have an English version. This device is based on
> Samsung S3C2440 AL-40 CPU. It runs under open-source firmware called
> pocketbookfree, that is based on Linux 2.6.18.2 armv4tl.
>
> The above query run on pocketbook returns corrupted values for
> floats if they have a non-zero fractional part:
>
> text = first
> val = 100.0
>
> text = second
> val = 1.90359837350824e+185
>
> Sorting by columns containing float numbers also fails when
> specified with ORDER BY. I am not sure whether this is an issue with
> SQLite or with cross-compiler for PocketBook, but I would greatly
> appreciate any suggestions on how to treat this problem.


I'm guessing that your hardward does not implement IEEE 754 floating
point correctly.  We've seen that sort of thing before, especially
with GCC.  There are some options to GCC (which escape my memory right
now) that can force it to use strict IEEE 754 floating point rather
than its preferred, speedier but non-standard alternative.


D. Richard Hipp
d...@hwaci.com



___
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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite on PocketBook

[Beau Wilkinson]

>I'm guessing that your hardward does not implement IEEE 754 floating
>point correctly.  We've seen that sort of thing before, especially
>with GCC.  There are some options to GCC (which escape my memory right
>now) that can force it to use strict IEEE 754 floating point rather
>than its preferred, speedier but non-standard alternative.

What he's getting back is so far from correct, though, that I would tend to 
blame a run-of-the-mill bug rather than some point-of-detail. Non-IEEE floating 
point often sacrifices things like the distinctions between "Not-a-Number" and 
"Infinity," or the difference between positive and negative zero, and so on. 
Perhaps in some cases the rounding of the last bit is wrong. But no FP system 
should give results that are flat out wrong, especially when doing arithmetic. 
(I can see where series of operations involving exponents  might get way 
out-of-line but I don't think Sqlite is doing any of these things.)

What he is getting back looks like Double.MaxVal... is there a divide-by-zero 
somewhere? That is the sort of thing that different FP specs will legimately 
handle differently.

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of D. Richard Hipp [...@hwaci.com]
Sent: Wednesday, November 04, 2009 9:26 AM
To: General Discussion of SQLite Database
Cc: Alexander Drozd
Subject: Re: [sqlite] SQLite on PocketBook

On Nov 4, 2009, at 4:53 AM, Alexander Drozd wrote:
>
>  My name is Alexander. I am working on an open-source  spaced-
> repetition software project  (http://code.google.com/p/pbanki/). My
> software relies on SQLite library. I came across some bug-like
> problems with running SQLite on a low-memory e-ink reader device. I
> am very  sorry to bother you, but I tried to submit my  problem to
> the bugtracker at the SQLite site, and for some reason anonymous
> login failed.
>
>  The problem appears at the point of reading real values from an
> SQLite database. I created a simple database
>
> CREATE TABLE cards (
>text TEXT NOT NULL,
>value REAL NOT NULL
> );
>
> I also tried to use NUMERIC and FLOAT instead of REAL. Then I
> inserted a few values:
>
> INSERT INTO cards VALUES('second',100.1);
> INSERT INTO cards VALUES('first', 100.0);
>
> Then I execute "select * from cards order by due" query with sample
> code from http://www.sqlite.org/quickstart.html It works perfectly
> when compiled on desktop computer, but fails on target device. The
> device is PocketBook301+ (http://pocketbook.com.ua/). Unfortunately
> their site does not have an English version. This device is based on
> Samsung S3C2440 AL-40 CPU. It runs under open-source firmware called
> pocketbookfree, that is based on Linux 2.6.18.2 armv4tl.
>
> The above query run on pocketbook returns corrupted values for
> floats if they have a non-zero fractional part:
>
> text = first
> val = 100.0
>
> text = second
> val = 1.90359837350824e+185
>
> Sorting by columns containing float numbers also fails when
> specified with ORDER BY. I am not sure whether this is an issue with
> SQLite or with cross-compiler for PocketBook, but I would greatly
> appreciate any suggestions on how to treat this problem.


I'm guessing that your hardward does not implement IEEE 754 floating
point correctly.  We've seen that sort of thing before, especially
with GCC.  There are some options to GCC (which escape my memory right
now) that can force it to use strict IEEE 754 floating point rather
than its preferred, speedier but non-standard alternative.


D. Richard Hipp
d...@hwaci.com



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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What sort of programming errors would cause these strange results?

[Beau Wilkinson]

>Is it possible that the library is sorting the CUSTOMER.id list
>alphabetically not numerically, and jumping into the sorted PK list at
>the correct offset but finding the wrong value in that location as a
>result of the improper sort?

That seems plausible. But speaking as a programmer, I do not typically get much 
value from such "armchair" debugging. Sometimes I get bug reports, and from the 
information given I get the sense that a whole workgroup has spent the whole 
morning reverse engineering some problem in my code. It is almost always more 
efficient to just go ahead and report the error.

However, the economics change if you have the source code. You said that the 
library is coming from a "major player." If it is written using Microsoft .NET, 
you probably can decompile the source code very effectively using Lutz Roeder's 
.NET Reflector, which is shareware (maybe even freeware). Then, you will no 
longer be faced with a "black box" and you might even be able to extract the 
code and recompile it with the sorting bug patched.



From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Tim Romano [tim.rom...@yahoo.com]
Sent: Tuesday, November 03, 2009 5:21 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] What sort of programming errors would cause these strange 
results?

I have a SQLite database working quite nicely. The three main tables
have 4 million rows and 275,000 rows respectively, and query response
times are excellent: I have used several GUI front ends to query the
data, and they jibe with each other, both with regard to the (accurate)
results they return and the time they take. Very happy with SQLite. But
all is not rosy.

I recently wrote a little app using a library I hadn't worked with
before (source code for it not available -- it's a black box) and this
library is making some very basic mistakes with the data. Their results
are always wrong. There is something fundamentally awry in the library code.

I will describe two basic problems, and maybe those two points will
create a line pointing to the problem. I hope the combined knowledge and
intuition of this group will allow me to suggest where the authors of
this library might look for bugs in their code. They are a major player.
Their library and only their library is having problems with my data.
Three other tools for SQLite (Razor, Maestro, .NET provider by Robert
Simpson) are returning correct results.


Bug #1
In a select involving a simple equijoin.
NOTE: ORDERS.custid and CUSTOMER.id are integer

select ORDERS.id, CUSTOMER.name
from ORDERS inner join CUSTOMER on ORDERS.custid = CUSTOMER.id
where ORDERS.ordertype in ('a','b','c')

The correct rows from ORDERS are being returned;  the WHERE ordertype
IN(...) condition is working.
But the CUSTOMER.name values are all wrong!
When I examine the rows in CUSTOMER that hold the (wrong) names returned
by the query,  I see that the PK of those rows (CUSTOMER.id) would be
found very  near the ORDER.custid value **if these CUSTOMER.id integer
values were being sorted AS TEXT**.  For example

ORDERS.custid...CUSTOMER.id of the wrong customer name returned by
the query
140..1400
160..1600
253025230
276027520

Is it possible that the library is sorting the CUSTOMER.id list
alphabetically not numerically, and jumping into the sorted PK list at
the correct offset but finding the wrong value in that location as a
result of the improper sort?

Bug#2
In a select against a table with 275,000 rows:

select pkcol, textcol
where textcol = 'x'

Some pkcol values are incorrect!  Some are correct.
There's a non-unique index on textcol, which is varchar.

Thanks for the help.









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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT * vs SELECT columns ?

[Beau Wilkinson]

>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.

This will "save you some time" in the sense that the program may execute faster 
at runtime. But in those situations where I'm going to need everything anyway, 
I feel no guilt about using the asterisk. For example, the asterisk can afford 
a measure of dynamicity when writing a data access layer, ORM, or the like. If 
the schema changes, a listing of every column can be invalidated, but the 
asterisk cannot. I can imagine architectures in which tables are mapped to 
structures on a one-to-one basis, with all fields included, and in that case 
use of the asterisk could be the difference between "success" and "success with 
a side of carpal tunnel syndrome."

>I don't know about SQLite, but in all SQL courses you learn that you
>should NEVER use the asterisk.

Generally, I am skeptical about absolute statements (e.g. "always" and 
"never"). Also, I am skeptical of commentaries that attempt to embellish the 
specification of a language with points-of-advice expressed in natural 
langauge, e.g. the old saw that "C has a preprocessor but you shouldn't use it" 
(or pretty much any statement ever made by Herbert Schildt). This sort of 
hand-holding is fine in the semster of instruction, perhaps, but after that I 
really have no use for it. The specification includes the asterisk and I refuse 
to feel guilt for using it.

The "dark underbelly" of these commonsensical aphorisms is that they end up 
being immortalized into corporate standards. I am sure that somewhere, some 
poor programmer is burning up his keyboard entering thousands of column names 
needlessly because some architect-type at his company heard that the asterisk 
was bad.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kristoffer
Danielsson
Sent: Wednesday, October 28, 2009 12:51 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SELECT * vs SELECT columns ?


I don't know about SQLite, but in all SQL courses you learn that you
should NEVER use the asterisk.



The asterisk is merely there to let you quickly view data _manually_.


> Date: Wed, 28 Oct 2009 16:02:01 +0200
> From: mi...@limbasan.ro
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] SELECT * vs SELECT columns ?
>
> I would expect there to be a speed and memory performance *impact* if
> the result set contains columns other than the three specified ones,
> since obviously the library will need to allocate more memory to hold
> the extra data.
>
> On 10/28/2009 03:52 PM, Pete56 wrote:
> > I am searching across two joined tables and am interested in a few
> > parameters:
> >
> > SELECT a.first a.third b.first FROM a JOIN b ON a.RowID = b.RowID
WHERE
> > value = :value
> >
> > Is there any speed or memory performance improvement by using SELECT
*,
> > rather than SELECT ?
> >
> > If I know there will only be one item (unique :value) selected
(LIMIT 1) can
> > I make any performance improvements ?
> >
>
> --
> Cu stima,
> Mihai Limbasan
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_
Nya Windows 7 - Hitta en dator som passar dig! Mer information.
http://windows.microsoft.com/shop
___
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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New open source data synchronization tool

[Beau Wilkinson]

>Hello,
>
>We are trying to find an ETL tool open source. Basically, we need our
>software to perform ETL, data migration and data synchronization.
>
>The program should not be used on larger projects. A few open source tools
>are on the market.

>Some ideas? Thanks.

What is ETL?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cygwin and sqlite

[Beau Wilkinson]

>The point I was trying to make is that the majority of commands cygwin
>supports don't have to do any forking. They could just be method
>calls. Pipes would be parameters going in and return values coming
>out, all running in a single process. If fork() is specifically called
>in a script, then the slower approach would have to be used, but most
>of the time it would not.

Yes, in theory that's true, but Cygwin strives to be a lightweight connecting 
layer between the Unix-like facilities of Windows and the wealth of Unix code 
available. Wherever these "Unix-like" features turn out to not actually not be 
so "Unix-like," the model breaks down (e.g. fork()), and Cygwin has to be 
larger and more intrusive. There are many other architectures in use out there. 
The many Unix-like tools that run natively on Windows are, in some sense, an 
example of another POSIX / Windows hybridization archietcture. But this is not 
the architecture of Cygwin.

Incidentally, I think the fundamental problem is the asinine way Windows treats 
processes - vs. - threads. Microsoft seems to encourage multithreading, not 
just by making processes relatively heavy, but also in their documentation and 
examples and in the overall direction of their R efforts.

My opinion is that processes should be lightweight, because they offer a much 
more predictable and useful way to effect parallel processing than threads. I 
always try to use processes over threads when I detect the need for 
parallelism, even in Windows. Other Windows developers consider my 
process-spawning to be awkward, or even "a hack," but as someone with Unix 
experience this seems natural, and the end results are typically more 
deterministic.

- Original Message -
From: "Pavel Ivanov" 
To: "General Discussion of SQLite Database" 
Sent: Tuesday, September 22, 2009 6:58 AM
Subject: Re: [sqlite] cygwin and sqlite


>> But after all these years I wonder why they don't fix the fork
>> problem?
>> MacOS runs on Intel processors. Windows runs on Intel processors.
>> Surely
>> they could learn how it *should* be done by studying things like
>> the
>> Open Source Java code?
>
> You seem to forget the basics. It's not the processor who makes
> fork()
> possible, it's OS. Unix kernel implemented fork() and Unix kernel
> implemented process management in the way that makes implementation
> of
> fork() very quick and easy. Windows didn't implement fork() and it
> implemented process management in the way making fork() impossible.
> I'd say it's a superior achievement on cygwin side that they were
> able
> to implement fork() somehow at all. Just a simple fact: you execute
> some code that uses memory in some way then you call fork() on Unix
> and you already have 2 absolutely different processes that can
> access
> the same data in memory. On windows there's no way to start a new
> process so that it can access the same data as first process unless
> you thought about that beforehand, placed all your data into shared
> memory (which is a lot harder to work with, btw) and made another
> process to read the same shared memory. Also there's no way to start
> new process on Windows so that it executes the same code as first
> process from the point where second process was started...
> So I'd better not complained but tried to understand the roots of
> the problem...
>
> And if you want the comparable speed of scripts on both platforms I
> suggest you to look into perl (or python, whatever you prefer). The
> both have native implementations and their speed should be
> comparable
> to each other. That said of course if you don't start a lot of
> processes from scripts and don't try to run command line
> utilities...
>
> Pavel
>
> On Mon, Sep 21, 2009 at 10:29 PM, John  wrote:
>> Pavel Ivanov wrote:
 MacBook Mac OS X 10.5.8
 2 GHz Intel Core Duo
 1 GB memory:
 17 minutes 46 seconds.

 IBM ThinkPad
 Windows XP (latest patches)
 1.70 GHz, 512 MB memory:
 6 hours 25 minutes 57 seconds
>>>
>>> Windows is very slow in starting new processes if compared to any
>>> Unix
>>> system (especially if compared Windows + 512 MB and Unix + 1 GB).
>>> In
>>> cygwin starting new processes even slower because for some reason
>>> emulating fork() involves starting 2 processes one of which dies
>>> immediately. And bash scripts use processes a lot especially with
>>> Unix
>>> paradigm when for each small action you start new program (like
>>> sed,
>>> awk, test, true and whole lot of others). Thus bash scripts on
>>> cygwin
>>> will be slow unavoidably.
>>> But I'm digressing. This is subject for some other mailing list.
>>> :)
>>
>> I'm blacklisted apparently on the cygwin mailing list for when a
>> couple of years ago I complained rather unflatteringly about how
>> slow
>> it was/is when I was writing a simple expenses program (that works
>> in
>> seconds on my Mac). I forgot about that. A 

Re: [sqlite] Integer Storage class

[Beau Wilkinson]

>i chose Integer for Performance isues... i'm develop. an POS Software and 
>our articles table has lot's of >records. i need to optimize search. 
>an Integer (1,2,4,6,8 bytes) is faster that 13-bytes-ean text for >comparison.

That's reasonable, but I think Sqlite stores everything as textual data anyway. 
I think the implication is that this data will take at least 
one-byte-per-digit. INT should still be a bit smaller than TEXT for your 
purposes, because it will drop the zeros (e.g. string "100" is smaller in 
memory than "100" even though both are still larger than the integer 
100). But if INT simply doesn't have the necessary range, your options are 
limited.


--- El mié 2-sep-09, Beau Wilkinson <b...@mtllc.us> escribió:

> De: Beau Wilkinson <b...@mtllc.us>
> Asunto: Re: [sqlite] Integer Storage class
> Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Fecha: miércoles, 2 de septiembre de 2009, 11:14 am
> I probably wouldn't use INT for that
> data. I would use TEXT. My feeling is that the data is not
> so much a number as it is an incoming stream of characters
> from an IO device. So, I suspect code built around a TEXT
> column will ultimately be more rational looking. For
> example, you won't have to pad periodically pad your data
> with zeros, there won't be any signed-vs.-unsigned
> confusion, etc.
>
> The one drawback is that an INT implementation may be more
> optimal than a TEXT implementation. The string "65535" takes
> up at least 5 bytes, for example, whereas the number 65535
> (base 10) will fit in two bytes.
> 
> From: sqlite-users-boun...@sqlite.org
> [sqlite-users-boun...@sqlite.org]
> On Behalf Of Simon Davies [simon.james.dav...@googlemail.com]
> Sent: Wednesday, September 02, 2009 9:05 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Integer Storage class
>
> 2009/9/2 Sebastian Bermudez <sebastian.bermu...@yahoo.com>:
> > Ok. my problem is my SQLITE front end (SQLITE
> ADMINISTRATOR v 0.8.3.2) ... show me 0 (cero) in that
> column.
>
> Looks like SQLITE ADMINISTRATOR v 0.8.3.2 only deals with
> signed 32
> bit values. 2147483647 it accepts as a valid value,
> 2147483648 it
> rejects, saying that it is not a valid integer.
>
> >
> > thanks
> >
>
> Regards,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> The information contained in this e-mail is privileged and
> confidential information intended only for the use of the
> individual or entity named.  If you are not the
> intended recipient, or the employee or agent responsible for
> delivering this message to the intended recipient, you are
> hereby notified that any disclosure, dissemination,
> distribution, or copying of this communication is strictly
> prohibited.  If you have received this e-mail in error,
> please immediately notify the sender and delete any copies
> from your system.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


  Yahoo! Cocina

Encontra las mejores recetas con Yahoo! Cocina.


http://ar.mujer.yahoo.com/cocina/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Integer Storage class

[Beau Wilkinson]

I probably wouldn't use INT for that data. I would use TEXT. My feeling is that 
the data is not so much a number as it is an incoming stream of characters from 
an IO device. So, I suspect code built around a TEXT column will ultimately be 
more rational looking. For example, you won't have to pad periodically pad your 
data with zeros, there won't be any signed-vs.-unsigned confusion, etc.

The one drawback is that an INT implementation may be more optimal than a TEXT 
implementation. The string "65535" takes up at least 5 bytes, for example, 
whereas the number 65535 (base 10) will fit in two bytes.

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Simon Davies [simon.james.dav...@googlemail.com]
Sent: Wednesday, September 02, 2009 9:05 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Integer Storage class

2009/9/2 Sebastian Bermudez :
> Ok. my problem is my SQLITE front end (SQLITE ADMINISTRATOR v 0.8.3.2) ... 
> show me 0 (cero) in that column.

Looks like SQLITE ADMINISTRATOR v 0.8.3.2 only deals with signed 32
bit values. 2147483647 it accepts as a valid value, 2147483648 it
rejects, saying that it is not a valid integer.

>
> thanks
>

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Viable alternatives to SQL?

[Beau Wilkinson]

> I want a query language that non-techies can use easily, but also
> supports arbitrarily complex queries. Does such a language exist?

I remember reading once, in an old book about RDBMS, that SQL was intended to 
be something that non-technical decision-maker types could learn in a few 
hours. Apparently some early proponents of SQL had a naive vision of Robert 
McNamara-types writing queries like "SELECT NAME FROM PRODUCT ORDER BY 
GROSS_MARGIN" and then basing decisions on the result. All that was necessary 
(they thought) was a little abstraction (i.e. SQL) to hide the accidental 
difficulties of "computer language."

Oh, to be so young and stupid again...

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Darren Duncan [dar...@darrenduncan.net]
Sent: Thursday, August 27, 2009 2:16 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Viable alternatives to SQL?

Kelly Jones wrote:
> Many sites let you search databases of information, but the search
> queries are very limited.

A fact I can agree with time and again.

> I'm creating a site that'll allow arbitrary SQL queries to my data (I
> realize I'll need to handle injection attacks).

Now by "arbitrary SQL queries", you mean "arbitrary SQL SELECT statements" I
assume; otherwise, no need for injection as you're already letting them do
whatever they want.

Generally speaking, if you truly want arbitrary queries, you essentially have to
provide a facility to users that is analogous to letting them write in a
programming language, like SQL.  To a large extent, you could accomplish this
either by providing lots of form fields where they build up queries by picking
from smaller rules, or alternately you could let them type an actual query like
one would with SQL but instead they write in some simpler language, say one of
your own design, which you then parse and translate to SQL for SQLite to run.
To prevent injection attacks, you in the many-fields case make sure to escape or
validate/restrict all inputs to allowed values, or in the latter case you simply
don't provide features in your simpler query language that you don't want them
to have, such as non-read queries.

> Are there other viable ways to query data? I read a little on
> "Business System 12" (BS12), Tutorial D, and even something called
> T-SQL (I think), but they all seem theoretical and not fully
> implemented.

Business System 12 is a legacy project, one of the original relational database
implementations, that predates SQL.  It isn't a separate language and has no
bearing on using with SQLite.

T-SQL is a variant of SQL used by the likes of Sybase and MS SQL Server, I
think, and maybe some other DBMSs; T-SQL is for those DBMSs what PL-SQL is for
Oracle.  You see T-SQL/PL-SQL in SQL stored procedures, which SQLite doesn't
natively support anyway.

Tutorial D is indeed an actual language which isn't tied to a specific DBMS, as
with generic SQL itself, and could potentially be something SQLite could support
directly in the future, but it doesn't now.  There *are* several Tutorial D
implementations, but not over SQLite.  A Java DBMS named "Rel" supports it for
one thing, and also the major SQL DBMS named Ingress is looking to add support
for it as a native language.

The syntax of Tutorial D is superficially like SQL and has most of the same
features, but with some extra features and some omission of mis-features.

For example, here are some simple query comparisons (I think):

SQL:
   SELECT * FROM mytable
TD:
   mytable

SQL:
   SELECT col1, col2 FROM mytable
TD:
   mytable{col1, col2}

SQL:
   SELECT * FROM mytable WHERE col1 = 'foo' AND col2 = 'bar' OR col1 = 'baz' AND
col2 = 'quux'
TD:
   mytable WHERE col1 = 'foo' AND col2 = 'bar' OR col1 = 'baz' AND col2 = 'quux'
or:
   mytable MATCHING RELATION { TUPLE { col1('foo'), col2('bar') }, TUPLE {
col1('baz'), col2('quux') } }

> I want a query language that non-techies can use easily, but also
> supports arbitrarily complex queries. Does such a language exist?

That actually describes SQL to some extent (and Tutorial D).  Compared to other
general purpose languages, SQL is fundamentally easier to use, because it
focuses on people just saying "what" they want to happen rather than "how".

If you want your solution now, and use SQLite, you either may have to roll your
own solution, and/or look at the various database wrapper frameworks out there
(there are a bunch for Perl for example) which may help you do this.

I will also say that I'm making a solution for constructing arbitrarily complex
relational or SQL queries out of data structures in Perl, focusing on enabling
what you can do with stored procedures (which includes all other queries), which
would work with SQLite.  But it isn't ready to use yet.  You might be able to
use it though depending on your time table.  This project is multi-pronged, and
see 

Re: [sqlite] INSERT with multiple VALUES clause?

[Beau Wilkinson]

>On Tue, Aug 18, 2009 at 08:28:13AM -0500, Beau Wilkinson scratched on the wall:
>> To me that seems like an annoying deviation from standard practice.
>> Do other databases support such an INSERT?
>
>Yes.  MySQL, PostgreSQL, and SQLServer all support this syntax.
>
>Oracle supports a slightly modified syntax that allows multple rows
>to be inserted into multiple tables with one INSERT ALL.  The syntax
>for that is basically INSERT ALL INTO... INTO... INTO...
>
> DB2 and HSQL (and SQLite) do not support this.
>
> Personally, I think the first syntax would be a nice enhancement.
>
> It is also worth noting that every non-SQL Relational language (e.g.
>  Tutorial-D) I've looked at supports some form of multiple inserts, usually
>  into multiple tables, with one command.  If you're a stickler for
> constraints, and believe enforcement shouldn't be delayed (as most
>  theory-heavy folks do), you have to have something like this.

I definitely see what you mean about constraints; I guess when I was doing 
heavy (Oracle) database programming I must have been sidestepping the issue by 
turning things constraints off selectively, or maybe just inserting things in a 
certain order.

Doesn't what you said about constraints imply that the individual sub-INSERTs 
must be treated as if they all arrive at the database at once, e.g. for 
purposes of constraint checking? This will be a detail to consider if this gets 
implemented...

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT with multiple VALUES clause?

[Beau Wilkinson]

To me that seems like an annoying deviation from standard practice. Do other 
databases support such an INSERT? Is it envisioned by standards? I suspect the 
answer is "no" in both cases, and this is a classic example of how "less" 
functionality is actually "more" useful.

That said, if you're in posession of the source code, you can certainly hack 
something up to support that. A better option might be to pre-process the MySQL 
file using C, Perl, XSLT (just kidding - don't use XSLT) or whatever else you 
prefer for this kind of rote file manipulation


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Terrence Brannon [scheme...@gmail.com]
Sent: Tuesday, August 18, 2009 7:50 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] INSERT with multiple VALUES clause?

Hello, when converting a .sql file containing MySQL INSERT clauses,
one often runs into the problem that the MySQL INSERT can accept
multiple VALUES arguments:

INSERT INTO actor VALUES (1,'PENELOPE','GUINESS','2006-02-15
04:34:33'),(2,'NICK','WAHLBERG','2006-02-15
04:34:33'),(3,'ED','CHASE','2006-02-15 04:34:33')


 whereas the SQLite INSERT can only take one...

Is there any possibility of extending the syntax of the SQLite insert
to accept multiple VALUES arguments?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Bad CPU type in executable"?

[Beau Wilkinson]

>> Sounds like I'm not welcome on this list.  Go hassle someone else.
>> Goodbye.
>A bit sensitive, no?
>I was actually defending you.

With the mix of top and bottom posting, it's difficult to tell who's talking to 
whom. My preference would be for a forum or email system that enforces one or 
the other. I don't really care which; what I really dislike is anything that's 
on the honor system. One way or the other ought to be enforced by the tools 
wherever possible. Too often the "wrong way" becomes little more than a straw 
man for mockery ("he didn't call 'MARSHALL(_widgelet_)' at the end of his 
method... ha! what an idiot").

The sad result of this misunderstanding is that the world will just have to do 
without the "Texas State Probation Officer Visit Scheduler App," "Texas Death 
Penalty Last Meal Expense Calculator," etc.

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subtotal SQL

[Beau Wilkinson]

I think this may involve a subquery, probably in the SELECT list itself, which 
uses ROWID (or ROWNUMBER... I can't remember which one is a part of the SQL 
standard rather than proprietary).


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of cmar...@unc.edu [cmar...@unc.edu]
Sent: Wednesday, July 29, 2009 9:08 AM
To: punk...@eidesis.org; General Discussion of SQLite Database
Subject: Re: [sqlite] Subtotal SQL

On Wed, 29 Jul 2009, P Kishor wrote:

> For my part, I don't know how to do a running total in a result set
> unless I have some kind of a counter that keeps track of the "row
> before the current row"

This is also the only way I know of.

Chris


> On Wed, Jul 29, 2009 at 8:50 AM, Rich Shepard wrote:
>> On Wed, 29 Jul 2009, Adler, Eliedaat wrote:
>>
>>> SQL/sqlite challenge  for all:
>>
>>   No challenge for anyone who knows SQL.
>>
>>> I need a running sum of size that works regardless of what order the 
>>> objects are in.
>>> User Function/Aggregates welcome!
>>
>>   _All_ implementations of SQL include a suite of aggregate functions, and
>> SUM() is among those. Take a look at the SQLite Web site, Mike Owens's book,
>> Rick van der Lans's book, or any introduction to SQL.
>>
>>   You will write, "SELECT filename, date, owner, size, SUM(size) AS total
>>FROM mytable;"
>>
>
>
> Well, not really. The above totals across the entire result set. The
> OP wants a running total, kinda like in a spreadsheet.
>
> Since the question is phrased as a "challenge," I wonder if the OP has
> the answer already, and merely wants to test the SQL sages on the
> list. Otherwise, a better spirit would be to simply ask the question
> as a question, and not as a challenge.
>
> For my part, I don't know how to do a running total in a result set
> unless I have some kind of a counter that keeps track of the "row
> before the current row"
>
>
>
>
>
> --
> Puneet Kishor
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Beau Wilkinson]

>> Rich Shepard wrote:
>>What is more unfortunate is when someone with greater knowledge takes over
>> a project but is prevented from re-doing it in a more efficient way because
>> someone else's ego will be bruised or the powers that be cannot appreciate
>> the need.

I think my knowledge is just broader than my predecessor on that project. He 
cultivated a really detailed knowledge of the Windows / Intel platform, which 
didn't seem to allow any time or brainpower to learn about more general 
concepts.

I try to be more of a generalist, although it can be unpopular. My colleagues 
seem to prefer to pick a single nascent technology and cling mightily to it 
until they perceive something better. Trying to discuss architecture with 
people who do that is like talking to someone in a cult... no matter what the 
topic or question is, the discussion eventually flows back to You Know What. 
The sad result is that I end up gravitating toward maintenance programming, 
simply because the arguments have already been had.

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Rich Shepard [rshep...@appl-ecosys.com]
Sent: Monday, July 27, 2009 10:46 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] What is a Relation?

On Mon, 27 Jul 2009, Beau Wilkinson wrote:

> I am dealing with such a project now. The schema consists of time stamp
> plus blob, where the blobs "map" directly to C++ structs. Of course, there
> are all sorts of useful data items in those blobs, and many of the
> capabilities of SQL are lost by reducing data to blobs. I think this is an
> extreme example of what you describe. The architectural excuse given was,
> I think, performance.

   I'm not a professional coder or DBA, but I've been writing code and
building database applications for about 3 decades. The problem, I believe,
is shifting paradigms and the difficulties people have in doing so.

   The original spaghetti-code model I used with FORTRAN in the early 1970s
gave way to structured programming with C. Most of us still effectively use
structured programming with procedural languages, either compiled or
interpreted. While OO is a nice concept, and does have use in the
appropriate applications, it produces bloated binaries because all classes
in the hierarchy need to be compiled and included, even if one function is
all that's needed. However, even within the huge tribe of procedural
languages, there are different paradigms that one needs to understand to
most efficiently use a language. The differences between C and Common LISP
immediately come to mind.

   Regardless, it is very common to see those new to SQL take whatever
knowledge and experience they have with procedural languages and try to
apply it to working with sets. Your project summarized above seems to be an
example of this. Trying to apply the flow control and other structures of a
procedural language to SQL tends to result in a mess. Too few books or other
resources stress these differences so newcomers are taught to look at the
whole database manipulation language (principally the SELECT statement) in a
different way.

   What is more unfortunate is when someone with greater knowledge takes over
a project but is prevented from re-doing it in a more efficient way because
someone else's ego will be bruised or the powers that be cannot appreciate
the need.

Rich

--
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
<http://www.appl-ecosys.com> Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Beau Wilkinson]

>> There are still people who just want
>> a cursor to a chunk of data which they pull in and iterate over rather than
>> use SQL's power to manage data a set-at-a-time

I am dealing with such a project now. The schema consists of time stamp plus 
blob, where the blobs "map" directly to C++ structs. Of course, there are all 
sorts of useful data items in those blobs, and many of the capabilities of SQL 
are lost by reducing data to blobs. I think this is an extreme example of what 
you describe. The architectural excuse given was, I think, performance.

>> Call me old fashioned but object-relational mappers worry me on this score

I agree, except that you're not old-fashioned. In fact, I think you're on the 
cutting edge here. OO (including any attempt to apply it to database work) is 
no longer trendy. At best, it is tolerated... perhaps many people still haven't 
admitted that the proverbial emporer has no clothes, but no one's bragging 
about his (i.e. OOP's) new jacket, either.

Microsoft, for example, is moving rapidly to functional programming and generic 
programming. Implementation inheritance has been lobotomized in C# compared to 
C++, for example, and Microsoft's new "Linq" database client technology is 
functional and generic.

As for ORM, OODBMS (whatever that is), etc. proponents of such technologies 
continue to hem and haw about "object-relational impedance." This is just  a 
fancy way of describing the problems that result when a good (Relational) model 
of reality must interface with a dysfunctional (OO) model. OO creates problems 
(or "opportunities"...) because it doesn't work. MIT has been saying this for 
30 years. Few people are willing to consciously stick their necks out and say, 
"you know, I've always thought OOP was a bunch of B.S" but the sentiment's out 
there and it's justified.


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of CityDev [nab...@recitel.net]
Sent: Monday, July 27, 2009 9:33 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What is a Relation?

It's true that Codd and Date used the term 'relational' (They championed the
N-ary Relational Model - others were around at the same time) but it's not
easy to track the origin of the term in mathematics. Certainly the word
implies joining things together. I guess the joining refers to fields
(domains) within each row (n-tuple).

If you look at other forums you often see novices (and others) using the
word 'relate' as if it is some special way of joining information between
two tables. They also clearly feel that you need to declare foreign keys in
order to have a logical connection between tables. As you are no doubt
aware, one of the guiding principles of the relational model is there is
nothing that is not a data value so you are totally free to join anything to
anything as long as you feel it might make sense. Relationships between
tables are contingent - they can be there one day and gone the next. Any
persistent relationship information (eg foreign keys) is optional and there
for other purposes (eg documentation or referential integrity enforcement).

Another common conceptual misunderstanding I've seen over the years is that
the database is just a fancy filestore. There are still people who just want
a cursor to a chunk of data which they pull in and iterate over rather than
use SQL's power to manage data a set-at-a-time. Call me old fashioned but
object-relational mappers worry me on this score.

--
View this message in context: 
http://www.nabble.com/What-is-a-Relation--tp24674278p24681797.html
Sent from the SQLite mailing list archive at Nabble.com.

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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pros and cons of various online code sharing sites

[Beau Wilkinson]

I wholeheartedly agree about the importance of source control, even though my 
specific advice didn't really involve that.

To the OP in particular:
How do you plan to statically link your new library into multiple projects? Are 
you planning to make a copy of the code files for each C# project that uses 
Sqlite? I don't think you'll be able to do this for assemblies written in any 
other language, e.g. VB.NET. In fact, people using these languages are 
basically locked out of your library, at least without a wrapper to make it a 
DLL (I think).

Also, this approach (making copies of the code) introduces parallel maintenance 
issues, esp. if you end up with multiple copies of the library on your own 
computer. I guess the existence of parallel copies is acceptable - even 
desirable - under some circumstances, I just wonder if you've considered these 
issues. There is also the "Add as Link" feature, which is similar to #include 
in C++. I have never worked through all of the issues re. source control and 
"Add as Link," although I suspect this can be made to work.

I don't mean to defy your disclaimer; these are topics of some practical 
importance to me. I deal with C++ / Sqlite apps daily that may eventually need 
to be (rapidly) transitioned to C#. My growing sentiment is that .NET does not 
really lend itself to this... I guess I want "#include" from C++.

Another area I miss "#include" is in declaring simple constants; should two 
different assemblies that, say, both need to know that WAKEUP_STRING means 
"~~~WTFU~~~" really have to use some kind of DLL or IPC mechanism at runtime to 
ascertain this fact? Or should they have parallel copies of, say, Contants.cs? 
Or should we use "Add as Link" and kludge around source control? I wonder if 
Microsoft really thought these things through, especially since it is there 
source control tool that so greatly mishandles "Add as Link."

Maybe you or someone else can dispel my confusion on these points. Someone once 
suggested to me that the best answer is to select "~~~WTFU~~~" (or whatever) 
from a "constants" table in a database. That seems like an arbitrary and 
unwelcome requirement, although if this really must be done, Sqlite might be 
the way to go.




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Roger Binns [rog...@rogerbinns.com]
Sent: Thursday, July 09, 2009 12:29 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Pros and cons of various online code sharing sites

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Noah Hart wrote:
> I have looked at sourceforge, googlecode, codeproject and a few others.
> However, while they all look fine, I not sure what works well in the real
> world.

TL;DR: Being familiar with DVCS is an important developer skill these
days. Concentrate on that.  I'd recommend Google Code, BitBucket and
then SF.

I can't speak for codeproject but can for the others.  Google code only
allows a small subset of licenses (a good thing) but doesn't include
public domain which is what SQLite itself is.  Google's mailing lists
are the best.  SF has horrendous mailing lists and web interface.  SF
has over the last year or so added every bell and whistle imaginable
(wikis, bug trackers, trac etc).

What I would recommend is that you also take this as an opportunity to
learn how to use a DVCS.  The main players are git, mercurial and bzr
and you'll get adherents coming out of the woodwork for each, but they
are all far more similar than they are different.  Unless you love all
things Ubuntu, I'd recommend starting with mercurial and once
comfortable with it try out git to get a different perspective.  SF and
GoogleCode both support mercurial, and there is a dedicated mercurial
based hoster at http://www.bitbucket.org

 http://hgbook.red-bean.com/

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkpWKQIACgkQmOOfHg372QRgMQCbBGWsCD0t2GwotjeWaPJOzcTA
LykAniJas2Yy/iemeVyuma6kxRwrfui2
=kExD
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pros and cons of various online code sharing sites

[Beau Wilkinson]

By the way, Noah, I think share your feelings about DLL-free static linking in 
.NET. It ought to be easier to statically link to libraries in .NET, e.g. to 
create a truly standalone .EXE. This oversight is one of the main reasons I now 
avoid .NET.

In particular, C# doesn't have "#include" or anything like it, except that 
Visual Studio does provide an "Add as Link" option for existing files which 
statically links to them. The "link" gets converted into a project-specific 
copy if you add the project to SourceSafe, though. This happens silently, even 
though it introduces a very undesirable parallel maintenance burden!

-
Beau Wilkinson
Software Development Engineer, DP
MARINE TECHNOLOGIES, LLC
985-612-1313 (office)
x52913 (ECO phone)
985-705-5203 (cell)

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Noah Hart [n...@lipmantpa.com]
Sent: Thursday, July 09, 2009 11:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite]  Pros and cons of various online code sharing sites

BACKGROUND:
In order to learn C#, I have spent the last year converting the source code
of SQLite3 from C to C#. As of version 3.6.16, it is now ready to release in
the wild. I don't want to self-host CVS or some other repository, so I am
trying to decide where to post the code.

My goals for this are simple. I just want to allow people to download the
source code, submit feedback, bug reports, etc.

I have looked at sourceforge, googlecode, codeproject and a few others.
However, while they all look fine, I not sure what works well in the real
world.

Any suggestions from your personal experience would be appreciated.

DISCLAIMER:
This port was done simply to learn C#, and then embed SQLite3 into a C#
application without the need for a dll.  Please no criticism for doing this,
or a discussion of if it was advisable to port SQLite3 to C# in the first
place.

Also, I know this is off-topic, but I am really interested in your
suggestions.  If you want to flame me, please reply directly, and not to the
list.

Regards,

Noah Hart

--
View this message in context: 
http://www.nabble.com/Pros-and-cons-of-various-online-code-sharing-sites-tp24413069p24413069.html
Sent from the SQLite mailing list archive at Nabble.com.

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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pros and cons of various online code sharing sites

[Beau Wilkinson]

In my personal experience Code Project is the most useful, especially if you 
work heavily with C#.

I have worked with SourceForge and my personal opinion is that it's a bit too 
UNIX-oriented for my taste. I love UNIX, and use (for example) Cygwin and MinGW 
very extensively for development work. But for mundane chores like browsing the 
Web, learning about a project, downloading and decompressing its code, etc. the 
reality of 2009 is that I'm working in Windows and CodeProject seems to respect 
that more than SourceForge.

And I will respect your disclaimer, but I am having to grab my arm a la Dr. 
Strangelove to do so. What's done is done.
-
Beau Wilkinson
Software Development Engineer, DP
MARINE TECHNOLOGIES, LLC
985-612-1313 (office)
x52913 (ECO phone)
985-705-5203 (cell)

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Noah Hart [n...@lipmantpa.com]
Sent: Thursday, July 09, 2009 11:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite]  Pros and cons of various online code sharing sites

BACKGROUND:
In order to learn C#, I have spent the last year converting the source code
of SQLite3 from C to C#. As of version 3.6.16, it is now ready to release in
the wild. I don't want to self-host CVS or some other repository, so I am
trying to decide where to post the code.

My goals for this are simple. I just want to allow people to download the
source code, submit feedback, bug reports, etc.

I have looked at sourceforge, googlecode, codeproject and a few others.
However, while they all look fine, I not sure what works well in the real
world.

Any suggestions from your personal experience would be appreciated.

DISCLAIMER:
This port was done simply to learn C#, and then embed SQLite3 into a C#
application without the need for a dll.  Please no criticism for doing this,
or a discussion of if it was advisable to port SQLite3 to C# in the first
place.

Also, I know this is off-topic, but I am really interested in your
suggestions.  If you want to flame me, please reply directly, and not to the
list.

Regards,

Noah Hart

--
View this message in context: 
http://www.nabble.com/Pros-and-cons-of-various-online-code-sharing-sites-tp24413069p24413069.html
Sent from the SQLite mailing list archive at Nabble.com.

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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

[Beau Wilkinson]

I did some tests today and demonstrated that this problem goes away if the main 
thread is forced to sleep while the helper thread is running. Since the threads 
are operating on distinct databases, I don't think this should be necessary.

If I've misunderstood the level of thread safety provided by Sqlite, please 
tell me. Otherwise, I think this may be a Sqlite bug. Perhaps some confusion is 
resulting from the fact that my "distinct" databases actually share much in 
common (a schema, and one of the databases previously had the same name as the 
first but was renamed).

I'd appreciate everyone's thoughts on this... I don't really want either thread 
to have to wait and it doesn't seem like they should have to.

Also, I do plan to upgrade my version of Sqlite to whatever the latest is on 
the site (assuming I don't have the latest version already). I've only put that 
off because I'm dealing with a fairly brittle (and critical) app here, and I 
want to tread lightly.

____
From: Beau Wilkinson
Sent: Tuesday, May 05, 2009 9:02 AM
To: General Discussion of SQLite Database
Subject: RE: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

Thanks for the reply. SQLITE_MISUSE would make sense in the scenario you 
describe. But it is difficult for me to see how I could be calling prepare with 
an unopened or closed connection. I am basically in the process of executing a 
series of statements against an open database that is exclusive to the thread. 
Everything succeeds until a random point. Is the database being closed somehow, 
and I am not realizing it? Or are my threads interacting in a way I've not 
considered?


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, May 04, 2009 5:18 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

Beau Wilkinson <b...@mtllc.us> wrote:
> Nevertheless, I am getting some very puzzling errors. In particular,
> there are cases where sqlite3_prepare() is the first call to cause an
> error, typically SQLITE_MISUSE.

You are passing a bad (never opened, already closed) connection handle
to sqlite3_prepare.

Igor Tandetnik



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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

[Beau Wilkinson]

Thanks for the reply. SQLITE_MISUSE would make sense in the scenario you 
describe. But it is difficult for me to see how I could be calling prepare with 
an unopened or closed connection. I am basically in the process of executing a 
series of statements against an open database that is exclusive to the thread. 
Everything succeeds until a random point. Is the database being closed somehow, 
and I am not realizing it? Or are my threads interacting in a way I've not 
considered?


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, May 04, 2009 5:18 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

Beau Wilkinson <b...@mtllc.us> wrote:
> Nevertheless, I am getting some very puzzling errors. In particular,
> there are cases where sqlite3_prepare() is the first call to cause an
> error, typically SQLITE_MISUSE.

You are passing a bad (never opened, already closed) connection handle
to sqlite3_prepare.

Igor Tandetnik



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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_prepare returns SQLITE_MISUSE

[Beau Wilkinson]

I have inherited a large Sqlite project recently. The project is 
multi-threaded, but has thus far kept Sqlite calls in a single thread. 
Recently, I have complicated this somewhat by adding a second thread that deals 
with other, essentially independent databases. No connections, statements, etc. 
are shared. Each thread operates on independent .DB files.



Nevertheless, I am getting some very puzzling errors. In particular, there are 
cases where sqlite3_prepare() is the first call to cause an error, typically 
SQLITE_MISUSE. Here are more details:



The main thread (i.e. the one that has always interacted with Sqlite) 
periodically closes its DB file, renames it, and moves on to open and write to 
a new database using the same file name as the original file that was just 
renamed. The renaming is done at the file level, using a Windows API call. 
After all of this is complete (from the "close" of the first database to the 
"open" for the new one), a new thread is created by the first thread. This 
second thread then opens the renamed .DB file, and creates a third .DB file 
with yet another name. This thread then uses SELECT and INSERT to copy a subset 
of the second .DB file's data into this third .DB file.



For example, suppose thread 1 is writing Info.db. After some time, it will 
close this database, rename Info.db to be Info.0415Mar090040.db using an API 
call, and then re-create Info.db, open it, and start writing it. After doing 
this, it will create a second thread that will open Info.0415Mar090040, create 
a new database called Info.0415Mar090040.Incident.db, and copy a subset of the 
former to the latter using SQL commands.



So, each database should be used exclusively by a single thread. Assuming each 
thread calls sqlite3_prepare, sqlite3_bind, sqlite3_finalize, etc. in the 
correct sequence, then everything should always work regardless of how the 
threads are time-sliced.



Unfortunately, this is not the behavior I have observed. Instead, things seem 
to work most of the time, but behavior differs from run to run. This makes me 
suspect some kind of threading problem. So, my questions are:



1) Is my basic threading model workable? For example, if there's some kind of 
database name string stored internally that makes (using my example above) 
Info.db and Info.0415Mar090040.db to be really "the same" in some way, even 
after the latter is renamed, then that would explain my problems. In that case, 
I would presumably need to make a renamed copy of the database using Sqlite 
calls.



2) How can sqlite3_prepare() ever return SQLITE_MISUSE for a statement that was 
just successfully finalized? Specifically, how can the following ever reach the 
second call to exit(1)?:



// (Done using pStmt against dbSrc)

rc = _sqlite3_finalize(pStmt);



if(SQLITE_OK != rc)

{

 exit(1);

}



sprintf(szQuery,"SELECT * FROM ACCESSORIES");



rc = _sqlite3_prepare(dbSrc,szQuery, -1, , 0);



if(SQLITE_OK == SQLITE_MISUSE)

{

 exit(1);

 //How can this ever happen? If there was a problem with

 //the finalize call, then the program should have exited then.

 //Does this imply that the threads, and their databases, are

 //really connected somehow?

}



3) How am I supposed to troubleshoot this? Presumably *pStmt was in an 
inappropriate state, and it would be nice to know what state that was. 
Alternately, I guess SQLITE_MISUSE would be the appropriate error is dbSrc were 
actually closed, and I would like to develop the troubleshooting skills to 
detect whether this is the case or not. (It doesn't seem to be in this bug, 
i.e. dbSrc seems to be open).



4) Is there an update I should apply? The version of Sqlite3.h that I am 
including contains the defines SQLITE_VERSION as 3.4.0, but I also seem to be 
missing a few things, such as sqlite_prepare_v2(), sqlite3_threadsafe(), and 
sqlite3_stmt _status(). So, I am not at all sure that I'm using exactly what I 
should be for v3.4.0. (And I think that some of the functions I mentioned might 
help my basic problem.)



Many thanks in advance for your help. To some extent, I can and will continue 
debugging this (and perhaps answering the above questions) on my own. But at 
this point I'm really overwhelmed and far behind my schedule, and really want 
some help with Sqlite. If I start complaining about it, no doubt management 
will request I use something more "full-featured," and I think that would be a 
shame.


The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named. If you 
are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited. If you have received this e-mail in error, please 
immediately notify the sender and delete any copies