[sqlite] .genfkey in 3.6.12
Hi, I noticed that the (recommended) amalgation version 3.6.12 does not contain the new .genfkey functionality, while the (not recommended) full version does. Is this on purpose? -- Best, Frank. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .genfkey in 3.6.12
Hi, I noticed that the (recommended) amalgation version 3.6.12 does not contain the new .genfkey functionality, while the (not recommended) full version does. Is this on purpose? No, that was a mistake. It has now been fixed. Please download the sqlite-amalgamation-3.6.12.tar.gz again and rebuild. Yep, works like a charm, tnx! -- Best, Frank. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] csv files with unquoted data containing comma's
L.S. Comma seperated files tend to become a bit too seperated when the field data contains the character used for seperation while not being quoted ;) Sqlite 3.5.4 uses shell.c::needsCsvQuote() to determine whether or not to quote the field contents, but it doesn't check for the comma.. the following patch changes that: --- shell.c.orig2007-11-30 02:28:11.0 +0100 +++ shell.c 2008-01-30 11:29:29.0 +0100 @@ -441,7 +441,7 @@ static const char needCsvQuote[] = { 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, - 1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, + 1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, I understand a number of open tickets exist related to csv-behaviour, so the above might be combined with those (the particular problem didn't seem to be mentioned earlier, though). -- Best, Frank. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] csv files with unquoted data containing comma's
Dennis, Your change assumes the separator is always a comma. SQLite does not make that assumption. I'll look into it tomorrow, but was under the impression that: * when using .mode csv, the separator _always_ is a comma * when one wants a different separator, one has to use .separator ... in combination with .mode list needCsvQuote() is called from output_csv(), which is called when in csv-mode only -- Best, Frank. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] csv files with unquoted data containing comma's
Ken / Dennis, My version of output_Csv (3.5.4) is missing conditional check. static void output_csv(struct callback_data *p, const char *z, int bSep){ FILE *out = p-out; if( z==0 ){ fprintf(out,%s,p-nullvalue); }else{ int i; for(i=0; z[i]; i++){ if( needCsvQuote[((unsigned char*)z)[i]] ){ i = 0; break; } } Yep, as stated earlier, that's the same version I'm using here and I'm also looking at: for(i=0; z[i]; i++){ if( needCsvQuote[((unsigned char*)z)[i]] ){ i = 0; break; } Maybe your runtime library id different than the source code? It seems this got fixed here: http://www.sqlite.org/cvstrac/filediff?f=sqlite/src/shell.cv1=1.170v2=1.171 Looks like a done deal, will try this patch instead. -- Best, Frank. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] CONFIG_FPE_FASTFPE and the text representation of a float
L.S. Here's the patch for those interested: --- sqlite-3.4.2-orig/src/printf.c 2007-06-28 14:46:19.0 +0200 +++ sqlite-3.4.2/src/printf.c 2007-10-08 11:56:49.0 +0200 @@ -158,7 +158,11 @@ static int et_getdigit(LONGDOUBLE_TYPE *val, int *cnt){ int digit; LONGDOUBLE_TYPE d; +#ifdef SQLITE_FPE_FASTFPE + if( (*cnt)++ = 10 ) return '0'; +#else if( (*cnt)++ = 16 ) return '0'; +#endif /* SQLITE_FPE_FASTFPE */ digit = (int)*val; d = digit; digit += '0'; and diff -u -r sqlite-3.4.2-orig/src/vdbemem.c sqlite-3.4.2/src/vdbemem.c --- sqlite-3.4.2-orig/src/vdbemem.c 2007-06-28 14:46:19.0 +0200 +++ sqlite-3.4.2/src/vdbemem.c 2007-10-08 11:58:07.0 +0200 @@ -217,7 +217,11 @@ sqlite3_snprintf(NBFS, z, %lld, pMem-u.i); }else{ assert( fg MEM_Real ); +#ifdef SQLITE_FPE_FASTFPE +sqlite3_snprintf(NBFS, z, %!.9g, pMem-r); +#else sqlite3_snprintf(NBFS, z, %!.15g, pMem-r); +#endif /* SQLITE_FPE_FASTFPE */ } pMem-n = strlen(z); pMem-z = z; FYI, the (latest?) 2.6 kernels seem to have ditched FASTFPE completely, so the above is probably a 2.4 - issue only. -- Best, Frank. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] CONFIG_FPE_FASTFPE and the text representation of a float
L.S. Ouch, bad copypaste, obviously log(2^33) ~= 9.934 = 10 digits Drh, can I wrap up this stuff and send you the patch or did other locations come to mind that need checking? -- Best, Frank. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] CONFIG_FPE_FASTFPE and the text representation of a float
L.S. As per earlier posts: I'm using Sqlite on an Arm architecture without FP-unit running a kernel that has FASTFPE built in. After solving the mixed-endian issue, it turns out there's yet another problem when one is looking at floating point numbers in a text representation.. A number like 1.24, which is not exactly representable, will be presented still by Sqlite as '1.24', but the underlying 'magic' fails in the situation of FASTFPE, simply because the assumptions made on accuracy are obviously 'wrong' then ;( * a regular 64 bit float has 52+1 bits for the mantissa and thus an estimated number of accurate digits of log(2^53) ~= 15.955 = 16 digits * a fastfpe float has 32+1 bits for the mantissa and thus an estimated number of accurate digits of log(2^33) ~= 15.955 = 10 digits So, I've been trying to find the locations for this 'magic' and came up with the following two: src/printf.c:158 contains et_getdigit() with line 161: if( (*cnt)++ = 16 ) return '0'; in which I'd need to change the 16 into 10. src/vdbemem.c:201 contains sqlite3VbdeMemStringify() with line 220: sqlite3_snprintf(NBFS, z, %!.15g, pMem-r); in which I'd need to change the 15 into 9 Changing these values seems to work out fine (and it returns '1.24' again instead of something like '1.2399962718'), but since I'm not thát known with sqlite's source, I'm wondering if there are more locations that have some hardcoded notion on float-accuracy... DRH, any hints...? I'll wrap the changes up in a patch using some define like SQLITE_FASTFPE or something, unless DRH has a different idea ;) -- Best, Frank. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite's exposure to floating point errors under SQLITE_MIXED_ENDIAN_64BIT_FLOAT
I'm unsure how D. Richard Hipp would prefer to handle this, I reported these findings directly to him before posting here, but got no response ;( My response was http://www.sqlite.org/cvstrac/chngview?cn=4339 :-) That serves me for not paying close attention to the changes in cvs ;) Thanks. -- Best, Frank. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Delay during insert - Was: [sqlite] How to cross compile sqlite-3.2.7 for arm9 s3c2410?
Hi Ron, I have noticed that when I create a database and do a simple INSERT, (using the sqlite3 binary), there is an appreciable delay (1-2 seconds) before sqlite3 returns to the prompt. When doing SELECT there is no delay. Mmm, can't say that I have seen any strange delays on the arm platform, albeit that it obviously *is* a bit slower than i386 ;). But seriously, given that we're talking about a newly created db, a fairly simple table with no 'weird' triggers etc, then a plain insert is not that much work and should return almost instantly. It might be due to some locking issues, but you didn't mention accessing the db from multiple processes / threads. Also, your filesystem / kernel might have issues (re)opening the file readwrite. You could use something like strace to see what exactly is happening during the delay, given that this tool is available on the platform, but if it isn't you could built it. I have not noticed this on my Windows or Linux machines (x86). Me neither. Is this possibly because of not using transactions? Not likely. The device is storing the database on a hard-disk, so it shouldn't have to do with paged RAM or something. I wouldn't jump to the same conclusion when it was anything else than a simple insert.., but anyway. The only additional thing that comes to mind is that you were seeing a select coming from cache and that the insert triggered a powerup of the drive..? Again, something like strace could tell you what's happening. Thanks for any suggestions, and thanks again for the good instructions! You're welcome, good luck ! -- Best, Frank.
Re: [sqlite] How to cross compile sqlite-3.2.7 for arm9 s3c2410?
I have downloaded sqlite-3.2.7.tar.gz from Internet, but I do not know how to compile it on s3c2410(linux 2.6, arm9, samsung)? There's some info in the wiki on it, but basically the steps are: 1. change the configure script (see attached patch for 3.2.6) 2. configure with something like this (in my case an arm sa1110): cd $PATH/sqlite-3.2.6 CC=arm-sa1100-linux-gnu-gcc CXX=arm-sa1100-linux-gnu-g++ \ CFLAGS=-I$TOOLCHAIN_BASE/include BUILD_CC=gcc \ ./configure --host=i686-pc-linux --target=arm-sa1100-linux \ --disable-shared --disable-tcl --prefix=$PATH_INSTALL 3. change the Makefile a bit, setting 'BCC' to your regular compiler and (if you want) add the static build flag, for example doing something like this: mv Makefile Makefile.sed cat Makefile.sed | sed 's/^BCC.*/BCC = gcc/' | sed 's/^LTLINK.*/ -all-static/' Makefile rm Makefile.sed 4. make make install Mind you, depending on which core you have and whether you want floating point, you might find the other patch attached usefull as well. It is from an earlier posting of myself: In order to wrap this up: apparently there's a feature / bug (choose one) in any ARM core earlier than v5 due to which a float will be stored in big endian quad order. The processor in this particular case is an SA1110, which is default little endian while having a v4 core. (and thus is 'swapping' the quads). -- Best, Frank. --- configure_orig 2005-09-19 10:46:30.0 +0200 +++ configure 2005-09-19 10:46:34.0 +0200 @@ -19209,11 +19209,11 @@ ac_link='$CC -o conftest$ac_exeext $CFLAGS $CPPFLAGS $LDFLAGS conftest.$ac_ext $LIBS 5' ac_compiler_gnu=$ac_cv_c_compiler_gnu - if test $cross_compiling = yes; then -{ { echo $as_me:$LINENO: error: unable to find a compiler for building build tools 5 -echo $as_me: error: unable to find a compiler for building build tools 2;} - { (exit 1); exit 1; }; } - fi +# if test $cross_compiling = yes; then +#{ { echo $as_me:$LINENO: error: unable to find a compiler for building build tools 5 +#echo $as_me: error: unable to find a compiler for building build tools 2;} +# { (exit 1); exit 1; }; } +# fi BUILD_CC=$CC default_build_cflags=$CFLAGS else @@ -20165,11 +20165,11 @@ echo $ECHO_N checking for $dir/include/readline.h... $ECHO_C 6 if eval test \\${$as_ac_File+set}\ = set; then echo $ECHO_N (cached) $ECHO_C 6 -else - test $cross_compiling = yes - { { echo $as_me:$LINENO: error: cannot check for file existence when cross compiling 5 -echo $as_me: error: cannot check for file existence when cross compiling 2;} - { (exit 1); exit 1; }; } +#else +# test $cross_compiling = yes +# { { echo $as_me:$LINENO: error: cannot check for file existence when cross compiling 5 +#echo $as_me: error: cannot check for file existence when cross compiling 2;} +# { (exit 1); exit 1; }; } if test -r $dir/include/readline.h; then eval $as_ac_File=yes else @@ -20191,11 +20191,11 @@ echo $ECHO_N checking for $dir/include/readline/readline.h... $ECHO_C 6 if eval test \\${$as_ac_File+set}\ = set; then echo $ECHO_N (cached) $ECHO_C 6 -else - test $cross_compiling = yes - { { echo $as_me:$LINENO: error: cannot check for file existence when cross compiling 5 -echo $as_me: error: cannot check for file existence when cross compiling 2;} - { (exit 1); exit 1; }; } +#else +# test $cross_compiling = yes +# { { echo $as_me:$LINENO: error: cannot check for file existence when cross compiling 5 +#echo $as_me: error: cannot check for file existence when cross compiling 2;} +# { (exit 1); exit 1; }; } if test -r $dir/include/readline/readline.h; then eval $as_ac_File=yes else --- vdbeaux.c_orig 2005-09-17 19:48:56.0 +0200 +++ vdbeaux.c 2005-09-19 10:55:50.0 +0200 @@ -1509,7 +1509,11 @@ } len = i = sqlite3VdbeSerialTypeLen(serial_type); while( i-- ){ - buf[i] = (v0xFF); + if( serial_type==7 ){ +buf[(i+4)%8] = (v0xFF); + }else{ +buf[i] = (v0xFF); + } v = 8; } return len; @@ -1568,28 +1572,20 @@ pMem-flags = MEM_Int; return 6; } -case 6: /* 8-byte signed integer */ +case 6: { /* 8-byte signed integer */ + u64 x = (buf[0]24) | (buf[1]16) | (buf[2]8) | buf[3]; + u32 y = (buf[4]24) | (buf[5]16) | (buf[6]8) | buf[7]; + x = (x32) | y; + pMem-i = *(i64*)x; + pMem-flags = MEM_Int; + return 8; +} case 7: { /* IEEE floating point */ - u64 x; - u32 y; -#ifndef NDEBUG - /* Verify that integers and floating point values use the same - ** byte order. The byte order differs on some (broken) architectures. - */ - static const u64 t1 = ((u64)0x3ff0)32; - assert( 1.0==*(double*)t1 ); -#endif - - x = (buf[0]24) | (buf[1]16) | (buf[2]8) | buf[3]; - y = (buf[4]24) | (buf[5]16) | (buf[6]8) | buf[7]; + u64 x = (buf[4]24) | (buf[5]16) | (buf[6]8)
Re: [sqlite] Possible bug regarding endiannes and realstorageclass (sqlite3)
L.S. In order to wrap this up: apparently there's a feature / bug (choose one) in any ARM core earlier than v5 due to which a float will be stored in big endian quad order. The processor in this particular case is an SA1110, which is default little endian while having a v4 core. (and thus is 'swapping' the quads). [Doug Currie] So for your ARM FP library, the code goes from For the part that reads the data it's just semantics, really, but I'm using the patch now (against v3.2.3): --- vdbeaux.c_orig 2005-08-22 21:32:53.0 +0200 +++ vdbeaux.c 2005-08-22 21:39:46.0 +0200 @@ -1649,7 +1649,11 @@ } len = i = sqlite3VdbeSerialTypeLen(serial_type); while( i-- ){ - buf[i] = (v0xFF); + if( serial_type==7 ){ +buf[(i+4)%8] = (v0xFF); + }else{ +buf[i] = (v0xFF); + } v = 8; } return len; @@ -1708,18 +1712,20 @@ pMem-flags = MEM_Int; return 6; } -case 6: /* 8-byte signed integer */ -case 7: { /* IEEE floating point */ +case 6: { /* 8-byte signed integer */ u64 x = (buf[0]24) | (buf[1]16) | (buf[2]8) | buf[3]; u32 y = (buf[4]24) | (buf[5]16) | (buf[6]8) | buf[7]; x = (x32) | y; - if( serial_type==6 ){ -pMem-i = *(i64*)x; -pMem-flags = MEM_Int; - }else{ -pMem-r = *(double*)x; -pMem-flags = MEM_Real; - } + pMem-i = *(i64*)x; + pMem-flags = MEM_Int; + return 8; +} +case 7: { /* IEEE floating point */ + u64 x = (buf[4]24) | (buf[5]16) | (buf[6]8) | buf[7]; + u32 y = (buf[0]24) | (buf[1]16) | (buf[2]8) | buf[3]; + x = (x32) | y; + pMem-r = *(double*)x; + pMem-flags = MEM_Real; return 8; } default: { [D. Richard Hipp] The code shown was for reading the database. You'll also need to find and fix the spot where the database is written Obviously, but thanks for the heads-up anyway ;);) The patch above includes that. -- Best, Frank.
[sqlite] Possible bug regarding endiannes and real storage class (sqlite3)
L.S. It looks like there's something wrong with the endiannes when using sqlite3 (v3.2.2) on an ARM architecture (SA1100 nanoboard) while storing floating point data. Databases created on i386 can basically be read and used on the ARM device and viceversa. However, data that is stored using the REAL storage class seems to be misinterpreted when it is read on the 'other' architecture. This seems to be independent of which architecture created the database and/or the table. So, doing this on one architecture: sqlite3 test.db create table f1 (value numeric); insert into f1 values (12.345); and then (after transferring the database file) doing a select on f1 using the other architecture will yield a result like: -1.20245408977952e+111 updating this table/row/column with some value and re-reading it will give the correct result, but then after switching sides the other architecture will return the wrong number. Any ideas what might be causing this as far as sqlite is concerned? -- Best, Frank.
Re: [sqlite] Possible bug regarding endiannes and real storageclass (sqlite3)
Hi, My test code is below. Please run this on your ARM and let me know what you get. # ./test f03f 0100 It's getting smelly.. 32 bits only? -- Best, Frank.
Re: [sqlite] Possible bug regarding endiannes and real storageclass (sqlite3)
ARM has at least two FL formats. Yes, I'm currently rebuilding my crosscompiler with specific soft-float options, but it'll take a while. Also, it seems that apart from the endiannes of the processor, there's also 'endiannes of peripheral wiring', i.e. the way the memory is connected to the processor. At the moment I have no tech data on that regarding this particular machine. -- Best, Frank.
Re: [sqlite] Possible bug regarding endiannes and realstorageclass (sqlite3)
I repeated the test using the value 1.2345678 in order to be able to identify the position of each byte: linux i386: 1bde8342cac0f33f 0100 linux arm: cac0f33f1bde8342 0100 So, it indeed looks like 32bits based middle-endian or something -- Best, Frank.
Re: [sqlite] Possible bug regarding endiannes and realstorageclass (sqlite3)
Right. So for your ARM FP library, the code goes from cut Yep, will try that patch tomorrow morning, the soft-float cross-compiler should be ready then as well, so we'll see if that makes any difference as well. -- Best, Frank.