Re: [sqlite] [EXTERNAL] Preupdate hook column name

[Hick Gunter]

See pragma table_info;

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von João Ramos
Gesendet: Donnerstag, 12. April 2018 20:54
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Preupdate hook column name

Hi,

I've successfully added support for the sqlite3_preupdate_hook(), but I also 
need to get the column name when calling either
sqlite3_preupdate_old() or sqlite3_preupdate_new().
I know that I can obtain the type of the value of the column at index i by 
calling sqlite3_value_type() but I also need the name of the column and I can't 
find a way to do this.

Is this even possible?

Thank you,

--
*João Ramos*
___
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] [EXTERNAL] Does mmap increase PSS?

[Nick]

OK, I understand.

I ran a simple program to test if mmap will cause the increasing of PSS. But
I did not find the PSS increase according to showmap: 
addr = (char *)mmap(NULL, length, PROT_READ, MAP_SHARED, fd, 0);
for(i=0; i

Re: [sqlite] memory leak?

[king3306]

first thanks you reply 

After running for some time  i found sqlite3_memory_used return value keep
constant about 2M,but linux free memory is fewer and fewer,i make sure no
other places are leaked,why?

this whether or not a normal behavior?

if not ,how can i to analysis this problem?  

Looking forward to your answer



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory leak?

[king3306]

my platform is armv5 not support valgrind and lint,

can you help me ?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory leak?

[king3306]

thans your reply , when find the problem,i try to use valgrind tool,but i
find this tools is not support armv5,i use mcu is AT91SAM9G25



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory leak?

[Tim Streater]

On 12 Apr 2018, at 21:34, Simon Slavin  wrote:

> On 12 Apr 2018, at 8:00pm, Warren Young  wrote:
>
>> Also: http://valgrind.org/docs/manual/faq.html#faq.pronounce
>
> I didn't know that.  So they want it to be pronounced like 'lint'.
> Interesting.

That they want it pronounced that way is a matter of indifference to me.


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

Re: [sqlite] memory leak?

[Simon Slavin]

On 12 Apr 2018, at 8:00pm, Warren Young  wrote:

> Also: http://valgrind.org/docs/manual/faq.html#faq.pronounce

I didn't know that.  So they want it to be pronounced like 'lint'.  Interesting.

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

Re: [sqlite] To use or not to use single quotes with integers

[Simon Slavin]

On 12 Apr 2018, at 7:49pm, Thomas Kurz  wrote:

> [Simon Slavin wrote]
> 
>> A similar thing happens when you specify that a column has affinity of REAL. 
>>  In both cases, SQLite considers that the CREATE command knows better than 
>> whatever specifies the value, and does the conversion.  However for the 
>> number to be stored the conversion has to be reversible.  If SQLite reverses 
>> the conversion and doesn't get the original string back it stores the string 
>> instead.
> 
> I don't think this is actually the case:
> 
> create table test (i integer, r real, s text);
> insert into test (i, r, s) values (3.141592653589793238462643, 
> 3.141592653589793238462643, 3.141592653589793238462643);
> insert into test (i, r, s) values ('3.141592653589793238462643', 
> '3.141592653589793238462643', '3.141592653589793238462643');
> select * from test;
> 
> In any case except of actually storing the string to column s, I get the 
> stored value truncated to 14 decimals.

From the above data (nice test dataset, by the way):

sqlite> SELECT i,typeof(i) FROM test;
3.14159265358979|real
3.14159265358979|real
sqlite> SELECT r,typeof(r) FROM test;
3.14159265358979|real
3.14159265358979|real
sqlite> SELECT s,typeof(s) FROM test;
3.14159265358979|text
3.141592653589793238462643|text

Where you specified that you wanted a string stored and provided a string in 
the INSERT command, SQLite stored a string.  In all other cases SQLite had to 
handle the value as a number at some point in the storage process, and it 
turned it into a floating point value as long as its floating point library can 
handle.  Since it would have to reduce the precision in this way before doing 
any mathematical operation on the resulting value, this counts as 'identical 
and reversible'.

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

Re: [sqlite] To use or not to use single quotes with integers

[David Raymond]

http://www.sqlite.org/datatype3.html#type_affinity

The key sentence in that section is
"SQLite considers the conversion to be lossless and reversible if the first 15 
significant decimal digits of the number are preserved"


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Thomas Kurz
Sent: Thursday, April 12, 2018 2:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] To use or not to use single quotes with integers

Dear Simon,

> A similar thing happens when you specify that a column has affinity of REAL.  
> In both cases, SQLite considers that the CREATE command knows better than 
> whatever specifies the value, and does the conversion.  However for the 
> number to be stored the conversion has to be reversible.  If SQLite reverses 
> the conversion and doesn't get the original string back it stores the string 
> instead.

I don't think this is actually the case:

create table test (i integer, r real, s text);
insert into test (i, r, s) values (3.141592653589793238462643, 
3.141592653589793238462643, 3.141592653589793238462643);
insert into test (i, r, s) values ('3.141592653589793238462643', 
'3.141592653589793238462643', '3.141592653589793238462643');
select * from test;

In any case except of actually storing the string to column s, I get the stored 
value truncated to 14 decimals.

select cast(s as text) from test;

Even here, the first value is truncated to 14 decimals. Only the 2nd is correct.

Kind regards,
Thomas

___
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] SELECT with CASE

[Csányi Pál]

2018-04-12 21:09 GMT+02:00 Csányi Pál :
> Thank you very much for the help and for the explanations.
>
> Waw! It is so complicated at first! I hope I shall understand these soon.
>
> Finally I decide to use this query:
> SELECT Keltezes FROM Orak WHERE Keltezes >= date('now','localtime')
> ORDER BY Keltezes LIMIT 1;
>
> At last this works on my Gentoo linux system here, on my laptop.
> I used datefudge to give a fake date to the sqlite3.
> When I run the above mentioned query without 'localtime', it gives the
> right date!
> But, when I run the same query with 'localtime', then I get the localtime.
>
> Because I think an Android operating system also uses localtime, I am
> going to try out this query now.

Well, it not works on my phone, altough I apply the 'localtime' in the query.
When I start the app it shows the TOMORROW date out there.
Can I ask you here what could be the solution?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Csányi Pál]

Thank you very much for the help and for the explanations.

Waw! It is so complicated at first! I hope I shall understand these soon.

Finally I decide to use this query:
SELECT Keltezes FROM Orak WHERE Keltezes >= date('now','localtime')
ORDER BY Keltezes LIMIT 1;

At last this works on my Gentoo linux system here, on my laptop.
I used datefudge to give a fake date to the sqlite3.
When I run the above mentioned query without 'localtime', it gives the
right date!
But, when I run the same query with 'localtime', then I get the localtime.

Because I think an Android operating system also uses localtime, I am
going to try out this query now.

2018-04-12 18:48 GMT+02:00 Keith Medcalf :
>
> The Query Planner should decide in the case of the MIN containing query that 
> the best solution is to traverse TheDate in order and return the first hit.  
> This may entail the creation of the necessary index if it does not exist and 
> so the two plans should be more or less identical.
>
> However, if used in a subquery, the inclusion of the LIMIT may preclude 
> flattening whereas the MIN function version will not preclude flattening.  
> Since the most likely alternative to flattening is a co-routine it probably 
> would not make much of a difference.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>>Sent: Thursday, 12 April, 2018 10:24
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>On 12 Apr 2018, at 5:16pm, R Smith  wrote:
>>
>>> SELECT MIN(TheDate) -- get the smallest date
>>>   FROM Orak -- from the table with School-days
>>>  WHERE TheDate >= date('now')   -- where the school-day is later or
>>equal to today.
>>> ;
>>
>>This reflects exactly the right structure for the data.  The
>>following may be a little faster:
>>
>>  SELECT TheDate
>>FROM Orak
>>   WHERE TheDate >= date('now')
>>ORDER BY TheDate
>>   LIMIT 1
>>
>>Both the above queries will perform far faster if there is an index
>>on the "TheDate" column in Orak.  I'm not quite sure how your data is
>>organised but this may perform another job too if it is a UNIQUE
>>index.
>>
>>Simon.
>>___
>>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] memory leak?

[Warren Young]

On Apr 12, 2018, at 11:14 AM, Jens Alfke  wrote:
> 
> I'm a Mac/iOS developer so I use the 'leaks' tool and Instruments app; I 
> don't know how this is done on Linux.

