Custom CDBConnection

Hello

I’m using a Sqlite database, but i can only access it over a custom made server.

How the server works is that user connects to some host and port, sends sql, server executes it and returns data in a json format.

My question is, would it be possible to modify (or add) something to yii fw (I’m thining CDBConnection) so that i could connect to my server and execute queries on it, parse results and get models out of it? Basically i would like to use User::model()->fetchAll(), relations and everything with my server.

Thanks.

Interesting problem - i’d say you’re out of luck, but i’m not 100% sure :)

CDbConnection wraps up PDO. Some higher level components (DAO, AR) directly access PDO methods. PDO doesn’t support your setup, you’d have to create your custom connection that wraps up some custom PDO components. These custom components would have to behave exactly like PDO objects (i.e. PDO, PDOStatement). With the difference that they adapt the PDO calls to your remote server. Not an easy job, i suppose.

It would be easier, if you could fetch the generated SQL from a PDOStatement object, which you can’t.

Yes, after reviewing the CDBConnection code i’ve come to the same conclusion. A bit of a design flaw if you ask me.

I guess I’ll have to fill models manually and then use CArrayDataProvider. Too bad, it would be nice to use CActiveRecords :(.

“Design flaw” is a too strong accusation, if you ask me :)

PDO is the motor that drives many of the advanced features of Yii and does a great job in abstracting DB access. It wouldn’t make sense to re-implement all it’s functionality in raw PHP - that would be much the same as you would have to do now.

But maybe there’s still hope: this extension here could give you some ideas. They try to solve a similar problem:

http://www.yiiframework.com/extension/phppdo

Just out of curiosity I looked further into the “capture sql string” part of the problem. It’s possible to get the effective SQL string from PHPPDO’s db specific statement script.

For the mysql driver mysql_statement.php




protected function _execute()

{

  $query = $this->_build_query();

  // SQL string now in $query

  ...

}



The sqlite driver sqlite2_statement.php looks exactly the same but I didn’t test this.




protected function _execute()

{

  $query = $this->_build_query();

  if(!$query) return false;


  $this->_result = @sqlite_query($this->_link, $query, SQLITE_NUM, $errstr);

  ...

}




From my very limited investigation I can tell there’s at least one more place to care about:

In my small test case, the method exec() of phppdo.php is used to issue the statement “SET NAMES ‘utf8’”




public function exec($statement)

{

  // SQL string in $statement

  ...

}




I also tried to find a way to just hook into PDOStatement using the real PDO, but wasn’t able to get the “effective SQL” (with parameters bound).

My extended PdoStatement installed without problem




Item::model()->getDbConnection()->getPdoInstance()->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('MyPdoStatement'));






class MyPdoStatement extends PDOStatement

{

  public function execute($input_parameters = array())

  {

    $retval = parent::execute($input_parameters);


    // 1st try: just unbounded SQL, no params

    Yii::log('querystring: '.$this->queryString, 'info', 'sql');


    // 2nd try: SQL plus params

    ob_start(function(){return '';});

    $this->debugDumpParams();

    $query = ob_get_flush();

    Yii::log('query dump:'.$query, 'info', 'sql');


    return $retval;

  }

}




Bottom line is: Using PHPPDO it can be done. Without PHPPDO we can get the same unassembled SQL in an even more parsing friendly format from the Yii standard logging.

/Tommy

I managed to solve the problem.

Basically I implemented new pdo driver, which communicates with my server. By new driver i mean made a class named SqlListenerPDOClient, opened php docs and implemented every function that PDO class has. Made the same with PDOStatement.

Then I made a class SqlListenerDbConnection that inherits from CBConnection and overrides method createPdoInstance().

here is the code.


<?php


class SqlListenerDbConnection extends CDbConnection {


    protected function createPdoInstance() {

        return new SqlListenerPDOClient($this->connectionString);

    }


}


?>



here is main.php conf




        'db' => array(

            'class' => 'SqlListenerDbConnection',

            'connectionString' => 'host=192.168.2.34;port=50004;priority=2',)

...



Had some problems getting it working at first. Had to alter my server to return column names, because yii uses assoc arrays when fetching data.

Now it works great.

Wow, congratulations!

One more update.

Be sure to implement fetch type PDO::FETCH_COLUMN.

Yiic tool uses that when you generate models.

find the solution of phpuser0129387 very useful

you could upload the code of the class SqlListenerPDOClient where you implement all PDO and PDOStatement functions

thanks in advance