Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread James K. Lowden
On Thu, 13 Nov 2014 14:38:10 +
Simon Slavin  wrote:

> In summary, if you need ultimate precision, use integers.  If not,
> use 64-bit IEEE-571 like everyone else does without being sued.  If
> you somehow really need 23,10 maths, then you're going to have to
> write your own mathematical library anyway, because I'm not aware of
> any usable libraries which actually support 23,10 outside the world
> of physics.

http://www.mpfr.org/#free-sw

I'm sure you're aware of such things.  I don't believe the OP's problem
statement, but if he's bound and determined to go that route, I don't
see how he's obliged to write the math functions.  Why are none of
these suitable?  

--jkl

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [SQLite]Basic queries

2014-11-13 Thread Shinichiro Yoshioka
Thank you for your promt answer!

I understood.

2014-11-14 9:33 GMT+09:00 :

> On Thursday, November 13, 2014 04:27:02 PM Shinichiro Yoshioka wrote:
> > Is there any special reason why there are 2 dlls on the page?
> > And the dll for win 32bits doesn't work on win 64bits OS
> > in spite of exsistance of WOW64?
>
> While a 32 bit version will work on a 64 bit Windows as you said, it will
> not
> work when linked to a 64 bit application. The 64-bit version is supplied
> for
> developers of 64-bit applications.
>
> >
> > 2) I have compiled the amalgamation source code on win7 32bits OS.
> > Though I haven't specified any compile option, in this case,
> > For which OS platform is the generated binary?
> > for 32bits win OS or 64bits win OS?
>
> 32 bits.
>
> Charles
> ___
> 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] [SQLite]Basic queries

2014-11-13 Thread Shinichiro Yoshioka
Thank you for your explanation!.
I understood.

2014-11-14 9:33 GMT+09:00 Igor Tandetnik :

> On 11/13/2014 7:27 PM, Shinichiro Yoshioka wrote:
>
>> 1) For Windows platform, there are 2 dlls on SQLite download page.
>>  means, for 32bits and 64bits Windows OS.
>>  However, in my understanding, the dll for win 32bits can work
>>  also on 64bits win OS with WOW64 as it is.
>>
>>  Is there any special reason why there are 2 dlls on the page?
>>
>
> A 32-bit application can only load 32-bit DLLs, and a 64-bit application
> can only load 64-bit DLLs. Windows can run both kinds of applications, but
> each must have a matching flavor of DLLs it uses.
>
>  2) I have compiled the amalgamation source code on win7 32bits OS.
>>  Though I haven't specified any compile option, in this case,
>>  For which OS platform is the generated binary?
>>  for 32bits win OS or 64bits win OS?
>>
>
> Depends on which compiler you built it with - 32-bit or 64-bit.
> --
> Igor Tandetnik
>
>
> ___
> 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] [SQLite]Basic queries

2014-11-13 Thread Igor Tandetnik

On 11/13/2014 7:27 PM, Shinichiro Yoshioka wrote:

1) For Windows platform, there are 2 dlls on SQLite download page.
 means, for 32bits and 64bits Windows OS.
 However, in my understanding, the dll for win 32bits can work
 also on 64bits win OS with WOW64 as it is.

 Is there any special reason why there are 2 dlls on the page?


A 32-bit application can only load 32-bit DLLs, and a 64-bit application 
can only load 64-bit DLLs. Windows can run both kinds of applications, 
but each must have a matching flavor of DLLs it uses.



2) I have compiled the amalgamation source code on win7 32bits OS.
 Though I haven't specified any compile option, in this case,
 For which OS platform is the generated binary?
 for 32bits win OS or 64bits win OS?


Depends on which compiler you built it with - 32-bit or 64-bit.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [SQLite]Basic queries

2014-11-13 Thread sqlite
On Thursday, November 13, 2014 04:27:02 PM Shinichiro Yoshioka wrote:
> Is there any special reason why there are 2 dlls on the page?
> And the dll for win 32bits doesn't work on win 64bits OS
> in spite of exsistance of WOW64?

While a 32 bit version will work on a 64 bit Windows as you said, it will not 
work when linked to a 64 bit application. The 64-bit version is supplied for 
developers of 64-bit applications.

