Re: [sqlite] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

[Petite Abeille]

> On Mar 24, 2020, at 09:13, Luuk  wrote:
> 
> No, i do NOT mean the source of the forum, but the source for "The list is 
> deprecated"


http://sqlite.1065341.n5.nabble.com/New-SQLite-Forum-established-this-mailing-list-is-deprecated-td111358.html

But that's just DRH. The mailing list is fully functional.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cannot select specific columns from temp.sqlite_master unless temp.sqlite_master is aliased

[Petite Abeille]

> On Mar 14, 2020, at 03:14, Justin Ng  wrote:
> 
> This works fine,
> 
> SELECT
>x.sql
> FROM
>temp.sqlite_master AS x;
> 

As is this:

SELECT
   sql
FROM
   temp.sqlite_master;

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

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Petite Abeille]

> On Mar 13, 2020, at 23:09, Michael Falconer  
> wrote:
> 
> So I'll take the opportunity now to thank (too many to single out) the many 
> contributors to this list who have made it unique, and well...just a nice 
> place to be.

So long, and thanks for all the fish :)

Will miss this place. Good bye and good luck to all. Take care.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Petite Abeille]

> On Mar 13, 2020, at 01:04, Keith Medcalf  wrote:
> 
> Uck.  That is the most horrible looking thing I have ever seen in my life.  
> Good luck with it.

If only this was confine to the esthetics. But yes, horrible sums it up.

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

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Petite Abeille]

> On Mar 13, 2020, at 00:54, BohwaZ  wrote:
> 
> So that people wishing to use emails could still do it by subscribing
> to alerts and then replying to notifications.

Also, would it be possible to actually mark such emails to include proper List 
Header Fields, e.g.:

   List-Id: List Header Mailing List 
   List-Help: 
   List-Unsubscribe: 
   List-Subscribe: 
   List-Post: 
   List-Owner:  (Contact Person for Help)
   List-Archive: 


https://tools.ietf.org/html/rfc5983 

Anyway, tried the forum 'notification'. Very subpar, much like the entire 
'forum' experience. Unsubscribed. To each their own I guess. 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Petite Abeille]

> On Mar 12, 2020, at 21:17, Richard Hipp  wrote:
> 
> a replacement for this mailing list:

Tragic. Oh well. Anyhow, thanks for SQLite itself :)

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

Re: [sqlite] Best way to store key,value pairs

[Petite Abeille]

> On Jan 13, 2020, at 19:37, Jens Alfke  wrote:
> 
>> What is the motivation behind this advice?  It's completely unnecessary.  
> 
> Thanks for your opinion, James! I disagree. 

Arnt Gulbrandsen, of Archiveopteryx fame, would disagree with you, Jens :)

https://archiveopteryx.org/schema

In any case, this is not a matter of opinion, but rather one of purpose and 
benchmarks.

On that note, Happy New Year! Welcome to the future.




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

Re: [sqlite] Getting the week of the month from strftime or date functions

[Petite Abeille]

> On May 7, 2019, at 05:35, Jens Alfke  wrote:
> 
> [https://en.wikipedia.org/wiki/French_Republican_calendar]

Along the same lines: International Fixed Calendar

https://en.m.wikipedia.org/wiki/International_Fixed_Calendar

> You Advocate An Approach To Calendar Reform; Your Idea Will Not Work; Here Is 
> Why  (brutal takedown)

While at it:  So You Want To Abolish Time Zones

https://qntm.org/abolish



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

Re: [sqlite] Getting the week of the month from strftime or date functions

[Petite Abeille]

> On May 6, 2019, at 19:58, Jose Isaias Cabrera  wrote:
> 
> something is wrong where one week only has 1 day

... per month :P

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

Re: [sqlite] Getting the week of the month from strftime or date functions

[Petite Abeille]

> On May 4, 2019, at 21:24, Thomas Kurz  wrote:
> 
>> What about just sticking with the ISO week definition?
>> 
>> https://en.wikipedia.org/wiki/ISO_week_date
> 
> From the document you cited:
> 
> "The ISO standard does not define any association of weeks to months."

True enough, even though one could convert a 'week of year' into a 'week of 
month':

with
DayRange( day, until )
as
(
  select '2019-01-01' as day,
 '2019-12-31' as until

  union all
  select  date( DayRange.day, '+1 day' ) as day,
  DayRange.until
  fromDayRange

  where   date( DayRange.day, '+1 day' ) <= DayRange.until
),
DateRange
as
(
  select  day,
  cast( strftime( '%Y', day ) as number ) as year,
  cast( strftime( '%m', day ) as number ) as month,
  cast( strftime( '%W', day ) as number ) as week -- where is '%V' when 
one needs it :P
  fromDayRange
)
select  DateRange.*,
dense_rank() over( partition by year, month order by week ) as 
week_of_month
fromDateRange;


2019-01-01|2019|1|0|1
2019-01-02|2019|1|0|1
2019-01-03|2019|1|0|1
2019-01-04|2019|1|0|1
2019-01-05|2019|1|0|1
2019-01-06|2019|1|0|1
2019-01-07|2019|1|1|2
2019-01-08|2019|1|1|2
2019-01-09|2019|1|1|2
2019-01-10|2019|1|1|2
2019-01-11|2019|1|1|2
2019-01-12|2019|1|1|2
2019-01-13|2019|1|1|2
2019-01-14|2019|1|2|3
2019-01-15|2019|1|2|3
2019-01-16|2019|1|2|3
2019-01-17|2019|1|2|3
2019-01-18|2019|1|2|3
2019-01-19|2019|1|2|3
2019-01-20|2019|1|2|3
2019-01-21|2019|1|3|4
2019-01-22|2019|1|3|4
2019-01-23|2019|1|3|4
2019-01-24|2019|1|3|4
2019-01-25|2019|1|3|4
2019-01-26|2019|1|3|4
2019-01-27|2019|1|3|4
2019-01-28|2019|1|4|5
2019-01-29|2019|1|4|5
2019-01-30|2019|1|4|5
2019-01-31|2019|1|4|5
2019-02-01|2019|2|4|1
2019-02-02|2019|2|4|1
2019-02-03|2019|2|4|1
2019-02-04|2019|2|5|2
2019-02-05|2019|2|5|2
2019-02-06|2019|2|5|2
2019-02-07|2019|2|5|2
2019-02-08|2019|2|5|2
2019-02-09|2019|2|5|2
2019-02-10|2019|2|5|2
2019-02-11|2019|2|6|3
2019-02-12|2019|2|6|3
2019-02-13|2019|2|6|3
2019-02-14|2019|2|6|3
2019-02-15|2019|2|6|3
2019-02-16|2019|2|6|3
2019-02-17|2019|2|6|3
2019-02-18|2019|2|7|4
2019-02-19|2019|2|7|4
2019-02-20|2019|2|7|4
2019-02-21|2019|2|7|4
2019-02-22|2019|2|7|4
2019-02-23|2019|2|7|4
2019-02-24|2019|2|7|4
2019-02-25|2019|2|8|5
2019-02-26|2019|2|8|5
2019-02-27|2019|2|8|5
2019-02-28|2019|2|8|5
2019-03-01|2019|3|8|1
2019-03-02|2019|3|8|1
2019-03-03|2019|3|8|1
2019-03-04|2019|3|9|2
2019-03-05|2019|3|9|2
2019-03-06|2019|3|9|2
2019-03-07|2019|3|9|2
2019-03-08|2019|3|9|2
2019-03-09|2019|3|9|2
2019-03-10|2019|3|9|2
2019-03-11|2019|3|10|3
2019-03-12|2019|3|10|3
2019-03-13|2019|3|10|3
2019-03-14|2019|3|10|3
2019-03-15|2019|3|10|3
2019-03-16|2019|3|10|3
2019-03-17|2019|3|10|3
2019-03-18|2019|3|11|4
2019-03-19|2019|3|11|4
2019-03-20|2019|3|11|4
2019-03-21|2019|3|11|4
2019-03-22|2019|3|11|4
2019-03-23|2019|3|11|4
2019-03-24|2019|3|11|4
2019-03-25|2019|3|12|5
2019-03-26|2019|3|12|5
2019-03-27|2019|3|12|5
2019-03-28|2019|3|12|5
2019-03-29|2019|3|12|5
2019-03-30|2019|3|12|5
2019-03-31|2019|3|12|5
2019-04-01|2019|4|13|1
2019-04-02|2019|4|13|1
2019-04-03|2019|4|13|1
2019-04-04|2019|4|13|1
2019-04-05|2019|4|13|1
2019-04-06|2019|4|13|1
2019-04-07|2019|4|13|1
2019-04-08|2019|4|14|2
2019-04-09|2019|4|14|2
2019-04-10|2019|4|14|2
2019-04-11|2019|4|14|2
2019-04-12|2019|4|14|2
2019-04-13|2019|4|14|2
2019-04-14|2019|4|14|2
2019-04-15|2019|4|15|3
2019-04-16|2019|4|15|3
2019-04-17|2019|4|15|3
2019-04-18|2019|4|15|3
2019-04-19|2019|4|15|3
2019-04-20|2019|4|15|3
2019-04-21|2019|4|15|3
2019-04-22|2019|4|16|4
2019-04-23|2019|4|16|4
2019-04-24|2019|4|16|4
2019-04-25|2019|4|16|4
2019-04-26|2019|4|16|4
2019-04-27|2019|4|16|4
2019-04-28|2019|4|16|4
2019-04-29|2019|4|17|5
2019-04-30|2019|4|17|5
2019-05-01|2019|5|17|1
2019-05-02|2019|5|17|1
2019-05-03|2019|5|17|1
2019-05-04|2019|5|17|1
2019-05-05|2019|5|17|1
2019-05-06|2019|5|18|2
2019-05-07|2019|5|18|2
2019-05-08|2019|5|18|2
2019-05-09|2019|5|18|2
2019-05-10|2019|5|18|2
2019-05-11|2019|5|18|2
2019-05-12|2019|5|18|2
2019-05-13|2019|5|19|3
2019-05-14|2019|5|19|3
2019-05-15|2019|5|19|3
2019-05-16|2019|5|19|3
2019-05-17|2019|5|19|3
2019-05-18|2019|5|19|3
2019-05-19|2019|5|19|3
2019-05-20|2019|5|20|4
2019-05-21|2019|5|20|4
2019-05-22|2019|5|20|4
2019-05-23|2019|5|20|4
2019-05-24|2019|5|20|4
2019-05-25|2019|5|20|4
2019-05-26|2019|5|20|4
2019-05-27|2019|5|21|5
2019-05-28|2019|5|21|5
2019-05-29|2019|5|21|5
2019-05-30|2019|5|21|5
2019-05-31|2019|5|21|5
2019-06-01|2019|6|21|1
2019-06-02|2019|6|21|1
2019-06-03|2019|6|22|2
2019-06-04|2019|6|22|2
2019-06-05|2019|6|22|2
2019-06-06|2019|6|22|2
2019-06-07|2019|6|22|2
2019-06-08|2019|6|22|2
2019-06-09|2019|6|22|2
2019-06-10|2019|6|23|3
2019-06-11|2019|6|23|3
2019-06-12|2019|6|23|3
2019-06-13|2019|6|23|3
2019-06-14|2019|6|23|3
2019-06-15|2019|6|23|3
2019-06-16|2019|6|23|3
2019-06-17|2019|6|24|4
2019-06-18|2019|6|24|4
2019-06-19|2019|6|24|4
2019-06-20|2019|6|24|4
2019-06-21|2019|6|24|4
2019-06-22|2019|6|24|4
2019-06-23|2019|6|24|4
2019-06-24|2019|6|25|5
2019-06-25|2019|6|25|5

Re: [sqlite] Getting the week of the month from strftime or date functions

[Petite Abeille]

> On May 4, 2019, at 15:59, Luuk  wrote:
> 
> This is the 'standard' used here where i live, so i can accept that ;)

