We did a major update to one of our financial sites on Aug 30th 2005, we converted all forms to use DB_Table using HTML_QUICKform . During implementation we faced few problems, let me put it over here.
- Problem with AUTO_INCREMENT
- Problem with Data types
DB_Table (in fact PEAR DB class es) doesn’t support AUTO_INCREMENT as it is not supported across DB architectures. In other words it is non-standard. So we had to avoid nextID() and use the max . I had to make it support AUTO_INCREMENT as we were using three different open source apps on one DB other two running on AUTO_INCREMENT. Make sure when you use DB_Table classes a table with _seq suffix (default, this can be edited as well) is created, so while doing the system conversion to DB_Table make sure you update your _seq tables with the AUTO_INCREMENT count manually else it will give duplicate entry error as it starts from 1. Get more help at http://wiki.ciaweb.net/yawiki/index.php?area=DB_Table&page=GettingStartedManip
Next problem was with date and text data types, we were using timestamp and text as the data type, while debugging with nusphere we found the warnings, it still works though. Then I did a googling and found that we can use varchar or integers for mysql datatype timestamp and clob for text. This solved the problem. read more at http://wiki.ciaweb.net/yawiki/index.php?area=DB_Table&page=DataTypes
We thought we have solved almost all problems but then one table was not getting populated and we tested it many times. In all our tests it was working fine but system was failing randomly making the programmers life more difficult. We guessed, checked and corrected many times but without a complete cure. Then we decided to log the errors to a log file. We sat and thought it will be a long process to put check at each step of mysql connection and operation but thanks to a great PEAR architecture, we implemented a good and efficient error logging system in an hour. We used the Log Package to log the errors. It was quite a simple program.
Now I understand how imp is to have a well defined, stable, community supported and flexible architecture for faster and efficient programming. We are commited towards a simple and scalable architecture based on open and free systems.
Today morning we were able to track and fix the error.