> 
> 2) I have compiled the amalgamation source code on win7 32bits OS.
> Though I haven't specified any compile option, in this case,
> For which OS platform is the generated binary?
> for 32bits win OS or 64bits win OS?

32 bits.

Charles
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] [SQLite]Basic queries

2014-11-13 Thread Shinichiro Yoshioka
Hi,

Can anyone tell me answers against below my queries?
Since I'm beginner of SQLite, please forgive me about
so basic queries.

1) For Windows platform, there are 2 dlls on SQLite download page.
means, for 32bits and 64bits Windows OS.
However, in my understanding, the dll for win 32bits can work
also on 64bits win OS with WOW64 as it is.

Is there any special reason why there are 2 dlls on the page?
And the dll for win 32bits doesn't work on win 64bits OS
in spite of exsistance of WOW64?

2) I have compiled the amalgamation source code on win7 32bits OS.
Though I haven't specified any compile option, in this case,
For which OS platform is the generated binary?
for 32bits win OS or 64bits win OS?

Thanks,
Dekochan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] sqlite bug report

2014-11-13 Thread Richard Hipp
On Thu, Nov 13, 2014 at 4:41 PM, Hinrichsen, John 
wrote:

> In this example, bad data is returned.  There is no assert.  valgrind does
> not complain either.
>

It asserts if you recompile with -DSQLITE_DEBUG



>
> Is there an ETA on when 3.8.7.2 will be released?
>

Next week sometime.  Why not just pull the latest code from the source tree?


>
>
> On Thu, Nov 13, 2014 at 1:12 PM, Richard Hipp  wrote:
>
> > This is https://www.sqlite.org/src/info/094d39a4c95ee4 which has been
> > fixed
> > in trunk and will be fixed in 3.8.7.2.
> >
> > On Thu, Nov 13, 2014 at 1:05 PM, Hinrichsen, John 
> > wrote:
> >
> > > The following SQL produces an incorrect result with sqlite-3.8.7.1:
> > >
> > > CREATE TABLE A(
> > >   symbol TEXT,
> > >   type TEXT
> > > );
> > > INSERT INTO A VALUES('ABCDEFG','chars');
> > > INSERT INTO A VALUES('1234567890','num');
> > > CREATE TABLE B(
> > >   chars TEXT,
> > >   num TEXT
> > > );
> > >
> > > CREATE TABLE IF NOT EXISTS C AS
> > > SELECT A.symbol AS symbol,A.type,
> > > CASE A.type
> > > WHEN 'chars' THEN A.symbol
> > > WHEN 'num' THEN B.chars
> > > ELSE NULL
> > > END AS chars
> > > FROM A LEFT OUTER JOIN B ON A.type='num'  AND B.num=A.symbol;
> > >
> > > SELECT * FROM C;
> > >
> > > with 3.8.7.1:
> > >
> > > sqlite> SELECT * FROM C;
> > > ABCDEFG|chars|ABCDEFG
> > > 1234567890|num|1234567
> > >
> > > with 3.8.6:
> > >
> > > sqlite> SELECT * FROM C;
> > > ABCDEFG|chars|ABCDEFG
> > > 1234567890|num|
> > >
> > > --
> > >
> > > This message contains confidential information and is intended only for
> > the
> > > individual named. If you are not the named addressee, you should not
> > > disseminate, distribute, alter or copy this e-mail. Please notify the
> > > sender immediately by e-mail if you have received this e-mail by
> mistake
> > > and delete this e-mail from your system. E-mail transmissions cannot be
> > > guaranteed to be secure or without error as information could be
> > > intercepted, corrupted, lost, destroyed, or arrive late or incomplete.
> > The
> > > sender, therefore, does not accept liability for any errors or
> omissions
> > in
> > > the contents of this message which arise during or as a result of
> e-mail
> > > transmission. If verification is required, please request a hard-copy
> > > version. This message is provided for information purposes and should
> not
> > > be construed as a solicitation or offer to buy or sell any securities
> or
> > > related financial instruments in any jurisdiction.
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
> --
>
> This message contains confidential information and is intended only for the
> individual named. If you are not the named addressee, you should not
> disseminate, distribute, alter or copy this e-mail. Please notify the
> sender immediately by e-mail if you have received this e-mail by mistake
> and delete this e-mail from your system. E-mail transmissions cannot be
> guaranteed to be secure or without error as information could be
> intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The
> sender, therefore, does not accept liability for any errors or omissions in
> the contents of this message which arise during or as a result of e-mail
> transmission. If verification is required, please request a hard-copy
> version. This message is provided for information purposes and should not
> be construed as a solicitation or offer to buy or sell any securities or
> related financial instruments in any jurisdiction.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] sqlite bug report

