[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-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-134] Doctrine\DBAL\Connection::query() no logging Created: 24/Jun/11  Updated: 28/Jun/11  Resolved: 28/Jun/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.6
Fix Version/s: 2.0.7, 2.1

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


 Description   

Doctrine\DBAL\Connection::query() does not use SqlLogger.

I guess it may be fixed like this:

    public function query()
    {
        $this->connect();

        $args = func_get_args();

        $logger = $this->getConfiguration()->getSQLLogger();
        if ($logger) {
            $logger->startQuery($args[0]);
        }

        $statement = call_user_func_array(array($this->_conn, 'query'), $args);

        if ($logger) {
            $logger->stopQuery();
        }

        return $statement;
    }


 Comments   
Comment by Benjamin Eberlei [ 28/Jun/11 ]

Fixed





[DBAL-126] schema-tool creates primary keys as unique constraints Created: 09/Jun/11  Updated: 26/Jun/11  Resolved: 26/Jun/11

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

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


 Description   

The schema tool calls AbstractPlatform::getCreateIndexSQL() to create new indexes. When the index is primary, this creates a unique key instead.



 Comments   
Comment by Karsten Dambekalns [ 25/Jun/11 ]

While the description here is very sparse, I think it is about the following problem.

When a primary key is to be created for MySQL, the statement generated is

ADD UNIQUE INDEX PRIMARY ON foo (bar)

This fails with MySQL stating primary is an invalid name. Since "ADD INDEX" is mapped to "ALTER TABLE" anyway, I just made that

ALTER TABLE foo ADD PRIMARY KEY (bar)

which works fine. Also

DROP INDEX primary ON foo

doesn't work, unless I quote primary (since it is a reserved word), but there

ALTER TABLE foo DROP PRIMARY KEY

seems better as well.

Comment by Benjamin Eberlei [ 26/Jun/11 ]

Fixed





Generated at Mon Apr 21 12:30:42 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.