CActiveFinder->populateRecord and Column Case

[rickgrana (Ricardo Grana)]

Not confortable with the change i saw in 1.0.8 version.

I think is more flexible to check PDO::ATTR_CASE. Even if this does not affect Oracle, PDO will remains marked as UPPER/LOWERcased and the change will work to any database.

[rickgrana (Ricardo Grana)]

I am coming to correct myself and say again that ATTR_CASE works for Oracle!

I was a bit confused because I was very busy and it was a long time I have wrote the driver and have made the changes.

I ran this simple test in Yii 1.0.7:

<?php


class OraclePDOTest extends PHPUnit_Framework_TestCase

{

	public function testModelUpperOrLowerCase()

	{

		$db = Yii::app()->db;


		$db->active = false;

		$db->active = true;

		$userModel = User::model();

		$columnsCaseDefault = $userModel->getMetaData()->columns;

		$columnNameDefault = key($columnsCaseDefault);


		$db->active = false;

		$db->active = true;

		$db->setAttribute(PDO::ATTR_CASE,PDO::CASE_LOWER);

		$userModel = Contact::model();

		$columnsCaseLower = $userModel->getMetaData()->columns;

		$columnNameLower = key($columnsCaseLower);


		$db->active = false;

		$db->active = true;

		$db->setAttribute(PDO::ATTR_CASE,PDO::CASE_UPPER);

		$userModel = EmailAddress::model();

		$columnsCaseUpper = $userModel->getMetaData()->columns;

		$columnNameUpper = key($columnsCaseUpper);


		$report .= '';

		$report .= 'php version     = '.phpversion();

		$report .= "\n";

		$report .= 'oci8 version    = '.phpversion('oci8');

		$report .= "\n";

		$pdoVersion = phpversion('pdo');

		if(empty($pdoVersion)) {

			$pdoVersion = 'emulated by PHPPDO';

		}

		$report .= 'pdo version     = '.$pdoVersion;

		$report .= "\n";

		$report .= '$columnNameDefault = '.$columnNameDefault;

		$report .= "\n";

		$report .= '$columnNameLower   = '.$columnNameLower;

		$report .= "\n";

		$report .= '$columnNameUpper   = '.$columnNameUpper;

		$report .= "\n";

		echo $report;

	}

}

I got the following results:

php version = 5.1.6

oci8 version = 1.3.5

pdo version = 1.0.3

$columnNameDefault = USER_ID

$columnNameLower =

$columnNameUpper = ADDRESS

php version = 5.1.6

oci8 version = 1.3.5

pdo version = emulated by PHPPDO

$columnNameDefault = USER_ID

$columnNameLower =

$columnNameUpper = ADDRESS

php version = 5.2.9-4

oci8 version = 1.3.5

pdo version = 1.0.4dev

$columnNameDefault = USER_ID

$columnNameLower =

$columnNameUpper = ADDRESS

php version = 5.2.9-4

oci8 version = 1.3.5

pdo version = emulated by PHPPDO

$columnNameDefault = USER_ID

$columnNameLower =

$columnNameUpper = ADDRESS

In all these versions and combinations, I got the same result: setting the column case to lower does not work. Maybe have you changed the framework more?

Also, I ran the command

find . -type f -print0 | xargs -0 -e grep -nHi -e "'t"

in system.db and could not see any direct dependence in alias names.

The problem is that the ATTR_CASE must to have set BEFORE activate the db.

So, the best place to put the proper code is in the initConnection method of the AR. Instead of overusing CDbConnections, I wrote a COciConnection:

<?php

/**

 * COciConnection class file

 *

 * @author Ricardo Grana <rickgrana@yahoo.com.br>

 * @link http://www.yiiframework.com/

 * @copyright Copyright &copy; 2008-2009 Yii Software LLC

 * @license http://www.yiiframework.com/license/

 */


class COciConnection extends CDbConnection

{

	/**

	 * Initializes the open db connection.

	 * This method is invoked right after the db connection is established.	 

	 * @param PDO the PDO instance

	 */

	protected function initConnection($pdo)

	{

		parent::initConnection($pdo);


		Yii::trace('Setting Oracle case','system.db.schema.oci.CDbConnection');		

		$pdo->setAttribute(PDO::ATTR_CASE,PDO::CASE_LOWER);

		

		Yii::trace('Setting NLS_DATE_FORMAT','system.db.schema.oci.CDbConnection');

		$stmt=$pdo->prepare("alter session set NLS_DATE_FORMAT='dd/MM/yyyy HH24:MI:SS'");

		$stmt->execute();	


		Yii::trace('Setting NLS_TIMESTAMP_FORMAT','system.db.schema.oci.CDbConnection');		

		$stmt=$pdo->prepare("alter session set NLS_TIMESTAMP_FORMAT='dd/MM/yyyy HH24:MI:SS'");

		$stmt->execute();		

	}

}

At the entry script, db params, I specify at the class the COciConnection to be used.

But look, using this with the actual driver will crash the application, because the actual driver is configured ro return columns references in lowercase.

You need a new COciSchema, that is attached.

My intention is to rewrite this driver so it will support both cases. If I upload my actual driver (using lowercases), it will crash the existing projects done with uppercased columns.

[fruitpocket (Dsp)]

Hi Ricardo!

Great work - unfortunately I did a few days ago exactly the same within COciSchema.php - next time a read more in detail what people already worked out!

I also included the lowercasing of the table names in my COciSchema.php - Implementation.

To make things running smooth all lowercase, I had to change CActiveFinder.php, where the following lines made parameters uppercase when using oci - which caused problems in relations within CActiveModel

This fix is counterproductive when lowercasing, so simple change this:

                if($this->model->getDbConnection()->getDriverName()==='oci')  // Issue 482

                        $prefix='T'.$id.'_C';

                else

                        $prefix='t'.$id.'_c';

to only

                 $prefix='t'.$id.'_c';

As I see you wrote initially the entire oci - Implementation, maybe we can work out an out-of-the-box support - using Oracle all lowercase. Most Database-schemes are all uppercase (no quoting used), and when generating model with giic result in "ugly" UPPERCASE-Members in the model class.

Regards

fruitpocket