SQLite Forum

Timeline
Login

50 most recent forum posts by user CecilWesterhof

2021-06-08
11:32 Reply: Feature request: Stored Procedures (artifact: a720247d2f user: CecilWesterhof)

It is also very handy for having to maintain it at one place. When several programs do at some part the same it would be better to maintain stored procedures as to have to maintain al the programs.

2021-04-12
13:10 Post: Getting Minimum, Maximum and Current Weight (artifact: da1e7eea39 user: CecilWesterhof)

One of my new years resolutions was that I wanted to loose significant weight. To help me with this I created the following table:

    CREATE TABLE dayValues (
        measureDate DATE  DEFAULT CURRENT_DATE NOT NULL,
        weight      REAL  NOT NULL,

        PRIMARY KEY   (measureDate)
    );

Interesting information would be when I first hit my lowest weight and last my highest weight.
For this I created the following query:

    WITH vals AS (
        SELECT MIN(weight) AS minimum
        ,      MAX(weight) AS maximum
        ,      DATE()      AS today
        FROM   dayValues
    )
    SELECT 'Minimum'        AS 'Type'
    ,      MIN(measureDate) AS 'Measure Date'
    ,      minimum          AS 'Weight'
    FROM   dayValues
    ,      vals
    WHERE  weight = minimum
    UNION ALL
    SELECT 'Maximum'
    ,      MAX(measureDate)
    ,      maximum
    FROM   dayValues
    ,      vals
    WHERE  weight = maximum
    UNION ALL
    SELECT 'Today'
    ,      today
    ,      weight
    FROM   dayValues
    ,      vals
    WHERE  measureDate = today
    ;

Is that a reasonable query, or can it be improved upon?

10:11 Reply: UNION Sorts on First Field (artifact: 32bcdc60f0 user: CecilWesterhof)

With UNION ALL it goes correct. But it can go haywire any-time?

I do not know of an ORDER BY clause that will give the correct sequence. ;-)
But at the moment it works.
When it does not any-more I could prepend '1 ', '2 ' and '3 ' to them.

09:28 Post: UNION Sorts on First Field (artifact: 5aa76a510d user: CecilWesterhof)

When I execute:

    SELECT 'Minimum'
    UNION
    SELECT 'Maximum'
    UNION
    SELECT 'Today'
    ;

I get:

    Maximum
    Minimum
    Today

While I would expect:

    Minimum
    Maximum
    Today

Is this a bug or the way it should work?
Is it possible to circumvent the sort?

2021-02-08
18:23 Reply: How to create the views (artifact: 6e89a7c184 user: CecilWesterhof)

I should have thought a little longer before posting. :'-(

This are the views I have created:

CREATE VIEW monthValuesExt AS
SELECT *
,      striveWeight - realWeight AS extraLoss
FROM   monthValues
WHERE  striveWeight > 0
   AND realWeight IS NOT NULL
;
CREATE VIEW weekValuesExt AS
SELECT curWeek.*
,      prevWeek.weight                            AS oldWeight
,      ROUND(prevWeek.weight - curWeek.weight, 1) AS lost
FROM   weekValues AS curWeek
JOIN   weekValues AS prevWeek ON prevWeek.week = curWeek.Week - 1
;

I 'need' two tables. I measure at the first day (Sunday) of every week and the first day of the month. (It looks like the hour administration I had at several companies.)

For me it is probably not necessary, but I look into if the second few can be updated. (Previous week max of lower as current and dived by the number of weeks between them. But that is an exercise for when I am bored.)

I should be finished in half a year, so just week number (1 -27) should be good enough.

15:24 Post: How to create the views (artifact: c04a71938d user: CecilWesterhof)

Background Information

Like a lot of other people I did get some extra kilos during the lockdown. And I already had to many.

I set some quite rigorous goals to get rid of those.

Today I was thinking: SQLite can help me.

The Tables I Created

CREATE  TABLE monthValues (
    month           INTEGER PRIMARY KEY,
    striveWeight    REAL    NOT NULL,
    realWeight      REAL
);
-- striveWeight is -1 in the first month, because that is when I started.
-- realWeight is only filled when the month has started yet.