2014-11-13 Thread Hinrichsen, John
In this example, bad data is returned.  There is no assert.  valgrind does
not complain either.

Is there an ETA on when 3.8.7.2 will be released?


On Thu, Nov 13, 2014 at 1:12 PM, Richard Hipp  wrote:

> This is https://www.sqlite.org/src/info/094d39a4c95ee4 which has been
> fixed
> in trunk and will be fixed in 3.8.7.2.
>
> On Thu, Nov 13, 2014 at 1:05 PM, Hinrichsen, John 
> wrote:
>
> > The following SQL produces an incorrect result with sqlite-3.8.7.1:
> >
> > CREATE TABLE A(
> >   symbol TEXT,
> >   type TEXT
> > );
> > INSERT INTO A VALUES('ABCDEFG','chars');
> > INSERT INTO A VALUES('1234567890','num');
> > CREATE TABLE B(
> >   chars TEXT,
> >   num TEXT
> > );
> >
> > CREATE TABLE IF NOT EXISTS C AS
> > SELECT A.symbol AS symbol,A.type,
> > CASE A.type
> > WHEN 'chars' THEN A.symbol
> > WHEN 'num' THEN B.chars
> > ELSE NULL
> > END AS chars
> > FROM A LEFT OUTER JOIN B ON A.type='num'  AND B.num=A.symbol;
> >
> > SELECT * FROM C;
> >
> > with 3.8.7.1:
> >
> > sqlite> SELECT * FROM C;
> > ABCDEFG|chars|ABCDEFG
> > 1234567890|num|1234567
> >
> > with 3.8.6:
> >
> > sqlite> SELECT * FROM C;
> > ABCDEFG|chars|ABCDEFG
> > 1234567890|num|
> >
> > --
> >
> > This message contains confidential information and is intended only for
> the
> > individual named. If you are not the named addressee, you should not
> > disseminate, distribute, alter or copy this e-mail. Please notify the
> > sender immediately by e-mail if you have received this e-mail by mistake
> > and delete this e-mail from your system. E-mail transmissions cannot be
> > guaranteed to be secure or without error as information could be
> > intercepted, corrupted, lost, destroyed, or arrive late or incomplete.
> The
> > sender, therefore, does not accept liability for any errors or omissions
> in
> > the contents of this message which arise during or as a result of e-mail
> > transmission. If verification is required, please request a hard-copy
> > version. This message is provided for information purposes and should not
> > be construed as a solicitation or offer to buy or sell any securities or
> > related financial instruments in any jurisdiction.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee, you should not 
disseminate, distribute, alter or copy this e-mail. Please notify the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] sqlite bug report

2014-11-13 Thread Richard Hipp
This is https://www.sqlite.org/src/info/094d39a4c95ee4 which has been fixed
in trunk and will be fixed in 3.8.7.2.

On Thu, Nov 13, 2014 at 1:05 PM, Hinrichsen, John 
wrote:

