PostgreSQL web application examples?

halley has asked for the wisdom of the Perl Monks concerning the following question:

Okay, I've started to hack my way through my last known frontier, software-development-wise. Database work. I don't particularly like it, but I need to get used to it.

My goal is to develop a straightforward web-based application.

I am finding a lack of suitable examples from which I can learn "best practices" beyond the barest fundamentals. I understand CGI. I have already set up my database. I know how SELECT and INSERT work. I understand one-to-one, one-to-many and many-to-many relations. I know what placeholders are, and how to use them.

What I don't know is how to architect the application which allows users to manipulate their own records and view others. What kinds of designs contribute to a hard-to-maintain soup of code. What kinds of designs end up hitting the bottlenecks by joining too many tables into queries. What kinds of architecture makes it tough to refactor the data design or the functionality without completely razing the database and starting over.

A brief outline of my desired project application's schema:

--
-- A database of "events" to which a community can contribute.
--
-- There are three basic entities: events, agents, and blurbs.
--
--  > An event is a unique point of data with a complex structure.
--  > An agent is essentially a user who can log into the application.
--  > A blurb is a text comment which can be attached to an event.
--  > Blurbs know which agent added them.  Blurbs can be edited later.
--

CREATE TABLE agents
    (  agentid SERIAL UNIQUE,
       agent TEXT NOT NULL,
       biography TEXT,
       PRIMARY KEY agentid
       ) ;

CREATE TABLE blurbs
    (  blurbid SERIAL UNIQUE,
       blurb TEXT NOT NULL,
       agentid INTEGER NOT NULL REFERENCES agents (agentid),
       PRIMARY KEY blurbid
       ) ;

CREATE TABLE events
    (  eventid SERIAL UNIQUE,
       event TEXT NOT NULL,
       -- other fields of interest
       PRIMARY KEY eventid
       ) ;

CREATE TABLE event-blurbs
    (  eventid INTEGER NOT NULL REFERENCES events (eventid),
       blurbid INTEGER NOT NULL REFERENCES blurbs (blurbid)
       ) ;
[download]

At root, it should not be that much different from other content management systems like Slash or Everything or even iStockPhoto: users can view all existing entries, add new entries, and edit the entries they added. Each of those examples are crufty and incompatible, though. I want to implement my own for the sake of learning what architecture is effective and what is not, and I'm looking for clean implementations from which I could learn by example.

Most of the examples I have found focus on the basics which I already understand. I would like to find good standalone examples which are between 1000 and 5000 lines of Perl; meaty enough to get something useful about the architecture, not just the syntax to use the primitives. Examples which aren't based on editing purchase orders would be a plus, but maybe I'm asking too much.

I've selected PostgreSQL and Perl (DBI/DBD::Pg and CGI) and Linux, as they're the most friendly to my way of working at home. I know there are alternatives to these but that's not particularly pursuant to my question. I especially don't want to entertain arguments about the superiority or completeness of mySQL or PHP-Nuke or Zope or Java, thanks. ;)

--
[ e d @ h a l l e y . c c ]

Comment on PostgreSQL web application examples? Select or Download Code

