[DBAL-863] [GH-564] [DBAL-630] Incorrect PostgreSQL boolean handling Created: 04/Apr/14  Updated: 07/Apr/14

Status: Awaiting Feedback
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.4
Fix Version/s: None
Security Level: All

Type: Bug Priority: Critical
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: boolean, dbal, orm, postgresql


 Description   

This issue is created automatically through a Github pull request on behalf of davividal:

Url: https://github.com/doctrine/dbal/pull/564

Message:

Working on PostgreSQL incorrect boolean handling when emulating prepared statements



 Comments   
Comment by Davi Koscianski Vidal [ 04/Apr/14 ]

This PR is related to http://www.doctrine-project.org/jira/browse/DBAL-630

Comment by Davi Koscianski Vidal [ 04/Apr/14 ]

The only issue with this PR is that it breaks Doctrine2 ORM. I already have a patch for that too, but I'm not sure on how to proceed.

Comment by Davi Koscianski Vidal [ 04/Apr/14 ]

I'm not breaking ORM anymore.





[DBAL-859] OraclePlatform: rownum should not be used directly in WHERE clausule Created: 12/Feb/14  Updated: 01/Apr/14

Status: Open
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.4.1
Fix Version/s: None
Security Level: All

Type: Bug Priority: Critical
Reporter: Mariusz Jaskółka Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Oracle, All OSes.


Attachments: File OraclePlatform.php     File OraclePlatform_bugfix.php    

 Description   

At 90% of cases when we use ROWNUM in WHERE clause it will work correctly, but sometimes not. I noticed that that is why Doctrine sometimes works incorrect.
Source:
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

Quote:
"That is why a query in the following form is almost certainly an error:

select *
from emp
where ROWNUM <= 5
order by sal desc;
"

I prepared modified OraclePlatform.php with solution (attachment) - rownum is being compared after all operations.



 Comments   
Comment by Steve Müller [ 01/Apr/14 ]

Mariusz Jaskółka can you please provide an example where the current implementation fails? We have functional tests LIMIT queries in DBAL but they run fine on Oracle. I need more information to be able to reproduce this problem.





[DBAL-774] DBAL parses joins in wrong order Created: 08/Jan/14  Updated: 21/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.4, 2.5, 2.4.1, 2.4.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Critical
Reporter: Jeroen Thora Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: querybuilder


 Description   

I have a problem that doctrine dbal orders the joins in a wrong order if you use more complex join combinations (worked fine in DBAL 2.3)

Dbal Querybuilder:

        $qb->select('tbl_profile_additional_property.pkid AS pkid')
        ->from('tbl_profile_additional_property', 'tbl_profile_additional_property')
        ->leftjoin('tbl_profile_additional_property', 'tbl_rating_system', 'tbl_rating_system', 'tbl_profile_additional_property.fk_rs = tbl_rating_system.pkid')
        ->leftjoin('tbl_rating_system', 'tbl_rating_system_translation', 'tbl_rating_system_translation', 'tbl_rating_system_translation.fk_rs = tbl_rating_system.pkid AND tbl_rating_system_translation.fk_language = :languageid')
        ->leftjoin('tbl_profile_additional_property', 'tbl_score_level', 'tbl_score_level', 'tbl_profile_additional_property.fk_scoregoal = tbl_score_level.pkid')
        ->leftjoin('tbl_rating_system_translation', 'tbl_score_level_translation', 'tbl_score_level_translation', 'tbl_score_level_translation.fk_rs_translation = tbl_rating_system_translation.pkid AND tbl_score_level_translation.fk_sl = tbl_score_level.pkid')
        ->where('tbl_profile_additional_property.fk_function = :functionid')
        ->setParameter('functionid', $functionId)
        ->setParameter('languageid', $languageId);

Expected Query:

SELECT 
tbl_profile_additional_property.pkid AS pkid 
FROM tbl_profile_additional_property tbl_profile_additional_property 
LEFT JOIN tbl_rating_system tbl_rating_system ON tbl_profile_additional_property.fk_rs = tbl_rating_system.pkid 
LEFT JOIN tbl_rating_system_translation tbl_rating_system_translation ON tbl_rating_system_translation.fk_rs = tbl_rating_system.pkid AND tbl_rating_system_translation.fk_language = :languageid 
LEFT JOIN tbl_score_level tbl_score_level ON tbl_profile_additional_property.fk_scoregoal = tbl_score_level.pkid 
LEFT JOIN tbl_score_level_translation tbl_score_level_translation ON tbl_score_level_translation.fk_rs_translation = tbl_rating_system_translation.pkid AND tbl_score_level_translation.fk_sl = tbl_score_level.pkid 
WHERE tbl_profile_additional_property.fk_function = :functionid

Resulted Query:

SELECT 
tbl_profile_additional_property.pkid AS pkid 
FROM tbl_profile_additional_property tbl_profile_additional_property 
LEFT JOIN tbl_rating_system tbl_rating_system ON tbl_profile_additional_property.fk_rs = tbl_rating_system.pkid 
LEFT JOIN tbl_rating_system_translation tbl_rating_system_translation ON tbl_rating_system_translation.fk_rs = tbl_rating_system.pkid AND tbl_rating_system_translation.fk_language = :languageid 
LEFT JOIN tbl_score_level_translation tbl_score_level_translation ON tbl_score_level_translation.fk_rs_translation = tbl_rating_system_translation.pkid AND tbl_score_level_translation.fk_sl = tbl_score_level.pkid 
LEFT JOIN tbl_score_level tbl_score_level ON tbl_profile_additional_property.fk_scoregoal = tbl_score_level.pkid 
WHERE tbl_profile_additional_property.fk_function = :functionid

(The last 2 LEFT JOINS of the query are the problem)

The problem is with getSQLForJoins it loops over all the joins and foreach join it follows all the used joins aliases until the deepest point. Therefor it will parse this join first

->leftjoin('tbl_rating_system_translation', 'tbl_score_level_translation', 'tbl_score_level_translation', 'tbl_score_level_translation.fk_rs_translation = tbl_rating_system_translation.pkid AND tbl_score_level_translation.fk_sl = tbl_score_level.pkid')

Before it generates the sql for this line (this line is needed becaus the line above needs a join on tbl_score_level first)

->leftjoin('tbl_profile_additional_property', 'tbl_score_level', 'tbl_score_level', 'tbl_profile_additional_property.fk_scoregoal = tbl_score_level.pkid')

The order the querybuilder in php is build (select, from, joins, etc) is the order it should be parsed as sql.

Ps. I have added 2.5 also as affectsversion because the code didn't change as far is i know



 Comments   
Comment by Chesley Brown [ 22/Jan/14 ]

Noticing this issue with v2.4 as well. However, I'm also noticing the leftJoins being ordered incorrectly on v2.3.3 as well... however the ordering between the two versions are not the same. They are both just ordered differently than the order that I actually call the leftJoin methods in.

Comment by Jeroen Thora [ 21/Mar/14 ]

I have added a failing test for this problem in doctrine/dbal#548





[DBAL-630] Incorrect PostgreSQL boolean handling Created: 14/Oct/13  Updated: 04/Apr/14

Status: Open
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.4
Fix Version/s: None
Security Level: All

Type: Bug Priority: Critical
Reporter: Stan Imbt Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This is a follow-up to issue #457 ("Use int values instead of strings for PostgreSQL booleans"), which is still not fixed. Int values are no solution at all. In fact the root cause lies deeper, outside the PostgreSQL platform class.

1. The patch to fix #457 does not change the default behaviour of the PostgreSQL platform class (method convertBooleans() returns strings 'true'/'false'). When the PostgreSQL PDO driver is configured to emulate prepared statements, it still results in unexpected failures, storing boolean false entity values as true in the database.

2. The new alternative boolean conversion mode activated by PostgreSqlPlatform::setUseBooleanTrueFalseStrings(false) is of no use as it prevents execution of DQL queries with boolean conditions, because integers 0 and 1 are not valid boolean literals in PostgreSQL.

The root cause is the notion of a PHP value being convertible to a database value. Because in fact there are two different types of "database values":

  • Literals used directly in SQL statements
  • Values passed as parameters to prepared statements

To make this absolutely clear:

Prerequisites
$pdo = new PDO(...);
$pdo->exec('CREATE TABLE my_table(bool_col BOOLEAN NOT NULL)');
$stmt = $pdo->prepare('INSERT INTO my_table(bool_col) VALUES(?)');
Using string 'false'
$value = 'false';

// This works, using the SQL literal false
$pdo->exec('INSERT INTO my_table(bool_col) VALUES(' . $value . ')');

// This works, too. But it's remarkable that Postgres accepts the string 'false'
// as a boolean value. Compare this to the string 'NULL' in an SQL statement vs.
// 'NULL' as a prepared statement param (instead of PHP null).
$stmt->bindValue(1, $value, PDO::PARAM_BOOL);
$stmt->execute();

// Sets bool_col to true! The PostgreSQL PDO driver correctly expects a boolean
// and (string)'false' yields true.
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$stmt->bindValue(1, $value, PDO::PARAM_BOOL);
$stmt->execute();
Using boolean false
$value = false;

// This will obviously fail, because false is cast to an empty string, resulting
// in "... VALUES()".
$pdo->exec('INSERT INTO my_table(bool_col) VALUES(' . $value . ')');

// Works
$stmt->bindValue(1, $value, PDO::PARAM_BOOL);
$stmt->execute();

// Works, too
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$stmt->bindValue(1, $value, PDO::PARAM_BOOL);
$stmt->execute();
Using integer 0
$value = 0;

// Causes 'ERROR: column "bool_col" is of type boolean but expression is of type integer'
$pdo->exec('INSERT INTO my_table(bool_col) VALUES(' . $value . ')');

// Works
$stmt->bindValue(1, $value, PDO::PARAM_BOOL);
$stmt->execute();

// Works, because of implicit PHP type cast 0 -> false
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$stmt->bindValue(1, $value, PDO::PARAM_BOOL);
$stmt->execute();

There are two locations in DBAL and ORM where AbstractPlatform::convertBooleans() is called to build SQL literals:

DoctrineDBALPlatformsAbstractPlatform::getDefaultValueDeclarationSQL()
$default = " DEFAULT '" . $this->convertBooleans($field['default']) . "'";

Wow, this is even being enclosed in single quotes!? But then the whole method is buggy anyway, e.g. using an unescaped string value for a string literal (scenarios for SQL injection unlikely but possible).

DoctrineORMQuerySqlWalker::walkLiteral()
case AST\Literal::BOOLEAN:
	$bool = strtolower($literal->value) == 'true' ? true : false;
	$boolVal = $this->conn->getDatabasePlatform()->convertBooleans($bool);
	return $boolVal;

...and the result is later used as a boolean literal in an SQL query.

To solve this we need something like AbstractPlatform::convertBoolToSqlLiteral() (returning strings true and false for the Postgres platform) and AbstractPlatform::convertBoolToDbValue() (converting to integer 0 or 1 for platforms without a native bool type).

Note 1: The docs currently suggest to call $conn->getDatabasePlatform()->setUseBooleanTrueFalseStrings($flag). This is bad OO design, because getDatabasePlatform() returns an AbstracPlattform instance which does not have a contract for the method.

Note 2: What makes this problem so nasty is the fact that switching to emulated prepares makes an application fail in a non-obvious way. There will be no traceable errors but simply all boolean false values in ORM entities stored as boolean true. When integration tests use a different database (e.g. an SQLite in-memory DB to minimize test execution time) the problem will even escape the tests. And the distance between cause and effect also makes the problem very hard to find. Who would expect a database driver setting to cause booleans in the DB to be the opposite of what they're supposed to be? Especially as this only becomes apparent after later re-hydrating stored entities.

Note 3: Why emulated prepared statements matter: When PostgreSQL processes a prepared statement, its query planner works out a query plan and uses it for all subsequent executions of this query. This way it has to make a rather crude guess at the number of affected rows from each table in a join. When a non-prepared query is executed, the query planner can take into account the given values (mostly the ones in the "WHERE" part of the query) and make a much more specific guess at which plan will perform best.
In our case, we decided to switch to emulated prepares after we found out that a complex query in our application would run five times faster with emulated prepares.

Note 4: Is there a reason for AbstractPlatform::convertBooleans() accepting either a single bool value or an array of bool values? I didn't find client code calling it with an array. This makes the method less obvious, is currently implemented with code duplication and at least for the PostgreSQL plattform class, the "array of bool" functionality is not even tested.



 Comments   
Comment by Benjamin Eberlei [ 01/Jan/14 ]

PDO::ATTR_EMULATE_PREPARES finally explains why I was unable to reproduce this before. This is obviously a very critical error. Increasing priority.

Comment by Benjamin Eberlei [ 01/Jan/14 ]

Stan Imbt I cannot really reproduce the ATTR_EMULATE_PREPARES issue, see https://github.com/doctrine/dbal/commit/f29f0fae8479955911928888ebab07ccd4e8ab0c

I agree that we need two methods on the Platform for casting values to SqlLiterals and to Params, as they are in two different contexts.

Comment by Benjamin Eberlei [ 01/Jan/14 ]

We have a reproduce-case, it fails on Travis: https://travis-ci.org/doctrine/dbal/jobs/16217622

Comment by Stan Imbt [ 02/Jan/14 ]

Thanks for looking into this, Benjamin.
We could reproduce the problem with different PHP 5.4 builds on both Linux and Windows (Postgres version shouldn't matter as emulated prepares are handled in the Postgres PDO driver). Travis runs the tests on PHP 5.3 and also fails as expected. Are you using PHP 5.5? If so, I assume the PHP folks have recently changed the PG PDO driver's behaviour, making it act more like PG itself (i.e. converting a bool param with string value 'false' to boolean false).

Comment by Davi Koscianski Vidal [ 31/Mar/14 ]

I'm using PHP 5.5.3 + PostgreSQL 9.3.3 and I confirm that this is not working.

But it looks like it won't work for anyone because PHP: https://bugs.php.net/bug.php?id=57157.

Comment by Stan Imbt [ 01/Apr/14 ]

Davi, I can reproduce the referenced PHP bug, both with and without emulated prepares, but it is not related to this Doctrine bug.

Doctrine calls PDOStatement::bindValue(), specifying the data type PDO::PARAM_BOOL in case of bool fields. The PHP bug only occurs when the param data type is not provided, apparently converting the param value to string ((string)false === '') and passing that on to the DBMS.

Comment by Davi Koscianski Vidal [ 01/Apr/14 ]

Stan, almost the same 'test case' as PHP bug:

<?php

/*
CREATE TABLE "booleantest" (
  "persistence_object_identifier" serial NOT NULL,
  "hidden" boolean NOT NULL
);
 */

$handle = new PDO('pgsql:host=127.0.0.1 dbname=postgres', 'postgres', 'postgres');
$handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$handle->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$handle->setAttribute(PDO::PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT, true);

$statement = $handle->prepare('INSERT INTO booleantest (hidden) VALUES (?)');

// works as expected
$statement->execute(array(true));
echo 'TRUE has been inserted' . PHP_EOL;

$statement->bindValue(1, "true", PDO::PARAM_BOOL);
echo 'TRUE has been inserted' . PHP_EOL;

// dies with
// PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type boolean: ""
// $statement->execute(array(FALSE));

$statement->bindValue(1, "false", PDO::PARAM_BOOL);
$statement->debugDumpParams();
$statement->execute();
echo 'FALSE has been inserted' . PHP_EOL;

When PDO::ATTR_EMULATE_PREPARES is set to true, $stmt->debugDumpParams() outputs:

SQL: [43] INSERT INTO booleantest (hidden) VALUES (?)
Params:  1
Key: Position #0:
paramno=0
name=[0] ""
is_param=1
param_type=2

But when it is disabled, it outputs:

SQL: [43] INSERT INTO booleantest (hidden) VALUES (?)
Params:  1
Key: Position #0:
paramno=0
name=[0] ""
is_param=1
param_type=5

This is exactly the same output from tests/Doctrine/Tests/DBAL/Functional/Ticket/DBAL630Test.php (after adding debugDumpParam() to DBAL\Driver\PDOStatement.php, obviously). param_type = 2 when type = PDO::PARAM_STR.
For debugging purposes, I changed DBAL\Driver\PDOStatement.php line 67 from return parent::bindValue($param, $value, $type); to parent::bindValue(1, "false", \PDO::PARAM_BOOL); (so I will always store a boolean false on database), but debugDumpParam() keeps telling me that param_type is 2 if I'm using emulated prepares.

Comment by Davi Koscianski Vidal [ 01/Apr/14 ]

Using $stmt->bindValue(1, 'false', \PDO::PARAM_STR); works as expected. Maybe a dirty workaround?

Comment by Davi Koscianski Vidal [ 03/Apr/14 ]

This same test case passes with PHP 5.3.18, but fails with 5.5.3 and 5.5.11.

$ phpenv shell 5.5.11

$ php --version
PHP 5.5.11 (cli) (built: Apr  3 2014 09:52:27) 
Copyright (c) 1997-2014 The PHP Group
Zend Engine v2.5.0, Copyright (c) 1998-2014 Zend Technologies

$ vendor/bin/phpunit -c postgres.phpunit.xml tests/Doctrine/Tests/DBAL/Functional/Ticket/DBAL630Test.php 
PHPUnit 3.7.34 by Sebastian Bergmann.

Configuration read from.postgres.phpunit.xml

..F

Time: 3.91 seconds, Memory: 4.25Mb

There was 1 failure:

1) Doctrine\Tests\DBAL\Functional\Ticket\DBAL630Test::testBooleanConversionBoolParamEmulatedPrepares
Failed asserting that true is false.

./tests/Doctrine/Tests/DBAL/Functional/Ticket/DBAL630Test.php:79

FAILURES!
Tests: 3, Assertions: 6, Failures: 1.

$ phpenv shell system 

$ php --version
PHP 5.5.3-1ubuntu2.2 (cli) (built: Feb 28 2014 20:06:05) 
Copyright (c) 1997-2013 The PHP Group
Zend Engine v2.5.0, Copyright (c) 1998-2013 Zend Technologies
    with Zend OPcache v7.0.3-dev, Copyright (c) 1999-2013, by Zend Technologies

$ vendor/bin/phpunit -c postgres.phpunit.xml tests/Doctrine/Tests/DBAL/Functional/Ticket/DBAL630Test.php 
PHPUnit 3.7.34 by Sebastian Bergmann.

Configuration read from ./postgres.phpunit.xml

..F

Time: 1.59 seconds, Memory: 4.25Mb

There was 1 failure:

1) Doctrine\Tests\DBAL\Functional\Ticket\DBAL630Test::testBooleanConversionBoolParamEmulatedPrepares
Failed asserting that true is false.

./tests/Doctrine/Tests/DBAL/Functional/Ticket/DBAL630Test.php:79

FAILURES!
Tests: 3, Assertions: 6, Failures: 1.

$ phpenv shell 5.3.18 

$ php --version
PHP 5.3.18 (cli) (built: Apr  2 2014 16:47:04) 
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies

$ vendor/bin/phpunit -c postgres.phpunit.xml tests/Doctrine/Tests/DBAL/Functional/Ticket/DBAL630Test.php 
PHPUnit 3.7.34 by Sebastian Bergmann.

Configuration read from ./postgres.phpunit.xml

...

Time: 1.06 seconds, Memory: 6.50Mb

OK (3 tests, 6 assertions)
Comment by Davi Koscianski Vidal [ 04/Apr/14 ]

I think I messed something when creating the PR. The bot automatically created this ticket: http://www.doctrine-project.org/jira/browse/DBAL-863

I'm sorry.





[DBAL-855] [GH-560] Fix DateTimeTz type compatibility on SQL Anywhere versions < 12 Created: 31/Mar/14  Updated: 31/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/560

Message:

SQL Anywhere versions < 12 do not support a native date time with timezone type. Therefore the fallback strategy is to use the normal date time type. However the format of the date time tz type declaration has to correspond to the normal date time type declaration, too then.

`getDateTimeTzTypeDeclarationSQL()` -> `getDateTimeTypeDeclarationSQL()`
`getDateTimeTzFormatString()` -> `getDateTimeFormatString()`

I thought about changing that in the `AbstractPlatform` but I am not sure if that might break assumptions in userland code and therefore BC. So I left the implementation SQL Anywhere specific.






[DBAL-856] [GH-561] Fix FOR UPDATE SQL on SQL Anywhere Created: 31/Mar/14  Updated: 31/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/561

Message:

It looks like there was a misunderstanding on SQL Anywhere with the `FOR UPDATE` SQL as this is actually a statement used in prepared statements and does not work the ANSI way in ORM. So I removed it in favour of the table lock hint implementation which works out quite well and makes the `getForUpdateSQL()` rather useless anyways. SQL Server does it like this, too btw and both dialects are pretty similar because SQL Anywhere once drived from it.






[DBAL-853] [GH-558] Fix integer 0 default value reverse engineering on SQL Anywhere Created: 31/Mar/14  Updated: 31/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/558

Message:

If an integer column was created with a default value of `0`, SQL Anywhere reverse engineers the default value to `null`. This is fixed by this PR.






[DBAL-842] [GH-548] DBAL-774 - added failing test for parsing order of joins Created: 21/Mar/14  Updated: 21/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of acrobat:

Url: https://github.com/doctrine/dbal/pull/548

Message:

I had finaly some time to investigate the problem a bit more, and the problem is the way doctrine/dbal >= 2.4 handles the parsing of joins

The expected result is the way doctrine/dbal 2.3 handles the joins, which is not quite the way i expect that the query would be outputted but it is correct for execution.



 Comments   
Comment by Jeroen Thora [ 21/Mar/14 ]

Pullrequest related to DBAL-774





[DBAL-844] [GH-549] Fix truncate on MySQL >= 5.5 Created: 22/Mar/14  Updated: 22/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of ddeboer:

Url: https://github.com/doctrine/dbal/pull/549

Message:

When truncating tables on MySQL >= 5.5, an error is thrown:

```mysql
SQLSTATE[42000]: Syntax error or access violation:
1701 Cannot truncate a table referenced in a foreign key constraint ...
```

