Обсуждение: 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