Valgrind: http://valgrind.org/

Also: http://valgrind.org/docs/manual/faq.html#faq.pronounce

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

[sqlite] Preupdate hook column name

[João Ramos]

Hi,

I've successfully added support for the sqlite3_preupdate_hook(), but I
also need to get the column name when calling either
sqlite3_preupdate_old() or sqlite3_preupdate_new().
I know that I can obtain the type of the value of the column at index i by
calling sqlite3_value_type() but I also need the name of the column and I
can't find a way to do this.

Is this even possible?

Thank you,

-- 
*João Ramos*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] To use or not to use single quotes with integers

[Thomas Kurz]

Dear Simon,

> A similar thing happens when you specify that a column has affinity of REAL.  
> In both cases, SQLite considers that the CREATE command knows better than 
> whatever specifies the value, and does the conversion.  However for the 
> number to be stored the conversion has to be reversible.  If SQLite reverses 
> the conversion and doesn't get the original string back it stores the string 
> instead.

I don't think this is actually the case:

create table test (i integer, r real, s text);
insert into test (i, r, s) values (3.141592653589793238462643, 
3.141592653589793238462643, 3.141592653589793238462643);
insert into test (i, r, s) values ('3.141592653589793238462643', 
'3.141592653589793238462643', '3.141592653589793238462643');
select * from test;

In any case except of actually storing the string to column s, I get the stored 
value truncated to 14 decimals.

select cast(s as text) from test;

Even here, the first value is truncated to 14 decimals. Only the 2nd is correct.

Kind regards,
Thomas

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

Re: [sqlite] Documentation Typo : https://sqlite.org/optoverview.html section 15

[Richard Hipp]

Fixed now.  Tnx.

On 4/12/18, Keith Medcalf  wrote:
>
> https://sqlite.org/optoverview.html
>
> 15, the push down optimization.  The BETWEEN clause is in error?
>
> SELECT x, y, b
>   FROM t2
>   JOIN (SELECT DISTINCT a, b FROM t1 WHERE b BETWEEN 10 AND 20)
>  WHERE b BETWEEN 10 AND 10;
>
> should probably be BETWEEN 10 AND 20;
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a
> lot about anticipated traffic volume.
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] memory leak?

[Jens Alfke]

Trying to find memory leaks by looking at the OS memory statistics is 
pointless. It's like looking at satellite photos to find a dropped contact 
lens. :) The kernel's virtual memory system (on any modern OS) is very complex, 
and the behavior of malloc/free in a process is also complex.

If you want to look for memory leaks, the best way is to use 
(platform-specific) instrumentation tools for that purpose. I'm a Mac/iOS 
developer so I use the 'leaks' tool and Instruments app; I don't know how this 
is done on Linux.

You can also look at the process's overall heap size (again, the name of this 
varies by platform, it's called RPRVT on Mac OS) and see if it's growing 
monotonically over time. But this is only a rough guide, as malloc/free don't 
always free up address space when heap blocks are freed.

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

[sqlite] Documentation Typo : https://sqlite.org/optoverview.html section 15

[Keith Medcalf]

https://sqlite.org/optoverview.html

15, the push down optimization.  The BETWEEN clause is in error?

SELECT x, y, b
  FROM t2
  JOIN (SELECT DISTINCT a, b FROM t1 WHERE b BETWEEN 10 AND 20)
 WHERE b BETWEEN 10 AND 10;

should probably be BETWEEN 10 AND 20;

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.





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

Re: [sqlite] SELECT with CASE

[Keith Medcalf]

The Query Planner should decide in the case of the MIN containing query that 
the best solution is to traverse TheDate in order and return the first hit.  
This may entail the creation of the necessary index if it does not exist and so 
the two plans should be more or less identical.

However, if used in a subquery, the inclusion of the LIMIT may preclude 
flattening whereas the MIN function version will not preclude flattening.  
Since the most likely alternative to flattening is a co-routine it probably 
would not make much of a difference.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Thursday, 12 April, 2018 10:24
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>On 12 Apr 2018, at 5:16pm, R Smith  wrote:
>
>> SELECT MIN(TheDate) -- get the smallest date
>>   FROM Orak -- from the table with School-days
>>  WHERE TheDate >= date('now')   -- where the school-day is later or
>equal to today.
>> ;
>
>This reflects exactly the right structure for the data.  The
>following may be a little faster:
>
>  SELECT TheDate
>FROM Orak
>   WHERE TheDate >= date('now')
>ORDER BY TheDate
>   LIMIT 1
>
>Both the above queries will perform far faster if there is an index
>on the "TheDate" column in Orak.  I'm not quite sure how your data is
>organised but this may perform another job too if it is a UNIQUE
>index.
>
>Simon.
>___
>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] SELECT with CASE

[Simon Slavin]

On 12 Apr 2018, at 5:16pm, R Smith  wrote:

> SELECT MIN(TheDate) -- get the smallest date
>   FROM Orak -- from the table with School-days
>  WHERE TheDate >= date('now')   -- where the school-day is later or equal to 
> today.
> ;

This reflects exactly the right structure for the data.  The following may be a 
little faster:

  SELECT TheDate
FROM Orak
   WHERE TheDate >= date('now')
ORDER BY TheDate
   LIMIT 1

Both the above queries will perform far faster if there is an index on the 
"TheDate" column in Orak.  I'm not quite sure how your data is organised but 
this may perform another job too if it is a UNIQUE index.

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

Re: [sqlite] SELECT with CASE

[R Smith]

You're right.
I am developing an Android app on App Inventor2.
The app is in Hungarian language so the SQLite database contains
tables and columns with Hungarian names.

The whole schema is like this:
CREATE TABLE Beiratkozottak(
   az INTEGER PRIMARY KEY UNIQUE,
   TanuloNeve TEXT NOT NULL,
   EvFolyam INTEGER NOT NULL,
   Tagozat TEXT NOT NULL
);
CREATE TABLE Hianyzok(
   az INTEGER PRIMARY KEY,
   HianyzoTanulo TEXT NOT NULL,
   Orak INTEGER REFERENCES Orak(az) NOT NULL
);
CREATE TABLE JelenLevok(
   az INTEGER PRIMARY KEY UNIQUE,
   JelenLevoTanulo TEXT NOT NULL,
   Orak INTEGER REFERENCES Orak(az) NOT NULL
);
CREATE TABLE Orak(
   az INTEGER PRIMARY KEY UNIQUE,
   Keltezes DATE DEFAULT NULL,
   OrarendNapja TEXT DEFAULT NULL,
   BlokkoraSzama TEXT DEFAULT NULL,
   EvFolyam INTEGER DEFAULT NULL,
   Tagozat TEXT DEFAULT NULL,
   OraVazlat TEXT DEFAULT NULL,
   OraNaplo TEXT DEFAULT NULL,
   Emlekezteto TEXT DEFAULT NULL,
   OraRend INTEGER REFERENCES OraRend(az) DEFAULT NULL,
   Beiratkozottak INTEGER REFERENCES Beiratkozottak(az) DEFAULT NULL,
   TanSzombatokOraRendjei INTEGER REFERENCES TanSzombatokOraRendjei(az)
DEFAULT NULL,
   KivetelesNapokOraRendjei INTEGER REFERENCES
KivetelesNapokOraRendjei(az) DEFAULT NULL
);
CREATE TABLE OraRend(
   az INTEGER PRIMARY KEY UNIQUE,
   aHetNapja TEXT,
   HanyadikIskolaOra INTEGER,
   EvFolyam INTEGER,
   Tagozat TEXT
);
CREATE TABLE TanSzombatokOraRendjei(
   az INTEGER PRIMARY KEY UNIQUE,
   Keltezes DATE DEFAULT NULL,
   aHetNapja TEXT
);
CREATE TABLE KivetelesNapokOraRendjei(
   az INTEGER PRIMARY KEY UNIQUE,
   Keltezes DATE DEFAULT NULL,
   aHetNapja TEXT
);

There are datas already in this database.
The Orak table contains dates on which we have school, that is teaching.
That mean not every day in the year is a school day.
The OraRend table contains the timetable.

So when I start the android app on my phone it should display the
school day at that day, or, if on that day we have not a schoolday,
then to display the next school day from the Orak table.


Ah, good we checked.

