attempt at output with thousands separator via extension

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

attempt at output with thousands separator via extension

Bruce Hohl
(1) SQLite will not return output with a thousands separator as follows:

sqlite> select printf("%15.2f",123456789.12789);
   123456789.13

sqlite> select printf("%'15.2f",123456789.12789);
<no output>

--------------------------------------------------------------------------------
(2) C language printf("%'15.2f",x) honors ' in printf() for thousands
separator:

vi comma1.c
#include <stdio.h>
#include <locale.h>
int main(void)
{
   printf("%'15.2f\n", 123456789.1234);
   setlocale(LC_ALL, "");
   printf("%'15.2f\n", 123456789.1234);
   return 0;
}

$ gcc comma1.c -o comma1

$ ./comma1
   123456789.12
 123,456,789.12

--------------------------------------------------------------------------------
(3) So I thought maybe a C extension to SQLite might honor the thousands
separator:

Using the half.c extension example from
https://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions with
printf("%'12.2f",x) added:

#include <stdio.h>
#include <locale.h>
#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1

// The half() SQL function returns half of its input value.
static void halfFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  sqlite3_result_double(context,
printf("%'12.2f",0.5*sqlite3_value_double(argv[0])));
}

// SQLite invokes this routine once when it loads the extension.
int sqlite3_extension_init(
  sqlite3 *db,
  char **pzErrMsg,
  const sqlite3_api_routines *pApi
){
  SQLITE_EXTENSION_INIT2(pApi)
  sqlite3_create_function(db, "half", 1, SQLITE_ANY, 0, halfFunc, 0, 0);
  return 0;
}


$ gcc -shared -fPIC -I. -o half.so half.c
$ sqlite3
sqlite> select load_extension('./half.so');

sqlite> select half(750000);
   375000.0012.0
sqlite> select half(7500000);
  3750000.0012.0
sqlite> select half(75000000);
 37500000.0012.0
sqlite> select half(750000000);
375000000.0012.0
sqlite> select half(7500000000);
3750000000.0013.0
sqlite> select half(75000000000);
37500000000.0014.0

Result:
(1) Output includes unwanted '12.0', '13.0', '14.0'.
    The 12, 13, 14 appear to be the number of characters printed.
    Not sure of the reason for the '.0'

(2) No thousands separator as wanted by including ' in printf():
    printf("%'12.2f",0.5*sqlite3_value_double(argv[0]))

I mostly use sqlite from the command line so it would really be nice to
have a thousands separator for more readable output.  Comments or
suggestions please.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: attempt at output with thousands separator via extension

Scott Robison-2
On Sat, Nov 21, 2015 at 1:47 PM, Bruce Hohl <[hidden email]> wrote:

> (1) SQLite will not return output with a thousands separator as follows:
>
> sqlite> select printf("%15.2f",123456789.12789);
>    123456789.13
>
> sqlite> select printf("%'15.2f",123456789.12789);
> <no output>
>
>
> --------------------------------------------------------------------------------
> (2) C language printf("%'15.2f",x) honors ' in printf() for thousands
> separator:
>

Some libraries support this as a non-standard extension, but it is not part
of standard C. Thus it will not work uniformly everywhere. And since SQLite
doesn't use printf directly, it won't work at all.


