Re: [sqlite] Interrupting custom collation processing
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/07/2015 05:55 PM, Dan Ackroyd wrote: > Due to the nature of PHP, it would be expected that the processing > of the collation should stop immediately. However I can't see how > to indicate to SQLite that an error has occurred, and so the > function is called for the remaining rows that need to be ordered. I'm the author of a python wrapper for SQLite and faced a similar problem. Python records the exception information separately. At the place you want processing to abort, call sqlite3_interrupt. The top level caller checks the exception information first and if there signals a Python exception. Only if that is None does it check the sqlite api return code, so that error (SQLITE_ABORT in this case) will not be signalled. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlTW1GoACgkQmOOfHg372QSxDQCfWouTfdBvBn1LnHYd0cQToQ4C 4gYAoI+kvxC5+ZxcvKEOUoo81h/jvB6U =yi8Y -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Interrupting custom collation processing
Hi, After setting a custom collator function with `sqlite3_create_collation`, is it possible to set or return an error value to indicate that the collating should stop immediately from within the callback function? Basically I'm looking at a bug in PHP using the SQLite extension when: * A user has set a function to use as the collation. * That function throws an exception. Due to the nature of PHP, it would be expected that the processing of the collation should stop immediately. However I can't see how to indicate to SQLite that an error has occurred, and so the function is called for the remaining rows that need to be ordered. btw this is in no way a feature request - if it's not possible I'm just going to update the manual to say that throwing exceptions in the collator callback will give undefined behavior. cheers Dan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite FLOAT values
Why would an application need to use the SQLite printf function to convert
doubles to formatted text? The application ought to store and retrieve the raw
doubles completely unadulterated (with no diddling, using the value_double and
bind_double interfaces), and "format the value for display" when it is
displayed.
---
Theory is when you know everything but nothing works. Practice is when
everything works but no one knows why. Sometimes theory and practice are
combined: nothing works and no one knows why.
>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Kees Nuyt
>Sent: Saturday, 7 February, 2015 18:14
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Bug in SQLite FLOAT values
>
>On Sat, 7 Feb 2015 11:27:59 +0530, Abdul Aziz
>wrote:
>
>> Hi there!
>> I hope you are well!
>>
>> Recently I was working on project based on Android Sensors and
>encountered
>> a bug in sqlite db, situation was this:
>> I was setting there three values x,y,z as FLOAT, android inbuilt
>sensors
>> were receiving values as float upto 8 decimal places, but I wanted to
>store
>> value only upto 6 decimal place, so in android this is the way that
>first
>> you will have to convert that value into String , as* String sLongitude
>=
>> String.format("%.6f", x);*
>
>As others have said, you shouldn't confuse the storage
>format (how a value is stored in the database) with
>the presentation (how data is displayed on output).
>
>Luckily, recently sqlite got a printf() function.
>
>Demo:
>
>$ sqlite3 test.db
>SQLite version 3.8.8 2015-01-30 20:59:27
>Enter ".help" for usage hints.
>sqlite> create table t3 (id INTEGER PRIMARY KEY, lat REAL, lon REAL);
>sqlite> insert into t3 (id,lat,lon) VALUES
>(1,1.234567890123,5.6789012345678);
>sqlite> select printf('id:%3d, latitude: %9.6f, longitude:
>%9.6f',id,lat,lon) from t3;
>id: 1, latitude: 1.234568, longitude: 5.678901
>sqlite>
>
>Hope this helps.
>
>--
>Regards,
>
>Kees Nuyt
>
>___
>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] Bug in SQLite FLOAT values
On Sat, 7 Feb 2015 11:27:59 +0530, Abdul Aziz wrote:
> Hi there!
> I hope you are well!
>
> Recently I was working on project based on Android Sensors and encountered
> a bug in sqlite db, situation was this:
> I was setting there three values x,y,z as FLOAT, android inbuilt sensors
> were receiving values as float upto 8 decimal places, but I wanted to store
> value only upto 6 decimal place, so in android this is the way that first
> you will have to convert that value into String , as* String sLongitude =
> String.format("%.6f", x);*
As others have said, you shouldn't confuse the storage
format (how a value is stored in the database) with
the presentation (how data is displayed on output).
Luckily, recently sqlite got a printf() function.
Demo:
$ sqlite3 test.db
SQLite version 3.8.8 2015-01-30 20:59:27
Enter ".help" for usage hints.
sqlite> create table t3 (id INTEGER PRIMARY KEY, lat REAL, lon REAL);
sqlite> insert into t3 (id,lat,lon) VALUES (1,1.234567890123,5.6789012345678);
sqlite> select printf('id:%3d, latitude: %9.6f, longitude: %9.6f',id,lat,lon)
from t3;
id: 1, latitude: 1.234568, longitude: 5.678901
sqlite>
Hope this helps.
--
Regards,
Kees Nuyt
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite give "database or disk full"
jitendar kumar wrote: > where is the temporary files location ?? 1. temp_directory, if set 2. SQLITE_TMPDR, if set 3. TMPDIR, if set 4. /var/tmp 5. /usr/tmp 6. /tmp Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ordinary CTE containing sum()
On Sat, 7 Feb 2015 12:31:37 -0500 Doug Currie wrote: > In response to this SO question: > > http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table > > I tried to formulate a query without temp tables using an ordinary > CTE, but received an error "misuse of aggregate: sum()". My standard answer is http://www.schemamania.org/sql/#rank.rows. You don't need a CTE; it's just a syntactic convenience. Substituting his table in my example, something like this should work: select S.id, S.total, count(lesser.total) as RANK from ( select id, sum(cal1 + cal2 + exam) as total from T group by id ) as S join ( select id, sum(cal1 + cal2 + exam) as total from T group by id ) as as lesser on S.SID >= lesser.SID group by S.SID, S.total order by S.SID; Someone will doubtless be tempted to suggest that's inefficient in SQLite, and that may be so. (I haven't checked.) It needed be, though. The system could detect the repeated subquery and evaluate it once. Not that it matters for any dataset of students and exams on the planet! :-) --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite for Pocket PC & RTREE
> Thanks. I've run the schema and query here with the desktop version of > SpatiaLite and it does not crash. I suspect the underlying issue may be > an alignment fault (or stack overflow) due to bad interaction between the > ARM processor architecture and the SpatiaLite extension. I'm not using the Spatialite extension for the query, only the indexes created by it on the desktop which I think is a pretty standard SQLite RTree. At least it works that way on the desktop. Maybe I should attempt to create the database from scratch using geometry bounding boxes for the index, but I can't see how this would be different. Spatialite databases are really just complex SQLite databases > Out of curiousity, do you know where to find the loadable module binaries > for Windows CE (for SpatiaLite)? That would be nice. I have been trying to do something like this for years, but lacked the necessary C skills to compile for Mobile. I think it has been done, but not for recent versions that I am aware of. My current projects for Android and IOS ran up against licensing issues for the Spatialite dependent libraries in IOS so I started using the RTree to run the query, then decode the Spatialite geometry BLOB afterwards. It seems to run fine for my purposes without too much performance hit, so I revisited the Windows Mobile 5/6 platform as we have quite a few still running and they are still being used and sold in the mobile GIS world despite Microsoft's abandonment. I was delighted to find that you are still supporting it. Duncan -- View this message in context: http://sqlite.1065341.n5.nabble.com/Re-System-Data-SQLite-for-Pocket-PC-RTREE-tp80344p80423.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
Re: [sqlite] Bug in SQLite FLOAT values
On 7 Feb 2015, at 5:57am, Abdul Aziz wrote: > *again I converted back to float this string formatted value, and printed > into Log, I was clearly seeing values upto 6 decimal places , but after > insertion into sqlite db, when after generation of sqlite db file, was > getting values upto 11-12 decimal places!* SQLite itself would have converted your text (to six digits) and stored the resulting number. When you asked to retrieve your value, SQLite would have retrieved that number -- still at six digits. However if you ask for the retrieved figure as a number (as opposed to a string) the programming language you use has to put the resulting number into a 'float' variable. And in doing this it would have to turn the number back into float format, which would introduce the extra 'garbage' digits. So yes, you can argue that there is a bug somewhere, but if there is one it's in the Android interface to SQLite, not in SQLite itself. You can avoid this by asking for the retrieved figure as a string, not a number. Or by storing the value as a TEXT column instead of a REAL column, which is the solution you came up with. So I'm glad you found a solution. It might be worth asking yourself why you are trimming your values to six digits and then saving the result as a number. It would make more sense to trim your values and then handle the number as a string from then onwards. Or to handle all the digits you have and to convert to text as six digits just before you put the number on the display. Both of these would be more mathematically 'correct' than what you are doing. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ordinary CTE containing sum()
In response to this SO question: http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table I tried to formulate a query without temp tables using an ordinary CTE, but received an error "misuse of aggregate: sum()". This works: sqlite> with tt (S_id, total) as ...>(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total ...> from t group by S_id) ...> select * from tt ; 1|143 2|198 3|165 4|198 5|183 but with the same CTE this fails, even though the select statement after the CTE works with an equivalent temporary table: sqlite> with tt (S_id, total) as ...>(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total ...> from t group by S_id) ...> select s.S_id, s.total, ...> (select count(*)+1 from tt as r where r.total > s.total) as rank ...> from tt as s; Error: misuse of aggregate: sum() Any suggestions? Thanks. -- e ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite FLOAT values
On 2015/02/07 15:47, Abdul Aziz wrote:
Thanks for replybut I am now using VARCHARS, then how this is working?
not generating any errors?
Please elaborate, my query to create DB is:
mSQLiteDatabase.execSQL("CREATE TABLE " + tableName
+ " ( "
+ EVENT_TIME + " INTEGER, " + SYSTEM_TIME + " INTEGER PRIMARY KEY, "
+ ACCURACY + " INTEGER," + X + " VARCHAR, " + Y + " VARCHAR, " + Z
+ " VARCHAR );");
It translates Varchar to Text, that's why no errors are generated, it understands what you mean by Varchar, which is really just
some text, so it translates it to the internal type TEXT.
As for your question about decimal points and floats, no float in any language stores values up to a certain length... lengths are
the domain of Strings and text, not Floating point numbers. any floating point number is an approximate number with a representation
as close as is possible to the actual number. That representation includes many significant digits in the significand and an
exponent. You may need to read up on floats some more to see how it works - my point is just that it doesn't store numbers up to a
certain length, for that you need a formatter.
Many DB engines offer formatted types, such as Decimal (in PG, Oracle, MSSQL, etc) where you can say you need the number with so
many decimals after the point. In SQLite you can format the output (much like your C solution) by doing SELECT
printf('%.6f',somevalue); etc.
Read the pages offered by the other posters and maye check out the Wikipedia pages on floating point storage and representation to
understand WHY all the above happens, but to solve your immediate problem, use the output formatting or store as strings - there is
no way to tell a true floating number to keep itself short.
Other interesting things you can see about this floating point problem (it's a mathematical problem too), look on youtube for "Why
is 0.9... equal to 1?" or "How do we know two numbers are distinct?" - The Numberphile videos in general do a good job of
explaining it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite give "database or disk full"
Hello sqlite Users, I am using sqlite on arm-board and getting a "database or disk full" error (SQLITE_FULL) while using the update command. 1. DB file location is set to some /opt/dbspace/*.db. ( 32 Gb space and 4 GB RAM) 2. Not used compile time option SQLITE_TEMP_STORE so, deafult value 1. 3. I have used pragma journal_mode=WAL. 4. No pragma temp_store. Please help me with below queries a) where is the temporary files location ?? How is it determined if i dont give any storage settings. b) Do i need to set TMPDIR env variable to give another location for temporary files or temp_store_directory ? c) If i set another location for temporary files, how does the performance gets affected ? Thanks in advance regards, Jitendar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite FLOAT values
On 2/7/2015 8:47 AM, Abdul Aziz wrote: Thanks for replybut I am now using VARCHARS, then how this is working? not generating any errors? When Tim said "Read this", he meant it. http://www.sqlite.org/datatype3.html answers your questions (but only if you read it). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite FLOAT values
Thanks for replybut I am now using VARCHARS, then how this is working?
not generating any errors?
Please elaborate, my query to create DB is:
mSQLiteDatabase.execSQL("CREATE TABLE " + tableName
+ " ( "
+ EVENT_TIME + " INTEGER, " + SYSTEM_TIME + " INTEGER PRIMARY KEY, "
+ ACCURACY + " INTEGER," + X + " VARCHAR, " + Y + " VARCHAR, " + Z
+ " VARCHAR );");
On Sat, Feb 7, 2015 at 7:08 PM, Tim Streater wrote:
> On 07 Feb 2015 at 05:57, Abdul Aziz wrote:
>
> > *I think this is a bug, this means float values in sqlite will always be
> > filled upto 11-12 decimal places, in any case, you will have to fill it,
> or
> > sqlite will fill it itself with junk values, **this may create lot of
> > consumption of memory while working on larger projects...*
>
> Read this:
>
> http://www.sqlite.org/datatype3.html
>
> Note that:
>
> 1) There are no varchars in SQLite
>
> 2) Floats always occupy 8 bytes
>
> --
> Cheers -- Tim
>
> ___
> 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] Bug in SQLite FLOAT values
On 07 Feb 2015 at 05:57, Abdul Aziz wrote: > *I think this is a bug, this means float values in sqlite will always be > filled upto 11-12 decimal places, in any case, you will have to fill it, or > sqlite will fill it itself with junk values, **this may create lot of > consumption of memory while working on larger projects...* Read this: http://www.sqlite.org/datatype3.html Note that: 1) There are no varchars in SQLite 2) Floats always occupy 8 bytes -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in SQLite FLOAT values
Hi there!
I hope you are well!
Recently I was working on project based on Android Sensors and encountered
a bug in sqlite db, situation was this:
I was setting there three values x,y,z as FLOAT, android inbuilt sensors
were receiving values as float upto 8 decimal places, but I wanted to store
value only upto 6 decimal place, so in android this is the way that first
you will have to convert that value into String , as* String sLongitude =
String.format("%.6f", x);*
*again I converted back to float this string formatted value, and printed
into Log, I was clearly seeing values upto 6 decimal places , but after
insertion into sqlite db, when after generation of sqlite db file, was
getting values upto 11-12 decimal places!*
*upto 6 decimal places were matching with my values and rest were garbage
values...*
*Then to avoid this I converted from FLOAT to VARCHAR into DB, and inserted
that formatted values (**String sLongitude = String.format("%.6f", x)**)*
*now I was getting correct formatted values from sqlite db file,*
*I think this is a bug, this means float values in sqlite will always be
filled upto 11-12 decimal places, in any case, you will have to fill it, or
sqlite will fill it itself with junk values, **this may create lot of
consumption of memory while working on larger projects...*
*Thanks, waiting for a reply*
*Abdul Aziz Ansari*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in SQLite FLOAT values
Hi there!
I hope you are well!
Recently I was working on project based on Android Sensors and encountered
a bug in sqlite db, situation was this:
I was setting there three values x,y,z as FLOAT, android inbuilt sensors
were receiving values as float upto 8 decimal places, but I wanted to store
value only upto 6 decimal place, so in android this is the way that first
you will have to convert that value into String , as* String sLongitude =
String.format("%.6f", x);*
*again I converted back to float this string formatted value, and printed
into Log, I was clearly seeing values upto 6 decimal places , but after
insertion into sqlite db, when after generation of sqlite db file, was
getting values upto 11-12 decimal places!*
*upto 6 decimal places were matching with my values and rest were garbage
values...*
*Then to avoid this I converted from FLOAT to VARCHAR into DB, and inserted
that formatted values (**String sLongitude = String.format("%.6f", x)**)*
*now I was getting correct formatted values from sqlite db file,*
*I think this is a bug, this means float values in sqlite will always be
filled upto 11-12 decimal places, in any case, you will have to fill it, or
sqlite will fill it itself with junk values, **this may create lot of
consumption of memory while working on larger projects...*
*Thanks, waiting for a reply*
*Abdul Aziz Ansari*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