What guarantee is there then that "tomorrow" will be a valid school day 
if today isn't?  What if today is Saturday, and we don't have school on 
Saturday, then tomorrow is Sunday, which also might not be a school day?


To solve this puzzle, let's start by stating the problem in English:
"Give me the date for TODAY if TODAY is a valid school day, else give me 
the date for the next possible valid school day."


Now let's translate that in terms of tables and records in a database:
"Give me the date for TODAY if any record exists in my table of 
school-days for TODAY, else give me the first date (i.e. smallest date) 
from a record that exists in the schooldays table AFTER (i.e. 
later-than) today.


now let's say that in SQL:

SELECT MIN(TheDate)             -- get the smallest date
  FROM Orak                     -- from the table with School-days
 WHERE TheDate >= date('now')   -- where the school-day is later or 
equal to today.

;

That should show the real date you need, in all cases, but assuming the 
table has more available school days than today. (If nothing more 
exists, nothing more can be shown).


Good luck!
Ryan

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

Re: [sqlite] SELECT with CASE

[David Raymond]

The CASE method you were using was going through the Dates table and returning 
1 result . Since you're looking for only 1 return row 
from an entire table, then you might want something different.

select case exists (select 1 from Dates where TheDate = date('now'))
  when 1 then date('now')
  else date('now', '+1 day')
  end as TheDate;

Or if you need the next day which isn't in the table:

with recursive foo (tempDate) as (
values (date('now'))
union all
select date(tempDate, '+1 day') from foo
where exists (select 1 from Dates where TheDate = tempDate)
)
select max(tempDate) as TheDate from foo;



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Csányi Pál
Sent: Thursday, April 12, 2018 11:36 AM
To: SQLite mailing list
Subject: Re: [sqlite] SELECT with CASE

Thank you very much!

Just can't understand why the CASE method does not work?
It can't be done with the CASE expression at all?

2018-04-12 17:26 GMT+02:00 Keith Medcalf :
>
> Then Richard is correct (of course) ... which is a perfect translation of the 
> problem statement into SQL.
>
> SELECT COALESCE((SELECT thedate FROM dates WHERE thedate==date('now')), 
> date('now','+1 day')) as TheDate;
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>Sent: Thursday, 12 April, 2018 09:20
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>Yes, this is what I am asking.
>>
>>2018-04-12 17:17 GMT+02:00 Keith Medcalf :
>>>
>>> The question you asked was:
>>>
>>> "Then how can I get only that date from the Dates table - which is
>>> equal to the current date?"
>>>
>>> and you are now posing a second question:
>>>
>>> ">Yes, but I want the CASE because if there is no such date in the
Dates
table which is equal to the date('now') then it should return the
date('now','+1 day')."
>>>
>>> Which seems like a rather long winded way of stating the problem:
>>>
>>> "I have a table with a bunch-o-dates in it.  I want a query which
>>will return, at the time the query is run, based on the comuter on
>>which the query is run concept of today's date, today's date, if that
>>date is in the table otherwise the tomorrow's date (based on the
>>current concept of 'tomorrow' on the computer on which the query is
>>run."
>>>
>>> Is this what you are asking?
>>>
>>> ---
>>> The fact that there's a Highway to Hell but only a Stairway to
>>Heaven says a lot about anticipated traffic volume.
>>>
>>>
-Original Message-
From: sqlite-users [mailto:sqlite-users-
boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
Sent: Thursday, 12 April, 2018 09:10
To: SQLite mailing list
Subject: Re: [sqlite] SELECT with CASE

2018-04-12 17:08 GMT+02:00 Keith Medcalf :
>
> select TheDate from Dates where TheDate == date('now');

Yes, but I want the CASE because if there is no such date in the
Dates
table which is equal to the date('now') then it should return the
date('now','+1 day').
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
___
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] SELECT with CASE

[Keith Medcalf]

Yes.  An alternate solution might be to return the "smallest" date (assuming 
that today occurs before (is less than) tomorrow -- currently the case, but who 
knows if it will always be so ...)

SELECT MIN(CASE TheDate WHEN date('now') THEN TheDate ELSE date('now','+1 day') 
END) as TheDate FROM Dates;

Of course, this requires that there be at least one row in the table and will 
return nothing if there are no rows in Dates at all.  Thus the problem 
statement becomes "Return todays date if it is in the table, if not return 
tomorrow's date, unless there are no dates in the table at all in which case 
return nothing".  This is not the case as the original problem statement.

It will also be somewhat (perhaps a lot) more inefficient, particularly if 
there are more than a trivial number of rows in your Dates table.

You can also implement COALESCE using case:

SELECT CASE WHEN (SELECT TheDate FROM Dates WHERE TheDate==date('now')) IS NOT 
NULL
THEN (SELECT TheDate FROM Dates WHERE TheDate==date('now))
ELSE date('now', '+1 day')
   END as TheDate;

However, it is not as efficient as using COALESCE, and is about two times more 
inefficient than the first example since now the Dates table has to be scanned 
twice.  It is, however, compliant with the original problem definition.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Thursday, 12 April, 2018 09:36
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>Thank you very much!
>
>Just can't understand why the CASE method does not work?
>It can't be done with the CASE expression at all?
>
>2018-04-12 17:26 GMT+02:00 Keith Medcalf :
>>
>> Then Richard is correct (of course) ... which is a perfect
>translation of the problem statement into SQL.
>>
>> SELECT COALESCE((SELECT thedate FROM dates WHERE
>thedate==date('now')), date('now','+1 day')) as TheDate;
>>
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says a lot about anticipated traffic volume.
>>
>>
>>>-Original Message-
>>>From: sqlite-users [mailto:sqlite-users-
>>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>>Sent: Thursday, 12 April, 2018 09:20
>>>To: SQLite mailing list
>>>Subject: Re: [sqlite] SELECT with CASE
>>>
>>>Yes, this is what I am asking.
>>>
>>>2018-04-12 17:17 GMT+02:00 Keith Medcalf :

 The question you asked was:

 "Then how can I get only that date from the Dates table - which
>is
 equal to the current date?"

 and you are now posing a second question:

 ">Yes, but I want the CASE because if there is no such date in
>the
>Dates
>table which is equal to the date('now') then it should return the
>date('now','+1 day')."

 Which seems like a rather long winded way of stating the problem:

 "I have a table with a bunch-o-dates in it.  I want a query which
>>>will return, at the time the query is run, based on the comuter on
>>>which the query is run concept of today's date, today's date, if
>that
>>>date is in the table otherwise the tomorrow's date (based on the
>>>current concept of 'tomorrow' on the computer on which the query is
>>>run."

 Is this what you are asking?

 ---
 The fact that there's a Highway to Hell but only a Stairway to
>>>Heaven says a lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Thursday, 12 April, 2018 09:10
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>2018-04-12 17:08 GMT+02:00 Keith Medcalf :
>>
>> select TheDate from Dates where TheDate == date('now');
>
>Yes, but I want the CASE because if there is no such date in the
>Dates
>table which is equal to the date('now') then it should return the
>date('now','+1 day').
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>___
>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] SELECT with CASE

[Peter Da Silva]

Maybe something like: SELECT MIN(thedate) FROM dates WHERE thedate >= 
date('now');

On 4/12/18, 11:05 AM, "sqlite-users on behalf of Peter Da Silva" 
 wrote:

Ah, so if there's two days in a row that aren't school days, you need to be 
able to select a day two or more days in the future.

On 4/12/18, 11:02 AM, "sqlite-users on behalf of Csányi Pál" 
 
wrote:
So when I start the android app on my phone it should display the
school day at that day, or, if on that day we have not a schoolday,
then to display the next school day from the Orak table.
___
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] SELECT with CASE

[Peter Da Silva]

Ah, so if there's two days in a row that aren't school days, you need to be 
able to select a day two or more days in the future.

On 4/12/18, 11:02 AM, "sqlite-users on behalf of Csányi Pál" 
 
wrote:
So when I start the android app on my phone it should display the
school day at that day, or, if on that day we have not a schoolday,
then to display the next school day from the Orak table.
___
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] SELECT with CASE

[R Smith]

On 2018/04/12 5:35 PM, Csányi Pál wrote:

Thank you very much!

Just can't understand why the CASE method does not work?
It can't be done with the CASE expression at all?


The CASE expression modifies a single line, the WHERE clause restricts 
the selection to the lines that qualify.


So if you decide to do it in a CASE expression, your case expression 
worked just fine.


