Syntax rules for ambiguous name resolution

[asfernandes]

Name resolution (FB 6.0)

With the introduction of schemas in Firebird 6.0, the syntax <name>.<name> - used for tables, views, procedures,
and functions (both standalone and packaged) - introduces ambiguity when resolving object names using the schema
search path. The ambiguity arises between:

• <schema>.<object> (a schema and its object)
• <package>.<object> (a package and its object)

This document focuses on name resolution rules for tables, views, procedures, and functions within queries and
code blocks.

Scope specifier (%)

To resolve these ambiguities, Firebird introduces a scope specifier, represented by the % symbol. This
allows unambiguous referencing of objects.

Syntax

<name> % { SCHEMA | PACKAGE } . <name>

Examples

select *
    from plg$profiler%schema.plg$prof_sessions;

execute procedure rdb$profiler%package.pause_session;

call rdb$profiler%package.pause_session();

select rdb$time_zone_util%package.database_version()
    from system%schema.rdb$database;

select *
    from rdb$time_zone_util%package.transitions('America/Sao_Paulo', timestamp '2017-01-01', timestamp '2019-01-01');

Detailed name resolution rules

Firebird resolves object names following a structured sequence of rules. Once an object is located, the resolution
process halts, ensuring no ambiguity errors occur.

• name1.name2.name3

  1. Look for routine name3 inside package name2, inside schema name1.

• name1%schema.name2

  1. Look for object name2 inside schema name1.

• name1%package.name2

  1. Look for object name2 inside a package name1 using the schema search path.

• name1.name2

  1. If inside a package named name1, look for routine name2 in the same package.
  2. Look in schema name1 for object name2.
  3. Look for object name2 inside a package name1 using the schema search path.

• name

  1. Look for subroutine name.
  2. If inside a package, look for routine name in the same package.
  3. Look for object name using the schema search path.

Note: Object resolution also depends on the context in which they are used. For example, in
select * from name1.name2, name2 could be a table, view, or procedure. However, in
execute procedure name1.name2, name2 must be a procedure. This distinction means that an execute procedure
command versus a select command can resolve to different objects.

[aafemt]

Packages cannot contain tables and view, what's the point to include them into the specifications?

[asfernandes]

Packages cannot contain tables and view, what's the point to include them into the specifications?

Obviously they cannot be inside packages, but they participate in rules in the possible cases.

[sim1984]

If constants are introduced at the package level, this can be expanded. That is, I propose to make the syntax more general. In this case, there is a conflict between the package name and the table/view name.

name1.name2

name1 - package, name2 -const or name1 - table, name2- column

Name resolution:

name1%package.name2

or

name1%table.name2

[asfernandes]

Please specify full example where you see the ambiguity, i.e., query and its metadata (or some comments making it clear).

[DaltonCalford]

I believe when deciding upon how to handle schema vs package naming, we must first answer what we are using schema for?

For most developers, we either need to emulate existing data structures for porting from one sql engine to another, or we need clean name spaces for organizing our systems.

Some databases have limited depth in their schema ie mssql or postgress where you have one level of schema under the database, while others have a recursive structure where you have multiple levels of schema depth.

If you have schema, especially with a recursive structure, you need certain session/user parameters, very much like a directory structure on a file system. Examples include "current schema", "search path", "home schema" etc.,

An example of this, lets say we are trying to emulate a MSSQL configuration
MSSQL has "instances" - from a firebird pov, this is a firebird server sitting on a specified port OR a firebird database
MSSQL has "databases" - from a firebird pov, these can be root level schema for the database
MSSQL has "schema" - single level depth - from a firebird pov, these can be a sub-schema off of the parent schema.

This would allow Firebird to emulate many of the existing structures in a MSSQL installation.

For another example, Oracle, with multi level schema, and default schema paths, allows for a classroom to have a series of sub-schema for students. Each student has their own home schema for the class, where they create their own tables, but, can select from tables, not in their personal schema, but which exist in the classroom shared schema, without specifying the full path to the table ie select * from common_table would work, even though the common_table is in the shared schema, and not in the local schema. Doing a select from a table in the local (users current) schema, does not need the schema specified.

In the case of name conflicts, the first matching name in the search path is the one chosen. This is why keeping the search path and using synonyms inside the search structure removes ambiguity (full path is always better, but, in case of the classroom example, that is how it was setup at a college)