> The following SQL produces an incorrect result with sqlite-3.8.7.1:
>
> CREATE TABLE A(
>   symbol TEXT,
>   type TEXT
> );
> INSERT INTO A VALUES('ABCDEFG','chars');
> INSERT INTO A VALUES('1234567890','num');
> CREATE TABLE B(
>   chars TEXT,
>   num TEXT
> );
>
> CREATE TABLE IF NOT EXISTS C AS
> SELECT A.symbol AS symbol,A.type,
> CASE A.type
> WHEN 'chars' THEN A.symbol
> WHEN 'num' THEN B.chars
> ELSE NULL
> END AS chars
> FROM A LEFT OUTER JOIN B ON A.type='num'  AND B.num=A.symbol;
>
> SELECT * FROM C;
>
> with 3.8.7.1:
>
> sqlite> SELECT * FROM C;
> ABCDEFG|chars|ABCDEFG
> 1234567890|num|1234567
>
> with 3.8.6:
>
> sqlite> SELECT * FROM C;
> ABCDEFG|chars|ABCDEFG
> 1234567890|num|
>
> --
>
> This message contains confidential information and is intended only for the
> individual named. If you are not the named addressee, you should not
> disseminate, distribute, alter or copy this e-mail. Please notify the
> sender immediately by e-mail if you have received this e-mail by mistake
> and delete this e-mail from your system. E-mail transmissions cannot be
> guaranteed to be secure or without error as information could be
> intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The
> sender, therefore, does not accept liability for any errors or omissions in
> the contents of this message which arise during or as a result of e-mail
> transmission. If verification is required, please request a hard-copy
> version. This message is provided for information purposes and should not
> be construed as a solicitation or offer to buy or sell any securities or
> related financial instruments in any jurisdiction.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] sqlite bug report

2014-11-13 Thread Hinrichsen, John
The following SQL produces an incorrect result with sqlite-3.8.7.1:

CREATE TABLE A(
  symbol TEXT,
  type TEXT
);
INSERT INTO A VALUES('ABCDEFG','chars');
INSERT INTO A VALUES('1234567890','num');
CREATE TABLE B(
  chars TEXT,
  num TEXT
);

CREATE TABLE IF NOT EXISTS C AS
SELECT A.symbol AS symbol,A.type,
CASE A.type
WHEN 'chars' THEN A.symbol
WHEN 'num' THEN B.chars
ELSE NULL
END AS chars
FROM A LEFT OUTER JOIN B ON A.type='num'  AND B.num=A.symbol;

SELECT * FROM C;

with 3.8.7.1:

sqlite> SELECT * FROM C;
ABCDEFG|chars|ABCDEFG
1234567890|num|1234567

with 3.8.6:

sqlite> SELECT * FROM C;
ABCDEFG|chars|ABCDEFG
1234567890|num|

-- 

This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee, you should not 
disseminate, distribute, alter or copy this e-mail. Please notify the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread Hick Gunter
IIRC there was a programmer working for a bank that managed to siphon off the 
sub-unit fractions that the interest calculating software generated (how much 
interest is owed for $1 at 0,25% p.a. for 2 days*) onto his own account and 
temporarily got rich quick.

$1 * 0,25% = $25 (interest for 1 year)
$25 * 2 / 360 = $0,1389 (interest for 2 days)

This is split into 13 cents for the client and nearly 0,9 cents that the bank 
keeps

-Urspr√ľngliche Nachricht-
Von: Simon Slavin [mailto:slav...@bigfraud.org]
Gesendet: Donnerstag, 13. November 2014 18:07
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] [SQLite] Support for 23,10 Precision number format


On 13 Nov 2014, at 3:44pm, Dominique Devienne  wrote:

> On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin  wrote:
>
>> 100,000,000,000,000,000,000
>
> Assuming he means Oracle's NUMBER(23, 10), and given [1], that's more
>
> 9,999,999,999,999.99
>
> i.e. "just" under 10 trillion max, with 10 decimal digits accuracy, and not
> 100 million trillion.

But he's using the field to store an amount of money in.  So why ask for 
anything with ten places after the decimal point ?  No genuine currency 
requires more than three places.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread RSmith


On 2014/11/13 19:06, Simon Slavin wrote:



On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin  wrote:


100,000,000,000,000,000,000

But he's using the field to store an amount of money in.  So why ask for 
anything with ten places after the decimal point ?  No genuine currency 
requires more than three places.


He mightn't be storing the actual money, but the working factors and figures used in the formulas seeding the money calculations, 
conversions, etc. which may have a bit higher accuracy required. Though if this is the case, I would suggest getting back to your 
original suggestion of saving the money as integer cents (or milli-dollars if you like) and storing the factors and figures as good 
old 64 bit IEEE floats which will give you up to 15 digits after the decimal point accurately, twice the required accuracy - not to 
mention the luxury of being able to access both types natively in the api /and/ represent it easily in human-readable format in data 
dumps.


