Database Specific Syntax in an Extension

Yii 1.1 General Discussion
1

I’ve been working on an extension for grid filtering, but some of the functionality requires database specific syntax. For my own uses, I need to support MySQL and SQL Server, but I’d like to make it easy to extend to other databases.

I’ve looked into how Yii handles variances in database syntax using the $driverMap property of CDbConnection to create a database specific schema class, which in turn, can create a database specific command builder class.

I need to do essentially the same thing. What’s the cleanest way for me to apply database specific syntax? I could create a property in the extension, which maps either the driver or the schema class name to a database helper class name, but this seems like the wrong approach.

2

For me this is not a wrong approach.

  • create a property dbAdapterClass

  • implement a abstract BaseDbAdapter with common methods for all db and abstract methods for the dbspecific stuff

    alternative:

    • implement all methods for db handling

      with some empty methods that needs to be overridden - or add throw new CException(‘Not implemented’)

    • define an interface with methods that every specific dbAdapter must implement

Create you specific dbAdapterClasses: DbAdapterMySql, DbAdapterSqlServer, … inherited from the BaseDbAdapter

In your extension you add the method ‘getDbAdapter’ that returns a memory cached instance.




class MyExtension

{

   public $dbAdapterClass='DbAdapterMySql'; //the default


   protected $_dbAdapter;


   public function getDbAdapter()

   {

      if(!isset($this->_dbAdapter)

      {

        $class = $this->dbAdapterClass;

        $this->_dbAdapter = new $class;   

      }


      return $this->_dbAdapter;      

   }




}


//call

$myExtension->getDbAdapter()->someDbAction($formModel,$data,$whatever);


...


$myExtension->getDbAdapter()->anotherDbAction();
3

That’s almost exactly the solution I went with, although I’m referring to the classes as database helpers. (Is adapter a better name?)

I decided to duplicate Yii’s driverMap approach, so I’ve added this property:




	/**

	 * @var array mapping between PDO driver and database helper class path.

	 * Each database helper must extend BaseAdvancedFilterDbHelper.

	 * If the $dbConnection has a driver name that is not specified in this

	 * array, or it maps to null, an AdvancedFilterException will be thrown.

	 */

	public $driverMap = array(

		'pgsql'=>null,                           // PostgreSQL

		'mysqli'=>'MysqlAdvancedFilterDbHelper', // MySQL

		'mysql'=>'MysqlAdvancedFilterDbHelper',  // MySQL

		'sqlite'=>null,                          // sqlite 3

		'sqlite2'=>null,                         // sqlite 2

		'mssql'=>'MssqlAdvancedFilterDbHelper',  // Mssql driver on windows

		'dblib'=>'MssqlAdvancedFilterDbHelper',  // dblib drivers on linux

		'sqlsrv'=>'MssqlAdvancedFilterDbHelper', // Mssql

		'oci'=>null,                             // Oracle driver

	);

It should allow the system to select the right class based on the provided connection, and will throw an

exception at the point that a filter condition is added to a criteria if the driver isn’t recognised or is unsupported.

The base class contains default implementations for syntax that is commonly used, but abstract function declarations for things that are database specific, like regular expressions.

I’m almost ready for an initial release. It’ll be interesting to get feedback on extensibility and internationalisation concerns from people who are more experienced in extension authoring.