doctrine/dbal

Doctrine Database Abstraction Layer

dbal's Issues

DBAL-26: DateTime type column can't be nullable

Jira issue originally created by user koubas:

After upgrade from B1 to B2 my app stopped working, throwing ConversionException on nullable DateTime field, value of which is null.
There is part of DateTimeType.php which causes it - if (!$val) matches even the valid null value.

public function convertToPHPValue($value, AbstractPlatform $platform)
{
    $val = ($value !== null)
        ? \DateTime::createFromFormat($platform->getDateTimeFormatString(), $value) : null;
    if (!$val) {
        throw ConversionException::conversionFailed($value, $this->getName());
    }
    return $val;
}

DBAL-22: Saving UTC Offset in Postgres DATETIME messes with PHP DateTime and Timezones

Jira issue originally created by user @guilhermeblanco:

The DateTime type is currently unusable in PostgreSQL due to a wrong parsing format.

PostgreSqlPlatform::getDateTimeFormatString() should be changed from:

Y-m-d H:i:sO

to

Y-m-d H:i:s.uO

According to http://twitter.com/auroraeosrose/statuses/16154268629

Also, there's no way to check if a convert From and To PHP/Database worked correctly. Maybe we should introduce an exception when a DBAL\Type conversion fails, otherwise, there's no way to figure it out what happened.

DBAL-31: Move Schema related Creation code from AbstractPlatform to AbstractSchemaManager

Jira issue originally created by user @beberlei:

Move Schema related Creation code from AbstractPlatform to AbstractSchemaManager

DBAL-51: "No identifier/primary key specified for Entity" problem, Doctrine Beta 3 cannot find primary key

Jira issue originally created by user minxuan.guo:

when execute

php doctrine.php orm:convert-mapping --from-database annotation "Entities"

get error message

No identifier/primary key specified for Entity 'Classname(Tablename)'. Every Entity must have an identifier/primary key.

For resolving this problem
I've replaced the line
{quote}
public function loadMetadataForClass($className, ClassMetadataInfo $metadata)
{
...
{color:red}$indexes = $this->tables[$tableName]->getIndexes();{color}
...
}
{quote}
in function "loadMetadataForClass" in \Doctrine\ORM\Mapping\Driver\DatabaseDriver.php by
{quote}
public function loadMetadataForClass($className, ClassMetadataInfo $metadata)
{
...
{color:red}$indexes = $this->_sm->listTableIndexes($tableName);{color}
...
}
{quote}
then the beta 3 works for my date base

It is because doctrine look for $indexes['prime'] to construit the primary key in entities. when we use "$this->tables[$tableName]->getIndexes()", the keys in $indexes are the real name of indexes of the data base, and in my database, the primary key indexes are named PK_TABLENAME. So I have to use $this->_sm->listTableIndexes($tableName) to pre-edit the indexes before get the list

DBAL-11: Improve API of Sqllogger

Jira issue originally created by user @beberlei:

The SqlLogger currently has a very simple API, which lacks for example the possibilty to log the execution time of queries. This would require a second call of the logger however, but i made some performance tests on the current implementation. Its using the following check to look if logging is enabled:

if($this->_config->getSqlLogger())

However:

if($this->_hasLogger)

Only takes half the time. See the test at the bottom.

We could switch to a boolean flag and use the "saved" time to enhance the SQL Logger API to include the following information:

1. SQL (Have already)
2. Params (Have already)
3. Elapsed Time (Start Time and End Time)
4. Query Type (SELECT, INSERT, UPDATE, DELETE, OTHER)

This would benefit both Symfony and ZF since they could hook into the logger for their respective debugging facilities (sf Web Toolbar, Zend Firebug SQL Profiling).

<?php                                                                                             
class Config                                                                                      
{                                                                                                 
    protected $_logger;                                                                           
    public function **construct()                                                                 
    {                                                                                             
        $this->_logger = new stdClass();
    }
    public function getLogger()
    {
        return $this->_logger;
    }
}

class Foo
{
    protected $_config = null;
    public function **construct()
    {
         $this->_config = new Config();
    }
    public function execute()
    {
         if($this->_config->getLogger()) {
         }
    }
}

class Bar
{
    protected $_config = null;
    protected $_loggerExists = false;
    public function **construct()
    {
         $this->_config = new stdClass();
         $this->_loggerExists = true;
    }
    public function execute()
    {
        if($this->_loggerExists) {

        }
    }
}

