Re: [sqlite] thousand separator for printing large numbers

[Scott Robison]

On Feb 11, 2017 7:15 PM, "James K. Lowden"  wrote:

On Fri, 10 Feb 2017 10:46:24 +0100
Dominique Devienne  wrote:

> PS: In this context, I don't want to use a host-program provided UDF.
> This is data meant to be viewed with any SQLite client, so kind of
> "report".

https://github.com/jklowden/sqlrpt

While Clemens was parsimonously adding 14 lines to support a thousands
separator in the SQL interpreter, I was extravagantly adding 225 to
create a new utility.


{snip}

I thought it was DRH that added it? Regardless, what everyone seems to
ignore, is the stated desire for a query that works with any stock
compatible SQLite implementation. So it can be used with the sqlite shell,
or any of the sqlite embedded database managers or library wrappers for a
variety of languages.

Sure, in a perfect world, people would use something like your new utility.
Thank you for it. But given that SQLite already uses certain American-isms
(decimal point for string coerced reals), adding this is not some horrible
affront to software development, any more than a lack of "type safety" is.
It's just a tool that you are free to ignore if it doesn't suit your use
one's use case.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] thousand separator for printing large numbers

[James K. Lowden]

On Fri, 10 Feb 2017 10:46:24 +0100
Dominique Devienne  wrote:

> PS: In this context, I don't want to use a host-program provided UDF.
> This is data meant to be viewed with any SQLite client, so kind of
> "report".

https://github.com/jklowden/sqlrpt

While Clemens was parsimonously adding 14 lines to support a thousands
separator in the SQL interpreter, I was extravagantly adding 225 to
create a new utility.  

sqlrpt processes a query and prepares the output to be rendered in
groff with the tbl preprocessor.  By using tbl, we get:

0.  intelligent table rendering, based on the data
1.  boldface column headings
2.  numeric columns aligned on the decimal point 
and centered under the heading
3.  word-wrapped columns & headings for comfortable viewing
4.  data never truncated in display 

In honor of Dominique, sqlrpt honors the locale and renders numeric
data with a thousands separator.  Separators can be suppressed by
setting the locale to "C".  

I wrote sqlrpt partly as an experiment, provoked by Dominique saying he
wants a "report".  Although intended for interactive use at the command
line, it could be used to facilitate very nice printed reports, should
anyone wish to do so.  Of course groff can paginate, and tbl can
produce column headings at the top of each page.  

In my limited use so far, I find the output more compact and easier to
read than "sqlite3 -column -header".  

BSD license.  Enjoy. 

--jkl




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

[sqlite] file descriptor leak

[alexc]

Hi,


In file shell.c
static char *readFile(const char *zName, int *pnByte){ FILE *in = fopen(zName, 
"rb”); // == allocate fd in long nIn; size_t nRead; char *pBuf; if( in==0 ) 
return 0; fseek(in, 0, SEEK_END); nIn = ftell(in); rewind(in); pBuf = 
sqlite3_malloc64( nIn+1 ); // == try to allocate memory if( pBuf==0 ) return 0; 
// == check return value, if memory allocation failed, fd in will leak
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug: CREATE TABLE AS with GROUP BY preserves backticks in generated column name

[Alek Storm]

> CREATE TABLE t1 (col1 INT);
> CREATE TABLE t2 AS SELECT `col1` FROM t1 GROUP BY col1;
> .schema t2
CREATE TABLE t2("`col1`" INT);

I expected: CREATE TABLE t2(col1 INT);

Note the following generate the schema I expect:
# With backticks, without GROUP BY:
> CREATE TABLE t2 AS SELECT `col1` FROM t1;
> .schema t2
CREATE TABLE t2(col1 INT);

# Without backticks, with GROUP BY:
> CREATE TABLE t2 AS SELECT col1 FROM t1 GROUP BY col1;
> .schema t2
CREATE TABLE t2(col1 INT);

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

[sqlite] SQLITE_ENABLE_UPDATE_DELETE_LIMIT - my "final" patch

[Ziemowit Laski]

Hello again,

So after discussions with Jan and further contemplation, I concluded that the 
only way to get a hold on the '#line ... ' issues in the generated parse.c is 
to write a dedicated tool to do it.  The patch herein contains such a tool, 
named 'lineclean'.  The tool detects sequences of the form

#if(n)def 
#line ... "parse.c"
#else
#line ... "parse.c"
#endif

and replaces them with a single line

#line ... "parse.c"

Also, lineclean fixes up all '#line ... "parse.c"' commands so that they 
actually refer to the next line in the file.  (This was completely broken as a 
result of running the diff).

Anyhoo, since I still haven't heard from any of the maintainers, I'll leave it 
at that.  Should you decide to apply the patch (with attribution, of course 
:-)) and run into problems, please e-mail me directly (zlaski _at_ ziemas _dot_ 
net) since I'm not a member of sqlite-users.

Thank you,

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

Re: [sqlite] thousand separator for printing large numbers

[Michael Falconer]

Congrats to all contributors to this thread. Robust discussions like this
make this my absolute favourite list. For the record I like the OP's
suggestion which was more about the features of the printf() function than
anything else. Everybody wins though, because of the great discussion and
the differing viewpoints brought to light. Another great SQLite thread! ;-)