By the way, my Oracle friends should intersect here if need be, but I believe the oracle method of /decimal(n,m)/ is simply a 
representation directive and constraint, there is no native datatype that actually stores or communicates such a value. Oracle 
stores it internally in a very specific arrangement of bytes and you need to still interpret it in your software.


Nor do I think the Oracle SQL engine would be able to do (without any add-on 
modules):
SELECT (B.Money*B.Factor) FROM BigMoney B;
from said table with Money and Factor both as decimal(38,6) each containing >30 
decimals - or would it?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread Simon Slavin

On 13 Nov 2014, at 3:44pm, Dominique Devienne  wrote:

> On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin  wrote:
> 
>> 100,000,000,000,000,000,000
> 
> Assuming he means Oracle's NUMBER(23, 10), and given [1], that's more
> 
> 9,999,999,999,999.99
> 
> i.e. "just" under 10 trillion max, with 10 decimal digits accuracy, and not
> 100 million trillion.

But he's using the field to store an amount of money in.  So why ask for 
anything with ten places after the decimal point ?  No genuine currency 
requires more than three places.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread Stephan Beal
On Thu, Nov 13, 2014 at 4:50 PM, Dinesh Navsupe 
wrote:

> I mean Oracle's NUMBER(23, 10), and given [1], that's more
>


> > > > My need is 23 decimal digits of precision. We work on complex payout
> > > > calculation engine where in formula outputs are quite large numbers
> and
> > > > clients do not want to round off.
>

The first answer to this thread might be helpful (but also probably not
what you want to hear):

http://sqlite.1065341.n5.nabble.com/How-point-numbers-are-they-stored-in-sqlite-td35739.html

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread Dinesh Navsupe
You are right Dominique.

I mean Oracle's NUMBER(23, 10), and given [1], that's more

9,999,999,999,999.99
Thanks.



On Thu, Nov 13, 2014 at 9:14 PM, Dominique Devienne 
wrote:

> On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin 
> wrote:
>
> > On 13 Nov 2014, at 1:01pm, Dinesh Navsupe 
> > wrote:
> >
> > > My need is 23 decimal digits of precision. We work on complex payout
> > > calculation engine where in formula outputs are quite large numbers and
> > > clients do not want to round off.
> >
>
>
> > [...].  So for 23 digits of accuracy you seem to have a requirement to
> > manipulate
> >
> > 100,000,000,000,000,000,000
> >
>
> Assuming he means Oracle's NUMBER(23, 10), and given [1], that's more
>
> 9,999,999,999,999.99
>
> i.e. "just" under 10 trillion max, with 10 decimal digits accuracy, and not
> 100 million trillion.
>
> FWIW. --DD
>
> [1] http://www.orafaq.com/wiki/Number
> ___
> 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] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread Dominique Devienne
On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin  wrote:

> On 13 Nov 2014, at 1:01pm, Dinesh Navsupe 
> wrote:
>
> > My need is 23 decimal digits of precision. We work on complex payout
> > calculation engine where in formula outputs are quite large numbers and
> > clients do not want to round off.
>


> [...].  So for 23 digits of accuracy you seem to have a requirement to
> manipulate
>
> 100,000,000,000,000,000,000
>

Assuming he means Oracle's NUMBER(23, 10), and given [1], that's more

9,999,999,999,999.99

i.e. "just" under 10 trillion max, with 10 decimal digits accuracy, and not
100 million trillion.

FWIW. --DD

[1] http://www.orafaq.com/wiki/Number
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread Simon Slavin

On 13 Nov 2014, at 1:01pm, Dinesh Navsupe  wrote:

> My need is 23 decimal digits of precision. We work on complex payout
> calculation engine where in formula outputs are quite large numbers and
> clients do not want to round off.

If you're working with floating-point numbers, you will get roundoff [1].  
Integer arithmetic really is how big banks work.  That way you don't have to 
test for rounding problems and never get accused of Salami Slicing.  If you 
really need money precision you'll be working in integers representing paise, 
pennies, halalas, whatever.  You'd have asked for 23 digit integers, not 23,10.

