[DBAL-132] wrong mysql dump generation for foreign keys Created: 22/Jun/11  Updated: 31/Jul/11  Resolved: 31/Jul/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.5
Fix Version/s: 2.0.7, 2.1.1

Type: Bug Priority: Major
Reporter: Stefan Zerkalica Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: None
Environment:

PHP 5.3.3-7+squeeze1 with Suhosin-Patch, Debian GNU/Linux 6.0.1 (squeeze)



 Description   

Problem in methods Doctrine\DBAL\Platforms\AbstractPlatform::getDropForeignKeySQL() and Doctrine\DBAL\Platforms\AbstractPlatform::getCreateForeignKeySQL()

This methods do not receive FK name and don't put it in sql dump. While executing dump, mysql autogenerate FK names, but at the migration generation moment doctrine knowns nothing about them.

I think, doctrine must generate this names and put it into the schema.

Autogenerated migrations in mysql with fk are unusable.

Doctrine migrations:migrate in current doctrine version:
up:
ALTER TABLE Catalog_Field ADD CONSTRAINT FOREIGN KEY (groupId) REFERENCES Catalog_FieldGroup(id)
down:
ALTER TABLE Catalog_Field DROP FOREIGN KEY

should be:
up:
ALTER TABLE Catalog_Field ADD CONSTRAINT Catalog_Field_ibfk_2 FOREIGN KEY (groupId) REFERENCES Catalog_FieldGroup(id)
down:
ALTER TABLE Catalog_Field DROP FOREIGN KEY catalog_Field_ibfk_2

see: https://github.com/doctrine/migrations/issues/32

https://github.com/doctrine/migrations/issues/35



 Comments   
Comment by Benjamin Eberlei [ 31/Jul/11 ]

Fixed





[DBAL-140] Problem with null-length string comparison in Schema Created: 31/Jul/11  Updated: 31/Jul/11  Resolved: 31/Jul/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.1
Fix Version/s: 2.1.1

Type: Bug Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

If you compare a column with a string type and length of null to its database value with the default length then Comparator reports a difference. It should automatically convert NULL to 255 (default for all platforms).



 Comments   
Comment by Benjamin Eberlei [ 31/Jul/11 ]

Fixed.





[DBAL-82] orderBy(), setFirstResult() bug with MSSQL Server Created: 16/Jan/11  Updated: 31/Jul/11  Resolved: 31/Jul/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0
Fix Version/s: 2.0.7, 2.1.1

Type: Bug Priority: Major
Reporter: Aaron DM Assignee: Juozas Kaziukenas
Resolution: Fixed Votes: 1
Labels: None
Environment:

Windows 7, Apache2, PHP 5.3.5, Microsoft SQL Server 2008



 Description   
QueryBuilder
		$query = $this->createQueryBuilder('account')
					  ->select('account')
					  ->orderBy('account.id', 'DESC')
					  ->getQuery();

                $result = $query->setMaxResults($this->resultsPerPage+1)
        							->setFirstResult($this->offset)
        							->getResult();

The above, when using "setFirstResult" and "orderBy" (like that), results in an error like so:

SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]The multi-part identifier "t0_.id" could not be bound.

With a statement that looks something like so:

WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY t0_.id DESC) AS "doctrine_rownum",   ...

The reason the error is occurring is because you apparently need to use the "alias" (e.g, SELECT t0_.id AS id0) "id0" in the "ORDER BY" clause.

So query will run with no problems if t0_.id as id0

WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY id0 DESC) AS "doctrine_rownum",   ...

It looks like this behavior might only occur with Microsoft SQL Server, but it is a bug.

---------------

If you go into:
Doctrine\DBAL\Platforms\MsSqlPlatform

Find:

$over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby);

Add before:

                    # Get Columns
                    $columns = array();
                    if(preg_match_all('/([a-zA-Z][0-9]+_\.[a-zA-Z0-9\-_]+)\sAS\s([a-zA-Z0-9\-\_]+)/', $query, $matched)) {
                        for($i=0; $i<count($matched[1]); ++$i)
                        {
                            $columns[$matched[1][$i]] = $matched[2][$i];
                        }
                    }
                    
                    # Replace columns with their alias in the "orderby" statement
                    if(preg_match_all('/([a-zA-Z][0-9]+_\.[a-zA-Z0-9\-_]+)\s/i', $orderby, $matches)) {
                        foreach($matches[1] as $column) 
                        {
                            $orderby = preg_replace('/'.$column.'/', $columns[$column], $orderby);
                        }
                    }

Obviously this is a really ugly hack, but this resolves it.



 Comments   
Comment by Aaron DM [ 16/Jan/11 ]

Added the ugly hack fix.

Comment by Benjamin Eberlei [ 23/Jan/11 ]

Assigned to Juozas.

Comment by Minxuan GUO [ 05/May/11 ]

Thanks Aaron DM

Your code works perfectly

Comment by Jean-Marc Fontaine [ 20/Jun/11 ]

I am experiencing this bug too. Is there a way to get it fixed anytime soon? Maybe in the 2.1 release.

Comment by Aaron DM [ 24/Jun/11 ]

Here is what I think, a proper fix for this.

I re-wrote the query however it works the same and is pretty much the exact same in performance (from couple of tests I've done looking at profiler) + it fixes the issues regarding this ticket.

    /**
     * Adds an adapter-specific LIMIT clause to the SELECT statement.
     *
     * @param string $query
     * @param mixed $limit
     * @param mixed $offset
     * @link http://lists.bestpractical.com/pipermail/rt-devel/2005-June/007339.html
     * @return string
     */
    protected function doModifyLimitQuery($query, $limit, $offset = null)
    {
        if ($limit > 0) {
            $count = intval($limit);
            $offset = intval($offset);

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

            if ($offset == 0) {
                $query = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . $count . ' ', $query);
            } else {
                $orderby = stristr($query, 'ORDER BY');

                if (!$orderby) {
                    $over = 'ORDER BY (SELECT 0)';
                } else {
                    $over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby);
                }

                // Remove ORDER BY clause from $query
                $query = preg_replace('/\s+ORDER BY(.*)/', '', $query);
                $query = preg_replace('/SELECT\s/', '', $query);

                $start = $offset + 1;
                $end = $offset + $count;

                // Limit query
                $query = "SELECT * FROM (SELECT ROW_NUMBER() OVER ($over) AS \"doctrine_rownum\", $query) AS doctrine_tbl WHERE \"doctrine_rownum\" BETWEEN $start AND $end";
            }
        }

        return $query;
    }

I had found this query a long time ago and found it to be fairly fast, however I'm not sure where exactly I found it.
I'm going to take a guess and assume it might be this comment:
http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/comment-page-1/#comment-28594

Comment by Aaron DM [ 24/Jun/11 ]

Pull request: https://github.com/doctrine/dbal/pull/37

Comment by Benjamin Eberlei [ 31/Jul/11 ]

Fixed, merged pull request





[DBAL-137] Setting empty host in PostgreSQL prevents connection to socket Created: 24/Jul/11  Updated: 24/Jul/11  Resolved: 24/Jul/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.1
Fix Version/s: 2.1.1

Type: Bug Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

Reported on https://github.com/symfony/symfony/issues/1788#issuecomment-1640104






Generated at Thu Oct 30 16:47:41 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.