Re: [sqlite] [EXTERNAL] Does mmap increase PSS?
AFAICT the showmap program is just a pretty-printer for the /proc//smaps file on android. You need to compare three states: 1) before mmap() is called (there should be no section referring to your file) 2) after mmap() is called (the section should be there, but RSS=PSS=0) 3) after the loop (the section should be there and RSS=PSS > 0) Which length(s) did you try? Perhaps android mmap is trying to be clever and is preloading parts of the file (you would see this in state 2). With respect to PSS, there is no difference between setting the page cache size and setting the mmap size to the amount of PSS you are willing to allow for db access (instead of the database size). What makes you think that using mmap will be superior to using the page cache? -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Nick Gesendet: Freitag, 13. April 2018 05:37 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] [EXTERNAL] Does mmap increase PSS? 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; ihttp://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] Preupdate hook column name
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?
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; ihttp://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?
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?
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?
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?
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?
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
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
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
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
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?
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
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
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
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?
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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?
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?
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
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?
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?
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,&stmt,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,&dbresult,&nrow,&ncol,&errmsg); elog_d(SYS_LOG_MDB,"get_fre_infor:ncol=%d nrow=%d",ncol,nrow); if(result == SQLITE_OK) { for(amr_time_index = 0; amr_
Re: [sqlite] sqlite 3.23.0 zipfile2 test failures
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, &pTab->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
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
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
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
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, &pTab->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?
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?
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?
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?
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