Since the abandonment of the Lira, no decimal country currency has needed 
anything more than three places of decimals for manipulation.  So for 23 digits 
of accuracy you seem to have a requirement to manipulate

100,000,000,000,000,000,000

units of currency with perfect accuracy.  Even the World Bank Group doesn't 
need that.  And I don't think the total wealth of any country in its own 
currency requires that many digits. 

I've worked with international financial organisations and we never did 
anything that needed 23 digits of precision, and that includes complicated 
cumulative interest calculations and those unbelievable asset value predictions 
that require integration and antilogs.

In summary, if you need ultimate precision, use integers.  If not, use 64-bit 
IEEE-571 like everyone else does without being sued.  If you somehow really 
need 23,10 maths, then you're going to have to write your own mathematical 
library anyway, because I'm not aware of any usable libraries which actually 
support 23,10 outside the world of physics.

Simon.

[1] This is a little hand-waving but only a little.  I'm only mentioning that 
because I don't want someone in the industry to dig this up and use it against 
me.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread Dominique Devienne
On Thu, Nov 13, 2014 at 2:33 PM, RSmith  wrote:

> On 2014/11/13 15:01, Dinesh Navsupe wrote:
>
>> My need is 23 decimal digits of precision. We work on complex payout
>> calculation engine where in formula outputs are quite large numbers and
>> clients do not want to round off.
>
>
If IEEE double is not good enough, you can use already suggested string or
blob based representation, or store the integral part and decimal part in
separate integer-typed columns, which have also the advantage to be smaller
for small values than larger one (varint format [1]). Or if your exact
arithmetic package is based on rational numbers, store
numerator/denominator in separate columns similarly. Both remaining
human-readable like string, and it might be faster to re-instantiate your
custom integer type than parsing a string. FWIW. --DD

[1] https://www.sqlite.org/fileformat2.html#varint
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread RSmith


On 2014/11/13 15:01, Dinesh Navsupe wrote:

Hi,

My need is 23 decimal digits of precision. We work on complex payout
calculation engine where in formula outputs are quite large numbers and
clients do not want to round off.


I do not think that re-stating your need suffices as a good enough argument. We heard you the first time and understand the need and 
understand exactly that you are working with numbers in that precision so you would like to be able to store them efficiently. 
SQLite does not do this in binary terms (it can produce 64 bit Integers/Reals at best in accordance with IEEE as some others already 
explained). No other Engine does this natively either (meaning that in your code there is no way to exchange that size number with 
the prepared statement/api/object within a standard C data type other than a string or blob). Using strings is preferable because 
they will be human readable in DB dumps, but you will need to use a BigInt type library or create your own conversion routines to 
actually translate those numbers to and from strings for the purposes of communicating it to/from any DB engine, SQLite included.




We want to use SQLite for local disk data store and calculations.


That's a good decision, but it comes with a bit of work, same as any other 
chosen RDBMS.

As an aside, do you really need that precision? You can accurately state the American National Debt in dollars and cents within 16 
digits of precision (That's 18 trillion dollars btw, or 18^12). I can't imagine a payout ever needing to be much higher than that, 
or by another 8 or so digits more precise? (well, that would allow you to express the entire World's collective national debts in 
Zimbabwe dollars). 16 digits can very well be represented by a Float in SQLite and transferred to your program in standard 64-bit 
floats via the API. Above that you will need to make the routines.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread Dinesh Navsupe
Hi,

My need is 23 decimal digits of precision. We work on complex payout
calculation engine where in formula outputs are quite large numbers and
clients do not want to round off.

We want to use SQLite for local disk data store and calculations.

Thanks,
Dinesh Navsupe

On Thu, Nov 13, 2014 at 6:14 PM, Simon Slavin  wrote:

>
> On 13 Nov 2014, at 12:23pm, Dinesh Navsupe 
> wrote:
>
> > Does any of SQLite data Type support 23,10 precision format for Number?
> >
> > If yes, could you pleas help with right data type or approach to achieve
> > this.
>
> SQL stores REAL numbers in a REAL field which conforms to 64-bit IEEE 754
> (as much as SQL permits).  This allows 16 decimal digits of precision.
>
> You can store numbers of greater precision than that -- by storing them as
> strings or BLOBs.  You just can't have SQLite do its own maths on them.
>
> > If No, then is there something that can be added to SQLite and how
> quickly?
>
> It is unlikely that the developer team would be interested in doing this.
> Given that the source code for SQLite is open, you might want to implement
> them yourself.  However, the test library for having the developer team do
> this this would probably have to be huge.
>
> SQLite4, which is not released yet, uses an 18-digit decimal number with a
> 3-digit base-10 exponent.  It is possible that this might change if you are
> able to produce a good argument for doing so
>
> 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] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread Hick Gunter
Data types are 64bit integer (~18 decimal digits) and 64 Bit IEEE Float(11 bit 
exponent, 52 bit fraction), so no.

