Re: [sqlite] Lack of "decimal" support

[Nico Williams]

On Sun, Mar 27, 2011 at 10:20 PM, Darren Duncan  wrote:
> Nico Williams wrote:
>> User defined types.  There are two types in particular that I'd like
>> to see added:
>>
>>  - Bit strings.  Bit strings are like character strings, but the
>> elements can only be bits.  The key is that bit string length matters
>> when it comes to collation (000 sorts before ).  Related
>> sub-types: IP (v4 and 6) addresses (e.g., 10/8 is easily represented
>> as an eight bit long bit string: 1010, while 10.1.2.3.4 is easily
>> represented as a 32 bit long bit string).
>
> SQLite already has Blobs, and I see those as being exactly the same thing, 
> which
> is a string of bits.  Maybe you're just wanting more operators so it is easier
> to introspect or manipulate them? -- Darren Duncan

Blobs are _octet_ strings.  Sure, one could write functions that
encode and manipulate bit strings as blobs were the first octet (or
three bits from it) encodes the remainder of the bit string length div
8.   (SQLite3 would have to do something similar anyways.  The
difference lies in syntax.)

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

[sqlite] ICU Collator Base on Locale and Customize Rule

[ashish yadav]

Hi ,

If its possible to use Collator of ICU collation service which is base on
both simultaneously :
  1. Locale base like "ko_KR"   ( coll = ucol_open("ko_KR", ) )
  2. Customized Rules ie "tailoring" ( coll = ucol_openRules(rlz, rlen,
UCOL_OFF,UCOL_TERTIARY,NULL,) )

  Collator_Final = Collator_Locale  + Collator_customize_rule

If yes, then please suggest how we can use collator base on both at same
time ?

Thanks in Advance.

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

Re: [sqlite] Lack of "decimal" support

[Darren Duncan]

Nico Williams wrote:
> User defined types.  There are two types in particular that I'd like
> to see added:
> 
>  - Bit strings.  Bit strings are like character strings, but the
> elements can only be bits.  The key is that bit string length matters
> when it comes to collation (000 sorts before ).  Related
> sub-types: IP (v4 and 6) addresses (e.g., 10/8 is easily represented
> as an eight bit long bit string: 1010, while 10.1.2.3.4 is easily
> represented as a 32 bit long bit string).

SQLite already has Blobs, and I see those as being exactly the same thing, 
which 
is a string of bits.  Maybe you're just wanting more operators so it is easier 
to introspect or manipulate them? -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Import data in SQLite from excel using C# code

[Mohd Radzi Ibrahim]

Hi,
Try use www.libxl.com that has C/C++/C#/Delphi interface to read excel files. 
The rest are just normal sqlite commands.


On 28-Mar-2011, at 9:17 AM, Deepti Marathe wrote:

> 
> Hi, 
> 
> I am new to SQLite and am using it for the first time. I need to create an
> application using C# that will export the data from EXCEL to SQLite.  Please
> can anybody guide me.
> Thanks in advance!
> 
> -- 
> View this message in context: 
> http://old.nabble.com/Import-data-in-SQLite-from-excel-using-C--code-tp31253267p31253267.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

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

Re: [sqlite] SQLite & NHibernate

[Nico Williams]

On Sat, Mar 26, 2011 at 8:56 PM, Patrick Earl  wrote:
> 1.  Support for a base-10 numeric data type.

Looking at the wikipedia page for NHibernate it seems that you don't
export a SQL interface -- all SQL is generated.  So, given that, you
should be able to generate SQLite3 statements that use user-defined
functions for handling bignums.  There have been other threads on this
topic recently.

> 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.

If it's just foreign keys you could just generate/drop triggers (there
used to be a program for that).  Actually, most table constraints can
be implemented via triggers (CHECK? check; FOREIGN KEY? check; UNIQUE?
check!); only collation can't be handled via triggers.

> 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.

