Connect to external database

[freeyland]

Hi,

I want to connect to an extarnal database using Typolight database librarie.

Is this possible? I tried to extend DB_Oracle class (driver) but the problem is that the class DB_Oracle is not found because I am using Mysql to store Typolight data.

Do I need to create a new abstract class 'Database'?

Rgds,
Frederick

[ga.n]

you can extend the DB_Mysql class and override the connect method

http://dev.typolight.org/repositorie...php?rev=65#L57

[freeyland]

Thx ga.n,

This is what I thought. But i get the error:
Fatal error: Call to protected Database::__construct() from context 'ModuleOratest'


ModuleOratest is my new created module

How do I create an new instance?

class ModuleOratest extends Module
{
	protected $strTemplate = 'mod_oratest';
	
	public function generate()
	{
			
		$objInstance = new Oracle_connection();
		$objInstance->Connect();

		return parent::generate();
	}
	
	protected function compile()
	{
		
	}
}

class Oracle_connection extends DB_Oracle
{

	public function connect()
	{
		
		$this->resConnection = @oci_connect("root", "test", '', $charset);

	}
}

If I check Database.php Class, is see the method :

public static function getInstance()
	{
		if (!is_object(self::$objInstance))
		{
			$strClass = 'DB_' . ucfirst(strtolower($GLOBALS['TL_CONFIG']['dbDriver']));
			$strFile = sprintf('%s/system/drivers/%s.php', TL_ROOT, $strClass);

			if (!file_exists($strFile))
			{
				throw new Exception(sprintf('Could not load database driver %s', $strClass));
			}

			include_once($strFile);
			define('DB_DRIVER', $strClass);

			self::$objInstance = new $strClass();
		}

		return self::$objInstance;
	}

Do I need to extend this abstarct class or do I create a complete new class. I am not a pro in OO PHP, so I don't now if it is possible to extend abstract classess.

Reamrk I am using Mysql for typolight tables but I want to connect to an oracle database containg some other dat.
Thx

[ga.n]

instantiate the class with

$db = Oracle_Connection::getinstance();

a good starting point for oop is the manual on php.net

[freeyland]

Ok,

I've tried this but :
class Oracle_connection extends DB_Mysql is working because DB_Mysql has already an instance
But I want to use:
class Oracle_connection extends DB_Oracle

The problem is that the driver is loaded through a global var in settings file (See Database.php):

	public static function getInstance()
	{
		if (!is_object(self::$objInstance))
		{
			$strClass = 'DB_' . ucfirst(strtolower($GLOBALS['TL_CONFIG']['dbDriver']));
			$strFile = sprintf('%s/system/drivers/%s.php', TL_ROOT, $strClass);

			if (!file_exists($strFile))
			{
				throw new Exception(sprintf('Could not load database driver %s', $strClass));
			}

			include_once($strFile);
			define('DB_DRIVER', $strClass);

			self::$objInstance = new $strClass();
		}

		return self::$objInstance;
	}

Is it possible to also extend the abstract class 'Database' to override method 'getInstance''?


Rgds,
Frederick

[ga.n]

Hi freeyland,

put the following code at the beginning of your extended class file

<?php

require_once TL_ROOT.'/system/drivers/DB_Oracle.php';

class My_Oracle extends DB_Oracle() {

// ...
// your code here
// you *have to* override connect method
}

?>

then initialize it through

<?php

$db = My_Oracle::getInstance();

?>

please name the file as the class name (e.g. My_Oracle.php) and put it into your extension folder
(or try to understand how the autoload of TYPOlight works and put it in a suitable place)

[freeyland]

Hi,

I've been investigating the code and saw that it is not possible to use the existing class 'Database' for connecting to external databases.
The problem is that Database.php can oly have 1 instance (Singleton).

The only solution I see is to create my own class with a public constructer:

class DB_Oracle_custom extends DB_Oracle
{

	public function __construct()
	{
		$this->connect();
		if (!is_resource($this->resConnection) && !is_object($this->resConnection))
		{
			throw new Exception(sprintf('Could not connect to database (%s)', $this->error));
		}
	}
	
	/**
	 * Connect to database server and select database
	 */
	protected function connect()
	{
		$this->resConnection  = @oci_connect($GLOBALS['TL_CONFIG']['dbUser_Infobase'],$GLOBALS['TL_CONFIG']['dbPass_Infobase'], $GLOBALS['TL_CONFIG']['dbSid_Infobase']);
	}
	
	/**
	 * Disconnect from database
	 */
	protected function disconnect()
	{
		@oci_close($this->resConnection);
	}
}

Is this correct what I am saying?

Rgds,
Frederick

[ga.n]

you are right ... but you can still use the singleton logic in your extended class and continue using the getInstance method

require_once TL_ROOT.'/system/drivers/DB_Postgresql.php';
class pgDB extends DB_Postgresql {

    protected static $myInstance;

    public function connect() {
        $this->resConnection = pg_connect("host=localhost port=5432 dbname=tl_pg_28test user=postgres password=xxxxxx");
    }
  // this does the trick
    public static function getInstance() {
        if (!is_object(self::$myInstance)) {
            self::$myInstance = new pgDB();
        }

        return self::$myInstance;
    }

}

hope this helps

[freeyland]

Million thx!

[freeyland]

This is the code I created.
It is working fine but I get the following error message:
Runtime notice: Declaration of Database_custom::getInstance() should be compatible with that of Database::getInstance() in /var/www/html/infobase/system/drivers/DB_Oracle_custom.php on line 42
I get this error by calling:
$db2 = Database_custom::getInstance($GLOBALS['TL_CONFIG']['dbUser_Infobase'],$GLOBALS['TL_CONFIG']['dbPass_Infobase'], $GLOBALS['TL_CONFIG']['dbSid_Infobase']);