On 12 February 2017 at 05:38, R Smith  wrote:

>
>
> On 2017/02/11 6:50 PM, Clemens Ladisch wrote:
>
>> James K. Lowden wrote:
>>
>>> I doubt you'll win that argument.
>>>
>> You should have checked before writing this.  ;-)
>> http://www.sqlite.org/cgi/src/info/064445b12f99f76e
>>
>
> Pfff, my subsequent points all made moot. Well done and thanks for this!
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] thousand separator for printing large numbers

[R Smith]

On 2017/02/11 6:50 PM, Clemens Ladisch wrote:

James K. Lowden wrote:

I doubt you'll win that argument.

You should have checked before writing this.  ;-)
http://www.sqlite.org/cgi/src/info/064445b12f99f76e


Pfff, my subsequent points all made moot. Well done and thanks for this!

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

Re: [sqlite] thousand separator for printing large numbers

[R Smith]

On 2017/02/11 1:36 AM, Jens Alfke wrote:

On Feb 10, 2017, at 2:45 AM, Dominique Devienne  wrote:

Honestly Clemens? There wouldn't be a built-in printf() and substr() etc...
if that was the case.

Not really. Those aren’t necessarily intended to format data for display, and 
I’ve never used them for that. //...snipped...


That's a tad self-important innit?  I'm sure the intended use for my 
vehicle's wheels was not to occupy the trunk, but when they puncture I 
still put them there. The fact that you never had a flat before is no 
argument against a wheel-bay in the trunk or a spare wheel.


printf() exists, for whatever "intent" got it there. Enhancing it should 
surely be merited by functionality of the function itself and not by 
arguing the function's own necessity or intent, non?


Cheers!
Ryan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] thousand separator for printing large numbers

[E.Pasma]

10 feb 2017, Dominique Devienne:


There's
http://sqlite.1065341.n5.nabble.com/printf-with-thousands-separator-td85022.html

And my feeble attempt below. But there's got to be a better way, no?
What would be the shortest and/or most efficient way to do this in  
SQL?

..

sqlite> with s(v) as (
  ...>   select 23
  ...>   union all
  ...>   select 1097
  ...>   union all
  ...>   select 123456789
  ...>   union all
  ...>   select 4123456789
  ...> )
  ...> select v,
  ...> case
  ...> when v < 1000 then cast(v as text)
  ...> when v < 100 then printf("%d,%03d", v/1000, v%1000)
  ...> when v < 10 then printf("%d,%03d,%03d", v/100,
v%100/1000, v%1000)
  ...> else printf("%d,%03d,%03d,%03d", v/10,
v%10/100, v%100/1000, v%1000)
  ...> end
  ...> from s
  ...> ;
23|23
1097|1,097
123456789|123,456,789
4123456789|4,123,456,789
sqlite>


Hello, I reply to the original mail as the question for a shorter/ 
moree efficient SQL solution isn't touched in the further discussion,   
Below is my attempt. Thanks, Edzard Pasma


SQLite version 3.16.2
select ltrim(substr(x,-9,3)||','||substr(x,-6,3)||','|| 
substr(x,-3,3),'0,') from (select 1234 as x union select 7 union  
select 123456789);

