Joomla PDO Database Driver

[Herman Peeren]

In the database package we have a PDO-driver Joomla\Database\Pdo\PdoDriver.

• is anybody using it?
• does anybody know something about its current status, issues, people involved, history, future plans?

I searched a bit and found some old threads, mainly from 2011 and 2009, but nothing recent.

Is anybody else interested in this db-driver?

[Don Gilbert]

Some of the other core drivers extend it, so it is being used. Do you want to refactor / improve it?

Sent from my iPhone

--
Framework source code: https://github.com/joomla/joomla-framework
Visit http://developer.joomla.org for more information about developing with Joomla!
---
You received this message because you are subscribed to the Google Groups "Joomla! Framework Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-frame...@googlegroups.com.
Visit this group at http://groups.google.com/group/joomla-dev-framework.

[Herman Peeren]

I want to extend it to a pdo-mysql driver for Joomla.

I use Doctrine ORM in Joomla. The Doctrine DBAL uses PDO. I had no problem to use their pdo_mysql driver besides the mysqli-driver the rest of the Joomla system in which my extensions run, BUT I've read some reports of it giving problems (mainly "native" mysql not operating correctly immediately after having used the pdo_mysql connection). Last year, at JAB13, some people (Nicholas from Akeeba and Matthias from Kunena) mentioned those same problems. Although I didn't encounter those problems myself in production environments, one of which is running with that double database connection for 2 and a half years now, I don't completely like the double database connection, the pdo_mysql for my extensions and mysqli for the rest of Joomla.  Therefore I'm now investigating in two ways: 1. to use a pdo_mysql connection (which has greatly improved over the years and is not worse than "native" mysqli-driver anymore. It is BTW also used in Drupal). Therefore I'd have to make a pdo_mysql-driver for Joomla  2. to rewrite the Joomla CMS core components using Doctrine, not using the Joomla Database and Model classes at all. I'll present the work I've done on it at JAB14 (nice to meet you then Don): http://jandbeyond.org/program/sessions/next-generation-joomla.html

Anyone using Oracle or Sqlite with the Joomla Database package at the moment? As those drivers extend from the PDO-driver. Do I understand it correctly that although those drivers are shipped with this package, they are not supported? Read something vague like that in the tracker.

[piotr_cz]

I use Sqlite for testing, and it's being used in the Database tests and in Jooma CMS tests too, it's really handy.

By the way, did you manage to use Doctrine with table prefix (Joomla style)? While it works for me when creating entities and updating table schema, it doesn't when going other way around, I'm getting double-prefixed table names.

see my DoctrineServiceProvider.

[Herman Peeren]

Thanks. Will make a pdo_mysql driver and report here again.

As for the prefix in Doctrine: I use an eventlistener, like in the cookbook-example in the Doctrine documentation: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/sql-table-prefixes.html

I only changed it to replace #__ like we are used to do in Joomla This is my TablePrefix listener:

namespace Jooctrine;
use \Doctrine\ORM\Event\LoadClassMetadataEventArgs;

class TablePrefix
{
    protected $prefix = '';

    public function __construct($prefix)
    {
        $this->prefix = (string) $prefix;
    }

    public function loadClassMetadata(LoadClassMetadataEventArgs $eventArgs)
    {
        $classMetadata = $eventArgs->getClassMetadata();
        $classMetadata->setTableName(str_replace('#__', $this->prefix, $classMetadata->getTableName()));
        foreach ($classMetadata->getAssociationMappings() as $fieldName => $mapping) {
            if ($mapping['type'] == \Doctrine\ORM\Mapping\ClassMetadataInfo::MANY_TO_MANY) {
                $mappedTableName = $classMetadata->associationMappings[$fieldName]['joinTable']['name'];
                $classMetadata->associationMappings[$fieldName]['joinTable']['name'] = str_replace('#__', $this->prefix, $mappedTableName);
            }
        }
    }

}

When creating my EntityManager I have:

// Event-listener to change the  #__ db-prefix into the current db-prefix
        $prefix = \JFactory::getConfig()->get('dbprefix');
        $evm = new EventManager;
        $tablePrefix = new TablePrefix($prefix);
        $evm->addEventListener(Events::loadClassMetadata, $tablePrefix);

I'll upload some code I use on https://github.com/jooctrine and write some on http://jooctrine.org/
Planned that long ago allready, but couln't find the time for it, sorry.

[Herman Peeren]

@piotr_cz in line 139 of your DoctrineServiceProvider you use

	'driver' 	=> $appConfig->get('database.driver'),

to get the db-driver you use in the Doctrine EntityManager. But I don't understand how that could work. For it will retrieve the driver used in Joomla, like mysqli, while AFAIK Doctrine needs a pdo-driver. Did you get that working? How? What was your driver? Interesting!

BTW in my implementation you'll see much CMS-stuff (like \Factory), but that is because my Doctrine-applications are mainly Joomla CMS extensions (as that is what my customers need... and are paying for). Only recently also started to use Doctrine with some Joomla Framework parts, without the whole CMS.

On Sunday, 6 April 2014 13:37:06 UTC+2, piotr_cz wrote:

[Herman Peeren]

You are right: a mysqli-driver can be used in Doctrine DBAL (and I guess by Doctrine ORM on top of that):
http://doctrine-dbal.readthedocs.org/en/latest/reference/configuration.html
Because I haven't see that before, I thought it was impossible.

I'm quickly going to try out using that in a Joomla-extension with Doctrine (let you know). That would then solve the mentionded problem too. Great! Thanks for sharing that code!

[Herman Peeren]

Confirmed!
Just changed
     'driver' => $joomlaConfig->'pdo_mysql',
into
    'driver' => $joomlaConfig->get('dbtype'),
in the configuration for my Doctrine EntityManager in a Joomla CMS and everything still works...

Just because everybody uses PDO with Doctrine (and many also without Doctrine), doesn't mean it is the only possibility. So, I can just keep using the same mysqli-database-connection. Thanks a lot!

Still going to experiment with a pdo_mysql driver for Joomla. But it is less urgent now.

[Herman Peeren]

eh, typo, sorry for extra confusion. The first codeline in my last posting (how I always used pdo) was intended to be
     'driver' => 'pdo_mysql',
not

[piotr_cz]

I didn't know that I should use the PDO versions for DBAL connection, so I just passed the same as I have configured for Joomla\DatabaseDriver, it worked and I moved along :)

However, options are limited: https://github.com/doctrine/dbal/tree/master/lib/Doctrine/DBAL/Driver so using sqlite would work for Joomla DatabaseDriver, but DBAL probaly requires pdo-sqlite so I guess I should create separate branch in configuration just for doctrine ('system.doctrine.driver').

BTW: for simple tasks (listing application extensions) I'm thinking about using generic PDO trough PdoServiceProvider , so folks don't have to require either Joomla\Database nor Doctrine.

[Herman Peeren]

Just a small remark, I hope you don't mind: in your DoctrineServiceProvider line 159:

$tablePrefix = new Doctrine\TablePrefix($prefix);

However,  there is no TablePrefix class in the Doctrine namespace. You'll have to define that yourself. Examples like in the Doctrine cookbook define such a TablePrefix class as an event-listener. Just as with Joomla CMS plugins you use the function name to communicate on what event you want to have the function triggered. Here: loadClassMetadata ( ).
You added that event and the listener correctly to Doctrine's EventManager:

$evm->addEventListener(\Doctrine\ORM\Events::loadClassMetadata, $tablePrefix);

but you also have to provide that TablePrefix class, = the listener class, yourself. See what I wrote some postings ago.

I just wondered: didn't you get an error that that TablePrefix class was not found? Or did you add it to the Doctrine namespace yourself? AFAIK normally there is nothing in there:all Doctrine's classes are in subnamspaces Doctrine\ORM, Doctrine DBAL and Doctrine\Common.

[Amy Stephen]

Herman -

Can you reproduce the problem in an easy to use way so that we can play with the example that doesn't work?

I've had no problem using the database to connect to multiple databases before. I've even connected to both SQL Server and mySQL so not sure what the problem is?

Thanks!

[piotr_cz]

On Sunday, April 6, 2014 8:16:40 PM UTC+2, Herman Peeren wrote:

Just a small remark, I hope you don't mind: in your DoctrineServiceProvider line 159:

$tablePrefix = new Doctrine\TablePrefix($prefix);

As this is happening in the joomContrib\Providers namespace

Doctrine\TablePrefix is resolved to joomContrib\Providers\Doctrine\TablePrefix

Provider works fine, I'm just having problems with exporting the Table structure back to schema definition.

Anyway we should probably start a new topic.

[Herman Peeren]

I have no problem connecting to multiple databases at all. That is not what I said.

There can be problems connecting to the same database in different ways concurrently. For instance using pdo and mysqli on the same database at the same time. See for instance http://stackoverflow.com/questions/22671730/is-it-okay-to-mix-pdo-and-mysqli.
Besides the problems that could occur in this way, you also use 2 database connections where 1 would be sufficient. When you have a high-traffic site, that is an unnecessary (and maybe even fatal) load.

Why would you use those two connections for one database at the same time? When you are making extensions for the Joomla CMS an want to use Doctrine in it (like I do). You need the mysqli-connection to run Joomla and I always used pdo (as everybody does) to run Doctrine. Now Ihave not encountered problems with that the last years, and it was running on production sites, not just some tests. But as outlined above, there can be connection problems and unnecessary overload. I'm using the same database; sometimes even the same tables (for instance the users-table etc).

My solution was: to look at a pdo_mysql connection for Joomla. That is still my preferred solution, as I prefer pdo above mysqli (but that's a different discussion). Fron this thread I learned that you can also use mysqli as connection in Doctrine. That avoids problems that could occur when mixing pdo and mysqli (I once read somewhare that some mysqli-commands don't work well if immediately issued after having used pdo to the same db).

[Herman Peeren]

On Sunday, 6 April 2014 21:01:04 UTC+2, piotr_cz wrote:

(...) As this is happening in the joomContrib\Providers namespace

Doctrine\TablePrefix is resolved to joomContrib\Providers\Doctrine\TablePrefix

Provider works fine, I'm just having problems with exporting the Table structure back to schema definition.

Oh yeah, sorry I overlooked that...
But now I see, that you always add a prefix, as in the Doctrine cookbook example, That is why I changed my listener-class to change the #__ prefix-dummy into the real prefix. I also sometimes use (temporary) tables with  with another prefix (like temp_) and then I don't want the prefix added. Works OK also when exporting the mapping schema back to a database scheme. BTW, when using Doctrine via the CLI I have a slightly different bootstrap

 On Sunday, 6 April 2014 21:01:04 UTC+2, piotr_cz wrote:

Anyway we should probably start a new topic.

Yes. The part with you using the mysqli-driver in Doctrine was relevant for this discussion (and solved a part of the critic I got on the way I use Doctrine in the Joomla CMS). But the prefix-stuff is a different discussion. I'll also open a possibility for discussion about Doctrine ORM in Joomla related things on jooctrine.org and github.com/jooctrine and gather relevant links from this and other lists there too.

Let's definitely keep in touch about this.

 

[Herman Peeren]

To finish this off-topic part:

In your DoctrineDatabaseProvider you can delete lines 157-169:

// Process all excludes
foreach ($excludes as &$exclude)
{
// Strip placeholder
if (substr($exclude, 0, 3) == '#__')
{
$exclude = substr($exclude, 3);
}

// Add prefix
// TODO: not sure if Doctrine takes care of this
// $exclude = $prefix . $exclude;
}

In your TablePrefix class you can change the line 53:
$classMetadata->setTableName($this->prefix . $classMetadata->getTableName());
into:

$classMetadata->setTableName(str_replace('#__', $this->prefix, $classMetadata->getTableName()));

and line 60:
$classMetadata->associationMappings[$fieldName]['joinTable']['name'] = $this->prefix . $mappedTableName;
into:

$classMetadata->associationMappings[$fieldName]['joinTable']['name'] = str_replace('#__', $this->prefix, $mappedTableName);

In that way you just replace the #__ prefix by the real one, instead of always sticking a prefix in front of the table-name.

[Amy Stephen]

On Sunday, April 6, 2014 2:26:31 PM UTC-5, Herman Peeren wrote:

I have no problem connecting to multiple databases at all. That is not what I said.

Actually, you've said very little about the problem which is why I am asking if you can reproduce it.
 

There can be problems connecting to the same database in different ways concurrently. For instance using pdo and mysqli on the same database at the same time. See for instance http://stackoverflow.com/questions/22671730/is-it-okay-to-mix-pdo-and-mysqli.

This question simply asks if it's okay to use both PDO and MySQLi connections on the same database in the same application. (And the answers are "Why would you do that?")
 

Besides the problems that could occur in this way, you also use 2 database connections where 1 would be sufficient. When you have a high-traffic site, that is an unnecessary (and maybe even fatal) load.

Why would you use those two connections for one database at the same time? When you are making extensions for the Joomla CMS an want to use Doctrine in it (like I do). You need the mysqli-connection to run Joomla and I always used pdo (as everybody does) to run Doctrine.

Right.
 

Now Ihave not encountered problems with that the last years, and it was running on production sites, not just some tests. But as outlined above, there can be connection problems and unnecessary overload. I'm using the same database; sometimes even the same tables (for instance the users-table etc).

Definitely is going to cause more overhead -- doing the same thing twice does that.

But, other than memory and time needed to create a second DB instance, I'm still not sure what problems would result.
 

My solution was: to look at a pdo_mysql connection for Joomla. That is still my preferred solution, as I prefer pdo above mysqli (but that's a different discussion).

PDO is "better" in that it provides an approach to handles escaping -- but so does Joomla Query. (Probably less questions with the PHP community if Joomla adopted PDO but really they are both the same in terms of security.)
 

Fron this thread I learned that you can also use mysqli as connection in Doctrine. That avoids problems that could occur when mixing pdo and mysqli (I once read somewhare that some mysqli-commands don't work well if immediately issued after having used pdo to the same db).

OK - sounds like maybe you can head the mysqli way for Doctrine, too, and avoid duplicate connection resources.

But, it shouldn't create any problems (failures) to connect to the same database using Joomla Database package to the best of my knowledge. I have done this before, too, without any issue.

Yes, it's less performant since the work connecting to the database is done twice. Beyond the connection, however, it should be as fast to use two as one. It should work, too. But if there are problems, it'd be good to describe those in a reproducible way so that it can be fixed.

Thanks Herman.

[Andrew Eddie]

I think it's worth having a PDO version of a MySQL driver for
completeness if nothing else - Herman, I think it would be awesome if
you could do this. I also seem to remember something about native
drivers being dropped in a future version of PHP.

Also, since we will be bumping to V2 with the LGPL license change,
reasonable requests for making breaking changes could be considered.

Regards,
Andrew Eddie