"The nice thing about standards is that you have so many to choose from."
-- Andrew Stuart "Andy" Tanenbaum

:P

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

Re: [sqlite] Getting the week of the month from strftime or date functions

[Petite Abeille]

> On May 4, 2019, at 12:47, Luuk  wrote:
> 
> As others have noted, it's a question of definition, and which definition do 
> you follow?

What about just sticking with the ISO week definition?

https://en.wikipedia.org/wiki/ISO_week_date



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

Re: [sqlite] Possible bug in Alter Table

[Petite Abeille]

> On Nov 27, 2018, at 06:16, Wout Mertens  wrote:
> 
> If it's on a mac, this terrible misfeature can be turned off in system
> preferences - keyboard - text - smart quotes.

Oh my... right you are :|

Grrr indeed.

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

Re: [sqlite] Possible bug in Alter Table

[Petite Abeille]

> On Nov 26, 2018, at 21:16, Shawn Wagner  wrote:
> 
> It's waiting on a plain single quote to end the string. You have a Unicode
> smart quote character U+2019 (’) instead of a ' at the end before the
> semicolon, which doesn't count.

D’oh. Facepalm. Right you are. Long live Unicode! :)

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

Re: [sqlite] Possible bug in Alter Table

[Petite Abeille]

> On Nov 26, 2018, at 20:25, Richard Hipp  wrote:
> 
> Unable to repro:

Hmmm… ok… then… local problem of some type…

$ uname -a
Darwin 18.2.0 Darwin Kernel Version 18.2.0: Fri Oct  5 19:41:49 PDT 2018; 
root:xnu-4903.221.2~2/RELEASE_X86_64 x86_64

$ brew info sqlite3
sqlite: stable 3.25.3 (bottled) [keg-only]

==> Dependencies
Required: readline ✔
==> Options
--with-fts
Enable the FTS3 module
--with-fts5
Enable the FTS5 module (experimental)
--with-functions
Enable more math and string functions for SQL queries
--with-json1
Enable the JSON1 extension


No clue what could interfere with the CLI.



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

Re: [sqlite] Possible bug in Alter Table

[Petite Abeille]

> On Nov 26, 2018, at 20:11, Richard Hipp  wrote:
> 
> though there probably is not time to get warnings in to the forthcoming 
> 3.26.0 release.

Talking of which, the CLI doesn’t seem to handle the following statement very 
gracefully:

sqlite> select DATE '1998-12-25’;
  ...>
  ...>
  …>

Note how the CLI doesn’t recognize the semicolon marking the end-of-statement 
and expects more input.  

sqlite3 -version

3.25.3 2018-11-05 20:37:38 
89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b75036f2
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite iOS timestamp type mapping settings must be set to float to get correct data

[Petite Abeille]

> On Nov 20, 2018, at 21:49, Thomas Kurz  wrote:
> 
>> (Does SQL itself have a numeric timestamp type, or explicitly endorse the 
>> POSIX epoch for numeric timestamps?)
> 
> SQL has an explicit TIMESTAMP type since SQL-92, one thing that I'm heavily 
> missing in SQlite ;-)

DATE '1998-12-25’ & TIMESTAMP '1997-01-31 09:26:50.124’  literals :))

But do *not* try this:

sqlite> select DATE '1998-12-25’;
   ...>
   ...>
   …>


sqlite3 -version
3.25.3 2018-11-05 20:37:38 
89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b75036f2
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite for datalogging - best practices

[Petite Abeille]

> On Oct 28, 2018, at 11:32 PM, Keith Medcalf  wrote:
> 
> will last 50 years (which is 10 times the warranty period)

Thank you.

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

Re: [sqlite] Regarding CoC

[Petite Abeille]

> On Oct 26, 2018, at 3:21 PM, Thomas Kurz  wrote:
> 
>> What'da ya think?
> 
> That's a great idea. I've already had some concerns that SQLite development 
> might cease now. Hoping for great new features in the next release :-)

