[DBAL-1291] In PostgreSqlSchemaManager::_getPortableTableColumnDefinition Regex for nextval/sequence does not match Created: 03/Sep/15  Updated: 03/Sep/15

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

Type: Bug Priority: Minor
Reporter: Jason B Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: postgresql
Environment:

PostgreSQL 9.2.13



 Description   

I ran into a schema that was showing the default value for a nextval as:

  "default" => "nextval(('tablename_id_seq'::text)::regclass)"
           Column           |       Type       |                     Modifiers
----------------------------+------------------+----------------------------------------------------
 id                         | bigint           | default nextval(('tablename_id_seq'::text)::regclass)

There seems to be an extra set of parentheses. I'm not sure how or why they're showing the extra set of parentheses. As far as I can tell the table column was created with BIGSERIAL. This table/schema could have originally been created in an older version of PostgreSQL.

I am suggesting we loosen up the regex to detect sequences/autoincrement

From:

        if (preg_match("/^nextval\('(.*)'(::.*)?\)$/", $tableColumn['default'], $matches)) {

To:

        if (preg_match("/^nextval\({1,2}'(.*)'(::.*)?\)$/", $tableColumn['default'], $matches)) {

This will match 1 or 2 open parentheses.






[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-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-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-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-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-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-812] SchemaTool ignores multi-columns (composite) indexes when creating a foreign key Created: 13/Feb/14  Updated: 15/Feb/14

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

Type: Improvement Priority: Minor
Reporter: Peter Huynh Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: schematool
Environment:

MySQL



 Description   

When using the schema tool to generate the differences between the db and the entities, I notice the following:

ALTER TABLE sales ADD CONSTRAINT FK_36D222EF603EE73 FOREIGN KEY (vid) REFERENCES vendor (id);
CREATE INDEX IDX_36D222EF603EE73 ON sales (vid);

Normally, it doesn't bother me, however I have had an existing composite index consists of (vid, submit). This leads to the redundant index IDX_36D222EF603EE73 and therefore forcing unnecessary overheads.

It would be nice to have a mean to disable this from happening.

The code in question can be found at https://github.com/doctrine/dbal/blob/594e326bd58d1d7af578f0dc3143655b9d119d45/lib/Doctrine/DBAL/Schema/Table.php#L543.

A helpful member from #doctrine IRC also pointed out that it does not check the unique constraints also.

Regards,



 Comments   
Comment by Marco Pivetta [ 13/Feb/14 ]

Just a hint on this: looks like the current logic just checks hashed indexes, ignoring the "uniq" prefixed indexes to avoid duplicates.

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

Peter Huynh I get the point here. However after having a quick look into this it seems this behaviour exists for a good reason. See here: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/Index.php#L191-L195
The question is how could this reasonable extended to fulfil the needs you explained. I am not entirely sure about this and this peace of code is rather sensitive and critical. I don't want to rush any assumptions here.

Comment by Peter Huynh [ 15/Feb/14 ]

Hi Steve,

I fully understand your reservation.

Thanks for looking into things.

Peter





[DBAL-761] Driver\ResultStatement::fetchAll() returns empty array on a seemingly valid Driver\PDOStatement object Created: 03/Jan/14  Updated: 08/Jan/14

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

Type: Improvement Priority: Minor
Reporter: Dennis Matveyev Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mysql, orm, query, schematool
Environment:

Windows 7 64 bit, Zend Server/Studio, PHP 5.4.16,
Server: Apache/2.2.22 (Win32) mod_ssl/2.2.22 OpenSSL/0.9.8x



 Description   

I came across a weird issue, where when running:

vendor/bin/doctrine-module orm:schema-tool:update

I would get:

[Doctrine\DBAL\Schema\SchemaException]
There is no column with name 'resource_id' on table 'role_resource'.

But I did have a column with the above name in the above table, so that was a weird message for me. So I traced it all the way to this line of code:

https://github.com/doctrine/dbal/blob/2.4/lib/Doctrine/DBAL/Connection.php#L630

If I remove "->fetchAll()" from that line, I get this object:

object(Doctrine\DBAL\Driver\PDOStatement)#531 (1) {
["queryString"]=> string(332) "SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type, IS_NULLABLE AS `Null`, COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS Extra, COLUMN_COMMENT AS Comment, CHARACTER_SET_NAME AS CharacterSet, COLLATION_NAME AS CollactionName FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'loginauth' AND TABLE_NAME = 'role_resource'"
}

Which has a valid SQL query that runs fine and shows Field names on my local machine's MySQL prompt. But when I add ->fetchAll() back in that line of code, an empty array is returned, field names are not returned, and a SchemaException is generated. I found this odd and wanted to report it. Whether it is a bug or not, hopefully I can find the cause of this issue.

For loads more info, please see this: http://stackoverflow.com/questions/20907491/doctrine-2-there-is-no-column-with-name-columnname-on-table-table



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

Dennis Matveyev It seems you are using MySQL. Can you please tell which version you use?

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

Dennis Matveyev Please also provide whether you use mysqli or PDO_MYSQL.

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

Dennis Matveyev Sounds dumb, but also are you sure that the vendor/bin/doctrine-module orm:schema-tool:update uses the correct connection parameters and does not by error connect to another database server or something? Because otherwise everything looks good IMO.

Comment by Dennis Matveyev [ 07/Jan/14 ]

Yes, I am using:
Server version: 5.5.23-log MySQL Community Server (GPL)
mysql client Ver 14.14 Distrib 5.5.23, for Win32 (x86)

Doctrine connection string for 'driverClass' is 'Doctrine\DBAL\Driver\PDOMySql\Driver', so it is PDO_MYSQL.

I am connecting to the right database, but your last suggestion is what helped to uncover the issue. When connecting to the database with the very same user/pass, and running the command, here is what I saw:

mysql> select resource_id from role_resource;
ERROR 1142 (42000): SELECT command denied to user 'login'@'localhost' for table 'role_resource'

Running a GRANT command to allow SELECT for this user solved the problem.

To improve debugging of similar issues, I'd see if there is a way to propagate the error from MySQL server to ./doctrine-module,
or
improve the error message of SchemaException, to i.e. "There is no column with name 'action_id' on table 'role_action', or database permissions prevent table access."

Thanks!

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

Dennis Matveyev Thank you for reporting this and I'm glad I could help. I will mark this as improvement, though. You are right in that the root cause of the error should be propagated to the user instead.

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

Dennis Matveyev Okay I found the root cause of the problem. See what MySQL states about access to the information_schema table for retrieving metadata about a certain database:

Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges will see NULL.

So it returns NULL instead of raising an error and this is why Doctrine is not able to propagate the proper exception in this case. I'm not sure if changing the exception message in the SchemaException class is the proper way of handling this. Also please note that this is not only about columns (where you got stuck at) but can occurr for almost every list*() action called in the SchemaManager I suppose. Furthermore this might be a MySQL specific issue, not sure about that.

The proper solution would be to throw an exception much earlier, to have a decent behaviour in such an edge case. I think we should throw an exception here: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php#L267 because a table cannot live without columns and indicates an error somewhere. But I would like to hear the opinion of Benjamin Eberlei on that issue.

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

I checked the SQL Server documentation and it seems they behave exactly the same: http://technet.microsoft.com/en-us/library/ms187113.aspx

In SQL Server 2005 and later, the visibility of metadata is limited to securables that a user either owns or on which the user has been granted some permission. For example, the following query returns a row if the user has been granted a permission such as SELECT or INSERT on the table myTable.

However, if the user does not have any permission on myTable, the query returns an empty result set.





[DBAL-319] Doctrine\DBAL\Types\Type Created: 12/Aug/12  Updated: 22/Dec/13

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

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


 Description   

The API could be improved in the next major release:

  • Type::add() instead of addType()
  • Type::isRegistered() instead of hasType() (has sounds weird)

Etc.. I think the 'type' in the methods is redundant since Type is already the object I am dealing with.

I'd also like a remove() method to unregister a type at runtime. Would make testing a little easier and I don't have to check with hasType() etc..



 Comments   
Comment by Marco Pivetta [ 12/Aug/12 ]

I don't think those namings are really important.
There's one major change to do, which is to somehow get rid of the staticness of the `Doctrine\DBAL\Types\Type` class, and it is not a simple task.
It would also be interesting to set the type objects manually, such as a database `password` type that does encryption/decryption based on a given service. That cannot be done without staticness right now, which renders two different connections requiring the same functionality but with different parameters very difficult.

Comment by Till [ 12/Aug/12 ]

I agree on the static. But I'd also like the API to be cleaned up and the remove method.

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

API cleanup and staticness can be addressed in 3.0 for the first time. Otherwise we cannot keep BC. You can use Doctrine\DBAL\Types\Type::overrideType('someType', null); as a workaround for removing a type from the registry.





[DBAL-175] Table comments in Doctrine\DBAL\Schema\Table Object Created: 06/Oct/11  Updated: 17/Oct/11

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

Type: New Feature Priority: Minor
Reporter: Asmir Mustafic Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: None


 Description   

Should be useful discover the table comments from database schema.

This feature is already available for column comments, but not for table comments






[DBAL-150] noSQL Shema Management Created: 21/Aug/11  Updated: 21/Aug/11

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

Type: New Feature Priority: Minor
Reporter: Alexey Shatunov Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

all



 Description   

I have a few ideas to improve DBAL and ORM management for php:
1. Reading if needed ORM schemas definitions from YAML(existing plugin)
2. Then migration it into the one of NoSQL databases(such as MongoDB or Memcache/MemcacheDB)
3. Creating transactional temporary-changes data layer in NoSQL for a current-changes due the webapp launch and running for each run
4. Periodically migrations of a current changes with migration script into RDB(for example by cron)

So we get:
Fast automatic shemas management(Symphony users - I say you "haha" because you have to do some unuseful things like "doctrine:build-schema" etc)
All advantages of RDB (like JOINS) in a data extraction
Full-independence in data changing due to webapp launch(and no transactions are needed) in the current NoSQL data-layer
Low load to the server because after extration we have a lazy return data into the RDB
In feature a simple caching any fast-changed data into NoSQL

What do you thinking about it?






Generated at Fri Sep 04 23:51:55 EDT 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.