>
> vi comma1.c
> #include <stdio.h>
> #include <locale.h>
> int main(void)
> {
>    printf("%'15.2f\n", 123456789.1234);
>    setlocale(LC_ALL, "");
>    printf("%'15.2f\n", 123456789.1234);
>    return 0;
> }
>
> $ gcc comma1.c -o comma1
>
> $ ./comma1
>    123456789.12
>  123,456,789.12
>
>
> --------------------------------------------------------------------------------
> (3) So I thought maybe a C extension to SQLite might honor the thousands
> separator:
>
> Using the half.c extension example from
> https://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions with
> printf("%'12.2f",x) added:
>
> #include <stdio.h>
> #include <locale.h>
> #include <sqlite3ext.h>
> SQLITE_EXTENSION_INIT1
>
> // The half() SQL function returns half of its input value.
> static void halfFunc(
>   sqlite3_context *context,
>   int argc,
>   sqlite3_value **argv
> ){
>   sqlite3_result_double(context,
> printf("%'12.2f",0.5*sqlite3_value_double(argv[0])));
> }
>
> // SQLite invokes this routine once when it loads the extension.
> int sqlite3_extension_init(
>   sqlite3 *db,
>   char **pzErrMsg,
>   const sqlite3_api_routines *pApi
> ){
>   SQLITE_EXTENSION_INIT2(pApi)
>   sqlite3_create_function(db, "half", 1, SQLITE_ANY, 0, halfFunc, 0, 0);
>   return 0;
> }
>
>
> $ gcc -shared -fPIC -I. -o half.so half.c
> $ sqlite3
> sqlite> select load_extension('./half.so');
>
> sqlite> select half(750000);
>    375000.0012.0
> sqlite> select half(7500000);
>   3750000.0012.0
> sqlite> select half(75000000);
>  37500000.0012.0
> sqlite> select half(750000000);
> 375000000.0012.0
> sqlite> select half(7500000000);
> 3750000000.0013.0
> sqlite> select half(75000000000);
> 37500000000.0014.0
>
> Result:
> (1) Output includes unwanted '12.0', '13.0', '14.0'.
>     The 12, 13, 14 appear to be the number of characters printed.
>     Not sure of the reason for the '.0'
>
> (2) No thousands separator as wanted by including ' in printf():
>     printf("%'12.2f",0.5*sqlite3_value_double(argv[0]))
>
> I mostly use sqlite from the command line so it would really be nice to
> have a thousands separator for more readable output.  Comments or
> suggestions please.
>

The problem with the extension is that it is using printf, which displays
the actual characters of the floating point value directly to stdout,
completely bypassing SQLite. Then the return value of printf (the number of
characters printed) is used as the value of sqlite3_result_double.

Obviously the ' extension isn't being honored here either, maybe because
the "C" locale is in use. Either way, it isn't doing what you expect.

One way to go would be to extend the format capabilities of the SQLite
version of the printf function (which has nothing in common with the
standard C library printf function) to honor a thousands separator. I don't
know if there would be any interest on the part of the SQLite team to
implement something like that.

--
Scott Robison
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: attempt at output with thousands separator via extension

Bruce Hohl
Thanks for those comments, I now understand better what is being returned.
It seems an easy work around for shell output with thousands separator may
not be possible.  Within sqlite3.c there are ~1000 lines of printf.c code
(added in 3.8.3) so it does seem to be a development matter.  printf
support for a thousands separator would be nice ... is there a place for
feature requests?

On Sat, Nov 21, 2015 at 4:04 PM, Scott Robison <[hidden email]>
wrote:

> On Sat, Nov 21, 2015 at 1:47 PM, Bruce Hohl <[hidden email]> wrote:
>
> > (1) SQLite will not return output with a thousands separator as follows:
> >
> > sqlite> select printf("%15.2f",123456789.12789);
> >    123456789.13
> >
> > sqlite> select printf("%'15.2f",123456789.12789);
> > <no output>
> >
> >
> >
> --------------------------------------------------------------------------------
> > (2) C language printf("%'15.2f",x) honors ' in printf() for thousands
> > separator:
> >
>
> Some libraries support this as a non-standard extension, but it is not part
> of standard C. Thus it will not work uniformly everywhere. And since SQLite
> doesn't use printf directly, it won't work at all.
>
>
> >
> > vi comma1.c
> > #include <stdio.h>
> > #include <locale.h>
> > int main(void)
> > {
> >    printf("%'15.2f\n", 123456789.1234);
> >    setlocale(LC_ALL, "");
> >    printf("%'15.2f\n", 123456789.1234);
> >    return 0;
> > }
> >
> > $ gcc comma1.c -o comma1
> >
> > $ ./comma1
> >    123456789.12
> >  123,456,789.12
> >
> >
> >
> --------------------------------------------------------------------------------
> > (3) So I thought maybe a C extension to SQLite might honor the thousands
> > separator:
> >
> > Using the half.c extension example from
> > https://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions with
> > printf("%'12.2f",x) added:
> >
> > #include <stdio.h>
> > #include <locale.h>
> > #include <sqlite3ext.h>
> > SQLITE_EXTENSION_INIT1
> >
> > // The half() SQL function returns half of its input value.
> > static void halfFunc(
> >   sqlite3_context *context,
> >   int argc,
> >   sqlite3_value **argv
> > ){
> >   sqlite3_result_double(context,
> > printf("%'12.2f",0.5*sqlite3_value_double(argv[0])));
> > }
> >
> > // SQLite invokes this routine once when it loads the extension.
> > int sqlite3_extension_init(
> >   sqlite3 *db,
> >   char **pzErrMsg,
> >   const sqlite3_api_routines *pApi
> > ){
> >   SQLITE_EXTENSION_INIT2(pApi)
> >   sqlite3_create_function(db, "half", 1, SQLITE_ANY, 0, halfFunc, 0, 0);
> >   return 0;
> > }
> >
> >
> > $ gcc -shared -fPIC -I. -o half.so half.c
> > $ sqlite3
> > sqlite> select load_extension('./half.so');
> >
> > sqlite> select half(750000);
> >    375000.0012.0
> > sqlite> select half(7500000);
> >   3750000.0012.0
> > sqlite> select half(75000000);
> >  37500000.0012.0
> > sqlite> select half(750000000);
> > 375000000.0012.0
> > sqlite> select half(7500000000);
> > 3750000000.0013.0
> > sqlite> select half(75000000000);
> > 37500000000.0014.0
> >
> > Result:
> > (1) Output includes unwanted '12.0', '13.0', '14.0'.
> >     The 12, 13, 14 appear to be the number of characters printed.
> >     Not sure of the reason for the '.0'
> >
> > (2) No thousands separator as wanted by including ' in printf():
> >     printf("%'12.2f",0.5*sqlite3_value_double(argv[0]))
> >
> > I mostly use sqlite from the command line so it would really be nice to
> > have a thousands separator for more readable output.  Comments or
> > suggestions please.
> >
>
> The problem with the extension is that it is using printf, which displays
> the actual characters of the floating point value directly to stdout,
> completely bypassing SQLite. Then the return value of printf (the number of
> characters printed) is used as the value of sqlite3_result_double.
>
> Obviously the ' extension isn't being honored here either, maybe because
> the "C" locale is in use. Either way, it isn't doing what you expect.
>
> One way to go would be to extend the format capabilities of the SQLite
> version of the printf function (which has nothing in common with the
> standard C library printf function) to honor a thousands separator. I don't
> know if there would be any interest on the part of the SQLite team to
> implement something like that.
>
> --
> Scott Robison
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: attempt at output with thousands separator via extension

Simon Slavin-3

On 22 Nov 2015, at 3:34pm, Bruce Hohl <[hidden email]> wrote:

> printf
> support for a thousands separator would be nice

One problem with thousand separators is that different countries use different characters for them.  There's an unholy mix of commas, spaces, dots and apostrophes out there, not to mention whether people want a comma or U+066C.  C has access to your locale so it can get it right, but SQLite doesn't, and some people are bound to feel that whatever it implements is wrong.  The use of a point for decimals is part of SQL92 (which talks about 'decimal point' not 'decimal separator') but a thousands separator isn't.