You could always use a UNION to emulate FULL OUTER JOIN (a UNION of
two LEFT OUTER JOINs with the tables swapped).  Support in the engine
could probably be significantly more efficient than that.

> 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).

You can define that yourself.

> 5.  Support for operations like "= all (subquery)", "= some
> (subquery)", and "= any (subquery)".

What's that?

> 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.

If by distributed you mean "client/server" then SQLite3 is likely not
for you (but there are third party
projects that add a networked layer above SQLite).  In any case,
SQLite3 does do two-phase commits (and provides ACID semantics).

Nico

PS: I'm just a SQLite3 fan.  My comments above you be taken with salt.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Nico Williams]

On Sun, Mar 27, 2011 at 11:33 AM, jeff archer  wrote:
>>From: Patrick Earl 
>>Subject: Re: [sqlite] Lack of "decimal" support
>>
>>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.
>
> So, like a virtual type interface.  This would be a nice feature.

User defined types.  There are two types in particular that I'd like
to see added:

 - Bit strings.  Bit strings are like character strings, but the
elements can only be bits.  The key is that bit string length matters
when it comes to collation (000 sorts before ).  Related
sub-types: IP (v4 and 6) addresses (e.g., 10/8 is easily represented
as an eight bit long bit string: 1010, while 10.1.2.3.4 is easily
represented as a 32 bit long bit string).

 - Bignums.  64-bit signed integers are not enough :(

However, adding user-defined types must not be easy, since, among
other things, it means making the lexer run-time pluggable so it can
recognize constant value tokens for the new type (unless one is
willing to rebuild SQLite every time one adds a type).

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

Re: [sqlite] Installing _sqlite3.so for Python 2.7 on ubuntu

[Tim Johnson]

* Tim Johnson  [110327 16:59]:
> I have python 2.6 on my ubuntu 10.04 workstation.
> the sqlite module imports without a problem.
> 
> I have installed python 2.7 by compiling it.
> There is no /usr/local/lib/python2.7/lib-dynload/_sqlite3.so,
> so am getting an import error.
> What do I need to do?
 
  OK. I solved it. (For ubuntu) I had to install libsqlite3-dev.
  Not sure what the solution would be on other distros.
  tj

> FYI: Programmer 24 years, linux 11 years, python 8 years.
> Never have used sqlite.
> 
> TIA
> -- 
> Tim 
> tim at johnsons-web dot com or akwebsoft dot com
> http://www.akwebsoft.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
Tim 
tim at johnsons-web dot com or akwebsoft dot com
http://www.akwebsoft.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Import data in SQLite from excel using C# code

[Deepti Marathe]

Hi, 

I am new to SQLite and am using it for the first time. I need to create an
application using C# that will export the data from EXCEL to SQLite.  Please
can anybody guide me.
Thanks in advance!

-- 
View this message in context: 
http://old.nabble.com/Import-data-in-SQLite-from-excel-using-C--code-tp31253267p31253267.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] sqlite3_step behavior on empty set

[Igor Tandetnik]

Sam Carleton  wrote:
> Is my impression correct that when calling sqlite3_step() on a query
> that returns no rows, the result will be [SQLITE_DONE]?

Yes.

> If that is the case, might that be added to the documentation?

"SQLITE_DONE means that the statement has finished executing successfully."
"If the SQL statement being executed returns any data, then SQLITE_ROW is 
returned each time a new row of data is ready for processing by the caller."

The implication is that if SQL statement doesn't return any data, then 
sqlite3_step doesn't return SQLITE_ROW. Since it nevertheless finishes 
executing successfully, it returns SQLITE_DONE.
-- 
Igor Tandetnik

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

[sqlite] Installing _sqlite3.so for Python 2.7 on ubuntu

[Tim Johnson]

I have python 2.6 on my ubuntu 10.04 workstation.
the sqlite module imports without a problem.

I have installed python 2.7 by compiling it.
There is no /usr/local/lib/python2.7/lib-dynload/_sqlite3.so,
so am getting an import error.
What do I need to do?

FYI: Programmer 24 years, linux 11 years, python 8 years.
Never have used sqlite.

TIA
-- 
Tim 
tim at johnsons-web dot com or akwebsoft dot com
http://www.akwebsoft.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step behavior on empty set

[Simon Slavin]

On 27 Mar 2011, at 9:24pm, Sam Carleton wrote:

> Is my impression correct that when calling sqlite3_step() on a query
> that returns no rows, the result will be [SQLITE_DONE]?  If that is
> the case, might that be added to the documentation?

The SQLite documentation would profit greatly from a list of result values each 
function can generate and what they mean (when it's not obvious).

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

[sqlite] sqlite3_step behavior on empty set

[Sam Carleton]

Is my impression correct that when calling sqlite3_step() on a query
that returns no rows, the result will be [SQLITE_DONE]?  If that is
the case, might that be added to the documentation?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Simon Slavin]

On 27 Mar 2011, at 7:17pm, Darren Duncan wrote:

> Simon Slavin wrote:
>> I forgot a bunch of functions.  You need to be able to do comparisons, so you
>> can determine whether one decimal is greater, less or equal to another.  And
>> users will want abs(), max(), min(), round(), avg(), sum(), and total().
> 
> What is this "total" function you speak of and how does it differ from "sum"?

http://www.sqlite.org/lang_aggfunc.html

> Also, it's better to call a function "mean" than "avg" if that's what is 
> intended, since there are other kinds of averages like "median" and "mode".

Sorry, it's in the Standard that way.  See, for example,

http://www.postgresql.org/docs/9.0/static/functions-aggregate.html

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

Re: [sqlite] Lack of "decimal" support

[Darren Duncan]

Simon Slavin wrote:
> I forgot a bunch of functions.  You need to be able to do comparisons, so you
> can determine whether one decimal is greater, less or equal to another.  And
> users will want abs(), max(), min(), round(), avg(), sum(), and total().

What is this "total" function you speak of and how does it differ from "sum"?

Also, it's better to call a function "mean" than "avg" if that's what is 
intended, since there are other kinds of averages like "median" and "mode".

-- 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

[jeff archer]

>From: Patrick Earl 
>Subject: Re: [sqlite] Lack of "decimal" support
>
>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.
>
 
So, like a virtual type interface.  This would be a nice feature.  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Simon Slavin]

