[DBAL-1239] Comparator::compare() erroneously includes schema creation Created: 22/May/15  Updated: 22/May/15

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

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

DoctrineMigrations, PostgreSQL v9.4



 Description   

I reported the issue below to the Migrations project, but was told that the problem stems from DBAL's comparison of schema objects—

Whenever I use :diff against a PostgreSQL backend, the generated down migrations always start with CREATE SCHEMA.

This not only causes down migrations to fail (because the schema already exists and hence that command raises an error), but also results in :diff generating migrations even where no differences exist (they contain that command alone).






[DBAL-1238] [GH-863] Strip leading slash of databasename from URL Created: 21/May/15  Updated: 21/May/15

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-1234 Additional slash in dbname when provi... Open

 Description   

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

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

Message:

This is actuall the commit for DBAL-1234(http://www.doctrine-project.org/jira/browse/DBAL-1234)

When using a custom driver via `driverClass` in all (some?) cases one cannot set
the scheme properly. In the earlier implementation when the scheme is missing the
`DriverManager` leaves the leading slash in the path, because it silently assumes,
that this is a SQLite-connection. With custom drivers this leads to invalid database
names.

Additionally this takes care, that if one specifies the driver via configuration key
`driver`, but the connection with scheme-less URL it ends up in an invalid database
name too������

driver: pdo_mysql
������ url: //user:pass@localhost/database

Another solution is to introduce a special `custom`-scheme, that doesn't point to a driver, but declares, that `driverClass` is required

custom://foo:bar@localhost:123/my_db

However, this would not take care of the other use-case,






[DBAL-1234] Additional slash in dbname when providing settings as URL without scheme Created: 21/May/15  Updated: 21/May/15

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

Type: Bug Priority: Minor
Reporter: Sebastian Krebs Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates DBAL-1238 [GH-863] Strip leading slash of datab... Open

 Description   

Hi,

I use https://github.com/realestateconz/MssqlBundle to connect to an MSSQL-database and I'd like to provide the connection parameters as URL. Because dblib is not a supported driver I setup the driverClass instead

driver_class:   \Realestate\MssqlBundle\Driver\PDODblib\Driver

So the corresponding URL would look like

//user:pass@127.0.0.1/dabasename

But now it tries to connect to the database /databasename instead of databasename. I can set an arbitrary scheme here as long as it exists and is supported (and is not SQLite)

mysqli://user:pass@127.0.0.1/dabasename

Now it works, but it's a hack.

It seems, that the issue is here
https://github.com/doctrine/dbal/blob/32b1a4f85a078f67752851c27be4065071db1f8b/lib/Doctrine/DBAL/DriverManager.php#L262
As long as there is no scheme the leading slash remains. I'd guess, that it should also take into account, that there might be no driver name, but a concrete driverClass instead

(!isset($url['scheme']) && !isset(isset($url['driverClass']))

?






[DBAL-1237] [GH-862] Pass table object instead of table table name Created: 21/May/15  Updated: 21/May/15

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 mpoiriert:

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

Message:

On the MySQLPlatform::getDropForeignKeySQL table name will not be escaped if the name is passed instead of the object table itself.

Since the getLocalTableName use the localTable property the object is always available, there is no reason not to use it.






[DBAL-1236] [GH-861] Check for foreign table name on removed tables foreign key Created: 21/May/15  Updated: 21/May/15

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 mpoiriert:

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

Message:

According to the comment

// deleting duplicated foreign keys present on both on the orphanedForeignKey
// and the removedForeignKeys from changedTables

A check must be had on the $removedForeignKey so it does point on the removed table. Currently it does unset all keys removal even the one pointing on other table.

This should probably be added to a previous version since it's a bug fix but I don't know the exact flow you are following for this.






[DBAL-95] Interbase/Firebird support Created: 26/Feb/11  Updated: 20/May/15

Status: Awaiting Feedback
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: 6
Labels: None


 Description   

Implemented support for Interbase/Firebird dialects



 Comments   
Comment by Andreas Prucha [ 17/Apr/15 ]

Hi all,

I've create a driver (or two) for Firebird.

The development branch is available at

https://github.com/helicon-os/doctrine-dbal.git DBAL-95-firebird

There are two versions of the driver: One is based on the ibase-api, there other on Firebird PDO.

I'd consider the ibase-version as almost finished, the PDO-Version is rather experimental.

Just send me a comment and maybe we can merge it into the official doctrine dbal (propably just the ibase-version, not the PDO-Version - at least not as long the driver problems are not fixed)

I've tested it with FB 2.5, but not with the upcoming FB 3 or Interbase.

___________________
ibase_firebird

The Ibase-Driver passes the complete Doctrine-Testsuite.

___________________
pdo_firebird

The PDO-Version does not, and it has some limitations which may be related to Firebirds PDO interface itself:

  • BLOBs: Runs into memory leaks if BLOBs are used quite quickly
  • PDO Firebird's transaction handling is quite strange and the reason why it does not pass the testsuite.
  • Nested Transactions (Savepoints): Fails despite Firebird supports them. -
  • Turns a negative signed 32bit integer into a positive unsigned, if the client is running a 64bit-system. I fixed this with a special IntegerType Class in a project, but that's a ugly workaround.
Comment by Andreas Prucha [ 17/Apr/15 ]

One more comment:

I am not sure how to handle one firebird-specificity: Firebird does not preserve the case of identity-names and converts them all-upper, unless they are quoted. The behaviour looks quite similar to Oracle.

Currently I do not normalize names as the Oracle-driver does, which leads to the problem, that they are all-upper in the database anyway, but automatically quoted keywords are lowercase.

Which behaviour would you guys prefere:

Normalize them All-Upper if not quoted (including keywords)? This would allow case-insensitive references in queries, because Firebird handles unquoted names as all-upper.
Quote everything to preserve case: Unfortunately this would require manual quoting in every query.

Personally I do not really like this all-upper-pattern, but having to quote every identifier everywhere looks even more cumbersome, so it's propably the best to follow the behaviour of the oracle driver and assume uppercase if not quoted.

Does any platform have configuration-options? It might be a solution to let the user decide about the naming, e.g.

setNamingConvention(ALL_UPPER | PRESERVE_CASE)

BTW - Is the doctrine-dev mailinglist gone? I wanted to send this there, but it came back with an error.

Andreas

Comment by Andreas Prucha [ 06/May/15 ]

Is it possible to setup a firebird test environment at travis?

Comment by Jürgen [ 20/May/15 ]

Hello Andreas,

good to hear about your work!

As a long time firebird user I can say that for me it is common to use column names case insensitive. So if you normalize all SQL commands to upper case (except quoted names) is the natural usage in firebird for me.

Jürgen





[DBAL-1168] Schema's getMigrateFromSql always adds CREATE SCHEMA Created: 11/Mar/15  Updated: 19/May/15

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

Type: Bug Priority: Major
Reporter: Varga Bence Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: postgresql, schematool
Environment:

Postgresql



 Description   

I originally posted this to Migrations; noticing that all the generated down() methods start with a "CREATE SCHEMA public" line.

Inspecting the return from Schema#getMigrateFromSql it indeed contains the create statement.



 Comments   
Comment by Adam Sentner [ 19/May/15 ]

I am also having this issue. The down() method always adds: $this->addSql('CREATE SCHEMA public');

Same environment, also using Postgres.

Any chance this is on anyone's radar for a release in the near future?





[DBAL-1233] TEXT type in MSSQL should be NVARCHAR(MAX) not VARCHAR(MAX) Created: 19/May/15  Updated: 19/May/15

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: Javad Rahimi Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mssql, nvarchar(max), schema, text, validator


 Description   

If a field type is defined as "TEXT" by generating the schema in MSSQL Server it generates a field type as "VARCHAR(MAX)". There will be no problem unless some UTF8 characters be inserted to DB; they all will be saved as "?????". If the field type be changed to "NVARCHAR(MAX)" there will be no problem and UTF8 characters will saved properly.
In this case I changed the DBAL core for SQL server as:

DoctrineDBALplatformsSQLServer2005Platform.php
public function getClobTypeDeclartionSQL(array $field)
{
     return 'NVARCHAR(MAX)';
}

This fixed the main issue but after I generate the schema, whenever I validate my schema, it returns false on DB level.
Could anybody help me in this case? Is there any other fixes I need to do?

Appreciate it in advance.






[DBAL-1207] Schema Update Issue with DBAL 2.5 Binary Type Created: 24/Apr/15  Updated: 18/May/15

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

Type: Bug Priority: Minor
Reporter: Alex Gurrola Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: schematool
Environment:

Ubuntu 14.04.2 LTS, Apache 2.4.7, PHP 2.5.9, MySQL 5.5, Symfony 2.6.6



 Description   

Every time I run a doctrine:schema:update command within Symfony, using DBAL 2.5, it tries to execute this SQL Query, every single time:

SQL Query
ALTER TABLE user_sessions CHANGE sess_id sess_id VARBINARY(128) NOT NULL;

The PHP Annotations I am using for this column is as follows:

Binary Column
/**
 * @var string
 *
 * @ORM\Column(name="sess_id", type="binary", length=128, nullable=false)
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="IDENTITY")
 */
private $sessId;

It seems the binary type is somehow registering a change even though no change has actually been made. I updated to DBAL to 2.5 before getting the binary type supported by the doctrine:schema:update command.

Thanks in advance for any assistance in squashing this odd bug.

--Alex Gurrola



 Comments   
Comment by Nate Baker [ 15/May/15 ]

Do you intentionally have @ORM\GeneratedValue(strategy="IDENTITY"), or is that there from an auto import? I had the same problem but if I changed to strategy="NONE" it doesn't happen anymore. See this issue: http://www.doctrine-project.org/jira/browse/DBAL-353

Comment by Alex Gurrola [ 18/May/15 ]

It was an auto import. Your link resolved the issue, thanks.





[DBAL-1232] [GH-856] MySQL getListTableForeignKeysSQL: use current database if null passed Created: 18/May/15  Updated: 18/May/15

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 naderman:

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

Message:

In line with the behavior of `getListTableIndexesSQL()` the foreign key function should select only foreign keys from the current database if no database name is specified. Otherwise it returns foreign keys of all tables in any database with the given name. This can especially lead to issues if you install different versions of the same schema into multiple databases on the same server.

This function is always called with `$database = null` in the following chain, which leads to SQL errors when trying to setup/delete a schema in tests on a mysql server that contains another copy of the schema in another database:

```
Doctrine\ORM\Tools\SchemaTool::dropDatabase()
Doctrine\ORM\Tools\SchemaTool::getDropDatabaseSQL()
Doctrine\DBAL\Schema\AbstractSchemaManager::createSchema()
Doctrine\DBAL\Schema\AbstractSchemaManager::listTables()
Doctrine\DBAL\Schema\AbstractSchemaManager::listTableDetails($tableName)
Doctrine\DBAL\Schema\AbstractSchemaManager::listTableForeignKeys($table, null)
Doctrine\DBAL\Platforms\MySqlPlatform::getListTableForeignKeysSQL($table, null)
```

I think the `$database` parameter would ideally be required, and an exception should be thrown if it is null. The AbstractSchemaManager should be modified to consistently pass the database name to the platform in all its calls. But for now this workaround corrects the issue for foreign keys.






[DBAL-1231] [GH-855] Connection::ping() no longer produces warnings on connection timeout Created: 16/May/15  Updated: 16/May/15

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 fprochazka:

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

Message:

With PDOMySQL (I haven't tested other drivers), the ping call produces warnings, that IMHO should be handled by the library.

Because the test suite is ignoring warnings and notices, I assume nobody noticed this before. But our application has strict no-errors policy.
I've created similar temporary-hotfix https://github.com/Kdyby/Doctrine/commit/f7250e5b771eb1ba6c0abe23cb2dc689247d1b4c for my integration lib with Nette, but I believe that this is best handled on the library level.

I was considering adding a global error handler to bootstrap file, but that broke several other tests so that should be IMHO taken care of in separate pullrq.






[DBAL-1230] timestamp not supported, although no timestamp is ever defined Created: 15/May/15  Updated: 15/May/15

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

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

windows 8, sqlserver2008



 Description   

as I tried to run doctrine validate or schema update, following error message came up, although NO timestamp data type is ever used in my bundle.

[Doctrine\DBAL\DBALException] Unknown database type timestamp requested, Doctrine\DBAL\Platforms\SQLServer2008Platform may not support it






[DBAL-1229] Escape underscore when ignoring pg_ schemas in PostgreSqlPlatform Created: 12/May/15  Updated: 12/May/15

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

Type: Bug Priority: Minor
Reporter: Tom Ploskina Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

In PostgreSqlPlatform.php, there are 3 lines (240,264,263) which query for schema by:

LIKE 'pg_%'

The underscore needs to be escaped:

LIKE 'pgBACKSLASH_%'

If you have a schema that starts with pg, for example, "pglims", the _ will not be evaluated and the tables, views and sequences returned will not include your tables. Our company's initials are PG by coincidence so we name our schemas accordingly. I will create a pull request for this issue.






[DBAL-1228] [GH-854] DateInterval Type Created: 09/May/15  Updated: 09/May/15

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 v-bartusevicius:

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

Message:

Added DateInterval Type to store/receive PHP DateInterval objects.
Internally DateInterval is converted to string to store in VARCHAR field.
For maximum performance full DateInterval format is used when converting to database value.






[DBAL-1227] Comparator finding diff for custom mapping type Created: 07/May/15  Updated: 07/May/15

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

Type: Bug Priority: Major
Reporter: Danny van der Sluijs Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal, mapping, schematool


 Description   

When having a custom mapping type Filter

<?php

namespace MyProject\DBAL\Types;

use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use MyProject\Types\Filter as FilterType;

class Filter extends Type
{
    const TYPE = 'filter';

    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        return new FilterType($value);
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        return (string) $value;
    }

    public function getName()
    {
        return Filter::TYPE;
    }
}

The cli command orm:schema-tool:update keeps outputting the changes:

ALTER TABLE my_table ALTER filter TYPE VARCHAR(255);
ALTER TABLE my_table ALTER filter DROP DEFAULT;

When doing some debugging I've found this is caused by //lib/Doctrine/DBAL/Schema/Comparator.php at line 429
https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/Comparator.php#L429

Where the compared values are:
Doctrine\DBAL\Types\StringType vs MyProject\DBAL\Types\Filter

In the application bootstrap the custom type is registered as a type, and is registered as a doctrine type mapping

Type::addType('filter', '\PersonalWebsite\DBAL\Types\Filter');

        $conn = $em->getConnection();
        $conn->getDatabasePlatform()->registerDoctrineTypeMapping('filter', 'filter');

        return $this;





[DBAL-1225] [GH-852] Add SQL Anywhere builds to Travis Created: 06/May/15  Updated: 06/May/15

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/852

Message:

This is an attempt to enable functional testing for SQL Anywhere 12 + 16 on Travis.
It uses precompiled `sqlanywhere` PHP extensions for 5.3, 5.4, 5.5 and 5.6. Server-wise it uses preinstalled, lightweight packages (around 15 MB). This allows for a fast environment setup.
The packages reside on my Google Drive account, the URLs for the server packages are secured by Travis encryption.
The only downside of using secured environment variables is, that builds won't work on forks because of different SSH keys but that is a fair trade-off IMO.

/cc @zeroedin-bill @Ocramius @beberlei thoughts?






[DBAL-1224] [GH-851] Change MySQL defaults from broken utf8 to fixed utf8mb4 Created: 04/May/15  Updated: 06/May/15

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 DHager:

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

Message:

This is a conservative echo of https://github.com/doctrine/dbal/pull/317 .

Essentially MySQL's `uft8` character set is broken and does not support full UTF-8, and a better alternative, `utf8mb4` has existed for about five years now. Insofar as Doctrine has a MySQL default configuration, `utf8mb4` is a better choice.



 Comments   
Comment by Doctrine Bot [ 05/May/15 ]

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

Comment by Doctrine Bot [ 06/May/15 ]

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





[DBAL-1222] [GH-850] Allow to specify a charset and collation per column for Mysql Created: 03/May/15  Updated: 03/May/15

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 Freeaqingme:

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

Message:






[DBAL-1220] [GH-849] Fix dropping database with active connection on PostgreSQL Created: 01/May/15  Updated: 01/May/15

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/849

Message:

PostgreSQL fails to drop a database if there are active connections using that particular database.
This PR closes all active connections before dropping the database if dropping the database failed before.






[DBAL-1219] [GH-848] [DBAL-1219] Add missing functional driver test cases Created: 30/Apr/15  Updated: 30/Apr/15

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/848

Message:






[DBAL-1218] [GH-847] [DBAL-1217] Fix retrieving the database name connected to for SQL Anywhere Created: 30/Apr/15  Updated: 30/Apr/15

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/847

Message:

When connecting to SQL Anywhere without `dbname` parameter set, the underlying driver emits a `undefined index "dbname"` when trying to retrieve the database name connected to via `Doctrine\DBAL\Driver::getDatabase()`.
This PR implements the "live" retrieval of the current database as seen in other drivers already.






[DBAL-1217] [GH-846] Fix retrieving the database name connected to for SQL Server Created: 30/Apr/15  Updated: 30/Apr/15

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/846

Message:

When connecting to SQL Server without `dbname` parameter set, the underlying driver emits a `undefined index "dbname"` when trying to retrieve the database name connected to via `Doctrine\DBAL\Driver::getDatabase()`.
This PR implements the "live" retrieval of the current database as seen in other drivers already.






[DBAL-1190] [GH-829] Pgsql connection test with charset parameter Created: 02/Apr/15  Updated: 30/Apr/15

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

Type: Improvement Priority: Minor
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: charset, client_encoding, pdo_pgsql, pgbouncer, pgsql, postgresql, testing


 Description   

This issue is created automatically through a Github pull request on behalf of zeroedin-bill:

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

Message:

This PR adds 2 tests for connecting to pgsql via PDOPgSql, while using a charset parameter.

Related to #828, #823



 Comments   
Comment by Doctrine Bot [ 30/Apr/15 ]

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





[DBAL-1214] MySQL has gone away using ImportCommand Created: 29/Apr/15  Updated: 29/Apr/15

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

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


 Description   

Importing a fairly large SQL file (a MySQL dump) fails with a MySQL has gone away error.

When reading the ImportCommand code, I see that this issue is already "fixed" by checking if the connection is an instance of `\Doctrine\DBAL\Driver\PDOConnection`.

The problem is that I don't see how the connection could be an instance of this class since it doesn't extend `\Doctrine\DBAL\Connection`.

If I'm wrong, then I don't know how to configure my connection to extend the PDOConnection class. Thanks for your help on that






[DBAL-96] Make approach towards identifier quoting consistent Created: 26/Feb/11  Updated: 28/Apr/15

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

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

Issue Links:
Duplicate
is duplicated by DBAL-120 MySql platform getAlterTableSQL does ... Resolved
Reference
relates to DBAL-45 Add CLI tool that checks for Reserved... Resolved
relates to DBAL-477 Just doublequote all schema names and... Open
is referenced by DBAL-40 Transparent table&column names escaping Open

 Description   
  • Make the use of `` a general approach for explicit quoting of identifiers
  • introduce AbstractPlatform::getRegularSQLIdentifierCase($identifier)
  • Introduce AbstractPlatform::isRegularIdentifier($identifier)
  • Fix Schema Assets not to lower-case, but to check for explicit quoting before.
  • Filter values of identifiers passed to all platform functions when they are used in information schema queries according to `` explicit quoting rules.

Problem: Schema is independent of a vendor, this means we have to pick a behavior, i propose SQL-92

This means:

  • strtoupper() ALL tables, column, index, foreign key names that are not quoted by ``
  • For any Quoted identifiers by `` the case is kept.
  • We can introduce a validator to detect a schema that cannot be implemented with a given vendor platform.

In conjunction with the SQL reserved keywords tickets we can then improve the DatabaseDriver considerably to detect identifier casings



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

Benjamin Eberlei this is an interesting approach and I like it. But I have some complaints about it.
1. I doubt users will be happy about forced default casing rules (ALL upper or ALL lower). Therefore we should think about adding a simple configuration option in DBAL allowing to override the default casing behaviour to the user's preference.
2. Using a consistent default casing means we ALWAYS have to quote identifiers as otherwise the underlying database could silently change the case again (don't know if this is an issue).
3. Introducing this approach in 2.x branch is a BC break as it breaks users' mixed-case identifier mappings.

For 2.x we should maybe at least make use of Identifier class throughout the platforms where necessary.

Comment by Sebastien Lavoie [ 28/Mar/15 ]

My 2 cents:

1. Users should not have to worry about platform-specific quoting when using the query builder or helpers, the DBAL should do that for you.
2. Users should be able to explicitly quote using a standard quote (`), the quote would then be converted to the platform’s quote upon SQL generation, without any case change.
3. DBAL should not needlessly quote, it adds bloat and it has been said in DBAL-40 that there is a performance hit.
4. DBAL should not change the case without the user’s knowledge.
5. A connection configuration option (normalize_case) could be added:
• uppercase: always convert unquoted identifiers to uppercase
• lowercase: always convert unquoted identifiers to lowercase
• platform: will use the default value for specific platform. For the case of case-sensitive platform, even when unquoted (MySQL on UNIX), do nothing.
• null (default): no normalization
6. Future versions of DBAL could change the default value to platform, but this would greatly reduce the risk of causing BC breaks at the beginning, giving time to test everything.
7. When using Doctrine\DBAL\Connection::query directly, you must do the quoting yourself since the SQL is executed directly.

Comment by Arthur Bodera [ 28/Mar/15 ]

Sebastien, ad 3. that is incorrect. Read the ticket more closely, look at the PR, look inside schema tool and platform classes. There is already a lot of quoting+unquoting being performed in 2.* and a lot of assumptions. Having quoting enabled across the board might actually increase performance in some cases, because there will be less scanning for keywords (see platform classes) and possibly less quoting/unquoting across Schema*.

The problem is, the quoting right now works in some places and in some platforms and is being performed only when schema/schematool/dql needs it, but is being ignored in all other cases. This means that columns like "group" or table names like "platform" will fail randomly depending on platform/rdbms you actually use. It's a nightmare with cross-platform apps and a struggle for single-platform apps, where your tables are named according to domain-rules and happen to overlap with some rdbms.

Quoting identifiers being "a bloat" is similar to saying, that implicit quoting values is a bloat. Although from security standpoint the former is much rarer, it's the same for portability and stability of the DBAL across platforms.

Comment by Andreas Prucha [ 28/Apr/15 ]

IMO the big problem is, that behaviour across the RDBMs may be completely different:

Some preserve case and are case-insenstive if not quoted (the nicest approach)
Some do not preserve case and normalize to lower or uppercase and are case-insensitive
And some do not preserve case and are case-sensitive (the worst)

The biggest issue arises if the DB needs to be used outside the Doctrine-Environment and all identifiers need to be quoted in statements.





[DBAL-1212] Array as result of getXxxxSql functions in Platforms Created: 28/Apr/15  Updated: 28/Apr/15

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

Type: Improvement Priority: Minor
Reporter: Andreas Prucha Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

It's possible to return an array of SQL-Statements in getAlterTableSql, CreateTableSql etc because the Schema-Classes handle them as an array and merge the result. Unfortunately in many cases (like getDropTable), the result is not merged, but addes as single array item:

$sql[] = $platform->getDropTableSQL($table);

Thus, it's not possible to return multiple statements there. This does not hurt in most cases, but drivers might need to perform additional statements (e.g. drop a trigger or drop related views).

This is obviously the case in the Oracle-Driver and it's also necessary in the Firebird driver.

I think it might be better to allow arrays as result everywhere. Of course it's possible to workaround the limitation by combing multiple statements into a single "execute block"-statement, but an array of statements as result would be cleaner and easier.






[DBAL-1211] Wrapper Class should enforce a Interface not a Subclass Created: 27/Apr/15  Updated: 27/Apr/15

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

Type: Improvement Priority: Major
Reporter: Flavio Botelho Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal


 Description   

We are creating a Wrapper Class to allow the use of the Oracle Proxy User feature. For that I need to Wrapper a Class around DBAL\Connection.
Unfortunely, the wrapper class needs to be a subclass of DBAL\Connection which doesn't make sense, there should exist an Interface and the wrapper class should be forced to implement that interface.

That way I don't need to create methods to call all DBAL\Connection methods thru polymorphism.






[DBAL-1210] [GH-842] Fixed incorrect handling of single quotes in SQL-Strings Created: 26/Apr/15  Updated: 26/Apr/15

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 ancpru:

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

Message:

escaped by repeated single-quote (DBAL-1205)






[DBAL-1208] [GH-840] Driver for SQLite3 Created: 25/Apr/15  Updated: 25/Apr/15

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/840

Message:

This PR adds an additional driver using the [SQLite3](http://php.net/manual/fr/book.sqlite3.php) class, in addition to the PDO_SQLITE driver.

    1. Why this driver?

Even though the PDO SQLite driver is already available to interact with SQLite databases, PDO currently has a big limitation: it [does not allow to load SQLite extensions](https://bugs.php.net/bug.php?id=64810).

This functionality is provided by the `SQLite3` class, which becomes your only option if you need to use your PHP application with an SQLite extension such as [SpatiaLite](http://www.gaia-gis.it/gaia-sins/).






[DBAL-1206] Generating Table SQL without indexes is invalid if using AUTO_INCREMENT Created: 24/Apr/15  Updated: 24/Apr/15

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

Type: Bug Priority: Minor
Reporter: Markus Fasselt Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

MySQL (but maybe other database vendors too)



 Description   

Dumping the following table

CREATE TABLE `users` (
    `id` INT AUTO_INCREMENT NOT NULL,
    PRIMARY KEY (`id`)
);

with the following snippet (0 = NoIndexes)

$platform->getCreateTableSQL($table, 0);

results in


CREATE TABLE `users` (
    `id` INT AUTO_INCREMENT NOT NULL,
);

The problem is, that the table contains an AUTO_INCREMENT column which cannot be used without a primary key. But the primary key is skipped, as I skipped all indexes.

As this SQL is invalid, I suggest to skip the AUTO_INCREMENT argument, too, if the indexes are skipped. Alternatively, the Primary Key always has to be included.

What do you think? I can provide a fix, if you agree with me.






[DBAL-1205] getPlaceholderPositions finds placeholders which are actually no placeholder if string contains single quotes Created: 24/Apr/15  Updated: 24/Apr/15

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

Type: Bug Priority: Critical
Reporter: Andreas Prucha Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

The following statement obviously does not contain any parameters:

select
'quoted1 '' :not_a_param1 quoted2 "'':not_a_param2''" ''' foo
from rdb$database

But the following call:

$params = \Doctrine\DBAL\SQLParserUtils::getPlaceholderPositions
('select \'quoted1 \'\' :not_a_param1 quoted2 "\'\':not_a_param2\'\'" \'\'\' foo from rdb$database', false);

returns

(
[19] => not_a_param1
)

It seems that getUnquotedStatementFragments() does not handle escaping by doubling single quotes correctly.






[DBAL-1204] Description of SQLParserUtils::getPlaceholderPositions() misleading Created: 24/Apr/15  Updated: 24/Apr/15

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

Type: Documentation Priority: Trivial
Reporter: Andreas Prucha Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

The description of this function is slightly misleading:

>> Returns an integer => integer pair (indexed from zero) for a positional statement and a string => int[] pair for a named statement. <<

This seems to be correct for positional parameters, but wrong for named parameters. According to the description i would expect the parameter names as keys and the positions as values, but the function returns an array with positions as key, and the parameter name of the position as value.






[DBAL-1202] JoinTable causes table already exists exception to be flung Created: 22/Apr/15  Updated: 22/Apr/15

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

Type: Bug Priority: Major
Reporter: Iain Cambridge Assignee: Marco Pivetta
Resolution: Unresolved Votes: 0
Labels: None


 Description   
/**
     * @var Collection
     * @ORM\ManyToMany(targetEntity="Foodpanda\Bundle\Core\CmsBundle\Entity\Tag", inversedBy="cmsPages")
     * @JoinTable(name="CmsCmsTags")
    */

The @JoinTable causes the exception to be flung when running schema create



 Comments   
Comment by Marco Pivetta [ 22/Apr/15 ]

Can't reproduce: please design a test case around the failure.

Comment by Iain Cambridge [ 22/Apr/15 ]

Can what you did to try and reproduce?

Comment by Iain Cambridge [ 22/Apr/15 ]

Also how are you guys even testing this? I can't see, therefore can't write a breaking test.

Comment by Marco Pivetta [ 22/Apr/15 ]

Iain Cambridge I created a test like following:

<?php

namespace Doctrine\Tests\ORM\Functional\Ticket;

/**
 * @group DDC-1452
 */
class DDC1452Test extends \Doctrine\Tests\OrmFunctionalTestCase
{
    protected function setUp()
    {
        parent::setUp();

        try {
            $this->_schemaTool->dropSchema(array(
                $this->_em->getClassMetadata(DDC9999EntityA::CLASSNAME),
                $this->_em->getClassMetadata(DDC9999EntityB::CLASSNAME),
            ));
        } catch (\Exception $e) {
            // ignored
        }
    }

    public function testIssue()
    {
        $this->_schemaTool->createSchema(array(
            $this->_em->getClassMetadata(DDC9999EntityA::CLASSNAME),
            $this->_em->getClassMetadata(DDC9999EntityB::CLASSNAME),
        ));
    }
}

/** @Entity */
class DDC9999EntityA
{
    const CLASSNAME = __CLASS__;

    /** @Id @Column(type="integer") @GeneratedValue */
    private $id;

    /**
     * @ORM\ManyToMany(targetEntity=DDC9999EntityB::class)
     * @JoinTable(name="CmsCmsTags")
     */
    private $b;
}

/** @Entity */
class DDC9999EntityB
{
    const CLASSNAME = __CLASS__;

    /** @Id @Column(type="integer") @GeneratedValue */
    private $id;
}
Comment by Iain Cambridge [ 22/Apr/15 ]

Thanks, so the test has to be in the ORM package?

Comment by Marco Pivetta [ 22/Apr/15 ]

Iain Cambridge yes, since it seems to be a mapping issue - see https://github.com/doctrine/doctrine2/tree/v2.5.0/tests/Doctrine/Tests/ORM/Functional/Ticket

Comment by Iain Cambridge [ 22/Apr/15 ]

This below fails for me.

<?php

namespace Doctrine\Tests\ORM\Functional\Ticket;

/**
 * @group DBAL-1202
 */
class DBAL1202Test extends \Doctrine\Tests\OrmFunctionalTestCase
{
    protected function setUp()
    {
        parent::setUp();

        try {
            $this->_schemaTool->dropSchema(array(
                $this->_em->getClassMetadata(DDC9999EntityA::CLASSNAME),
                $this->_em->getClassMetadata(DDC9999EntityB::CLASSNAME),
                $this->_em->getClassMetadata(DDC9999EntityC::CLASSNAME),
            ));
        } catch (\Exception $e) {
            // ignored
        }
    }

    public function testIssue()
    {
      var_dump($this->_em->getClassMetadata(DDC9999EntityA::CLASSNAME));
        $this->_schemaTool->createSchema(array(
            $this->_em->getClassMetadata(DDC9999EntityA::CLASSNAME),
            $this->_em->getClassMetadata(DDC9999EntityB::CLASSNAME),
            $this->_em->getClassMetadata(DDC9999EntityC::CLASSNAME),
        ));
    }
}

/** @Entity */
class DDC9999EntityA
{
    const CLASSNAME = __CLASS__;

    /** @Id @Column(type="integer") @GeneratedValue */
    private $id;

    /**

     * @ManyToMany(targetEntity=DDC9999EntityC::class)
     * @JoinTable(name="CmsCmsTags")
     */
    private $b;
}

/**
 * @Entity
 * @Table(
 *      name="CmsCmstags",
 * )
 */
class DDC9999EntityB
{
    const CLASSNAME = __CLASS__;

    /** @Id @Column(type="integer") @GeneratedValue */
    private $id;

}

/**
  * @Entity
  * @Table(
  *      name="Cmstags",
  * )
  */
class DDC9999EntityC
{
    const CLASSNAME = __CLASS__;

    /** @Id @Column(type="integer") @GeneratedValue */
    private $id;
}

Comment by Marco Pivetta [ 22/Apr/15 ]

Seems an obvious failure to me - two declarations for the same table. What's the bug?

Comment by Iain Cambridge [ 22/Apr/15 ]

One entity uses cmscmstags and another cmstags. (Don't ask me why, just started)

Comment by Marco Pivetta [ 22/Apr/15 ]

Iain Cambridge I see that DDC9999EntityB has @Table(name="CmsCmstags"), and there is also a @JoinTable(name="CmsCmsTags")

Comment by Iain Cambridge [ 22/Apr/15 ]

I thought @table defines the table name for an entity. With @jointable defines the table to be used for a join. Are you saying they both define tables?

Comment by Marco Pivetta [ 22/Apr/15 ]

Yes, both cause a new table to be created on the DB.

Comment by Iain Cambridge [ 22/Apr/15 ]

I figured they would caused tables to be created but I wouldn't expect defining a join to cause an exception of defining a table twice. Especially since it seems quite possible and logical to use @JoinTable more than once?

Another quite possible and logical example would be one big entity and then only wanting a subset of that data for a join so use a smaller entity to hold that data.

Not saying the use case provided is exactly sane, however I would expect it to work.





[DBAL-1203] [GH-839] Dbal 1200 Created: 22/Apr/15  Updated: 22/Apr/15

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 jbh-:

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

Message:

Add order by support in modify limit function for DB2. This takes care of DBAL-122(http://www.doctrine-project.org/jira/browse/DBAL-1200).

The current tests pass.






[DBAL-1201] [GH-838] DBAL-95 Firebird Driver, Platform and Schema Manager Created: 22/Apr/15  Updated: 22/Apr/15

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 ancpru:

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

Message:

Hi,

I've implemented the driver for Firebird. It's currently tested with FB 2.5.

The driver uses the ibase-api. Originally I also had a PDO-based implementation. Despite it worked quite well in a real world application, it terribly failed in the Doctrine test suite because of strange transaction behaviour of the Firebird-PDO, thus I finally removed it.

The name of the driver is ibase_firebird, the platform name is firebird.

Because of the common ancestor Firebird an Interbase, it should be possible to use the implementation for Interbase, too. But this is not done nore tested, yet.

Andreas






[DBAL-1200] DB2 Platform doModifyLimitQuery ORDER BY Created: 21/Apr/15  Updated: 21/Apr/15

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

Type: Bug Priority: Minor
Reporter: Mark Gullings Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

IBM i v7r1



 Description   

Implement TODO note in doModifyLimitQuery

DB2Platform.php
    protected function doModifyLimitQuery($query, $limit, $offset = null)
    {
        if ($limit === null && $offset === null) {
            return $query;
        }
        $limit = (int) $limit;
        $offset = (int) (($offset)?:0);
        // Todo OVER() needs ORDER BY data!
        $sql = 'SELECT db22.* FROM (SELECT ROW_NUMBER() OVER() AS DC_ROWNUM, db21.* '.
               'FROM (' . $query . ') db21) db22 WHERE db22.DC_ROWNUM BETWEEN ' . ($offset+1) .' AND ' . ($offset+$limit);
        return $sql;
    }


 Comments   
Comment by Mark Gullings [ 21/Apr/15 ]

solution is in testing locally





[DBAL-1193] "requiresSQLCommentHint" is ignored by migration if the SQL type does not change Created: 08/Apr/15  Updated: 08/Apr/15

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

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


 Description   

I have found a previous issue, http://www.doctrine-project.org/jira/browse/DBAL-1085 which tells about the need to override "requiresSQLCommentHint" if the custom type uses an SQL type which is already known by Doctrine.

See also:
https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Types/Type.php#L327-L340

The bug: After I learned this and added the required TRUE return value, the migration (generated by "diff") won't contain the comment.

After adding the comment manually, everything works as expected.






[DBAL-1188] Oracle Platform: List table columns are returned in the wrong order Created: 01/Apr/15  Updated: 01/Apr/15

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

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


 Description   

The function:

Doctrine\DBAL\Platforms\OraclePlatform::getListTableColumnsSQL

returns the columns sorted by name. This causes a problem in my application. Other platforms sort them on position. So when I build a model for a HTML table (placing datatype formatters for each column) the order of formatters gets messed up.

The equivalent method in other platforms like MySQL or PostgreSQL all make sure the order is preserved (the same as in the database).

To fix I changed

...ORDER BY c.column_name

to

...ORDER BY c.column_id





[DBAL-1184] [GH-824] Added Postgres 9.4 platform (DBAL-1143) Created: 29/Mar/15  Updated: 29/Mar/15

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 mbeccati:

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

Message:

Features and changes:

  • jsonb can be used with: options= {"jsonb"=true}
  • OVER is no longer reserved
  • LATERAL is now reserved





[DBAL-40] Transparent table&column names escaping Created: 05/Aug/10  Updated: 28/Mar/15

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: 13
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

Comment by Sebastien Lavoie [ 28/Mar/15 ]

My 2 cents from DBAL-96:

1. Users should not have to worry about platform-specific quoting when using the query builder or helpers, the DBAL should do that for you.
2. Users should be able to explicitly quote using a standard quote (`), the quote would then be converted to the platform’s quote upon SQL generation, without any case change.
3. DBAL should not needlessly quote, it adds bloat and it has been said that there is a performance hit.
4. DBAL should not change the case without the user’s knowledge.
5. A connection configuration option (normalize_case) could be added:
• uppercase: always convert unquoted identifiers to uppercase
• lowercase: always convert unquoted identifiers to lowercase
• platform: will use the default value for specific platform. For the case of case-sensitive platform, even when unquoted (MySQL on UNIX), do nothing.
• null (default): no normalization
6. Future versions of DBAL could change the default value to platform, but this would greatly reduce the risk of causing BC breaks at the beginning, giving time to test everything.
7. When using Doctrine\DBAL\Connection::query directly, you must do the quoting yourself since the SQL is executed directly.

Comment by Arthur Bodera [ 28/Mar/15 ]

Sebastien, ad 3. that is incorrect. Read the ticket more closely, look at the PR, look inside schema tool and platform classes. There is already a lot of quoting+unquoting being performed in 2.* and a lot of assumptions. Having quoting enabled across the board might actually increase performance in some cases, because there will be less scanning for keywords (see platform classes) and possibly less quoting/unquoting across Schema*.

The problem is, the quoting right now works in some places and in some platforms and is being performed only when schema/schematool/dql needs it, but is being ignored in all other cases. This means that columns like "group" or table names like "platform" will fail randomly depending on platform/rdbms you actually use. It's a nightmare with cross-platform apps and a struggle for single-platform apps, where your tables are named according to domain-rules and happen to overlap with some rdbms.

Quoting identifiers being "a bloat" is similar to saying, that implicit quoting values is a bloat. Although from security standpoint the former is much rarer, it's the same for portability and stability of the DBAL across platforms.





[DBAL-732] MySQL 5.6 - Cannot change column 'fkProjectId': used in a foreign key constraint Created: 24/Dec/13  Updated: 27/Mar/15

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: 8
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.

Comment by Vyacheslav Petrov [ 27/Mar/15 ]

I have the same issue

Comment by Maxim Mukhin [ 27/Mar/15 ]

The same issue in the project.





[DBAL-1182] No schema difference detected when changing length of a text field Created: 26/Mar/15  Updated: 26/Mar/15

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

Type: Bug Priority: Major
Reporter: Evan Sheffield Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal, mysql, schema-tool
Environment:

Database: MySQL 5.6



 Description   

I have a text field with a length specified that causes it to be chosen as TINYTEXT in MySQL.

/**  
 * @ORM\Column(name="message", type="text", length=255,  nullable=true)
 */
protected $message;

When I remove the length field from the annotation, I would expect the field to then be interpreted as LONGTEXT as specified in the documentation. However, when I run orm:schematool:update, it says that there is nothing to update.






[DBAL-1180] [GH-821] Added method for switching support for foreign keys for SQLite Created: 25/Mar/15  Updated: 25/Mar/15

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 hason:

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

Message:






[DBAL-1178] Mapping import errors on SQL Server 2000 Created: 19/Mar/15  Updated: 23/Mar/15

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

Type: Bug Priority: Critical
Reporter: Ciro Vargas Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal, mapping, sqlserver
Environment:

Microsoft SQL Server 2000 - 8.00.2066 (Intel X86)
May 11 2012 18:41:14
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


Attachments: PNG File System tables.png    

 Description   

The Doctrine documentation says:

SQLServerPlatform for version 2000 and above.

And the mapping query on the platform SQLServerPlatform.php is:

    public function getListTableColumnsSQL($table, $database = null)
    {
        return "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       " . $this->getTableWhereClause($table, 'scm.name', 'obj.name');
    }

But the system tables in the database is (attached)

There are several errors in queries using tables and fields that do not exist in SQL Server (in all map methods). So I can not map the entities



 Comments   
Comment by Marco Pivetta [ 19/Mar/15 ]

Ciro Vargas we don't support SQLServer 2000: the oldest version we support is SQLServer 2005 as per https://github.com/doctrine/dbal/blob/3b901cd314d1f79a54c93131d634aad507114b34/lib/Doctrine/DBAL/Platforms/SQLServer2005Platform.php

Comment by Benjamin Eberlei [ 19/Mar/15 ]

This doesnt end the world for you, you must extend from the SQL Server platform and change what you need.

Comment by Ciro Vargas [ 19/Mar/15 ]

Marco Pivetta http://doctrine-dbal.readthedocs.org/en/latest/reference/platforms.html in the docs says:

"SQLServerPlatform for version 2000 and above."

The right way is create mapping querys in the version platform and override on the upper, right? The doctrine team are updating the base platform

Comment by Ciro Vargas [ 19/Mar/15 ]

Benjamin Eberlei yes, i can do. Or mapping hardcoded

Comment by Ciro Vargas [ 19/Mar/15 ]

See on https://github.com/doctrine/dbal/blob/2a9e9943f33610bfde4637abeafe00edd201803c/lib/Doctrine/DBAL/Platforms/SQLServerPlatform.php

that's works no fine, but works

the right is update for each database version, no update only for the last.

Down versions of 2012 can be bugged too

Comment by Ciro Vargas [ 23/Mar/15 ]

Solved https://gist.github.com/cirovargas/e5c0bfbc404bb414bb2b





[DBAL-1179] [GH-820] SchemaManager quoting fixes Created: 19/Mar/15  Updated: 19/Mar/15

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: 1
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of zeroedin-bill:

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

Message:

The SchemaManager, when used with SQL Server, fails to quote incoming table names and column names that should be quoted. That means that when a table that exists in the database called 'quote-address' needs to be dropped, the drop table statement will fail because the identifer is not marked as quoted when the schema diff is created.

This patch adds a method isValidIdentifier to the AbstractPlatform API - the purpose of this is to check if an identifier is valid to be used in SQL on the platform.

This is used by a new protected method in AbstractSchemaManager, quoteIncomingIdentifier. This method checks if the passed string literal is a valid identifier using isValidIdentifier. If the identifier is not valid, it quotes it for the platform and returns it. If it is valid, or is already quoted, it returns the identifier unchanged.






[DBAL-1177] Cannot drop index 'site': needed in a foreign key constraint. Doctrine drops index before create Created: 18/Mar/15  Updated: 18/Mar/15

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

Type: Bug Priority: Major
Reporter: seyfer Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal, migrations, schemadiff


 Description   

The problem is occurred when I generate migration for an entity on an existed database.
I'm add mapping from one table to table site. Generated migrations was like this

$this->addSql('ALTER TABLE request ADD CONSTRAINT FK_3B978F9F694309E4 FOREIGN KEY (site) REFERENCES sites (id)');
$this->addSql('DROP INDEX site ON request');
$this->addSql('CREATE INDEX IDX_3B978F9F694309E4 ON request (site)');

I'm already have index site on site column before generation migration, and migration tries, as you can see, first drop old index and than add new. But

[Doctrine\DBAL\Exception\DriverException]
An exception occurred while executing 'DROP INDEX site ON request':
SQLSTATE[HY000]: General error: 1553 Cannot drop index 'site': needed in a foreign key constraint

[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[HY000]: General error: 1553 Cannot drop index 'site': needed in a foreign key constraint

If i change migration like that - all works.

$this->addSql('ALTER TABLE request ADD CONSTRAINT FK_3B978F9F694309E4 FOREIGN KEY (site) REFERENCES sites (id)');
$this->addSql('CREATE INDEX IDX_3B978F9F694309E4 ON request (site)');
$this->addSql('DROP INDEX site ON request');

I think migration should drop old or duplicate indexes AFTER adding new, not before.



 Comments   
Comment by mikeSimonson [ 18/Mar/15 ]

Wouldn't it make more sense not to touch the index at all ?

Comment by seyfer [ 18/Mar/15 ]

It generates automatically as is. I don't know why it tries add another index and drop mine. As I said - I add mapping on already existed table with index added by hand.
When I added a mapping - I need generate migration to sync my mapping and database. And it generates this migration.





[DBAL-1176] [GH-819] Added support for inline comments for SQLite Created: 18/Mar/15  Updated: 18/Mar/15

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 hason:

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

Message:






[DBAL-969] [GH-658] DBAL-968 Created: 11/Aug/14  Updated: 17/Mar/15

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 zeroedin-bill:

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

Message:

The recent change to SQLServerPlatform.php (https://github.com/doctrine/dbal/commit/17dad30dc9acd91a5cda0da2c5ce2c40d522f766) broke the ORM Paginator's queries on SQL server.

I investigated, and found that some of the test cases for the SQL Server platform weren't actually correct SQL. Also, there were no test cases that covered what the paginator is doing, so I've written test cases for those. I will open a pull request for this issue.

The modifyLimitQuery method in SQLServerPlatform.php should be fixed to pass the fixed old tests and the new tests.

My concern is that that method is becoming too complex, but that's an issue for another day.



 Comments   
Comment by Doctrine Bot [ 17/Mar/15 ]

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





[DBAL-1175] [GH-818] Rebuild SQLServerPlatform::doModifyLimitQuery again to use a CTE Created: 17/Mar/15  Updated: 17/Mar/15

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 zeroedin-bill:

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

Message:

Also only uses 1 regex.

This method is vastly simpler and more reliable than the old method of parsing a whole lot of stuff.

A caveat: if a query is passed that contains a subquery with an ORDER BY clause, the inner ORDER BY clause will be dropped. SQL server doesn't support using ORDER BY inside subqueries. In the future I think we should consider throwing an exception when these are found.

For now, hold off on merging this. There is a PR open for the ORM Paginator that prevents it from sending queries with multiple ORDER BY clauses. The Paginator is the only place in the ORM where this occurs. Until that PR is merged, this one should probably stay open.

I'll be adding a set of functional tests for the Paginator later this week.






[DBAL-1146] [GH-798] Add application_name to PostgreSQL driver Created: 04/Feb/15  Updated: 16/Mar/15

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 davividal:

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

Message:

PostgreSQL allows the user to set the application_name is connecting
to database. It is useful for monitoring purposes.
Currently one could just manually add 'application_name=foo' to DSN,
but having a parameter eases setting it using DoctrineBundle.



 Comments   
Comment by Davi Koscianski Vidal [ 16/Mar/15 ]

Hi, any thoughts on this?





[DBAL-1173] MySQL schema-tool:update fails if there are two tables with the same name (lower and uppercase) Created: 16/Mar/15  Updated: 16/Mar/15

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

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

ubuntu 14.04
php 5.5.12
mysql 5.6.19



 Description   

Hi,

when performing:
vendor/bin/doctrine orm:schema-tool:update --force --dump-sql

I get:

[RuntimeException]
Error Output:
[Doctrine\DBAL\Schema\SchemaException]
The table with name 'hospital.user_roles' already exists.

I have 2 tables with names USER_ROLES and user_roles in database, which are unrelated to the updated schema. They just exist in the db.

After deletion of one of them schema update works well.

Regards,
Andrzej






[DBAL-1131] [GH-785] Handle default values for datetime/datetimetz columns in PostgreSQL Created: 25/Jan/15  Updated: 13/Mar/15

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 sarcher:

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

Message:

(This was originally in #670 but am re-submitting due to a busted merge)

When dealing with legacy schema it would be nice to be able to map default values and not have the schema spit out ALTER statements each time. This works correctly today for basic integer and string columns, but does not handle columns with special types such as boolean or datetime.

For example, the following statement:

`ALTER TABLE test_table ALTER test_column SET DEFAULT CURRENT_TIMESTAMP;`

Results in a column definition like:

`test_column | timestamp with time zone | not null default now()`

However, repeating the same schema generation will result in the same ALTER statement each time, because it will always detect that the default value has changed. The same is true for boolean columns.

This simple change prevents this situation from happening and correctly detects that the column default has not changed. It is specific to PostgreSQL.



 Comments   
Comment by Doctrine Bot [ 13/Mar/15 ]

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





[DBAL-1170] self-referential column fails with sqlite and InheritanceType("JOINED") Created: 11/Mar/15  Updated: 11/Mar/15

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

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


 Description   

Per summary, if I have a base abstract class with a property referencing its own ID, and InheritanceType("JOINED"), the generated sqlite DDL will cause runtime constraint errors.

trimmed down example of what I'm doing, seeing

/**
 * @Entity
 * @InheritanceType("JOINED")
 * @DiscriminatorColumn(name="discr", type="string")
 */
    
abstract class GraphElement  {
    /**
     * @Id 
     * @Column(type="integer")
     * @GeneratedValue(strategy="AUTO")
     * @var int
     * */
    public $id;
            
    /**
     * @OneToOne(targetEntity="GraphElement")
     * @JoinColumn(name="owningProcessDefinition_id", referencedColumnName="id")
     * @var ProcessDefinition
     */
    protected $processDefinition = null;
......
}
/** @Entity **/
class ProcessDefinition extends GraphElement {}

Generates:
CREATE TABLE GraphElement (id INTEGER NOT NULL, name VARCHAR(255) NOT NULL, owningProcessDefinition_id INTEGER DEFAULT NULL, discr VARCHAR(255) NOT NULL, PRIMARY KEY(id), CONSTRAINT FK_4779DC6C1693DAAD FOREIGN KEY (owningProcessDefinition_id) REFERENCES GraphElement (id) NOT DEFERRABLE INITIALLY IMMEDIATE)

and then:

2015-03-11T15:46:15+00:00 [sql] "START TRANSACTION"
2015-03-11T15:46:15+00:00 [sql] INSERT INTO GraphElement (name, owningProcessDefinition_id, discr) VALUES (?, ?, ?)
2015-03-11T15:46:15+00:00 [sql] INSERT INTO ProcessDefinition (id, startState_id) VALUES (?, ?)
.....
2015-03-11T15:46:15+00:00 [sql] INSERT INTO GraphElement (name, owningProcessDefinition_id, discr) VALUES (?, ?, ?)
....
2015-03-11T15:46:15+00:00 [sql] UPDATE GraphElement SET owningProcessDefinition_id = ? WHERE id = ?
2015-03-11T15:46:15+00:00 [sql] "ROLLBACK"

will ultimately give:

Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'UPDATE GraphElement SET owningProcessDefinition_id = ? WHERE id = ?' with params [1, 1]:
SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: GraphElement.owningProcessDefinition_id' in /home/jwarnica/workspace/azBPM/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractSQLiteDriver.php:48

because of that "NOT DEFERRABLE INITIALLY IMMEDIATE"

Note: Everything just works with @InheritanceType("SINGLE_TABLE")

It does look like SqlitePlatform.php has the ability to not create this constraint, but I wasn't able to find any documentation on how (or if possible) to apply (loosen) FK constraints.

In any case, if SINGLE_TABLE just works, JOINED should trigger the right (no) constraints on self-referential OneToOne. Or at least have what one needs to do documented.






[DBAL-602] Deprecate Migrations in favor of stable tools Created: 12/Sep/13  Updated: 10/Mar/15

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

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

Sub-Tasks:
Key
Summary
Type
Status
Assignee
DBAL-603 DbDeploy Support Sub-task Open Benjamin Eberlei  
DBAL-604 Liquibase Support Sub-task Open Benjamin Eberlei  
DBAL-605 Phinx Support Sub-task Open Benjamin Eberlei  

 Description   

The Migrations project is very big and currently unmaintained, even if there is definately need for a solution of the migration problem.

The idea would be introduce a subcomponent in DBAL that delegates this to proven tools (DbDeploy and Liquibase, and Phinx for PHP based).

The functionality Doctrine should provide is integration with the \Doctrine\DBAL\Schema API. Three operations come to mind:

  • status - What version are we? Do we need to execute more versions?
  • migrate - Execute the migration tool
  • create-migration - Create a new migration file of the underlying platform.

The last operation needs to check if no versions need to be applied at the moment.

interface MigrationTool
{
   /** @return MigrationCurrentStatus */
   public function getStatus();

   /** @return MigrationPerformedStatus */
   public function migrate($toVersion = null);

   /** @return MigrationRolledBackStatus */
   public function rollback($toVersion = null);

   /** @return MigrationCreatedStatus */
   public function create(Schema $toSchema);
}

Every tool implements this interface and then we need 3 new commands for "status", "migrate" and "rollback". The "create" command can only be implemented in the context of the ORM.



 Comments   
Comment by Christophe Coevoet [ 12/Sep/13 ]

What is the idea here ?

I don't agree about removing the Migrations project in favor of using only the schema diff tool (which we already have as a command in the ORM btw). Migrating is not only about updating the schema. It also requires migrating data. Otherwise, it is not safe to use in production. This is why the

{doctrine:schema:update}

command displays a warning before running.
A good example is adding a new non-nullable unique field. Applying the schema update works on an empty DB but fails when the table already contains data.

Comment by Benjamin Eberlei [ 12/Sep/13 ]

Christophe Coevoet The idea is not to keep only ORM Schema-Tool, which is really only a Dev-Tool. We would rather add support for DbDeploy, Liquibase and Phinx into DBAL via some integration sub-component and using DBAL\Schema to create migration files for their formats.

Comment by Miha Vrhovnik [ 04/Jan/14 ]

There is also http://dbv.vizuina.com/

Comment by Jonathan Cardoso Machado [ 31/Jan/14 ]

The command line support is going to stay right? The idea here is to use third party deploy framework, but with specific bindings to use with Doctrine, Am I right?

Comment by Jonathan Cardoso Machado [ 10/Mar/15 ]

@beberlei Can we get an update here? Is this still planned for 2.6?





[DBAL-1167] [GH-814] allow serverVersion to be unspecified Created: 09/Mar/15  Updated: 09/Mar/15

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: 1
Labels: None


 Description   

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

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

Message:

Hi,

this PR allows `serverVersion`to be nullable.

We use doctrine/dbal in Integration-Tests and to prevent the unit-tests from connecting to a database, we specify `serverVersion` with something made-up (like `5.6`).

However, i'm not comfortable set `serverVersion` to a made-up server-version to prevent connections from being made, when there even isn't a server to connect to. With this PR merged, we could specify `serverVersion` to be `null` instead of something like `5.6` and still prevent connections from being made. `null` is a valid return value for `getDatabasePlatformVersion()`.






[DBAL-1166] [GH-813] Treat SQLite Connection URLs Differently in DriverManager Created: 07/Mar/15  Updated: 07/Mar/15

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 chrisguitarguy:

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

Message:

In addition to setting `dbname`, which is ignored by the SQLite Driver, set the
`path` and `memory` params based on the database URL.

See DBAL-1164






[DBAL-1164] Creating SQLite Connections via a URL Does Not Work Created: 07/Mar/15  Updated: 07/Mar/15

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

Type: Bug Priority: Minor
Reporter: Christopher Davis Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

When creating a SQL light connection put the URL path into the `dbname` param. But the SQLite driver doesn't using the `dbname` param: it uses the `path` parameter.

So doing this:

$conn = DriverManager::getConnection([
'url' => 'sqlite:////some/path/here',
]);

Generates a PDO DSN like this: `sqlite:`. The path is completely ignored.

See the driver code here: https://github.com/doctrine/dbal/blob/6b6143ba16e5f17242835910173c033a8f73f845/lib/Doctrine/DBAL/Driver/PDOSqlite/Driver.php#L81-L88

`DriverManager` either needs some logic to use the path when it sees a sqlite URL or the Driver itself should use `dbname` (eg. check path, check dbname, check memory).



 Comments   
Comment by Christopher Davis [ 07/Mar/15 ]

Same is true for memory databases. `dbname` is set, but the driver never checks it.





[DBAL-1144] [GH-797] unsigned boolean Created: 31/Jan/15  Updated: 06/Mar/15

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 liutec:

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

Message:



 Comments   
Comment by Doctrine Bot [ 06/Mar/15 ]

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





[DBAL-1163] Pagination issue with order by statement Created: 05/Mar/15  Updated: 05/Mar/15

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

Type: Bug Priority: Major
Reporter: Vahe Shadunts Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: paginator, postgresql
Environment:

linux, PostgreSQL



 Description   

For example I have 2 entities (family, person) with ManyToOne(familyId) association in Person entity, and OneToMany(mappedBy="familyId") in Family Entity (Full class definitions are in the end of description)

And this is my DQL part

$query = $em -> createQuery('
select e0, e1 from TestPagingBundle:Family e0 join e0.people e1
order by e1.firstName asc
');
$query -> setMaxResults(2);

This is working perfectly when the ordered firstNames are from different families.

But the paginator class generates 3 queries, 1st for fetching count, second for id's 3rd is a general query for data.

This is the 2nd Query:

SELECT DISTINCT id0, first_name3
FROM (
SELECT f0_.id AS id0, f0_.name AS name1, p1_.id AS id2, p1_.first_name AS first_name3, p1_.last_name AS last_name4
FROM family f0_
INNER JOIN person p1_ ON f0_.id = p1_.family_id
ORDER BY p1_.first_name ASC
)dctrn_result
ORDER BY first_name3 ASC
LIMIT 2

So in the select statement in this query there are distinctly selected 2 fields, id and first_name.

And if we'll have 2 people in one family which names are Aaron and Abraham, this query result will be

id | name
-------------------
1 | Aaron
1 | Abraham
-------------------

So we have fetched only one id from families instead of 2, which we wanted to select.

Then the where statement of 3rd query will be where id in ( ? ) with params [1,1], and we are getting 1 Family instead of 2.

----------------
class Family

{ /** * @var integer * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="IDENTITY") */ private $id; /** * @var string * * @ORM\Column(name="name", type="string", length=256, nullable=true) */ private $name; /** * @OneToMany(targetEntity="Person", mappedBy="familyId") **/ private $people; }

class Person

{ /** * @var integer * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="IDENTITY") */ private $id; /** * @ManyToOne(targetEntity="Family") * @JoinColumn(name="family_id", referencedColumnName="id") **/ private $familyId; /** * @var string * * @ORM\Column(name="first_name", type="string", length=256, nullable=false) */ private $firstName; /** * @var string * * @ORM\Column(name="last_name", type="string", length=256, nullable=true) */ private $lastName; }




[DBAL-1160] Oracle - UuidGenerator bug Created: 04/Mar/15  Updated: 04/Mar/15

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

Type: Bug Priority: Major
Reporter: Loïc Lavoie Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal


 Description   

The current implementation of the generator strategy UUID for Oracle does not work with any of the version I have availalble (9-10-12)

When using it, the database raise an error the following error:

ORA-00923: FROM keyword not found where expected

The reason is that the statement return into the file DBAL\Plateform\OraclePlateform is missing the "FROM DUAL" (mandatory in oracle, you can't execute a query without a from).

Once this is fix, another error is raised:

ORA-01465: invalid hex number

The reason is that the getGuidExpression() return a binary result. Unfortunately, into the ORM part, it is not converted back in hex (using raw2hex).

My recommandation would be to simply return the following code in the OraclePlateform file:

    /**
     * {@inheritDoc}
     */
    public function getGuidExpression()
    {
        return 'RAWTOHEX(SYS_GUID()) FROM DUAL';
    }

This solution actually work on every plateform I've tested so far.






[DBAL-1158] Using alias with order by and then applying a limit causes an SQL invalid column name error Created: 02/Mar/15  Updated: 02/Mar/15

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

Type: Bug Priority: Major
Reporter: Karl Dawson Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal, sqlsrv
Environment:

Windows Server 2012 with SQL Server 2012. Using PHP sqlsrv extension and SQL Native Client 11.



 Description   

I was originally having a problem where aliases were not working with order by/group by. I switched to 2.5.1 and this fixed the issue; however this led to another one. When applying a limit to a query using an alias in conjunction with order by, it generates the following error:

SQLSTATE [42S22, 207]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'sclr1'

See the following example:

$qb = $this->createQueryBuilder('u');

$select = array(
'u.title',
'SUM(u.seconds) AS total_seconds',
);

$qb->select($select)
->groupBy('u.title')
->orderBy('total_seconds', 'DESC')
->setMaxResults(5);

return $qb->getQuery()->getResult();

This will return the above error, but removing the setMaxResults(5) from the query will allow it to work.






[DBAL-1157] [GH-808] Compatibility layer for PHP installations without PDO support - ticket D... Created: 28/Feb/15  Updated: 28/Feb/15

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 AZielinski:

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

Message:

Even though DBAL currently offers non-PDO drivers, it depends on a number of PDO constants which renders it unusable if PHP was explicitly compiled without PDO. This PR is an attempt to shim PDO constants as stated in ticket DBAL-1156 (http://www.doctrine-project.org/jira/browse/DBAL-1156)



 Comments   
Comment by Doctrine Bot [ 28/Feb/15 ]

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





[DBAL-1156] Doctrine assumes that PDO is available Created: 23/Feb/15  Updated: 24/Feb/15

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

Type: Bug Priority: Major
Reporter: Adam Zielinski Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: compatibility, dbal, mysqli, pdo


 Description   

`use PDO` and references to PDO class are seen in following files:
Connection.php
Statement.php
Cache/ArrayStatement.php
Cache/ResultCacheStatement.php
Driver/PDOConnection.php
Driver/Mysqli/MysqliStatement.php
Driver/OCI8/OCI8Statement.php
Driver/SQLSrv/SQLSrvStatement.php
Driver/Portability/Statement.php

It's all about using constants like PDO::FETCH_COLUMN. No actual methods are invoked, no objects are instantiated. This could be easily abstracted out to a class included in doctrine-dbal.

I stumbled upon this because I tried to use `mysqli` driver specifically because my installation of PHP is compiled with --disable-pdo.

As a quick & dirty workaround I included a file PDO.php with a shim, but it would be nice if Doctrine did not assume PDO is installed.

I am more than happy to prepare a pull request to fix it if you confirm this is something that needs attention.



 Comments   
Comment by Marco Pivetta [ 23/Feb/15 ]

Seems rather like a missing dependency in composer.json to me: we rely on PDO's APIs, and we're not really interested in polyfilling it when it's not available, as it's actually a lot of code to write for a little achievement :-\

Comment by Adam Zielinski [ 23/Feb/15 ]

Why provide non-PDO drivers then? In doctrine-dbal not a single method or object from PDO is used (aside of PDOConnection.php), it's all about accessing constants like PDO::PARAM_STR. This particular thing could be polyfilled very easily.

Comment by Marco Pivetta [ 23/Feb/15 ]

Those drivers work as long as PDO is also installed.

Comment by Adam Zielinski [ 23/Feb/15 ]

Sure they do, my point is that with minimal effort (that I offer to provide) those drivers could work without PDO as well. In fact that would make more sense - I can imagine that one of typical use cases for e.g. Mysqli driver is a situation where PDO cannot be used for some reason.

Correct me if I'm wrong, but I believe there is no real reason for doctrine-dbal to depend on PDO. Aside of accessing constants, PDO is only used by PDOConnection (which is only used by PDO-based drivers). PDO constants can be shimmed extremely easily.

Comment by Marco Pivetta [ 24/Feb/15 ]

Adam Zielinski if that's the minimal requirement, then a shim is fine





[DBAL-1155] [GH-807] Add support for named primary keys on SQL Server Created: 23/Feb/15  Updated: 23/Feb/15

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 zeroedin-bill:

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

Message:

This PR adds support for named primary keys on SQL Server, and fixes 2 SQL generation tests that should generate named primary keys to do so.






[DBAL-1077] Query limit for sql server Created: 16/Dec/14  Updated: 21/Feb/15

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

Type: Bug Priority: Major
Reporter: yannick LE LAN Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal, regex, sqlserver
Environment:

sql server



 Description   

On branch master: tests/Doctrine/Tests/DBAL/platforms/AbstractSQLServerPlatformTestCase.php
On branch 2.4: tests/Doctrine/Tests/DBAL/platforms/SqlServerPlatformTest.php

public function testModifyLimitQueryFolcoerr()
{

    $sql = $this->_platform->modifyLimitQuery('SELECT son.label AS Name FROM SqlObjectName son WHERE ( SELECT COUNT(eso.identifier) FROM ExtractedSqlObject eso INNER JOIN ProductionDbName pdn ON eso.ref_ProductionDbName_ID = pdn.identifier AND (pdn.label IN (?, ?)) WHERE eso.ref_SqlObjectName_ID = son.identifier) > 0 ORDER BY son.identifier DESC', 1, 0);

    $this->assertEquals('SELECT * FROM (SELECT son.label AS Name, ROW_NUMBER() OVER (ORDER BY son.identifier DESC) AS doctrine_rownum FROM SqlObjectName son WHERE ( SELECT COUNT(eso.identifier) FROM ExtractedSqlObject eso INNER JOIN ProductionDbName pdn ON eso.ref_ProductionDbName_ID = pdn.identifier AND (pdn.label IN (?, ?)) WHERE eso.ref_SqlObjectName_ID = son.identifier) > 0) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 1 ORDER BY doctrine_rownum', $sql);

}

Correction proposed:
on both branches: lib/Doctrine/DBAL/Platforms/SQLServerPlatform.php

protected function doModifyLimitQuery(...)
{
    ...

    #ACTUAL: 
    $selectFromPattern = '/^(\s*SELECT\s+(?:(.*)(?![^(]*\))))\sFROM\s/i';

#CORRECT:

    $selectFromPattern = '/^(\s*SELECT\s+(?:(.*)(?![^(]*\))))\sFROM\s/iU';

explanation: "/U" pattern modifier => http://php.net/manual/en/reference.pcre.pattern.modifiers.php
Ungreedy behavior not susceptible to fall on ?! negative lookahead on parenthesis hunger

  1. has impacts on ORM with setMaxResults (which was failing in my case and made me investigate)


 Comments   
Comment by Marco Pivetta [ 16/Dec/14 ]

Can you send a PR for this change instead?

Comment by yannick LE LAN [ 16/Dec/14 ]

I will do so by the end of the week,

Comment by yannick LE LAN [ 21/Feb/15 ]

Pull requests made:

For dbal version 2.4:
https://github.com/doctrine/dbal/pull/803

For dbal master branch:
https://github.com/doctrine/dbal/pull/804

(as branch 2.4 seems to lack an 'order by' the patch had to be slightly different)





[DBAL-1152] [GH-804] bugfix(Jira1077): Correction for parenthesis misbehavior on querylimit for sqlserver Created: 21/Feb/15  Updated: 21/Feb/15

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 folcoerr:

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

Message:

bugfix(Jira1077): Correction for parenthesis misbehavior on querylimit for sqlserver

Correction added to production code
Test added for automated proof

Environment:
Windows 7/ Git Bash/ Sql Server
Test command line run : "./vendor/bin/phpunit -c ./sqlsrv.phpunit.xml --verbose ./tests/Doctrine/Tests/DBAL/Platforms/SQLServerPlatformTest.php"

Results:
![jira1077_dbalmaster_results](https://cloud.githubusercontent.com/assets/10148824/6312055/9d79743a-b96d-11e4-8842-5b9c649431a5.PNG)






[DBAL-1151] [GH-803] bugfix(Jira-1077): Correction for parenthesis misbehavior on querylimit for sql server Created: 21/Feb/15  Updated: 21/Feb/15

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 folcoerr:

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

Message:

bugfix(Jira-1077): Correction for parenthesis misbehavior on querylimit for sql server

Correction added to production code
Test added for automated proof

Environment:
Windows 7/ Git Bash/ Sql Server
Test command line run : "./vendor/bin/phpunit -c ./phpunit.xml --verbose ./tests/Doctrine/Tests/DBAL/platforms/SqlServerPlatformTest.php"

Results:
![jira1077_dbal24_results](https://cloud.githubusercontent.com/assets/10148824/6312030/298f9c02-b96d-11e4-80a6-d1f1e5fcc9ef.PNG)






[DBAL-1150] [GH-802] Fix issue where schema diffs on sql server try and fail to drop nonexistent indexes Created: 19/Feb/15  Updated: 19/Feb/15

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 zeroedin-bill:

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

Message:

When running a schema diff on SQL Server, the diff generated includes commands to drop indexes that don't exist. This doesn't fix that problem.

This patch works around the problem by changing sql generated like this:
```sql
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'IDX_1234567890')
ALTER TABLE sometable DROP CONSTRAINT IDX_1234567890
ELSE
DROP INDEX IDX_1234567890 ON sometable
```

to this:

```sql
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'IDX_1234567890')
ALTER TABLE sometable DROP CONSTRAINT IDX_1234567890
ELSE IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IDX_1234567890')
DROP INDEX IDX_1234567890 ON sometable
```

Checking for the existence of the index to be dropped before trying to drop it.

The root of the problem, however, is that when the "from" schema is hydrated from schema-details via Table::__construct, a unique index is added for @JoinColumns that are flagged as unique. This also happens for joined table inheritance child tables.






[DBAL-1048] Function based index Created: 20/Nov/14  Updated: 13/Feb/15

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

Type: New Feature Priority: Minor
Reporter: Grégoire Paris Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: postgresql


 Description   

I would like to create a function-based index, but it does not seem to be possible ATM. The final goal is to be able to have case insensitive string filters with Postgresql without losing performance.

I tried this mapping :

  indexes:
        my_entity_name_index:
            columns: [ LOWER(name) ]

But here is what doctrine answers :

[Doctrine\DBAL\Schema\SchemaException]

There is no column with name 'LOWER(name)' on table 'my_entity'.



 Comments   
Comment by Marco Pivetta [ 20/Nov/14 ]

I don't think that we can provide platform-specific index column name support here.

Comment by Michael Lambert [ 02/Jan/15 ]

To me this problem is caused by the bigger issue of some DBs defaulting to case-sensitive and some to case-insensitive. From my searching I have been unable to find a solution to make doctrine operate the same across different collations/databases.

Perhaps the issue would be better described as:
Doctrine does not provide consistency between case-sensitive and case insensitive collations/databases

IMHO it would be very beneficial if Doctrine implemented some method for consistency.

I'm not sure if this is the right place to go into more detail, so I'll refrain for now.

Comment by Steve Müller [ 02/Jan/15 ]

Could you please clarify a bit on "From my searching I have been unable to find a solution to make doctrine operate the same across different collations/databases."? What exactly is broken in Doctrine? Can you give examples?

Comment by Michael Lambert [ 02/Jan/15 ]

Nothing is broken, it could just be improved. Having said that, I don't think this would be a minor task.

The issue I am having that lead me to this issue is that like in mysql != like in postgres. Mysql like == postgres ilike.

Additionally, postgres requires a citext for case insensitive text, mysql relies on the db/table collation.

The only way to make a case insensitive unique key in postgres (at least as far as I could find) is to use a function based index.

I hope this helps define the scope of the case sensitivity issues that would be somewhat solved by adding a function based index or a method to enforce case sensitivity /insensitivity.

(sorry for any auto correct failures, I'm on a mobile device on a train)

Comment by Grégoire Paris [ 13/Feb/15 ]

When I create the index manually, without resorting to Doctrine, it does not detect it when updating the schema (might be a bug that was fixed in the latest version IIRC), so I guess this is a workaround in the meantime, but a dangerous one because one can't safely upgrade. Any other ideas ?





[DBAL-1145] Add support for temporary tables Created: 01/Feb/15  Updated: 01/Feb/15

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: Jeroen De Dauw Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Right now there is no real support for creating temporary tables. Having an interface that takes a Table and constructs temporary table creation sql from it would solve this.

Suggested by beberlei:

$table = new Table();
$table->addOption('temporary', true);
$platform->getCreateTableSQL( $table );






[DBAL-1143]  PostgreSQL PostgreSQL94 Created: 31/Jan/15  Updated: 31/Jan/15

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

Type: Task Priority: Minor
Reporter: Konstantin Nizhinskiy Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: postgresql


 Description   

Add new type jsonb
doc:
http://info.enterprisedb.com/rs/enterprisedb/images/EDB_White_Paper_Using_the_NoSQL_Features_in_Postgres.pdf






[DBAL-1139] [GH-793] fix SequenceName for Oracle Created: 30/Jan/15  Updated: 30/Jan/15

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 Gemorroj:

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

Message:

I want to use a new feature, specify the oracle IDENITY generator (http://www.doctrine-project.org/jira/browse/DDC-2875) . But after inserting a new row, doctrine causes the wrong sequence to find out the id of the inserted row. My problem is solved by adding processing the column name.






[DBAL-1135] [GH-789] Fix when finding arrays of binary strings Created: 28/Jan/15  Updated: 29/Jan/15

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

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: array, binary, expansion, lob, parameter, sqlparserutils


 Description   

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

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

Message:

What the problem was
-------------------------
When using the ORM findBy() method to select data in a binary column (compared against an array of binary strings), I would get exceptions. Code example:

```php
/**

  • @param string[] $email_addresses Encrypted email addresses
  • @return \Project\Entity\EmailAddresses[]
    */
    public function retrieveEmailAddressEntities(array $email_addresses) { return $this->repository->findBy(['EmailAddress' => $email_addresses]); }

    ```

When I would only search for one binary string, however, everything would work correctly. I noticed I was getting a PHP Notice for array to string conversion in my error log:

```
PHP Notice: Array to string conversion in/var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php on line 91
```

In my MySQL table, the column is a varbinary(255) and the ORM entity was marked as type "binary".

How I fixed
--------------
I noticed that the `$type` in `SQLParserUtils::expandListParameters` was marking the type as 103. When this method was checking array types, it was not checking for binary strings. By simply adding the extra check for type 103 in this method, everything started working properly. Line comments below.

Extra
------
This worked to fix it for me and I haven't noticed any side effects of this, however I have not extensively tested all features. If there are any problems with this please let me know.

Specific file commit comments:
--------------------------------------
Connection.php

  • Added constant PARAM_LOB_ARRAY for binary

SQLParserUtils.php

  • Added check to see if the array type is binary
  • Changed the way that the foreach checked if the type was not equal to int, string, or binary





[DBAL-1129] [GH-783] Fixes issue (DBAL-1057) use default platform when not connected Created: 23/Jan/15  Updated: 29/Jan/15

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 weaverryan:

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

Message:

Hi guys!

This is a fix for http://www.doctrine-project.org/jira/browse/DBAL-1057. It leaves the ability to set the platform and platform version from sha: 3176f51d1426022d305c6531a9b9bc93a868bddd, but does not try to connect if we're not already connected. This is consistent with the previous behavior (again see the linked sha) - before there was never a connection made to determine the platform.

The alternate solution is to connect, but surround this by a try-catch (`PDOException`, `DriverException`, `Exception`?) and return `null`. in case we have some situation where the database isn't created or the connection information is wrong.

I know this issue is causing a lot of problems around my world (I ran into myself yesterday), so thanks in advance for the attention.

Thanks!



 Comments   
Comment by Doctrine Bot [ 23/Jan/15 ]

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

Comment by Doctrine Bot [ 23/Jan/15 ]

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

Comment by Doctrine Bot [ 29/Jan/15 ]

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





[DBAL-903] php app/console doctrine:migration:diff generates redundant sql queries for postgres Created: 12/May/14  Updated: 26/Jan/15

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

Type: Bug Priority: Minor
Reporter: Hanov Ruslan Assignee: Steve Müller
Resolution: Unresolved Votes: 1
Labels: None


 Description   

php app/console doctrine:migration:diff

generates redundant sql queries for postgres

symfony 2.4.2,
postgres 9.3
doctrine/orm: ~2.2,>=2.2.3
doctrine/doctrine-bundle: 1.2.*
doctrine/migrations: dev-master
doctrine/doctrine-migrations-bundle: dev-master

    public function up(Schema $schema)
    {
      
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != "postgresql", "Migration can only be executed safely on 'postgresql'.");
        
        $this->addSql("DROP SEQUENCE acl_classes_id_seq1 CASCADE");
        $this->addSql("DROP SEQUENCE acl_security_identities_id_seq1 CASCADE");
        $this->addSql("DROP SEQUENCE acl_object_identities_id_seq1 CASCADE");
        $this->addSql("DROP SEQUENCE acl_entries_id_seq1 CASCADE");
    }

    public function down(Schema $schema)
    {
       
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != "postgresql", "Migration can only be executed safely on 'postgresql'.");
        
        $this->addSql("CREATE SEQUENCE acl_classes_id_seq INCREMENT BY 1 MINVALUE 1 START 1");
        $this->addSql("CREATE SEQUENCE acl_security_identities_id_seq INCREMENT BY 1 MINVALUE 1 START 1");
        $this->addSql("CREATE SEQUENCE acl_object_identities_id_seq INCREMENT BY 1 MINVALUE 1 START 1");
        $this->addSql("CREATE SEQUENCE acl_entries_id_seq INCREMENT BY 1 MINVALUE 1 START 1");
        $this->addSql("CREATE SEQUENCE acl_classes_id_seq1 INCREMENT BY 1 MINVALUE 1 START 1");
        $this->addSql("CREATE SEQUENCE acl_security_identities_id_seq1 INCREMENT BY 1 MINVALUE 1 START 1");
        $this->addSql("CREATE SEQUENCE acl_object_identities_id_seq1 INCREMENT BY 1 MINVALUE 1 START 1");
        $this->addSql("CREATE SEQUENCE acl_entries_id_seq1 INCREMENT BY 1 MINVALUE 1 START 1");
    }


 Comments   
Comment by Steve Müller [ 26/Oct/14 ]

Doctrine ORM 2.2.x is EOL and won't receive any updates anymore. Please consider upgrading to at least 2.3 and reopen if the issue is still there. There have been a LOT of fixes to platforms' SQL generation since 2.2.x.
Also if you still encounter the issue, please add your mapping information, otherwise it will be hard to rack the issue down.

Comment by Steve Müller [ 26/Oct/14 ]

Oh sorry read your ORM version constraint wrong. Reopening. Please can you give the exact DBAL version you are using and mapping information? Thanks.

Comment by Timur Ramazanov [ 26/Jan/15 ]

Vote for this issue.

postgres: 9.3,
doctrine/orm: "~2.2,>=2.2.3",
doctrine/doctrine-bundle: "~1.3",
doctrine/migrations": "1.0.x-dev",
doctrine/doctrine-migrations-bundle": "2.1.x-dev"





[DBAL-1130] [GH-784] "Breaking" a test temporarily to show that it's not doing what is expect... Created: 23/Jan/15  Updated: 23/Jan/15

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 weaverryan:

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

Message:

Hi guys!

This is not meant to be merged, at least not yet. The test added in #691 is flawed. It's failing NOT because there is an exception when trying to use a closed connection (in fact, if the connection is closed, it simply re-opens), but instead, the exception is:

>
Access denied for user 'root'@'localhost' (using password: YES)

The tests should show this. The reason is that we're using connection parameters at the top of this class (https://github.com/doctrine/dbal/blob/master/tests/Doctrine/Tests/DBAL/ConnectionTest.php#L19) that, until now, were never used to actually connect to the database. But now, they are being used to connect to the database, but they're incorrect - they should be pulling from `TestUtil`.

So, this test needs to be fixed or removed.

Thanks!






[DBAL-1127] [GH-781] Call detectDatabasePlatform only once Created: 22/Jan/15  Updated: 22/Jan/15

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 rosier:

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

Message:

Database platform detection is triggered twice if `Doctrine/DBAL/Connection::getDatabasePlatform()` is called before `Doctrine/DBAL/Connection::connect()`






[DBAL-1126] Amazon SimpleDB/DynamoDB Support Created: 21/Jan/15  Updated: 21/Jan/15

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: Bo Zhou Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Hello

Would you please to tell me if there is any plan to support ORM for Amazon SimpleDB/DynamoDB ? Thanks !



 Comments   
Comment by Marco Pivetta [ 21/Jan/15 ]

I don't see join support in AWS DynamoDB, therefore I don't see how support from our side can be provided





[DBAL-1124] License notes whis porting of classes Created: 19/Jan/15  Updated: 20/Jan/15

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

Type: Task Priority: Minor
Reporter: Vladimir Khramov Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

I ported SQLParserUtils to zephir language for Phalcon 2 framework and used modified php tests for this class.

Phalcon licended by New BSD. Now I used default phalcon templates for files:
https://github.com/quantum13/cphalcon/blob/bind_array/phalcon/db/utils/sqlparser.zep
https://github.com/quantum13/cphalcon/blob/bind_array/tests/unit/Phalcon/Db/Utils/SQLParserTest.php

Please say, what I should to add to this files. Should I to add MIT license text to list of phalcon licenses?



 Comments   
Comment by Marco Pivetta [ 19/Jan/15 ]

The license clearly states:

Permission is hereby granted, free of charge, to any person obtaining a copy of
this software and associated documentation files (the "Software"), to deal in
the Software without restriction, including without limitation the rights to
use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies
of the Software, and to permit persons to whom the Software is furnished to do
so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

You need to copy the LICENSE from https://github.com/doctrine/doctrine2/blob/2418f8f5e661c653e4b13cd433d569ece7318f62/LICENSE at least into the derived files, and keep a reference to the original author there.

Other than that, MIT, BSD-2-Clause and BSD-3-Clause are compatible.





[DBAL-1057] Connection is not lazy anymore when guessing the platform is necessary Created: 05/Dec/14  Updated: 16/Jan/15

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

Type: Bug Priority: Critical
Reporter: Christophe Coevoet Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 12
Labels: None

Issue Links:
Duplicate
is duplicated by DBAL-1067 mysql: selecting db issue Resolved
Reference
is referenced by DDC-3475 Avoid db connection in constructor Open

 Description   

In DBAL 2.5, many driver can rely on different versions of the platform. Unless the version is explicitly provided, the driver will guess it at instantiation time, killing the lazyness of the connection.
This is a critical issue in any context using DI as it means that injecting the connection into anything else will connect to the server.



 Comments   
Comment by Christophe Coevoet [ 05/Dec/14 ]

Actually, the Connection class itself defers the guessing until the first time the platform is accessed. But many places in DBAL and in the ORM are retrieving the platform and storing it in a property of the class at instantiation time to avoid method calls when they need to access the platform. So this might be much harder to fix

Comment by Steve Müller [ 05/Dec/14 ]

Christophe Coevoet Can we analyze the use cases where retrieving the platform is necessary before actually connecting? I only know the custom type registering so far... Maybe we can defer that somehow?

Comment by Christophe Coevoet [ 05/Dec/14 ]

Steve Müller the issue is that many places in DBAL and the ORM are retrieving the connection before they use it. the Connection class itself in DBAL is already deferring the guessing

Comment by Steve Müller [ 05/Dec/14 ]

Christophe Coevoet I know that. The question is WHY do those defered connections need to access Connection::getDatabasePlatform() without connecting? What are the use cases?

Comment by Christophe Coevoet [ 05/Dec/14 ]

Steve Müller The issue is that all those objects are calling $connection->getDatabasePlatform() in their own constructor to store a reference to it for faster access later (no more method calls). This means that *instantiating* the ORM (or some parts of DBAL) triggers the platform guessing, which connects to the DB. This breaks the lazyness and hurts DI contexts (maybe the ORM will not even be used in this process, but it was instantiated because of being a dependency in a complex object graph).

Comment by Christophe Coevoet [ 05/Dec/14 ]

and the issue is precisely that all these parts of Doctrine are *not* deferring the retrieval of the platform.

Comment by Steffen Brem [ 07/Dec/14 ]

This is causing a lot of issues when using CI servers. Where it is very important that those things are lazy, since you do not have the database configured on most applications that build on a CI server.

Comment by Craig Heydenburg [ 01/Jan/15 ]

This is also an issue for the project I am working as I am trying to use Symfony to to generate forms and so on in order to install the project. sForms and the main project's front controller trigger DI events and trigger this error. The workaround mentioned here (https://github.com/doctrine/DoctrineBundle/issues/351#issuecomment-65771528) fixes the problem but this cannot be a long term solution. I look forward to the fix for this issue.

Comment by Alan Hartless [ 07/Jan/15 ]

I have the same issue as Craig. This poses a major hassle for applications that have an installer UI. We have a Symfony based application that gives the user options to configure the database such as driver, table, credentials, etc. Then the application will make a dynamic connection to check database, install data, etc. Eventually Symfony's cache is cleared to use the new credentials post install.

Because of this, the installer fails out of the box since it can't connect to the server.

We can't use the work around as mentioned above because of giving the option to choose what driver to use in the installer.

Thanks,
Alan

Comment by Craig Heydenburg [ 14/Jan/15 ]

Tried an experiment today. As noted in the workaround you can set the server_version: 5.1 (or whatever your version is).

Well, my version is 5.5 so I originally used that. Today I tried 5 and 52 and 1 and they all worked!

so it seems that the actual version doesn't matter (at least when I need them, which is before I've actually set up the DB credentials) as long is there is some value.

Comment by Steve Müller [ 15/Jan/15 ]

Craig Heydenburg this is expected behaviour. You can theoretically specify any value you want, DBAL will try to find the appropriate platform for you by the specified version via version compare.
See the example for PostgreSQL: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php#L97-L119
As soon as the specified value/version does not match a specific platform version, DBAL will always fallback to the "default" platform (the one that was chosen by default prior 2.5).
Once the serverVersion parameter is set, auto detection of the proper platform is always bypassed.

Comment by Jan Rosier [ 16/Jan/15 ]

Doctrine now also throws a connection exception when it tries to detect the database platform, but can't make a connection to the server. Even if the connection isn't really used.

I think it would be better to do a fallback to the "default" platform in that case and only throw an connection exception if the connection is required for something else than guessing the platform.

For example if you haven't set up the DB credentials yet and only call Doctrine\DBAL\Connection::getDatabasePlatform() you get the "default" platform.





[DBAL-1117] id names not quoted on create in MySQL Created: 14/Jan/15  Updated: 14/Jan/15

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

Type: Bug Priority: Minor
Reporter: Anders Jenbo Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 14.04, MySQL 5.5, MySQLi driver



 Description   

Having the following line in the xml configurations will cause creation to fail with a sql error because group is a key word.
<id name="group" association-key="true"/>






[DBAL-1114] Problem with drop database on PostgreSQL Created: 13/Jan/15  Updated: 13/Jan/15

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

Type: Task Priority: Minor
Reporter: Marcin Zawadzki Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Reference
relates to DBAL-1093 [GH-757] Fix creating and dropping da... Resolved

 Description   

Hello,

After upgrading doctrine/dbal from version v2.4.3 to v2.4.4 I couldn't drop database by command line with zero open connections.

Example:

./app/console doctrine:database:drop --force
Could not drop database for connection named "test"
An exception occurred while executing 'DROP DATABASE "test"':

SQLSTATE[55006]: Object in use: 7 ERROR: cannot drop the currently open database

details:
• PostgreSQL 9.2.4
• PHP 5.5.10

Any suggestions or workarounds for this issue?

Thank you



 Comments   
Comment by Steve Müller [ 13/Jan/15 ]

Issue introduced in DBAL-1093, to be fixed in DoctrineBundle. PR provided: https://github.com/doctrine/DoctrineBundle/pull/368





[DBAL-1107] Doctrine Migrations diff gets different table name for up and down Created: 06/Jan/15  Updated: 12/Jan/15

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

Type: Bug Priority: Minor
Reporter: Krzysztof Hasiński Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

I've changed one field in my model from not null to default null, generated a migration using diff and got:

class Version20150105175136 extends AbstractMigration
{
    public function up(Schema $schema)
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

        $this->addSql('ALTER TABLE badge ALTER company_id DROP NOT NULL');
    }

    public function down(Schema $schema)
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

        $this->addSql('ALTER TABLE Badge ALTER company_id SET NOT NULL');
    }
}

Please note the name Badge and badge in up and down migrations. Is this a bug?

I've got response from Doctrine Migrations project:

"This should be reported in the issue tracker of the DBAL project, because the Migrations project is not responsible for computing the schema changes."

Link to this issue on github: https://github.com/doctrine/migrations/issues/197



 Comments   
Comment by Steve Müller [ 07/Jan/15 ]

Can you please provide the ORM mapping information before and after the migrations:diff command?
Did you maybe remove or add explicit quotes from/to your table name mapping? Really hard to evaluate without further information...

Comment by Krzysztof Hasiński [ 07/Jan/15 ]

Sure thing:

Entity (imports + header):

use Doctrine\ORM\Mapping as ORM;
use JsonSerializable;
use Iphp\FileStoreBundle\Mapping\Annotation as FileStore;
use Symfony\Component\Validator\Constraints as Assert;
/**
 * Badge
 * @FileStore\Uploadable
 * @ORM\Table()
 * @ORM\Entity
 */
class Badge implements JsonSerializable

Field before change:

    /**
     * @ORM\ManyToOne(targetEntity="Company")
     * @ORM\JoinColumn(nullable=false)
     */
    private $company;

Field after change:

    /**
     * @ORM\ManyToOne(targetEntity="Company")
     * @ORM\JoinColumn(nullable=true)
     */
    private $company;

Comment by Steve Müller [ 09/Jan/15 ]

How is your table named in the database? Is it "Badge" or "badge"? I assume that your table was created with the name "Badge" (with a capital letter). If this is the case, did you have your table name explicitly quoted in the mapping some time before like `Badge` or "Badge" (including backticks/quotes)?

Comment by Krzysztof Hasiński [ 09/Jan/15 ]

Created using migrations:diff, relevant line (note the capital letter):

        $this->addSql("CREATE TABLE Badge (id INT NOT NULL, user_id INT DEFAULT NULL, team_id VARCHAR(63) NOT NULL, name VARCHAR(255) NOT NULL, image VARCHAR(512) NOT NULL, condition TEXT NOT NULL, PRIMARY KEY(id))");

In all subsequent migrations any ALTER TABLE (generated) is using this mixed case for up and down.

Status in db now (from psql):

khasinski=> \d badge
                          Table "public.badge"
   Column    |          Type           |            Modifiers            
-------------+-------------------------+---------------------------------
 id          | integer                 | not null
 user_id     | integer                 | 
 company_id  | character varying(63)   | not null
 name        | character varying(255)  | not null
 image       | text                    | not null
 condition   | text                    | 
 description | character varying(1024) | default NULL::character varying
Indexes:
    "badge_pkey" PRIMARY KEY, btree (id)
    "idx_3f316719296cd8ae" btree (company_id)
    "idx_3f316719a76ed395" btree (user_id)
Foreign-key constraints:
    "fk_3f316719296cd8ae" FOREIGN KEY (company_id) REFERENCES company(id)
    "fk_3f316719a76ed395" FOREIGN KEY (user_id) REFERENCES guser(id)
Referenced by:
    TABLE "userbadge" CONSTRAINT "fk_9a64d968f7a2c2fc" FOREIGN KEY (badge_id) REFERENCES badge(id)
Comment by Marco Pivetta [ 09/Jan/15 ]

Can you please tell us the exact versions (see composer.lock) of the DBAL, ORM and migrations installed?

Comment by Krzysztof Hasiński [ 09/Jan/15 ]

Sure, always happy to help

DBAL:

            "name": "doctrine/dbal",
            "version": "v2.5.0",
            "source": {
                "type": "git",
                "url": "https://github.com/doctrine/dbal.git",
                "reference": "71140662c0a954602e81271667b6e03d9f53ea34"
            },
            "dist": {
                "type": "zip",
                "url": "https://api.github.com/repos/doctrine/dbal/zipball/71140662c0a954602e81271667b6e03d9f53ea34",
                "reference": "71140662c0a954602e81271667b6e03d9f53ea34",
                "shasum": ""
            },

ORM:

            "name": "doctrine/orm",
            "version": "v2.4.7",
            "source": {
                "type": "git",
                "url": "https://github.com/doctrine/doctrine2.git",
                "reference": "2bc4ff3cab2ae297bcd05f2e619d42e6a7ca9e68"
            },
            "dist": {
                "type": "zip",
                "url": "https://api.github.com/repos/doctrine/doctrine2/zipball/2bc4ff3cab2ae297bcd05f2e619d42e6a7ca9e68",
                "reference": "2bc4ff3cab2ae297bcd05f2e619d42e6a7ca9e68",
                "shasum": ""
            },

Migrations:

            "name": "doctrine/doctrine-migrations-bundle",
            "version": "dev-master",
            "target-dir": "Doctrine/Bundle/MigrationsBundle",
            "source": {
                "type": "git",
                "url": "https://github.com/doctrine/DoctrineMigrationsBundle.git",
                "reference": "81575a4316951125ce408c70f30547c77d98f78a"
            },
            "dist": {
                "type": "zip",
                "url": "https://api.github.com/repos/doctrine/DoctrineMigrationsBundle/zipball/81575a4316951125ce408c70f30547c77d98f78a",
                "reference": "81575a4316951125ce408c70f30547c77d98f78a",
                "shasum": ""
            },
Comment by Steve Müller [ 09/Jan/15 ]

I think I get the problem here. Btw do the migrations actually fail? IMO this is not really an "issue" as casing of identifiers is ignored by PostgreSQL unless you explicitly quote identifiers. So:

ALTER TABLE badge ALTER company_id DROP NOT NULL;
ALTER TABLE Badge ALTER company_id DROP NOT NULL;
ALTER TABLE BADGE ALTER company_id DROP NOT NULL;

all do the same thing because PostgreSQL internally lowercases identifiers if they are not quoted.
Please see: http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html#.VLAQal0z1C0

Comment by Steve Müller [ 09/Jan/15 ]

BTW if you find it annoying that migrations uses "Badge" instead of "badge" you'll just have to use another naming strategy.
See: http://doctrine-orm.readthedocs.org/en/latest/reference/namingstrategy.html

Comment by Krzysztof Hasiński [ 09/Jan/15 ]

I am well aware, that PostgreSQL use lowercase when it comes to table names without a quote, that's why I'm not considering a major bug, just some inconsistency, that I was curious about. It seems like up and down migrations should read the data from a single source of truth.

I might be mistaken, but it seems like migrations are important enough to care about this kind of minor details.

Comment by Steve Müller [ 12/Jan/15 ]

The DBAL schema manager that introspects your database does not know about your schema mappings you defned in ORM so it won't know that you created the table with an uppercase first letter. If you want consistency, please use another naming strategy in ORM or quote your table identifier explicitly in your mapping.
Doctrine will only quote reserved keyword identifiers and does not quote all identifiers automatically. There have been several discussions about this to why auto-quoting brings more problems than it solves.

Comment by Krzysztof Hasiński [ 12/Jan/15 ]

As I said - I am aware of that and it's probably ok

So my understanding is that it creates "up" migrations from ORM (which knows about the capital letter) and "down" using DBAL schema manager, is that correct?

Comment by Steve Müller [ 12/Jan/15 ]

I think you mean the other way around. See here: https://github.com/doctrine/migrations/blob/master/lib/Doctrine/DBAL/Migrations/Tools/Console/Command/DiffCommand.php#L101-L102
For "up" migrations the ORM schema mapping is compared against the database schema mapping (base). For "down" migrations it's the other way around.





[DBAL-218] Add Object for BulkInsert Abstraction Created: 05/Feb/12  Updated: 08/Jan/15

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

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

Issue Links:
Reference
relates to DBAL-994 [GH-682] [WIP] [DBAL-218] Add bulk in... Open




[DBAL-994] [GH-682] [WIP] [DBAL-218] Add bulk insert query Created: 30/Sep/14  Updated: 08/Jan/15

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
is referenced by DBAL-218 Add Object for BulkInsert Abstraction Open

 Description   

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

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

Message:

This is an approach to make use of database vendors' bulk row insert query syntax.
As the nature of bulk inserts usually is to insert a LOT of rows into a table (primarily from an external source), the implementation tries to focus on good performance and low memory consumption. It is NOT intended for `INSERT INTO ... SELECT ...` statement queries.

TODO:

  • Add an "executor" class that encapsulates the `BulkInsertQuery` object, adds options like bulk size and internally automatically evaluates the underlying platform's limits for a single `INSERT` statement and splits queries accordingly.
  • Evaluate platforms' max insert rows per `INSERT` statement and implement in platforms.
  • Add unit tests for quoted identifiers.
  • Add functional tests.

Future additions:

  • Add support for expressions.
  • Query builder (if there will be more bulk insert queries like `INSERT INTO ... SELECT ...`)?

Open for discussion. Ideas welcome!






[DBAL-167] Schema comparator doesn't work properly with columnDefinition's Created: 17/Sep/11  Updated: 08/Jan/15

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

Issue Links:
Duplicate
is duplicated by DBAL-1096 schema-tool:update does not understan... Resolved

 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-1086] [GH-750] Store the parameters of the chosen connection when using Master/Slave. Created: 19/Dec/14  Updated: 04/Jan/15

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 xrash:

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

Message:

When using the MasterSlaveConnection, the internal parameters array has a different structure than the usual Connection. This can cause the following methods to fail: getHost(), getPort(), getUsername() and getPassword().

I changed the MasterSlaveConnection so that it stores the parameters of the chosen connection in a private property. With this change, the methods that used to fail can now retrieve the configuration for the actual chosen connection, which is, in my opinion, clearly the expected behavior.

I chose to create a new private property in MasterSlaveConnection instead of inherit the $_params from Connection in order to not cause any confusion by having two different array structures in the same variable at different situations.

I also expanded the tests for this case.



 Comments   
Comment by Doctrine Bot [ 04/Jan/15 ]

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





[DBAL-446] Type json_array can't be null Created: 13/Feb/13  Updated: 04/Jan/15

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

Type: Bug Priority: Major
Reporter: Jan Hruban Assignee: Marco Pivetta
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Reference
is referenced by DBAL-966 [GH-655] json_array columns should re... Resolved

 Description   

Column type json_array can be set to nullable, but if there's null in the database, it is returned as an empty array to PHP.

Null should be returned instead, as that's how the other types behave too.



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

This was fixed in 2.3.3

Comment by Joseph Wynn [ 06/Aug/14 ]

I'm seeing this behaviour again in v2.5.0-BETA3 (6d0b048). If I get time this week I can perform a bisect to figure out when it regressed.

Comment by Joseph Wynn [ 06/Aug/14 ]

Actually I don't think this was a regression; it looks like a fix was never made. I've opened a PR: https://github.com/doctrine/dbal/pull/655

Comment by Doctrine Bot [ 06/Aug/14 ]

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

Comment by Doctrine Bot [ 06/Aug/14 ]

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

Comment by Marco Pivetta [ 06/Aug/14 ]

Re-opened, as this behavior seems reproducible also in 2.4.x

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

It was never fixed. That seems to have been a misunderstanding here. As pointed out by Marco Pivetta changing this behaviour is a BC break and can't be fixed before 3.0.

Comment by Nate Bessette [ 04/Jan/15 ]

I spent the time to develop a custom data type to workaround this bug: https://frickenate.com/2014/10/json-data-type-doctrine/ . I can't imagine anybody storing null expecting to receive back an empty array. That would be a fundamental break from the very purpose of the SQL NULL value.





[DBAL-1105] [GH-767] Removed drizzle support. Created: 04/Jan/15  Updated: 04/Jan/15

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 kimhemsoe:

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

Message:






[DBAL-1103] [GH-765] Remove PHP 5.3 support Created: 03/Jan/15  Updated: 03/Jan/15

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 Ocramius:

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

Message:

Just a suggestion for master (2.6). We should move on.



 Comments   
Comment by Doctrine Bot [ 03/Jan/15 ]

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





[DBAL-982] [GH-669] Correct schema generation for altering PostgreSQL sequences Created: 25/Aug/14  Updated: 03/Jan/15

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 sarcher:

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

Message:

I wrote a detailed explanation here so it is hopefully easy to follow:

        1. Background

With PostgreSQL there are three states for a column to receive a sequence generator: no generator, an internal shortcut towards generating an auto incrementing sequence (SERIAL), and a manually-created sequence. In its current state, DBAL accepts only a boolean "autoincrement" to its `getAlterTableSql()` method (via the passed-in `TableDiff`).

This results in the following scenario:

  • ORM maps a column to "AUTO" which is treated as a sequence (autoincrement = false)
  • DBAL `PostgreSqlSchemaManager` inspects existing table and notices a sequence (autoincrement = true)
  • Diff logic in `getAlterTableSql()` will always detect that autoincrement has changed, and that the requested value is false, so it will always issue a `DROP DEFAULT` statement

This is clearly a bug, and can be proved via a unit test; run the `AbstractPostgreSqlPlatformTestCase::testAlterSchemaSequenceToSequence` test that I have committed against the current DBAL code and you will see it fail (based on what is currently passed in via the ORM; see Q&A below for detailed explanation).

        1. Solution

There already existed a few references to a not-yet-implemented `sequence` property of a column definition, which would store the name of the sequence being used on the column. This makes sense and allows us to support all three column states with regards to sequence, while also preserving backwards compatibility. The default is always null, so it will result in no changes to functionality on other platforms.

So, this PR:

  • Adds the `Column::_sequence` property
  • Correctly sets that property during the `PostgreSqlSchemaManager::_getPortableTableColumnDefinition` method (it actually was already there, just not being used)
  • Checks the value during the `Comparator::diffColumn` operation
  • Uses the value to more correctly determine when to create/drop a sequence during a schema alter
  • Adds the appropriate unit tests to verify the six different combinations here
        1. Q&A

Is this just an ORM bug? Could the ORM be changed to just set autoincrement = true for the AUTO and SEQUENCE strategies?

This would solve the immediate problem, yes. However, it would leave no possible distinction between the three ORM mapping strategies of AUTO, SEQUENCE, and IDENTITY. Further, it would cause a break in `getAlterTableSql()` because setting autoincrement to true implies that we are using the database-generated identity sequence which has a specific name, thereby removing the ability for a user to define a sequence manually with a custom name and have it be used here. This strategy opens the door for addressing those cases later, and does not cause a BC break today.

This seems incomplete; for example, this still doesn't handle the case where a sequence is requested to change from AUTO to a specifically-named sequence.

That is intentional. I think these other cases could and probably should be handled, but they would require changes to both the DBAL and ORM. For example, we pass a `Sequence` object to the create/alter/drop sequence functions, but we do not pass one to the alter function. As a result, the actual sequence name is not available here, so we would absolutely need to make a more thought-out ORM change to solve this. I think that should be separate work, or it could just be something we do not support.

Won't this still require an ORM change to set the `sequence` property?

Yes, the following needs to be added to the `SchemaTool::gatherColumn` method:

```php
if ($class->isIdGeneratorSequence() && $class->getIdentifierFieldNames() == array($mapping['fieldName']))

{ $options['sequence'] = $class->sequenceGeneratorDefinition['sequenceName']; }

```

However, even without that change, this solves the problem on the DBAL side and opens the door to fixing the ORM to behave correctly here.

How do things behave today and how do we want them to behave with respect to alters?

This is what should happen in each alter scenario:

Existing State Mapped to AUTO or SEQUENCE Mapped to IDENTITY (Auto Increment) Regular Column
-------------- ----------------------- ----------------------------------- --------------
*No Sequence* Add Sequence; Default to `nextval(seqeuence)` Add Serial (Identity Sequence) No Change
*Default `nextval(seqeuence)`* No Change No Change Drop Default

This is what does happen today:

Existing State Mapped to AUTO or SEQUENCE Mapped to IDENTITY (Auto Increment) Regular Column
-------------- ----------------------- ----------------------------------- --------------
*No Sequence* No Change (Bad) Add Serial (Identity Sequence) No Change
*Default `nextval(seqeuence)`* Drop Default (Bad) No Change Drop Default


 Comments   
Comment by Doctrine Bot [ 19/Oct/14 ]

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

Comment by Doctrine Bot [ 03/Jan/15 ]

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





[DBAL-1084] MySQL DateTime fractional seconds exception Created: 18/Dec/14  Updated: 31/Dec/14

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

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

MySQL 5.6.4+



 Description   

Since MySQL 5.6.4 it is possible to include fractional seconds in datetime/timestamp fields.

Doctrine cannot handle this at the moment, and it is not clear how this can be resolved.

Doctrine\DBAL\Types\ConversionException(#67): Could not convert database value "2014-12-16 17:06:38.385" to Doctrine Type datetime. Expected format: Y-m-d H:i:s
  #0 /var/www/vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/DateTimeType.php(67): Doctrine\DBAL\Types\ConversionException::conversionFailedFormat('2014-12-16 17:0...', 'datetime', 'Y-m-d H:i:s')
  #1 /var/www/vendor/doctrine/orm/lib/Doctrine/ORM/Internal/Hydration/AbstractHydrator.php(330): Doctrine\DBAL\Types\DateTimeType->convertToPHPValue('2014-12-16 17:0...', Object(Doctrine\DBAL\Platforms\MySqlPlatform))
  #2 /var/www/vendor/doctrine/orm/lib/Doctrine/ORM/Internal/Hydration/ObjectHydrator.php(359): Doctrine\ORM\Internal\Hydration\AbstractHydrator->gatherRowData(Array, Array, Array, Array)
  #3 /var/www/vendor/doctrine/orm/lib/Doctrine/ORM/Internal/Hydration/ObjectHydrator.php(179): Doctrine\ORM\Internal\Hydration\ObjectHydrator->hydrateRowData(Array, Array, Array)
  #4 /var/www/vendor/doctrine/orm/lib/Doctrine/ORM/Internal/Hydration/AbstractHydrator.php(140): Doctrine\ORM\Internal\Hydration\ObjectHydrator->hydrateAllData()
  #5 /var/www/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(804): Doctrine\ORM\Internal\Hydration\AbstractHydrator->hydrateAll(Object(Doctrine\DBAL\Driver\PDOStatement), Object(Doctrine\ORM\Query\ResultSetMapping), Array)
  #6 /var/www/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(574): Doctrine\ORM\AbstractQuery->execute(NULL, 1)
  #7 /var/www/index.php(142): Doctrine\ORM\AbstractQuery->getResult()

The solution for MsSQL (see DBAL-860) cannot be used, because the fractional seconds definition can be different across fields:

  `insert` timestamp NULL DEFAULT NULL,
  `update` timestamp(3) NULL DEFAULT NULL,
  `delete` timestamp(5) NULL DEFAULT NULL,

My current approach is to create a custom type, but I would like to create a solution in Doctrine.

Any thoughts on this?



 Comments   
Comment by Marco Pivetta [ 18/Dec/14 ]

We don't currently support this nor can support a change in the DateTime type in 2.x. If you want support for this, then I suggest using a custom type (as you are doing) or proposing something new for 3.x

Comment by Jachim Coudenys [ 19/Dec/14 ]

For anyone that is having the same issue, I've added my version of the custom mapping in a Github Gist.

Comment by Steve Müller [ 24/Dec/14 ]

Jachim Coudenys can you please check if it fixes your issue if you adopted this:

https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/SQLAnywherePlatform.php#L495-L501

to MysqlPlatform? Because it is basically the same issue. I currently don't have a MySQL 5.6 setup so I cannot test it on my machine.

Comment by Jachim Coudenys [ 31/Dec/14 ]

Steve Müller I think I tried that in the beginning, but normal fields threw errors. I will try it on a VM the following days.





[DBAL-999] Get a Sql Server error on Order By - Symfony2 Created: 08/Oct/14  Updated: 31/Dec/14

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

Type: Bug Priority: Major
Reporter: Maël SOURISSEAU Assignee: Marco Pivetta
Resolution: Unresolved Votes: 0
Labels: orderBy, query, sqlserver


 Description   

Using Symfony with Sql Server and from what I've read, it seems that the connection to the database is not stable.

As soon as I use the orderBy method I get an error :

Here's an example :

Unable to find source-code formatter for language: php. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
  $qStores =
        $this->getManager()
             ->createQueryBuilder()
             ->select('rpdv')
             ->from('MainBundle:PointDeVenteReference', 'rpdv')
             ->andWhere( 'rpdv.partenaireClient = :id_partner ' )
                 ->setParameter( 'id_partner', $this->getUser()->getPartenaire()->getIdPartenaire() )
             ->orderBy( 'rpdv.idPointDeVenteReference' , 'DESC' )
             ->setFirstResult( 0 )
             ->setMaxResults( 30 );

And the error :

An exception has been thrown during the rendering of a template ("An exception occurred while executing
'SELECT DISTINCT TOP 30 id_point_de_vente_reference0
FROM ( SELECT p0_.id_point_de_vente_reference AS id_point_de_vente_reference0,
p0_.reference AS reference1,
p0_.date_derniere_modification AS date_derniere_modification2,
p0_.blocage AS blocage3
FROM point_de_vente_reference p0_
WHERE p0_.id_partenaire_client = ?
ORDER BY p0_.id_point_de_vente_reference DESC ) dctrn_result
ORDER BY id_point_de_vente_reference0 DESC'
with params [2829]:SQLSTATE[42000]:
[Microsoft][SQL Server Native Client 11.0][SQL Server]
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions,
unless TOP, OFFSET or FOR XML is also specified.") in MainBundle:Default:store/list.html.twig at line 79.
I tried to change the class SQLServerPlatform with corrections found on the net, without success.

Do you have any idea?

Thx !



 Comments   
Comment by Steve Müller [ 08/Oct/14 ]

Which version of DBAL are you using? A lot of fixes have been applied to SQL Server's LIMIT/OFFSET query rewriting in DBAL during the last months.

Comment by Maël SOURISSEAU [ 08/Oct/14 ]

2.4 for DBAL.

Under my request, I have :

$stores = new Paginator( $qStores, TRUE );

In passing the second parameter to FALSE, I have no error.

Comment by Marco Pivetta [ 19/Oct/14 ]

I'd suggest checking ORM+DBAL latest to see if the issue still exists, as those component have suffered from radical changes in the last few months.

Comment by Maël SOURISSEAU [ 29/Dec/14 ]

I currently still have the anomaly with the following configuration :

"doctrine/doctrine-bundle": "1.3.*@dev"
"doctrine/dbal": "~2.5"
"doctrine/orm": "~2.4"

Comment by Maël SOURISSEAU [ 29/Dec/14 ]

The problem apparently comes from the class SQLServerPlatform (doModifyLimitQuery)

Comment by Marco Pivetta [ 29/Dec/14 ]

Maël SOURISSEAU that kind of information is insufficient: please pick exact versions from your composer.lock instead.

Comment by Maël SOURISSEAU [ 30/Dec/14 ]
Unable to find source-code formatter for language: json. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
...
{
    "name": "doctrine/dbal",
    "version": "v2.5.0",
    "source": {
        "type": "git",
        "url": "https://github.com/doctrine/dbal.git",
        "reference": "71140662c0a954602e81271667b6e03d9f53ea34"
    },
    "dist": {
        "type": "zip",
        "url": "https://api.github.com/repos/doctrine/dbal/zipball/71140662c0a954602e81271667b6e03d9f53ea34",
        "reference": "71140662c0a954602e81271667b6e03d9f53ea34",
        "shasum": ""
    },
    "require": {
        "doctrine/common": ">=2.4,<2.6-dev",
        "php": ">=5.3.2"
    },
    "require-dev": {
        "phpunit/phpunit": "4.*",
        "symfony/console": "2.*"
    },
    "suggest": {
        "symfony/console": "For helpful console commands such as SQL execution and import of files."
    },
    "bin": [
        "bin/doctrine-dbal"
    ],
    "type": "library",
    "extra": {
        "branch-alias": {
            "dev-master": "2.5.x-dev"
        }
    },
    "autoload": {
        "psr-0": {
            "Doctrine\\DBAL\\": "lib/"
        }
    },
    "notification-url": "https://packagist.org/downloads/",
    "license": [
        "MIT"
    ],
    "authors": [
        {
            "name": "Roman Borschel",
            "email": "roman@code-factory.org"
        },
        {
            "name": "Benjamin Eberlei",
            "email": "kontakt@beberlei.de"
        },
        {
            "name": "Guilherme Blanco",
            "email": "guilhermeblanco@gmail.com"
        },
        {
            "name": "Jonathan Wage",
            "email": "jonwage@gmail.com"
        }
    ],
    "description": "Database Abstraction Layer",
    "homepage": "http://www.doctrine-project.org",
    "keywords": [
        "database",
        "dbal",
        "persistence",
        "queryobject"
    ],
    "time": "2014-12-04 21:57:15"
},
{
    "name": "doctrine/doctrine-bundle",
    "version": "dev-master",
    "source": {
        "type": "git",
        "url": "https://github.com/doctrine/DoctrineBundle.git",
        "reference": "3beb3a780485ab01f86941f4892cd23ef8c39c6b"
    },
    "dist": {
        "type": "zip",
        "url": "https://api.github.com/repos/doctrine/DoctrineBundle/zipball/3beb3a780485ab01f86941f4892cd23ef8c39c6b",
        "reference": "3beb3a780485ab01f86941f4892cd23ef8c39c6b",
        "shasum": ""
    },
    "require": {
        "doctrine/dbal": "~2.3",
        "doctrine/doctrine-cache-bundle": "~1.0",
        "jdorn/sql-formatter": "~1.1",
        "php": ">=5.3.2",
        "symfony/doctrine-bridge": "~2.2",
        "symfony/framework-bundle": "~2.2"
    },
    "require-dev": {
        "doctrine/orm": "~2.3",
        "phpunit/php-code-coverage": "~1.2",
        "phpunit/phpunit": "~3.7",
        "phpunit/phpunit-mock-objects": "~1.2",
        "satooshi/php-coveralls": "~0.6.1",
        "symfony/validator": "~2.2",
        "symfony/yaml": "~2.2",
        "twig/twig": "~1"
    },
    "suggest": {
        "doctrine/orm": "The Doctrine ORM integration is optional in the bundle.",
        "symfony/web-profiler-bundle": "to use the data collector"
    },
    "type": "symfony-bundle",
    "extra": {
        "branch-alias": {
            "dev-master": "1.3.x-dev"
        }
    },
    "autoload": {
        "psr-4": {
            "Doctrine\\Bundle\\DoctrineBundle\\": ""
        }
    },
    "notification-url": "https://packagist.org/downloads/",
    "license": [
        "MIT"
    ],
    "authors": [
        {
            "name": "Symfony Community",
            "homepage": "http://symfony.com/contributors"
        },
        {
            "name": "Benjamin Eberlei",
            "email": "kontakt@beberlei.de"
        },
        {
            "name": "Doctrine Project",
            "homepage": "http://www.doctrine-project.org/"
        },
        {
            "name": "Fabien Potencier",
            "email": "fabien@symfony.com"
        }
    ],
    "description": "Symfony DoctrineBundle",
    "homepage": "http://www.doctrine-project.org",
    "keywords": [
        "database",
        "dbal",
        "orm",
        "persistence"
    ],
    "time": "2014-11-28 08:32:03"
},
{
    "name": "doctrine/orm",
    "version": "v2.4.7",
    "source": {
        "type": "git",
        "url": "https://github.com/doctrine/doctrine2.git",
        "reference": "2bc4ff3cab2ae297bcd05f2e619d42e6a7ca9e68"
    },
    "dist": {
        "type": "zip",
        "url": "https://api.github.com/repos/doctrine/doctrine2/zipball/2bc4ff3cab2ae297bcd05f2e619d42e6a7ca9e68",
        "reference": "2bc4ff3cab2ae297bcd05f2e619d42e6a7ca9e68",
        "shasum": ""
    },
    "require": {
        "doctrine/collections": "~1.1",
        "doctrine/dbal": "~2.4",
        "ext-pdo": "*",
        "php": ">=5.3.2",
        "symfony/console": "~2.0"
    },
    "require-dev": {
        "satooshi/php-coveralls": "dev-master",
        "symfony/yaml": "~2.1"
    },
    "suggest": {
        "symfony/yaml": "If you want to use YAML Metadata Mapping Driver"
    },
    "bin": [
        "bin/doctrine",
        "bin/doctrine.php"
    ],
    "type": "library",
    "extra": {
        "branch-alias": {
            "dev-master": "2.4.x-dev"
        }
    },
    "autoload": {
        "psr-0": {
            "Doctrine\\ORM\\": "lib/"
        }
    },
    "notification-url": "https://packagist.org/downloads/",
    "license": [
        "MIT"
    ],
    "authors": [
        {
            "name": "Roman Borschel",
            "email": "roman@code-factory.org"
        },
        {
            "name": "Benjamin Eberlei",
            "email": "kontakt@beberlei.de"
        },
        {
            "name": "Guilherme Blanco",
            "email": "guilhermeblanco@gmail.com"
        },
        {
            "name": "Jonathan Wage",
            "email": "jonwage@gmail.com"
        }
    ],
    "description": "Object-Relational-Mapper for PHP",
    "homepage": "http://www.doctrine-project.org",
    "keywords": [
        "database",
        "orm"
    ],
    "time": "2014-12-16 13:45:01"
},
...
Comment by Steve Müller [ 31/Dec/14 ]

I think this issue is being dealt with in this PR: https://github.com/doctrine/doctrine2/pull/1220
It's basically an issue with the ORM paginator because it uses the ORDER BY clause in the derived table which is invalid in SQL Server.





[DBAL-1081] Paginator - Query Limit for SQL Server - SqlServerPlatform.php Created: 16/Dec/14  Updated: 29/Dec/14

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

Type: Bug Priority: Major
Reporter: Maciej Grajcarek Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal, dql, orderBy, sqlserver
Environment:

Windows



 Description   

Hi!
I have a problem with Query results limit when ordering by SUM of a field.

My query looks like this:

            SELECT ypk.name keyword, SUM(ypk.value) total
            FROM YpBundle:YellowPageKeyword ypk
              JOIN ypk.yellowpage yp
              JOIN yp.listing l
            WHERE l.customer = :customerId
              AND ypk.originDate >= :dateFrom
              AND ypk.originDate <= :dateTo
            GROUP BY ypk.nameCrc, ypk.name
            ORDER BY SUM(ypk.value) DESC

First I was catching following error:
message: "[Syntax Error] line 0, col 395: Error: Expected known function, got 'SUM'"
class: Doctrine\ORM\Query\QueryException

It only accourse if SUM is used in ORDER BY clause.

I have registered new class Sum which extends FunctionNode.

Now, query is build and executed but it has an error:

'SELECT * FROM 
     (SELECT y0_.name AS name_0, SUM(y0_.value) AS sclr_1, ROW_NUMBER() OVER (ORDER BY value) DESC) AS doctrine_rownum FROM yellowpage_keywords y0_ INNER JOIN yellowpages y1_ ON y0_.yellowpage_id = y1_.id INNER JOIN listings l2_ ON y1_.listing_id = l2_.id WHERE l2_.customer_id = ? AND y0_.origin_date >= ? AND y0_.origin_date <= ? GROUP BY y0_.name_crc, y0_.name) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 10 ORDER BY doctrine_rownum' with params ["111", "2013-01-01 00:00:00.000000", "2014-12-31 23:59:59.000000"]

The line :

ROW_NUMBER() OVER (ORDER BY value) DESC)

should look like

ROW_NUMBER() OVER (ORDER BY SUM(y0_.value) DESC)

In doModifyLimitQuery method I have modified:

                $pattern = sprintf('/%s\.%s\s+(?:AS\s+)?([^,\s)]+)/i', $column['table'], $column['column']);

to:

                $pattern = sprintf('/%s\.%s\s+(?:AS\s+)?([^,\s)]+)/i', str_replace('(', '\(', $column['table']), str_replace(')', '\)', $column['column']));

Now preg_match founds matching strings and OVER part of query is build correctly.

I checked other issues about this problem (which are marked as already fixed) and I have no idea why it's not working for me.

Thanks in advance!



 Comments   
Comment by Marco Pivetta [ 17/Dec/14 ]

Seems like a bit of information is missing: Is this issue related to the paginator API or not?

Comment by Maciej Grajcarek [ 17/Dec/14 ]

First of all - thank you for formatting my issue.
Secondly yes - issue is directly connected with paginator API.

Here is a code which should help you to replicate the problem:

            SELECT ypk.name keyword, SUM(ypk.value) total
            FROM YpBundle:YellowPageKeyword ypk
              JOIN ypk.yellowpage yp
              JOIN yp.listing l
            WHERE l.customer = :customerId
              AND ypk.originDate >= :dateFrom
              AND ypk.originDate <= :dateTo
            GROUP BY ypk.nameCrc, ypk.name
            ORDER BY SUM(ypk.value) DESC

        $dql = $this->getEntityManager()->createQuery($query);
        $dql
            ->setParameter('customerId', $customerId)
            ->setParameter('dateFrom', $dateFrom)
            ->setParameter('dateTo', $dateTo);
        $dql->setMaxResults(10);    

        $keywords = $dql->getArrayResult(); 
Comment by Marco Pivetta [ 17/Dec/14 ]

That doesn't involve the paginator, just DQL.

SUM() and computed values are not supported in the ORDER BY clause: you have to select them first. Try:

            SELECT ypk.name keyword, SUM(ypk.value) total
            FROM YpBundle:YellowPageKeyword ypk
              JOIN ypk.yellowpage yp
              JOIN yp.listing l
            WHERE l.customer = :customerId
              AND ypk.originDate >= :dateFrom
              AND ypk.originDate <= :dateTo
            GROUP BY ypk.nameCrc, ypk.name
            ORDER BY total DESC
Comment by Maciej Grajcarek [ 18/Dec/14 ]

If I will do it, it will result in a following query:

SELECT *
FROM (
	SELECT y0_.name AS name_0, SUM(y0_.value) AS sclr_1, ROW_NUMBER() OVER (ORDER BY sclr_1 DESC) AS doctrine_rownum 
	FROM yellowpage_keywords y0_
	INNER JOIN yellowpages y1_ ON y0_.yellowpage_id = y1_.id
	INNER JOIN listings l2_ ON y1_.listing_id = l2_.id
	WHERE l2_.customer_id = 111
		AND y0_.origin_date >= '2014-01-01'
		AND y0_.origin_date <= '2014-12-01'
	GROUP BY y0_.name_crc, y0_.name
) AS doctrine_tbl
WHERE doctrine_rownum BETWEEN 1 AND 10
ORDER BY doctrine_rownum

It's an incorrect query for an SQL Server. Take a look on this part:

ROW_NUMBER() OVER (ORDER BY sclr_1 DESC) AS doctrine_rownum

SQL Server do not support aliasing in OVER clause.

It should look like this, to work:

ROW_NUMBER() OVER (ORDER BY SUM(y0_.value) DESC) AS doctrine_rownum

It looks like this is the copy of this issue: http://www.doctrine-project.org/jira/browse/DBAL-788
I'm on doctrine version 2.5 which has patch from that issue included, but I have no idea why it's not working for me.

Comment by Steve Müller [ 24/Dec/14 ]

Maciej Grajcarek looks like it is an issue with your SUM function implementation. If you change your DQL to use ORDER BY COUNT(ypk.value) instead of ORDER BY SUM(ypk.value), does it work then? If so, there is something wrong with your SUM function and therefore not an issue with DBAL.

Comment by Steve Müller [ 24/Dec/14 ]

well forget about it I think I am wrong here.

Comment by Maciej Grajcarek [ 29/Dec/14 ]

Hi,
I will try to use COUNT as soon as I will be able to do that.

But I already tried other aggregation functions before and the result was exactly the same.





[DBAL-947] [GH-634] Transaction object definition Created: 21/Jul/14  Updated: 28/Dec/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
is referenced by DDC-2279 [GH-571] Update lib/Doctrine/ORM/Enti... Resolved

 Description   

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

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

Message:

To move forward with the Transaction Object, here is an alternative proposal to #571.

This keeps the same basic idea, but now `createTransaction()` returns a `TransactionDefinition` object, which is configurable, and has a `begin()` method that starts the underlying transaction and returns the `Transaction` object:

$tx = $em->createTransaction() // TransactionDefinition
->withIsolationLevel(Connection::TRANSACTION_SERIALIZABLE) // TransactionDefinition
->begin(); // Transaction

I think that this implementation checks all the boxes:

  • Clear separation between the Transaction and its Definition
  • Once the Transaction is created, its Definition is set and cannot be changed
  • No risk to forget to call `begin()`: if you do, you'll deal with a TransactionDefinition and just get a call to undefined method if you try to `commit()` it. Plus, your IDE will be able to warn you while coding.

And needless to say, we're still keeping 100% BC compatibility.

What do you think?



 Comments   
Comment by Doctrine Bot [ 28/Dec/14 ]

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





[DBAL-1089] [GH-753] Add a unit test for broken backslashes on MySQL in LIKE Created: 19/Dec/14  Updated: 19/Dec/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: 1
Labels: None


 Description   

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

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

Message:






[DBAL-1070] AzureSQL specificities are not taken into account Created: 11/Dec/14  Updated: 11/Dec/14

Status: Open
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.5
Fix Version/s: 2.4.3

Type: Bug Priority: Major
Reporter: Nicolas Séverin Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: azure, dbal, sqlserver
Environment:

Azure website using an AzureSQL database (based on SQL Server 2012).



 Description   

In Azure SQL, table ‘sys.extended_properties’ does not exist.
But SQLAzurePlatform inherits from class SQLServerPlatform, which uses it.
The code in question is here /Doctrine/DBAL/Platforms/SQLServerPlatform.php#L845.

Modifications to this portion of code were made to handle comments on columns differently in doctrine/dbal 2.5, but it used to work in 2.4.3.



 Comments   
Comment by Marco Pivetta [ 11/Dec/14 ]

Reverted to priority Major





[DBAL-1069] datetimetz type needs to be a commented one by default Created: 10/Dec/14  Updated: 10/Dec/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: Christophe Coevoet Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

By default, the AbstractPlatform creates datetimetz fields as datetime field for platforms not defining a specific creation for fields with timezones.
This can be an acceptable fallback if you know that your application always send the value as UTC even when the database can accept any timezone as input thanks to the datetimetz type (database not supporting timezone would still have consistent data if you always send the same timezone).
However it creates an issue for the SchemaTool: the field will obviously be reported as datetime when inspecting the DB, not as datetimetz (since it does not have a datetimetz). So for platforms not supporting datetimetz natively, the special comment should be used to avoid useless updates.
The special Doctrine command should however not be used for platforms supporting the type natively (PostgreSQL, SQLServer2008, Oracle and SqlAnywhere12).

I have an idea about a fix, so I may send a PR for this in the following days.






[DBAL-1050] [GH-729] Support for database URLs Created: 26/Nov/14  Updated: 05/Dec/14

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

Type: New Feature Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: drivermanager, dsn, uri


 Description   

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

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

Message:

With a bunch of tests.

Of note:

1. in the case of information present in both URL and "normal" parameters, I'm currently giving priority to the information from the URL; IMO this makes more sense than vice versa (base info would be defined in params, and each developer or environment has a URL that may or may not override that base info, such as charset)
1. the syntax for SQLite is `sqlite:///relativepath.db` or `sqlite://ignoredhost/relativepath.db` for relative, and `sqlite:////tmp/absolutepath.db` or `sqlite://ignoredhost//tmp/absolutepath.db` for absolute paths to the database file (I went back and forth on this, but this way is easier and more consistent; https://github.com/kennethreitz/dj-database-url does the same)
1. extra query params are simply "copied" into `$params` verbatim; makes sense IMO especially considering point number 1



 Comments   
Comment by Doctrine Bot [ 04/Dec/14 ]

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





[DBAL-131] Remove Static Types Created: 19/Jun/11  Updated: 04/Dec/14

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

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


 Description   

the static types should be removed and made to instances of types that can differ between platforms.

Following reasons:

1. Some vendors already convert values to PHP types, for example integers. Not converting again could save about 15% wall-time for integers for example.
2. Some vendors require different type code, this is currently handled by expensive if checks.
3. If you want to change a type for one vendor but also use another vendor in the same request, then you have a problem currently.
4. If types were platform aware then we could make registering custom types simpler.






[DBAL-162] BeforeInsertID & AfterInsertID emulation workflow, wie bei MDB2 Created: 10/Sep/11  Updated: 04/Dec/14

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

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


 Description   

The following workflow is necessary for portable code to work on all platforms:

$nextId = $conn->nextSequenceId();
$query = "INSERT INTO (id) VALUES (?)";
$conn->executeUpdate($query, array($nextId));
$nextId = $conn->lastInsertId();





[DBAL-163] Upsert support in DBAL Created: 10/Sep/11  Updated: 04/Dec/14

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

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


 Description   

Upsert support in DBAL (replace, insert into usw..)



 Comments   
Comment by Miha Vrhovnik [ 11/Jun/12 ]

FYI: http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/





[DBAL-444] OraclePlatform getSequenceNextValSQL not handling case/quoting properly on 11g Created: 10/Feb/13  Updated: 04/Dec/14

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

Type: Bug Priority: Major
Reporter: Max Milaney Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: oci8, oracle, sequence
Environment:

PHP version 5.4.11
Oracle 11g Instant Client version 11.2.0.3.0
Oracle Database 11g Enterprise Edition version 11.2.0.3.0 (x64)
OCI8 DBAL driver


Attachments: File example.php    

 Description   

I have an installer script that uses ORM SchemaTool to create the entities in the DB and then populates with basic data using basic EM->persist calls via ORM.

Sequence objects are created, and when using the 10g Instant Client everything worked correctly, however, upon upgrade to latest version of the Instant Client Oracle seems to be expecting consistent case for these schema objects. It appears as if they are being created with a quoted name as they are created in lowercase. OraclePlatform::getSequenceNextValSQL, however, generates "SELECT entity_id_seq.nextval FROM DUAL" and this fails with error "General error: 2289 OCIStmtExecute: ORA-02289: sequence does not exist".

Executing "SELECT "entity_id_seq".nextval FROM DUAL" directly on the DB returns the correct value.

I believe this may also impact the code in http://www.doctrine-project.org/jira/browse/DBAL-278



 Comments   
Comment by Max Milaney [ 10/Mar/13 ]

Hi there,
Wondering if there is any update on this? I'm having to use a workaround in my applications.
Cheers,
Max

Comment by Benjamin Eberlei [ 14/Mar/13 ]

Can you maybe show an entity definition with its sequence mapping?

Comment by Max Milaney [ 17/Mar/13 ]

Here you are mate. Please see attachment.

Comment by Benjamin Eberlei [ 04/Apr/13 ]

I cant seem to find the problem, in DBAL "lib/Doctrine/DBAL/Platforms/OraclePlatform.php" on line 171, the sequence statement is created with $sequence->getQuotedName($platform), but this only works if quoting is requrested for the sequence.

How do you actually create the sequence? Your entity doesnt have @GeneratedValue.

What does the create schema command say with "--dump-sql" flag? Is the SQL quoted?

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

Max Milaney Can you please test if this still exists in the current master branch? If so, can you please provide the information requested by Benjamin Eberlei ? Otherwise hunting this down is rather hard... Thank you!





[DBAL-221] Schema toSQL() and toDropSQL() both need to delegete creation/drop of schema-level elements Created: 13/Feb/12  Updated: 04/Dec/14

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

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


 Description   

The following schema-level changes have to be done before tables are created:

  • create Schema (PGSQL, MySQL, ...)
  • Create Federations (Azure)
  • Create Types (PGSQL, Oracle, ...)
  • Create Views, Stored Procedures, Triggers

We should add the following APIs:

array $sql AbstractPlatform::getCreateSchemaAdditionalStatements(Schema $schema);
$object = new SQLObject($sqlUpCommand, $sqlDropCommand);
$schema->addSQLObject($object);





[DBAL-182] Insert and Merge Query Objects Created: 18/Nov/11  Updated: 04/Dec/14

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

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

Issue Links:
Reference
relates to DBAL-320 allow SQL QueryBuilder to do INSERTS Resolved
is referenced by DBAL-599 Add support of insert and insert sele... Open
is referenced by DBAL-636 QueryBuilder insert Resolved

 Description   

We are missing Insert and Merge Query Objects.

See Drupal DBTNG:

Merge: http://drupal.org/node/310085
Insert: http://drupal.org/node/310079



 Comments   
Comment by Benjamin Eberlei [ 18/Nov/11 ]

From the first glance: Drupal API has some problems in that it assumes literal values are the default, which makes working with them simple if no expression is necessary. But inconsistent otherwise.

Implementation Details:

  • Difference compared to QueryBuilder is that these objects are no builders, but actually executors.
  • Don't assume Literals
  • Creation is delegated to Platform (Runtime API of a Vendor)
{conn}
$conn->createInsertQuery();
$conn->createMergeQuery();{conn}

Sample API:

$conn->createInsertQuery($tbl)->fields(array('foo', 'bar'))->values(array('?', '?'))->(array(1, 2))->execute();
$conn->createInsertQuery($tbl)->fields(array('foo', 'bar'))->params(array(1, 2))->execute(); // values(?, ?) is implicit.
$conn->createInsertQuery($tbl)->fields(array('foo', 'bar'))->params(array('NOW()', '1'))->execute(); // if no "params" set assume execute once.
$conn->createInsertQuery($tbl)->fields(array('foo', 'bar'))->value('foo', 'NOW())->params(array(1))->params(array(2))->execute();
$conn->createInsertQuery($tbl)->fields(array('foo', 'bar'))->select($queryBuilder)->execute();

Merge: I dont know yet:

problem i see here is that people mistake values() for a "safe" method and pass values in there that should be quoted instead.





[DBAL-217] Introduction Interface for Connection Created: 05/Feb/12  Updated: 04/Dec/14

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

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





[DBAL-225] Add events for onBeginTransaction, onCommit, onCommitFailure Created: 13/Feb/12  Updated: 04/Dec/14

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

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


 Description   

Allow to switch a CommitFailure into a successful event.

This could be done by saving all insert/update/delete statements starting from begin transaction and then replaying them N-times until success is achieved.






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

[DBAL-604] Liquibase Support Created: 12/Sep/13  Updated: 04/Dec/14

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

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





[DBAL-1054] Expose native database handler from Connection Created: 03/Dec/14  Updated: 03/Dec/14

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

Type: Improvement Priority: Trivial
Reporter: Davide Romanini Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: None


 Description   

DBAL drivers totally hide the wrapped native connection handler. Sometimes it is useful having a way to access it, eg: integrating old non-dbal code sharing the same connection, or to access specific funcionalities not available through the driver.
With oci8 for example I'd want to use oci_set_client_info/oci_set_module_name/oci_set_action on postConnect for better dba auditing and analysis. In current implementation the only way is to use reflection and access the protected $dbh instance variable, that's very fragile.
I propose to add a Connection::getNativeConnection() or similar name for this purpose and trivially implement it on the various drivers. BC compatibility could be an issue here, but eventually it could be factored in a more specific interface.



 Comments   
Comment by Steve Müller [ 03/Dec/14 ]

Davide Romanini which Connection are you refering to? The DBAL Connection class provides a getWrappedConnection() method which returns the underlying driver connection object.
If you refer to driver connections in the first place I doubt we will expose the internal connection ressource to avoid possible weird side effects. We had similar discussion several times now. If you need advanced access to driver specific functionality on the connection, please don't use DBAL. DABL is about abstraction accross different drivers and database vendors. It is not intended for advanced driver specific usage.

Comment by Davide Romanini [ 03/Dec/14 ]

getWrapperConnection() returns the DBAL specific connection (OCI8Connection in my use case). That is not a real "native" connection since it is a mere wrapper around the oci8 php module. In particular it doesn't allow to access some specific functions such as:

  • oci_set_action / oci_set_module_name / oci_set_client_info
  • oci_set_prefetch
  • ... other useful functions
    In my case I'm creating a listener that uses the first functions on the list to useful contextual values (eg: symfony controller/action names) to allow fine tuning and monitoring at the dba level. Obviously this is implemented as a cross-cutting concern with a listener without even touching the main application code. So your advise to not using DBAL for such a purpose seems impractical at best.
    Probably a cleaner solution could be to fully encapsulate all the oci8 functions in the OCI8Connection wrapper, creating a real "native" connection. But this is an hard effort, so my approach is more pragmatic.
    I really don't see the whole point of avoiding to "expose the internal connection ressource to avoid possible weird side effects". As a developer I should in any case know what I'm doing and especially when gradually migrating old oci8-based code sharing the same connection is the best approach in my experience. As a side note, improper use of a Connection::getNativeConnection() is just as harmful at least as improper use of EntityManager::getConnection().




[DBAL-1046] Broken link Created: 12/Nov/14  Updated: 02/Dec/14

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

Type: Task Priority: Minor
Reporter: ted bohus Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

On the website, the link to download Doctrine DBAL 2.3.5 doesn't work...

http://www.doctrine-project.org/downloads/DoctrineDBAL-2.3.5-full.tar.gz

Thanks



 Comments   
Comment by Steve Müller [ 02/Dec/14 ]

ted bohus for now please use this link: https://github.com/doctrine/dbal/archive/v2.3.5.zip
We are having a look into the issue.





[DBAL-1030] [GH-713] Prevent result cache key collisions when sharing cache across different connections Created: 01/Nov/14  Updated: 10/Nov/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 vilartoni:

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

Message:

There's an issue when using the default cache key generation for the result cache and using the same cache system across different connections as the generated key will be the same regardless of the connection used.

We can solve this just by using the connection params in the key generation. This issue is quite similar to the one fixed in 1075(https://github.com/doctrine/doctrine2/pull/1075).



 Comments   
Comment by Doctrine Bot [ 10/Nov/14 ]

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





[DBAL-1036] [GH-719] Added TraceLogger to get backtrace for executed queries Created: 05/Nov/14  Updated: 05/Nov/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 pierredup:

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

Message:

Added a TraceLogger that returns a backtrace for the executed query.
The backtrace returns the last 10 frames, and it also formats the frames for easy reading






[DBAL-1035] [GH-718] implement method for retrying database queries/transactions Created: 05/Nov/14  Updated: 05/Nov/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 Tobion:

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

Message:

It is best practice to implement retry logic for transactions that are aborted because of deadlocks or timeouts. This makes such method available inside the DBAL and also adds detection for errors where retrying makes sense in the different database drivers.

Deadlocks and timeouts are caused by lock contention and you often can design your application to reduce the likeliness that such an error occurs. But it's impossible to guarantee that such error conditions will never occur. This is why implementing retrying logic for such errors is actually a must when you have to ensure the application does not fail in edge cases or high load.
Some references where something similar has already been implemented:

I chose the name `retryable` because it is consistent with `transactional`. I think the implementation is quite straight forward and fits very well with the DBAL design.






[DBAL-879] Sequence default value [PGSQL] Created: 29/Apr/14  Updated: 26/Oct/14

Status: Open
Project: Doctrine DBAL
Component/s: Drivers, Schema Managers
Affects Version/s: 2.4.2
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Mohammad Niknam Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: autoincrement, postgresql, sequence
Environment:

ArchLinux
PostgreSQL 9.3.4



 Description   

Hi
I'm using dbal to generate schmea from database via Schema-Manager. The problem is that my primary field 'id' have default value of 'nextval('test1_id_seq'::regclass)' but when I retrive columns using Doctrine\DBAL\Schema\AbstractSchemaManager::listTableDetails() or Doctrine\DBAL\Schema\Table::getColumns() , default value of the column 'id' is null.
In Doctrine\DBAL\Schema\PostgreSqlSchemaManager::_getPortableTableColumnDefinition() method at line 292 default value replaced with null, I don't know why but I guess It's because Driver compatibility.
Also Doctrine\DBAL\Schema\Sequence has no method to retrieve that table.
So I don't have the default value (pointing at sequence) and I can't find out what Sequence is linked to this table either.



 Comments   
Comment by Steve Müller [ 26/Oct/14 ]

Can you please provide a code example of how you create table + sequence and retrieve it?





[DBAL-955] No exception thrown for query error Created: 31/Jul/14  Updated: 26/Oct/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: Flip Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

SQL Server 2012



 Description   

Consider the following code:

IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable

CREATE TABLE #TestTable
( 
id INT  NOT NULL IDENTITY(1,1) PRIMARY KEY, 
aDate DATETIME2(6) NULL
)

INSERT INTO #TestTable
(
aDate
) VALUES
(
'2014-07-30 08:54:23.000000'
)

SELECT *
FROM #TestTable
WHERE aDate > 2000

Error:

Msg 206, Level 16, State 2, Line 21
Operand type clash: datetime2 is incompatible with smallint

Problem: for this error no DBALexception is thrown

By the way, this does work (but does not affect problem description):

SELECT *
FROM #TestTable
WHERE aDate > '2000'


 Comments   
Comment by Marco Pivetta [ 31/Jul/14 ]

Flip your code example includes no DBAL code: could you also add the PHP wrapping around those SQL statements?

Comment by Steve Müller [ 26/Oct/14 ]

Flip ping.





[DBAL-1000] MySQL DB cannot be created from Cli, returns "QLSTATE[42000] [1049] Unknown database" Created: 14/Oct/14  Updated: 26/Oct/14

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

Type: Bug Priority: Critical
Reporter: Marcus Malka Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: Cli, mysql

Attachments: PNG File Screen Shot 2014-10-14 at 10.53.44.png    

 Description   

When trying to create a database via Symfony (tested 2.3, 2.4, 2.5) console using Doctrine, if returns

[Doctrine\DBAL\Exception\ConnectionException]
An exception occured in driver: SQLSTATE[42000] [1049] Unknown database 'livedb_ci_tco_dev'

This effectively prevents re-creating the database.

I traced the error, it seems database existence is either not checked or not functioning, and it tries to connect to the DB, resulting in an exception. In older versions the trace seemed like it checked if DB exists, and didn't try to connect.

I tested some different commit versions to assess where the bug was introduced - here is my brief list of working/non-working versions.

812dd9d (v.2.5.0-BETA3) fail
61eb1ee fail
3176f51 fail
da43b76 works
ce3a56e works
594e326 works
ba9aa63 (v.2.5.0-BETA2) works

So looking from the commit graph, to me it seems like it might have been introduced in commit 3176f51



 Comments   
Comment by Marco Pivetta [ 14/Oct/14 ]

Marcus Malka are you sure that you are running the correct command? If the DB is not there, I would expect an exception.

Comment by Christophe Coevoet [ 14/Oct/14 ]

Marco Pivetta This command is precisely about creating the database when it does not exist yet. I think this failure is related to the guessing of the platform version in DBAL 2.5, which will require connecting to the DB early.

Comment by Marco Pivetta [ 14/Oct/14 ]

Christophe Coevoet the screenshot shows the `drop` command being used

Comment by Marcus Malka [ 14/Oct/14 ]

I tested with multiple commands, mainly drop and create were most common.

The difference is in the error that gets produced with the different commits applied - other one gives the "The database is not there" kind of error you would expect. The other version gives an "can't do the operation because I can't connect to the database" even when you try to create it, and gives a similar "can't drop the database because I can't connect to the database" kind of error, which still says it tries to do something weird. My screenshot could've been taken from the create-command too, the error was identical.

I should have a breaking composer.json file in my version control on another machine. I'll try to add that later to facilitate debugging.

Comment by Steve Müller [ 14/Oct/14 ]

Christophe Coevoet you are right, that DBAL now connects early if you request the database platform but that should not be an issue as you may not specify a non-existing database name when connecting to creating a new database, anyways. And as far as I can see the CreateDatabaseDoctrineCommand even explicitly unsets the database name in the connection params here: https://github.com/doctrine/DoctrineBundle/blob/master/Command/CreateDatabaseDoctrineCommand.php#L71-L80 for a "temporary" connection.
Dropping a database still should require the database to be existent so connecting to it before dropping should be fine or am I missing something here?

Comment by Marcus Malka [ 15/Oct/14 ]

@Steve Muller - is it tries to create the connection and creates an error also when the database is not supposed to be there, for ex. in create action. Effectively breaking createDatabase command.

When I traced the code, it seemed to go in to the platform version checking, and seemed like it just didn't realize early enough that the DB isn't there (that was my guess - I don't know doctrine internals well enough to say if that's how it's planned to work or not).

Comment by Benjamin Eberlei [ 26/Oct/14 ]

This is an issue in DoctrineBundle that only appears in combination with DBAL 2.5.

The problem is Symfony apparently connects to the database in "Container::get" of the connection already. That has to be fixed.





[DBAL-965] [GH-654] doModifyLimitQuery() was missing an outer "ORDER BY doctrine_rownum" Created: 06/Aug/14  Updated: 23/Oct/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 mariusklocke:

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

Message:

Refer to:
http://www.doctrine-project.org/jira/browse/DBAL-940



 Comments   
Comment by Doctrine Bot [ 23/Oct/14 ]

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





[DBAL-1020] Postgres and using Schema tool throws cardinality errors Created: 22/Oct/14  Updated: 23/Oct/14

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

Type: Bug Priority: Critical
Reporter: Dominic Watson Assignee: Marco Pivetta
Resolution: Unresolved Votes: 0
Labels: ddl, postgresql

Attachments: PNG File 6ZeW_jJFUbOCx5uGJ2feANtAsZOr72zhDL4szJ6p5VE.png     File pg_catalog_pg_class.csv    

 Description   

Postgres: 9.3.5.0 (Postgres App for OSX) w/ PostGIS extensions
doctrine/common: 2.4.x-dev ae92d076442e27b6910dd86a1292a8867cf5cfe4
doctrine/dbal: dev-master 1c9c24a7e2295b71249ae2a719ce38861fccd551
creof/doctrine2-spatial: https://github.com/intellix/doctrine2-spatial 4023ca8fbe703043012c31d6df26b9bc7b0a972d

It seems every now and again when I come to use the schema-tool I'm getting exceptions which can only be fixed by dropping the database and recreating from scratch.

The following SQL looks to be generated here: \Doctrine\DBAL\Platforms\AbstractPlatform::getListTableForeignKeysSQL

SELECT quote_ident(r.conname) as conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef
                  FROM pg_catalog.pg_constraint r
                  WHERE r.conrelid =
                  (
                      SELECT c.oid
                      FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
                      WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND c.relname = 'state' AND n.nspname = ANY(string_to_array((select replace(replace(setting,'"$user"',user),' ','') from pg_catalog.pg_settings where name = 'search_path'),',')) AND n.oid = c.relnamespace
                  )
                  AND r.contype = 'f'

The full stack trace is as follows:

 ---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~--
  Dropping database schema...
      ./bin/doctrine-module orm:schema-tool:drop --force --verbose
---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~--
Dropping database schema...


                                                                                                                                                                                                       
  [Doctrine\DBAL\Exception\DriverException]                                                                                                                                                            
  An exception occurred while executing 'SELECT quote_ident(r.conname) as conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef                                                              
                    FROM pg_catalog.pg_constraint r                                                                                                                                                    
                    WHERE r.conrelid =                                                                                                                                                                 
                    (                                                                                                                                                                                  
                        SELECT c.oid                                                                                                                                                                   
                        FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n                                                                                                                          
                        WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND c.relname = 'state' AND n.nspname = ANY(string_to_array((select replace(replace(setting,'"$user"'  
  ,user),' ','') from pg_catalog.pg_settings where name = 'search_path'),',')) AND n.oid = c.relnamespace                                                                                              
                    )                                                                                                                                                                                  
                    AND r.contype = 'f'':                                                                                                                                                              
  SQLSTATE[21000]: Cardinality violation: 7 ERROR:  more than one row returned by a subquery used as an expression                                                                                     
                                                                                                                                                                                                       


Exception trace:
 () at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php:82
 Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:116
 Doctrine\DBAL\DBALException::driverExceptionDuringQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:833
 Doctrine\DBAL\Connection->executeQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:761
 Doctrine\DBAL\Connection->fetchAll() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:319
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableForeignKeys() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:284
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:268
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:1039
 Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:783
 Doctrine\ORM\Tools\SchemaTool->getDropSchemaSQL() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:727
 Doctrine\ORM\Tools\SchemaTool->dropSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/DropCommand.php:100
 Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand->executeSchemaCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:65
 Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:252
 Symfony\Component\Console\Command\Command->run() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:891
 Symfony\Component\Console\Application->doRunCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:195
 Symfony\Component\Console\Application->doRun() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:126
 Symfony\Component\Console\Application->run() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module.php:58
 include() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module:4




                                                                                                                    
  [Doctrine\DBAL\Driver\PDOException]                                                                               
  SQLSTATE[21000]: Cardinality violation: 7 ERROR:  more than one row returned by a subquery used as an expression  
                                                                                                                    


Exception trace:
 () at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:94
 Doctrine\DBAL\Driver\PDOConnection->query() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:830
 Doctrine\DBAL\Connection->executeQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:761
 Doctrine\DBAL\Connection->fetchAll() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:319
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableForeignKeys() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:284
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:268
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:1039
 Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:783
 Doctrine\ORM\Tools\SchemaTool->getDropSchemaSQL() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:727
 Doctrine\ORM\Tools\SchemaTool->dropSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/DropCommand.php:100
 Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand->executeSchemaCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:65
 Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:252
 Symfony\Component\Console\Command\Command->run() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:891
 Symfony\Component\Console\Application->doRunCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:195
 Symfony\Component\Console\Application->doRun() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:126
 Symfony\Component\Console\Application->run() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module.php:58
 include() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module:4




                                                                                                                    
  [PDOException]                                                                                                    
  SQLSTATE[21000]: Cardinality violation: 7 ERROR:  more than one row returned by a subquery used as an expression  
                                                                                                                    


Exception trace:
 () at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:92
 PDO->query() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:92
 Doctrine\DBAL\Driver\PDOConnection->query() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:830
 Doctrine\DBAL\Connection->executeQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:761
 Doctrine\DBAL\Connection->fetchAll() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:319
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableForeignKeys() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:284
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:268
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:1039
 Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:783
 Doctrine\ORM\Tools\SchemaTool->getDropSchemaSQL() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:727
 Doctrine\ORM\Tools\SchemaTool->dropSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/DropCommand.php:100
 Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand->executeSchemaCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:65
 Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:252
 Symfony\Component\Console\Command\Command->run() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:891
 Symfony\Component\Console\Application->doRunCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:195
 Symfony\Component\Console\Application->doRun() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:126
 Symfony\Component\Console\Application->run() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module.php:58
 include() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module:4


orm:schema-tool:drop [--dump-sql] [-f|--force] [--full-database]


 Comments   
Comment by Marco Pivetta [ 22/Oct/14 ]

What are the contents of pg_catalog.pg_class ?

Comment by Dominic Watson [ 22/Oct/14 ]

Uploaded CSV of that table

Comment by Dominic Watson [ 22/Oct/14 ]

After running the subquery as suggested in IRC:

  SELECT c.oid                                                                                                                                                                   
                        FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n                                                                                                                          
                        WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND c.relname = 'state' AND n.nspname = ANY(string_to_array((select replace(replace(setting,'"$user"'  
  ,user),' ','') from pg_catalog.pg_settings where name = 'search_path'),',')) AND n.oid = c.relnamespace  

oid
-------
40152
39687

Comment by Marco Pivetta [ 22/Oct/14 ]

Can you run the query:

SELECT
    c.*
FROM
   pg_catalog.pg_class c, pg_catalog.pg_namespace n
WHERE
    n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
    AND c.relname = 'state'
    AND n.nspname = ANY(string_to_array((
        select replace(replace(setting,'"$user"', user), ' ', '')
        from pg_catalog.pg_settings
        where name = 'search_path'
    ),','))
    AND n.oid = c.relnamespace
Comment by Dominic Watson [ 22/Oct/14 ]
  relname varchar,
  relnamespace oid,
  reltype oid,
  reloftype oid,
  relowner oid,
  relam oid,
  relfilenode oid,
  reltablespace oid,
  relpages int,
  reltuples real,
  relallvisible int,
  reltoastrelid oid,
  reltoastidxid oid,
  relhasindex bool,
  relisshared bool,
  relpersistence char(1),
  relkind char(1),
  relnatts smallint,
  relchecks smallint,
  relhasoids bool,
  relhaspkey bool,
  relhasrules bool,
  relhastriggers bool,
  relhassubclass bool,
  relispopulated bool,
  relfrozenxid xid,
  relminmxid xid,
  relacl _aclitem,
  reloptions _text

state,2200,40154,0,10,0,40152,0,0,0,0,0,0,true,false,p,r,2,0,false,true,false,true,false,true,6694,1,NULL,NULL
state,39587,39689,0,10,0,39687,0,0,0,0,39694,0,true,false,p,r,15,3,false,true,false,false,false,true,6629,1,NULL,NULL
Comment by Dominic Watson [ 22/Oct/14 ]

My ZF2 onBootstrap as well, in case it changes anything:

Unable to find source-code formatter for language: php. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
<?php
namespace Flatscanner;

use Doctrine\ORM\Mapping\UnderscoreNamingStrategy;
use ZF\Apigility\Provider\ApigilityProviderInterface;
use Zend\Uri\UriFactory;
use Doctrine\DBAL\Types\Type;

class Module implements ApigilityProviderInterface
{
    public function getConfig()
    {
        return include __DIR__ . '/../../config/module.config.php';
    }

    public function onBootstrap($e)
    {
        Type::addType('geometry', 'CrEOF\Spatial\DBAL\Types\GeometryType');
        Type::addType('point', 'CrEOF\Spatial\DBAL\Types\Geometry\PointType');
        UriFactory::registerScheme('chrome-extension', 'Zend\Uri\Uri');

        // Set naming strategy
        $em = $e->getTarget()->getServiceManager()->get('doctrine.entitymanager.orm_default');
        $em->getConnection()->getDatabasePlatform()->registerDoctrineTypeMapping("_text", "text"); // assuming it is a text LOB
        $em->getConfiguration()->setNamingStrategy(new UnderscoreNamingStrategy(CASE_LOWER));
    }

    public function getAutoloaderConfig()
    {
        return array(
            'ZF\Apigility\Autoloader' => array(
                'namespaces' => array(
                    __NAMESPACE__ => __DIR__,
                ),
            ),
        );
    }
}
Comment by Steve Müller [ 23/Oct/14 ]

Most probably also affects 2.4 as the codebase has not changed at the critical places. Possibly 2.3 is also affected by this. Could need a check.





[DBAL-1017] Altering a foreign key column is not done properly for MySQL Created: 21/Oct/14  Updated: 21/Oct/14

Status: Open
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.5, 2.4.3
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Christophe Coevoet Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mysql, schematool


 Description   

Altering a foreign key column column (for instance to make it not-nullable) or the index of a foreign key does not work on MySQL (to be exact, it does not work on some MySQL setups, but I haven't found the config setting impacting it yet). Making it work requires dropping the foreign key before altering the column/index and readding it after



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

Christophe Coevoet DBAL-732 related?





[DBAL-873] [GH-571] Introduced a Transaction object Created: 18/Apr/14  Updated: 23/Aug/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!



 Comments   
Comment by Doctrine Bot [ 23/Aug/14 ]

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





[DBAL-970] Implement partial indexes for missing platforms Created: 12/Aug/14  Updated: 12/Aug/14

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

Type: Improvement Priority: Major
Reporter: Steve Müller Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

As a follow-up to DBAL-900 there are some platforms left that support partial indexes too and need the implementation for that feature.
Currently partial indexes are only implemented for PostgreSQL.
Additionally there should be a functional test covering creation and introspection of partial indexes if possible.






[DBAL-968] SQL Server modifyLimitQuery broken Created: 11/Aug/14  Updated: 11/Aug/14

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

Type: Bug Priority: Critical
Reporter: Bill Schaller Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: paginator, sqlserver
Environment:

SQL Server



 Description   

The recent change to SQLServerPlatform.php @jaylinski:improved sqlserver 'doModifyLimitQuery' select-from pattern broke the ORM Paginator's queries on SQL server.

I investigated, and found that some of the test cases for the SQL Server platform weren't actually correct SQL. Also, there were no test cases that covered what the paginator is doing, so I've written test cases for those. I will open a pull request for this issue.

The modifyLimitQuery method in SQLServerPlatform.php should be fixed to pass the fixed old tests and the new tests.

My concern is that that method is becoming too complex, but that's an issue for another day.



 Comments   
Comment by Marco Pivetta [ 11/Aug/14 ]

I'm gonna cry. Thank you, MSSQL, you make our lives so much "easier"





[DBAL-954] Custom QuoteStrategy doesn't work when creating new schema Created: 30/Jul/14  Updated: 30/Jul/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: Piotr Łyczba Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: schematool


 Description   

Implementing custom quote strategy:

class SingleQuoteStrategy extends DefaultQuoteStrategy implements QuoteStrategy
{
    public function getColumnName($fieldName, ClassMetadata $class, AbstractPlatform $platform)
    {
        return isset($class->fieldMappings[$fieldName]['quoted'])
            ? $platform->quoteSingleIdentifier($class->fieldMappings[$fieldName]['columnName'])
            : $class->fieldMappings[$fieldName]['columnName'];
    }
}

doesn't work when a new schema is created.

The problem is that in class file mapping I have column that has dot in its name escaped with back single quotes:

<field name="anuncianteId_delete" type="integer" column="`anunciante_id.delete`" nullable="true"/>

My custom quote strategy is necessary to force Doctrine to respect that. Otherwise Doctrine treats it as: namespace.name.

If I already have table with that column in my database update schema tool doesn't complains, the problem occurs only when I try to create new schema.

The error in resulting SQL Statement:

(SQLSTATE[HY000]: General error: 1 near ".": syntax error' while executing DDL: CREATE TABLE account ( (...) "anunciante_id"."delete" INTEGER DEFAULT NULL, (...) )





[DBAL-943] dbal db2 platform uses incorrect column modification strategy for clob Created: 20/Jul/14  Updated: 20/Jul/14

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

Type: Bug Priority: Critical
Reporter: chris rehfeld Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

db2 v10.5 centos 6.5



 Description   

db2 only allows certain column types to be used in an ALTER TABLE ALTER COLUMN myCol ... type statement.

Example entity

Widget2.php
<?php
/**
 * @ORM\Entity
 **/
class Widget
{
    /** @ORM\Id @ORM\Column(type="integer") @ORM\GeneratedValue **/
    protected $id;

    /** @ORM\Column(type="string") **/
    private $str;
}

orm:schema-tool:create produces:
CREATE TABLE Widget2 (id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, str VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id));

If you then change the column type from string to text via ...

Widget2.php
    /** @ORM\Column(type="text") **/
    private $str;

... and you then run orm:schema-tool:update, it will try to run:

ALTER TABLE WIDGET2 ALTER STR str CLOB(1M) NOT NULL;

The sql syntax is wrong, but that's a different issue I'll address elsewhere.Lets assume it's fixed to be proper syntax:
ALTER TABLE WIDGET2 ALTER COLUMN str SET DATA TYPE CLOB(1M) ALTER COLUMN str SET NOT NULL;

This triggers sql error code -190, sqlstate 42837
http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.codes/src/tpc/n190.dita

Because the from type => to type isn't valid. This link:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.1.0/com.ibm.db2.udb.admin.doc/doc/r0000888.htm?lang=en
seems to list the valid alterations.

I'm guessing that a different strategy needs to be used; where we drop the old column, and create the new one in such scenarios. This could cause unexpected data loss though.






[DBAL-939] schema update doesnt detect boolean type correctly Created: 16/Jul/14  Updated: 20/Jul/14

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

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

centos 6 64, db2 luw10.5



 Description   

*edited*

Dbal for db2 doesn't seem to be able to differentiate between a smallint and a boolean field on db2. If I make an entity which has a boolean field, it will create a table using type smallint. If I later try to update the schema, it will detect the column in the table as a small int, not a boolean. So, it will produce sql update statements to change the type from smallint to smallint, which is obviously unnecessary. I understand db2 doesn't have a boolean type, but it would be nice if dbal realized that a smallint is essentially already a dbal boolean.

Additionally, the update statement is invalid syntax and fails, although this is probably a separate issue.

Example entity

Widget.php
<?php
/**
 * @ORM\Entity
 **/
class Widget
{
    /** @ORM\Id @ORM\Column(type="integer") @ORM\GeneratedValue **/
    protected $id;

    /** @ORM\Column(type="boolean", nullable=false) **/
    private $isGoat;
}

orm:schema-tool:create produces:
CREATE TABLE Widget (id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, isGoat SMALLINT NOT NULL, PRIMARY KEY(id));

If you then run orm:schema-tool:update, it will try to run:

ALTER TABLE WIDGET ALTER ISGOAT isGoat SMALLINT NOT NULL; CALL SYSPROC.ADMIN_CMD ('REORG TABLE WIDGET');

but no column alteration is obviosuly needed.

So in summary, smallint to boolean type mapping isn't realized, causing the update command to think it needs to change the type of the column.






[DBAL-937] Make fieldDeclaration available in getName method of class Type Created: 11/Jul/14  Updated: 11/Jul/14

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: Dominic Tubach Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

I've created a custom type that stores its data in a VARCHAR field. As requiresSQLCommentHint() returns true the value returned by getName() is used as type hint. If I change the length of the field in the entity mapping the scheme won't be updated because getName() still returns the same string. Now I would like to add the length to the returned string. Because this requires access to the fieldDeclaration I suggest to pass this array as paramater to getName().






[DBAL-936] Doctrine type not found exception thrown before checking the comment Created: 10/Jul/14  Updated: 11/Jul/14

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

Type: Bug Priority: Major
Reporter: Maxime Veber Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mapping, mysql


 Description   

Here is the piece of code:

$type = $this->_platform->getDoctrineTypeMapping($dbType);

// In cases where not connected to a database DESCRIBE $table does not return 'Comment'
if (isset($tableColumn['comment'])) {
    $type = $this->extractDoctrineTypeFromComment($tableColumn['comment'], $type);
    $tableColumn['comment'] = $this->removeDoctrineTypeFromComment($tableColumn['comment'], $type);
}

The method `getDoctrineTypeMapping` throw an exception if the type is not found. But for example if you have an enum type (see the doctrine cookbook on the subject), the type is setted as comment.

Doctrine throw the exception before having the occasion to get the type via comment.

Here are two solutions:

  • Check for comment before throw the exception
  • Adding the enum type to the doctrine platform and mapping it to string





[DBAL-933] Get Statement Column Metadata Created: 05/Jul/14  Updated: 06/Jul/14

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

Type: New Feature Priority: Trivial
Reporter: Benoît Burnichon Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

It would be nice to have a utility method in \Doctrine\Dbal\Driver\ResultStatement to properly retrieve the result column names.

Currently, we have to rely on somthing like
$columnNames = array_keys($statement->fetch(\PDO::FETCH_ASSOC));

Problem: It does not work with empty result-sets, and more checks should be performed to handle these.

With PDO, http://www.php.net/manual/en/pdostatement.getcolumnmeta.php could be used to properly retrieve names.

For Sqlite3 it is easy, http://www.php.net/manual/en/sqlite3result.columnname.php

For Mysql, http://www.php.net/manual/en/mysqli-result.fetch-fields.php would do the trick






[DBAL-662] Supporting PHP 5.5 DateTimeImmutable Created: 14/Nov/13  Updated: 02/Jul/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: 3
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?

Comment by Yaroslav Nechaev [ 02/Jul/14 ]

Please add this feature. Mutable DateTime causes too much trouble: now we have to use clone in every getter and setter just in case someone modifies the value afterwards and spoils the value stored in entity.

Comment by Steve Müller [ 02/Jul/14 ]

Yaroslav Nechaev I don't think we can support this before Doctrine 3.0 without breaking BC as Christophe Coevoet already stated because it would require us to bump the minimal supported PHP version to 5.5 (which currently is 5.3.2).





[DBAL-867] Doctrine\DBAL\Driver\Connection should be marked as an internal interface Created: 16/Apr/14  Updated: 26/Jun/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.

Comment by Marco Pivetta [ 26/Jun/14 ]

Guilherme Blanco can you re-check this?





[DBAL-642] Generated IDs are not guaranteed to be unique over the table's lifetime in SQLite Created: 27/Oct/13  Updated: 25/Jun/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: 1
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...

Comment by flack [ 25/Jun/14 ]

Just noticed that the link I posted above is broken now. Here's a stable one:

https://github.com/flack/midgard-portable/blob/04d473356d804c7f64e940ef82dd1538c39fccdd/src/storage/subscriber.php#L170

The workaround is a bit less project-specific now, and might serve as the basis for a real solution I guess (basically, only checks for column type and composite keys would need to be added)





[DBAL-916] Some alter table statements do not respect @Table name value Created: 30/May/14  Updated: 04/Jun/14

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

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

MacOS



 Description   

I have specified the table name for each class generating a table. I find that some table names are respected during doctrine update upon the database, and some are not.

Specifically it seems that simpler entities, one's annotated with entity/table become lower case, while more complicated ones (inheritance map) respect the given table name.



 Comments   
Comment by Julia Smith [ 03/Jun/14 ]

On further inspection, this does not appear to be a bug with Doctrine. A simple dump of the update statements generated shows no table name change, but:

ALTER TABLE Comment DROP FOREIGN KEY FK_5BC96BF03DBEAF48;
DROP INDEX IDX_5BC96BF03DBEAF48 ON Comment;
ALTER TABLE Comment CHANGE replyto_id replytox_id INT DEFAULT NULL;
ALTER TABLE Comment ADD CONSTRAINT FK_5BC96BF0484A4D29 FOREIGN KEY (replytox_id) REFERENCES Comment (id);
CREATE INDEX IDX_5BC96BF0484A4D29 ON Comment (replytox_id);

Somehow causes mysql to change the case of the table on MacOS.

weird.

Comment by Steve Müller [ 04/Jun/14 ]

Julia Smith can you please provide more details concerning your actual problem? I'm not sure I understand what the problem is here. I can't see how the SQL you pointed out changes a table's name in any way so I don't understand how that could change the table name's case.
Concerning identifier casing in general I would suggest you to read the following from the MySQL documentation:
http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html

And a pretty good explanation of the problem with identifiers and case accross operating systems and database vendors:
http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html#.U47DV3IZWlM

If the casing of identifiers (table names, column names etc.) in your schema is important for you, you will either have to quote those in your mapping explicitly or use a custom quoting strategy. Please refer to the documentation for further details:
http://docs.doctrine-project.org/en/latest/reference/basic-mapping.html#quoting-reserved-words

Hope that helps.





[DBAL-474] SchemaManager / Connection on PostgreSQL platform does not respect filterExpression for sequences Created: 27/Mar/13  Updated: 16/May/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