What is your favorite front end for user interaction to postgresql databases?

Karen Hill wrote:

What is your favorite front end for end users to interact with your
postgresql db? Is it java, .net, web apache + php, MS-Access, ruby on
rails? Why is it your favorite? Which would you recommend for end
users on multiple OSes?

I use PHP for the same reason I use Postgres, easy, powerful, and free.

Also, what do you think of having the database management system do all
work (business logic etc) with the front end as a user interface vs.
having N-tier with an application server handling business logic, and
the db just accepting data. I currently have the opinion that N-tier
is not as good as having the db contain the business logic via stored
procedures. This comes from having to re-invent the wheel every time a
new user app needs to be created.

Agreed. I like to have the database do all of the work, and I like to
use a dictionary-based generator to build the triggers for me.

Shameless plug: My own project, Andromeda, which does just this, is a
GPL project using PHP and PostgreSQL which works as you describe.
http://docs.secdat.com. I consider the project late-beta for my own
projects, but probably early Alpha for others, since there are probably
foibles that don't bother me because I wrote it and know where they are.

On May 8, 2006, at 3:33 PM, Karen Hill wrote:

What is your favorite front end for end users to interact with your
postgresql db? Is it java, .net, web apache + php, MS-Access, ruby on
rails? Why is it your favorite? Which would you recommend for end
users on multiple OSes?

This is totally dependent on the individual user's preference:

- Many users comfortable with command line shells prefer psql (this
is my choice).
- Others who like a gui might use pgAdmin (or miriad others)
- If interoperability with MSOffice is your main concern, I could see
Access/ODBC being useful. As a general front-end, probably not.

When you start talking about java v. .net v. php vs. ruby, that's a
whole other ball of wax. That's a lot less about interaction, or even
about databases and a lot more about programmer preference.
Personally, I would recommend:

Python + psycopg (highly interactive, great for complex scripts or
even apps)
Java + JDBC + Hibernate (I think JDBC sucks, but Hibernate totally
rocks and more or less hides it from you)
Shell + psql (great for simple reporting, automating dba tasks, etc.
etc)

Also, what do you think of having the database management system do
all
work (business logic etc) with the front end as a user interface vs.
having N-tier with an application server handling business logic, and
the db just accepting data. I currently have the opinion that N-tier
is not as good as having the db contain the business logic via stored
procedures. This comes from having to re-invent the wheel every
time a
new user app needs to be created.

From personal experience (and others will disagree), I find putting
logic in the database to be a bad idea. I only use stored procedures
for triggers and the like, and I try to avoid those whenever possible.

Here are my reasons why:

- I don't like the PL/pgSQL language (yes there are alternatives, but
they have their own drawbacks)
- It's complex to test and upgrade (we actually wrote non-trivial
infrastructure to automate both)
- It's difficult to debug (compared to external languages like python
or java)
- It's difficult to profile, therefore getting good performance can
be difficult

I had a very complex system coded in stored procedures that performed
poorly and was hard to maintain. It's now refactored into java/
hibernate code that's simpler to understand, performs much better and
is easy to extend and maintain. Of course that's just my particular
case and obviously YMMV.

Stored procs could make a lot of sense if you have many different
clients accessing the db in different ways and you want to strictly
enforce business rules across all of them. I had no such requirements
in my case.

In any case I would strongly recommend doing the simplest thing that
you can get away with. If your business rules can be fulfilled with
grants, views and constraints alone, use them.

-Casey

I agonized over this decision when I migrated my application to PostgreSQL. I know Tony Caduto likes Delphi and he can give some good reasons for this (are you out there, Tony?). There has also been a good article recently about Ruby on Rails. For my purposes, I wanted to maximize cross-platform compatibility and decided on Java. I was advised (correctly, I think) to opt for SWT rather than standard Java Swing and ended up with Eclipse. I must say that Eclipse has not been particularly easy to learn - the documentation is quite uneven and the mail lists aren't nearly as helpful as with the PostgreSQL community. Also, Eclipse is changing so quickly that books have a hard time keeping up. Nevertheless, it is powerful and I've found it rewarding. To make things easier for others, I uploaded a basic Eclipse front end (without editors, views, and reports) to pgFoundry (Komo PostgreSQL Client).