$f = new Foo();
$s = microtime(true);
for($i = 0; $i < 100; $i<ins></ins>) {
    $f->execute();
}
echo (microtime(true)-$s)."s\n";

$f = new Bar();
$s = microtime(true);
for($i = 0; $i < 100; $i<ins></ins>) {
    $f->execute();
}
echo (microtime(true)-$s)."s\n"

benny@benny-pc:<sub>/code/php/wsnetbeans/Doctrine/trunk/tests$ php /tmp/boolvsmethod.php                
0.00055313110351562s                                                                                  
0.00025200843811035s                                                                                  
benny@benny-pc:</sub>/code/php/wsnetbeans/Doctrine/trunk/tests$ php /tmp/boolvsmethod.php                
0.00051999092102051s                                                                                
0.00025200843811035s                                                                                
benny@benny-pc:<sub>/code/php/wsnetbeans/Doctrine/trunk/tests$ php /tmp/boolvsmethod.php                
0.00036001205444336s                                                                                
0.00017309188842773s                                                                                
benny@benny-pc:</sub>/code/php/wsnetbeans/Doctrine/trunk/tests$ php /tmp/boolvsmethod.php                
0.0005180835723877s                                                                                 
0.00025010108947754s 

DBAL-43: Doctrine\DBAL\Platforms\MsSqlPlatform Generates invalid T-SQL

Jira issue originally created by user the_angry_angel:

MsSqlPlatform::getAlterTableSQL generates a reasonable quantity of invalid T-SQL (to my knowledge, I'd happily be corrected if wrong).

Unfortunately SqlSrv (to my knowledge) only accepts "ALTER old_old_name column_definition" and renames of tables and columns are only capable using a stored procedure.

I'm happy to produce a patch but I think I need a bit of guidance to do so more quickly :)

The major one is that getAlterTableSQL will need to produce multiple SQL statements, but in my testing I'm not able to get this to do so without some additional problems, and I've not yet had the opportunity to dive into the bowels of the rest of the schema bits and bobs to understand why. If anyone could point me in the vague direction that would be really handy :)

DBAL-58: Schema tool does not see difference between onDelete="NO ACTION" and onDelete="RESTRICT"

Jira issue originally created by user obrys:

If database has ON DELETE CASCADE, schema tool detects change to NO ACTION and RESCRICT.
If database has ON DELETE RESCRICT, schema tool detects change only in case of CASCADE.
If database has ON DELETE NO ACTION, schema tool detect change only in case of CASCADE.

Detecting of changes is done by:
doctrine orm:schema-tool update --dump-sql
and
doctrine orm:validate-schema

DBAL-45: Add CLI tool that checks for Reserved Keywords

Jira issue originally created by user @beberlei:

Add a CLI tool that checks for reserved keywords in your current schema (for other databases vendors) to support cross vendor portability.

DBAL-28: Better error messages while creating schema

Jira issue originally created by user zyxist:

I'd like to suggest some improvements in the schema builder concerning error messages, which do not tell the most important information needed to find the problem quickly and without irritation. Below, I present a simple case I encountered yesterday: I had a quite big schema to generate, but I made a mistake in index column names. Doctrine thrown me the following exception:

Doctrine\DBAL\Schema\SchemaException
An unknown column-name name was given.

What I miss, is the table name, where I try to define such an index. I took a look at the messages for SchemaException and I noticed that none of them provides it, except those ones which refer directly to tables.

Another small, but useful improvement is enclosing the table/column/index/sequence/anything-else names in quotes or double quotes in order to make them better distinguishable from the message body. My first impression from the message above was that I have a "column-name" name defined somewhere, not a column named "name".

To sum up, the expected message should look like this:

Doctrine\DBAL\Schema\SchemaException
An unknown column-name 'name' was given for index 'foo' in table 'bar'.

The problem is minor, but I would be grateful if you remembered about it in the near future, so that I could have more useful error messages at least in the stable releases.

DBAL-48: Doctrine\DBAL\Platforms\MsSqlPlatform Incorrect Offset Limit Sql

Jira issue originally created by user scottsweep:

The way the offset query is constructed (line 473) it always pulls the top records from the query even when and offset is present, so if you have an offset of 0 and limit of 10 you get the first 10 records. When you change the offset to 5 you still get the first 10 records from the original query. This issue was also in the 1.2 driver code.