I also have to point out that printf is not going to be used by the majority of users.  SQLite is a database.  Its job is to store and retrieve data.  Formatting for print can be done in whatever programming language you're calling SQLite from.  Or 'awk' or 'sed' if you're writing a shell script.

If you can rewrite existing printf code to be more compact and squeeze in more of the standard features at the time, great.  But to enlarge the code for every user of SQLite for a feature few people use may be seen as time and bytes better spent elsewhere.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: attempt at output with thousands separator via extension

Richard Hipp-3
In reply to this post by Bruce Hohl
On 11/22/15, Bruce Hohl <[hidden email]> wrote:
> Within sqlite3.c there are ~1000 lines of printf.c code
> (added in 3.8.3) so it does seem to be a development matter.

Let me clarify that: The printf.c file has been part of SQLite since
the beginning - over 15 years. The SQL "printf()" function was added
in 3.8.3, but that is just a 25-line wrapper
(https://www.sqlite.org/src/artifact/ecdd69ec6?ln=223-247) over the
preexisting printf implementation that has been in the source tree
since 2000, and which was actually written over a decade prior to
that, in the late 1980s.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: attempt at output with thousands separator via extension

Scott Robison-2
In reply to this post by Simon Slavin-3
On Sun, Nov 22, 2015 at 10:34 AM, Simon Slavin <[hidden email]> wrote:

>
> On 22 Nov 2015, at 3:34pm, Bruce Hohl <[hidden email]> wrote:
>
> > printf
> > support for a thousands separator would be nice
>
> One problem with thousand separators is that different countries use
> different characters for them.  There's an unholy mix of commas, spaces,
> dots and apostrophes out there, not to mention whether people want a comma
> or U+066C.  C has access to your locale so it can get it right, but SQLite
> doesn't, and some people are bound to feel that whatever it implements is
> wrong.  The use of a point for decimals is part of SQL92 (which talks about
> 'decimal point' not 'decimal separator') but a thousands separator isn't.
>
> I also have to point out that printf is not going to be used by the
> majority of users.  SQLite is a database.  Its job is to store and retrieve
> data.  Formatting for print can be done in whatever programming language
> you're calling SQLite from.  Or 'awk' or 'sed' if you're writing a shell
> script.
>
> If you can rewrite existing printf code to be more compact and squeeze in
> more of the standard features at the time, great.  But to enlarge the code
> for every user of SQLite for a feature few people use may be seen as time
> and bytes better spent elsewhere.
>

All technically correct (except the implication that a thousands separator
format is standard, as it is not). But the OP wants to be able to do
formatting from within SQL as he is using the SQLite shell, thus there is
no programming language at his disposal in this use case. An understandable
wishlist item, even if it isn't likely to happen for equally understandable
reasons.
--
Scott Robison
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: attempt at output with thousands separator via extension

Simon Slavin-3

On 22 Nov 2015, at 6:18pm, Scott Robison <[hidden email]> wrote:

> All technically correct (except the implication that a thousands separator
> format is standard, as it is not). But the OP wants to be able to do
> formatting from within SQL as he is using the SQLite shell, thus there is
> no programming language at his disposal in this use case. An understandable
> wishlist item, even if it isn't likely to happen for equally understandable
> reasons.

Actually, if it's in the shell I would have no objection with a view to code size, since code in the shell is not loaded when someone uses the SQLite API.  In fact I might argue that it would be a good idea to move the whole of printf into the shell rather than have it as a core function as it is now.

Of course that too is unlikely to happen, this time for backward-compatibility reasons.  Maybe it could be a consideration of SQLite4.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: attempt at output with thousands separator via extension

Rowan Worth
In reply to this post by Bruce Hohl
Hi Bruce,

I had a go at post-processing the sqlite3 shell's output to apply thousand
separators. I don't recommend looking too hard at the sed for the sake of
your sanity, but the gist is it repeatedly prepends a comma to trailing
groups of three digits, and then repeatedly removes commas which appear
after a decimal point[1].

[1] which now that I think of it will cause problems if you have lists of
floating point values separated by commas

The simplest way to use it is to pipe sqlite3's output into the sed
command. However this has a heavy impact on the prompt - we can do slightly
better with the more complicated invocation, assuming some flavour of
linux/bsd:

sqlite3 temp.sqlite 3> >(sed ': a;
s/\([0-9]\+\)\([0-9][0-9][0-9]\)\($\|[^0-9]\)/\1,\2\3/g; t a; : b;
s/\.\([0-9]\+\),\([0-9]\)/.\1\2/; t b')
SQLite version 3.8.1 2013-10-17 12:57:35
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .output /dev/fd/3


This still interacts poorly with the prompt:

sqlite> select * from a;
sqlite> 1,029,587
1,287,750

Note the first row (1029587) appears after the prompt. Its more usable if
you get rid of the prompt entirely:

sqlite> .prompt ''
select * from a;
1,029,587
1,287,750

This still preserves the continuation prompt in the case that you
mistype/forget a semicolon:

select * from a
   ...> ;
1,029,587
1,287,750

The downside is you can't copy/paste results into a new sql query. Also
note this doesn't differentiate between numerical/text values, anything
that looks like a number will have separators inserted.

-sqweek


On 22 November 2015 at 23:34, Bruce Hohl <[hidden email]> wrote:

> Thanks for those comments, I now understand better what is being returned.
> It seems an easy work around for shell output with thousands separator may
> not be possible.  Within sqlite3.c there are ~1000 lines of printf.c code
> (added in 3.8.3) so it does seem to be a development matter.  printf
> support for a thousands separator would be nice ... is there a place for
> feature requests?
>
> On Sat, Nov 21, 2015 at 4:04 PM, Scott Robison <[hidden email]>
> wrote:
>
> > On Sat, Nov 21, 2015 at 1:47 PM, Bruce Hohl <[hidden email]> wrote:
> >
> > > (1) SQLite will not return output with a thousands separator as
> follows:
> > >
> > > sqlite> select printf("%15.2f",123456789.12789);
> > >    123456789.13
> > >
> > > sqlite> select printf("%'15.2f",123456789.12789);
> > > <no output>
> > >
> > >
> > >
> >
> --------------------------------------------------------------------------------
> > > (2) C language printf("%'15.2f",x) honors ' in printf() for thousands
> > > separator:
> > >
> >
> > Some libraries support this as a non-standard extension, but it is not
> part
> > of standard C. Thus it will not work uniformly everywhere. And since
> SQLite
> > doesn't use printf directly, it won't work at all.
> >
> >
> > >
> > > vi comma1.c
> > > #include <stdio.h>
> > > #include <locale.h>
> > > int main(void)
> > > {
> > >    printf("%'15.2f\n", 123456789.1234);
> > >    setlocale(LC_ALL, "");
> > >    printf("%'15.2f\n", 123456789.1234);
> > >    return 0;
> > > }
> > >
> > > $ gcc comma1.c -o comma1
> > >
> > > $ ./comma1
> > >    123456789.12
> > >  123,456,789.12
> > >
> > >
> > >
> >
> --------------------------------------------------------------------------------
> > > (3) So I thought maybe a C extension to SQLite might honor the
> thousands
> > > separator:
> > >
> > > Using the half.c extension example from
> > > https://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions with
> > > printf("%'12.2f",x) added:
> > >
> > > #include <stdio.h>
> > > #include <locale.h>
> > > #include <sqlite3ext.h>
> > > SQLITE_EXTENSION_INIT1
> > >
> > > // The half() SQL function returns half of its input value.
> > > static void halfFunc(
> > >   sqlite3_context *context,
> > >   int argc,
> > >   sqlite3_value **argv
> > > ){
> > >   sqlite3_result_double(context,
> > > printf("%'12.2f",0.5*sqlite3_value_double(argv[0])));
> > > }
> > >
> > > // SQLite invokes this routine once when it loads the extension.
> > > int sqlite3_extension_init(
> > >   sqlite3 *db,
> > >   char **pzErrMsg,
> > >   const sqlite3_api_routines *pApi
> > > ){
> > >   SQLITE_EXTENSION_INIT2(pApi)
> > >   sqlite3_create_function(db, "half", 1, SQLITE_ANY, 0, halfFunc, 0,
> 0);
> > >   return 0;
> > > }
> > >
> > >
> > > $ gcc -shared -fPIC -I. -o half.so half.c
> > > $ sqlite3
> > > sqlite> select load_extension('./half.so');
> > >
> > > sqlite> select half(750000);
> > >    375000.0012.0
> > > sqlite> select half(7500000);
> > >   3750000.0012.0
> > > sqlite> select half(75000000);
> > >  37500000.0012.0
> > > sqlite> select half(750000000);
> > > 375000000.0012.0
> > > sqlite> select half(7500000000);
> > > 3750000000.0013.0
> > > sqlite> select half(75000000000);
> > > 37500000000.0014.0
> > >
> > > Result:
> > > (1) Output includes unwanted '12.0', '13.0', '14.0'.
> > >     The 12, 13, 14 appear to be the number of characters printed.
> > >     Not sure of the reason for the '.0'
> > >
> > > (2) No thousands separator as wanted by including ' in printf():
> > >     printf("%'12.2f",0.5*sqlite3_value_double(argv[0]))
> > >
> > > I mostly use sqlite from the command line so it would really be nice to
> > > have a thousands separator for more readable output.  Comments or
> > > suggestions please.
> > >
> >
> > The problem with the extension is that it is using printf, which displays
> > the actual characters of the floating point value directly to stdout,
> > completely bypassing SQLite. Then the return value of printf (the number
> of
> > characters printed) is used as the value of sqlite3_result_double.
> >
> > Obviously the ' extension isn't being honored here either, maybe because
> > the "C" locale is in use. Either way, it isn't doing what you expect.
> >
> > One way to go would be to extend the format capabilities of the SQLite
> > version of the printf function (which has nothing in common with the
> > standard C library printf function) to honor a thousands separator. I
> don't
> > know if there would be any interest on the part of the SQLite team to
> > implement something like that.
> >
> > --
> > Scott Robison
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: attempt at output with thousands separator via extension

Dominique Devienne
In reply to this post by Simon Slavin-3
On Sun, Nov 22, 2015 at 8:14 PM, Simon Slavin <[hidden email]> wrote:

> Of course that too is unlikely to happen, this time for
> backward-compatibility reasons.  Maybe it could be a consideration of
> SQLite4.
>

Why keep bringing up SQLite4? AFAIK, SQLite4 has been inactive for a long
time, and not much of a response anytime someone asks about it.

And who's tracking all those rejected SQLite3 feature-requests (of any
kind) anyway, should SQLite4 ever become active again? --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: attempt at output with thousands separator via extension

Simon Slavin-3

On 23 Nov 2015, at 7:41am, Dominique Devienne <[hidden email]> wrote:

> On Sun, Nov 22, 2015 at 8:14 PM, Simon Slavin <[hidden email]> wrote:
>
>> Of course that too is unlikely to happen, this time for
>> backward-compatibility reasons.  Maybe it could be a consideration of
>> SQLite4.
>
> Why keep bringing up SQLite4? AFAIK, SQLite4 has been inactive for a long
> time, and not much of a response anytime someone asks about it.

It's a way of telling people who ask for features that it's not entirely impossible they'll see them in the future.  Just not until a major rewrite.

> And who's tracking all those rejected SQLite3 feature-requests (of any
> kind) anyway, should SQLite4 ever become active again?

