A few nice new features in this release. Allowing ORDER BY aggregate functions could be very useful.
Batch size one billion: SQLite insert speedups, from the useful to the absurd
Interesting post on how to speed up batch inserts with SQLite.
Short post about how SQLite sometimes creates indexes just for a single query. The SQLite documentation has a little more information.
It can be worth setting up and monitoring the error log for SQLITE_WARNING_AUTOINDEX warnings as good indication a persistent index should be created.
On today's show, I'm talking to Richard Hipp about surviving becoming core infrastructure for the world. SQLite is everywhere. It's in your web browser, it's in your phone, it's probably in your car, and it's definitely in commercial planes. It's where your iMessages and WhatsApp messages are stored...
Interview with Richard Hipp about the history of SQLite.
SQLite 3.43.0 released
The main changes look to be contentless-delete FTS5 tables and performance enhancements to the query planner and JSON functions.
sqlite-utils - CLI tool and Python library for manipulating SQLite databases
From the homepage:
> This library and command-line utility helps create SQLite databases from an existing collection of data. > > Most of the functionality is available as either a Python API or through the sqlite-utils command-line tool. > > sqlite-utils is not intended to be a full ORM: the focus is utility helpers to make creating the initial database and populating it with data as productive as possible. > > It is designed as a useful complement to Datasette. > > Cleaning data with sqlite-utils and Datasette provides a tutorial introduction (and accompanying ten minute video) about using this tool.
Firefox Profiles work like Chrome profiles, although the UX is a little cliunky without an addon like Profile Switcher.
Make sqlite faster with a connection pool and prepared statements.
The author managed to reach 56,600 req/s!
While written about Go, not all the techniques used are Go specific.
Understanding how the write-ahead log turns your scaling up to 11.
Explains how using WAL allows writes without blocking reads.
FerretDB - MongoDB protocol for SQLite or PostgreSQL
A truly Open Source MongoDB alternative. Contribute to FerretDB/FerretDB development by creating an account on GitHub.
Converts MongoDB protocol to SQL using SQLite or PostgreSQL as the database engine.
Could be useful if wanting to migrate off MongoDB.
High level overview of how SQLite rollback journal works.
sqlpkg - The (unofficial) SQLite package registry.
Registry of SQLite extensions along with a package manager to install them.
Interesting brief overview of SQLite internals.
The ultimate set of SQLite extensions. Contribute to nalgeon/sqlean development by creating an account on GitHub.
A useful collection of extensions for SQLite
I had a very similar issue, the touchpad in my Dell laptop would randomly stop working but I could still click. I couldn’t work out why until one of my keys stopped working. I replaced the keyboard and the touchpad issues disappeared. I suspect the cause was the faulty keyboard not sending the key up event as I had noticed pressing keys sometimes unfroze it. It could also have been a loose connection though.
If you’re using GNOME, you could try disabling the “disable touchpad on while typing” setting: gsettings set org.gnome.desktop.peripherals.touchpad disable-while-typing false
Verify the keyboard is not disabled when pressing keys and see if the issue still happens. If that fixes it then it might be keyboard that’s the problem.
To enable “disable touchpad while typing”: gsettings set org.gnome.desktop.peripherals.touchpad disable-while-typing true
It is indexable but will take time. Google has started indexing Lemmy.world but doesn’t have that many pages yet.
SQLite experimental high-concurrency backend HC-tree
While not ready for production, it looks really promising.
There’s some tests showing > 1 million write transactions per second in some cases.
That’s sucks, hopefully squarespace will be alright. I had considered moving to Google too but was wary of Google support, glad I didn’t now.
If you need an alternative, Cloudflare has a decent DNS editor and offers free email forwarding. Plus you don’t have to move your domains to them so easy to try out. I’m not a fan of how much of the internet depends on them but they are decent.
I remember a similar issue with some newsletter software I worked on. The free version added a powered by line to newsletters among other limitations.
Unfortunately, spammers started using it which inevitably led to people thinking we were spamming them and a lot of angry emails.
The software enforced unsubscribe links but the spammers modified it to remove that. Weirdly, they left the powered by link… maybe it helped pass spam filters? Or they just didn’t care.
There wasn’t much we could do to stop them so in the end the free version had to be discontinued.
Why sqlite3 temp files were renamed 'etilqs_*' (2006)
Unofficial git mirror of SQLite sources (see link for build instructions) - sqlite/src/os.h at 18cf47156abe94255ae1495ba2da84517dce6081 · mackyle/sqlite
Saw this on Hacker News, seemed interesting.
SQLite 3.42.0
Adds the ability for JSON functions to read JSON5 which could be handy.
Looks really good! I love your workshop, looks like a nice space to be in. The pillar drill in the background looks huge, do you metal work too?