MERGE! :D

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

Re: [sqlite] Regarding CoC

[Petite Abeille]

> On Oct 26, 2018, at 5:12 AM, Philip Warner  wrote:
> 
> knowingly and deliberately ignoring large chunks, and broadly disagreeing 
> with even more, and laughing at the rest.

Bah… Everything Is Amazing And Nobody Is Happy: 
https://www.youtube.com/watch?v=nUBtKNzoKZ4

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

Re: [sqlite] Regarding CoC

[Petite Abeille]

> On Oct 25, 2018, at 1:20 PM, Mike King  wrote:
> 
> I’m more a Mr Creosote kind of guy. Wafer thin mint anybody? :)

Please bring your own bucket. This is SQL*lite*. :P

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

Re: [sqlite] Regarding CoC

[Petite Abeille]

> On Oct 25, 2018, at 12:59 PM, Mike King  wrote:
> 
> The beer is bloody good and very strong.

"Beer is proof that God loves us and wants us to be happy."
— Benjamin Franklin, allegedly

https://quoteinvestigator.com/2016/06/24/beer-wine/

> Also, I’m happy to report I didn’t go up in flames when I went there and 
> purchased a case :)

Where is the Rabbit of Caerbannog when needed?

https://en.wikipedia.org/wiki/Rabbit_of_Caerbannog
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding CoC

[Petite Abeille]

> On Oct 25, 2018, at 8:33 AM, Philip Warner  wrote:
> 
>> The second exhortation tells us that that's not enough, and we also have a 
>> duty to maximise pleasure.
> 
> lol, good point. Quite the opposite of monkish orders.

And yet… 

https://en.wikipedia.org/wiki/Trappist_beer

Let SQLite be the Trappist beer of software :D

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

Re: [sqlite] Regarding CoC

[Petite Abeille]

> On Oct 22, 2018, at 11:31 PM, Simon Slavin  wrote:
> 
> And now has reached the summit of Slashdot's front page:

Slow news day. Good night, and good luck.

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

Re: [sqlite] Help!

[Petite Abeille]

> On Oct 22, 2018, at 11:35 PM, am...@juno.com wrote:
> 
> October 22, 2018 Hello Good People: I need to import a large bunch of names 
> (first and last), and id numbers into SQ Lite. How do I do en-mas--as opposed 
> to copying and pasting each individual name, clock number, etc? I would be 
> most appreciative if you would give me very explicit directions. Thanks very 
> much in advance. Respectfully yours, Alex Stavis

You could try the following perhaps:

Import a CSV File Into an SQLite Table
http://www.sqlitetutorial.net/sqlite-import-csv/



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

Re: [sqlite] Regarding CoC

[Petite Abeille]

> On Oct 22, 2018, at 10:32 PM, Donald Shepherd  
> wrote:
> 
> It's disappointing

Why so serious? Plus, really, what have the romans ever done for us?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding CoC

[Petite Abeille]

> On Oct 22, 2018, at 9:43 PM, Rob Dixon  wrote:
> 
> weird and antagonistic

Thank you for the kind words of support. Your wisdom and insights will be 
missed. Farewell Rob Dixon. Godspeed.

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

Re: [sqlite] Regarding CoC

[Petite Abeille]

> On Oct 22, 2018, at 5:29 PM, Richard Hipp  wrote:
> 
> In summary

Leave it as it is, no one is going to loose sleep over it. Now, about that 
MERGE command... :)

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

Re: [sqlite] Regarding CoC

[Petite Abeille]

> On Oct 19, 2018, at 11:05 PM, Wout Mertens  wrote:
> 
>  I can live with that.

Yes, let’s get along for once.

In the memorable words of President Dale:

"Why can't we work out our differences? Why can't we work things out? Little 
forum people, why can't we all just get along?”
Mars Attacks! (1996)

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

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Petite Abeille]

> On Oct 15, 2018, at 5:36 AM, Rowan Worth  wrote:
> 
> Anyway my point is I'm not seeing evidence to support the assertion that
> gmail treats messages to the list in general as spammish.

Ditto.

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

Re: [sqlite] sqlite-users Digest, Vol 130, Issue 11

[Petite Abeille]

> On Oct 11, 2018, at 6:35 PM, Shawn Wagner  wrote:
> 
> I'm about ready to unsubscribe from this list and not come back until it
> transitions to a forum (which is the interface I prefer anyways) because of
> all this nattering clogging up my inbox the last few days. I thought I was
> a grumpy old fart set in my ways, but I don't have anything on some of you.

Thank you for the kind word of encouragement ☺

Have we meet before?

https://www.youtube.com/watch?v=Uo0KjdDJr1c



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

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Petite Abeille]

> On Oct 9, 2018, at 7:19 PM, Warren Young  wrote:
> 
> The arguments about mailing lists vs forums have all been had.

It’s not clear why this entire conversation is presented in such stark terms: 
LIST *OR* FORUM. Puzzling.

If this is all driven by DRH edict that “all things shall run on fossil” , then 
you know what to do: list2forum + forum2list

There is no problem, if you don’t dogmatically insist on making it one.


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

Re: [sqlite] sqlite-users Digest, Vol 130, Issue 11

[Petite Abeille]

> On Oct 11, 2018, at 4:51 PM, Balaji Ramanathan  
> wrote:
> 
> The 1990's called and they want their mailing lists back.

It is/was there all the time. No one moved the cheese yet.

(11) Every old idea will be proposed again with a different name and a 
different presentation, regardless of whether it works.
RFC 1925, The Twelve Networking Truths
https://tools.ietf.org/html/rfc1925



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

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Petite Abeille]

> On Oct 10, 2018, at 8:31 PM, Warren Young  wrote:
> 
> addresses can be *forged*

forged fɔːdʒd/ adjective • copied fraudulently; fake.

O no! :( 

Perhaps the time has come for you to learn the gory details of that so-called 
'Simple Mail Transfer Protocol'! :)

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

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Petite Abeille]

> On Oct 10, 2018, at 8:31 PM, Warren Young  wrote:
> 
> The next version of Fossil is likely to include a fully-capable SMTP server

Zawinski's Law at work! :D

“Every program attempts to expand until it can read mail. Those programs which 
cannot so expand are replaced by ones which can.” 
http://www.catb.org/jargon/html/Z/Zawinskis-Law.html


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

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Petite Abeille]

> On Oct 10, 2018, at 6:02 PM, Warren Young  wrote:
> 
> I’ve written up a long list of advantages to the Fossil forum feature here:

Quixotic, but your dedication has to be applauded.

Could we agree to disagree though?

Or is it a case of "somebody is wrong on the internet”? https://xkcd.com/386/

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

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Petite Abeille]

> On Oct 9, 2018, at 11:42 PM, Simon Slavin  wrote:
> 
> On 9 Oct 2018, at 10:38pm, Keith Medcalf  wrote:
> 
>> And just what is NSFW spam?
> 
> Messages purporting to come from young women.  Some including images 
> purporting to be them in various states of undress.

( How does one subscribe? Asking for a friend… )

( Also, fwiw: please keep the mailing list, it's perfectly functional as is. )

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

Re: [sqlite] [sqlite-dev] SQLite version 3.25.0 enters release testing

[Petite Abeille]

> On Sep 11, 2018, at 8:07 PM, Richard Hipp  wrote:
> 
> We are in final testing for SQLite 3.25.0.  Details on this release
> can be seen at
> 
>   https://www.sqlite.org/draft/releaselog/3_25_0.html

Window Functions! Hurray! Hurray! :)

https://www.sqlite.org/draft/windowfunctions.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The upcoming alter table rename column

[Petite Abeille]

> On Aug 15, 2018, at 11:55 AM, R Smith  wrote:
> 
> This has been existing for quite a while in SQLite, and not only this, but 
> quite a few schema enumeration functions via the table-valued-function form 
> of the pragmas.

FWIW, information_schema would be the relevant ANSI-standard in that matter:

https://en.wikipedia.org/wiki/Information_schema

