I've since gotten a bit off track, posting three entries about some of the Repoze stuff I've been doing lately. As I've been getting deeper and deeper into other projects I realize I'd better catch up and make good on my original promise or else I may never get back to it.
Today I figured I'd cover the principles behind Antichrist Watch's back-end database connection. It's using PostgreSQL and making heavy use of some of the features that many other database servers lack. In particular, PostgreSQL has great support for embedded logic (also known in database land as stored functions or stored procedures); not only does it have its own PL/pgSQL (similar to Oracle's PL/SQL) it also supports embedding code in other languages like Python, Perl, Tcl, and others besides. This embedded code can be tied into constraints that get executed (roughly) prior to a SQL command, rules that get executed (roughly) during a SQL command, and triggers that get executed after a SQL command. Additionally these stored procedures can be called directly allowing not only the effective batching of SQL commands but also multiple SQL commands bound together logically (that is, it's trivial to make a stored procedure execute one list of SQL commands under one set of conditions but execute another under a different set of conditions), or fashioned into views that behave like tables.
This combination gives the designer two big advantages over less capable database systems: first, it becomes possible to embed code to help guarantee data integrity within the database itself by careful use of constraints, rules, and triggers; and second it becomes possible to hide the database's implementation details from any client code by creating a public API via stored procedures. Thus we're basically using information hiding and restricting data access to certain controlled channels. I'm guessing that anyone who has read this far will understand in principle why these are good things. I'll carry it further though and list some specific benefits obtained by using this type of database design.
No raw tables are accessed in Antichrist Watch. It has a database access API fashioned from stored procedures and views. This level insulates the client from the raw tables; right now internally it is heavily normalized and even uses table inheritance to avoid repetition, but if at some point down the road it became necessary to duplicate some information for the sake of performance, the tables could be completely redesigned without requiring any change in client code whatsoever. Furthermore, with a clean interface it's easy to change clients. Antichrist Watch has had three completely different front-ends already. My original version was hacked together quickly from basic Python; my second version used PHP; the current version uses Twisted and Zope Page Templates (via Repoze Chameleon). At each stage making the transition was reasonably easy. It would have been a bear (possibly even unthinkable) if the interface were using raw tables and the client was responsible for data integrity.
Please note that I'm not advocating the moving of all logic out of things like Zope, Twisted, or PHP into the database server itself. This would be counterproductive. Tools for developing in languages like PL/pgSQL tend to be more primitive than tools for developing in languages like unembedded Python, and application logic definitely belongs outside the database. What I am advocating is making the database responsible for its own data checking while hiding its source internals.