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