On the second question, I also like to have the database do as much of the work as possible.

Michael Schmidt

Michael Schmidt wrote:

I agonized over this decision when I migrated my application to
PostgreSQL. I know Tony Caduto likes Delphi and he can give some good
reasons for this (are you out there, Tony?). There has also been a good
article recently about Ruby on Rails.

Well it depends on your needs. I am partial to:

http://www.djangoproject.com for web stuff but I also hear really good
things about Turbogears. Both of these are Python based which has
excellent cross platform capabilities (including Win32).

In general Ruby on Rails *feels* like more a vehicle with 600 HP just to
drive 10 miles to work. Why?

Also in terms of Delphi... I would actually pick Python over Delphi at
this point. I like Delphi and have actually written a couple of
articles on Kylix (Delphi for Linux) but IMHO the time for closed
languages is over.

Use a language that is OSS and actively developed. Python, Ruby or if
you must Perl or Java ;)

Joshua D. Drake

On the second question, I also like to have the database do as much of
the work as possible.

Michael Schmidt

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

On 09.05.2006, at 0:33 Uhr, Karen Hill wrote:

What is your favorite front end for end users to interact with your
postgresql db? Is it java, .net, web apache + php, MS-Access, ruby on
rails? Why is it your favorite? Which would you recommend for end
users on multiple OSes?

You mean what technology to use to build business logic?

<advertisement>

It just depends on your needs. I prefer to use Apple WebObjects for
building web frontends to the db.

Why? Because it is extremely powerful, free if you are able to
develop on Mac OS X (it comes with the developer tools) and even if
you have to buy a couple of Powerbooks and Xserves it may be cheaper
then only the software license for one of the other big Java
application server technologies. You can deploy it to every Java
plattform because it's just a Java application, but it's build on and
for Mac OS X. It completely frees you from thinking in tables, joins,
selects and other sql stuff, well, at least nearly completely. I can
switch my whole application including a couple of hundred MB database
content in half a day (mostly the time to transfer the data) - I have
done this two months ago from another dbms to PostgreSQL.

It's just as "cool" as Ruby on Rails right now, not as fat and slow
in development as most J2EE servers, you can deploy everywhere, I can
develop on Mac OS X (yes, that's a reason for me, because it speeds
up my work by a good percentage), it is extremely fast for
development once you have the concepts and a good set of your own
components in stock which you are used to. It's pure Java, so I can
easily include third party tools like Lucene or others. It does one
connection per application instance which keeps concurrent db tasks
low. Also it has more than one working approach for multi language /
internationalized applications.

And, most important, it has a tool, which enables me to do things
alone in days, other teams do in weeks with other J2EE tools:
DirectToWeb - a rule and template driven dev plattform. You write the
db schema, lots of "rules" how list, inspect, edit and search pages
should look like and some basic html for wrappers and you can create
an administration application faster as with everything I've seen so
far. Including Ruby on Rails, PHP and other Java tools. But, to make
this clear: the starting point to use it is VERY high. I had a
couple of years doing WebObjects development when I started using D2W
and it was good to have this background.

Also you can build WebServices, rich Java client apps, can use Ajax
for rich web applications and I have a tool that proved it's
scalability, reliability and ease of use in a real high volume
environment: the iTunes Music Store.

For more information, send me an email and/or go to <http://
www.apple.com/webobjects>. (I hope the url is correct, I'm not online
right now - sitting in the train to work .... ;-))

Ah, and if you download it for testing: go to one of the WO mailing
lists and ask for some good tips for starters. It will help.

</advertisement>

Also, what do you think of having the database management system do
all
work (business logic etc) with the front end as a user interface vs.
having N-tier with an application server handling business logic, and
the db just accepting data.