The following data dictionaries can be implemented in a pretty straightforward 
way in sqlite:

information_schema.catalog_name
information_schema.schemata
information_schema.tables
information_schema.columns
information_schema.table_constraints
information_schema.referential_constraints
information_schema.key_column_usage

YMMV.



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

Re: [sqlite] UPSERT fails for unique partial index

[Petite Abeille]

> On Aug 14, 2018, at 12:57 PM, Richard Hipp  wrote:
> 
> I will strive to bring the behavior of SQLite into alignment with PostgreSQL.

If only all this energy was spent on a proper MERGE clause instead… sigh… :D

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

Re: [sqlite] UPSERT available in pre-release

[Petite Abeille]

> On Apr 19, 2018, at 1:06 PM, Richard Hipp  wrote:
> 
> We are open to adding MERGE INTO at some point in the future.

Excellent! 

>  But the UPSERT syntax is both easier to understand

Debatable.

> and easier to implement,

Possibly.

> and we prefer to follow PostgreSQL syntax whenever possible.  See
> https://wiki.postgresql.org/wiki/UPSERT#SQL_MERGE_syntax for
> PostgreSQL's rationale for rejecting MERGE.

Let’s agree to disagree on that long running opinion piece.

MERGE, as per SQL:2003 & SQL:2008 & co. is the way to go.

The situation remind me of the introduction of recursive common table 
expression (CTE) in SQLite, which at first you wanted to implement solely in 
terms of Oracle’s 'CONNECT BY’ syntax, but ultimately saw the benefit of 
embracing the full-fledged CTE syntax instead.

And I’m personally very grateful for that thought process which gifted SQLite a 
kickass CTE implementation. Thanks for that!

Hopefully, the UPSERT vs. MERGE conversation will move that way as well: MERGE 
FTW! :)


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

Re: [sqlite] UPSERT available in pre-release

[Petite Abeille]

> On Apr 19, 2018, at 12:29 PM, Richard Hipp  wrote:
> 
> The latest pre-release snapshot [1]

Link missing?

> contains support for UPSERT
> following the PostgreSQL syntax.
>  The documentation is still pending.
> Nevertheless, early feedback is welcomed.  You can respond either to
> this mailing list, or directly to me.

Postgres UPSERT?!?

Wouldn’t a standard ANSI MERGE be more appropriate?

https://en.wikipedia.org/wiki/Merge_(SQL)

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Petite Abeille]

> On Nov 21, 2017, at 6:23 PM, Warren Young  wrote:
> 
> This is what drh does.  We’re fans because he does it well.

drh + djb = bliss?

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Petite Abeille]

> On Nov 21, 2017, at 3:30 PM, Richard Hipp  wrote:
> 
> I think what we are seeing is the beginning of the end of email as a viable 
> communication medium.

Nonsense. Email is one of these cockroach technologies: it will survive us all. 

> I really need to come up with an alternative to the mailing list.

No you don’t. You just want to.

> Perhaps some kind of forum system.  Suggestions are welcomed.

Stay put. Relax. It will pass. 

There are more productive way to spend your time: what about adding analytic 
functions, and MERGE to SQLite for one.

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Petite Abeille]

> On Nov 21, 2017, at 8:42 PM, Warren Young  wrote:
> 
> As far as I can tell, the only really hard part is the email gatewaying 
> problem, evidenced by the fact that Fossil still doesn’t have a feature to 
> echo commits, ticket changes, etc. via email.

“Every program attempts to expand until it can read mail. Those programs which 
cannot so expand are replaced by ones which can.” 

— Zawinski's Law


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

Re: [sqlite] group_concat() reverses order given where clause?

[Petite Abeille]

> On Aug 16, 2017, at 11:11 AM, Jean-Luc Hainaut  
> wrote:
> 
> The implementation of SQLite "group_concat" (a very powerful but often 
> overlooked function) provides some but not all the features found in other 
> DBMS (MySQL "group_concat" and PostgreSQL "string_agg" for example):

For diversity's sake, Oracle’s LISTAGG:

https://docs.oracle.com/cloud/latest/db112/SQLRF/functions089.htm#SQLRF30030


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

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Petite Abeille]

> On Jun 28, 2017, at 4:15 PM, R Smith  wrote:
> 
> I did ponder whether it would be a nice "feature" to use the default if both 
> a DEFAULT and a NOT NULL constraint existed on a column - but then again, 
> that will go against strict design principles and can cause a lot of 
> confusion later.

Some databases, which we shall not name, provide both options [1]:

- DEFAULT for columns without an explicit value
- DEFAULT ON NULL for columns with an explicitly null

[1] 
https://oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1#nulls

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

Re: [sqlite] Thread safety of serialized mode

[Petite Abeille]

> On Feb 17, 2017, at 12:21 AM, Warren Young  wrote:
> 
> How can we expect people to write threaded programs when even a simple 
> integer increment is prone to race conditions and read-modify-write errors?

"… we did not (and still do not) believe in the standard multithreading model, 
which is preemptive concurrency with shared memory: we still think that no one 
can write correct programs in a language where ‘a=a+1’ is not deterministic.”

— Roberto Ierusalimschy & Co., The Evolution of Lua
https://www.lua.org/doc/hopl.pdf

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

Re: [sqlite] Group contiguous rows (islands)

[Petite Abeille]

> On Feb 15, 2017, at 11:16 AM, Clemens Ladisch  wrote:
> 
> SQLite does not have windowing functions.

A continuous/continual tragedy indeed :|

Still, worthwhile mentioning The Tabibitosan Method, for reference purpose:

http://www.orchestrapit.co.uk/?p=53
https://community.oracle.com/message/3991678

Rather nifty in its simplicity and power. Sadly, out of reach to SQLite 
dwellers.




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

Re: [sqlite] 2 consecutive rises in value

[Petite Abeille]

> On Oct 20, 2016, at 1:58 AM, Bart Smissaert  wrote:
> 
> I worked it round to get 3 consecutive drops:
> 
> but I must have done something wrong as it picked 62 up from this:

It has three consecutive drops, no? E.g.  47 < 46 < 37


Example:

with
DataSet
as
(
  select 62 as key, '2005-01-07' as date, 44 as value union all
  select 62 as key, '2006-02-01' as date, 47 as value union all 
  select 62 as key, '2006-05-22' as date, 45 as value union all
  select 62 as key, '2007-04-05' as date, 45 as value union all
  select 62 as key, '2007-08-14' as date, 45 as value union all
  select 62 as key, '2008-05-21' as date, 46 as value union all
  select 62 as key, '2009-08-24' as date, 46 as value union all
  select 62 as key, '2010-10-08' as date, 45 as value union all
  select 62 as key, '2011-12-07' as date, 47 as value union all
  select 62 as key, '2013-01-17' as date, 46 as value union all
  select 62 as key, '2014-02-25' as date, 37 as value union all
  select 62 as key, '2015-03-30' as date, 39 as value union all
  select 62 as key, '2016-09-02' as date, 40 as value 
),
NextSet
(
  key,
  date,
  value,
  next_date,
  count
)
as
(
  select  DataSet.*,
  (
selectSelf.date
from  DataSet as Self
where Self.key = DataSet.key
and   Self.date > DataSet.date
order by  Self.date
limit 1
  ) as next_date,
  0 as count
  fromDataSet

  union all
  select  NextSet.key as key,
  NextSet.date as date,
  NextSet.value as value,
  (
selectSelf.date
from  DataSet as Self
where Self.key = NextSet.key
and   Self.date > NextSet.next_date
order by  Self.date
limit 1
  ) as next_date,
  NextSet.count + 1 as count
  fromNextSet
  where   exists
  (
select  1
fromDataSet
where   DataSet.key = NextSet.key
and DataSet.date = NextSet.next_date
and DataSet.value < NextSet.value
  )
)
selectNextSet.key
from  NextSet
where NextSet.count = 3

group by  NextSet.key

order by  1;

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

