The second Andromeda feature, aside from schema version control, that Pyrseas wanted to rescue was the automatic generation of search and detailed editing screens.
It is somewhat amazing that 40 years after Moshé Zloof’s “Query by Example” and over 30 years after “A Forms Application Development System” by Prof. Larry Rowe and Kurt Shoens, most applications for searching, entering and maintaining data are still reinventing the wheel.
There are of course apps such as PgAdmin and Adminer that can be used for general querying and editing, but their target audience is developers and DBAs, not end users. Furthermore, these tools typically only allow querying a single table at a time (see QBE queries #3 and subsequent, in the aforementioned paper, for examples that usually can’t be handled).
At first, I thought I would develop a typical Python WSGI application. I planned to use Werkzeug because the extra machinery in larger frameworks such as Django is unnecessary for the envisioned purpose. An additional consideration was that a smaller library or framework would make it easier for anyone wanting to extend the automatically generated pieces into a full-fledged app.
I started designing a prototype application and also exploring other WSGI contenders. Then Jacob Kaplan-Moss and Tarek Ziadé jolted my thought process: a JavaScript-based framework was the way forward for a web-based app with Python (and Werkzeug) relegated to a REST API server.
So I took a look at AngularJS, Ember.js, Backbone.js and several other JavaScript frameworks and libraries. At this time, it seems a simpler library such as Backbone.js, or perhaps the Marionette framework, may be more appropriate. It is comforting to see that PgAdmin4 has chosen a similar path for its web-deployed client.
This capability is ultimately quite ambitious, so perhaps Pyrseas ought to stick just to schema version control. Nevertheless, whether integrated with Pyrseas or not, this functionality ought to re-use the database-catalog-to-Python-structures-to-YAML (and vice versa) features developed for Pyrseas.
This concludes this three-part series. Please also see part 1 and part 2.
Taming Serpents and Pachyderms 
Joe
Thanks for the thoughtful retrospective of how Pyrseas got to where it is today, and where it could go in the future. I wish I had found a project that allowed us to build the business rules engine I wanted Augmenter to become, but sadly that wasn’t to be.
Personally, I see the need for a web-based search and editing application as a separate thing to Pyrseas, for a couple of reasons:
1. A UI for search and edit really only requires an understanding of the tables, columns and relationships in the database. It doesn’t require the deep understanding of the Postgresql objects that Pyrseas has.
2. There are already mature ORMs and REST API tools that offer the basis for a generic data layer, and have the advantage of supporting multiple databases.
I think the tools have moved on since your earlier comparison; have you looked at https://github.com/jeffknupp/sandman2 ?
I love Pyrseas for it’s ability to tell me definitively what is different between my stage and production databases. I’m using Django in my current job, and so I’m using it’s Migrations framework to generate schema changes. The inevitable requirement for data migrations means that some sort of migrations framework is essential and a purely metadata-driven approach doesn’t stand up to real-world use.
But Pyrseas is the only effective way that I can confirm that the migrations are making the changes I wanted, and that the various databases are consistent with the schema they are supposed to contain.
Consequently, I think that making sure that Pyrseas keeps up with new Postgresql features (e.g. materialized views as a recent example) is vital.
In conclusion, Joe – thank you very much for Pyrseas it is an indispensable tool in my box. I wish it, and you, all the best for the future.
Thanks for the kind words, Roger. (I don’t know why I didn’t see this earlier, didn’t get a comment notification).
Augmenter was the one area that I didn’t cover in terms of future planning but I think it belongs somewhere after getting dependencies right.
When you say that “Pyrseas is the only effective way that I can confirm that the migrations are making the changes I wanted” do you mean that you (a) run dbtoyaml against the stage and production databases and compare the outputs, (b) run yamltodb against the production database using YAML definition file(s) from the stage database or (c) something else? If (a), isn’t a comparison of pg_dump -s outputs almost equivalent (order of triggers is a problem)? If (b), then getting dependencies right seems to be an important first step, otherwise you may get false positives.
I hadn’t seen sandman before but I took a quick look now. Since it uses Flask, I don’t think it can be considered state-of-the-art. I have nothing against Flask, it’s just that using wsgi apps for editing a database table feels like using a 3270 when VT100s and PCs are available, i.e., full page/record at a time instead of immediate interactivity at the field level. And there are at least two other things that I would like to implement in a DB search/edit interface that I haven’t seen in any of the current apps, but were present in tools I used in 80’s/90’s.
I keep a master schema.yml file under version control. After we create a new migration and apply it in development, we use dbtoyaml to update the schema.yml and then use git diff to confirm that the changes being made are the ones we wanted. Then the updated schema.yml is committed. The migrations are run against each database in the pipeline as the release progresses, and after each one we can run yamltodb against that database using the master schema.yml and check that there are no further changes required. A change normally indicates that someone made a manual change to the database, but sometimes uncovers variations caused by running migrations in the wrong order as a result of undocumented dependencies.
OK, so you’re using essentially option (b). It’s unlikely that you’ll run into problems (such as those tagged with “dependencies” in the GitHub tracker) because you’re mostly using yamltodb as verification of the migration process.
Aside: WP is weird (or broken). Your previous comment: no notification. This time around: sent an approval request.
> Since it uses Flask, I don’t think it can be considered state-of-the-art.
This is an extremely odd comment. I’m the author of sandman (and sandman2) and the use of Flask has no bearing on the capabilities of the application. It’s akin to saying “That application can’t be state of the art because it supports FreeBSD.”
Regardless, the use of Flask and, indeed, the entire purpose of the application has nothing to do with UI interaction. The admin page is included as a convenience to the user. The main purpose is to auto-generate a RESTful API service by database introspection, and it does that quite well.
I’m sorry, Jeff. I took a very quick look at sandman2, but evidently didn’t read enough. Because of the context of where I was coming from (i.e., an application to provide UI interraction for searching/editing databases), I thought that Roger was pointing me somewhat in that direction, definitiely not in the direction of a RESTful API service (which I believe is also needed).
Just as a matter of curiosity, does the sandman2 database introspection involve querying each database’s catalogs or does it instead depend on SQLAlchemy introspection (which actually handles the catalog introspection)?
Pingback: The Future of Pyrseas, revisited | Taming Serpents and Pachyderms