Re: [sqlite] SQLITE_LOCKED behavior
On 2008 April 12 (Sat) 05:44:53pm PDT, Shawn Wilsher [EMAIL PROTECTED] wrote: When using SQLite 3.5.4.1 (a special branch cut for Mozilla, based mostly off of 3.5.4 with some OS/2 fixes), I'm getting SQLITE_LOCKED returned unexpectedly. The documentation seems to indicate that I should only be getting SQLITE_LOCKED if I'm calling sqlite3_exec recursively writing to the same table. However, it seems to me that I'm having that happen when two different threads are trying to write to the same table. I would expect to get SQLITE_BUSY at this point, but perhaps I'm misusing the API or have the wrong expectations. This is happening by using a different sqlite3 database pointers, one for each thread. Are you using a shared cache? You can get also get SQLITE_LOCKED when using a shared cache. See section 2.2 of http://www.sqlite.org/sharedcache.html. I've not used a shared cache myself. One day I was wondering if I needed to worry about handling SQLITE_LOCKED errors and I came across that page. Are these the only times you can get SQLITE_LOCKED errors? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_LOCKED behavior
On Apr 13, 2008, at 3:01 AM, Tomas Lee wrote: On 2008 April 12 (Sat) 05:44:53pm PDT, Shawn Wilsher [EMAIL PROTECTED] wrote: When using SQLite 3.5.4.1 (a special branch cut for Mozilla, based mostly off of 3.5.4 with some OS/2 fixes), I'm getting SQLITE_LOCKED returned unexpectedly. The documentation seems to indicate that I should only be getting SQLITE_LOCKED if I'm calling sqlite3_exec recursively writing to the same table. However, it seems to me that I'm having that happen when two different threads are trying to write to the same table. I would expect to get SQLITE_BUSY at this point, but perhaps I'm misusing the API or have the wrong expectations. This is happening by using a different sqlite3 database pointers, one for each thread. Are you using a shared cache? You can get also get SQLITE_LOCKED when using a shared cache. See section 2.2 of http://www.sqlite.org/sharedcache.html. I've not used a shared cache myself. One day I was wondering if I needed to worry about handling SQLITE_LOCKED errors and I came across that page. Are these the only times you can get SQLITE_LOCKED errors? If you are in the middle of a SELECT statement and from the same database connection you try to DROP one of the tables that is being read, the DROP statement will return SQLITE_LOCKED. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL error: no such function: and no such column:
bash-3.1# gcc -shared labsinf.c -o inf.so bash-3.1# sqlite3 SQLite version 3.5.7 Enter .help for instructions sqlite select load_extension('/root/inf.so'); sqlite select inf(savio); SQL error: no such column: savio sqlite select inf('savio'); SQL error: no such function: inf sqlite I tell help me for this little program: #include stdio.h #include stdlib.h #include string.h #include sqlite3ext.h SQLITE_EXTENSION_INIT1 void soundex(sqlite3_context* ctx, int nargs, sqlite3_value** values) { int i,j; char c,r; int d; int count; char* str2; char* result; int dim; const char* str; char ret[4]; str = sqlite3_value_text(values[0]); dim = strlen(str); for (i=0;idim;i++) str2[i] = str[i]; for (i=0;i=dim;i++) str2[i] = toupper(str2[i]); for (i=0;i=dim;i++) switch (str[i]) { case 'A': case 'E': case 'I': case 'O': case 'U': case 'H': case 'W': case 'Y': str2[i] = '0'; } for (i=1;idim;i++) switch (str2[i]) { case 'B': case 'F': case 'P': case 'V': str2[i] = '1'; break; case 'C': case 'G': case 'J': case 'K': case 'Q': case 'S': case 'X': case 'Z': str2[i] = '2'; break; case 'D': case 'T': str2[i] = '3'; break; case 'L': str2[i] = '4'; break; case 'M': case 'N': str2[i] = '5'; break; case 'R': str2[i] = '6'; break; } count=1; for (i=0;idim-1;i++) if (str2[i] != str2[i+1]) count++; result = malloc(count); j=0; for (i=0;idim-1;i++) if (str2[i] != str2[i+1]) { result[j]=str2[i]; j++; } // for (i=0;i4;i++) printf(%c, result[i]); for (i=0;i4;i++) ret[i] = result[i]; printf(\n\n); sqlite3_result_text(ctx,(const char*)ret, 4, SQLITE_TRANSIENT); } int sqlite3_extension_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) { SQLITE_EXTENSION_INIT2(pApi) sqlite3_create_function(db, soundex, 1, SQLITE_UTF8, NULL, soundex, NULL, NULL); return 0; } - Inviato da Yahoo! Mail. La casella di posta intelligente. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: no such function: and no such column:
dark0s dark0s [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] bash-3.1# gcc -shared labsinf.c -o inf.so bash-3.1# sqlite3 SQLite version 3.5.7 Enter .help for instructions sqlite select load_extension('/root/inf.so'); sqlite select inf(savio); SQL error: no such column: savio sqlite select inf('savio'); SQL error: no such function: inf Which part of you've created a function named 'soundex', not 'inf' is it difficult to understand? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] segmentation fault
This is last suggestion that I tell, where is the problem now for segmentation fault: bash-3.1# gcc -shared labsinf.c -o soundex.so bash-3.1# sqlite3 SQLite version 3.5.7 Enter .help for instructions sqlite select load_extension('/root/soundex.so'); sqlite select soundex('saverio'); S010 Segmentation fault bash-3.1# - Inviato da Yahoo! Mail. La casella di posta intelligente. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] segmentation fault
gdb is your friend here. compile with: gcc -O0 -g -shared labsinf.c -o soundex.so then run sqlite with gdb gdb sqlite3 () gdb run then you can get the backtrace of your exception -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of dark0s dark0s Sent: domingo, 13 de abril de 2008 10:46 To: sqlite-users@sqlite.org Subject: [sqlite] segmentation fault This is last suggestion that I tell, where is the problem now for segmentation fault: bash-3.1# gcc -shared labsinf.c -o soundex.so bash-3.1# sqlite3 SQLite version 3.5.7 Enter .help for instructions sqlite select load_extension('/root/soundex.so'); sqlite select soundex('saverio'); S010 Segmentation fault bash-3.1# - Inviato da Yahoo! Mail. La casella di posta intelligente. ___ 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] segmentation fault
dark0s dark0s [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] This is last suggestion that I tell, where is the problem now for segmentation fault: A few threads back, I predicted precisely this outcome. Writing through uninitialized pointers tends to end up this way, you know. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] segmentation fault
How must I interpret this output: bash-3.1# gcc -O0 -g -shared labsinf.c -o soundex.so bash-3.1# gdb sqlite3 GNU gdb 6.6 Copyright (C) 2006 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type show copying to see the conditions. There is absolutely no warranty for GDB. Type show warranty for details. This GDB was configured as i486-slackware-linux... Using host libthread_db library /lib/libthread_db.so.1. (gdb) select load_extension('/root/soundex.so'); No symbol load_extension in current context. (gdb) select soundex('saverio'); No symbol soundex in current context. (gdb) - Inviato da Yahoo! Mail. La casella di posta intelligente. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] segmentation fault
You must have to do a run inside gdb to get sqlite shell working then you can get your segfault -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of dark0s dark0s Sent: domingo, 13 de abril de 2008 11:00 To: sqlite-users@sqlite.org Subject: [sqlite] segmentation fault How must I interpret this output: bash-3.1# gcc -O0 -g -shared labsinf.c -o soundex.so bash-3.1# gdb sqlite3 GNU gdb 6.6 Copyright (C) 2006 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type show copying to see the conditions. There is absolutely no warranty for GDB. Type show warranty for details. This GDB was configured as i486-slackware-linux... Using host libthread_db library /lib/libthread_db.so.1. (gdb) select load_extension('/root/soundex.so'); No symbol load_extension in current context. (gdb) select soundex('saverio'); No symbol soundex in current context. (gdb) - Inviato da Yahoo! Mail. La casella di posta intelligente. ___ 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
[sqlite] Dealing with monetary huge values in sqlite
Hi all! In the application I'm developing, I'm dealing with monetary values that may become *very* huge (but with few decimals), and I have the feeling that the sqlite representation of REAL numbers with 8 bytes doubles may not be enough in my case, and I may get overflows (which would obviously be unacceptable in my case :-) ). Following the recent thread BCD representation of float ( http://thread.gmane.org/gmane.comp.db.sqlite.general/36787/focus=36803 ), I thought that maybe a custom implementation of BCD would be a possible solution in my case. Maybe I could also implement my own floating point format, based for instance on an int64 and a way to tell where the decimal point is. But in this case I would need to reimplement addition, division, etc. to deal with the position of the decimal point, and this may have a negative impact on performance and a storage overhead. Do you think BCD is the way to go for me? Maybe somebody has already solved this problem (John Stanton?) and could share a possible solution? Thank you for any insight. I'm very interested in pointers or source code dealing about that... :-) Aladdin _ Votre contact a choisi Hotmail, l'e-mail ultra sécurisé. Créez un compte gratuitement ! http://www.windowslive.fr/hotmail/default.asp ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dealing with monetary huge values in sqlite
On Sun, Apr 13, 2008 at 06:13:04PM +0200, Aladdin Lampé wrote: Maybe I could also implement my own floating point format, based for instance on an int64 and a way to tell where the decimal point is. But in this case I would need to reimplement addition, division, etc. to deal with the position of the decimal point, and this may have a negative impact on performance and a storage overhead. Why not just express all money values in terms of the smallest division of the currency? For example, for dollars, use cents, for pounds use pennies. Sqlite has up to 8 byte integers which would allow for all reasonable values. -- Two hands working can do more than a thousand clasped in prayer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dealing with monetary huge values in sqlite
Why not just express all money values in terms of the smallest division of the currency? For example, for dollars, use cents, for pounds use pennies. Yes, and you can create views to show the amounts in decimal notation where you need to. I do this (store as integers, show sometimes as decimals) for a large product list. Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Comparison of SQLite applications for Mac
Hi all, I've put together a rough tabulated comparison of a few SQLite GUI applications for the Mac. It shows whether each shows an entity list, whether they facilitate data entry in tables and views, what sort of syntax coloring they offer, etc. Please let me know (on this forum preferably) if you: 1. Have any corrections to what I've shown 2. Know of another application that should be included. 3. Have a feature that you find important, that should be compared between the programs. If so, please test that feature on as many of the programs listed as you can and let me know your results so I can add them. Are there any other similar comparisons around? http://www.tandb.com.au/sqlite/compare/?mlp Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dealing with monetary huge values in sqlite
Why not just express all money values in terms of the smallest division of the currency? For example, for dollars, use cents, for pounds use pennies. Sqlite has up to 8 byte integers which would allow for all reasonable values. This is mostly a test of replying to a post from Yahoo mail, but I agree with this suggestion. Two hands working can do more than a thousand clasped in prayer This is one of the best things I have read in a long time. Dennis Cote __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Quick Review/Reference
Amit Uttamchandani wrote: I also purchased Beginning Database Design - from Novice to Professional by Clare Churcher, but found it to much of a beginners book for my needs. Let me recommend SQL For Smarties as an excellent starting point. The book is perfectly suitable for beginners, but can take you all the way to very advanced topics. Probably one of the best computer books I have purchased. This isn't an SQLite specific book, by the way. It's fairly generic, so what you learn here will work elsewhere. Clay Dowling http://www.lazarusid.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dealing with monetary huge values in sqlite
In my opinion (please tell me if I'm wrong), your method only works if you want to *display* the values in your column, and if the decimal precision doesn't change form line to line. I would like to be able to perform operations (+, /, %, etc.) and to store intermediary results into other columns (such as x% of the value, etc.), which may have an arbitrary precision, and - only at the end - round the result to cents/pennies etc. This is required into the technical specifications provided by my client, because high precision matters for them. Indeed, an error of 0.01$ on 10,000,000 lines would lead to a significant error at the end... Any help appreciated. I really wouldn't have to propose another software than SQLite for this job... even if I have to reimplement from scratch BCD numbers for the sqlite engine! ;-) Aladdin Date: Sun, 13 Apr 2008 17:20:03 +0100 From: [EMAIL PROTECTED] To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with monetary huge values in sqlite On Sun, Apr 13, 2008 at 06:13:04PM +0200, Aladdin Lampé wrote: Maybe I could also implement my own floating point format, based for instance on an int64 and a way to tell where the decimal point is. But in this case I would need to reimplement addition, division, etc. to deal with the position of the decimal point, and this may have a negative impact on performance and a storage overhead. Why not just express all money values in terms of the smallest division of the currency? For example, for dollars, use cents, for pounds use pennies. Sqlite has up to 8 byte integers which would allow for all reasonable values. -- Two hands working can do more than a thousand clasped in prayer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Recevez tous vos e-mails sur un seul compte ! Créez une adresse Hotmail ! http://www.windowslive.fr/hotmail/default.asp ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] segmentation fault
I did not used never gdb. Can I post me commands that I must to use (gdb) run sqlite3 Starting program: sqlite3 No executable file specified. Use the file or exec-file command. (gdb) run select load_extension('/root/soundex.so'); Starting program: select load_extension('/root/soundex.so'); No executable file specified. Use the file or exec-file command. (gdb) run select soundex('saverio'); Starting program: select soundex('saverio'); No executable file specified. Use the file or exec-file command. (gdb) (gdb) run sqlite3 Starting program: sqlite3 No executable file specified. Use the file or exec-file command. (gdb) exec-file select load_extension('/root/soundex.so'); select: No such file or directory. (gdb) You must have to do a run inside gdb to get sqlite shell working then you can get your segfault - Inviato da Yahoo! Mail. La casella di posta intelligente. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite.kit (tcl starkit) for multiple platforms?
Hi, is there called sqlite.kit (tcl starkit) for multiple platforms somewhere? There is link at sdarchive: http://tcl.tk/starkits/ - but when I click it, it says it's not found. I tried search for it with google but didn't find yet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dealing with monetary huge values in sqlite
On Sun, Apr 13, 2008 at 07:37:33PM +0200, Aladdin Lampé wrote: In my opinion (please tell me if I'm wrong), your method only works if you want to *display* the values in your column, and if the decimal precision doesn't change form line to line. I would like to be able to perform operations (+, /, %, etc.) and to store intermediary results into other columns (such as x% of the value, etc.), which may have an arbitrary precision, and - only at the end - round the result to cents/pennies etc. This is required into the technical specifications provided by my client, because high precision matters for them. Indeed, an error of 0.01$ on 10,000,000 lines would lead to a significant error at the end... The proposed method allows you do perform arithmetic operations using SQLite's built-in operators and functions. If the greatest error your customer is willing to accept is, say, one part in a million (that is, a millionth of a cent), then using 64-bit would allow you to represent values up to ~ 10e13 -- 10 trillion, not so much, but perhaps good enough for you. If the tolerance is more like one in 10,000 ($0.001), then you can represent up to ~ 1,000 trillion as the largest value. That's still pretty small, IMO, but almost certainly good enough for you. You can adjust where you put the virtual fixed point. If you can't find a suitable break then you should consider your arbitrary precision extended function function scheme (or a database engine that provides the features you need). Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dealing with monetary huge values in sqlite
Thank you Nicolas for your answer. I understand that an int64 certainly gives me enough precision in the general case. Now what am I supposed to do if the user decides to add a virtual 4 decimal digits number to another number which has only 2 decimal digits? I should first identify this and then multiply by 100 the second number in order to be able to use sqlite built-in operators and functions... Not so simple, I think... And in my case, it is the user who determines the precision he desires for each number within the application, so I cannot have the precision fixed once for all like you suggest. Aladdin Date: Sun, 13 Apr 2008 13:41:46 -0500 From: [EMAIL PROTECTED] To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with monetary huge values in sqlite On Sun, Apr 13, 2008 at 07:37:33PM +0200, Aladdin Lampé wrote: In my opinion (please tell me if I'm wrong), your method only works if you want to *display* the values in your column, and if the decimal precision doesn't change form line to line. I would like to be able to perform operations (+, /, %, etc.) and to store intermediary results into other columns (such as x% of the value, etc.), which may have an arbitrary precision, and - only at the end - round the result to cents/pennies etc. This is required into the technical specifications provided by my client, because high precision matters for them. Indeed, an error of 0.01$ on 10,000,000 lines would lead to a significant error at the end... The proposed method allows you do perform arithmetic operations using SQLite's built-in operators and functions. If the greatest error your customer is willing to accept is, say, one part in a million (that is, a millionth of a cent), then using 64-bit would allow you to represent values up to ~ 10e13 -- 10 trillion, not so much, but perhaps good enough for you. If the tolerance is more like one in 10,000 ($0.001), then you can represent up to ~ 1,000 trillion as the largest value. That's still pretty small, IMO, but almost certainly good enough for you. You can adjust where you put the virtual fixed point. If you can't find a suitable break then you should consider your arbitrary precision extended function function scheme (or a database engine that provides the features you need). Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Créez votre disque dur virtuel Windows Live SkyDrive, 5Go de stockage gratuit ! http://www.windowslive.fr/skydrive/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dealing with monetary huge values in sqlite
On 2008 April 13 (Sun) 12:43:22pm PDT, Aladdin Lamp? [EMAIL PROTECTED] wrote: Thank you Nicolas for your answer. I understand that an int64 certainly gives me enough precision in the general case. Now what am I supposed to do if the user decides to add a virtual 4 decimal digits number to another number which has only 2 decimal digits? I should first identify this and then multiply by 100 the second number in order to be able to use sqlite built-in operators and functions... Not so simple, I think... And in my case, it is the user who determines the precision he desires for each number within the application, so I cannot have the precision fixed once for all like you suggest. Aladdin You may want to look at http://www2.hursley.ibm.com/decimal/, which is about General Decimal Arithmetic. There's a lot of information there about doing decimal arithmetic, both in fixed-size and arbitrary-precision. You can download implementations with very liberal licenses too. Date: Sun, 13 Apr 2008 13:41:46 -0500 From: [EMAIL PROTECTED] To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with monetary huge values in sqlite On Sun, Apr 13, 2008 at 07:37:33PM +0200, Aladdin Lamp? wrote: In my opinion (please tell me if I'm wrong), your method only works if you want to *display* the values in your column, and if the decimal precision doesn't change form line to line. I would like to be able to perform operations (+, /, %, etc.) and to store intermediary results into other columns (such as x% of the value, etc.), which may have an arbitrary precision, and - only at the end - round the result to cents/pennies etc. This is required into the technical specifications provided by my client, because high precision matters for them. Indeed, an error of 0.01$ on 10,000,000 lines would lead to a significant error at the end... The proposed method allows you do perform arithmetic operations using SQLite's built-in operators and functions. If the greatest error your customer is willing to accept is, say, one part in a million (that is, a millionth of a cent), then using 64-bit would allow you to represent values up to ~ 10e13 -- 10 trillion, not so much, but perhaps good enough for you. If the tolerance is more like one in 10,000 ($0.001), then you can represent up to ~ 1,000 trillion as the largest value. That's still pretty small, IMO, but almost certainly good enough for you. You can adjust where you put the virtual fixed point. If you can't find a suitable break then you should consider your arbitrary precision extended function function scheme (or a database engine that provides the features you need). Nico ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] segmentation fault
HINT: se a debugger: for (i=0;i for (i=0;i=dim;i++) str2[i] = toupper(str2[i]); str2 is never initialized and hence the assignment above is probably the issue dark0s dark0s [EMAIL PROTECTED] wrote: This is last suggestion that I tell, where is the problem now for segmentation fault: bash-3.1# gcc -shared labsinf.c -o soundex.so bash-3.1# sqlite3 SQLite version 3.5.7 Enter .help for instructions sqlite select load_extension('/root/soundex.so'); sqlite select soundex('saverio'); S010 Segmentation fault bash-3.1# - Inviato da Yahoo! Mail. La casella di posta intelligente. ___ 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] Dealing with monetary huge values in sqlite
On Sun, 13 Apr 2008, Aladdin Lampé wrote: Thank you Nicolas for your answer. I understand that an int64 certainly gives me enough precision in the general case. Now what am I supposed to do if the user decides to add a virtual 4 decimal digits number to another number which has only 2 decimal digits? I should first identify this and then multiply by 100 the second number in order to be able to use sqlite built-in operators and functions... Not so simple, I think... And in my case, it is the user who determines the precision he desires for each number within the application, so I cannot have the precision fixed once for all like you suggest. Aladdin, The question of how to represent currency in a computer program has been around since computers added business applications to calculating firing solutions for artillary. I've been aware of the problem when writing dbms applications since the early days of DOS. There is no BCD data type in SQLite, but you may find an external library in the language of your choice that will do calculations for you. Another approach (less elegant but workable) is to add 0.05 to all values with two fractional monetary digits. Since the answer is usually truncated rather than rounded, this old trick will give you results accurate to the penny. If the application has currencies as a critical element, then you do want to fix the precision to the smallest unit. 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
Re: [sqlite] Dealing with monetary huge values in sqlite
On Sun, 13 Apr 2008, Rich Shepard wrote: approach (less elegant but workable) is to add 0.05 to all values with two Oops! Make that 0.005. Mea culpa! 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
Re: [sqlite] Dealing with monetary huge values in sqlite
Our solution for the large number problem and escape the floating point jungle was to implement a display format fixed point decimal arithmetic processor with an arbitrary precision. It has add, subtract, multiply and divide plus aggregate functions. A wrapper handles the interface and intercepts the DECIMAL declared type which uses the standard SQL definition for precision and scale. The purpose of the format is that these numbers can be displayed unchanged into HTML pages, PostScript documents or general reports without editing or radix changes. They are not the best solution for arithmetically intensive applications but are very suitable for representing money and other commercial number usage where radix changes and editing tend to consume more resources than arithmetic. Using 64 bit integers with an associated precision and scale would probably be a better solution where you have a lot of arithmetic and less presentation. You are welcome to the code if it would work for you. Email me at johns at viacognis.com. BCD is a good way to go only if you need to save space with the packed decimal format and if you are using a machine with built-in BCD arithmetic instructions or have to interface with legacy COBOL applications. We also have some BCD routines from an old COBOL compatibility product if you are interested. John S Transportable Software Aladdin Lampé wrote: Hi all! In the application I'm developing, I'm dealing with monetary values that may become *very* huge (but with few decimals), and I have the feeling that the sqlite representation of REAL numbers with 8 bytes doubles may not be enough in my case, and I may get overflows (which would obviously be unacceptable in my case :-) ). Following the recent thread BCD representation of float ( http://thread.gmane.org/gmane.comp.db.sqlite.general/36787/focus=36803 ), I thought that maybe a custom implementation of BCD would be a possible solution in my case. Maybe I could also implement my own floating point format, based for instance on an int64 and a way to tell where the decimal point is. But in this case I would need to reimplement addition, division, etc. to deal with the position of the decimal point, and this may have a negative impact on performance and a storage overhead. Do you think BCD is the way to go for me? Maybe somebody has already solved this problem (John Stanton?) and could share a possible solution? Thank you for any insight. I'm very interested in pointers or source code dealing about that... :-) Aladdin _ Votre contact a choisi Hotmail, l'e-mail ultra sécurisé. Créez un compte gratuitement ! http://www.windowslive.fr/hotmail/default.asp ___ 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] Dealing with monetary huge values in sqlite
The solution I mentioned earlier stores decimal numbers in display format. Right justified, leading spaces and embedded decimal point. The arithmetic functions handle the fixed point logic. The effect is the same as using a desktop calculator. Rounding is performed using the traditional algorithm which minimizes drift, not by truncation. Aladdin Lampé wrote: In my opinion (please tell me if I'm wrong), your method only works if you want to *display* the values in your column, and if the decimal precision doesn't change form line to line. I would like to be able to perform operations (+, /, %, etc.) and to store intermediary results into other columns (such as x% of the value, etc.), which may have an arbitrary precision, and - only at the end - round the result to cents/pennies etc. This is required into the technical specifications provided by my client, because high precision matters for them. Indeed, an error of 0.01$ on 10,000,000 lines would lead to a significant error at the end... Any help appreciated. I really wouldn't have to propose another software than SQLite for this job... even if I have to reimplement from scratch BCD numbers for the sqlite engine! ;-) Aladdin Date: Sun, 13 Apr 2008 17:20:03 +0100 From: [EMAIL PROTECTED] To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with monetary huge values in sqlite On Sun, Apr 13, 2008 at 06:13:04PM +0200, Aladdin Lampé wrote: Maybe I could also implement my own floating point format, based for instance on an int64 and a way to tell where the decimal point is. But in this case I would need to reimplement addition, division, etc. to deal with the position of the decimal point, and this may have a negative impact on performance and a storage overhead. Why not just express all money values in terms of the smallest division of the currency? For example, for dollars, use cents, for pounds use pennies. Sqlite has up to 8 byte integers which would allow for all reasonable values. -- Two hands working can do more than a thousand clasped in prayer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Recevez tous vos e-mails sur un seul compte ! Créez une adresse Hotmail ! http://www.windowslive.fr/hotmail/default.asp ___ 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
[sqlite] How to set memory usage as high as possible -- but not too high?
I need to create a huge database (about 6 GB, more than 6 mio records, blobs, and FTS text) in as little time as possible. Since memory is the key to speed, I try to use as much memory as is available. However, there is the danger of running out of memory. This is where memory usage control comes into play. I can see there are two options: * OPTION 1: PRAGMA cache_size = 1000; Advantage: SQLite will use ample memory, but no more than that. Disadvantage: Difficulty to establish exact memory requirements in advance. The help states that Each page uses about 1.5K of memory., but I found this to be wrong. Memory usage obviously depends on the page size, and my measurement shows that there is an additional small overhead of undocumented size. Is there a formula to calculate the required memory for a cache_size of x? * OPTION 2: sqlite3_soft_heap_limit(max_mem_bytes); Advantage: Memory limit can be set to a known value (amount of free memory as returned from the OS). Disadvantage: My tests indicate that SQLite slows down drastically when it hits the memory limit. Inserts drop from a few hundred per second to just one or two per sec. * OPTION 3: Catch out-of-memory errors and reduce cache_size accordingly (untested scenario). Advantage: Use memory up to the least bits available. Disadvantage: How to avoid data loss after the out-of-memory error. Can I just call sqlite3_release_memory(some_bytes) and sqlite3_step again and again until it passes without the out-of-memory error? This raises a few questions: * Do sqlite3_soft_heap_limit(), or PRAGMA cache_size=x;, or both establish SQLite's upper memory limit? Do they work independently of each other, i.e. does the lower limit always kick in first? * Does PRAGMA cache_size=some_smaller_value; cause SQLite to free used pages and release their memory straight away? * Is there another runtime -- important! -- setting to establish a maximum memory limit, possibly undocumented? In the end this boils down to a simple problem: * Wow to keep SQLite's memory usage as close to, but not exceeding the memory available to applications? I will be very grateful for any suggestion! Many thanks, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to set memory usage as high as possible -- but not too high?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ralf Junker wrote: I need to create a huge database (about 6 GB, more than 6 mio records, blobs, and FTS text) in as little time as possible. Since memory is the key to speed, I try to use as much memory as is available. However, there is the danger of running out of memory. This is where memory usage control comes into play. I can see there are two options: Are you using a 32 bit or 64 bit process. Also is there a requirement to create the database in the filesystem? If not you could ensure your swap is sufficiently large (I use a mininmum of 16GB on my machines :-) and create in a tmpfs filesystem, and then copy the database to persistent storage when you are done. You also didn't list turning off synchronous etc while creating the database and turning it back on when done. I am curious why you think memory is the bottleneck anyway! Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIArulmOOfHg372QQRAvNrAKDM5b4Tvf+QWfp2tWk6fYIuILE4xgCgj7tQ QatXpI5lnZEw6uPjDtnBGu0= =toXf -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to set memory usage as high as possible -- but not too high?
On Apr 14, 2008, at 5:53 AM, Ralf Junker wrote: I need to create a huge database (about 6 GB, more than 6 mio records, blobs, and FTS text) in as little time as possible. Since memory is the key to speed, I try to use as much memory as is available. However, there is the danger of running out of memory. This is where memory usage control comes into play. I can see there are two options: * OPTION 1: PRAGMA cache_size = 1000; Advantage: SQLite will use ample memory, but no more than that. Disadvantage: Difficulty to establish exact memory requirements in advance. The help states that Each page uses about 1.5K of memory., but I found this to be wrong. Memory usage obviously depends on the page size, and my measurement shows that there is an additional small overhead of undocumented size. Is there a formula to calculate the required memory for a cache_size of x? * OPTION 2: sqlite3_soft_heap_limit(max_mem_bytes); Advantage: Memory limit can be set to a known value (amount of free memory as returned from the OS). Disadvantage: My tests indicate that SQLite slows down drastically when it hits the memory limit. Inserts drop from a few hundred per second to just one or two per sec. That is an odd result. How did you test it? What was the memory limit? Any chance the machine started using swap space? * OPTION 3: Catch out-of-memory errors and reduce cache_size accordingly (untested scenario). Advantage: Use memory up to the least bits available. Disadvantage: How to avoid data loss after the out-of-memory error. Can I just call sqlite3_release_memory(some_bytes) and sqlite3_step again and again until it passes without the out-of-memory error? This raises a few questions: * Do sqlite3_soft_heap_limit(), or PRAGMA cache_size=x;, or both establish SQLite's upper memory limit? Do they work independently of each other, i.e. does the lower limit always kick in first? Both limits can be used simultaneously. The cache_size limit is per database cache, soft_heap_limit() sets a global parameter that governs all sqlite connections opened by the process. * Does PRAGMA cache_size=some_smaller_value; cause SQLite to free used pages and release their memory straight away? No. If the cache_size parameter is set to a value that is less than the number of pages currently allocated for the cache, no more pages will be allocated. But no existing pages will be freed. * Is there another runtime -- important! -- setting to establish a maximum memory limit, possibly undocumented? There is the SQLITE_MEMORY_SIZE option. But that's not really useful for the very large memory limits you're talking about. So soft_heap_limit() and pragma cache_size are it. In the end this boils down to a simple problem: * Wow to keep SQLite's memory usage as close to, but not exceeding the memory available to applications? It's not really that simple. On a workstation, not all memory is equal. The maximum amount of memory available to an application is all of the RAM + all of the swap space. Best performance probably comes by using up all of the RAM and never using the swap. Realistically, you should probably just set a large cache_size as in option 1. Does SQLite really run faster with 1GB available than it would with 100MB? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users