Re: [sqlite] 2 consecutive rises in value

[Petite Abeille]

> On Oct 20, 2016, at 12:23 AM, Bart Smissaert  wrote:
> 
> Ah, yes, sorry, I needed actually drops and was looking at that.

Just for fun, here is one using recursion:

with
DataSet
as
(
  select 1 as id, 2 as key, '2004-06-23' as date, 42 as value union all
  select 2 as id, 2 as key, '2006-12-28' as date, 39 as value union all
  select 3 as id, 2 as key, '2007-10-09' as date, 42 as value union all
  select 4 as id, 2 as key, '2007-10-24' as date, 43 as value union all
  select 5 as id, 2 as key, '2009-06-17' as date, 45 as value union all
  select 6 as id, 2 as key, '2015-09-09' as date, 36 as value
),
NextSet
(
  id,
  key,
  date,
  value,
  next_date,
  count
)
as
(
  select  DataSet.*,
  (
selectSelf.date
from  DataSet as Self
where Self.key = DataSet.key
and   Self.date > DataSet.date
order by  Self.date
limit 1
  ) as next_date,
  0 as count
  fromDataSet

  union all
  select  NextSet.id as id,
  NextSet.key as key,
  NextSet.date as date,
  NextSet.value as value,
  (
selectSelf.date
from  DataSet as Self
where Self.key = NextSet.key
and   Self.date > NextSet.next_date
order by  Self.date
limit 1
  ) as next_date,
  NextSet.count + 1 as count
  fromNextSet
  where   exists
  (
select  1
fromDataSet
where   DataSet.key = NextSet.key
and DataSet.date = NextSet.next_date
and DataSet.value > NextSet.value
  )
)
selectNextSet.key,
  min( NextSet.id ) as min_id,
  max( NextSet.id ) as max_id
from  NextSet
where NextSet.count = 2

group by  NextSet.key

order by  1;

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

Re: [sqlite] 2 consecutive rises in value

[Petite Abeille]

> On Oct 19, 2016, at 11:40 PM, Bart Smissaert  wrote:
> 
> ID 2 for example shouldn't be selected.

Hmmm? ID 2 has two ‘raises' as per your definition: 39 > 42 > 43 and 42 > 43 > 
45

2 2004-06-23 42
2 2006-12-28 39
2 2007-10-09 42
2 2007-10-24 43
2 2009-06-17 45
2 2015-09-09 36

Or?

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

Re: [sqlite] Searching for a percent symbol

[Petite Abeille]

> On Oct 5, 2016, at 11:50 AM, Paul Sanderson  
> wrote:
> 
> How do I search for a % symbol within a string when % is a wild card
> and I am escaping that very wildcard?

For example:

with
DataSet
as
(
  select 'I got 20 quid' as value union all
  select 'i got 20% of it' as value union all
  select 'i got just 20%' as value union all
  select 'some money' as value union all
  select 'this is an underscore _ ok' as value
)
select  value
fromDataSet
where   value like '%\%%' escape '\’;


i got 20% of it
i got just 20%

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

Re: [sqlite] Searching for a percent symbol

[Petite Abeille]

> On Oct 5, 2016, at 11:38 AM, Paul Sanderson  
> wrote:
> 
> How casn I find just the rows containing the percent symbol? is it possible?

Use the ESCAPE clause:

"If the optional ESCAPE clause is present, then the expression following the 
ESCAPE keyword must evaluate to a string consisting of a single character. This 
character may be used in the LIKE pattern to include literal percent or 
underscore characters. The escape character followed by a percent symbol (%), 
underscore (_), or a second instance of the escape character itself matches a 
literal percent symbol, underscore, or a single escape character, respectively.”
— The LIKE, GLOB, REGEXP, and MATCH operators

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

Re: [sqlite] smartest way to exchange a sqlite3 database with another empty

[Petite Abeille]

> On Oct 3, 2016, at 11:18 AM, Luca Ferrari  wrote:
> 
> What is the right way to do it without having to stop the application
> (and therefore without knowing when a new I/O operation will be
> issued)?

You could use the attach/detach [1] functionality to transparently roll the 
logs over.

Something like main -> attach current -> insert current.log

Every now and then, change what ‘current’ points to.

[1] https://www.sqlite.org/lang_attach.html

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

Re: [sqlite] SQLite 3.15.0 scheduled for 2016-10-14

[Petite Abeille]

> On Sep 22, 2016, at 9:04 PM, Richard Hipp  wrote:
> 
> (https://www.sqlite.org/draft/releaselog/3_15_0.html).

Oh! Row Values! Nice! :)

https://www.sqlite.org/draft/rowvalue.html



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

[sqlite] FOREIGN KEY constraint failed

[Petite Abeille]

> On Apr 4, 2016, at 6:14 PM, Richard Hipp  wrote:
> 
> On 4/4/16, Domingo Alvarez Duarte  wrote:
>> sqlite knows which table/field failed
> 
> No it doesn't, actually. 

And yet, that same question comes over, and over, and over, ad nauseam. Each 
and every time a poor soul is confronted with that obscure message. Sigh.

Oh well? 

[sqlite] Fastest way to find whether at least one row has a certain column value

[Petite Abeille]

> On Mar 6, 2016, at 9:37 PM, James K. Lowden  
> wrote:
> 
> I've never seen a system that provides queryable optimizer metadata.  

Oracle does, for a given definition of ? queryable?.

> I don't remember ever having read a paper on the idea, either.

I have to confess using query plans to get free-form (facetted searches) query 
estimate *before* deciding to run them or not :D

[sqlite] whish list for 2016

[Petite Abeille]

> On Dec 21, 2015, at 5:43 PM, Darren Duncan  wrote:
> 
> Comic act?  Do you consider the MERGE defined in the SQL standard to be a 
> better designed feature than Postgres' alternative,

Yes.

> or do you prefer the former soley because it is in the SQL standard?

Yes.

>  I recall that Postgres went with their brand-new alternative because it was 
> a better design, more useable in practice; both simpler and more powerful, 
> easier to express the user's intent and not have arbitrary limitations. 

No.

But lets agree to disagree in the spirit of the Holiday Season! :)

[sqlite] whish list for 2016

[Petite Abeille]

> On Dec 21, 2015, at 4:08 AM, Darren Duncan  wrote:
> 
> If you want that feature, instead do it the better way that Postgres 9.5 did, 
> which is as an extension to the INSERT statement in the form "ON CONFLICT DO 
> UPDATE/IGNORE?.

Please, enough of the comic act :P

MERGE it is.

Oh, yes, also, +1 for for Mr Smith PRAGMA strict_mode = 1; :))

[sqlite] whish list for 2016

[Petite Abeille]

> On Dec 20, 2015, at 7:12 PM, Big Stone  wrote:
> 
> To prepare for 2016 greetings moment, here is my personnal whish list
> for sqlite:

MERGE statement! Yeah! :)

https://en.wikipedia.org/wiki/Merge_(SQL)

[sqlite] [OT] Re: Worked in 3.8.9, but now "recursive aggregate queries not supported"

[Petite Abeille]

> On Sep 13, 2015, at 3:53 PM, E.Pasma  wrote:
> 
> I just learned that the strftime function returns '0' for Sundays, not 0, and 
> that 0<>'0?.

Yes, always compare likes to likes:

with
DataSet
as
(
  select strftime( '%w', date() ) as value
)
select  value,
typeof( value ) as typeof1,
cast( value as interger ) as integer,
typeof( cast( value as interger ) ) as typeof2
fromDataSet

> value|typeof1|integer|typeof2
> 0|text|0|integer

[sqlite] Feedback request: JSON support in SQLite

[Petite Abeille]

> On Sep 11, 2015, at 6:31 PM, Richard Hipp  wrote:
> 
> a passing fad

On the other hand, we can now embrace NormalFormZero without undue 
embarrassment. Swell.

[sqlite] Feedback request: JSON support in SQLite

[Petite Abeille]