<?php if (!defined('TL_ROOT')) die('You can not access this file directly!');
class Database_custom  extends  Database
{
	protected static $myInstance;
	protected $user;
	protected $pass;
	protected $sid;
	
	public static function getInstance($user,$pass,$sid) {
        if (!is_object(self::$myInstance)) {
            self::$myInstance = new DB_Oracle_custom($user,$pass,$sid);
        }
        return self::$myInstance;
    }
	
	
	public function prepare($strQuery)
	{		
	
		$strClass = 'DB_Oracle_Statement_custom';
		$objStatement = new $strClass($this->resConnection, $this->blnDisableAutocommit);

		return $objStatement->prepare($strQuery); // prepare oproepen van klasse DB_Oracle_Statement
	}
	
	
	
	/**
	 * Abstract database driver methods
	 */
	
	 protected function connect(){}
	 protected function disconnect(){}
	 protected function get_error(){}
	 protected function begin_transaction(){}
	 protected function commit_transaction(){}
	 protected function rollback_transaction(){}
	 protected function list_fields($strTable){}
	 protected function set_database($strDatabase){}
}
/**
 * Class DB_Oracle_custom
 *
 * Driver class for Oracle databases to connect to external database.
 * @copyright  Flexima 2010
 * @author     Frederick Eyland <http://www.flexima.be>
 * @package    Driver
 */
class DB_Oracle_custom extends Database_custom
{
	protected $user;
	protected $pass;
	protected $sid;
	
	public function __construct($user,$pass,$sid)
	{		
		$this->connect2($user,$pass,$sid);
		if (!is_resource($this->resConnection) && !is_object($this->resConnection))
		{
			throw new Exception(sprintf('Could not connect to database (%s)', $this->error));
		}		
	}
	
	protected function connect2($user,$pass,$sid)
	{
	
		$this->resConnection  = @oci_connect($user,$pass,$sid);
	}
	
}

class DB_Oracle_Statement_custom extends DB_Oracle_Statement
{
	/**
	 * Escape parameters and execute the current statement
	 * @return object
	 * @throws Exception
	 */
	public function execute()
	{
		$arrParams = func_get_args();

		if (is_array($arrParams[0]))
		{
			$arrParams = array_values($arrParams[0]);
		}

		$arrParams = $this->escapeParams($arrParams);

		$this->strQuery = preg_replace('/%([^bcdufosxX])/', '%%$1', $this->strQuery);
		$this->strQuery = preg_replace('/%%+/', '%%', $this->strQuery);

		if (($this->strQuery = @vsprintf($this->strQuery, $arrParams)) == false)
		{
			throw new Exception('Too few arguments to build the query string');
		}

		// Execute the query
		if (($this->resResult = $this->execute_query()) == false)
		{
			throw new Exception(sprintf('Query error: %s (%s)', $this->error, $this->strQuery));
		}

		// Check whether there is a result
		if (!is_resource($this->resResult) && !is_object($this->resResult))
		{
			$this->debugQuery();
			return $this;
		}

		$strClass = 'DB_Oracle_Result';
		$objResult = new $strClass($this->resResult, $this->strQuery);

		$this->debugQuery($objResult);
		return $objResult;
	}
	
}

?>

[ga.n]

This is the code I created.
It is working fine but I get the following error message:
Runtime notice: Declaration of Database_custom::getInstance() should be compatible with that of Database::getInstance() in /var/www/html/infobase/system/drivers/DB_Oracle_custom.php on line 42
I get this error by calling:
$db2 = Database_custom::getInstance($GLOBALS['TL_CONFIG']['dbUser_Infobase'],$GLOBALS['TL_CONFIG']['dbPass_Infobase'], $GLOBALS['TL_CONFIG']['dbSid_Infobase']);

you have to put the credentials *inside* the getInstance method and not as arguments

[freeyland]

OK, but how do I then connect to 2 different databases?

thx for quick reply

[ga.n]

OK, but how do I then connect to 2 different databases?

thx for quick reply

I think there is no "clean" solution

just get your instance and then use the setDatabase function

http://dev.typolight.org/projects/typol ... tabase.php

probably you'll need to implement the set_database function in order to reflect this change (e.g. reconnecting or something similar)

[spartanin]

Hello
I have a question concerning the above subject. How would you can connect to the second base - for example: if the table tl_comments comments module would in another database? The question relates to the amount of cooperation Contao with distributed databases.

[acenes]

Contao has no explicit implementation of distributed data, because such things can and should be implemented in the database layer and should be transparent to the application. Here are some pointers to different solutions, where what you describe is the second option:

1. Load Balancing / Clustering[/*:m:2b4p1wp5]
2. Federated Tables[/*:m:2b4p1wp5]

Depending on the path you take, the tables created by the Contao installer need to be modified. Also, the built in database administration is only designed for MySQL, using any other database means you need to create the tables manually anyway.

[spartanin]

Ok thx for answer but this is not the answer to my question. It's not about the installation, only an existing page. For example: because the page is big size (large city portal) we would have to break down with time tables, news and comments on a separate database.The question is - freeyland gave some solution, and if such can be used for MySQL connect with other db?

[acenes]

Thats exactly what the linked documentation about Federated Table describes.

Only, the application (contao) connects to one single (main) MySQL instance, and that main instance links distinct tables to another MySQL instance (on an other server typically). This is completely transparent to the application.

[thyon]

I agree with acenes.

I used to be involved in systems design with our people from the database division, and the application connects to the database. The database server has a transparent mechanism to create replication and load balancing. The application doesn't have to be modified in any way. This is purely a database function. The port listener on the db intercepts the calls and redistributes them with load sharing on several replicated databases.