This is basically what gets created when an offset is present with a limit of 10 and an offset of 5 :
SELECT * FROM
(SELECT TOP 10 * FROM
(SELECT TOP 15
col1, col2, colN FROM table
) AS [inner_tbl]
) AS [outer_tbl]

The fix is to reconstruct the query to remove the outer_tbl SELECT TOP and make it something like the following:
SELECT * FROM (
SELECT TOP [limit] ROW_NUMBER() OVER (ORDER BY [primary_key]) as RowNum, col1, col2, colN FROM table
) as [inner_tbl] WHERE [inner_tbl].RowNum BETWEEN [offset] AND [limit + offset]

DBAL-8: Fix MsSql Platform

Jira issue originally created by user @beberlei:

MsSql is currently not supported anymore, because the platform driver is major out of date. We need to find someone that fixes the platform. Additionally an SqlSrv Driver for use of MsSql with Windows would be awesome.

DBAL-49: PDOSqlsrv's constructor breaks when using non "local" server host

Jira issue originally created by user aarondm:

Problem:

Currently, the *_constructPdoDsn()* for the PDOSqlsrv driver works fine only if the host provided is: local or localhost.
If using an IP address, this breaks. Reason is because _constructPdoDsn() applies ( and ) around the host.

        $dsn = 'sqlsrv:server=(';
        if (isset($params['host'])) {
            $dsn .= $params['host'];
        }
        $dsn .= ')';

This is fine for when $params['host'] = 'local'; because I believe local is a name pipe (I think) and to properly use it you need to add the brackets around it

However if $params['host'] = '127.0.0.1'; this breaks!

Fix:

Get rid of the forced brackets

        $dsn = 'sqlsrv:server=';
        if (isset($params['host'])) {
            $dsn .= $params['host'];
        }

That way, when supplying *local* as a host, you have to enter (local) (which makes more sense as that's how you would do it normally in other applications).

DBAL-46: Fix doctrine-dbal console command

Jira issue originally created by user @beberlei:

It still uses Symfony\Components with S.

DBAL-17: E_STRICT error in Doctrine\DBAL\Driver\OCI8\Driver::_constructDsn()

Jira issue originally created by user eriksencosta:

I get an E_STRICT error in Doctrine\DBAL\Driver\OCI8\Driver::_constructDsn() because I forgot to set a 'dbname' key in the params array to Doctrine\DBAL\DriverManager.

My Doctrine is a svn checkout, from 2.0.0-beta1 tag.

A patch is attached (patch -p0 < patch.diff on project root).

DBAL-23: PostgreSQL datatypes not recognised (tsvector, _int4, cidr)

Jira issue originally created by user mitchtid:

On reverse engineering one of my PostgreSQL apps, I noticed a few datatypes that weren't recognised in ..

Doctrine/DBAL/Platforms/PostgreSqlPlatform.php

tsvector (treat as string?)
cidr (treat as string?)
_int4 (treat as integer?)

DBAL-24: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S02]: Base table or view not found: 1109 Unknown table

Jira issue originally created by user hinikato:

The /Doctrine/DBAL/Platforms/MySqlPlatform->getListTableForeignKeysSQL() method returns SQL, that causes the following error:

exception 'PDOException' with message 'SQLSTATE[42S02]: Base table or view not found: 1109 Unknown table 'variables' in information_schema' in X:\home\localhost\www\incubator\doctrine2\libraries\Doctrine\DBAL\Connection.php:568
Stack trace:
#0 X:\home\localhost\www\incubator\doctrine2\libraries\Doctrine\DBAL\Connection.php(568): PDO->query('SELECT DISTINCT...')
#1 X:\home\localhost\www\incubator\doctrine2\libraries\Doctrine\DBAL\Connection.php(524): Doctrine\DBAL\Connection->executeQuery('SELECT DISTINCT...', Array)
#2 X:\home\localhost\www\incubator\doctrine2\libraries\Doctrine\DBAL\Schema\AbstractSchemaManager.php(254): Doctrine\DBAL\Connection->fetchAll('SELECT DISTINCT...')
#3 X:\home\localhost\www\incubator\doctrine2\libraries\Doctrine\DBAL\Schema\AbstractSchemaManager.php(214): Doctrine\DBAL\Schema\AbstractSchemaManager->listTableForeignKeys('variables')
#4 X:\home\localhost\www\incubator\doctrine2\libraries\Doctrine\DBAL\Schema\AbstractSchemaManager.php(199): Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails('variables')
#5 X:\home\localhost\www\incubator\doctrine2\libraries\Doctrine\ORM\Mapping\Driver\DatabaseDriver.php(155): Doctrine\DBAL\Schema\AbstractSchemaManager->listTables()
#6 X:\home\localhost\www\incubator\doctrine2\libraries\Doctrine\ORM\Mapping\ClassMetadataFactory.php(103): Doctrine\ORM\Mapping\Driver\DatabaseDriver->getAllClassNames()
#7 X:\home\localhost\www\incubator\doctrine2\index.php(53): Doctrine\ORM\Mapping\ClassMetadataFactory->getAllMetadata()
#8 {main}