CREATE  TABLE weekValues (
    week    INTEGER PRIMARY KEY,
    weight  REAL    NOT NULL
);

The Views I Would Like

I would like a view on monthValues that shows how much of I am. That would be:

    realWeight - striveWeight

but there is a catch: I do not want a value when striveWeight = -1, or when realWeight = NULL.

Can this be done?

I also would like a view that shows how many kilos I lost in a week. That would be:

    weight_from_previous_week - weight_from_this_week

Can this be done?

14:57 Reply: Recover Database (artifact: 7bbcc812bd user: CecilWesterhof)

There was nothing in the original file yet. So it seems that when you do something an empty file is created of 8 KB and copied to the -journal file.

I did a quick look and it seemed they where both mostly empty.

14:35 Reply: Recover Database (artifact: afc56b78bb user: CecilWesterhof)

I was afraid of that. But luckily it was not to much work. I need to learn to use 'Write Changes' more often.

Just curious: why is there data written to those two files?

11:01 Post: Recover Database (artifact: 62e3571c62 user: CecilWesterhof)

I started creating a database with 'DB Browser for SQLite'. During this the program crashed.

There is a .sqlite file (I end all SQLite databases with .sqlite) and a .sqlite-journal file.

Would it be possible to recover the database?

The .sqlite file is not empty, but when I open it with sqlite3 .tables and .schema do not return anything.

2021-01-29
16:58 Reply: Getting rid of double DATE() (artifact: c8dafb5222 user: CecilWesterhof)
Thanks. Then I leave it as it is,
11:26 Reply: Getting rid of double DATE() (artifact: 7d55d00548 user: CecilWesterhof)
This goes wrong when there is no record for today. Rewrote it to:
    WITH now(today)
    AS (
        SELECT DATE('now', 'localtime')
    ),
    cte(today, idx)
    AS (
        SELECT (SELECT today FROM now)
        ,      IFNULL(MAX(lastUsedIdx), 0) + 1
        FROM   selectRandom, now
        WHERE  lastUsed == today
    )
    UPDATE selectRandom
    SET    (lastUsed, lastUsedIdx) = (SELECT today, idx FROM cte)
    WHERE  description = :teaToBrew

Or would it be better to use:
        FROM   selectRandom
        WHERE  lastUsed == (SELECT today FROM now)
2021-01-28
13:16 Reply: No more moderation? (artifact: 8b1461b0fa user: CecilWesterhof)
Yes, I find the forum better as the mailing list.
2021-01-27
09:43 Reply: Not jumping to last entered message in a thread (artifact: 5919159390 user: CecilWesterhof)
I was afraid that it would be a can of worms.

But an option would be that default you get the last entered message, but that an user could set in his user options that he wants to be at the start of the thread.
I for one would override the default.
09:39 Reply: No more moderation? (artifact: 2e83b320e6 user: CecilWesterhof)
Thanks, that explains it.
08:57 Post: No more moderation? (artifact: da0df9d9e7 user: CecilWesterhof)
In the past when I posted something it needed to be moderated. Lately when I post something it is visible immediately. Is moderation cancelled?

It is not a complaint, I am just curious.
08:53 Post: Not jumping to last entered message in a thread (artifact: 3c6f1ec3f9 user: CecilWesterhof)
When I see a thread that I think is interesting and open it, it always goes to the last entered message. Especially when I did not read any of it, I do not find it helpful.
Is there an option to change it? Could it otherwise be made, or is that a can of worms?
2021-01-26
05:59 Reply: Getting rid of double DATE() (artifact: 408dcb835a user: CecilWesterhof)
I probably should find a better name for the table now, or its field. I do not like that the name of the table and the field is the same.
Maybe rename the field to today. (I think it will always be current day.)
2021-01-25
14:03 Reply: Getting rid of double DATE() (artifact: 4299b8b63a user: CecilWesterhof)
That was not the intention. (Four times.)
Busy with other things: I will look into it later.

I should at last start exploring EXPLAIN. Just need to bite the bullet.

