Middleware
by Ed Sawicki
Accelerated Learning Center
Tailored Computers
March 30, 2007
At the March 2007 PANUG meeting, I presented information about using the features of enterprise-class databases in Web sites. I pointed out that mixing business logic with presentation logic, as most PHP-based Web applications do, is poor practice and poor design. The sheer number of security issues with PHP-based Web sites proves that point. I advocated moving business logic out of Web pages and into database stored procedures, triggers, etc.
There was useful feedback during and after the presentation based on the experiences of others in the room who had done this. The most useful for me was something I had already thought about - debugging stored procedures. Those who have had to do it said it was, at times, difficult. Early in my presentation, I mentioned that designing the data structures - the database schema - should take place first with great care - before you start coding or creating tables. This should reduce the number of problems that require debugging stored procedures.
No amount of up-front care will completely eliminate the need for debugging stored procedures for a complex Web application. I chose PostgreSQL for my Web applications because it supports many stored procedure languages. Its native language is PL/pgSQL but it supports others including PL/sh (shell). I can debug my stored procedures in shell script if I wanted to.
One of those attending the meeting advocated using a 3-tier design instead of the 2-tier design I was advocating. This places business logic in a layer between the presentation logic of the Webapp and the database. This layer of code is commonly called middleware. I have never done 3-tier design before so I was interested in what he had to say. PANUG meetings are a great place to learn new things.
He pointed out that the 2-tier design I was advocating had numerous disadvantages. Here are some of them with my responses:
- With my 2-tier design, my Web application would be tied to Postgres.
Postgres might not be available in the future.
Open source enthusiasts know that this is a non-issue. Postgres will be available in the future because it is open source - there's nobody with the authority to kill it. Postgres enthusiasts know that it is actively being developed. It will not likely disappear during my career. - I might want to migrate to another database, such as Oracle, in the future.
My PL/pgSQL stored procedures would need conversion.
I had already considered this and researched the effort it would take to convert Postgres stored procedures to Oracle. The differences between PL/pgSQL (Postgres) and PL/SQL (Oracle) do not seem to be that great to me. Converting my typical Web application may take several hours or a few days- a trivial amount of time compared to the time it takes to develop middleware. - Storing business logic in a database is incredibly slow.
The database hasn't been a performance bottleneck in my Web applications but it could be for busy sites. Understanding query optimization is important when working with a database product. If you're concerned about performance, you should understand this optimization and design your schema to take advantage of it.
There were other disadvantages mentioned as well but, as I said, I've not done a 3-tier design so I won't comment on them. I did spend several hours doing research on the issue of 2-tier versus 3-tier design and found a few things that were not surprising to me:
- I found Web sites that said that business logic absolutely belongs in the database.
- I found Web sites that said that business logic absolutely does not belong in the database. All of these sites advocated the 3-tier middleware design. None advocated putting business logic in the Web page with the presentation logic.
- Most of the sites that advocate middleware focus on a Java/Beans environment. There were far fewer sites that talked about Windows-specific middleware.
- I spoke to one person who had working knowledge of EJB (Enterprise JavaBeans). He said that a typical EJB middleware project is a big project having several team members and a manager. It's a costly effort. It seems that whenever big money is involved, there are deadlines, missed dealines, and ill-written code in an attempt to catch up. The same fellow said that the best results are often obtained when you keep the project inexpensive and under the radar.
PHP became popular in part because companies didn't want to spend a lot on Web application design. Now that their Web sites are being successfully attacked, they need better designed Web sites, but how do they get there?
If they choose the 2-tier approach I advocate, they need someone with good database skills. This means someone who knows how to avoid SQL Injection and other forms of attack. Their costs will increase but only for an additional person. This could be a contractor hired for the project.
The middleware approach requires far more knowledge. The population of people with these skills is relatively small. It's far easier to get up-to-speed on database stored procedures that on Enterprise JavaBeans. I'm guessing that this approach is more expensive by a factor of 10 - maybe more.
Placing business logic in a database seems to be the prudent way to go for most Web applications that don't have the benefit of million dollar budgets. I think the vast majority of Web sites don't need middleware - but I'm still learning.