Your sql was (expanded a little for legibility):

SELECT
CASE TheDate
  WHEN date('now') THEN TheDate
  ELSE date('now','+1 day')
END TheDate
  FROM Dates
;


Which, when translated to plain English says:

a. For every record in table"Dates", show me a value called "TheDate" 
which is decided upon as follows:
  b. When the value in column "TheDate" is equal to today's date (for 
what my current computer thinks is "today" locally - let's call this 
TODAY), then simply put THAT TheDate value,

  c. Else put tomorrow's date (let's call this TOMORROW).

Now some things you can see from this:
- Because of a. - you will always see ALL rows listed.
- In b. the selection is superfluous. The only value that can ever be 
given for TheDate is TODAY.   [If TheDate==TODAY, then show TheDate 
(i.e. TODAY)]
- in c. the selection can only ever be TOMORROW, nothing else. For you 
specify that if NOT (TheDate == TODAY) - i.e. the ELSE, then show 
TOMORROW. Always.


This means the only two possible dates that can be the result of your 
CASE statement is either TODAY, or TOMORROW. Nothing else. And indeed, 
in the output that you sent, it is clearly the case, you have lots of 
lines showing TOMORROW (obviously for the entries in your table where 
the "TheDate" column was NOT equal to TODAY), and one line showing TODAY 
(obviously for the one entry where the "TheDate" column was indeed equal 
to TODAY).


What you then later asked is that you do not wish to see any of this, 
you want to know whether there exists a date such as TODAY in the table 
at all... If so, you want one single answer showing that date (i.e. 
TODAY), else you would like the one single answer to say TOMORROW. This 
absolutely /HAS/ to be filtered out using a WHERE clause. There is no 
way CASE can limit the shown rows - it only selects based upon a value 
in the current row.


Does that answer the CASE question? If not, feel free to ask again with 
maybe examples of how you expect it to work, which will help us to know 
what misconception to assist with.


Good luck!
Ryan

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

Re: [sqlite] SELECT with CASE

[Csányi Pál]

Hi Ryan,

2018-04-12 17:36 GMT+02:00 R Smith :
> On 2018/04/12 5:20 PM, Csányi Pál wrote:
>>
>> Yes, this is what I am asking.
>>
>> 2018-04-12 17:17 GMT+02:00 Keith Medcalf :
>>>
>>> Which seems like a rather long winded way of stating the problem:
>>> "I have a table with a bunch-o-dates in it.  I want a query which will
>>> return, at the time the query is run, based on the comuter on which the
>>> query is run concept of today's date, today's date, if that date is in the
>>> table otherwise the tomorrow's date (based on the current concept of
>>> 'tomorrow' on the computer on which the query is run."
>>>
>>> Is this what you are asking?
>
>
> Ok, now that we know what you are asking, there are some ways of doing it
> easily, of which Richard's way will work perfectly.
>
> But, some more information will be useful:
> - Can there be dates later than today in the table or not?
> - Can there be multiple dates for today in the table, or just the one?
> - Do you need to run this query often, or is it used simply to determine the
> next date available for another query (insert perhaps)?
>
> I'm asking because I feel like the query you are asking for is achieving
> something as part of a larger query or group of functions that may all be
> made simpler. I could of course be wrong, but if you'd like to find out,
> post the whole schema and method you are trying to make and we could suggest
> what might work the fastest/easiest/best - or we might at least confirm that
> you are already doing it the best way.
>
> Cheers!
> Ryan

> - Can there be dates later than today in the table or not?
Yes, it can.

> - Can there be multiple dates for today in the table, or just the one?
Yes, it can.

> - Do you need to run this query often, or is it used simply to determine the
> next date available for another query (insert perhaps)?
It is used simply to determine the next date available for another query.

> I'm asking because I feel like the query you are asking for is achieving
> something as part of a larger query or group of functions that may all be
> made simpler. I could of course be wrong, but if you'd like to find out,
> post the whole schema and method you are trying to make and we could suggest
> what might work the fastest/easiest/best - or we might at least confirm that
> you are already doing it the best way.

You're right.
I am developing an Android app on App Inventor2.
The app is in Hungarian language so the SQLite database contains
tables and columns with Hungarian names.

The whole schema is like this:
CREATE TABLE Beiratkozottak(
  az INTEGER PRIMARY KEY UNIQUE,
  TanuloNeve TEXT NOT NULL,
  EvFolyam INTEGER NOT NULL,
  Tagozat TEXT NOT NULL
);
CREATE TABLE Hianyzok(
  az INTEGER PRIMARY KEY,
  HianyzoTanulo TEXT NOT NULL,
  Orak INTEGER REFERENCES Orak(az) NOT NULL
);
CREATE TABLE JelenLevok(
  az INTEGER PRIMARY KEY UNIQUE,
  JelenLevoTanulo TEXT NOT NULL,
  Orak INTEGER REFERENCES Orak(az) NOT NULL
);
CREATE TABLE Orak(
  az INTEGER PRIMARY KEY UNIQUE,
  Keltezes DATE DEFAULT NULL,
  OrarendNapja TEXT DEFAULT NULL,
  BlokkoraSzama TEXT DEFAULT NULL,
  EvFolyam INTEGER DEFAULT NULL,
  Tagozat TEXT DEFAULT NULL,
  OraVazlat TEXT DEFAULT NULL,
  OraNaplo TEXT DEFAULT NULL,
  Emlekezteto TEXT DEFAULT NULL,
  OraRend INTEGER REFERENCES OraRend(az) DEFAULT NULL,
  Beiratkozottak INTEGER REFERENCES Beiratkozottak(az) DEFAULT NULL,
  TanSzombatokOraRendjei INTEGER REFERENCES TanSzombatokOraRendjei(az)
DEFAULT NULL,
  KivetelesNapokOraRendjei INTEGER REFERENCES
KivetelesNapokOraRendjei(az) DEFAULT NULL
);
CREATE TABLE OraRend(
  az INTEGER PRIMARY KEY UNIQUE,
  aHetNapja TEXT,
  HanyadikIskolaOra INTEGER,
  EvFolyam INTEGER,
  Tagozat TEXT
);
CREATE TABLE TanSzombatokOraRendjei(
  az INTEGER PRIMARY KEY UNIQUE,
  Keltezes DATE DEFAULT NULL,
  aHetNapja TEXT
);
CREATE TABLE KivetelesNapokOraRendjei(
  az INTEGER PRIMARY KEY UNIQUE,
  Keltezes DATE DEFAULT NULL,
  aHetNapja TEXT
);

There are datas already in this database.
The Orak table contains dates on which we have school, that is teaching.
That mean not every day in the year is a school day.
The OraRend table contains the timetable.

So when I start the android app on my phone it should display the
school day at that day, or, if on that day we have not a schoolday,
then to display the next school day from the Orak table.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Keith Medcalf]

Assuming, of course, that all the dates are in UT1 (UTC/GMT/Zulu) and not the 
computers' (running the query) current concept of 'local wall clock time'.  

If your table of Dates are not UT1 then you need to add the 'localtime' 
modifier to the datetime function calls and pray that the computer has the 
correct "rules" for the translation from UT1/GMT/Zulu to your desired 
'localtime'.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
>Sent: Thursday, 12 April, 2018 09:26
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>
>Then Richard is correct (of course) ... which is a perfect
>translation of the problem statement into SQL.
>
>SELECT COALESCE((SELECT thedate FROM dates WHERE
>thedate==date('now')), date('now','+1 day')) as TheDate;
>
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>Sent: Thursday, 12 April, 2018 09:20
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>Yes, this is what I am asking.
>>
>>2018-04-12 17:17 GMT+02:00 Keith Medcalf :
>>>
>>> The question you asked was:
>>>
>>> "Then how can I get only that date from the Dates table - which is
>>> equal to the current date?"
>>>
>>> and you are now posing a second question:
>>>
>>> ">Yes, but I want the CASE because if there is no such date in the
Dates
table which is equal to the date('now') then it should return the
date('now','+1 day')."
>>>
>>> Which seems like a rather long winded way of stating the problem:
>>>
>>> "I have a table with a bunch-o-dates in it.  I want a query which
>>will return, at the time the query is run, based on the comuter on
>>which the query is run concept of today's date, today's date, if
>that
>>date is in the table otherwise the tomorrow's date (based on the
>>current concept of 'tomorrow' on the computer on which the query is
>>run."
>>>
>>> Is this what you are asking?
>>>
>>> ---
>>> The fact that there's a Highway to Hell but only a Stairway to
>>Heaven says a lot about anticipated traffic volume.
>>>
>>>
-Original Message-
From: sqlite-users [mailto:sqlite-users-
boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
Sent: Thursday, 12 April, 2018 09:10
To: SQLite mailing list
Subject: Re: [sqlite] SELECT with CASE

2018-04-12 17:08 GMT+02:00 Keith Medcalf :
>
> select TheDate from Dates where TheDate == date('now');

Yes, but I want the CASE because if there is no such date in the
Dates
table which is equal to the date('now') then it should return the
date('now','+1 day').
___
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
>
>
>
>___
>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] SELECT with CASE