> On Sep 11, 2015, at 5:58 PM, Richard Hipp  wrote:
> 
> JSON support in SQLite

JSON, eh? No MERGE. No analytics. But serialization of the week is covered. 
Sweet :D

[sqlite] Fwd: OT: Oracle functions for SQlite

[Petite Abeille]

Perhaps of interest:

http://sqlite-libs.cis.ksu.edu



> Begin forwarded message:
> 
> From: St?phane Faroult 
> Subject: OT: Oracle functions for SQlite
> Date: September 8, 2015 at 2:30:24 AM GMT+2
> To: "Oracle-L (E-mail)" 
> Reply-To: sfaroult at roughsea.com
> 
> I don't know if there are many people on the list using SQLite, but I use it 
> more and more often; teaching SQL is one reason (give a master file to 
> students, and let them create, drop tables, run DML at will without any 
> worry, and no need to bother about having a conveniently set server), another 
> one is consulting, whenever I'd *like* to store some data but I am either 
> unauthorized or unwilling to create my stuff on the database I'm working on. 
> Great also for implementing the poor man's performance pack - dump your v$ 
> every so often to a SQLite file, and you have something far more flexible 
> than statspack. 
> The only snag is that SQLite is a bit weak function-wise. I have last spring 
> given as assignment to the students in one of my classes the writing for 
> SQLite of functions available in other products. Making everything 
> homogeneous, writing a few functions I couldn't decently ask of 
> undergraduates (even if I usually set the bar rather high), substituting my 
> own date functions to the standard Unix ones so as to have the same 
> behavior as Oracle in October 1582 and so forth has been a huge endeavor (not 
> finished), it may still be a bit rough here and there but I have started 
> publishing this collective effort as an open source library. 
> 
> It's at http://sqlite-libs.cis.ksu.edu/  
> 
> There isn't EVERYTHING, but all the classic functions are there. 
> 
> Enjoy. 
> 
> St?phane Faroult 

[sqlite] Aquameta Layer 0: meta - Writable System Catalog for PostgreSQL

[Petite Abeille]

> On Aug 29, 2015, at 11:28 PM, Simon Slavin  wrote:
> 
>   Someone else wrote some code which performed various queries and created 
> real TABLEs with the appropriate data in. 

Yes, something along these lines:


[[
attach database 'information_schema.db' as information_schema
]],

[[
pragma information_schema.automatic_index = off
]],

[[
pragma information_schema.encoding = 'utf-8'
]],

[[
pragma information_schema.foreign_keys = off
]],

[[
pragma information_schema.journal_mode = off
]],

[[
pragma information_schema.legacy_file_format = off
]],

[[
pragma information_schema.locking_mode = exclusive
]],

[[
pragma information_schema.synchronous = off
]],

[[
pragma information_schema.temp_store = memory
]],

[[
drop table if exists information_schema.catalog_name
]],

[[
create table if not exists information_schema.catalog_name
(
catalog_nametext not null default 'main',

constraint  catalog_name_pk 
primary key ( 
catalog_name
)
)
]],

[[
insert
intoinformation_schema.catalog_name
(
catalog_name
)
values  (
'main'
)
]],

[[
drop table if exists information_schema.schemata
]],

[[
create table if not exists information_schema.schemata
(
catalog_nametext not null default 'main',
schema_name text not null,

constraint  schemata_pk 
primary key ( 
catalog_name, 
schema_name 
),

constraint  schemata_catalog_name_fk 
foreign key ( 
catalog_name
) 
references  catalog_name
( 
catalog_name
)
)
]],

[[
drop table if exists information_schema.tables
]],

[[
create table if not exists information_schema.tables
(
catalog_nametext not null default 'main',
schema_name text not null,
table_name  text not null,

table_type  text not null,

constraint  tables_pk 
primary key ( 
catalog_name, 
schema_name, 
table_name 
),

constraint  tables_schemata_fk 
foreign key ( 
catalog_name, 
schema_name 
) 
references  schemata
( 
catalog_name, 
schema_name 
)
)
]],

[[
drop table if exists information_schema.columns
]],

[[
create table if not exists information_schema.columns
(
catalog_nametext not null default 'main',
schema_name text not null,
table_name  text not null,
column_name text not null,

ordinal_positioninteger not null,
column_default  text,
is_nullable text not null,
data_type   text not null,

constraint  columns_pk 
primary key ( 
catalog_name, 
schema_name, 
table_name, 
column_name 
),

constraint  columns_uk 
unique  ( 
catalog_name, 
schema_name, 
table_name, 
ordinal_position 
),

constraint  columns_tables_fk 
foreign key ( 
catalog_name, 
schema_name, 
table_name 
) 
references  tables
( 
catalog_name, 
schema_name, 
table_name 
)
)
]],

[[
drop table if exists information_schema.table_constraints
]],

