[DBAL-109] Doctrine/DBAL/Platforms/MySqlPlatform.php is missing test coverage around _getCreateTableSQL method Created: 12/Apr/11  Updated: 28/Dec/13  Resolved: 28/Dec/13

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

Type: Improvement Priority: Major
Reporter: Wil Moore III Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

2.1.0-DEV



 Description   

In "Doctrine/DBAL/Platforms/MySqlPlatform.php", the following methods are public: getShowDatabasesSQL, getCreateDatabaseSQL, getDropDatabaseSQL, getDropTableSQL (easy to test); however, the "_getCreateTableSQL" method is not public.

Is there a specific reason for this difference?

As a result of this method not being tested, there is a "quote" method being called which doesn't exist.

See: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/MySqlPlatform.php#L410

The only way at this time to test for this case is to get an error when doing: $table->addOption('comment', '...');

I would suggest making the protected method public so it can be tested. I also suggest moving the options parsing out of _getCreateTableSQL into a helper method...this method seems a bit overloaded.

If agreed, I'm happy to take care of the cleanup. Just wanted to get some feedback on the ideas first.



 Comments   
Comment by Benjamin Eberlei [ 18/Nov/11 ]

This is an improvement

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

Is this issue still an issue? I neither see a "quote" method nor a "$table->addOption()" here...

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

Undefined method call fixed in commit: https://github.com/doctrine/dbal/commit/79e04e98895648e2a72075377dac13a3e7c7fd53





Length of a string column cannot exceed 255 (DBAL-62)

[DBAL-69] Varchar definition should automatically switch to CLOB for sizes larger than max varchar length. Created: 27/Nov/10  Updated: 23/Dec/13  Resolved: 23/Dec/13

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.0-RC1-RC3
Fix Version/s: 2.1

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


 Description   

In the future we would probably allow arbitrary large sizes here and switch to a CLOB definition automatically if the specifed string length is larger than max length.



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

Fixed in commit: https://github.com/doctrine/dbal/commit/e57e92e1c0ee7e7949c5e82236a5bb96973ab044





[DBAL-136] OCI8 Driver MUST support connections by SERVICE_NAME Created: 19/Jul/11  Updated: 18/Aug/11  Resolved: 18/Aug/11

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

Type: New Feature Priority: Major
Reporter: Daniel Lima Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates DBAL-141 PDO Connection Failure through TNS - ... Resolved

 Description   

The existing oci8 driver connection only supports connections from database name configuraton.
It will be very good if is possible set a connection by service name.

Take a look in this DSN:

(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=192.168.0.1) (PORT=1521) ) (CONNECT_DATA= (SERVICE_NAME=TH01) ) )

More info:
http://stackoverflow.com/questions/43866/how-sid-is-different-from-service-name-in-oracle-tnsnames-ora






[DBAL-148] Foreign key creation fails with MySQL 5.1.54 Created: 16/Aug/11  Updated: 18/Aug/11  Resolved: 18/Aug/11

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

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

MySQL 5.1.54, PHP 5.3.5, Ubuntu 11.04, Zend Framework 1.11.10


Attachments: Text File git_commit_9242fb332baaaeb81be4.txt     File Job.php     Text File orm_schema-tool_create--dump-sql.txt     File Position.php    

 Description   

doctrine orm:schema-tool:create

returns

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Position(id)' at line 1

The problem seem to be that the create commande creates the following SQL syntax
ALTER TABLE Job ADD FOREIGN KEY (position_id) REFERENCES Position(id);

which doesn't work. If I use the following (added a space between Postion and (id) in the end) it works.
ALTER TABLE Job ADD FOREIGN KEY (position_id) REFERENCES Position (id);

Patch for AbstractPlatform.php attached.



 Comments   
Comment by Benjamin Eberlei [ 18/Aug/11 ]

Fixed and merged 2.1.x





[DBAL-134] Doctrine\DBAL\Connection::query() no logging Created: 24/Jun/11  Updated: 28/Jun/11  Resolved: 28/Jun/11

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

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


 Description   

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

I guess it may be fixed like this:

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

        $args = func_get_args();

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

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

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

        return $statement;
    }


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

Fixed





[DBAL-135] Index::overrules() does not detect pks overruling unique indexes Created: 26/Jun/11  Updated: 26/Jun/11  Resolved: 26/Jun/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.6
Fix Version/s: 2.1

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


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

