Thread: PostgreSQL as an application server

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Consider this. Most (well-written) applications are written in three
layers. The data abstraction layer provides a clean interface to the
underlying data so other people don't have to write SQL statements. The
GUI layer handles all the GUI events and translates them into function
calls or attribute modifications. Then the layer in between coordinates
the two and often handles more complicated business rules.

A few nights ago, I implemented some of my application logic in PostgreSQL
via PL/PythonU. I was simply amazed at what I was able to do. My question
becomes: Why not get rid of the middle layer and move it into the databse
entirely?

The GUI layer would then merely connect to the database and just connect
user actions to actions in the database, and then suck all the data it
needs disrectly from the databsae with a simple interface. Think SOAP,
but where the SOAP server is on the PostgreSQL, and without all the nasty
overhead.

The layer on top of the database would provide proxy objects that the GUI
could access and modify. These accesses and modifications are translated
into accesses and modifications of the objects underneath. All the
business logic is stored in the database server.

Thoughts? Comments? Hasn't Oracle done something like this?

- --
Jonathan Gardner
jgardner@jonathangardner.net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBExaqqp6r/MVGlwwRAtcTAJ4xXmWFyGWOT0s7gyc9yyr2VtYYtwCeMSbH
5eMhuRiyyB6oVDaLIcdRA64=
=tcLi
-----END PGP SIGNATURE-----

Jonathan M. Gardner wrote:
> Thoughts? Comments? Hasn't Oracle done something like this?

Probably this is more suited to -general?

I haven't done anything near this. I wonder how much more painful it is 
to debug the application, put it under version control, etc. Personally, 
I can't stand editing/debugging application if they are stored in 
something less flexible than a filesystem. Probably in the far future 
Postgres can provide an FTP interface like Zope...

-- 
dave

Jonathan,

This is exactly how my company has built a very robust ERP application.  See www.openmfg.com.

All the ERP business logic is in pl/pgsql (20,000+ lines, very high fiber content).  The GUI is the Qt framework for
C++,which gives us a client in Linux, Windows, Mac OS X, and even wireless (embedded Linux) - with one source tree to
maintain. Bringing up various web views into the system (using python, PHP, etc) is a snap as well.
 

Performance is outstanding, as you would expect.  We have commodity Intel servers running Linux typically serving
dozensof concurrent users of a very transaction-intensive system.
 

Cheers,
Ned Lilly




Jonathan M. Gardner wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Consider this. Most (well-written) applications are written in three 
> layers. The data abstraction layer provides a clean interface to the 
> underlying data so other people don't have to write SQL statements. The 
> GUI layer handles all the GUI events and translates them into function 
> calls or attribute modifications. Then the layer in between coordinates 
> the two and often handles more complicated business rules.
> 
> A few nights ago, I implemented some of my application logic in PostgreSQL 
> via PL/PythonU. I was simply amazed at what I was able to do. My question 
> becomes: Why not get rid of the middle layer and move it into the databse 
> entirely?
> 
> The GUI layer would then merely connect to the database and just connect 
> user actions to actions in the database, and then suck all the data it 
> needs disrectly from the databsae with a simple interface. Think SOAP, 
> but where the SOAP server is on the PostgreSQL, and without all the nasty 
> overhead.
> 
> The layer on top of the database would provide proxy objects that the GUI 
> could access and modify. These accesses and modifications are translated 
> into accesses and modifications of the objects underneath. All the 
> business logic is stored in the database server.
> 
> Thoughts? Comments? Hasn't Oracle done something like this?

On Thu, 2004-08-05 at 22:27, Jonathan M. Gardner wrote:
> A few nights ago, I implemented some of my application logic in PostgreSQL 
> via PL/PythonU. I was simply amazed at what I was able to do. My question 
> becomes: Why not get rid of the middle layer and move it into the databse 
> entirely?


In the mid-90s, I was a lead on just such an app using Oracle PL/SQL. 
IIRC, it was about a half million lines of code.  At that time, Oracle
hadn't seen anything quite like it and was pretty interested in what
we'd done.  I'll add that if we knew what it would be like when we
started, we probably would have done it somewhat differently than we
did.

The advantage is that you have very tight integration with the database
and there are few or no external dependencies to worry about.  For
database heavy applications that require some level of portability, this
isn't a bad way to do development.

The major disadvantage is that the development environment and tools for
in-database languages aren't nearly as rich as your typical standalone
environment, which makes programming a pain in the ass for many types of
codes.  I might have missed something in the intervening years, but I
don't think anyone has really bridged that gap.  The database guys
generally don't like running application code in their database, mostly
because it creates new failure modes and problems that they have to
manage.  For example, at least in older versions of Oracle, if you
accidentally programmed an infinite loop or some other busy
non-functioning state, it took the DBA to kill it.  In the course of
application development, this could happen many, many times as code was
being debugged, much to the annoyance of the DBAs.

That said, I don't see any obvious reason why it couldn't be done well
with a moderate amount of effort.


j. andrew rogers

> The major disadvantage is that the development environment and tools for
> in-database languages aren't nearly as rich as your typical standalone
> environment, which makes programming a pain in the ass for many types of
> codes.  I might have missed something in the intervening years, but I


Although the gap still exists within the environment itself, one
significant advantage with PostgreSQL is you can use a more native (to
the programmer anyway) language to generate your logic.

With PostgreSQL alone you can use plPerl, plPython and plPHP. The
language itself hasn't change in it's implementation of the pL. You just
have to remember to make all ' a '' :) (at least for the most part).

Sincerely,

Joshua D. Drake