If a default schema (ie like MSSQL [Database].[...].[Table] syntax, the [...] would be replaced with the users default schema.

So, packages, for the purpose of name collision, can not be in a schema path beside a sub-schema with the same name.

So [root].[schema1].[schema2].[schema3]..... works
also [root].[schema1].[schema2].[schema3].[packagename].[FunctionName] works
but you can not have a schema and package with the same name at the same level.

Once this type of structure is implemented, packages become less important.

Having package/name conflicts, along with a recursive schema structure would allow for emulating anyone of the existing sql engines, while giving flexibility beyond most engines in regards to organizational structures and name spaces.

Just a suggestion

[dyemanov]

Is it really better than just name1.name2 = package.name, name1..name2 = schema.name? I think cross-schema routine calls would be less frequent than packaged routine calls and local calls do not need schema prefix at all. Or do you want to avoid the confusion/inconsistency between schema.name for tables and schema..name for routines?

[sim1984]

Please specify full example where you see the ambiguity, i.e., query and its metadata (or some comments making it clear).

Easy as pie.

CREATE PACKAGE SOME
BEGIN
  CONST A INTEGER = 1;
END

CREATE TABLE SOME (
  A INTEGER,
  B INTEGER
);

CREATE TABLE T (
  B INTEGER,
  C INTEGER
);

SELECT *
FROM T JOIN SOME ON T.B = SOME.B
WHERE T.C = SOME.A;

Here SOME.A will be treated as a column of table SOME. But what if I want to use constant A from package SOME?

[aafemt]

I believe when deciding upon how to handle schema vs package naming, we must first answer what we are using schema for?

It is better to ask what advantages Firebird packages have over Firebird schemas? Currently I see no advantages so packages can be declared deprecated and disregarded.

[EPluribusUnum]

@@aafemt , packages can have 'private' (not published) methods. (visibility control)

[sim1984]

ROW TYPE (named) and CONST are going to be added at the package level. There is no need to include these objects in the schema.

I would like to point out that the name resolution operator in the program code is rather an exception to the rule. That is, we should try to avoid ambiguous names, but since we cannot prohibit the creation of conflicting entities, in some cases we should somehow indicate to the parser what exactly was meant if a rule other than the default is required.

[aafemt]

packages can have 'private' (not published) methods. (visibility control)

They can, but they don't. This feature is not implemented and it is better (and simpler) to make procedures in schema which user don't have execution rights on invisible to them than private package members.

ROW TYPE (named) and CONST are going to be added at the package level. There is no need to include these objects in the schema.

But can you name a good reason NOT to include these objects into schemas?

[EPluribusUnum]

Name resoultion will happen at compile time or exection time?

[EPluribusUnum]

@aafemt? 'private' method is not about visibility to the user (user grant perspective), but about code visibility to other code (developer perspective). We don't publish every method from packages body to the head, this feature already exists in FB30 and we use this.

[sim1984]

But can you name a good reason NOT to include these objects into schemas?

At least because in this case these objects will require additional privileges. At the package level, everything is simpler, there are rights to use the package - then there are rights to everything that is declared in its header.

Packages solve three problems:

• hiding implementation details (encapsulation)
• simplifying work with dependencies
• simplifying work with privileges (within the package, you do not need to grant rights to each function)

And do not forget that packages significantly simplify the work of those who came from the Oracle world.

Secondly, in the case of constants, you do not solve anything in terms of name conflicts, they will also conflict with unspecified column names.

[asfernandes]

Please specify full example where you see the ambiguity, i.e., query and its metadata (or some comments making it clear).

Easy as pie.

CREATE PACKAGE SOME
BEGIN
CONST A INTEGER = 1;
END

CREATE TABLE SOME (
A INTEGER,
B INTEGER
);

CREATE TABLE T (
B INTEGER,
C INTEGER
);

SELECT *
FROM T JOIN SOME ON T.B = SOME.B
WHERE T.C = SOME.A;
Here SOME.A will be treated as a column of table SOME. But what if I want to use constant A from package SOME?

Then just replace SOME alias. We don't need disambiguate syntax for that at all as was already discussed in the constant RFC.

[EPluribusUnum]

@aafemt , also schema is not about access (grant) control.

[asfernandes]

Name resoultion will happen at compile time or exection time?

Compile (SQL->BLR) time.

[asfernandes]

Is it really better than just name1.name2 = package.name, name1..name2 = schema.name? I think cross-schema routine calls would be less frequent than packaged routine calls and local calls do not need schema prefix at all. Or do you want to avoid the confusion/inconsistency between schema.name for tables and schema..name for routines?

The current syntax has choosen because:

1. nobody strongly commented as wanting the first proposal (.. based)
2. nobody objected the last proposal
3. it's based on DBMS with packages and schemas (DB2)
4. it makes the standard part (schemas) more conformant
5. it makes plugins queries more secure by default
6. it is now already implemented so schemas integration can advance

[asfernandes]

I believe when deciding upon how to handle schema vs package naming, we must first answer what we are using schema for?

We're actively discussing it in fb-devel by a year, and no, it's not based in MSSQL, we had a number of problems to resolve even without its "flexibility".

[EPluribusUnum]

@asfernandes , suggestion for a new firebird.conf option : NameAmbiguityError.
0/false by default
But when it is ON, then in case of actual ambiguity SQL exception should be raised. (better security)