I prefer the second approach because it frees me from one specific
database. I can use Oracle, PostgreSQL, MySQL, FrontBase, OpenBase,
Sybase, MSSQL without changing one line of code (if I have avoided
using custom SQL, which I mostly do) by just switching one framework.
Okay, right, there are always some problems you have to solve, but
they are nothing compared to what a PHP developer has to do to use
different dbms as the one he develops on.

This comes from having to re-invent the wheel every time a
new user app needs to be created.

You do something wrong if you don't build libraries of your basic and
generic work over the time. This has nothing to do with the tool you
use or the underlying dbms - it's just bad coding style if you have
to re-invent the wheel for every app.

For me and our customers it's more important to be able to switch the
dbms than the application server.

But, as with everything: YMMV.

Ah, and for curiosity I'm just evaluating Ruby on Rails: it has
interesting ideas and places itself somewhere between the fat J2EE
app servers and the scripting languages PHP and Perl. It is more
closely placed to WebObjects then I have thought on the first look,
but is a bit easier to learn and not the big 800 pound gorilla of web
development, with hooks, tool, templates, ideas and concepts for
nearly every case.

But in my opinion it lacks a couple of things, two of the most
important for me are complete abstraction from the db (you may reach
this with easy schemas and generic sql but not enough) and a working
internationalization approach. It is lightweight and it would be
interesting to build an application with it. Perhaps, if I have
something with a smaller db schema (under 30 tables) I will try it.

cug

On May 8, 2006, at 11:05 PM, Guido Neitzer wrote:

On 09.05.2006, at 0:33 Uhr, Karen Hill wrote:

What is your favorite front end for end users to interact with your
postgresql db? Is it java, .net, web apache + php, MS-Access,
ruby on
rails? Why is it your favorite? Which would you recommend for end
users on multiple OSes?

You mean what technology to use to build business logic?

<advertisement>

It just depends on your needs. I prefer to use Apple WebObjects for
building web frontends to the db.

Why? Because it is extremely powerful, free if you are able to
develop on Mac OS X (it comes with the developer tools) and even if
you have to buy a couple of Powerbooks and Xserves it may be
cheaper then only the software license for one of the other big
Java application server technologies. You can deploy it to every
Java plattform because it's just a Java application

Is that actually true? My understanding was that under the most recent
license changes it was not possible to deploy it to any platform other
than XServe. Technically possible, sure, but a violation of the license.
That's the main reason I stopped considering it a viable development
environment.

Cheers,
Steve

Steve Atkins wrote:

On May 8, 2006, at 11:05 PM, Guido Neitzer wrote:

On 09.05.2006, at 0:33 Uhr, Karen Hill wrote:

What is your favorite front end for end users to interact with your
postgresql db? Is it java, .net, web apache + php, MS-Access, ruby on
rails? Why is it your favorite? Which would you recommend for end
users on multiple OSes?

You mean what technology to use to build business logic?

<advertisement>

It just depends on your needs. I prefer to use Apple WebObjects for
building web frontends to the db.

Why? Because it is extremely powerful, free if you are able to
develop on Mac OS X (it comes with the developer tools) and even if
you have to buy a couple of Powerbooks and Xserves it may be cheaper
then only the software license for one of the other big Java
application server technologies. You can deploy it to every Java
plattform because it's just a Java application

Is that actually true? My understanding was that under the most recent
license changes it was not possible to deploy it to any platform other
than XServe. Technically possible, sure, but a violation of the license.
That's the main reason I stopped considering it a viable development
environment.

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

from http://developer.apple.com/softwarelicensing/agreements/webobjects.html

WebObjects Distribution License

*WebObjects Distribution License
$699 per copy*

WebObjects, the premier web application server used by hundreds of
corporations, is now available for redistribution by web application
developers just like you.