I assume that if someone on the Dev Team likes an idea posted here they'll make a note of it.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: attempt at output with thousands separator via extension

Simon Slavin-3

On 23 Nov 2015, at 7:56am, Simon Slavin <[hidden email]> wrote:

> On 23 Nov 2015, at 7:41am, Dominique Devienne <[hidden email]> wrote:
>
>> Why keep bringing up SQLite4? AFAIK, SQLite4 has been inactive for a long
>> time, and not much of a response anytime someone asks about it.
>
> It's a way of telling people who ask for features that it's not entirely impossible they'll see them in the future.  Just not until a major rewrite.

Forgot to mention that one of those people is me.  I have a number of major problems with tiny little details of SQLite.  Most of them work the way they do because of backward compatibility.  So I'm promising myself a simpler brighter life.  At some unspecified (may never come) time in the future.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: attempt at output with thousands separator via extension

Jake
This post has NOT been accepted by the mailing list yet.
In reply to this post by Bruce Hohl
Hi Bruce,

I wanted a thousands separator format function a while back, and wrote the following extension function for this purpose.

Beware that this will fail for very large values. E.g thousands_separator(10e100, 2) will crash SQLite, so if you are expecting such input, then you will have to make modifications to handle this.

~Jake

----------------------------------------

#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

/*
** thousands_separator(x)
** thousands_separator(x, n)
**
**   thousands_separator() takes value x as a double, and returns
**   a string, formatted with optional n decimal places, and uses a
**   comma as a thousands separator.
**
**   If n is ommited, or if n < 0 then do not round.
**   If either argument is null, then return null.
**
** e.g. thousands_separator(0)            = 0
**      thousands_separator(1278006.367)  = 1,278,006.367
**      thousands_separator(-1234.123, 2) = -1,234.12
*/
static void thousands_separatorFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
  int n = -1;
  double r = sqlite3_value_double(argv[0]);
  char *zIn;
  char *z;
  int i, j = 0, k = 0;
  int dp; // decimal point location
         
  if( argc==2 ){
    if( sqlite3_value_type(argv[1])==SQLITE_NULL ) return;
    n = sqlite3_value_int(argv[1]);
        if( n>30 ) n = 30;
  }
  zIn = n < 0 ? sqlite3_mprintf("%.15g",r) : sqlite3_mprintf("%.*f",n,r);
 
  n = 0;
  while( zIn[n] )
    n++;
  dp = n;
  j = n+10;
  z = sqlite3_malloc(j);

  if( !z ){
    sqlite3_free(zIn);
    return;
  }
 
  for( i=0; i<=n; i++ ){
    if( zIn[i] == '.' )
      dp = i;
  }
  for( i=n-1; i>=0; i-- ){
    z[j--] = zIn[i];
    if( i<dp && i ){
      if( zIn[i-1]!='-' ){
        if( ++k%3 == 0 )
          z[j--] = ',';
      }
    }
  }
  sqlite3_result_text(context, z+j+1, n+k/3, SQLITE_STATIC);
  sqlite3_free(zIn);
  sqlite3_free(z);
}

