CActiveFinder->populateRecord and Column Case

Archives Bug Discussions
1

Hello,

While testing oracle driver, I had a problem using 'with' clause (like Post:model()->with('author')…). Yii was losing data and returning nothing.

After searching, I realized that the problem was not in the query, but in the CActiveFinder->populateRecord method. There, we see the following line:

if(!isset($row[$this->_pkAlias])) return null;

The _pkAlias is generated by Yii, as "t0_c0" column name.

BUT, Oracle PDO driver forces the query to return the column names in uppercase, in this case, t0_c0 is returned T0_C0, which means that the PK is not found by $row[$this->_pkAlias], because of the case sentivity.

Independent of the database, I think that the _pkAlias must observe the PDO::ATTR_CASE configuration, obtaining it from the $connection->getColumnCase()

2

Thanks for finding out this issue. What about normal attributes/columns? Are they also changed to upper-case by Oracle? Do they work as expected?

3

Normal attributes/columns aren´t working, too. Problems with the aliases because they are uppercased.

4

Could you create a ticket for this? If you could provide more details about column namings in Oracle, it would be better since I don't have Oracle to test with.

Thank you very much!

5

Ok, i will do that!

Anyway, i just resolved it. Please check to see if this can be applied to the framework core.

In CDbConnection->createPdoInstance()

below the line

    $mypdo = new $pdoClass($this->connectionString,$this->username,

								$this->password,$this->_attributes);

add:

    if($driver==='oci'){

$mypdo->setAttribute(PDO::ATTR_CASE,PDO::CASE_UPPER); // to identify that our database use uppercase

    }

In ActiveFinder->construct():

Below the lines:

$table=$this->_table;

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

add:

$case = $this->_builder->getDbConnection()->getPdoInstance()->getAttribute(PDO::ATTR_CASE);

if ($case == PDO::CASE_UPPER){

$prefix = strtoupper($prefix);

}

This resolves the aliases in lowercase. However, the best solution is to use $mypdo->setAttribute(PDO::ATTR_CASE,PDO::CASE_LOWER) and make the oracle driver to work with column names in lowercase, as the other databases. When I get time, I will do this!

6

This problem is not in PDO driver, but in oracle itself, and Yii assumes that aliases for tables and columns will be answered as they are sent:

If we sent a query like

[sql] select abc as def from ghi[/sql]

we will get a table with a column in upper case always:

[font="Lucida Console"] DEF

 value1


 value2[/font]

If we create a table with quoted identifiers, then they will be treated case sensitive, but in all queries we will need to quote identifiers:

[sql]

create table "User" … colunm "myName" …

select "myName" from "User" OK

select myName from User Error

[/sql]

Yii (1.0.7) make aliases for columns and tables in lowercase in system.db.ar.CActiveFinder constructor.

To solve these issues, I just changed lines 326, 373 and 375 as follows:




	public function __construct($finder,$relation,$parent=null,$id=0)

	{

		$this->_finder=$finder;

		$this->id=$id;

		if($parent!==null)

		{

			$this->relation=$relation;

			$this->_parent=$parent;

			$this->_builder=$parent->_builder;

326			$this->tableAlias=$relation->alias===null?'T'.$id:$relation->alias;

			$this->model=CActiveRecord::model($relation->className);

			$this->_table=$this->model->getTableSchema();

		}

		else  // root element, the first parameter is the model.

		{

			$this->model=$relation;

			$this->_builder=$relation->getCommandBuilder();

			$this->_table=$relation->getTableSchema();

		}


373		// set up column aliases, such as T1_C2

		$table=$this->_table;

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

		foreach($table->getColumnNames() as $key=>$name)

		{

			$alias=$prefix.$key;

			$this->_columnAliases[$name]=$alias;

			if($table->primaryKey===$name)

				$this->_pkAlias=$alias;

			else if(is_array($table->primaryKey) && in_array($name,$table->primaryKey))

				$this->_pkAlias[$name]=$alias;

		}

	}

Aliases in uppercase will always work in oracle and probably in all other supported databases.

Changing PDO driver case will not work since table descriptions return columns names and table names in uppercase.

Can these changes be commited in Yii 1.0.8 ?

Thanks

7

With above changes, DB driver for oracle can do relations(), with(), together() and scopes().

Now (1.0.7), this driver can just do scopes().

Thanks

8

Sorry Marcio, but you are wrong. Yes, columns came from Oracle in uppercase, but change Column Case works.

In my own version for Yii-Oci driver, my columns are set to lower-case. It is not submitted to official version for compatibility (because of this bug, columns are generated by yiic uppercased).

Yes, actually the driver don’t do ‘with’ or ‘together’, but with the changes I’ve posted before, it works.

I think Qiang had not applied these changes to core framework yet.

9

I have tested the first solution and I got the columns in upper case too. Maybe it is a version issue, since my php version is 5.1.6 and its pdo_oci is the pecl one.

I have seen the source code for pdo_oci in the php tree versions 5.2, 5.3 and the one in pecl, it probably is causing our differences.

Using PHPPDO to emulate pdo also causes the same upper case column names when reading the models descriptions.

So the later solution are more "portable".

10

Not sure, but I think your solution will brake compatibility with the other drivers (which expects lowercase aliases as "t0_c0" and so on).

Edit: Sorry, I haven´t paid attention to what you exactly said. My solution still makes columns in Uppercase, it just makes explicity to PDO that we are using uppercase, so the second part of the code I posted will work.

If you want to the framework works weel with Oracle, you could do the following:




  if($driver==='oci'){

$mypdo->setAttribute(PDO::ATTR_CASE,PDO::CASE_LOWER); // to identify that our database use uppercase

    }

This will change your columns to lowercase and the stuffs will start to work well :)

Regards!

11

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.

12

So sorry, you are right!! :-X

I use


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

just to certify myself that the columns cases will return right. But it doesn’t do the trick!

If you want your columns in lowercase, you will have to do another changes. There are many modifications, so I think it is better to upload it as an extension. I’m going to do that.

13

Thanks.

I could not find a way to change these without changing the framework.

14

Is it required that we modify the case of table aliases?

I can change the column aliases to upper case for Oracle. Could you help verify if this works (without changing the current table aliases)? Thanks!

15

Yes, I can.

Where can I find "this" to test?

Oracle always convert every column/alias to uppercase, so every reference to them must be in the same case.

If some patch converts every column to lowercase, aliases must be converted too and references also.

16

No code yet. I plan to use your code at http://www.yiiframework.com/forum/index.php?/topic/1982-cactivefinder-populaterecord-and-column-case/page__view__findpost__p__18828

A difference is that line 326 (table alias) will not be changed.

17

Yes, it works without changing table alias to upper case.

The following test was ran with all my changes, and without changing table alias:




<?php


class ActiveRecordTest extends PHPUnit_TestCase

{

	protected function getContactsFixture($userId, $relationValid = false,

	    $numEmailAddresses = 3)

	{

		$contact = new Contact;

		$contact->first_name = 'nome';

		$contact->user_id = $userId;

		$emailAddresses = array();

		for($j = 0 ; $j < $numEmailAddresses ; $j++) {

			$emailAddress = new EmailAddress;

			if($relationValid) {

				$emailAddress->address = 'abc@def_'.$j.'.com';

			} else {

				$emailAddress->address = 'abc_'.$j;

			}

			$emailAddress->contact_id = $contact->contact_id;

			$emailAddresses[] = $emailAddress;

		}

		$contact->emailAddresses = $emailAddresses;

		$contact->defaultEmailAddress = $emailAddress;

		return $contact;

	}


	public function testSetAndSaveRelations()

	{

		$this->setCurrentUserFixture();

		$userId = Yii::app()->user->id;

		Contact::model()->deleteAll('user_id = :userId',

		    array(':userId'=>$userId));


		$contact = self::getContactsFixture($userId);

		$emailAddresses = $contact->emailAddresses;

		$this->assertFalse(empty($emailAddresses));

		$defaultEmailAddress = $contact->defaultEmailAddress;

		$this->assertFalse(empty($defaultEmailAddress));

		$valid = $contact->save();

		$this->assertEquals(false, $valid);


		$contact = self::getContactsFixture($userId, true);

		$emailAddresses = $contact->emailAddresses;

		$this->assertFalse(empty($emailAddresses));

		$defaultEmailAddress = $contact->defaultEmailAddress;

		$this->assertFalse(empty($defaultEmailAddress));

		$valid = $contact->saveWithRelated();

		$this->assertEquals(true, $valid);

		$contacts = Contact::model()->forCurrentUser()->

		    with('emailAddresses', 'defaultEmailAddress')->together()->

		    findAll();

		$contact = $contacts[0];

		$this->assertFalse(empty($contact));

		$emailAddresses = $contact->emailAddresses;

		$this->assertFalse(empty($emailAddresses));

		$defaultEmailAddress = $contact->defaultEmailAddress;

		$this->assertFalse(empty($defaultEmailAddress));

		$this->assertEquals($defaultEmailAddress->emailaddress_id,

		    $contact->default_emailaddress_id);


		$contact->delete();

		$contacts = Contact::model()->forCurrentUser()->

		    with('emailAddresses', 'defaultEmailAddress')->together()->

		    findAll();

		$contact = $contacts[0];

		$this->assertTrue(empty($contact));

	}

...

}
18

Thanks! I just checked in the fix to 1.0 branch. Could you help test it again?

19

It is working now. I downloaded CActiveFinder from 1.0 branch and ran the same tests.

Very thanks.

20

Great! Thank you.