While there are many areas of Xoops that need upgrading, recently a discussion began about upgrading the database engine. This is an area that should be looked at soon. This thread is intended to discuss this and only this.

Reason for this thread is that Wishcraft is pushing to add ADOdb as he has been doing for at least a couple of years that I am aware of. Mamba has challenged that we should be using PDO.. I started to get into it as well with some generalizations but I decided to spend some time and look into it.

If you do a simple Google search of PDO and ADOdb you can find some interesting information about their comparisons...

The main reason to look into a replacement for our current database is that the current database core is very old and does not allow for other databases to be used. From what is available the two best options are the above mentioned two for different reasons. Both are stable, mature and used by many. Using either of these would add some strength to Xoops in the long haul and it is something that needs to be at least considered at some point. There are other systems available as well but realistically the choice really becomes between these two. PEAR has a system but realistically there is no reason to use it over PDO and I saw mention that Zend uses PDO as its base and builds on that so if that is true then it would then obviously be slower than PDO. Also do not confuse the ADOdb mentioned here which resides at adodb.sourceforge.net with the PHP included PEAR module by the same name. They are completely different animals and from the benchmarks I have seen this system is faster than PEAR:ADOdb and has much more "toys"

So, when I first started looking into this I thought it should be an easy decision. However it is really a mixed bag. I will describe each system in separate messages and then another message to tie it all together. (As opposed to one very large message.)

It should also be noted that Native drivers are the fastest method of connecting to any database. These are the drivers delivered with the databases to connect to the databases but they are very specific to that database and are not generally usable by other databases. The whole point of PDO, ADOdb and others is to add a layer on top of these Native drivers to allow you to use ANY of the supported databases.

PDO is a library written totally in C++ or C and has been included as part of PHP since 5.1. Because it is a part of PHP it stays loaded and doesn't need to be reloaded like add on Libraries like ADOdb have to. PDO is the fastest solution available right now for PHP because of the optimizations done over time as well as the fact it is always loaded. This Library is used as the base for many applications that will be used by many people as it doesn't require anything to make it work fast. It works fast right out of the box. You can find many examples of applications that use PDO such as Drupal and many others.

Where PDO does lag behind other systems somewhat is in the security of the system. More could be done to prevent Database Injection or other database based attacks. Also PDO lacks many of the features an enterprise company may use to audit and maintain the databases. For the general user that wants to just use a system without any extra fuss PDO is the way to go which is why you find PDO as the system used for Drupal and many other systems. It may lack many of the features some of the other libraries have but for the vast majority of users out there it does all they need without need of further add ons.

ADOdb as mentioned here is found at adodb.sourceforge.net and is NOT the PEAR module by the same name. Benchmarks showed ADOdb as faster than any other PHP based solution. (I mention the pear module because that is the module Wishcraft was pointing out that this one was not. If you read the emails you know what I mean.)

Anyhow what does ADOdb do that makes it worth a look? From what I read it has more tools to prevent database attacks and is really made to satisfy enterprise companies that will want to take advantage of the additional items ADOdb supports for auditing and other things. An enterprise company would be one that has their own dedicated servers and an IT staff on hand to deal with things.

Prior to PDO being available this was the best solution.

Problems with ADOdb are mostly performance based. Because it is PHP based it is MUCH slower without additional help than PDO. I saw mentions that ADOdb without help can be CONSIDERABLY slower than using Native drivers. To the point of being unbearable in some situations.

Because of the performance hits the developers of this Library developed an "extension" that essentially took some of the most used PHP functions and put them into a compiled library designed with C or C++. If you add the extension to ADOdb it will dramatically improve the performance of it. The problem is that this extension needs to be compiles specifically for the system it is run on. They mention on the site that they suggest you compile it on the site you are going to be using it on but this is not an option for 90%+ of the users of Xoops. Hopefully you can find out what your server is and get a version already compiled for it.

Another add on that can make a significant improvement is the use of a PHP accelerator or code caching system. Mention was made that an ADOdb with the extension and using a PHP accelerator comes reasonably close to the speed of PDO.

A couple of issues that you get with using ADOdb are due to the fact it is an add on library and not included in the system. When you load a new page the whole system needs to be reloaded before it can be used. This is where code accelerators and caches help dramatically. The other issue is that the code for this is pretty good sized to support everything. That means if you decide to include this with the core of xoops you will increase the size of the download by quite a bit. Plus you can not include the extension because it is system dependent since it is compiled machine code. So if built into the core of xoops it is likely to considerably slower without "helpers".

All in all though in an enterprise environment where they have the resources to provide the "help" ADOdb needs to be manageable and have the desires to use its added features it would be a very good solution because it has many of the tools an enterprise will want to make use of.

So what does this all mean?

In my opinion I think the above information makes it clear that PDO is the option that should be considered for Xoops at this time to provide the core database features. It is the fastest and the best "common denominator" for the masses. However the power of ADOdb's additional features are significant enough that xoops needs to provide a method to be able to use ADOdb in place of PDO when it makes sense. Since MOST users will not use the extra features of ADOdb and will not have accelerators or have the extension available it just doesn't make sense to base the core base on ADOdb by default.

Xoops database usage was designed to use a more universal system that could eventually develop into a multidatabase system. Because of this it should be relatively easy to plug in either PDO or ADOdb. My suggestion is that we build the core to default to use PDO as its code base but with the intention that you can drop in ADOdb and use it instead when that makes sense and you have the hardware and/or software to make use of it without major penalty. If done in this manner we can support the "masses" as well as the enterprise solutions with the best available solutions.

When doing my investigation I did see mention where some people were going from ADOdb to PDO and they made it sound like it was relatively simple. If we continue to use the xoops style database factory we can quite possibly just have two different factories setup. Once working I would have to guess the database factory should not have to be touched again for the foreseeable future and could be used through out the 2.x series of xoops. The default factory would be based off PDO of course with a drop in ADOdb replacement for those that need it.

I think the ability to use either PDO or ADOdb would be a considerable bonus.

Ok, I spent more time typing this than the time I spent researching this...

I wanted to bring this here to get more discussion moving because I am sure there is more others can add. But please, we already know we need SEO and other things. Lets keep this thread ONLY about database discussions.


Rodney

So anyhow, I haven't seen any one respond to this... But because of this research I will be looking into the difference in programming for both PDO and ADOdb.

I will be using PDO in the upgrade I am doing to the install script to support future versions of xoops easier regardless of which way someone goes.

Since wishcraft has already done work to make a "Dropin" for ADOdb it would seem reasonable that if he would be willing he could code the PDO upgrade with the idea of being able to drop in ADOdb as a replacement.

I do think it is a good idea to install this into the 2.6 version ASAP since other changes will be needed to modules to work with 2.6 anyhow, it would be a good time to incorporate this into core. The module framework and such can be added later and not really impact module development much but changing the database interface can make a big difference in module development.