Fixed in https://github.com/doctrine/dbal/commit/2cb22496a732029373d98ff9e4d54a7187ee9bb8





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

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

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


 Description   

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



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

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

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

ADD UNIQUE INDEX PRIMARY ON foo (bar)

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

ALTER TABLE foo ADD PRIMARY KEY (bar)

which works fine. Also

DROP INDEX primary ON foo

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

ALTER TABLE foo DROP PRIMARY KEY

seems better as well.

Comment by Benjamin Eberlei [ 26/Jun/11 ]

Fixed





[DBAL-123] Add Ability to Unset SQL Logger in Configuration Created: 16/May/11  Updated: 19/Jun/11  Resolved: 19/Jun/11

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

Type: New Feature Priority: Trivial
Reporter: Michael Ridgway Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

Add the ability to remove the SQL logger after it has been set on the configuration.

public function setSQLLogger(SQLLogger $logger)

could be changed to

public function setSQLLogger(SQLLogger $logger = null)

or another function could be added to disable the logger.



 Comments   
Comment by Michael Ridgway [ 24/May/11 ]

Added pull request for making the parameter default to null: https://github.com/doctrine/dbal/pull/26

Comment by Benjamin Eberlei [ 19/Jun/11 ]

Implemented





[DBAL-113] The Sequence class lacks last value / start value Created: 26/Apr/11  Updated: 19/Jun/11  Resolved: 19/Jun/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.4
Fix Version/s: 2.1

Type: Bug Priority: Minor
Reporter: Thomas Lundquist Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux / Postgresql



 Description   

The Sequence class only has _initalValue which means that when a sequence is altered (by DROP and CREATE) the existing last_value, aka existing sequence ID is lost and the first attempt on flushing a new record results in a pkey exception.

This result in the sequence counter starting all over.

This can be handled by setting _initalValue to whatever is the last sequence value but since it is also used for setting MINVALUE this may not be a good idea. They should be split.

Not sure what name to use, last_value is from postgresql but I'm sure others use different names.



 Comments   
Comment by Denis [ 08/Jun/11 ]

Not sure if this is entirely relevant to the question, but you can get the full details on a sequence by selecting * from it:

test=# select * from test_id_seq;
sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
------------------------------------------------------------------------------------------------------+----------
test_id_seq | 1 | 1 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | t
(1 row)

Comment by Benjamin Eberlei [ 19/Jun/11 ]

Fixed by using ALTER SEQUENCE which only changes the increment.





[DBAL-112] Sequence alteration does not alter. Created: 26/Apr/11  Updated: 19/Jun/11  Resolved: 19/Jun/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.4
Fix Version/s: 2.1

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

Linux & Postgesql



 Description   

When the schema diff decides there are differences between the existing SEQUENCE and the "new" one it alters the SEQUENCE by a DROP and then a CREATE.

The CREATE is done with the fromSchema as a base which results in a no-changed SEQUENCE.

Line 102 in Doctrine/DBAL/Schema/Comparator.php uses fromSchema but shuld have been using toSchema or just $sequence

This will fix the immediate issue but still require the sequence name being the same.



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

Fixed





[ORACLE] Speed improvement of mechanism for limiting query results (DBAL-93)

[DBAL-130] Add tests for modify limit query functionality Created: 17/Jun/11  Updated: 17/Jun/11  Resolved: 17/Jun/11

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

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





[DBAL-93] [ORACLE] Speed improvement of mechanism for limiting query results Created: 16/Feb/11  Updated: 17/Jun/11  Resolved: 17/Jun/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0.1
Fix Version/s: 2.1

Type: Improvement Priority: Minor
Reporter: Martin Ivičič Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

any


Sub-Tasks:
Key
Summary
Type
Status
Assignee
DBAL-130 Add tests for modify limit query func... Sub-task Resolved Benjamin Eberlei  

 Description   

Currently the mechanism for limiting query results in Oracle is suboptimal.

Doctrine uses the following pseudo SQL statement to limit query results:

SELECT b.* FROM (
   SELECT a.*, ROWNUM AS doctrine_rownum FROM (
      $query
  ) a
) b
WHERE doctrine_rownum BETWEEN $min AND $max

It's much faster to use the following:

SELECT * FROM (
  SELECT a.*, rownum AS doctrine_rownum FROM {
    $query
  ) a WHERE rownum <= $max
) WHERE doctrine_rownum >= $min