#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_extension_init(
  sqlite3 *db,
  char **pzErrMsg,
  const sqlite3_api_routines *pApi
){
  int rc = SQLITE_OK;
  SQLITE_EXTENSION_INIT2(pApi);
  (void)pzErrMsg;  /* Unused parameter */
  rc = sqlite3_create_function(db, "thousands_separator", 1, SQLITE_UTF8, 0,
                                 thousands_separatorFunc, 0, 0);
  if( rc==SQLITE_OK ){
    rc = sqlite3_create_function(db, "thousands_separator", 2, SQLITE_UTF8, 0,
                                 thousands_separatorFunc, 0, 0);
  }
  return rc;
}
Reply | Threaded
Open this post in threaded view
|

Re: attempt at output with thousands separator via extension

Mohit Sindhwani-5
In reply to this post by Rowan Worth
On 23/11/2015 11:32 AM, Rowan Worth wrote:
> Hi Bruce,
>
> I had a go at post-processing the sqlite3 shell's output to apply thousand
> separators. I don't recommend looking too hard at the sed for the sake of
> your sanity, but the gist is it repeatedly prepends a comma to trailing
> groups of three digits, and then repeatedly removes commas which appear
> after a decimal point[1].

  I thought that it should be "easy enough" to add a custom function
that outputs the formatted view for numbers... so, instead of
 > select int_val
