#sql

• Sqitch Update: The Plan

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

  I gave my first presentation on Sqitch at PGCon last week. The slides are on Slideshare and the PGCon site. It came together at the last minute, naturally. I was not able to pay as close attention to PGCon sessions as I would have liked, as I was doing last minute hacking to get the deploy command working on PostgreSQL, and then writing the slides (which are based on the tutorial). I was pleased with the response, given that this is very much a project that is still under heavy development and available only as a very very early alpha. There was great discussion and feedback afterward, which I appreciate.

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

• New in PostgreSQL 9.2: format()

  https://justatheory.com/2012/11/postgres-format-function

• Sqitch: Back to the VCS

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

• The Ever Evolving Sqitch Plan

  https://justatheory.com/2012/05/evolving-sqitch-plan

• 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

• https://justatheory.com/2009/11/pgtap-best-practices

  Last month I gave two presentations at the PostgreSQL Conference West. The first was a variation on Unit Test Your Database!, which I’ve now given about six times (and will at least two more times, including tomorrow night for Portland PLUG and in two weeks at the JPUG 10th Anniversary Conference). The second was a new talk, a 90-minute tutorial, called “pgTAP Best Practices.” And here it is (download and Slideshare). Enjoy!

• MySQL's REPLACE Considered Harmful

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

• Sqitch: Depend On It!

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

• Mocking Serialization Failures

  https://justatheory.com/2012/11/mock-postgres-serialization-failures

• 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

  Let’s say that you’ve been following the latest research in key/value data storage and are interested in managing such data in a PostgreSQL database. You want to have functions to store and retrieve pairs, but there is no natural way to represent pairs in SQL. Many languages have hashes or or data dictionaries to fulfill this role, and you can pass them to functional interfaces. SQL’s got nothin’. In PostgreSQL, have two options: use nested arrays (simple, fast) or use a custom composite data type (sugary, legible).

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

• More on Database Testing

  https://justatheory.com/2009/05/more-on-database-testing

• Neither NULL nor NOT NULL: An SQL WTF

  https://justatheory.com/2009/07/sql-neither-null-nor-not-null

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

• My First C: A GTIN Data Type for PostgreSQL

  https://justatheory.com/2006/09/postgres-gtin-0.01

• 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

• Understanding Window Functions

  https://justatheory.com/2013/08/understanding-window-functions

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

• Need Help Naming Result Set Testing Functions

  https://justatheory.com/2009/06/pgtap-result-testing-function-names

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

• pgTAP 0.16 in the Wild

  https://justatheory.com/2009/02/pgtap-0.16

  I’ve been writing a lot tests for a client in pgTAP lately. It’s given me a lot to think about in terms of features I need and best practices in writing tests. I’m pleased to say that, overall, it has been absolutely invaluable. I’m doing a lot of database refactoring, and having the safety of solid test coverage has been an absolute godsend. pgTAP has done a lot to free me from worry about the effects of my changes, as it ensures that everything about the databases continue to just work.

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

• pgTAP 0.21 Drops

  https://justatheory.com/2009/05/pgtap-0.21

  I just dropped a new version of pgTAP following a few marathon hack sessions since my talk at PGCon (movie here, BTW). Actually, the new performs_ok() function came about as I wrote the presentation, but all the rest came on the flight home and in the few days since. Not sure when I’ll hack on it this much again (it’s getting a bit big at 5,758 lines of PL/pgSQL and SQL).

Follow #sql on RSS or use the

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