[[
create table if not exists information_schema.table_constraints
(
constraint_catalog  text not null default 'main',
constraint_schema   text not null,
constraint_name text not null,

catalog_nametext not null default 'main',
schema_name text not null,
table_name  text not null,

constraint_type text not null,

constraint  table_constraints_pk 
primary key ( 
constraint_catalog, 
  

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Petite Abeille]

> On Aug 25, 2015, at 8:53 PM, R.Smith  wrote:
> 
> I vote to change it every release... Stimulate better habits!

Seconded. Keep them on their toes!

[sqlite] order by not working in combination with random()

[Petite Abeille]

> On Aug 17, 2015, at 12:01 PM, Clemens Ladisch  wrote:
> 
> Just because the ORDER BY clause refers to a column of the
> SELECT clause does not mean that the value is not computed
> a second time.

And yet:

with
DataSet( position, value )
as
(
  select  1 as position,
  random() as value
  union all
  select  DataSet.position + 1 as position,
  random() as value
  fromDataSet
  where   DataSet.position < 10
)
selectDataSet.value
from  DataSet

order by  DataSet.value;

-4870943077918547304
-4477154295778822007
-3065995363549703972
-2365142629623062778
-1659827499818666566
-422422602260941678
122478153305412750
2673926196199910076
2741194622440706627
4802559261241955862

[sqlite] Enhance the SELECT statement?

[Petite Abeille]

> On Aug 17, 2015, at 8:08 PM, R.Smith  wrote:
> 
> CORRECTION: It seems one of the two options I've mentioned earlier, namely 
> the CREATE TABLE AS SELECT... does not actually work on the back of a WITH 
> clause. The other option still do, but this request has more appeal now.

Hmmm?!?

create table foo as

with
DataSet( position )
as
(
  select  1 as position
  union all
  select  DataSet.position + 1 as position
  fromDataSet
  where   DataSet.position < 10
)
select  *
fromDataSet;

select * from foo;

[sqlite] Enhance the SELECT statement?

[Petite Abeille]

> On Aug 17, 2015, at 5:44 PM, John McKown  
> wrote:
> 
> One thing that would be really useful for me in SQLite3 which is in 
> PostgreSQL is the INTO phrase.

CTAS? AKA 'create table as'?

[sqlite] Doc page revision request

[Petite Abeille]

> On Jul 22, 2015, at 12:40 AM, R.Smith  wrote:
> 
> Reminds me of the old saying:  I'd rather have a bottle in front of me than a 
> frontal lobotomy!

Cheers to that! :D

[sqlite] Mozilla wiki 'avoid SQLite'

[Petite Abeille]

> On Jun 18, 2015, at 11:11 PM, James K. Lowden  
> wrote:
> 
> There's a reason Larry Ellison can affort a yacht almost 500 feet long.  

And an island to moor it.

In any case, best luck to David and his project. He will need it.

[sqlite] Mozilla wiki 'avoid SQLite'

[Petite Abeille]

> On Jun 18, 2015, at 5:10 PM, Marc L. Allen  
> wrote:
> 
> Frankly, I've avoided taking a close look at Andl because of its massive use 
> of special characters.  I can usually work out a rough meaning on your 
> examples because I know the SQL it came from.  If I became 'Andl proficient' 
> it probably wouldn't be a problem, but I gotta ask.. what's the issue with 
> just using keywords?  I feel like I'm looking at APL.

mwahahaha! ?. my feeling exactly :P

[sqlite] User-defined types -- in Andl

[Petite Abeille]

> On Jun 14, 2015, at 4:01 PM, david at andl.org wrote:
> 
> First, I added a RECURSE() function to Andl, similar to the CTE in SQLite.

Nice.

> The Mandelbrot algorithm looks like this.

Could we see something more, hmmm, pedestrian? Perhaps a simple recursive 
query, showing, say, all the managers of an employee given the following 
structure: create table employee( id integer not null, manager_id integer, 
constraint  employee_pk primary key( id ), constraint employee_manager_fk 
foreign key( manager_id ) references employee( id )  )

[sqlite] User-defined types -- in Andl

[Petite Abeille]

> On Jun 9, 2015, at 2:53 PM, Jean-Christophe Deschamps  
> wrote:
> 
> Most probably! I can imagine that you don't encounter such style in common 
> business-like environments.

Just for ?corporate' fun: analytic recursive common table expression - oh, my?

with
Clock( start_at, end_at, interval, tick )
as
(
  select  date '2015-01-01' as start_at,
  timestamp '2015-01-01 23:59:59' as end_at,
  interval '15' minute  as interval,
  date '2015-01-01' as tick
  fromdual

  union all
  select  start_at,
  end_at,
  interval,
  tick + interval as tick
  fromClock
  where   tick + interval between start_at and end_at
)
selectClock.tick,
  lead( Clock.tick ) over( order by Clock.tick ) as next_tick
from  Clock

order by  Clock.tick;

[sqlite] AUTOINC vs. UUIDs

[Petite Abeille]

> On May 20, 2015, at 8:05 PM, Simon Slavin  wrote:
> 
> "Today, I?ll talk about why we stopped using serial integers for our primary 
> keys, and why we?re now extensively using Universally Unique IDs (or UUIDs) 
> almost everywhere.?

Argh? seriously?

tl;dr: don?t.

[sqlite] Best way to temporarily store data before processing

[Petite Abeille]

> On Apr 14, 2015, at 11:40 AM, Jonathan Moules  
> wrote:
> 
> Options that have come to mind (probably missed a lot):

I personally use temp tables, e.g. 'create temporary table if not exists foo?, 
coupled with 'pragma temp_store = memory?, and drop/create them as necessary, 
e.g. 'drop table if exists?. There is a set of ETL (Extract, Transform and 
Load) operations to massage the data just so to their final resting place.

Will not qualify this as ?best?, but it?s very workable and without much of a 
headache. As always, your milage may vary.

[sqlite] Transpose selected rows into columns

[Petite Abeille]

> On Apr 7, 2015, at 5:53 PM, John McKown  
> wrote:
> 
> I'm an idiot, thanks for not pointing that out. I thought I was on the
> PostgreSQL forum and managed to mess up. My apologies to all.

We all have been there. 

I, for one, wish SQLite had some syntax sugar such as PIVOT/UNPIVOT:

http://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php

More handy than I care to admit :)

[sqlite] bug in transactions implementation ?

[Petite Abeille]

> On Mar 18, 2015, at 10:24 PM, Jason Vas Dias  
> wrote:
> 
> This seems very buggy to me.

Correct.

http://www.styleite.com/wp-content/uploads/2014/11/legallyblonde.gif

[sqlite] how to determine the status of last statement in sqlite3 shell ?

[Petite Abeille]

> On Mar 18, 2015, at 9:51 PM, Jason Vas Dias  
> wrote:
> 
> I don't see how anything like that is possible in the sqlite3 shell .

Not in the shell per se, no (.bail on|off may or may not help in your case). 

Perhaps ON CONFLICT clause might help you:

https://www.sqlite.org/lang_conflict.html

Or you can trail all your transactions with a commit; rollback; pair.  

[sqlite] bug in transactions implementation ?

[Petite Abeille]

> On Mar 18, 2015, at 9:45 PM, Jason Vas Dias  
> wrote:
> 
> Would you care to expand on that ?

As it says on the tin [1]: you cannot start a transactions inside another 
transaction (use savepoint if you want that), so?

create table foo( value text, constraint uk unique( value ) );
begin transaction; insert into foo( value ) values( 'bar' ); commit; ? ok
begin transaction; insert into foo( value ) values( 'bar' ); commit; ? Error: 
UNIQUE constraint failed: foo.value
begin transaction; insert into foo( value ) values( 'baz' ); commit; ? Error: 
cannot start a transaction within a transaction
rollback; ? ok
begin transaction; insert into foo( value ) values( 'baz' ); commit; ? ok


[1] https://www.sqlite.org/lang_transaction.html

[sqlite] bug in transactions implementation ?

[Petite Abeille]

> On Mar 18, 2015, at 9:11 PM, Jason Vas Dias  
> wrote:
> 
> am I missing something?

rollback?

Re: [sqlite] Support for millisecond

[Petite Abeille]

> On Jan 8, 2015, at 7:21 PM, Lance Shipman  wrote:
> 
> Can SQLite support millisecond precision in date time data? I looking at doc 
> I think so, but it's not clear.

There is no 'date time’ data type in SQLite. Feel free to store your time data 
as either text or number. To whatever precision suits you.

There are a couple of built-in utility functions to convert things back and 
forth:

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

Re: [sqlite] using a hex integer as input to DateTime

[Petite Abeille]

> On Jan 7, 2015, at 11:35 PM, Paul Sanderson  
> wrote:
> 
> Hmm - why can't I get that to work when the hex value is stored in a
> column, i.e.

Most likely your data is stored as text, e.g.:

with
DataSet
as
(
  select  '0x49962d2' as value
  union all
  select  0x49962d2 as value
)
select  DataSet.value,
DateTime( DataSet.value, 'unixepoch' ) as date,
typeof( DataSet.value ) as type
fromDataSet

value|date|type
0x49962d2||text
77161170|1972-06-12 01:39:30|integer

To quote the nice manual:

"hexadecimal integer notation is only understood by the SQL language parser, 
not by the type conversions routines”:

https://www.sqlite.org/lang_expr.html#hexint



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

Re: [sqlite] using a hex integer as input to DateTime

[Petite Abeille]

> On Jan 7, 2015, at 11:08 PM, Paul Sanderson  
> wrote:
> 
> Is this possible?

With a contemporary version of SQLite, yes:

sqlite> select DateTime(77161170, 'unixepoch');
1972-06-12 01:39:30

sqlite> select DateTime(0x49962d2, 'unixepoch');
1972-06-12 01:39:30


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

Re: [sqlite] Whish List for 2015

[Petite Abeille]

> On Dec 21, 2014, at 10:47 AM, big stone  wrote:
> 
> - a minimal subset of analytic functions

+ MERGE! Yeah!

Happy Holidays!

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

Re: [sqlite] Search for text in all tables

[Petite Abeille]

> On Dec 4, 2014, at 10:26 PM, Roger Binns  wrote:
> 
> That will only work under the simplest of cases. 

Simplicity first and foremost. 

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

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

[Petite Abeille]

On Oct 8, 2014, at 8:51 PM, Stephen Chrzanowski  wrote:

> If the field def'n were to be changed to [ col2 NUMBER DEFAULT ON NULL 0 ]
> and then when I insert/update something that becomes NULL and the result
> becomes 0 for that field, then yeah, bingo.

Yep, that’s exactly what it says on the tin.

But back to SQLite... your best bet is to implement such behavior with 
triggers. Same difference really.

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

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

[Petite Abeille]

On Oct 8, 2014, at 6:14 AM, Stephen Chrzanowski  wrote:

> When adding a NULL value to a table that has the NOT NULL flag set on that
> field, instead of raising an exception, if the field definition were to
> have the word "USE" between "ON CONFLICT" and "DEFAULT" in its declaration,
> it'd use whatever the fields default value was set to.  If USE is included,
> the DEFAULT value must be included, otherwise the table isn't created.

Oh, wait… isn’t that what an other, unnamed database does? [1]

E.g. DEFAULT ON NULL

[1] 
http://www.oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1.php#nulls


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

Re: [sqlite] An order by problem, maybe a bug?

[Petite Abeille]

On Sep 20, 2014, at 9:21 PM, Simon Slavin  wrote:

> Anyone who worked for a big company these days and created such a database 
> should get called in and told to do it again properly.

Along these same lines:

Your last name contains invalid characters
http://blog.jgc.org/2010/06/your-last-name-contains-invalid.html

Falsehoods Programmers Believe About Names
http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/

How do you like the vCard specification?

http://tools.ietf.org/html/rfc6350

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

Re: [sqlite] Create join and add unique column

[Petite Abeille]

On Sep 16, 2014, at 8:15 PM, RSmith  wrote:

> could you show how to achieve this in SQL via the ranking method you linked

Well, ranking is the same as numbering, no?

So, for example:

with
NameSet
as
(
  select  1 as id, 'paul' as name union all
  select  2 as id, 'helen' as name union all
  select  3 as id, 'melanie' as name
),
CountrySet
as
(
  select  1 as id, 'uk' as name union all
  select  20 as id, 'scotland' as name -- 
),
DataSet
as
(
  select  NameSet.id || '.' || CountrySet.id as key,
  NameSet.id as name_id,
  NameSet.name as name_name,
  CountrySet.id as country_id,
  CountrySet.name as country_name
  fromNameSet
  cross join  CountrySet
)
selectcount( * ) as id,
  DataSet.name_id as name_id,
  DataSet.name_name as name_name,
  DataSet.country_id as country_id,
  DataSet.country_name as country_name
from  DataSet

join  DataSet self
onself.key >= DataSet.key

group by  DataSet.name_id,
  DataSet.name_name,
  DataSet.country_id,
  DataSet.country_name

order by  1;


> id|name_id|name_name|country_id|country_name
> 1|3|melanie|2|scotland
> 2|3|melanie|1|uk
> 3|2|helen|2|scotland
> 4|2|helen|1|uk
> 5|1|paul|2|scotland
> 6|1|paul|1|uk


Or something :D

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

Re: [sqlite] BUG: Aggregate functions in subqueries

[Petite Abeille]

On Sep 15, 2014, at 4:48 PM, Richard Hipp  wrote:

> On Sun, Sep 14, 2014 at 12:18 AM, Lea Verou  wrote:
> 
>> Per the 3.7.11 changelog [1], queries of the form SELECT max(x), y FROM
>> table return the value of y from the same row that contains the maximum x
>> value. However, this:
>> 
>> select y from (SELECT max(x), y FROM table);
>> 
>> would not return the same y rows. This would work as expected:
>> 
>> select m, y from (SELECT max(x) as m, y FROM table);
>> 
> 
> I'm not sure if this qualifies as a "bug" or not, since the behavior is
> unspecified in the official documentation.  Nevertheless, it is now fixed
> on trunk.

Considering that the original query is non-sensical to start with, not quite 
sure what’s there to fix in the first place. Aside, of course, from raising an 
exception.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SET (x,y) = (x1,y1)?

[Petite Abeille]

On Sep 15, 2014, at 7:08 PM, Hick Gunter  wrote:

> Maybe you can reformulate the query to fit
> 
> INSERT OR UPDATE INTO t SELECT t.a,t.b,...,s.x,s.y FROM t, s …

There is no such a thing as 'INSERT OR UPDATE’ in SQLite. There is a ‘REPLACE’, 
but it’s definitively not the same as an update. 

Anyway, what the OP would benefit from is a straightforward MERGE statement:

http://en.wikipedia.org/wiki/Merge_(SQL)

But there is no such functionality in SQLite either. Sigh...


 



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

Re: [sqlite] XML into sqlite

[Petite Abeille]

On Sep 11, 2014, at 5:45 PM, Carlos A. Gorricho  
wrote:

> Next step is to venture into XML - sqlite integration...both ways. 

Considering you are on a *nix system, you may find Dan Egnor’s xml2 set of 
command line utilities of interest:

http://www.ofb.net/~egnor/xml2/

Allows for rather straightforward transformation of XML into something more 
palpable, and back.

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

Re: [sqlite] Index on expressions

[Petite Abeille]

On Sep 3, 2014, at 3:01 PM, Dominique Devienne  wrote:

> Asked differently, if adding this support, could this be done by adding
> virtual / computed columns to tables, and indexing those columns?

Ohohohoho… virtual columns [1][2]…. yes… shinny! :)