Store the numbers as TEXT (human readable)  or BLOB (e.g. 128Bit binary) and 
write user-defined functions to manipulate them.

-Urspr√ľngliche Nachricht-
Von: Dinesh Navsupe [mailto:dinesh.navs...@gmail.com]
Gesendet: Donnerstag, 13. November 2014 13:23
An: sqlite-users@sqlite.org
Betreff: [sqlite] [SQLite] Support for 23,10 Precision number format

Hi,

Does any of SQLite data Type support 23,10 precision format for Number?

If yes, could you pleas help with right data type or approach to achieve this.

If No, then is there something that can be added to SQLite and how quickly?

Thanks,
Dinesh Navsupe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread Simon Slavin

On 13 Nov 2014, at 12:23pm, Dinesh Navsupe  wrote:

> Does any of SQLite data Type support 23,10 precision format for Number?
> 
> If yes, could you pleas help with right data type or approach to achieve
> this.

SQL stores REAL numbers in a REAL field which conforms to 64-bit IEEE 754 (as 
much as SQL permits).  This allows 16 decimal digits of precision.

You can store numbers of greater precision than that -- by storing them as 
strings or BLOBs.  You just can't have SQLite do its own maths on them.

> If No, then is there something that can be added to SQLite and how quickly?

It is unlikely that the developer team would be interested in doing this.  
Given that the source code for SQLite is open, you might want to implement them 
yourself.  However, the test library for having the developer team do this this 
would probably have to be huge.

SQLite4, which is not released yet, uses an 18-digit decimal number with a 
3-digit base-10 exponent.  It is possible that this might change if you are 
able to produce a good argument for doing so

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread Dinesh Navsupe
Hi,

Does any of SQLite data Type support 23,10 precision format for Number?

If yes, could you pleas help with right data type or approach to achieve
this.

If No, then is there something that can be added to SQLite and how quickly?

Thanks,
Dinesh Navsupe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Using sqlite3_backup* while the destination connection has "busy" read-only statements.

2014-11-13 Thread Dan Kennedy

On 11/13/2014 05:26 AM, Shaun Seckman (Firaxis) wrote:

Greetings all,
I'm running into a situation in where our application is crashing during a call 
to sqlite_backup_finish inside of btreeParseCellPtr because some of the 
structure is corrupted.

Both the source and destination database are using the same page sizes and I'm 
running everything from within a single-thread.

I'd like to get clarification on one possible issue in order to determine 
whether this is the culprit or at least rule it out.  This destination 
connection (which is an in-memory database) has several prepared statements, 
some of which are marked as busy (from sqlite3_stmt_busy) due to having step 
called but not reset.  All of these statements are read-only (simple select 
statements).

My question is, is it safe to have these busy statements on the destination 
connection while the backup happens (again, this is all single threaded so 
nothing happens DURING the backup process)?


It's not safe.

There are few reasons for this internally - the most obvious is that the 
backup operation might change the database schema.


The docs seem to omit this, but basically leaving the active statements 
around while the backup happens is the same as using the destination 
database while the backup is ongoing. See the "Concurrent Usage of 
Database Handles" section at the end of this page:


  http://www.sqlite.org/c3ref/backup_finish.html



If not,  then should sqlite3_backup_init check for this (at least in debug) and 
return NULL if any statements are busy?


I think we likely should. In both debug and non-debug builds.

Thanks for reporting this.

Dan.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users