It is maybe not important for the execution time, but it never hurts to do things clean. That increases the change I will do it clean when it is important.
2021-01-24
22:51 Reply: Getting rid of double DATE() (artifact: 9e945ab566 user: CecilWesterhof)
Well I find clearness of code more important as efficiency. (That is why I still tweaked it a bit.)
But could the execute twice be circumvented?
22:47 Reply: Getting rid of double DATE() (artifact: 2a961cdf4f user: CecilWesterhof)
This is a bit better I think:
    WITH cte AS (
        SELECT DATE('now', 'localtime')        AS now
        ,      IFNULL(MAX(lastUsedIdx), 0) + 1 AS idx
        FROM   selectRandom
        WHERE  lastUsed == now
    )
    UPDATE selectRandom
    SET    lastUsed    = (SELECT now FROM cte)
    ,      lastUsedIdx = (SELECT idx FROM cte)
    WHERE  description = :teaToBrew
21:18 Reply: Getting rid of double DATE() (artifact: 1355e984e7 user: CecilWesterhof)
CTE to the rescue:
    WITH now AS (
	    SELECT DATE('now', '+1 day', 'localtime') AS val
	)
    UPDATE selectRandom
    SET    lastUsed = (SELECT val FROM now), 
           lastUsedIdx = (
		SELECT IFNULL(MAX(lastUsedIdx), 0) + 1
        FROM   selectRandom
        WHERE  lastUsed == (SELECT val FROM now)
    ) 
    where description == 'White Tea'

I need to use the IFNULL instead of the double MAX.
20:56 Reply: Getting rid of double DATE() (artifact: f782cd694c user: CecilWesterhof)
Thank you. I am using Debian and they do not have that in backports (yet).
Lets look if that can be changed.

On the other hand: it is not terrible. Especially because I now know that the two date calls cannot give a different value. But it would still nice to have.
19:12 Reply: Getting rid of double DATE() (artifact: 3304058b20 user: CecilWesterhof)
I did not know that the value could not be different. Good to know.

It does not work.

The definition of the table is:
    CREATE TABLE "selectRandom" (
	`selectRandomID`    INTEGER PRIMARY KEY AUTOINCREMENT,
	`description`	    TEXT NOT NULL UNIQUE,
	`comment`	    TEXT,
	`lastUsed`	    TEXT,
        `lastUsedIdx`       INTEGER,
        `stockTypeID`       INTEGER,
        `locationID`        INTEGER,

	FOREIGN KEY(`stockTypeID`) REFERENCES `stockTypes`(`stockTypeID`),
	FOREIGN KEY(`locationID`) REFERENCES `locations`(`locationID`)
)

When I execute (in DB Browser For SQLite):
    UPDATE selectRandom
    SET    lastUsed = now, 
           lastUsedIdx = (
        SELECT MAX(MAX(lastUsedIdx), 0) + 1
        FROM   selectRandom
        WHERE  lastUsed == now
    ) 
    FROM (
        SELECT DATE('now', 'localtime') as now
    ) 
    where description == 'White Tea'

I get:
near "FROM": syntax error: UPDATE selectRandom
    SET    lastUsed = now,
           lastUsedIdx = (
        SELECT MAX(MAX(lastUsedIdx), 0) + 1
        FROM   selectRandom
        WHERE  lastUsed == now
    )
    FROM
15:47 Post: Getting rid of double DATE() (artifact: a900de4ddc user: CecilWesterhof)
I have a tcl program where I set a variable like this:
set updateLastBrewed "
    UPDATE selectRandom
    SET    lastUsed    = DATE('now', 'localtime')
    ,      lastUsedIdx = (
        SELECT IFNULL(MAX(lastUsedIdx), 0)
        FROM   selectRandom
        WHERE  lastUsed = DATE('now', 'localtime')
    ) + 1
    WHERE  description = :teaToBrew
"

This variable I later use to update a table.
But it contains two times:
    DATE('now', 'localtime')

That is maybe less efficient, but I do not care about that. There are two potential problems:
- If I change what I put into lastUsed, I have to change it in both places.
- When the statement is executed just when the day changes, they could give different results.

