Encryption at rest

The Guardian kisses off MongoDB.

Another great thing about Postgres is how mature it is: every question we wanted to ask had in most cases already been answered on Stack Overflow.

NoWarehouse

Gregory Vial surveys the current landscape of database management systems: RDBMS, key-value stores, and systems in between -- for transaction processing and/or analytics. The emphasis is what I might call narrow-attribute storage, as opposed to free text-oriented systems like Elasticsearch.

Fail better

As product owner Patrick Cooper explains in an unusually frank post, our launch a couple of weeks ago was actually our second try.

So, it’s healthy every once in while to have an out-and-out fail.

The code deployment was never an issue; the procedural code was solid, and it always built and deployed successfully to production and staging environments. What burned us was the database migration: the new release entailed a major refactoring of an important subset of the schema.

The root cause of the fail was a database version incompatibility that we should have prepared for, but didn't. Even so, there were steps we could have taken that would have told us the migration was in trouble an hour or two sooner.

The migration was a series of SQL scripts. For most small updates (the app is usually updated on a 2-week scrum cycle), the dev team drops one or two short scripts (to create a new table, add some codes to to a dictionary table, that sort of thing) into a canonically-named folder, and migration is a short 5-minute process that takes no more time than the code deployment. But in this case, we had haphazardly put together a series of about a dozen scripts, two of which ran resource-devouring stored procedures.

What I would have done, in hindsight: organize the scripts so that all of the DML is executed first, then the dictionary tables are populated, and then finally the heavy lifting of moving data from one table to another happens. Add monitoring steps so that we can track the progress of the migration (we did do this for the second try). Seriously rethink the choice of stored procedures, and instead use a scripting language like Perl or Python or a compiled program. To the extent possible, provide a log or audit trail of migrated entities (the team did this very successfully in a smaller-scale migration a couple of years ago).

Fun with MySQL

The problem: Given a pair of tables in a header-detail relationship (or any one-to-many relationship), write a query that returns one row for each header row, including a column that is a comma-delimited vector of the subkeys that are found in the detail rows (for instance, line item numbers). For example:

Table INVOICE

Invoice  Date
-------  ----
AZ456    1-Jan-2010
AZ457    2-Jan-2010
AZ459    2-Jan-2010

Table INVOICE_LINE

Invoice  Line  Part
-------  ----  ----
AZ456    1     ZQX3
AZ456    2     WF612
AZ457    1     TY301
AZ459    3     TY301

Colleague Jared gave me this screwdriver from the MySQL toolbox: the GROUP_CONCAT() function.

SELECT I.Invoice, I.Date, GROUP_CONCAT(IL.Line) AS Vector
FROM INVOICE I
INNER JOIN INVOICE_LINE IL ON IL.Invoice = I.Invoice
GROUP BY I.Invoice

The results:

Invoice  Date        Vector
-------  ----        ------
AZ456    1-Jan-2010  1,2
AZ457    2-Jan-2010  1
AZ459    2-Jan-2010  3

In particular, we used this query to produce a table of photo image assets retrieved from the CMS (that met certain search criteria against the metadata like caption, photographer, agency). Each image asset has one or more crops in various aspect ratios (standard 4:3, square, wide 16:9). The search results table includes a vector of which crops are available for each image asset. In the UI, this vector is rendered as a nifty, compact row of gray and black icons designed by colleague Vincent.

This technique would work for any other attributes of the detail table, not just keys, in which case you may want to add the DISTINCT keyword to GROUP_CONCAT's argument. The SEPARATOR clause can overide the default comma delimiter.

Might come in handy again

The table amgp_release associates UPC codes (amgp_upc) with album IDs (amgp_a_id). You'd think that this would be a one-to-one relationship, but apparently multiple IDs can share the same UPC code. Question: What are the UPCs that are associated with multiple IDs? Query:

select count(*), amgp_upc 
    from (select distinct amgp_upc, amgp_a_id from amg.amgp_release) tbl
    group by amgp_upc 
    having count(*) > 1

Oracle numeric test

A colleague and I found ourselves needing to write a one-off bit of SQL that used a character column in a join condition. The character column is performing double duty, sometimes acting as a (numeric) foreign key, and sometimes holding other data.

I was perplexed by the lack of some kind of "is numeric" test in Oracle's dialect of SQL. I scrounged around forums and found something that I thought would work, but my colleague finally put me straight and we used this condition:

REGEXP_LIKE(char_column, '^[[:digit:]]+$')