7
1,234
123,456,789

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

Re: [sqlite] thousand separator for printing large numbers

[Dominique Devienne]

On Sat, Feb 11, 2017 at 5:51 PM Clemens Ladisch  wrote:

> James K. Lowden wrote:
> > I doubt you'll win that argument.
>
> You should have checked before writing this.  ;-)
> http://www.sqlite.org/cgi/src/info/064445b12f99f76e
>

> I saw that too this morning. Made my day. Thanks Richard.

And in 14 lines not 20. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] thousand separator for printing large numbers

[R Smith]

On 2017/02/10 8:15 PM, Dominique Devienne wrote:

On Fri, Feb 10, 2017 at 6:56 PM, Dominique Devienne 
wrote:

I'm sure DRH could probably add it in his sleep in [1] , around the switch
line 236 with a new flag,
with room to spare in et_info.flags to store it, and with the actual
formatting code in less than 20 lines,
in that 1099 line file. So +2% in that one file which is a tiny subset (<
1%) of 100K+ lines of the amalgamation. --DD


I don't think this line of argument is valid - stuff shouldn't be done 
because their bloat level is minimal or that they will be easy to do - 
however, I do agree with the idea, so +1 for the suggestion.


And yes - I've seen all the arguments about SQL not having a formatting 
responsibility (with which I agree) but there is nothing wrong with 
rolling a stone out of the way with a simple addition - and as was 
mentioned before, there already /IS/ a printf() function in the SQL with 
the sole purpose of formatting stuff, so the addition does qualify as 
"simple". It seems silly to me to bang on about SQL having no formatting 
jurisdiction when it's already there - all that was asked is making it a 
very small bit more functional with an enhancement many might use.


That said, please avoid ANY locale nonsense - it's evil.

/2c
Ryan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index list

[Dominique Devienne]

On Sat, Feb 11, 2017 at 3:54 PM Simon Slavin  wrote:

> On 11 Feb 2017, at 2:50pm, Rob van der sloot 
> wrote:
> > I want to use the index of a specific column of a table as a pulldown
> list
> > But I can't find any syntax how to select or view an index.
>
> Sorry.  There is no way to find the contents of an index.


> [DD] There is. See http://www.sqlite.org/imposter.html

ALthough like others have said just select from the table

In such a way to use the covering index.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] thousand separator for printing large numbers

[jose isaias cabrera]

DD, just adding a comma separator for long integers (numbers), as you are 
suggesting, would be a bad idea>  The next thing would be a set of questions 
to Dr. Hipp: "why just comma separator for large numbers?  Why doesn't 
SQLite support the full feature of locale and formatting depending on 
language, etc." And so, the full feature for localizing numbers must be 
added for it.  Again, everyone has said it, you can add a small function 
inside, or after having acquired the number, and format the number there.


just my 64 Dominican cents.

-Original Message- 
From: Dominique Devienne

Sent: Friday, February 10, 2017 1:06 PM
To: SQLite mailing list
Subject: Re: [sqlite] thousand separator for printing large numbers

On Fri, Feb 10, 2017 at 6:53 PM, Stephen Chrzanowski 
wrote:


Bringing in "Other Database Engines do it!" discussion [...]



When did I do that?



Any element that is to be portrayed to the users screen should be handled
by whatever UI engine is displaying the information, not something that
handles only three types of data.  The UI needs to translate locality
information.  The date/time in your windows/linux/consoles/etc are
presented to you formatted.  The date and time are stored as a number, not
"Friday, February 10, 2017 12:43:33pm".



And that's exactly why SQLite has date and time functions.
Notably the one converting a number of seconds since the Epoch
into a human readable date time. Which I also use in my views.
That's no different.



SQLite is lite.  It is designed to be run on machines that have KILOBYTES
of memory.  Todays phones and devices that are all the rage do have
MEGABYTES to GIGABYTES of memory and storage, sure, but there are devices
out there that have literally KILOBYTES of data to be worked with.  When
you start adding beautification methods [...]



Adding what? printf() is already here, and already has formatting options.

If the application of your choice isn't displaying the numbers as you want,




You're mistaken. I am explicitly generating a string as a thousand-separated
number using an SQL expression, not explicitly asking as app to display
numbers one way or another. And using printf('%,d', num) instead of a big
and ugly (and limited to billions) SQL expression is a good thing.



Now, if you'd like, you could possibly throw a suggestion for the SQLite3
Client, sure, maybe with a particular command line  option, or, even an
option set in the CLI itself to format numbers to your OS's locale.



Again, I don't want or need implicit number formatting. I do it explicitly.
And again again, I don't need/want locale-aware formatting. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 


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

Re: [sqlite] thousand separator for printing large numbers

[Clemens Ladisch]

James K. Lowden wrote:
> I doubt you'll win that argument.

You should have checked before writing this.  ;-)
http://www.sqlite.org/cgi/src/info/064445b12f99f76e


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index list

[Niall O'Reilly]

On 11 Feb 2017, at 14:50, Rob van der sloot wrote:

> I want to use the index of a specific column of a table as a pulldown list
> in my application.

  Wouldn't

  SELECT DISTINCT column FROM table;

  give you the same effect?

  I expect the query planner would use the table or not according to its
  estimate of the benefit of doing so.


  Best regards,
  Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index list

[Simon Slavin]

On 11 Feb 2017, at 2:50pm, Rob van der sloot  wrote:

> I want to use the index of a specific column of a table as a pulldown list
> in my application.
> But I can't find any syntax how to select or view an index.

Sorry.  There is no way to find the contents of an index.

However, you can do SELECT on the column which was indexed using an ORDER BY 
clause.  And SQLite will automatically use the index to do the SELECT in the 
fastest way.  (Unless there are two of them !)

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

[sqlite] Index list

[Rob van der sloot]

I want to use the index of a specific column of a table as a pulldown list
in my application.
But I can't find any syntax how to select or view an index.

Thanks
Rob van der Sloot

-- 


This email and any attachments to it may be confidential and are intended
solely for the use of the individual to whom it is addressed. Any views or
opinions expressed are solely those of the author and do not necessarily
represent those of the sender of this email.

If you are not the intended recipient of this email, you must neither take
any action based upon its contents, nor copy or show it to anyone.

Please contact the sender if you believe you have received this email in
error.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] thousand separator for printing large numbers

[Keith Maxwell]

If the `printf` function could add a thousands separator there I are
times I would have used it! Instead I've ended up using a recursive
CTE  and `||` operators to build up the string. I can't find the query
right now.

Maybe the CLI tool is a better place for the feature, I suggest either
as a function like `readfile(X)` and `writefile(X, Y)` or as a dot
command like `.width`. As an aside I started to use `printf` less when
I realised that in the CLI `.width` can right align numbers.

I would certainly be another user of the feature if it were available
within SQLite somewhere.

On 11 February 2017 at 02:43, James K. Lowden  wrote:
> On Fri, 10 Feb 2017 20:57:34 +0100
> Dominique Devienne  wrote:
>
>> the view itself calls printf.
>
> I see now.  Your request is very specific: to support a thousands
> separator in the SQL printf.  You don't want to write a UDF, and you
> don't want to use any facilities above the API (in C or other).
>
> I doubt you'll win that argument.  Hard-coding the separator
> (insensitive to locale) won't sit well, nor will introducing locale to
> the SQLite code.
>
> Locale-dependent behavior in a library is tricky.  To use the features
> in libc, setlocale(3) must be called first.  But who should call it?
> The SQLite library can't call it, else it provokes locale-dependent
> behavior in an application that might not want it, or might want
> something other than the default. OTOH, by leaving it up to the
> application, SQLite's observed behavior becomes not only
> locale-dependent, but dependent on when/if/how setlocale(3) is called.
>
> FWIW it's my belief that printf doesn't belong in SQL.  SQL is a query
> language, not a report-writing tool.  I mean, what about headers and
> footers, and page numbers?  I'm not being facetious: report-writing has
> all kinds of needs besides formating numbers that you would never
> suggest belong in SQL.  So what's the point in supporting this one
> aspect, knowing that any real report will have to resort to
> host-language formatting features anyway?
>
> I think there's pretty much always a case to be made to improve the
> formatting capabilities of the SQLite shell, both for user convenience
> and simple reports.  But that's a separate discussion, and not what you
> asked for.
>
> --jkl
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users