> don't think anyone has really bridged that gap.  The database guys
> generally don't like running application code in their database, mostly
> because it creates new failure modes and problems that they have to
> manage.  For example, at least in older versions of Oracle, if you
> accidentally programmed an infinite loop or some other busy
> non-functioning state, it took the DBA to kill it.  In the course of
> application development, this could happen many, many times as code was
> being debugged, much to the annoyance of the DBAs.
>
> That said, I don't see any obvious reason why it couldn't be done well
> with a moderate amount of effort.
>
>
> j. andrew rogers
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

On Fri, 2004-08-06 at 10:53, Joshua D. Drake wrote:
> Although the gap still exists within the environment itself, one 
> significant advantage with PostgreSQL is you can use a more native (to 
> the programmer anyway) language to generate your logic.
> 
> With PostgreSQL alone you can use plPerl, plPython and plPHP. The 
> language itself hasn't change in it's implementation of the pL. You just
> have to remember to make all ' a '' :) (at least for the most part).


One of the things I very much like about PostgreSQL is that it feels
like more of a programmer's RDBMS than Oracle.  As in the needs and
preferences of programmers were obviously given a higher priority in the
design of PostgreSQL.  I find this to be a very attractive feature and a
good thing.

This is a case of where a focus on the needs and preferences of hackers
in the development of the software by hackers has worked out pretty
well, at least for me.  People say that is a bad thing about a lot of
OSS, but I actually think it was needed in RDBMS software.


j. andrew rogers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Friday 06 August 2004 10:00 am, J. Andrew Rogers wrote:
>
> The major disadvantage is that the development environment and tools
> for in-database languages aren't nearly as rich as your typical
> standalone environment, which makes programming a pain in the ass for
> many types of codes.

I've been using PL/PythonU with great success. I haven't plugged in a
debugger yet, but I don't see why that isn't possible. As far as error
handling goes, the exceptions are caught like native database exceptions,
and the information presented is more than enough to solve the problems.

- --
Jonathan Gardner
jgardner@jonathangardner.net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBFc+Vqp6r/MVGlwwRAl1XAJ4ic4l5OVVmsPWF9JZCestI4EnJmwCgtzIe
wW198egMzUxDmu3yH8u2rPk=
=Kenn
-----END PGP SIGNATURE-----

ned@nedscape.com (Ned Lilly) wrote in message news:<4113AB0A.5020903@nedscape.com>...
> Jonathan,
> 
> This is exactly how my company has built a very robust ERP application.  See www.openmfg.com.
> 
Hi Mr. Lilly:

I not sure who to address this question to at your company. 

I'm a valued added reseller who combines a dozen plus products, CAD
engineering and project management to serve a construction niche
market. Probably a third of our dollar purchases go to our primary
OEM. Though they are vital to us we are probably less than 5% of their
sales.

Back in March 2003 I lobbied (e-mailed .pdf files and web URLs; made
calls, followed up) both the company president and the general manager
of that OEM to consider OpenMFG in lieu of a MRP system proposed by a
Michigan based Microsoft "partner". Last week the general manager
mentioned in passing that she was going back to the office this
weekend to begin learning their new OpenMFG software. [ Hummm. I wish
we were in "the loop" more. :>) ]

Our micro business also wants to update its accounting, sales and
project management software/processes. Even SOHO businesses like
ourselves dispair of order entry and vendor acknowledgements via a
stream of faxes. As an on-going buyer from your OpemMFG customer how
can we can easily upload/download purchase orders and receive back
acknowledgements? From a transaction processing standpoint faxes are
utterly "brain dead". E-mailing Excel files is marginally better but
still very labor intensive, error prone and not easily manipulated or
indexed.

This next week we will contract with a free lance programmer/analyst
to update our Access '97 VBA based accounting software to an Access
2003 front end and a SQL Server 2005 Express backend (ODBC links).
This involves upsizing our static customer, vendor, project and job
related revenue and cost transaction data to a format we can
manipulate in Access 2003 or independently.

Does your "Sales Order Management" have some kind of file related
batch processing that would allow us to e-mail orders to our OEM (your
OpenMFG customer)? This OEM says they only need occassional dial-up
for internet access so web portal data entry is not an option. That's
OK. I prefer not to rekey orders on our end anyway.

Presently our faxed orders to this OEM are 1-5 pages of bone wearying
detail printed from Excel spreadsheets. The detail comes from macros
acting on "lookup" Excel worksheets, reformated and with quantities
added, extended and totaled. The "lookup" Excel worksheets are created
from this OEM's product-price schedules faxed to us, OCR'd, edited and
copied to Excel.

Is there any way our OEM can grab a snap shot of the pertinent tables
from your OpenMFG product PostgreSQL database, export them (file
format?) and e-mail to us? Then we could revised our Excel quotation
spreadsheets to "lookup"
product/price/description/weight/dimensions/cubes/notations from SQL
tables (via ODBC, OLE.DB or 3rd party software). If we get the job and
the quote becomes an order we could then sort product line-items by
OEM, copy/paste to Access 2003 and (hopefully) export to whatever file
format will batch process back into OpenMFG's "Sales Order Management"
module.

For what it may be worth we've already started on the XSL/XSLT
learning curve with Altova's XMLSPY 2004 Professional Edition. Though
XML is intended for machine to machine interaction we use XML to keep
track of sheets of different sizes and hundreds of "off cut" parts.
XML is the native import/export format for the panel nesting program
we use. We've gotten familar with cutting and pasting the XML data
file. Does this have application?  See especially
http://www.altova.com/whatsnew.html#db .

I'm probably being simplistic above. Hey, a micro/SOHO distributor can
dream, can't they?  How far through does OpenMFG (or related software)
followed the "food chain" downstream?

Thank you in advance for your thoughts (or referral within your
company to whoever can assist us).

Alanzo Manton