On 27 Mar 2011, at 11:49am, Simon Slavin wrote:

> decimal + decimal --> decimal
> decimal - decimal --> decimal
> decimal * real --> decimal
> decimal / real --> decimal
> decimal / decimal --> real

I forgot a bunch of functions.  You need to be able to do comparisons, so you 
can determine whether one decimal is greater, less or equal to another.  And 
users will want abs(), max(), min(), round(), avg(), sum(), and total().

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

Re: [sqlite] Lack of "decimal" support

[Jean-Christophe Deschamps]

>This page has a lot of info about
>Decimal Number support, including
>a set of libraries:
>
>http://speleotrove.com/decimal/

Yes!  IBM and Intel are two of the big names having done significant 
work in this direction and made research and/or results publicly available.

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

Re: [sqlite] Lack of "decimal" support

[Jean-Christophe Deschamps]

>There are many cases where people are doing calculations or using 
>numbers expecting them to retain all digits.  This would allow the BCD 
>type to be used for that if they really need it.

Currency conversions (rarely exact!) or tax (or margin) calculations 
come to mind as very common uses requiring a bit more than plus and 
minus operations with extended integers support and expected to be 
available at the SQL level along with correct rounding.

It's true that embedding anything like full GNU GMP would make "lite" a 
joke, but a decent subset and something like decimal(n,p) type would 
certainly be beneficial for a large number of SLite 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

[Konrad J Hambrick]

All --

This page has a lot of info about
Decimal Number support, including
a set of libraries:

http://speleotrove.com/decimal/

-- kjh