Is there a neater way to do this?
2021-01-01
16:09 Reply: Checking database took about 350 more time as normal (artifact: ed9281dcd9 user: CecilWesterhof)
Nope: it is on a Debian system.
16:08 Reply: Checking database took about 350 more time as normal (artifact: 94e8e284e7 user: CecilWesterhof)
I can, but it is not very useful, because it only happened that one time. I am running it for about a year in the night batch. There are difference between the execution times, but normally not more as a factor of four. So this was really a strange instance.

I think I keep an eye on it, but for the moment think about it as a fluke.
11:57 Reply: Checking database took about 350 more time as normal (artifact: f3da0324ff user: CecilWesterhof)
It is larger as the others, but not unusually much. Besides afterwards I did not get it any-more.

Everyday the same number and type of records is added. So unusually fragmented seems also not likely.

The checks on the databases before and after took the same amount of time. So I do not think it would be I/O bandwidth.

That seems to indicate I have a problem with my disk. :'-(
03:08 Post: Checking database took about 350 more time as normal (artifact: 81ee52e252 user: CecilWesterhof)
I have a script that I use in crontab to check 26 databases. Normally this takes about 20 seconds. One particular database takes the most time: about ten seconds.

A few nights ago this database took almost two hours to be checked. The other databases needed the usual time. The nights after this everything was back to normal.

I check the following pragmas:
    INTEGRITY_CHECK
    FOREIGN_KEY_CHECK
    JOURNAL_MODE

And I use the following tcl proc to check if the database is locked (it was not):
    proc checkLocked {database} {
        if {[catch {db eval "BEGIN IMMEDIATE"} SQLError]} {
            if {${SQLError} ne "database is locked"} {
                error "UNEXPECTED ERROR: ${SQLError}"
            }
            puts "The database ${database} is locked."
        } else {
            db eval "ROLLBACK"
        }
    }

What could be the reason of this?
Is it to worry about?
2020-12-25
11:38 Post: Check date when not null (artifact: fefb83b658 user: CecilWesterhof)
Part of my table definition is:
    created   TEXT DEFAULT CURRENT_DATE() NOT NULL,
    resolved  TEXT DEFAULT NULL,

    CONSTRAINT created CHECK(created = date(strftime('%s', created), 'unixepoch')),


In this way I know that in created is always a correct date.
When resolved is not null, it has to also be a correct date and beside that it has to be greater as created. How should I create this constraint?
11:18 Reply: Interesting question: Why aren't you using SQLite more? (artifact: 5106b1bd20 user: CecilWesterhof)
Because I cannot.
For my own code SQLite is the only database I use.
In the corporate world I have not enough leverage to change the database used.
2020-12-06
16:40 Reply: How to delete a thread before it is published (artifact: 2d85d29445 user: CecilWesterhof)
Ah, learning all the time.

I am always using Plain Text.
This is a reason to learn Markdown at last.
16:38 Post: Is there a way to get these constraints (artifact: 78182c261f user: CecilWesterhof)
I created a table with triggers. And it does mostly what I want. But I would like to have two constraints, but it seems it is not possible.

I have the following table:
CREATE TABLE results (
    resultID    INTEGER PRIMARY KEY AUTOINCREMENT,
    dateStr     TEXT    NOT NULL,
    weekNr      INTEGER, -- NOT NULL
    result      INTEGER NOT NULL,

    CONSTRAINT  date    CHECK(dateStr = date(strftime('%s', dateStr), 'unixepoch')),
    -- CONSTRAINT  weekNr  CHECK(TYPEOF(weekNr)  = 'integer')
    CONSTRAINT  result  CHECK(TYPEOF(result)  = 'integer')
)

And the following triggers:
CREATE TRIGGER resultsInsert AFTER INSERT ON results BEGIN
    UPDATE results
    SET    weekNr = STRFTIME('%W', results.dateStr)
    WHERE  rowId  = NEW.RowId;
END

CREATE TRIGGER resultsUpdateDateStr AFTER UPDATE OF dateStr, weekNr ON results BEGIN
    UPDATE results
    SET    weekNr = STRFTIME('%W', results.dateStr)
    WHERE  rowId  = RowId;
END