[R Smith]

On 2018/04/12 5:20 PM, Csányi Pál wrote:

Yes, this is what I am asking.

2018-04-12 17:17 GMT+02:00 Keith Medcalf :

Which seems like a rather long winded way of stating the problem:
"I have a table with a bunch-o-dates in it.  I want a query which will return, at 
the time the query is run, based on the comuter on which the query is run concept of 
today's date, today's date, if that date is in the table otherwise the tomorrow's date 
(based on the current concept of 'tomorrow' on the computer on which the query is 
run."

Is this what you are asking?


Ok, now that we know what you are asking, there are some ways of doing 
it easily, of which Richard's way will work perfectly.


But, some more information will be useful:
- Can there be dates later than today in the table or not?
- Can there be multiple dates for today in the table, or just the one?
- Do you need to run this query often, or is it used simply to determine 
the next date available for another query (insert perhaps)?


I'm asking because I feel like the query you are asking for is achieving 
something as part of a larger query or group of functions that may all 
be made simpler. I could of course be wrong, but if you'd like to find 
out, post the whole schema and method you are trying to make and we 
could suggest what might work the fastest/easiest/best - or we might at 
least confirm that you are already doing it the best way.


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

Re: [sqlite] SELECT with CASE

[Csányi Pál]

Thank you very much!

Just can't understand why the CASE method does not work?
It can't be done with the CASE expression at all?

2018-04-12 17:26 GMT+02:00 Keith Medcalf :
>
> Then Richard is correct (of course) ... which is a perfect translation of the 
> problem statement into SQL.
>
> SELECT COALESCE((SELECT thedate FROM dates WHERE thedate==date('now')), 
> date('now','+1 day')) as TheDate;
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>Sent: Thursday, 12 April, 2018 09:20
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>Yes, this is what I am asking.
>>
>>2018-04-12 17:17 GMT+02:00 Keith Medcalf :
>>>
>>> The question you asked was:
>>>
>>> "Then how can I get only that date from the Dates table - which is
>>> equal to the current date?"
>>>
>>> and you are now posing a second question:
>>>
>>> ">Yes, but I want the CASE because if there is no such date in the
Dates
table which is equal to the date('now') then it should return the
date('now','+1 day')."
>>>
>>> Which seems like a rather long winded way of stating the problem:
>>>
>>> "I have a table with a bunch-o-dates in it.  I want a query which
>>will return, at the time the query is run, based on the comuter on
>>which the query is run concept of today's date, today's date, if that
>>date is in the table otherwise the tomorrow's date (based on the
>>current concept of 'tomorrow' on the computer on which the query is
>>run."
>>>
>>> Is this what you are asking?
>>>
>>> ---
>>> The fact that there's a Highway to Hell but only a Stairway to
>>Heaven says a lot about anticipated traffic volume.
>>>
>>>
-Original Message-
From: sqlite-users [mailto:sqlite-users-
boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
Sent: Thursday, 12 April, 2018 09:10
To: SQLite mailing list
Subject: Re: [sqlite] SELECT with CASE

2018-04-12 17:08 GMT+02:00 Keith Medcalf :
>
> select TheDate from Dates where TheDate == date('now');

Yes, but I want the CASE because if there is no such date in the
Dates
table which is equal to the date('now') then it should return the
date('now','+1 day').
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Keith Medcalf]

Then Richard is correct (of course) ... which is a perfect translation of the 
problem statement into SQL.

SELECT COALESCE((SELECT thedate FROM dates WHERE thedate==date('now')), 
date('now','+1 day')) as TheDate;


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Thursday, 12 April, 2018 09:20
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>Yes, this is what I am asking.
>
>2018-04-12 17:17 GMT+02:00 Keith Medcalf :
>>
>> The question you asked was:
>>
>> "Then how can I get only that date from the Dates table - which is
>> equal to the current date?"
>>
>> and you are now posing a second question:
>>
>> ">Yes, but I want the CASE because if there is no such date in the
>>>Dates
>>>table which is equal to the date('now') then it should return the
>>>date('now','+1 day')."
>>
>> Which seems like a rather long winded way of stating the problem:
>>
>> "I have a table with a bunch-o-dates in it.  I want a query which
>will return, at the time the query is run, based on the comuter on
>which the query is run concept of today's date, today's date, if that
>date is in the table otherwise the tomorrow's date (based on the
>current concept of 'tomorrow' on the computer on which the query is
>run."
>>
>> Is this what you are asking?
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says a lot about anticipated traffic volume.
>>
>>
>>>-Original Message-
>>>From: sqlite-users [mailto:sqlite-users-
>>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>>Sent: Thursday, 12 April, 2018 09:10
>>>To: SQLite mailing list
>>>Subject: Re: [sqlite] SELECT with CASE
>>>
>>>2018-04-12 17:08 GMT+02:00 Keith Medcalf :

 select TheDate from Dates where TheDate == date('now');
>>>
>>>Yes, but I want the CASE because if there is no such date in the
>>>Dates
>>>table which is equal to the date('now') then it should return the
>>>date('now','+1 day').
>>>___
>>>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



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

Re: [sqlite] SELECT with CASE

[Csányi Pál]

Yes, this is what I am asking.

2018-04-12 17:17 GMT+02:00 Keith Medcalf :
>
> The question you asked was:
>
> "Then how can I get only that date from the Dates table - which is
> equal to the current date?"
>
> and you are now posing a second question:
>
> ">Yes, but I want the CASE because if there is no such date in the
>>Dates
>>table which is equal to the date('now') then it should return the
>>date('now','+1 day')."
>
> Which seems like a rather long winded way of stating the problem:
>
> "I have a table with a bunch-o-dates in it.  I want a query which will 
> return, at the time the query is run, based on the comuter on which the query 
> is run concept of today's date, today's date, if that date is in the table 
> otherwise the tomorrow's date (based on the current concept of 'tomorrow' on 
> the computer on which the query is run."
>
> Is this what you are asking?
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>Sent: Thursday, 12 April, 2018 09:10
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>2018-04-12 17:08 GMT+02:00 Keith Medcalf :
>>>
>>> select TheDate from Dates where TheDate == date('now');
>>
>>Yes, but I want the CASE because if there is no such date in the
>>Dates
>>table which is equal to the date('now') then it should return the
>>date('now','+1 day').
>>___
>>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] SELECT with CASE

[Keith Medcalf]

The question you asked was: 

"Then how can I get only that date from the Dates table - which is
equal to the current date?"

and you are now posing a second question:

">Yes, but I want the CASE because if there is no such date in the
>Dates
>table which is equal to the date('now') then it should return the
>date('now','+1 day')."

Which seems like a rather long winded way of stating the problem:

"I have a table with a bunch-o-dates in it.  I want a query which will return, 
at the time the query is run, based on the comuter on which the query is run 
concept of today's date, today's date, if that date is in the table otherwise 
the tomorrow's date (based on the current concept of 'tomorrow' on the computer 
on which the query is run."

Is this what you are asking?

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Thursday, 12 April, 2018 09:10
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>2018-04-12 17:08 GMT+02:00 Keith Medcalf :
>>
>> select TheDate from Dates where TheDate == date('now');
>
>Yes, but I want the CASE because if there is no such date in the
>Dates
>table which is equal to the date('now') then it should return the
>date('now','+1 day').
>___
>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] SELECT with CASE

[Richard Hipp]

On 4/12/18, Csányi Pál  wrote:
> 2018-04-12 17:08 GMT+02:00 Keith Medcalf :
>>
>> select TheDate from Dates where TheDate == date('now');
>
> Yes, but I want the CASE because if there is no such date in the Dates
> table which is equal to the date('now') then it should return the
> date('now','+1 day').

