[DBAL-124] OCI8 Adapter (convertPositionalToNamedPlaceholders) Statemachine doesn't consider comments and strings Created: 20/May/11  Updated: 28/Jun/11

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

Type: Improvement Priority: Major
Reporter: Carsten Hetzel Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

SQL statements may contain question marks in strings, inline comments or comment blocks. The current implementation of the method "convertPositionalToNamedPlaceholders()" doesn't consider these implications and falsely replaces them by named bind variables.

Replacement code with example:

<?php
$s = '-- Testkomm?ntar
	select	/* ? *//* ??? */ ?||\'H"al?l"o?\' as "h?""llo" union /* "? Kommentar" \' */
	/* ?" */ select \'/*Hallo\'||to_char( ? ) union select \'--Welt\'
	union--
	select ?';

echo 'In: ' . $s . PHP_EOL;

$bind = 0;
$skip = array( '--' => PHP_EOL, '/*' => '*/', '"' => '"', "'" => "'" );
for( $i = 0; $i < strlen( $s ) /* size of string might change! */; /* yes, no increment here! */ )
{
	// Skipping comments and literals
	foreach( $skip as $begin => $end )
	{
		$matches = substr_compare( $s, $begin, $i, strlen( $begin ) );
		if( $matches !== false && $matches == 0 )
		{
			$pos = strpos( $s, $end, $i+strlen( $begin ) );
			// echo "Found $begin, skipping at $i to $end at $pos" . PHP_EOL;
			if( $pos === false )
			{
				// No more data or illegal statement - anyway: no more replacements!
				// echo "EOD" . PHP_EOL;
				break 2;
			}
			$i = $pos + strlen( $end );
			continue 2; // Ensure we match /*..*//*..*/, '''' or """" - that's why we don't ++$i in the for-loop!
		}
	}
	if( $s[$i] == "?" )
	{
		// Positional to named
		// echo "Replace $bind" . PHP_EOL;
		$r = ':name' . ++$bind;
		$s = substr_replace( $s, $r, $i, 1 );
		$i += strlen( $r );
	}
	++$i;
}

echo 'Out: ' . $s . PHP_EOL;


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

This algorithmus is painfully slow. There has to be something better, why do you need to foreach the loop inside the for?

Comment by Carsten Hetzel [ 28/Jun/11 ]

Ok, sent you a PM but I'll put my response here, too:

"Painfully slow" at what circumstances? Benchmarks?

This was meant as an example implementation to show the problems of the current implementation - there's always room for improvement.

And never forget: Premature optimization is the root of all evil! ;-p

You want speed - try this one:

<?php
$sql = '-- Testkomm?ntar
        select  /* ? *//* ??? */ ?||\'H"al?l"o?\' as "h?""llo" union /* "? Kommentar" \' */
        /* ?" */ select \'/*Hallo\'||to_char( ? ) union select \'--Welt\'
        union--
        select ?';

echo 'In: ' . $sql . PHP_EOL;

$map = array();
for( $cnt = 0; $cnt < 1000; ++$cnt )
{
        $bind = 0;
        $s = $sql;

        if( isset( $map[$s] ) )
                continue;

        $strlen = strlen( $s );
        for( $i = 0; $i < $strlen /* size of string might change! */; /* yes, no increment here! */ )
        {
                $c1 = $s[$i];
                $c2 = isset( $s[$i+1] ) ? $s[$i+1] : '';
                if( $c1 == '-' && $c2 == '-')
                {
                        $pos = strpos( $s, PHP_EOL, $i+2 );
                        if( $pos === false )
                        {
                                break 2;
                        }
                        $i = $pos + 2;
                }
                elseif( $c1 == '/' && $c2 == '*' )
                {
                        $pos = strpos( $s, '*/', $i+2 );
                        if( $pos === false )
                        {
                                break 2;
                        }
                        $i = $pos + 2;
                }
                elseif( $c1 == '"' )
                {
                        $pos = strpos( $s, '"', $i+1 );
                        if( $pos === false )
                        {
                                break 2;
                        }
                        $i = $pos + 1;
                }
                elseif( $c1 == "'" )
                {
                        $pos = strpos( $s, "'", $i+1 );
                        if( $pos === false )
                        {
                                break 2;
                        }
                        $i = $pos + 1;
                }
                elseif( $c1 == "?" )
                {
                        // Positional to named
                        // echo "Replace $bind" . PHP_EOL;
                        $r = ':name' . ++$bind;
                        $s = substr_replace( $s, $r, $i, 1 );
                        $rLen = strlen( $r );
                        $i += $rLen;
                        $strlen += $rLen - 1;
                }
                else
                        ++$i;
        }
        $map[$sql] = $s;
}
echo 'Out: ' . $map[$sql] . PHP_EOL;

