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

[Jens Alfke]

> On May 6, 2019, at 6:15 PM, Warren Young  wrote:
> 
> Ideas for fixing this aren’t new. 

The French had a supremely utopian "Republican Calendar" that lasted from 1793 
to 1805 ("and for 18 days by the Paris Commune 
 in 1871" … such pathos in that 
little aside.)

> There were twelve months, each divided into three ten-day weeks called 
> décades. The tenth day, décadi, replaced Sunday as the day of rest and 
> festivity. The five or six extra days needed to approximate the solar or 
> tropical year were placed after the months at the end of each year and called 
> complementary days. … Each day in the Republican Calendar was divided into 
> ten hours, each hour into 100 decimal minutes, and each decimal minute into 
> 100 decimal seconds."

[https://en.wikipedia.org/wiki/French_Republican_calendar]

Face it, if they couldn't ram through a pointy-headed decimalized regularized 
calendar during the effin' *French Enlightenment*, it's certainly not going to 
work in the current dark ages.

Also relevant to this entire thread, since apparently a lot of people aren't 
aware of this stuff:

Falsehoods Programmers Believe About Time 

 (really a must-read for anyone dealing with dates and times)
You Advocate An Approach To Calendar Reform; Your Idea Will Not Work; Here Is 
Why  (brutal takedown)

—Jens
___
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

[Warren Young]

On May 6, 2019, at 11:58 AM, Jose Isaias Cabrera  wrote:
> 
> we have discover DNA; shouldn't we have the knowledge to come up with a 
> dating system that should work for the world. :-)

The Earth year doesn’t divide evenly by Earth days.  No matter what you do, the 
solution *will* be messy.

Even the concept “Earth year” is variable:

   
https://en.wikipedia.org/wiki/Year#Variation_in_the_length_of_the_year_and_the_day

Ideas for fixing this aren’t new.  Start here and follow the links and 
references from there:

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

When you get bored with that, start here and repeat:

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

> someday, as John Lennon sang, "...the world will live as one." ;-)

Okay, but one *what*?  Serious question.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: FILTER on aggregate functions

[Igor Tandetnik]

On 5/6/2019 5:19 PM, Shawn Wagner wrote:

I just found out that postgres (And possibly others?) supports FILTER on
aggregate functions in general, not just when they're used as a window
function.

Trivial example:

 SELECT count(*), count(*) FILTER (WHERE amount > 100) FROM blah

which is a lot cleaner than

 SELECT count(*), sum(CASE WHEN amount > 100 THEN 1 ELSE 0 END) FROM blah


sum(amount > 100)   is sufficient.
--
Igor Tandetnik


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

Re: [sqlite] SEE encryption password

[Jens Alfke]

> On May 6, 2019, at 2:56 PM, Scott Doctor  wrote:
> 
> Here is a suggestion. You can select between decimal and hexadecimal output.
> 
> https://nousrandom.net/randominteger/index.html 
> 

Random data generated using some unspecified algorithm, by a website belonging 
to someone I don’t know, which may or may not be storing copies of those 
numbers, really doesn’t seem secure to me! I can’t think of any reasonable use 
for this API when every platform already has a secure RNG built in.

—Jens
___
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

[Richard Damon]

On 5/6/19 1:58 PM, Jose Isaias Cabrera wrote:
> Petite Abeille, on Sunday, May 5, 2019 09:10 AM, wrote...
>>> On May 4, 2019, at 21:24, Thomas Kurz  wrote:
>> True enough, even though one could convert a 'week of year' into a 'week of 
>> month':
> [clip]
>
>> 2019-11-30|2019|11|47|5
>> 2019-12-01|2019|12|47|1  <--
>> 2019-12-02|2019|12|48|2
>> 2019-12-03|2019|12|48|2
>> 2019-12-04|2019|12|48|2
> Not that I want to continue with this subject, but something is wrong where 
> one week only has 1 day.  I know all the arguments about dates and countries 
> and, etc., but we have discover DNA; shouldn't we have the knowledge to come 
> up with a dating system that should work for the world. :-)  Yes, I know.  
> It's probably why we still have wars.  But, I am one of those that believes 
> that someday, as John Lennon sang, "...the world will live as one." ;-)
>
> Happy dating...
>
> josé
It depends a lot on how you want to define a 'week' and what you are
going to use it for. If printing a traditional calendar, a one day week
makes a lot of sense. The first week is the first row of the calendar,
the second week is the second row, and so on.

-- 
Richard Damon

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

[sqlite] INSERT OR FAIL inserts row although it violates a table constraint

[Manuel Rigger]

Hi everyone,

consider the following example:

PRAGMA foreign_keys=true;
CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1));
INSERT OR FAIL INTO t0(c0, c1) VALUES
(0, 1),
(0, 2);
SELECT * FROM t0; -- returns 0|1

I expect the INSERT to fail, since both the UNIQUE and the FOREIGN KEY
constraints are violated. However, the (0, 1) row is inserted, as the
result of the SELECT query above demonstrates. When splitting up the INSERT
into two INSERTS, no row is inserted, as expected:

PRAGMA foreign_keys=true;
CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1));
INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1);
INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 2);
SELECT * FROM t0; -- returns no row

I found this bug because a WHERE clause did not fetch the row after a
REINDEX:

PRAGMA foreign_keys=true;
CREATE TABLE t0 (c0 INT UNIQUE, c1 TEXT UNIQUE, FOREIGN KEY(c0) REFERENCES
t0(c1));
INSERT OR FAIL INTO t0(c0, c1) VALUES
(0, 1),
(0, 2);
REINDEX;;
SELECT * FROM t0; -- returns 0|1
SELECT * FROM t0 WHERE c1=1; -- returns nothing

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

Re: [sqlite] ALTER TABLE fails when renaming an INTEGER PRIMARY KEY column in a WITHOUT ROWID table

[Manuel Rigger]

Great, thanks a lot, Dan!

Best,
Manuel

On Mon, May 6, 2019 at 6:18 PM Dan Kennedy  wrote:

>
> On 6/5/62 16:42, Manuel Rigger wrote:
> > Hi everyone,
> >
> > the following example fails with an error "no such column: c0":
> >
> > CREATE TABLE t0 (c0 INTEGER, PRIMARY KEY (c0)) WITHOUT ROWID;
> > ALTER TABLE t0 RENAME COLUMN c0 TO c1;
>
> Thanks again for the bug reports. This one is now fixed here:
>
> https://sqlite.org/src/info/91f701d39852ef1ddb29
>
> Dan.
>
>
>
> >
> > However, specifying c0 as the PRIMARY KEY in the column definition rather
> > than in a table constraint seems to work:
> >
> > CREATE TABLE t0 (c0 INTEGER PRIMARY KEY) WITHOUT ROWID;
> > ALTER TABLE t0 RENAME COLUMN c0 TO c1;
> >
> > Best,
> > Manuel
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SEE encryption password

[Scott Doctor]

Here is a suggestion. You can select between decimal and 
hexadecimal output.



https://nousrandom.net/randominteger/index.html


-
Scott Doctor
sc...@scottdoctor.com
-

On 5/6/2019 13:20, Jens Alfke wrote:



On May 5, 2019, at 11:04 PM, Birajendu Sahu  wrote:

I would like to generate a device specific key upon installation of the
application. Same time I don't want to save the key in device memory too.

For AES256, just generate 32 bytes (256 bits) of securely random data and use 
that as the key. (“Securely random” means do not use `rand` or `random`, rather 
a random number generator provided by the OS or a crypto library. This varies 
by platform. On macOS see SecRandom.h.)

(Don’t try to do anything fancy to generate the key. There is nothing you can 
do that will give you a more-random, less-guessable key than your OS’s secure 
RNG.)

The key should be stored in OS-provided secure storage. On macOS that’s the 
Keychain. I’m sure Windows and Android have some equivalent of that.

—Jens
___
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

[sqlite] Feature request: FILTER on aggregate functions

[Shawn Wagner]

I just found out that postgres (And possibly others?) supports FILTER on
aggregate functions in general, not just when they're used as a window
function.

Trivial example:

SELECT count(*), count(*) FILTER (WHERE amount > 100) FROM blah

which is a lot cleaner than

SELECT count(*), sum(CASE WHEN amount > 100 THEN 1 ELSE 0 END) FROM blah

Would be nice to have someday...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SEE encryption password

[Jens Alfke]

> On May 5, 2019, at 11:04 PM, Birajendu Sahu  wrote:
> 
> I would like to generate a device specific key upon installation of the
> application. Same time I don't want to save the key in device memory too.

For AES256, just generate 32 bytes (256 bits) of securely random data and use 
that as the key. (“Securely random” means do not use `rand` or `random`, rather 
a random number generator provided by the OS or a crypto library. This varies 
by platform. On macOS see SecRandom.h.)

(Don’t try to do anything fancy to generate the key. There is nothing you can 
do that will give you a more-random, less-guessable key than your OS’s secure 
RNG.)

The key should be stored in OS-provided secure storage. On macOS that’s the 
Keychain. I’m sure Windows and Android have some equivalent of that.

—Jens
___
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

[Jose Isaias Cabrera]

Yes. :-), per month.

From: sqlite-users  on behalf of 
Petite Abeille 
Sent: Monday, May 6, 2019 03:57 PM
To: SQLite mailing list
Subject: Re: [sqlite] Getting the week of the month from strftime or date 
functions



> 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
___
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

[Jose Isaias Cabrera]

Petite Abeille, on Sunday, May 5, 2019 09:10 AM, wrote...
>> On May 4, 2019, at 21:24, Thomas Kurz  wrote:

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

[clip]

> 2019-11-30|2019|11|47|5
> 2019-12-01|2019|12|47|1  <--
> 2019-12-02|2019|12|48|2
> 2019-12-03|2019|12|48|2
> 2019-12-04|2019|12|48|2

