Re: [sqlite] Lack of "decimal" support
Patrick Earl wrote: > On Sat, Mar 26, 2011 at 10:03 PM, Darren Duncan > wrote: >> You could store your exact precision numbers as a pair of integers >> representing >> a numerator/denominator ratio and then have math operators that work on these >> pairs like they were one number. You would then know at the end how to move >> the >> radix point since that was kept track of along with the number. -- Darren >> Duncan > > If you did this, you wouldn't be able to compare numbers in the > database without resorting to division. Sure you can. You make sure the two operands have the same denominator and then compare the numerators. Or you resort to multiplication, as they taught in grade school (dividing by a fraction is the same as multiplying by its inverse). Everything is just integers. If your normal operations are just straight-up addition/subtraction and multiplication and all your operands have the same radix (are in base 10), then your results are all guaranteed to be in base-10 as well, since any denominators in results would be positive powers of 10. Likewise if you're doing division but you ensure that any divisor is a power of 10. > If you just specified how > many fixed decimal places there were, you could zero-pad strings if > you only needed to perform comparison operations. Obviously you'd > need to create custom operations, as you suggest, for other math > operators. We should be able to avoid strings with this entirely. > If SQLite can't decide on a base-10 format itself, perhaps the answer > lies in enhancing the API to allow for custom type storage and > operators. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lack of "decimal" support
On Sat, Mar 26, 2011 at 10:03 PM, Darren Duncan wrote: > You could store your exact precision numbers as a pair of integers > representing > a numerator/denominator ratio and then have math operators that work on these > pairs like they were one number. You would then know at the end how to move > the > radix point since that was kept track of along with the number. -- Darren > Duncan If you did this, you wouldn't be able to compare numbers in the database without resorting to division. If you just specified how many fixed decimal places there were, you could zero-pad strings if you only needed to perform comparison operations. Obviously you'd need to create custom operations, as you suggest, for other math operators. If SQLite can't decide on a base-10 format itself, perhaps the answer lies in enhancing the API to allow for custom type storage and operators. Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lack of "decimal" support
If you use a view to return a double, you've lost the exact value you were trying to save by storing the decimal as a text value. If you continue to work with it as an integer, it's exact, but that requires continual awareness of the number of decimal places at any point in time. In essence, you have to build significant numeric infrastructure into your program to emulate the missing numeric infrastructure in SQLite. Patrick Earl On Sat, Mar 26, 2011 at 9:52 PM, BareFeetWare wrote: > On 27/03/2011, at 2:09 PM, Patrick Earl wrote: > >> if you're in a context where you don't have significant understanding of the >> user's query, how do you determine if 1.05 is $1.05 or 105%? > > Can you give us a bit more background and an example of this? > > How is the interface for the query represented to the user and what can they > enter there to create a query? > > You can probably do this fairly easily via views which display data in a > particular format for the user to see or create a query. > > Tom > BareFeetWare > > -- > iPhone/iPad/iPod and Mac software development, specialising in databases > develo...@barefeetware.com > -- > Comparison of SQLite GUI tools: > http://www.barefeetware.com/sqlite/compare/?ml > > > > ___ > 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] Lack of "decimal" support
Patrick Earl wrote: > That is true, but then when you are formulating generic queries within > a place such as an ORM like NHibernate, you would need to figure out > when to translate the user's "100" into "1". As well, if you > multiplied numbers, you'd need to re-scale the result. For example, > (1 * 1) would be (100 * 100 = 1), which is 1 * 1 = 100. :( If one > wanted to get excessively complicated, they could implement a series > of user functions that perform decimal operations using strings and > then reformulate queries to replace + with decimal_add(x,y). That > said, it'd be so much nicer if there was just native support for > base-10 numbers. :) You could store your exact precision numbers as a pair of integers representing a numerator/denominator ratio and then have math operators that work on these pairs like they were one number. You would then know at the end how to move the radix point since that was kept track of along with the number. -- Darren Duncan > On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare > wrote: >> On 27/03/2011, at 12:39 PM, Patrick Earl wrote: >> >>> Base-10 numbers are frequently used in financial calculations because >>> of their exact nature. SQLite forces us to store decimal numbers as >>> text to ensure precision is not lost. Unfortunately, this prevents >>> even simple operations such as retrieving all rows where an employee's >>> salary is greater than '100' (coded as a string since decimal types >>> are stored as strings). >> Can you store all money amounts as integers, as the cents value? That is >> exact, searchable etc. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lack of "decimal" support
On 27/03/2011, at 2:09 PM, Patrick Earl wrote: > if you're in a context where you don't have significant understanding of the > user's query, how do you determine if 1.05 is $1.05 or 105%? Can you give us a bit more background and an example of this? How is the interface for the query represented to the user and what can they enter there to create a query? You can probably do this fairly easily via views which display data in a particular format for the user to see or create a query. Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lack of "decimal" support
You're right, it doesn't make sens to multiply dollars, but if you're in a context where you don't have significant understanding of the user's query, how do you determine if 1.05 is $1.05 or 105%? I understand that one can custom-code everything for SQLite and get reasonable results in some cases, but please understand that I'm looking for solutions that don't require the framework to understand the user's intentions any more than "I want to work with base-10 numbers up to a certain precision/scale." Patrick Earl On Sat, Mar 26, 2011 at 8:43 PM, Gerry Snyder wrote: > Do money values really get multiplied together? > > What is the meaning of square cents as a unit? > > Gerry > > On 3/26/11, Patrick Earl wrote: >> That is true, but then when you are formulating generic queries within >> a place such as an ORM like NHibernate, you would need to figure out >> when to translate the user's "100" into "1". As well, if you >> multiplied numbers, you'd need to re-scale the result. For example, >> (1 * 1) would be (100 * 100 = 1), which is 1 * 1 = 100. :( If one >> wanted to get excessively complicated, they could implement a series >> of user functions that perform decimal operations using strings and >> then reformulate queries to replace + with decimal_add(x,y). That >> said, it'd be so much nicer if there was just native support for >> base-10 numbers. :) >> >> Patrick Earl >> >> On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare >> wrote: >>> On 27/03/2011, at 12:39 PM, Patrick Earl wrote: >>> Base-10 numbers are frequently used in financial calculations because of their exact nature. SQLite forces us to store decimal numbers as text to ensure precision is not lost. Unfortunately, this prevents even simple operations such as retrieving all rows where an employee's salary is greater than '100' (coded as a string since decimal types are stored as strings). >>> >>> Can you store all money amounts as integers, as the cents value? That is >>> exact, searchable etc. >>> >>> Thanks, >>> Tom >>> BareFeetWare >>> >>> -- >>> iPhone/iPad/iPod and Mac software development, specialising in databases >>> develo...@barefeetware.com >>> -- >>> Comparison of SQLite GUI tools: >>> http://www.barefeetware.com/sqlite/compare/?ml >>> >>> ___ >>> 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 >> > > -- > Sent from my mobile device > ___ > 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] Lack of "decimal" support
> On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare > wrote: >> Can you store all money amounts as integers, as the cents value? That is >> exact, searchable etc. On 27/03/2011, at 1:27 PM, Patrick Earl wrote: > That is true, but then when you are formulating generic queries within > a place such as an ORM like NHibernate, you would need to figure out > when to translate the user's "100" into "1". You can keep all internal transactions as integers, so there are no float rounding errors. You only have to translate the final figures if you want to display to the user as dollars. You can do this in selects or use views to convert the data if needed. For instance: create table Staff ( ID integer primary key not null , Name text collate nocase not null , Salary integer -- in cents ) ; create view "Staff Dollars" as select ID , Name , round(Salary/ 100.0, 2) as Salary from "Staff" ; > As well, if you multiplied numbers, you'd need to re-scale the result. For > example, (1 * 1) would be (100 * 100 = 1), which is 1 * 1 = 100. :( I can't think of any reason for multiplying two money amounts. You would only ever multiple a money amount by a plane number, so you only ever have to /100 if you want to present your final answer in dollars. I do this for invoice totals, tax return calculations and similar. Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lack of "decimal" support
Do money values really get multiplied together? What is the meaning of square cents as a unit? Gerry On 3/26/11, Patrick Earl wrote: > That is true, but then when you are formulating generic queries within > a place such as an ORM like NHibernate, you would need to figure out > when to translate the user's "100" into "1". As well, if you > multiplied numbers, you'd need to re-scale the result. For example, > (1 * 1) would be (100 * 100 = 1), which is 1 * 1 = 100. :( If one > wanted to get excessively complicated, they could implement a series > of user functions that perform decimal operations using strings and > then reformulate queries to replace + with decimal_add(x,y). That > said, it'd be so much nicer if there was just native support for > base-10 numbers. :) > >Patrick Earl > > On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare > wrote: >> On 27/03/2011, at 12:39 PM, Patrick Earl wrote: >> >>> Base-10 numbers are frequently used in financial calculations because >>> of their exact nature. SQLite forces us to store decimal numbers as >>> text to ensure precision is not lost. Unfortunately, this prevents >>> even simple operations such as retrieving all rows where an employee's >>> salary is greater than '100' (coded as a string since decimal types >>> are stored as strings). >> >> Can you store all money amounts as integers, as the cents value? That is >> exact, searchable etc. >> >> Thanks, >> Tom >> BareFeetWare >> >> -- >> iPhone/iPad/iPod and Mac software development, specialising in databases >> develo...@barefeetware.com >> -- >> Comparison of SQLite GUI tools: >> http://www.barefeetware.com/sqlite/compare/?ml >> >> ___ >> 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 > -- Sent from my mobile device ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lack of "decimal" support
That is true, but then when you are formulating generic queries within a place such as an ORM like NHibernate, you would need to figure out when to translate the user's "100" into "1". As well, if you multiplied numbers, you'd need to re-scale the result. For example, (1 * 1) would be (100 * 100 = 1), which is 1 * 1 = 100. :( If one wanted to get excessively complicated, they could implement a series of user functions that perform decimal operations using strings and then reformulate queries to replace + with decimal_add(x,y). That said, it'd be so much nicer if there was just native support for base-10 numbers. :) Patrick Earl On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare wrote: > On 27/03/2011, at 12:39 PM, Patrick Earl wrote: > >> Base-10 numbers are frequently used in financial calculations because >> of their exact nature. SQLite forces us to store decimal numbers as >> text to ensure precision is not lost. Unfortunately, this prevents >> even simple operations such as retrieving all rows where an employee's >> salary is greater than '100' (coded as a string since decimal types >> are stored as strings). > > Can you store all money amounts as integers, as the cents value? That is > exact, searchable etc. > > Thanks, > Tom > BareFeetWare > > -- > iPhone/iPad/iPod and Mac software development, specialising in databases > develo...@barefeetware.com > -- > Comparison of SQLite GUI tools: > http://www.barefeetware.com/sqlite/compare/?ml > > ___ > 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] Lack of "decimal" support
On 27/03/2011, at 12:39 PM, Patrick Earl wrote: > Base-10 numbers are frequently used in financial calculations because > of their exact nature. SQLite forces us to store decimal numbers as > text to ensure precision is not lost. Unfortunately, this prevents > even simple operations such as retrieving all rows where an employee's > salary is greater than '100' (coded as a string since decimal types > are stored as strings). Can you store all money amounts as integers, as the cents value? That is exact, searchable etc. Thanks, Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lack of "decimal" support
I've found the decimal numbers to be most generally useful in narrow ranges. For reference, here are a couple notes on how other databases implement them: MSSQL stores up to 38 digits in 17 bytes, with a specific precision. http://msdn.microsoft.com/en-us/library/aa258832(v=sql.80).aspx PostgreSQL is more flexible and supports up to 1000 digits. http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL In order to get a jump on the implementation, I would suggest that it might be possible to use C routines from the PostgreSQL project or some appropriately licensed library. Perhaps an author from a numeric library would be willing to donate their work to the SQLite project. Patrick Earl On Sat, Mar 26, 2011 at 7:43 PM, Simon Slavin wrote: > > On 27 Mar 2011, at 2:39am, Patrick Earl wrote: > >> Base-10 numbers are frequently used in financial calculations because >> of their exact nature. SQLite forces us to store decimal numbers as >> text to ensure precision is not lost. Unfortunately, this prevents >> even simple operations such as retrieving all rows where an employee's >> salary is greater than '100' (coded as a string since decimal types >> are stored as strings). >> >> I would like to encourage the developers to consider adding support >> for base-10 numbers. This is clearly a very pertinent issue, as even >> this month there was another thread regarding decimal support. > > Intersting idea. You will need to develop your own C routines to do > calculations with decimals. Do you feel they should be implemented at a > fixed length or would you want to be able to use decimal strings of arbitrary > lengths ? > > Simon. > ___ > 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] SQLite & NHibernate
Greetings. I'm a committer for NHibernate who has been working on improving the support for SQLite. I've been able to get most of the over 3000 tests passing on SQLite. Kudos to Richard and the team for producing such an impressive little database. I wanted to share with you the main limitations I found on this journey in the hopes that some day they will no longer be limitations. They are ordered by my view on their importance. 1. Support for a base-10 numeric data type. 2. Support for altering tables (especially the removal or addition of foreign keys). Granted, tables can be updated by turning off foreign key constraints, copying all data, manually checking foreign key consistency, and then turning on foreign key constraints again. Not having the ability to alter tables ultimately leads to a great of complexity in any system that has to deal with updating database schemas. 3. FULL OUTER JOIN support. There are work-arounds, but implementing those as part of NHibernate proved quite complicated, so I opted to wait unless there seems to be extreme demand for it. 4. Some sort of locate function to get the index of substring within another string. I couldn't even find any way to emulate this (aside from user defined functions). 5. Support for operations like "= all (subquery)", "= some (subquery)", and "= any (subquery)". 6. Better support for distributed transactions. I don't pretend to be an expert here, but it seems plausible that SQLite could participate in a transaction across multiple databases. Perhaps implementing two phase commit would help with this. Thanks for your consideration. Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lack of "decimal" support
On 27 Mar 2011, at 2:39am, Patrick Earl wrote: > Base-10 numbers are frequently used in financial calculations because > of their exact nature. SQLite forces us to store decimal numbers as > text to ensure precision is not lost. Unfortunately, this prevents > even simple operations such as retrieving all rows where an employee's > salary is greater than '100' (coded as a string since decimal types > are stored as strings). > > I would like to encourage the developers to consider adding support > for base-10 numbers. This is clearly a very pertinent issue, as even > this month there was another thread regarding decimal support. Intersting idea. You will need to develop your own C routines to do calculations with decimals. Do you feel they should be implemented at a fixed length or would you want to be able to use decimal strings of arbitrary lengths ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Lack of "decimal" support
Base-10 numbers are frequently used in financial calculations because of their exact nature. SQLite forces us to store decimal numbers as text to ensure precision is not lost. Unfortunately, this prevents even simple operations such as retrieving all rows where an employee's salary is greater than '100' (coded as a string since decimal types are stored as strings). I would like to encourage the developers to consider adding support for base-10 numbers. This is clearly a very pertinent issue, as even this month there was another thread regarding decimal support. Thanks for your consideration. Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full Table Scan after Analyze
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/26/2011 03:12 AM, Black, Michael (IS) wrote: > When you say "All an index does" don't forget that an index is also usually > smaller than the data, thereby increase cache performance and reducing disk > seeks. That is muddied in the case (probably most common) where the index does not include all the columns needed for the query. Consequently the rowid has to be found in the index and then the main data has disk seeks to retrieve the remaining columns from the row. Seeking in the index will be random access whereas doing a table scan will predominantly be sequential access. These factors are why it is a not a trivial determination as to which is better and why analyze helps. It is also why an index can be slower more commonly than expected. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk2OWrAACgkQmOOfHg372QSNFQCgqXlZu7V09GSA0JWOq9in+JB7 u8EAn19m1Sn8RhV/grSWcIEuPJCAEU0v =LsnS -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full Table Scan after Analyze
When you say "All an index does" don't forget that an index is also usually smaller than the data, thereby increase cache performance and reducing disk seeks. For a good chunk of typical uses (large tables with simple lookups) an index is notably faster. I'll admit my use of sqtlite3 hasn't been on horrendously complex databases but I can say an index beats the pants off of non-indexed for all my usage (at least where I would expect it to). Much as one would expect. I tend to have 1000's to millions of rows with simple ID lookups. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Friday, March 25, 2011 6:33 PM To: j...@kreibi.ch; General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Full Table Scan after Analyze On 25 Mar 2011, at 11:11pm, Jay A. Kreibich wrote: > On Fri, Mar 25, 2011 at 10:30:59PM +, Simon Slavin scratched on the wall: > >> Actually I'm surprised and not terribly impressed that SQLite ever >> does a scan when there's an ideal index available. > > Why? Do you want it to run slower? > > Indexes are not magic bullets. Using an index to retrieve a row is > typically 5x to 20x more expensive than scanning a row. There are > plenty of instances when a scan will be faster than an index use, and > not just in small tables. Just as SQLite tries to use any index it > can to speed up a query, it also tries to avoid using indexes that > will slow it down-- and there are plenty of ways this can happen. You know, I'd never thought of that. All an index does is let you search a B-tree rather than a list. Thank you. Simon. ___ 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] Full Text Search
Hi, Thanks for help. Sumesh. On Sat, Mar 26, 2011 at 1:48 PM, Dan Kennedy wrote: > On 03/26/2011 02:18 PM, Sumesh KS wrote: >> Hi, >> >> I am Sumesh, student from india. I currently doing a project using qt >> and sqlite. I want to implement Full Text Search in that project. >> Anyone please tell me, from where i start to learn FTS and it's >> working and how it is implemented. > > http://www.sqlite.org/fts3.html > ___ > 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] Full Text Search
On 03/26/2011 02:18 PM, Sumesh KS wrote: > Hi, > > I am Sumesh, student from india. I currently doing a project using qt > and sqlite. I want to implement Full Text Search in that project. > Anyone please tell me, from where i start to learn FTS and it's > working and how it is implemented. http://www.sqlite.org/fts3.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Full Text Search
Hi, I am Sumesh, student from india. I currently doing a project using qt and sqlite. I want to implement Full Text Search in that project. Anyone please tell me, from where i start to learn FTS and it's working and how it is implemented. regards, Sumesh. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