... COALESCE((SELECT thedate FROM dates WHERE thedate==date('now')),
date('now','+1 day'))

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

Re: [sqlite] SELECT with CASE

[Csányi Pál]

2018-04-12 17:08 GMT+02:00 Keith Medcalf :
>
> select TheDate from Dates where TheDate == date('now');

Yes, but I want the CASE because if there is no such date in the Dates
table which is equal to the date('now') then it should return the
date('now','+1 day').
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Keith Medcalf]

select TheDate from Dates where TheDate == date('now');

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Thursday, 12 April, 2018 09:06
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>2018-04-12 17:00 GMT+02:00 Peter Da Silva
>:
>> One of the lines of the output does indeed have '2018-04-12' as
>expected.
>
>Indeed, I did not notice.
>Then how can I get only that date from the Dates table - which is
>equal to the current date?
>___
>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] SELECT with CASE

[Csányi Pál]

2018-04-12 17:00 GMT+02:00 Peter Da Silva :
> One of the lines of the output does indeed have '2018-04-12' as expected.

Indeed, I did not notice.
Then how can I get only that date from the Dates table - which is
equal to the current date?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Peter Da Silva]

One of the lines of the output does indeed have '2018-04-12' as expected.

On 4/12/18, 9:59 AM, "sqlite-users on behalf of Csányi Pál" 
 
wrote:

2018-04-12 16:51 GMT+02:00 Peter Da Silva :
> You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which 
is what you're getting, no?

Yes, indeed.
But I thought the first part would be done:
CASE TheDate WHEN date('now') THEN TheDate

that is, if the TheDate is = date('now') THEN
it should select that TheDate which is equal to the current date:
date('now'), no?
___
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] SELECT with CASE

[Csányi Pál]

2018-04-12 16:51 GMT+02:00 Peter Da Silva :
> You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which is 
> what you're getting, no?

Yes, indeed.
But I thought the first part would be done:
CASE TheDate WHEN date('now') THEN TheDate

that is, if the TheDate is = date('now') THEN
it should select that TheDate which is equal to the current date:
date('now'), no?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Peter Da Silva]

You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which is what 
you're getting, no?

On 4/12/18, 9:47 AM, "sqlite-users on behalf of Csányi Pál" 
 
wrote:

Hi Simon,

2018-04-12 14:32 GMT+02:00 Simon Slavin :
> On 12 Apr 2018, at 1:25pm, Csányi Pál  wrote:
>
>> SELECT CASE TheDate = date('now') WHEN TheDate ...
>
> I don't think that's what you wanted.  Perhaps
>
> SELECT CASE TheDate WHEN date('now') ...
>
> But you should test the output of "date('now')" to make sure it is in the 
format you want.

The output of "date('now') is in format I want:

SELECT date('now');
2018-04-12

I tried this:

SELECT CASE TheDate WHEN date('now') THEN TheDate ELSE date('now','+1
day') END TheDate FROM Dates;
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-12
2018-04-13
2018-04-13
2018-04-13

but I expect the output like this:
2018-04-12

because today date is:
2018-04-12

What am I missing here?

-- 
Best, Pali
___
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] SELECT with CASE

[Csányi Pál]

Hi Simon,

2018-04-12 14:32 GMT+02:00 Simon Slavin :
> On 12 Apr 2018, at 1:25pm, Csányi Pál  wrote:
>
>> SELECT CASE TheDate = date('now') WHEN TheDate ...
>
> I don't think that's what you wanted.  Perhaps
>
> SELECT CASE TheDate WHEN date('now') ...
>
> But you should test the output of "date('now')" to make sure it is in the 
> format you want.

The output of "date('now') is in format I want:

SELECT date('now');
2018-04-12

I tried this:

SELECT CASE TheDate WHEN date('now') THEN TheDate ELSE date('now','+1
day') END TheDate FROM Dates;
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-12
2018-04-13
2018-04-13
2018-04-13

but I expect the output like this:
2018-04-12

because today date is:
2018-04-12

What am I missing here?

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

Re: [sqlite] memory leak?

[Richard Hipp]

On 4/12/18, king3306 <1809860...@qq.com> wrote:
>
> 1:
> before i use insert cmd ,the linux memory and sqlite3_memory_used is
> linux: tota=29126656 used=16998400 free=12128256
> sqlite3_memory_used:1372576
>
> after i use inset cmd ,the linux memory and sqlite3_memory_used is
> linux: tota=29126656 used=17608704 free=11517952
> sqlite3_memory_used:1710016
>
> why 1710016-1372576=337440   !=   12128256-11517952=610304

When SQLite calls free() to release memory back to the native memory
allocation, free() does not necessarily release that memory back to
the operating system.  Usually free() will keep that memory around to
be reused for the next malloc() request.  I think you are seeing the
memory that free() has kept back for reuse.

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

Re: [sqlite] memory leak?

[king3306]

sorry i made a mistake  SQLite version 3.3.6  is linux built-in sqlite3


i use is SQLITE_VERSION"3.22.0" on arm 




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Salt endianness

[Richard Hipp]

On 4/12/18, Harmen  wrote:
>
> So I guess that's not true for the salt1 and salt2 values? They are to be
> interpreted as either bigendian?
>

The documentation has been updated.  Thanks.
-- 
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

Re: [sqlite] memory leak?

[Richard Hipp]

On 4/12/18, king3306 <1809860...@qq.com> wrote:
>
> I am use SQLite version 3.3.6

Version 3.3.6 dates from 2006-06-06.  Version 3.23.1 is current.
Please upgrade.  Report back if you are still having problems.

-- 
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] memory leak?

[king3306]

HI

I am use SQLite version 3.3.6

default page_size is 4096  , i set 1024

i have four question ,i don't understand 


1:
before i use insert cmd ,the linux memory and sqlite3_memory_used is 
linux: tota=29126656 used=16998400 free=12128256
sqlite3_memory_used:1372576

after i use inset cmd ,the linux memory and sqlite3_memory_used is 
linux: tota=29126656 used=17608704 free=11517952
sqlite3_memory_used:1710016

why 1710016-1372576=337440   !=   12128256-11517952=610304


2: 
i keep the sqlite3 run two days,i found when i use sqlite select cmd or
insert cmd  i found sqlite3_memory_used = 2315024 is not change but linux
free memory is reduce(use free cmd)

if i  comment out the select cmd and insert cmd ,linux free memory is not
change

this whether or not  a Normal behavior ?


3:
sqlite3_memory_used  return value whether or not include the memory leak?

4:
i found anther situation,when linux free memory reduce to about 1M,free
memory immediately increase to about 8M




my code:

void insert_table_data(mtype_group_str table,uint8_t class_type,char
time_str[])
{
char sql_cmd[MAX_BUFF_LEN];
char meter_addr_str[CJT188_ADDR_LEN*2+100];
int32_t result;
sqlite3_stmt *stmt=NULL;
uint8_t id_index;
uint16_t meter_index;
uint32_t di_offset=0;
printf_mem_infor("insert_table_data1");
begin_transaction();
printf_mem_infor("insert_table_data2");
make_insert_cmd(table,class_type,sql_cmd);
printf_mem_infor("insert_table_data3");
result = sqlite3_prepare_v2(sqlite_mdb.mdb,sql_cmd,-1,,NULL);
if(result != SQLITE_OK)
{
elog_d(SYS_LOG_MDB,"insert_table_data:result=%d",result);
sqlite3_finalize(stmt);
rollback_transaction();
return ;
}
printf_mem_infor("insert_table_data4");
for(meter_index = 0; meter_index < table.m_num; meter_index++)
{
result = sqlite3_bind_text(stmt,1,xxx,-1,SQLITE_STATIC);
if(result != SQLITE_OK)
{
elog_d(SYS_LOG_MDB,"insert_table_data:result1=%d",result);
}
di_offset = 0;
if(class_type == REAL_DATA)
{
result = sqlite3_bind_text(stmt,2,time_str,-1,SQLITE_STATIC);
if(result != SQLITE_OK)
{
elog_d(SYS_LOG_MDB,"insert_table_data:result2=%d",result);
}
}
else if(class_type == DAY_DATA)
{
result = sqlite3_bind_text(stmt,2,time_str,-1,SQLITE_STATIC);
if(result != SQLITE_OK)
{
elog_d(SYS_LOG_MDB,"insert_table_data:result3=%d",result);
}
}
else if(class_type == MONTH_DATA)
{
result = sqlite3_bind_text(stmt,2,time_str,-1,SQLITE_STATIC);
if(result != SQLITE_OK)
{
elog_d(SYS_LOG_MDB,"insert_table_data:result4=%d",result);
}
}
for(id_index = 0; id_index < table.mtype_infor[class_type].di_sum;
id_index++)
{
di_offset += ID_INDEX_SIZE;
result =
sqlite3_bind_blob(stmt,id_index+3,xxx,SQLITE_STATIC);
if(result != SQLITE_OK)
{
elog_d(SYS_LOG_MDB,"insert_table_data:result5=%d",result);
}
di_offset += DATA_TIME_SIZE +
table.mtype_infor[class_type].mtype_di[id_index].data_len;
}
printf_mem_infor("insert_table_data5");
result = sqlite3_step(stmt);
printf_mem_infor("insert_table_data6");
if(result != SQLITE_DONE)
{
elog_d(SYS_LOG_MDB,"result = sqlite3_step(stmt)=%d",result);
rollback_transaction();
}
printf_mem_infor("insert_table_data7");
result = sqlite3_reset(stmt);
printf_mem_infor("insert_table_data8");
if(result != SQLITE_OK)
{
elog_d(SYS_LOG_MDB,"result = sqlite3_reset(stmt)=%d",result);
}
printf_mem_infor("insert_table_data9");
result = sqlite3_clear_bindings(stmt);
printf_mem_infor("insert_table_data10");
if(result != SQLITE_OK)
{
elog_d(SYS_LOG_MDB,"result =
sqlite3_clear_bindings(stmt)=%d",result);
}
}
printf_mem_infor("insert_table_data11");
sqlite3_finalize(stmt);
printf_mem_infor("insert_table_data12");
commit_transaction();
printf_mem_infor("insert_table_data13");
}



uint8_t get_fre_infor(mtype_group_str table,uint8_t class_type,char
time_str[])
{
char sql_cmd[MAX_BUFF_LEN];
int32_t result;
char *errmsg=NULL,**dbresult=NULL;
int16_t amr_time_index = 0;
int32_t nrow = 0,ncol=0;

sprintf(sql_cmd,"select distinct amr_time from tab_%d_%d_%c order by
amr_time asc",table.big_type,table.sml_type,data_type_mark[class_type]);
result =
sqlite3_get_table(sqlite_mdb.mdb,sql_cmd);
elog_d(SYS_LOG_MDB,"get_fre_infor:ncol=%d nrow=%d",ncol,nrow);
if(result == SQLITE_OK)
{
for(amr_time_index = 0; amr_time_index < nrow; 

Re: [sqlite] sqlite 3.23.0 zipfile2 test failures

[Petr Kubat]

Actually forgot to append the link tot he full logs so one more mail to 
fix it:


https://kojipkgs.fedoraproject.org//work/tasks/7200/26137200/build.log

On 04/12/2018 02:23 PM, Petr Kubat wrote:

Hello everyone,

I have recently rebased Fedora's version of sqlite to 3.23.0 and saw 
some failures in one of the zipfile2 test cases during running 
self-tests (full logs [1]):


   Time: zipfile.test 193 ms
   ! zipfile2-2.0 expected: [1 {error in fread()}]
   ! zipfile2-2.0 got:  [0 {}]
   Time: zipfile2.test 12 ms
   SQLite 2018-04-02 11:04:16 
736b53f57f70b23172c30880186dce7ad9baa3b74e3838cae5847cffb98falt1

   1 errors out of 187562 tests on  Linux 64-bit big-endian

After looking a bit more into the failure it seems like this piece of 
code (from (ext/misc/zipfile.c:zipfileReadEOCD)) is the reason for the 
failures:


  fseek(pFile, 0, SEEK_END);
  szFile = (i64)ftell(pFile);
  if( szFile==0 ){
    memset(pEOCD, 0, sizeof(ZipfileEOCD));
    return SQLITE_OK;
  }
  nRead = (int)(MIN(szFile, ZIPFILE_BUFFER_SIZE));
  iOff = szFile - nRead;
  rc = zipfileReadData(pFile, aRead, nRead, iOff, 
>base.zErrMsg);


The issue here is that `fseek` and `ftell` are being run on a FILE 
pointer that is created from fopen-ing a directory. On some systems, 
and I think this is tied to the filesystem used (I managed to 
reproduce the failure on a VM using XFS), this leads to the 
`zipfileReadData` call being skipped due to `ftell` returning 0 
(returns LONG_MAX on ext4) and SQLITE_OK is returned instead of 
failing with error (through `zipfileReadData`).


To me it seems like calling `fseek` and `ftell` on a directory results 
in undefined behaviour so it would make more sense to explicitly check 
the type of the target before attempting it. However, I am not sure 
where such a change would be best to take place (which is why there is 
no fix attached to this).


Petr

___
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] Salt endianness

[Harmen]

Hi,

I was looking at the salt values in the -wal and -shm files, and got confused
of their endianness.

the -wal is always big endian, this is a -wal hexdump:
0007f3782062d0018e20010
010a631f202c1e0fdc7e9c644a9997cc942
0200100a631f202c1e0fdc7

That's 0xa631f202 for salt1 bytes.

and the corresponding -shm file (on a little endian machine) has:
000e218002d000200011000
010000800065a2ccbbc3055df6c
020a631f202c1e0fdc7926aeeff6afcebb3

Which is the same byte squence for salt1.

According to the docs at https://sqlite.org/walformat.html section "2.1. The
WAL-Index Header":

"Individual fields of the shm header are all unsigned integers in the
native byte-order of the host machine"

So I guess that's not true for the salt1 and salt2 values? They are to be
interpreted as either bigendian?

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

Re: [sqlite] SELECT with CASE

[Simon Slavin]

On 12 Apr 2018, at 1:25pm, Csányi Pál  wrote:

> SELECT CASE TheDate = date('now') WHEN TheDate ...

I don't think that's what you wanted.  Perhaps

SELECT CASE TheDate WHEN date('now') ...

But you should test the output of "date('now')" to make sure it is in the 
format you want.

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

[sqlite] SELECT with CASE

[Csányi Pál]

Hi,

I have a small database:
DatesOfYear.db

with only one table:

CREATE TABLE Dates(id integer PRIMARY KEY UNIQUE, TheDate date NOT NULL);

I insert into the table some datas with:

INSERT INTO Dates VALUES(1,'2018-04-01');
INSERT INTO Dates VALUES(2,'2018-04-02');
INSERT INTO Dates VALUES(3,'2018-04-03');
INSERT INTO Dates VALUES(4,'2018-04-04');
INSERT INTO Dates VALUES(5,'2018-04-05');
INSERT INTO Dates VALUES(6,'2018-04-06');
INSERT INTO Dates VALUES(7,'2018-04-07');
INSERT INTO Dates VALUES(8,'2018-04-08');
INSERT INTO Dates VALUES(9,'2018-04-09');
INSERT INTO Dates VALUES(10,'2018-04-10');
INSERT INTO Dates VALUES(11,'2018-04-11');
INSERT INTO Dates VALUES(12,'2018-04-12');
INSERT INTO Dates VALUES(13,'2018-04-13');
INSERT INTO Dates VALUES(14,'2018-04-14');
INSERT INTO Dates VALUES(15,'2018-04-15');

I want the following by using SELECT and CASE:

to select the date:
 if TheDate is = with the current date then select that TheDate
 else to select only once the next date  from Dates table.

I tried with this query:
SELECT CASE TheDate = date('now') WHEN TheDate THEN TheDate ELSE
date('now','+1 day') END TheDate FROM Dates;

and the output is this:
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13

but I expect - because to day is 2018-04-12 this:
2018-04-12

What am I doing wrong?
NOTE! The dates are in Hungarian.

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

[sqlite] sqlite 3.23.0 zipfile2 test failures

[Petr Kubat]

Hello everyone,

I have recently rebased Fedora's version of sqlite to 3.23.0 and saw 
some failures in one of the zipfile2 test cases during running 
self-tests (full logs [1]):


   Time: zipfile.test 193 ms
   ! zipfile2-2.0 expected: [1 {error in fread()}]
   ! zipfile2-2.0 got:  [0 {}]
   Time: zipfile2.test 12 ms
   SQLite 2018-04-02 11:04:16 
736b53f57f70b23172c30880186dce7ad9baa3b74e3838cae5847cffb98falt1
   1 errors out of 187562 tests on  Linux 64-bit big-endian

After looking a bit more into the failure it seems like this piece of 
code (from (ext/misc/zipfile.c:zipfileReadEOCD)) is the reason for the 
failures:


  fseek(pFile, 0, SEEK_END);
  szFile = (i64)ftell(pFile);
  if( szFile==0 ){
    memset(pEOCD, 0, sizeof(ZipfileEOCD));
    return SQLITE_OK;
  }
  nRead = (int)(MIN(szFile, ZIPFILE_BUFFER_SIZE));
  iOff = szFile - nRead;
  rc = zipfileReadData(pFile, aRead, nRead, iOff, >base.zErrMsg);

The issue here is that `fseek` and `ftell` are being run on a FILE 
pointer that is created from fopen-ing a directory. On some systems, and 
I think this is tied to the filesystem used (I managed to reproduce the 
failure on a VM using XFS), this leads to the `zipfileReadData` call 
being skipped due to `ftell` returning 0 (returns LONG_MAX on ext4) and 
SQLITE_OK is returned instead of failing with error (through 
`zipfileReadData`).


To me it seems like calling `fseek` and `ftell` on a directory results 
in undefined behaviour so it would make more sense to explicitly check 
the type of the target before attempting it. However, I am not sure 
where such a change would be best to take place (which is why there is 
no fix attached to this).


Petr

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

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

[Hick Gunter]

Not necessarily. Only if you run queries that access more than 256M of the db 
file AND there is no contention for memory resources.

Why is the PSS of your process such a concern?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nick
Gesendet: Donnerstag, 12. April 2018 11:24
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

Thanks a lot, Hick.
So, if
- mmap_size=256M
- run only one copy of my program (has no other process to split PSS)
- have a large enough amount of main memory (bigger than 256M)
- a big db file (bigger than 256M)
Then the PSS of my program will be about 256M.

Is that correct?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
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] [EXTERNAL] Does mmap increase PSS?

[Nick]

Thanks a lot, Hick.
So, if 
- mmap_size=256M
- run only one copy of my program (has no other process to split PSS)
- have a large enough amount of main memory (bigger than 256M)
- a big db file (bigger than 256M)
Then the PSS of my program will be about 256M. 

Is that correct?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

[Hick Gunter]

No.

You are confusing several issues here.

There is "process size", which is the number of byte addresses that are 
theoretically valid within a process, as registered in the "process page 
table". Attempting to reference an address that has no corresponding page table 
entry causes a SEGV. Because accidentally dereferencing a NULL pointer is a 
common mistake and NULL is usually represented by binary all zeroes, the first 
page of a process never has a page table entry.

The cache_size and the mmap_size both influence how much of the "process size" 
is used to hold pages from your database file, by keeping a copy in the cache 
or mapping memory to the db disk file respectively.

Then there is "resident set size" (RSS), which is the portion of "process size" 
that, at a specific instant, actually refers to an address in main memory. 
Attempting to reference an address whose page table entry indicates that it is 
not currently in main memory causes a "page fault". The OS will interrupt 
program execution, allocate a page in main memory and provide the currently 
valid contents before restarting program execution at the instruction that 
caused the page fault.

Because main memory is usually much smaller than the combined process size of 
all currently running processes, the latter compete for main memory. The OS 
attempts to keep frequently accessed pages in main memory. It also attempts to 
re-use immutable pages by making them "shared"; it keeps just one copy in main 
memory and lets multiple processes map that page into their address space.

This is where "process set size" PSS comes into play. PSS is a measure of how 
much memory load is a process' fault. Each resident page that is exclusively 
used by a single process is that process' fault alone. The portion of 
cache_size that is used and currently resident falls within that category., as 
do stack, static non const and heap memory. Each resident page that is used by 
more than one process is the collective fault of all the processes. The portion 
of mmap_size that is used and currently resident falls within this category, as 
do code (most notably libraray code) and static const memory. Since blame is 
apportioned by current use, the PSS of a given process can and often does 
change without any action of its own. Creating or terminating a process that 
runs the same program, calls the same library or uses the same shared memory 
segment will change the PSS of any given process.

IF
- you have a system with a large enough amount of main memory
- and with a large enough amount of free main memory
- and run only one copy of your program
- and cache_size or mmap_size respectively are set larger than your db file
- and you are running a single copy of your application

THEN

- running a query than visits every row in your database (i.e. causes a page 
fault for each page) is likely to cause the PSS of the process to increase by 
the size of your db file.
- running the same query in a second process will cause the RSS of the original 
process to decrease by about half of the application code size and half the 
mmap_size

SO

- both cache_size and mmap_size TEND TO increase PSS, but mmap_size has the 
potential to split this among several instances of your application runnung 
with the same db file.

If you are running on a system that has severe main memory constraints, both 
settings will probably just shift the load between file IO and swap/page IO. 
Note thsat the sum of all RSS of all running processes can never exceed main 
memory.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nick
Gesendet: Donnerstag, 12. April 2018 09:05
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

Thanks for your explanation.
I want to get a confirmation that my understanding is correct and that if I use 
mmap_size=256M and I have only 1 process, then the PSS of the process will 
always the same as the size of my db file, as unixMapfile(-1) means map the 
whole file. (A big db file means 256M PSS) Is that correct?

In fact I had expected mmap only took up virtual memory instead of PSS.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
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] [EXTERNAL] Does mmap increase PSS?

[Nick]

Thanks for your explanation.
I want to get a confirmation that my understanding is correct and that if I
use mmap_size=256M and I have only 1 process, then the PSS of the process
will always the same as the size of my db file, as unixMapfile(-1) means map
the whole file. (A big db file means 256M PSS) Is that correct?

In fact I had expected mmap only took up virtual memory instead of PSS.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Constraints must be defined last?

[Hick Gunter]

Yes. See syntax diagram in http://sqlite.org/lang_createtable.html.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von J Decker
Gesendet: Mittwoch, 11. April 2018 19:41
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] Constraints must be defined last?

Is there something about SQL that requires constraints to follow all column 
definitions?


sqlite> create table `option4_blobs` (`option_id` char(36) default '0',
CONSTRAINT `value_id` UNIQUE (`option_id`),`binary` blob, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE 
CASCADE)
   ...> ;
Error: near "`binary`": syntax error

sqlite> create table `option4_blobs` (`option_id` char(36) default
'0',`binary` blob, CONSTRAINT `value_id` UNIQUE (`option_id`), FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE 
CASCADE)
   ...> ;
-- works
___
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] [EXTERNAL] Help using 'attach database' or Why is this an error?