Not that I want to continue with this subject, but something is wrong where one 
week only has 1 day.  I know all the arguments about dates and countries and, 
etc., but we have discover DNA; shouldn't we have the knowledge to come up with 
a dating system that should work for the world. :-)  Yes, I know.  It's 
probably why we still have wars.  But, I am one of those that believes that 
someday, as John Lennon sang, "...the world will live as one." ;-)

Happy dating...

josé


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

Re: [sqlite] ALTER TABLE fails when renaming an INTEGER PRIMARY KEY column in a WITHOUT ROWID table

[Dan Kennedy]

On 6/5/62 16:42, Manuel Rigger wrote:

Hi everyone,

the following example fails with an error "no such column: c0":

CREATE TABLE t0 (c0 INTEGER, PRIMARY KEY (c0)) WITHOUT ROWID;
ALTER TABLE t0 RENAME COLUMN c0 TO c1;


Thanks again for the bug reports. This one is now fixed here:

https://sqlite.org/src/info/91f701d39852ef1ddb29

Dan.





However, specifying c0 as the PRIMARY KEY in the column definition rather
than in a table constraint seems to work:

CREATE TABLE t0 (c0 INTEGER PRIMARY KEY) WITHOUT ROWID;
ALTER TABLE t0 RENAME COLUMN c0 TO c1;

Best,
Manuel
___
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] [EXTERNAL] If two orders are both the same order?

[Hick Gunter]

Your schema implies that there can be more than one TIME for any GNAME and AN 
combination (otherwise the primary key would not need to include alle three 
fields). This contradicts your statement that AN and TIME are "the same order". 
(consider the tuples ("T1",1,1) and ("T2",1,2); the AN field compares equal, so 
ORDER BY AN is free to return the T2 row before the T1 row).

Which query specifically would you have in mind that relies on your assertion?

Also, if your application requires that rows be returned in a specifc order, 
your MUST specify this with en ORDER BY clause and not rely on the visitation 
order. The visitation order may change due to a number of factors including the 
SQLite version, the "shape" of your data, running ANALYZE and maybe more.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von sql...@zzo38computer.org
Gesendet: Sonntag, 05. Mai 2019 21:56
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] If two orders are both the same order?

I have a schema with the following definition:
  CREATE TABLE "XPOST"("GNAME" TEXT, "AN" INT, "TIME" INT, PRIMARY KEY 
("GNAME", "AN", "TIME")) WITHOUT ROWID;

However, the order by "AN" and the order by "TIME" will be the same order.
(I also have a table "ART" where "AN" is the rowid, and again the order by 
"TIME" will be the same order.)

How can you make SQLite to make that assumption in order to optimize the query?
(It should be done presumably without adding another index, since the data is 
already in the correct order.)

(This is my "sqlnetnews" NNTP server software, which is public domain open 
source. I don't know if maybe you might want to use NNTP for your mailing 
lists?) ___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Opening a DataBase file with a custom "user data" parameter

[Esenthel]

That solution is not perfect, because the full URI (including cipher) would
get passed to xAccess (into const char *zPath parameter) which is called
very frequently, and I would have to parse it (to decode the actual file
name without the cipher parameter) in each call, which would reduce
performance.

On Wed, 17 Apr 2019 at 17:36, Richard Hipp  wrote:

> On 4/16/19, Esenthel  wrote:
> >
> > I never got any reply for this
>
> You have to sign up for the mailing list in order to get email
> replies.  If you do not sign up, you have to poll for responses on one
> of the archives, such as
>
> https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/thrd5.html#114065
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SEE encryption password

[Birajendu Sahu]

Hi Folks,

I am implementing SEE for my multi platform application which supports
macOS, Windows and Android. I would like to understand what the best way to
provide a strong password for SEE.

I would like to generate a device specific key upon installation of the
application. Same time I don't want to save the key in device memory too.

Please suggest!

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

[sqlite] If two orders are both the same order?

[sqlite]

I have a schema with the following definition:
  CREATE TABLE "XPOST"("GNAME" TEXT, "AN" INT, "TIME" INT, PRIMARY KEY 
("GNAME", "AN", "TIME")) WITHOUT ROWID;

However, the order by "AN" and the order by "TIME" will be the same order.
(I also have a table "ART" where "AN" is the rowid, and again the order by 
"TIME" will be the same order.)

How can you make SQLite to make that assumption in order to optimize the query?
(It should be done presumably without adding another index, since the data is 
already in the correct order.)

(This is my "sqlnetnews" NNTP server software, which is public domain open 
source. I don't know if maybe you might want to use NNTP for your mailing 
lists?)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ALTER TABLE fails when renaming an INTEGER PRIMARY KEY column in a WITHOUT ROWID table

[Manuel Rigger]

Hi everyone,

the following example fails with an error "no such column: c0":

CREATE TABLE t0 (c0 INTEGER, PRIMARY KEY (c0)) WITHOUT ROWID;
ALTER TABLE t0 RENAME COLUMN c0 TO c1;

However, specifying c0 as the PRIMARY KEY in the column definition rather
than in a table constraint seems to work:

CREATE TABLE t0 (c0 INTEGER PRIMARY KEY) WITHOUT ROWID;
ALTER TABLE t0 RENAME COLUMN c0 TO c1;

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