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.
Taming Serpents and Pachyderms 