Replies are listed 'Best First'.
Re: PostgreSQL web application examples? by Fendaria (Beadle) on Feb 21, 2005 at 19:47 UTC
You may want to take a look at Bugzilla. It is an open source Bug Tracking facility written in Perl which has much of the functionallity you are intersted in (though it deals with bugs). It defaults to use MySQL but I believe it can use Postgresql as well (though I don't think it is as trivial as a configuration option). Also, as a side note, the whole O'Reilly Practical ModPerl book is online and a good read, though it covers alot on the administration side. I have to agree though, I haven't found much on good practices for structuring/organizing/developing large perl projects/systems. Fendaria	[reply]
Re^2: PostgreSQL web application examples? by halley (Prior) on Feb 22, 2005 at 14:36 UTC
Thanks for the idea. I gave it a look. It's certainly small and easy to manage for all the capability it has. After a quick review of the code, it's not organized well for my learning about the database side of things. I've used it often from the outside, and it's interesting to see what other features it has from the administrator's side, but the DB aspects are a bit crufty. They also rewrite CGI.pm, perhaps because it's older than CGI.pm's wide acceptance, but it gave me a feeling of Matt's Script Archive when I saw CGI.pl ... Cool app, good applicability to my concept, but not a pleasant code-reading experience so far. -- `[ e d @ h a l l e y . c c ]`	[reply]
Re: PostgreSQL web application examples? by punkish (Priest) on Feb 21, 2005 at 21:23 UTC
Your question seems to have little to do with the PostgreSQL-ness of the application and more with the web-ness of it. Other than that, it seems to be a standard CRUD (create, read, update, delete) application. While I have not had much success with CGI::Application (and I attribute it to my own obtuseness -- dragonchild very kindly and patiently explained to me the basics of CGI::App, and while his explanation seemed to make sense, I got bogged down by myself), it seems to be a good framework for organizing code. Since you are going to be dealing with user-specific issues, you would probably need CGI::Session of sump'n like that to manage sessions. CGI::App (or any other such framework) will allow you to create separate CRUD-specific modules and organize them in separate directory hierarchies. Size of the app is irrelevant here in my view -- bugzilla may or may not be a good starting point as it might be too complex, but then, it might also be a good study. -- when small people start casting long shadows, it is time to go to bed	[reply]
Re^2: PostgreSQL web application examples? by mpeters (Chaplain) on Feb 21, 2005 at 22:29 UTC
I have to agree that CGI::Application will give you a good start in learning how to organize your code for a web application. To further the push, there are several new plugins available to let you use other existing, best practices for things like session handling, logging, configuration, templating, etc. The mailing list is also very supportive when ever anyone has any application structure questions.	[reply]
Re: PostgreSQL web application examples? by aufflick (Deacon) on Feb 22, 2005 at 00:02 UTC
If you learn right, you may learn to love SQL - well I do anyway. There's something so nice about having a query optimiser on your side (how nice would a perl optimiser be ?!) I suggest reading through SQL for web nerds by Philip Greenspun. It uses Oracle for it's examples, but the ideas are what you want. All the Oracle stuff directly transaltes to Postgres anyway (except for the tree queries). Don't let the fact that it was written years ago bother you - RDBMS's and SQL have not changed in any way that would make the ideas presented irrelevant. It's worth learning this stuff well - I am constantly working on projects hampered by poor database design that is very hard to change down the track. Learn the theory, and databases will be your friends...	[reply]
Re: PostgreSQL web application examples? by Tuppence (Pilgrim) on Feb 22, 2005 at 00:08 UTC
While not being directly applicable to your question (it's not an example per say), allow me to recommend using `Class::DBI` to manage your tables. It wraps a thin OO layer around your tables, allowing you to not have to write the trivial insert / update statements that are the oh-so-boring side of DB development. As far as the web generation side of it goes, I have had good success with `HTML::Mason`	[reply] [d/l] [select]
Re^2: PostgreSQL web application examples? by halley (Prior) on Feb 22, 2005 at 14:31 UTC
Holy cow, I am happy to find Class::DBI! I was thinking last night how I could somehow make a generic schema layer that would translate record-to-object, and I was hoping there would be something already like it. This module goes a bit beyond what I was imagining, which is a relief to me. I should not get TOO hooked on it, because I do want to learn the underlying SQL methodology, but this looks like it'll take tons of drudgery out of Perl apps. I also want to mention a link I found shortly after digging into Class::DBI. I found a script someone wrote to output an Entity Relation Diagram (ERD): http://www.adp-gmbh.ch/perl/erd.html -- `[ e d @ h a l l e y . c c ]`	[reply]
Re: PostgreSQL web application examples? by tphyahoo (Vicar) on Feb 23, 2005 at 09:53 UTC
SQL-Ledger is an open source web application with a large user base, lots of interesting features (multi user accounting) that uses postgres and perl. It is discussed here in (OT) Perl Open Source accounting packages?. Unfortunately, as Ovid notes, the code has a lot of aspects that deviate from "best practices" including `* Poorly written * Doesn't use strict * Extensive use of global variables * Has security concerns * Is non-portable * Hand-rolled template system * Difficult to test * Will be difficult to extend * And guess how it handles CGI data ...` [download] So it probably isn't the best example for a web framework. However, it may be good for seeing how they set up the postgres stuff, including how they use postgres's rollback features which ensures the integrity of the general ledger by protecting against cases when updates conflict with each other. This would be impossible to do in mysql for the foreseeable future, as far as I know and is one of the main reason certain projects choose to go with the less mainstream postgres. Sql-ledger is licensed under the GPL but it's documentation, including developer guide, are not open source and are only available for a price, which is fairly steep. I suspect the documentation packages are one of the main sources of income for the developer, along with consulting for customizations for his heavier users.	[reply] [d/l]
Re: PostgreSQL web application examples? by tphyahoo (Vicar) on Feb 24, 2005 at 15:21 UTC
"Bricolage is a full-featured, open-source, enterprise-class content management system. Written entirely in Perl and using Mason to drive the UI, it offers a browser-based interface for ease-of use, a full-fledged, Mason-based templating system for flexibility, and many other features. It operates in an Apache/mod_perl environment, and uses the PostgreSQL RDBMS for its repository." Mason Powered Apps mentions bricolage and lots of other open source apps packages that run on mason. Bricolage is mentioned in perlmonks at "The Register" and eval "use Bricolage";. See also my Survey of Surveys on HTML Templating systems for FMTYEWTK.	[reply]