As mentioned elsewhere PDO looks to be the PHP library to use. I will document my converting the core over to PDO in this thread.

Any modules that currently use their own MySQL statements instead of the built in xoops statements will break for sure when this is completed. However updating those modules to work with PDO should be relatively simple. The power REALLY comes when modules are rewritten not to just use PDO instead of MySQL statements but to use the power of PDO to make the code faster and hopefully reduce queries.

I am looking for locations that the Database is accessed with MySQL statements within the core. I intend to use 2.5.5 as the base for this and convert the code over to use PDO. This will be a drop in replacement for 2.5.5. If there are any more versions in the 2.5 branch I will update if necessary but I suspect that should not be the case. PDO does support other databases very simply as well so once the original conversion is complete and fully operational it should be very simple to add other database types to the mix.

I have seen code to access MySQL directly in the xoops object class and the database factory class. Is anyone else aware of other places in the core it is accessed directly? I will be searching the code to identify all locations before I attempt this conversion.

I will also look at the postings Mamba put up and keep that information in mind as I attempt to create this "drop in" replacement for the current Database routines.

At this stage I won't spend much time on this but adding PDO support I think is important to the core. It looks like it will be easy to implement BASIC PDO support within the Database class and go from there. I hope to have something available shortly for testing purposes. I wanted to play with this so I could incorporate PDO into the install program as well.

The original core Database class system is designed to support many different databases but other databases were never made to be a big part of Xoops. Xoops is currently using the original MySQL connect library which is over a decade old and I have seen mention is depreciated. Problem with such a "flexible" core code is that in other core code you can not use optimizations that are now available because it may not be usable in other databases so things are limited to the perceived "most common denominator". This design was done well before a multiple database support system was available and stable.

I believe in using PDO we should totally redo the Database class because it doesn't need to support multiple databases in the manner it does now. By using something like PDO that supports many different databases out of the box we can now support many different databases with a much more recent library and make use of the optimizations and performance upgrades ingrained into it.

I will do what I can to publish a simple PDO interface for the current xoops for testing purposes. I may tinker with it to fix any bugs found while I continue developing the install program and working on other projects I have planned.

Once I have the install program in testable form I will revisit this and work on a replacement database class that is PDO based for possible inclusion in 2.6. I believe by changing this over to PDO we should be able to make use of a lot of the improvements for performance, security and optimization allowed by this library directly into the core code.

Modules that do not fully use the Xoops API to access the database are likely to break with this. However since the same features are available with PDO as with the original MySQL connect that Xoops is based off as well as MySQLi it should require only converting over to the API functions or using PDO directly instead of the MySQL connect or MySQLi.

When I get to that point I can provide a tutorial for database access since I will be fully in tune with it by the time I am totally done with this project.

While trying to get PDO to work I found a script that converts MySQL scripts to MySQLi scripts and have converted the MySQL database class to MySQLi.

Please try out this class and see if it works. It works fine for me with 2.5.5.

Do NOT use it on a production server since I have no clue if it will work right elsewhere.

I do expect some modules to break with this if they access MySQL extension directly instead of using XOOPS API.

Assuming this works I will add Transactions and Prepared statements to it.
Before I spend the time doing that please try this out and see if there are problems with it. I don't want to spend a bunch of time on this if it doesn't
work as is.

Rodney

Find it in the SVN:
http://xoops.svn.sourceforge.net/viewvc/xoops/ThirdParty/mysqli/

edited:
Testing MySQLi against the standard MySQL extension it seems to work at every step. Had a couple minor bugs I fixed. Also will add Transactions and Prepared statement support to this as well. I figured out what was going on with my PDO script and have that working now too. Have some loose ends to tidy up with the PDO version and then I will put that up for testing purposes. Will likely work on a object version of the MySQLi extension as well since the current one is using global variables where "objectifying" it should put it all within the class.

I should explain a little more what I am doing... A couple people have contacted me confused about this.

To install these with 2.55 (And probably older xoops as well. And likely 2.6.0)

Inside your xoops folder there is a class folder and inside that there is a database folder. These mentioned .php files all go into this database folder. They are simply drop in extensions for the current database structure. If you look at the databasefactory.php file you can see how it accesses the different database classes if you are interested in how this is setup to work.

To actually use these new classes you need to change the line: define('XOOPS_DB_TYPE', 'mysql');

Find this in either xoops_data\data\secure.php or in older versions of xoops the mainfile.php.

As of the writing of this message there is a MySQLi class based on the Procedural version of MySQLi commands as mentioned in an earlier message. So to use this just drop the class file into the above mentioned class/database folder and change the database type to "mysqli" and it should run. I have it running locally without issue. I removed the original mysqldatabase.php file from the directory for testing purposes to make sure nothing was still using the old php file.

My goals here between today and before the 15th is to have 3 new database classes.

MySQLi procedural based class. (This may end up being unnecessary once the OOP based class is built but think it is important for potential compatibility issues with older modules. This will not be used with 2.6 but can be used as a simple drop in replacement when the MySQL extension no longer is included with PHP.)