The updated code in DBAL/Platforms/OraclePlatform.php would look like this:

public function modifyLimitQuery($query, $limit, $offset = null)
{
	$limit = (int) $limit;
	$offset = (int) $offset;
	if (preg_match('/^\s*SELECT/i', $query)) {
		if ( ! preg_match('/\sFROM\s/i', $query)) {
			$query .= " FROM dual";
		}
		if ($limit > 0) {
			$max = $offset + $limit;
			$column = '*';
			if ($offset > 0) {
				$min = $offset + 1;
				$query = 'SELECT * FROM (SELECT a.' . $column . ', rownum AS doctrine_rownum FROM (' . 
						 $query . 
						 ') a WHERE rownum <= ' . $max . ') WHERE doctrine_rownum >= ' . $min;
			} else {
				$query = 'SELECT a.'.$column.' FROM (' . $query .') a WHERE ROWNUM <= ' . $max;
			}
		}
	}
	return $query;
}

With the current implementation the request for fairly complicated query selecting data from multiple tables one of which reaches 2 million rows takes 5.98 seconds comparing to 1.64 with the proposed one.

In addition It would be nice to have the min and max values as SQL query parameters so that Oracle's internal caching mechanisms could take part, but I'm aware of the fact that it would not be so easy to implement.



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

Fixed





[DBAL-12] Add Doctrine\DBAL\SQLQuery package with simple Query Objects Created: 24/Apr/10  Updated: 15/May/11  Resolved: 15/May/11

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

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


 Description   

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:

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



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

Discussions on the API:

First observations/requirements:

  • Identifiers are NEVER quoted by the SQL Query Object
  • A query object cannot ever be as efficient as our Persisters building SQL in a custom way
  • A SQL Query instance is only an Object with an internal stack of SQL parts, it should not have any logic whatsoever
  • A SQL Query instance is a stack for bound params and values, i.e. it should work towards prepared statements and not inline and quote values (like Zend_Db_Select)

Questions:

  • Should we allow methods to accept either array or string? Or streamline API to allow only one? select(string) + selectAll(array)?

Binding values:

$sql->bindValue(1); // adds 1 to the bind stack and returns a ":doctrineValue$inc" string
$sql->bindParam($foo, PDO::PARAM_INT); // the same as before, with additional type binding

Building a Select clause A:

$sql->select(array('foo', 'bar', 'baz')); // SELECT foo, bar, baz
$sql->select(array('foo' => 'foo1', 'bar' => 'bar2', 'baz' => 'baz3')); // SELECT foo AS foo1, bar AS bar2, baz AS baz3

Building a Select clause B:

$sql->select('foo')->select('bar')->select('baz'); // SELECT foo, bar, baz
$sql->select('foo', 'f'); // SELECT foo AS f

Building the From clause A:

$sql->from('foo'); // FROM foo
$sql->from('foo', 'f'); // FROM foo f
$sql->from('foo', 'f', array('foo')); // SELECT foo FROM foo f

Building the From Clause B:

$sql->from('foo'); // FROM foo
$sql->from(array('foo')); // FROM foo
$sql->from(array('f' => 'foo')); // FROM foo f
$sql->from(array('f' => 'foo'), array('foo')); // SELECT foo FROM foo f

Building a Join clause (obviously depends on the From syntax also for a consistent approach):

public function join($table, $alias, $onClause, $cols);
public function join($table, $onClause, $cols);

Join Method Names: joinInner, joinLeft, joinRight (Zend) OR innerJoin, leftJoin, rightJoin (Zeta)?

Where Syntax:

This is pretty difficult and i prefer the approach from ORM\QueryBuilder:

$sql->where("foo = ?");
$sql->where("foo = ?", $foo); // allow this? its Zend_Db_Select syntax
$sql->where("foo = " . $sql->bindParam($foo));
$sql->where($sql->expr()->eq("foo", $sql->bindParam($foo));

For an OR on the main level:

$sql->where("foo = ?")->orWhere("bar = ?");

For a nested condition:

$sql->where("foo = ? OR bar = ? OR baz = ?");
$sql->where($sql->expr()->or(
    $sql->expr()->eq("foo", $sql->bindParam($foo)),
    $sql->expr()->eq("bar", $sql->bindParam($bar)),
    $sql->expr()->eq("baz", $sql->bindParam($baz)),
));

There is an Expression class that has control flow and sql functions, using a platform internally for vendor specific sql.

Execution or Preparing the SQL:

$stmt = $sql->prepare();
$sql->execute();

Comments?

Comment by Roman S. Borschel [ 14/Jun/10 ]

The usage of bindValue/bindParam is very confusing to me. It should be in-line with PDO where the only difference is whether the parameter is bound by value (the value of the variable gets bound at the point of the method call) or by reference (the value of the variable gets bound at the point the statement is executed).

Comment by Benjamin Eberlei [ 15/Jun/10 ]

How would that look like in your opinion? This is what Zend_Db_Select gets wrong...

Comment by Roman S. Borschel [ 15/Jun/10 ]

Well I would say either having bindValue/bindParam but with the same semantics as PDO or trashing both and just having setParameter, like we do in the ORM, that binds the value at the time of the invocation, intuitively.
I would go with the latter as I have yet to find a real use-case for bindParam (binding "by reference").

The only "wrong" direction here IMHO is to reuse PDOs names but give them a completely different meaning.

I would just go with setParameter($key, $value, [$type = PDO::PARAM_STR]).

I might be missing something though because I dont really understand the bindValue/bindParam examples given in your comments, i.e. I dont understand why it "generates" a key. Can you show a "real" example usage in a query?

Comment by Benjamin Eberlei [ 26/Jun/10 ]

But how would that bind stuff work with sub selects?

Say you do:

$sub = new SelectQuery();
$sub->select('a')->from('subtable')->where('b = ?')
$root = new SelectQuery();
$root->select('b')->from('root')->where('b IN (' . $sub . ' )');
Comment by Benjamin Eberlei [ 28/Dec/10 ]

Assigned to Guilherme for 2.1

Comment by Benjamin Eberlei [ 15/May/11 ]

Implemented





[DBAL-111] MySQL Driver possibly subject to sql injections with PDO::quote() Created: 18/Apr/11  Updated: 14/May/11  Resolved: 14/May/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.0.0-BETA2, 2.0.0-BETA3, 2.0.0-BETA4, 2.0.0-RC1-RC3, 2.0-RC4, 2.0-RC5, 2.0, 2.0.1, 2.0.2, 2.0.3, 2.0.4, 2.0.5, 2.1
Fix Version/s: 2.0.4, 2.0.5, 2.1

Type: Bug Priority: Critical
Reporter: Anthony Ferrara Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

MySQL Drivers



 Description   

Prior to 5.3.6, the MySQL PDO driver ignored the character set parameter to options. Due to MySQL's C api (and MySQLND), this is required for the proper function of mysql_real_escape_string() (the C API call). Since PDO uses the mres() C call for PDO::quote(), this means that the quoted string does not take into account the connection character set.

Starting with 5.3.6, that was fixed. So now if you pass the proper character set to PDO via driver options, sql injection is impossible while using the PDO::quote() api call.

PDO proof of concept
$dsn = 'mysql:dbname=INFORMATION_SCHEMA;host=127.0.0.1;charset=GBK;';
$pdo = new PDO($dsn, $user, $pass);
$pdo->exec('SET NAMES GBK');
$string = chr(0xbf) . chr(0x27) . ' OR 1 = 1; /*';
$sql = "SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME LIKE ".$pdo->quote($string)." LIMIT 1;";
$stmt = $pdo->query($sql);
var_dump($stmt->rowCount());

Expected Result: `int(0)`.
Actual Result: `int(1)`.

There are 2 issues to fix. First, the documentation does not indicate that you can pass the `charset` option to the MySQL Driver. This should be fixed so that users are given the proper option to set character sets.

Secondly, `Connection::setCharset()` should be modified for MySQL to throw an exception, since the character set is only safely setable using the DSN with PDO. This is a limitation of the driver and could be asked as a feature request for the PHP core. Either that, or a big warning should be put on the documentation of the API to indicate the unsafe character set change



 Comments   
Comment by Anthony Ferrara [ 19/Apr/11 ]

Note: issued same bug report for Doctrine1 as it's also affected: http://www.doctrine-project.org/jira/browse/DC-998

Comment by Anthony Ferrara [ 29/Apr/11 ]

Also note that prepared statements in PDO will suffer the same bug since PDO always emulates prepared statements for the mysql driver (even though it fully supports them in the source). See: http://bugs.php.net/bug.php?id=54638

Comment by Benjamin Eberlei [ 14/May/11 ]

Fixed, updated the docs





[DBAL-107] Graphviz Schema Visitor Created: 07/Apr/11  Updated: 07/Apr/11  Resolved: 07/Apr/11

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

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


 Description   

We should include a Graphviz Schema Visitor.

Features:

  • Display Tables and Columns
  • Highlight Primary Keys
  • Connect tables through foreign keys


 Comments   
Comment by Benjamin Eberlei [ 07/Apr/11 ]

Implemented





[DBAL-56] Ship a compatibility wrapper like MDB2 Created: 29/Sep/10  Updated: 05/Mar/11  Resolved: 05/Mar/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.0.0-BETA4
Fix Version/s: 2.1

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


 Description   

Database Portability is expensive but often necessary. We should support the important Compatibility Layer Options that MDB2 has:

http://pear.php.net/manual/en/package.database.mdb2.intro-portability.php

The following I think are doable:

  • Empty to Nulls
  • Change Case of Field-Assocs
  • Oracle RTRIM for Chars
  • Num Rows "hack" for Oracle
  • Delete Count (Some vendors rewrite a DELETE tablename into a TRUNCATE and dont report the delete count)

There should be a class extending "Doctrine\DBAL\Connection" making use of the 'wrapperClass' option. Two new classes are necessary:

Doctrine\DBAL\Portability\Connection
Doctrine\DBAL\Portability\Statement



 Comments   
Comment by Benjamin Eberlei [ 05/Mar/11 ]

Implemented.

To use define the following three parameters:

$params = array();
// ...
$params['wrapperClass'] = 'Doctrine\DBAL\Portability\Connection';
$params['portability'] = Doctrine\DBAL\Portability\Connection::PORTABILITY_ALL;
$params['fetch_case'] = \PDO::CASE_LOWER;
$conn = DriverManager::getConnection($params, $config, $evm);

This mode probably hits performance pretty hard,but it allows you to write code that works with all the quirks of fetching values from all database vendors.





[DBAL-78] Native support for parameter lists Created: 24/Dec/10  Updated: 05/Mar/11  Resolved: 05/Mar/11

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

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


 Description   

Prerequisites:

1. Detection of this mode has to be super-fast. It should not have an auto-detection but should be triggered explicitly.
2. This mode cannot be supported after $conn->prepare(). It is only viable for $conn->executeQuery() or $conn->executeUpdate().

Case 1: Positional

$stmt = $conn->executeQuery("SELECT a.id FROM articles a WHERE a.id IN (?) AND a.foo = ?",
    array($paramList, $foo), array( Connection::PARAM_ARRAY => PDO::PARAM_INT) , PDO:PARAM_STR ),
    Connection::FLAG_EXPAND_ARRAYS
);

