{"uri":"https://octothorp.es/","term":"sql","thorpes":[{"uri":"https://justatheory.com/2012/05/sqitch-plan","title":"Sqitch Update: The Plan","description":"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."},{"uri":"https://justatheory.com/2009/01/postgres-dynamic-limit","title":"Dynamic OFFSETs and LIMITs","description":null},{"uri":"https://justatheory.com/2012/05/sqitch-dependencies","title":"Sqitch: Where to Define Dependencies?","description":null},{"uri":"https://justatheory.com/2012/01/sql-change-management-sans-redundancy","title":"SQL Change Management Sans Duplication","description":"Here’s how I propose to eliminate the duplication of code between deploy and revert SQL change management scripts."},{"uri":"https://justatheory.com/2012/11/postgres-format-function","title":"New in PostgreSQL 9.2: format()","description":null},{"uri":"https://justatheory.com/2012/06/sqitch-vcs-again","title":"Sqitch: Back to the VCS","description":null},{"uri":"https://justatheory.com/2012/05/evolving-sqitch-plan","title":"The Ever Evolving Sqitch Plan","description":null},{"uri":"https://justatheory.com/2012/09/sqitch-symbolism","title":"Sqitch Symbolism","description":null},{"uri":"https://justatheory.com/2009/07/pgtap-results-eq","title":"Committed: pgTAP Result Set Assertion Functions","description":"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."},{"uri":"https://justatheory.com/2010/11/postgres-fk-locks-project","title":"Fixing Foreign Key Deadlocks in PostgreSQL","description":null},{"uri":"https://justatheory.com/2009/11/pgtap-best-practices","title":null,"description":"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!"},{"uri":"https://justatheory.com/2005/03/mysql-replace-considered-harmful","title":"MySQL's REPLACE Considered Harmful","description":null},{"uri":"https://justatheory.com/2012/08/sqitch-depend-on-it","title":"Sqitch: Depend On It!","description":null},{"uri":"https://justatheory.com/2012/11/mock-postgres-serialization-failures","title":"Mocking Serialization Failures","description":null},{"uri":"https://justatheory.com/2009/09/postgres-unicode-normalization","title":"Unicode Normalization in SQL","description":null},{"uri":"https://justatheory.com/2010/08/postgres-key-value-pairs","title":"Managing Key/Value Pairs in PostgreSQL","description":"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)."},{"uri":"https://justatheory.com/2012/04/sqitch-draft","title":"Sqitch — VCS-powered SQL Change Management","description":"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.”"},{"uri":"https://justatheory.com/2009/05/more-on-database-testing","title":"More on Database Testing","description":null},{"uri":"https://justatheory.com/2009/07/sql-neither-null-nor-not-null","title":"Neither NULL nor NOT NULL: An SQL WTF","description":null},{"uri":"https://justatheory.com/2009/06/pgtap-comparing-relations","title":"Thoughts on Testing SQL Result Sets","description":"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:"},{"uri":"https://justatheory.com/2012/01/simple-sql-change-management","title":"Simple SQL Change Management","description":"I’ve been thinking a lot about SQL change management. I think I may finally have cracked this thing wide open."},{"uri":"https://justatheory.com/2006/09/postgres-gtin-0.01","title":"My First C: A GTIN Data Type for PostgreSQL","description":null},{"uri":"https://justatheory.com/2012/07/sqitch-almost-usable","title":"Sqitch Update: Almost Usable","description":null},{"uri":"https://justatheory.com/2012/01/vcs-sql-change-management","title":"VCS-Enabled SQL Change Management","description":"Some thoughts on how to use VCS history to determine what changes need to be deployed or reverted without relying on a configuration file."},{"uri":"https://justatheory.com/2012/12/sqitch-rebase","title":"Sqitch Update: All Your Rebase Are...Never Mind","description":null},{"uri":"https://justatheory.com/2013/08/understanding-window-functions","title":"Understanding Window Functions","description":null},{"uri":"https://justatheory.com/2008/01/postgres-recurring-events","title":"How to Generate Recurring Events in the Database","description":"Going deep on using Postgres functions to create efficient recurring events."},{"uri":"https://justatheory.com/2009/06/pgtap-result-testing-function-names","title":"Need Help Naming Result Set Testing Functions","description":null},{"uri":"https://justatheory.com/2004/11/sqlite-foreign-key-triggers","title":"Enforce Foreign Key Integrity in SQLite with Triggers","description":"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."},{"uri":"https://justatheory.com/2009/02/pgtap-0.16","title":"pgTAP 0.16 in the Wild","description":"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."},{"uri":"https://justatheory.com/2012/06/sqitch-steps-to-change","title":"Sqitch: Rename Step Objects and the SQL Directory?","description":"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."},{"uri":"https://justatheory.com/2010/01/enforce-set-of-postgres-values","title":"Enforcing a Set of Values","description":null},{"uri":"https://justatheory.com/2013/01/sqitch-trust-but-verify","title":"Sqitch: Trust, But Verify","description":null},{"uri":"https://justatheory.com/2009/11/sql-view-aggregate-magic","title":"My Catalyst Tutorial: Add Authors to the View","description":null},{"uri":"https://justatheory.com/2013/02/sqitch-homebrew-tap","title":"Sqitch Homebrew Tap","description":"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."},{"uri":"https://justatheory.com/2009/05/pgtap-0.21","title":"pgTAP 0.21 Drops","description":"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)."}],"bookmarks":[]}