######octothorp.es

#sql

  • Dynamic OFFSETs and LIMITs

    https://justatheory.com/2009/01/postgres-dynamic-limit

  • Sqitch: Where to Define Dependencies?

    https://justatheory.com/2012/05/sqitch-dependencies

  • SQL Change Management Sans Duplication

    https://justatheory.com/2012/01/sql-change-management-sans-redundancy

    Here’s how I propose to eliminate the duplication of code between deploy and revert SQL change management scripts.

  • Sqitch: Back to the VCS

    https://justatheory.com/2012/06/sqitch-vcs-again

  • Sqitch Symbolism

    https://justatheory.com/2012/09/sqitch-symbolism

  • Committed: pgTAP Result Set Assertion Functions

    https://justatheory.com/2009/07/pgtap-results-eq

    Regular readers will know that I’ve been thinking a lot about testing SQL result sets and how to how to name result testing functions, and various implementation issues. I am very happy to say that I’ve now committed the first three such test functions to the Git repository. They’ve been tested on 8.4 and 8.3. Here’s what I came up with.

  • Fixing Foreign Key Deadlocks in PostgreSQL

    https://justatheory.com/2010/11/postgres-fk-locks-project

  • Sqitch: Depend On It!

    https://justatheory.com/2012/08/sqitch-depend-on-it

  • Unicode Normalization in SQL

    https://justatheory.com/2009/09/postgres-unicode-normalization

  • Managing Key/Value Pairs in PostgreSQL

    https://justatheory.com/2010/08/postgres-key-value-pairs

  • Sqitch — VCS-powered SQL Change Management

    https://justatheory.com/2012/04/sqitch-draft

    Back in January, I wrote three posts outlining some ideas I had about a straight-forward, sane way of managing SQL change management. The idea revolved around specifying scripts to deploy and revert in a plan file, and generating that plan file from VCS history. I still feel pretty good about the ideas there, and work has agreed to let me write it and open-source it. Here is the first step making it happen. I call it “Sqitch.”

  • Thoughts on Testing SQL Result Sets

    https://justatheory.com/2009/06/pgtap-comparing-relations

    pgTAP: The Critique I’ve been continuing hacking on pgTAP in order to add a lot more schema-testing functionality and a few other niceties. But back when I started the project, I using it to write tests for CITEXT, which was great for my sanity as I developed it, but proved a bit controversial. In a pgsql-hackers post, Tom Lane wrote:

  • Simple SQL Change Management

    https://justatheory.com/2012/01/simple-sql-change-management

    I’ve been thinking a lot about SQL change management. I think I may finally have cracked this thing wide open.

  • Sqitch Update: Almost Usable

    https://justatheory.com/2012/07/sqitch-almost-usable

  • VCS-Enabled SQL Change Management

    https://justatheory.com/2012/01/vcs-sql-change-management

    Some thoughts on how to use VCS history to determine what changes need to be deployed or reverted without relying on a configuration file.

  • Sqitch Update: All Your Rebase Are...Never Mind

    https://justatheory.com/2012/12/sqitch-rebase

  • How to Generate Recurring Events in the Database

    https://justatheory.com/2008/01/postgres-recurring-events

    Going deep on using Postgres functions to create efficient recurring events.

  • 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.

  • Sqitch: Rename Step Objects and the SQL Directory?

    https://justatheory.com/2012/06/sqitch-steps-to-change

    After all of the thinking and rethinking about how to manage a Sqitch plan, I am just about done with all the changes to make it all work. One of the changes I’ve made is that tags are no longer objects that stand on their own between change steps, but are simply names the refer to specific change steps. Not only is this much more like how a VCS thinks of tags (basically another name for a single commit), but it also greatly simplifies the code for iterating over a plan and updating metadata in the database.

  • Enforcing a Set of Values

    https://justatheory.com/2010/01/enforce-set-of-postgres-values

  • Sqitch: Trust, But Verify

    https://justatheory.com/2013/01/sqitch-trust-but-verify

  • My Catalyst Tutorial: Add Authors to the View

    https://justatheory.com/2009/11/sql-view-aggregate-magic

  • Sqitch Homebrew Tap

    https://justatheory.com/2013/02/sqitch-homebrew-tap

    If Sqitch is to succeed, it needs to get into the hands of as many people as possible. That means making it easy to install for people who are not Perl hackers and don’t want to deal with CPAN. The Sqitch Homebrew Tap is my first public stab at that. It provides a series of “Formulas” for Homebrew users to easily download, build, and install Sqitch and all of its dependencies.

    Follow #sql on RSS or use the

    JSON API 
    
curl -X GET \
  -H "Content-type: application/json" \
  -H "Accept: application/json" \
  "https://octothorp.es/~/sql"