At work a few months ago, I was directed to start looking into some options for developing small database applications. So, I set out on my quest to find the best solution out there. Several weeks, and at least as many frameworks later, I finally found a solution that seems to work.
The first thing I did while considering where we should go in the future was to sit down and purposefully consider the past, asking questions and trying to pull from the experience of my coworkers (who have all been working there far longer than me). Many of our old solutions were implemented in MS Access or Paradox. While these are powerful programs that definitely have their place, the presence of many independent databases all over the place was causing problems, from (incorrect) duplication of data, wrong data, difficult-to-support solutions, and frustration on the part of the users. For this reason, we decided that a centrally-based solution would be far more manageable for a small team.
As a result of a centralized system, we would need to make sure that access control was done right. As such, it was decided that the solution should integrate into our existing authentication framework: Novell eDirectory. Sure, that should be easy enough -- we'll just be sure the solution can authenticate to LDAP. Another issue with centralization is that we'd have to be sure that the solution could have centralized data, and more importantly, integrate with our current "official" data sources. Again, no problem -- everything talks to SQL Server, right?
One of the other things I learned from my colleagues, is that with any new little "custom" application, there is a chance that the people requesting it might end up not using it, or might discover that what they thought they needed doesn't in fact fulfill their requirements. While sometimes it's hard to fight to urge to just say, "Decide EXACTLY what you want, then talk to me," that just doesn't work -- because most end users don't have the exposure to the technologies we're bringing to bear to make informed decisions about how they want it to look... exactly. This scenario combined again with the low manpower variable meant that we needed to make sure that a solution would have a rapid development cycle, so we could build it, tear it down, and rebuild it in a timely fashion without putting (too many) things on hold.
Finally, we came up with the following list of criteria for a solution for our custom applications:
- Simple, easy-to-learn user interface
- Rapid development cycle (ie: under two weeks for a small application; under four weeks for a larger one) 
- Integration with our Novell eDirectory
- Backend support for Microsoft SQL Server, MySQL, and Oracle (primarily MS-SQL)
- Solution should be free or relatively low cost, with good support and active community
There were a few initial Commercial software packages I considered. Most of them were companies I'd not heard of before that offered proprietary, Windows-only web server software that generated web interfaces in a similar manner to Access or Paradox. The familiarity of the development seemed nice, but there were limitations all over the place, and while the cost was not prohibitive, I felt that what those solutions offered could be accomplished for far less with much more flexibility and power.
About the same time I started my search, a coworker was preparing to deploy a new intranet based on Drupal, a rather popular open source content management system. While talking with him, we found that our project goals had a lot in common: LDAP integration, simple interface, low cost, low maintenance, etc. This got us thinking that integrating our applications into Drupal would be ideal -- a user would log into our intranet, and have a link on the side to access all the applications that their LDAP group memberships grant them.
Since Drupal was based on PHP, and PHP is an almost pervasive language, I started going through lists on Wikipedia of PHP Frameworks. I was looking for something that would basically write itself for me. After installing and playing with a few of the lesser-known frameworks, I soon discovered that they offered far less than they claimed, or were too buggy to use. Then I happened upon PHP 4 Applications. I was excited, because getting P4A running was very simple, writing forms involved no HTML, it was fairly attractive, and the system just worked. For a while, I thought I had the solution we needed -- that is, until I tried to integrate it with Drupal. Now, I'll be honest and say right now, that I'm not expert at integrating disparate systems with each other -- but getting P4A to run inside of Drupal was nothing short of a nightmare. Even by the time I managed to overcome (most of) the session challenges, the interfaces were completely incompatible. After a couple days, I moved on.
I moved on right into the arms of CakePHP.
I had taken a look at CakePHP about 6 months ago. I had been considering beginning an Open Source project to write a web-based University Security Department management system (more on this some other time), and CakePHP showed up on my radar. I took a closer look while considering it for our needs this time, though, and discovered the riches that CakePHP framework offers. After not even 30 minutes of playing with Cake, I had a functioning web-based database application. I was sold. Then, about an hour later, I happened upon Drake, a Drupal module that integrates CakePHP with Drupal. By the end of the day, I had a proof-of-concept CakePHP application running inside of Drupal via Drake. With some minor modifications to the Drake module, I had per-application permissions based on Drupal's module permission system.
The final piece of the puzzle was a bit of wrestling with the CakePHP MSSQL driver (only needed when using custom finderQuery in a hasManyAndBelongsTo relationship), and we had a working solution that met all the criteria. The first small application was finished in about 7 work days, and now I'm on the second (much larger) project.
|||Development times based on a single programmer working on the project "as he can" through the day, as he is regularly interrupted for support calls, walk-in repairs, etc. -- we are a small IT department.|