Would internally, right before execution, be rewritten to:

$c = count($paramList); // 3!
"SELECT a.id FROM articles a WHERE a.id IN (?, ?, ?) AND a.foo = ?"

Case 2: Named

$stmt = $conn->executeQuery("SELECT a.id FROM articles a WHERE a.id IN (:id) AND a.foo = :bar",
    array('id' => $paramList, 'bar' => $foo),
    array( array(Connection::PARAM_ARRAY => PDO::PARAM_INT), PDO::PARAM_STR ),
    Connection::FLAG_EXPAND_ARRAYS
);

Would internally, right before execution, be rewritten to:

$c = count($paramList); // 3!
"SELECT a.id FROM articles a WHERE a.id IN (:id1, :id2, :id3) AND a.foo = :bar"


 Comments   
Comment by Benjamin Eberlei [ 05/Mar/11 ]

Implementing this for Named parameters is not really possible without re-parsing the query after every replacement just because the named parameters can occur more then once and this messes with the positions.

Comment by Benjamin Eberlei [ 05/Mar/11 ]

Using arrays as param list is a very bad idea performance wise (and also code handling).

Algorithm now:

1. New Constants Connection::PARAM_INT_ARRAY and PARAM_STR_ARRAY, being apart by Connection::ARRAY_TYPE_OFFSET from their PDO::PARAM_STR and PDO:PARAM_INT plain values.
2. Detect if there is one _ARRAY param. If not abort expansion (complexity O(T)) where T is the number of types.
3. Parse SQL statement for positional needle => character position
4. Iterate param list and inline arrays values

