Submitted to Sourceforge.net

Tonight I submitted the application to add TPAS to sourcforge. We will know in a couple of days if we were accepted or denied.

Help System

I've been thinking about how to implement a help system for TPAS. My first thought was to add 'context sensitve' help links (ex [?] ) in places were I think help might be useful. When the user clicks on the link, a popup window or CSS popup is displayed witht the help. All of the help entries would be stored in the database system and would be accessed though a PHP class. An index of all the help entries could be easily generated. Instead of storing the help in the database, it could be stored in files (plain text, psedo-html, or xml) and read by the php class. What do you think? Is this overkill? not enough? just right?

Big Three Bake Off

Since all three database servers (Postgresql, MySQL and MS SQL) are now working with TPAS, I thought I'd have alittle friendly speed compititon between the three. The results were somewhat surprising. Here they are:

big three back off

 load inital tables

     mysql: 5.2578

     mssql: 0.5065

     pgsql: 1.5341

 

 load inital tables and data:

     mysql: 144.944

     mssql: 8.9279

     pgsql: 120.9597

 

 uninstall TPAS:

     mysql: 0.9318

     mssql: 0.1975

     pgsql: 0.3979

 

 display all periodicals (253 periodicals):

     mysql: 6.1405

     mssql: 18.0786

     pgsql: 9.0072

Conclustion: MS SQL is fastest for loading data. MySQL is fastest for getting it out. Postgresql is somewhere in between. I'm not sure why the loading of the inital data so heavely favors MS SQL. It could be that MS SQL was running in it's native enviroment while Postgresql was running under Cygwin and MySQL's native water is Linux, but it was running on Windows. I used the same server for all the tests. The database servers were all running on the web server.

Track search hits?

I had an idea that might help with statistics: to keep track of periodicals that show up in searches. This would provide a way of seeing how many times a periodical came up in a search vs. the number of times it was clicked on. Would this be a useful feature? The only downside I see is it might slow down searching.

Posgresql is done.

I've finished testing TPAS with postgresql. The transiton went faily smothly. It helped that TPAS was already setup to handle multiple database in tricky spots. The worst part about having multiple database is using date and time type variables. Each database server has it's own way of dealing with dates and times. The other tricky part is the slight differences between data types in the different databases.

Posgresql, you're next

I've begun testing TPAS against Postgresql. This should go pretty smoothly because the multi-database code is already in TPAS and postgres supports more complicated SQL. Once all three databases work with TPAS, I will have a little race between the 3 and see which one is the fastest.

Up to speed: MySQL

Using TPAS with MySQL is now upto speed with using MS SQL. I will continue to debug and maybe find more MySQL related issues. All new features from this point on will be tested with both MS SQL and MySQL.

MySQL, not there yet

There are a few features that TPAS relies on that MySQL does not support. Theses are: User Defined Functions and Sub-queries. Without theses features, and others (that I haven't used in TPAS) such as Stored Procedures and Triggers, MySQL cannot compete with it's major rival, MS SQL Server or it's open source rival, Postgresql. Postgresql, is one of the Open Source worlds most overlooked, yet very good, programs. It supports, as far as I know, all of the features that are missing in MySQL. One another note, the default table type in MySQL is not ACID compliant and does not support foreign keys. If there is anything I learned in COS 350, Database Concepts, it's the importance of having ACID compliant tables and having key constraints. Thanks, Dr. Aguilar :). Fortunately, MySQL has the InnoDB table type, which provides Foreign Key Constrains and is fully ACID compliant.

For small applications, not needing much database support, MySQL seems to be a good option, but for highly database driven applications, MySQL, may not be the best choice. Postgresql, which is also Open Source, has all of the much needed features missing in MySQL.

What does this mean for TPAS? I'm not sure yet. There are two paths that I see, drop MySQL support, or rewrite the parts that depend on the missing features.