11
redheadedrod
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6

Ok I updated my test program to fully test the base functions of the databases and I was able to fully test 2 database connectors that are designed as drop in replacements with 2.5.5 xoops for the old MySQL connector.

The MySQLi connector I converted with a conversion script then updated to make fully work is running 100% as far as I can tell from here and needs more robust testing. This connector is virtually unchanged from months ago when I originally did it. I am considering it Beta at this point since It needs to be tested further.

I also have a fully functioning PDO connector as well. This connector also is running 100% as far as I can tell and needs further testing. This connector is the one I have been working on in between class work for the past few months. Today I was able to finally get it fully tested and running on my system for the first time.

Both of these connectors ran very well from my machine at home. I tested all functions as much as I could and also ran a test site with no problems. I did not try with any additional modules installed. Be aware that any module that accesses the database directly will not work properly with these connectors and will crash altogether if you use a different database. These connectors do not currently contain support for the new features of MySQL 4 or 5. As such should be considered Alpha. The PDO connector however is designed to easily add additional databases by cloning the mysqlpdodatabase.php file and making the appropriate changes.

There are two items that are NOT supported by PDO that are part of the MySQL connector.

Numrows returns the number of rows accessed by a SELECT statement. As explained online, MySQL gets this number by running another SELECT statement with the COUNT(*) command. In the PDO connector included here I use this technique but it uses an additional QUERY to accomplish this. There are warnings that this is NOT the best way to do this. You SHOULD NOT use Numrows even with the standard MySQL connector. The suggestion is to count the records as you access them.

MySQL connector supports selecting a different database from an open connection. PDO does NOT support this but you can do the same thing using a "USES new_database" SQL statement and I include this into the code as a viable solution. Not sure why you would need to access a different Database from the system and I believe this might be a security risk but it is there in case it is needed.

I hopefully will have support for the additional functions soon.

Please download and test this with your system. Please also test with modules installed so we can see which ones break. If they fully use the Xoops API they should work well. So this could be a decent test to see if certain modules are coded properly with relation to the database.

To get these new connectors please get them from the SVN...
They are available from:
http://svn.code.sf.net/p/xoops/svn/ThirdParty/rhr_database_connectors


12
kakos
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6
  • 2013/1/5 20:29

  • kakos

  • Friend of XOOPS

  • Posts: 77

  • Since: 2002/6/6 2


the above link asks for login ...

13
Mamba
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6
  • 2013/1/6 12:39

  • Mamba

  • Moderator

  • Posts: 11366

  • Since: 2004/4/23


It should now work.

I've removed the "s" from the original "https://" link
Support XOOPS => DONATE
Use 2.5.10 | Docs | Modules | Bugs

14
redheadedrod
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6

Quote:

Mamba wrote:
It should now work.

I've removed the "s" from the original "https://" link


Thanks Mamba, I will pay a little closer attention to that in the future.

Rodney

15
redheadedrod
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6

Also, if you do test this out please comment here if it worked fine for you or if it didn't so I can make adjustments to it as needed.

I wanted to have this tested out while I add the new features so I can make any adjustments as needed.

I will post a news article once I have added the new features but I am hoping the "old" features work well as well.

16
redheadedrod
New PDO class and proposed structual changes.

Ok I haven't heard much feedback yet so I guess that is somewhat good?

I am currently working on some "proposed" new methods to work with this new Database connector. The methods as exist should remain working for the time being but if this connector is accepted there won't be a need for a lot of the old stuff.

Currently if you access the database connector directly when you do SELECT, INSERT, DELETE and UPDATE queries you use the query statement. This would look similar to this:
$sql 'SELECT * FROM table ORDER BY T_id';
$result $testDB->query($sql);


An insert would be done like:
$sql 'INSERT INTO table  (LastName, FirstName) VALUES ('McBeth', 'Lady' )";
$result = $testDB->query($sql);

And so on.

We are using the same code along the way and it is relatively simple to inject code into this if you don't use some sort of sanitizer. Xoops has all sorts of sanitizers throughout the code but on occasion something slips through. Not to mention the times you might not quite have your code written 100% right.

I am looking at building methods that would allow the same functionality we have now but will make calling the queries simpler, allow us to make use of features of the different databases transparently and should be a lot more secure since we would be using prepared statements.

The idea is that instead of sending the actual sql query to our code we want to send JSON style data to the methods and it will go from there. Why JSON? JSON is a common structure used throughout web design and works well for us here. I am using the "PDO 4 you" class for a basis for most of this code although it is modified anywhere it is used.

A simple example would be to look at a normal delete query.
Normally we might call a delete similar to the ones above.
$sql 'DELETE FROM TABLE WHERE FirstName='Mickey' AND LastName='Mouse'";
$result = $testDB->query($sql);


This is simple if we only want to delete one entry but if we wanted to delete a series of records we would have to repeat our setup of the query then call it over and over again. And check it each time to make sure it worked.

