[DC-279] Code Corrections For MsSql modifyLimitQuery Created: 23/Nov/09  Updated: 23/Nov/09  Resolved: 23/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Connection
Affects Version/s: 1.1.5
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Michael Card Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None


 Description   

Below are some code corrections for the modifyLimitQuery. These fixes should also fix DC-37

Code details:

  • Fixed case on sorting orders to match all other syntax.
  • Added additional explode to ensure we only get fieldname and not tablename.fieldname.
  • Added code to determine primary key for use in select statement. There may be a better way of doing this, but not with the input sent to the function I don't think.
  • Removed the outer table (not sure how this could have ever worked) and switched inner table to return only primary key and not all fields.
  • Removed outer table order by logic.

Diff - Old To New:

160c160
<                     $sorts[$i] = (stripos($orders[$i], ' desc') !== false) ? 'desc' : 'asc';
---
>                     $sorts[$i] = (stripos($orders[$i], ' desc') !== false) ? 'DESC' : 'ASC';
171a172
>                     $aux2 = explode('.', end($aux2));
185a187,191
>             $fields_string = substr($query, strlen($selectReplace), strpos($query, ' FROM ') - strlen($selectReplace));
>             $field_array = explode(',', $fields_string);
>             $aux2 = explode('.', $field_array[0]);
>             $key_field = trim(end($aux2));
> 
187c193
<             $query = 'SELECT * FROM (SELECT TOP ' . $count . ' * FROM (' . $query . ') AS ' . $this->quoteIdentifier('inner_tbl');
---
>             $query = 'SELECT TOP ' . $count . ' ' . $this->quoteIdentifier('inner_tbl') . '.' . $key_field . ' FROM (' . $query . ') AS ' . $this->quoteIdentifier('inner_tbl');
199,212d204
<                 }
<             }
< 
<             $query .= ') AS ' . $this->quoteIdentifier('outer_tbl');
< 
<             if ($orderby !== false) {
<                 $query .= ' ORDER BY '; 
< 
<                 for ($i = 0, $l = count($orders); $i < $l; $i++) { 
<                     if ($i > 0) { // not first order clause 
<                         $query .= ', '; 
<                     } 
< 
<                     $query .= $this->quoteIdentifier('outer_tbl') . '.' . $aliases[$i] . ' ' . $sorts[$i];

Entire New Function:

    public function modifyLimitQuery($query, $limit = false, $offset = false, $isManip = false)
    {
        if ($limit > 0) {
            $count = intval($limit);
            $offset = intval($offset);

            if ($offset < 0) {
                throw new Doctrine_Connection_Exception("LIMIT argument offset=$offset is not valid");
            }

            $orderby = stristr($query, 'ORDER BY');

            if ($orderby !== false) {
                // Ticket #1835: Fix for ORDER BY alias
                // Ticket #2050: Fix for multiple ORDER BY clause
                $order = str_ireplace('ORDER BY', '', $orderby);
                $orders = explode(',', $order);

                for ($i = 0; $i < count($orders); $i++) {
                    $sorts[$i] = (stripos($orders[$i], ' desc') !== false) ? 'DESC' : 'ASC';
                    $orders[$i] = trim(preg_replace('/\s+(ASC|DESC)$/i', '', $orders[$i]));

                    // find alias in query string
                    $helper_string = stristr($query, $orders[$i]);

                    $from_clause_pos = strpos($helper_string, ' FROM ');
                    $fields_string = substr($helper_string, 0, $from_clause_pos + 1);

                    $field_array = explode(',', $fields_string);
                    $field_array = array_shift($field_array);
                    $aux2 = spliti(' as ', $field_array);
                    $aux2 = explode('.', end($aux2));

                    $aliases[$i] = trim(end($aux2));
                }
            }

            // Ticket #1259: Fix for limit-subquery in MSSQL
            $selectRegExp = 'SELECT\s+';
            $selectReplace = 'SELECT ';

            if (preg_match('/^SELECT(\s+)DISTINCT/i', $query)) {
                $selectRegExp .= 'DISTINCT\s+';
                $selectReplace .= 'DISTINCT ';
            }

            $fields_string = substr($query, strlen($selectReplace), strpos($query, ' FROM ') - strlen($selectReplace));
            $field_array = explode(',', $fields_string);
            $aux2 = explode('.', $field_array[0]);
            $key_field = trim(end($aux2));

            $query = preg_replace('/^'.$selectRegExp.'/i', $selectReplace . 'TOP ' . ($count + $offset) . ' ', $query);
            $query = 'SELECT TOP ' . $count . ' ' . $this->quoteIdentifier('inner_tbl') . '.' . $key_field . ' FROM (' . $query . ') AS ' . $this->quoteIdentifier('inner_tbl');

            if ($orderby !== false) {
                $query .= ' ORDER BY '; 

                for ($i = 0, $l = count($orders); $i < $l; $i++) { 
                    if ($i > 0) { // not first order clause 
                        $query .= ', '; 
                    } 

                    $query .= $this->quoteIdentifier('inner_tbl') . '.' . $aliases[$i] . ' '; 
                    $query .= (stripos($sorts[$i], 'asc') !== false) ? 'DESC' : 'ASC';
                }
            }
        }

        return $query;
    }


 Comments   
Comment by Jonathan H. Wage [ 23/Nov/09 ]

Thanks for the ticket and changes to the function.





[DC-276] HAVING does not parse column aliases Created: 21/Nov/09  Updated: 08/Jan/10  Resolved: 23/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.0-BETA3
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Andrei Dziahel Assignee: Guilherme Blanco
Resolution: Fixed Votes: 0
Labels: None


 Description   

How to reproduce

$query->having('<aggregate_alias> <operator> <column_alias>').

Expected SQL:

 
... HAVING <internal_aggregate_alias> <operator> a<N>__<column_name>...

Produced SQL

... HAVING <internal_aggregate_alias> <operator> <column_alias> ... /* yes, the same <column_alias> as in ->having() call! */

I've prepared this "real-world example".

Please fix it someone since I'm forced to invent weird workarounds to emulate this behaviour.

Thanks in advance.



 Comments   
Comment by Timo Haberkern [ 08/Jan/10 ]

Doesn't work for me in Symfony 1.3.1, Doctrine 1.2.1

using this DQL:

Doctrine_Query::create()
->select('COUNT(s.status) as cn MIN(s.status) as minstat')
->from('LogisticOrder o')
->innerJoin('o.Status os')
->innerJoin('o.PickingRequests r')
->innerJoin('r.Status s')
->where('os.status=?', OrderStatusLog::$STATUS_PICKING)
->groupBy('r.order_id')
->having('minstat=? AND cn=1', array(512))
->getSqlQuery();

I get

SELECT COUNT(s4.status) AS s4__0 FROM sd_logisticorder s INNER JOIN sd_order_status_log s2 ON s.status_id = s2.id INNER JOIN sd_picking_request s3 ON s.id = s3.order_id INNER JOIN sd_picking_request_status s4 ON s3.id = s4.request_id WHERE (s2.status = ?) GROUP BY s3.order_id HAVING (minstat=? AND cn=1 )

Comment by Timo Haberkern [ 08/Jan/10 ]

Just to inform you: The problem is the missing space at the having operators

Doesn't work:
->having('minstat=? AND cn=1', array(512))

Works:
->having('minstat = ? AND cn = 1', array(512))





[DC-274] Doctrine_Parser_Yml: hardcoded require calls for sfYaml Created: 21/Nov/09  Updated: 23/Nov/09  Resolved: 23/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: None
Affects Version/s: 1.2.0-BETA3
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Lukas Kahwe Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None


 Description   

In Doctrine_Parser_Yml the requires calls to sfYaml are hardcoded:
if ( ! class_exists('sfYaml', false)) {
require_once dirname(_FILE_) . '/../../vendor/sfYaml/sfYaml.php';
require_once dirname(_FILE_) . '/../../vendor/sfYaml/sfYamlDumper.php';
require_once dirname(_FILE_) . '/../../vendor/sfYaml/sfYamlInline.php';
require_once dirname(_FILE_) . '/../../vendor/sfYaml/sfYamlParser.php';
}

This causes issues when one has already included sfYaml elsewhere. Either one needs to include the code for sfYaml twice or one needs to always load sfYaml before Doctrine parses any yaml files.

A better approach to handling the fact that sfYaml does not adhere to the standard naming convention is to do these require calls inside the autoloader, but only using "include" in order to allow a fallback autoloader to also be able to load the code.



 Comments   
Comment by Jonathan H. Wage [ 23/Nov/09 ]

Thanks!





[DC-267] Strange behaviour with HYDRATE_ARRAY Created: 19/Nov/09  Updated: 19/Nov/09  Resolved: 19/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Relations
Affects Version/s: 1.2.0-BETA3
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Thomas Wahle Assignee: Roman S. Borschel
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux, Oracle 10g



 Description   

Some values of a join can be found in root component some in the relations - even for the same value

$q = Doctrine_Query::create()
-> select ('a.id, b.code, b.code as also_b_code')
-> from('Foo a')
-> innerJoin('a.Bar b')
->setHydrationMode(Doctrine::HYDRATE_ARRAY);

$res = $q->execute();

id is primary key of table Foo and code is primary key of table Bar

The result will contain id AND also_b_code as $res[0]['id'] and $res[0]['also_b_code'] but code is under $res[0]['Bar']['code']

Is there any way to get Doctrine::HYDRATE_ARRAY to work as under 1.1?






[DC-266] I18n - PostgreSQL - translation table name Created: 19/Nov/09  Updated: 19/Nov/09  Resolved: 19/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: I18n
Affects Version/s: 1.0.12
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Jakub Novotny Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None
Environment:

PostgreSQL



 Description   

Tables, which are translated, do not use table name from classes of tables with translations, but it seems that Doctrine just transforms
class name from camel case to underscore and appends translation. We use different class names, because of the problem with schemas described in
http://www.doctrine-project.org/jira/browse/DC-264.

Is it possible to fix this behavior - get table name from class?
Or at least write in documentation, that we can use atribute tableName in section I18n?






[DC-263] Incorrect type in PGSQL import. Created: 19/Nov/09  Updated: 19/Nov/09  Resolved: 19/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Import/Export
Affects Version/s: None
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Pierre Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None
Environment:

no matter



 Description   

in http://trac.doctrine-project.org/browser/branches/1.1/lib/Doctrine/Import/Pgsql.php

line 172

used if (strtolower($val['type']) === 'varchar') {

but in postgres varchar does not exist so you must compare with character varying like that :

if (strtolower($val['type']) === 'character varying') {






[DC-261] Query overrwtires explicitly passed connection with bound one Created: 19/Nov/09  Updated: 19/Nov/09  Resolved: 19/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Connection, Query
Affects Version/s: 1.2.0-ALPHA1, 1.2.0-ALPHA2, 1.2.0-ALPHA3, 1.2.0-BETA1, 1.2.0-BETA2, 1.2.0-BETA3
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Eugene Janusov Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None


 Description   

Doctrine_Query::loadRoot() and Doctrine_Query::buildInheritanceJoinSql() have the following snippet of code:

        // get the connection for the component
        $manager = Doctrine_Manager::getInstance();
        if ($manager->hasConnectionForComponent($name)) {
            $this->_conn = $manager->getConnectionForComponent($name);
        }

Seems clear that here we would like to use the connection bound to the given component instead of default one. But if we explicitly pass a connection in (like Doctrine_Query::create($conn)), then this code will also overwrite it with bound connection, although passed connection should have higher priority than bound one.

I would like to prepare a patch, but I'm not absolutely sure, why we check for bound connection only inside loadRoot() and buildInheritanceJoinSql()?






[DC-260] Improper translation of data types Created: 18/Nov/09  Updated: 23/Nov/09  Resolved: 23/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Schema Files
Affects Version/s: 1.0.13, 1.1.4, 1.1.5
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Thomas Wahle Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
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.



 Comments   
Comment by Jonathan H. Wage [ 19/Nov/09 ]

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

Comment by Thomas Wahle [ 19/Nov/09 ]

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.

Comment by Jonathan H. Wage [ 19/Nov/09 ]

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';
Comment by Thomas Wahle [ 19/Nov/09 ]

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;

Comment by Jonathan H. Wage [ 19/Nov/09 ]

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.

Comment by Thomas Wahle [ 19/Nov/09 ]
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';
Comment by Jonathan H. Wage [ 23/Nov/09 ]

Thanks!





[DC-257] Missing identifier quoting in Doctrine_Connection_Oracle Created: 18/Nov/09  Updated: 18/Nov/09  Resolved: 18/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.0.12, 1.0.13, 1.1.4, 1.1.5
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Thomas Wahle Assignee: Guilherme Blanco
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux, Oracle 10g



 Description   

If identifier quoting is enabled doctrine does not perform quoting in some subqueries.

$this->quoteIdentifier has to be added as below in Doctrine_Connection_Oracle oracle.php at line 106

$query = 'SELECT ' . $this->quoteIdentifier('b') . '.' .$column. ' FROM ('.
'SELECT a.*, ROWNUM AS doctrine_rownum FROM ('
. $query . ') a '.
') ' . $this->quoteIdentifier('b') .' '.
'WHERE doctrine_rownum BETWEEN ' . $min . ' AND ' . $max;






[DC-256] Identifier Quoting not made in generated subquery Created: 18/Nov/09  Updated: 19/Nov/09  Resolved: 19/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.0.12, 1.1.4, 1.1.5
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Thomas Wahle Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux, Oracle 10g



 Description   

If identifier quoting is enabled doctrine does not perform quoting in some subqueries.

$this->_conn->quoteIdentifier has to be added twice as below in query.php line 1426:

$subquery = 'SELECT doctrine_subquery_alias.' . $this->_conn->quoteIdentifier($table->getColumnName($table->getIdentifier()))

    . ' FROM (' . $subquery . ') doctrine_subquery_alias'
    . ' GROUP BY doctrine_subquery_alias.' . $this->_conn->quoteIdentifier($table->getColumnName($table->getIdentifier()))
    . ' ORDER BY MIN(ROWNUM)'; 


 Comments   
Comment by Thomas Wahle [ 19/Nov/09 ]

In 1.2 Beta 3 the quoting is not fixed

Identifier Quoting need to be done in Doctrine_Query line 1462 and line 1464 to $table->getColumnName($table->getIdentifier

$subquery = 'SELECT doctrine_subquery_alias.' . $table->getColumnName($table->getIdentifier())
. ' FROM (' . $subquery . ') doctrine_subquery_alias'
. ' GROUP BY doctrine_subquery_alias.' . $table->getColumnName($table->getIdentifier())
. ' ORDER BY MIN(ROWNUM)';

The above code creates incorrect statements if identifier quoting is enabled

Comment by Jonathan H. Wage [ 19/Nov/09 ]

This is fixed in SVN and will be in 1.2.0-RC1





[DC-255] XML Export not in UTF-8 Created: 18/Nov/09  Updated: 19/Nov/09  Resolved: 19/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Import/Export
Affects Version/s: 1.2.0-BETA3
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Benno Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None
Environment:

W2003 Server
MSSQL 2000 (Collation: Latin1_General_CI_AS)
PHP 5.2.5 (cli) (built: Nov 8 2007 23:18:51)
Copyright (c) 1997-2007 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2007 Zend Technologies


Attachments: File parser_encoding_parser.diff     File parser_encoding_xml.diff    

 Description   

I'm testing doctrine 1.2.0-B3 connected to mssql 2000 for XML based exports.

We use latin1 (ISO-8859-1) as collation for the mssql server. When i now create an XML export in Doctrine, Doctrine uses SimpleXML which requires the strings encoded in UTF-8, but our mssql collation is latin1. This results in a invalid, non UTF-8 XML file.
It's required to add the ability to convert the strings from the database encoding to the 'export' encoding.
(similar to http://trac.doctrine-project.org/ticket/1887)

I have added a simple patch to add this possibility, then we can do something like this:

Doctrine_Parser::dump($data->toArray(false, true), 'xml', 'testExport.xml', 'ISO-8859-1');


 Comments   
Comment by Benno [ 19/Nov/09 ]

Because arrayToXml is a recursive method, add the encoding parameter to the recursive method call.

81c81
<                 self::arrayToXml($value, $rootNodeName, $node, $charset);
---
>                 self::arrayToXml($value, $rootNodeName, $node);

I think the use of htmlentities is not recommended, because the method doesn't create XML compliant strings.

If you want to use the function, use it with encoding.

$value = htmlentities($value, ENT_COMPAT, 'UTF-8');
OR
$value = htmlspecialchars($value, ENT_COMPAT, 'UTF-8');
Comment by Jonathan H. Wage [ 19/Nov/09 ]

Thanks for letting me know I missed that





[DC-254] getLimitSubquery Not Including All Order By Parts Created: 18/Nov/09  Updated: 27/Feb/11

Status: Reopened
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.0-BETA3
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Michael Card Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 1
Labels: None


 Description   

The getLimitSubquery function is only including the first orderby field and ignoring all remaining parts. The function also needs to be changed to include mssql and odbc.

The diff's below provide corrections.

Thanks,

Mike

diff -r Doctrine-1.1.2\lib\Doctrine\Query.php Doctrine-1.1.2-Fixes\lib\Doctrine\Query.php
1367c1367
<         if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci') {
---
>         if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'mssql' || $driverName == 'odbc') {
1371,1387c1371,1392
<                 $part_original = trim($e[0]);
<                 $callback = create_function('$e', 'return trim($e, \'[]`"\');');
<                 $part = trim(implode('.', array_map($callback, explode('.', $part_original))));
< 
<                 if (strpos($part, '.') === false) {
<                     continue;
<                 }
< 
<                 // don't add functions
<                 if (strpos($part, '(') !== false) {
<                     continue;
<                 }
< 
<                 // don't add primarykey column (its already in the select clause)
<                 if ($part !== $primaryKey) {
<                     $subquery .= ', ' . $part_original;
<                 }
---
> 
>                 foreach ($e as $f) {
>                     if ($f == 0 || $f % 2 == 0) {
>                         $part_original = trim($f);
>                         $callback = create_function('$e', 'return trim($e, \'[]`"\');');
>                         $part = trim(implode('.', array_map($callback, explode('.', $part_original))));
> 
>                         if (strpos($part, '.') === false) {
>                             continue;
>                         }
> 
>                         // don't add functions
>                         if (strpos($part, '(') !== false) {
>                             continue;
>                         }
> 
>                         // don't add primarykey column (its already in the select clause)
>                         if ($part !== $primaryKey) {
>                             $subquery .= ', ' . $part_original;
>                         }
>                     }
>                 }



 Comments   
Comment by Michael Card [ 22/Dec/09 ]

Additonal fix for this issue, was missing dblib originally.

Mike


diff -r Doctrine-1.1.2\lib\Doctrine\Query.php Doctrine-1.1.2-Fixes\lib\Doctrine\Query.php
1367c1367
< if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'mssql' || $driverName == 'odbc') {
—
> if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'mssql' || $driverName == 'odbc' || $driverName == 'dblib') {

Comment by Andrej Pavlovic [ 27/Feb/11 ]

Come on guys, please include this fix into 1.2.4...





[DC-253] Missing Data Types In DataDict For MsSql Created: 18/Nov/09  Updated: 18/Nov/09  Resolved: 18/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Import/Export
Affects Version/s: 1.2.0-BETA3
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Minor
Reporter: Michael Card Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None


 Description   

There are some missing data types in the getPortableDeclaration function for MsSql. The following diff adds the types I found missing so far.

Thanks,
Mike

diff -r doctrine\lib\Doctrine\DataDict\Mssql.php Doctrine-1.1.2\lib\Doctrine\DataDict\Mssql.php
193,202d192
< case 'uniqueidentifier':
< $type[] = 'string';
< $length = 36;
< break;
< case 'sql_variant':
< case 'sysname':
< case 'binary':
< $type[] = 'string';
< $length = null;
< break;






[DC-252] Doctrine ignores UNIQUE:true on int Created: 18/Nov/09  Updated: 18/Nov/09  Resolved: 18/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Import/Export
Affects Version/s: 1.1.5
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Ignacio El Kadre Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None
Environment:

Gentoo 2.6.21.5, PHP Version 5.2.8



 Description   

Consider this schema.yml:

Orden:
  tableName: ordenes    
  options:
    type: InnoDB  
  columns:
    id:
      primary: true
      type: integer(4)
      autoincrement: true
    numero: 
      type: integer(4)
      unique: true

sql:

CREATE TABLE ordenes (id INT AUTO_INCREMENT, numero INT, PRIMARY KEY(id)) COLLATE utf8_unicode_ci ENGINE = InnoDB; 

unique constraint don't appear.



 Comments   
Comment by Ignacio El Kadre [ 18/Nov/09 ]

spliting problem and solution

Comment by Ignacio El Kadre [ 18/Nov/09 ]

Editing Doctrine/DataDict/Mysql.php, getIntegerDeclaration method, adding:

$unique = (isset($field['unique']) && $field['unique']) ? ' UNIQUE' : ''; //line 490

and

return $name . ' ' . $this->getNativeDeclaration($field) . $unsigned
. $default . $unique . $notnull . $autoinc . $comment; //adding unique field

the problem was solved.

Sorry by my english and sorry if this post is in wrong place.
Cheers
Ignacio

Comment by Ignacio El Kadre [ 18/Nov/09 ]

Don't forget add $unique to return sentence:

return $name . ' ' . $this->getNativeDeclaration($field) . $unsigned
. $default . $unique . $notnull . $autoinc . $comment;





[DC-249] Doctrine_Manager::closeConnection() always changes current connection Created: 18/Nov/09  Updated: 18/Nov/09  Resolved: 18/Nov/09

Status: Resolved
Project: Doctrine 1
Component/s: Connection
Affects Version/s: 1.2.0-ALPHA1, 1.2.0-ALPHA2, 1.2.0-ALPHA3, 1.2.0-BETA1, 1.2.0-BETA2, 1.2.0-BETA3
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Minor
Reporter: Eugene Janusov Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None

Attachments: Text File DC-249.patch    

 Description   

Doctrine_Manager::closeConnection() should try to change current connection only if the just closed one was the current connection.



 Comments   
Comment by Eugene Janusov [ 18/Nov/09 ]

Proposed patch attached.





[DC-245] Table name prefix for table migration_version Created: 17/Nov/09  Updated: 28/May/10  Resolved: 17/Nov/09

Status: Resolved
Project: Doctrine 1
Component/s: Migrations
Affects Version/s: 1.1.5
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Minor
Reporter: Erik Wegner Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None

Attachments: Text File migration_table_prefix.patch    

 Description   

The doctrine migration class sets a default table name for storing its version information, which bypasses any formatting rules, e.g. a table name prefix. Calling the function setTableName() does apply the format defined, but one cannot call _createMigrationTable() afterwards. This results in a situation, where on one hand I can have the table automatically created with the "wrong" name and on the other hand have the class use the correct table name but not create it at first.

The attached patch is a simple solution to this dilemma, it added a call from the contructor to set the table name by the function call.



 Comments   
Comment by Jonathan H. Wage [ 17/Nov/09 ]

This is fixed properly in Doctrine 1.2

Comment by Erik Wegner [ 28/May/10 ]

For Doctrine 1.2 see DC-383.





[DC-244] Missing savepoint methods on Doctrine_EventListener_Chain Created: 17/Nov/09  Updated: 17/Nov/09  Resolved: 17/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: None
Affects Version/s: 1.2.0-BETA2
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Pierrot Evrard Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None
Environment:

symfony 1.4


Attachments: File Chain.php    

 Description   

A couple of methods are missing to use savepoint argument in transaction methods from Doctrine_Transaction object.

If you want to process the bug, start a transaction, start a savepoint, and you will see a fatal error.

Below, you will find a piece of code with missing methods to add to Doctrine_EventListener_Chain class:

// @edit: Missing methods – loops
// @start

/**
* postSavepointCommit
* an event invoked after a Doctrine_Connection transaction with savepoint
* is committed
*
* @param Doctrine_Event $event
* @return void
*/
public function postSavepointCommit(Doctrine_Event $event)
{
foreach ($this->_listeners as $listener)

{ $listener->postSavepointCommit($event); }

}

/**
* preSavepointCommit
* an event invoked before a Doctrine_Connection transaction with savepoint
* is committed
*
* @param Doctrine_Event $event
* @return void
*/
public function preSavepointCommit(Doctrine_Event $event)
{
foreach ($this->_listeners as $listener)

{ $listener->preSavepointCommit($event); }

}

/**
* postSavepointRollback
* an event invoked after a Doctrine_Connection transaction with savepoint
* is being rolled back
*
* @param Doctrine_Event $event
* @return void
*/
public function postSavepointRollback(Doctrine_Event $event)
{
foreach ($this->_listeners as $listener)

{ $listener->postSavepointRollback($event); }

}

/**
* preSavepointRollback
* an event invoked before a Doctrine_Connection transaction with savepoint
* is being rolled back
*
* @param Doctrine_Event $event
* @return void
*/
public function preSavepointRollback(Doctrine_Event $event)
{
foreach ($this->_listeners as $listener)

{ $listener->preSavepointRollback($event); }

}

/**
* postSavepointCreate
* an event invoked after a Doctrine_Connection transaction with savepoint
* has been started
*
* @param Doctrine_Event $event
* @return void
*/
public function postSavepointCreate(Doctrine_Event $event)
{
foreach ($this->_listeners as $listener)

{ $listener->postSavepointCreate($event); }

}

/**
* preSavepointCreate
* an event invoked before a Doctrine_Connection transaction with savepoint
* is being started
*
* @param Doctrine_Event $event
* @return void
*/
public function preSavepointCreate(Doctrine_Event $event)
{
foreach ($this->_listeners as $listener)

{ $listener->preSavepointCreate($event); }

}
// @end

Please update.

Loops



 Comments   
Comment by Jonathan H. Wage [ 17/Nov/09 ]

Thanks for the ticket and patch. It is fixed in Doctrine 1.2 in svn now.





[DC-243] Passing $options to Doctrine_Core::generateModelsFromDb() From Doctrine_Cli() Created: 17/Nov/09  Updated: 17/Nov/09  Resolved: 17/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Cli
Affects Version/s: 1.2.0-BETA2
Fix Version/s: 1.2.0-RC1

Type: Improvement Priority: Major
Reporter: Dolly Aswin Harahap Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None
Environment:

Doctrine 1.2-BETA2


Attachments: Text File DC-243.patch    

 Description   

Here the issue when I create models from existing database.
I use doctrine-cli to do that, and make some configuration like this

Array
(
[connection_string] => mysql://user:password@localhost/db
[data_fixtures_path] => /data/fixtures/path
[models_path] => /models/path
[migrations_path] => /migrations/path
[sql_path] => /sql/path
[yaml_schema_path] => /yaml/schema/path
[generate_models_options] => Array
(
[generateBaseClasses] => 1
[classPrefixFiles] => Model
[classPrefix] => Model
[baseClassPrefix] => Base_
)
)

But, when I run the command with like this
php doctrine-cli.php generate-models-db

All models is created, but not follow the configuration.
I have look the code and find the real problem is options argument didn't pass when call Doctrine_Core::generateModelsFromDb()

Regards

Dolly



 Comments   
Comment by Dolly Aswin Harahap [ 17/Nov/09 ]

Here I attach a patch for this issue. This patch is created from branches 1.2
Please check the patch.





[DC-242] Linking (re-linking) o/m2m relations with array of ids in Doctrine_Record fromArray stopped working Created: 17/Nov/09  Updated: 17/Nov/09  Resolved: 17/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Record
Affects Version/s: 1.2.0-BETA3
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Blocker
Reporter: Maciej Hołyszko Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 1
Labels: None
Environment:

1.2 svn



 Description   
class User extends Doctrine_Record
{
	public function setTableDefinition()
	{
		$this->hasColumn('username', 'string', 64, array('notnull' => true));
		$this->hasColumn('password', 'string', 128, array('notnull' => true));
	}
	
	public function setUp()
	{
		$this->hasMany('Role as Roles', array('local' => 'id_user', 'foreign' => 'id_role', 'refClass' => 'UserRole'));
		$this->actAs('SoftDelete');
	}
}

class Role extends Doctrine_Record
{
	public function setTableDefinition()
	{
		$this->hasColumn('name', 'string', 64);
	}
	
	public function setUp()
	{
		$this->hasMany('User as Users', array('local' => 'id_role', 'foreign' => 'id_user', 'refClass' => 'UserRole'));
	}
}

class UserRole extends Doctrine_Record
{
	public function setTableDefinition()
	{
		$this->hasColumn('id_user', 'integer', null, array('primary' => true));
		$this->hasColumn('id_role', 'integer', null, array('primary' => true));
	}
	
	public function setUp()
	{
		$this->hasOne('User', array('local' => 'id_user', 'foreign' => 'id', 'onDelete' => 'CASCADE'));
		$this->hasOne('Role', array('local' => 'id_role', 'foreign' => 'id', 'onDelete' => 'CASCADE'));
	}
}


$role = new Role();
$role->name = 'publisher';
$role->save();

$role = new Role();
$role->name = 'reviewer';
$role->save();

$role = new Role();
$role->name = 'mod';
$role->save();

$user = new User();
$user->fromArray(array(
	'username' => 'test',
	'password' => 'test',
	'Roles' => array(1, 2, 3),
));
$user->save();

//--------------------- here goes the failure
$user->fromArray(array(
	'Roles' => array(1, 3),
));
$user->save();

After a User is saved once with some roles, it cannot be saved here with another combination of roles, consisting of some roles which was saved to the User before.
In short, ->unlink() in fromArray method does not schedule a deletion of old relations before adding new ones on save, like it was in previous versions of Doctrine.
Hence the primary key constraint is violated and the exception is thrown:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-1' for key 1
#0 D:\projekty\cms-trunk\cms\library\Doctrine\Doctrine\Connection\Statement.php(269): Doctrine_Connection->rethrowException(Object(PDOException), Object(Doctrine_Connection_Statement))
#1 (...)\library\Doctrine\Doctrine\Connection.php(1032): Doctrine_Connection_Statement->execute(Array)
#2 (...)\library\Doctrine\Doctrine\Connection.php(677): Doctrine_Connection->exec('INSERT INTO `us...', Array)
#3 (...)\library\Doctrine\Doctrine\Connection\UnitOfWork.php(631): Doctrine_Connection->insert(Object(Doctrine_Table), Array)
#4 (...)\library\Doctrine\Doctrine\Connection\UnitOfWork.php(562): Doctrine_Connection_UnitOfWork->processSingleInsert(Object(UserRole))
#5 (...)\library\Doctrine\Doctrine\Connection\UnitOfWork.php(81): Doctrine_Connection_UnitOfWork->insert(Object(UserRole))
#6 (...)\library\Doctrine\Doctrine\Connection\UnitOfWork.php(452): Doctrine_Connection_UnitOfWork->saveGraph(Object(UserRole))
#7 (...)\library\Doctrine\Doctrine\Connection\UnitOfWork.php(137): Doctrine_Connection_UnitOfWork->saveAssociations(Object(User))
#8 (...)\library\Doctrine\Doctrine\Record.php(1690): Doctrine_Connection_UnitOfWork->saveGraph(Object(User))
#9 (...)\test3.php(124): Doctrine_Record->save()
#10

{main}

 Comments   
Comment by Maciej Hołyszko [ 17/Nov/09 ]

Now I see I duplicated the following ticket: DC-228
Please fix this issue.

Comment by Maciej Hołyszko [ 17/Nov/09 ]

Aaand DC-189 is also related.





[DC-241] Count query with join o2m + limit + where throws an exception Created: 17/Nov/09  Updated: 17/Nov/09  Resolved: 17/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Record, Relations
Affects Version/s: 1.2.0-BETA3
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Blocker
Reporter: Maciej Hołyszko Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 1
Labels: None
Environment:

1.2 svn



 Description   
class Poll extends Doctrine_Record
{
	public function setTableDefinition()
	{
		$this->setTableName('module_polls');
		
		$this->hasColumn('id_category', 'integer', null, array('notnull' => true));
		$this->hasColumn('question', 'string', 256);
	}
	
	public function setUp()
	{
		$this->hasMany('PollAnswer as Answers', array('local' => 'id', 'foreign' => 'id_poll', 'orderBy' => 'position'));
	}
}

class PollAnswer extends Doctrine_Record
{
	public function setTableDefinition()
	{
		$this->setTableName('module_polls_answers');
		
		$this->hasColumn('id_poll', 'integer', null, array('notnull' => true));
		$this->hasColumn('answer', 'string', 256);
		$this->hasColumn('votes', 'integer', null, array('notnull' => true, 'default' => 0));
		$this->hasColumn('position', 'integer');
	}
	
	public function setUp()
	{
		$this->hasOne('Poll', array('local' => 'id_poll', 'foreign' => 'id', 'onDelete' => 'CASCADE'));
	}
}

$query = Doctrine_Query::create()
	->from('Poll p')
	->leftJoin('p.Answers pa')
	->where('id_category = ?', 1)
	->limit(10);

print_r($query->count());

This fails with the following exception being thrown:

SQLSTATE[42000]: Syntax error or access violation: 1064 Something is wrong in your syntax obok '? LIMIT 10' w linii 1. Failing Query: "SELECT DISTINCT `m3`.`id` FROM `module_polls` `m3` LEFT JOIN `module_polls_answers` `m4` ON `m3`.`id` = `m4`.`id_poll` WHERE `m3`.`id_category` = ? LIMIT 10"
#0 (...)\library\Doctrine\Doctrine\Connection.php(1015): Doctrine_Connection->rethrowException(Object(PDOException), Object(Doctrine_Connection_Mysql), 'SELECT DISTINCT...')
#1 (...)\library\Doctrine\Doctrine\Query.php(1246): Doctrine_Connection->execute('SELECT DISTINCT...', Array)
#2 (...)\library\Doctrine\Doctrine\Query.php(1106): Doctrine_Query->buildSqlQuery()
#3 (...)\library\Doctrine\Doctrine\Query\Abstract.php(1126): Doctrine_Query->getSqlQuery(Array)
#4 (...)\library\Doctrine\Doctrine\Query\Abstract.php(1088): Doctrine_Query_Abstract->_getDqlCallbackComponents(Array)
#5 (...)\library\Doctrine\Doctrine\Query.php(1126): Doctrine_Query_Abstract->_preQuery()
#6 (...)\library\Doctrine\Doctrine\Query.php(1106): Doctrine_Query->buildSqlQuery()
#7 (...)\library\Doctrine\Doctrine\Query.php(1995): Doctrine_Query->getSqlQuery()
#8 (...)\library\Doctrine\Doctrine\Query.php(2091): Doctrine_Query->getCountSqlQuery()
#9 (...)\test2.php(123): Doctrine_Query->count()
#10

{main}

You can see the generated query which is wrong, moreover it is passed with an empty params array().
Without either where condition or limit removed, it works. With a combo of both of them it fails.

E.g. without where condition the following query is generated:
SELECT COUNT AS `num_results` FROM (SELECT `m`.`id` FROM `module_polls` `m` LEFT JOIN `module_polls_answers` `m2` ON `m`.`id` = `m2`.`id_poll` GROUP BY `m`.`id`) `dctrn_count_query`



 Comments   
Comment by Maciej Hołyszko [ 17/Nov/09 ]

This is related to recently closed, but not fixed due to unability to reproduce, DC-138 - seems like the same problem. In that ticket it was not clearly stated that ->count() method is used on the query. It fails when a 'where' condition is added to the query, no matter if it's simple =? condition or LIKE one.

Comment by Jonathan H. Wage [ 17/Nov/09 ]

I am not sure that these are the same issues. Can you test this patch though?

Index: lib/Doctrine/Query.php
===================================================================
--- lib/Doctrine/Query.php	(revision 6739)
+++ lib/Doctrine/Query.php	(working copy)
@@ -1334,15 +1334,6 @@
             array_pop($this->_sqlParts['where']);
         }
 
-        if ($needsSubQuery) {
-            // We need to double shift if > 2
-            if (count($this->_sqlParts['where']) > 2) {
-                array_shift($this->_sqlParts['where']);
-            }
-
-            array_shift($this->_sqlParts['where']);
-        }
-
         $this->_sql = $q;
 
         $this->clear();

I think this should fix the issue. I am not sure what that block of code is supposed to be doing, but I think it is there incorrectly.

Comment by Maciej Hołyszko [ 17/Nov/09 ]

Unfortunately this patch does not fix the problem. The result is the same exception being thrown.
I think that part of code is there for some purpose, are you sure that removing will not break anything else? It is steered by $needsSubQuery variable which is set to true or false in few conditions above it.

Comment by Jonathan H. Wage [ 17/Nov/09 ]

No after talking with the team we realized that the code is not needed and should have been removed. It was there to "fix" a problem but at some point the real problem was fixed so this is no longer needed.

Comment by Jonathan H. Wage [ 17/Nov/09 ]

I committed some changes, can you test those? Also, can you get on IRC or something where we can chat so we can get this issue fixed today?





[DC-240] Automatic ordering through orderBy in many-to-many relation definition issue Created: 17/Nov/09  Updated: 17/Nov/09  Resolved: 17/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Record, Relations
Affects Version/s: 1.2.0-BETA3
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Critical
Reporter: Maciej Hołyszko Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 1
Labels: None
Environment:

1.2 svn



 Description   

I would like to utilize new nice feature in Doctrine 1.2, namely automatic ordering of relations using orderBy property in relation definition. However I see small flaw with m2m relations:

class User extends Doctrine_Record
{
	public function setTableDefinition()
	{
		$this->setTableName('core_users');
		
		$this->hasColumn('username', 'string', 64, array('notnull' => true));
		$this->hasColumn('password', 'string', 128, array('notnull' => true));
	}
	
	public function setUp()
	{
		$this->hasMany('Role as Roles', array('local' => 'id_user', 'foreign' => 'id_role', 'refClass' => 'UserRole', 'orderBy' => 'position'));
		$this->actAs('SoftDelete');
	}
}

class Role extends Doctrine_Record
{
	public function setTableDefinition()
	{
		$this->setTableName('core_roles');
		
		$this->hasColumn('name', 'string', 64);
	}
	
	public function setUp()
	{
		$this->hasMany('User as Users', array('local' => 'id_role', 'foreign' => 'id_user', 'refClass' => 'UserRole'));
		$this->hasMany('Role as Parents', array('local' => 'id_role_child', 'foreign' => 'id_role_parent', 'refClass' => 'RoleReference', 'orderBy' => 'position'));
		$this->hasMany('Role as Children', array('local' => 'id_role_parent', 'foreign' => 'id_role_child', 'refClass' => 'RoleReference'));
	}
}

class UserRole extends Doctrine_Record
{
	public function setTableDefinition()
	{
		$this->setTableName('core_users_roles');
		
		$this->hasColumn('id_user', 'integer', null, array('primary' => true));
		$this->hasColumn('id_role', 'integer', null, array('primary' => true));
		$this->hasColumn('position', 'integer', null, array('notnull' => true));
	}
	
	public function setUp()
	{
		$this->hasOne('User', array('local' => 'id_user', 'foreign' => 'id', 'onDelete' => 'CASCADE'));
		$this->hasOne('Role', array('local' => 'id_role', 'foreign' => 'id', 'onDelete' => 'CASCADE'));
	}
}

class RoleReference extends Doctrine_Record
{
	public function setTableDefinition()
	{
		$this->setTableName('core_roles_reference');
		
		$this->hasColumn('id_role_parent', 'integer', null, array('primary' => true));
		$this->hasColumn('id_role_child', 'integer', null, array('primary' => true));
		$this->hasColumn('position', 'integer', null, array('notnull' => true));
	}
	
	public function setUp()
	{
		$this->hasOne('Role as Parent', array('local' => 'id_role_parent', 'foreign' => 'id', 'onDelete' => 'CASCADE'));
		$this->hasOne('Role as Child', array('local' => 'id_role_child', 'foreign' => 'id', 'onDelete' => 'CASCADE'));
	}
}

$query = Doctrine_Query::create()
	->from('User u')
	->leftJoin('u.Roles r')
	->orderBy('username ASC')
	->execute();

Results in following query being generated:

SELECT `c`.`id` AS `c__id`, `c`.`username` AS `c__username`, `c`.`password` AS `c__password`, `c`.`deleted_at` AS `c__deleted_at`, `c2`.`id` AS `c2__id`, `c2`.`name` AS `c2__name` FROM `core_users` `c` LEFT JOIN `core_users_roles` `c3` ON (`c`.`id` = `c3`.`id_user`) LEFT JOIN `core_roles` `c2` ON `c2`.`id` = `c3`.`id_role` WHERE (`c`.`deleted_at` IS NULL) ORDER BY `c`.`username` ASC, position

As you can see, 'position' column at the end is not prefixed by an alias (should be `c3` here), which may arise problems when there are more than one column of that name used within tables used in the query.

Same with lazy-loading:

$query = Doctrine_Query::create()
	->from('User u')
	->orderBy('username ASC');
	
$result = $query->execute();
foreach($result as $item)
{
	echo count($item->Roles);
}

SELECT `c`.`id` AS `c__id`, `c`.`name` AS `c__name`, `c2`.`id_user` AS `c2__id_user`, `c2`.`id_role` AS `c2__id_role`, `c2`.`position` AS `c2__position` FROM `core_roles` `c` LEFT JOIN `core_users_roles` `c2` ON `c`.`id` = `c2`.`id_role` WHERE (`c2`.`id_user` IN (?)) ORDER BY position





[DC-239] Doctrine_Query::getCountQuery() produces subquery if the query contains joins Created: 16/Nov/09  Updated: 18/Nov/09  Resolved: 18/Nov/09

Status: Resolved
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.0-ALPHA1, 1.2.0-ALPHA2, 1.2.0-ALPHA3, 1.2.0-BETA1, 1.2.0-BETA2, 1.2.0-BETA3
Fix Version/s: 1.2.0-RC1

Type: Improvement Priority: Major
Reporter: Fabian Spillner Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None


 Description   

Is there a reason why Doctrine_Query::getCountQuery() produces subquery only if there is joins or having clause.

Its not good for high performance environment like ours: it makes the temp file disk full and could kill the server performance.

Let's the developer the freedom to decide what the count method should return: I prefer it as option like that:

public function count($withSubquery = false)

{ ... }

But I want to know the reason why subquery.



 Comments   
Comment by Jonathan H. Wage [ 18/Nov/09 ]

This is fixed in Doctrine 1.2.





[DC-238] Doctrine_Query::count() doesn't follow useResultCache Created: 16/Nov/09  Updated: 17/Nov/09  Resolved: 17/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.1.5
Fix Version/s: 1.2.0-RC1

Type: Improvement Priority: Critical
Reporter: Fabian Spillner Assignee: Guilherme Blanco
Resolution: Fixed Votes: 1
Labels: None

Attachments: File doctrine-query-count-cache.diff    

 Description   

The count method of Doctrine_Query doesn't follow useResultCache option, so I have to make loop away with fetchOne or something like that use "execute()".

So I fixed this issue, see the attachment for details!



 Comments   
Comment by Jonathan H. Wage [ 17/Nov/09 ]

I decided to fix this in Doctrine 1.2 before it really is too late.





[DC-228] Doctrine_Record::fromArray() may fail when input contains related component IDs. Created: 13/Nov/09  Updated: 17/Nov/09  Resolved: 17/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Record
Affects Version/s: 1.2.0-BETA1, 1.2.0-BETA2
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Zoltan Kovago Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None


 Description   

I try to explain through an example.

Data model:
===========

User — UserGroup — Group // many-to-many relation

Example code:
=============

$user = new User;
$user->name = 'user1';
$user->Group[0]->name = 'group1'; // id => 1
$user->Group[1]->name = 'group2'; // id => 2
$user->save();

$group3 = new Group;
$group3->name = 'group3';
$group3->save(); // id => 3

// edit user in an HTML form, and select all groups with checkboxes
// $_POST = array('name' => 'user1', 'Group' => array(1, 2, 3))
$user->fromArray($_POST);
$user->save(); // fail, unique constraint violation

Reason:
=======

Doctrine_Record::fromArray() calls unlink() in line 1948:

$this->unlink($key, array(), false);

unlink() fills _pendingUnlinks variable with User's Group relation IDs in line 2418:

if ( ! $ids) {
    $ids = $allIds;
}
foreach ($ids as $id) {
    $this->_pendingUnlinks[$alias][$id] = true;
}

So _pendingUnlinks[$alias] will be an array.

Then fromArray() calls link() in line 1949:

foreach ($value as $id) {
    $this->link($key, $id, false);
}

link() remove new IDs (1, 2 and 3) from _pendingUnlinks[$alias] in line 2496:

foreach ($ids as $id) {
    if (isset($this->_pendingUnlinks[$alias][$id])) {
        unset($this->_pendingUnlinks[$alias][$id]);
    }
}

So _pendingUnlinks[$alias] will be an empty array.

When we save user, this code is executed in Doctrine_Connection_UnitOfWork::saveGraph() (in line 103):

foreach ($record->getPendingUnlinks() as $alias => $ids) {
    if ($ids === false) {
        $record->unlinkInDb($alias, array());
    } else if ($ids) {
        $record->unlinkInDb($alias, array_keys($ids));
    }
}

BUT

Because _pendingUnlinks[$alias] is an empty array, neither IF branch will be executed, so relations won't be deleted.

And then Doctrine try to insert new relations, but it will fail because it will violate existed primary keys in UserGroup.

In an earlier version of Doctrine-1.2, the unlink() method set _pendingUnlinks[$alias] to false if $ids attribute was an empty array, so in saveGraph() all relations were deleted before new relations inserted.

/Sorry for my poor English/






[DC-198] refreshRelated inserts records when One-to-One relationships are used and a related record is deleted Created: 08/Nov/09  Updated: 19/Nov/09  Resolved: 19/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: None
Affects Version/s: 1.1.5
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Mario Bittencourt Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None
Environment:

Mac OS X, PHP 5.2.11, MySQL 5.1.37


Attachments: File DC198TestCase.php    

 Description   

When one-to-one relationship is used and a refreshRelated is performed after a delete a new record is inserted with NULL values.

schema.yml

Email:
columns:
user_id: integer
address: string(150)
relations:
User:
local: user_id
foreign: id
type: one
foreignType: one
foreignAlias: email

User:
columns:
name: string(150)

Sample code

Create.php - create the user/email

$u = new User();
$u->name = 'Test';
$u->email->address = 'foo@bar.com';
$u->save();

Delete.php - remove the email

$u = Doctrine_Query::create()>from('User')>fetchOne();
$u->name = 'Test2' ;
$u->email->delete();
$u->refreshRelated('email');
$u->save();

The profile (of Delete.php)

connect 0.005211

query 0.000493
SELECT u.id AS u_id, u.name AS u_name FROM user u
prepare 0.000028
SELECT e.id AS e_id, e.user_id AS euser_id, e.address AS e_address FROM email e WHERE e.user_id = ?
execute 0.001086
SELECT e.id AS e_id, e.user_id AS euser_id, e.address AS e_address FROM email e WHERE e.user_id = ?
Array
(
[0] => 1
)
Total time: 0.00681829452515
fetch 0.000024
SELECT e.id AS e_id, e.user_id AS euser_id, e.address AS e_address FROM email e WHERE e.user_id = ?
fetch 0.000016
SELECT e.id AS e_id, e.user_id AS euser_id, e.address AS e_address FROM email e WHERE e.user_id = ?
begin 0.000147

prepare 0.000019
DELETE FROM email WHERE id = ?
execute 0.000253
DELETE FROM email WHERE id = ?
Array
(
[0] => 1
)
Total time: 0.00727772712708
commit 0.001120

prepare 0.000021
SELECT e.id AS e_id, e.user_id AS euser_id, e.address AS e_address FROM email e WHERE e.user_id = ?
execute 0.000333
SELECT e.id AS e_id, e.user_id AS euser_id, e.address AS e_address FROM email e WHERE e.user_id = ?
Array
(
[0] => 1
)
Total time: 0.0087513923645
fetch 0.000017
SELECT e.id AS e_id, e.user_id AS euser_id, e.address AS e_address FROM email e WHERE e.user_id = ?
begin 0.000104

prepare 0.000020
UPDATE user SET name = ? WHERE id = ?
execute 0.000291
UPDATE user SET name = ? WHERE id = ?
Array
(
[0] => Test2
[1] => 1
)
Total time: 0.00918316841125
prepare 0.000021
INSERT INTO email (id, user_id, address) VALUES (?, ?, ?)
execute 0.000193
INSERT INTO email (id, user_id, address) VALUES (?, ?, ?)
Array
(
[0] =>
[1] =>
[2] =>
)
Total time: 0.00939726829529
commit 0.000823

a var_dump of $u->toArray() after refreshRelated shows

array(3) {
["id"]=>
string(1) "1"
["name"]=>
string(7) "Test2"
["email"]=>
array(3)

{ ["id"]=> string(1) "1" ["user_id"]=> string(1) "1" ["address"]=> string(8) "foo@bar.com" }

}

And in the database I have null values for the email record.

This does not happen if I change the relationship to hasMany



 Comments   
Comment by Jonathan H. Wage [ 10/Nov/09 ]

This should be fixed in 1.2 now. Can you test 1.2?

Comment by Mario Bittencourt [ 11/Nov/09 ]

Sorry. At least in my tests this is still not solved.

After I delete and call refreshRelated the object still contains the old values and if I do a save it adds a new item with NULL values to the related table.

My code

$f = Doctrine_Query::create()>from('User')>fetchOne();
$f->name = 'Test2' ;
$f->email->delete();
$f->refreshRelated('email');
var_dump($f->toArray()); // first var_dump

$f->save();

var_dump($f->toArray()); // second var_dump

// first var_dump
array(3) {
["id"]=>
string(1) "1"
["name"]=>
string(7) "Test2"
["email"]=>
array(3)

{ ["id"]=> string(1) "1" ["user_id"]=> string(1) "1" ["address"]=> string(8) "foo@bar.com" }

}

// second var_dump
array(3) {
["id"]=>
string(1) "1"
["name"]=>
string(7) "Test2"
["email"]=>
array(3)

{ ["id"]=> string(1) "2" ["user_id"]=> string(1) "1" ["address"]=> string(8) "foo@bar.com" }

}

In the database the email table contains

id = 2
user_id = NULL
address = NULL

Comment by Jonathan H. Wage [ 12/Nov/09 ]

When I test the code you're providing, against 1.2. I don't get the results you explain. Can you make a Doctrine test case to show the problem? Because I can't seem to reproduce it after my changes to fix the issue.

Comment by Mario Bittencourt [ 15/Nov/09 ]

Hi,
Sure I can make a test case as soon as I find out how to do that.

Any urls of how to create a test case?

Comment by Jonathan H. Wage [ 16/Nov/09 ]

In the documentation: http://www.doctrine-project.org/documentation/manual/1_2/en/unit-testing

Comment by Jonathan H. Wage [ 18/Nov/09 ]

Any update on this? I am still trying to produce the problem but everything is working for me in my tests. I want to get this fixed in RC1 by this friday if a issue really exists.

Comment by Mario Bittencourt [ 18/Nov/09 ]

Hi Jon,

I'll be able to take another look today (at night) so I'd probably will have an update by tomorrow.

Regards.

Comment by Jonathan H. Wage [ 18/Nov/09 ]

Any update? Can we chat in IRC or something to help try and find the problem?

Comment by Mario Bittencourt [ 19/Nov/09 ]

Hi,

I've attached the test file you've requested. I hope this helps.

Tested: 419 test cases.
Successes: 4333 passes.
Failures: 2 fails.
Number of new Failures: 0

Doctrine_Ticket_DC198_TestCase..................................................failed

Doctrine_Ticket_DC198_TestCase : method testRemoveEmail failed on line 32
Value1: 0
!=
Value2: 3

Doctrine_Ticket_DC198_TestCase : method testRemoveEmail failed on line 36

Comment by Jonathan H. Wage [ 19/Nov/09 ]

Thanks. As soon as I ran the test case I saw the problem and could fix it much much faster Thanks a lot!!





[DC-189] When applying changes to n:n relations that contain partially same data DELET is not fired Created: 04/Nov/09  Updated: 17/Nov/09  Resolved: 17/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Relations
Affects Version/s: 1.2.0-BETA1, 1.2.0-BETA2
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Critical
Reporter: Jaanus Heeringson Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None
Environment:

Error introduced in revision 6611



 Description   

When changing n:n relations in a way that the new dataset contains relations that were present in the previous state (as in adding a relation to existing ones) DELETE is not fired before insert is done. In the example below I simply reapply an existing relation. This is done by Record::synchronizeWithArray().

Result:

Doctrine_Connection_Mysql_Exception: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '4-14' for key 'PRIMARY'

SQL Query pre r6611:
6 Query START TRANSACTION
6 Query UPDATE `structure` SET `user_create_id` = NULL, `user_modify_id` = NULL, `updated_at` = '2009-11-04 23:38:02' WHERE `id` = '4'
6 Query DELETE FROM `structure_category` WHERE (`structure_id` = '4')
6 Query INSERT INTO `structure_category` (`category_id`, `structure_id`) VALUES ('5', '4')
6 Query COMMIT

SQL QUERY with r6611:
5 Query START TRANSACTION
5 Query UPDATE `structure` SET `user_create_id` = NULL, `user_modify_id` = NULL, `updated_at` = '2009-11-04 23:36:33' WHERE `id` = '4'
5 Query INSERT INTO `structure_category` (`category_id`, `structure_id`) VALUES ('5', '4')
5 Query ROLLBACK



 Comments   
Comment by Jaanus Heeringson [ 04/Nov/09 ]

Added 1.2.0-BETA2

Comment by Jonathan H. Wage [ 16/Nov/09 ]

Can you show the code you're using? a test case of some sort. It is really hard to fix the problem with so little information

Comment by Jaanus Heeringson [ 16/Nov/09 ]

This is quite a complex project so I'll try as best as I can.
What is happening here is that I'm adding another category (id:28) to the object (in this case an entry) that allready has one existing category (id: 10). The primary Id's are named - id!

The Code
	public function setJsonData($query,$data){
		$_query=$this->primaryQuery($query,self::RELATIONS_FULL); //generates DQL query - not relevant
		$_record=$_query->getOne(array(),2); //Fetches the existing record from DQL query
		$data=$this->fromJsonData($data,$query); //Cleans up the recieved JSON data and returns an array
		ss_error::log($data,'In data'); //Data output 1 - incoming data
		ss_error::log($_record->toArray(),'Existing data'); //Data output 2 - record contents
		$_record->synchronizeWithArray($data,true);//Synchronize
		ss_error::log($_record->toArray(),'Merged data'); //Data output 3 - resulting datac
		$_record->save(); //#ERROR#
		return $_record->toArray(true);
	}
Data output 1 - incoming, cleaned up data
array (
  'id' => '5',
  'active' => true,
  'timedpub' => true,
  'slug' => 'how_we_work',
  'date_pub' => NULL,
  'date_rej' => NULL,
  'UserCreate' => NULL,
  'UserModify' => NULL,
  'Category' => 
  array (
    0 => '10',
    1 => '28',
  ),
  'User' => 
  array (
  ),
  'Translation' => 
  array (
    'en' => 
    array (
      'headline' => 'How we work',
      'name' => 'how we work',
      'teaser' => NULL,
      'text' => '...',
      'lang' => 'en',
      'id' => '5',
    ),
    'sv' => 
    array (
      'headline' => 'Så arbetar vi',
      'name' => 'Så arbetar vi',
      'teaser' => '<br />',
      'text' => '...',
      'lang' => 'sv',
      'id' => '5',
    ),
  ),
  'user_create_id' => NULL,
  'user_modify_id' => NULL,
)
Data output 2 - record contents
array (
  'id' => '5',
  'active' => true,
  'timedpub' => true,
  'slug' => 'how_we_work',
  'date_pub' => NULL,
  'date_rej' => NULL,
  'user_create_id' => NULL,
  'user_modify_id' => NULL,
  'created_at' => '2009-11-12 01:41:36',
  'updated_at' => '2009-11-12 01:41:36',
  'Category' => 
  array (
    0 => 
    array (
      'id' => '10',
      'slug' => 'arbetsmetod',
      'path' => '/om_hamilton/arbetsmetod',
      'parent_id' => NULL,
      'owner_id' => '8',
      'public' => true,
      'created_at' => '2009-11-12 01:41:38',
      'updated_at' => '2009-11-12 01:41:38',
      'Translation' => 
      array (
        'sv' => 
        array (
          'id' => '10',
          'name' => 'arbetsmetod',
          'headline' => 'Vår arbetsmetod',
          'lang' => 'sv',
        ),
      ),
    ),
  ),
  'User' => 
  array (
  ),
  'Translation' => 
  array (
    'en' => 
    array (
      'id' => '5',
      'headline' => 'How we work',
      'name' => 'how we work',
      'teaser' => NULL,
      'text' => '...',
      'lang' => 'en',
    ),
    'sv' => 
    array (
      'id' => '5',
      'headline' => 'Så arbetar vi',
      'name' => 'Så arbetar vi',
      'teaser' => '<br />',
      'text' => '...',
      'lang' => 'sv',
    ),
  ),
)
Data output 3 - result after Synchronize
array (
  'id' => '5',
  'active' => true,
  'timedpub' => true,
  'slug' => 'how_we_work',
  'date_pub' => NULL,
  'date_rej' => NULL,
  'user_create_id' => NULL,
  'user_modify_id' => NULL,
  'created_at' => '2009-11-12 01:41:36',
  'updated_at' => '2009-11-12 01:41:36',
  'Category' => 
  array (
    0 => 
    array (
      'id' => '10',
      'slug' => 'arbetsmetod',
      'path' => '/om_hamilton/arbetsmetod',
      'parent_id' => NULL,
      'owner_id' => '8',
      'public' => true,
      'created_at' => '2009-11-12 01:41:38',
      'updated_at' => '2009-11-12 01:41:38',
      'Translation' => 
      array (
        'sv' => 
        array (
          'id' => '10',
          'name' => 'arbetsmetod',
          'headline' => 'Vår arbetsmetod',
          'lang' => 'sv',
        ),
      ),
    ),
    1 => 
    array (
      'id' => '28',
      'slug' => 'att_arbeta_hos_oss',
      'path' => '/karriar/arbeta_hos_oss',
      'parent_id' => NULL,
      'owner_id' => '21',
      'public' => true,
      'created_at' => '2009-11-12 01:41:39',
      'updated_at' => '2009-11-12 01:41:39',
    ),
  ),
  'User' => 
  array (
  ),
  'Translation' => 
  array (
    'en' => 
    array (
      'id' => '5',
      'headline' => 'How we work',
      'name' => 'how we work',
      'teaser' => NULL,
      'text' => '...',
      'lang' => 'en',
    ),
    'sv' => 
    array (
      'id' => '5',
      'headline' => 'Så arbetar vi',
      'name' => 'Så arbetar vi',
      'teaser' => '<br />',
      'text' => '...',
      'lang' => 'sv',
    ),
  ),
)
The error
'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5-10' for key 'PRIMARY''
Comment by Jonathan H. Wage [ 16/Nov/09 ]

Is it possible for you to make an actual test case? That way I can just execute it and troubleshoot the problem very fast.





[DC-140] _createLimitSubquery generates a query with error ORA-00904 Created: 26/Oct/09  Updated: 18/Nov/09  Resolved: 18/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Connection
Affects Version/s: 1.1.4
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Igor D'Astolfo Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None
Environment:

Oracle database 10 or higher



 Description   

If I create a limited query with Oracle sometimes it generates an invalid query, giving ORA-00904 errror.

A simple DQL query like this:
$q = Doctrine_Query::create()
->select("$i.nome, $s.denominazione")
->from("Istituto $i")
->leftJoin("$i.Sedi $s");

creates this intermediate sql:
SELECT
"i"."id" AS "i__id",
"i"."nome" AS "i__nome",
"s"."id" AS "s__id",
"s"."denominazione" AS "s__denominazione"
FROM "istituto" "i"
LEFT JOIN "sede" "s" ON "i"."id" = "s"."id_istituto"

that passed to Doctrine_Pager generates this query:
SELECT "i"."id" AS "i__id",
"i"."nome" AS "i__nome",
"s"."id" AS "s__id",
"s"."denominazione" AS "s__denominazione"
FROM "istituto" "i"
LEFT JOIN "sede"
"s" ON "i"."id" = "s"."id_istituto"

WHERE "i"."id" IN (SELECT a."id" FROM (SELECT DISTINCT "i2"."id" FROM "istituto" "i") a WHERE ROWNUM <= 10)

The problem is in "i2" referenced in the inner SELECT statement. This inner query hasn't been correctly parsed, while the column has been aliased, the table name has an incorrect alias.

The problem is that the injected sql code necessary to pagination has some space missing inside the parenthesis, thus the tokenizer misses to alias the table name.
Here it follows a simple patch:

--- Oracle.php  2009-10-26 17:04:17.000000000 +0100
+++ Oracle-orig.php     2009-09-25 19:12:10.000000000 +0200
@@ -103,13 +103,13 @@
                 $column = $column === null ? '*' : $this->quoteIdentifier($column);
                 if ($offset > 0) {
                     $min = $offset + 1;
+                    $query = 'SELECT b.'.$column.' FROM ( '.
+                                 'SELECT a.*, ROWNUM AS doctrine_rownum FROM ( '
+                                   . $query . ' ) a '.
+                              ' ) b '.
-                    $query = 'SELECT b.'.$column.' FROM ('.
-                                 'SELECT a.*, ROWNUM AS doctrine_rownum FROM ('
-                                   . $query . ') a '.
-                              ') b '.
                               'WHERE doctrine_rownum BETWEEN ' . $min .  ' AND ' . $max;
                 } else {
+                    $query = 'SELECT a.'.$column.' FROM ( ' . $query .' ) a WHERE ROWNUM <= ' . $max;
-                    $query = 'SELECT a.'.$column.' FROM (' . $query .') a WHERE ROWNUM <= ' . $max;
                 }
             }
         }


 Comments   
Comment by Jonathan H. Wage [ 02/Nov/09 ]

This is already fixed in all versions. 1.0, 1.1 and 1.2

Comment by Igor D'Astolfo [ 03/Nov/09 ]

Sorry, maybe I didn't explain well the bug, but it still exist in version 1.1.4 and I think also in 1.2.0
Maybe you were thinking to a previous bug that gave ORA-00904, but this is a different one.

It shows up only when using _createLimitSubquery, the same query without limit works fine. In the first post I described the problem, I traced the Doctrine_Query::parseSelect function, when it parses a query modified by the _createLimitSubquery it aliases the field name but not the table name in the inner subquery:

SELECT "i"."id" AS "i__id",
"i"."nome" AS "i__nome",
"s"."id" AS "s__id",
"s"."denominazione" AS "s__denominazione"
FROM "istituto" "i"
LEFT JOIN "sede"
"s" ON "i"."id" = "s"."id_istituto"
WHERE "i"."id" IN (SELECT a."id" FROM (SELECT DISTINCT "i2"."id" FROM "istituto" "i" ) a WHERE ROWNUM <= 10)

if you look in the subuery (the last line), you'll see that the field name is "i2"."id", but the table name is "i" (FROM "istituto" "i" means FROM "istituto" AS "i"), so Oracle gives error since the query references an "i2" table that isn't defined anywhere.

I also found why Doctrine_Query::parseSelect doesn't parse correctly the table alias, it's because the tokenization does not split the parenthesis after the table name, so the table name pass unchanged. My patch adds a blank after the end of the subquery in the Doctrine_Connection_Oracle::_createLimitSubquery, so the parenthesis does not interfere anymore with the parseSelect.

Regards

Comment by Jonathan H. Wage [ 03/Nov/09 ]

I understand, but what I am telling you is that your patch is already applied. When I look at the code in 1.0, 1.1, and 1.2. The changes you've made in your patch already exist. So someone must have fixed this bug already. Please make sure your Doctrine libs are up to date.

Comment by Igor D'Astolfo [ 06/Nov/09 ]

Sorry... I made a mistake with the diff, inverted the original and the patched version :|
I updated the patch, now you could apply it correctly.

Comment by Jonathan H. Wage [ 18/Nov/09 ]

Ok. Now I see the patch, but I don't understand. This is the SQL that is being passed to Oracle. Oracle has a problem without the extra spacing that you've added?

Comment by Jonathan H. Wage [ 18/Nov/09 ]

Ok. I understand now.





[DC-138] WHERE LIKE doesn't work on JOIN SELECTs. Created: 24/Oct/09  Updated: 18/Nov/09  Resolved: 18/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.0-ALPHA3
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Marcin Gil Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 1
Labels: None
Environment:

SVN 1.2 branch


Attachments: PNG File screen.png    

 Description   

WHERE LIKE doesn't work on JOIN SELECTs.
Params are not passed to the query.

Reproduce code:

	public function addSearchTerm(Doctrine_Query $query, array $fields)
	{
		$phrase = $this->getRequest()->getPost('query');
		if(!empty($phrase) && !empty($fields))
		{
			$phrase = '%'. $phrase .'%';
			$searchString = array();
			$searchParams = array();
			foreach($fields As $field)
			{
				$searchString[] = "$field LIKE ?";
				$searchParams[] = $phrase;
			} 
			$searchString = implode(' OR ', $searchString);
			
			$query->andWhere($searchString, $searchParams);
		}	
	}

$this->addSearchTerm($query, array(
			'username',
			'email',
			'last_login_ip',
			'Data.title',
			'Data.first_name',
			'Data.last_name',
			'Data.company',
			'Roles.name'
		));

Result attached as screenshot.

Query produced is correct, however '?' are not replaced with params from the array.

Error message is:
Doctrine_Connection_Mysql_Exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? OR `c8`.`email` LIKE ? OR `c8`.`last_login_ip` LIKE ? OR `c9`.`title` LIKE ? O' at line 1

Query passed to MySQL by Doctrine:
SELECT DISTINCT `c8`.`id` FROM `core_users` `c8` LEFT JOIN `core_users_data` `c9` ON `c8`.`id` = `c9`.`id_user` LEFT JOIN `core_users_roles` `c11` ON (`c8`.`id` = `c11`.`id_user`) LEFT JOIN `core_roles` `c10` ON `c10`.`id` = `c11`.`id_role` LEFT JOIN `core_users_roles` `c13` ON (`c8`.`id` = `c13`.`id_user`) LEFT JOIN `core_roles` `c12` ON `c12`.`id` = `c13`.`id_role` LEFT JOIN `core_users_roles` `c14` ON `c12`.`id` = `c14`.`id_role` WHERE (`c8`.`username` LIKE ? OR `c8`.`email` LIKE ? OR `c8`.`last_login_ip` LIKE ? OR `c9`.`title` LIKE ? OR `c9`.`first_name` LIKE ? OR `c9`.`last_name` LIKE ? OR `c9`.`company` LIKE ? OR `c10`.`name` LIKE ?) ORDER BY `c8`.`username` ASC, `c14`.`position` LIMIT 20



 Comments   
Comment by Jonathan H. Wage [ 16/Nov/09 ]

Something else is wrong, this is not a bug in Doctrine. The params are not replaced by Doctrine, they are simply passed to PDO and PDO replaces the values internally. If you have more information, i.e. a failing test case you can re-open the issue.

Comment by Marcin Gil [ 17/Nov/09 ]

More information:

Last working revision was 6394 - after this the functionality is broken.

Comment by Marcin Gil [ 17/Nov/09 ]

Last working revision was 6394 - after this the functionality is broken.

Comment by Marcin Gil [ 17/Nov/09 ]

I attach test case later.

Comment by Marcin Gil [ 17/Nov/09 ]

It seems this is the same problem as brought up here:
http://www.doctrine-project.org/jira/browse/DC-241

Comment by Jonathan H. Wage [ 17/Nov/09 ]

Are you sure that revision is right?

http://trac.doctrine-project.org/changeset/6394
http://trac.doctrine-project.org/changeset/6395

Those two revisions are unrelated to the code that is in question here. Take a look.

Comment by Jonathan H. Wage [ 17/Nov/09 ]

Can you try the patch in the comments here?

http://www.doctrine-project.org/jira/browse/DC-241

Comment by Maciej Hołyszko [ 17/Nov/09 ]

Actually, the revision with changed a lot in Doctrine_Query was that one: http://trac.doctrine-project.org/changeset/6396
But seems like that wasn't the one which causes the problem, sorry.
I will investigate further.

Comment by Jonathan H. Wage [ 18/Nov/09 ]

I think this should be fixed now





[DC-62] decimal fields are rounded to 6 decimal places on save() Created: 25/Sep/09  Updated: 14/Apr/10  Resolved: 18/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Record
Affects Version/s: 1.0.13, 1.1.4, 1.2.0-BETA2
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: brady Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None
Environment:

db engine: MySQL 5.0.75 and SQLite
php ini: precision = 12



 Description   

Decimal fields with high precision are rounded down to 6 decimal places prior to inserting into database on Doctrine_Record->save()

For example,
if you define a column to represent decimal numbers with 12 digits following the decimal:

MySet:
columns:
p_value:

{ type: decimal(18), scale: 12 }

Then create a new doctrine record object and save:

$record = new MySet();
$record['p_value'] = 5.012345678912;
$record->save();

The value in the database at this point has been rounded down to 6 decimal places:
value = 5.012346000000

print_r($record->toArray()) shows the correct value to 12 decimal places



 Comments   
Comment by brady [ 12/Nov/09 ]

I found that retrieving from the database gives the correct precision, so the rounding problem only occurs on saving. I've tested doctrine versions 1.0.13 (through symfony), 1.1.4, and 1.2 alpha2 - all have this problem.

//require_once('lib/vendor/Doctrine-1.2.0/lib/Doctrine.php');
require_once('lib/vendor/Doctrine-1.1.4/Doctrine.php');
spl_autoload_register(array('Doctrine', 'autoload'));

class TestRecord extends Doctrine_Record
{
public function setTableDefinition()

{ $this->setTableName('test'); $this->hasColumn('id', 'integer', null, array( 'type' => 'integer', 'notnull' => false, 'primary' => true, 'autoincrement' => true, )); $this->hasColumn('test_field', 'decimal', 18, array( 'scale' => 12, )); }

}

$pdo = new PDO('sqlite:testdb.db');
//$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'root', 'pass');
$conn = Doctrine_Manager::connection($pdo);

// test retreival of value (first row has 12.891841075016 inserted manually)
$q = Doctrine_Query::create()->from('TestRecord')
->where('id = ?', 1);
$record = $q->fetchOne();
echo 'retrieved value = ', $record['test_field'], "\n";
// outputs 12.891841075016 (OK)

// test save of high precision decimal
$test = new TestRecord();
$test['test_field'] = 12.891841075016;
$test->save();
// database shows 12.891841000000 (rounded incorrectly)

Comment by Jonathan H. Wage [ 18/Nov/09 ]

This is a problem with mysql. In Doctrine decimal won't store that value properly. If you change it to float, it works properly. I had to make a change in Doctrine so that Doctrine float maps to mysql float, double to double and decimal to decimal. Update Doctrine 1.2 from SVN and change your schema type to be 'float' instead of 'decimal'. It should work properly then.

Comment by Jörg Eichhorn [ 14/Apr/10 ]

Seems another issue is generated by the fix to this ticket. For a schema like:

Data:
columns:
id:
type: integer(5)
primary: true
value:
type: double

and a MySQL database the generated column type for value is DOUBLE(18,2), which obviously isn't what was intended. Actually it means that storing values like "1.234567" or "12 345 678 901 234 567 890" doesn't work like one expects when working with double precision floats.

Maybe I oversee something but I think there isn't a way to just use plain doubles.

A possible fix could be to check for a explicitly set scale or length (line 233 and 237 of DataDict/Mysql.php):

if (!empty($field['length']) || !empty($field['scale']))

{ $length = !empty($field['length']) ? $field['length'] : 18; $scale = !empty($field['scale']) ? $field['scale'] : $this->conn->getAttribute(Doctrine_Core::ATTR_DECIMAL_PLACES); return 'DOUBLE('.$length.', '.$scale.')'; }

else

{ return 'DOUBLE'; }

This way there is a fallback to the version before rev 6750.





[DC-37] Performing JOIN and ORDERBY and using the PAGER in MSSQL results in bad query Created: 17/Sep/09  Updated: 23/Nov/09  Resolved: 23/Nov/09

Status: Closed
Project: Doctrine 1
Component/s: Pager, Query, Relations
Affects Version/s: 1.1.4
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Daniel Cousineau Assignee: Guilherme Blanco
Resolution: Fixed Votes: 1
Labels: None
Environment:

PHP 5.2.9-2, Apache, Microsoft SQL Server 2005, Windows XP SP 3



 Description   

When adding an ORDER BY and a JOIN to a query when using MSSQL as the back end, an incomplete query is produced with duplicated ORDER BY sections.

$query = Doctrine_Query::create()
            ->from('User u')
            ->leftJoin('u.Roles')
            ->addOrderBy('u.lastname, u.firstname');

$pager = new Doctrine_Pager($query, $page, $per_page);

return array(
    'pager' => $pager,
    'results' => $pager->execute(),
);

Produces

SELECT 
	[u].[id] AS [u__id], 
	[u].[username] AS [u__username], 
	[u].[password] AS [u__password], 
	[u].[firstname] AS [u__firstname], 
	[u].[lastname] AS [u__lastname], 
	[u].[email] AS [u__email], 
	[u].[phone] AS [u__phone], 
	[u].[data] AS [u__data], 
	[u].[created_at] AS [u__created_at], 
	[u].[updated_at] AS [u__updated_at], 
	[u].[deleted_at] AS [u__deleted_at], 
	[r].[id] AS [r__id], 
	[r].[name] AS [r__name] 
FROM 
	[users] [u] 
	LEFT JOIN [user_has_role] [u2] ON ([u].[id] = [u2].[user_id]) 
	LEFT JOIN [role] [r] ON [r].[id] = [u2].[role_id] 
WHERE 
	[u].[id] IN (
		SELECT * FROM (
			SELECT TOP 1 * FROM (
				SELECT 
					DISTINCT TOP 1 [u].[id] 
				FROM 
					[users] [u] 
					LEFT JOIN [user_has_role] [u2] ON ([u].[id] = [u2].[user_id]
			) 
			LEFT JOIN [role] [r] ON [r].[id] = [u2].[role_id] 
			ORDER BY 
				[u].[lastname], [u].[firstname]
		) AS [inner_tbl] 
		ORDER BY 
			[inner_tbl].[ DESC, [inner_tbl].[ DESC
	) AS [outer_tbl] 
ORDER BY 
	[outer_tbl].[ asc, [outer_tbl].[ asc) 
ORDER BY 
	[u].[lastname], [u].[firstname]

Particularly, notice in the first ORDER BY blocks the incomplete clauses (e.g. [outer_tbl].[ )

*SCHEMA*

Role:
  columns:
    id:
      primary: true
      autoincrement: true
      type: integer
      notnull: true
    name:
      type: string(100)
      notnull: true
  relations:
    Users:
      foreignAlias: Roles
      class: User
      refClass: UserHasRole

UserHasRole:
  columns:
    user_id:
      type: integer
      primary: true
      notnull: true
    role_id:
      type: integer
      primary: true
      notnull: true
  relations:
    User:
      local: user_id
      foreign: id
    Role:
      local: role_id
      foreign: id

User:
  tableName: users
  actAs: [Timestampable, Softdelete]
  columns:
    id:
      primary: true
      autoincrement: true
      type: integer
      notnull: true
    username:
      type: string(255)
      notnull: true
    password:
      type: string(255)
      notnull: true
    firstname:
      type: string(255)
    lastname:
      type: string(255)
    email:
      type: string(255)
      email: true
    phone:
      type: string(20)
    data:
      type: string
  indexes:
    user_index:
      fields: [username]
      type: unique

I will be digging around to find out what the problem is later, and start playing with the 1.2 branch to see if the problem exists there. If someone could help me and point me in a good direction to start looking that would be great



 Comments   
Comment by Daniel Cousineau [ 17/Sep/09 ]

CORRECTION: Notice it was only when using the pager.

Comment by Daniel Cousineau [ 17/Sep/09 ]

Possibly related to http://www.doctrine-project.org/jira/browse/DC-36

Comment by Jonathan H. Wage [ 17/Sep/09 ]

Are you using the latest 1.1.3?

Comment by Felix-Johannes Jendrusch [ 20/Sep/09 ]

Seems like I'm having the same issue using MSSQL, (Doctrine 1.1.3, Doctrine 1.2-ALPHA1), Zend_Paginator_Adapter_Doctrine and Doctrine::LIMIT_RECORDS (works with Doctrine::LIMIT_ROWS):

SELECT
	[c].[clientarchivefileid] AS [c__clientarchivefileid],
	[c].[filename] AS [c__filename],
	[c].[thumbnailimagename] AS [c__thumbnailimagename],
	[c].[archivedate] AS [c__archivedate],
	[c2].[identifier] AS [c2__identifier],
	[c2].[archive] AS [c2__archive],
	[c2].[trash] AS [c2__trash],
	[c2].[name] AS [c2__name],
	[f].[filetypeid] AS [f__filetypeid],
	[f].[fileextension] AS [f__fileextension],
	[c3].[clientarchivevideoserverid] AS [c3__clientarchivevideoserverid],
	[c4].[identifier] AS [c4__identifier],
	[c4].[url] AS [c4__url],
	[c5].[videoserverid] AS [c5__videoserverid],
	[c5].[videoservername] AS [c5__videoservername],
	[l].[identifier] AS [l__identifier],
	[l].[name] AS [l__name],
	[t].[identifier] AS [t__identifier],
	[t].[name] AS [t__name]
FROM [ClientArchiveFile] [c]
LEFT JOIN [ClientArchiveFileExtension] [c2]
	ON [c].[clientarchivefileid] = [c2].[identifier]
LEFT JOIN [FileType] [f]
	ON [c].[filetypeid] = [f].[filetypeid]
LEFT JOIN [ClientArchiveVideoServer] [c3]
	ON [c].[clientarchivevideoserverid] = [c3].[clientarchivevideoserverid]
LEFT JOIN [ClientArchiveVideoServerExtension] [c4]
	ON [c3].[clientarchivevideoserverid] = [c4].[identifier]
LEFT JOIN [ClientVideoServer] [c5]
	ON [c3].[videoserverid] = [c5].[videoserverid]
LEFT JOIN [ClientArchiveFileLabel] [c6]
	ON ([c].[clientarchivefileid] = [c6].[fileidentifier])
LEFT JOIN [Label] [l]
	ON [l].[identifier] = [c6].[labelidentifier]
LEFT JOIN [TerminalFile] [t2]
	ON ([c].[clientarchivefileid] = [t2].[fileidentifier])
LEFT JOIN [Terminal] [t]
	ON [t].[identifier] = [t2].[terminalidentifier]
WHERE
	[c].[clientarchivefileid] IN (
		SELECT
			*
		FROM (
			SELECT TOP 10
				*
			FROM (
				SELECT DISTINCT TOP 10
					[c].[clientarchivefileid]
				FROM [ClientArchiveFile] [c]
				LEFT JOIN [ClientArchiveFileExtension] [c2]
					ON [c].[clientarchivefileid] = [c2].[identifier]
				LEFT JOIN [FileType] [f]
					ON [c].[filetypeid] = [f].[filetypeid]
				LEFT JOIN [ClientArchiveVideoServer] [c3]
					ON [c].[clientarchivevideoserverid] = [c3].[clientarchivevideoserverid]
				LEFT JOIN [ClientArchiveVideoServerExtension] [c4]
					ON [c3].[clientarchivevideoserverid] = [c4].[identifier]
				LEFT JOIN [ClientVideoServer] [c5]
					ON [c3].[videoserverid] = [c5].[videoserverid]
				LEFT JOIN [ClientArchiveFileLabel] [c6]
					ON ([c].[clientarchivefileid] = [c6].[fileidentifier])
				LEFT JOIN [Label] [l]
					ON [l].[identifier] = [c6].[labelidentifier]
				LEFT JOIN [TerminalFile] [t2]
					ON ([c].[clientarchivefileid] = [t2].[fileidentifier])
				LEFT JOIN [Terminal] [t]
					ON [t].[identifier] = [t2].[terminalidentifier]
				WHERE
					[c].[filetypeid] = 1
					AND (
						[c2].[identifier] = NULL
						OR (
							[c2].[archive] = 0
							AND [c2].[trash] = 0
						)
					)
				ORDER BY
					[c].[archivedate] DESC,
					[l].[name] ASC
			) AS [inner_tbl]
			ORDER BY
				[inner_tbl].[ ASC,
				[inner_tbl].[ DESC
		) AS [outer_tbl]
		ORDER BY
			[outer_tbl].[ desc,
			[outer_tbl].[ asc
	)
	AND [c].[filetypeid] = 1
	AND (
		[c2].[identifier] = NULL 
		OR (
			[c2].[archive] = 0
			AND [c2].[trash] = 0
		)
	)
ORDER BY
	[c].[archivedate] DESC,
	[l].[name] ASC

Probably a bug in Doctrine_Connection_Mssql::modifyLimitQuery()?





Generated at Wed Apr 23 11:11:45 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.