Comment by Benjamin Eberlei [ 05/Mar/11 ]

Implemented in https://github.com/doctrine/dbal/commit/df50f44a179028a3e30dcab02cd3ff33cc4f8498





[DBAL-45] Add CLI tool that checks for Reserved Keywords Created: 31/Aug/10  Updated: 27/Feb/11  Resolved: 27/Feb/11

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

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

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

 Description   

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



 Comments   
Comment by Benjamin Eberlei [ 27/Feb/11 ]

Implemented.





[DBAL-79] Add Schema\Table::hasPrimaryKey method Created: 30/Dec/10  Updated: 26/Feb/11  Resolved: 26/Feb/11

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

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


 Description   

Currently the name of the primary key is internal to the Table instance so there is no way to check if the table has a primary key without getting all the indexes and iterating over them.



 Comments   
Comment by Benjamin Eberlei [ 26/Feb/11 ]

This is fixed in trunk.





[DBAL-88] MySqlPlatform not escaping table names Created: 10/Feb/11  Updated: 26/Feb/11  Resolved: 26/Feb/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0
Fix Version/s: 2.0.2, 2.1

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

Attachments: File MySqlPlatform.php    
Issue Links:
Duplicate
duplicates DBAL-91 orm:convert-mapping SchemaException o... Resolved

 Description   

Methods in the MySql Platform that use the table name are not escaping the table name. This causes problems when your table name is the same as a reserved word in MySql. Example:

public function getListTableConstraintsSQL($table)

{ return 'SHOW INDEX FROM ' . $table; }

If my table name is User this method will fail and cause a SQL error because the word User is a reserved word in MySql. You need to escape the table name with backticks to prevent these errors. Example:

public function getListTableConstraintsSQL($table)

{ return 'SHOW INDEX FROM `' . $table . '`'; }

I encountered this problem while reverse engineering my existing MySql database. I've attached a fixed version that solved my reverse engineering problems.



 Comments   
Comment by Benjamin Eberlei [ 11/Feb/11 ]

Duplicate of DDC-976

Comment by Benjamin Eberlei [ 12/Feb/11 ]

This issue is not a duplicate, only related to DBAL-91.

Comment by Benjamin Eberlei [ 26/Feb/11 ]

Fixed





[DBAL-84] Long string fields are being silently changed to 255 characters in the schema Created: 31/Jan/11  Updated: 20/Feb/11  Resolved: 20/Feb/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0, 2.0.1
Fix Version/s: 2.0.2, 2.1

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


 Description   

I have an entity like this:

/**
 * @orm:Entity
 */
class Product
{
    /**
     * @orm:Id
     * @orm:Column(name="Merchant",type="string",length=50)
     */
    protected $merchant;

    /**
     * @orm:Column(name="Name",type="string",length=500)
     */
    protected $name;
}

When I change the length of $name field to 4000 it updates the schema like it is supposed to:

php.exe -f console doctrine:schema:update --dump-sql
ALTER TABLE product CHANGE Name Name VARCHAR(4000) NOT NULL

But when I change the length to 5000 it just silently without any warning uses value 255 instead:

php.exe -f console doctrine:schema:update --dump-sql
ALTER TABLE product CHANGE Name Name VARCHAR(255) NOT NULL

I had to look into actual table definition using mysql browser to find this issue because doctrine doesn't even warn about it. This could lead to some pretty serious bugs!



 Comments   
Comment by Benjamin Eberlei [ 20/Feb/11 ]

Fixed in master and 2.0.x





[DBAL-42] Use column comments for further Doctrine Type Inference Created: 12/Aug/10  Updated: 20/Feb/11  Resolved: 20/Feb/11

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

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

Issue Links:
Duplicate
is duplicated by DBAL-90 SchemaManager not able to reverse-eng... Resolved

 Description   

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.



 Comments   
Comment by Benjamin Eberlei [ 18/Feb/11 ]

MySQL, postfix column with "COMMENT 'The KEY obviously'"

Postgres, COMMENT syntax (another SQL statement, narf!): http://www.postgresql.org/docs/8.3/static/sql-comment.html

Oracle, COMMENT syntax (another SQL statement): http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_4009.htm

Sqlite: Nada

Mssql: (woah this is ugly) http://msdn.microsoft.com/en-us/library/ms180047.aspx

Comment by Benjamin Eberlei [ 20/Feb/11 ]

