HackW/E@LinuxHotel oh boy, `spatialite` doesn't use a geographic index? You need to do this complicated nested queries to use it?
Maybe I need to switch to #postgis
#sqlite #gis #spatialite
Recent searches
Search options
#sqlite
2 posts2 participants0 posts today
Ok. Just culled ~20,000 of 25,000 #tumblr posts accumulated via #RSS over a month. I've gotta get to work on my own project applying basic statistics to this stuff. Think "reverse chronological + $all_your_own_filtering_and_sorting_and_bucketing". Not sure if I should do that within the confines of a #thunderbird add-on, or try some other approach. I really, really want to be able to use #sql for this stuff. It's a natural fit. But #sqlite seems to be a no-go for #WebExtensions. There is only #IndexedDB, which, in my limited experience with it, is absolute garbage to work with.
Continued thread
Replied to argv minus one
@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.
Replied to argv minus one
@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
I am disappointed to find that, with #SQLite, an INSERT statement with both ON CONFLICT DO NOTHING and RETURNING clauses won't return anything if there is a conflict.
I have a table that's basically just for interning frequently-used strings. It has a numeric ID column and a unique text column, and I want to get the ID for a given string, inserting if necessary.
With this RETURNING behavior, that has to be done in two steps, not one. 
DIY AI Butler Is Simpler and More Useful Than Siri - [Geoffrey Litt] shows that getting an effective digital assistant that’s tailored ... - https://hackaday.com/2025/04/15/diy-ai-butler-is-simpler-and-more-useful-than-siri/ #artificialintelligence #digitalassistant #aiassistant #sqlite #diy #llm
Hackaday · DIY AI Butler Is Simpler And More Useful Than Siri[Geoffrey Litt] shows that getting an effective digital assistant that’s tailored to one’s own needs just needs a little DIY, and thanks to the kinds of tools that are available today, …
As a stress test I loaded 8M bookmarks into a little SQLite backed bookmark saving/sharing web app I'm building. The slowest part turned out to be the `SELECT count(1) FROM bookmarks` query that's used as part of the pagination UI. Not what I was expecting but known/expected behaviour from what I've read. It takes 400ms on Snapdragon X Elite CPU, which is fine in practice—I'd be surprised if anyone actually stored more than 100k items in it.
GitHub - splitbrain/meh: Meh... another comment system https://github.com/splitbrain/meh #alternative #integration #OpenSource #comments #mastodon #node.js #website #GitHub #sqlite #static #blog #PHP
GitHubGitHub - splitbrain/meh: Meh... another comment systemMeh... another comment system. Contribute to splitbrain/meh development by creating an account on GitHub.
Continued thread
Brought to you by Mozilla #Firefox using dozens of #SQLite databases and clearly not batching the transactions.
Did no one stop to think for a second of the performance implications of dozens of close-together fsyncs|dsyncs on HDDs?
Also, why in the world so many databases? It's completely unnecessary and even though they *could* be used as attached databases, that means one can only use them in non-WAL mode (yay, blocking readers) to maintain proper global transactionality (https://sqlite.org/lang_attach.html https://sqlite.org/wal.html).
Did they even read the docs before doing this nonsense?
Did no one stop to think for a second of the performance implications of dozens of close-together fsyncs|dsyncs on HDDs?
Also, why in the world so many databases? It's completely unnecessary and even though they *could* be used as attached databases, that means one can only use them in non-WAL mode (yay, blocking readers) to maintain proper global transactionality (https://sqlite.org/lang_attach.html https://sqlite.org/wal.html).
Did they even read the docs before doing this nonsense?
sqlite.orgATTACH DATABASE
How-To Geek: How I Track My Music Collection With an Easy SQLite Database. “If you’re a music fan, you probably have shelves of records or CDs. How do you keep track of them? You could use a database, but aren’t they complicated to set up? SQLite is a powerful tool that lets you set up SQL databases without a server. It’s surprisingly easy to set up. Here’s how I did it, despite having a […]
searchcode.com’s SQLite database is probably 6 terabytes bigger than yours
boyter.orgsearchcode.com’s SQLite database is probably 6 terabytes bigger than yours | Ben E. C. Boyter
The .spd file format for drawings from #Supernote Atelier ...
... is a #sqlite database with PNG tiles?!
Go and Rust for the Small Web: How We Build Faster, Simpler Apps
limeleaf.coopGo and Rust for the Small Web: How We Build Faster, Simpler Apps — Limeleaf Worker Collective
Sometimes my hobby is examining iOS apps and macOS application to see how they store their data and then if it's a SQLite database I dig into it and see what's in there and how I can get it out...
#Development #Launches
SQL Noir · A game to learn SQL by solving crimes https://ilo.im/162ciw
_____
#OpenSource #Game #Database #SQL #MySQL #SQLite #PostgreSQL #Npm #WebDev #Backend
ilo.imSQL Noir - A Detective SQL GameSolve crimes and mysteries using SQL queries in this detective-themed SQL learning game.
A nice tool to explore SQLite databases page-by-page, the way they're stored on disk and the way SQLite sees them:
GitHubGitHub - QuadrupleA/sqlite-page-explorer: Visual tool to explore SQLite databases page-by-page, the way they're stored on disk and the way SQLite sees them.Visual tool to explore SQLite databases page-by-page, the way they're stored on disk and the way SQLite sees them. - QuadrupleA/sqlite-page-explorer
SQLite for Everyone!
My talk from the 2024 Research Software Engineering conference is up on YouTube.
It's a 25-minute intro to the benefits relational databases and #SQL. The target audience is "past me", from when I was working with data as a research scientist and learning #python.
It includes a tour of useful tools including #sqlitebrowser, @qgis, @datasette and #etlhelper.
youtu.be- YouTubeEnjoy the videos and music you love, upload original content, and share it all with friends, family, and the world on YouTube.
Continued thread
Error: stepping, database or disk is full (13)
Hngggghn. #sqlite is giving me trouble. All I want is a SELECT user_agent, COUNT(*) FROM (...) GROUP_BY user_agent, but if the table is too large, it doesn't let me.
I might need to create a temporary table.