[sqlite] Database speed comparison
I ran across this page: https://sqlite.org/speed.html It is a 'bit' outdated. (It uses 2.7.6.) Where can I find the scripts? And would it be a lot of work to update them and regularly rerun them? If it is manageable I would not mind to provide regularly updates. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When not using threads: should I switch to single-thread mode
Op vr 27 dec. 2019 om 17:01 schreef Simon Slavin : > On 27 Dec 2019, at 3:06pm, Cecil Westerhof wrote: > > > My applications only use one thread (for the db stuff). Would it be a > good idea to switch to single-thread mode, or does that not give a real > performance improvement? > > On a desktop computer, or a mobile phone, the increase in speed is not > large. Maybe a few percent. If your application is already fast enough. > to please your users, I would not do the switching. > OK, thanks. I will not bother about that then. ;-) -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] When not using threads: should I switch to single-thread mode
My applications only use one thread (for the db stuff). Would it be a good idea to switch to single-thread mode, or does that not give a real performance improvement? If the performance is not really improved, I can better keep the default. Then there is no risk that I forget the change the mode when I switch to multiple threads. Maybe a good idea to add something about that at: https://www.sqlite.org/threadsafe.html -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Only enter higher values in table
Op vr 27 dec. 2019 om 13:12 schreef Hick Gunter :
> You need an UPDATE trigger for this, since the comparison requires
> knowledge of the old and new values.
>
Of-course. I should have thought of that. :'-(
I will look into that this weekend.
In my case it is not important (I do not expect to insert records from the
past), but I also add that it will not be higher as record from a later
date.
-Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Cecil Westerhof
> Gesendet: Freitag, 27. Dezember 2019 13:05
> An: SQLite mailing list
> Betreff: [EXTERNAL] [sqlite] Only enter higher values in table
>
> Just to have a way to see my progress at Udemy, I created the following
> table and view:
> CREATE TABLE rawSummaryUdemy (
> dateTEXTNOT NULL DEFAULT CURRENT_DATE,
> total INTEGER NOT NULL,
> completed INTEGER NOT NULL,
>
> CONSTRAINT formatDate CHECK(date = date(strftime('%s',
> date), 'unixepoch')),
> CONSTRAINT notInFuture CHECK(date <= date()),
> CONSTRAINT totalIsInt CHECK(TYPEOF(total) = 'integer'),
> CONSTRAINT completedIsInt CHECK(TYPEOF(completed) = 'integer'),
> CONSTRAINT totalGEZero CHECK(total >= 0),
> CONSTRAINT completedGEZero CHECK(completed >= 0),
> CONSTRAINT completedLETotal CHECK(completed <= total),
>
> PRIMARY KEY(date)
> );
> CREATE VIEW summaryUdemy AS
> SELECT *
> , total - completed AS toComplete
> FROM rawSummaryUdemy
> ;
>
> If this can be done better: let me know.
>
> Normally speaking total and completed should never decrease. It is not
> really important, but just as an exercise: is it possible to add
> constraints so that you cannot enter a total, or a completed that is lower
> as the previous one?
>
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Only enter higher values in table
Just to have a way to see my progress at Udemy, I created the following
table and view:
CREATE TABLE rawSummaryUdemy (
dateTEXTNOT NULL DEFAULT CURRENT_DATE,
total INTEGER NOT NULL,
completed INTEGER NOT NULL,
CONSTRAINT formatDate CHECK(date = date(strftime('%s',
date), 'unixepoch')),
CONSTRAINT notInFuture CHECK(date <= date()),
CONSTRAINT totalIsInt CHECK(TYPEOF(total) = 'integer'),
CONSTRAINT completedIsInt CHECK(TYPEOF(completed) = 'integer'),
CONSTRAINT totalGEZero CHECK(total >= 0),
CONSTRAINT completedGEZero CHECK(completed >= 0),
CONSTRAINT completedLETotal CHECK(completed <= total),
PRIMARY KEY(date)
);
CREATE VIEW summaryUdemy AS
SELECT *
, total - completed AS toComplete
FROM rawSummaryUdemy
;
If this can be done better: let me know.
Normally speaking total and completed should never decrease. It is not
really important, but just as an exercise: is it possible to add
constraints so that you cannot enter a total, or a completed that is lower
as the previous one?
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is this rewrite of a query OK
I had the following query: SELECT MIN(totalUsed) AS minimum , MAX(totalUsed) AS maximum , MAX(totalUsed) - MIN(totalUsed) AS range FROM quotes But I did not like it because I repeated the MIN and MAX. So I rewrote it to the following: SELECT Minimum , Maximum , Maximum - Minimum AS Range FROM ( SELECT MIN(totalUsed) AS Minimum , MAX(totalUsed) AS Maximum FROM quotes ) Is this acceptable, or could there be unintended consequences? Is there a better way to do it? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Conditional lowering of value
I have the following query: SELECT MIN(totalUsed) - 1 FROM quotes WHERE totalUsed <> 'notUsed' What I want is that when the SELECT returns a positive value every record where totalUsed <> 'notUsed' is lowered with the returned value. Is easy to accomplish in programming code, but I was wondering if this could be done with a SQL statement. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] wal-mode and checkpoint
2018-09-02 17:31 GMT+02:00 Simon Slavin : > On 2 Sep 2018, at 2:43pm, Cecil Westerhof wrote: > > > When I do in sqlitebrowser: > >PRAGMA TABLE_INFO(messages) > > Just for peace of mind, since you are reporting unexpected behaviour, > please run an integrity_check. > As expected that gave OK. The problem was that sqlitebrowser thought something had changed while it had not. > Certain cleaning-up jobs are done only when the last connection to the > database is closed. So if one program is holding the database open you may > find that these tasks are not completed. > As I understood it, it should be done when the last writer has closed. But maybe the fact that sqlitebrowser can write automatically makes it a writer. > Do you often leave sqlitebrowser running when you're not doing anything > with it ? Perhaps just as a background or minimised window on your screen ? > Yes, I find it very handy when I want to check upon something to have it open. Maybe I should rethink that. But just using: PRAGMA WAL_CHECKPOINT(TRUNCATE) solves the problem that is created by having it open. So I think it is not a problem (anymore). And probably I am going to use integrity_check and foreign_key_check also. Better to careful as not careful enough. ;-) -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] wal-mode and checkpoint
I changed from the default delete mode to wal mode. I had some strange results, but it is working now. One of the programs is a service and can run for weeks. That is why I decided to call every hour: PRAGMA WAL_CHECKPOINT(TRUNCATE) I was wondering what people on this list thought about this, because after the wal becomes bigger as 4 MB a checkpoint is (normally) done, but then I noticed that for at least one reason it is a good idea. When I do in sqlitebrowser: PRAGMA TABLE_INFO(messages) sqlitebrowser thinks the database has changed and checkpoint does not work. Luckily I output a message when it goes wrong and that is why I noticed it and could do revert changes in sqlitebrowser. Anybody an idea why this happens? Another strange thing I saw was that when I have a database open in sqlitebrowser that after termination of a program the wal file is not written to the database. So I added a checkpoint to the end of my programs also. Any idea why this happens? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [O] A strange problem with org-babel and SQLite
Oops, wrong group. Sorry. :'-( 2018-08-31 12:21 GMT+02:00 Cecil Westerhof : > 2018-08-31 11:17 GMT+02:00 Robert Klein : > >> Hi Cecil, >> >> On Fri, 31 Aug 2018 10:47:50 +0200 >> Cecil Westerhof wrote: >> >> > I have a strange problem with org-babel and SQLite. >> > >> > I have a database that is created with: >> > CREATE TABLE "quotes" ( >> > quoteID TEXT PRIMARY KEY, >> > quote TEXT NOT NULL UNIQUE, >> > lastUsedTEXT, >> > totalUsed INT DEFAULT 'unused' >> > ) >> > >> > When using: >> > #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes >> > SELECT lastUsed >> > ,totalUsed >> > FROM quotes >> > ORDER BY lastused ASC >> > ,totalUsed DESC >> > LIMIT40 >> > #+END_SRC >> > >> > Everything is fine. But when I use (add the quote field in the >> > select): #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes >> > SELECT quote >> > ,lastUsed >> > ,totalUsed >> > FROM quotes >> > ORDER BY lastused ASC >> > ,totalUsed DESC >> > LIMIT40 >> > #+END_SRC >> > >> > I get: >> > executing Sqlite code block... >> > Wrote /tmp/babel-27920y_/ob-input-2792BTG >> > org-babel-read: End of file during parsing >> > >> > What could be the problem? >> > >> >> does it work outside of org/babel/emacs, that is, when you use the >> query in a command line sqlite session, does it work? “quote” is also >> a function in sqlite, so this might be your issue. >> > > Yes, in sqlite3 and sqlitebrowser it works without problems. > In org-babel even 'SELECT *' goes wrong. > > -- > Cecil Westerhof > -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [O] A strange problem with org-babel and SQLite
2018-08-31 11:17 GMT+02:00 Robert Klein : > Hi Cecil, > > On Fri, 31 Aug 2018 10:47:50 +0200 > Cecil Westerhof wrote: > > > I have a strange problem with org-babel and SQLite. > > > > I have a database that is created with: > > CREATE TABLE "quotes" ( > > quoteID TEXT PRIMARY KEY, > > quote TEXT NOT NULL UNIQUE, > > lastUsedTEXT, > > totalUsed INT DEFAULT 'unused' > > ) > > > > When using: > > #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes > > SELECT lastUsed > > ,totalUsed > > FROM quotes > > ORDER BY lastused ASC > > ,totalUsed DESC > > LIMIT40 > > #+END_SRC > > > > Everything is fine. But when I use (add the quote field in the > > select): #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes > > SELECT quote > > ,lastUsed > > ,totalUsed > > FROM quotes > > ORDER BY lastused ASC > > ,totalUsed DESC > > LIMIT40 > > #+END_SRC > > > > I get: > > executing Sqlite code block... > > Wrote /tmp/babel-27920y_/ob-input-2792BTG > > org-babel-read: End of file during parsing > > > > What could be the problem? > > > > does it work outside of org/babel/emacs, that is, when you use the > query in a command line sqlite session, does it work? “quote” is also > a function in sqlite, so this might be your issue. > Yes, in sqlite3 and sqlitebrowser it works without problems. In org-babel even 'SELECT *' goes wrong. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Better way to get range of dates
2018-08-30 8:13 GMT+02:00 Keith Medcalf : > > Slightly more efficient code is generated for the BETWEEN version (the LHS > of the between is only calculated once). It is also somewhat easier to > read. > That is the primary reason to use BETWEEN, but it does not hurt that it is more efficient. ;-) > sqlite> select x from x where x between 1 and 10; > QUERY PLAN > I should learn to read QUERY PLAN's. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Better way to get range of dates
When getting data between a range of dates you can use:
WHERE date >= DATE('now', '-7 days')
AND date < DATE('now')
or:
WHERE date BETWEEN
DATE('now', '-7 days')
AND DATE('now', '-1 days')
Is there a preferred way? In a way I like the second better.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get data in one query
2018-08-29 21:44 GMT+02:00 Keith Medcalf :
>
> ... don't forget that Date('now') returns the UT1 date, not the local (as
> in Wall Clock/Calendar) date ... date('now', 'localtime') gives the local
> date in accordance with the timezone where your computer thinks it is
> located and should always be accurate for 'now' but maybe not a few years
> in the past on Operating Systems that do not contain/use a full set of UT1
> -> localtime (timezone) conversion rules (such as Windows).
>
I know and I prefer it that way. No problems with date + time when the
clock is set back. That is why I do my statistics after two in the morning.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get data in one query
2018-08-29 21:26 GMT+02:00 Wout Mertens : > By the way, why not store the time as epoch? Date and time in one... > Because I think it is better to have date and time as different (text) fields. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get data in one query
2018-08-29 18:58 GMT+02:00 Cecil Westerhof :
> 2018-08-29 18:06 GMT+02:00 R Smith :
>
>>
>> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
>> FROM (SELECT 1 AS Tot, (time NOT LIKE '%:00') AS Late
>> FROM messages
>> WHERE date = DATE('now')
>>)
>>
>
> Works like a charm. Thanks.
>
> I made it even more useful:
> SELECT Total
> , Late
> , CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
> FROM(
> SELECT SUM(total) AS Total
> , SUM(late) AS Late
> FROM (
> SELECT 1 AS Total
> , (time NOT LIKE '%:00') AS Late
> FROM messages
> WHERE date = DATE('now')
> )
> )
>
And even more useful:
SELECT date
, Total
, Late
, CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
FROM(
SELECT date
, SUM(total) AS Total
, SUM(late) AS Late
FROM (
SELECT date
, 1 AS Total
, (time NOT LIKE '%:00') AS Late
FROM messages
WHERE date >= DATE('now', '-7 days')
AND date < DATE('now')
)
GROUP BY date
)
ORDER BY date DESC
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get data in one query
2018-08-29 18:06 GMT+02:00 R Smith :
>
> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
> FROM (SELECT 1 AS Tot, (time NOT LIKE '%:00') AS Late
> FROM messages
> WHERE date = DATE('now')
>)
>
Works like a charm. Thanks.
I made it even more useful:
SELECT Total
, Late
, CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
FROM(
SELECT SUM(total) AS Total
, SUM(late) AS Late
FROM (
SELECT 1 AS Total
, (time NOT LIKE '%:00') AS Late
FROM messages
WHERE date = DATE('now')
)
)
> On 2018/08/29 5:56 PM, Cecil Westerhof wrote:
>
>> I have a table messages in which something is put every minute. The total
>> messages that are added today I can get with:
>> SELECT COUNT(*) AS Total
>> FROM messages
>> WHERE date = DATE('now')
>>
>> And the number of messages that where entered today, but not at the start
>> of a minute I can get with:
>> SELECT COUNT(*) AS Late
>> FROM messages
>> WHERE date = DATE('now')
>> AND time NOT LIKE '%:00'
>>
>> Is there a way to get this information in one query?
>>
>
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Get data in one query
I have a table messages in which something is put every minute. The total
messages that are added today I can get with:
SELECT COUNT(*) AS Total
FROM messages
WHERE date = DATE('now')
And the number of messages that where entered today, but not at the start
of a minute I can get with:
SELECT COUNT(*) AS Late
FROM messages
WHERE date = DATE('now')
AND time NOT LIKE '%:00'
Is there a way to get this information in one query?
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ssubstitution in Tcl goes sometimes wrong Posted
2018-07-30 20:39 GMT+02:00 Cecil Westerhof : > I described the problem here: > http://paste.tclers.tk/4800 > The solution is a CAST: AND Temperature > CAST(:tempAbove AS real) -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Ssubstitution in Tcl goes sometimes wrong Posted
I described the problem here: http://paste.tclers.tk/4800 -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Correct way for INSERT OR REPLACE and COMMIT
2018-07-19 11:37 GMT+02:00 R Smith :
> On 2018/07/19 8:35 AM, Cecil Westerhof wrote:
>
>> I have the following Tcl code:
>> set rollback "
>> INSERT OR REPLACE INTO pipRollback
>> (pipType, package, old, new)
>> VALUES
>> (:pip, :package, :old, :new)"
>>
>> db eval {BEGIN TRANSACTION}
>> foreach verbose ${packagesVerbose} {
>> .
>> .
>> .
>> if {${doAppend}} {
>> append packages "${package} "
>> db eval ${rollback}
>> }
>> }
>> db eval {COMMIT TRANSACTION}
>>
>> I suppose it is the correct way, but is there something I should change?
>>
>
> It looks perfect as far as replacing things go. The only note I would add
> is to be aware that if you insert a row that already has an existing Key
> value (I will assume here your Primary Key is "package") , then the
> original row will be deleted first (firing any possible ON DELETE triggers
> and possibly Foreign Key constraints[1]) and then be re-inserted (firing
> any ON INSERT, but not ON UPDATE) with the new values.
>
I should have been more clear: the primary key is: pipType, package.
The field pipType contains the type of pip: pip2, or pip3.
Package contains the package, for example youtube-dl.
A better option, to fix all this, is the new upsert feature which doesn't
> delete-and-re-insert, but in stead takes the logic of "Insert if needed,
> else update" firing the correct triggers/constraints for the required
> action.
>
> The correct format is given here:
> https://sqlite.org/lang_UPSERT.html
I had seen that, but it works from 3.24.0, my version in Tcl is: 3.16.2.
That is why I use INSERT OR REPLACE.
> And your query will need to be re-written like this (again, assuming
> "package" is the primary key, but it will work for any other PK field or
> combination of fields):
>
> INSERT INTO pipRollback (pipType, package, old, new)
> VALUES (:pip, :package, :old, :new)
> ON CONFLICT (package) DO UPDATE
> SET (pipType, old, new) = (:pip, :old, :new)
>
>
> Lastly, I thought I would add an example in case of multiple Key fields,
> if only to show how trivial the difference is. So assuming your Primary Key
> (or perhaps another UNIQUE constraint) was declared on multiple columns
> (pipType, package), the same query would now look like this:
>
> INSERT INTO pipRollback (pipType, package, old, new)
> VALUES (:pip, :package, :old, :new)
> ON CONFLICT (pipType, package) DO UPDATE
> SET (old, new) = (:old, :new)
>
> Note1 - The upsert feature is only available since SQLite version 3.24.0,
> so you have to be up-to-date.
> Note2 - Upsert doesn't currently work for Virtual Tables.
>
Thanks. I will look how easy/difficult it is to upgrade to 3.24.0.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Correct way for INSERT OR REPLACE and COMMIT
I have the following Tcl code:
set rollback "
INSERT OR REPLACE INTO pipRollback
(pipType, package, old, new)
VALUES
(:pip, :package, :old, :new)"
db eval {BEGIN TRANSACTION}
foreach verbose ${packagesVerbose} {
.
.
.
if {${doAppend}} {
append packages "${package} "
db eval ${rollback}
}
}
db eval {COMMIT TRANSACTION}
I suppose it is the correct way, but is there something I should change?
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lowering totalUsed
2018-07-12 9:30 GMT+02:00 Cecil Westerhof : > A few tables have a not completely apt named column totalUsed. > > It is used to see which records are more used as other records and give > the less used records a bigger chance of being selected. When the numbers > become high I do something like: > UPDATE tips > SET totalUsed = totalUsed - (SELECT MIN(totalUsed) FROM tips) + 1 > > I am not quit happy with this. Would it be better to split it in two > queries and feed the result of the first to the second? > By the way, I wanted to use: UPDATE quotes SET totalUsed = totalUsed - MIN(totalUsed) + 1 but that gives: Error: misuse of aggregate function MIN() -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Lowering totalUsed
A few tables have a not completely apt named column totalUsed. It is used to see which records are more used as other records and give the less used records a bigger chance of being selected. When the numbers become high I do something like: UPDATE tips SET totalUsed = totalUsed - (SELECT MIN(totalUsed) FROM tips) + 1 I am not quit happy with this. Would it be better to split it in two queries and feed the result of the first to the second? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] What happens when a call contains two SQL statement
2018-07-09 8:28 GMT+02:00 Hick Gunter :
> Why should a failure in transaction #2 rollback transaction #1?
>
I was thinking that, but was not sure. I thought that maybe everything in
a call would be seen as a transaction. But that is not the case then: every
statement in a call is its own transaction.
> If you want this behaviour, do "begin; delete ...; insert ... on conflict
> rollback; commit;" to make both statements run in one transaction
>
Is this standard SQL, or SQLite specific?
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Cecil Westerhof
> Gesendet: Montag, 09. Juli 2018 08:21
> An: SQLite mailing list
> Betreff: [EXTERNAL] [sqlite] What happens when a call contains two SQL
> statement
>
> I am working with Tcl. The best is of-course a general answer, but if it
> is depending on the used language I will be satisfied with the Tcl answer.
> ;-)
>
> Say I have the following code:
> set SQLCmd "
> DELETE FROM testing
> WHERE key = 12
> ;
> INSERT INTO testing
> (key, value)
> VALUES
> (12, 'Just some text')
> "
> db eval ${sqlCmd}
>
> If the insert goes wrong, will the delete be rolled back, or not?
>
> I could use INSERT OR REPLACE, but the above code would be database
> independent.
>
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What happens when a call contains two SQL statement
I am working with Tcl. The best is of-course a general answer, but if it is
depending on the used language I will be satisfied with the Tcl answer. ;-)
Say I have the following code:
set SQLCmd "
DELETE FROM testing
WHERE key = 12
;
INSERT INTO testing
(key, value)
VALUES
(12, 'Just some text')
"
db eval ${sqlCmd}
If the insert goes wrong, will the delete be rolled back, or not?
I could use INSERT OR REPLACE, but the above code would be database
independent.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Kind of pivot table
2018-07-08 11:00 GMT+02:00 Cecil Westerhof : > 2018-07-08 8:19 GMT+02:00 Cecil Westerhof : > >> I thought there was a problem with RANDOM. I used: >> ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser >> >> And it seemed I got a lot of threes. >> >> To check this I used: >> SELECT Randomiser >> , COUNT(*) AS Count >> FROM ( >> SELECT date >> ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser >> FROM CPUUsage >> ORDER BY date >> ) >> GROUP BY Randomiser >> ORDER BY Randomiser >> >> And this gave results like: >> "0""165491" >> "1""166270" >> "2""166207" >> "3""165727" >> "4""165619" >> "5""165749" >> "6""98042" >> >> So 6 is created less often as 0 - 5, but that is in my use case not a >> problem. >> >> This worked for me because I have a big table CPUUsage. But if I would >> not have, is there another way to to do this? >> > > Solved it. Do not need a table any-more and generate Randomiser in the > correct way: > > SELECT Randomiser > , COUNT(*) AS Count > FROM ( > WITH RECURSIVE > cnt(x) AS ( > SELECT 1 > UNION ALL > SELECT x + 1 FROM cnt > LIMIT 1.1E6 > ) > SELECT x > ,ABS(RANDOM()) % 7 AS Randomiser > FROM cnt > ORDER BY x > ) > GROUP BY Randomiser > ORDER BY Randomiser > > And this gives: > "0""157139" > "1""157865" > "2""156849" > "3""157226" > "4""156916" > "5""157230" > "6""156775" > > By the way: it is only slightly faster as the version where I used the > table. > I can shave off about 30% by rewriting it to: WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x + 1 FROM cnt LIMIT 1.1E6 ) SELECT ABS(RANDOM()) % 7 AS Randomiser ,COUNT(*) FROM cnt GROUP BY Randomiser ORDER BY Randomiser -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Kind of pivot table
2018-07-08 8:19 GMT+02:00 Cecil Westerhof : > I thought there was a problem with RANDOM. I used: > ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser > > And it seemed I got a lot of threes. > > To check this I used: > SELECT Randomiser > , COUNT(*) AS Count > FROM ( > SELECT date > ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser > FROM CPUUsage > ORDER BY date > ) > GROUP BY Randomiser > ORDER BY Randomiser > > And this gave results like: > "0""165491" > "1""166270" > "2""166207" > "3""165727" > "4""165619" > "5""165749" > "6""98042" > > So 6 is created less often as 0 - 5, but that is in my use case not a > problem. > > This worked for me because I have a big table CPUUsage. But if I would not > have, is there another way to to do this? > Solved it. Do not need a table any-more and generate Randomiser in the correct way: SELECT Randomiser , COUNT(*) AS Count FROM ( WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x + 1 FROM cnt LIMIT 1.1E6 ) SELECT x ,ABS(RANDOM()) % 7 AS Randomiser FROM cnt ORDER BY x ) GROUP BY Randomiser ORDER BY Randomiser And this gives: "0""157139" "1""157865" "2""156849" "3""157226" "4""156916" "5""157230" "6""156775" By the way: it is only slightly faster as the version where I used the table. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Kind of pivot table
2018-07-08 10:20 GMT+02:00 Keith Medcalf : > > You probably do not have the kurtosis or skew aggregate functions either. > > generate_series is the series.c extension. > OK, thanks. Something to look into at a later moment. > >-Original Message- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof > >Sent: Sunday, 8 July, 2018 02:16 > >To: SQLite mailing list > >Subject: Re: [sqlite] Kind of pivot table > > > >2018-07-08 9:10 GMT+02:00 Keith Medcalf : > > > >> > >> sqlite> > >> > >> select kurt(abs(random() % 7)) from generate_series where start=1 > >and > >> stop=1e6; > >> -1.25154453962449 > >> > >> sqlite> select skew(abs(random() % 7)) from generate_series where > >start=1 > >> and stop=1e6; > >> 0.00104535938599554 > >> > >> The PRNG is pretty random. > >> > >> It is slightly concave (that is, anti-normal) (a "flat" > >distribution would > >> have a kurtosis of -1.2) and the curve is slightly skewed above the > >average. > >> > > > >At the moment it does not say much to me. But when I try the first > >statement I get: > >Error: no such table: generate_series > > > >Am I overlooking something? > -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Kind of pivot table
2018-07-08 9:10 GMT+02:00 Keith Medcalf : > > sqlite> > > select kurt(abs(random() % 7)) from generate_series where start=1 and > stop=1e6; > -1.25154453962449 > > sqlite> select skew(abs(random() % 7)) from generate_series where start=1 > and stop=1e6; > 0.00104535938599554 > > The PRNG is pretty random. > > It is slightly concave (that is, anti-normal) (a "flat" distribution would > have a kurtosis of -1.2) and the curve is slightly skewed above the average. > At the moment it does not say much to me. But when I try the first statement I get: Error: no such table: generate_series Am I overlooking something? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] To JSON or not to JSON
A long time ago I changed a line base application to a SQLite application. Every line was a record and this record could have several elements. Every element was send to Twitter with a minute between them. When converting I kept it like this. So my SQLite database contains for example: "5BF19111-9FD5-48CA-B919-A09411346A87""[ ""The journey of a thousand miles must begin with a single step. - Lao Tzu"", ""Welke stap kun je vandaag zetten, om dat verre doel te bereiken?"" ]""2018-07-07" Respectively quoteID, quote (with extra line(s)) and lastUsed. Is this an acceptable way to implement it, or would it be better to unravel the elements in different records? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Kind of pivot table
2018-07-08 8:49 GMT+02:00 Keith Medcalf : > > Why not use MOD (%) as in > > ABS(RANDOM() % 6) > You are completely right. How stupid of me. :'-( It only has to be: ABS(RANDOM() % 7) > >-Original Message- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof > >Sent: Sunday, 8 July, 2018 00:44 > >To: SQLite mailing list > >Subject: Re: [sqlite] Kind of pivot table > > > >2018-07-08 8:19 GMT+02:00 Cecil Westerhof : > > > >> I thought there was a problem with RANDOM. I used: > >> ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser > >> > >> And it seemed I got a lot of threes. > >> > >> To check this I used: > >> SELECT Randomiser > >> , COUNT(*) AS Count > >> FROM ( > >> SELECT date > >> ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS > >Randomiser > >> FROM CPUUsage > >> ORDER BY date > >> ) > >> GROUP BY Randomiser > >> ORDER BY Randomiser > >> > >> And this gave results like: > >> "0""165491" > >> "1""166270" > >> "2""166207" > >> "3""165727" > >> "4""165619" > >> "5""165749" > >> "6""98042" > >> > >> So 6 is created less often as 0 - 5, but that is in my use case not > >a > >> problem. > >> > >> This worked for me because I have a big table CPUUsage. But if I > >would not > >> have, is there another way to to do this? > >> > > > >By the way better select is: > >SELECT Randomiser > >, COUNT(*) AS Count > >FROM ( > >SELECT date > >,CAST(ABS(RANDOM()) / (9.223E18 / 7) AS INTEGER) AS > >Randomiser > >FROM CPUUsage > >ORDER BY date > >) > >GROUP BY Randomiser > >ORDER BY Randomiser > > > >This gives: > >"0""156204" > >"1""157032" > >"2""155636" > >"3""156399" > >"4""156256" > >"5""155480" > >"6""156073" > > "7""52" > > > >This is much better. Only very rarely you get a seven you do not > >want. > >(Again in my case not really a problem.) > > > >Because in my case I use Randomiser to get a small subset of the > >records, > >this can be solved with: > >SELECT Randomiser > >, COUNT(*) AS Count > >FROM ( > >SELECT date > >,CAST(ABS(RANDOM()) / (9.223E18 / 7) AS INTEGER) AS > >Randomiser > >FROM CPUUsage > >ORDER BY date > >) > >WHERERandomiser <> 7 > >GROUP BY Randomiser > >ORDER BY Randomiser > > > > > >Then I get something like: > >"0""155806" > >"1""156270" > >"2""156473" > >"3""155748" > >"4""155828" > >"5""156196" > >"6""156733" > -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Kind of pivot table
2018-07-08 8:19 GMT+02:00 Cecil Westerhof :
> I thought there was a problem with RANDOM. I used:
> ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser
>
> And it seemed I got a lot of threes.
>
> To check this I used:
> SELECT Randomiser
> , COUNT(*) AS Count
> FROM (
> SELECT date
> ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser
> FROM CPUUsage
> ORDER BY date
> )
> GROUP BY Randomiser
> ORDER BY Randomiser
>
> And this gave results like:
> "0""165491"
> "1""166270"
> "2""166207"
> "3""165727"
> "4""165619"
> "5""165749"
> "6""98042"
>
> So 6 is created less often as 0 - 5, but that is in my use case not a
> problem.
>
> This worked for me because I have a big table CPUUsage. But if I would not
> have, is there another way to to do this?
>
By the way better select is:
SELECT Randomiser
, COUNT(*) AS Count
FROM (
SELECT date
,CAST(ABS(RANDOM()) / (9.223E18 / 7) AS INTEGER) AS
Randomiser
FROM CPUUsage
ORDER BY date
)
GROUP BY Randomiser
ORDER BY Randomiser
This gives:
"0""156204"
"1""157032"
"2""155636"
"3""156399"
"4""156256"
"5""155480"
"6""156073"
"7""52"
This is much better. Only very rarely you get a seven you do not want.
(Again in my case not really a problem.)
Because in my case I use Randomiser to get a small subset of the records,
this can be solved with:
SELECT Randomiser
, COUNT(*) AS Count
FROM (
SELECT date
,CAST(ABS(RANDOM()) / (9.223E18 / 7) AS INTEGER) AS
Randomiser
FROM CPUUsage
ORDER BY date
)
WHERERandomiser <> 7
GROUP BY Randomiser
ORDER BY Randomiser
Then I get something like:
"0""155806"
"1""156270"
"2""156473"
"3""155748"
"4""155828"
"5""156196"
"6""156733"
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Kind of pivot table
I thought there was a problem with RANDOM. I used: ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser And it seemed I got a lot of threes. To check this I used: SELECT Randomiser , COUNT(*) AS Count FROM ( SELECT date ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser FROM CPUUsage ORDER BY date ) GROUP BY Randomiser ORDER BY Randomiser And this gave results like: "0""165491" "1""166270" "2""166207" "3""165727" "4""165619" "5""165749" "6""98042" So 6 is created less often as 0 - 5, but that is in my use case not a problem. This worked for me because I have a big table CPUUsage. But if I would not have, is there another way to to do this? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automatic numbering
2018-07-07 14:57 GMT+02:00 Simon Slavin :
> On 7 Jul 2018, at 12:04pm, Cecil Westerhof wrote:
>
> > I went for the following solution:
> >UPDATE
>
>
> selectRandom
> >SETlastUsed= DATE('now', 'localtime')
> >, lastUsedIdx = (
> >SELECT IFNULL(MAX(lastUsedIdx), 0)
> >FROM selectRandom
> >WHERE lastUsed = DATE('now', 'localtime')
> >) + 1
> >WHERE description = :teaToBrew
> >
> > I would think that is not to convoluted.
>
> Elegant. I understood it. Though I was primed with what you're trying to
> do.
>
I drink a lot of different sorts of tea. Often I have between 20 and 30
different types. To make the choice easier I wrote a little
application
that only shows a few to select from. The longer ago that I drank a tea,
the greater the change it is shown in the list. I also display the latest
tea I drank. (I have several chai teas, but you should not drink it more as
once in three days.) To show them in the correct order (not important, but
nice to have) I wanted this index. For that I use:
SELECT *
FROM teaInAndOutStock
ORDER BY LastUsedDESC
,lastUsedIdx DESC
LIMIT:limitNr
teaInAndOutStock is a view on selectRandom.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automatic numbering
2018-07-05 5:37 GMT+02:00 Simon Slavin :
> On 5 Jul 2018, at 3:22am, Cecil Westerhof wrote:
>
> > I only want to store a date with a record. But it is possible that more
> as
> > one record will be inserted, so I want to use another field to use as an
> > index. So that the first gets an one, the second a two, etc.
> > Is this possible, or do I just have to check if there is already a date
> and
> > fetch the highest index and increase this with one?
>
> There's no magical shortcut.
>
> I would create an index on (theDate, dateEventNumber). Then do
>
> BEGIN
> SELECT MAX(dateEventNumber) FROM MyTable WHERE theDate = ?1
> [ in your code see whether you got NULL back, substitute 0 ]
> INERT INTO MyTable ... ?1 + 1
> COMMIT
>
> You can combine the two commands into one more complicated thing, but I'd
> do that only if I was sure nobody would ever have to figure out why my code
> wasn't working.
>
I went for the following solution:
UPDATE selectRandom
SETlastUsed= DATE('now', 'localtime')
, lastUsedIdx = (
SELECT IFNULL(MAX(lastUsedIdx), 0)
FROM selectRandom
WHERE lastUsed = DATE('now', 'localtime')
) + 1
WHERE description = :teaToBrew
I would think that is not to convoluted.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automatic numbering
2018-07-05 5:37 GMT+02:00 Simon Slavin : > On 5 Jul 2018, at 3:22am, Cecil Westerhof wrote: > > > I only want to store a date with a record. But it is possible that more > as > > one record will be inserted, so I want to use another field to use as an > > index. So that the first gets an one, the second a two, etc. > > Is this possible, or do I just have to check if there is already a date > and > > fetch the highest index and increase this with one? > > There's no magical shortcut. > > I would create an index on (theDate, dateEventNumber). Then do > > BEGIN > SELECT MAX(dateEventNumber) FROM MyTable WHERE theDate = ?1 > [ in your code see whether you got NULL back, substitute 0 ] > INERT INTO MyTable ... ?1 + 1 > COMMIT > > You can combine the two commands into one more complicated thing, but I'd > do that only if I was sure nobody would ever have to figure out why my code > wasn't working. > OK, thank you. I am going to play with it. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Automatic numbering
I only want to store a date with a record. But it is possible that more as one record will be inserted, so I want to use another field to use as an index. So that the first gets an one, the second a two, etc. Is this possible, or do I just have to check if there is already a date and fetch the highest index and increase this with one? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 'Best' way to create calculated field
At the moment I have the following query: SELECT Minimum , Maximum , Maximum - Minimum AS Range FROM ( SELECT MIN(totalUsed) AS Minimum , MAX(totalUsed) AS Maximum FROM quotes ) I like this better as: SELECT MIN(totalUsed) AS Minimum , MAX(totalUsed) AS Maximum , MAX(totalUsed) - MIN(totalUsed) AS Range FROM quotes Or is there a reason to go for the second query, or even a total different query? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check Constraint
2018-06-12 12:38 GMT+02:00 Clemens Ladisch : > Cecil Westerhof wrote: > > I want to create a field that only has values that consist of letters, > > numbers end '-'. So no spaces, quotes or special characters like: '@%$!'. > > What is the best way to write this check constraint? > > The GLOB operator has inverted character classes. So the field is valid > if its value does not contain any character that is not in the valid list: > > CREATE TABLE [] ( > Field CHECK(Field NOT GLOB '*[^0-9A-Za-z-]*') > ); > Thanks, seems to work. The field is not allowed to be NULL or empty, so I use: CREATE TABLE pipManual ( package TEXT NOT NULL, CHECK(package != ''), CHECK(package NOT GLOB '*[^0-9A-Za-z-]*') ); -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Check Constraint
I want to create a field that only has values that consist of letters, numbers end '-'. So no spaces, quotes or special characters like: '@%$!'. What is the best way to write this check constraint? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] This list is getting spammed again
2018-05-11 11:12 GMT+02:00 Cecil Westerhof : > 2018-05-10 1:09 GMT+02:00 Simon Slavin : > >> >> >> On 9 May 2018, at 9:37pm, Cecil Westerhof wrote: >> >> > I am bitten by it also now. I posted a question and within two minutes >> I >> > got a spam message >> >> I got three or four of these, each one soon after I'd posted a message. >> Then I got no more. I didn't do anything to stop them and I have checked >> my spam system to see if it stopped them, but the spam system didn't >> receive any more. >> > > I marked the sender as spammer. (It was from one sender.) And the > messages are send to spam now. I got a few more. I'll watch if this > triggers again, or that it is a 'smart' spammer that stops when you do not > reply. > I got no new spam message. So it is a 'smart' spammer. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] This list is getting spammed again
2018-05-10 1:09 GMT+02:00 Simon Slavin : > > > On 9 May 2018, at 9:37pm, Cecil Westerhof wrote: > > > I am bitten by it also now. I posted a question and within two minutes I > > got a spam message > > I got three or four of these, each one soon after I'd posted a message. > Then I got no more. I didn't do anything to stop them and I have checked > my spam system to see if it stopped them, but the spam system didn't > receive any more. > I marked the sender as spammer. (It was from one sender.) And the messages are send to spam now. I got a few more. I'll watch if this triggers again, or that it is a 'smart' spammer that stops when you do not reply. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] This list is getting spammed again
2018-05-08 9:37 GMT+02:00 Domingo Alvarez Duarte : > Again this list is getting spammed, I just received spam after publish. > I am bitten by it also now. I posted a question and within two minutes I got a spam message that pretended to be a reply on my message. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Only see unused when there are unused records
I have a table where I use 'unused' to signify that a record is not yet used. I want to know the number of unused records (but only if there are unused records) and the total number of records. At the moment I implemented it like: SELECT * FROM ( SELECT 'Not used' AS Type ,COUNT(*)AS NoUsed FROM quotes WHEREtotalUsed == 'unused' UNION ALL SELECT 'Total' AS Type ,COUNT(*) AS NoUsed FROM quotes ) WHERENoUsed > 0 Is this correct, or is there a better way? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How many AUTOINCREMENT tables are in your schema?
2018-03-16 16:37 GMT+01:00 Richard Hipp : > This is a survey, the results of which will help us to make SQLite faster. > > How many tables in your schema(s) use AUTOINCREMENT? > > I just need a single integer, the count of uses of the AUTOINCREMENT > in your overall schema. You might compute this using: > >sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l > A lot less as I thought: 0 5 0 0 0 0 0 0 0 2 0 2 0 0 0 1 0 0 0 -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why this LIMIT
2018-03-11 9:49 GMT+01:00 Clemens Ladisch : > Cecil Westerhof wrote: > > I see that in certain older queries I use: > > LIMIT (SELECT COUNT(*) FROM TABLE) > > > > It looks like this has no use (limiting the selected records to the > > number of records there are). Anyone an idea what could be a reason for > > this? > > Trying to put some table data into the cache? > > Reading into a fixed-sized array, and protecting against concurrent > inserts (from inside the same transaction)? > > Needing the LIMIT clause for some reason (disabling subquery flattening?) > but not knowing about "LIMIT -1"? > None ring a bell. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Need two ORDER two times when using RANDOM
I have the following query: SELECT * , randomiser , randomiser FROM ( SELECT * , ABS(RANDOM()) / 5E17 AS randomiser FROM proverbs WHERE used <> 'notUsed' ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC ) ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC And this gives for example: "Voor niets gaat de zon op.""2017-01-12""0.337325790117148" "0.337325790117148""0.337325790117148" "Met de wolven in het bos meehuilen.""2017-01-11" "2.59601454335206""2.59601454335206""2.59601454335206" "Als katten muizen, mauwen ze niet.""2017-01-11""2.7932230420896" "2.7932230420896""2.7932230420896" "Uitstel is afstel.""2017-01-12""1.88933779146209" "1.88933779146209""1.88933779146209" "Het is rozengeur en maneschijn.""2017-01-13""1.16363975452034" "1.16363975452034""1.16363975452034" But when I remove the last ORDER (which seems redundant) I get: "Het is rozengeur en maneschijn.""2017-01-13""3.0795495790489" "2.7886449148631""6.78717082169993" "Met de kippen op stok.""2017-01-16""6.56483737827297" "1.32007069440753""5.18920985400017" "Schoenmaker, blijf bij je leest.""2017-01-12""8.17180081902947" "5.91518750003302""4.78956808218011" "Een goed begin is het halve werk.""2017-01-17" "7.70627730482033""15.580638189131""2.0146022387495" "Zoals het klokje thuis tikt, tikt het nergens.""2017-01-18""8.53059705262686" "15.7307229942""1.1516802288132" So randomiser is not stable any-more. For the moment I work with: SELECT * ,randomiser ,randomiser FROM ( SELECT * ,ABS(RANDOM()) / 5E17 AS randomiser FROM proverbs WHEREused <> 'notUsed' ORDER BY used ASC ) ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC Then randomiser is stable and the time needed is only slightly more as time for the second query. (It is about 43, 53 and 46 ms.) -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why this LIMIT
I see that in certain older queries I use: LIMIT (SELECT COUNT(*) FROM TABLE) It looks like this has no use (limiting the selected records to the number of records there are). Anyone an idea what could be a reason for this? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tee to a table
2018-02-01 21:49 GMT+01:00 Peter Da Silva :
> It's pretty easy in Tcl
>
> Sqlite3 db file.sqlite
> while {[gets stdin line] > 0} {
> parse_line_into index content; # or whatever you do to extract content
> from the line
> db eval {INSERT INTO whatever (index, content) VALUES ($index,
> $content);}
> }
> db close
>
Looks promising. The 'problem' is that I get a record pro line. But that
is not a big problem I think. On the plus side it is easy to make a GUI
instead of a command line version.
Thanks.
> On 2/1/18, 2:25 PM, "sqlite-users on behalf of Cecil Westerhof" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> cldwester...@gmail.com> wrote:
>
> At the moment I have a script where I send the output of a ffmpeg
> command
> to the terminal and a file. Is it possible to send the output to a
> SQLite
> table. I like to use tcl for this.
>
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tee to a table
2018-02-01 21:42 GMT+01:00 Simon Slavin : > On 1 Feb 2018, at 8:25pm, Cecil Westerhof wrote: > > > At the moment I have a script where I send the output of a ffmpeg command > > to the terminal and a file. Is it possible to send the output to a SQLite > > table. I like to use tcl for this. > > Is the output a string of text ? > Text. It start for example with: 07:00:25: converting MVI_6580.MOV ffmpeg version 3.2.8-1~deb9u1 Copyright (c) 2000-2017 the FFmpeg developers built with gcc 6.3.0 (Debian 6.3.0-18) 20170516 configuration: --prefix=/usr --extra-version='1~deb9u1' --toolchain=hardened --libdir=/usr/lib/x86_64-linux-gnu --incdir=/usr/include/x86_64-linux-gnu --enable-gpl --disable-stripping --enable-avresample --enable-avisynth --enable-gnutls --enable-ladspa --enable-libass --enable-libbluray --enable-libbs2b --enable-libcaca --enable-libcdio --enable-libebur128 --enable-libflite --enable-libfontconfig --enable-libfreetype --enable-libfribidi --enable-libgme --enable-libgsm --enable-libmp3lame --enable-libopenjpeg --enable-libopenmpt --enable-libopus --enable-libpulse --enable-librubberband --enable-libshine --enable-libsnappy --enable-libsoxr --enable-libspeex --enable-libssh --enable-libtheora --enable-libtwolame --enable-libvorbis --enable-libvpx --enable-libwavpack --enable-libwebp --enable-libx265 --enable-libxvid --enable-libzmq --enable-libzvbi --enable-omx --enable-openal --enable-opengl --enable-sdl2 --enable-libdc1394 --enable-libiec61883 --enable-chromaprint --enable-frei0r --enable-libopencv --enable-libx264 --enable-shared libavutil 55. 34.101 / 55. 34.101 libavcodec 57. 64.101 / 57. 64.101 libavformat57. 56.101 / 57. 56.101 libavdevice57. 1.100 / 57. 1.100 libavfilter 6. 65.100 / 6. 65.100 libavresample 3. 1. 0 / 3. 1. 0 libswscale 4. 2.100 / 4. 2.100 libswresample 2. 3.100 / 2. 3.100 libpostproc54. 1.100 / 54. 1.100 Input #0, mov,mp4,m4a,3gp,3g2,mj2, from 'MVI_6580.MOV': Can you use scripting commands to include it in a file which scripts the > SQLite command-line tool ? > I am not sure what you mean. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tee to a table
At the moment I have a script where I send the output of a ffmpeg command to the terminal and a file. Is it possible to send the output to a SQLite table. I like to use tcl for this. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Check if SQLite Databases Are Locked
I am using a lot of SQLite databases. The problem is that I sometimes do things in a DB browser, but do not write or revert changes. This can give problems with my cron scripts that use the locked database. Because of this I wrote a script that accept a series of databases as argument and check all those databases for being locked and print a line for the databases that are locked. So when no databases are locked, there is no output. I have a cron entry that runs this script twice an hour, so I will be notified if I have to unlock a database. If you are interested you can find the script here: https://wiki.tcl.tk/54781 -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can this be done with SQLite
2018-01-23 1:02 GMT+01:00 Keith Medcalf : > > Part of the problem is going to be that you have not defined the problem > sufficiently for a "solution" to be proposed. Based on your somewhat silly > example one can deduce the following constraints: > > With respect to "key": > - this is TEXT (UTF-8 or something else)? > - you specify check(length(key)) == 1 do you mean: >- one character in some encoding (key between 0 and 0x) >- one byte? (ie, ord(key) between 0 and 255) >- something else entirely? >- is it contiguous? >- if not contiguous what are the rules defining the non-contiguousness? > I would like a general solution. So the type of key is not defined and it is not necessary to be contiguous. > - what is the "rotation order" based on? >- next arithmetic value, upper wraps to lower? >- next used key (by some collation order? Which collation order?) >- based on "used" values? >- based on "entire domain"? > Rotation is either up or down. In my example it was up. (In my perception.) Order is just the default order. > The problem and its solution is rather simple, once you define problem to > be solved with sufficient specificity to permit a solution. > > Your "example" below does not provide sufficient referents to generate a > solution that is cohesive over any problem domain other than that covered > by the example, and your referential constraints are inadequate to ensure > integrity for your limited example. > I think I can solve it generally. I will look into it and share it here. > >-Original Message- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof > >Sent: Monday, 22 January, 2018 13:30 > >To: SQLite mailing list > >Subject: [sqlite] Can this be done with SQLite > > > >I have the following table: > >CREATE TABLE playYouTubeVideo ( > >key TEXTNOT NULL, > >speed FLOAT NOT NULL, > > > >CONSTRAINT key CHECK(length(key) == 1), > >CONSTRAINT speed CHECK(TYPEOF(speed) = "real"), > > > >PRIMARY KEY(key) > >); > > > > > >Say I want to rotate a part: > >- The value by key '1' becomes the value by key '2'. > >- The value by key '2' becomes the value by key '3'. > >- The value by key '3' becomes the value by key '4'. > >- The value by key '4' becomes the value by key '5'. > >- The value by key '5' becomes the value by key '6'. > > > >I suppose that I need to do this programmatically, or can this be > >done > >with SQL? > > > >And optionally also: > >- The value by key '1' becomes the value by key '5'. > -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can this be done with SQLite
2018-01-22 23:15 GMT+01:00 David Raymond : > Ok, so you're looking for a "rotate" sort of thing? > Yes. ;-) > (The schema with a text key with length of 1 made me think it wasn't going > to get too big) > In this particular case it is a string with length 1, but I am 'always' looking at the general case. > Are the keys all integers then? All positive? Continuous? > In this case yes, but it does not need to be. > begin transaction; > create temp table t (key int primary key, speed real); > insert into t select key, (select t1.speed from playYouTubeVideo as t1 > where t1.key = foo.key % 5 + 1) from playYouTubeVideo; > update playYouTubeVideo set speed = (select speed from t where key = > playYouTubeVideo.key); > drop table t; > commit; > Comes a good end in the right direction, but I am thinking I am going to do it programmatically. Maybe write a general function for it. Everyone thanks for the fast replies. > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Cecil Westerhof > Sent: Monday, January 22, 2018 4:37 PM > To: SQLite mailing list > Subject: Re: [sqlite] Can this be done with SQLite > > 2018-01-22 21:38 GMT+01:00 David Raymond : > > > Unless I'm reading you wrong then just do the normal > > > > begin transaction; > > update playYouTubeVideo set speed = ( > > select speed from playYouTubeVideo where key = '2') > > where key = '1'; > > update playYouTubeVideo set speed = ( > > select speed from playYouTubeVideo where key = '3') > > where key = '2'; > > ... > > update playYouTubeVideo set speed = ( > > select speed from playYouTubeVideo where key = '5') > > where key = '1'; > > commit; > > > > Nope. By the way I see that I did not write it correctly. :'-( > > When I do this, I get: > sqlite> SELECT * >...> FROM playYouTubeVideo >...> WHERE key BETWEEN '1' AND '5' >...> ; > 1|1.0 > 2|2.0 > 3|3.0 > 4|4.0 > 5|5.0 > sqlite> begin transaction; > sqlite> update playYouTubeVideo set speed = ( >...> select speed from playYouTubeVideo where key = '2') >...> where key = '1'; > sqlite> update playYouTubeVideo set speed = ( >...> select speed from playYouTubeVideo where key = '3') >...> where key = '2'; > sqlite> update playYouTubeVideo set speed = ( >...> select speed from playYouTubeVideo where key = '4') >...> where key = '3'; > sqlite> update playYouTubeVideo set speed = ( >...> select speed from playYouTubeVideo where key = '5') >...> where key = '4'; > sqlite> update playYouTubeVideo set speed = ( >...> select speed from playYouTubeVideo where key = '1') >...> where key = '5'; > sqlite> commit; > sqlite> SELECT * >...> FROM playYouTubeVideo >...> WHERE key BETWEEN '1' AND '5' >...> ; > 1|2.0 > 2|3.0 > 3|4.0 > 4|5.0 > 5|2.0 > > But I want the last one needs to be 1.0. > Also, when the range becomes big, it will be a lot of code. > > > I was hoping I overlooked a smart trick, but I probably need to do it > programmatically. > > > -Original Message- > > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > On Behalf Of Cecil Westerhof > > Sent: Monday, January 22, 2018 3:30 PM > > To: SQLite mailing list > > Subject: [sqlite] Can this be done with SQLite > > > > I have the following table: > > CREATE TABLE playYouTubeVideo ( > > key TEXTNOT NULL, > > speed FLOAT NOT NULL, > > > > CONSTRAINT key CHECK(length(key) == 1), > > CONSTRAINT speed CHECK(TYPEOF(speed) = "real"), > > > > PRIMARY KEY(key) > > ); > > > > > > Say I want to rotate a part: > > - The value by key '1' becomes the value by key '2'. > > - The value by key '2' becomes the value by key '3'. > > - The value by key '3' becomes the value by key '4'. > > - The value by key '4' becomes the value by key '5'. > > - The value by key '5' becomes the value by key '6'. > > > > I suppose that I need to do this programmatically, or can this be done > > with SQL? > > > > A
Re: [sqlite] Can this be done with SQLite
2018-01-22 23:07 GMT+01:00 Igor Tandetnik :
> On 1/22/2018 4:36 PM, Cecil Westerhof wrote:
>
>>
>> When I do this, I get:
>> sqlite> SELECT *
>> ...> FROM playYouTubeVideo
>> ...> WHERE key BETWEEN '1' AND '5'
>> ...> ;
>> 1|1.0
>> 2|2.0
>> 3|3.0
>> 4|4.0
>> 5|5.0
>>
>> [snip]
>>
>> sqlite> SELECT *
>> ...> FROM playYouTubeVideo
>> ...> WHERE key BETWEEN '1' AND '5'
>> ...> ;
>> 1|2.0
>> 2|3.0
>> 3|4.0
>> 4|5.0
>> 5|2.0
>>
>> But I want the last one needs to be 1.0.
>>
>
> Something along these lines, perhaps:
>
> update playYouTubeVideo set key=char(61440+unicode(key));
> update playYouTubeVideo set key=case when key=char(61440+unicode('1'))
> then '5' else char(unicode(key)-61440-1) end;
>
This also expects the values to be constant. But what I want is that the
record with key 1 gets the value from key 2, with key 2 from key 3, …
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can this be done with SQLite
2018-01-22 22:36 GMT+01:00 Jim Morris : > Wouldn't the mod operator do this? > > Do an update and set key = 1 + (5 + key)%5 Only when the values are: 1.0, 2.0, 3.0, 4.0 and 5.0. But not when they are: 1.25, 1.5, 1.75, 2.0 and 1.0. On 1/22/2018 12:38 PM, David Raymond wrote: > >> Unless I'm reading you wrong then just do the normal >> >> begin transaction; >> update playYouTubeVideo set speed = ( >> select speed from playYouTubeVideo where key = '2') >> where key = '1'; >> update playYouTubeVideo set speed = ( >> select speed from playYouTubeVideo where key = '3') >> where key = '2'; >> ... >> update playYouTubeVideo set speed = ( >> select speed from playYouTubeVideo where key = '5') >> where key = '1'; >> commit; >> >> >> -Original Message- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf Of Cecil Westerhof >> Sent: Monday, January 22, 2018 3:30 PM >> To: SQLite mailing list >> Subject: [sqlite] Can this be done with SQLite >> >> I have the following table: >> CREATE TABLE playYouTubeVideo ( >> key TEXTNOT NULL, >> speed FLOAT NOT NULL, >> >> CONSTRAINT key CHECK(length(key) == 1), >> CONSTRAINT speed CHECK(TYPEOF(speed) = "real"), >> >> PRIMARY KEY(key) >> ); >> >> >> Say I want to rotate a part: >> - The value by key '1' becomes the value by key '2'. >> - The value by key '2' becomes the value by key '3'. >> - The value by key '3' becomes the value by key '4'. >> - The value by key '4' becomes the value by key '5'. >> - The value by key '5' becomes the value by key '6'. >> >> I suppose that I need to do this programmatically, or can this be done >> with SQL? >> >> And optionally also: >> - The value by key '1' becomes the value by key '5'. >> >> > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can this be done with SQLite
2018-01-22 21:38 GMT+01:00 David Raymond : > Unless I'm reading you wrong then just do the normal > > begin transaction; > update playYouTubeVideo set speed = ( > select speed from playYouTubeVideo where key = '2') > where key = '1'; > update playYouTubeVideo set speed = ( > select speed from playYouTubeVideo where key = '3') > where key = '2'; > ... > update playYouTubeVideo set speed = ( > select speed from playYouTubeVideo where key = '5') > where key = '1'; > commit; > Nope. By the way I see that I did not write it correctly. :'-( When I do this, I get: sqlite> SELECT * ...> FROM playYouTubeVideo ...> WHERE key BETWEEN '1' AND '5' ...> ; 1|1.0 2|2.0 3|3.0 4|4.0 5|5.0 sqlite> begin transaction; sqlite> update playYouTubeVideo set speed = ( ...> select speed from playYouTubeVideo where key = '2') ...> where key = '1'; sqlite> update playYouTubeVideo set speed = ( ...> select speed from playYouTubeVideo where key = '3') ...> where key = '2'; sqlite> update playYouTubeVideo set speed = ( ...> select speed from playYouTubeVideo where key = '4') ...> where key = '3'; sqlite> update playYouTubeVideo set speed = ( ...> select speed from playYouTubeVideo where key = '5') ...> where key = '4'; sqlite> update playYouTubeVideo set speed = ( ...> select speed from playYouTubeVideo where key = '1') ...> where key = '5'; sqlite> commit; sqlite> SELECT * ...> FROM playYouTubeVideo ...> WHERE key BETWEEN '1' AND '5' ...> ; 1|2.0 2|3.0 3|4.0 4|5.0 5|2.0 But I want the last one needs to be 1.0. Also, when the range becomes big, it will be a lot of code. I was hoping I overlooked a smart trick, but I probably need to do it programmatically. -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Cecil Westerhof > Sent: Monday, January 22, 2018 3:30 PM > To: SQLite mailing list > Subject: [sqlite] Can this be done with SQLite > > I have the following table: > CREATE TABLE playYouTubeVideo ( > key TEXTNOT NULL, > speed FLOAT NOT NULL, > > CONSTRAINT key CHECK(length(key) == 1), > CONSTRAINT speed CHECK(TYPEOF(speed) = "real"), > > PRIMARY KEY(key) > ); > > > Say I want to rotate a part: > - The value by key '1' becomes the value by key '2'. > - The value by key '2' becomes the value by key '3'. > - The value by key '3' becomes the value by key '4'. > - The value by key '4' becomes the value by key '5'. > - The value by key '5' becomes the value by key '6'. > > I suppose that I need to do this programmatically, or can this be done > with SQL? > > And optionally also: > - The value by key '1' becomes the value by key '5'. > > -- > Cecil Westerhof > ___ > 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 > -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can this be done with SQLite
I have the following table: CREATE TABLE playYouTubeVideo ( key TEXTNOT NULL, speed FLOAT NOT NULL, CONSTRAINT key CHECK(length(key) == 1), CONSTRAINT speed CHECK(TYPEOF(speed) = "real"), PRIMARY KEY(key) ); Say I want to rotate a part: - The value by key '1' becomes the value by key '2'. - The value by key '2' becomes the value by key '3'. - The value by key '3' becomes the value by key '4'. - The value by key '4' becomes the value by key '5'. - The value by key '5' becomes the value by key '6'. I suppose that I need to do this programmatically, or can this be done with SQL? And optionally also: - The value by key '1' becomes the value by key '5'. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tcl script to get temperature statistics
| 162
44.0 |73
43.5 |40
43.0 |53
42.5 |70
-+--
SubTotal | 398
-+--
42.0 |96
41.5 | 161
41.0 |96
40.5 | 8
-+--
SubTotal | 361
-+--
Total | 1440
-+--
If people are interested I can share some more.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_analyzer for Debian
I was looking at: Tcl'2017 - SQLite's use of Tcl (Richard Hipp) There is talk about the sqlite3_analyzer. But I do not have this on my Debian system. Is that only for Windows? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to store as integer
2017-12-06 15:46 GMT+01:00 Peter Da Silva :
> I’d recommend expr {double($temp)} so the bytecode compiler can optimize
> the expression.
>
> On 12/6/17, 8:40 AM, "sqlite-users on behalf of Cecil Westerhof" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> cldwester...@gmail.com> wrote:
>
> return [expr double(${temp})]
>
Not very important in this case (it is only executed once a minute), but
it is good to pick up good habits, so I changed it. Thanks.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to store as integer
2017-12-06 14:58 GMT+01:00 Simon Slavin :
>
>
> On 6 Dec 2017, at 1:19pm, Cecil Westerhof wrote:
>
> >message NOT NULL
>
> Given thqt you want the "message" stored as REAL, you should be defining
> this column as REAL. This is necessary, though not sufficient.
I only want to store it as a real in this case (or other cases where it is
a real). In other cases I want to store it as text. (Probably most cases.)
But I found the solution. I just rewrote getCPUTemp to:
proc getCPUTemp {} {
if {1 != [regexp -all -line {^CPU_TEMP: +\+([0-9.]+)°C } [exec
sensors] -> temp]} {
error {Did not get exactly a single temperature line from [exec
sensors] output}
}
return [expr double(${temp})]
}
In the return statement I changed the string to double. And who-la it is
stored as real.
I updated the about 3.000 records with:
UPDATE messages
SETmessage = CAST(message AS REAL)
WHERE TYPEOF(message) = 'text'
AND type = 'cpu-temp'
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to store as integer
2017-12-06 13:34 GMT+01:00 Darko Volaric :
> How it's stored depends on how the messages table is defined (which type
> the message column has been given), which you haven't shown, and whether
> storeMessage quotes the message argument when forming the string. My advice
> is to remove any column type and make sure numbers are not quoted when they
> are inserted into the database.
>
I should have added those also:
CREATE TABLE messages(
messageID INTEGER PRIMARY KEY AUTOINCREMENT,
dateTEXT NOT NULL DEFAULT CURRENT_DATE,
timeTEXT NOT NULL DEFAULT CURRENT_TIME,
typeTEXT NOT NULL,
message NOT NULL
)
As you see message does not have any type.
storeMessage:
proc storeMessage {type message} {
db eval "
INSERT INTO messages
(type, message)
VALUES
(:type, :message)
"
}
I changed it to:
proc storeMessage {type message} {
db eval {
INSERT INTO messages
(type, message)
VALUES
(:type, :message)
}
}
But that does not make a difference.
On Wed, Dec 6, 2017 at 11:09 AM, Cecil Westerhof
> wrote:
>
> > I have the following tcl script:
> > #!/usr/bin/env tclsh
> >
> > ### Improvements
> > # Get database from conf-file
> >
> >
> > package require sqlite3
> >CREATE TABLE messages(
> messageID INTEGER PRIMARY KEY AUTOINCREMENT,
> dateTEXT NOT NULL DEFAULT CURRENT_DATE,
> timeTEXT NOT NULL DEFAULT CURRENT_TIME,
> typeTEXT NOT NULL,
> message NOT NULL
> )
> >
> > proc getCPUTemp {} {
> > if {1 != [regexp -all -line {^CPU_TEMP: +\+([0-9.]+)°C } [exec
> > sensors] -> temp]} {
> > error {Did not get exactly a single temperature line from
> [exec
> > sensors] output}CREATE TABLE messages(
> messageID INTEGER PRIMARY KEY AUTOINCREMENT,
> dateTEXT NOT NULL DEFAULT CURRENT_DATE,
> timeTEXT NOT NULL DEFAULT CURRENT_TIME,
> typeTEXT NOT NULL,
> message NOT NULL
> )
> > }
> > return ${temp}
> > }
> >
> > proc storeCPUTemp {} {
> > storeMessage cpu-temp [getCPUTemp]
> > }
> >
> > proc storeMessage {type message} {
> > db eval "
> > INSERT INTO messages
> > (type, message)
> > VALUES
> > (:type, :message)
> > "
> > }CREATE TABLE messages(
> messageID INTEGER PRIMARY KEY AUTOINCREMENT,
> dateTEXT NOT NULL DEFAULT CURRENT_DATE,
> timeTEXT NOT NULL DEFAULT CURRENT_TIME,
> typeTEXT NOT NULL,
> message NOT NULL
> )
> >
> > proc storeSwap {} {
> > storeMessage swap-usage [exec swapon --noheadings --show]
> > }
> >
> > if {$argc != 1} {
> > error "Error: ${argv0} DATABASE"
> > }
> > sqlite db [lindex $argv 0]
> > db timeout 1
> > while {true} {
> > after [expr {1000 * (60 - [clock seconds] % 60)}]
> > set currentMinute [clock format [clock seconds] -format %M]
> > db transaction {
> > storeCPUTemp
> > # At the whole hour we save swap usage
> > if {${currentMinute} == "00"} {
> > storeSwap
> > }
> > }
> > }
> > # Not really necessary because the above loop never ends
> > # But I find this more clear and is robuster against change
> > db close
> >
> > If I enter:
> > SELECT date
> > , message
> > , TYPEOF(message)
> > FROM messages
> > WHERE type = 'cpu-temp'
> >AND date = '2017-12-06'
> >
> > I see that the temperature is saved as text.
> > In the past I had a script like this in Python who would save the
> > temperature as real. What do I need to change to let this script save it
> as
> > real also?
> >
> > --
> > Cecil Westerhof
> > ___
> > 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
>
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to store as integer
I have the following tcl script:
#!/usr/bin/env tclsh
### Improvements
# Get database from conf-file
package require sqlite3
proc getCPUTemp {} {
if {1 != [regexp -all -line {^CPU_TEMP: +\+([0-9.]+)°C } [exec
sensors] -> temp]} {
error {Did not get exactly a single temperature line from [exec
sensors] output}
}
return ${temp}
}
proc storeCPUTemp {} {
storeMessage cpu-temp [getCPUTemp]
}
proc storeMessage {type message} {
db eval "
INSERT INTO messages
(type, message)
VALUES
(:type, :message)
"
}
proc storeSwap {} {
storeMessage swap-usage [exec swapon --noheadings --show]
}
if {$argc != 1} {
error "Error: ${argv0} DATABASE"
}
sqlite db [lindex $argv 0]
db timeout 1
while {true} {
after [expr {1000 * (60 - [clock seconds] % 60)}]
set currentMinute [clock format [clock seconds] -format %M]
db transaction {
storeCPUTemp
# At the whole hour we save swap usage
if {${currentMinute} == "00"} {
storeSwap
}
}
}
# Not really necessary because the above loop never ends
# But I find this more clear and is robuster against change
db close
If I enter:
SELECT date
, message
, TYPEOF(message)
FROM messages
WHERE type = 'cpu-temp'
AND date = '2017-12-06'
I see that the temperature is saved as text.
In the past I had a script like this in Python who would save the
temperature as real. What do I need to change to let this script save it as
real also?
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Good resources for TCL/TK
2017-12-04 14:33 GMT+01:00 Cecil Westerhof :
>
>
> 2017-11-19 23:00 GMT+01:00 jungle boogie :
>
>> Thus said Cecil Westerhof on Sat, 18 Nov 2017 14:43:23 +0100
>>
>>> I found the benefits for TCL/TK. But this is a SQLite mailing list, so
>>> not
>>> the right place to ask questions if it is not connected to SQLite also.
>>> What would be good resources for TCL/TK?
>>>
>>>
>> There's also a pretty active IRC room on freenode, it's #tcl.
>>
>> Let us know how your experiences go with tcl.
>
>
I also made a script to store the values from vmstat:
#!/usr/bin/env tclsh
### Improvements
# Get database from conf-file
package require sqlite3
if {$argc != 1} {
error "Error: ${argv0} DATABASE"
}
sqlite db [lindex $argv 0]
db timeout 1
setinsertVmstat "
INSERT INTO vmstat (
runlength,
-- procs
runable, uninteruptable,
-- memory
swap,free, buffers, cache,
-- swap
swapIn, swapOut,
-- io
blockIn, blockOut,
-- system
interuptsPerSec, contextSwitchesPerSec,
-- cpu
userTime,systemTime,idleTime, waitTime,
stolenTime
) VALUES (
:runLength,
:runable, :uninteruptable,
:swap, :free, :buffers, :cache,
:swapIn, :swapOut,
:blockIn, :blockOut,
:interuptsPerSec, :contextSwitchesPerSec,
:userTime, :systemTime, :idleTime, :waitTime, :stolenTime
);
"
set runLength 60
puts "Using an interval of ${runLength} seconds"
after [expr {1000 * (60 - [clock seconds] % 60)}]
set vmstat [open "|vmstat -n ${runLength}"]
# The first three lines need to be skipped
for {set i 0} {${i} < 3} {incr i} {
gets ${vmstat}
}
while {true} {
lassign [gets ${vmstat}] \
runable uninteruptable \
swapfree buffers cache \
swapIn swapOut \
blockIn blockOut \
interuptsPerSec contextSwitchesPerSec\
userTimesystemTimeidleTime waitTime stolenTime
db eval ${insertVmstat}
}
# Not really necessary because the above loop never ends
# But I find this more clear and is robuster against change
close vmstat
dbclose
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Good resources for TCL/TK
2017-12-04 16:40 GMT+01:00 Gerry Snyder :
> No. One set of braces around the whole list of arguments.
>
That does not work:
exec {swapon --noheadings --show}
couldn't execute "swapon --noheadings --show": no such file or directory
while evaluating {exec {swapon --noheadings --show}}
or:
exec swapon {--noheadings --show}
swapon: unrecognized option '--noheadings --show'
On Dec 4, 2017 8:27 AM, "Cecil Westerhof" wrote:
>
> > 2017-12-04 15:24 GMT+01:00 Gerry Snyder :
> >
> > > It is always a good idea to put the arguments of [expr] in braces. That
> > way
> > > they are byte-compiled.
> > >
> >
> > You mean like:
> > exec {swapon} (--noheadings} {--show}
>
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Good resources for TCL/TK
2017-12-04 15:24 GMT+01:00 Gerry Snyder :
> It is always a good idea to put the arguments of [expr] in braces. That way
> they are byte-compiled.
>
You mean like:
exec {swapon} (--noheadings} {--show}
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Good resources for TCL/TK
2017-11-19 23:00 GMT+01:00 jungle boogie :
> Thus said Cecil Westerhof on Sat, 18 Nov 2017 14:43:23 +0100
>
>> I found the benefits for TCL/TK. But this is a SQLite mailing list, so not
>> the right place to ask questions if it is not connected to SQLite also.
>> What would be good resources for TCL/TK?
>>
>>
> There's also a pretty active IRC room on freenode, it's #tcl.
>
> Let us know how your experiences go with tcl.
I like it very much. It is a bit get used to, but I will manage I think.
;-)
One think I like that global variables are not default exposed in
procedures.
I wrote something to help me choose which (of my about 30) teas I am going
to brew. ;-)
I also wrote a program to store systems statistics in a SQLite database:
#!/usr/bin/env tclsh
### Improvements
# Get database from conf-file
package require sqlite3
proc getCPUTemp {} {
if {1 != [regexp -all -line {^CPU_TEMP: +\+([0-9.]+)°C } [exec
sensors] -> temp]} {
error {Did not get exactly a single temperature line from [exec
sensors] output}
}
return ${temp}
}
proc storeCPUTemp {} {
storeMessage cpu-temp [getCPUTemp]
}
proc storeMessage {type message} {
db eval "
INSERT INTO messages
(type, message)
VALUES
(:type, :message)
"
}
proc storeSwap {} {
storeMessage swap-usage [exec swapon --noheadings --show]
}
if {$argc != 1} {
error "Error: ${argv0} DATABASE"
}
sqlite db [lindex $argv 0]
while {true} {
after [expr 1000 * (60 - [clock format [clock seconds] -format %S])]
set currentSeconds [clock seconds]
db transaction {
storeCPUTemp
# At the whole hour we save swap usage
if {[clock format ${currentSeconds} -format %M] == "00"} {
storeSwap
}
}
}
# Not really necessary because the above loop never ends
# But I find this more clear and is robuster against change
db close
I am open for improvements.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Good resources for TCL/TK
2017-11-18 15:14 GMT+01:00 Eric : > On Sat, 18 Nov 2017 14:43:23 +0100, Cecil Westerhof < > cldwester...@gmail.com> wrote: > > I found the benefits for TCL/TK. But this is a SQLite mailing list, so > not > > the right place to ask questions if it is not connected to SQLite also. > > What would be good resources for TCL/TK? > > > > There is the Usenet group comp.lang.tcl , also accessible through Google > Groups at https://groups.google.com/forum/#!forum/comp.lang.tcl . > > There is also the Tclers' Wiki at http://wiki.tcl.tk/ which is full > of information. You can ask questions by editing them into the "Ask, > and it shall be given" page http://wiki.tcl.tk/37862 . > Thanks, I will look into those. By the way TCL can calculate factorial 995 in less as 4 seconds. Not to bad. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Good resources for TCL/TK
I found the benefits for TCL/TK. But this is a SQLite mailing list, so not the right place to ask questions if it is not connected to SQLite also. What would be good resources for TCL/TK? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Starting with TCL
2017-11-17 13:51 GMT+01:00 Peter Da Silva :
> Sqlite will perform the substitution of Tcl variables in a query. You can
> flag the variable with a ‘$’ or with a ‘:’ (which makes it more like other
> SQL APIs).
>
Yes, I found that. The disadvantage is that you have to have a variable
with the correct name, but I do not mind.
>
> So you can write:
>
> $db eval {
> SELECT Tea
> FROM teaInStock
> ORDER BY LastUsed DESC
> LIMIT :nrToFetch;
> } {
> ...
> }
>
I always want to define my queries in one place. So I have:
proc getTeaList {{limitNr 5}} {
global getTeasToDrinkStr
set teaList []
db eval ${getTeasToDrinkStr} {
And the definition:
set getTeasToDrinkStr "
SELECT *
FROM (
SELECT *
,abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser
FROM teaInStock
ORDER BY LastUsed
)
ORDER BY randomiser + IFNULL(JULIANDAY(LastUsed), 0) ASC
LIMIT:limitNr
"
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Starting with TCL
2017-11-17 12:43 GMT+01:00 Cecil Westerhof :
> I have the following:
> set getLatestTeasStr {
> SELECT Tea
> FROM teaInStock
> ORDER BY LastUsed DESC
> LIMIT5
> ;
> }
>
> But because I want to define the limit at runtime I want to change it to:
> set getLatestTeasStr {
> SELECT Tea
> FROM teaInStock
> ORDER BY LastUsed DESC
> LIMIT?
> ;
> }
>
> In Java I would do something like:
> psSel.setInt(1, nrToFetch);
>
> How do I do this in TCL?
>
You have to work with named parameters in your queries if I understand it
correctly. For updating (which is a bit more interesting) I have now:
set updateLastBrewed "
UPDATE selectRandom
SETlastUsed= DATE()
WHERE description = :teaToBrew
"
And my ‘main’ is:
while {1} {
emptyLines
displayLatestTeas 7
emptyLines 1
set teaToBrew [chooseTea [getTeaList 7]]
if {${teaToBrew} == "#r"} {
continue
}
puts [format "Need to update the database with: %s" ${teaToBrew}]
db eval ${updateLastBrewed}
puts [format "Number of records changed: %d" [db changes]]
break
}
I display the last teas I drank.
Then I choose a tea I am going to brew now.
I display it and update the database and make sure that exactly one record
was modified.
chooseTea:
proc chooseTea {teaList} {
set nrOfTeas [llength ${teaList}]
set i 0
while {${i} < ${nrOfTeas}} {
puts [format "%d: %-30s %-10s %2s" \
[expr ${i} + 1]\
[dict get [lindex $teaList ${i}] Tea] \
[dict get [lindex $teaList ${i}] LastUsed] \
[dict get [lindex $teaList ${i}] Location]]
incr i
}
set refresh 0
while {1} {
puts -nonewline "Which tea: "
flush stdout
gets stdin choice
if {${choice} == "#q"} {
exit
} elseif {${choice} == "#r"} {
return ${choice}
} elseif {(${choice} >= 1) && (${choice} <= ${nrOfTeas})} {
incr choice -1
return [dict get [lindex ${teaList} ${choice}] Tea]
}
puts "Input incorrect."
}
}
getTeaList:
proc getTeaList {{limitNr 5}} {
global getTeasToDrinkStr
set teaList []
db eval ${getTeasToDrinkStr} {
lappend teaList [dict create\
Tea${Tea} \
LastUsed ${LastUsed} \
Location ${Location} \
Randomiser ${Randomiser}]
}
return ${teaList}
}
It is a bit to get used to, but it is not to difficult.
Any tips how things could be done better are welcome of-course.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Starting with TCL
I have the following:
set getLatestTeasStr {
SELECT Tea
FROM teaInStock
ORDER BY LastUsed DESC
LIMIT5
;
}
But because I want to define the limit at runtime I want to change it to:
set getLatestTeasStr {
SELECT Tea
FROM teaInStock
ORDER BY LastUsed DESC
LIMIT?
;
}
In Java I would do something like:
psSel.setInt(1, nrToFetch);
How do I do this in TCL?
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Starting with TCL
In Bash I can use: continue 2 to continue not the current loop, but the loop surrounding it. This does not work in TCL. Is there another way to do this? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Starting with TCL
2017-11-17 9:57 GMT+01:00 Dan Kennedy :
>
> This gives:
>> wrong # args: should be "for start test next command"
>> while executing
>> "for {t last_used loc} $teaChoices {
>> puts $t
>> }
>>
>
> Sorry - [for] should be [foreach].
>
> So with your query as above, you want:
>
> set teaChoices [db eval $getTeasToDrinkStr]
> foreach t $teaChoices {
> puts $t
> }
>
That solved it, yes.
>
> There is something going wrong, because:
>> puts [llength teaChoices]
>> gives:
>> 1
>> while it should give:
>> 5
>>
>
> Missing $ sign. Should be:
>
> puts [llength $teaChoices]
>
> Without the $, it's returning the length of the literal "teaChoices" - one
> element. Not the length of the list contained in the variable named
> "teaChoices".
That was the problem.
I am trying a bit different route:
set teaList []
db eval ${getTeasToDrinkStr} {
lappend teaList [dict create\
Tea${Tea} \
LastUsed ${LastUsed} \
Location ${Location} \
Randomiser ${Randomiser}]
}
set i 0
while {${i} < [llength ${teaList}]} {
puts [format "%d: %-30s %-10s %2s %d" [expr ${i} + 1] \
[dict get [lindex $teaList ${i}] Tea] \
[dict get [lindex $teaList ${i}] LastUsed] \
[dict get [lindex $teaList ${i}] Location] \
[dict get [lindex $teaList ${i}] Randomiser]]
incr i
}
This does what I want (I need the value of Tea to update the database), but
is there a better way?
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Starting with TCL
2017-11-17 9:38 GMT+01:00 Dan Kennedy :
> On 11/17/2017 03:20 PM, Cecil Westerhof wrote:
>
>> The folowing works:
>> db eval ${getTeasToDrinkStr} {
>> puts [format "%-30s %-10s %2s %d" ${Tea} ${Last Used} ${Location}
>> ${Randomiser}]
>> }
>>
>> But I want to reuse what I get, so I tried the following:
>> set teaChoices [db eval ${getTeasToDrinkStr}]
>> foreach tea [array names teaChoices] {
>> puts ${teaChoices}(${tea})
>> }
>>
>> But that does not work. teaChoices is filled, but not as an array. When
>> using:
>> puts ${teaChoices}
>> puts [array size teaChoices]
>>
>> I see in the first line what I expect in teaChoices, but the size is zero.
>> So teaChoices is a string instead of an array. How do I get it filled as
>> an
>> array?
>>
>
> $teaChoices is a Tcl list. Assuming your query is still:
>
> SELECT tea, "last used", location FROM teaInStock;
>
Nope, this one is:
SELECT Tea
FROM teaInStock
ORDER BY "Last Used" DESC
LIMIT5
;
> then $teaChoices contains three elements for each row returned by the
> query. The first of each set of three is the "tea", the second the "last
> used" value and the third the "location". So:
>
> set teaChoices [db eval $getTeasToDrinkStr]
> for {t last_used loc} $teaChoices {
> puts $t
> }
>
> will print the list of teas.
>
This gives:
wrong # args: should be "for start test next command"
while executing
"for {t last_used loc} $teaChoices {
puts $t
}
There is something going wrong, because:
puts [llength teaChoices]
gives:
1
while it should give:
5
> Not sure whether or not you really want an "array". In Tcl, array means
> associative array - a key-value structure like an STL map. A list is a flat
> vector of values, like an STL vector or an array in plain old C.
Nope, I want a list then. (Or maybe a list of arrays.)
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Starting with TCL
The folowing works:
db eval ${getTeasToDrinkStr} {
puts [format "%-30s %-10s %2s %d" ${Tea} ${Last Used} ${Location}
${Randomiser}]
}
But I want to reuse what I get, so I tried the following:
set teaChoices [db eval ${getTeasToDrinkStr}]
foreach tea [array names teaChoices] {
puts ${teaChoices}(${tea})
}
But that does not work. teaChoices is filled, but not as an array. When
using:
puts ${teaChoices}
puts [array size teaChoices]
I see in the first line what I expect in teaChoices, but the size is zero.
So teaChoices is a string instead of an array. How do I get it filled as an
array?
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Starting with TCL
2017-11-17 5:38 GMT+01:00 Cecil Westerhof : > setsqliteVersion [sqlite3 -version] > By the way, I think it is a good idea to amend: https://sqlite.org/tclsqlite.html to show this possibility. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Starting with TCL
2017-11-16 22:20 GMT+01:00 Richard Hipp :
> On 11/16/17, Cecil Westerhof wrote:
> > Is it possible to get the library version before connecting to a
> database?
>
> puts [sqlite -version]
>
Combining yours and Eric's version, I made:
#!/usr/bin/env tclsh
package require sqlite3
setsqliteVersion [sqlite3 -version]
puts ${sqliteVersion}
sqlite db ~/Databases/general.sqlite
In real life I will use it to checkthe SQLite verion if necessary.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Starting with TCL
Is it possible to get the library version before connecting to a database? Now I do the following: #!/usr/bin/env tclsh package require sqlite3 sqlite3 db ~/Databases/general.sqlite puts [db version] But I would prefer to check the version before connecting to a database. Is this possible? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Starting with TCL
2017-11-16 18:44 GMT+01:00 Peter Da Silva :
>
> On 11/16/17, 11:37 AM, "sqlite-users on behalf of Cecil Westerhof" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> cldwester...@gmail.com> wrote:
> > When I use:
> > db eval {SELECT * FROM teaInStock} {
> >puts $Tea, $Location
> > }
>
> puts takes a single string, so you can do {puts “$Tea\t$Location”.
> Arguments are separated by space, comma has no intrinsic meaning, and puts
> takes two arguments: the file handle to write on and the string to print.
> So it’s interpreting “$Tea,” as the name of a file handle.
>
> You probably want something like:
>
> db eval {SELECT * FROM teaInStock} {
> puts [format “%12s %12s %s” $Tea ${Last Used} $Location]
> }
>
This is what I use:
puts [format "%-30s %-10s %2s" $Tea ${Last Used} $Location]
Thanks.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Starting with TCL
I just wanted to start using SQLite with TCL. How can I give a formatted
output?
When I use:
puts [db eval {SELECT * FROM teaInStock}]
I get:
Brandnetel {} 2017-11-16 1 Oolong {} 2017-10-29 2 Goudsbloem {} 2017-10-22
3 Jasmijn …
When I use:
db eval {SELECT * FROM teaInStock} {
puts $Tea
}
I get:
Brandnetel
Oolong
Goudsbloem
Jasmijn
…
When I use:
db eval {SELECT * FROM teaInStock} {
puts $Tea, $Location
}
I get:
can not find channel named "Brandnetel,"
while executing
"puts $Tea, $Location"
invoked from within
"db eval {SELECT * FROM teaInStock} {
puts $Tea, $Location
}"
I would like something like:
Brandnetel 2017-11-16 1
Oolong 2017-10-29 2
Goudsbloem 2017-10-22 3
Jasmijn …
And probably another complication: one of the columns is called: "Last
Used".
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How not to let random be calculated again and again and
2017-11-06 11:11 GMT+01:00 Cecil Westerhof :
> 2017-11-06 10:39 GMT+01:00 Keith Medcalf :
>
>>
>> The easiest way is likely to make the query so that it cannot be
>> flattened by adding an ORDER BY (that does not reference the column
>> containing the non-deterministic function by name -- ie, use only OUTPUT
>> column position indicators (ordinals), not names or aliases). This will
>> require the query planner to use a co-routine for the inner table so that
>> only the values will get passed up to the outer query.
>>
>
It looks like that is not necessary.
I played again a little with it. It could be optimised a bit by sorting on
the date, so it will be almost sorted correctly in the inner sort. I should
just use 3 then.
But I just tried what happens if I order by name and that seems to work OK
also.
I have now:
SELECT *
, Randomiser
, Randomiser
FROM (
SELECT *
,abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser
FROM teaInStock
ORDER BY "Last Used"
)
ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC
LIMIT5
And this gives:
"Goudsbloem""2017-10-22""3" "2""2""2"
"Groene Sencha" "2017-10-29""B6""0""0""0"
"Lemon" "2017-10-24""B2""6""6""6"
"Darjeeling""2017-10-30""5" "0""0""0"
"Ginger Lemon Chai" "2017-10-30""D4""1""1""1"
So you can use the name. Something I prefer vastly above positional.
> Depending on the version of SQLite you are using, which you did not
>> mention.
>>
>> SELECT
>> FROM ( SELECT ...
>> FROM ...
>> ORDER BY 1)
>> ORDER BY ...
>> LIMIT ...
>> ;
>>
>
> That works likes a charm. I now use:
> SELECT "Last Used"
> , Randomiser
> , Randomiser
> , Randomiser + IFNULL(JULIANDAY("Last Used"), 0)
> FROM (
> SELECT *
> ,abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser
> FROM teaInStock
> ORDER BY 1
> )
> ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC
> LIMIT5
>
> And this gives for example:
>
>
> "2017-10-17""2""2""2458045.5"
> "2017-10-20""0""0""2458046.5"
> "2017-10-19""3""3""2458048.5"
> "2017-10-18""6""6""2458050.5"
> "2017-10-19""5""5""2458050.5"
>
> So problem solved. The double order is inefficient, but is only used
> during debugging, which now works. :-D
>
> Thanks.
>
>
> By the I am testing it in “DB Browser for SQLite” which uses 3.15.2.
>
> I am not sure in which programming language the real version will be
> implemented, but it will probably use a version near that one. For example
> my Java uses 3.20.0.
>
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How not to let random be calculated again and again and
2017-11-06 10:39 GMT+01:00 Keith Medcalf :
>
> The easiest way is likely to make the query so that it cannot be flattened
> by adding an ORDER BY (that does not reference the column containing the
> non-deterministic function by name -- ie, use only OUTPUT column position
> indicators (ordinals), not names or aliases). This will require the query
> planner to use a co-routine for the inner table so that only the values
> will get passed up to the outer query.
>
> Depending on the version of SQLite you are using, which you did not
> mention.
>
> SELECT
> FROM ( SELECT ...
> FROM ...
> ORDER BY 1)
> ORDER BY ...
> LIMIT ...
> ;
>
That works likes a charm. I now use:
SELECT "Last Used"
, Randomiser
, Randomiser
, Randomiser + IFNULL(JULIANDAY("Last Used"), 0)
FROM (
SELECT *
,abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser
FROM teaInStock
ORDER BY 1
)
ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC
LIMIT5
And this gives for example:
"2017-10-17""2""2""2458045.5"
"2017-10-20""0""0""2458046.5"
"2017-10-19""3""3""2458048.5"
"2017-10-18""6""6""2458050.5"
"2017-10-19""5""5""2458050.5"
So problem solved. The double order is inefficient, but is only used during
debugging, which now works. :-D
Thanks.
By the I am testing it in “DB Browser for SQLite” which uses 3.15.2.
I am not sure in which programming language the real version will be
implemented, but it will probably use a version near that one. For example
my Java uses 3.20.0.
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof
> >Sent: Monday, 6 November, 2017 01:16
> >To: SQLite mailing list
> >Subject: [sqlite] How not to let random be calculated again and again
> >and
> >
> >I have a query that I use to randomly select a set of records, but an
> >older
> >one should have a higher change and a never used record is selected
> >before
> >a used record. For this I use a query that looks a bit like this:
> >SELECT "Last Used"
> >, Randomiser
> >, Randomiser
> >, Randomiser + IFNULL(JULIANDAY("Last Used"), 0)
> >FROM (
> >SELECT *
> >,abs(random()) / CAST(1.4E18 AS INTEGER) AS
> >Randomiser
> >FROM foo
> >)
> >ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC
> > LIMIT 5
> >
> >But Randomiser is calculated every-time it is used. For example this
> >just
> >gave:
> >"2017-10-20""1""1""2458046.5"
> >"2017-10-18""0""3""2458047.5"
> >"2017-10-19""5""5""2458047.5"
> >"2017-10-17""2""5""2458048.5"
> >"2017-10-20""3""1""2458048.5"
> >
> >Is there a way to generate Randomiser in such a way it is only
> >calculated
> >once pro record?
>
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How not to let random be calculated again and again and
I have a query that I use to randomly select a set of records, but an older
one should have a higher change and a never used record is selected before
a used record. For this I use a query that looks a bit like this:
SELECT "Last Used"
, Randomiser
, Randomiser
, Randomiser + IFNULL(JULIANDAY("Last Used"), 0)
FROM (
SELECT *
,abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser
FROM foo
)
ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC
LIMIT5
But Randomiser is calculated every-time it is used. For example this just
gave:
"2017-10-20""1""1""2458046.5"
"2017-10-18""0""3""2458047.5"
"2017-10-19""5""5""2458047.5"
"2017-10-17""2" "5""2458048.5"
"2017-10-20""3""1""2458048.5"
Is there a way to generate Randomiser in such a way it is only calculated
once pro record?
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
2017-09-07 0:57 GMT+02:00 Simon Slavin : > > > On 6 Sep 2017, at 11:31pm, Cecil Westerhof wrote: > > > 2017-09-07 0:20 GMT+02:00 Richard Hipp : > > > >> On 9/6/17, Cecil Westerhof wrote: > >> > >>> Maybe this is correct, but it is certainly confusing. > >> > >> The constraint check occurs before the implicit conversion. > > > > Should that not be the other way around? > > This is an important point. But I’d say not. Constraint checks are there > to make sure that the programmers are doing the Right Thing, not that the > DBMS is doing the Right Thing. So it’s the source value which is checked, > not the value stored in the database. > > To do it the other way around suggests that SQLite needs to check that > SQLite is doing the Right Thing, which would be a waste of cycles, and a > sign that the developers need to consult a psychiatrist. > I would not see it as database checking, but that is just my opinion. ;-) But it would be a good idea to mention this. I just tested my assumption, but maybe someone else ‘knows’ he only has to check for real. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
2017-09-07 0:36 GMT+02:00 Wolfgang Enzinger :
> Am Thu, 7 Sep 2017 00:28:56 +0200 schrieb Cecil Westerhof:
>
> > 2017-09-07 0:20 GMT+02:00 Wolfgang Enzinger :
>
> >> Add this trigger and everything is fine. ;-)
> >>
> >> CREATE TRIGGER weights_float_force_datatype
> >> BEFORE INSERT ON weights
> >> FOR EACH ROW
> >> BEGIN
> >> INSERT INTO weights(float) VALUES (CAST (new.float AS REAL));
> >> SELECT RAISE(IGNORE);
> >> END
> >>
> >
> > I do not think I should do that.
> > Executing:
> > SELECT CAST("Hello" AS REAL), TYPEOF(CAST("Hello" AS REAL))
> >
> > Gives:
> > "0.0""real"
>
> Depends. ;-) What else do you think CAST("Hello" AS REAL) should be
> converted to?
>
I think the cast itself is not wrong, but if I would use the mentioned
trigger then "Hello" would be inserted as 0.0 instead of generating an
exception.
> Seriously: in case you cannot be sure that only numbers will be inserted
> into this column you should probably expand the trigger with a CASE WHEN
> ... ELSE ... END construct.
>
It seems that:
CONSTRAINT float CHECK(TYPEOF(float) IN ("real","integer"))
is doing what I want.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
2017-09-07 0:05 GMT+02:00 R Smith :
> On 2017/09/06 11:58 PM, R Smith wrote:
>
>> Your CHECK constraint should really find that the value is acceptable
>> when it is either a REAL, OR an INT, because both those types of data
>> satisfies your requirement.
>>
>>
> To be specific, this should work for you:
>
> CREATE TABLE weights(
> float REAL,
> CONSTRAINT float CHECK(TYPEOF(float) IN ("real","int"))
> );
It does not, but this does:
CREATE TABLE weights(
float REAL,
CONSTRAINT float CHECK(TYPEOF(float) IN ("real","integer"))
);
Instead of "int" you need "integer".
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
2017-09-07 0:20 GMT+02:00 Richard Hipp : > On 9/6/17, Cecil Westerhof wrote: > > > > Maybe this is correct, but it is certainly confusing. > > > > The constraint check occurs before the implicit conversion. > Should that not be the other way around? But probably not doable, because there could be code out there that depends on this behaviour. But maybe but a warning somewhere. Because it really got me by surprise. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
2017-09-07 0:20 GMT+02:00 Wolfgang Enzinger :
> Am Thu, 7 Sep 2017 00:15:39 +0200 schrieb Cecil Westerhof:
>
> > 2017-09-07 0:05 GMT+02:00 R Smith :
> >
> >> On 2017/09/06 11:58 PM, R Smith wrote:
> >>
> >>> Your CHECK constraint should really find that the value is acceptable
> >>> when it is either a REAL, OR an INT, because both those types of data
> >>> satisfies your requirement.
> >>>
> >>>
> >> To be specific, this should work for you:
> >>
> >> CREATE TABLE weights(
> >> float REAL,
> >> CONSTRAINT float CHECK(TYPEOF(float) IN ("real","int"))
> >> );
> >
> >
> > But it does not.
> >
> > The strange thing is: when I remove the constraint and do:
> > INSERT INTO testing
> > (float)
> > VALUES
> > (0)
> >
> > The insert is successful of-course.
> > When I then execute:
> > SELECT float, TYPEOF(float)
> > FROM testing
> >
> > I get:
> > "0.0""real"
> >
> >
> > Maybe this is correct, but it is certainly confusing.
>
> Add this trigger and everything is fine. ;-)
>
> CREATE TRIGGER weights_float_force_datatype
> BEFORE INSERT ON weights
> FOR EACH ROW
> BEGIN
> INSERT INTO weights(float) VALUES (CAST (new.float AS REAL));
> SELECT RAISE(IGNORE);
> END
>
I do not think I should do that.
Executing:
SELECT CAST("Hello" AS REAL), TYPEOF(CAST("Hello" AS REAL))
Gives:
"0.0""real"
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
2017-09-07 0:05 GMT+02:00 R Smith :
> On 2017/09/06 11:58 PM, R Smith wrote:
>
>> Your CHECK constraint should really find that the value is acceptable
>> when it is either a REAL, OR an INT, because both those types of data
>> satisfies your requirement.
>>
>>
> To be specific, this should work for you:
>
> CREATE TABLE weights(
> float REAL,
> CONSTRAINT float CHECK(TYPEOF(float) IN ("real","int"))
> );
But it does not.
The strange thing is: when I remove the constraint and do:
INSERT INTO testing
(float)
VALUES
(0)
The insert is successful of-course.
When I then execute:
SELECT float, TYPEOF(float)
FROM testing
I get:
"0.0" "real"
Maybe this is correct, but it is certainly confusing.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
2017-09-06 23:58 GMT+02:00 R Smith : > On 2017/09/06 11:37 PM, Cecil Westerhof wrote: > >> But should in the first case the 0 not be cast to a 0.0? >> > > What makes you believe SQLite should massage the data into specific types > for you without you requesting it explicitly? > At https://www.sqlite.org/datatype3.html I read: A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. > In fact, that would consume valuable extra CPU cycles and would definitely > make most of us unhappy. > > Your CHECK constraint should really find that the value is acceptable when > it is either a REAL, OR an INT, because both those types of data satisfies > your requirement. Because of the above I thought it not necessary. I could change it of-course. The only thing could be if they enter am integer, then maybe they made a mistake. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
2017-09-06 23:49 GMT+02:00 Jens Alfke : > > > > On Sep 6, 2017, at 2:37 PM, Cecil Westerhof > wrote: > > > > But should in the first case the 0 not be cast to a 0.0? > > No, SQLite ignores column type declarations. There's a whole article on > the website on SQLite's dynamic approach to data typing. > OK, I have to look into that then. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Should the INTEGER not be cast to a REAL
I defined the following table: CREATE TABLE weights( float REAL, CONSTRAINT float CHECK(TYPEOF(float) = "real") ); I try the following insert: INSERT INTO testing (float) VALUES (0) But this gives: CHECK constraint failed: float When I try this insert: INSERT INTO testing (float) VALUES (0.0) I get: Query executed successfully But should in the first case the 0 not be cast to a 0.0? I do this in DBBrowser which uses 3.15.2. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sharing data between desktop and Android
2017-09-06 12:54 GMT+02:00 Andy Ling : > > Cecil Westerhof wrote: > > > I am thinking about writing some Android applications. I would like to > > > share data between the phone (or tablet) and de desktop. What is the > best > > > way to do this? In a way that would also be convenient for other > people. > > > > I use an Android app that does this. It has a companion PC app that lets > > you backup and modify the underlying sqlite database. It transfers the > > database between Android and PC using a web link. There is a "sync > > to PC" menu on the app that opens the connection. > > > > > Can you share information about it? > > It's not my app, I just use it. It's called MobileSheetsPro. There's more > information > here > > http://www.zubersoft.com/mobilesheets/ > > Mike, the guy that wrote it, is extremely helpful. So if you contact him > I'm sure > he'll help you out. > OK, I will do that. Thanks. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sharing data between desktop and Android
2017-09-06 12:01 GMT+02:00 Andy Ling : > Cecil Westerhof wrote: > > I am thinking about writing some Android applications. I would like to > > share data between the phone (or tablet) and de desktop. What is the best > > way to do this? In a way that would also be convenient for other people. > > I use an Android app that does this. It has a companion PC app that lets > you backup and modify the underlying sqlite database. It transfers the > database between Android and PC using a web link. There is a "sync > to PC" menu on the app that opens the connection. > Can you share information about it? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a performance difference between COUNT(*) and COUNT(name)
2017-09-05 23:11 GMT+02:00 Simon Slavin : > > > On 5 Sep 2017, at 9:21pm, Cecil Westerhof wrote: > > > I want to know the number of teas I have in stock. For this I use: > > SELECT COUNT(Tea) > > FROM teaInStock > > > > Tea cannot be NULL, so this is the same as: > > SELECT COUNT(*) > > FROM teaInStock > > > > But I find the first more clear. > > I almost always see the second variant. Is this because it is more > > efficient, or are people just ‘lazy’? > > Your guess is right ! > > To do COUNT(*) SQLite has to retrieve all the rows. > To do COUNT(value) has to retrieve all the rows and test the value of each > row to make sure it is not NULL. > > Also, SQLite has a specific piece of code which makes COUNT(*) more > efficient than counting the values. However, unless you have a big > database, the difference for your case may be small. If you find > COUNT(Tea) easier to understand perhaps you should use that one. > I will keep using COUNT(Tea) then, but keep in the back of my mind that I maybe should change that if a table becomes big. Thanks. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting number of rows with NULL
2017-09-05 22:46 GMT+02:00 R Smith : > > > On 2017/09/05 10:13 PM, John McKown wrote: > >> On Tue, Sep 5, 2017 at 3:00 PM, Stephen Chrzanowski >> wrote: >> >> On behalf of Cecil, the fault in that logic is that count(*) returns the >>> number of rows in that table, not whether there is a hole "somewhere: >>> Your >>> query will either return 1, or, 0. >>> >>> >>> I either don't understand you, or I am doing something wrong. I used "a" >> instead of "Last Used" in my example because I'm just plain lazy. >> > > I think Stephen assumed the OP meant that he wanted to know the number of > "holes" (i.e. skipped IDs) in the DB, which I thought was obviously not > what the OP wanted, until the OP mentioned his Tea column cannot have NULL > values, so now I'm slightly lost and Stephen's interpretation seems more > sensible, but then the OP said that Igor's solution is working for him, > which should only work if there are NULL values... so yes, I am very much > confused as to exactly what goes on now. The Tea column cannot have NULL, but "Last Used" can. :-D I want to know how many teas are not used yet. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting number of rows with NULL
2017-09-05 22:09 GMT+02:00 Igor Tandetnik : > It's possible I misunderstand what it is the OP is trying to do. But in > any case, the query I show is equivalent to the query the OP has shown > (which, apparently, does what they want), except formulated in a less > roundabout way. Yes, your query is the better one. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