It turns out that with MySQL 5.5.7, `TRUNCATE` behaviour has changed. From the [MySQL docs](http://dev.mysql.com/doc/refman/5.5/en/truncate-table.html):

> TRUNCATE TABLE fails for an InnoDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted.

With this PR foreign key checks are disabled just before and re-enabled just after truncate.

As I encountered this issue using doctrine/data-fixtures, I originally submitted a fix there: https://github.com/doctrine/data-fixtures/pull/127. However, as @deeky666 pointed out, the DBAL is a better place for the fix.






[DBAL-846] [GH-551] Foreign key checks on MySQL >= 5.5.7 for TRUNCATE Created: 24/Mar/14  Updated: 24/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of tPl0ch:

Url: https://github.com/doctrine/dbal/pull/551

Message:

  • Added `ConfigurablePlatform` interface to set the connection
    parameters to the Platform
  • Connection is now setting the parameters to the Platform if it
    implements `ConfigurablePlatform`
  • `MySQLPlatform::getTruncateSql()` now checks for a new param
    `disable_fk_checks`, if it is true and the version is affected, it
    automatically adds the required SQL.

Additional solution to https://github.com/doctrine/dbal/pull/549






[DBAL-848] [GH-553] [DBAL-843] Fix reverse engineering LOB type column types in MySQL Created: 25/Mar/14  Updated: 25/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/553

Message:

This PR fixes reverse engineering the length of `TextType` / `BlobType` columns in MySQL which is important for proper SQL generation of distinct native `TINYTEXT`, `TEXT`, `MEDIUMTEXT`, `LONGTEXT`, `TINYBLOB`, `BLOB`, `MEDIUMBLOB` and `LONGBLOB` types.






[DBAL-849] [GH-554] Add support string date modifiers for SqlitePlatform Created: 26/Mar/14  Updated: 26/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of leonoff:

Url: https://github.com/doctrine/dbal/pull/554

Message:

In some cases we need support of non-numeric date(datetime) modifiers.
For example if we store interval-value-field in the table. Sqlite doesn't support 'field day' modifier. Common solution for this case is concatenate interval to a string: '' || field || 'day'






[DBAL-840] [GH-546] [DBAL-474] Fix filtering sequence names on PostgreSQL Created: 19/Mar/14  Updated: 19/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/546

Message:

The PostgreSQL schema manager has to filter sequence names before actually creating `Sequence` objects to avoid errors on accessing sequence database objects where the user has not enough privileges for.
The reason for this is that retrieving sequence attributes other than the sequence name requires accessing the particular sequence database object directly which requires the connected user to have enough privileges. This might not always be the case if for example a particular user can only access certain schemas but not others.
This patch might not be the best solution but a good compromise IMO. Changing the `AbstractSchemaManager` to filter sequence names before creating `Sequence` objects might affect other platforms and could also perhaps break BC. Furthermore this issue is completely PostgreSQL specific as it is the only currently supported platform not having a sequence's attributes stored directly in the system catalogs (AFAIK).






[DBAL-839] [GH-545] [DBAL-835] Quote old column name in rename column SQL Created: 19/Mar/14  Updated: 19/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/545

Message:

Quotes old column name in `ALTER TABLE` statements if necessary when a column gets renamed.

Note: The `AbstractSQLServerPlatformTestCase::getQuotedAlterTableRenameColumnSQL()` method need adjustments as soon as PR #542 gets merged (the `ALTER TABLE` statements need to be removed).






[DBAL-833] [GH-542] [DBAL-825] Drop default constraints before altering column type on SQL Server Created: 07/Mar/14  Updated: 07/Mar/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: sqlserver, sqlsrv

Issue Links:
Duplicate
duplicates DBAL-825 ALTER COLUMN on mssql is failing if d... In Progress
duplicates DBAL-826 [GH-536] [WIP] unit test for DBAL-825 Resolved

 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/542

Message:

SQL Server implements column default values as constraints and therefore requires them to be dropped before a column type change.
This PR implements the recreation of default constraints on column type alterations.
Additionally some code in `SQLServerPlatform::getAlterTableSQL()` has been refactored to avoid code duplication and unnecessary SQL generation.
Please note that due to the issue's tests the PostgreSQL platform had to be altered to fulfill the same behaviour. PostgreSQL returned some strange default value like `666:smallint` when reverse engineering a column type change with a default value of `666` from type `smallint` to `integer`. So I think this PR fixes a bug in this platform, too. Additionally I had to change the deprecated default value retrieval SQL in PostgreSQL in order to work flawlessly. See the [documentation](http://www.postgresql.org/docs/9.3/static/catalog-pg-attrdef.html) at the very end.

I would also like to note that this PR is definitely not the end of the line concerning default values and column alterations. Some weird errors were revealed on other platforms while fixing this issue. MySQL for example denies default values on BLOB/TEXT type columns, DB2 just throws non-understandable syntax errors around and Oracle seems to map decimal/numeric types without a defined scale to integer when reverse engineering (not really related to this issue but it came up while testing).

Otherwise the tests pass on all platforms.



 Comments   
Comment by Doctrine Bot [ 07/Mar/14 ]

A related Github Pull-Request [GH-536] was closed:
https://github.com/doctrine/dbal/pull/536





[DBAL-835] Old column name not quoted during column rename in MySQL Created: 12/Mar/14  Updated: 19/Mar/14

Status: In Progress
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Arttu Manninen Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: None
Environment:

CentOS with Doctrine 2.3 MySQL 5.5.31



 Description   

This is a broken feature, because escaping only sometimes (behavior at least in 2.3, if it has not been fixed after that) leads to breaking things. It is possible to CREATE a column named `usage` (without any backticks in the code), but things break from that point on. If I try to change the column name to a non-reserved word later, ALTER TABLE syntax will break

[Doctrine\DBAL\DBALException]
An exception occurred while executing 'ALTER TABLE products CHANGE usage purpose VARCHAR(256) NOT NULL':

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 'usage purpose VARCHAR(256) NOT NULL' at line 1

Migrating down then again escapes the column name even if it wasn't escaped in the definition.

I found a ticket from 2012 describing that column names using reserved keywords should be escaped manually, but since migrations will lead into a dead-end with at least using `usage` as a column name, this feature is somewhat broken.



 Comments   
Comment by Steve Müller [ 12/Mar/14 ]

Arttu Manninen There have been some fixes around quoting identifiers in DDL lately. It seems there is one missing around $oldColumnName in MySqlPlatform::getAlterTableSQL().
I have moved this issue to DBAL as it is an issue there.

Comment by Steve Müller [ 19/Mar/14 ]

Patch supplied in PR: https://github.com/doctrine/dbal/pull/545





[DBAL-830] [GH-539] unit test added for altering a column's default where the column name is... Created: 04/Mar/14  Updated: 05/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.4.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: sqlsrv


 Description   

This issue is created automatically through a Github pull request on behalf of DeepDiver1975:

Url: https://github.com/doctrine/dbal/pull/539

Message:

... a keyword - fails on mssql:

Exception : [Doctrine\DBAL\DBALException] An exception occurred while executing 'ALTER TABLE column_keyword_test DROP CONSTRAINT DF_D3D4D2F1_4BF2EAC0':

SQLSTATE [42000, 3728]: [Microsoft][SQL Server Native Client 11.0][SQL Server]'DF_D3D4D2F1_4BF2EAC0' is not a constraint.
SQLSTATE [42000, 3727]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Could not drop constraint. See previous errors.

With queries:
5. SQL: 'ALTER TABLE column_keyword_test DROP CONSTRAINT DF_D3D4D2F1_4BF2EAC0' Params: 
4. SQL: 'SELECT    col.name,
                          type.name AS type,
                          col.max_length AS length,
                          ~col.is_nullable AS notnull,
                          def.definition AS [default],
                          col.scale,
                          col.precision,
                          col.is_identity AS autoincrement,
                          col.collation_name AS collation,
                          CAST(prop.value AS NVARCHAR(MAX)) AS comment -- CAST avoids driver error for sql_variant type
                FROM      sys.columns AS col
                JOIN      sys.types AS type
                ON        col.user_type_id = type.user_type_id
                JOIN      sys.objects AS obj
                ON        col.object_id = obj.object_id
                JOIN      sys.schemas AS scm
                ON        obj.schema_id = scm.schema_id
                LEFT JOIN sys.default_constraints def
                ON        col.default_object_id = def.object_id
                AND       col.object_id = def.parent_object_id
                LEFT JOIN sys.extended_properties AS prop
                ON        obj.object_id = prop.major_id
                AND       col.column_id = prop.minor_id
                AND       prop.name = 'MS_Description'
                WHERE     obj.type = 'U'
                AND       (obj.name = 'column_keyword_test' AND scm.name = SCHEMA_NAME())' Params: 
3. SQL: 'ALTER TABLE column_keyword_test ADD CONSTRAINT DF_D3D4D2F1_ACF51D19 DEFAULT 23 FOR [select]' Params: 
2. SQL: 'CREATE TABLE column_keyword_test ([select] INT NOT NULL)' Params: 

Trace:
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Connection.php:988
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:971
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:612
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\SQLServerSchemaManager.php:232
C:\projects\doctrine\dbal\tests\Doctrine\Tests\DBAL\Functional\Schema\SchemaManagerFunctionalTestCase.php:619
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:976
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:831
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php:648
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:776
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:775
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:745
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php:349
C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php:176
C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:268
C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:506

#0 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(946): Doctrine\Tests\DbalFunctionalTestCase->onNotSuccessfulTest(Object(Doctrine\DBAL\DBALException))
#1 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php(648): PHPUnit_Framework_TestCase->runBare()
#2 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(776): PHPUnit_Framework_TestResult->run(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest))
#3 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(775): PHPUnit_Framework_TestCase->run(Object(PHPUnit_Framework_TestResult))
#4 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(745): PHPUnit_Framework_TestSuite->runTest(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest), Object(PHPUnit_Framework_TestResult))
#5 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php(349): PHPUnit_Framework_TestSuite->run(Object(PHPUnit_Framework_TestResult), false, Array, Array, false)
#6 C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php(176): PHPUnit_TextUI_TestRunner->doRun(Object(PHPUnit_Framework_TestSuite), Array)
#7 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(268): PHPUnit_TextUI_Command->run(Array, true)
#8 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(506): IDE_Base_PHPUnit_TextUI_Command::main()
#9 {main}





[DBAL-831] [GH-540] unit test to create constraint on forced lowercase table in oracle Created: 04/Mar/14  Updated: 04/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of DeepDiver1975:

Url: https://github.com/doctrine/dbal/pull/540

Message:

This might be crazy - but this was working in the 2.3.x code basis.

On master as well as 2.4.2 following error is throws:
````
Exception : [Doctrine\DBAL\Exception\TableNotFoundException] An exception occurred while executing 'DECLARE
constraints_Count NUMBER;
BEGIN
SELECT COUNT(CONSTRAINT_NAME) INTO constraints_Count FROM USER_CONSTRAINTS WHERE TABLE_NAME = '"OC_STORAGES"' AND CONSTRAINT_TYPE = 'P';
IF constraints_Count = 0 OR constraints_Count = '' THEN
EXECUTE IMMEDIATE 'ALTER TABLE "OC_STORAGES" ADD CONSTRAINT "OC_STORAGES_AI_PK" PRIMARY KEY ("NUMERIC_ID")';
END IF;
END;':

ORA-00942: table or view does not exist
ORA-06512: at line 6

With queries:
6. SQL: 'DECLARE
constraints_Count NUMBER;
BEGIN
SELECT COUNT(CONSTRAINT_NAME) INTO constraints_Count FROM USER_CONSTRAINTS WHERE TABLE_NAME = '"OC_STORAGES"' AND CONSTRAINT_TYPE = 'P';
IF constraints_Count = 0 OR constraints_Count = '' THEN
EXECUTE IMMEDIATE 'ALTER TABLE "OC_STORAGES" ADD CONSTRAINT "OC_STORAGES_AI_PK" PRIMARY KEY ("NUMERIC_ID")';
END IF;
END;' Params:
5. SQL: 'CREATE TABLE "oc_storages" ("id" VARCHAR2(64) NOT NULL, "numeric_id" NUMBER(10) NOT NULL, PRIMARY KEY("id"))' Params:
4. SQL: 'DROP TABLE "oc_storages"' Params:
3. SQL: 'DROP TRIGGER "OC_STORAGES"_AI_PK' Params:
2. SQL: 'ALTER SESSION SET NLS_TIME_FORMAT = 'HH24:MI:SS' NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM' NLS_NUMERIC_CHARACTERS = '.,'' Params:

Trace:
/home/deepdiver/Development/ownCloud/dbal/lib/Doctrine/DBAL/DBALException.php:116
/home/deepdiver/Development/ownCloud/dbal/lib/Doctrine/DBAL/Connection.php:988
/home/deepdiver/Development/ownCloud/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:971
/home/deepdiver/Development/ownCloud/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:429
/home/deepdiver/Development/ownCloud/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:569
/home/deepdiver/Development/ownCloud/dbal/tests/Doctrine/Tests/DBAL/Functional/Schema/OracleSchemaManagerTest.php:118

#0 /home/deepdiver/Development/ownCloud/dbal/vendor/phpunit/phpunit/PHPUnit/Framework/TestCase.php(946): Doctrine\Tests\DbalFunctionalTestCase->onNotSuccessfulTest(Object(Doctrine\DBAL\Exception\TableNotFoundException))
#1 /home/deepdiver/Development/ownCloud/dbal/vendor/phpunit/phpunit/PHPUnit/Framework/TestResult.php(648): PHPUnit_Framework_TestCase->runBare()
#2 /home/deepdiver/Development/ownCloud/dbal/vendor/phpunit/phpunit/PHPUnit/Framework/TestCase.php(776): PHPUnit_Framework_TestResult->run(Object(Doctrine\Tests\DBAL\Functional\Schema\OracleSchemaManagerTest))
#3 /home/deepdiver/Development/ownCloud/dbal/vendor/phpunit/phpunit/PHPUnit/Framework/TestSuite.php(775): PHPUnit_Framework_TestCase->run(Object(PHPUnit_Framework_TestResult))
#4 /home/deepdiver/Development/ownCloud/dbal/vendor/phpunit/phpunit/PHPUnit/Framework/TestSuite.php(745): PHPUnit_Framework_TestSuite->runTest(Object(Doctrine\Tests\DBAL\Functional\Schema\OracleSchemaManagerTest), Object(PHPUnit_Framework_TestResult))
#5 /home/deepdiver/Development/ownCloud/dbal/vendor/phpunit/phpunit/PHPUnit/TextUI/TestRunner.php(349): PHPUnit_Framework_TestSuite->run(Object(PHPUnit_Framework_TestResult), '/::testConstrai...', Array, Array, false)
#6 /usr/share/php/PHPUnit/TextUI/Command.php(176): PHPUnit_TextUI_TestRunner->doRun(Object(PHPUnit_Framework_TestSuite), Array)
#7 /tmp/ide-phpunit.php(268): PHPUnit_TextUI_Command->run(Array, true)
#8 /tmp/ide-phpunit.php(506): IDE_Base_PHPUnit_TextUI_Command::main()
#9

{main}

````






[DBAL-827] [GH-537] Update PDOConnection.php Created: 04/Mar/14  Updated: 04/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of mmicael1:

Url: https://github.com/doctrine/dbal/pull/537

Message:

Remove lot of if making beautifull code using call_user_func_array






[DBAL-825] ALTER COLUMN on mssql is failing if default constraint is attached Created: 03/Mar/14  Updated: 07/Mar/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Thomas Müller Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: None
Environment:

MSSQL


Issue Links:
Duplicate
is duplicated by DBAL-833 [GH-542] [DBAL-825] Drop default cons... In Progress
is duplicated by DBAL-826 [GH-536] [WIP] unit test for DBAL-825 Resolved

 Description   

Here is the unit test - implemented in class SchemaManagerFunctionalTestCase

 
    public function testChangeColumnsTypeWithDefault()
    {
        $table = new \Doctrine\DBAL\Schema\Table('column_change_type_test');
        $table->addColumn('id', 'integer', array('default' => 5));

        $this->_sm->createTable($table);

        $columns = $this->_sm->listTableColumns("column_change_type_test");
        $this->assertEquals(1, count($columns));
        $this->assertInstanceOf('Doctrine\DBAL\Types\IntegerType', $columns['id']->getType());

        $tableDiff = new \Doctrine\DBAL\Schema\TableDiff('column_change_type_test');
        $tableDiff->changedColumns['id'] = new \Doctrine\DBAL\Schema\ColumnDiff(
            'id', new \Doctrine\DBAL\Schema\Column(
                'id', \Doctrine\DBAL\Types\Type::getType('smallint'), array('default' => 5)
            ),
            array('type'),
            new \Doctrine\DBAL\Schema\Column(
                'id', \Doctrine\DBAL\Types\Type::getType('integer'), array('default' => '5')
            )
        );

        $this->_sm->alterTable($tableDiff);

        $columns = $this->_sm->listTableColumns("column_change_type_test");
        $this->assertEquals(1, count($columns));
        $this->assertInstanceOf('Doctrine\DBAL\Types\SmallIntType', $columns['id']->getType());
        $this->assertSame('', $columns['id']->getDefault());
    }

Causes following result

 
Exception : [Doctrine\DBAL\DBALException] An exception occurred while executing 'ALTER TABLE column_change_type_test ALTER COLUMN id SMALLINT NOT NULL':

SQLSTATE [42000, 5074]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The object 'DF_A74995E2_BF396750' is dependent on column 'id'.
SQLSTATE [42000, 4922]: [Microsoft][SQL Server Native Client 11.0][SQL Server]ALTER TABLE ALTER COLUMN id failed because one or more objects access this column.

With queries:
5. SQL: 'ALTER TABLE column_change_type_test ALTER COLUMN id SMALLINT NOT NULL' Params: 
4. SQL: 'SELECT    col.name,
                          type.name AS type,
                          col.max_length AS length,
                          ~col.is_nullable AS notnull,
                          def.definition AS [default],
                          col.scale,
                          col.precision,
                          col.is_identity AS autoincrement,
                          col.collation_name AS collation,
                          CAST(prop.value AS NVARCHAR(MAX)) AS comment -- CAST avoids driver error for sql_variant type
                FROM      sys.columns AS col
                JOIN      sys.types AS type
                ON        col.user_type_id = type.user_type_id
                JOIN      sys.objects AS obj
                ON        col.object_id = obj.object_id
                JOIN      sys.schemas AS scm
                ON        obj.schema_id = scm.schema_id
                LEFT JOIN sys.default_constraints def
                ON        col.default_object_id = def.object_id
                AND       col.object_id = def.parent_object_id
                LEFT JOIN sys.extended_properties AS prop
                ON        obj.object_id = prop.major_id
                AND       col.column_id = prop.minor_id
                AND       prop.name = 'MS_Description'
                WHERE     obj.type = 'U'
                AND       (obj.name = 'column_change_type_test' AND scm.name = SCHEMA_NAME())' Params: 
3. SQL: 'ALTER TABLE column_change_type_test ADD CONSTRAINT DF_A74995E2_BF396750 DEFAULT 5 FOR id' Params: 
2. SQL: 'CREATE TABLE column_change_type_test (id INT NOT NULL)' Params: 

Trace:
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Connection.php:988
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:971
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:612
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\SQLServerSchemaManager.php:232
C:\projects\doctrine\dbal\tests\Doctrine\Tests\DBAL\Functional\Schema\SchemaManagerFunctionalTestCase.php:621
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:976
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:831
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php:648
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:776
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:775
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:745
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php:349
C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php:176
C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:268
C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:506

#0 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(946): Doctrine\Tests\DbalFunctionalTestCase->onNotSuccessfulTest(Object(Doctrine\DBAL\DBALException))
#1 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php(648): PHPUnit_Framework_TestCase->runBare()
#2 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(776): PHPUnit_Framework_TestResult->run(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest))
#3 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(775): PHPUnit_Framework_TestCase->run(Object(PHPUnit_Framework_TestResult))
#4 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(745): PHPUnit_Framework_TestSuite->runTest(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest), Object(PHPUnit_Framework_TestResult))
#5 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php(349): PHPUnit_Framework_TestSuite->run(Object(PHPUnit_Framework_TestResult), false, Array, Array, false)
#6 C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php(176): PHPUnit_TextUI_TestRunner->doRun(Object(PHPUnit_Framework_TestSuite), Array)
#7 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(268): PHPUnit_TextUI_Command->run(Array, true)
#8 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(506): IDE_Base_PHPUnit_TextUI_Command::main()
#9 {main}



 Comments   
Comment by Thomas Müller [ 03/Mar/14 ]

Possible solution: http://www.select-sql.com/mssql/how-to-alter-column-with-default-constraint-in-mssql.html

Comment by Thomas Müller [ 03/Mar/14 ]

Here is the unit test: https://github.com/DeepDiver1975/dbal/commit/53238301f7e124d31232e9b3eab774c32c9e04c4

Comment by Steve Müller [ 03/Mar/14 ]

Thomas Müller Thanks for reporting. Which version of SQL Server is affected by this?

Comment by Thomas Müller [ 03/Mar/14 ]

SQL Server 2012 Express Edition





[DBAL-819] Schema-tools does not work on multiple Oracle's schemas Created: 21/Feb/14  Updated: 22/Feb/14

Status: Open
Project: Doctrine DBAL
Component/s: Platforms, Schema Managers
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Antoine Descamps Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: Cli, oracle, orm, schematool
Environment:

DB: Oracle 11g



 Description   

The schema-tools, used via the CLI, is not able to detect schema's changes when working on multiple schemas.

For instance, the ORM is configured with a "global" Oracle's user, having permissions on every schemas. To specify which entities belong to which schema, I've prefixed the table name with the corresponding schema.

When trying to do the following command:

orm:schema-tool:update --dump-sql

Doctrine returns me the following message:

Nothing to update - your database is already in sync with the current entity metadata.

If, instead of using the "global" user in the Doctrine's configuration, I set the user of the specific schema I'm trying to generate a table on based from an entity, it works.



 Comments   
Comment by Steve Müller [ 21/Feb/14 ]

Moved this to DBAL for now. It seems to be related to schema prefixed table names not being evaluated in the platforms when generating the SQL for reverse engineering the database schema.

Comment by Steve Müller [ 22/Feb/14 ]

Antoine Descamps Okay after having investigated on this in detail and thinking about the possibilities we have to find a solution for this, I came to the following conclusion:
ORM's schema tool and DBAL's schema introspection are designed to be bound to what Doctrine defines as "database" for each platform. This is the scope of the whole operation. In case of Oracle it is the "user". You cannot break out of this scope in any way as the current DBAL implementation is not designed for a "complete" schema introspection and therefore does not allow it at some points. Fully understanding your concern I am afraid we cannot find a reasonable solution for your use case at this point in development (2.x). Furthermore there is a good reason for limiting the schema introspection to a certain layer. If the schema tool would introspect the "complete" schema without regard to the database it is connected to, it would suggest a lot of schema changes from other databases that do not belong to the context of the entity manager / database. This behaviour would even cause a lot more annoyance to users as it is the default use case that users are only interested in one database per entity manager.
Also this problem is completely independant from your mapping definitions. So it doesn't matter whether you prefix your table names or not. When running the "update" operation on the schema tool, it is the online schema introspection part that is preventing the schema tool from behaving as you would wish.
Changing the schema introspection behaviour in DBAL would completely break BC and is at some places in the code not even possible without changing the API.
I am sorry that I have to disappoint you with this conclusion but we I am afraid we cannot do anything about your issue until we start developing 3.x. We might reevaluate your use case their and see what we can do.





[DBAL-821] [GH-532] DBAL-807 [DBAL-807] - Added failing test reproduces a problem. Created: 22/Feb/14  Updated: 22/Feb/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Reference
relates to DBAL-807 Index renaming in postgresql does not... In Progress
is referenced by DBAL-822 [GH-533] [DBAL-807] Respect schema wh... Open

 Description   

This issue is created automatically through a Github pull request on behalf of Strate:

Url: https://github.com/doctrine/dbal/pull/532

Message:

Added failing test reproduces problem, decribed in ticket http://www.doctrine-project.org/jira/browse/DBAL-807



 Comments   
Comment by Doctrine Bot [ 22/Feb/14 ]

A related Github Pull-Request [GH-532] was closed:
https://github.com/doctrine/dbal/pull/532





[DBAL-822] [GH-533] [DBAL-807] Respect schema when renaming indexes Created: 22/Feb/14  Updated: 22/Feb/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Reference
relates to DBAL-821 [GH-532] DBAL-807 [DBAL-807] - Added ... Open
relates to DBAL-807 Index renaming in postgresql does not... In Progress

 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/533

Message:

Statements for renaming indexes have to include the schema name if applicable.



 Comments   
Comment by Doctrine Bot [ 22/Feb/14 ]

A related Github Pull-Request [GH-532] was closed:
https://github.com/doctrine/dbal/pull/532





[DBAL-818] Fetching identity value from an insert fails with merge replication enabled Created: 20/Feb/14  Updated: 20/Feb/14

Status: Open
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.4.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Michael Anthon Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

The way that the PDOSqlsrv driver fetches the identity value for a freshly inserted record will fail if there are triggers on a table that do a secondary insert on a table that also has an identity column.

This is the case when you set up merge replication in SQL Server. The replication creates a series of triggers on the tables to catch any of the changes made for the purposes of replication and inserts those changes elsewhere.

We have switched to using the native SQLSrv drivers instead to work around this since that uses "SELECT SCOPE_IDENTITY() AS LastInsertId" to fetch the value during the insert command.



 Comments   
Comment by Steve Müller [ 20/Feb/14 ]

I'm not quite sure whether we can fix this is in a reasonable way. See PDO's lastInsertId method in itself is documented to be very inconsistent and behaves differently throughout different drivers and even database versions.

This method may not return a meaningful or consistent result across different PDO drivers, because the underlying database may not even support the notion of auto-increment fields or sequences.

Because of this we do not have tests in our testsuite yet that cover the last insert id topic that work as expected on all drivers.
IIRC the fact that DBAL's implementation for the native sqlsrv driver uses "SELECT SCOPE_IDENTITY() AS LastInsertId" is a workaround to support this feature at all and come around the driver limitation IIRC. This as such is a dirty workaround and should IMO not be relied on for scenarios such as you describe.
I'm not quite sure what the expected behaviour of PDO drivers in general would be concerning triggers on PK columns that do other inserts regarding last insert IDs. IMO this is not a Doctrine bug but rather an unsupported use case or even a driver bug/limitation? Not sure on this.
The only thing we could actually do is implement the same workaround we have in the native sqlsrv driver for the PDO driver. But I would rather not do this for such an edge case scenarion.
But that's just my opinion

Comment by Michael Anthon [ 20/Feb/14 ]

Yes, I agree that any workaround will be a bit of a dirty hack.

The main problem is the PDO driver using @@identity to get the last inserted id, which is pretty much the wrong way to do it in all but the simplest of cases... SCOPE_IDENTITY is there for a reason but won't work when called subsequently since it's run inside an sp_prepexec and will be out of scope anyway (it has to be tacked onto the end of the insert statement as it is in the sqlsrv driver)

There's another piece of code in the SQLSrvConnection that uses this method...

 $sql = "SELECT IDENT_CURRENT(".$this->quote($name).") AS LastInsertId";

That could potentially be used as well and would probably give a more accurate answer but is also subject to race conditions on busy systems.





[DBAL-816] [GH-529] fix for postres listTableNames Created: 18/Feb/14  Updated: 18/Feb/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of MaksSlesarenko:

Url: https://github.com/doctrine/dbal/pull/529

Message:

fix for listTableNames to return unescaped table names






[DBAL-811] [GH-527] Birko boolean conversion Created: 12/Feb/14  Updated: 12/Feb/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of birko:

Url: https://github.com/doctrine/dbal/pull/527

Message:

Added platform specific conversion form database Boolean type to PHP bool type






[DBAL-808] [GH-525] Added flags support for mysqli::real_connect in Mysqli driver. Created: 08/Feb/14  Updated: 08/Feb/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of marcini:

Url: https://github.com/doctrine/dbal/pull/525

Message:

This is necessary if you want to set connection options like compression or SSL encryption.

Recently I wanted to use DBAL with Mysqli driver and I noticed that there is no possibility to enable connection compression, which is done in mysqli by setting flags to mysqli::real_connect. DBAL Mysqli driver lacks support for setting any flag, so I decided to propose my change to add such possibility.






[DBAL-804] [GH-523] SQLSTATE[HY093]: Invalid parameter number: parameter was not defined Created: 06/Feb/14  Updated: 08/Feb/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.4.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: querybuilder
Environment:

OSX 10.8, PHP5.4, MySQL56


Issue Links:
Duplicate
is duplicated by DBAL-803 SQLSTATE[HY093]: Invalid parameter nu... Resolved

 Description   

I am using this code from documentation

QueryBuilder Positional with ?
$query = $this->subject->getDatabaseHandle()->createQueryBuilder();
$query->delete($this->testTable)->where($this->testFieldSecond . ' = ?')
$query->setParameter(1, (int)$GLOBALS['EXEC_TIME']);
$query->execute();

... and I also tried

QueryBuilder Positional with ?1
$query = $this->subject->getDatabaseHandle()->createQueryBuilder();
$query->delete($this->testTable)->where($this->testFieldSecond . ' = ?1')
$query->setParameter(1, (int)$GLOBALS['EXEC_TIME']);
$query->execute();

but I got this error:

Error in test case deletePositionalParameter
File: vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php
Line: 91
An exception occurred while executing 'DELETE FROM test_t3lib_dbtest WHERE fieldblub = ?' with params [1391699318]:

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

When I provide a type to setParamter like

QueryBuilder Positional with ?1 and type
$query = $this->subject->getDatabaseHandle()->createQueryBuilder();
$query->delete($this->testTable)->where($this->testFieldSecond . ' = ?1')
$query->setParameter(1, (int)$GLOBALS['EXEC_TIME'], \PDO::PARAM_INT);
$query->execute();

or when I changed it to a named query it works

QueryBuilder Positional with named query
$query = $this->subject->getDatabaseHandle()->createQueryBuilder();
$query->delete($this->testTable)->where($this->testFieldSecond . ' = :test')
$query->setParameter(':test', (int)$GLOBALS['EXEC_TIME']);
$query->execute();

Url: https://github.com/doctrine/dbal/pull/523

Message:

Creating Unit Tests to proof the behavior. Its my first PR and I
don't know if I created the Unit Test in the right place. It was the
location where the test actually writing to database which is needed
provoke the error.

DBAL-803 #Provides a test for this issue



 Comments   
Comment by Doctrine Bot [ 08/Feb/14 ]

A related Github Pull-Request [GH-523] was closed:
https://github.com/doctrine/dbal/pull/523

Comment by Benjamin Eberlei [ 08/Feb/14 ]

Fixed in the docs.





[DBAL-807] Index renaming in postgresql does not work when index relates to table inside namespace Created: 08/Feb/14  Updated: 22/Feb/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.5
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Artur Eshenbrener Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Reference
is referenced by DBAL-821 [GH-532] DBAL-807 [DBAL-807] - Added ... Open
is referenced by DBAL-822 [GH-533] [DBAL-807] Respect schema wh... Open

 Description   
CREATE SCHEMA test;
CREATE TABLE test.table_name (id INT);
CREATE INDEX idx_1 ON test.table_name (id);

If index would be renamed, generated sql is:

ALTER INDEX idx_1 RENAME TO new_index_name;

But valid sql code should be:

ALTER INDEX test.idx_1 RENAME TO new_index_name;


 Comments   
Comment by Steve Müller [ 21/Feb/14 ]

Artur Eshenbrener Can you please provide more details about your use case so that we can reproduce it better. How do you get the wrong SQL?

Comment by Artur Eshenbrener [ 22/Feb/14 ]

I've pushed failing test, reproduces this problem.
https://github.com/doctrine/dbal/pull/532

Comment by Doctrine Bot [ 22/Feb/14 ]

A related Github Pull-Request [GH-532] was closed:
https://github.com/doctrine/dbal/pull/532

Comment by Steve Müller [ 22/Feb/14 ]

Patch supplied in PR: https://github.com/doctrine/dbal/pull/533





[DBAL-800] [GH-522] Update DB2Platform.php to add ORDER BY data. Created: 30/Jan/14  Updated: 30/Jan/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of ellier:

Url: https://github.com/doctrine/dbal/pull/522

Message:

Added ORDER BY to doModifyLimitQuery in DB2Platform.php.






[DBAL-798] [GH-520] [WIP] Add pdo informix driver support Created: 28/Jan/14  Updated: 28/Jan/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of josemalonsom:

Url: https://github.com/doctrine/dbal/pull/520

Message:

I have created this branch to add support to PDO_INFORMIX in DBAL.

First I had put a new topic in the doctrine-dev group (https://groups.google.com/forum/#!topic/doctrine-dev/gndS00nxSQA) where i explain some issues i have.






[DBAL-794] [GH-517] Fix method signature in Doctrine\DBAL\Driver\Connection Created: 19/Jan/14  Updated: 29/Jan/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by DBAL-799 [GH-521] Fix Connection Interface Resolved

 Description   

This issue is created automatically through a Github pull request on behalf of raziel057:

Url: https://github.com/doctrine/dbal/pull/517

Message:

The method ``prepare`` must have an optional driverOptions parameter to be compatible with class which implement the interface Doctrine\DBAL\Driver\Connection.

To avoid this problem:

HipHop Fatal error: Declaration of Doctrine\DBAL\Driver\PDOConnection::prepare() must be compatible with that of Doctrine\DBAL\Driver\Connection::prepare() in /home/travis/build/symfony/symfony/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php on line 30






[DBAL-796] [GH-518] support to ibmi db2 - as400 Created: 22/Jan/14  Updated: 22/Jan/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of nfrignani:

Url: https://github.com/doctrine/dbal/pull/518

Message:

support for ibmi db2 (as400)
some code in DB2Platform doesn't yet work on ibmi db2






[DBAL-777] [GH-503] Decode hex-encoded clobs/blobs when using pgsql on windows Created: 09/Jan/14  Updated: 09/Jan/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of Seldaek:

Url: https://github.com/doctrine/dbal/pull/503

Message:

http://stackoverflow.com/a/15112973 explains the why. It'd be great to offer support for this natively. I run an `->executeQuery('SET bytea_output=escape')` every time now as a workaround but that's not very nice.



 Comments   
Comment by Doctrine Bot [ 09/Jan/14 ]

A related Github Pull-Request [GH-503] was closed:
https://github.com/doctrine/dbal/pull/503

Comment by Jordi Boggiano [ 09/Jan/14 ]

This was reopened now





[DBAL-778] [GH-504] Decode hex-encoded clobs/blobs when using pgsql on windows Created: 09/Jan/14  Updated: 09/Jan/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of Seldaek:

Url: https://github.com/doctrine/dbal/pull/504

Message:

http://stackoverflow.com/a/15112973 explains the why. It'd be great to offer support for this natively. I run an `->executeQuery('SET bytea_output=escape')` every time now as a workaround but that's not very nice.






[DBAL-753] Evaluate owncloud patch for Oracle quoting Created: 31/Dec/13  Updated: 01/Jan/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

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


 Description   

See https://github.com/owncloud/3rdparty/commit/7ae81563894b971e51cee7bdb0ac2da83ecc5149



 Comments   
Comment by Steve Müller [ 01/Jan/14 ]

There are already several open tickets concerning general quotation issues. The patch might solve a little subset of the root problem which is actually really tricky. We need a more general and robust approach that works on all platforms.
See DBAL-96





[DBAL-746] [GH-479] [DBAL-624] Fix parsing parameters in comments Created: 29/Dec/13  Updated: 29/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/479

Message:

Parameter placeholders should not be parsed out of comments in statements.






[DBAL-730] [GH-464] [DBAL-139] [WIP] Finish CACHE option implementation for sequences Created: 23/Dec/13  Updated: 23/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/464

Message:

@beberlei Started implementation for DBAL-139(http://www.doctrine-project.org/jira/browse/DBAL-139) in commit https://github.com/doctrine/dbal/commit/e8efcd7621e85003f3ce496bc26a108b53371838 and https://github.com/doctrine/dbal/commit/d397c005dd3ff3d48a81eeb47c2a0d74d6b7400d which was first drafted in PR #202. It's about adding a cache option for sequences.

*The following missing aspects of the implementation were added:*

  • SQL Server support
  • SQL Anywhere support
  • Reverse engineering the cache option with the schema managers
  • Comparator implementation
  • Schema::createSequence() adjustment
  • More tests

*Additional optimizations:*

  • Rename `$cache` to `$cacheSize` in Sequence (unified naming with `$allocationSize`)
  • More strict input validation for cache size values in Sequence to avoid SQL generation errors
  • Better API documentation
  • Unified `getSequenceCacheClause()` method on all platforms

*Todo:*

  • Fix comparator issues (see below)
  • Finish test suite for this (depends on comparator issues solution)

*Comparator issues*
The comparator will get into trouble when comparing cache size values. To get an understanding of this I'll first explain how cache size values evaluate to the database:

  • `null` -> No cache size specified, don't generate cache clause for sequences and use platform's default
  • `0` -> Explicitly disable caching for sequences, generates `NOCACHE` clause on platforms
  • every other positive value is an explicite specification of the cache size and results in `CACHE <size>` clause

Now there are two problems.

First one is that a value of `1` has a special meaning on some platforms, too. Oracle does not accept it and throws an error as the minimum allowed value is `2`. PostgreSQL allows a value of `1` but in fact interprets it as `NOCACHE`. SQL Server and SQL Anywhere accept a value of `1` but the behaviour is unclear compared to an explicite `NOCACHE` on these platforms. Now I am assuming that a cache size of `1` maybe also physically has the same effect as if specifying no cache at all and that this is more of a "compatibility" value and we maybe should not allow it at all to preserve portability throughout the vendors.

The second problem is the `null` value for `$cacheSize` in Sequences. This tells the vendor to use it's own specific default cache size which differs on all platforms. The comparator will always mark a sequence as changed then when `null` was initially defined for the sequence and the sequence is introspected from database again. This will lead to repeated `ALTER SEQUENCE` statements in the schema tool which is not good. Also we cannot determine in the platform itself if the sequence has really changed as there currently is no `SequenceDiff` class which could get passed to `AbstractPlatform::getAlterSequenceSQL()`. Instead we only get the changed sequence object. I have put together some [examples](https://gist.github.com/deeky666/6c4e47a275b1c9e7c068) to clarify on what I mean as it is hard to explain.

So I'm asking for opinions and input on this






[DBAL-731] [GH-465] [DBAL-423] Optimize non-native GUID type declaration Created: 23/Dec/13  Updated: 23/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/465

Message:

Currently non-native GUID type is mapped to `VARCHAR(255)` which is not effective as GUID always has a fixed length of 36 characters. Therefore it should be mapped to `CHAR(36)` instead.






[DBAL-715] Using caching with fetchColumn() of single row does not work Created: 20/Dec/13  Updated: 20/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

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

Issue Links:
Reference
is referenced by DBAL-468 [GH-288] Fix fetchColumn not caching Resolved

 Description   

The caching mechanism only triggers when the cursor is fully iterated and the result is closed. This breaks caching for code of the kind:

$cnt = $conn->executeQuery($sql)->fetchColumn();





[DBAL-669] Postgresql platform schema creation fails if it already exists Created: 18/Nov/13  Updated: 02/Apr/14

Status: Awaiting Feedback
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.4, 2.4.1
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Chris Ramakers Assignee: Marco Pivetta
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Postgresql 8.3.14 on CentOS6
Also happens on Postgresql 9.2 on the same box



 Description   

This patch (https://github.com/doctrine/dbal/commit/fabe3c346b24dcb70eba0cb3936998ec6cc152f0) introduced a bug where the schemaNeedsCreation method always returns true if the schema name isn't 'default' or 'public'.

We heavily use schema's in our application and whenever an insert query is queued, it fails because the schema in question already exists but the platform adapter fails to detect that and continues with a "CREATE SCHEMA" query which fails.

The easy fix is to add the 'IF NOT EXISTS' clause to the 'CREATE SCHEMA' query but that will only function on Postgresql 9.3 and upward since 'IF NOT EXISTS' wasn't possible in earlier versions for schema creation.

Beter would be to load the existing schema's and compare them to those. I would create a pull request but i'm not sure how to obtain a database connection in the Platform (if at all possible) to pull a list of already known schema's?



 Comments   
Comment by Marco Pivetta [ 13/Dec/13 ]

Chris Ramakers I'm trying to look into it. The method

schemaNeedsCreation

seems indeed to be wrong.

Comment by Marco Pivetta [ 13/Dec/13 ]

Provided a fix for this at https://github.com/doctrine/dbal/pull/444

We won't fix the schema creation command, since it is supposed to fail on already existing conflicting elements (tables/etc)

Comment by Chris Ramakers [ 02/Apr/14 ]

Any news on this? The bug keeps causing problems every time we deploy a new version. We are practically required to edit the vendor files for doctrine in our project so this bug doesn't cause issues.





[DBAL-642] Generated IDs are not guaranteed to be unique over the table's lifetime in SQLite Created: 27/Oct/13  Updated: 01/Jan/14

Status: Open
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.4
Fix Version/s: None
Security Level: All

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


 Description   

In

http://docs.doctrine-project.org/en/2.0.x/reference/basic-mapping.html#identifier-generation-strategies

it says that MySQL and SQLite use AUTO_INCREMENT by default. The generated SQL for creating an ID field with GENERATOR_TYPE_AUTO looks like this (abbreviated
for readability):

CREATE_TABLE_TEST (id INTEGER DEFAULT 0 NOT NULL, PRIMARY KEY(id))

http://www.sqlite.org/faq.html#q1 states:

If you declare a column of a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty. [...] Note that the integer key is one greater than the largest key that was in the table just prior to the insert. The new key will be unique over all keys currently in the table, but it might overlap with keys that have been previously deleted from the table.

So in other words, if you remove an entity and then create a new one, the new one will have the same id as the previously deleted one. If you do both operations on the same entitymanager, id references (in proxies f.x.) will suddenly get confused and point to something else (at least that's my current theory..)

The point is: SQLite doesn't act like MySQL as the documentation implies, and IMHO SQLite's current behaviour makes it useless for more complex scenarios. I've found some reference to this problem here:

https://github.com/doctrine/dbal/pull/66

Unfortunately, it doesn't mention any solution. The problem is that you can't override the columnDefinition, because it would have to be "INTEGER PRIMARY KEY AUTOINCREMENT", but then, you get an exception, because the Platform appends ", PRIMARY KEY(id)", so it's defined twice



 Comments   
Comment by Steve Müller [ 25/Nov/13 ]

As far as I understand there is a conflict in SQLite between having an autoincrement primary key and having a composite primary because you can only choose either way. The PR you referenced removed the autoincrement behaviour in favour of having the opportunity to define composite primary keys. So what would you expect to be the solution here?

Comment by flack [ 25/Nov/13 ]

Well, from a user's point of view, it would be nice if the SQLite Platform implementation would make full use of the possibilities of SQLite. That is to say: If someone uses composite primary, they get the behaviour Doctrine has right now, and those that use the simple case (which is recommended all over the documentation), get the behaviour previous to the pull request, i.e. autoincrement that works like in MySQL (which the documentation implies). As far as I understood the discussion in the pull request, the author was looking for a solution to implement this, but then the PR was merged before the issue was solved.

Comment by flack [ 25/Nov/13 ]

I guess what is bothering me is that the current behaviour breaks assumptions that I think many applications using Doctrine make. At least I know that in my own code, I never planned for the possibility of a database primary key being re-used for a different object, especially not during the same request. And like I wrote above, I suspect that Doctrine itself is not totally prepared for that situation either (also mentioned here: https://github.com/doctrine/dbal/pull/66#discussion_r173623). So IMHO the IDENTIY generator strategy for SQLite seems broken, or at least is behaving unexpectedly. The documentation says

AUTO (default): Tells Doctrine to pick the strategy that is preferred by the used database platform. The preferred strategies are IDENTITY for MySQL, SQLite and MsSQL and SEQUENCE for Oracle and PostgreSQL. This strategy provides full portability.

I don't really see how the current behaviour can be said to provide full portability (at least with MySQL, which supposedly uses the same preferred strategy)

Comment by Steve Müller [ 25/Nov/13 ]

Yeah I get your point. But it's always hard and error prone to work around the vendors lack of common features. A possibility COULD be to implement a trigger in columns declared as autoincrement which simulates the behaviour of an auotincrement column on inserts. Oracle uses a similar workaround with a trigger and a sequence to simulate autoincrement columns. But this is just an idea and has to evaluated for usability first.

Comment by Steve Müller [ 25/Nov/13 ]

Yes that's true. The documentation is misleading here. I guess that it was written before the issue came up and then was not updated. Unfortunately SQLite does not support native sequences which would make life a lot easier. But I will keep that in mind and investigate a solution for this.

Comment by Benjamin Eberlei [ 13/Dec/13 ]

I think there is no fix for this, this is just how SQLite works, and we cannot really keep the last ids somewhere. IMHO its a documentation issue.

Comment by flack [ 13/Dec/13 ]

Well, you cannot fix it for cases with multiple id columns (but the Doctrine documentation already suggests that they should be avoided where possible), but for single integer columns (which is the normal case, as suggested by documentation), SQLite provides all the necessary functionality, as long as you create the column with INTEGER PRIMARY KEY AUTOINCREMENT. So IMHO the best solution would be if support for this could be implemented somehow in the SQL Platform driver.

Comment by flack [ 14/Dec/13 ]

Case in point: I implemented exactly this in a Doctrine adapter I'm working on:

https://github.com/flack/midgard-portable/blob/master/src/midgard/portable/storage/subscriber.php#L136

Granted, this is a very ugly workaround that only works because I know all my ID columns are actually called 'id' (and will never change), but I'm fairly sure that a more general solution could be built with reasonable effort.

Comment by Benjamin Eberlei [ 01/Jan/14 ]

flack We removed the Sqlite AUTOINCREMENT for some weird reason. I am inclined to add this again, however I need to find out what the reasons for removing this have been.

Comment by Steve Müller [ 01/Jan/14 ]

Benjamin Eberlei I checked that lately and I came to the same conclusion. The reason why it was removed is to support composite primary keys which was not possible before somehow. We could add autoincrement if only a single column integer primary key is given I think...





[DBAL-624] Parameter in comments is parsed Created: 03/Oct/13  Updated: 22/Nov/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

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

Issue Links:
Reference
relates to DBAL-552 Colon (":") in field name treats like... Resolved

 Description   

Text in comment is parsed as params.
I.e.:
select abc – :par
from table

select abc /* :par */
from table



 Comments   
Comment by Steve Müller [ 21/Nov/13 ]

Which platform / driver do you refer to? Maybe a duplicate of this?
http://www.doctrine-project.org/jira/browse/DBAL-124

Comment by Vitaliy [ 22/Nov/13 ]

No platform / driver depend. I try test class SQLParserUtils in separate module. And this sql parsed wrong.





Deprecate Migrations in favor of stable tools (DBAL-602)

[DBAL-605] Phinx Support Created: 12/Sep/13  Updated: 18/Sep/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Sub-task Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: None


 Comments   
Comment by Rob Morgan [ 18/Sep/13 ]

Hi Guys, I'm the lead developer for Phinx. Happy to give support here if needed.

Rob





[DBAL-600] Add support of joins in update statements when using DBAL QueryBuilder Created: 09/Sep/13  Updated: 23/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.3.4
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: Konstantin Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Comments   
Comment by Steve Müller [ 23/Dec/13 ]

This is tricky as UPDATE statements based on JOINs are not in the SQL standard and therefore the syntax and support differs alot throughout the vendors. But could be doable with some effort.





[DBAL-599] Add support of insert and insert select statements in DBAL QueryBuilder Created: 09/Sep/13  Updated: 25/Nov/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.3.4
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: Konstantin Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Reference
relates to DBAL-182 Insert and Merge Query Objects Open
relates to DBAL-320 allow SQL QueryBuilder to do INSERTS Resolved
is referenced by DBAL-636 QueryBuilder insert Resolved




[DBAL-587] Support for Triggers, Views, Procedures Created: 27/Aug/13  Updated: 27/Aug/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Schema should have support for triggers, views and stored procedures. Since this is not standardizable accross platforms easily, the idea is to allow arbitrary SQL DDL Statements to be registered on a Schema, maybe even qualified by platform.

$artifact = new SQLArtifact();
$artifact->setName('some_view_name');
$artifact->create('CREATE VIEW IF NOT EXISTS foo SELECT 1');
$artifact->drop('DROP VIEW foo');
$artifact->setPlatforms("mysql"); // accepts string or array, if nothing is set applies to ALL platforms.
$schema->addSQLArtifact($artifact);

During the SQL generation for the schema, the following will be appended to the currently generated SQL:

DROP VIEW foo;
CREATE VIEW IF NOT EXISTS foo SELECT 1

The drop part of the statement is always rendered before the create statement in the Create Schema Visitor. In the Drop schema listener only the drop part is used.






[DBAL-550] Complete/non-overridable event dispatching in DDL methods of AbstractPlatform Created: 22/Jun/13  Updated: 23/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: Stéphane Klein Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: migrations, schematool


 Description   

The default implementation provided by the AbstractPlatform class dispatches several events related to schema modifications (create table, change column and so on) and offers the ability to prevent the default behaviour by setting a flag on the dispatched event. However, it appears that some DDL operations aren't covered by this mechanism (foreign keys creation, for example, doesn't seem to be observable). Furthermore, the concrete platforms can override any of these methods, potentially leading to inconsistancies in components that expect those events to be dispatched.

I'm working on a large modular application build on top of Symfony, in which each bundle must have its own migration classes (i.e. migrations on a per bundle basis, not at the application level), possibly with references to other parts of the schema. I used to write those classes manually using the dbal api, but this is tiedous and error-prone, and I'd like to take advantage of the mapping-based schema generation. I found that using the event system and "filtering" ddl operations to keep only the operations related to a given bundle is a clean and easy way to achieve this goal, but the issues I mentionned prevent me to complete the implementation.

I'd be glad to hear your opinion on that topic and, providing you're interested in it, contribute in a way or another to improve the existing code.



 Comments   
Comment by Benjamin Eberlei [ 22/Jun/13 ]

This is not a blocker

Comment by Benjamin Eberlei [ 22/Jun/13 ]

The DBAL event system is not in its best shape and definately could use some improvement. Your easiest way however would be to hook the process of generating the whole schema, and then dropping tables from those two to only contain the ones you are interested in.

Comment by Stéphane Klein [ 23/Jun/13 ]

That's indeed the simplest solution. Thanks a lot for your answer.

Comment by Steve Müller [ 23/Dec/13 ]

Benjamin Eberlei Is there anything to do here? Should the missing events be added or is that not necessary?





[DBAL-547] Evaluate moving away from prepared statements to sprintf() Created: 18/Jun/13  Updated: 18/Jun/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: New Feature Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

We are using prepared statements and parsing on top at the moment to implement things like array param quoting and such.

We should evaluate if it makes sense to introduce a strategy for preparing/parsing queries and make sprintf() usage a default in 3.0 instead of using prepared statements. At least for MySQL the PHP Driver guys are arguing for this kind of processing for years.






[DBAL-512] Update schema not working on MsSql due to no support for alter identity Created: 06/May/13  Updated: 16/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.2.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Flip Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Symfony 2.1, SQL Server 2008, driver: pdo_sqlsrv



 Description   

When running: php app/console doctrine:schema:update --force

[Doctrine\DBAL\DBALException]
An exception occurred while executing 'ALTER TABLE tableName ALTER COLUMN id INT IDENTITY NOT NULL':

SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'IDENTITY'.

According to this stackoverflow http://stackoverflow.com/a/1049305/1833322 MSSQL does not support this query.



 Comments   
Comment by Steve Müller [ 25/Nov/13 ]

Obviously SQL Server doesn't support this. So what solution would you expect here? IMO such a scenario should be avoided as there is no reasonable way to deal with changing identity columns. I guess most people are not even aware of this. The solutions available are very risky (creating new schema and then migrating data for example) and can take very long to be finished and is extremly error prone. I don't know if we should support one of those workarounds.

Comment by Flip [ 27/Nov/13 ]

The bug is about the SQL statement that does not work.
1. One solution could be to prevent generating this statement and issue a warning instead. (not preferred)

Two other solutions are mentioned in that Stackoverflow post I mentioned, namely:
2. Create a new table with identity & drop the existing table
3. Create a new column with identity & drop the existing column

That is just the way things have to be done in SQL Server (unfortunately), so it wouldn't be a workaround. I think this is a very reasonable way and in fact similar solutions to similar problems have already been implemented into Doctrine. See http://www.doctrine-project.org/blog/doctrine-2-4-released.html "ALTER TABLE support for SQLite (by hason) by creating new tables, moving all the data and then renaming."

Comment by Benjamin Eberlei [ 13/Dec/13 ]

Flip not many people actually use Sqlite in production, its more of a "play database" with Doctrine, wheras SQL Server carries much more weigh. I don't want to implement this kind of workaround, Steve Müller what do you think is safe here?

Comment by Flip [ 13/Dec/13 ]

Not to argue .. but it's not a workaround

Really .. if it is ever implemented it would have to be in a few steps because SQL Server does not accept this one-line command like that. So basically when you don't want this solution it means that will never be implemented.

Talking about safe .. mind that the safety should also be ensured in other area's like 1. don't run --force on a production database 2. make backups. So when regarding the safety it's a very narrow area to take a look at. The operation could be setup in such a way that if the new table/column could not be created then the old one doesn't get deleted. That way you have always the safety like a database transaction.

I guess no point in making a Proof of Concept, because the general outline of the problem is actually not that complicated ..

Thx guys for the input on this issue.

Comment by Steve Müller [ 16/Dec/13 ]

Benjamin Eberlei we could of course use one of the approaches Flip mentioned, but the question is what to do in case something goes wrong during one of the steps required to alter an IDENTITY column. If it is acceptable that there exist "dead" tables/columns in case something goes wrong, we could try implementing that.





[DBAL-477] Just doublequote all schema names and field names in PostgreSQL sql command generation, and the same for MySQL Created: 28/Mar/13  Updated: 24/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: Platforms, Schema Managers
Affects Version/s: 2.3.2
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: jos de witte Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mysql, postgresql
Environment:

Any PostgreSQL environment


Issue Links:
Reference
is referenced by DBAL-96 Make approach towards identifier quot... Open

 Description   

Generation of any SQL command to the database (From entities or migration versions) does not quote all the reserved keywords (For example a fieldname `right`.

Simple fix that always works: double-quote dbname, schemaname and fieldname

e.g "dbsecurity"."userschema"."users" or "tblusers"

MySQL : use the ` sign.

e.g `security`.`users` or `tblusers` (No support for schemas since I last checked some time ago)



 Comments   
Comment by Steve Müller [ 24/Jun/13 ]

If those are reserved keywords, they should be added to the "PostgreSQLKeywords" class and they will be quoted by Doctrine. As far as I can see those keywords mentioned are not present in this class. Maybe there is something missing?





[DBAL-474] SchemaManager / Connection on PostgreSQL platform does not respect filterExpression for sequences Created: 27/Mar/13  Updated: 19/Mar/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.2.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: jos de witte Assignee: Steve Müller
Resolution: Unresolved Votes: 1
Labels: postgresql, schematool
Environment:

Windows & Linux



 Description   

Dear Symfony team,

the filterExpression on AbstractSchemaManager seems not to work for sequences.

This only happens under postgres.

It seems the way the sequences are handled are the culprit: It tries to get min_value etc of sequences without matching sequence names to the filter expression in advance.

If for example access to the sequences is denied, (Different schema without permissions for the current entity manager), any higher-level ORM operations like generating migration versions fail.

--------------------- UPDATE

the context is when using migrations. Positive regexp expressions do not limit the migration to a single schema. eg ^schemaname.$
Instead, all sequences on the current database are returned.
When trying to limit a migration to a single schema consecutively this doesn't work.
We are using a per-schema connection, so this results in a lot of hassle for us.



 Comments   
Comment by Benjamin Eberlei [ 14/Apr/13 ]

Can you paste an exception trace? I see that filtering is applied to sequences, but your description seems to indicate this happens due to an SQL query much earlier?

Comment by jos de witte [ 24/Apr/13 ]

Dear Benjamin,

the context is when using migrations. Positive regexp expressions do not limit the migration to a single schema. eg ^schemaname.$
Instead, all sequences on the current database are returned.
When trying to limit a migration to a single schema consecutively this doesn't work.
We are using a per-schema connection, so this results in a lot of hassle for us.

Comment by Steve Müller [ 06/Jan/14 ]

jos de witte I think your issue got fixed in commit: https://github.com/doctrine/dbal/commit/8beb732fe9d5cd40a0d677f250d2be325482744f
This patch was first introduced in 2.3. Can you please confirm that this is fixed? Otherwise can you please provide a concrete example so that we can reproduce you issue?

Comment by Arnout Standaert [ 29/Jan/14 ]

I believe we are bumping into the same issue. Our webapp uses Migrations, but for our webapp we are limited to a certain schema within a bigger PostgreSQL database. We only have permissions on our own schema and public.
Now, listSequences in AbstractSchemaManager does filter the asset names correctly with the mentioned fix.

But the problem is with the step before, _getPortableSequencesList (see line 135 of AbstractSchemaManager):

        return $this->filterAssetNames($this->_getPortableSequencesList($sequences));

This function goes out and does a _getPortableSequenceDefinition on every sequence in the unfiltered list. For every sequence, the _getPortableSequenceDefinition in PostgreSqlSchemaManager performs a SELECT:

        $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));

Now, our user role in the database doesn't have SELECT permissions on these sequences in other schemas, so the migration fails with a privilege error.

There should be some kind of filtering on the sequence list BEFORE the SELECT statement in the _getPortableSequenceDefinition function are performed, no?

Comment by Arnout Standaert [ 30/Jan/14 ]

I currently have a workaround running locally, which filters the sequences list before creating the PortableSequence's. This is probably hackish and a poor workaround, just posting here as a temporary solution and further illustration of the problem.

Altered line 135 in AbstractSchemaManager:

        return $this->_getPortableSequencesList($this->filterSequenceNames($sequences));

I added function filterSequenceNames() in AbstractSchemaManager for appropriate sequence filtering:

    /**
     * Filters sequence names if they are configured to return only a subset of all
     * the found elements.
     *5
     * @param array $sequenceNames
     *
     * @return array
     */
    protected function filterSequenceNames($sequenceNames)
    {
        $filterExpr = $this->getFilterSchemaAssetsExpression();
        if ( ! $filterExpr) {
            return $sequenceNames;
        }
        
        return array_values ( array_filter($sequences, function ($sequenceName) use ($filterExpr) {
                $sequenceName = $sequenceName["schemaname"].".".$sequenceName["relname"];
                return preg_match($filterExpr, $sequenceName);
            })
        );

    }

After these modifications, doctrine:migrations:migrate operations complete succesfully, even with our limited-permission database account.

Comment by Steve Müller [ 21/Feb/14 ]

Arnout Standaert Your fix looks promising and reasonable. Can you create a PR on the DBAL repo for that?

Comment by Viktor Sidochenko [ 15/Mar/14 ]

Why this fix is not in master?

Comment by Steve Müller [ 15/Mar/14 ]

Viktor Sidochenko because nobody has fixed it yet Feel free to provide a patch on GitHub.

Comment by Arnout Standaert [ 17/Mar/14 ]

I haven't gotten around to doing the PR on GitHub yet, I'm not yet too familiar with that.
I'll try to find some time for this the coming days.

Comment by Viktor Sidochenko [ 17/Mar/14 ]

Will be good. I`m not professional developer to make patches to upstream. So just voted for this issue.

Comment by Steve Müller [ 19/Mar/14 ]

Patch supplied in PR: https://github.com/doctrine/dbal/pull/546
jos de witte, Arnout Standaert, Viktor Sidochenko can you please test if the supplied PR fixes the problem?





Deprecate Migrations in favor of stable tools (DBAL-602)

[DBAL-603] DbDeploy Support Created: 27/Feb/13  Updated: 12/Sep/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Sub-task Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   
  • DbDeploy Diff Generation
  • Schema Serialization
  • SchemaTool gets new event when diff is applied, then you can update a "stable" schema xml. On Generation new db deploy script, use current schema vs stable schema vom disc.





[DBAL-449] [GH-274] Support column charset/collation on capable platforms Created: 19/Feb/13  Updated: 19/Feb/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

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


 Description   

This issue is created automatically through a Github pull request on behalf of adrienbrault:

Url: https://github.com/doctrine/dbal/pull/274

Message:

Basically the same feature wanted as in #245






[DBAL-404] Support of index length for text fields Created: 29/Dec/12  Updated: 29/Dec/12

Status: Open
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: Maksim Lunochkin Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 2
Labels: None


 Description   

For table with text field description with index on it generated query is:

CREATE TABLE t (id INT UNSIGNED AUTO_INCREMENT NOT NULL, description LONGTEXT DEFAULT NULL, number INT DEFAULT NULL, INDEX index2 (description), INDEX index3 (description, number), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB

This query lead to error, because for index on text field absented key length.






[DBAL-390] Wrap SQL for Selects in an Object for Metadata? Created: 23/Nov/12  Updated: 23/Nov/12

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None





[DBAL-357] Missing way to set types for CAST declaration Created: 04/Oct/12  Updated: 04/Oct/12

Status: Open
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.2, 2.3
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: Pete Sisson Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

For a query such as

x = CASE WHEN id=$1 THEN CAST($2 AS int)

there doesn't seem to be a way to correctly assign the type across multiple platforms. E.g. Postgres required "int" but mysql just requires "unsigned". Attempting to use "int" here with mysql will fail.

The method Doctrine\DBAL\Platforms method getIntegerTypeDeclarationSQL will return something like "INT unsigned" for sql, which also fails.






[DBAL-348] [GH-202] Fixed DBAL-139 Oracle's sequences with NOCACHE Created: 22/Sep/12  Updated: 22/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: New Feature Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of AduroIdea:

Url: https://github.com/doctrine/dbal/pull/202

Message:



 Comments   
Comment by Benjamin Eberlei [ 22/Sep/12 ]

A related Github Pull-Request [GH-202] was closed
https://github.com/doctrine/dbal/pull/202

Comment by Benjamin Eberlei [ 22/Sep/12 ]

A related Github Pull-Request [GH-202] was reopened
https://github.com/doctrine/dbal/pull/202

Comment by Benjamin Eberlei [ 22/Sep/12 ]

A related Github Pull-Request [GH-202] was closed
https://github.com/doctrine/dbal/pull/202

Comment by Benjamin Eberlei [ 23/Sep/12 ]

A related Github Pull-Request [GH-204] was opened
https://github.com/doctrine/dbal/pull/204

Comment by Doctrine Bot [ 22/Dec/13 ]

A related Github Pull-Request [GH-204] was closed:
https://github.com/doctrine/dbal/pull/204





[DBAL-324] SchemaManager should first look into comment instead of infer the type first. Created: 17/Aug/12  Updated: 03/Jan/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Guilherme Blanco Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None


 Description   

When using schema tool, Doctrine tries to infer the Doctrine type via the mapped types in Platform.
It should first try to read from the comment; if found, ignore the Database type inference.



 Comments   
Comment by Benjamin Eberlei [ 29/Aug/12 ]

Why is this a bug? Can you say some more about why we need to do this and what error occurs?

Comment by Guilherme Blanco [ 29/Aug/12 ]

This issue is strictly correlated to the commit I've done here: https://github.com/doctrine/dbal/commit/e25c774dde971dc4afd40648e9ccd0af53b34ce9

Mainly, we may have legacy database that we do know how Doctrine should operate. Under this circumstance, we may want to add a comment to the field defining the Doctrine DBAL type.
Even though after doing this, Doctrine still complains (in my situation, a SET column type) that it's unable to handle this column type.

That happens because MySQL Schema Manager (and others) first looks for the column type:
https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/MySqlSchemaManager.php#L112
https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/AbstractPlatform.php#L312
which automatically fails.

But if we first try to look for the commented data type:
https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php#L884
And forget about the rest if it finds one definition there, it would allow even unknown data types to be managed instead of forcing to define the DBAL Type compatible.

Comment by Steve Tauber [ 14/May/13 ]

This also applies for a type of yaml.
Example:

/** @Column(type="yaml") */
protected $data = array();

./scripts/doctrine orm:schema-tool:update --dump-sql
ALTER TABLE meta CHANGE data data LONGTEXT NOT NULL;

Very frustrating.... Might be related to http://www.doctrine-project.org/jira/browse/DBAL-42

Comment by Steve Müller [ 03/Jan/14 ]

Steve Tauber You have defined a custom data type. You need to override `Type::requiresSQLCommentHint()` to return true in your custom data type, otherwise Doctrine cannot infer the custom type mapping from the column comment and the comparator will always detect changes between Yaml and LONGTEXT. This is not directly related to this issue here I think.
Guilherme Blanco I don't really get the issue you describe here. Is it maybe fixed already? Otherwise can you maybe try to explain again so that I can fix that?





[DBAL-321] [GH-184] Added INSERT support to dbal QueryBuilder Created: 13/Aug/12  Updated: 20/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: New Feature Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of Tim-Erwin:

Url: https://github.com/doctrine/dbal/pull/184

Message:

See also http://www.doctrine-project.org/jira/browse/DBAL-320
Please review and commit as fits.



 Comments   
Comment by Doctrine Bot [ 20/Dec/13 ]

A related Github Pull-Request [GH-420] was closed:
https://github.com/doctrine/dbal/pull/420

Comment by Doctrine Bot [ 20/Dec/13 ]

A related Github Pull-Request [GH-184] was closed:
https://github.com/doctrine/dbal/pull/184





[DBAL-294] [GH-163] [WIP] Upsert support protoype. Created: 29/Jun/12  Updated: 20/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: New Feature Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of kimhemsoe:

Url: https://github.com/doctrine/dbal/pull/163

Message:



 Comments   
Comment by Doctrine Bot [ 20/Dec/13 ]

A related Github Pull-Request [GH-163] was closed:
https://github.com/doctrine/dbal/pull/163





[DBAL-235] Column order is not synchronized by Comparator Created: 08/Mar/12  Updated: 19/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.2.1
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: Artem Goutsoul Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: None
Environment:

MySQL



 Description   

Comparator does not synchronize column order. It would be great if it did.



 Comments   
Comment by Chris Woodford [ 02/Apr/12 ]

I assume that this means adding AFTER to any ALTER ADD statements? if so, this is something that i would really appreciate as well

Comment by Artem Goutsoul [ 03/Apr/12 ]

Yup, that's exactly what I meant!

Comment by Steve Müller [ 18/Dec/13 ]

AFAIK only MySQL supports changing column order after table creation. All other vendors need a table recreation for this. Why exactly do you need this? I cannot think of a use case where the order in which columns are stored in the table is of any importance. And as this is a MySQL only specific feature which needs some heavy calculations in the comparator, I doubt this will be implemented...

Comment by Artem Goutsoul [ 19/Dec/13 ]

It's very convenient to use MySQL column order so that the columns show up in some sane order in DB management tools.
Also an application can show some tables in a sane way without explicit column order specification.

We are currently using it widely in our app. Our ORM is actually picking up the object specifications directly from the database and it's not specified in the application code.

Comment by Steve Müller [ 19/Dec/13 ]

Artem Goutsoul I get the point. But as I already stated only MySQL supports column reordering without requiring a table recreation AFAIK. How would you expect other platforms to behave in column reordering scenarios? I think it's weird if only MySQL platform can react to column reorderings and we have to adjust the comparator for a just a single platform. Benjamin Eberlei what would you say?





[DBAL-125] Add Informix Support Created: 08/Jun/11  Updated: 11/Jun/11

Status: Open
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major
Reporter: Adolfo Flores Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Linux, Unix & Windows



 Description   

Today exist many customers with Informix 11.50 and they will deveoplment with php+simphony, but doctrine will be an alternative inside the framework but it isn't supported.
My customers can't migration ( they are happy with informix) your application and he need a framework as simphony and an abstration of your environment as Doctrine.
There is a possibility to support Informix in Doctrine?
Thanks and regards

Adolfo Flores
Consultant



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

There is a possibility to support Informix, this is described here:

http://www.doctrine-project.org/docs/dbal/2.0/en/reference/supporting-other-databases.html

I don't know informix and never worked with it, if you would volunteer to code this i would be enternelly grateful I could help you with the details of implementation.

I think a working prototype can be implemented in 4-6h of time.

Comment by Adolfo Flores [ 08/Jun/11 ]

good idea, I am an expert in informix database and I may need support in doctrine.

Is there any template to follow? I have read the required structure in the url given, it seems feasible.

What next step?

I am installing Informix, php 5.3 on RHES 5.2 All 32-bit

Regards,

Adolfo

Note: .I have no time basis, so that one week would be a reasonable time for this activity

Comment by Benjamin Eberlei [ 11/Jun/11 ]

I added some more details to the chapter, here is the part:

Implementation Steps in Detail
------------------------------

1. Add your driver shortcut to class-name `Doctrine\DBAL\DriverManager`.
2. Make a copy of tests/dbproperties.xml.dev and adjust the values to your driver shortcut and testdatabase.
3. Create three new classes implementing ``\Doctrine\DBAL\Driver\Driver``, ``\Doctrine\DBAL\Driver\Statement``
and ``Doctrine\DBAL\Driver``. You can take a look at the ``Doctrine\DBAL\Driver\OCI8`` driver.
4. You can run the testsuite of your new database driver by calling "cd tests/ && phpunit -c myconfig.xml Doctrine/Tess/AllTests.php"
5. Start implementing AbstractPlatform and AbstractSchemaManager. Other implementations should serve as good example.

Essentially Driver, Statement are the PDO equivalents of "PDO" and "PDOStatement", they even have the exact same API. OCI8 is a good example to see how it works. If Informix works with PDO then its even simpler, take a look at Doctrine\DBAL\Driver\PDO namespace, you don't need to implement the connection and statement then, just the driver taking care of the configuraiton.

The most complicated thing is to get the Platform and SchemaManager to work. Both are responsible for SQL generation for DDL statements for example. It makes sense to run the phpunit testsuite and fix the bugs when occuring until all tests pass.

Also looking at the platform and schema managers of other databases is a very good help





[DBAL-124] OCI8 Adapter (convertPositionalToNamedPlaceholders) Statemachine doesn't consider comments and strings Created: 20/May/11  Updated: 28/Jun/11

Status: Open
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.0.5
Fix Version/s: None

Type: Improvement Priority: Major
Reporter: Carsten Hetzel Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

SQL statements may contain question marks in strings, inline comments or comment blocks. The current implementation of the method "convertPositionalToNamedPlaceholders()" doesn't consider these implications and falsely replaces them by named bind variables.

Replacement code with example:

<?php
$s = '-- Testkomm?ntar
	select	/* ? *//* ??? */ ?||\'H"al?l"o?\' as "h?""llo" union /* "? Kommentar" \' */
	/* ?" */ select \'/*Hallo\'||to_char( ? ) union select \'--Welt\'
	union--
	select ?';

echo 'In: ' . $s . PHP_EOL;

$bind = 0;
$skip = array( '--' => PHP_EOL, '/*' => '*/', '"' => '"', "'" => "'" );
for( $i = 0; $i < strlen( $s ) /* size of string might change! */; /* yes, no increment here! */ )
{
	// Skipping comments and literals
	foreach( $skip as $begin => $end )
	{
		$matches = substr_compare( $s, $begin, $i, strlen( $begin ) );
		if( $matches !== false && $matches == 0 )
		{
			$pos = strpos( $s, $end, $i+strlen( $begin ) );
			// echo "Found $begin, skipping at $i to $end at $pos" . PHP_EOL;
			if( $pos === false )
			{
				// No more data or illegal statement - anyway: no more replacements!
				// echo "EOD" . PHP_EOL;
				break 2;
			}
			$i = $pos + strlen( $end );
			continue 2; // Ensure we match /*..*//*..*/, '''' or """" - that's why we don't ++$i in the for-loop!
		}
	}
	if( $s[$i] == "?" )
	{
		// Positional to named
		// echo "Replace $bind" . PHP_EOL;
		$r = ':name' . ++$bind;
		$s = substr_replace( $s, $r, $i, 1 );
		$i += strlen( $r );
	}
	++$i;
}

echo 'Out: ' . $s . PHP_EOL;


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

This algorithmus is painfully slow. There has to be something better, why do you need to foreach the loop inside the for?

Comment by Carsten Hetzel [ 28/Jun/11 ]

Ok, sent you a PM but I'll put my response here, too:

"Painfully slow" at what circumstances? Benchmarks?

This was meant as an example implementation to show the problems of the current implementation - there's always room for improvement.

And never forget: Premature optimization is the root of all evil! ;-p

You want speed - try this one:

<?php
$sql = '-- Testkomm?ntar
        select  /* ? *//* ??? */ ?||\'H"al?l"o?\' as "h?""llo" union /* "? Kommentar" \' */
        /* ?" */ select \'/*Hallo\'||to_char( ? ) union select \'--Welt\'
        union--
        select ?';

echo 'In: ' . $sql . PHP_EOL;

$map = array();
for( $cnt = 0; $cnt < 1000; ++$cnt )
{
        $bind = 0;
        $s = $sql;

        if( isset( $map[$s] ) )
                continue;

        $strlen = strlen( $s );
        for( $i = 0; $i < $strlen /* size of string might change! */; /* yes, no increment here! */ )
        {
                $c1 = $s[$i];
                $c2 = isset( $s[$i+1] ) ? $s[$i+1] : '';
                if( $c1 == '-' && $c2 == '-')
                {
                        $pos = strpos( $s, PHP_EOL, $i+2 );
                        if( $pos === false )
                        {
                                break 2;
                        }
                        $i = $pos + 2;
                }
                elseif( $c1 == '/' && $c2 == '*' )
                {
                        $pos = strpos( $s, '*/', $i+2 );
                        if( $pos === false )
                        {
                                break 2;
                        }
                        $i = $pos + 2;
                }
                elseif( $c1 == '"' )
                {
                        $pos = strpos( $s, '"', $i+1 );
                        if( $pos === false )
                        {
                                break 2;
                        }
                        $i = $pos + 1;
                }
                elseif( $c1 == "'" )
                {
                        $pos = strpos( $s, "'", $i+1 );
                        if( $pos === false )
                        {
                                break 2;
                        }
                        $i = $pos + 1;
                }
                elseif( $c1 == "?" )
                {
                        // Positional to named
                        // echo "Replace $bind" . PHP_EOL;
                        $r = ':name' . ++$bind;
                        $s = substr_replace( $s, $r, $i, 1 );
                        $rLen = strlen( $r );
                        $i += $rLen;
                        $strlen += $rLen - 1;
                }
                else
                        ++$i;
        }
        $map[$sql] = $s;
}
echo 'Out: ' . $map[$sql] . PHP_EOL;

ATTENTION: Each "break 2;" means, you have an invalid statement! Do, whatever the statement class is supposed to do in this case!

Regards, Carsten





[DBAL-95] Interbase/Firebird support Created: 26/Feb/11  Updated: 12/Mar/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 4
Labels: None


 Description   

Implemented support for Interbase/Firebird dialects






[DBAL-76] PostgreSQL Platform list* SQL code is in need of serious love Created: 12/Dec/10  Updated: 08/Jun/11

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

The Postgres Schema code is very hard to read and inconsistent across the board. Some use pg_class, some pg_tables. Namespaces /Schema are not always properly checked for. There should be a really unified way on how to approach schema query issues.



 Comments   
Comment by Denis [ 08/Jun/11 ]

I'm not sure what this ticket is about exactly, but...

"Namespaces /Schema are not always properly checked for."

Usually, one would not want to specify them and set the search_path instead. Or are you meaning the schema analysis queries used internally? (If so, yes, it kind of sucks in that case, but note that there are a bunch of *_is_visible() methods, e.g. pg_catalog.pg_table_is_visible(rel.oid) which will strip out invisible schemas directly. This may be simpler than injecting schema references all over the place in that it also processes permissions.)

Also, note that PG has a whole bunch of pg_catalog views, which are available in the information_schema.





[DBAL-57] Handling of Quoted Elements Created: 30/Oct/10  Updated: 24/Jun/13

Status: Open
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.0-BETA4
Fix Version/s: None

Type: Improvement Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Reference
is referenced by DDC-832 Not quoted class name when updating e... Resolved

 Description   

Handling of Quoted Table, Column (etc) names is not as good as it could be. Any input is currently accepted, however it is not processed further and used in conjunction with the platform.



 Comments   
Comment by Steve Müller [ 24/Jun/13 ]

May this be an issue fixed by https://github.com/doctrine/dbal/pull/302 ?





[DBAL-40] Transparent table&column names escaping Created: 05/Aug/10  Updated: 26/Sep/13

Status: Open
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3, 2.4, 2.4.1
Fix Version/s: None

Type: Improvement Priority: Major
Reporter: Jan Tichý Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 12
Labels: None

Issue Links:
Reference
relates to DBAL-96 Make approach towards identifier quot... Open

 Description   

Hello, I would like to re-open the discussion about automatic transparent escaping of all table/column names sent from DBAL to database. It was already discussed in http://www.doctrine-project.org/jira/browse/DDC-88 without any satisfactory result.

Why do I have to quote any reserved word used in table or column name? Why Doctrine doesn't do this automatically for all table and column
names used in generated SQL queries?

Before you start to explain how complicated it is and what problems you will be faced with, try to look at excellent DIBI database layer - how it acts in this way - it's behaviour is very cool. Unfortunally at the moment the full documentation is in czech only, but here is a brief automatic google-translation to english - http://dibiphp.com/en/quick-start.

My suggestion to Doctrine 2 ORM/DBAL solution is:

1. Developer should never care about any escaping or avoiding any reserved words - it is not his business, the DBAL shoult solve it transparently and safely.

2. So there should be no need and even no possibility to add any quotation chars in @column or @table annotations as well as in DQL queries. ORM layer has nothing to do with escaping, it is all a business of the DBAL layer. Current possibility for manual escaping the names in mentioned annotations is totally wrong and should be discontinued.

3. DBAL should escape ALL table and column names transparently and automatically. There should be ne option to enable or disable the escaping, there is no reason for disabling it.

4. The escaping should be performed just in the final translation of DBAL queries to native SQL query, not earlier. This is the right place to do that.

So what do you think about that?



 Comments   
Comment by Roman S. Borschel [ 05/Aug/10 ]

My point of view (and the reason for the current implementation) is as follows:

  • Using reserved words is bad practice.
  • Quoting everything is like hitting all the SQL with a huge big hammer just to hit the 1% of reserved words (which are again, bad practice), thus overkill.
  • Quoting everything bloats the generated SQL (just to hit the 1% of reserved words which are bad practice to begin with)
  • Quoting everything automatically is like hiding the fact from developers that they use reserved words, thus hiding a bad practice and silently encouraging usage of reserved words in new database schemas. This is not desirable.
  • Quoting reserved words has more effects than simply making the database "accept" that identifier. It affects the case-sensitivity and that in a very inconsistent way across databases and operating systems (See http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html , especially the conclusion). You say there is no reason for disabling it but in fact there are a lot of reasons to do so, so many that it is disabled by default in MDB2 and discouraged to enable it.

So, supporting selective quoting in the name of a (slightly) better interoperability with legacy schemas looked (and still looks) like the best solution for us. The support is limited, explicit, does not require much implementation or overhead and does not unnecessarily bloat the SQL.

There is only one solution for reserved words: not using them. Quoting is a workaround, not a solution and especially not a good one.

ps: I really wish quoting reserved words would not be available in SQL It's not available in most programming languages and noone cares, people just don't use reserved words, because they simply can't.

Comment by Jan Tichý [ 05/Aug/10 ]

Hi Roman, thank you very much for your response! I storngly disagree with most of your points .

There is no doubt that using reserved words is bad practice - FROM THE VIEW OF DATABASE SYSTEM.

But we are discussing about ORM and DBAL. One of the biggest goals of ORM/DBAL is to provide transparent usage of the storage behind the scene. No matter if it is MySQL or PostgreSQL or even maybe something completely diferent.

The ORM/DBAL layer should prevent me from any specifics of particular storage as much as possible. I don't want to remember (and I never should to) that I cannot create entity Order because "order" is reserved word in some weird technology far away from me as ORM programmer.

It is strictly consistent with what you have written above in your PS - "It's not available in most programming languages and noone cares, people just don't use reserved words, because they simply can't" - just consider Doctrine 2 to be another programming language - and there is no real systematic reason in Doctrine 2 itself to prevent developers create entities named "Order".

Here is an analogy - It is the same as if you would say that you cannot use associative arrays in PHP because C-language or Assembler behind PHP doesn't support associative arrays. Yes, they don't support them but it is the responsibility of PHP to provide them. In the same way I don't want to respect this weird limitations of particular RDBMS behind Doctrine 2. This is Doctrine's responsibility to transparently cover the limitation.

Moreover, when list of registered keywords is different from one to the other RDBMS, so the naming of entities is strongly dependent on current database server.

Moreover, when I realize that I have used a registered keyword as lately as an error returns from database engine, not earlier.

I suppose here is probably no risk of SQL injection, but I feel the current Doctrine 2 acting to be "vulnerable" in very similar way, on principle. Simply - you are sending an unescaped piece of SQL query to the database without any warranty what it is. And sometimes it fails, sometimes not. From this view I don't consider overall escaping to be overkill at all, I consider it to be a necessity.

I am strongly convinced that developer working upon DBAL or even ORM layer should never think about such naming limitations and he even shouldn't know anything about reserved words in his particular DBMS.

Now to mentioned problems with case sensitivity. Resulting from the fact that Doctrine 2 entity names are case insensitive I belive that all table definitions and SQL queries comming from Doctrine 2 to database should act as case insensitive too. And that the only practicable way is to normalize (lowercase) all table and column names just on DBAL side before it is passed as SQL query to database.

Jan

Comment by Benjamin Eberlei [ 05/Aug/10 ]

There is actually a very good reason for not quoting. Oracle columns behave differently in their internal structure when escaped.

for example:

/**
  * @column(type="integer")
 */
private $foo;

With quoting it would lead to a column "foo" being lower-cased IN the database and even returned so from resultsets. Without casing it would be a column "FOO". We would essentially need to implement lots of glue code just to get this annoying Oracle feature to work and i think Postgres has the same with lower-cased columns.

Comment by Roman S. Borschel [ 05/Aug/10 ]

@"Hi Roman, thank you very much for your response! I storngly disagree with most of your points"

I guess we can agree to disagree then

@"But we are discussing about ORM and DBAL. One of the biggest goals of ORM/DBAL is to provide transparent usage of the storage behind the scene. No matter if it is MySQL or PostgreSQL or even maybe something completely diferent."

Actually, no, "hiding" the storage completely from the developer is not the goal just as it is not the goal to "hide" SQL. There is an object model on one side and a relational database on the other side. The goal is to provide a mapping between them which is not the same as "hiding" one from the other. In order to create good applications that use ORM technology you need to know both very well, OOP and relational databases. The goal is not to make relational database knowledge "unnecessary". This only results in inefficient use of the databases. The goal is to give people who know both sides equally well a tool to map between the two. Not even "portability" between different relational database vendors is a main goal of an ORM technology, it is just obvious to provide assistance with that as part of the mapping.

@"and there is no real systematic reason in Doctrine 2 itself to prevent developers create entities named "Order".

Noone prevents you from naming domain classes anything you want. Class naming is different from table naming. That the table name defaults to the class name is just that, a default, that can and should be changed if necessary.

@"Moreover, when list of registered keywords is different from one to the other RDBMS, so the naming of entities is strongly dependent on current database server."

Correct, and if you want to create a portable application that works, and will be deployed on, a different set of vendors, you need to have some knowledge of these databases and consider their characteristics. An ORM/DBAL technology does not give you any guarantee for complete and transparent portability between vendors and especially not that it will perform equally well on all of them. The ORM/DBAL technology helps you for the most part in a lot of cases with portability issues but it is no free ticket.

@"I suppose here is probably no risk of SQL injection, but I feel the current Doctrine 2 acting to be "vulnerable" in very similar way, on principle. Simply - you are sending an unescaped piece of SQL query to the database without any warranty what it is. And sometimes it fails, sometimes not. From this view I don't consider overall escaping to be overkill at all, I consider it to be a necessity."

Do not confuse identifier quoting with quoting/escaping of special characters as it is used for security reasons on input. Identifier quoting is absolutely not a necessity, it is a workaround for using otherwise reserved words as schema element names. Speaking of goals, it is neither a "goal" of ORM/DBAL technology to completely remove the possibilities of SQL injections. You can't. It'll always be possible with wrong usage.

@"I am strongly convinced that developer working upon DBAL or even ORM layer should never think about such naming limitations and he even shouldn't know anything about reserved words in his particular DBMS."

And I am strongly convinced that a developer working with a DBAL/ORM should know the underlying databases pretty well.

I think you're really not aware of all the consequences it has across different database vendors to quote every identifier. If not for developers using Doctrine, you cause at least any developer or application pain that does not access the database through Doctrine and is thus feels the full pain of case-sensitivity and mandatory quoting you enforced on the whole schema. Ubiquitious access to the data is actually a strong point of a relational database and it is far from uncommon that the same database is accessed by many parties.

I think the approach taken by DIBI is a bad idea and even worse if there is no way to turn this behavior off. Do they have Oracle or DB2 users? I'm wondering what the sysadmins behind these databases might think if they see this quoting nightmare since to my knowledge this is considered bad practice among them as well.

Yes, we're disagreeing on many points but if you really think identifier quoting is a good idea then you're ignoring a whole lot of prior experience (not only mine).

Comment by Lukas Kahwe [ 05/Aug/10 ]

I was one of the lead developers of MDB2 and we just ran into tons of issues when we overly aggressively did identifier quoting by default. even the option caused lots of headaches. furthermore I agree that the ORM is not about turning an RDBMS into an Object Database, but instead to make a mapping possible. In this vain using reserved words or making all identifiers case sensitive will be a big pain for the people that do work one level lower aka the DBA's. heck even as a developer I frequently work on the DB's command line.

Now as for helping people prevent issues with reserved words. Back then I added some reserved word checking into MDB2_Schema. Obviously its hard to really keep track of all of the different reserved words for all RDBMS. Maybe its possible to work with this guy for this: http://www.petefreitag.com/item/290.cfm This way it could be possible to validate if the names chosen in the models will not cause issues with a certain list of RDBMS.

Comment by Benjamin Eberlei [ 07/Aug/10 ]

Reserved words checking sounds to be a fair compromise!

Comment by Jan Tichý [ 30/Aug/10 ]

Hello, thank you all for your responses.

This helped me understand much about Doctrine 2 basic objectives - especially that it is designed mainly to "make a mapping possible" only, not to be as much as possible transparent layer between database and application. And even if I don't like this conception (because I personally think ORM should provide all such features - like automatic reserved keywords escaping - to make the particular database as transparent as possible), at the same time I fully understand all metioned arguments for doing things in such way. Thank you again.

Comment by Damian Boune [ 17/Jan/11 ]

I would like to state an agreement with the OP.

I understand where there are difficulties in handling reserved words and backtick/quoting, and certainly one should always avoid the use of reserved words in their own schema designs. This is a given when one is able to exert control.

At present I am working on a project in which I am dealing with an outside database where I have no control over the schema, nor am I able to push the remote into making the most sensible changes to their schema. I must live with what they provide.

DBAL presents me with a set of invaluable tools that can not be used as-is, because it lacks the ability to handle quoting when generating schema sql. I'm sure there are some other places where I will find this lacking as well. This is disappointing.

Regardless of what we as developers should do when designing our own schema, we still need to be able to work and play with others who may not follow the same common sense conventions.

Edit:
My temporary quick solution to just "make it work", was to modify AbstractAsset::getQuotedName and force the use of $platform->quoteIdentifier. It did the trick for now until a more suitable solution presents itself.

Comment by Francesco Montefoschi [ 03/Feb/11 ]

"its hard to really keep track of all of the different reserved words for all RDBMS"

That's the main point for me.

Comment by Adrian Rudnik [ 26/Apr/11 ]

@Damian thanks for the hint. I just ran into a similar situation.

Not every project is a startup. I tried to use doctrine2 on a customers database for a small web ui. Well I told them to rename their `iso3166-1` table and `alpha-2` field, then we had a good laugh. We made the mapping possible but i'll remember the one thing i learned: doctrine did not help, guide, prevent or cared at all. It did not even hesitate to spew invalid sql snippets when asked to dump. Its okay for me, but i've expected something more resilient from a DBAL.

Comment by Robert (Jamie) Munro [ 02/Feb/13 ]

What do you mean by "Quoting everything is like hitting all the SQL with a huge big hammer"? Is there a performance hit?

I have always quoted all names when working with PostGres. Not quoting them has always felt like not quoting strings in PHP (e.g. $foo[bar] instead of $foo['bar'] because unless the string is keyword or defined as a constant somewhere, you don't need to (although you will get a "Use of undefined constant" warning). In the early days of PHP, not quoting array keys was common example practise.

Comment by Marco Pivetta [ 02/Feb/13 ]

If you want quoting by default on everything we have a quoting strategy (in ORM) that you can use. I don't think quoting everything by default is a viable solution. Back in `Zend_Db` times this was eating up a lot of performance for no real reason. Users having a clean schema without horrors like columns called `order` or `group` should not be penalized because of users not using valid naming schemes.

Comment by Steve Müller [ 24/Jun/13 ]

Hello, if I understand correctly, the issue of quoting reserved keywords automatically is solved in https://github.com/doctrine/dbal/pull/302. Besides reserved keywords you can still decide quoting or not quoting identifier manually by passing quotes to the identifier or not.

Comment by Arthur Bodera [ 26/Sep/13 ]

It's still broken in 2.4.

PR 302 only selectively fixes indexes, PK and FK, but ALTER and all CRUD will still fail (and schema tool will produce invalid sql).

There is no performance hit, as all operations already hit `DefaultQuoteStrategy`.

Currently you have the following workarounds:

  • selectively add `quoted=true` to table and column names (ugh)
  • replace `DefaultQuoteStrategy` with strategy that quotes all identifiers.

Here is a class you can use: https://gist.github.com/Thinkscape/6713196

Comment by Arthur Bodera [ 26/Sep/13 ]

QuoteStrategies are not used for ALTER queries. This means that using the EagerQuoteStrategy mentioned above won't fix invalid ALTER queries generated by schema tool.

For ALTER to work, we need this merged:

https://github.com/doctrine/dbal/pull/379

Comment by Doctrine Bot [ 26/Sep/13 ]

A related Github Pull-Request [GH-379] was closed:
https://github.com/doctrine/dbal/pull/379





[DBAL-31] Move Schema related Creation code from AbstractPlatform to AbstractSchemaManager Created: 04/Jul/10  Updated: 28/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Move Schema related Creation code from AbstractPlatform to AbstractSchemaManager



 Comments   
Comment by Benjamin Eberlei [ 08/Aug/10 ]

Scheduled for Beta4

Comment by Roman S. Borschel [ 16/Aug/10 ]

If this task is more complex and requires larger refactorings we can re-evaluate it in a post-2.0 release.

Comment by Steve Müller [ 28/Dec/13 ]

Benjamin Eberlei Do you still remember what is meant by this ticket? Could be solved already...





[DBAL-873] [GH-571] Introduced a Transaction object Created: 18/Apr/14  Updated: 18/Apr/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of BenMorel:

Url: https://github.com/doctrine/dbal/pull/571

Message:

This is a first draft on the idea of a Transaction object, as suggested by @guilhermeblanco and @beberlei in doctrine/doctrine2#949.

This makes the following changes to `Connection`:

  • *Adds* the following methods:
  • `createTransaction()` : begins a transaction and returns the associated `Transaction` object
  • `getTransactionManager()` : returns the `TransactionManager`
  • *Deprecates* `beginTransaction()` in favour of `createTransaction()`
  • *Deprecates* the following methods, in favour of their counterparts on `Transaction`:
  • `commit()`
  • `rollBack()`
  • `setRollbackOnly()`
  • `isRollbackOnly()`
  • *Deprecates* the following methods, in favour of their counterparts on `TransactionManager`:
  • `isTransactionActive()`
  • `getTransactionNestingLevel()`
  • `setNestTransactionsWithSavepoints()`
  • `getNestTransactionsWithSavepoints()`

The new way of dealing with transactions is then:

$transaction = $connection->createTransaction();
$transaction->commit();

It also automatically propagates `commit()` and `rollback()` to nested transactions:

$transaction1 = $connection->createTransaction();
$transaction2 = $connection->createTransaction();
$transaction1->commit(); // will commit $transaction2 then $transaction1

Overall, it's not a complicated change, does not introduce any BC break, and passes all existing tests.

I'm looking forward to hearing what you think!






[DBAL-872] [GH-570] Add support for out cursor in OCI8 Created: 18/Apr/14  Updated: 18/Apr/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of Juliens:

Url: https://github.com/doctrine/dbal/pull/570

Message:

I don't know if it is the best solution, but it's a beginning.






[DBAL-867] Doctrine\DBAL\Driver\Connection should be marked as an internal interface Created: 16/Apr/14  Updated: 22/Apr/14

Status: Reopened
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Documentation Priority: Major
Reporter: Christophe Coevoet Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Currently, the main entry point Doctrine\DBAL\Connection is documented as a wrapper around Doctrine\DBAL\Driver\Connection.
This is very misleading and encourages other library to typehint against Doctrine\DBAL\Driver\Connection rather than Doctrine\DBAL\Connection. See https://github.com/symfony/symfony/pull/10720 for the original discussion.

However, the discussion in https://github.com/doctrine/dbal/pull/414#discussion_r7688765 implies that they should actually not be related together (but it cannot be fixed for BC reasons). The phpdoc should at least be changed



 Comments   
Comment by Guilherme Blanco [ 21/Apr/14 ]

This issue was fixed some time ago.

Commit reference: https://github.com/doctrine/dbal/commit/5fdedc4f8f304e8035580807bd36d59e97a87265

Comment by Steve Müller [ 22/Apr/14 ]

Guilherme Blanco How is your commit reference related to this issue?

Comment by Guilherme Blanco [ 22/Apr/14 ]

Steve Müller The suggestion about Doctrine\DBAL\Connection and Doctrine\DBAL\Driver\Connection started around the misleading support of ping and how to consistently support it across different drivers.
The commit reference I pointed out is Benjamin Eberlei's resolution to remove misleading phpdoc around ping support (which is also highlighted in the ticket as "phpdoc should at least be changed").
If there's anything else that is missing, I'm probably not seeing. All I've done is followed dbal's discussion. =\

Comment by Christophe Coevoet [ 22/Apr/14 ]

Your commit does not fix it at all. Benjamin Eberlei's comment was about the ping method only indeed. But he explained that Doctrine\DBAL\Connection should actually not be a Doctrine\DBAL\Driver\Connection except for legacy reasons, which is why makign it implement Doctrine\DBAL\Driver\PingableConnection was a bad idea even if it has a ping method.

My issue is related to the description of the class itself: https://github.com/doctrine/dbal/blob/aa2ed45ade6582a24e4f72f674f6989873d72112/lib/Doctrine/DBAL/Connection.php#L36
It still describes it as a wrapper around the driver connection, making other devs think that the right typehint in other libraries is the internal driver connection. See the discussion in the Symfony PR I linked

Comment by Guilherme Blanco [ 22/Apr/14 ]

So... it's reopened. I'll look into this later today.





[DBAL-866] Foreign Key Constraints does not work with Doctrine/Symfony and SQLite Created: 12/Apr/14  Updated: 22/Apr/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.4.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Christian Stoller Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

PHP 5.5.9, SQLite3 module version 0.7-dev, SQLite Library 3.8.3.1



 Description   

I have posted a question on stackoverflow already to get help on this issue, but nobody could give me a sufficient answer. See here.

#370 says that support for foreign keys support for SQLite has been implemented. But in my case it does not work. I have defined two entities:

Unable to find source-code formatter for language: yaml. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
Category:
    type: entity
    id:
        id:
            type: integer
            id: true
            generator:
                strategy: AUTO
    oneToMany:
        ministries:
            targetEntity: Ministry
            cascade: [persist]
            mappedBy: category

And

Unable to find source-code formatter for language: yaml. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
Ministry:
    type: entity
    id:
        id:
            type: integer
            id: true
            generator:
                strategy: AUTO
    manyToOne:
        category:
            targetEntity: Category
            inversedBy: ministries
            joinColumn:
                name: category_id
                nullable: false
                onDelete: CASCADE

But when I delete a category, the ministry entities do not get deleted, although the constraint should cascade. What am I missing?

Do I have to configure anything to get that working or is it a bug?



 Comments   
Comment by Marco Pivetta [ 22/Apr/14 ]

While some FK functionalities are supported by the DBAL, I reverted the feature in https://github.com/doctrine/dbal/commit/7282289fee625a24c26c1fccc0474e8ca583470f since it was too clunky, so the ORM doesn't recognize the platform as a platform that supports FKs.





[DBAL-874] [GH-572] Fix reverse engineering quoted table names on PostgreSQL Created: 22/Apr/14  Updated: 22/Apr/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/572

Message:

Commit https://github.com/doctrine/dbal/commit/83fe296de44ef3e2f04f2342021f656a797787ec introduced a bug with reverse engineering table details for tables with names requiring quotation (reserved keyword, non-identifier characters, case-folded) on PostgreSQL.
`PostgreSqlPlatform::getListTablesSQL()` fetches table names quoted if necessary with `quote_ident(tablename)` which in turn causes other `getListTable*SQL($table)` to fail retrieving results if the table name is quoted. Those methods always have to use unquoted table names in their statements.

See https://github.com/ZF-Commons/ZfcUserDoctrineORM/issues/77 for more details.






[DBAL-875] [GH-573] [DBAL-834] Fix order by with aggregate function(s) for limit/offset queries on SQL Server Created: 23/Apr/14  Updated: 23/Apr/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/573

Message:

When modifying a query with limit/offset clauses on SQL Server, using aggregate functions, the platform generates wrong SQL.
See http://www.doctrine-project.org/jira/browse/DBAL-834






[DBAL-834] SQLServer modifyLimitQuery does not work with aggregate functions in ORDER BY Created: 10/Mar/14  Updated: 24/Apr/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.4.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Francesco Montefoschi Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: paginator
Environment:

SQL Server 2008 SP3


Issue Links:
Duplicate
duplicates DBAL-788 ORDER BY with function COUNT() fails In Progress

 Description   

Starting with Doctrine 2.4, the `modifyLimitQuery` method does not work anymore with query using ORDER BY MAX(...)
See this example:

$sql = "SELECT MAX(heading_id) aliased, code
	FROM operator_model_operator
	GROUP BY code
	ORDER BY MAX(heading_id) DESC
";
$sql = $this->em->getConnection()->getDatabasePlatform()->modifyLimitQuery(
	$sql, 1, 0
);

Doctrine generates this SQL, which is invalid:

SELECT * FROM (SELECT MAX(heading_id) aliased, code
, ROW_NUMBER() OVER (ORDER BY MAX(heading_id) AS doctrine_rownum FROM operator_model_operator GROUP BY code) DESC
) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 1

The ORDER BY in moved into the OVER(), but the `preg_replace` in SQLServerPlatform.php stops to replace at the closing ")".



 Comments   
Comment by Francesco Montefoschi [ 10/Mar/14 ]

It is not possible to write `ORDER BY aliased` because it leads to a syntax error in SQL Server.

Comment by Steve Müller [ 10/Mar/14 ]

Francesco Montefoschi There have been some modifications to the modifyLimitQuery() method in SQL Server lately for 2.5 which address some problems with subqueries and aggregate functions. Not sure if that might already solve your issue. Can you please check if the problem also exists in the current master branch of DBAL?
See commit: https://github.com/doctrine/dbal/commit/9f3cb437c0f491599de4e1bd847235965f98ffd4

Comment by Francesco Montefoschi [ 11/Mar/14 ]
  - Removing doctrine/common (v2.4.1)
  - Installing doctrine/common (2.4.x-dev 9a7e20e)
    Cloning 9a7e20e779360f3b8a02c27a89d47d5a6fdce8d1

  - Removing doctrine/dbal (v2.4.2)
  - Installing doctrine/dbal (dev-master c61361d)
    Cloning c61361d8fcf65a977d8610ba78eb542a1d2f44b4

  - Removing doctrine/orm (v2.4.2)
  - Installing doctrine/orm (2.4.x-dev a949e87)
    Cloning a949e87ca88299cde368d2b574740753526b62c9

Same issue.

Comment by Flip [ 14/Mar/14 ]

on this line here https://github.com/doctrine/dbal/blob/9f3cb437c0f491599de4e1bd847235965f98ffd4/lib/Doctrine/DBAL/Platforms/SQLServerPlatform.php#L1164

try the following stuffs:

Puts "DESC" in a second capturing group (closer to the original regex, but not sure why you want to do this)
/ORDER\s+BY\s+([^)(]*(?:\(\w+\))?)(.*)/

Includes "DESC" in the first capturing group
/ORDER\s+BY\s+([^)(]*(?:\(\w+\))?.*)/

Same as last one, except this one stops capturing when it hits a ")" after "DESC"
/ORDER\s+BY\s+([^)(]*(?:\(\w+\))?[^)]*)/
Comment by M.K. [ 21/Mar/14 ]

This not only affects Queries with aggregate functions, but every Query that uses a limit and order by an entity-field alias.

Try this Testcase:

$query = $this->em->createQuery("
	SELECT a.id AS test
	FROM prim\\entity\\Article a
	ORDER BY test ASC
");
$query->setMaxResults(10);
echo "<h3>DQL</h3>";
var_dump($query->getDQL());
echo "<br><h3>SQL</h3>";
var_dump($query->getSQL());
echo "<br><h3>Result</h3>";
var_dump($query->getResult());
Comment by Flip [ 21/Mar/14 ]

The test case is incomplete as we don't have `kare\\entity
Article`. Please try the 3 proposed solutions and show the results from those adjustments.

Comment by M.K. [ 21/Mar/14 ]

This is just a sample Query for illustration. Replace it with whatever Entity you like.

Comment by Steve Müller [ 23/Apr/14 ]

Patch supplied in PR: https://github.com/doctrine/dbal/pull/573

Francesco Montefoschi Flip M.K. please review.





[DBAL-828] [GH-538] Json_Array: Convert database null to PHP null instead of empty array Created: 04/Mar/14  Updated: 24/Apr/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of localheinz:

Url: https://github.com/doctrine/dbal/pull/538

Message:

Related to https://github.com/doctrine/doctrine2/pull/968.



 Comments   
Comment by Doctrine Bot [ 17/Apr/14 ]

A related Github Pull-Request [GH-968] was closed:
https://github.com/doctrine/doctrine2/pull/968

Comment by Doctrine Bot [ 24/Apr/14 ]

A related Github Pull-Request [GH-538] was closed:
https://github.com/doctrine/dbal/pull/538

Comment by Steve Müller [ 24/Apr/14 ]

To be addressed in 3.0. We cannot change the behaviour in 2.x for BC reasons.





[DBAL-788] ORDER BY with function COUNT() fails Created: 08/Jan/14  Updated: 24/Apr/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Flip Assignee: Steve Müller
Resolution: Unresolved Votes: 1
Labels: None
Environment:

mssql 2008 R2


Issue Links:
Duplicate
is duplicated by DBAL-834 SQLServer modifyLimitQuery does not w... In Progress

 Description   

This:
ORDER BY ad.name ASC, count(filter.value) DESC

Fails with:
Error: Expected end of string, got '('



 Comments   
Comment by M.K. [ 15/Jan/14 ]

I have the same problem using SUM() in ORDER BY. I think the doctrine documentation says, that you have to use an alias in ORDER BY. This works fine with MySQL, but fails in MSSQL, because MSSQL doesn't allow aliases in ORDER BY.
I think using aliases in DQL should be fine, so it's rather a problem in SQLServerPlatform class. Aggregate functions in ORDER BY are pretty basic stuff.

Issue should be moved to DBAL.

Comment by Steve Müller [ 15/Jan/14 ]

Is using aggregate functions in ORDER BY even possible in SQL Server? It's not clear from the documentation. However it looks like ORDER BY SQL generation might have to be delegated to the specific platform just like LIMIT/OFFSET clauses. This would be another big mess for SQL Server

Comment by M.K. [ 15/Jan/14 ]

I use MSSQL 2008 R2 as well and it seems MSSQL is fine with using aliases in ORDER BY after all. If i run a DQL-Query like that:

SELECT t.id, SUM(pos.price) AS amount
FROM Project\Entity\Task t
LEFT JOIN Project\Entity\Position pos WITH t.id = pos.tid
GROUP BY t.id
ORDER BY amount ASC

... everything is fine. SQL looks like this:

SELECT t0_.id AS id0, SUM(p0_.price) AS sclr1
FROM task t0_ WITH (NOLOCK)
LEFT JOIN position p0_ ON (t0_.id = p0_.tid)
GROUP BY t0_.id
ORDER BY sclr1 ASC

MSSQL seems to be okay with the alias. But if apply a limit on the DQL-Query, SQL looks like this:

SELECT *
FROM (
	SELECT t0_.id AS id0, SUM(p0_.price) AS sclr1, ROW_NUMBER() OVER (ORDER BY sclr1 ASC) AS doctrine_rownum
	FROM task t0_ WITH (NOLOCK)
	LEFT JOIN position p0_ ON (t0_.id = p0_.tid)
	GROUP BY p0_.id
) AS doctrine_tbl
WHERE doctrine_rownum BETWEEN 1 AND 50

Execution fails with Error: "Invalid column name 'sclr1'"

I'm not really familiar with MSSQL which is why i decided to use Doctrine after all. But i hope this helps.

Comment by Steve Müller [ 15/Jan/14 ]

M.K. Thanks for the detailed information. The fact that you are using a limit here was missing. SQL Server does not support referring to expressions or column aliases from the select list in OVER() clause.
See here: http://msdn.microsoft.com/en-us/library/ms189461.aspx
Could you please test if it possible to specify the SUM() expression directly in the OVER() clause? Like the following:

SELECT *
FROM (
	SELECT t0_.id AS id0, SUM(p0_.price) AS sclr1, ROW_NUMBER() OVER (ORDER BY SUM(p0_.price) ASC) AS doctrine_rownum
	FROM task t0_ WITH (NOLOCK)
	LEFT JOIN position p0_ ON (t0_.id = p0_.tid)
	GROUP BY p0_.id
) AS doctrine_tbl
WHERE doctrine_rownum BETWEEN 1 AND 50

If that works we might be able to rewrite the SQLServerPlatform::modifyLimitQuery() to respect that. Otherwise I really don't know what to do about it.

Comment by M.K. [ 15/Jan/14 ]

Yup, using the SUM() expression in the OVER() clause works just fine.

Comment by Steve Müller [ 15/Jan/14 ]

M.K. Thank you for investigating. I would like to move this ticket to DBAL because it is a DBAL issue. But I guess that makes two issues now because if I understand correctly, Flip did not use a limit/offset query modification but did not use an alias in the ORDER BY clause either but instead directly specified a COUNT() expression...

Comment by Flip [ 24/Jan/14 ]

I can confirm it works when using an alias. Issue can be closed.

Comment by M.K. [ 14/Mar/14 ]

This issue refers to the same problem:
http://www.doctrine-project.org/jira/browse/DBAL-834





[DBAL-877] [GH-575] [DBAL-801] Add date arithmetic interval methods for seconds, minutes, weeks, quarters and years Created: 24/Apr/14  Updated: 24/Apr/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/575

Message:

This PR adds missing methods for additional date arithmetic interval expression of seconds, minutes, weeks, quarters and years.
Additionally all platforms have been refactored to avoid a lot of code duplication through a more common protected extension point method `AbstractPlatform::getDateArithmeticIntervalExpression()`.






[DBAL-843] Doctrine DBAL getSchema() detect wrong text type (LONGTEXT instead of TEXT) Created: 22/Mar/14  Updated: 26/Mar/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Stefano Kowalke Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: None


 Description   

I created a database from Doctrine Schema (refer as "expected schema" in the text below) which worked fine. Later I need to compare the "expected schema" with the "current schema" from the database. While I am doing that I figured out the the SQL statements of both schemas differs. While the "expected schema" defines a TEXT type for a field, the "current schema" defines a LONGTEXT for the field.

Here is what I am doing:
$tableName = $schema->createTable('tableName');
$tableName->addColumn('fieldName', 'text', array('length' => 65535, 'notnull' => FALSE));

$currentSchema = $connection->getSchemaManager()->createSchema();
$currentSQL = $currentSchema->toSql($connection->getPlatform());

// CREATE TABLE tableName (fieldName LONGTEXT DEFAULT NULL) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB

$expectedSchema = $this->getExpectedSchema();
$expectedSQL = $expectedSchema->toSql($connection->getPlatform());
// CREATE TABLE tableName (fieldName TEXT DEFAULT NULL) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB

This happens because in Doctrine/DBAL/Schema/MySqlSchemaManager.php the type of text is detect correctly but the length is set to FALSE instead of 65535 and when it comes to create the SQL string from the schema it just returns LONGTEXT because the variable $length is FALSE (See Doctrine/DBAL/Platforms/MySqlPlatform::getClobTypeDeclarationSQL().

I added a new case in MySqlSchemaManager::_getPortableTableColumnDefinition which set the length of text types to 65535 and it works. I don't know if this is the correct place in code to do that.

Maybe you can point at the correct place and I will create a PR at Github.



 Comments   
Comment by Steve Müller [ 22/Mar/14 ]

Stefano Kowalke which DBAL version are you using? As far as I can see the length is preserved for text type columns. See here: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/MySqlSchemaManager.php#L131-L160

Comment by Stefano Kowalke [ 22/Mar/14 ]

But there is no length information inside $tableColumn['type']. While the value of INT type in $tableColumn['type'] is 'int(11)', the value for TEXT type is just 'text'.

The number inside the parentheses is extracted in https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/MySqlSchemaManager.php#L111 for INT type but not for the TEXT type - line 111 returns and save FALSE in $length.

Comment by Steve Müller [ 25/Mar/14 ]

Patch supplied in PR: https://github.com/doctrine/dbal/pull/553

Comment by Stefano Kowalke [ 26/Mar/14 ]

Thanks for taking care of this.

Comment by Steve Müller [ 26/Mar/14 ]

Thanks for reporting





[DBAL-837] Cannot drop index needed in a foreign key constraint Created: 17/Mar/14  Updated: 17/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.4.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Cliff Odijk Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: None
Environment:

MariaDB version 10.0.7
InnoDB version 5.6.10
doctrine/orm version v2.4.1
doctrine/dbal version v2.4.2


Issue Links:
Reference
relates to DBAL-732 MySQL 5.6 - Cannot change column 'fkP... Open

 Description   

I'm trying to remove an relation from an entity and i'm getting an error that it could not be executed. After testing it, it's missing the DROP FOREIGN KEY query.

The generated SQL is:

DROP INDEX IDX_DCE815B325C79A8C ON moveMembers;
ALTER TABLE moveMembers DROP fkAccessId;

When I use --force to execute it I get the following error:

[Doctrine\DBAL\DBALException]
An exception occurred while executing 'DROP INDEX IDX_DCE815B325C79A8C ON moveMembers':

SQLSTATE[HY000]: General error: 1553 Cannot drop index 'IDX_DCE815B325C79A8C': needed in a foreign key constraint

[PDOException]
SQLSTATE[HY000]: General error: 1553 Cannot drop index 'IDX_DCE815B325C79A8C': needed in a foreign key constraint



 Comments   
Comment by Cliff Odijk [ 17/Mar/14 ]

Maybe related to DBAL-732?





[DBAL-812] SchemaTool ignores multi-columns (composite) indexes when creating a foreign key Created: 13/Feb/14  Updated: 15/Feb/14

Status: Open
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.4.2
Fix Version/s: None

Type: Improvement Priority: Minor
Reporter: Peter Huynh Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: schematool
Environment:

MySQL



 Description   

When using the schema tool to generate the differences between the db and the entities, I notice the following:

ALTER TABLE sales ADD CONSTRAINT FK_36D222EF603EE73 FOREIGN KEY (vid) REFERENCES vendor (id);
CREATE INDEX IDX_36D222EF603EE73 ON sales (vid);

Normally, it doesn't bother me, however I have had an existing composite index consists of (vid, submit). This leads to the redundant index IDX_36D222EF603EE73 and therefore forcing unnecessary overheads.

It would be nice to have a mean to disable this from happening.

The code in question can be found at https://github.com/doctrine/dbal/blob/594e326bd58d1d7af578f0dc3143655b9d119d45/lib/Doctrine/DBAL/Schema/Table.php#L543.

A helpful member from #doctrine IRC also pointed out that it does not check the unique constraints also.

Regards,



 Comments   
Comment by Marco Pivetta [ 13/Feb/14 ]

Just a hint on this: looks like the current logic just checks hashed indexes, ignoring the "uniq" prefixed indexes to avoid duplicates.

Comment by Steve Müller [ 15/Feb/14 ]

Peter Huynh I get the point here. However after having a quick look into this it seems this behaviour exists for a good reason. See here: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/Index.php#L191-L195
The question is how could this reasonable extended to fulfil the needs you explained. I am not entirely sure about this and this peace of code is rather sensitive and critical. I don't want to rush any assumptions here.

Comment by Peter Huynh [ 15/Feb/14 ]

Hi Steve,

I fully understand your reservation.

Thanks for looking into things.

Peter





[DBAL-761] Driver\ResultStatement::fetchAll() returns empty array on a seemingly valid Driver\PDOStatement object Created: 03/Jan/14  Updated: 08/Jan/14

Status: Open
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.4
Fix Version/s: None

Type: Improvement Priority: Minor
Reporter: Dennis Matveyev Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mysql, orm, query, schematool
Environment:

Windows 7 64 bit, Zend Server/Studio, PHP 5.4.16,
Server: Apache/2.2.22 (Win32) mod_ssl/2.2.22 OpenSSL/0.9.8x



 Description   

I came across a weird issue, where when running:

vendor/bin/doctrine-module orm:schema-tool:update

I would get:

[Doctrine\DBAL\Schema\SchemaException]
There is no column with name 'resource_id' on table 'role_resource'.

But I did have a column with the above name in the above table, so that was a weird message for me. So I traced it all the way to this line of code:

https://github.com/doctrine/dbal/blob/2.4/lib/Doctrine/DBAL/Connection.php#L630

If I remove "->fetchAll()" from that line, I get this object:

object(Doctrine\DBAL\Driver\PDOStatement)#531 (1)

{ ["queryString"]=> string(332) "SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type, IS_NULLABLE AS `Null`, COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS Extra, COLUMN_COMMENT AS Comment, CHARACTER_SET_NAME AS CharacterSet, COLLATION_NAME AS CollactionName FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'loginauth' AND TABLE_NAME = 'role_resource'" }

Which has a valid SQL query that runs fine and shows Field names on my local machine's MySQL prompt. But when I add ->fetchAll() back in that line of code, an empty array is returned, field names are not returned, and a SchemaException is generated. I found this odd and wanted to report it. Whether it is a bug or not, hopefully I can find the cause of this issue.

For loads more info, please see this: http://stackoverflow.com/questions/20907491/doctrine-2-there-is-no-column-with-name-columnname-on-table-table



 Comments   
Comment by Steve Müller [ 03/Jan/14 ]

Dennis Matveyev It seems you are using MySQL. Can you please tell which version you use?

Comment by Steve Müller [ 03/Jan/14 ]

Dennis Matveyev Please also provide whether you use mysqli or PDO_MYSQL.

Comment by Steve Müller [ 03/Jan/14 ]

Dennis Matveyev Sounds dumb, but also are you sure that the vendor/bin/doctrine-module orm:schema-tool:update uses the correct connection parameters and does not by error connect to another database server or something? Because otherwise everything looks good IMO.

Comment by Dennis Matveyev [ 07/Jan/14 ]

Yes, I am using:
Server version: 5.5.23-log MySQL Community Server (GPL)
mysql client Ver 14.14 Distrib 5.5.23, for Win32 (x86)

Doctrine connection string for 'driverClass' is 'Doctrine\DBAL\Driver\PDOMySql\Driver', so it is PDO_MYSQL.

I am connecting to the right database, but your last suggestion is what helped to uncover the issue. When connecting to the database with the very same user/pass, and running the command, here is what I saw:

mysql> select resource_id from role_resource;
ERROR 1142 (42000): SELECT command denied to user 'login'@'localhost' for table 'role_resource'

Running a GRANT command to allow SELECT for this user solved the problem.

To improve debugging of similar issues, I'd see if there is a way to propagate the error from MySQL server to ./doctrine-module,
or
improve the error message of SchemaException, to i.e. "There is no column with name 'action_id' on table 'role_action', or database permissions prevent table access."

Thanks!

Comment by Steve Müller [ 07/Jan/14 ]

Dennis Matveyev Thank you for reporting this and I'm glad I could help. I will mark this as improvement, though. You are right in that the root cause of the error should be propagated to the user instead.

Comment by Steve Müller [ 08/Jan/14 ]

Dennis Matveyev Okay I found the root cause of the problem. See what MySQL states about access to the information_schema table for retrieving metadata about a certain database:

Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges will see NULL.

So it returns NULL instead of raising an error and this is why Doctrine is not able to propagate the proper exception in this case. I'm not sure if changing the exception message in the SchemaException class is the proper way of handling this. Also please note that this is not only about columns (where you got stuck at) but can occurr for almost every list*() action called in the SchemaManager I suppose. Furthermore this might be a MySQL specific issue, not sure about that.

The proper solution would be to throw an exception much earlier, to have a decent behaviour in such an edge case. I think we should throw an exception here: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php#L267 because a table cannot live without columns and indicates an error somewhere. But I would like to hear the opinion of Benjamin Eberlei on that issue.

Comment by Steve Müller [ 08/Jan/14 ]

I checked the SQL Server documentation and it seems they behave exactly the same: http://technet.microsoft.com/en-us/library/ms187113.aspx

In SQL Server 2005 and later, the visibility of metadata is limited to securables that a user either owns or on which the user has been granted some permission. For example, the following query returns a row if the user has been granted a permission such as SELECT or INSERT on the table myTable.

However, if the user does not have any permission on myTable, the query returns an empty result set.





[DBAL-732] MySQL 5.6 - Cannot change column 'fkProjectId': used in a foreign key constraint Created: 24/Dec/13  Updated: 18/Mar/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.3.4
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Cliff Odijk Assignee: Steve Müller
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Reference
is referenced by DBAL-837 Cannot drop index needed in a foreign... Open

 Description   

I'm using doctrine migrations to change a null field to a not null field. MySQL 5.6 is strict on altering tables with foreign key constraint's.

Generated SQL

ALTER TABLE badges CHANGE fkProjectId fkProjectId INT NOT NULL

Result in the following error

Cannot change column 'fkProjectId': used in a foreign key constraint 'FK_1483A5E9F28AE4EA'

More info:

As of 5.6.7, the server prohibits changes to foreign key columns with the potential to cause loss of referential integrity. A workaround is to use ALTER TABLE ... DROP FOREIGN KEY before changing the column definition and ALTER TABLE ... ADD FOREIGN KEY afterward.

Original issue:



 Comments   
Comment by Marco Pivetta [ 24/Dec/13 ]

Cliff Odijk what versions of DBAL/ORM are affected by the bug?

Comment by Cliff Odijk [ 24/Dec/13 ]

Doctrine 2.3.4 / 2a37b007dda8e21bdbb8fa445be8fa0064199e13.

Comment by Steve Müller [ 27/Dec/13 ]

Marco Pivetta I wonder whether we should introduce MySqlPlatform567 to fix this which adds this behaviour. We could also fix this is MySqlPlatform directly but I don't know if this impacts performance for older versions of MySQL that don't require this behaviour.

Comment by Timothée Martin [ 09/Jan/14 ]

I encounter the same issue with doctrine/dbal 2.4.2 (commit fec965d330c958e175c39e61c3f6751955af32d0).

Have you any idea of when this bug will be fixed? Or may be can you guide me on how to fix it and I could make a PR on doctrine/dbal.

Thanks.

Comment by Steve Müller [ 09/Jan/14 ]

Cliff Odijk , Timothée Martin I will work on this issue as soon as I have time. Expect a fix for this in the upcoming weeks. Thank you for your patience.

Comment by Cliff Odijk [ 28/Jan/14 ]

The same error occurs with MariaDB version 10.0.7

  • InnoDB version 5.6.10
  • doctrine/orm version v2.4.1
  • doctrine/dbal version v2.4.2
Comment by Steve Müller [ 18/Mar/14 ]

I already started work on this but didn't have time to finish it, yet. I will try to find some time for this this evening.





[DBAL-662] Supporting PHP 5.5 DateTimeImmutable Created: 14/Nov/13  Updated: 03/Jan/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: New Feature Priority: Minor
Reporter: Christophe Coevoet Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mapping, schematool


 Description   

Introducing new types converting dates into a DateTimeImmutable rather than a DateTime could be useful for applications prefering the use of immutable datetimes (and relying on PHP 5.5+).

The existing types already support setting a DateTimeImmutable in a field mapped with the datetime type, as it does not check the value against DateTime but only expects a format method. but the conversion from DB to PHP will always produce a mutable DateTime instance, thus preventing to use the immutable flavour consistently.

Not that this is a low priority issue as any code interacting with third party packages will probably need to use DateTime anyway because of the typehints (typehinting DateTimeInterface would not work if you need to keep compatibility with 5.4)

If DBAL 3.0 bumps the minimal supported version to 5.5 (which might be possible depending of the release date of 3.0), we could decide to break BC and use the immutable flavour in the datetime type directly.



 Comments   
Comment by Steve Müller [ 03/Jan/14 ]

Christophe Coevoet Should we mark that for 3.0 as suggested? Or do you see any need/possibility of implementing this already in 2.x branch?





[DBAL-631] NuoDB compatibility Created: 15/Oct/13  Updated: 15/Oct/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Minor
Reporter: Pierre Bonneau Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Hello,

I'm looking for a scalable SQL database, and I found nuoDB(old nimbus DB) that seems to do the job on paper.
My company tend to encourage us to move to many small VM architecture in place of large machines.

I wanted to know if such a DB engine was going to be supported in the near future, or if it was now.
For what I saw, the only really scalable solution I have now is nosql DB as mongo DB with doctrine.
As Doctrine is a requirment on our application, I was condering if you had any feedback for us on this subject.

Best regards, and thank you for your work.

Pierre Bonneau



 Comments   
Comment by Christophe Coevoet [ 15/Oct/13 ]

The best way to have NuoDB support in DBAL is probably to contribute it.
Introducing new drivers is best done by people using them (because they actually need it, and because they can more easily test them)

You will need to provide 3 classes (and their tests):

Comment by Pierre Bonneau [ 15/Oct/13 ]

Hello,

I already asked the question to NuoDB company, waiting their answer.
I'm absolutly not familiar with it, so I'm not very confident in the fact I would be able to provide a correct interface for this server.

I read mysql driver, shema and platform as an example. Driver and shema development should be dueable... but platform is totally out of my scope / scope... It would require a good knowledge of the database, and honnestly, I need only to perform 3 operations on it : INSERT / UPDATE and SELECT... without even a join. Would it be possible to let the other method empty and to let the community fill the blank ?

Thank you,
Pierre

Comment by Christophe Coevoet [ 15/Oct/13 ]

unfortunately, you need more than this in case you are using the ORM, because the ORM needs more methods from the platform (many of them has been added based on the needs of the ORM). However, the nuodb doc should only implementing these methods: doc.nuodb.com/display/doc/SQL+Reference

Comment by Pierre Bonneau [ 15/Oct/13 ]

Hello,

Thank you for your answer...

If we want to be compliant to Doctrine, is there a number of method that are required, and some other that are usefull?
I mean I can implement the method, but do nothing in some of them. The next user who need more will develop the module, or we will do later if needed.

The idea for me is to lower the cost, and to not pay on my project budget the entire connectivity...
I'm not againts speding 3-5 man day in this subject, but more will be an issue regarding the total amount of day I have on the project.

Pierre





[DBAL-598] Schema Comparator is case insensitive for table names Created: 05/Sep/13  Updated: 07/Oct/13

Status: Open
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Justin Martin Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/Comparator.php#L102

The schema comparator currently uses strtolower for all table name comparison, so differences in case will not be detected.

When updating a schema using the schema tool, the tool will not perform any modification if the case of a table name has changed.

An example of this case would be when one is writing entities for the ORM, and the naming includes uppercase letters. You generate your schema and it uses the equivalent case for the initial generation. Then, you write Table annotations to change the name of the tables to lowercase. The schema comparator would not detect this, and depending upon the case sensitivity of the SQL engine, this could result in SQL errors.



 Comments   
Comment by Jeremiah Small [ 07/Oct/13 ]

This is the closest existing issue I could find to a problem we are having with the schema tool update and case insensitivity. If this is a separate bug, I'd be happy to write it up, but I don't have create privs in Jira yet.

Here's the bug:

If we add a new association, and do php vendor/bin/doctrine-module orm:schema-tool:create, the new association will be created in MySQL information schema REFERENCED_TABLE_NAME will be created case sensitively.

Example (note second to last column):

CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
def mydb FK_E2C31786CF786E13 def mydb ObservationDiagnosticParameterValue diagnostic_parameter_value_id 1 1 mydb DiagnosticParameterValue id

However, if we add a new association, and do php vendor/bin/doctrine-module orm:schema-tool:update, the new association will be created in MySQL information schema REFERENCED_TABLE_NAME will be created case insensitively.

Example (note second to last column):

CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
def mydb FK_E2C31786CF786E13 def mydb ObservationDiagnosticParameterValue diagnostic_parameter_value_id 1 1 mydb diagnosticparametervalue id

In the second case, we then get errors like this:

An exception occurred while executing 'INSERT INTO ObservationDiagnosticParameterValue (observation_id, diagnostic_parameter_value_id) VALUES (?, ?)' with params [1211, 6916]:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`mydb`.`ObservationDiagnosticParameterValue`, CONSTRAINT `FK_E2C31786CF786E13` FOREIGN KEY (`diagnostic_parameter_value_id`) REFERENCES `diagnosticparametervalue` (`id`))

#0 /var/opt/theapp/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(140): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(PDOException), 'INSERT INTO Obs...', Array)

To fix this, we can alter the affected table like this:

alter table `ObservationDiagnosticParameterValue` drop foreign key `FK_E2C31786CF786E13`;
alter table `ObservationDiagnosticParameterValue` add constraint `FK_E2C31786CF786E13` foreign key (`diagnostic_parameter_value_id`) REFERENCES `DiagnosticParameterValue` (`id`);

The behavior of schema-tool:create and schema-tool:update should not be different.





[DBAL-423] Type GUID = VARCHAR(255) on platforms that don't have a native GUID support Created: 25/Jan/13  Updated: 08/Mar/14

Status: In Progress
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Minor
Reporter: amr Assignee: Steve Müller
Resolution: Unresolved Votes: 1
Labels: None


 Description   

I'm using MySQL with entities that have GUID ids. Therefore I'm using @ORM\Column(type="guid") for the ORM mapping. As MySQL does not have a native GUID data type, it gets mapped to type="string" with a default length of 255 -> VARCHAR(255). I don't really understand why we don't limit the length to 36, which is the fixed length for GUIDs. You could even think about using CHAR(36) for MySQL.

-> see Doctrine\DBAL\Platforms\AbstractPlatform -> getGuidTypeDeclarationSQL()



 Comments   
Comment by Steve Müller [ 23/Dec/13 ]

Patch PR: https://github.com/doctrine/dbal/pull/465

Comment by Michael Kühn [ 28/Feb/14 ]

With the latest support for the MyISAM-Engine merging this pull request would save some trouble.

Background/Steps to reproduce:
If you have 2 entities with guid/uuid as primary key, @ManyToMany/@JoinTable fails on MyISAM, because it would create a jointable with 2 VARCHAR(255) columns and would apply a combined primary key on these two columns. But MyISAM doesn't support keys longer than 1000 bytes so you can't create the jointable.

See: https://dev.mysql.com/doc/refman/5.6/en/myisam-storage-engine.html

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.

In my opinion this isn't just a "minor" issue but a major because some people can't run on MySQL with InnoDB for some reason.

Comment by Marco Pivetta [ 28/Feb/14 ]

Michael Kühn MyISAM is niche support for the ORM - using a custom type is perfectly fine in such a case.

Comment by Michael Kühn [ 04/Mar/14 ]

Marco Pivetta I agree, but i don't see why we would assume a GUID/UUID - which is per definition 36 chars long - as a 255 char long string. It would save storage space (for platforms don't supporting a native uuid type) and circle around at least 1 barrier if using MyISAM.

By the way, the PR is missing something. While it works perfectly fine the first time, every orm:schema-tool:update would output the guid-columns every time if you don't specifiy "length=36" and "fixed=true" on every GUID-@Column. IMHO the GUID-Type should implicit this both attributes in this pull request so you don't get column updates that don't change anything.

Comment by Steve Müller [ 08/Mar/14 ]

Michael Kühn I get your point and thanks for pointing out the remaining issue. The problem is caused by the comparator which detects differences in the column definition because the length and fixed attribute are hardcoded in the platform which is beyond comparator's knowledge. Still I think this can be fixed easily but as long as Benjamin Eberlei is of the opinion that this change is a minor BC break, this PR won't make it into the master branch.





[DBAL-422] Wrong VARCHAR default length in SQLServerPlatform Created: 24/Jan/13  Updated: 27/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Steve Müller Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: platform, sqlserver, sqlsrv, varchar


 Description   

In SQLServerPlatform the default length for a VARCHAR declaration is set to "255". But according to the SQLServer documentation from Microsoft the default length is "1", if omitted in the declaration.
See remarks: http://msdn.microsoft.com/en-us/library/ms186939.aspx
Also the default length is hardcoded in the "getVarcharTypeDeclarationSQLSnippet" method which in my opinion should be evaluated through "getVarcharDefaultLength".

I don't exactly know if the current implementation is intended, otherwise it should be fixed. I would then create an PR if desired.



 Comments   
Comment by Steve Müller [ 27/Dec/13 ]

The implementation is inconsistent on other platforms, too and can first be addressed in 3.0 I think. Otherwise this would be a BC break.





[DBAL-858] oracle IN statement with more than 1000 values Created: 11/Jan/13  Updated: 01/Apr/14

Status: Open
Project: Doctrine DBAL
Component/s: Drivers, Platforms
Affects Version/s: 2.2.2
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Minor
Reporter: Marc Drolet Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

If I have a query with a IN statement with more tahn 1000 values I get an sql error.

I've try IN with implode:
select * from test where id IN(' . implode(',', $values) . ')
and I've also try with executeQuery:
select * from test where id IN(:test)
executeQuery($sql, array($values), array(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY))



 Comments   
Comment by Marc Drolet [ 11/Jan/13 ]

Here is the way I've implement the solution on my side: (for oracle)

into Doctrine/DBAL/Statement.php, I've add this method:

/**
     * Binds a parameter value to the statement.
     * This is implemented this way for oracle only. Other drivers are redirected to bindValue method.
     *
     * The value will be bound with to the type provided (that required to be a table type).
     *
     * @param String $name The name or position of the parameter.
     * @param Array $value The value of the parameter.
     * @param String $type The name of the type to use to bind.
     * @return boolean TRUE on success, FALSE on failure.
     */
    public function bindList($name, Array $value, $type)
    {
        if ('oracle' !== $this->platform->getName())
        {
            $this->bindValue($name, $value, $type);
        }
        else
        {
            return $this->stmt->bindList($name, $value, $type);
        }
    }

into Doctrine/DBAL/Driver/Statement.php I've add:

/**
     * @TODO: docs
     */
    function bindList($param, Array $values, $type);

into Doctrine/DBAL/Driver/OCI8/OCI8Statement.php I've add this method:

/**
     * {@inheritdoc}
     */
    public function bindList($param, Array $value, $type)
    {
        if (!($list = oci_new_collection($this->_dbh, $type)))
        {
            //throw new OCI8Exception::fromErrorInfo($this->errorInfo());
        }

        foreach ($value as $entry)
        {
            $list->append($entry);
        }
        if (!oci_bind_by_name($this->_sth, $param, $list, -1, OCI_B_NTY))
        {
            //throw new OCI8Exception::fromErrorInfo($this->errorInfo());
        }
    }

// NOTE: we should probably add the bindList to all driver Statement object.

into your code you can use it this way:

$sql = "
    SELECT *
    FROM test
    WHERE id IN
    (
        SELECT *
        FROM
        (
            CAST (: p_ids AS list_int_type)
        )
    )
";
$stmt = connection->prepare($sql);
$stmt->bindList(': p_ids', $ids, 'list_int_type');
$stmt->execute();
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);

NOTE:
list_int_type need to be a valid oracle data type. You can create one with the name you want.
example:
you can have 2 type of accepted array of values: integer and string
let's say we create one for string named: list_str_type and one for integer list_int_type

create or replace type list_str_type as table of varchar2(4000);
create or replace type list_int_type as table of number;

Comment by Benjamin Eberlei [ 01/Apr/13 ]

Hey Marc Drolet

thanks for the feedback and the solution, however i would like to have something generic that is working independent of the database driver. This code is very specific.

Can you point me to some documentation why oci collection works with more than 1000 elements and how it works in PHP?

Comment by Marc Drolet [ 02/Apr/13 ]

Hi Benjamin,

The limitation is not from the oci driver, it's an oracle limitation. There are a couple of possible solution/implementation that can be done but the one I've provide is the one that perform better for the test I've done and from what I can found over the blogs I've read.

I can't find the exact documentation of oracle. oracle doc is so poor.
Here is the best description link I can provide that describe some possible implementation.
http://vsadilovskiy.wordpress.com/substituting-a-collection-for-in-list-performance-study/

I don't know if there is similar limitation with other database. With the implementation I've provided, It will be possible to implement the proper solution depending on the database limitation you face otherwise it will execute the generic IN. What's bad, we need to create the type into the database.

NOTE: In my case, I can not perform a sub-query, I get the my collection from a web service call.





[DBAL-406] PostgreSqlSchemaManager::tablesExist() misses schema-qualified table names if they exist in the first schema on the search path Created: 07/Jan/13  Updated: 07/Jan/13

Status: Open
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.3.1
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Roger Hunwicks Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: postgresql
Environment:

Postgresql 9.1



 Description   

Please see https://github.com/doctrine/migrations/issues/99 for additional background.

To reproduce:

CREATE SCHEMA test_schema;
CREATE TABLE test_schema.test_table (test_column TEXT);

Then in Doctrine:

Unable to find source-code formatter for language: php. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
$connection->executeUpdate('SET search_path=test_schema;');
$result = $connection->getSchemaManager()->tablesExist(array('test_schema.test_table'));

$result is false when it should be true.

The error occurs because PostgreSqlSchemaManager returns the bare table name from getPortableTablesList() if the schema is the first one in the search path.

The full explanation is...

AbstractSchemaManager::tablesExist() calls $this->getPortableTablesList() before checking if the tables exist.

PostgreSqlSchemaManager overrides this in _getPortableTableDefinition() by comparing the schema for the table with the search path for the connection. If the table schema is the first one in the search path, then it returns the bare table name, if it isn't then it returns the schema-qualified table name (i.e. schema.table).

tablesExist() does an array_intersect to check that all the tables in the search array exist in the database.

If one of the tables in the search array was schema-qualified but also in the first schema on the search path, then you end up checking:

array_intersect(array('test_schema.test_table'), array('test_table'))

which fails.

One way to fix it would be to override tablesExist() in PostgreSqlSchemaManager so that it passes the search array through getPortableTableDefinition() before doing the array_intersect:

Unable to find source-code formatter for language: php. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
    /**
     * Return true if all the given tables exist.
     *
     * @param array $tableNames
     * @return bool
     */
    public function tablesExist($tableNames)
    {
        foreach ($tableNames as $key => $tableName) {
            if (strpos($tableName, '.') !== false) {
                $tableName = explode('.', $tableName, 2);
                $tableNames[$key] = $this->_getPortableTableDefinition(array('schema_name'=>$tableName[0], 'table_name'=>$tableName[1]));
            }
        }
        return parent::tablesExist($tableNames);
    }

I'm happy to provide a PR on GitHub if you want.






[DBAL-375] Warning "Udefined index dbname" while creating database with oci8 driver Created: 31/Oct/12  Updated: 20/Apr/13

Status: Open
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.2.2, 2.3.1
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: pavel patrin Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: dbname, oci8


 Description   

In config specified:

doctrine:
dbal:
driver: "oci8"
host: "localhost"
port: "1521"
dbname: "orcl50"
user: "SYSTEM"
password: "123456"
charset: UTF8

When i create database (with symfony 2, doctrine:database:create), got that error:

=====================================
Could not create database for connection named orcl50
Notice: Undefined index: dbname in /path/to/symfony/vendor/doctrine-dbal/lib/Doctrine/DBAL/Driver/OCI8/Driver.php line 67
=====================================

If i comment "unset($params['dbname'])" in CreateDatabaseDoctrineCommand.php:54 all works fine.



 Comments   
Comment by Kris Willis [ 13/Nov/12 ]

I'm experiencing the same issue and your fix appears to work for me too; thanks!

Comment by Benjamin Eberlei [ 20/Apr/13 ]

on Oracle CREATE DATABASE is actually a CREATE USER. I am not sure the command should allow to do this.





[DBAL-373] Indexes and uniqueConstraints has been ignored Created: 26/Oct/12  Updated: 20/Apr/13

Status: Awaiting Feedback
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Diego Oliveira Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 12.04 with MySQL 5.5 and PHP 5.4


Attachments: Text File dbal-fixing-373.patch     Text File orm-fixing-373.patch    

 Description   

I using the Doctrine Migrations and when I declared my entity with the indexes section, the index name has been ignored. It's like this:

indexes:
IDX_ADDRESS_CITY:
columns: city_id

but, the diff tools ignore it and give me this code:
$this->addSql("CREATE INDEX IDX_7299B5238BAC62AF ON tb_address (city_id)");

and it should be:
$this->addSql("CREATE INDEX IDX_ADDRESS_CITY ON tb_address (city_id)");

Notice: I open the same bug on the migrations repository in github and @kimhemsoe told me to open here, since this is generated by DBAL component.



 Comments   
Comment by Padraig O'Sullivan [ 11/Dec/12 ]

Did you specify an index name in the indexes property for your entity in your PHP file? In this case, you should have something like:

indexes={@Index(name="IDX_ADDRESS_CITY", columns={"city_id"})}

That should result in the correct SQL being generated. If I modify the above to:

indexes={@Index(columns={"city_id"})}

I also get a migration that has SQL with an auto-generated index name.

Comment by Diego Oliveira [ 02/Feb/13 ]

Yes I did specify a name, as you can see:

uniqueConstraints:
    UNIQUE_STATE_SLUG:
        columns: slug
indexes:
    IDX_USER_ADDRESS:
        columns: address_id

I don't try do to it using annotations because I choose do use yaml to describe my entities. I will take a look on the source code to see if I can fix it and send a PR.

Comment by Diego Oliveira [ 02/Apr/13 ]

I already sent this patch to Guilherme Blanco, but I guess he doesn't have time to take a look on it.

I guess my solution it's not ideal, but it solve the problem and can be a base to make a better solution.

Comment by Benjamin Eberlei [ 20/Apr/13 ]

Diego Oliveira the fix doesn't seem too nice with the additional boolean flag.

I would rather like to fix the root cause instead of adding this solution.

Comment by Benjamin Eberlei [ 20/Apr/13 ]

The issue is tricky, because we cannot just move the index definitions above, they will need the columns, however that directly generates the index in the schema tool. Maybe if the gather join column methods would check if they can add an index already it would work.





[DBAL-372] Add SQL parser Created: 25/Oct/12  Updated: 25/Oct/12

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: New Feature Priority: Minor
Reporter: Martin Hasoň Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

It is useful for create sql highlighter. See https://github.com/doctrine/DoctrineBundle/pull/117 or https://github.com/doctrine/DoctrineBundle/issues/107






[DBAL-352] Running doctrine:schema:update throws an exception the first time, succeeds the second time. Created: 25/Sep/12  Updated: 25/Sep/12

Status: Open
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Mark A. Hershberger Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Shell output from my symfony project:
$ app/console doctrine:schema:update --force
Updating database schema...

[Doctrine\DBAL\DBALException]
An exception occurred while executing 'ALTER TABLE Tn ADD CONSTRAINT FK_3504C6F07D3656A4 FOREIGN KEY (account) REFERENCES Account (id)':

SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'account' doesn't exist in table

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'account' doesn't exist in table

doctrine:schema:update [--complete] [--dump-sql] [--force] [--em[="..."]]

$ app/console doctrine:schema:update --force
Updating database schema...
Database schema updated successfully! "7" queries were executed
$



 Comments   
Comment by Christophe Coevoet [ 25/Sep/12 ]

Please run the command with the --dump-sql option instead of --force each time before launching it for real, to be able to see which SQL queries are executed in both cases

Comment by Mark A. Hershberger [ 25/Sep/12 ]

I'm not sure how to roll back the changes so that I can reproduce this.

Comment by Mark A. Hershberger [ 25/Sep/12 ]

Got it after a little poking around with git. Rolling back to a commit in my code before the one given here didn't didn't show this reproduce this.

$ php app/console doctrine:schema:create
ATTENTION: This operation should not be executed in a production environment.

Creating database schema...
Database schema created successfully!

$ git checkout d686a39fb664dca540167e1b3e96ea0ffd67bc00
Previous HEAD position was 0510a59... * First round of Customer-to-Accounts (1:n) mapping done * Start of Customer-to-Tn and Tn-to-Account mapping
HEAD is now at d686a39... * A little debugging for Entity::Customer::hasEntitlement * Add FK for Customer to Tn

$ php app/console doctrine:schema:update --dump-sql
ALTER TABLE Account CHANGE guid guid VARCHAR(255) NOT NULL;
ALTER TABLE Tn DROP FOREIGN KEY FK_3504C6F09B6B5FBA;
DROP INDEX IDX_3504C6F09B6B5FBA ON Tn;
ALTER TABLE Tn ADD tnType VARCHAR(10) NOT NULL, CHANGE account_id customer INT DEFAULT NULL;
ALTER TABLE Tn ADD CONSTRAINT FK_3504C6F07D3656A4 FOREIGN KEY (account) REFERENCES Account (id);
ALTER TABLE Tn ADD CONSTRAINT FK_3504C6F081398E09 FOREIGN KEY (customer) REFERENCES Customer (id);
CREATE INDEX IDX_3504C6F07D3656A4 ON Tn (account);
CREATE INDEX IDX_3504C6F081398E09 ON Tn (customer);
ALTER TABLE Customer CHANGE guid guid VARCHAR(255) NOT NULL, CHANGE authGuid authGuid VARCHAR(255) NOT NULL
mah@debian:~/comcast$ php app/console doctrine:schema:update --force
Updating database schema...

[Doctrine\DBAL\DBALException]
An exception occurred while executing 'ALTER TABLE Tn ADD CONSTRAINT FK_3504C6F07D3656A4 FOREIGN KEY (account)
REFERENCES Account (id)':

SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'account' doesn't exist in table

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'account' doesn't exist in table

doctrine:schema:update [--complete] [--dump-sql] [--force] [--em[="..."]]

$ php app/console doctrine:schema:update --dump-sql
ALTER TABLE Account CHANGE guid guid VARCHAR(255) NOT NULL;
ALTER TABLE Tn ADD account INT DEFAULT NULL;
ALTER TABLE Tn ADD CONSTRAINT FK_3504C6F07D3656A4 FOREIGN KEY (account) REFERENCES Account (id);
ALTER TABLE Tn ADD CONSTRAINT FK_3504C6F081398E09 FOREIGN KEY (customer) REFERENCES Customer (id);
CREATE INDEX IDX_3504C6F07D3656A4 ON Tn (account);
CREATE INDEX IDX_3504C6F081398E09 ON Tn (customer);
ALTER TABLE Customer CHANGE guid guid VARCHAR(255) NOT NULL, CHANGE authGuid authGuid VARCHAR(255) NOT NULL

$ php app/console doctrine:schema:update --force
Updating database schema...
Database schema updated successfully! "7" queries were executed

$

Comment by Christophe Coevoet [ 25/Sep/12 ]

hmm, the first update seems to miss the addition of the account field, which is done the second time through ALTER TABLE Tn ADD account INT DEFAULT NULL;





[DBAL-319] Doctrine\DBAL\Types\Type Created: 12/Aug/12  Updated: 22/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.2.2
Fix Version/s: None

Type: Improvement Priority: Minor
Reporter: Till Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

The API could be improved in the next major release:

  • Type::add() instead of addType()
  • Type::isRegistered() instead of hasType() (has sounds weird)

Etc.. I think the 'type' in the methods is redundant since Type is already the object I am dealing with.

I'd also like a remove() method to unregister a type at runtime. Would make testing a little easier and I don't have to check with hasType() etc..



 Comments   
Comment by Marco Pivetta [ 12/Aug/12 ]

I don't think those namings are really important.
There's one major change to do, which is to somehow get rid of the staticness of the `Doctrine\DBAL\Types\Type` class, and it is not a simple task.
It would also be interesting to set the type objects manually, such as a database `password` type that does encryption/decryption based on a given service. That cannot be done without staticness right now, which renders two different connections requiring the same functionality but with different parameters very difficult.

Comment by Till [ 12/Aug/12 ]

I agree on the static. But I'd also like the API to be cleaned up and the remove method.

Comment by Steve Müller [ 22/Dec/13 ]

API cleanup and staticness can be addressed in 3.0 for the first time. Otherwise we cannot keep BC. You can use Doctrine\DBAL\Types\Type::overrideType('someType', null); as a workaround for removing a type from the registry.





[DBAL-292] Multiple use of named parameter doesn't work Created: 12/Jun/12  Updated: 28/Dec/13

Status: Awaiting Feedback
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.2.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Jürgen Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

IIS 7.5, MS-SQL-Server 2005, PHP 5.4.0



 Description   

In the example in the documentation http://doctrine-dbal.readthedocs.org/en/2.0.x/reference/data-retrieval-and-manipulation.html#dynamic-parameters-and-prepared-statements there is the following example:

$sql = "SELECT * FROM users WHERE name = :name OR username = :name";
$stmt = $conn->prepare($sql);
$stmt->bindValue("name", $name);
$stmt->execute();

When I try this example using pdo_sqlsrv I get the following error:
PDOException: SQLSTATE[07002]: [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error

When I use instead the parameters name1 and name2 the query works as expected.



 Comments   
Comment by Alexander [ 07/Jul/12 ]

Are you sure you were using the 2.2.2 version of the ORM? Can you try to reproduce this with the latest master?

Comment by Steve Müller [ 28/Dec/13 ]

Jürgen ping. I cannot reproduce this error, either. Can you please try to reproduce this with the latest master branch? Otherwise I will close this ticket.





[DBAL-180] Documentation states that Doctrine 'decimal' (DecimalType) is mapped to PHP 'double', however, string is returned Created: 11/Nov/11  Updated: 28/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.1.2
Fix Version/s: None
Security Level: All

Type: Documentation Priority: Minor
Reporter: Menno Holtkamp Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

At http://www.doctrine-project.org/docs/orm/2.1/en/reference/basic-mapping.html#doctrine-mapping-types, it is stated that:

"decimal: Type that maps an SQL DECIMAL to a PHP double."

However, in the commit history, we can see that the casting to a float is removed: https://github.com/doctrine/dbal/commits/master/lib/Doctrine/DBAL/Types/DecimalType.php. Casting to a double is not possible in PHP?This seems to result in a float as well, that is probably why it was removed.

I found this out when using PHP's 'is_double()' function (alias of is_float()) to check whether a decimal property was set or not.

Suggestion is to either:

  • cast to a double (which seems not possible)
  • cast to a float (why was this removed?)
  • do nothing to the code, update documentation that string is returned.

In my check function I guess I will use the is_numeric() function.



 Comments   
Comment by Roel Harbers [ 19/Mar/12 ]

I would strongly suggest to leave the behaviour as-is, and fix the documentation, because of all the trouble associated with floating point and rounding. People use the DECIMAL type to prevent those issues, so having the ORM convert it to floating point again would be pretty bad.

Comment by Patrick McDougle [ 26/Apr/12 ]

I have submitted a pull request on this issue on github. Hopefully the doc will be updated soon so other people don't expect the wrong behavior!

https://github.com/doctrine/orm-documentation/pull/93

Mods, this issue can probably be closed.

Comment by Oleg Namaka [ 24/May/12 ]

Leaving decimal values as strings creates another issue with unnecessary entity updates because old and new same values have different types: old value is always the string type, the new one - decimal. If an old value is '10.00' as a string and the new value is 10 decimal than Doctrine will issue the UPDATE statement for that entity. This is plainly wrong IMHO.

Comment by karlie verkest [ 20/Dec/12 ]

There may be other issues around comparison. I'd rather be comparing numeric types than strings when comparing "decimal" values.

Comment by Steve Müller [ 28/Dec/13 ]

I don't think we can fix this in DBAL. We should leave the decimal values retrieved from database as string for the obvious conversion problems. AFAIK the documentation in ORM has also been updated. Don't know about ORM issues with entity updates, but I don't see any further todo here in DBAL. Can this ticket be closed?

Comment by Oleg Namaka [ 28/Dec/13 ]

@Steve Muller, is it feasible to change the way decimal values are compared from strict === to == to mitigate the issue with unnecessary updates:

If an old value is '10.00' as a string and the new value is 10 decimal than Doctrine will issue the UPDATE statement for that entity.

This is a very serious Doctrine shortcoming as for every single entity that has at least one decimal field with no real value changes updates still will be issued.

Comment by Steve Müller [ 28/Dec/13 ]

Oleg Namaka I understand that. But IMO this is an issue that has to be adressed in ORM because casting the values in the DBAL type breaks the precision/correctness of the original database value in some circumstances. Therefore the only possible solution can be to fix this in ORM IMO.





[DBAL-179] SQLLogger API improvement: log rows affected Created: 10/Nov/11  Updated: 10/Nov/11

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.1.2
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Minor
Reporter: Howard Ha Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

A small suggestion. It is useful in our application to log the affected rows in queries in order to make it easier to trace what queries did what. I think a simple way to enable this in the SQLLogger is to modify the stopQuery() interface to receive an option rowcount parameter.






[DBAL-175] Table comments in Doctrine\DBAL\Schema\Table Object Created: 06/Oct/11  Updated: 17/Oct/11

Status: Open
Project: Doctrine DBAL
Component/s: Platforms, Schema Managers
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Minor
Reporter: Asmir Mustafic Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Should be useful discover the table comments from database schema.

This feature is already available for column comments, but not for table comments






[DBAL-167] Schema comparator doesn't work properly with columnDefinition's Created: 17/Sep/11  Updated: 16/Jul/12

Status: Open
Project: Doctrine DBAL
Component/s: Drivers, Platforms, Schema Managers
Affects Version/s: 2.0.8, 2.1, 2.1.1, 2.1.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Dmitry Strygin Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 3
Labels: None


 Description   

Schema comparator will mostly always return changed properties on columns for entities defined with columnDefinition even they are identical in the DB. This is due to weak low-lever compatibility of SchemaTool#getCreateSchemaSql() and SchemaTool#getSchemaFromMetadata() – the first one doesn't reconstruct columnDefinition, and the other one never supports 'fixed', 'default', cannot determine, whether it is boolean or integer (ex. TINYINT in the DB), etc...

All this results in extremely annoying unnecessary alter-table-change-columns surrounded by dropping and after that re-enabling constrains dependent on those columns.

I mean stuff like this:

symfony2#app/console doctrine:schema:update --dump-sql
...
ALTER TABLE es_hotels DROP FOREIGN KEY FK_527F88EE584598A3F92F3E70;
ALTER TABLE es_hotels DROP FOREIGN KEY FK_527F88EE584598A37A3ABE5D;
ALTER TABLE es_hotels DROP FOREIGN KEY FK_527F88EE584598A3EE551564;
ALTER TABLE es_hotels CHANGE is_active is_active TINYINT(1) NOT NULL DEFAULT '1', CHANGE checksum checksum CHAR(32) DEFAULT NULL;
ALTER TABLE es_hotels ADD CONSTRAINT FK_527F88EE584598A3F92F3E70 FOREIGN KEY (operator_id, country_id) REFERENCES es_countries(operator_id, id) ON DELETE CASCADE;
ALTER TABLE es_hotels ADD CONSTRAINT FK_527F88EE584598A37A3ABE5D FOREIGN KEY (operator_id, resort_id) REFERENCES es_resorts(operator_id, id) ON DELETE CASCADE;
ALTER TABLE es_hotels ADD CONSTRAINT FK_527F88EE584598A3EE551564 FOREIGN KEY (operator_id, subresort_id) REFERENCES es_subresorts(operator_id, id) ON DELETE CASCADE;
...

The simple solution would be to fix schema comparator not to signal any changes on columns with columnDefinition properties.
But would be much and much better to add some code to all *SchemaManager#getPortableTableColumnDefinition so they would reconstuct columnDefinition and they would be matched in the schema comparator.

I can do this



 Comments   
Comment by Roderick Schaefer | We handle IT [ 16/Oct/11 ]

I'm having the same issue on my production webserver, but not on the development webserver. I find that odd. It tries to drop all foreign keys and create them again, although without the CHANGE statement you are referring to, Dmitry.

Comment by Benjamin Eberlei [ 09/Jan/12 ]

This maybe fixable by making a hash out of the column definition and saving it into a database comment.

The Foreign Key problem maybe because of an old MySQL version 5.0.x

Comment by Joe Cai [ 16/Jul/12 ]

@beberlei, sounds good to me. any plan of implementing this?





[DBAL-150] noSQL Shema Management Created: 21/Aug/11  Updated: 21/Aug/11

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Minor
Reporter: Alexey Shatunov Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

all



 Description   

I have a few ideas to improve DBAL and ORM management for php:
1. Reading if needed ORM schemas definitions from YAML(existing plugin)
2. Then migration it into the one of NoSQL databases(such as MongoDB or Memcache/MemcacheDB)
3. Creating transactional temporary-changes data layer in NoSQL for a current-changes due the webapp launch and running for each run
4. Periodically migrations of a current changes with migration script into RDB(for example by cron)

So we get:
Fast automatic shemas management(Symphony users - I say you "haha" because you have to do some unuseful things like "doctrine:build-schema" etc)
All advantages of RDB (like JOINS) in a data extraction
Full-independence in data changing due to webapp launch(and no transactions are needed) in the current NoSQL data-layer
Low load to the server because after extration we have a lazy return data into the RDB
In feature a simple caching any fast-changed data into NoSQL

What do you thinking about it?






[DBAL-876] [GH-574] Fixed the installation of HHVM nightly on Travis Created: 24/Apr/14  Updated: 24/Apr/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of stof:

Url: https://github.com/doctrine/dbal/pull/574

Message:

This is an attempt to fix the installation of HHVM nightly on Travis, which is failing because of dependency versions: https://travis-ci.org/doctrine/dbal/jobs/23685468

Once Travis updates its VM, we will get HHVM 3.0.1, but the process of deploying the new versions seems staled: https://github.com/travis-ci/travis-ci/issues/2126






[DBAL-801] add SECOND, MINUTE, WEEK into DATE_SUB, DATE_ADD Created: 04/Feb/14  Updated: 24/Apr/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Minor
Reporter: gondo Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: None


 Description   

currently only HOUR, MONTH, YEAR options are implemented
would be nice to have all of them but for now at least the major one would be fine, so to complete the list, i would like to see:
SECOND, MINUTE, WEEK to be implemented

im not sure if all the platforms are capable of this, so if anyone can verify that would be great.
after that, implementation is simple copy/paste of existing code with very minor changes.



 Comments   
Comment by Steve Müller [ 24/Apr/14 ]

Patch supplied in PR: https://github.com/doctrine/dbal/pull/575





Generated at Fri Apr 25 04:46:18 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.