Thread: Application Design and PostgreSQL
Hi, i asked some question about this topic few weeks ago, but i am still thinking about good application design with postgresql. good apps have three components: - frontend (like html, gui, psql etc.) - middleware (Objectorientated modules) - backend (postgresql) But then i thought: Do i really need complex middleware or is it just a wrapper around PostgreSQL?? I think its possible to implement the whole logic inside postgresql. You always have some persistent objects and some non-persistent objects. and persistent objects may have some attributes which are not-persistant. now i build classes inside postgresql. persistents objects => tables non-persistent obj. => views and so on and i also can implement all object methods inside postgresql like init() methods inside an object are triggers inside postgresql. if i add a new object to database i can implement a function which accepts the new data and checks it, so i can also do the error checking in postgresql. i still have big problems with thinking about this stuff. I already wrote some oo-apps and i am familiar with it. but i always took a home-brew solution for object persistence. In these applications the whole logic was programmed inside the oo-modules. i am still looking for a real world example like a cd database which has an object orientaded interface and storing persistent objects in postgres. and all book just describe SQL _OR_ OO-Programming. Anybody here who likes to keep my brain burning?? Can anybody show me a small app with oo design and postgfresql persistence? Any hints for further readings or books? anybody who has the same problems and wants to discuss those things. janning -- Planwerk 6 /websolutions Herzogstraße 86 40215 Düsseldorf fon 0211-6015919 fax 0211-6015917 http://www.planwerk6.de
Hi Thanks for your very detailed answer. but i am still not sure about few points Am Dienstag, 17. Juli 2001 19:03 schrieb Chad R. Larson: > On Tue, Jul 17, 2001 at 12:59:09PM +0200, Janning Vygen wrote: > > But then i thought: Do i really need complex middleware or is it > > just a wrapper around PostgreSQL?? > > The middleware is the part that is particularly custom to your > world. It should be written as portably as possible, for example > Java Servlets in a container such as Tomcat (free) or JRun (not > free). > > The communication to the database engine should be only ANSI SQL > via ODBC or JDBC. ok i got your point. vendor independency is great! > > I think its possible to implement the whole logic inside > > postgresql. > > Perhaps. And if your application is for internal use only, and you > never imagine selling a license version to anyone, and you'll never > get into the ASP business with your app, go for it. but first whats the hype about object orientaded databases? Why do i need them? and second. what happens if you have a custom application with a database backend and you still have a frontend like pgsql? A customer wants to have a html based formular for their customers to insert rows into the tables and still wants to access this databse via MS Access or other tools. If i have all logic inside the database i dont need a middleware to prevent the data from getting corrupted via other tools. wouldnt it be fine to have all logic inside the database and i can use my object orientaded wrapper or a frontend like pgsql or something else. It seems to me that it would make more sense if all logic is inside the databse because i can prevent my data from being corrupted. AND: What happens if i change a type from int2 to int4 inside the databse. i still have to maintain the middleware. So i always have to keep two things up to date. There will always be some logic inside the database at least something like types. Is your opinion to put mostly every business logic inside the middleware just because the object orientated approach inside databases isnt standarized yet? thanks a lot again for your very detailed answers. It was a great help! janning -- Planwerk 6 /websolutions Herzogstra�e 86 40215 D�sseldorf fon 0211-6015919 fax 0211-6015917 http://www.planwerk6.de
> If i have all logic inside the database i dont need a middleware to > prevent the data from getting corrupted via other tools. > > wouldnt it be fine to have all logic inside the database and i can > use my object orientaded wrapper or a frontend like pgsql or > something else. It seems to me that it would make more sense if all > logic is inside the databse because i can prevent my data from being > corrupted. > > AND: What happens if i change a type from int2 to int4 inside the > databse. i still have to maintain the middleware. So i always have to > keep two things up to date. There will always be some logic inside > the database at least something like types. > > Is your opinion to put mostly every business logic inside the > middleware just because the object orientated approach inside > databases isnt standarized yet? Middleware is good! By using a database as a fancy "filing cabinet," and implementing the logic in the middleware applications that feed the DB all the SQL commands and format the results returned by the database, you've not only reduced your dependence on one vendor, but you've enabled your company to increase the scale of its operations in a more agile manner. A personal example: My first database driven website used MS Access as the database and ColdFusion as the middleware product. Everything was fine until visitors started accessing the site. ;) Shortly thereafter, I switched from using MS Access to MS SQL server. All I had to do was dump the data from Access & import the data into MS SQL server. I didn't need to modify my application to access the more powerful database. Six months after the MS SQL server database couldn't handle the load, I migrated from the MS SQL server database to Oracle, and again didn't have to change any of my middleware code. Five months after our budget cuts, I dumped the outrageous Oracle support fees, and moved the database over to PostgreSQL, and migrated the ColdFusion middleware applications over to an open source solution [Linux, mod_perl, DBI and Apache::DBI] with just a little bit of effort. The open source solution has been in use for three years, and has proven to be far more reliable, robust, FLEXIBLE, and AGILE than any of the far more costly, closed source solutions I had previously implemented at Prudential and Merrill Lynch. Had it not been for the middleware layer, I would never have been able to make the transitions that were required to enable our clients to flourish. Bye!
Different problems require different solutions. I try not to drive a thumbtack with a sledgehammer - usually. ;) I provided a personal experience because the solution you select will be dependent upon on the specific project & client requirements. More personal experience: I found that many of our internal clients were using different databases. Some clients had DBA's to administer their Oracle database farms, while other clients had a computer literate individual to watch over their Microsoft Access Database. The level of experience, and breadth of knowledge varied considerably from client to client. By implementing the business logic within the application layer, we were able to create applications that easily worked with the client's preferred database. Additionally, we were most often involved in creating applications that extracted data from a database owned & operated by individuals & groups beyond our control & influence. Some DBA's would only allow others to access their databases using stored procedures, while other departments and groups allowed dynamic SQL to be used. Considering the easy availability of a considerable number of data storage solutions available around the globe, we decided to incorporate our business logic within the application layer. This helped us create some standardized application components that we were able to REUSE for a number clients. We were not forced to constantly retool when a new client surfaced, or an established client decided to switch to a different data storage solution. Middleware makes this agility possible, and I believe that it should be the preferred solution to help keep pace with information technologies rapid change. We've had quite a number of clients switch from using MS Access to more scalable solutions, and few of them foresaw the need at the time they chose MS Access... If only they had the foresight to have chosen PostgreSQL to begin with. :) Ian Harding wrote: > > If you start with PostgreSQL, you can put your logic in the database, as you will prevent any requirement to migrate downthe road! <BSEG> This from someone who is currently migrating stored procedures and triggers from SQL Server to PostgreSQL... However, I don't have to change my front end app (AOLServer) much at all. >
Bruno Wolff III wrote: > > I think having the business rules in the applications is a good way to > lock your customers into your tools. Unless you provide good and current > documentation about the business rules, writing local tools to update > the database is difficult to do with any confidence. > > If the integrity checking is in the database, the constaints document > the business rules and will keep broken programs from updating the database > in a way that breaks the assumptions made by the applications programs > that are written in the database. > > I find dealing with PeopleSoft and Magic helpdesk databases frustrating, > because it isn't easy to get the rules used by their applications. We > either have to use their crappy labor intensive interfaces for entering > data or try to figure out what the rules are and hope we get things > right writing scripts for simplifying administration. I've personally found that open source tools provide the best solution, and prefer the mod_perl, Apache, and DBI solution. They're free, provide no vendor lockin, and have been around for a while [they have legs], and outside the web sphere, you can quickly utilize the perl/DBI solution for just about anything pulling data from a database, not to mention interfacing with email servers, encryption, and the hundreds of other modules freely available from cpan. Since we specialized in moving data from any database to the desktop via a browser client, we first used ColdFusion. This closed solution was not as flexible as our open source choice, but it allowed us to "get our feet wet" using a standard tool with wide programmer support available. Integrity checking in a database is necessary, but attempting to construct an application with triggers & stored procedures will lock you into a particular database, or at the very least, make it very costly and time consuming to move to a different platform tomorrow... This approach also isn't very productive if you're charged with developing applications that need to extract data from Sybase, DB2, Oracle and MS SQL server databases, but if you're just using one database you can pretty much do what you want. Many large companies, and consulting groups serving the needs of many large & small companies need to develop flexible solutions to the myriad of databases their clients have chosen to use. Implementing the logic of your applications in the middleware enables you to develop an application that will work with virtually any database that has a standard ODBC, JDBC, DBI, etc... driver, and the components you develop for one client are usually portable to satisfy another clients requirements. All this is lost if you try to develop all the triggers & stored procedures needed to mimic an application through a database's internal structure. Agile solutions are better implemented through middleware... [Also, I remember having a consulting company charge us $180.00 per hour for an Oracle DBA, but I can get a perl programmer for $70.00 an hour. Do the math... Not only is a middleware solution more flexible, but it's also less expensive...]
I don't agree with the philsophy. A better approach would be put the code in the back end when you can, else fluff it into middleware. Your approach may seem the most flexible however my experience make me believe that the reverse is true. The company that I use to work for had two applications one which used server side logic on oracle the other was a middleware solution that could float on a number of different DBs. Three transitions were The Oracle product was ported to informix. Easy The Oracle/Informix product was web enabled Easy The Middleware product was web enabled Initially appeared easy but was very difficult and had many teething problems. Middleware tends to bloat quickly and hits exceptions get pulled into body of the code. I believe that IBM's DB2 adopted a somewhat similiar architecture to postgresql because they found that middleware was slower and less flexable. -- Ian Willis -----Original Message----- From: Buddy Lee Haystack [mailto:haystack@email.rentzone.org] Sent: Thursday, 19 July 2001 1:39 AM To: Ian Harding Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Application Design and PostgreSQL Different problems require different solutions. I try not to drive a thumbtack with a sledgehammer - usually. ;) I provided a personal experience because the solution you select will be dependent upon on the specific project & client requirements. More personal experience: I found that many of our internal clients were using different databases. Some clients had DBA's to administer their Oracle database farms, while other clients had a computer literate individual to watch over their Microsoft Access Database. The level of experience, and breadth of knowledge varied considerably from client to client. By implementing the business logic within the application layer, we were able to create applications that easily worked with the client's preferred database. Additionally, we were most often involved in creating applications that extracted data from a database owned & operated by individuals & groups beyond our control & influence. Some DBA's would only allow others to access their databases using stored procedures, while other departments and groups allowed dynamic SQL to be used. Considering the easy availability of a considerable number of data storage solutions available around the globe, we decided to incorporate our business logic within the application layer. This helped us create some standardized application components that we were able to REUSE for a number clients. We were not forced to constantly retool when a new client surfaced, or an established client decided to switch to a different data storage solution. Middleware makes this agility possible, and I believe that it should be the preferred solution to help keep pace with information technologies rapid change. We've had quite a number of clients switch from using MS Access to more scalable solutions, and few of them foresaw the need at the time they chose MS Access... If only they had the foresight to have chosen PostgreSQL to begin with. :) Ian Harding wrote: > > If you start with PostgreSQL, you can put your logic in the database, as you will prevent any requirement to migrate down the road! <BSEG> This from someone who is currently migrating stored procedures and triggers from SQL Server to PostgreSQL... However, I don't have to change my front end app (AOLServer) much at all. > ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
On Tue, Jul 17, 2001 at 12:59:09PM +0200, Janning Vygen wrote:
> But then i thought: Do i really need complex middleware or is it just
> a wrapper around PostgreSQL??
Your original thinking is correct. You want a front-end (client),
some middleware that implements your business logic, and then the
database.
The client should be a basic display/input tool, ideally a browser.
Every computer has one these days.
The middleware is the part that is particularly custom to your
world. It should be written as portably as possible, for example
Java Servlets in a container such as Tomcat (free) or JRun (not
free).
The communication to the database engine should be only ANSI SQL via
ODBC or JDBC.
By doing this, you will not be at the mercy of any vendor. You can
swap database engines if desired. You can play Oracle off against
Informix or Sybase by saying, "It doesn't really matter to me, give
me your best deal."
Every vendor will try to get you to use their proprietary extensions
and/or 4GL. Be very aware of the hidden costs of such things. Use
the extensions if it is the =only= way you can build your
application.
We've been through this design cycle several times for some very
large applications, and we've gotten smarter (you might even say
paranoid) about vendor lock-in each time.
> I think its possible to implement the whole logic inside postgresql.
Perhaps. And if your application is for internal use only, and you
never imagine selling a license version to anyone, and you'll never
get into the ASP business with your app, go for it.
But some day, some pesky customer will say, "Yes, but we've already
got a site license to Informix and a bunch of Informix DBAs so we
won't buy unless it can run on Informix (replace your favorite
annoying commercial database vendor here). Layer your application
properly and you can say, "We think that would be a mistake, but we
can accomodate your need."
-crl
--
Chad R. Larson (CRL22) chad@eldocomp.com
Eldorado Computing, Inc. 602-604-3100
5353 North 16th Street, Suite 400
Phoenix, Arizona 85016-3228