Zend\Db\Sql\Sql and schema with .dbo

[werc]

On sql queries connecting to another database is valid with query like this:

... FROM [db2].[dbo].[archive_sap] ....

When I provide schema with [], left part is "removed":

$select->from('db2].[dbo].[archive_sap') -> FROM [db2].\dbo].\archive_sap] $select->from('db2.dbo.archive_sap') -> FROM [db2.dbo.archive_sap] ... not correct

The ON part in second case is wrapped correctly .... ON [db2].[dbo].[archive_sap].[id] = ....

[alextech]

Thank you for report! At the moment cross schema or database joins are not supported. It is annoying, I agree. For now, maybe try Zend\Db\Sql\Expression which allows arbitrary SQL or consider creating a view that queries the other database so Zend would have indirect access to it.

The way identifier quoting currently works is through strong assumption that you are within the constraint of the schema specified in the adapter. Whatever is passed into from(), or anything else accepting an identifier, is treated as name of an object directly under that schema. User should not attempt to manually escape identifier, as it will cause double escaping. There is currently no way to distinguish between parameter value that is name of a database object and parameter that is path towards an object with escapes/quotes/brackets. Trying to parse this based on existence of separator character would be unreliable, since there are legitimate cases of having dot . as part of a table name and coming up with regular expressions that look out for a dot between closing and opening identifier quotes, as opposed to within quotes (brackets) is not worth the risk. Instead, in places where schema name is allowed to be part of identifier, parameter can be array of values composing the path toward the object, or an instance of TableIdentifier. In this case use of array, will too, be inappropriate because here, arrays are used to describe table alias. Last remaining option is TableIdentifier, which from() can accept, with none of the ambiguity associated with using a string, but at this time only supports name of schema, not name of database, which is left up to adapter to manage.

I ran into this some years ago, was very annoying. I might evaluate adding database as third parameter to TableIdentifier which will then take advantage of quoteIdentifierChain which I know correctly assembles path towards an object. But I am still working on Sequence fixes, plus there are bugs in several places which ignore array or TableIdentifier for schema name, even where it is supposedly supported, which I need to sweep through before this has a chance of working.

[werc]

I had the same results using TableIdentifier, but I thing TableIdentifier would be the way where to adapt this situation for SqlServer platform.

Anyway, I used two ways for quering to another database, with current Sql:

1. When paginator is not required I use

$sqlString = $sql->buildSqlString($select);
$sqlString = str_replace('[db2]', '[db2].[dbo]', $sqlString);

2. When paginator is required the DbSelect accepts Select object. I moved querinq into database view, You suggested this solution too.

[alextech]

How did you get same results with TableIdentifier? I did $select->from(new TableIdentifier('users', 'db1'));

which correctly produced SELECT [db1].[users].* FROM [db1].[users]

MSSQL seems to be the only one supporting this, so maybe I can think of a way to decorate it without affecting abstraction for other engines.

[werc]

I tried this (quering to Microsoft Sql Server):

required: SELECT [db2].[dbo].[archive_sap].*

but:
$tableOne = new TableIdentifier('dbo.archive_sap', 'db2');
$sql = new Sql($this->adapter, $tableOne);
.....
produce -> SELECT [db2].[dbo.archive_sap].*, ..... 

$tableOne = new TableIdentifier('dbo].[archive_sap', 'db2');
produce-> SELECT [db2].[dbo].\archive_sap].*, ..... 

And again .. the joining part ON of sql is wrapped correctly:

 ... ON [db2].[dbo].[archive_sap].[id] = .......

[alextech]

right, all classes share same quoting function so it would do that. Wonder if there is a way to decorate TableIdentifier at parse time specifically for SQLSRV platform to split that into identifierChain instead of identifier. There is a PR by @turrsis that does a better job translating classes to SQL code, maybe he has an idea?

[turrsis]

@werc are you need query across several databases?

[alextech]

While no answer from OP I will quickly jump in to give context. There are different cultures in table organizations between database vendors.

• MySql devs separate applications by creating new databases. Categorize tables with prefixes.
• PostgreSQL devs separate applications by creating new databases. Categorize tables through schemas (hence influx of bug reports about that recently)
• SQL Server takes it a step further. Applications are separated by installing a hole new instance of the server under different IP port and name instance. Categorize tables by creating new databases. So if you have many customer related tables, then many document related tables, SQL Server folks will often put those in separate databases and the SQL query syntax will join them as if they were one database, and on same adapter connection so its not even hack where one would set up separate connections.

[michalbundyra]

This repository has been closed and moved to laminas/laminas-db; a new issue has been opened at https://github.com/laminas/laminas-db/issues/85.