With this "new" method we call the same routine differently.
$sql '
{
    query : [
        {
            table: "TABLE" ,
            whereAnd: { FirstName: '
Mickey', LastName: 'Mouse' }
        }
    ] 
}
'
;
$result=$testDB->delete($sql);


This may seem a bit of work for something simple but if you want to do more than one deletion you could do something like this:
$sql '
{
    query : [
        {
            table: "users" ,
            where: { id: 2 }
        },{
            table: "users" ,
            where: { id: 5 }
        },{
            table: "users" ,
            where: { id: 10 }
        },{
            table: "books" ,
            where: { id: 10 }
        }
    ] 
}
'
;
$result=$testDB->delete($sql);


This would delete 4 separate records in two different databases securely and simply.

This is what I am working on now...

It really makes a difference for select queries as well.

(Note you need to count your own rows with PDO. There is no rowCount
outside of MySQL normally and PDO programmers talk about it is better to
count your own rows even with MySQL and MySQLi connectors because it
is not accurate in ALL cases.)
So currently you might use the following code to read in data and get the number of rows:

$sql 'SELECT * FROM table ORDER BY T_id';
$result $testDB->query($sql); 
if (!
error()) 
    
$rowCount=0;
    while (
$row $testDB->fetchRow($result)) 
    {
    
$resultArray[]=$row;
        
$rowCount++;
    }


New method:
$sql '
{
    query : [
        {
            table: "table" ,
            column: "*",
                        order: "T_id"
        }
    ] 
}
'
;
$result $testDB->select($sql);

The results will be returned in an associative array called $result.
After this is done our row count will be available as:
$rowNumber $testDB->rowCount;


This is only an example and may not be exact. But this is the idea behind the new "proposed" PDO based code I am working on.

The $sql query section will be setup a little different but I have to play around with it a little until I get what I want. Instead of "query" it may state what it is actually doing such as delete, insert, select etc... This makes more sense when we look at transactions...

It may also allow me to simplify the code too and allow for one "query" command using the json style to accomplish everything instead of the current structure based on the PDO4You class that requires 8 separate methods calling 2 other methods.



17
redheadedrod
New PDO class and proposed structual changes. Transactions

Transactions will be handled in one of two ways. PDO4You gives you the beginTransaction, commit and rollback methods and I will duplicate this for those times when you need to do your own transactions.

Otherwise to simplify this I plan to have a JSON style string you will send to a "transaction" method to do everything for you if it is a simple transaction. If the transaction is successful it will be committed and a true will be returned. If it fails it will return a false and will be rolled back and an error will be logged by the system.

beginTransaction may not return an error if the current database doesn't support transactions. It will depend on the database used. MySQL MyISAM tables do not support transactions. MOST xoops tables are MyISAM tables and will need to be changed to INNODB to support transactions.

I wanted to let a little of this out because I would like some comments on this if possible. I am spending some quality time on this and hope to have something solid in the next week or so for this stuff.

I will also make the changes that are included in the 2.6 database structure as well where possible so this can be dropped right in with 2.6. A brief look shows that only some minor changes were made to the 2.6 code to make it more OOP compliant.

18
zyspec
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6
  • 2013/1/14 3:41

  • zyspec

  • Module Developer

  • Posts: 1095

  • Since: 2004/9/21


I'm personally more interested in having this 'integrated' with XoopsObjects rather than directly using SQL calls.

In other words the ./class/model/read.php should be modified so that anything that uses the XoopsObject methods (get, insert, etc) can remain unchanged.

I assume most of the discussion here is how the core team will construct the models (joint, read, stats, write, sync, etc), database and Criteria classes/methods. This needs to be transparent to the modules.

From a module developer's point of view I believe the goal should be that code like the following should 'just work':

$obj_handler =& xoops_getmodulehandler('myObj''myModule');
$criteria = new CriteriaCompo();
$criteria->add(new Criteria('field'3'='));
$criteria->setLimit(4);
$criteria->setOrder('ASC');
$criteria->setGroupby('field2');
$objs $obj_handler->getAll($criteria);


All modules need to be modified to eliminate direct SQL statements so that modules can eventually get to the point of being dB engine agnostic.

19
irmtfan
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6
  • 2013/1/14 7:07

  • irmtfan

  • Module Developer

  • Posts: 3419

  • Since: 2003/12/7


Quote:

All modules need to be modified to eliminate direct SQL statements so that modules can eventually get to the point of being dB engine agnostic.

+1
very true. Now even a very faithful module developer to XOOPS API have to use hardcoded sql commands in a few occasions.


20
redheadedrod
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6

I am not very experienced with the xoops API for the database structure and I probably should look into it more so I can understand it better. The intent for this connector was to create some low level methods to access the database in a manner compatible with how it is now but using a different connector.

With added support for new features due to the newer databases I am hoping to take advantage of them to simplify the low level methods.

With the JSON style calling of the commands and using prepared statements and controlling all aspects of it with related code should be simpler to do and more secure. Multiple queries using the same prepared functions are much quicker as well.

I would really appreciate it if someone could point me to all of the xoops API related database methods that are NOT included in the database class directory.

I am familiar with criteria (Although no clue hot to use it).
I have seen "getVar" and I believe there are related "setVar" and "clearVar" routines but I haven't really looked for them. I assume they are part of the xoops object and will likely look at them and try to decipher how they work as well.

So please if you know of methods designed to use the database like Criteria let me know. When I get done with this Database connector I will be building a Database Tutorial for module developers to know how to use Databases.

For 90+% of the modules out there the high level xoops API commands will work. For the other <10% that need to use specific low level commands they should be easier to use with this connector assuming the new features are accepted.

Current code does not make use of Prepared Statements or Transactions and there is likely no easy way to setup Transactions with the current system. I will look at what is there and see if there is a way that should be easy.

And by the way.. By moving to a JSON style "query" statement the statement will be very controlled and very structured which will make it much easier to deal with for different databases. Instead of trying to decipher the string and pull its parts out to test the query for different things you KNOW the parts and build the query accordingly. By using this style of system with prepared statements it becomes very secure and modules using or higher level API calls will be very unlikely to have issues with security issues related to SQL injections. Plus again, it will make it much simpler to work with different aspects of different databases. I believe that moving to PDO will remove the need for any other database connector since it supports pretty much everything out there and is "industry standard."

Rodney

Login

Who's Online

192 user(s) are online (103 user(s) are browsing Support Forums)


Members: 0


Guests: 192


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Mar 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits