[15]extend DB layer - implement PDO, or other DB-s like Pg

[zergemedve]

Hi,

It would be nice to make selectabel during the installation useing PDO to access database. And also will be a big step to allow to use Postgre or other databases.

I know there are projects to develeop this, but I think these functionalities place are in the core system.

[willebil]

I understand what you ask, but can you put more detail into you post. We are expecting white papers, and this sounds like a feature request. Please read http://forum.joomla.org/viewtopic.php?f=500&t=265654 on what we would like to see. Thank you very much in advance.

[zergemedve]

Summary:

The core system could be use a unirom object to access database, which can handle other databases than MySQL (and ofcourse integrate some auxiliary features like catching errors, help to debug or log orders) After PHP 5.1 there’s a PDO extension, which natively realize this. If PDO would be implement in Joomla! this can be the way to make Joomla! useable whith other database than MySQL (eg. Postgre, MsSql, Sybase, Oracle etc.) and even to use sqlite, if someone don’t have any database access.

Theory:

The first step might be to implement PDO to access MySQL. The theory is simple. Just like using mysqli instead of mysql, create another database driver which is the same like mysql or mysqli just using PDO functions, and make it selectabel during the install, and store it in the configuration.php This step is simply add a new feature, so it's not affect the backward compatibility.

Than let’s see what problems appear when we try to use this with other databases My knowns about other databases are limited, but I guess some of the sql queries won’t execute in all PDO supported DB-s. Theese need to be refactory to use PDO prepare or other ordinary way. Ofcourse the develepoers of the 3pd plugins also need to do this. Mysql and mysqli drivers might be need to extend to support this.

I’m not a PDO expert but afaik it works fine. I don’t have illusions about if Joomla! use PDO than Joomla! will be useable with all PDO support database whitout any problem, but I think it makes a chance to do this.

[ftore]

Good idea to implement multy database support in Joomla!. I think using such a system with only MySQL is not so and so effective. I think there should be other database support such as Oracle, MS SQL Server and etc.

It can be achieved by developing new extensions for these DBMS or implementation of PDO ...

[pasamio]

The issue isn't particularly with extended to other DB's, but adapting the queries. In some DB's (e.g. Oracle) there is no simple equivalent of "LIMIT start,amount", which means the entire Joomla! paging system needs to be rewritten before we can easily support Oracle. This is a simple example but building support for these things, whilst nice, also requires sometimes good knowledge of the system to work around its limitations.

[zergemedve]

Yes, you are right. But I think to make J! useable with Postgre or SQLite maybe less painfull to develop and will be a very attractive feature. And if J! use PDO than there's a way how to implement e.g. Oracle in the future. By the way, I think the problem with the DB specific queryes is unbypassable with or without PDO.

[pasamio]

PDO has its own stability issues as well that we also need to consider as well. Sometimes using the existing extensions to talk to the DB are better than using PDO's.

[ircmaxell]

Technically, you could use PDO right now if you wanted in 1.5... Just create a Database backend for that. PDO is a database CONNECTION abstraction layer. Not a database abstaction layer. The fact is that queries (anything except a very basic select, maybe) will need to be written differently for different DBMS. That leaves 3 ways of doing it (as I see).

#1. Language file - Where all queries are in a set of files (like language files), and are loaded by name. This will become a PITA to maintain, as there are a LOT of queries used

#2. Query Builder - Where you have an OO interface to tell the "builder" what you want from the query, and it builds the apropriate query for the DBMS in use. Not easy to do, and performance is a question.

#3. ORM or Active Record - Where the backend knows the relations, and all you tell it is WHAT you want, and it determnies how to get it. (Similar to how ROR works)...

PDO will just let you connect to different DBMS in a simple manor... It won't let you use them for data...

[rmullinnix]

Responding to:

In some DB's (e.g. Oracle) there is no simple equivalent of "LIMIT start,amount", which means the entire Joomla! paging system needs to be rewritten before we can easily support Oracle.

There is a way around rewriting the paging system for Oracle. I have an Oracle driver for external DB queries and I implemented the paging within the query function

Code: Select all

	function query()
	{
		if (!is_resource($this->_resource)) {
			return false;
		}

		// implement limit and offset in Oracle
		if ($this->_limit > 0 || $this->_offset > 0) {
			$end = $this->_offset + $this->_limit - 1;
			if ($index = stripos($this->_sql, 'ORDER BY'))
				$orderby = substr($this->_sql, $index);
			else
			{
				$index = strlen($this->_sql) + 1;
				$orderby = 'ORDER BY 1';
			}
			
			$modsql 	= substr($this->_sql, 0, $index - 1);
			$modsql	= substr($modsql, stripos($modsql, 'SELECT') + 6);
			
			$sql = 'SELECT * FROM (SELECT row_number() OVER ('.$orderby.') linenum, '.$modsql.')';
			$sql .= '  WHERE linenum BETWEEN '.$this->_offset.' AND '.$end;

			$this->_sql = $sql; 
		}
		if ($this->_debug) {
			$this->_ticker++;
			$this->_log[] = $this->_sql;
		}
		
		$this->_errorNum = 0;
		$this->_errorMsg = '';
		$this->_cursor = oci_parse( $this->_resource, $this->_sql );

		if (!oci_execute($this->_cursor, OCI_COMMIT_ON_SUCCESS))
		{
			$error = oci_error( $this->_cursor );
			$this->_errorNum = $error['code'];
			$this->_errorMsg = $error['message']." SQL=$this->_sql";

			if ($this->_debug) {
				JError::raiseError('joomla.database:'.$this->_errorNum, 'JDatabaseOracle::query: '.$this->_errorMsg );
			}
			return false;
		}
		return $this->_cursor;
	}

The code will currently blow up if you use DISTINCT and there are probably other holes, but I don't think it requires a rewrite of the paging.

[zergemedve]

Yes, PDO is a data-access abstraction layer, and it might be a part of a database abstraction layer (or maybe can be a separated layer) There are so many advantage to use a smarter data-access abstraction layer (e.g. handle errors, support for logging, transactions etc.. ) There's plenty of data-access layer such as PEAR or others (most of php programers use an own kind of mutations of theese .. the present mysql driver is a kind of thees too) but the power of PDO is that is natively in php. For example PEAR DB packege is very cool, but 400% (!) slower than a simple connect. So thats why I stared to use PDO in my own projects, becouse I've got the power of a smart data-access abstraction layer with an economic way.