#sqlite

• Add Regular Expression Operator to SQLite

  https://justatheory.com/2006/01/add-sqlite-regexen

• Pinning your SQLite version across environments

  https://blog.balthazar-rouberol.com/pinning-your-sqlite-version-across-environments

  A walkthrough of how to pin an sqlite version and feature set accross multiple environments, architectures and OSes.

• MySQL's REPLACE Considered Harmful

  https://justatheory.com/2005/03/mysql-replace-considered-harmful

• RFC: A Simple Markdown Table Format

  https://justatheory.com/2009/02/markdown-table-rfc

  I’ve been thinking about markdown tables a bit lately. I’ve had in mind to follow up on my definition list proposal with a second proposal for the creation and editing of simple tables in Markdown. For better or for worse, an aside on the markdown-discuss mail list led to a longish thread about a syntax for continuing lines in tables (not to mention a long aside on the use of monospaced fonts, but I digress), wherein I realized, after an open-minded post from MultiMarkdown’s Fletcher Penney, that I needed to set to working up this request for comments sooner rather than later.

• Sqitch: Now with SQLite Support

  https://justatheory.com/2013/04/sqitch-sqlite

• Enforce Foreign Key Integrity in SQLite with Triggers

  https://justatheory.com/2004/11/sqlite-foreign-key-triggers

  After some some Googling and experimentation, I’ve figured out how to enforce foreign key constraints in SQLite. I got most of the code from Cody Pisto’s sqlite_fk utility. I couldn’t get it to work, but the essential code for the triggers was in its fk.c file, so I just borrowed from that (public domain) code to figure it out.

• Notes on Upcoming Sqitch Improvements

  https://justatheory.com/2013/07/upcoming-sqitch-improvements

• Custom SQLite Aggregates in Perl

  https://justatheory.com/2005/10/sqlite-perl-aggregates

  About a year ago, Josh Berkus was reviewing some Bricolage SQL code, looking to optimize it for PostgreSQL. One of the things he noticed was that we were fetching a lot more rows for an object than we needed to. The reason for this is that an object might be associated with one or more groups, and to get back a list of all of the group IDs, we were getting multiple rows. For example, if I wanted to fetch a single story with the ID 10, I might get back rows like this: