Thread: What is your favorite front end for user interaction to postgresql databases?
What is your favorite front end for user interaction to postgresql databases?
From
"Karen Hill"
Date:
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? 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.
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.
Attachment
Re: What is your favorite front end for user interaction to postgresql databases?
From
Casey Duncan
Date:
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
Re: What is your favorite front end for user interaction to postgresql databases?
From
"Michael Schmidt"
Date:
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/
Re: What is your favorite front end for user interaction to postgresql databases?
From
Guido Neitzer
Date:
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
Re: What is your favorite front end for user interaction to postgresql databases?
From
Steve Atkins
Date:
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.
Re: What is your favorite front end for user interaction to postgresql databases?
From
Guido Neitzer
Date:
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
Re: What is your favorite front end for user interaction to postgresql databases?
From
"Jim C. Nasby"
Date:
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