Thread: Role Models Wanted
I'm searching for projects that would serve as good PostgreSQL examples. In fact it would be nice to have a place on the PostgreSQL web page that featured some good examples. (If such a place exists and I missed it, please let me know.) Ideally the project would have the following characteristics: -Open-source (naturally, it's hard to use as an example otherwise) -Good database structure/normalization -Good error detection/recovery -Appropriate use of back-end capabilities including functions, triggers and rules -Good security practices -Appropriate use of special PostgreSQL features (non-standard data types, inheritance, etc.) A single project might not fulfill all these requirements so feel free to add personal comments or warnings, eg. "Check out the foo project. It is a PHP based snarf tracker with a very good database design and extensive use of PL/PGSQL functions. Don't emulate the security, however, since the code fails to check for dangerous input leaving it vulnerable to SQL injection attacks." Also feel free to add characterstics to my list. Cheers, Steve
> I'm searching for projects that would serve as good PostgreSQL examples. In > fact it would be nice to have a place on the PostgreSQL web page that > featured some good examples. (If such a place exists and I missed it, please > let me know.) It is probably still a bit early to be promoting it as a role model, but the new membership system for the United States Chess Federation is being developed around PostgreSQL. -- Mike Nolan
> I'm searching for projects that would serve as good PostgreSQL examples. In Excellent idea. > Ideally the project would have the following characteristics: [...] - good use of connection pooling - good use of read-only vs. read-write connections (emulated via grants or as in 7.4devel) - use of notifies (dynamic/non-dynamic notify names) - good solutions to standard problems: - auditing - special purpose ref-counting - cross-database queries with dblink - tsearch - i18n for strings in the database - "fixed nr. of rows" in a table - ... This should not only serve as helpful advocacy material but also provide for best-of-breed examples to check one's own design and code against. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Take a look at HERMES (http://hermesweb.sourceforge.net). The demo is running on MySQL, but as I state in the README's, MySQL is only supported for demos due to a lack of support for many things. If you want I would be happy to set up a demo on a PostgreSQL site (like the one I use internally for testing). 0.1.0 is currently out and it may not be until 0.3.0 that the application really starts making use of the power of PostgreSQL, Best Wishes, Chris Travers ----- Original Message ----- From: "Steve Crawford" <scrawford@pinpointresearch.com> To: "pgsql General List" <pgsql-general@postgresql.org> Sent: Wednesday, July 23, 2003 1:55 PM Subject: [GENERAL] Role Models Wanted > I'm searching for projects that would serve as good PostgreSQL examples. In > fact it would be nice to have a place on the PostgreSQL web page that > featured some good examples. (If such a place exists and I missed it, please > let me know.) > > Ideally the project would have the following characteristics: > > -Open-source (naturally, it's hard to use as an example otherwise) > > -Good database structure/normalization > > -Good error detection/recovery > > -Appropriate use of back-end capabilities including functions, triggers and > rules > > -Good security practices > > -Appropriate use of special PostgreSQL features (non-standard data types, > inheritance, etc.) > > A single project might not fulfill all these requirements so feel free to add > personal comments or warnings, eg. "Check out the foo project. It is a PHP > based snarf tracker with a very good database design and extensive use of > PL/PGSQL functions. Don't emulate the security, however, since the code fails > to check for dangerous input leaving it vulnerable to SQL injection attacks." > > Also feel free to add characterstics to my list. > > Cheers, > Steve > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
I have not looked at this closely enough to know if this meets the requested criteria, but it is an extensive database. And hopefully they are serious about security :). http://www.gnumed.org/
On Wednesday, July 23, 2003, at 03:55 PM, Steve Crawford wrote: > I'm searching for projects that would serve as good PostgreSQL > examples. In > fact it would be nice to have a place on the PostgreSQL web page that > featured some good examples. (If such a place exists and I missed it, > please > let me know.) > -Open-source (naturally, it's hard to use as an example otherwise) > > -Good database structure/normalization http://www.loggerithim.org It probably only meets those two criteria, but I feel the database structure is pretty sound (but I'm biased) I use views to denormalize (rather than make the Perl ugly). It's a monitoring app that generates graphs (similar to MRTG), and 24 hours worth of data (144 samples) gets selected and graphed in approximately a quarter second (when vacuuming is done properly), even with 8 million+ rows. The code is a pre-release, with a full version out in a few days, but the db is sound. I'm open to suggestions. ;) Cory 'G' Watson http://www.loggerithim.org
> I have not looked at this closely enough to know if this meets the > requested criteria, but it is an extensive database. And hopefully they > are serious about security :). > > http://www.gnumed.org/ *Grin* I wasn't going to suggest it as I am one of the main developers ... :-) But I did supply that add-on list in one of the previous posts. We *are* serious about security. We have to, not the least reason for which being HIPAA rules in the US. Guess why we chose PostgreSQL instead of MySQL ;-) We do do: - inheritance - auditing - triggers / functions (the latter of which not enough yet) - grants with groups - simulated/real read-only connections - dynamic notifies Karsten Hilbert, MD GnuMed i18n coordinator Leipzig, Germany -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Wed, 2003-07-23 at 16:55, Steve Crawford wrote: > I'm searching for projects that would serve as good PostgreSQL examples. In > fact it would be nice to have a place on the PostgreSQL web page that > featured some good examples. (If such a place exists and I missed it, please > let me know.) > > Ideally the project would have the following characteristics: > > -Open-source (naturally, it's hard to use as an example otherwise) > > -Good database structure/normalization > > -Good error detection/recovery > > -Appropriate use of back-end capabilities including functions, triggers and > rules > > -Good security practices > > -Appropriate use of special PostgreSQL features (non-standard data types, > inheritance, etc.) > http://gforge.org/ fork of the sourceforge codebase, now much improved http://www.sql-ledger.com/ double entry accounting system http://openacs.org/ enterprise level web community system Robert Treat -- PostgreSQL :: The Enterprise Open Source Database