[sqlite] 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.

[Scott Robison]

Post by Bruce Hohl
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

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.

Post by Bruce Hohl
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
Using the half.c extension example from
https://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions with
#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
(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'
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.

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

Post by Bruce Hohl

Post by Bruce Hohl
sqlite> select printf("%15.2f",123456789.12789);
123456789.13
sqlite> select printf("%'15.2f",123456789.12789);
<no output>

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

Post by Bruce Hohl
(2) C language printf("%'15.2f",x) honors ' in printf() for thousands

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.

Post by Bruce Hohl
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

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

Post by Bruce Hohl
(3) So I thought maybe a C extension to SQLite might honor the thousands
Using the half.c extension example from
https://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions with
#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
(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'
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

Post by Bruce Hohl
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.

[Scott Robison]

Post by Simon Slavin

Post by Bruce Hohl
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.

[Simon Slavin]

Post by Scott Robison
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.

[Dominique Devienne]

Post by Simon Slavin
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

[Simon Slavin]

Post by Dominique Devienne

Post by Simon Slavin
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.

Post by Dominique Devienne
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.

[Simon Slavin]

Post by Simon Slavin

Post by Dominique Devienne
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.

[Richard Hipp]

Post by Bruce Hohl
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.

[Rowan Worth]

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

Post by 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?

Post by Scott Robison

Post by Bruce Hohl
(1) SQLite will not return output with a thousands separator as
sqlite> select printf("%15.2f",123456789.12789);
123456789.13
sqlite> select printf("%'15.2f",123456789.12789);
<no output>

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

Post by Scott Robison

Post by Bruce Hohl
(2) C language printf("%'15.2f",x) honors ' in printf() for thousands

Some libraries support this as a non-standard extension, but it is not

part

Post by Scott Robison
of standard C. Thus it will not work uniformly everywhere. And since

SQLite

Post by Scott Robison
doesn't use printf directly, it won't work at all.

Post by Bruce Hohl
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

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

Post by Scott Robison

Post by Bruce Hohl
(3) So I thought maybe a C extension to SQLite might honor the

thousands

Post by Scott Robison

Post by Bruce Hohl
Using the half.c extension example from
https://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions with
#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);

Post by Scott Robison

Post by Bruce Hohl
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
(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'
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

Post by Scott Robison
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

Post by Scott Robison
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Mohit Sindhwani]

Post by Rowan Worth
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

Post by Rowan Worth
select int_val
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.

[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 :)

Post by Rowan Worth
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

Post by Rowan Worth
select int_val
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Stephan Beal]

Post by Bruce Hohl
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.

Post by Bruce Hohl
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 ;).

[Simon Slavin]

Post by Stephan Beal

Post by Bruce Hohl
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.