ATTENTION: Each "break 2;" means, you have an invalid statement! Do, whatever the statement class is supposed to do in this case!

Regards, Carsten





[DBAL-131] Remove Static Types Created: 19/Jun/11  Updated: 04/Dec/14

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

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


 Description   

the static types should be removed and made to instances of types that can differ between platforms.

Following reasons:

1. Some vendors already convert values to PHP types, for example integers. Not converting again could save about 15% wall-time for integers for example.
2. Some vendors require different type code, this is currently handled by expensive if checks.
3. If you want to change a type for one vendor but also use another vendor in the same request, then you have a problem currently.
4. If types were platform aware then we could make registering custom types simpler.






[DBAL-125] Add Informix Support Created: 08/Jun/11  Updated: 11/Jun/11

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

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

Linux, Unix & Windows



 Description   

Today exist many customers with Informix 11.50 and they will deveoplment with php+simphony, but doctrine will be an alternative inside the framework but it isn't supported.
My customers can't migration ( they are happy with informix) your application and he need a framework as simphony and an abstration of your environment as Doctrine.
There is a possibility to support Informix in Doctrine?
Thanks and regards

Adolfo Flores
Consultant



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

There is a possibility to support Informix, this is described here:

http://www.doctrine-project.org/docs/dbal/2.0/en/reference/supporting-other-databases.html

I don't know informix and never worked with it, if you would volunteer to code this i would be enternelly grateful I could help you with the details of implementation.

I think a working prototype can be implemented in 4-6h of time.

Comment by Adolfo Flores [ 08/Jun/11 ]

good idea, I am an expert in informix database and I may need support in doctrine.

Is there any template to follow? I have read the required structure in the url given, it seems feasible.

What next step?

I am installing Informix, php 5.3 on RHES 5.2 All 32-bit

Regards,

Adolfo

Note: .I have no time basis, so that one week would be a reasonable time for this activity

Comment by Benjamin Eberlei [ 11/Jun/11 ]

I added some more details to the chapter, here is the part:

Implementation Steps in Detail
------------------------------

1. Add your driver shortcut to class-name `Doctrine\DBAL\DriverManager`.
2. Make a copy of tests/dbproperties.xml.dev and adjust the values to your driver shortcut and testdatabase.
3. Create three new classes implementing ``\Doctrine\DBAL\Driver\Driver``, ``\Doctrine\DBAL\Driver\Statement``
and ``Doctrine\DBAL\Driver``. You can take a look at the ``Doctrine\DBAL\Driver\OCI8`` driver.
4. You can run the testsuite of your new database driver by calling "cd tests/ && phpunit -c myconfig.xml Doctrine/Tess/AllTests.php"
5. Start implementing AbstractPlatform and AbstractSchemaManager. Other implementations should serve as good example.

Essentially Driver, Statement are the PDO equivalents of "PDO" and "PDOStatement", they even have the exact same API. OCI8 is a good example to see how it works. If Informix works with PDO then its even simpler, take a look at Doctrine\DBAL\Driver\PDO namespace, you don't need to implement the connection and statement then, just the driver taking care of the configuraiton.

The most complicated thing is to get the Platform and SchemaManager to work. Both are responsible for SQL generation for DDL statements for example. It makes sense to run the phpunit testsuite and fix the bugs when occuring until all tests pass.

Also looking at the platform and schema managers of other databases is a very good help





[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-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-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-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-933] Get Statement Column Metadata Created: 05/Jul/14  Updated: 06/Jul/14

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

Type: New Feature Priority: Trivial
Reporter: Benoît Burnichon Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

It would be nice to have a utility method in \Doctrine\Dbal\Driver\ResultStatement to properly retrieve the result column names.

Currently, we have to rely on somthing like
$columnNames = array_keys($statement->fetch(\PDO::FETCH_ASSOC));

Problem: It does not work with empty result-sets, and more checks should be performed to handle these.

With PDO, http://www.php.net/manual/en/pdostatement.getcolumnmeta.php could be used to properly retrieve names.

For Sqlite3 it is easy, http://www.php.net/manual/en/sqlite3result.columnname.php

For Mysql, http://www.php.net/manual/en/mysqli-result.fetch-fields.php would do the trick






Generated at Wed Sep 02 22:32:33 EDT 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.