Now that would be rather cool. 

On the other hand, if one had to choose, I would rather see a MERGE statement, 
than some funky virtual columns.

While virtual columns are handy at time, they are a bit exotic, all things 
being equal. 

On the other hand, MERGE is a must have. No amount of creative select + insert 
+ update concoctions can begin to compensate for its absence in SQLite. A huge 
gap altogether.


[1] http://en.wikipedia.org/wiki/Virtual_column
[2] http://www.oracle-base.com/articles/11g/virtual-columns-11gr1.php

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

Re: [sqlite] Selecting dates...

[Petite Abeille]

On Sep 2, 2014, at 9:48 PM, jose isaias cabrera  wrote:

> Thoughts?  Thanks.

SQLite doesn’t have date per se. You are free to store dates as either text or 
number, or anything you please. But it’s your responsibility to keep it 
straight.

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

Re: [sqlite] Sorting by month with two dates input

[Petite Abeille]

On Sep 1, 2014, at 9:19 PM, Keith Medcalf  wrote:

> 
>> On Sep 1, 2014, at 6:00 PM, Igor Tandetnik  wrote:
>> 
>>> (case when billdate != '' then billdate else bdate end)
>> 
>> Or, more succinctly:
>> 
>> coalesce( nullif( billdate, '' ), bdate )
>> 
>> (To OP: empty strings are E V I L. Don't use them. Ever.)
> 
> Unless of course it is one or more spaces and not an empty string ...
> 
> coalesce(nullif(rtrim(billdate), ''), bdate)
> 
> an empty string is indistinguishable from a string of one or more spaces 
> unless one makes efforts to tell the difference.

(To OP: if this is the case, you get what you deserve! :D )
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sorting by month with two dates input

[Petite Abeille]

On Sep 1, 2014, at 6:00 PM, Igor Tandetnik  wrote:

> (case when billdate != '' then billdate else bdate end)

Or, more succinctly:

coalesce( nullif( billdate, ‘’ ), bdate )

(To OP: empty strings are E V I L. Don’t use them. Ever.)

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

Re: [sqlite] Window functions?

[Petite Abeille]

On Aug 27, 2014, at 10:57 PM, Eduardo Morras  wrote:

> Sorry, don't understand why others will throw an exception in the group by, 
> perhaps I'm misunderstanding the group by, but that should work on others 
> engines.

Because not all expressions are accounted for, i.e.:

"not a GROUP BY expression

Cause: The GROUP BY clause does not contain all the expressions in the SELECT 
clause. SELECT expressions that are not included in a group function, such as 
AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY 
clause.

Action: Include in the GROUP BY clause all SELECT expressions that are not 
group function arguments.”

Try it. See what happen.



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

Re: [sqlite] Window functions?

[Petite Abeille]

On Aug 26, 2014, at 2:09 AM, Keith Medcalf  wrote:

>  select id, category_id, name, min(price) as minprice
>from cat_pictures
> group by category_id;
> 
> Done.  And no need for any windowing functions …

This peculiar behavior is very unique to SQLite. Most reasonable SQL engines 
will throw an exception when confronted with the above. SQLite calls it a 
feature. I personally see it as a misfeature. ( Ditto with tagging an implicit 
limit 1  to scalar queries. Anyway. )

On the other hand, one could look at the current ‘group by’ behavior as 
exhibited by SQLite as a precursor to a proper, more formalize, handling of 
analytic functions…. :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users