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

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

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

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

FYI: http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

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

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

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

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

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

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

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

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

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

Yes, this ticket has been superseded by ticket 330.

Insert is not supported by DQL

Well, that was quick and not helpful. I have read about the QueryBuilder in the ORM package. For some reason with persistence (that other libraries don't have), insert cannot be supported. However, I'm talking about DBAL here. What good reason is there not to support INSERT??

see previous comment, I'd appreciate some clarification

Tim Mundt Ouch, no, it was my fault, sorry.
I confused the project related to the issue.

This is actually valid (even the patch, though it needs to adds tests)

Glad to hear there seems to be no fundamental problem with this. Can I somehow help this patch go into the code? I'm not familiar with the tests here. If you give me some pointer, maybe I can come up with something useful. On the other hand, it could be a good idea for some more involved people to have a look at this before.

You'd need to add tests in https://github.com/doctrine/dbal/blob/master/tests/Doctrine/Tests/DBAL/Query/QueryBuilderTest.php (to be included in your patch or in a github pull request)
A Github PR is also the fastest way to get your code reviewed since not everyone visits the issue tracker.

Here's the PR: https://github.com/doctrine/dbal/pull/184

From the first glance: Drupal API has some problems in that it assumes literal values are the default, which makes working with them simple if no expression is necessary. But inconsistent otherwise.

Implementation Details:

• Difference compared to QueryBuilder is that these objects are no builders, but actually executors.
• Don't assume Literals
• Creation is delegated to Platform (Runtime API of a Vendor)

Sample API:

$conn->createInsertQuery($tbl)->fields(array('foo', 'bar'))->values(array('?', '?'))->(array(1, 2))->execute();
$conn->createInsertQuery($tbl)->fields(array('foo', 'bar'))->params(array(1, 2))->execute(); // values(?, ?) is implicit.
$conn->createInsertQuery($tbl)->fields(array('foo', 'bar'))->params(array('NOW()', '1'))->execute(); // if no "params" set assume execute once.
$conn->createInsertQuery($tbl)->fields(array('foo', 'bar'))->value('foo', 'NOW())->params(array(1))->params(array(2))->execute();
$conn->createInsertQuery($tbl)->fields(array('foo', 'bar'))->select($queryBuilder)->execute();

Merge: I dont know yet:

problem i see here is that people mistake values() for a "safe" method and pass values in there that should be quoted instead.

Fedora, RHEL

i have created some hackish fork and the whole testsuite is working already:

https://github.com/m0ppers/dbal

Still WIP but may be a start. I think the C extension is not really ready yet either. When i find time i will most likely have a look at it.

Can you branch it into something, like git checkout -bDrizzle then push it to your repo and open a Pull Request? Thats way easier to review and discuss.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Jan

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

for example:

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

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

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

I guess we can agree to disagree then

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Reserved words checking sounds to be a fair compromise!

Hello, thank you all for your responses.

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

I would like to state an agreement with the OP.

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

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

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

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

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

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

That's the main point for me.

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

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

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

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

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

Scheduled for Beta4

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

doctrine-dbal/2.1.6, driver PDOMySql

Sounds like a rather useful addition to me, would be cool to see this as a default feature.

We used this at work, it's simple, it could need more reflection

The problem with a generic solution here are the risks involved, we need to answer questions:

1. did a transaction get aborted beccause of this
2. is it safe to continue at this point with a second, new connection

I am not sure we can guarantee the 100% working.

@Julien:
I assume you are referring to the elaborated switch-statement? Well, since this is a DBAL I think data-integrity and performance are the more important things. In my experience when performance is important, you avoid reflection in PHP as much as possible. In the light of performance the switch-statement was a very deliberate substitution of a call to call_user_func_array. The number of arguments was finite and I am fairly sure all possible cases where covered.

@Benjamin:
It seems I forgot to mention this in my statement above. Transactions are indeed an issue I deliberately avoided in this early version. As far as I know, transactions in MySQL are connection-based so the transaction will be rolled back when the server disconnects. A solution would be to incorporate a cache of all statements in a transaction. I was waiting on feedback of the Doctrine-developers on incorporating this code into the DBAL itself, before extending it with more functionality... and I now notice that it got assigned

I'll try to answer your two questions:
1. If the connection is dropped when inside a transaction, the behaviour will revert to the normal Doctrine-behaviour, i.e. no reconnection-attempt will be made and the exception 'MySQL server has gone away' will be thrown.
2. To be clear, I always try to write bug-free code, but I do not guarantee anything about this code. To answer your question: yes, it is safe to continue if you try-catch your transaction in your application-code. Upon the exception you reconnect to MySQL and repeat the lost transaction.

And as a last note, this code is just to have a workable solution. I tend to agree with anyone who thinks that the correct place to fix this problem is in the MySQL-client itself.

Dieter

My idea would be to throw an exception on reconnect like it is done atm, when transactionNestingLevel > 0, and otherwise proceed with doing the reconnect. I am not sure i am missing something here though.

@Benjamin: I noticed after commenting that you're the assignee ... and corrected my comment a bit.

Now, if you want I can extend the functionality to support transactions. But I prefer to do this directly into the DBAL, not as a layer on top. The resulting code should be a bit cleaner than this layer now.

What do you think?

*edit*
A bit of crossposting here

The way you suggest is the way this layer is implemented
*edit*

Dieter

@Benjamin
It's been a while since I wrote the code. Above answers are from memory. I will revise the code this evening and give you exact answers. Also, related to transactions, I think the way I handle the savepoints should be revised.

@Benjamin:
Sorry for the spam...

I couldn't help myself doing a quick verification. The answer to your question lies in the file Connection.php. The method DoP\DoPBundle\Doctrine\DBAL\Connection::validateReconnectAttempt also checks that the transactionNestingLevel < 1, so the method will always return false when in a transaction. I.o.w. when in a transaction no attempt to reconnect will be made and the exception is simply rethrown, as per the default Doctrine behaviour.

I could really use this for kong running cronjobs and daemonized scripts. Is this being worked on right now?

Peter, you can use the above code, but it only works for statements outside of transactions. Also, the calls for savepoints in transactions need correction. Will do that when I find the time.

Symfony 2.1, SQL Server 2008, driver: pdo_sqlsrv

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

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

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

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

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

This also applies for a type of yaml.
Example:

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

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

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

PHP 5.4.15-pl0-gentoo
doctrine/annotations v1.1.1
doctrine/cache v1.0
doctrine/collections v1.1
doctrine/common 2.4.0-RC2
doctrine/dbal 2.4.0-BETA2
doctrine/doctrine-bundle v1.2.0
doctrine/inflector v1.0
doctrine/lexer v1.0
doctrine/orm 2.4.0-BETA2

Hello, I was recently looking if Doctrene supports CUBRID and found this issue. CUBRID PDO Driver is production-ready now, being used in Yii PHP Framework, for instance.

I am CUBRID Project Manager and would like to know if you plan to elevate this issue up so that we could use Doctrene, too. We also plan to include Doctrene into CUBRID Projects site after it gets official support.

If you need any assistance, let me know. I will be glad to assist you.

all

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.

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

Already fixed in master

A proposed fix is available with the following pull request: https://github.com/doctrine/dbal/pull/240

I would strongly suggest to leave the behaviour as-is, and fix the documentation, because of all the trouble associated with floating point and rounding. People use the DECIMAL type to prevent those issues, so having the ORM convert it to floating point again would be pretty bad.

I have submitted a pull request on this issue on github. Hopefully the doc will be updated soon so other people don't expect the wrong behavior!

https://github.com/doctrine/orm-documentation/pull/93

Mods, this issue can probably be closed.

Leaving decimal values as strings creates another issue with unnecessary entity updates because old and new same values have different types: old value is always the string type, the new one - decimal. If an old value is '10.00' as a string and the new value is 10 decimal than Doctrine will issue the UPDATE statement for that entity. This is plainly wrong IMHO.

There may be other issues around comparison. I'd rather be comparing numeric types than strings when comparing "decimal" values.

IIS 7.5, MS-SQL-Server 2005, PHP 5.4.0

Are you sure you were using the 2.2.2 version of the ORM? Can you try to reproduce this with the latest master?

Linux (ubuntu), PHP 5.3.10, MySQL 5.5.29, Symfony2

Resolved by pull request 236:

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