COciColumnSchema and NUMBER column type

Yii 1.1 General Discussion
1

Hi!

If column type in Oracle database is NUMBER with precision and scale designators are absent, COciColumnSchema.extractType returns ‘integer’. But Oracle documentation says:

.

ExtractType must return ‘double’ in this case.

My patches to correct this:




--- COciColumnSchema.php.orig   2009-12-10 19:45:31.000000000 +0700

+++ COciColumnSchema.php        2009-12-10 19:44:58.000000000 +0700

@@ -34,6 +34,8 @@

                                if(isset($values[1]) and (((int)$values[1]) > 0))

                                        return 'double';

                                else return 'integer';

+                       }else{

+                               return 'double';

                        }

                }else{

                        return 'string';







--- COciSchema.php.orig 2009-12-10 19:45:46.000000000 +0700

+++ COciSchema.php      2009-12-10 19:37:34.000000000 +0700

@@ -145,6 +145,7 @@

                     case when a.data_scale > 0 then ',' || a.data_scale else '' end

                 || ')'

         when data_type = 'DATE' then ''

+        when data_type = 'NUMBER' then ''

         else '(' || to_char(a.data_length) || ')'

     end as data_type,

     a.nullable, a.data_default,
2

NUMBER columns just need to behave like doube type if it have a specified precision.

e.g:


NUMBER(5,2)

NUMBER(2,1)

NUMBER(8,4)

etc.

Otherwise, it must behave like an integer type. E.g:




NUMBER //no scale or precision defined

NUMBER(2)

NUMBER(<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />

etc.

The actual code detect this situations:




if(strpos($dbType,'(') && preg_match('/\((.*)\)/',$dbType,$matches)) // check if $dbType has parenthesis '(*)'

            {

                // check if content inside parenthesis contains comma - if so, it has precision and it is double type

                $values=explode(',',$matches[1]);

                if(isset($values[1]) and (((int)$values[1]) > 0))  

                    return 'double';

                else return 'integer'; // if no precision, it is integer

            }
3

No.

The absence of precision and scale designators [size="4"]specifies the maximum range and precision[/size] for an Oracle number.

4 



Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production


SQL> create table test ( a number );


Таблица создана.


SQL> insert into test values (2/3);


1 строка создана.


SQL> select * from test;


	 A

----------

.666666667


SQL>
5

It looks like Capt’s patch made it into COciColumnSchema.php, causing all of the primary keys in our database to be evaluated as PHP doubles. This in itself is not a huge issue. The problem arises in the CDBColumnSchema::typecast() function which for some reason does not typecast doubles.




...

switch($this->type)

{

	case 'string': return (string)$value;

	case 'integer': return (integer)$value;

	case 'boolean': return (boolean)$value;

	case 'double':

	default: return $value;

}

...

I’m seeing a problem in the updateByPk() function in CActiveRecord. Because CActiveRecord is either storing or converting my primary keys as strings internally (for whatever reason), they need to be changed by typecast() back into a NUMBER (double) before they get built into the criteria for an update. But the lack of a double typecast in CDBColumnSchema::typecast() causes my primary key to evaluate to NULL in the update criteria. For now, I’m going to over-ride the typecast() function in my version of COciColumnSchema to the following:




...

switch($this->type)

{

	case 'string': return (string)$value;

	case 'integer': return (integer)$value;

	case 'boolean': return (boolean)$value;

	case 'double': return (double)$value;

	default: return $value;

}

...

This seems to work, but I don’t know if this is the proper thing to do.