Upon signature by Apple, the agreement, together with payment for your
initial request of licenses, allows you to resell WebObjects license
keys, and redistribute the WebObjects deployment runtime and adaptors as
part of your web application. The WebObjects runtime includes a powerful
object-relational engine for extracting and managing data from virtually
any database, without writing a single line of SQL. Its HTML component
model makes it a breeze to assemble dynamic, fully customizable web
pages. There�s even support for rich Java clients and Web services.

On 09.05.2006, at 16:31 Uhr, Steve Atkins wrote:

Is that actually true? My understanding was that under the most recent
license changes it was not possible to deploy it to any platform other
than XServe.

Wrong. You are allowed to deploy on any platform you like, but only
Mac OS X Server is officially supported by Apple.

Please note that this is for 5.3.1 - there was a bad license
formulation in 5.3 which actually did not allow the deployment. For
5.3.1 this was corrected. The issue came up when WebObjects was
released free as part of the Xcode tools and a new license was
necessary which wasn't well written.

Technically possible, sure, but a violation of the license.

Nope. Cliff Tuel of Apple clarified this on the WO mailing lists.

cug

On 09.05.2006, at 16:52 Uhr, Reid Thompson wrote:

*WebObjects Distribution License
$699 per copy*

WebObjects, the premier web application server used by hundreds of
corporations, is now available for redistribution by web
application developers just like you.

Upon signature by Apple, the agreement, together with payment for
your initial request of licenses, allows you to resell WebObjects
license keys, and redistribute the WebObjects deployment runtime
and adaptors as part of your web application. The WebObjects
runtime includes a powerful object-relational engine for extracting
and managing data from virtually any database, without writing a
single line of SQL. Its HTML component model makes it a breeze to
assemble dynamic, fully customizable web pages. There’s even
support for rich Java clients and Web services.

This is for WO 5.2.4. WO 5.3.1 (current release) is free for
development on Mac OS X and free for deployment on any chosen platform.

cug

On Mon, May 08, 2006 at 04:17:53PM -0700, Casey Duncan wrote:

From personal experience (and others will disagree), I find putting
logic in the database to be a bad idea. I only use stored procedures
for triggers and the like, and I try to avoid those whenever possible.

Here are my reasons why:

- I don't like the PL/pgSQL language (yes there are alternatives, but
they have their own drawbacks)

Huh? Just use whatever language you're already writing in.

- It's complex to test and upgrade (we actually wrote non-trivial
infrastructure to automate both)

Uhm... CREATE OR REPLACE FUNCTION. Unless you're changing parameters it
works fine.

As for testing, I fail to see how it's more difficult than testing the
same thing using external code. I will grant that unit testing is harder
though, since you don't have as many opportunities to hook into the
code, but if you create a set of known test data it's not all that
difficult.

- It's difficult to debug (compared to external languages like python
or java)
- It's difficult to profile, therefore getting good performance can
be difficult

Actually, I think there's a commercial product that allows you to do
both, but I'm not sure. It would certanly be nice if it was built in.

I had a very complex system coded in stored procedures that performed
poorly and was hard to maintain. It's now refactored into java/
hibernate code that's simpler to understand, performs much better and
is easy to extend and maintain. Of course that's just my particular
case and obviously YMMV.

If hibernate is performing better it's due to application design. A lot
of times people try and approach database development the same way you'd
approach procedural coding, which is a bad idea. Hibernate and other
products go to great lengths (ie: caching) to try and make procedural
coding techniques work well on databases.

Stored procs could make a lot of sense if you have many different
clients accessing the db in different ways and you want to strictly
enforce business rules across all of them. I had no such requirements
in my case.

You sure there won't every be any other apps hitting that database? :)
Part of how Pervasive makes money is dealing with exactly that kind of
attitude... "nothing else will ever have to communicate with this
system".

In any case I would strongly recommend doing the simplest thing that
you can get away with. If your business rules can be fulfilled with
grants, views and constraints alone, use them.

Or maybe more accurately, do what you have the expertise for. If you've
got a good database developer on staff there's a lot to be said for
putting stuff into procedures, especially if it's database-intensive.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461