I would like to have weekNr NOT NULL and the constraint on weekNr enabled. And only supply dateStr and result, not weekNr.


While writing this post I had a few ideas. I can enable the NOT NULL part if I also set the DEFAULT on -1.
It is not ideal, but acceptable.

But I cannot enable the constraint on weekNr. The default -1 is an integer as is the generated weekNr, but when enabled I keep getting:
    Error: CHECK constraint failed: weekNr

Can this be solved?

I am using 3.24.0.
16:12 Reply: How to delete a thread before it is published (artifact: 755b3e2f1f user: CecilWesterhof)
Ah, I understand. Thanks.

Something else: you did quote my post. How are you doing that? I often want to do that also.
16:10 Reply: Create view with best result pro week (artifact: b2dbac9417 user: CecilWesterhof)
I solved it with triggers. The week numbers are for weeks starting on Monday instead of Sunday, but I can live with that.

I created the following table:
CREATE TABLE results (
    resultID    INTEGER PRIMARY KEY AUTOINCREMENT,
    dateStr     TEXT    NOT NULL,
    weekNr      INTEGER, -- NOT NULL
    result      INTEGER NOT NULL,

    CONSTRAINT  date    CHECK(dateStr = date(strftime('%s', dateStr), 'unixepoch')),
    -- CONSTRAINT  weekNr  CHECK(TYPEOF(weekNr)  = 'integer')
    CONSTRAINT  result  CHECK(TYPEOF(result)  = 'integer')
)

There are two things I do not like, but that is for a new thread.


And I created the following triggers:
CREATE TRIGGER resultsInsert AFTER INSERT ON results BEGIN
    UPDATE results
    SET    weekNr = STRFTIME('%W', results.dateStr)
    WHERE  rowId  = NEW.RowId;
END

CREATE TRIGGER resultsUpdateDateStr AFTER UPDATE OF dateStr, weekNr ON results BEGIN
    UPDATE results
    SET    weekNr = STRFTIME('%W', results.dateStr)
    WHERE  rowId  = RowId;
END

Weeknr should not be updated manually, but in case someone does I added it. Better save as sorry. ;-)

This does what I want.

I do not have much data, but when there would be: could I create a index on the year part of dateStr with weekNr?
16:00 Post: How to delete a thread before it is published (artifact: eae7173d2a user: CecilWesterhof)
I had created a thread with a question about triggers. Later I found the answer myself. I tried to delete the thread. (There is a button for it.) But it does not work.
Am I doing something wrong?
14:28 Reply: Create view with best result pro week (artifact: b693037839 user: CecilWesterhof)
For the moment I have as view:
CREATE VIEW weekResults AS
SELECT *
,      STRFTIME('%W', dateStr) as weekNr
FROM   results
;

And use the following select:
SELECT   SUBSTR(dateStr, 1, 4) AS year
,        weekNR
,        MIN(result)           AS bestResult
FROM     weekResults
GROUP BY year
,        weekNr
;

At the moment I do not use weekResults. Would it be more efficient to merge the select into the view, or is that not a big win?
13:43 Reply: Create view with best result pro week (artifact: c4c50229db user: CecilWesterhof)
Computed columns is added in 3.31. Mine is to old.
I can use a view, or triggers.
I will start with the view and after that look into triggers.
13:38 Reply: Create view with best result pro week (artifact: d0b9d0a91d user: CecilWesterhof)
Still goes wrong. Maybe my version of SQLite is to old. :'-(

The CREATE VIEW in another of your replies seems to work.
2020-12-05
19:46 Reply: Create view with best result pro week (artifact: 71b6220458 user: CecilWesterhof)
Looks very promising.
Would it not be better to use integer?
There is something wrong with the syntax. I get:
    Error: near "as": syntax error

I am using SQLite 3.27.2.
17:43 Reply: Create view with best result pro week (artifact: 25e43316d1 user: CecilWesterhof)
Last week (there is only data on workdays) was 2020-11-30 - 2020-12-04.
Best is the lowest value. (It is a ranking.)

