A couple of Pyrseas enhancements

Based on feedback from users and contributors, Pyrseas now sports two enhancements.

Multi-line String Formatting

Up to Pyrseas 0.6, long textual elements such as view definitions, function source text and long object comments, would usually be shown in the YAML output as quoted strings with embedded newlines. Here are two examples from the autodoc database:

schema product:
  description: "This schema stores a list of products and information\n about the\
    \ product"
schema warehouse:
  view products:
    definition: " SELECT DISTINCT product.product_id, product.product_code, product.product_description\n\
      \   FROM warehouse.inventory\n   JOIN product.product USING (product_id);"

As you can imagine, this was particularly unsatisfactory for complex functions and views. Thanks to preliminary work by Andrey Popp, Pyrseas 0.7 will be able to format these elements in YAML block style. The above elements will be shown as follows:

schema product:
  description: |-
    This schema stores a list of products and information
     about the product
schema warehouse:
  view products:
    definition: |2-
       SELECT DISTINCT product.product_id, product.product_code, product.product_description
         FROM warehouse.inventory
         JOIN product.product USING (product_id);

Thanks to testing by Josep Martínez, 0.7 will also properly display and handle such strings even when they include non-ASCII characters such as accented characters. For example, in 0.6, “Martínez” would be shown as “Mart\xEDnez”. In 0.7, the output will be the original UTF-8 string.

Directory of Database Objects

Pyrseas 0.6 has a single format for output by dbtoyaml or input into yamltodb: a single YAML-formatted file. This becomes a problem when your database has hundreds or more tables, functions, etc (let alone 409,994 tables and counting!). Furthermore, as dbtoyaml and yamltodb are intended to assist with database version control, your team may want to store individual object specifications in your version control system, or you may want to diff individual objects.

The 0.7 --directory option to dbtoyaml and yamltodb allows you to split the YAML spec into multiple files in a directory (or folder) tree. For example, using the dbtoyaml -d option on the autodoc database results in the following tree (shown under Linux using ls -RF):

schema.inherit/      schema.public/      schema.warehouse/
schema.inherit.yaml  schema.public.yaml  schema.warehouse.yaml
schema.product/      schema.store/
schema.product.yaml  schema.store.yaml

table.tab1b.yaml  table.tab1.yaml  table.taba.yaml  table.tabb.yaml

function.worker.yaml  sequence.product_product_id_seq.yaml  table.product.yaml


sequence.store_store_id_seq.yaml  table.inventory.yaml  table.store.yaml

function.worker.yaml                      table.warehouse.yaml
sequence.warehouse_warehouse_id_seq.yaml  view.products.yaml

As can be seen, each schema gets its own directory wherein are stored each object belonging to that schema. In addition to schemas, the root level also stores non-schema owned objects such as foreign data wrappers and extensions (the latter can be placed in a schema, but are not owned by it).

The directory tree and multi-line string formats are still under review, so I’d like to encourage you to test both enhancements and provide feedback.


Gearing up for a second release

The first release of Pyrseas was aimed at addressing the essential version control issues described in my first posts. The second release, somewhat accidentally, has focused on being able to recreate the PostgreSQL autodoc regression database. In addition, Pyrseas will be released on the PostgreSQL Extension Network (PGXN).

Pyrseas already supports the basic schema, table, column, primary key, foreign key and index CREATE and ALTER operations provided by PostgreSQL (and every relational DBMS worthy of that designation). Here is a summary of PostgreSQL features that will be supported in the upcoming release:

  • COMMENTs on schemas, tables, columns and functions.
  • ALTER TABLE RENAME COLUMN and enhanced support for other ALTER object RENAME statements.
  • VIEWs
  • INHERITed tables, and by extension, partitioned tables.

Support for some of these features is still elementary. However, the Pyrseas utilities are now able to recreate the autodoc database and revert back to an empty database, i.e., they also know how to drop each object in the correct order.

The Pyrseas unit tests have also been enhanced so they can be run against different PostgreSQL installations. For example, using the PYRSEAS_TEST_PORT environment variable I was able to run the tests against the 9.1 Beta 1 release, as well as 9.0 and 8.4.

Looking forward, the subsequent release will tackle being able to recreate the Pagila sample database, thus adding support for TYPEs, DOMAINs, AGGREGATEs, TRIGGERs and RULEs, and improving support for existing features.

Update: Pyrseas 0.2.0 has been released and is now available via PyPI, PGXN, PgFoundry and from the GitHub repository.


SQL Database Version Control and RENAMEs

You’ve just watched Josh Berkus’ presentation “Ten Ways to Wreck Your Database” and bearing in mind point #2 “ranDom_naming(s),” your team has decided to get your act together and adopt a naming convention (although this may not be advantageous to your job security, per josh :-)).

PostgreSQL makes it fairly easy to rename schemas, tables and columns via an appropriate ALTER object RENAME statement. Aside: Other SQL implementations provide similar capabilities to various degrees of completeness. Some SQL version control tools support such changes. For example, Liquibase supports renaming tables, columns and views.

On the other hand, an ALTER statement –like a biological mutation– destroys information, which creates a problem for difference engines such as apgdiff, Andromeda and Pyrseas. It may be obvious to a human that the film.rating column and category table in the production database correspond to the renamed film.mpaa_rating column and categories table, respectively, in the development database. However, there is nothing in the latter’s system catalogs that tie the new names to the previous ones. As a result, difference engines will usually DROP the former objects and CREATE others with the new names.

The Pyrseas solution to this problem is to add an oldname field to the YAML spec. For example, if you have the following abbreviated spec (generated by dbtoyaml from the development database):

schema public:
  table film:
    - mpaa_rating:
        type: character(5)

By editing the spec and making the following change to the mpaa_rating column:

    - mpaa_rating:
        oldname: rating
        type: character(5)

Then yamltodb will, when run against a database that still uses the previous colum name, generate the following SQL script:

ALTER TABLE film RENAME COLUMN rating TO mpaa_rating;

Similarly, you can use oldname on a table to generate ALTER TABLE name RENAME TO new_name and on a schema to generate ALTER SCHEMA name RENAME TO new_name.

This is not a foolproof solution because the rename is a one-time action. You could commit the changed spec to your VCS, but if you ran yamltodb against a database after the RENAME has been applied, it will give an error because it cannot find the oldname object. Aside: I’m considering changing that to simply a warning. Another option would be to allow yamltodb to read more than one YAML spec or one that lists some objects more than once, so the extra information could be added when needed. For these types of changes, a complementary tool such as depesz Versioning can be used to introduce the RENAMEs into production.

Pyrseas 0.1.0 includes the RENAME capability for schemas and tables and I’ve recently committed the changes needed for RENAMEing columns.

In other project news, I’ve also added support for FOREIGN KEY ON UPDATE and ON DELETE actions, support for COMMENT statements on schemas, tables and columns, and corrected a problem with indexes being created in the wrong schema. With these changes, the autodoc regression database schemas, tables, columns, primary keys, foreign keys, indexes and comments on these objects can all be properly recreated using dbtoyaml and yamltodb, with the exception of the inheritance tables.