Archive for the ‘SQLite’ Category
Posted by jpluimers on 2025/07/08
While writing Some SQLite things I recently learned a while ago I bumped into SQLite extending the SQL syntax allowing SELECT column aliases in WHERE and ON conditions of JOIN clauses whereas standard SQL only allows it in the HAVING clause (some other DMBS allow this as well).
It reminded I never wrote a blog post about WHERE versus HAVING, which can be phrased into just two points:
WHERE is applied before the SELECT is assembledHAVING is applied after the SELECT is assembled
This also means WHERE has a lot more influence on performance than HAVING, so using HAVING just so you can use aliases there instead of in the WHERE clauses is not a smart idea unless you verify in the query plan and query optimisation output of our DBMS this has no influence (some DBMS even allow you to see the results of query rewriting which you can use to verify if two queries really are equivalent from the DBMS perspective).
Since HAVING can be used without GROUP BY, many people work around the WHERE alias limitation. Given the above effects on performance, this is not a wise idea, despite the same end-results. See the example below that uses the [Wayback/Archive] MySQL :: Sakila Sample Database: customer table.
Luckily there has been enough other material explaining WHERE versus HAVING, so I can reference it from here, for instance (note note limited to MySQL/MariaDB) [Wayback/Archive] mysql – WHERE vs HAVING – Stack Overflow (thanks [Wayback/Archive] baloo and [Wayback/Archive] Fishdrowned):
Read the rest of this entry »
Posted in Conference Topics, Conferences, Database Development, Development, Event, MariaDB, MySQL, SQL, SQLite | Tagged: 1054, this | Leave a Comment »
Posted by jpluimers on 2025/07/03
Almost 3 years ago, I wrote about some database/SQL fiddle sites in Database fiddle sites (which covers [Wayback/Archive] two different [Wayback/Archive] dbfiddle sites and the [Wayback/Archive] SQL Fiddle site).
In the meantime, I figured out that [Wayback/Archive] Toolbox for Developers has a few fiddle pages and database/SQL tools online (in their order, which is not alphabetical):
Read the rest of this entry »
Posted in Database Development, Development, JavaScript/ECMAScript, MariaDB, MongoDB, MySQL, NoSQL, PHP, PostgreSQL, Scripting, Software Development, SQL Server, SQLite, TypeScript | Leave a Comment »
Posted by jpluimers on 2025/07/02
Since I keep forgetting which DBMS uses which method to select just a plain value without a table. I always remember it as my search phrase [Wayback/Archive] SELECT from DUAL, but actually better titled like the question below:
[Wayback/Archive] sql – SELECT from nothing? – Stack Overflow
Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MariaDB, MySQL, OracleDB, PL/SQL, PostgreSQL, SQL, SQL Server, SQLite | Leave a Comment »
Posted by jpluimers on 2025/05/02
Quite a while ago, Chrome moved from a structure based on “Current Session“, “Current Tabs“, “Last Session” and “Last Tabs” into “Session_#################” and “Tabs_#################” stored in a “Sessions” folder (and similar migrations for other state and configuration files).
The numbers in the “Session_*” and “Tabs_*” files are time stamps of those sessions, for instance one needs to figure out what the “13310808970819630” in “Session_13310808970819630” and “Session_13310808970819630” means.
Lot’s of web-pages with tips and tricks around the old structures are still around, often surfacing high in Google Search results.
I was interested in a particular trick to export Google Chrome browsing history and had a hard time figuring out the easiest solution.
Read the rest of this entry »
Posted in Apple, Batch-Files, Chrome, Chrome, Database Development, Development, Google, JavaScript/ECMAScript, Mac OS X / OS X / MacOS, NirSoft, Polyglot, Power User, Scripting, SQLite, Web Browsers, Windows, Windows 10, Windows 11 | Tagged: define | Leave a Comment »
Posted by jpluimers on 2025/04/30
More on the reason why I learned a few SQLite things soon, but for my link and documentation archive, below is what I learned.
Most commands use the database file C:\temp\History which has no extension as that is how I got the file in the first place (spoiler: it’s a Chrome browser History from one of my user profiles).
Let’s get started:
Read the rest of this entry »
Posted in *nix, *nix-tools, bash, CommandLine, Conference Topics, Conferences, Console (command prompt window), Database Development, Development, Event, Power User, PowerShell, PowerShell, Software Development, SQL, SQLite | Leave a Comment »
Posted by jpluimers on 2025/04/29
I found [Wayback/Archive] DB Browser for SQLite via [Wayback/Archive] In z’n leren frakske | Tech45 Podcast (thanks [Wayback/Archive] Toon Van de Putte (@toonvandeputte)!).
It is a standalone reasonably sized database browser for the single-process SQLite database (which is itself a file storage replacement for highly table structured data, see below).
With SQLite gaining more and more popularity in standalone application usage (you can even host it inside a web browser session!), I bump in it more often to fix things (more on that in a future blog post), which means that besides the standard console support in SQLite, having a versatile browser is really useful.
DB Browser for SQLite, or in short sqlitebrowser, fulfills that need better than I expected. It’s cross-platform so it works on Mac OS, Windows and Linux (and sort of on WSL2 on Windows, see links below).
Hopefully I can show you how I used it in future blog-posts. For now, and for my link archive, below are just some links to get started.
Oh and the comment: as always with files containing structured data that is randomly accessed you should be really careful when opening them over file-shares or virtual drives like cloud storage.
Read the rest of this entry »
Posted in Chrome, Chrome, Database Development, Development, Google, Power User, Software Development, SQLite, Web Browsers | Tagged: 2084, 2142, 2209, 4689, 5053, 938, SQLite | Leave a Comment »
Posted by jpluimers on 2025/04/24
[Wayback/Archive] One-liner for running queries against CSV files with SQLite | Simon Willison’s TILs
I figured out how to run a SQL query directly against a CSV file using the sqlite3 command-line utility:
sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' \
'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'
This uses the special :memory: filename to open an in-memory database. Then it uses two -cmd options to turn on CSV mode and import the taxi.csv file into a table called taxi. Then it runs the SQL query.
sqlite3 :memory: -cmd '.import -csv taxi.csv taxi' \
'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'
Via [Wayback/Archive] Simon Willison on Twitter: “TIL you can run SQL queries directly against CSV files as a one-liner using the default sqlite3 command line utility”
Read the rest of this entry »
Posted in CSV, Database Development, Development, Software Development, SQLite | Leave a Comment »
Posted by jpluimers on 2025/04/22
From a long time ago, but the (impressive!) leaderboard still has the same order (the Rust implementation did get faster!).
[Wayback/Archive] Towards Inserting One Billion Rows in SQLite Under A Minute – blag
Recently, I ran into a situation where I needed a test database with lots of rows and needed it fast. So I did what any programmer would do: wrote a Python script to generate the DB. Unfortunately, it was slow. Really slow. So I did what any programmer would do: went down the rabbit hole of learning more about SQLite, Python, and eventually Rust… in my quest to get a 1B row database under a minute. This blog post is a summary of this fun and educational exercise.
Repository at [Wayback/Archive] avinassh/fast-sqlite3-inserts: Some bunch of test scripts to generate a SQLite DB with 1B rows in fastest possible way
Leaderboard
(for 100M insertions)
| Variant |
Time |
| Rust |
23 seconds |
| PyPy |
126 seconds |
| CPython |
210 seconds |
Via: [Wayback/Archive] Shawn Wildermuth #000000 { lives: matter; } 🇺🇦 on Twitter: “Towards Inserting One Billion Rows in SQLite Under A Minute””
--jeroen
Read the rest of this entry »
Posted in Database Development, Development, SQLite | Tagged: 000000 | Leave a Comment »
The Wiert Corner - irregular stream of stuff 