Re: [sqlite] Datatype for prices (1,500)
On 11/30/2016 11:42 AM, Richard Hipp wrote: On 11/30/16, Chris Locke <chrisjlo...@gmail.com> wrote: I recently had this problem. Values stored as real values. Had to check records in the database to see if any value had changed, and needed updating. Even though all values in my code were singles, I had bad rounding problems where (as an example) 0.1+2.2 did not equal 2.3 in the database. Aargh. Storing as integers is the way to go. Just to be clear, this is a property of binary floating-point numbers, not a quirk of SQLite. Using the IEEE 64-bit floating point format, there is no way to represent values 0.1, 2.2, and 2.3. The closest you can get are the following: 0.1: 0.155511151231257827021181583404541015625 2.2: 2.20017763568394002504646778106689453125 2.3: 2.29982236431605997495353221893310546875 If you add the first numbers you get: 2.300266453525910037569701671600341796875 which is not equal to the third number. Points to remember: (1) Floating point numbers are usually approximations, not exact values. (2) Never compare two floating point numbers for equality Excellent example. I remember when I was taking a digital fundamentals course, one of the exercise problems was to show the basic binary representation of 0.1 (decimal) using in a "flat binary" number. (For example, 0.1 binary would be 0.5 decimal--half, 0.01 binary would be 0.25 decimal, etc.) So, I set out to find 0.1 by this method. (This was a long time ago.) I found that by the 10th binary decimal place, I was getting tired of attempting this problem. I was young and dumb at the time, so the question was asked the next day in class and it was described as a property of binary floating point concepts. This also lead to a discussion of binary coded decimal. The only thing I would add to point #2 (or maybe add it as point #3) is if you do need to compare floating point numbers make sure to use some sort of tolerance value that is acceptable. Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simple Math Question
On 29 October 2015 at 09:46, SQLite mailing list < sqlite-users at mailinglists.sqlite.org> wrote: > > which I understood to mean, "if you can represent it in decimal, you > can represent it in binary". I didn't think that was true, but there > seemed to be concensus that it was. > The consensus was the other way: "If you can represent it in binary, you can represent it in decimal." -Rowan
[sqlite] Mailing list policy change
On Wed, Oct 28, 2015 at 6:52 PM, General Discussion of SQLite Database < sqlite-users at mailinglists.sqlite.org> wrote: > Effective immediately, the sender email address for mailing list posts > will be elided. All replies must go back to the mailing list itself. > Please reconsider. Not knowing who's talking is untenable. Let each and everyone's SPAM filter take care of it. As someone already mentioned, there are tons of way to harvest past email addresses from archives anyway. --DD
[sqlite] Simple Math Question
On 29 Oct 2015, at 2:09am, SQLite mailing list wrote: > The consensus was the other way: "If you can represent it in binary, you > can represent it in decimal." Well that one is actually true. If you can represent any non-recurring fraction in binary, in decimal it's a non-recurring fraction ending in a 5. Simon.
[sqlite] Simple Math Question
At 23:34 28/10/2015, you wrote: >--- > > Those binary representations can be converted back into precise decimal > > representations, but those decimal representations will not be the > original > > decimal values, because they were translated from decimal strings into > > binary floating-point values and back into decimal strings. > > > -scott > >This explains the deficiency in the SQLite print function, but it doesn't >have to be that way. > >See: Steele, Jr., Guy L., and White, Jon L. How to print floating-point >numbers accurately. In Proc. ACM SIGPLAN ???90 Conf. Prog. Lang. >Design and >Implementation. ACM (White Plains, NY, June 1990), 112?126. ACM SIGPLAN >Noticess 25, 6 (June 1990). > >A retrospective by Steele & White is here: > >http://grouper.ieee.org/groups/754/email/pdfq3pavhBfih.pdf > >I'm not advocating that SQLite add Steele & White's Dragon algorithm, just >pointing out that there are ways to fix the deficiency. > >-- >Doug Currie While it's possible to (somehow) minimize the issues involved with printing a floating-point value (albeit at high cost), the issue of comparing them as is done in the OP is a pretty different beast. There you have to convert a decimal FP target constant to a binary value stored in FP register or memory storage then perform a comparison. And contrary to Simon, I don't think that: >sqlite> CREATE TABLE t(r REAL PRIMARY KEY,t TEXT); >sqlite> INSERT INTO t VALUES (21.0,'twenty one point zero'); >sqlite> INSERT INTO t VALUES (9.2+7.9+0+1.0+1.3+1.6, 'calculation'); should bark for duplicate PK, since the values are hardly equal in practice. (Else SQLite would indeed raise a dup PK error!) BTW and following an entirely distinct thread: I'd rather filter Alexa out myself using my mail client features. jcd at antichoc.net
[sqlite] Mailing list policy change
I think I received about four, which I removed in a couple of seconds. Obviously it is a problem, but I don't think it calls for a change that makes it impossible to see the sender of each message. I always open messages from the SqLite developers sort of by default, for instance, which I can no longer do. Kind regards, Philip Bennefall On 10/28/2015 11:49 PM, SQLite mailing list wrote: > >> Has anybody received email from Alexa since the policy change? I have >> not > I have never received any ... presumably Alexa's MTA (s if more than one) is > blacklisted ... > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > . >
[sqlite] Simple Math Question
On 28 Oct 2015, at 11:23pm, SQLite mailing list wrote: > This can't possibly work. "Fuzzy equality" is not transitive (x is close > enough to y, y is close enough to z, but x is just far enough from z to be > non-equal), which would break any indexing scheme. Oh crumbs. You're right. I didn't think of it like that. Is there a way to do it correctly ? Simon Slavin.
[sqlite] Simple Math Question
On Thu, 29 Oct 2015 10:09:28 +0800 SQLite mailing list wrote: > The consensus was the other way: "If you can represent it in binary, > you can represent it in decimal." Gah, I see now. Thank you for the clarification. --jkl
[sqlite] Simple Math Question
Sorry, I missed out my point: SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> CREATE TABLE t(r REAL PRIMARY KEY,t TEXT); sqlite> INSERT INTO t VALUES (21.0,'twenty one point zero'); sqlite> INSERT INTO t VALUES (9.2+7.9+0+1.0+1.3+1.6, 'calculation'); sqlite> SELECT * FROM t WHERE r>10; 21.0|twenty one point zero 21.0|calculation It is this that worries me. No that both rows are printed as '21.0', but that SQLite did not use slop when checking to see whether the two values were duplicates. I feel that it should have rejected the second INSERT command because of the duplicate key. Obviously an extremely tiny minority of SQLite databases have a UNIQUE KEY on a REAL column. But fixing this may fix other bugs. Simon Slavin.
[sqlite] Mailing list policy change
On 2015-10-28 10:34 PM, SQLite mailing list wrote: > On 10/28/15, SQLite mailing list > wrote: >> This is ridiculous. I know how to handle spam. I can do nothing >> about not knowing who sent these emails. >> > One thing you could do is add a signature line, to tell the rest of us > who you are :-) > I think you've made his point for him precisely. If any of us fail to add such a line, as I never do, then it's a guess. I'm starting to miss Alexa.
[sqlite] Simple Math Question
On 28 Oct 2015, at 10:34pm, SQLite mailing list wrote: > This explains the deficiency in the SQLite print function, but it doesn't > have to be that way. I'm with a previous poster. SQLite is primarily a database system. Its primary jobs are storage and retrieval. It shouldn't really be used to print at all and putting unusual effort into its print functions may not be wise. However, I would support improvement in its floating point calculations, including implementing 'slop' in testing for equality. This is not only for use when expressions include the equal sign, but also for cases where comparing two numbers is done in important internal operations, like checking that primary keys do not include duplicate entries. However, I just tried to create some relevant problems: SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> CREATE TABLE t(r REAL PRIMARY KEY,t TEXT); sqlite> INSERT INTO t VALUES (1,'one'),(2,'two'); sqlite> INSERT INTO t VALUES (1.4,'one point four'),(1.5,'one point five'),(1.6,'one point six');; sqlite> SELECT * FROM t; 1.0|one 2.0|two 1.4|one point four 1.5|one point five 1.6|one point six sqlite> INSERT INTO t VALUES (1.6,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (1.3+0.3,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (1.2+0.2,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (0.2+0.8,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (0.3+0.7,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> SELECT 0.3+0.7, (0.3+0.7) = (0.2+0.8); 1.0|1 sqlite> SELECT 0.3+0.7, (0.3+0.7) - 1, (0.3+0.7) = 1; 1.0|0.0|1 sqlite> SELECT (9.2+7.9+0+1.0+1.3+1.6),(9.2+7.9+0+1.0+1.3+1.6)-21,(9.2+7.9+0+1.0+1.3+1.6)=21.0; 21.0|3.5527136788005e-15|0 As you can see (remembering that 1 = TRUE), SQLite is correctly noticing the problem when it does pure comparisons, whether in internal collations or for other tests for equality. What it's not doing is checking for near equality when printing. And I'm happy with that. Simon Slavin.
[sqlite] Mailing list policy change
Yeah. Let's not admit defeat to a lone a**hole. My spam filter is bored anyway -- let's give it something to do. Eric Sent from my iPhone > On Oct 28, 2015, at 19:12, SQLite mailing list mailinglists.sqlite.org> wrote: > > I agree. This cure is worse than the disease. > > At least for now (from the 2 I got) the Alexa sender address was constant and > can be blacklisted. Regardless of how Alexa got our email addresses, they > have them and can send spam like any spammer. > > -- Darren Duncan > >> On 2015-10-28 2:50 PM, SQLite mailing list wrote: >> This really is awful and unworkable. There a re a few options >> >> 1. maintain things as they are now - and everyone has to add a >> signature line and we need to open every message to see who has sent >> it. There are some posters I make a point of reading and just seeing >> their name in a mail header makes me much more likely to open it. >> >> 2. Somehow configure the system to display the senders name and not >> their email address - seems frought with issues >> >> 3. Go back to the old system and we have one more bit of spam that we >> need to get rid of (something I have already done). >> >> I vote for 3. Alexa was a minor inconvenience and the solution imposed >> is much more of a PITA than she was. >> >> >> >> >> On 28 October 2015 at 20:34, SQLite mailing list >> wrote: >>> On 10/28/15, SQLite mailing list >>> wrote: >>>> >>>> This is ridiculous. I know how to handle spam. I can do nothing >>>> about not knowing who sent these emails. >>> >>> One thing you could do is add a signature line, to tell the rest of us >>> who you are :-) >>> >>> -- >>> D. Richard Hipp >>> drh at sqlite.org > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Mailing list policy change
This really is awful and unworkable. There a re a few options 1. maintain things as they are now - and everyone has to add a signature line and we need to open every message to see who has sent it. There are some posters I make a point of reading and just seeing their name in a mail header makes me much more likely to open it. 2. Somehow configure the system to display the senders name and not their email address - seems frought with issues 3. Go back to the old system and we have one more bit of spam that we need to get rid of (something I have already done). I vote for 3. Alexa was a minor inconvenience and the solution imposed is much more of a PITA than she was. On 28 October 2015 at 20:34, SQLite mailing list wrote: > On 10/28/15, SQLite mailing list > wrote: >> >> This is ridiculous. I know how to handle spam. I can do nothing >> about not knowing who sent these emails. >> > > One thing you could do is add a signature line, to tell the rest of us > who you are :-) > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simple Math Question
On Wed, 28 Oct 2015 17:52:25 + Simon wrote: > On 28 Oct 2015, at 5:08pm, James K. Lowden > wrote: > > > If we accept what you say, above, then why should > > > >> (9.2+7.8+0+3.0+1.3+1.7) > > > > in particular present any problem? There's no division. Each value > > has an exact decimal representation. > > You didn't work it out yourself, did you ? > > 0.2 in binary is 0.0011001100110011... > 0.3 in binary is 0.0100110011001100... > > They both recur at the 1/16th level. 0.7 and 0.8 are, of course, > their complements. Only two tenths don't have problems in binary: > point zero and point five. I didn't work it out. The assertion was > > any base-2 representation right of the decimal should be > > precise to represent in base-10 which I understood to mean, "if you can represent it in decimal, you can represent it in binary". I didn't think that was true, but there seemed to be concensus that it was. Thanks for doing my homework. :-) --jkl P.S., To OFL, I wish the names were preserved in the From, so that mail software preserves the "Simon said, James said" context. It would also be less damaging if the addresses merely mangled with e.g. "-ciao-alexa" inserted. The malware is unlikely to adapt -- one way in which it is *not* like a real virus -- and human beings can easily remove the extra letters. One trick I've used with success is to insert the HTML zero-width space character into the email address. It looks the same, copies and pastes just fine, but scripts scraping a page will copy it verbatim and get a useless address.
[sqlite] Mailing list policy change
On Wed, Oct 28, 2015 at 9:08 PM, SQLite wrote: > > On 28 Oct 2015, at 7:36pm, General Discussion of SQLite Database > wrote: > >> Has anybody received email from Alexa since the policy change? I have >> not > > Nor me. I reliably got one for every post I made for about a week before the > change. This is ridiculous. I know how to handle spam. I can do nothing about not knowing who sent these emails. Dr Hipp, please reconsider.
[sqlite] Simple Math Question
On 10/28/2015 7:25 PM, SQLite mailing list wrote: > On 28 Oct 2015, at 11:23pm, SQLite mailing list mailinglists.sqlite.org> wrote: > >> This can't possibly work. "Fuzzy equality" is not transitive (x is close >> enough to y, y is close enough to z, but x is just far enough from z to be >> non-equal), which would break any indexing scheme. > > Oh crumbs. You're right. I didn't think of it like that. Is there a way to > do it correctly ? None that I know of. Which is probably why no DB engine is doing anything like this. -- Igor Tandetnik
[sqlite] Simple Math Question
On 10/28/2015 6:52 PM, SQLite mailing list wrote: > However, I would support improvement in its floating point calculations, > including implementing 'slop' in testing for equality. This is not only for > use when expressions include the equal sign, but also for cases where > comparing two numbers is done in important internal operations, like checking > that primary keys do not include duplicate entries. This can't possibly work. "Fuzzy equality" is not transitive (x is close enough to y, y is close enough to z, but x is just far enough from z to be non-equal), which would break any indexing scheme. -- Igor Tandetnik
[sqlite] Simple Math Question
On Wed, Oct 28, 2015 at 6:29 PM, SQLite mailing list < sqlite-users at mailinglists.sqlite.org> wrote: > On 10/28/2015 7:25 PM, SQLite mailing list wrote: > >> On 28 Oct 2015, at 11:23pm, SQLite mailing list < >> sqlite-users at mailinglists.sqlite.org> wrote: >> >> This can't possibly work. "Fuzzy equality" is not transitive (x is close >>> enough to y, y is close enough to z, but x is just far enough from z to be >>> non-equal), which would break any indexing scheme. >>> >> >> Oh crumbs. You're right. I didn't think of it like that. Is there a >> way to do it correctly ? >> > > None that I know of. Which is probably why no DB engine is doing anything > like this. It's probably why some have explicit decimal numeric types available. Often in a database engine the overhead of a manual math implementation is not the dominant factor in performance. -scott
[sqlite] Simple Math Question
> > Those binary representations can be converted back into precise decimal > representations, but those decimal representations will not be the original > decimal values, because they were translated from decimal strings into > binary floating-point values and back into decimal strings. > > -scott This explains the deficiency in the SQLite print function, but it doesn't have to be that way. See: Steele, Jr., Guy L., and White, Jon L. How to print floating-point numbers accurately. In Proc. ACM SIGPLAN ?90 Conf. Prog. Lang. Design and Implementation. ACM (White Plains, NY, June 1990), 112?126. ACM SIGPLAN Notices 25, 6 (June 1990). A retrospective by Steele & White is here: http://grouper.ieee.org/groups/754/email/pdfq3pavhBfih.pdf I'm not advocating that SQLite add Steele & White's Dragon algorithm, just pointing out that there are ways to fix the deficiency. e -- Doug Currie doug.currie at gmail.com
[sqlite] Simple Math Question
>> (9.2+7.8+0+3.0+1.3+1.7) >in particular present any problem? There's no division. Each value >has an exact decimal representation. I'm prepared to assert that any >permutation of their sums also has an exact decimal representation. >Therefore they should have an exact binary representation, too. Not true. They don't have an exact binary representation. If you check these numbers here.. http://www.h-schmidt.net/FloatConverter/IEEE754.html You'll find 9.2 -> 9.19809265137 7.8 -> 7.80190734863 and so on. So adding these numbers doesn't generate the same answer as doing it in base10 maths Andy Ling --- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http://www.mimecast.com ---
[sqlite] Mailing list policy change
> Has anybody received email from Alexa since the policy change? I have > not I have never received any ... presumably Alexa's MTA (s if more than one) is blacklisted ...
[sqlite] Mailing list policy change
On 10/28/15, SQLite mailing list wrote: > > This is ridiculous. I know how to handle spam. I can do nothing > about not knowing who sent these emails. > One thing you could do is add a signature line, to tell the rest of us who you are :-) -- D. Richard Hipp drh at sqlite.org
[sqlite] Mailing list policy change
I agree. This cure is worse than the disease. At least for now (from the 2 I got) the Alexa sender address was constant and can be blacklisted. Regardless of how Alexa got our email addresses, they have them and can send spam like any spammer. -- Darren Duncan On 2015-10-28 2:50 PM, SQLite mailing list wrote: > This really is awful and unworkable. There a re a few options > > 1. maintain things as they are now - and everyone has to add a > signature line and we need to open every message to see who has sent > it. There are some posters I make a point of reading and just seeing > their name in a mail header makes me much more likely to open it. > > 2. Somehow configure the system to display the senders name and not > their email address - seems frought with issues > > 3. Go back to the old system and we have one more bit of spam that we > need to get rid of (something I have already done). > > I vote for 3. Alexa was a minor inconvenience and the solution imposed > is much more of a PITA than she was. > > > > > On 28 October 2015 at 20:34, SQLite mailing list > wrote: >> On 10/28/15, SQLite mailing list >> wrote: >>> >>> This is ridiculous. I know how to handle spam. I can do nothing >>> about not knowing who sent these emails. >>> >> >> One thing you could do is add a signature line, to tell the rest of us >> who you are :-) >> >> -- >> D. Richard Hipp >> drh at sqlite.org
[sqlite] Simple Math Question
On Wed, Oct 28, 2015 at 3:52 PM, SQLite mailing list < sqlite-users at mailinglists.sqlite.org> wrote: > On 28 Oct 2015, at 10:34pm, SQLite mailing list < > sqlite-users at mailinglists.sqlite.org> wrote: > > This explains the deficiency in the SQLite print function, but it doesn't > > have to be that way. > > I'm with a previous poster. SQLite is primarily a database system. Its > primary jobs are storage and retrieval. It shouldn't really be used to > print at all and putting unusual effort into its print functions may not be > wise. > > However, I would support improvement in its floating point calculations, > including implementing 'slop' in testing for equality. This is not only > for use when expressions include the equal sign, but also for cases where > comparing two numbers is done in important internal operations, like > checking that primary keys do not include duplicate entries. > IMHO, this kind of thing can be subtle and full of bugs. For purposes of a value literally typed as a float, I think using the IEEE 754 value as a blob is the right thing to do. In addition, it would be reasonable to have a function for comparison including an epsilon. That wouldn't make sense for indices, though, because of the problem where two values may not be within epsilon of each other, but they may be within epsilon of a third, so insert success becomes order dependent (in one order, 1 of 3 succeeds, in the other order 2 of 3 succeed). Actually, I think I'd be willing to forbid UNIQUE plus REAL entirely :-). -scott (shess at google.com)
[sqlite] Casting ctype functions' arguments
Hi, everyone.
I've been auditing the OpenBSD codebase for calls to ctype functions
with potentially signed chars. This is undefined on some platforms. I
found a number of instances in Sqlite, so I cloned your repo and ran my
script on it.
Here's the relevant CERT entry:
https://www.securecoding.cert.org/confluence/display/c/STR37-C.+Arguments+to+character-handling+functions+must+be+representable+as+an+unsigned+char
The below diff was generated by Coccinelle, an extremely useful
automated tool, so some of the formatting may be odd and mistakes are
possible.
Let me know what you think.
Thanks,
Michael
Index: autoconf/tea/win/nmakehlp.c
==
--- autoconf/tea/win/nmakehlp.c
+++ autoconf/tea/win/nmakehlp.c
@@ -603,15 +603,15 @@
sp = fopen(substitutions, "rt");
if (sp != NULL) {
while (fgets(szBuffer, cbBuffer, sp) != NULL) {
char *ks, *ke, *vs, *ve;
ks = szBuffer;
- while (ks && *ks && isspace(*ks)) ++ks;
+ while (ks && *ks && isspace((unsigned char)*ks)) ++ks;
ke = ks;
- while (ke && *ke && !isspace(*ke)) ++ke;
+ while (ke && *ke && !isspace((unsigned char)*ke)) ++ke;
vs = ke;
- while (vs && *vs && isspace(*vs)) ++vs;
+ while (vs && *vs && isspace((unsigned char)*vs)) ++vs;
ve = vs;
while (ve && *ve && !(*ve == '\r' || *ve == '\n')) ++ve;
*ke = 0, *ve = 0;
list_insert(, ks, vs);
}
Index: ext/fts1/fts1.c
==
--- ext/fts1/fts1.c
+++ ext/fts1/fts1.c
@@ -203,17 +203,17 @@
** tokenizer-generated tokens rather than doing its own local
** tokenization.
*/
/* TODO(shess) Is __isascii() a portable version of (c&0x80)==0? */
static int safe_isspace(char c){
- return (c&0x80)==0 ? isspace(c) : 0;
+ return (c&0x80)==0 ? isspace((unsigned char)c) : 0;
}
static int safe_tolower(char c){
- return (c&0x80)==0 ? tolower(c) : c;
+ return (c&0x80)==0 ? tolower((unsigned char)c) : c;
}
static int safe_isalnum(char c){
- return (c&0x80)==0 ? isalnum(c) : 0;
+ return (c&0x80)==0 ? isalnum((unsigned char)c) : 0;
}
typedef enum DocListType {
DL_DOCIDS, /* docids only */
DL_POSITIONS, /* docids + positions */
Index: ext/fts1/simple_tokenizer.c
==
--- ext/fts1/simple_tokenizer.c
+++ ext/fts1/simple_tokenizer.c
@@ -136,11 +136,11 @@
for(ii=0; iipCurrent[ii];
-c->zToken[ii] = (unsigned char)ch<0x80 ? tolower(ch) : ch;
+c->zToken[ii] = (unsigned char)ch<0x80 ? tolower((unsigned char)ch) :
ch;
}
c->zToken[n] = '\0';
*ppToken = c->zToken;
*pnBytes = n;
*piStartOffset = (int) (c->pCurrent-c->pInput);
Index: ext/misc/amatch.c
==
--- ext/misc/amatch.c
+++ ext/misc/amatch.c
@@ -814,14 +814,14 @@
int nKey = (int)strlen(zKey);
int nStr = (int)strlen(zStr);
int i;
if( nStr0 && isspace(zOut[i-1]) ){ i--; }
Index: mptest/mptest.c
[sqlite] Problem : SQLite Database error
Heya guys, iv got a database which was made my a program called BluePhoneElite, its a Mac OS X piece of software which pairs with a mobile phone over bluetooth to allow messages and calls to be managed from the computer... awesome piece of software untill it breaks. Somehow the database has become corrupted, but my experience with programming and SQL is kinda limited, so turn to you guys... There are two databases, one for messages (text's) and the other for calls ! both are .sqlite extensions (e.g messages.sqlite & calls.sqlite) Iv managed to open them in SQLite Expert Personal 1.7.31 and look within the data, this is where it gets annoying, everything seems to look okay to me the data is perfectly readable (aka, no random symbols or things which look out of place)... But, when i ask SQLite Personal to check the integreity of the databases i get this; Messages.sqlite *** in database main *** On tree page 7686 cell 67: invalid page number 7692 On tree page 7686 cell 67: Child page depth differs On tree page 7686 cell 68: invalid page number 7693 On tree page 7686 cell 69: invalid page number 7694 On tree page 7686 cell 70: invalid page number 7701 On tree page 7686 cell 71: invalid page number 7702 On tree page 7686 cell 72: invalid page number 7695 On tree page 7686 cell 73: invalid page number 7697 On tree page 7686 cell 74: invalid page number 7700 On tree page 7686 cell 75: invalid page number 7696 On tree page 7686 cell 76: invalid page number 7698 On page 7686 at right child: invalid page number 7699 Calls.sqlite *** in database main *** On page 273 at right child: invalid page number 361 Suffice to say these messages dont mean much to me, anyone else have any ideas ? Also, when i scroll through the data, i keep getting alert messages popping up saying "The database disk image is malformed" and the details of this error just says Exception message: The database disk image is malformed Exception class: ESQLiteException Date/Time: 2008-10-11 23:02:35.734 Im kinda desperate to get these databases back into a working state, can anyone help ? Regards Aaron ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue when enabling fts3 in sqlite3
Call sqlite3_close() first. If it returns SQLITE_BUSY, *then* iterate over the
statements list and close what statements remain. sqlite3_close() will cleanup
internal prepared statements for you and eliminate the error you're seeing.
From: junkJon <[EMAIL PROTECTED]>
Sent: Friday, September 26, 2008 6:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Issue when enabling fts3 in sqlite3
Oh, I was using the amalgamate source code 3.6.3 ... so I believe the problem
was related to what Roger mentioned below.. Thanks though for the input.
-Jon
P Kishor-3 wrote:
>
> On 9/26/08, junkJon wrote:
>>
>> Hello all,
>>
>> I had a small little database (without fts3) that I setup that worked
>> perfectly. I was reading into full-text searching and I realized it
>> would
>> be cool if I could use it for my database, so I decided to implement it.
>> However, once I setup fts3 by enabling (SQLITE_ENABLE_FTS3) in the build
>> script (I am using the amalgamate source) and converted all my VARCHAR
>> to
>> TEXT in my database,
>
> You don't have to...
>
>> I get segmentation faults now... I can't figure out
>> what the problem could be.. It bombs out when I am trying to close my
>> Db...
>> it has something to do with the full-text searching code :(
>
> What version of the software are you using? I got segfaults using
> 3.6.1 (albeit via Perl). Upgraded to 3.6.2 and all is well.
>
>
>>
>> Any help would be greatly appreciate...
>>
>>
>> here is my sample DB:
>>
>> ORIGINAL TABLE:
>> --
>> CREATE VIRTUAL TABLE file USING FTS3(
>> file_id INTEGER NOT NULL PRIMARY KEY
>> AUTOINCREMENT,
>> sha2 TEXT NOT NULL,
>> )
>>
>> NEW ONE:
>>
>> CREATE TABLE file (
>> file_id INTEGER NOT NULL PRIMARY KEY
>> AUTOINCREMENT,
>> sha2 BLOB NOT NULL,
>> )
>>
>>
>>
>>
>> here is the stack trace:
>>
>> Program received signal SIGSEGV, Segmentation fault.
>> [Switching to Thread 0xb7c536c0 (LWP 12890)]
>> 0x080b18f9 in sqlite3_finalize ()
>> Current language: auto; currently asm
>> (gdb) bt
>> #0 0x080b18f9 in sqlite3_finalize ()
>> #1 0x080f2caa in fulltext_vtab_destroy ()
>> #2 0x080f3c55 in fulltextDisconnect ()
>> #3 0x080e24a0 in sqlite3VtabUnlock ()
>> #4 0x080e24cf in sqlite3VtabClear ()
>> #5 0x080c4e32 in sqlite3DeleteTable ()
>> #6 0x080ca878 in sqlite3SchemaFree ()
>> #7 0x080c4ac1 in sqlite3ResetInternalSchema ()
>> #8 0x080ed344 in sqlite3_close ()
>> #9 0x0808d4cf in MySqlite::Close ()
>> #10 0x0808d4eb in MySqlite::~CSqliteDb ()
>> #11 0x0808ccbd in MySqlite::~CSqlitePreparedConnection ()
>>
>>
>> here is how I close my DB:
>>
>> void MySqlite::Close()
>> {
>> sqlite3_stmt *stmt;
>> while((stmt = sqlite3_next_stmt(m_db, 0)) != 0)
>> {
>> sqlite3_finalize(stmt);
>> }
>>
>> sqlite3_close(m_db);
>> m_db = NULL;
>> }
>>
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Issue-when-enabling-fts3-in-sqlite3-tp19698347p19698347.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
>>
>
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
--
View this message in context:
http://www.nabble.com/Issue-when-enabling-fts3-in-sqlite3-tp19698347p19698866.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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re: Re: Cannot we reuse an sqlite3_stmt to insert multiple rows ?
Hello, Le 22-nov.-06 à 21:08, Igor Tandetnik a écrit : Pyramide-Ingenierie Developer List <[EMAIL PROTECTED]> wrote: I try to use "sqlite3_reset()" and/or "sqlite3_clear_bindings()" before "re-binds" and "re-step" but I always get an error 21 (SQLITE_MISUSE) when I am calling them on a already used sqlite3_stmt... sqlite3_reset should work. You are doing something wrong. Show a small complete sample that reproduces the problem. In fact, when I only use sqlite3_reset() before making the new binds to insert a new row, I don't get any error from sqlite3_reset() but I get a SQLITE_RANGE error at the first sqlite3_bind_xxx call I make (see the sequence of sqlite3_calls() below)... As if I had to "clear the bindings" made for the first "insert"... There is something wrong with your indexes. Show some code. You don't need sqlite3_clear_bindings, though the call is harmless. You can bind the same parameter multiple times - the newer binding simply overwrites the older. You must indeed call sqlite3_reset after you call sqlite3_step and before you can rebind parameters. Igor Tandetnik You perfectly right ! I do not sleep enough (you didn't say it but it is) ! I was well calling "sqlite3_reset", resetting the status that prevent me to call "sqlite3_step" after the last row has been fetched but I completely forgot to reset the binding index, so it was starting at "N +1" at the second row ! So why the SQLITE_RANGE error... Thanks for your help and for the interesting mail thread pointed out by Dennis Cote... Luc Demarche
[sqlite] Rép : Re: Cannot we reuse an sqlite3_stmt to insert multiple rows ?
Hello,
Le 22-nov.-06 à 20:00, Igor Tandetnik a écrit :
I try to use "sqlite3_reset()" and/or "sqlite3_clear_bindings()"
before "re-binds" and "re-step" but I always get an error 21
(SQLITE_MISUSE) when I am calling them on a already used
sqlite3_stmt...
sqlite3_reset should work. You are doing something wrong. Show a
small complete sample that reproduces the problem.
I checked a bit too fast before sending the first mail... Based on
your mail, I retried with only sqlite3_reset() and the results are a
bit different...
In fact, when I only use sqlite3_reset() before making the new binds
to insert a new row, I don't get any error from sqlite3_reset() but I
get a SQLITE_RANGE error at the first sqlite3_bind_xxx call I make
(see the sequence of sqlite3_calls() below)... As if I had to "clear
the bindings" made for the first "insert"...
When I carefully re-read the "sqlite3.h" file regarding
sqlite3_reset, it says "Any SQL statement variables that had values
bound to them using the sqlite3_bind_*() API retain their values...
That's why I was trying a "cocktail" using sqlite3_clear_bindings()
after sqlite3_reset()...
sqlite3_prepare("INSERT INTO Book (author_fk,title) VALUES
(?,?)")-> SQLITE_OK
// insert first row
sqlite3_reset() -> SQLITE_OK
sqlite3_bind_xxx() -> SQLITE_OK
sqlite3_bind_xxx() -> SQLITE_OK
sqlite3_step-> SQLITE_DONE
// insert second row
sqlite3_reset() -> SQLITE_OK
sqlite3_bind_xxx() -> SQLITE_RANGE
sqlite3_bind_xxx()
sqlite3_step
But I confirm that if I am using only sqlite3_clear_bindings()
instead of sqlite3_reset(), sqlite3_clear_bindings() produces an
SQLITE_MISUSE error at the second call, as below:
sqlite3_prepare("INSERT INTO Book (author_fk,title) VALUES
(?,?)")-> SQLITE_OK
// insert first row
sqlite3_clear_bindings()-> SQLITE_OK
sqlite3_bind_xxx() -> SQLITE_OK
sqlite3_bind_xxx() -> SQLITE_OK
sqlite3_step-> SQLITE_DONE
// insert second row
sqlite3_clear_bindings()-> SQLITE_MISUSE
sqlite3_bind_xxx()
sqlite3_bind_xxx()
sqlite3_step
I will try to reproduce this problem tomorrow out of the C++
SQLiteLibrary that is full of objects just to isolate the sqlite3
calls...
Regards,
Luc Demarche
[sqlite] Cannot we reuse an sqlite3_stmt to insert multiple rows ?
Hello, I am using SQLite 3.3.8 library on Mac OS X using XCode 2.4.1 (gcc 4.0)... I try to compile only once an SQL instruction like "INSERT INTO Book (author_fk,title,date,time,price,created) VALUES (?,?,?,?,?,?);" and insert multiple rows through a (sqlite3_bind_xxx / sqlite3_bind_step) loop but I everytime got errors... Does looping through step is only available for "SELECT" ? Cannot we think that a compiled SQL statement could be reuse many times as long as only the bindings change ? I try to use "sqlite3_reset()" and/or "sqlite3_clear_bindings()" before "re-binds" and "re-step" but I always get an error 21 (SQLITE_MISUSE) when I am calling them on a already used sqlite3_stmt... Here is a summary of what I try to do: sqlite3_prepare // insert first row sqlite3_reset() sqlite3_clear_bindings() sqlite3_bind_xxx() sqlite3_bind_xxx() ... sqlite3_bind_xxx() sqlite3_step // insert second row sqlite3_reset() sqlite3_clear_bindings() sqlite3_bind_xxx() sqlite3_bind_xxx() ... sqlite3_bind_xxx() sqlite3_step etc... I do not know why functions like "sqlite3_reset()" and "sqlite3_clear_bindings()" exist if it is not for doing this ? Regards, Luc Demarche - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Undefined symbols with libsqlite3.a 3.3.8 when just building the shell with Xcode 2.4...
Hello, I just try building the standard SQLite 3.3.8 shell using a really simple Xcode projet just having the files "shell.c", "sqlite3.h" and "libsqlite3.a". The last two were obtained builing the sqlite sources as told by the readme: tar xf sqlite-3.3.8.tar mkdir build cd build/ ../sqlite-3.3.8/configure make From this, I extract the "libsqlite3.a" hidden in the ".libs" folder (why is it so hidden) and the "sqlite3.h" file... If I build using "Development" mode, I got no problem (it is dynamic linking, so it should make a runtime error) but with "Deployment" mode, I got an error: /usr/bin/ld: warning prebinding disabled because of undefined symbols /usr/bin/ld: Undefined symbols: _sqlite3_enable_load_extension _sqlite3_load_extension /Users/luc/Developments/Projects_BSD/SQLite_338/shellWithLib/build/ shellWithLib.build/Deployment/shellWithLib.build/Objects-normal/ppc/ shell.o reference to undefined _sqlite3_enable_load_extension /Users/luc/Developments/Projects_BSD/SQLite_338/shellWithLib/build/ shellWithLib.build/Deployment/shellWithLib.build/Objects-normal/ppc/ shell.o reference to undefined _sqlite3_load_extension collect2: ld returned 1 exit status Looking at the Makefile produced by "configure", it seems the file "loadext.c" is correctly added to the library... And I see nowhere that the symbol SQLITE_OMIT_LOAD_EXTENSION could be defined... Regards, Luc Demarche .. Luc Demarche [EMAIL PROTECTED] Mac OS Software Developer Pyramide Ingenierie sprl Tel: +32 87 292120 188 rue de Liege Fax: +32 87 292129 B-4800 VerviersMail: [EMAIL PROTECTED] ..