you could do:
 > select to_thousands_formatted(int_val)
with an optional parameter that says how you want it separated "," being
the default.

It would be a bit like using upper(X) with a syntax that uses parameters
like group_concat() does.  Would that not work?  In that case, this
pretty_printer coule be code only within the sqlite3 shell (or as an
extension, it could be in anything).

Best Regards,
Mohit.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: attempt at output with thousands separator via extension

Bruce Hohl
Gentlemen, thanks all for your comments.  Before I posted I knew the
thousands separator was problematic for the reasons stated by Simon.  I
figured it was intentionally left out of sqlite's printf(). I wanted to
make sure I was not missing a known or easy solution.

As sqweek/Mohit suggested I will attempt a post processing/function
solution. I did not go that route to begin with as I was concerned about
performance and an extension seems cleaner.

www.sqlite.org/loadext.html states that: "Loadable extensions are C-code."
 Can someone confirm this please.

If I figure out some clever I will share for the benefit of other shell
junkies that like neat easily readable numeric output - all 6 of us :)

On Mon, Nov 23, 2015 at 5:18 AM, Mohit Sindhwani <[hidden email]> wrote:

> On 23/11/2015 11:32 AM, Rowan Worth wrote:
>
>> Hi Bruce,
>>
>> I had a go at post-processing the sqlite3 shell's output to apply thousand
>> separators. I don't recommend looking too hard at the sed for the sake of
>> your sanity, but the gist is it repeatedly prepends a comma to trailing
>> groups of three digits, and then repeatedly removes commas which appear
>> after a decimal point[1].
>>
>
>  I thought that it should be "easy enough" to add a custom function that
> outputs the formatted view for numbers... so, instead of
> > select int_val
> you could do:
> > select to_thousands_formatted(int_val)
> with an optional parameter that says how you want it separated "," being
> the default.
>
> It would be a bit like using upper(X) with a syntax that uses parameters
> like group_concat() does.  Would that not work?  In that case, this
> pretty_printer coule be code only within the sqlite3 shell (or as an
> extension, it could be in anything).
>
> Best Regards,
> Mohit.
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: attempt at output with thousands separator via extension

Stephan Beal-3
On Mon, Nov 23, 2015 at 1:57 PM, Bruce Hohl <[hidden email]> wrote:

> www.sqlite.org/loadext.html states that: "Loadable extensions are C-code."
>  Can someone confirm this please.
>

pedantically speaking: the _entry point_ for the extension is C. The
implementation may be in any language.


> If I figure out some clever I will share for the benefit of other shell
> junkies that like neat easily readable numeric output - all 6 of us :)
>

i think you mean all 6,0 of you ;).


--
----- 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: attempt at output with thousands separator via extension

Simon Slavin-3

On 23 Nov 2015, at 1:02pm, Stephan Beal <[hidden email]> wrote:

> On Mon, Nov 23, 2015 at 1:57 PM, Bruce Hohl <[hidden email]> wrote:
>
>> www.sqlite.org/loadext.html states that: "Loadable extensions are C-code."
>> Can someone confirm this please.
>
> pedantically speaking: the _entry point_ for the extension is C. The
> implementation may be in any language.

Here, for example, is how to create an external function written in PHP for use when using the SQLite3 interface from PHP.

<http://php.net/manual/en/function.sqlite-create-function.php>

It's up to whoever wrote the 'shim' to a language to implement sqlite3_create_function_v2() in their shim.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users