MySQLi OOP based class. (This may turn out to be much nicer than the procedural class and should support the same things. I just don't like the Procedural use of Global variables and this should keep everything inside an object. This again is intended ONLY for 2.5.x and older versions of xoops. This should work with 2.6.0 as well but hopefully will be unnecessary with 2.6.1 and newer)

PDO based class. (Since only the connect string is different between databases I am building a default class and to make it work with other databases just need to extend the PDO class with the connection string which I will make obvious in the PDOMySQL class. This is where Xoops is headed for the future. Assuming that this works out the whole database class is likely to be restructured to make use of PDO for a future version of Xoops.)

I am in the debugging stage of my PDO class. Once I have that complete I will be adding new features to this. I will try to add the same features to the MySQLi code as well for compatibility reasons for older modules. By compatibility I mean if someone used their own database code instead of the xoops API this should allow those modules to be refactored to use the newer features directly.

The Database Factory Class is designed to make use of different database types and does all the dirty work for you as long as you use it properly.

Assuming the PDO class works and doesn't break too much we can expect it to be included into xoops in a future version. This will allow the newer features to be used in the core and will require a refactoring of the core to make use of new features. The database factory was designed before things like PDO or ADODB were available which support many databases. So for a future version of xoops I would expect that the databasefactory gets removed and replaced with a much slimmer PDO based class.

I have what appears to be a very nice Generic PDO class that I want to pull some code from but It is apparently commented in Portuguese. Is there someone here that can help me by translating a file for me? This could help me get a viable PDO library done this weekend (baring any surprises in my classes that I don't foresee)

I can do the translation myself using Google Translator but it will likely take me hours to do and I would really appreciate if someone else could do it for me to save me some time that I can instead spend on finishing up my PDO class.

The importance of this class is that it resolves a lot of the issues I foresee while building a class to take advantage of the new features PDO allows and it also allows some great solutions to accessing the new features as well that I can add to a new class for xoops.

Thank you!

Hello redheadedrod,

I belive Mamba has worked on something similar. Not sure if that project matured but maybe a joint effort would be good.

I missed this reply some how...

But I don't believe Mamba was ever working on this. He did some research on it and has helped point me in a good direction however.

This week is the last week of my classes for the semester and then I am off until January 14th so I will be working hard at finishing up this Database class and hope to have it available soon to test since I quit my second job and am on holiday break for over a month.

We learned a little about unit testing this semester so I will likely try to build a testing class for the database object and go from there. Next semester will be big on quality assurance so I can see a summer project brewing if I don't take any classes.

If I am able to build a unit test for the database objects I may release that for review to see if I am missing anything prior to any sort of database release. It will be based on the old database object with minor modifications. I will then use this unit test to test and modify the PDO class I have already been working on.

At this point in time I am busy catching up on some other projects and I am reading up on PHPunit. I hope to have a PHPunit test available for the current MySQL connector shortly. I am going through the 200page documentation now and it seems pretty straight forward. When I have the unit test created I will post it in this thread for review.

My first unit test will only test the current functionality of the database class and no new features that PDO or MySQLi support.

The second test I will build after I have support for transactions and such built into the code. I will be basing this version on testing for PDO compatible new functions. (MySQLi supports a couple other features but I will not add support for those assuming we are moving to PDO. So only PDO compatible functions will be tested. affectedrows will be displayed as depreciated for use with select statements.)

A third version will be designed if necessary to be compatible with 2.6.

My Current road map for PDO involves similar steps.
The first step is to get it working as well as the standard MySQL connector.
Second Step is to add support for new functionality such as transactions and stored procedures.
Third step is to update for 2.6 if needed. (I have yet to look at the database code for 2.6)

Current Status... The coding is 99% finished for original capabilities and I just need to debug and do any final touches. In this version I test the SQL statement to see if it is a select statement. If it is a select statement I do the select statement then I build a new select statement that is a count only and adjust the affectedrows attribute. While this SHOULD work I believe it is a dirty solution and hope there is a better way to do it.

When the unit test is done I will test the PDO class and the MySQLi Class I already submitted to make sure they work the same as the original MySQL class. Once I feel comfortable about this I will release the PDO class for further testing and will make any needed adjustments to the MySQLi Class as well.

Mamba found a fully functional PDO class that is already available that I will be reverse engineering to better understand and support the new features that PDO will bring to the table. With the new PDO class I will take this knowledge and add new functionality into it. Along with this I will also build a unit test to test the new functions. When done I will release the unit test and the full PDO class for further testing.

I will also look at adding similar functionality to the MySQLi connector for compatibility sake only and only for 2.5.x. Again tested with the unit test...

Once this is complete I will finalize the 2.5.x version of both PDO and My MySQLi connector and then check 2.6 to see if the code needs to be adjusted. If not then it should be ready for code review and to be included in 2.6 Alpha 3 and to be included with 2.5.6 or at least be available.

I hope to be able to be at this third step before January 1st, 2013.

January 3rd Update:
After reading through most of the PHPUnit testing documentation I decided for now to NOT implement this. I will be getting very involved with unit testing this semester and will wait until then to build a unit test.

I will go through the PDO 4 you code to get some ideas on ways to implement the new support stuff. I am not going to directly port PDO 4 You to Xoops. I will be looking through the code contained within it for examples of good ways to get the extra support built in.

I glanced at xPDO at the request of a user and I remember looking at that before. xPDO is more of a wrapper for PDO and would be useless for xoops 2.5.5 and newer. If Xoops wanted to incorporate it as the Database System it does bring PDO support to the older PHP versions but in my mind it would be a waste.

I am pulling ideas and maybe code from PDO 4 you. But it will be a full xoops class. Not making use of a class as I would be if using xPDO. SO I am not going to spend any more time looking at xPDO unless the core developers would like to know more about it or something.

Rodney