[Hick Gunter]

Try

ATTACH DATABASE "test.db" as con2;

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von J Decker
Gesendet: Mittwoch, 11. April 2018 16:51
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] Help using 'attach database' or Why is this an 
error?

When I attempt to use attach database I get an error about 'no such column'


M:\sqlite3\sqlite3\build>sqlite3 test.db

SQLite version 3.23.0 2018-04-02 11:04:16es Enter ".help" for usage hints.
sqlite> attach database test.db as con2;
Error: no such column: test.db
sqlite>


I was trying to debug this set of commands, which is actually run on two 
connections within the same process... (oh I had misspelled foreign as
foriegn)




PRAGMA foreign_keys=on;

create table `option4_name` (`name_id` char(36) NOT NULL,`name`
varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);

create table `option4_map` (`option_id` char(36) NOT NULL,`parent_option_id` 
char(36) NOT NULL default '0',`name_id` char(36) NOT NULL default 
'0',`description` tinytext,CONSTRAINT `parent_key2` UNIQUE 
(`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`) REFERENCES 
`option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY  
(`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE CASCADE ON DELETE 
CASCADE);

create table `option4_values` (`option_id` char(36) default '0',`string`
varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT `value_id` 
UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE 
CASCADE);select tbl_name,sql from sqlite_master where type='table'
and

BEGIN TRANSACTION;

insert into option4_name (name_id,name) values( 
'82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );

insert into option4_map
(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
);

#SQLITE ERROR:Result of prepare failed? foreign key mismatch - "option4_map" 
referencing "option4_name" at char 185[] in [insert into option4_map 
(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
)]
___
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