Doctrine 1
  1. Doctrine 1
  2. DC-260

Improper translation of data types

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.0.13, 1.1.4, 1.1.5
    • Fix Version/s: 1.2.0-RC1
    • Component/s: Schema Files
    • Labels:
      None
    • Environment:
      Linux, Oracle 10g

      Description

      Doctrine translates the datatype integer to oracle datatype number.
      Doctrine generates number(8) for id's, numer(4) for left/right values
      in nested sets and number(2) for the level column in nested sets.

      This will result in corrupted trees or unexpected behavior if a tree
      does contain more than 9.999 records or has more than 99 levels.

        Activity

        Hide
        Jonathan H. Wage added a comment -

        Should it just be using INTEGER instead of NUMBER? It seems INTEGER is an alias for NUMBER(38) ?

        Show
        Jonathan H. Wage added a comment - Should it just be using INTEGER instead of NUMBER? It seems INTEGER is an alias for NUMBER(38) ?
        Hide
        Thomas Wahle added a comment -

        Hi Jon,

        an 8 byte integer has a range from -9.223.372.036.854.775.808 to 9.223.372.036.854.775.807

        As far as i know there is no column type integer with oracle. I dont know if any database support unsigned 8 byte integer. NUMBER(20,0) would be the best substitution for INTEGER in my opinion.

        Show
        Thomas Wahle added a comment - Hi Jon, an 8 byte integer has a range from -9.223.372.036.854.775.808 to 9.223.372.036.854.775.807 As far as i know there is no column type integer with oracle. I dont know if any database support unsigned 8 byte integer. NUMBER(20,0) would be the best substitution for INTEGER in my opinion.
        Hide
        Jonathan H. Wage added a comment -

        I read here:

        http://ss64.com/ora/syntax-datatypes.html

        INTEGER
        This ANSI datatype will be accepted by Oracle - it is actually a synonym for NUMBER(38)
        

        This is code that maps integer to NUMBER currently. What do you think we should change this to?

                    case 'int':
                        $length = (!empty($field['length'])) ? $field['length'] : false;
                        if ( $length && $length <= $this->conn->number_max_precision)  {
                            if ($length <= 1) {
                                return 'NUMBER(3)'; // TINYINT
                            } elseif ($length == 2) {
                                return 'NUMBER(5)'; // SMALLINT
                            } elseif ($length == 3) {
                                return 'NUMBER(8)'; // MEDIUMINT
                            } elseif ($length == 4) {
                                return 'NUMBER(10)'; // INTEGER
                            } elseif ($length <= 8) {
                                return 'NUMBER(20)'; // BIGINT
                            } else {
                                return 'NUMBER('.$length.')';
                            }
                        }
                        return 'INT';
        
        Show
        Jonathan H. Wage added a comment - I read here: http://ss64.com/ora/syntax-datatypes.html INTEGER This ANSI datatype will be accepted by Oracle - it is actually a synonym for NUMBER(38) This is code that maps integer to NUMBER currently. What do you think we should change this to? case ' int ': $length = (!empty($field['length'])) ? $field['length'] : false ; if ( $length && $length <= $ this ->conn->number_max_precision) { if ($length <= 1) { return 'NUMBER(3)'; // TINYINT } elseif ($length == 2) { return 'NUMBER(5)'; // SMALLINT } elseif ($length == 3) { return 'NUMBER(8)'; // MEDIUMINT } elseif ($length == 4) { return 'NUMBER(10)'; // INTEGER } elseif ($length <= 8) { return 'NUMBER(20)'; // BIGINT } else { return 'NUMBER('.$length.')'; } } return 'INT';
        Hide
        Thomas Wahle added a comment -

        Hi Jon,

        never seen before but i works:

        CREATE TABLE Foo (Bar INTEGER);

        table created successfuly

        Displaying oracle sql statement for the table:

        CREATE TABLE "FOO"
        ( "BAR" NUMBER(*,0)
        )
        /

        If oracle says NUMBER(38,0) is the subtype of INTEGER and SMALLINT then i would use these subtypes.

        From oracle standard package:

        type NUMBER is NUMBER_BASE;
        subtype FLOAT is NUMBER; – NUMBER(126)
        subtype REAL is FLOAT; – FLOAT(63)
        subtype "DOUBLE PRECISION" is FLOAT;
        subtype INTEGER is NUMBER(38,0);
        subtype INT is INTEGER;
        subtype SMALLINT is NUMBER(38,0);
        subtype DECIMAL is NUMBER(38,0);
        subtype NUMERIC is DECIMAL;
        subtype DEC is DECIMAL;

        subtype BINARY_INTEGER is INTEGER range '-2147483647'..2147483647;
        subtype NATURAL is BINARY_INTEGER range 0..2147483647;
        subtype NATURALN is NATURAL not null;
        subtype POSITIVE is BINARY_INTEGER range 1..2147483647;
        subtype POSITIVEN is POSITIVE not null;
        subtype SIGNTYPE is BINARY_INTEGER range '-1'..1;

        Show
        Thomas Wahle added a comment - Hi Jon, never seen before but i works: CREATE TABLE Foo (Bar INTEGER); table created successfuly Displaying oracle sql statement for the table: CREATE TABLE "FOO" ( "BAR" NUMBER(*,0) ) / If oracle says NUMBER(38,0) is the subtype of INTEGER and SMALLINT then i would use these subtypes. From oracle standard package: type NUMBER is NUMBER_BASE; subtype FLOAT is NUMBER; – NUMBER(126) subtype REAL is FLOAT; – FLOAT(63) subtype "DOUBLE PRECISION" is FLOAT; subtype INTEGER is NUMBER(38,0); subtype INT is INTEGER; subtype SMALLINT is NUMBER(38,0); subtype DECIMAL is NUMBER(38,0); subtype NUMERIC is DECIMAL; subtype DEC is DECIMAL; subtype BINARY_INTEGER is INTEGER range '-2147483647'..2147483647; subtype NATURAL is BINARY_INTEGER range 0..2147483647; subtype NATURALN is NATURAL not null; subtype POSITIVE is BINARY_INTEGER range 1..2147483647; subtype POSITIVEN is POSITIVE not null; subtype SIGNTYPE is BINARY_INTEGER range '-1'..1;
        Hide
        Jonathan H. Wage added a comment -

        Can you help me out with the code In Doctrine_DataDict_Oracle::getNativeDeclaration(), can you help me come up with are the best native types to translate the portable Doctrine types to.

        Show
        Jonathan H. Wage added a comment - Can you help me out with the code In Doctrine_DataDict_Oracle::getNativeDeclaration(), can you help me come up with are the best native types to translate the portable Doctrine types to.
        Hide
        Thomas Wahle added a comment -
        case 'integer':
        case 'int':
        	$length = (!empty($field['length'])) ? $field['length'] : false;
        	if ( $length && $length <= $this->conn->number_max_precision)  {
        		if ($length <= 1) {
        			return 'NUMBER(3)'; // TINYINT, unsigned max. 256
        		} elseif ($length == 2) {
        			return 'NUMBER(5)'; // SMALLINT, unsigend max. 65.536
        		} elseif ($length == 3) {
        			return 'NUMBER(8)'; // MEDIUMINT, unsigned max. 16.777.216
        		} elseif ($length == 4) {
        			return 'NUMBER(10)'; // INTEGER, unsigend max. 4.294.967.296
        		} elseif ($length <= 8) {
        			return 'NUMBER(20)'; // BIGINT, unsigend max. 18.446.744.073.709.551.616
        		} else {
        			return 'INTEGER';
        		}
        	}
                return 'INTEGER';
        
        Show
        Thomas Wahle added a comment - case 'integer': case ' int ': $length = (!empty($field['length'])) ? $field['length'] : false ; if ( $length && $length <= $ this ->conn->number_max_precision) { if ($length <= 1) { return 'NUMBER(3)'; // TINYINT, unsigned max. 256 } elseif ($length == 2) { return 'NUMBER(5)'; // SMALLINT, unsigend max. 65.536 } elseif ($length == 3) { return 'NUMBER(8)'; // MEDIUMINT, unsigned max. 16.777.216 } elseif ($length == 4) { return 'NUMBER(10)'; // INTEGER, unsigend max. 4.294.967.296 } elseif ($length <= 8) { return 'NUMBER(20)'; // BIGINT, unsigend max. 18.446.744.073.709.551.616 } else { return 'INTEGER'; } } return 'INTEGER';
        Hide
        Jonathan H. Wage added a comment -

        Thanks!

        Show
        Jonathan H. Wage added a comment - Thanks!

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Thomas Wahle
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: