Version Control for PostGIS

A year ago, I was only trying to “rescue” Andromeda

When I started the Pyrseas project, I was mainly interested in improving on the basic table/column version control of Andromeda. I never thought I’d be adding support for “exotic” features such as base types, operators or operator classes. However, the initial feedback led me to set my sights much higher: Pyrseas was to offer support for all PostgreSQL data definition features.

Over the past two and a half months, I’ve added coverage for those advanced features and more. I’m glad to report that, with the help of Leo Hsu and Regina Obe, the Pyrseas tools are now able to output all the objects1 in a database loaded with postgis.sql, spatial_ref_sys.sql and postgis_comments.sql from PostGIS 1.5, in YAML format. The tools are also able to take in that YAML and generate the SQL necessary to recreate all those objects, in the correct order, on an empty database, as well as to reverse the process.

The following is an update to the feature coverage table presented earlier. Although Pyrseas 0.4 has not been released yet, all these capabilities are present in the current HEAD GitHub repository.

DDL Feature 0.1 0.2 0.3 0.4
AGGREGATE  X  X
CAST  X
CONSTRAINT TRIGGER  X
CONVERSION  X
DOMAIN  X  X
FOREIGN DATA WRAPPER
FUNCTION  X  X  X
GROUP
INDEX  X  X  X  X
LANGUAGE  X  X  X
OPERATOR  X
OPERATOR CLASS  X
OPERATOR FAMILY  X
ROLE
RULE  X  X
SCHEMA  X  X  X  X
SEQUENCE  X  X  X  X
SERVER
TABLE  X  X  X  X
– CHECK CONSTRAINT  X  X  X  X
– PRIMARY KEY  X  X  X  X
– FOREIGN KEY  X  X  X  X
– UNIQUE CONSTRAINT  X  X  X  X
– INHERIT  X  X  X
TABLESPACE
TEXT SEARCH CONFIGURATION
TEXT SEARCH DICTIONARY
TEXT SEARCH PARSER
TEXT SEARCH TEMPLATE
TRIGGER  X  X
TYPE  X  X
– ENUM  X  X
– composite  X
– base type  X
USER
USER MAPPING
VIEW  X  X  X
COMMENT  X  X  X

I’d like to thank Leo and Regina for their help, as well aquilax, mhow and acornett at GitHub for being early adopters and raising issues to improve Pyrseas functionality.

If you have a challenging PostgreSQL database, I’d like to encourage you to give Pyrseas a try, and let me know how it works out.


1 For those interested, this includes 2 tables, 1 view, 9 base types, 1 composite type, 777 functions, 17 aggregate functions, 19 casts, 4 operator classes/families, 23 operators, and 315 comments.

3 thoughts on “Version Control for PostGIS”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s