NEW DATA TYPE IN SQLITE

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

NEW DATA TYPE IN SQLITE

John Stanton-3
First a disclaimer.  I am a new user of SQLITE and have not dug very
deeply into the code and literature, so my question may be trivial.

I have a system from an emulation of a legacy commercial language
processor which uses a very handy fixed point decimal number system.
The numbers are of arbitrary precision and held in right justified
display format.  As you can imagine arithmetic on such numbers is not
blindingly fast but for general commercial usage they are truly
excellent.  Commercial applications are not calculation intensive but
are display intensive so the time saved in radix transformation and
editing far exceeds the time lost in divisions.

This type of fixed point display format number with automatic rounding
makes it very easy to produce reports which balance to the penny and
incredibly easy to generate financial reports.  Such a number type does
seem to fit in with the SQLITE concept of loose typing, since it is
actually just a text field.

There is actually an obscure ANSI standard which defines these numbers.

How feasible and how difficult would it be to add this type to SQLITE?
The numbers store as text strings so what is involved is adding the new
numeric type and inserting the arithmetic functions so that they are
recognized by the SQL processor?

If anyone has a quick answer I should appreciate it.

PS, with such fixed point numbers you would have 5.0 / 2.0 = 2.5 exactly.
Reply | Threaded
Open this post in threaded view
|

RE: NEW DATA TYPE IN SQLITE

Jackson, Douglas H
Is the application written in the legacy language, or are you changing
it, too?  What language was/is it in?

The TCL language library has the ability to link functions to the
engine. Such functions can affect coercion of the data into a different
type, so that storage and presentation of the data need not be the same.
Combining the functions with triggers and views might do what you need.

Choose function names wisely, and the SQL could still be portable.

Doug

-----Original Message-----
From: John Stanton [mailto:[hidden email]]
Sent: Friday, September 30, 2005 2:49 PM
To: [hidden email]
Subject: [sqlite] NEW DATA TYPE IN SQLITE

First a disclaimer.  I am a new user of SQLITE and have not dug very
deeply into the code and literature, so my question may be trivial.

I have a system from an emulation of a legacy commercial language
processor which uses a very handy fixed point decimal number system.
The numbers are of arbitrary precision and held in right justified
display format.  As you can imagine arithmetic on such numbers is not
blindingly fast but for general commercial usage they are truly
excellent.  Commercial applications are not calculation intensive but
are display intensive so the time saved in radix transformation and
editing far exceeds the time lost in divisions.

This type of fixed point display format number with automatic rounding
makes it very easy to produce reports which balance to the penny and
incredibly easy to generate financial reports.  Such a number type does
seem to fit in with the SQLITE concept of loose typing, since it is
actually just a text field.

There is actually an obscure ANSI standard which defines these numbers.

How feasible and how difficult would it be to add this type to SQLITE?
The numbers store as text strings so what is involved is adding the new
numeric type and inserting the arithmetic functions so that they are
recognized by the SQL processor?

If anyone has a quick answer I should appreciate it.

PS, with such fixed point numbers you would have 5.0 / 2.0 = 2.5
exactly.
Reply | Threaded
Open this post in threaded view
|

Re: NEW DATA TYPE IN SQLITE

John Stanton-3
The program are all ANSI C, comprising run time libraries and a
compiler, so the various functions could well be implemented by linking
in the API used by the legacy compilation system.  Their most common
usage was to link into a byte-code interpreter not so different from the
Sqlite VDBE or a Java VM to provide platform independent execution.

Thanks for the hints about the TCL capability.  I shall dig into it.

As for function names, I was anticipating not using any, just
incorporating the type into SQL so that it would look like the
PostgreSQL NUMERIC(precision, scale) numbers, but actually be
fundamentally a TEXT type for Sqlite storage purposes.  Then SQL would
use the type just as PostgreSQL uses NUMERIC and portability would
actually be enhanced.

JS

Jackson, Douglas H wrote:

> Is the application written in the legacy language, or are you changing
> it, too?  What language was/is it in?
>
> The TCL language library has the ability to link functions to the
> engine. Such functions can affect coercion of the data into a different
> type, so that storage and presentation of the data need not be the same.
> Combining the functions with triggers and views might do what you need.
>
> Choose function names wisely, and the SQL could still be portable.
>
> Doug
>
> -----Original Message-----
> From: John Stanton [mailto:[hidden email]]
> Sent: Friday, September 30, 2005 2:49 PM
> To: [hidden email]
> Subject: [sqlite] NEW DATA TYPE IN SQLITE
>
> First a disclaimer.  I am a new user of SQLITE and have not dug very
> deeply into the code and literature, so my question may be trivial.
>
> I have a system from an emulation of a legacy commercial language
> processor which uses a very handy fixed point decimal number system.
> The numbers are of arbitrary precision and held in right justified
> display format.  As you can imagine arithmetic on such numbers is not
> blindingly fast but for general commercial usage they are truly
> excellent.  Commercial applications are not calculation intensive but
> are display intensive so the time saved in radix transformation and
> editing far exceeds the time lost in divisions.
>
> This type of fixed point display format number with automatic rounding
> makes it very easy to produce reports which balance to the penny and
> incredibly easy to generate financial reports.  Such a number type does
> seem to fit in with the SQLITE concept of loose typing, since it is
> actually just a text field.
>
> There is actually an obscure ANSI standard which defines these numbers.
>
> How feasible and how difficult would it be to add this type to SQLITE?
> The numbers store as text strings so what is involved is adding the new
> numeric type and inserting the arithmetic functions so that they are
> recognized by the SQL processor?
>
> If anyone has a quick answer I should appreciate it.
>
> PS, with such fixed point numbers you would have 5.0 / 2.0 = 2.5
> exactly.