The sessions table looks like:

CREATE TABLE `sessions` (
  `id` char(32) NOT NULL DEFAULT '',
  `modified` int(11) DEFAULT NULL,
  `lifetime` int(11) DEFAULT NULL,
  `data` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

DBAL-10: Implement PDO SqlSrv

Jira issue originally created by user @beberlei:

DBAL-6: MySQL BLOB datatypes throw DoctrineException::unknownColumnType() exception

Jira issue originally created by user mjh_ca:

MySQL BLOB datatype appears to be breaking ClassMetadataExporter. Very new to Doctrine so hopefully I've isolated this correctly.

CREATE TABLE foo (
    id      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    bar   BLOB,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

// ... setup entitymanager ....

$cme = new \Doctrine\ORM\Tools\Export\ClassMetadataExporter();
$sm = $em->getConnection()->getSchemaManager();
$cme->addMappingSource($sm, 'database');
$metadatas = $cme->getMetadatasForMappingSources();
$exporter = $cme->getExporter('annotation', '/path/to/annotations');
$exporter->setMetadatas($metadatas);
$exporter->export();

php -f doctrine Orm:convert-mapping --from-database --to=annotation --dest C:\www\app\models

Fatal error: Uncaught exception 'Doctrine\Common\DoctrineException' with message 'Unknown column type' in C:\www\app\lib\Doctrine\Common\DoctrineException.php:112

Stack trace:
#0 [internal function]: Doctrine\Common\DoctrineException::**callStatic('unknownColumnTy...', Array)
#1 C:\www\app\lib\Doctrine\DBAL\Types\Type.php(125): Doctrine\Common\DoctrineException::unknownColumnType('blob')
#2 C:\www\app\lib\Doctrine\DBAL\Schema\MySqlSchemaManager.php(262): Doctrine\DBAL\Types\Type::getType('blob')
#3 C:\www\app\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php(802): Doctrine\DBAL\Schema\MySqlSchemaManager->_getPortableTableColumnDefinition(Array)
#4 C:\www\app\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php(221): Doctrine\DBAL\Schema\AbstractSchemaManager->_getPortableTableColumnList(Array)
#5 C:\www\app in C:\www\app\lib\Doctrine\Common\DoctrineException.php on line 112

DBAL-47: Incorrect filename -- EchoSqlLogger.php

Jira issue originally created by user saniok:

I've got error:
require(/var/www/betportal/dev.betportal.org/application/libraries/Doctrine/DBAL/Logging/EchoSqlLogger.php) [function.require]: failed to open stream: No such file or directory

Doctrine/DBAL/Logging/EchoSQLLogger.php should be renamed to EchoSqlLogger.php.

DBAL-14: PATCH: Typo when taking over MySQL engine option

Jira issue originally created by user dennis.verspuij:

When providing option 'engine' the value is not taken over correctly.
Attached the simple patch to fix this.

DBAL-12: Add Doctrine\DBAL\SQLQuery package with simple Query Objects

Jira issue originally created by user @beberlei:

We should add a Query Objects package to Doctrine\DBAL. This won't necessarily be used with Doctrine\ORM, however it could be really helpful to people that only plan to use Doctrine\DBAL and not the ORM.

Requirements:

• Add Query Objects for all types of SELECT, INSERT, UPDATE, DELETE
• Fluent Interface
• Make use of Platform to abstract limit subquery and expressions
• Add factory method on Doctrine\DBAL\Connection

API Ideas:

• For the INSERT, UPDATE, DELETE I like the approach taken by eZ Components: http://www.ezcomponents.org/docs/api/trunk/introduction_Database.html#query-abstraction
• For the SELECT I find Zend_Db_Select much more usable: http://framework.zend.com/manual/en/zend.db.select.html

Are there query objects in other languages that could help here?

DBAL-9: Implement Sqlsrv Driver

Jira issue originally created by user @beberlei:

Implement Sqlsrv Driver

DBAL-19: Copyright to eZ Systems in some classes of DBAL

Jira issue originally created by user eriksencosta:

I found three classes with the copyright tag to "eZ Systems". I am not sure if it's a mistake or not.

• Doctrine\DBAL\Schema\Comparator
• Doctrine\DBAL\Schema\SchemaDiff
• Doctrine\DBAL\Schema\TableDiff

I searched recursively in Doctrine2 ORM 2.0.0-beta1. Only the above have this tag.

DBAL-30: Doctrine\DBAL\Connection::query() method does not connect automatically to the database

Jira issue originally created by user zyxist:

The database access methods in Doctrine\DBAL\Connection automatically connect to the database, if the connection has not been created yet. This is not true in case of Doctrine\DBAL\Connection::query() - if we forget to connect manually, we get:

Warning: call*user_func*array() expects parameter 1 to be a valid callback, first array member is not a valid class name or object in /.../Doctrine/DBAL/Connection.php on line 608

The problem is caused by the lack of

$this->connect();

line within the method body.

DBAL-5: Generalize SchemaManager Db Column to Doctrine Type Detection

Jira issue originally created by user @beberlei:

Currrently the Transformation from each Database Column to a Doctrine Type is hardcoded in the SchemaManager. This code has to be generalized and moved to the Platform so that people supplying custom doctrine types can overwrite this. The API for this sould look like:

$platform->registerDoctrineType($doctrineTypeName, $dbColumnType)

This way we could supply all the defaults using this API and give users the ability to overwrite or extend it based on their needs.

DBAL-29: Comparator: Changing one column name and dropping one leads to changing both into the new name.

Jira issue originally created by user @beberlei:

If you have a table and in a new version rename one and drop another column both of exactly the same definition, the comparator will errorusly optimize the one to rename both columns to the new one.

In these cases where it is not detectable which is the renamed and which the dropped it should drop both columns and create the new one. The user has to optimize this himself.

DBAL-13: Missing exception

Jira issue originally created by user jkleijn:

PHP Fatal error: Call to undefined method Doctrine\DBAL\ConnectionException::commitFailedNoActiveTransaction() in /usr/share/php/lib/Doctrine/DBAL/Connection.php on line 735

DBAL-21: Pgsql: getListTableColumnsSQL: read domains info (patch provided)

Jira issue originally created by user rdehouss:

Hello,

If you use DOMAIN (CREATE DOMAIN) in pgsql, PostgreSqlPlatform generate a "new \Doctrine\DBAL\DBALException("Unknown database type ".$dbType." requested, " . get_class($this) . " may not support it.");" exception because it does not know the type "domain_name" (CREATE DOMAIN domain_name AS VARCHAR(80); )

With this patch, it checks if the given type (either normal type, either domain) is a domain, and if yes, takes the right info (domain_name is type varchar and complete_type character varying(80)). If it's not a domain, it takes the normal type.

(patch -p0 doctrine2/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php < pgsql_domains.patch)

Also, it's not the goal here, but the queries could be optimized using joins in place of auto join + where, but, as said, the speed is not the goal since the queries are there only for entities mapping.

Hope it helps.

Best regards,

Raphaël Dehousse

DBAL-27: Array and Object are both CLOBs but get are subject to change in Comparator

Jira issue originally created by user @beberlei:

Array and Object Types are both CLOB fields, however the comparator detects them to be changed all the time since types are compared without checking if their underlying SQL is the same.

There should be an additional check $type1->getSQLDeclaration() == $type2->getSQLDeclaration()

DBAL-38: Unused DBAL\Events (preExecute, postExecute)...

Jira issue originally created by user bjori:

The ORM has a neat collection of Events, but the DBAL doesn't have any.
There is an DBAL/Events.php class which has defined constants for preExec, postExec, preExecute and postExecute (similar to ORM/Events.php), but these seem to be completely unused.
The only event/loging seems to be the ->logSql() event.

A preExecute & postExecute events would be great for timing/profiling queries.

DBAL-36: Database selection with DSN

Jira issue originally created by user bostjan:

When construstring DSN in Doctrine\DBAL\Driver\PDOSqlsrv\Driver it should add selection of database in DSN.

So for example DSN will look like:

$dsn ="sqlsrv:Server=(local) ; Database = AdventureWorks "; 

As writen in chm manual which come with Sql server drivers for PHP v2

DBAL-18: Doctrine\DBAL\Schema\SchemaDiff generates foreign keys statments for new tables even if the database platform doesn't support them

Jira issue originally created by user eriksencosta:

When running the code snippets of http://www.doctrine-project.org/projects/dbal/2.0/docs/reference/schema-representation/en, I found that Doctrine\DBAL\Schema\SchemaDiff generates foreign keys statments for new tables even if the database plataform doesn't support them. My connection is a pdo_sqlite.

<?php
$schema = new \Doctrine\DBAL\Schema\Schema();

// Doctrine\DBAL\Schema\Schema pode ser usado para criar representações do
// schema, como a criação de tabelas
$myTable = $schema->createTable('my_table');
$myTable->addColumn('id', 'integer', array('unsigned' => true));
$myTable->addColumn("username", "string", array("length" => 32));
$myTable->setPrimaryKey(array("id"));
$myTable->addUniqueIndex(array("username"));

// SQLite does not supports sequences.
//$schema->createSequence("my*table*seq");

// Clone just to test Doctrine\DBAL\Schema\Comparator
$fromSchema = clone $schema;

$myForeign = $schema->createTable("my_foreign");
$myForeign->addColumn("id", "integer");
$myForeign->addColumn("user_id", "integer");
$myForeign->addForeignKeyConstraint($myTable, array("user_id"), array("id"), array("onUpdate" => "CASCADE"));

$queries = $schema->toSql($conn1->getDatabasePlatform()); // get queries to create this schema.
$dropSchema = $schema->toDropSql($conn1->getDatabasePlatform()); // get queries to safely delete this schema.

var_dump($queries);
var_dump($dropSchema);


$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($fromSchema, $schema);

$queries     = $schemaDiff->toSql($conn1->getDatabasePlatform());     // queries to get from one to another schema.
$saveQueries = $schemaDiff->toSaveSql($conn1->getDatabasePlatform());

var_dump($queries);
var_dump($saveQueries);
?>

Both var_dump calls will output something like this:

array(2) {
  [0]=>
  string(71) "CREATE TABLE my*foreign (user*id INTEGER NOT NULL, id INTEGER NOT NULL)"
  [1]=>
  string(105) "ALTER TABLE my*foreign ADD CONSTRAINT my_foreign_user_id_fk FOREIGN KEY (user_id) REFERENCES my*table(id)"
}

DBAL-39: SQL Logger should log the last failed query

Jira issue originally created by user mridgway:

The SQL Logger is only called after a query has been executed (this was changed to allow profiling of the queries). This makes it difficult to determine which query has failed.

The commit that changed the functionality is at: http://github.com/doctrine/dbal/commit/fe0f0e4b8c6c5308cab3d683c552f61317de7067

It seems that the logger interface needs to be changed to two functions, a pre-query function and a post-query function. Profiling should be handled by the logger if it chooses and not by the connection class.

DBAL-41: missing column type "float"

Jira issue originally created by user chriswest:

The former supported column type "float" is not defined in Doctrine\DBAL\Types\Type.

I noticed that floats are mapped like numerics/decimals within mysq right nowl:

protected function _getPortableTableColumnDefinition($tableColumn)
    {
       ...
        // Map db type to Doctrine mapping type
        switch ($dbType) {
            ...
            case 'float':
            case 'double':
            case 'real':
            case 'numeric':
                $type = 'decimal';
                break;

I suggest to map them to a "true" float and double as available in mysql, since they use less storage space compared to numerics with fixed decimals in certain cirtumstances (-> performance issue). http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

DBAL-42: Use column comments for further Doctrine Type Inference

Jira issue originally created by user @beberlei:

Any custom type could use vendors column comment functionalities to allow more details on the specific type of a column that can be used inside the "listTableColumns()" method.

DBAL-2: Schema Comparator - Changes of only Identifier Strategy are not detected

Jira issue originally created by user @beberlei:

When changing metdata from a NONE to an IDENTITY generator only for example on MySQL, SchemaComparator does not pick up the changes.

DBAL-20: Add Connection Resolver

Jira issue originally created by user @beberlei:

There should be an additional, optional "connectionResolver" which returns a driver instance for differentation between different drivers in Doctrine\DBAL\Connection instead of using $this->_conn (which stays the default case)

This is useful for master/slave, master/master or failover strategies inside the application

DBAL-33: Doctrine2 fails handling microseconds from PostgreSQL record

Jira issue originally created by user jantichy:

A column in database may be defined as both TIMESTAMP WITH TIMEZONE and TIMESTAMP WITHOUT TIMEZONE. If I insert a new value directly to the database through NOW() function, the value is stored including microseconds.

But then, when I am trying to load the record to Doctrine entity, following exception is thrown:

---

Doctrine\DBAL\Types\ConversionException

Could not convert database value "2010-07-17 15:29:57.762+02" to Doctrine Type datetimetz

File: C:\dev\etrener\library\Doctrine\DBAL\Types\ConversionException.php Line: 46

---

The same with both datetime and datetimetz columns.

The problem is probably in PostgreSqlPlatform::getDateTimeTzFormatString(), where is the following row:
public function getDateTimeTzFormatString()

{ return 'Y-m-d H:i:sO'; }

But PostgreSQL stores timestamps with microseconds, so format should be maybe something like:

{ return 'Y-m-d H:i:s.uO'; }

The problem is already posted in http://www.doctrine-project.org/jira/browse/[DBAL-22](http://www.doctrine-project.org/jira/browse/DBAL-22)?focusedCommentId=13574&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_13574, but that issue is already closed so maybe it was overlooked already then.

Thank you for fix!

DBAL-4: missing column type "enum"

Jira issue originally created by user chriswest:

The former supported column type "enum" is not defined in Doctrine\DBAL\Types\Type.

I've included a patch which enables the missing "enum" type in AnnotationDriver, SchemaTool and DBAL/Types. Currently, only the MySQL Platform is supported (others throw exceptions). Please have a look if you find this a possible solution. A new type "EnumType" has been added.

An example docblock syntax would be:

     /****
     * @Column(name="myenum", type="enum", values={"email","nickname"})
     */
    private $myenum;

patch attached.

DBAL-34: MySql getListTableForeignKeysSQL doesn't work for 5.0.xx

Jira issue originally created by user tiw:

The sql contains mysql specific code. And for the mysql 5.0.xxx the sql statement has syntax error.

in /Doctrine/DBAL/Platforms/MySqlPlatform.php:
public function getListTableForeignKeysSQL($table, $database = null)
{
$sql = "SELECT DISTINCT k.CONSTRAINT*NAME, k.COLUMN_NAME, k.REFERENCED_TABLE*NAME, ".
"k.REFERENCED*COLUMN_NAME /_!50116 , c.update_rule, c.delete_rule / ".
"FROM information_schema.key_column_usage k /!50116 ".
"INNER JOIN information_schema.referential_constraints c ON ".
" c.constraint_name = k.constraint_name AND ".
" c.table_name = '$table' */ WHERE k.table_name = '$table'";

    if ($database) {
        $sql .= " AND k.table*schema = '$database' AND c.constraint*schema = '$database'";
    }

    $sql .= " AND `REFERENCED*COLUMN*NAME` is not NULL";

    return $sql;

}

For the mysql lower as 5.1.16 the SQL could be as the following:

SELECT DISTINCT k.CONSTRAINT*NAME, k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME FROM information_schema.key_column_usage k WHERE k.table_name = 'some_table' AND k.table_schema = 'some database' AND c.constraint_schema = 'some database' AND REFERENCED_COLUMN*NAME is not NULL

In this statement there is no reference of c

DBAL-35: PDO_Sqlsrv bug in _constructPdoDsn()

Jira issue originally created by user bostjan:

When connection to mssql server with pdo_sqlsrv driver you get:

[PDOException]
SQLSTATE[IMSSP]: The DSN string ended unexpectedly.

Sample $connectionOptions:

$connectionOptions = array(
    'dbname' => 'AdventureWorks',
    'user' => 'sa',
    'password' => 'sa_password',
    'host' => 'local',
    'driver' => 'pdo_sqlsrv'
);

Error is in Doctrine\DBAL\Driver\PDOSqlsrv\Driver* in function *_constructPdoDsn(array $params).
On line 53 dsn starts with

$dsn = 'sqlsrv:(';

instead of

$dsn = 'sqlsrv:server=(';

as written in offical Microsoft help which come with SQL Server Driver For PHP v2.

DBAL-40: Transparent table&column names escaping

Jira issue originally created by user jantichy:

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](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?

DBAL-1: PostgreSQL driver doesn't support date type TIMESTAMP WITHOUT TIME ZONE

Jira issue originally created by user drevolution:

Doctrine PostgreSqlPlatform driver supports only TIMESTAMP(0) WITH TIME ZONE, but I think TIMESTAMP WITHOUT TIME ZONE is also often used so Doctrine should support it.

DBAL-44: nullable is not working for all datatypes

Jira issue originally created by user d.freudenberger:

The nullable=true annotation is ignored from at least following Types:

• SmallIntType
• DecimalType
• BooleanType (not sure if nullable makes sense here)

DBAL-3: Dblib/MS SQL driver

Jira issue originally created by user ss10sb:

Since the MS SQL driver is only partly there and I was in desperate need of it, I threw together a pdo_dblib hacktacular mess. Unfortunately my MS SQL database is readonly and as it is magically updated from a remote Oracle database, somehow it doesn't have primary keys assigned, so I wasn't able to test if it pulls identifiers correctly (I disabled the primary key requirement and manually set the ID fields in the XML after I generated them from the database). I'm sure there are probably many other problems since I avoid MS SQL when at all possible and know next to nothing about it, but with the help of the other drivers and some fun Google searches, I managed to hack together a driver/platform/schema package that will at least read and hydrate without too many explosions :)
If you guys can use it, please take the code and revamp it to your standards.

DBAL-37: auto increment can not be added to a column, when postgresql is used.

Jira issue originally created by user tiw:

To add auto increment to on column using the following code in the
migration class:

$systemSettingsTable->changeColumn('setting_id',

array('autoincrement' => true));
or
$settingIdColumn = $systemSettingsTable->getColumn('setting_id');
$settingIdColumn->setAutoincrement(true);

This works fine when MySQL is used. But when PostGreSQL is used, this
statement doesn't create any SQL statement.

The patch in attachment could perhaps resolve this problem some way.

DBAL-25: allow string value in DateType and DateTimeType

Jira issue originally created by user ss10sb:

I am lazy and often dump dates into tables from forms without really manipulating them. It would be great to have the Date(Time)Type::convertToDatabaseValue() accept a string value as well as the DateTime object.
Something like this might work? It might need some error handling thrown in though for invalid date strings.

    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        if (!($value instanceof  \DateTime) && $value !== null) $value = new DateTime($value);
        return $value !== null ? $value->format($platform->getDateTimeFormatString ()) : null;
    }

DBAL-32: Ensure that error mode is EXCEPTION if existing PDO instance used

Jira issue originally created by user romanb:

I am not sure whether we do that already but I remember having talked about it. It would probably be good to ensure that error mode is EXCEPTION if an existing PDO instance is used to create a DBAL Connection. Simply forcing the error mode by using setAttribute(PDO::ATTR_ERRMODE, PDO::ATTR_ERRMODE_EXCEPTION) should be sufficient.

DBAL-50: PgSQL driver does not create indexes on foreign key columns

Jira issue originally created by user petr_motejlek:

The PostgreSQL database does not create indexes for foreign key columns, the user has to create them by hand. I think that indexes for foreign keys should be created automatically... On my system, an index will not be created automatically for the group_id column in the user table.

/****
 * @Entity
 */
class User {
    /****
     * @ManyToOne(targetEntity="Group", inversedBy="users")
     */
    protected $group;
}

/****
 * @Entity
 */
class Group {
    /****
     * @OneToMany(targetEntity="User", mappedBy="group")
     */
    protected $users;

    public function **construct() {
        $this->users = new \Doctrine\Common\Collections\ArrayCollection();
    }
}

I am using current git clone and PgSQL 8.4.

DBAL-16: dbal:import throws LogicException - Cannot add an argument after an array argument.

Jira issue originally created by user darkangel:

If I run the following command:

php doctrine.php dbal:import "..\data\sql\initial-data.sql"

I get:

Cannot add an argument after an array argument.```