Black, Michael (IS) wrote, On 03/27/2011 06:29 AM:
> Base 10 multiplication is needed.  Although money is the main reason for 
> doing BCD due to cumulative errors it's not the only reason.  So I'd 
> recommend adding decimal*decimal ->  decimal just to be complete.  It's easy 
> enough to implement using the + function so could just be noted as "slow".
>
> There are many cases where people are doing calculations or using numbers 
> expecting them to retain all digits.  This would allow the BCD type to be 
> used for that if they really need it.
>
> 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: Sunday, March 27, 2011 5:49 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Lack of "decimal" support
>
> For those who are scared by this, there really isn't that much to do.  Let 
> us, for the same of argument, say we're going to allow decimal values to an 
> arbitrary length: any number of digits, possibly with a decimal point 
> somewhere along the string, possibly starting with a minus sign.
>
> The major choice is how the numbers are going to be stored.  The two 
> conventional ways are with one digit per byte (either using ASCII 0 == 0x30 
> or with 0 == 0x00), or with two digits per byte, often known as Binary-Coded 
> Decimal.  The first option makes for faster input, output, easier reading of 
> file formats, and simpler programming and debugging.  The second makes for a 
> smaller database file and speeds up some calculations using tables.  You 
> generally turn the minus sign into a bit flag.
>
> Once you have decided that you'll need conversion routines.  These will 
> either be to-and-from REAL or to-and-from TEXT.
>
> The other things to be implemented are the maths routines.  There are really 
> only five of these to worry about since, as someone pointed out upthread, one 
> does not multiply two amounts of money together.  There are other things you 
> don't do either: you don't add a decimal to a real, nor raise decimals to 
> powers (the log of a decimal value doesn't mean much).  So you need
>
> decimal + decimal -->  decimal
> decimal - decimal -->  decimal
> decimal * real -->  decimal
> decimal / real -->  decimal
> decimal / decimal -->  real
>
> The first four are slower than adding reals together.  The last one is tricky 
> and usually dealt with by turning the decimals into reals before division 
> (since the answer will be a real anyhow, this doesn't lose much precision).  
> The same tactic can be used if the programmer asks for anything weird (e.g. 
> decimal + real) though those normally indicate that the programmer made a 
> programming error and some compilers flag them with warnings.
>
> That's pretty-much all there is to it.  It doesn't represent huge growth to 
> the SQLite codebase, or even of the SQLite compiled code, though it will stop 
> database files which use the format from being backward-compatible, and it 
> will add a lot of tests to the test suite.  I don't know if it could be added 
> as a compilation option or not: you need someone who understands how SQLite 
> is built.
>
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Black, Michael (IS)]

Base 10 multiplication is needed.  Although money is the main reason for doing 
BCD due to cumulative errors it's not the only reason.  So I'd recommend adding 
decimal*decimal -> decimal just to be complete.  It's easy enough to implement 
using the + function so could just be noted as "slow".

There are many cases where people are doing calculations or using numbers 
expecting them to retain all digits.  This would allow the BCD type to be used 
for that if they really need it.

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: Sunday, March 27, 2011 5:49 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Lack of "decimal" support

For those who are scared by this, there really isn't that much to do.  Let us, 
for the same of argument, say we're going to allow decimal values to an 
arbitrary length: any number of digits, possibly with a decimal point somewhere 
along the string, possibly starting with a minus sign.

The major choice is how the numbers are going to be stored.  The two 
conventional ways are with one digit per byte (either using ASCII 0 == 0x30 or 
with 0 == 0x00), or with two digits per byte, often known as Binary-Coded 
Decimal.  The first option makes for faster input, output, easier reading of 
file formats, and simpler programming and debugging.  The second makes for a 
smaller database file and speeds up some calculations using tables.  You 
generally turn the minus sign into a bit flag.

Once you have decided that you'll need conversion routines.  These will either 
be to-and-from REAL or to-and-from TEXT.

The other things to be implemented are the maths routines.  There are really 
only five of these to worry about since, as someone pointed out upthread, one 
does not multiply two amounts of money together.  There are other things you 
don't do either: you don't add a decimal to a real, nor raise decimals to 
powers (the log of a decimal value doesn't mean much).  So you need

decimal + decimal --> decimal
decimal - decimal --> decimal
decimal * real --> decimal
decimal / real --> decimal
decimal / decimal --> real

The first four are slower than adding reals together.  The last one is tricky 
and usually dealt with by turning the decimals into reals before division 
(since the answer will be a real anyhow, this doesn't lose much precision).  
The same tactic can be used if the programmer asks for anything weird (e.g. 
decimal + real) though those normally indicate that the programmer made a 
programming error and some compilers flag them with warnings.

That's pretty-much all there is to it.  It doesn't represent huge growth to the 
SQLite codebase, or even of the SQLite compiled code, though it will stop 
database files which use the format from being backward-compatible, and it will 
add a lot of tests to the test suite.  I don't know if it could be added as a 
compilation option or not: you need someone who understands how SQLite is built.

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] Lack of "decimal" support

[Simon Slavin]

For those who are scared by this, there really isn't that much to do.  Let us, 
for the same of argument, say we're going to allow decimal values to an 
arbitrary length: any number of digits, possibly with a decimal point somewhere 
along the string, possibly starting with a minus sign.

The major choice is how the numbers are going to be stored.  The two 
conventional ways are with one digit per byte (either using ASCII 0 == 0x30 or 
with 0 == 0x00), or with two digits per byte, often known as Binary-Coded 
Decimal.  The first option makes for faster input, output, easier reading of 
file formats, and simpler programming and debugging.  The second makes for a 
smaller database file and speeds up some calculations using tables.  You 
generally turn the minus sign into a bit flag.

Once you have decided that you'll need conversion routines.  These will either 
be to-and-from REAL or to-and-from TEXT.

The other things to be implemented are the maths routines.  There are really 
only five of these to worry about since, as someone pointed out upthread, one 
does not multiply two amounts of money together.  There are other things you 
don't do either: you don't add a decimal to a real, nor raise decimals to 
powers (the log of a decimal value doesn't mean much).  So you need

decimal + decimal --> decimal
decimal - decimal --> decimal
decimal * real --> decimal
decimal / real --> decimal
decimal / decimal --> real

The first four are slower than adding reals together.  The last one is tricky 
and usually dealt with by turning the decimals into reals before division 
(since the answer will be a real anyhow, this doesn't lose much precision).  
The same tactic can be used if the programmer asks for anything weird (e.g. 
decimal + real) though those normally indicate that the programmer made a 
programming error and some compilers flag them with warnings.

That's pretty-much all there is to it.  It doesn't represent huge growth to the 
SQLite codebase, or even of the SQLite compiled code, though it will stop 
database files which use the format from being backward-compatible, and it will 
add a lot of tests to the test suite.  I don't know if it could be added as a 
compilation option or not: you need someone who understands how SQLite is built.

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

Re: [sqlite] Lack of "decimal" support

[TR Shaw]

On Mar 26, 2011, at 10: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.
> 

No US National debt is over that :-(

Tom


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

Re: [sqlite] Lack of "decimal" support

[BareFeetWare]

On 27/03/2011, at 3:04 PM, Patrick Earl wrote:

> 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.

I'm not suggesting storing as a text value. I'm suggesting storing as an 
integer and only converting to a float (or, I guess a text value is possible 
too) for display purposes.

> 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.

If we're talking about money amounts, isn't the number of decimal places always 
two? ie an amount stored as an integer as 12345 means 12345 cents means 123.45 
dollars.

> In essence, you have to build significant numeric infrastructure into your 
> program to emulate the missing numeric infrastructure in SQLite.

Perhaps I'm missing something of your requirement. I use integer storage for 
exact lossless money amounts.

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