Implemented for Oracle, PostgreSQL and MySQL.

Now for Array and Object types a Column Comment is appended of the format "(DC2Type:$type)" which is parsed when reading from the database.





[DBAL-91] orm:convert-mapping SchemaException on Postgres reserved words Created: 10/Jan/11  Updated: 12/Feb/11  Resolved: 12/Feb/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0
Fix Version/s: 2.0.2, 2.1

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

CentOs 5.5, Postgres 8.1.22


Attachments: File test-creation.sql    
Issue Links:
Duplicate
is duplicated by DBAL-88 MySqlPlatform not escaping table names Resolved

 Description   

While execuiting

doctrine orm:convert-mapping --from-database yml /tmp/test.yml

I get presented an error

[Doctrine\DBAL\Schema\SchemaException]
Invalid index-name tablename_"input"_idx given, has to be [a-zA-Z0-9_]

The index "type" is quoted because this is a reserved word in Postgres. for example see the following definition:

FOREIGN KEY (something) REFERENCES somethings(id) ON UPDATE CASCADE
FOREIGN KEY (another) REFERENCES anothers(id) ON UPDATE CASCADE
FOREIGN KEY ("type") REFERENCES logtypes(id) ON UPDATE CASCADE

Only type will be quoted because it is a reserved word.



 Comments   
Comment by Benjamin Eberlei [ 10/Jan/11 ]

You have to quote with `` in Doctrine. That is translated to the underyling vendor quotation signs.

Comment by Emil [ 10/Jan/11 ]

@benjamin this considers a mapping error from database (Postgres) to YAML through the doctrine commandline tool, I do not think your comment is relevant for this issue.

Comment by Benjamin Eberlei [ 11/Jan/11 ]

@Emil it is, are you quoting the reserved word in the YML file using `` ? DBAL has some logic to fix escaping in these cases.

Comment by Emil [ 11/Jan/11 ]

@benjamin uhm, I might be baffled with the complexity of Doctrine or just plain wrong. But I'm trying to create a YML file from my database. How can I be making a mistake in the YML file with quotes?

The error message is presented because the Dcotrine ORM module tries to generate index names on columns, based on the database schema, but fails because the returned names (from the database schema) contain quotes (because they are reserved words).

Comment by Benjamin Eberlei [ 11/Jan/11 ]

Sorry, now i get it. I didnt relaize the usecase you were applying. Yes you are right

Comment by Benjamin Eberlei [ 23/Jan/11 ]

Can you give me an Example Postgresql Schema with DDL SQL that produces this error?

Comment by Emil [ 10/Feb/11 ]

These are the table creation for a test table which is able to reproduce the error.

Comment by Emil [ 10/Feb/11 ]

I hope this is enough information. The attached SQL reproduces the following error:

  1. doctrine orm:convert-mapping --from-database yml /tmp/test.yml

[Doctrine\DBAL\Schema\SchemaException]
Invalid index-name something_"input"_idx given, has to be [a-zA-Z0-9_]

orm:convert-mapping [--filter="..."] [--from-database] [-extend[="..."]] [-num-spaces[="..."]] [-h|--help] [-q|--quiet] [-v|--verbose] [-V|--version] [-a|--ansi] [-n|--no-interaction] command to-type dest-path

Comment by Benjamin Eberlei [ 12/Feb/11 ]

I can't reproduce it

Can you run the command with --verbose ? Additionally in lib/Doctrine/ORM/Tools/Console/Command/ConvertMappingCommand.php can you var_dump() $metadata after line 119?

Comment by Benjamin Eberlei [ 12/Feb/11 ]

Ok i could reproduce it now. Input is not a keyword for me though, type also not. Coudl reproduce it with "table"

Comment by Benjamin Eberlei [ 12/Feb/11 ]

Ok, this is a DBAL issue.

Comment by Benjamin Eberlei [ 12/Feb/11 ]

This is fixed and will be included in 2.0.2





[DBAL-80] Connection::_bindTypedValues() error when $types[0] is null Created: 01/Jan/11  Updated: 01/Jan/11  Resolved: 01/Jan/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0
Fix Version/s: 2.0.1, 2.1

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


 Description   

When $types[0] = null (default) then _bindTypedValues starts with the wrong offset and binds the wrong types against the wrong params.



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

Fixed





Generated at Thu Aug 28 05:18:49 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.