The last week I had the values: 202, 203, 60, 61, 27, 102, 24, 29.
So best for week 49 would be 24.
15:10 Post: Create view with best result pro week (artifact: 80bdee0e8f user: CecilWesterhof)
I am going to use a table like the following:
CREATE TABLE results (
    resultID    INTEGER PRIMARY KEY AUTOINCREMENT,
    date        TEXT    NOT NULL,
    result      INTEGER NOT NULL,

    CONSTRAINT  date    CHECK(date = date(strftime('%s', date), 'unixepoch')),
    CONSTRAINT  result  CHECK(TYPEOF(result)  = 'integer')
)

I would like to see the best result pro week. Is it possible to create a VIEW that looks like:
CREATE TABLE bestResults (
    year    INTEGER NOT NULL,
    week    INTEGER NOT NULL,
    best    INTEGER NOT NULL,

    PRIMARY KEY(year, week)
)

Or would I be better of filling a real table once a week with the best value from the last week?
2020-11-27
22:32 Post: Mail From This List Got In Spam (artifact: 353581b1b0 user: CecilWesterhof)
Luckily it does not happen that often any-more, but this week several of the mails of this list got into spam. (I use SpamAssassin.)
They all got marked with this:
 3.3 RCVD_IN_SBL_CSS        RBL: Received via a relay in Spamhaus SBL-CSS
                            [2600:3c00:0:0:f03c:91ff:fe96:b959 listed in]
                            [zen.spamhaus.org]

Is this useful information, or should I not bother the list with this?
2020-08-22
11:46 Post: A lot of messages are delivered into spam (artifact: f7c224ffeb user: CecilWesterhof)
I had not looked into my spamfolder for some time. (Normally I do that once a week.) I saw that quit a few messages of this list ended up there. Mostly because of:
 3.3 RCVD_IN_SBL_CSS        RBL: Received via a relay in Spamhaus SBL-CSS
                            [2600:3c00:0:0:f03c:91ff:fe96:b959 listed in]
                            [zen.spamhaus.org]

Is that something of the list itself, or is it a problem generated by the sender?
2020-07-07
14:12 Reply: Does in memory db behave the same as a normal db (artifact: aadfa33a6a user: CecilWesterhof)

By using an in memory db it takes a hundredth of the time. A real big improvement. :-D

It pays to think a little. Two little changes and a performance increase 0f 1.600.

12:59 Reply: Does in memory db behave the same as a normal db (artifact: d828635f69 user: CecilWesterhof)

I expected that, but as I 'always' say:
"Better a check to many, as a check to little." ;-)

The D is not important: it is only for the test. And at the moment the multiple client is also not important for me at the moment.
I cross that bridge when I get there/

Thanks for the feedback.

11:12 Post: Does in memory db behave the same as a normal db (artifact: f4b990cf94 user: CecilWesterhof)

I am testing a db application. The best would be to create the database for every test. I timed the creation of a normal test database. This takes about two seconds.
Currently I do not have many tests, so that would not be a problem. But when the number of tests would grow it could be.
I expect that an in memory database would be a lot faster. But can I assume that an in memory database behaves the same as a normal database? Otherwise it would be not very useful.

It is less important as I thought. When using transactions creating the database takes just a eight of a second.

2020-07-06
11:03 Reply: Main Topic and Non Main Topics (Was: Topic and subtopic) (artifact: 4a04dd936d user: CecilWesterhof)
09:22 Reply: Resetting id count (artifact: 54a79f11be user: CecilWesterhof)
You are correct. I thought the problem I initially had was with the id's, but I tried it again and now I do not have a problem.
Strange, but problem solved.
09:19 Reply: Resetting id count (artifact: a88e8e2b70 user: CecilWesterhof)
You are right. Dropping the views and tables and creating them anew does not give a problem. Probably the error I first got was a different one.
Well problem solved. In my case it does not matter, but dropping and recreating I find a much better option as deleting and recreating the database.
2020-07-05
23:17 Reply: Resetting id count (artifact: c7a74a188f user: CecilWesterhof)
If I try:
    SELECT * FROM sqlite_sequence;
I get:
    Error: no such table: sqlite_sequence

I am working with:
    SQLite version 3.27.2

The table sqlite_master does exist.
More ↓