@hn50

@fractaledmind you're everywhere now if #SQLite is in the game, aren't you?

one thing i certainly love about side projects is that it gets us out of the daily perpetual work rabbit whole with endless pre-defined constraints!

for this homelab/ #iott sensor stuff i finally got to play with #sqlite and i’m really loving it. since everything is also running locally (and i finally made sqlite indexes work) this home sensor page for my e-ink display loads *SO FAST* with just a couple of ms

#sqlite3 #database #sqlite I have a strong preference for solutions I can make work with pypika, since there are parts of the codebase that are not conducive to prepared statements and that's what I use for query building. I haven't looked into how to make the indexed by and likely/unlikely work with pypika yet

Side note: I swear I did something earlier today that made the query complete in 40ms (still with the indexed by)and I'm losing my marbles trying to recreate that

@leogdion For #SQLite I also have Lighter, which I think is actually really good: https://github.com/Lighter-swift
And for Core Data I have replicated the #SwiftData macros: https://github.com/Data-swift/ManagedModels/
This by @mattiem might also be relevant to your article: https://mastodon.social/@mattiem/114359442297769327

I had the idea that writing to the SDCard for 250ms every few seconds was a bad idea

That is how long it took for SQLite to insert 1 row in 4 databases, roughly 80ms times 4

(I have four databases because the daily DB, the day before, the day after, and the huge all-time database)

So instead, I added a CSV buffer: incoming readings are appended to a flat file instead of immediately SQLite, and every 5 minutes, one of the nodes takes a hit to commit the CSV into the databases

The CSV append operation takes a few ms, usually 2ms but sometimes 5ms, so I'm happy about this part

The CSV to SQL operation takes about 8s when all 10 nodes are up (50 measurements to 4 databases), so that's a tad slower than I'd want but it happens once every 5 minutes so I guess that's ok?

(every SQL insert now takes about 40ms and there is probably still room for improvement)

And now I can keep a console with watch cat buffer.csv which is great, and more readable than my server logs

Here is a sequence of four batches as a demo (first CSV field is a timestamp, second is the node ID, and the rest are air quality readings from the BME680)

@argv_minus_one
If it is actually written, yes. But #SQLite might detect there is no actual change and not declare the page dirty. And even if it is declared dirty, the row size will not change, so the B-Tree structure will be stable.
The page has to be read anyway, so both the disk cache and the SQLite page cache are primed. Except for hi-speed, hi-volume applications, I would not worry in advance. Premature optimization is the root of all evil.

The daily database files jumped to 2.6 MiB each since I started triple storing, as a lazy fix to my UTC issue

This is still much bigger than I’d like, but at least it’s a steady file size April 7 to April 18 included, so that’s good

And I can always reduce to 5 minute instead of every minute, or something

@argv_minus_one
CREATE TABLE T1 (
id INTEGER PRIMARY KEY NOT NULL
, tx TEXT UNIQUE
);
INSERT INTO T1 (tx)
VALUES ('abc'),('def'),('ghi');
SELECT * FROM T1;
id tx
-- ---
1 abc
2 def
3 ghi
INSERT INTO T1 (tx) VALUES ('def') ON CONFLICT (tx) DO
UPDATE SET id = id + 0
RETURNING id;
id
--
2